github.phpd.cn/amacneil/dbmate@v1.4.1/pkg/dbmate/sqlite_test.go (about)

     1  // +build cgo
     2  
     3  package dbmate
     4  
     5  import (
     6  	"database/sql"
     7  	"net/url"
     8  	"os"
     9  	"testing"
    10  
    11  	"github.com/stretchr/testify/require"
    12  )
    13  
    14  func sqliteTestURL(t *testing.T) *url.URL {
    15  	u, err := url.Parse("sqlite3:////tmp/dbmate.sqlite3")
    16  	require.NoError(t, err)
    17  
    18  	return u
    19  }
    20  
    21  func prepTestSQLiteDB(t *testing.T) *sql.DB {
    22  	drv := SQLiteDriver{}
    23  	u := sqliteTestURL(t)
    24  
    25  	// drop any existing database
    26  	err := drv.DropDatabase(u)
    27  	require.NoError(t, err)
    28  
    29  	// create database
    30  	err = drv.CreateDatabase(u)
    31  	require.NoError(t, err)
    32  
    33  	// connect database
    34  	db, err := drv.Open(u)
    35  	require.NoError(t, err)
    36  
    37  	return db
    38  }
    39  
    40  func TestSQLiteCreateDropDatabase(t *testing.T) {
    41  	drv := SQLiteDriver{}
    42  	u := sqliteTestURL(t)
    43  	path := sqlitePath(u)
    44  
    45  	// drop any existing database
    46  	err := drv.DropDatabase(u)
    47  	require.NoError(t, err)
    48  
    49  	// create database
    50  	err = drv.CreateDatabase(u)
    51  	require.NoError(t, err)
    52  
    53  	// check that database exists
    54  	_, err = os.Stat(path)
    55  	require.NoError(t, err)
    56  
    57  	// drop the database
    58  	err = drv.DropDatabase(u)
    59  	require.NoError(t, err)
    60  
    61  	// check that database no longer exists
    62  	_, err = os.Stat(path)
    63  	require.NotNil(t, err)
    64  	require.Equal(t, true, os.IsNotExist(err))
    65  }
    66  
    67  func TestSQLiteDumpSchema(t *testing.T) {
    68  	drv := SQLiteDriver{}
    69  	u := sqliteTestURL(t)
    70  
    71  	// prepare database
    72  	db := prepTestSQLiteDB(t)
    73  	defer mustClose(db)
    74  	err := drv.CreateMigrationsTable(db)
    75  	require.NoError(t, err)
    76  
    77  	// insert migration
    78  	err = drv.InsertMigration(db, "abc1")
    79  	require.NoError(t, err)
    80  	err = drv.InsertMigration(db, "abc2")
    81  	require.NoError(t, err)
    82  
    83  	// DumpSchema should return schema
    84  	schema, err := drv.DumpSchema(u, db)
    85  	require.NoError(t, err)
    86  	require.Contains(t, string(schema), "CREATE TABLE schema_migrations")
    87  	require.Contains(t, string(schema), ");\n-- Dbmate schema migrations\n"+
    88  		"INSERT INTO schema_migrations (version) VALUES\n"+
    89  		"  ('abc1'),\n"+
    90  		"  ('abc2');\n")
    91  
    92  	// DumpSchema should return error if command fails
    93  	u.Path = "/."
    94  	schema, err = drv.DumpSchema(u, db)
    95  	require.Nil(t, schema)
    96  	require.EqualError(t, err, "Error: unable to open database \".\": "+
    97  		"unable to open database file")
    98  }
    99  
   100  func TestSQLiteDatabaseExists(t *testing.T) {
   101  	drv := SQLiteDriver{}
   102  	u := sqliteTestURL(t)
   103  
   104  	// drop any existing database
   105  	err := drv.DropDatabase(u)
   106  	require.NoError(t, err)
   107  
   108  	// DatabaseExists should return false
   109  	exists, err := drv.DatabaseExists(u)
   110  	require.NoError(t, err)
   111  	require.Equal(t, false, exists)
   112  
   113  	// create database
   114  	err = drv.CreateDatabase(u)
   115  	require.NoError(t, err)
   116  
   117  	// DatabaseExists should return true
   118  	exists, err = drv.DatabaseExists(u)
   119  	require.NoError(t, err)
   120  	require.Equal(t, true, exists)
   121  }
   122  
   123  func TestSQLiteCreateMigrationsTable(t *testing.T) {
   124  	drv := SQLiteDriver{}
   125  	db := prepTestSQLiteDB(t)
   126  	defer mustClose(db)
   127  
   128  	// migrations table should not exist
   129  	count := 0
   130  	err := db.QueryRow("select count(*) from schema_migrations").Scan(&count)
   131  	require.Regexp(t, "no such table: schema_migrations", err.Error())
   132  
   133  	// create table
   134  	err = drv.CreateMigrationsTable(db)
   135  	require.NoError(t, err)
   136  
   137  	// migrations table should exist
   138  	err = db.QueryRow("select count(*) from schema_migrations").Scan(&count)
   139  	require.NoError(t, err)
   140  
   141  	// create table should be idempotent
   142  	err = drv.CreateMigrationsTable(db)
   143  	require.NoError(t, err)
   144  }
   145  
   146  func TestSQLiteSelectMigrations(t *testing.T) {
   147  	drv := SQLiteDriver{}
   148  	db := prepTestSQLiteDB(t)
   149  	defer mustClose(db)
   150  
   151  	err := drv.CreateMigrationsTable(db)
   152  	require.NoError(t, err)
   153  
   154  	_, err = db.Exec(`insert into schema_migrations (version)
   155  		values ('abc2'), ('abc1'), ('abc3')`)
   156  	require.NoError(t, err)
   157  
   158  	migrations, err := drv.SelectMigrations(db, -1)
   159  	require.NoError(t, err)
   160  	require.Equal(t, true, migrations["abc1"])
   161  	require.Equal(t, true, migrations["abc2"])
   162  	require.Equal(t, true, migrations["abc2"])
   163  
   164  	// test limit param
   165  	migrations, err = drv.SelectMigrations(db, 1)
   166  	require.NoError(t, err)
   167  	require.Equal(t, true, migrations["abc3"])
   168  	require.Equal(t, false, migrations["abc1"])
   169  	require.Equal(t, false, migrations["abc2"])
   170  }
   171  
   172  func TestSQLiteInsertMigration(t *testing.T) {
   173  	drv := SQLiteDriver{}
   174  	db := prepTestSQLiteDB(t)
   175  	defer mustClose(db)
   176  
   177  	err := drv.CreateMigrationsTable(db)
   178  	require.NoError(t, err)
   179  
   180  	count := 0
   181  	err = db.QueryRow("select count(*) from schema_migrations").Scan(&count)
   182  	require.NoError(t, err)
   183  	require.Equal(t, 0, count)
   184  
   185  	// insert migration
   186  	err = drv.InsertMigration(db, "abc1")
   187  	require.NoError(t, err)
   188  
   189  	err = db.QueryRow("select count(*) from schema_migrations where version = 'abc1'").
   190  		Scan(&count)
   191  	require.NoError(t, err)
   192  	require.Equal(t, 1, count)
   193  }
   194  
   195  func TestSQLiteDeleteMigration(t *testing.T) {
   196  	drv := SQLiteDriver{}
   197  	db := prepTestSQLiteDB(t)
   198  	defer mustClose(db)
   199  
   200  	err := drv.CreateMigrationsTable(db)
   201  	require.NoError(t, err)
   202  
   203  	_, err = db.Exec(`insert into schema_migrations (version)
   204  		values ('abc1'), ('abc2')`)
   205  	require.NoError(t, err)
   206  
   207  	err = drv.DeleteMigration(db, "abc2")
   208  	require.NoError(t, err)
   209  
   210  	count := 0
   211  	err = db.QueryRow("select count(*) from schema_migrations").Scan(&count)
   212  	require.NoError(t, err)
   213  	require.Equal(t, 1, count)
   214  }
   215  
   216  func TestSQLitePing(t *testing.T) {
   217  	drv := SQLiteDriver{}
   218  	u := sqliteTestURL(t)
   219  	path := sqlitePath(u)
   220  
   221  	// drop any existing database
   222  	err := drv.DropDatabase(u)
   223  	require.NoError(t, err)
   224  
   225  	// ping database
   226  	err = drv.Ping(u)
   227  	require.NoError(t, err)
   228  
   229  	// check that the database was created (sqlite-only behavior)
   230  	_, err = os.Stat(path)
   231  	require.NoError(t, err)
   232  
   233  	// drop the database
   234  	err = drv.DropDatabase(u)
   235  	require.NoError(t, err)
   236  
   237  	// create directory where database file is expected
   238  	err = os.Mkdir(path, 0755)
   239  	require.NoError(t, err)
   240  	defer func() {
   241  		err = os.RemoveAll(path)
   242  		require.NoError(t, err)
   243  	}()
   244  
   245  	// ping database should fail
   246  	err = drv.Ping(u)
   247  	require.EqualError(t, err, "unable to open database file")
   248  }