mysql - 在杂耍者模型中将二进制 16 id 转换为 36 字符 uuid

Question

这是我的简短问题:

我正在使用 StrongLoop 来处理现有的 MySQL 数据库。数据库使用二进制 16 数据类型来存储主键和外键。当我使用 Strongloop 工具创建模型时，数据会作为 JSON 中的字符串数组发送到客户端。我希望 JSON 包含转换后的 36 个字符的字符串。在数据库中，我有 bintouuid 和 uuidtobin 函数，可以将数据转换为 36 字符格式或从 36 字符格式转换数据。任何人都可以提供我将在模型或 REST 服务中使用的代码来扩展它以将数组(或二进制数据)转换为所需的字符串格式吗？

以下是该场景的详细信息:

表格脚本如下所示:

CREATE TABLE `alert` (
  `ID` binary(16) NOT NULL,
  `Subject` varchar(255),
  `Text` text NOT NULL,
  `Read` int(11) DEFAULT '0',
  `AddedDate` timestamp NULL DEFAULT NULL,
  `LastUpdated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `IsDeleted` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

我有用于创建 uuid 并将其转换为二进制格式的函数:

newUuidToBin()

CREATE DEFINER=`root`@`localhost` FUNCTION `newuuidtobin`() RETURNS binary(16)
BEGIN
DECLARE UUID char(37);
SET UUID = UUID();
RETURN uuidtobin(UUID);
END

uuidToBin(UUID char(36))

CREATE DEFINER=`root`@`localhost` FUNCTION `uuidtobin`(UUID char(36)) RETURNS binary(16)
BEGIN
RETURN CONCAT(UNHEX(LEFT(UUID,8)),UNHEX(MID(UUID,10,4)),UNHEX(MID(UUID,15,4)),UNHEX(MID(UUID,20,4)),UNHEX(RIGHT(UUID,12)));
END

binToUuid(UUID 二进制(16))

CREATE DEFINER=`root`@`localhost` FUNCTION `bintouuid`(UUID BINARY(16)) RETURNS char(36) CHARSET utf8
BEGIN
RETURN concat(HEX(LEFT(uuid,4)),'-', HEX(MID(uuid,5,2)),'-', HEX(MID(uuid,7,2)),'-',HEX(MID(uuid,9,2)),'-',HEX(RIGHT(uuid,6)));
END

我有一个触发器，可以在创建记录时创建二进制 uuid:

CREATE TRIGGER before_insert_alert
   BEFORE INSERT
   ON road.alert
   FOR EACH ROW
SET new.id = newuuidtobin();

我的模型的默认 Alert.json 如下所示:

{
  "name": "Alert",
  "base": "PersistedModel",
  "idInjection": false,
  "mysql": {
    "schema": "messaging",
    "table": "alert"
  },
  "properties": {
    "id": {
      "type": "Binary",
      "id": true,
      "required": true,
      "length": 16,
      "precision": null,
      "scale": null,
      "mysql": {
        "columnName": "ID",
        "dataType": "binary",
        "dataLength": 16,
        "dataPrecision": null,
        "dataScale": null,
        "nullable": "N"
      },
      "_selectable": false
    },
    "subject": {
      "type": "String",
      "required": false,
      "length": 65535,
      "precision": null,
      "scale": null,
      "mysql": {
        "columnName": "Subject",
        "dataType": "text",
        "dataLength": 65535,
        "dataPrecision": null,
        "dataScale": null,
        "nullable": "Y"
      },
      "_selectable": true
    },
    "text": {
      "type": "String",
      "required": true,
      "length": 255,
      "precision": null,
      "scale": null,
      "mysql": {
        "columnName": "Text",
        "dataType": "varchar",
        "dataLength": 255,
        "dataPrecision": null,
        "dataScale": null,
        "nullable": "N"
      },
      "_selectable": false
    },
    "read": {
      "type": "Number",
      "required": false,
      "length": null,
      "precision": 10,
      "scale": 0,
      "mysql": {
        "columnName": "Read",
        "dataType": "int",
        "dataLength": null,
        "dataPrecision": 10,
        "dataScale": 0,
        "nullable": "Y"
      },
      "_selectable": true
    },
    "addeddate": {
      "type": "Date",
      "required": false,
      "length": null,
      "precision": null,
      "scale": null,
      "mysql": {
        "columnName": "AddedDate",
        "dataType": "timestamp",
        "dataLength": null,
        "dataPrecision": null,
        "dataScale": null,
        "nullable": "Y"
      },
      "_selectable": true
    },
    "lastupdated": {
      "type": "Date",
      "required": true,
      "length": null,
      "precision": null,
      "scale": null,
      "mysql": {
        "columnName": "LastUpdated",
        "dataType": "timestamp",
        "dataLength": null,
        "dataPrecision": null,
        "dataScale": null,
        "nullable": "N"
      },
      "_selectable": false
    },
    "isdeleted": {
      "type": "Binary",
      "required": true,
      "length": null,
      "precision": 1,
      "scale": null,
      "mysql": {
        "columnName": "IsDeleted",
        "dataType": "bit",
        "dataLength": null,
        "dataPrecision": 1,
        "dataScale": null,
        "nullable": "N"
      },
      "_selectable": false
    }
  },
  "validations": [],
  "relations": {},
  "acls": [],
  "methods": []
}

使用 get 访问 REST 端点时，

http://localhost:3000/api/Alerts

...我得到这个结果。注意:id 以数组的形式返回。

[
  {
    "id": [
      97,
      55,
      50,
      102,
      52,
      49,
      50,
      48,
      45,
      57,
      53,
      54,
      97,
      45,
      49,
      49
    ],
    "subject": "uuids",
    "text": "my message",
    "read": 0,
    "addeddate": null,
    "lastupdated": "2015-01-05T23:10:03.000Z",
    "isdeleted": [
      0
    ]
  },
.
.
.
]

...但我希望结果如下所示:

[
  {
    "id": "C39BC3A2-381F-5568-11C3-A0C2B66E64EF"
    "subject": "uuids",
    "text": "my message",
    "read": 0,
    "addeddate": null,
    "lastupdated": "2015-01-05T23:10:03.000Z",
    "isdeleted": [
      0
    ]
  },
.
.
.
]

更新时，REST 端点应使用 mysql 函数转换字符串:

update alert set `read` = 1 where id = uuidtobin('C39BC3A2-381F-5568-11C3-A0C2B66E64EF');

如何扩展模型以使用 mysql 函数来选择将 ids 显示为 json 结果中 uuid 字符串的数据，并使用 uuid 36 字符串更新记录？

Answer 1

您可以在发送回响应之前使用远程 Hook 来操作响应。请参阅http://docs.strongloop.com/display/LB/Remote+hooks

另一种可能的解决方案是使用模型 Hook 在保存数据之前/之后修改数据。请参阅http://docs.strongloop.com/display/LB/Model+hooks