github.com/bingtel/dbmate@v1.4.1/pkg/dbmate/sqlite.go (about)

     1  // +build cgo
     2  
     3  package dbmate
     4  
     5  import (
     6  	"bytes"
     7  	"database/sql"
     8  	"fmt"
     9  	"net/url"
    10  	"os"
    11  	"regexp"
    12  	"strings"
    13  
    14  	_ "github.com/mattn/go-sqlite3" // sqlite driver for database/sql
    15  )
    16  
    17  func init() {
    18  	RegisterDriver(SQLiteDriver{}, "sqlite")
    19  	RegisterDriver(SQLiteDriver{}, "sqlite3")
    20  }
    21  
    22  // SQLiteDriver provides top level database functions
    23  type SQLiteDriver struct {
    24  }
    25  
    26  func sqlitePath(u *url.URL) string {
    27  	// strip one leading slash
    28  	// absolute URLs can be specified as sqlite:////tmp/foo.sqlite3
    29  	str := regexp.MustCompile("^/").ReplaceAllString(u.Path, "")
    30  
    31  	return str
    32  }
    33  
    34  // Open creates a new database connection
    35  func (drv SQLiteDriver) Open(u *url.URL) (*sql.DB, error) {
    36  	return sql.Open("sqlite3", sqlitePath(u))
    37  }
    38  
    39  // CreateDatabase creates the specified database
    40  func (drv SQLiteDriver) CreateDatabase(u *url.URL) error {
    41  	fmt.Printf("Creating: %s\n", sqlitePath(u))
    42  
    43  	db, err := drv.Open(u)
    44  	if err != nil {
    45  		return err
    46  	}
    47  	defer mustClose(db)
    48  
    49  	return db.Ping()
    50  }
    51  
    52  // DropDatabase drops the specified database (if it exists)
    53  func (drv SQLiteDriver) DropDatabase(u *url.URL) error {
    54  	path := sqlitePath(u)
    55  	fmt.Printf("Dropping: %s\n", path)
    56  
    57  	exists, err := drv.DatabaseExists(u)
    58  	if err != nil {
    59  		return err
    60  	}
    61  	if !exists {
    62  		return nil
    63  	}
    64  
    65  	return os.Remove(path)
    66  }
    67  
    68  func sqliteSchemaMigrationsDump(db *sql.DB) ([]byte, error) {
    69  	// load applied migrations
    70  	migrations, err := queryColumn(db,
    71  		"select quote(version) from schema_migrations order by version asc")
    72  	if err != nil {
    73  		return nil, err
    74  	}
    75  
    76  	// build schema_migrations table data
    77  	var buf bytes.Buffer
    78  	buf.WriteString("-- Dbmate schema migrations\n")
    79  
    80  	if len(migrations) > 0 {
    81  		buf.WriteString("INSERT INTO schema_migrations (version) VALUES\n  (" +
    82  			strings.Join(migrations, "),\n  (") +
    83  			");\n")
    84  	}
    85  
    86  	return buf.Bytes(), nil
    87  }
    88  
    89  // DumpSchema returns the current database schema
    90  func (drv SQLiteDriver) DumpSchema(u *url.URL, db *sql.DB) ([]byte, error) {
    91  	path := sqlitePath(u)
    92  	schema, err := runCommand("sqlite3", path, ".schema")
    93  	if err != nil {
    94  		return nil, err
    95  	}
    96  
    97  	migrations, err := sqliteSchemaMigrationsDump(db)
    98  	if err != nil {
    99  		return nil, err
   100  	}
   101  
   102  	schema = append(schema, migrations...)
   103  	return trimLeadingSQLComments(schema)
   104  }
   105  
   106  // DatabaseExists determines whether the database exists
   107  func (drv SQLiteDriver) DatabaseExists(u *url.URL) (bool, error) {
   108  	_, err := os.Stat(sqlitePath(u))
   109  	if os.IsNotExist(err) {
   110  		return false, nil
   111  	}
   112  	if err != nil {
   113  		return false, err
   114  	}
   115  
   116  	return true, nil
   117  }
   118  
   119  // CreateMigrationsTable creates the schema_migrations table
   120  func (drv SQLiteDriver) CreateMigrationsTable(db *sql.DB) error {
   121  	_, err := db.Exec("create table if not exists schema_migrations " +
   122  		"(version varchar(255) primary key)")
   123  
   124  	return err
   125  }
   126  
   127  // SelectMigrations returns a list of applied migrations
   128  // with an optional limit (in descending order)
   129  func (drv SQLiteDriver) SelectMigrations(db *sql.DB, limit int) (map[string]bool, error) {
   130  	query := "select version from schema_migrations order by version desc"
   131  	if limit >= 0 {
   132  		query = fmt.Sprintf("%s limit %d", query, limit)
   133  	}
   134  	rows, err := db.Query(query)
   135  	if err != nil {
   136  		return nil, err
   137  	}
   138  
   139  	defer mustClose(rows)
   140  
   141  	migrations := map[string]bool{}
   142  	for rows.Next() {
   143  		var version string
   144  		if err := rows.Scan(&version); err != nil {
   145  			return nil, err
   146  		}
   147  
   148  		migrations[version] = true
   149  	}
   150  
   151  	return migrations, nil
   152  }
   153  
   154  // InsertMigration adds a new migration record
   155  func (drv SQLiteDriver) InsertMigration(db Transaction, version string) error {
   156  	_, err := db.Exec("insert into schema_migrations (version) values (?)", version)
   157  
   158  	return err
   159  }
   160  
   161  // DeleteMigration removes a migration record
   162  func (drv SQLiteDriver) DeleteMigration(db Transaction, version string) error {
   163  	_, err := db.Exec("delete from schema_migrations where version = ?", version)
   164  
   165  	return err
   166  }
   167  
   168  // Ping verifies a connection to the database. Due to the way SQLite works, by
   169  // testing whether the database is valid, it will automatically create the database
   170  // if it does not already exist.
   171  func (drv SQLiteDriver) Ping(u *url.URL) error {
   172  	db, err := drv.Open(u)
   173  	if err != nil {
   174  		return err
   175  	}
   176  	defer mustClose(db)
   177  
   178  	return db.Ping()
   179  }