1package sqlparse 2 3import ( 4 "strings" 5 "testing" 6 7 . "gopkg.in/check.v1" 8) 9 10func Test(t *testing.T) { TestingT(t) } 11 12type SqlParseSuite struct { 13} 14 15var _ = Suite(&SqlParseSuite{}) 16 17func (s *SqlParseSuite) TestSemicolons(c *C) { 18 type testData struct { 19 line string 20 result bool 21 } 22 23 tests := []testData{ 24 { 25 line: "END;", 26 result: true, 27 }, 28 { 29 line: "END; -- comment", 30 result: true, 31 }, 32 { 33 line: "END ; -- comment", 34 result: true, 35 }, 36 { 37 line: "END -- comment", 38 result: false, 39 }, 40 { 41 line: "END -- comment ;", 42 result: false, 43 }, 44 { 45 line: "END \" ; \" -- comment", 46 result: false, 47 }, 48 } 49 50 for _, test := range tests { 51 r := endsWithSemicolon(test.line) 52 c.Assert(r, Equals, test.result) 53 } 54} 55 56func (s *SqlParseSuite) TestSplitStatements(c *C) { 57 type testData struct { 58 sql string 59 upCount int 60 downCount int 61 } 62 63 tests := []testData{ 64 { 65 sql: functxt, 66 upCount: 2, 67 downCount: 2, 68 }, 69 { 70 sql: multitxt, 71 upCount: 2, 72 downCount: 2, 73 }, 74 } 75 76 for _, test := range tests { 77 migration, err := ParseMigration(strings.NewReader(test.sql)) 78 c.Assert(err, IsNil) 79 c.Assert(migration.UpStatements, HasLen, test.upCount) 80 c.Assert(migration.DownStatements, HasLen, test.downCount) 81 } 82} 83 84func (s *SqlParseSuite) TestIntentionallyBadStatements(c *C) { 85 for _, test := range intentionallyBad { 86 _, err := ParseMigration(strings.NewReader(test)) 87 c.Assert(err, NotNil) 88 } 89} 90 91func (s *SqlParseSuite) TestJustComment(c *C) { 92 for _, test := range justAComment { 93 _, err := ParseMigration(strings.NewReader(test)) 94 c.Assert(err, NotNil) 95 } 96} 97 98func (s *SqlParseSuite) TestCustomTerminator(c *C) { 99 LineSeparator = "GO" 100 defer func() { LineSeparator = "" }() 101 102 type testData struct { 103 sql string 104 upCount int 105 downCount int 106 } 107 108 tests := []testData{ 109 { 110 sql: functxtSplitByGO, 111 upCount: 2, 112 downCount: 2, 113 }, 114 { 115 sql: multitxtSplitByGO, 116 upCount: 2, 117 downCount: 2, 118 }, 119 } 120 121 for _, test := range tests { 122 migration, err := ParseMigration(strings.NewReader(test.sql)) 123 c.Assert(err, IsNil) 124 c.Assert(migration.UpStatements, HasLen, test.upCount) 125 c.Assert(migration.DownStatements, HasLen, test.downCount) 126 } 127} 128 129var functxt = `-- +migrate Up 130CREATE TABLE IF NOT EXISTS histories ( 131 id BIGSERIAL PRIMARY KEY, 132 current_value varchar(2000) NOT NULL, 133 created_at timestamp with time zone NOT NULL 134); 135 136-- +migrate StatementBegin 137CREATE OR REPLACE FUNCTION histories_partition_creation( DATE, DATE ) 138returns void AS $$ 139DECLARE 140 create_query text; 141BEGIN 142 FOR create_query IN SELECT 143 'CREATE TABLE IF NOT EXISTS histories_' 144 || TO_CHAR( d, 'YYYY_MM' ) 145 || ' ( CHECK( created_at >= timestamp ''' 146 || TO_CHAR( d, 'YYYY-MM-DD 00:00:00' ) 147 || ''' AND created_at < timestamp ''' 148 || TO_CHAR( d + INTERVAL '1 month', 'YYYY-MM-DD 00:00:00' ) 149 || ''' ) ) inherits ( histories );' 150 FROM generate_series( $1, $2, '1 month' ) AS d 151 LOOP 152 EXECUTE create_query; 153 END LOOP; -- LOOP END 154END; -- FUNCTION END 155$$ 156language plpgsql; 157-- +migrate StatementEnd 158 159-- +migrate Down 160drop function histories_partition_creation(DATE, DATE); 161drop TABLE histories; 162` 163 164// test multiple up/down transitions in a single script 165var multitxt = `-- +migrate Up 166CREATE TABLE post ( 167 id int NOT NULL, 168 title text, 169 body text, 170 PRIMARY KEY(id) 171); 172 173-- +migrate Down 174DROP TABLE post; 175 176-- +migrate Up 177CREATE TABLE fancier_post ( 178 id int NOT NULL, 179 title text, 180 body text, 181 created_on timestamp without time zone, 182 PRIMARY KEY(id) 183); 184 185-- +migrate Down 186DROP TABLE fancier_post; 187` 188 189// raise error when statements are not explicitly ended 190var intentionallyBad = []string{ 191 // first statement missing terminator 192 `-- +migrate Up 193CREATE TABLE post ( 194 id int NOT NULL, 195 title text, 196 body text, 197 PRIMARY KEY(id) 198) 199 200-- +migrate Down 201DROP TABLE post; 202 203-- +migrate Up 204CREATE TABLE fancier_post ( 205 id int NOT NULL, 206 title text, 207 body text, 208 created_on timestamp without time zone, 209 PRIMARY KEY(id) 210); 211 212-- +migrate Down 213DROP TABLE fancier_post; 214`, 215 216 // second half of first statement missing terminator 217 `-- +migrate Up 218CREATE TABLE post ( 219 id int NOT NULL, 220 title text, 221 body text, 222 PRIMARY KEY(id) 223); 224 225SELECT 'No ending semicolon' 226 227-- +migrate Down 228DROP TABLE post; 229 230-- +migrate Up 231CREATE TABLE fancier_post ( 232 id int NOT NULL, 233 title text, 234 body text, 235 created_on timestamp without time zone, 236 PRIMARY KEY(id) 237); 238 239-- +migrate Down 240DROP TABLE fancier_post; 241`, 242 243 // second statement missing terminator 244 `-- +migrate Up 245CREATE TABLE post ( 246 id int NOT NULL, 247 title text, 248 body text, 249 PRIMARY KEY(id) 250); 251 252-- +migrate Down 253DROP TABLE post 254 255-- +migrate Up 256CREATE TABLE fancier_post ( 257 id int NOT NULL, 258 title text, 259 body text, 260 created_on timestamp without time zone, 261 PRIMARY KEY(id) 262); 263 264-- +migrate Down 265DROP TABLE fancier_post; 266`, 267 268 // trailing text after explicit StatementEnd 269 `-- +migrate Up 270-- +migrate StatementBegin 271CREATE TABLE post ( 272 id int NOT NULL, 273 title text, 274 body text, 275 PRIMARY KEY(id) 276); 277-- +migrate StatementBegin 278SELECT 'no semicolon' 279 280-- +migrate Down 281DROP TABLE post; 282 283-- +migrate Up 284CREATE TABLE fancier_post ( 285 id int NOT NULL, 286 title text, 287 body text, 288 created_on timestamp without time zone, 289 PRIMARY KEY(id) 290); 291 292-- +migrate Down 293DROP TABLE fancier_post; 294`, 295} 296 297// Same as functxt above but split by GO lines 298var functxtSplitByGO = `-- +migrate Up 299CREATE TABLE IF NOT EXISTS histories ( 300 id BIGSERIAL PRIMARY KEY, 301 current_value varchar(2000) NOT NULL, 302 created_at timestamp with time zone NOT NULL 303) 304GO 305 306-- +migrate StatementBegin 307CREATE OR REPLACE FUNCTION histories_partition_creation( DATE, DATE ) 308returns void AS $$ 309DECLARE 310 create_query text; 311BEGIN 312 FOR create_query IN SELECT 313 'CREATE TABLE IF NOT EXISTS histories_' 314 || TO_CHAR( d, 'YYYY_MM' ) 315 || ' ( CHECK( created_at >= timestamp ''' 316 || TO_CHAR( d, 'YYYY-MM-DD 00:00:00' ) 317 || ''' AND created_at < timestamp ''' 318 || TO_CHAR( d + INTERVAL '1 month', 'YYYY-MM-DD 00:00:00' ) 319 || ''' ) ) inherits ( histories );' 320 FROM generate_series( $1, $2, '1 month' ) AS d 321 LOOP 322 EXECUTE create_query; 323 END LOOP; -- LOOP END 324END; -- FUNCTION END 325$$ 326GO 327/* while GO wouldn't be used in a statement like this, I'm including it for the test */ 328language plpgsql 329-- +migrate StatementEnd 330 331-- +migrate Down 332drop function histories_partition_creation(DATE, DATE) 333GO 334drop TABLE histories 335GO 336` 337 338// test multiple up/down transitions in a single script, split by GO lines 339var multitxtSplitByGO = `-- +migrate Up 340CREATE TABLE post ( 341 id int NOT NULL, 342 title text, 343 body text, 344 PRIMARY KEY(id) 345) 346GO 347 348-- +migrate Down 349DROP TABLE post 350GO 351 352-- +migrate Up 353CREATE TABLE fancier_post ( 354 id int NOT NULL, 355 title text, 356 body text, 357 created_on timestamp without time zone, 358 PRIMARY KEY(id) 359) 360GO 361 362-- +migrate Down 363DROP TABLE fancier_post 364GO 365` 366 367// test a comment without sql instruction 368var justAComment = []string{ 369 `-- +migrate Up 370CREATE TABLE post ( 371 id int NOT NULL, 372 title text, 373 body text, 374 PRIMARY KEY(id) 375) 376 377-- +migrate Down 378-- no migration here 379`} 380