vonbraunlabs/sql-ddl-generator

该软件包最新版本（0.1.10）没有提供许可证信息。

维护者

详细信息

github.com/vonbraunlabs/sql-ddl-generator

源代码

问题

安装: 735

依赖项: 0

建议者: 0

安全: 0

星星: 5

关注者: 2

分支: 1

开放问题: 0

0.1.10 2021-08-10 01:13 UTC

Requires

  • php: ^7.0|^8.0

Requires (Dev)

Unknown License dae30743c77b88071bf01ad22565bd4cb893dcc3

  • Diogo Oliveira de Melo <melo.woop@vonbraunlabs.com.br>

This package is auto-updated.

Last update: 2024-09-07 17:42:41 UTC

README

一个辅助生成DDL SQL的工具。该工具旨在抽象出编写SQL表时的枯燥部分，让开发人员/数据库管理员能够专注于业务逻辑。该工具假设每个表都有以下属性：

  • 一个整数ID列，用于唯一标识行，名称为ID
  • 审计列，用于指定创建时间和最后更新时间
  • 审计列，用于指定创建和最后更新行的数据库用户
  • active列，表示该行是否活动或应被视为已删除/非活动

文件user.json包含以下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"]
        ]
    }
}

模式/数据库的名称为mydatabase。它正在创建一个名为user的表，包含四个列：username、email、password和confirmed_at。此外，它声明了一个指向自身的表的外键列inviter_id。在最后一部分，代码片段声明了唯一约束。行必须具有唯一的username和唯一的email。执行./sql-ddl-generator user.json，以下内容将被打印到标准输出

-- 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);

审计特殊功能

对于某些应用程序，跟踪表上的所有更改是必要的，通常是为了审计目的。设置audit标志会在表名前加上前缀audit_，并存储所有更改到克隆的表中。如果某行被更新N次，则在其对应的审计表中将有N条该特定行的记录。

{
    "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
    }
}

考虑上面的JSON。从这个模型中，sql-ddl-generator将生成以下SQL。注意手动编写这样的SQL是多么繁琐。现在想象一下，如果你有许多需要审计功能的表。

-- 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 ;