vonbraunlabs / sql-ddl-generator
Requires
- php: ^7.0|^8.0
Requires (Dev)
- phpstan/phpstan: ^0.9.1
- squizlabs/php_codesniffer: ^3.2
README
A tool to aid on generating DDL SQL. It was conceived to abstract away the boring part of writing the SQL tables and let developers/DBAs focus on the business logic. This tool assumes that every table have:
- an integer ID column that uniquely identifies the row and is named ID
- audit columns to specify created time and last updated time
- audit columns to specify the db user who created the row and the one who last updated the row
active
column that indicates wether that row is active or should be regarded as deleted/inactive
The file user.json
contains the following JSON:
{ "name": "mydatabase", "table": { "name": "user", "field_list": [ { "name": "username", "type": "VARCHAR(30)", "not_null": true, "comment": "Login username" }, { "name": "email", "type": "VARCHAR(64)", "not_null": true, "comment": "User email" }, { "name": "password", "type": "VARCHAR(128)", "not_null": true, "comment": "User password" }, { "name": "confirmed_at", "type": "DATETIME", "not_null": false, "comment": "User confirmation date" } ], "fk_list": [ { "name": "inviter_id", "references": "user", "comment": "If user was invited by someone, contains the ID of that user account, NULL otherwise" } ], "unique_list": [ ["username"], ["email"] ] } }
The name of the schema/database is mydatabase
. It is creating a table named
user
, with four columns: username
, email
, password
and confirmed_at
.
Moreover, it declares a foreign key column inviter_id
to itself. On its last
part, the snippet declares UNIQUE constraints. The rows must have unique
username
and unique email
. Executing ./sql-ddl-generator user.json
the
following is printed to standard output:
-- Generated by VBL - sql-ddl-generator -- ----------------------------------------------------- -- Database mydatabase -- ----------------------------------------------------- CREATE SCHEMA IF NOT EXISTS `mydatabase` DEFAULT CHARACTER SET utf8 ; USE `mydatabase`; -- ----------------------------------------------------- -- Table `mydatabase`.`user` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydatabase`.`user` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `username` VARCHAR(30) NOT NULL COMMENT 'Login username', `email` VARCHAR(64) NOT NULL COMMENT 'User email', `password` VARCHAR(128) NOT NULL COMMENT 'User password', `confirmed_at` DATETIME COMMENT 'User confirmation date', `active` BOOLEAN NOT NULL DEFAULT 1, `create_by` VARCHAR(32) NOT NULL, `create_time` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), `update_by` VARCHAR(32) NOT NULL, `update_time` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), `inviter_id` INT UNSIGNED COMMENT 'If user was invited by someone, contains the ID of that user account, NULL otherwise', PRIMARY KEY (`id`), CONSTRAINT `fk_user_inviter_id` FOREIGN KEY(`inviter_id`) REFERENCES `mydatabase`.`user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) Engine=InnoDB; CREATE UNIQUE INDEX `unique_user_username` ON `mydatabase`.`user` (`username`); CREATE UNIQUE INDEX `unique_user_email` ON `mydatabase`.`user` (`email`); CREATE TRIGGER `user_before_insert` BEFORE INSERT ON `mydatabase`.`user` FOR EACH ROW SET NEW.`create_by` = CURRENT_USER(), NEW.`update_by` = CURRENT_USER(); CREATE TRIGGER `user_before_update` BEFORE UPDATE ON `mydatabase`.`user` FOR EACH ROW SET NEW.`update_by` = CURRENT_USER(), NEW.`update_time` = CURRENT_TIMESTAMP(3);
Auditing Special Feature
For some application, it is necessary to keep track of all changes on table,
usually for auditing porposes. The audit
flag creates a clone of the table
with the prefix audit_
in its name and store all changes on the clonned table.
If a row is updated N times, there will be N records of that specific row on the
equivalent audit table.
{
"name": "mydatabase",
"table": {
"name": "user",
"field_list": [
{
"name": "username",
"type": "VARCHAR(30)",
"not_null": true,
"comment": "Login username"
},
{
"name": "email",
"type": "VARCHAR(64)",
"not_null": true,
"comment": "User email"
},
{
"name": "password",
"type": "VARCHAR(128)",
"not_null": true,
"comment": "User password"
},
{
"name": "confirmed_at",
"type": "DATETIME",
"not_null": false,
"comment": "User confirmation date"
}
],
"fk_list": [
{
"name": "inviter_id",
"references": "user",
"comment": "If user was invited by someone, contains the ID of that user account, NULL otherwise"
}
],
"unique_list": [
["username"],
["email"]
],
"audit": true
}
}
Consider the JSON above. From this model, the sql-ddl-generator will produce SQL below. Mind how tedious it would be to write that that SQL by hand. Now imagine you have many tables that require that auditing feature.
-- Generated by VBL - sql-ddl-generator -- ----------------------------------------------------- -- Database mydatabase -- ----------------------------------------------------- CREATE SCHEMA IF NOT EXISTS `mydatabase` DEFAULT CHARACTER SET utf8 ; USE `mydatabase`; -- ----------------------------------------------------- -- Table `mydatabase`.`user` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydatabase`.`user` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `username` VARCHAR(30) NOT NULL COMMENT 'Login username', `email` VARCHAR(64) NOT NULL COMMENT 'User email', `password` VARCHAR(128) NOT NULL COMMENT 'User password', `confirmed_at` DATETIME COMMENT 'User confirmation date', `active` BOOLEAN NOT NULL DEFAULT 1, `create_by` VARCHAR(32) NOT NULL, `create_time` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), `update_by` VARCHAR(32) NOT NULL, `update_time` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), `who_id` INT UNSIGNED NOT NULL, `inviter_id` INT UNSIGNED COMMENT 'If user was invited by someone, contains the ID of that user account, NULL otherwise', PRIMARY KEY (`id`), CONSTRAINT `fk_user_who_id` FOREIGN KEY(`who_id`) REFERENCES `mydatabase`.`user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_user_inviter_id` FOREIGN KEY(`inviter_id`) REFERENCES `mydatabase`.`user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) Engine=InnoDB; CREATE UNIQUE INDEX `unique_user_username` ON `mydatabase`.`user` (`username`); CREATE UNIQUE INDEX `unique_user_email` ON `mydatabase`.`user` (`email`); CREATE TRIGGER `user_before_insert` BEFORE INSERT ON `mydatabase`.`user` FOR EACH ROW SET NEW.`create_by` = CURRENT_USER(), NEW.`update_by` = CURRENT_USER(); CREATE TRIGGER `user_before_update` BEFORE UPDATE ON `mydatabase`.`user` FOR EACH ROW SET NEW.`update_by` = CURRENT_USER(), NEW.`update_time` = CURRENT_TIMESTAMP(3); -- ----------------------------------------------------- -- Audit Table for user -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydatabase`.`audit_user` LIKE `mydatabase`.`user`; ALTER TABLE `mydatabase`.`audit_user` ADD COLUMN `user_id` INT UNSIGNED NOT NULL AFTER `id`; ALTER TABLE `mydatabase`.`audit_user` ADD CONSTRAINT `fk_audit_user_user_id` FOREIGN KEY(`user_id`) REFERENCES `mydatabase`.`user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION; DROP INDEX `unique_user_username` ON `mydatabase`.`audit_user`; DROP INDEX `unique_user_email` ON `mydatabase`.`audit_user`; ALTER TABLE `mydatabase`.`audit_user` ADD CONSTRAINT `fk_audit_user_who_id` FOREIGN KEY(`who_id`) REFERENCES `mydatabase`.`user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE `mydatabase`.`audit_user` ADD CONSTRAINT `fk_audit_user_inviter_id` FOREIGN KEY(`inviter_id`) REFERENCES `mydatabase`.`user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION; delimiter // CREATE TRIGGER `audit_user_insert_trigger` AFTER INSERT ON `mydatabase`.`user` FOR EACH ROW BEGIN INSERT INTO `mydatabase`.`audit_user` ( `user_id`, `username`, `email`, `password`, `confirmed_at`, `active`, `create_by`, `update_by`, `who_id`, `inviter_id` ) VALUES ( NEW.`id`, NEW.`username`, NEW.`email`, NEW.`password`, NEW.`confirmed_at`, NEW.`active`, NEW.`create_by`, NEW.`update_by`, NEW.`who_id`, NEW.`inviter_id` ); END;// DELIMITER ; delimiter // CREATE TRIGGER `audit_user_update_trigger` AFTER UPDATE ON `mydatabase`.`user` FOR EACH ROW BEGIN INSERT INTO `mydatabase`.`audit_user` ( `user_id`, `username`, `email`, `password`, `confirmed_at`, `active`, `create_by`, `update_by`, `who_id`, `inviter_id` ) VALUES ( NEW.`id`, NEW.`username`, NEW.`email`, NEW.`password`, NEW.`confirmed_at`, NEW.`active`, NEW.`create_by`, NEW.`update_by`, NEW.`who_id`, NEW.`inviter_id` ); END;// DELIMITER ;