github.com/CloudCom/goose@v0.0.0-20151110184009-e03c3249c21b/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  }