setupSchema.js

var mysql = require('mysql') ;
var dbClient = undefined ;
var db_uri = "mysql://clickpoint:password@127.0.0.1/clickpoint" ;

var schema = {
    redirects : "(id int AUTO_INCREMENT, key VARCHAR(50), url VARCHAR(2048))",
    clicks : "(ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, IP VARBINARY(16))"
} ;

function createOnEmpty(err, results, fields, tableName, create_def) {
    console.log("createOnEmpty called on " + tableName) ;
    if (err) {
        console.error(err) ;
        process.exit(1) ;
    } else {
        if (0 == results.length) {
            dbClient.query(["create table ", tableName, create_def].join(" "),
                           function (err, results, fields) {} ) ;
        } else {
            console.log(tableName + " table already exists.") ;
        }
    }
    console.log("\n\n") ;
}

function setupSchema() {
    for (table in schema) {
        console.log("Checking for table: " + table) ;
        // FIXME: Why does this always seem to pass clicks as tablename?!
        dbClient.query("show tables LIKE '" + table + "'",
                       function (err, results, fields) {
                           createOnEmpty(err, results, fields, table,
                                         schema[table]) } ) ;
    }
}

function handleDBConnect(err) {
    if (err) {
        console.error("ERROR: problem connecting to DB: " + err.code) ;
        process.exit(1) ;
    } else {
        console.log("Connected to database.") ;
        // Automatically set up the schema, if the tables don't exist
        setupSchema() ;
    }
}

function MySQLConnect() {
    dbClient = mysql.createConnection(db_uri) ;
    dbClient.connect(handleDBConnect) ;
}

MySQLConnect() ;