Crudtable Reference | Smartchart开发手册

Introduction #

CRUD templates have certain database table structure requirements. MySQL is recommended as the target database. This document provides table creation references for MySQL, SQLite3, PostgreSQL, Oracle, and SQL Server, including standard field definitions for primary keys, auto-increment, timestamps, and updater fields.

Required Field	Description
`id`	Primary key, auto-increment
`tid`	Tenant ID (multi-tenant scenarios)
`create_time`	Creation time
`update_time`	Update time
`updater`	Updated by (auto-filled via `$username`)

-- sqlite3
CREATE TABLE work_order (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_no TEXT UNIQUE,
    order_type TEXT,
    order_qty INTEGER,
    create_time DATETIME DEFAULT (datetime('now', 'localtime')),
    update_time DATETIME DEFAULT (datetime('now', 'localtime')),
    updater TEXT
);

-- postgreSQL
CREATE TABLE work_order (
    id SERIAL PRIMARY KEY,
    "order_no" VARCHAR(50) UNIQUE,
    "order_type" VARCHAR(50),
    "order_qty" INT,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updater VARCHAR(50)
);
COMMENT ON TABLE work_order IS 'Work Order';
COMMENT ON COLUMN work_order.updater IS 'Updater';

-- oracle
CREATE TABLE work_order (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    "order_no" VARCHAR2(50) UNIQUE,
    "order_type" VARCHAR2(50),
    "order_qty" NUMBER(10),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updater VARCHAR2(50)
);
COMMENT ON TABLE work_order IS 'Work Order';
COMMENT ON COLUMN work_order.updater IS 'Updater';

-- sqlserver
CREATE TABLE work_order (
    id INT IDENTITY(1,1) PRIMARY KEY,
    [order_no] NVARCHAR(50) UNIQUE,
    [order_type] NVARCHAR(50),
    [order_qty] INT,
    create_time DATETIME2 DEFAULT GETDATE(),
    updater NVARCHAR(50)
);

EXEC sp_addextendedproperty
    @name = N'MS_Description',
    @value = N'Work Order',
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE',  @level1name = 'work_order';

EXEC sp_addextendedproperty
    @name = N'MS_Description',
    @value = N'Updater',
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE',  @level1name = 'work_order',
    @level2type = N'COLUMN', @level2name = 'updater';