1package goose 2 3import ( 4 "os" 5 "strings" 6 "testing" 7 8 "github.com/pkg/errors" 9) 10 11func TestSemicolons(t *testing.T) { 12 t.Parallel() 13 14 type testData struct { 15 line string 16 result bool 17 } 18 19 tests := []testData{ 20 {line: "END;", result: true}, 21 {line: "END; -- comment", result: true}, 22 {line: "END ; -- comment", result: true}, 23 {line: "END -- comment", result: false}, 24 {line: "END -- comment ;", result: false}, 25 {line: "END \" ; \" -- comment", result: false}, 26 } 27 28 for _, test := range tests { 29 r := endsWithSemicolon(test.line) 30 if r != test.result { 31 t.Errorf("incorrect semicolon. got %v, want %v", r, test.result) 32 } 33 } 34} 35 36func TestSplitStatements(t *testing.T) { 37 t.Parallel() 38 // SetVerbose(true) 39 40 type testData struct { 41 sql string 42 up int 43 down int 44 } 45 46 tt := []testData{ 47 {sql: multilineSQL, up: 4, down: 1}, 48 {sql: emptySQL, up: 0, down: 0}, 49 {sql: emptySQL2, up: 0, down: 0}, 50 {sql: functxt, up: 2, down: 2}, 51 {sql: mysqlChangeDelimiter, up: 4, down: 0}, 52 {sql: copyFromStdin, up: 1, down: 0}, 53 {sql: plpgsqlSyntax, up: 2, down: 2}, 54 {sql: plpgsqlSyntaxMixedStatements, up: 2, down: 2}, 55 } 56 57 for i, test := range tt { 58 // up 59 stmts, _, err := parseSQLMigration(strings.NewReader(test.sql), true) 60 if err != nil { 61 t.Error(errors.Wrapf(err, "tt[%v] unexpected error", i)) 62 } 63 if len(stmts) != test.up { 64 t.Errorf("tt[%v] incorrect number of up stmts. got %v (%+v), want %v", i, len(stmts), stmts, test.up) 65 } 66 67 // down 68 stmts, _, err = parseSQLMigration(strings.NewReader(test.sql), false) 69 if err != nil { 70 t.Error(errors.Wrapf(err, "tt[%v] unexpected error", i)) 71 } 72 if len(stmts) != test.down { 73 t.Errorf("tt[%v] incorrect number of down stmts. got %v (%+v), want %v", i, len(stmts), stmts, test.down) 74 } 75 } 76} 77 78func TestUseTransactions(t *testing.T) { 79 t.Parallel() 80 81 type testData struct { 82 fileName string 83 useTransactions bool 84 } 85 86 tests := []testData{ 87 {fileName: "./examples/sql-migrations/00001_create_users_table.sql", useTransactions: true}, 88 {fileName: "./examples/sql-migrations/00002_rename_root.sql", useTransactions: true}, 89 {fileName: "./examples/sql-migrations/00003_no_transaction.sql", useTransactions: false}, 90 } 91 92 for _, test := range tests { 93 f, err := os.Open(test.fileName) 94 if err != nil { 95 t.Error(err) 96 } 97 _, useTx, err := parseSQLMigration(f, true) 98 if err != nil { 99 t.Error(err) 100 } 101 if useTx != test.useTransactions { 102 t.Errorf("Failed transaction check. got %v, want %v", useTx, test.useTransactions) 103 } 104 f.Close() 105 } 106} 107 108func TestParsingErrors(t *testing.T) { 109 tt := []string{ 110 statementBeginNoStatementEnd, 111 unfinishedSQL, 112 noUpDownAnnotations, 113 multiUpDown, 114 downFirst, 115 } 116 for i, sql := range tt { 117 _, _, err := parseSQLMigration(strings.NewReader(sql), true) 118 if err == nil { 119 t.Errorf("expected error on tt[%v] %q", i, sql) 120 } 121 } 122} 123 124var multilineSQL = `-- +goose Up 125CREATE TABLE post ( 126 id int NOT NULL, 127 title text, 128 body text, 129 PRIMARY KEY(id) 130); -- 1st stmt 131 132-- comment 133SELECT 2; -- 2nd stmt 134SELECT 3; SELECT 3; -- 3rd stmt 135SELECT 4; -- 4th stmt 136 137-- +goose Down 138-- comment 139DROP TABLE post; -- 1st stmt 140` 141 142var functxt = `-- +goose Up 143CREATE TABLE IF NOT EXISTS histories ( 144 id BIGSERIAL PRIMARY KEY, 145 current_value varchar(2000) NOT NULL, 146 created_at timestamp with time zone NOT NULL 147); 148 149-- +goose StatementBegin 150CREATE OR REPLACE FUNCTION histories_partition_creation( DATE, DATE ) 151returns void AS $$ 152DECLARE 153 create_query text; 154BEGIN 155 FOR create_query IN SELECT 156 'CREATE TABLE IF NOT EXISTS histories_' 157 || TO_CHAR( d, 'YYYY_MM' ) 158 || ' ( CHECK( created_at >= timestamp ''' 159 || TO_CHAR( d, 'YYYY-MM-DD 00:00:00' ) 160 || ''' AND created_at < timestamp ''' 161 || TO_CHAR( d + INTERVAL '1 month', 'YYYY-MM-DD 00:00:00' ) 162 || ''' ) ) inherits ( histories );' 163 FROM generate_series( $1, $2, '1 month' ) AS d 164 LOOP 165 EXECUTE create_query; 166 END LOOP; -- LOOP END 167END; -- FUNCTION END 168$$ 169language plpgsql; 170-- +goose StatementEnd 171 172-- +goose Down 173drop function histories_partition_creation(DATE, DATE); 174drop TABLE histories; 175` 176 177var multiUpDown = `-- +goose Up 178CREATE TABLE post ( 179 id int NOT NULL, 180 title text, 181 body text, 182 PRIMARY KEY(id) 183); 184 185-- +goose Down 186DROP TABLE post; 187 188-- +goose Up 189CREATE TABLE fancier_post ( 190 id int NOT NULL, 191 title text, 192 body text, 193 created_on timestamp without time zone, 194 PRIMARY KEY(id) 195); 196` 197 198var downFirst = `-- +goose Down 199DROP TABLE fancier_post; 200` 201 202var statementBeginNoStatementEnd = `-- +goose Up 203CREATE TABLE IF NOT EXISTS histories ( 204 id BIGSERIAL PRIMARY KEY, 205 current_value varchar(2000) NOT NULL, 206 created_at timestamp with time zone NOT NULL 207); 208 209-- +goose StatementBegin 210CREATE OR REPLACE FUNCTION histories_partition_creation( DATE, DATE ) 211returns void AS $$ 212DECLARE 213 create_query text; 214BEGIN 215 FOR create_query IN SELECT 216 'CREATE TABLE IF NOT EXISTS histories_' 217 || TO_CHAR( d, 'YYYY_MM' ) 218 || ' ( CHECK( created_at >= timestamp ''' 219 || TO_CHAR( d, 'YYYY-MM-DD 00:00:00' ) 220 || ''' AND created_at < timestamp ''' 221 || TO_CHAR( d + INTERVAL '1 month', 'YYYY-MM-DD 00:00:00' ) 222 || ''' ) ) inherits ( histories );' 223 FROM generate_series( $1, $2, '1 month' ) AS d 224 LOOP 225 EXECUTE create_query; 226 END LOOP; -- LOOP END 227END; -- FUNCTION END 228$$ 229language plpgsql; 230 231-- +goose Down 232drop function histories_partition_creation(DATE, DATE); 233drop TABLE histories; 234` 235 236var unfinishedSQL = ` 237-- +goose Up 238ALTER TABLE post 239 240-- +goose Down 241` 242 243var emptySQL = `-- +goose Up 244-- This is just a comment` 245 246var emptySQL2 = ` 247 248-- comment 249-- +goose Up 250 251-- comment 252-- +goose Down 253 254` 255 256var noUpDownAnnotations = ` 257CREATE TABLE post ( 258 id int NOT NULL, 259 title text, 260 body text, 261 PRIMARY KEY(id) 262); 263` 264 265var mysqlChangeDelimiter = ` 266-- +goose Up 267-- +goose StatementBegin 268DELIMITER | 269-- +goose StatementEnd 270 271-- +goose StatementBegin 272CREATE FUNCTION my_func( str CHAR(255) ) RETURNS CHAR(255) DETERMINISTIC 273BEGIN 274 RETURN "Dummy Body"; 275END | 276-- +goose StatementEnd 277 278-- +goose StatementBegin 279DELIMITER ; 280-- +goose StatementEnd 281 282select my_func("123") from dual; 283-- +goose Down 284` 285 286var copyFromStdin = ` 287-- +goose Up 288-- +goose StatementBegin 289COPY public.django_content_type (id, app_label, model) FROM stdin; 2901 admin logentry 2912 auth permission 2923 auth group 2934 auth user 2945 contenttypes contenttype 2956 sessions session 296\. 297-- +goose StatementEnd 298` 299 300var plpgsqlSyntax = ` 301-- +goose Up 302-- +goose StatementBegin 303CREATE OR REPLACE FUNCTION update_updated_at_column() 304RETURNS TRIGGER AS $$ 305BEGIN 306 NEW.updated_at = now(); 307 RETURN NEW; 308END; 309$$ language 'plpgsql'; 310-- +goose StatementEnd 311-- +goose StatementBegin 312CREATE TRIGGER update_properties_updated_at BEFORE UPDATE ON properties FOR EACH ROW EXECUTE PROCEDURE update_updated_at_column(); 313-- +goose StatementEnd 314 315-- +goose Down 316-- +goose StatementBegin 317DROP TRIGGER update_properties_updated_at 318-- +goose StatementEnd 319-- +goose StatementBegin 320DROP FUNCTION update_updated_at_column() 321-- +goose StatementEnd 322` 323 324var plpgsqlSyntaxMixedStatements = ` 325-- +goose Up 326-- +goose StatementBegin 327CREATE OR REPLACE FUNCTION update_updated_at_column() 328RETURNS TRIGGER AS $$ 329BEGIN 330 NEW.updated_at = now(); 331 RETURN NEW; 332END; 333$$ language 'plpgsql'; 334-- +goose StatementEnd 335 336CREATE TRIGGER update_properties_updated_at 337BEFORE UPDATE 338ON properties 339FOR EACH ROW EXECUTE PROCEDURE update_updated_at_column(); 340 341-- +goose Down 342DROP TRIGGER update_properties_updated_at; 343DROP FUNCTION update_updated_at_column(); 344` 345