github.com/fr-nvriep/migrate/v4@v4.3.2/database/postgres/postgres_test.go (about) 1 package postgres 2 3 // error codes https://github.com/lib/pq/blob/master/error.go 4 5 import ( 6 "context" 7 "database/sql" 8 sqldriver "database/sql/driver" 9 "fmt" 10 "log" 11 12 "github.com/fr-nvriep/migrate/v4" 13 "io" 14 "strconv" 15 "strings" 16 "sync" 17 "testing" 18 19 "github.com/dhui/dktest" 20 21 dt "github.com/fr-nvriep/migrate/v4/database/testing" 22 "github.com/fr-nvriep/migrate/v4/dktesting" 23 _ "github.com/fr-nvriep/migrate/v4/source/file" 24 ) 25 26 var ( 27 opts = dktest.Options{PortRequired: true, ReadyFunc: isReady} 28 // Supported versions: https://www.postgresql.org/support/versioning/ 29 specs = []dktesting.ContainerSpec{ 30 {ImageName: "postgres:9.4", Options: opts}, 31 {ImageName: "postgres:9.5", Options: opts}, 32 {ImageName: "postgres:9.6", Options: opts}, 33 {ImageName: "postgres:10", Options: opts}, 34 {ImageName: "postgres:11", Options: opts}, 35 } 36 ) 37 38 func pgConnectionString(host, port string) string { 39 return fmt.Sprintf("postgres://postgres@%s:%s/postgres?sslmode=disable", host, port) 40 } 41 42 func isReady(ctx context.Context, c dktest.ContainerInfo) bool { 43 ip, port, err := c.FirstPort() 44 if err != nil { 45 return false 46 } 47 48 db, err := sql.Open("postgres", pgConnectionString(ip, port)) 49 if err != nil { 50 return false 51 } 52 defer func() { 53 if err := db.Close(); err != nil { 54 log.Println("close error:", err) 55 } 56 }() 57 if err = db.PingContext(ctx); err != nil { 58 switch err { 59 case sqldriver.ErrBadConn, io.EOF: 60 return false 61 default: 62 log.Println(err) 63 } 64 return false 65 } 66 67 return true 68 } 69 70 func Test(t *testing.T) { 71 dktesting.ParallelTest(t, specs, func(t *testing.T, c dktest.ContainerInfo) { 72 ip, port, err := c.FirstPort() 73 if err != nil { 74 t.Fatal(err) 75 } 76 77 addr := pgConnectionString(ip, port) 78 p := &Postgres{} 79 d, err := p.Open(addr) 80 if err != nil { 81 t.Fatal(err) 82 } 83 defer func() { 84 if err := d.Close(); err != nil { 85 t.Error(err) 86 } 87 }() 88 dt.Test(t, d, []byte("SELECT 1")) 89 }) 90 } 91 92 func TestMigrate(t *testing.T) { 93 dktesting.ParallelTest(t, specs, func(t *testing.T, c dktest.ContainerInfo) { 94 ip, port, err := c.FirstPort() 95 if err != nil { 96 t.Fatal(err) 97 } 98 99 addr := pgConnectionString(ip, port) 100 p := &Postgres{} 101 d, err := p.Open(addr) 102 if err != nil { 103 t.Fatal(err) 104 } 105 defer func() { 106 if err := d.Close(); err != nil { 107 t.Error(err) 108 } 109 }() 110 m, err := migrate.NewWithDatabaseInstance("file://./examples/migrations", "postgres", d) 111 if err != nil { 112 t.Fatal(err) 113 } 114 dt.TestMigrate(t, m, []byte("SELECT 1")) 115 }) 116 } 117 118 func TestMultiStatement(t *testing.T) { 119 dktesting.ParallelTest(t, specs, func(t *testing.T, c dktest.ContainerInfo) { 120 ip, port, err := c.FirstPort() 121 if err != nil { 122 t.Fatal(err) 123 } 124 125 addr := pgConnectionString(ip, port) 126 p := &Postgres{} 127 d, err := p.Open(addr) 128 if err != nil { 129 t.Fatal(err) 130 } 131 defer func() { 132 if err := d.Close(); err != nil { 133 t.Error(err) 134 } 135 }() 136 if err := d.Run(strings.NewReader("CREATE TABLE foo (foo text); CREATE TABLE bar (bar text);")); err != nil { 137 t.Fatalf("expected err to be nil, got %v", err) 138 } 139 140 // make sure second table exists 141 var exists bool 142 if err := d.(*Postgres).conn.QueryRowContext(context.Background(), "SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'bar' AND table_schema = (SELECT current_schema()))").Scan(&exists); err != nil { 143 t.Fatal(err) 144 } 145 if !exists { 146 t.Fatalf("expected table bar to exist") 147 } 148 }) 149 } 150 151 func TestErrorParsing(t *testing.T) { 152 dktesting.ParallelTest(t, specs, func(t *testing.T, c dktest.ContainerInfo) { 153 ip, port, err := c.FirstPort() 154 if err != nil { 155 t.Fatal(err) 156 } 157 158 addr := pgConnectionString(ip, port) 159 p := &Postgres{} 160 d, err := p.Open(addr) 161 if err != nil { 162 t.Fatal(err) 163 } 164 defer func() { 165 if err := d.Close(); err != nil { 166 t.Error(err) 167 } 168 }() 169 170 wantErr := `migration failed: syntax error at or near "TABLEE" (column 37) in line 1: CREATE TABLE foo ` + 171 `(foo text); CREATE TABLEE bar (bar text); (details: pq: syntax error at or near "TABLEE")` 172 if err := d.Run(strings.NewReader("CREATE TABLE foo (foo text); CREATE TABLEE bar (bar text);")); err == nil { 173 t.Fatal("expected err but got nil") 174 } else if err.Error() != wantErr { 175 t.Fatalf("expected '%s' but got '%s'", wantErr, err.Error()) 176 } 177 }) 178 } 179 180 func TestFilterCustomQuery(t *testing.T) { 181 dktesting.ParallelTest(t, specs, func(t *testing.T, c dktest.ContainerInfo) { 182 ip, port, err := c.FirstPort() 183 if err != nil { 184 t.Fatal(err) 185 } 186 187 addr := fmt.Sprintf("postgres://postgres@%v:%v/postgres?sslmode=disable&x-custom=foobar", ip, port) 188 p := &Postgres{} 189 d, err := p.Open(addr) 190 if err != nil { 191 t.Fatal(err) 192 } 193 defer func() { 194 if err := d.Close(); err != nil { 195 t.Error(err) 196 } 197 }() 198 }) 199 } 200 201 func TestWithSchema(t *testing.T) { 202 dktesting.ParallelTest(t, specs, func(t *testing.T, c dktest.ContainerInfo) { 203 ip, port, err := c.FirstPort() 204 if err != nil { 205 t.Fatal(err) 206 } 207 208 addr := pgConnectionString(ip, port) 209 p := &Postgres{} 210 d, err := p.Open(addr) 211 if err != nil { 212 t.Fatal(err) 213 } 214 defer func() { 215 if err := d.Close(); err != nil { 216 t.Fatal(err) 217 } 218 }() 219 220 // create foobar schema 221 if err := d.Run(strings.NewReader("CREATE SCHEMA foobar AUTHORIZATION postgres")); err != nil { 222 t.Fatal(err) 223 } 224 if err := d.SetVersion(1, false); err != nil { 225 t.Fatal(err) 226 } 227 228 // re-connect using that schema 229 d2, err := p.Open(fmt.Sprintf("postgres://postgres@%v:%v/postgres?sslmode=disable&search_path=foobar", ip, port)) 230 if err != nil { 231 t.Fatal(err) 232 } 233 defer func() { 234 if err := d2.Close(); err != nil { 235 t.Fatal(err) 236 } 237 }() 238 239 version, _, err := d2.Version() 240 if err != nil { 241 t.Fatal(err) 242 } 243 if version != -1 { 244 t.Fatal("expected NilVersion") 245 } 246 247 // now update version and compare 248 if err := d2.SetVersion(2, false); err != nil { 249 t.Fatal(err) 250 } 251 version, _, err = d2.Version() 252 if err != nil { 253 t.Fatal(err) 254 } 255 if version != 2 { 256 t.Fatal("expected version 2") 257 } 258 259 // meanwhile, the public schema still has the other version 260 version, _, err = d.Version() 261 if err != nil { 262 t.Fatal(err) 263 } 264 if version != 1 { 265 t.Fatal("expected version 2") 266 } 267 }) 268 } 269 270 func TestParallelSchema(t *testing.T) { 271 dktesting.ParallelTest(t, specs, func(t *testing.T, c dktest.ContainerInfo) { 272 ip, port, err := c.FirstPort() 273 if err != nil { 274 t.Fatal(err) 275 } 276 277 addr := pgConnectionString(ip, port) 278 p := &Postgres{} 279 d, err := p.Open(addr) 280 if err != nil { 281 t.Fatal(err) 282 } 283 defer func() { 284 if err := d.Close(); err != nil { 285 t.Error(err) 286 } 287 }() 288 289 // create foo and bar schemas 290 if err := d.Run(strings.NewReader("CREATE SCHEMA foo AUTHORIZATION postgres")); err != nil { 291 t.Fatal(err) 292 } 293 if err := d.Run(strings.NewReader("CREATE SCHEMA bar AUTHORIZATION postgres")); err != nil { 294 t.Fatal(err) 295 } 296 297 // re-connect using that schemas 298 dfoo, err := p.Open(fmt.Sprintf("postgres://postgres@%v:%v/postgres?sslmode=disable&search_path=foo", ip, port)) 299 if err != nil { 300 t.Fatal(err) 301 } 302 defer func() { 303 if err := dfoo.Close(); err != nil { 304 t.Error(err) 305 } 306 }() 307 308 dbar, err := p.Open(fmt.Sprintf("postgres://postgres@%v:%v/postgres?sslmode=disable&search_path=bar", ip, port)) 309 if err != nil { 310 t.Fatal(err) 311 } 312 defer func() { 313 if err := dbar.Close(); err != nil { 314 t.Error(err) 315 } 316 }() 317 318 if err := dfoo.Lock(); err != nil { 319 t.Fatal(err) 320 } 321 322 if err := dbar.Lock(); err != nil { 323 t.Fatal(err) 324 } 325 326 if err := dbar.Unlock(); err != nil { 327 t.Fatal(err) 328 } 329 330 if err := dfoo.Unlock(); err != nil { 331 t.Fatal(err) 332 } 333 }) 334 } 335 336 func TestWithInstance(t *testing.T) { 337 338 } 339 340 func TestPostgres_Lock(t *testing.T) { 341 dktesting.ParallelTest(t, specs, func(t *testing.T, c dktest.ContainerInfo) { 342 ip, port, err := c.FirstPort() 343 if err != nil { 344 t.Fatal(err) 345 } 346 347 addr := pgConnectionString(ip, port) 348 p := &Postgres{} 349 d, err := p.Open(addr) 350 if err != nil { 351 t.Fatal(err) 352 } 353 354 dt.Test(t, d, []byte("SELECT 1")) 355 356 ps := d.(*Postgres) 357 358 err = ps.Lock() 359 if err != nil { 360 t.Fatal(err) 361 } 362 363 err = ps.Unlock() 364 if err != nil { 365 t.Fatal(err) 366 } 367 368 err = ps.Lock() 369 if err != nil { 370 t.Fatal(err) 371 } 372 373 err = ps.Unlock() 374 if err != nil { 375 t.Fatal(err) 376 } 377 }) 378 } 379 380 func TestWithInstance_Concurrent(t *testing.T) { 381 dktesting.ParallelTest(t, specs, func(t *testing.T, c dktest.ContainerInfo) { 382 ip, port, err := c.FirstPort() 383 if err != nil { 384 t.Fatal(err) 385 } 386 387 // The number of concurrent processes running WithInstance 388 const concurrency = 30 389 390 // We can instantiate a single database handle because it is 391 // actually a connection pool, and so, each of the below go 392 // routines will have a high probability of using a separate 393 // connection, which is something we want to exercise. 394 db, err := sql.Open("postgres", pgConnectionString(ip, port)) 395 if err != nil { 396 t.Fatal(err) 397 } 398 defer func() { 399 if err := db.Close(); err != nil { 400 t.Error(err) 401 } 402 }() 403 404 db.SetMaxIdleConns(concurrency) 405 db.SetMaxOpenConns(concurrency) 406 407 var wg sync.WaitGroup 408 defer wg.Wait() 409 410 wg.Add(concurrency) 411 for i := 0; i < concurrency; i++ { 412 go func(i int) { 413 defer wg.Done() 414 _, err := WithInstance(db, &Config{}) 415 if err != nil { 416 t.Errorf("process %d error: %s", i, err) 417 } 418 }(i) 419 } 420 }) 421 } 422 func Test_computeLineFromPos(t *testing.T) { 423 testcases := []struct { 424 pos int 425 wantLine uint 426 wantCol uint 427 input string 428 wantOk bool 429 }{ 430 { 431 15, 2, 6, "SELECT *\nFROM foo", true, // foo table does not exists 432 }, 433 { 434 16, 3, 6, "SELECT *\n\nFROM foo", true, // foo table does not exists, empty line 435 }, 436 { 437 25, 3, 7, "SELECT *\nFROM foo\nWHERE x", true, // x column error 438 }, 439 { 440 27, 5, 7, "SELECT *\n\nFROM foo\n\nWHERE x", true, // x column error, empty lines 441 }, 442 { 443 10, 2, 1, "SELECT *\nFROMM foo", true, // FROMM typo 444 }, 445 { 446 11, 3, 1, "SELECT *\n\nFROMM foo", true, // FROMM typo, empty line 447 }, 448 { 449 17, 2, 8, "SELECT *\nFROM foo", true, // last character 450 }, 451 { 452 18, 0, 0, "SELECT *\nFROM foo", false, // invalid position 453 }, 454 } 455 for i, tc := range testcases { 456 t.Run("tc"+strconv.Itoa(i), func(t *testing.T) { 457 run := func(crlf bool, nonASCII bool) { 458 var name string 459 if crlf { 460 name = "crlf" 461 } else { 462 name = "lf" 463 } 464 if nonASCII { 465 name += "-nonascii" 466 } else { 467 name += "-ascii" 468 } 469 t.Run(name, func(t *testing.T) { 470 input := tc.input 471 if crlf { 472 input = strings.Replace(input, "\n", "\r\n", -1) 473 } 474 if nonASCII { 475 input = strings.Replace(input, "FROM", "FRÖM", -1) 476 } 477 gotLine, gotCol, gotOK := computeLineFromPos(input, tc.pos) 478 479 if tc.wantOk { 480 t.Logf("pos %d, want %d:%d, %#v", tc.pos, tc.wantLine, tc.wantCol, input) 481 } 482 483 if gotOK != tc.wantOk { 484 t.Fatalf("expected ok %v but got %v", tc.wantOk, gotOK) 485 } 486 if gotLine != tc.wantLine { 487 t.Fatalf("expected line %d but got %d", tc.wantLine, gotLine) 488 } 489 if gotCol != tc.wantCol { 490 t.Fatalf("expected col %d but got %d", tc.wantCol, gotCol) 491 } 492 }) 493 } 494 run(false, false) 495 run(true, false) 496 run(false, true) 497 run(true, true) 498 }) 499 } 500 501 }