[go: up one dir, main page]

[db] Table manipulation for Fluid SQL builder

Add the following functionality:

CREATE TABLE

db.table(mytable)
    .field("id", Integer, 8, true, true, true) // notnull, unique, pk
    .field("name", String, 10, "Unset")
    .field("total", Float, 2, true)
    .field("updated", LocalDateTime)
    .create([
       charset : "UTF16",
       comment : "My Comment"
    ])
/*
 CREATE TABLE IF NOT EXISTS mytable (
    id UNSIGNED INT(8) PRIMARY KEY UNIQUE NOT NULL, 
    name VARCHAR(10) DEFAULT "Unset",
    total DECIMAL(2,2) NOT NULL,
    updated DATETIME,
 ) CHARACTER SET=UTF16
 COMMENT='My Comment'
*/

Note: We are using Java classes like Integer, but we could use enum so we can represent Unsigned Int easily.

ALTER TABLE

db.table(mytable).dropField("total")
db.table(mytable).dropFields("total", "updated")
// ALTER TABLE mytable DROP COLUMN total

db.table(mytable).addField("total", Integer, 8, "name")
// ALTER TABLE mytable ADD COLUMN total INT(8) AFTER name

db.table(mytable).renameField("total","sum")
// ALTER TABLE mytable RENAME COLUMN total TO sum

db.table(mytable).field("name").addFK("table2", "name2", CASCADE)
// ALTER TABLE mytable ADD CONSTRAINT name_fk FOREIGN KEY(name) REFERENCES table2(name2) ON DELETE CASCADE

db.table(mytable).dropFK("name")
// ALTER TABLE mytable DROP FOREIGN KEY name_fk

db.table(mytable).addUnique("name", "age")
// ALTER TABLE mytable ADD CONSTRAINT name_age_uniq UNIQUE(name, age) 

db.table(mytable).dropUnique("name", "age)
// ALTER TABLE mytable DROP INDEX name_age_uniq

VIEWS

db.table(mytable).fields("one","two").createView("myview")
// CREATE OR REPLACE VIEW myview AS SELECT one,two FROM mytable

db.table(myview).drop()
// DROP VIEW myview

If #22 is added, we can create views using join

To upload designs, you'll need to enable LFS and have an admin enable hashed storage. More information