github.hscsec.cn/amacneil/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 }