package oltp
import (
"database/sql"
"path/filepath"
"regexp"
"strings"
"testing"
)
// A store that already exists is the case the schema block cannot see.
// CREATE TABLE IF NOT EXISTS is a no-op there, so a column a migration
// adds is absent while the block runs — and an index in the block that
// names such a column fails the open outright, for every deployment
// that has data. A fresh database passes, which is why this needs its
// own test rather than trusting the others.
//
// RFC 0009's `pair` shipped exactly that way and took the demo server
// down on restart.
func TestSchemaIndexesNeverNameAMigratedColumn(t *testing.T) {
migrated := map[string][]string{} // table -> columns added later
addCol := regexp.MustCompile(`(?i)ALTER TABLE (\w+) ADD COLUMN (\w+)`)
for _, m := range allMigrations() {
if g := addCol.FindStringSubmatch(m); g != nil {
migrated[g[1]] = append(migrated[g[1]], g[2])
}
}
if len(migrated) == 0 {
t.Fatal("no ADD COLUMN migrations found; this guard is not looking at anything")
}
idx := regexp.MustCompile(`(?is)CREATE INDEX[^;]*?ON (\w+)\s*\(([^)]*)\)`)
for _, schema := range allSchemas() {
for _, g := range idx.FindAllStringSubmatch(schema, -1) {
table, cols := g[1], g[2]
for _, late := range migrated[table] {
for _, c := range strings.Split(cols, ",") {
if strings.EqualFold(strings.TrimSpace(c), late) {
t.Errorf("schema indexes %s(%s), but %s.%s only exists after a migration —"+
" move the index into the migration list", table, cols, table, late)
}
}
}
}
}
}
// The same fault, proven against a real store rather than by reading:
// build the database, take it back to the shape it had before the
// migrations, and open it again.
func TestOpensAStoreThatPredatesTheMigrations(t *testing.T) {
path := filepath.Join(t.TempDir(), "oltp.db")
db, err := Open(path)
if err != nil {
t.Fatalf("first open: %v", err)
}
db.Close()
raw, err := sql.Open("sqlite3", path)
if err != nil {
t.Fatalf("reopen raw: %v", err)
}
// Undo what the migrations added. An index over the column has to
// go first — SQLite will not drop a column an index still names.
for _, stmt := range []string{
`DROP INDEX IF EXISTS ci_run_by_pair`,
`ALTER TABLE ci_run DROP COLUMN pair`,
} {
if _, err := raw.Exec(stmt); err != nil {
raw.Close()
t.Fatalf("aging the store (%s): %v", stmt, err)
}
}
raw.Close()
aged, err := Open(path)
if err != nil {
t.Fatalf("a store predating the migrations must still open: %v", err)
}
defer aged.Close()
var n int
if err := aged.db.QueryRow(`SELECT count(*) FROM pragma_table_info('ci_run') WHERE name='pair'`).Scan(&n); err != nil {
t.Fatalf("checking the migrated column: %v", err)
}
if n != 1 {
t.Error("opening an older store did not add the pair column back")
}
}