github.hscsec.cn/amacneil/dbmate@v1.4.1/pkg/dbmate/mysql_test.go (about) 1 package dbmate 2 3 import ( 4 "database/sql" 5 "net/url" 6 "testing" 7 8 "github.com/stretchr/testify/require" 9 ) 10 11 func mySQLTestURL(t *testing.T) *url.URL { 12 u, err := url.Parse("mysql://root:root@mysql/dbmate") 13 require.NoError(t, err) 14 15 return u 16 } 17 18 func prepTestMySQLDB(t *testing.T) *sql.DB { 19 drv := MySQLDriver{} 20 u := mySQLTestURL(t) 21 22 // drop any existing database 23 err := drv.DropDatabase(u) 24 require.NoError(t, err) 25 26 // create database 27 err = drv.CreateDatabase(u) 28 require.NoError(t, err) 29 30 // connect database 31 db, err := drv.Open(u) 32 require.NoError(t, err) 33 34 return db 35 } 36 37 func TestNormalizeMySQLURLDefaults(t *testing.T) { 38 u, err := url.Parse("mysql://host/foo") 39 require.NoError(t, err) 40 require.Equal(t, "", u.Port()) 41 42 s := normalizeMySQLURL(u) 43 require.Equal(t, "tcp(host:3306)/foo?multiStatements=true", s) 44 } 45 46 func TestNormalizeMySQLURLCustom(t *testing.T) { 47 u, err := url.Parse("mysql://bob:secret@host:123/foo?flag=on") 48 require.NoError(t, err) 49 require.Equal(t, "123", u.Port()) 50 51 s := normalizeMySQLURL(u) 52 require.Equal(t, "bob:secret@tcp(host:123)/foo?flag=on&multiStatements=true", s) 53 } 54 55 func TestMySQLCreateDropDatabase(t *testing.T) { 56 drv := MySQLDriver{} 57 u := mySQLTestURL(t) 58 59 // drop any existing database 60 err := drv.DropDatabase(u) 61 require.NoError(t, err) 62 63 // create database 64 err = drv.CreateDatabase(u) 65 require.NoError(t, err) 66 67 // check that database exists and we can connect to it 68 func() { 69 db, err := drv.Open(u) 70 require.NoError(t, err) 71 defer mustClose(db) 72 73 err = db.Ping() 74 require.NoError(t, err) 75 }() 76 77 // drop the database 78 err = drv.DropDatabase(u) 79 require.NoError(t, err) 80 81 // check that database no longer exists 82 func() { 83 db, err := drv.Open(u) 84 require.NoError(t, err) 85 defer mustClose(db) 86 87 err = db.Ping() 88 require.NotNil(t, err) 89 require.Regexp(t, "Unknown database 'dbmate'", err.Error()) 90 }() 91 } 92 93 func TestMySQLDumpSchema(t *testing.T) { 94 drv := MySQLDriver{} 95 u := mySQLTestURL(t) 96 97 // prepare database 98 db := prepTestMySQLDB(t) 99 defer mustClose(db) 100 err := drv.CreateMigrationsTable(db) 101 require.NoError(t, err) 102 103 // insert migration 104 err = drv.InsertMigration(db, "abc1") 105 require.NoError(t, err) 106 err = drv.InsertMigration(db, "abc2") 107 require.NoError(t, err) 108 109 // DumpSchema should return schema 110 schema, err := drv.DumpSchema(u, db) 111 require.NoError(t, err) 112 require.Contains(t, string(schema), "CREATE TABLE `schema_migrations`") 113 require.Contains(t, string(schema), "\n-- Dump completed\n\n"+ 114 "--\n"+ 115 "-- Dbmate schema migrations\n"+ 116 "--\n\n"+ 117 "LOCK TABLES `schema_migrations` WRITE;\n"+ 118 "INSERT INTO `schema_migrations` (version) VALUES\n"+ 119 " ('abc1'),\n"+ 120 " ('abc2');\n"+ 121 "UNLOCK TABLES;\n") 122 123 // DumpSchema should return error if command fails 124 u.Path = "/fakedb" 125 schema, err = drv.DumpSchema(u, db) 126 require.Nil(t, schema) 127 require.EqualError(t, err, "mysqldump: Got error: 1049: "+ 128 "\"Unknown database 'fakedb'\" when selecting the database") 129 } 130 131 func TestMySQLDatabaseExists(t *testing.T) { 132 drv := MySQLDriver{} 133 u := mySQLTestURL(t) 134 135 // drop any existing database 136 err := drv.DropDatabase(u) 137 require.NoError(t, err) 138 139 // DatabaseExists should return false 140 exists, err := drv.DatabaseExists(u) 141 require.NoError(t, err) 142 require.Equal(t, false, exists) 143 144 // create database 145 err = drv.CreateDatabase(u) 146 require.NoError(t, err) 147 148 // DatabaseExists should return true 149 exists, err = drv.DatabaseExists(u) 150 require.NoError(t, err) 151 require.Equal(t, true, exists) 152 } 153 154 func TestMySQLDatabaseExists_Error(t *testing.T) { 155 drv := MySQLDriver{} 156 u := mySQLTestURL(t) 157 u.User = url.User("invalid") 158 159 exists, err := drv.DatabaseExists(u) 160 require.Regexp(t, "Access denied for user 'invalid'@", err.Error()) 161 require.Equal(t, false, exists) 162 } 163 164 func TestMySQLCreateMigrationsTable(t *testing.T) { 165 drv := MySQLDriver{} 166 db := prepTestMySQLDB(t) 167 defer mustClose(db) 168 169 // migrations table should not exist 170 count := 0 171 err := db.QueryRow("select count(*) from schema_migrations").Scan(&count) 172 require.Regexp(t, "Table 'dbmate.schema_migrations' doesn't exist", err.Error()) 173 174 // create table 175 err = drv.CreateMigrationsTable(db) 176 require.NoError(t, err) 177 178 // migrations table should exist 179 err = db.QueryRow("select count(*) from schema_migrations").Scan(&count) 180 require.NoError(t, err) 181 182 // create table should be idempotent 183 err = drv.CreateMigrationsTable(db) 184 require.NoError(t, err) 185 } 186 187 func TestMySQLSelectMigrations(t *testing.T) { 188 drv := MySQLDriver{} 189 db := prepTestMySQLDB(t) 190 defer mustClose(db) 191 192 err := drv.CreateMigrationsTable(db) 193 require.NoError(t, err) 194 195 _, err = db.Exec(`insert into schema_migrations (version) 196 values ('abc2'), ('abc1'), ('abc3')`) 197 require.NoError(t, err) 198 199 migrations, err := drv.SelectMigrations(db, -1) 200 require.NoError(t, err) 201 require.Equal(t, true, migrations["abc1"]) 202 require.Equal(t, true, migrations["abc2"]) 203 require.Equal(t, true, migrations["abc2"]) 204 205 // test limit param 206 migrations, err = drv.SelectMigrations(db, 1) 207 require.NoError(t, err) 208 require.Equal(t, true, migrations["abc3"]) 209 require.Equal(t, false, migrations["abc1"]) 210 require.Equal(t, false, migrations["abc2"]) 211 } 212 213 func TestMySQLInsertMigration(t *testing.T) { 214 drv := MySQLDriver{} 215 db := prepTestMySQLDB(t) 216 defer mustClose(db) 217 218 err := drv.CreateMigrationsTable(db) 219 require.NoError(t, err) 220 221 count := 0 222 err = db.QueryRow("select count(*) from schema_migrations").Scan(&count) 223 require.NoError(t, err) 224 require.Equal(t, 0, count) 225 226 // insert migration 227 err = drv.InsertMigration(db, "abc1") 228 require.NoError(t, err) 229 230 err = db.QueryRow("select count(*) from schema_migrations where version = 'abc1'"). 231 Scan(&count) 232 require.NoError(t, err) 233 require.Equal(t, 1, count) 234 } 235 236 func TestMySQLDeleteMigration(t *testing.T) { 237 drv := MySQLDriver{} 238 db := prepTestMySQLDB(t) 239 defer mustClose(db) 240 241 err := drv.CreateMigrationsTable(db) 242 require.NoError(t, err) 243 244 _, err = db.Exec(`insert into schema_migrations (version) 245 values ('abc1'), ('abc2')`) 246 require.NoError(t, err) 247 248 err = drv.DeleteMigration(db, "abc2") 249 require.NoError(t, err) 250 251 count := 0 252 err = db.QueryRow("select count(*) from schema_migrations").Scan(&count) 253 require.NoError(t, err) 254 require.Equal(t, 1, count) 255 } 256 257 func TestMySQLPing(t *testing.T) { 258 drv := MySQLDriver{} 259 u := mySQLTestURL(t) 260 261 // drop any existing database 262 err := drv.DropDatabase(u) 263 require.NoError(t, err) 264 265 // ping database 266 err = drv.Ping(u) 267 require.NoError(t, err) 268 269 // ping invalid host should return error 270 u.Host = "mysql:404" 271 err = drv.Ping(u) 272 require.Error(t, err) 273 require.Contains(t, err.Error(), "connect: connection refused") 274 }