title: convert binary 16 id to 36 character uuid in juggler models
body: <p>Here is my question in brief: </p>
  
  <p>I am using StrongLoop to work with an existing MySQL database. The database uses binary 16 datatypes to store primary keys and foreign keys. When I create the models using Strongloop tools, the data is sent to the client as string arrays in the JSON. I would like the JSON to contain the converted 36 character string. In the database, I have bintouuid and uuidtobin functions that will convert the data to/from the 36 character format. Can anyone provide the code I would use in the model or REST services to extend it to convert the arrays (or binary data) into the needed string format?</p>
  
  <p>Here are details around the scenario:</p>
  
  <p>The table script looks like this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I have functions for creating and converting uuids to/from binary format:</p>
  
  <p>newUuidToBin()</p>
  
  <pre><code>CREATE DEFINER=`root`@`localhost` FUNCTION `newuuidtobin`() RETURNS binary(16)
  BEGIN
  DECLARE UUID char(37);
  SET UUID = UUID();
  RETURN uuidtobin(UUID);
  END
  </code></pre>
  
  <p>uuidToBin(UUID char(36))</p>
  
  <pre><code>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
  </code></pre>
  
  <p>binToUuid(UUID BINARY(16))</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I have a trigger that creates the binary uuid when a record is created:</p>
  
  <pre><code>CREATE TRIGGER before_insert_alert
     BEFORE INSERT
     ON road.alert
     FOR EACH ROW
  SET new.id = newuuidtobin();
  </code></pre>
  
  <p>My default Alert.json for the model looks like this:</p>
  
  <pre><code>{
    "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": []
  }
  </code></pre>
  
  <p>When accessing the REST endpoint with a get, </p>
  
  <pre><code>http://localhost:3000/api/Alerts
  </code></pre>
  
  <p>...I get this result. Note: the id comes back in the form of an array.</p>
  
  <pre><code>[
    {
      "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
      ]
    },
  .
  .
  .
  ]
  </code></pre>
  
  <p>...but I want the result to look like this:</p>
  
  <pre><code>[
    {
      "id": "C39BC3A2-381F-5568-11C3-A0C2B66E64EF"
      "subject": "uuids",
      "text": "my message",
      "read": 0,
      "addeddate": null,
      "lastupdated": "2015-01-05T23:10:03.000Z",
      "isdeleted": [
        0
      ]
    },
  .
  .
  .
  ]
  </code></pre>
  
  <p>On update, the REST endpoint should convert the string using the mysql function:</p>
  
  <pre><code>update alert set `read` = 1 where id = uuidtobin('C39BC3A2-381F-5568-11C3-A0C2B66E64EF');
  </code></pre>
  
  <p>How would I extend the model to use the mysql functions to select data showing the ids as uuid strings in the json result, and update records using the uuid 36 character string?</p>