github.com/gogriddy/goose@v0.0.0-20180817174216-2c751e0981c8/lib/goose/dialect.go (about) 1 package goose 2 3 import ( 4 "database/sql" 5 "strings" 6 ) 7 8 // SqlDialect abstracts the details of specific SQL dialects 9 // for goose's few SQL specific statements 10 type SqlDialect interface { 11 createVersionTableSql() string // sql string to create the goose_db_version table 12 insertVersionSql() string // sql string to insert the initial version table row 13 dbVersionQuery(db *sql.DB) (*sql.Rows, error) 14 } 15 16 // drivers that we don't know about can ask for a dialect by name 17 func dialectByName(d string) SqlDialect { 18 switch d { 19 case "postgres": 20 return &PostgresDialect{} 21 case "redshift": 22 return &RedshiftDialect{} 23 case "mysql": 24 return &MySqlDialect{} 25 case "sqlite3": 26 return &Sqlite3Dialect{} 27 } 28 29 return nil 30 } 31 32 //////////////////////////// 33 // Postgres 34 //////////////////////////// 35 36 type PostgresDialect struct{} 37 38 func (pg PostgresDialect) createVersionTableSql() string { 39 return `CREATE TABLE goose_db_version ( 40 id serial NOT NULL, 41 version_id bigint NOT NULL, 42 is_applied boolean NOT NULL, 43 tstamp timestamp NULL default now(), 44 PRIMARY KEY(id) 45 );` 46 } 47 48 func (pg PostgresDialect) insertVersionSql() string { 49 return "INSERT INTO goose_db_version (version_id, is_applied) VALUES ($1, $2);" 50 } 51 52 func (pg PostgresDialect) dbVersionQuery(db *sql.DB) (*sql.Rows, error) { 53 rows, err := db.Query("SELECT version_id, is_applied, tstamp from goose_db_version ORDER BY id DESC") 54 55 // XXX: check for postgres specific error indicating the table doesn't exist. 56 // for now, assume any error is because the table doesn't exist, 57 // in which case we'll try to create it. 58 if err != nil { 59 return nil, ErrTableDoesNotExist 60 } 61 62 return rows, err 63 } 64 65 //////////////////////////// 66 // Redshift 67 //////////////////////////// 68 69 type RedshiftDialect struct{} 70 71 func (pg RedshiftDialect) createVersionTableSql() string { 72 return `CREATE TABLE goose_db_version ( 73 version_id BIGINT NOT NULL, 74 is_applied BOOLEAN NOT NULL, 75 tstamp timestamp NOT NULL 76 ) SORTKEY(tstamp);` 77 } 78 79 func (pg RedshiftDialect) insertVersionSql() string { 80 return "INSERT INTO goose_db_version (version_id, is_applied, tstamp) VALUES ($1, $2, SYSDATE);" 81 } 82 83 func (pg RedshiftDialect) dbVersionQuery(db *sql.DB) (*sql.Rows, error) { 84 rows, err := db.Query("SELECT version_id, is_applied, tstamp from goose_db_version ORDER BY tstamp DESC") 85 86 // XXX: check for postgres specific error indicating the table doesn't exist. 87 // for now, assume any error is because the table doesn't exist, 88 // in which case we'll try to create it. 89 if err != nil { 90 return nil, ErrTableDoesNotExist 91 } 92 93 return rows, err 94 } 95 96 //////////////////////////// 97 // MySQL 98 //////////////////////////// 99 100 type MySqlDialect struct{} 101 102 func (m MySqlDialect) createVersionTableSql() string { 103 return `CREATE TABLE goose_db_version ( 104 id serial NOT NULL, 105 version_id bigint NOT NULL, 106 is_applied boolean NOT NULL, 107 tstamp timestamp NULL default now(), 108 PRIMARY KEY(id) 109 );` 110 } 111 112 func (m MySqlDialect) insertVersionSql() string { 113 return "INSERT INTO goose_db_version (version_id, is_applied) VALUES (?, ?);" 114 } 115 116 func (m MySqlDialect) dbVersionQuery(db *sql.DB) (*sql.Rows, error) { 117 rows, err := db.Query("SELECT version_id, is_applied, tstamp from goose_db_version ORDER BY id DESC") 118 119 // XXX: check for mysql specific error indicating the table doesn't exist. 120 // for now, assume any error is because the table doesn't exist, 121 // in which case we'll try to create it. 122 if err != nil { 123 return nil, ErrTableDoesNotExist 124 } 125 126 return rows, err 127 } 128 129 //////////////////////////// 130 // sqlite3 131 //////////////////////////// 132 133 type Sqlite3Dialect struct{} 134 135 func (m Sqlite3Dialect) createVersionTableSql() string { 136 return `CREATE TABLE goose_db_version ( 137 id INTEGER PRIMARY KEY AUTOINCREMENT, 138 version_id INTEGER NOT NULL, 139 is_applied INTEGER NOT NULL, 140 tstamp TIMESTAMP DEFAULT (datetime('now')) 141 );` 142 } 143 144 func (m Sqlite3Dialect) insertVersionSql() string { 145 return "INSERT INTO goose_db_version (version_id, is_applied) VALUES (?, ?);" 146 } 147 148 func (m Sqlite3Dialect) dbVersionQuery(db *sql.DB) (*sql.Rows, error) { 149 rows, err := db.Query("SELECT version_id, is_applied, tstamp from goose_db_version ORDER BY id DESC") 150 151 if err != nil && strings.Contains(err.Error(), "no such table") { 152 err = ErrTableDoesNotExist 153 } 154 return rows, err 155 }