Add versioning to perf SQL database

perf/go/db/db.go

 package db
 
 import (
         "database/sql"
         "fmt"
         "time"
 
         _ "github.com/go-sql-driver/mysql"
         "github.com/golang/glog"
         _ "github.com/mattn/go-sqlite3"
         "skia.googlesource.com/buildbot.git/perf/go/metadata"
+        "skia.googlesource.com/buildbot.git/perf/go/util"
 )
 
 var (
         // DB is the sql database where we have commit and annotation information stored.
         DB *sql.DB = nil
+
+        // Keeps track if we are connected to MySQL or SQLite
+        isMySQL = true
 )
 
 const (
+        // Key of the password for the readwrite user.
         METADATA_KEY = "readwrite"
+
+        // Path where the SQLite database is stored when running locally.
+        SQLITE_DB_PATH = "./perf.db"
 )
 
 // Init must be called once before DB is used.
 //
 // Since it used glog, make sure it is also called after flag.Parse is called.
-func Init() {
-        // Connect to MySQL server. First, get the password from the metadata server.
-        // See https://developers.google.com/compute/docs/metadata#custom.
-        password, err := metadata.Get(METADATA_KEY)
-        if err == nil {
-                // The IP address of the database is found here:
-                //    https://console.developers.google.com/project/31977622648/sql/instances/skiaperf/overview
-                // And 3306 is the default port for MySQL.
-                DB, err = sql.Open("mysql", fmt.Sprintf("readwrite:%s@tcp(173.194.104.24:3306)/skia?parseTime=true", password))
+func Init(mysqlConnStr string) {
+        // If there is a connection string then connect to the MySQL server.
+        // This is for testing only. In production we get the relevant information
+        // from the metadata server.
+        if mysqlConnStr != "" {
+                var err error
+                glog.Infoln("Opening local MySQL database: ", mysqlConnStr)
+                DB, err = sql.Open("mysql", mysqlConnStr)
                 if err != nil {
-                        glog.Fatalln("Failed to open connection to SQL server:", err)
+                        glog.Fatalln("Unable to connect to server: " + mysqlConnStr)
                 }
+                isMySQL = true
         } else {
-                glog.Infoln("Failed to find metadata, unable to connect to MySQL server (Expected when running locally):", err)
-                // Fallback to sqlite for local use.
-                DB, err = sql.Open("sqlite3", "./perf.db")
-                if err != nil {
-                        glog.Fatalln("Failed to open:", err)
+                // Connect to MySQL server. First, get the password from the metadata server.
+                // See https://developers.google.com/compute/docs/metadata#custom.
+                password, err := metadata.Get(METADATA_KEY)
+                if err == nil {
+                        // The IP address of the database is found here:
+                        //    https://console.developers.google.com/project/31977622648/sql/instances/skiaperf/overview
+                        // And 3306 is the default port for MySQL.
+                        DB, err = sql.Open("mysql", fmt.Sprintf("readwrite:%s@tcp(173.194.104.24:3306)/skia?parseTime=true", password))
+                        if err != nil {
+                                glog.Fatalln("Failed to open connection to SQL server:", err)
+                        }
+                } else {
+                        glog.Infoln("Failed to find metadata, unable to connect to MySQL server (Expected when running locally):", err)
+                        glog.Infof("Opening local sqlite database at: %s", SQLITE_DB_PATH)
+                        // Fallback to sqlite for local use.
+                        DB, err = sql.Open("sqlite3", SQLITE_DB_PATH)
+                        if err != nil {
+                                glog.Fatalln("Failed to open:", err)
+                        }
+                        isMySQL = false
                 }
+        }
 
-                sql := `CREATE TABLE clusters (
-      id         INTEGER      NOT NULL PRIMARY KEY AUTOINCREMENT,
-      ts         TIMESTAMP    NOT NULL,
-      hash       TEXT         NOT NULL,
-      regression FLOAT        NOT NULL,
-      cluster    MEDIUMTEXT   NOT NULL,
-      status     TEXT         NOT NULL,
-      message    TEXT         NOT NULL
-    )`
-                _, err = DB.Exec(sql)
-                glog.Infoln("Status creating sqlite table for clusters:", err)
-
-                sql = `CREATE TABLE shortcuts (
-      id      INTEGER     NOT NULL PRIMARY KEY AUTOINCREMENT,
-      traces  MEDIUMTEXT  NOT NULL
-    )`
-                _, err = DB.Exec(sql)
-                glog.Infoln("Status creating sqlite table for shortcuts:", err)
-
-                sql = `CREATE TABLE tries (
-      issue       VARCHAR(255) NOT NULL PRIMARY KEY,
-      lastUpdated TIMESTAMP    NOT NULL,
-      results     MEDIUMTEXT   NOT NULL
-    )`
-                _, err = DB.Exec(sql)
-                glog.Infoln("Status creating sqlite table for tries:", err)
+        // Make sure the migration table exists.
+        if err := ensureVersionTable(); err != nil {
+                glog.Fatalln("Unable to guarantee valid version table:", err.Error())
         }
 
         // Ping the database to keep the connection fresh.
         go func() {
                 c := time.Tick(1 * time.Minute)
                 for _ = range c {
                         if err := DB.Ping(); err != nil {
                                 glog.Warningln("Database failed to respond:", err)
                         }
                         glog.Infof("db: Successful ping")
                 }
         }()
 }
+
+func Migrate(targetVersion int) error {
+        if targetVersion < 0 {
+                glog.Fatalln("Target db version must be at least 0.")
+        }
+
+        // start a transaction
+        txn, txErr := DB.Begin()
+        if txErr != nil {
+                return txErr
+        }
+        defer func() {
+                if txErr != nil {
+                        txn.Rollback()
+                } else {
+                        txn.Commit()
+                }
+        }()
+
+        currentVersion, txErr := DBVersion()
+        if txErr != nil {
+                return txErr
+        }
+
+        // run through the transactions
+        runSteps := getMigrations(isMySQL, currentVersion, targetVersion)
+        for _, step := range runSteps {
+                for _, stmt := range step {
+                        _, txErr = txn.Exec(stmt)
+                        if txErr != nil {
+                                return txErr
+                        }
+                }
+        }
+
+        // update the dbversion table
+        txErr = setDBVersion(txn, targetVersion)
+        if txErr != nil {
+                return txErr
+        }
+
+        return nil
+}
+
+func DBVersion() (int, error) {
+        stmt := `
+                SELECT version

[tfarina, 2014/09/29 18:56:33]

does this fits in one line?

[stephana, 2014/09/30 13:43:06]

It probably could, I find it easier to read this way. 

+                FROM sk_db_version
+                WHERE id=1`
+
+        var version int
+        err := DB.QueryRow(stmt).Scan(&version)
+        return version, err
+}
+
+func MaxDBVersion() int {
+        return len(migrationSteps)
+}
+
+func setDBVersion(txn *sql.Tx, newDBVersion int) error {
+        stmt := `REPLACE INTO sk_db_version (id, version, updated) VALUES(1, ?, ?)`
+        _, err := txn.Exec(stmt, newDBVersion, time.Now().Unix())
+        return err
+}
+
+func ensureVersionTable() error {
+        txn, txnErr := DB.Begin()
+        defer func() {
+                if txnErr != nil {
+                        glog.Errorf("Encountered error rolling back: %s", txnErr.Error())
+                        txn.Rollback()
+                } else {
+                        glog.Infoln("Version table OK.")
+                        txn.Commit()
+                }
+        }()
+
+        if txnErr != nil {
+                fmt.Errorf("Unable to start database transaction. %s", txnErr.Error())
+        }
+
+        glog.Infoln("At 1   ------------------------------------------------")
+
+        stmt := `CREATE TABLE IF NOT EXISTS sk_db_version (

[jcgregorio, 2014/09/29 16:59:07]

I'm not sure if the current readwrite user has the ability to call CREATE, if
not then we may need to either add those permissions, or maybe migrations are
only done from a separate app?

I'm leery of giving the main app CREATE/DROP privileges, little bobby tables and
all.

http://xkcd.com/327/

[stephana, 2014/09/29 18:07:50]

Agreed. The readwrite user should not have those privileges. 
I am going to add a separate app that handles migration and that will prompt the
user for the root password. It will essentially just be a front to the functions
in here. 

+                id         INTEGER      NOT NULL PRIMARY KEY,
+                version    INTEGER      NOT NULL,
+                updated    BIGINT       NOT NULL
+        )`
+        if _, txnErr = txn.Exec(stmt); txnErr != nil {
+                return fmt.Errorf("Creating version table failed: %s", txnErr.Error())
+        }
+
+        glog.Infoln("At 2   ------------------------------------------------")
+        var count int
+        if txnErr = txn.QueryRow("SELECT COUNT(*) FROM sk_db_version").Scan(&count); txnErr != nil {
+                return fmt.Errorf("Unable to read version table: %s", txnErr.Error())
+        }
+
+        glog.Infoln("At 3   ------------------------------------------------")
+
+        if count == 0 {
+                if txnErr = setDBVersion(txn, 0); txnErr != nil {
+                        return txnErr
+                }
+        } else if count > 1 {
+                return fmt.Errorf("Version table contains more than one row.")
+        }
+
+        glog.Infoln("At 4   ------------------------------------------------")
+
+        return txnErr
+}
+
+// Add any new migration to this list of functions
+// NOTE: Do not change these once commited, but instead add a migration step.
+var migrationSteps = []func() ([]string, []string, []string, []string){
+        migration_1,
+}
+
+// Returns the SQL statements base on whether we are using MySQL and the
+// current and target DB version.
+func getMigrations(isMySQL bool, currentVersion int, targetVersion int) [][]string {
+        inc := util.SignInt(targetVersion - currentVersion)
+        if inc == 0 {
+                return [][]string{}
+        }
+
+        delta := util.AbsInt(targetVersion - currentVersion)
+        result := make([][]string, 0, delta)
+        for i := currentVersion + inc; (i >= 0) && (i < len(migrationSteps)) && (i != targetVersion); i += inc {
+                var temp []string
+                switch {
+                // using mysqlp
+                case (inc > 0) && isMySQL:
+                        _, _, temp, _ = migrationSteps[i]()
+                case (inc < 0) && isMySQL:
+                        _, _, _, temp = migrationSteps[i]()
+                // using sqlite
+                case (inc > 0):
+                        temp, _, _, _ = migrationSteps[i]()
+                case (inc < 0):
+                        _, temp, _, _ = migrationSteps[i]()
+                }
+                result = append(result, temp)
+        }
+        return result
+}
+
+func migration_1() (sqLiteUp, sqLiteDown, mySQLUp, mySQLDown []string) {
+        // SQLite statements to alter the datase.
+        sqLiteUp = []string{
+                `CREATE TABLE clusters (
+                      id         INTEGER      NOT NULL PRIMARY KEY AUTOINCREMENT,
+                      ts         TIMESTAMP    NOT NULL,
+                      hash       TEXT         NOT NULL,
+                      regression FLOAT        NOT NULL,
+                      cluster    MEDIUMTEXT   NOT NULL,
+                      status     TEXT         NOT NULL,
+                      message    TEXT         NOT NULL
+                )`,
+                `CREATE TABLE shortcuts (
+                    id      INTEGER     NOT NULL PRIMARY KEY AUTOINCREMENT,
+                    traces  MEDIUMTEXT  NOT NULL
+                )`,
+                `CREATE TABLE tries (
+                      issue       VARCHAR(255) NOT NULL PRIMARY KEY,
+                      lastUpdated TIMESTAMP    NOT NULL,
+                      results     MEDIUMTEXT   NOT NULL
+                )`,
+        }
+
+        // When using SQLite we fully reverse the previous step.
+        sqLiteDown = []string{
+                `DROP TABLE IF EXISTS clusters`,
+                `DROP TABLE IF EXISTS shortcuts`,
+                `DROP TABLE IF EXISTS tries`,
+        }
+
+        mySQLUp = []string{
+                `CREATE TABLE shortcuts (
+              id      INT             NOT NULL AUTO_INCREMENT PRIMARY KEY,
+              traces  MEDIUMTEXT      NOT NULL
+            )`,
+
+                `CREATE TABLE clusters (
+              id         INT          NOT NULL AUTO_INCREMENT PRIMARY KEY,
+              ts         BIGINT       NOT NULL,
+              hash       TEXT         NOT NULL,
+              regression FLOAT        NOT NULL,
+              cluster    MEDIUMTEXT   NOT NULL,
+              status     TEXT         NOT NULL,
+              message    TEXT         NOT NULL
+            )`,
+
+                `CREATE TABLE tries (
+              issue       VARCHAR(255) NOT NULL PRIMARY KEY,
+              lastUpdated BIGINT       NOT NULL,
+              results     LONGTEXT   NOT NULL
+            )`,
+        }
+
+        // We are not removing the tables to make sure we don't delete existing
+        // data. This is just for the first migration.
+        mySQLDown = []string{}
+        return
+}