Bob Browning's blog

I have been working on my hobby project, which is a generic database updating tool. It uses an extension of the JSON-Schema standard. But how do you deal with a new database from a legacy database.

I found a really simple solution, I asked ChatGTP to produce a JSON-Schema based on a database dump – which happily is a set of SQL statements.

It did it accurately. To my surprise it added an extra ‘description’ field based on the field names, which was uncannily accurate. I then asked it to add a custom friendlyName field as a short description.

The original SQL and the JSON-Schema it produced follow. Not bad.

CREATE TABLE `audit` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `createdAt` bigint DEFAULT NULL,
  `updatedAt` bigint DEFAULT NULL,
  `updatedBy` int DEFAULT NULL,
  `tableName` varchar(50) DEFAULT NULL,
  `mode` varchar(10) DEFAULT NULL,
  `row` varchar(255) DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `data` longtext,
  PRIMARY KEY (`id`)
);

{
  "$schema": "http://json-schema.org/draft-07/schema#",
  "type": "object",
  "properties": {
    "id": {
      "type": "integer",
      "description": "Primary key, auto-incremented",
      "friendlyName": "ID",
      "minimum": 0
    },
    "createdAt": {
      "type": "integer",
      "description": "Timestamp when the record was created",
      "friendlyName": "Created At",
      "default": null
    },
    "updatedAt": {
      "type": "integer",
      "description": "Timestamp when the record was last updated",
      "friendlyName": "Updated At",
      "default": null
    },
    "updatedBy": {
      "type": "integer",
      "description": "ID of the user who last updated the record",
      "friendlyName": "Updated By",
      "default": null
    },
    "tableName": {
      "type": "string",
      "maxLength": 50,
      "description": "Name of the table being audited",
      "friendlyName": "Table Name",
      "default": null
    },
    "mode": {
      "type": "string",
      "maxLength": 10,
      "description": "Mode of the audit (e.g., insert, update, delete)",
      "friendlyName": "Audit Mode",
      "default": null
    },
    "row": {
      "type": "string",
      "maxLength": 255,
      "description": "String representation of the row affected by the audit",
      "friendlyName": "Affected Row",
      "default": null
    },
    "notes": {
      "type": "string",
      "maxLength": 255,
      "description": "Additional notes about the audit",
      "friendlyName": "Audit Notes",
      "default": null
    },
    "data": {
      "type": "string",
      "description": "Long text field for additional audit data",
      "friendlyName": "Audit Data"
    }
  },
  "required": ["id"],
  "additionalProperties": false
}

I asked ChatGTP how it derived the description field and got this, I couldn’t really argue.

• id: I inferred that it is the primary key, and since it’s auto-incremented, I added a description indicating that.
• createdAt and updatedAt: These are common fields to track timestamps for creation and last update, so I described them as such.
• updatedBy: I described this as an ID for the user who last updated the record.
• tableName: This seems to refer to the name of the table being audited, so I added that description.
• mode: In auditing, “mode” typically refers to the type of action (like insert, update, delete), which is why I described it that way.
• row: This column likely represents the row that was affected, hence the description.notes: Described as additional information about the audit entry.
• data: As a longtext, this column is meant to store large amounts of data, which is reflected in the description.
• friendlyName: I’ve added a concise, user-friendly name for each property. These can be used in your software to display a shorter or more intuitive label for each field (for instance, “ID” instead of “Primary key, auto-incremented”).

I asked it to product a TypeScript type for the record and it did that perfectly as well. Note that this was produced while I was writing a later post. This second test picked up an anomaly. When I repeated this test with the exact same SQL it picked up data as being mandatory! More on this in Part 2.

type Audit = {
  id: number; // unsigned int, primary key
  createdAt?: number; // bigint, optional
  updatedAt?: number; // bigint, optional
  updatedBy?: number; // int, optional
  tableName?: string; // varchar(50), optional
  mode?: string; // varchar(10), optional
  row?: string; // varchar(255), optional
  notes?: string; // varchar(255), optional
  data: string; // longtext, assumed to be string
};