[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