migrate.ts

/**
 * ------------------------------------------------------------------
 * Compare and update the database with entity definitions.
 * ------------------------------------------------------------------
 */

import "lib/std"
import "lib/array"
import * as config from "lib/config"
import * as orm from "lib/orm";

let dropTables: number = 0;
let createTables: number = 0;
let alterColumns: number = 0;
let createColumns: number = 0;
let dropColumns: number = 0;
let execute: boolean;

function main(conn: string) {
    let db = getDB(conn);
    if (db == null) {
        db = config.getDB();
    } else {
        execute = true;
    }

    let entityMap = getEntities();
    let entities = map.values(entityMap) as orm.Entity[];

    let tenantEntities = entities.where(t => !t.isSystem)
    let tenants = db.setDatabase("system").loadTable("SELECT name FROM tenant");
    for (let row of tenants.rows) {
        let tenant = row.name;
        db.setDatabase(tenant)
        updateTenant(db, tenantEntities)
    }

    let systemEntities = entities.where(t => t.isSystem)
    db.setDatabase("system")
    updateTenant(db, systemEntities);

    fmt.println()
    fmt.println("Summary:")
    fmt.println("dropTables", dropTables)
    fmt.println("createTables", createTables)
    fmt.println("alterColumns", alterColumns)
    fmt.println("createColumns", createColumns)
    fmt.println("dropColumns", dropColumns)
}

function getDB(conn: string) {
    switch (conn) {
        case null:
            return null;

        case "0":
            return config.getDB();

        case "1":
            return sql.open("mysql", "clay:@tcp(127.0.0.1:2001)/");

        default:
            throw "Invalid conn " + conn;
    }
}

function updateTenant(db: sql.DB, entities: orm.Entity[]) {
    fmt.println(db.database)

    let tables = db.loadTable("SHOW tables").rows.select(t => t[0])
    for (var entity of entities) {
        let i = tables.indexOf(entity.name.toLower())
        if (i == -1) {
            let q = orm.createTableQuery(entity)
            createTables++;
            fmt.println(q);
            if (execute) {
                db.exec(q)
            }
            continue;
        }

        updateTable(db, entity)
        tables.removeAt(i)
    }

    for (let table of tables) {
        let q = fmt.sprintf("DROP TABLE %s.%s", db.database, table);
        dropTables++;
        fmt.println(q);
        if (execute) {
            db.execRaw(q)
        }
    }
}

interface Column {
    Field: string;
    Type: string;
    Null: string;
    Key: string;
}

function updateTable(db: sql.DB, entity: orm.Entity) {
    let cols: Column[] = <any>db.loadTable("SHOW columns FROM " + entity.name.toLower()).rows.select(t => t);

    for (let p of entity.properties) {
        let i = cols.firstIndex(t => strings.equalFold(t.Field, p.name));
        if (i == -1) {
            var q = fmt.sprintf("ALTER TABLE %s.%s ADD COLUMN %v %v %v",
                db.database,
                entity.name.toLower(),
                p.name,
                orm.convertToSQL(p),
                p.nullable ? "null" : "not null " + getDefaultValueClause(p));
            alterColumns++;
            fmt.println(q);
            if (execute) {
                db.execRaw(q)
            }
            continue;
        }

        let col = cols[i];
        cols.removeAt(i);

        let err = compareColumn(p, col);
        if (err) {
            if (!p.nullable && col.Null == "YES") {
                var q = fmt.sprintf("UPDATE %s SET %s = ? WHERE %s IS NULL", entity.name.toLower(), p.name, p.name);
                fmt.println(q);
                if (execute) {
                    db.exec(q, getDefaultValue(p))
                }
            }

            var q: string;
            if (p.name != col.Field) {
                q = fmt.sprintf("ALTER TABLE %s.%s CHANGE %s %s %v %s",
                    db.database,
                    entity.name.toLower(),
                    col.Field,
                    p.name,
                    orm.convertToSQL(p),
                    p.nullable ? "null" : "not null");
            } else {
                q = fmt.sprintf("ALTER TABLE %s.%s MODIFY %s %v %s",
                    db.database,
                    entity.name.toLower(),
                    p.name,
                    orm.convertToSQL(p),
                    p.nullable ? "null" : "not null");
            }

            alterColumns++;
            fmt.println(q);
            if (execute) {
                db.execRaw(q)
            }
            continue;
        }
    }

    for (let col of cols) {
        var q = fmt.sprintf("ALTER TABLE %s.%s DROP COLUMN %s", db.database, entity.name.toLower(), col.Field);
        dropColumns++;
        fmt.println(q);
        if (execute) {
            db.execRaw(q)
        }
    }
}

function compareColumn(p: orm.Property, col: Column) {
    if (p.name != col.Field) {
        return "Invalid case " + p.name + " != " + col.Field;
    }

    let pc = toSQLColumn(p);
    if (pc.Type != col.Type) {
        return pc.Type + " != " + col.Type;
    }
    if (pc.Null != col.Null) {
        return pc.Null + " != " + col.Null;
    }
}

function toSQLColumn(p: orm.Property) {
    if (p.name == "id") {
        return { Field: "id", Type: "int(11)", Null: "NO", Key: "PRI" }
    }

    let type;
    switch (p.type) {
        case "bool":
            type = "tinyint(1)";
        default:
            type = orm.convertToSQL(p)
    }

    if (type == "int") {
        type += "(11)";
    }

    return {
        Field: p.name,
        Type: type,
        Null: p.nullable ? "YES" : "NO"
    }
}

function getEntities() {
    let p = runtime.compile(filepath.join(user.homeDir, "amura/src/server/one/main.ts"));
    let vm = runtime.newVM(p)
    vm.setTrusted(true)
    vm.initialize();

    let f = p.functions.first(t => t.exported && t.fullName == "lib.orm.getEntities");
    if (!f) {
        throw "no orm.getEntities func"
    }
    return vm.runFunc(f.index) as orm.Entity[];
}

function getDefaultValueClause(p: orm.Property) {
    switch (p.type) {
        case "bool":
            return "DEFAULT false";

        case "string":
        case "encrypted":
        case "image":
        case "file":
        case "url":
        case "email":
        case "phone":
        case "hash":
        case "markdown":
        case "text":
        case "blob":
            return "DEFAULT ''";

        case "date":
        case "datetime":
        case "time":
            return "DEFAULT '1970-1-1'";

        case "int":
        case "lookup":
        case "picklist":
        case "float":
        case "currency":
            return "DEFAULT 0";
    }
}

function getDefaultValue(p: orm.Property) {
    switch (p.type) {
        case "bool":
            return false;

        case "string":
        case "encrypted":
        case "image":
        case "file":
        case "url":
        case "email":
        case "phone":
        case "hash":
        case "markdown":
        case "text":
        case "blob":
            return "";

        case "date":
        case "datetime":
        case "time":
            return time.date(1970, 1, 1);

        case "int":
        case "lookup":
        case "picklist":
        case "float":
        case "currency":
            return 0;
    }
}