github.com/gogriddy/goose@v0.0.0-20180817174216-2c751e0981c8/lib/goose/migration_sql_test.go (about)

     1  package goose
     2  
     3  import (
     4  	"strings"
     5  	"testing"
     6  )
     7  
     8  func TestSemicolons(t *testing.T) {
     9  
    10  	type testData struct {
    11  		line   string
    12  		result bool
    13  	}
    14  
    15  	tests := []testData{
    16  		{
    17  			line:   "END;",
    18  			result: true,
    19  		},
    20  		{
    21  			line:   "END; -- comment",
    22  			result: true,
    23  		},
    24  		{
    25  			line:   "END   ; -- comment",
    26  			result: true,
    27  		},
    28  		{
    29  			line:   "END -- comment",
    30  			result: false,
    31  		},
    32  		{
    33  			line:   "END -- comment ;",
    34  			result: false,
    35  		},
    36  		{
    37  			line:   "END \" ; \" -- comment",
    38  			result: false,
    39  		},
    40  	}
    41  
    42  	for _, test := range tests {
    43  		r := endsWithSemicolon(test.line)
    44  		if r != test.result {
    45  			t.Errorf("incorrect semicolon. got %v, want %v", r, test.result)
    46  		}
    47  	}
    48  }
    49  
    50  func TestSplitStatements(t *testing.T) {
    51  
    52  	type testData struct {
    53  		sql       string
    54  		direction Direction
    55  		count     int
    56  	}
    57  
    58  	tests := []testData{
    59  		{
    60  			sql:       functxt,
    61  			direction: DirectionUp,
    62  			count:     2,
    63  		},
    64  		{
    65  			sql:       functxt,
    66  			direction: DirectionDown,
    67  			count:     2,
    68  		},
    69  		{
    70  			sql:       multitxt,
    71  			direction: DirectionUp,
    72  			count:     2,
    73  		},
    74  		{
    75  			sql:       multitxt,
    76  			direction: DirectionDown,
    77  			count:     2,
    78  		},
    79  	}
    80  
    81  	for _, test := range tests {
    82  		stmts := splitSQLStatements(strings.NewReader(test.sql), test.direction)
    83  		if len(stmts) != test.count {
    84  			t.Errorf("incorrect number of stmts. got %v, want %v", len(stmts), test.count)
    85  		}
    86  	}
    87  }
    88  
    89  var functxt = `-- +goose Up
    90  CREATE TABLE IF NOT EXISTS histories (
    91    id                BIGSERIAL  PRIMARY KEY,
    92    current_value     varchar(2000) NOT NULL,
    93    created_at      timestamp with time zone  NOT NULL
    94  );
    95  
    96  -- +goose StatementBegin
    97  CREATE OR REPLACE FUNCTION histories_partition_creation( DATE, DATE )
    98  returns void AS $$
    99  DECLARE
   100    create_query text;
   101  BEGIN
   102    FOR create_query IN SELECT
   103        'CREATE TABLE IF NOT EXISTS histories_'
   104        || TO_CHAR( d, 'YYYY_MM' )
   105        || ' ( CHECK( created_at >= timestamp '''
   106        || TO_CHAR( d, 'YYYY-MM-DD 00:00:00' )
   107        || ''' AND created_at < timestamp '''
   108        || TO_CHAR( d + INTERVAL '1 month', 'YYYY-MM-DD 00:00:00' )
   109        || ''' ) ) inherits ( histories );'
   110      FROM generate_series( $1, $2, '1 month' ) AS d
   111    LOOP
   112      EXECUTE create_query;
   113    END LOOP;  -- LOOP END
   114  END;         -- FUNCTION END
   115  $$
   116  language plpgsql;
   117  -- +goose StatementEnd
   118  
   119  -- +goose Down
   120  drop function histories_partition_creation(DATE, DATE);
   121  drop TABLE histories;
   122  `
   123  
   124  // test multiple up/down transitions in a single script
   125  var multitxt = `-- +goose Up
   126  CREATE TABLE post (
   127      id int NOT NULL,
   128      title text,
   129      body text,
   130      PRIMARY KEY(id)
   131  );
   132  
   133  -- +goose Down
   134  DROP TABLE post;
   135  
   136  -- +goose Up
   137  CREATE TABLE fancier_post (
   138      id int NOT NULL,
   139      title text,
   140      body text,
   141      created_on timestamp without time zone,
   142      PRIMARY KEY(id)
   143  );
   144  
   145  -- +goose Down
   146  DROP TABLE fancier_post;
   147  `