github.com/gocaveman/caveman@v0.0.0-20191211162744-0ddf99dbdf6e/ddl/ddl-sqlite3_test.go (about)

     1  package ddl
     2  
     3  import (
     4  	"database/sql"
     5  	"testing"
     6  
     7  	"github.com/stretchr/testify/assert"
     8  
     9  	_ "github.com/mattn/go-sqlite3"
    10  )
    11  
    12  // TestSQLite3 tests each feature against a SQLite3 database to ensure syntax is correct.
    13  func TestSQLite3(t *testing.T) {
    14  
    15  	assert := assert.New(t)
    16  
    17  	f := NewSQLite3Formatter(false)
    18  
    19  	b := New()
    20  	b.SetCategory("test")
    21  
    22  	db, err := sql.Open("sqlite3", `file:TestSQLite3?mode=memory&cache=shared`)
    23  	if err != nil {
    24  		t.Fatal(err)
    25  	}
    26  
    27  	runSQL := func(up, _ []string, err error) {
    28  		if err != nil {
    29  			assert.NoError(err)
    30  			return
    31  		}
    32  		for _, s := range up {
    33  			t.Logf("Running SQL: %s", s)
    34  			_, err = db.Exec(s)
    35  			assert.NoError(err)
    36  		}
    37  	}
    38  
    39  	// -- create table
    40  
    41  	// one of each type on it (except the integer pk)
    42  	runSQL(b.Reset().
    43  		CreateTable("table_types").
    44  		Column("table_types_id", VarCharPK).PrimaryKey().
    45  		ColumnCustom("test_custom", "TEXT NOT NULL").
    46  		Column("test_varcharfk", VarCharFK).Length(255). // NOTE: lengths are ignored by SQLite, they don't get output
    47  		Column("test_bigintfk", BigIntFK).
    48  		Column("test_int", Int).
    49  		Column("test_intu", IntU).
    50  		Column("test_bigint", BigInt).
    51  		Column("test_bigintu", BigIntU).
    52  		Column("test_double", Double).
    53  		Column("test_datetime", DateTime).
    54  		Column("test_varchar", VarChar).Length(255).
    55  		Column("test_bool", Bool).
    56  		Column("test_text", Text).
    57  		Column("test_blob", Blob).
    58  		MakeSQL(f))
    59  
    60  	// integer autoinc pk
    61  	runSQL(b.Reset().
    62  		CreateTable("table_autoinc").
    63  		Column("table_autoinc_id", BigIntAutoPK).PrimaryKey().
    64  		Column("test_varchar", VarChar).
    65  		MakeSQL(f))
    66  
    67  	// mulitple pks
    68  	runSQL(b.Reset().
    69  		CreateTable("table_join").
    70  		Column("table_join_a_id", VarCharPK).PrimaryKey().
    71  		Column("table_join_b_id", VarCharPK).PrimaryKey().
    72  		MakeSQL(f))
    73  
    74  	// if not exists
    75  	runSQL(b.Reset().
    76  		CreateTable("table_existential").IfNotExists().
    77  		Column("table_existential_id", VarCharPK).PrimaryKey().
    78  		MakeSQL(f))
    79  
    80  	// null
    81  	runSQL(b.Reset().
    82  		CreateTable("table_null").
    83  		Column("table_null_id", VarCharPK).PrimaryKey().
    84  		Column("test_int", Int).Null().
    85  		Column("test_intu", IntU).Null().
    86  		Column("test_bigint", BigInt).Null().
    87  		Column("test_bigintu", BigIntU).Null().
    88  		Column("test_double", Double).Null().
    89  		Column("test_datetime", DateTime).Null().
    90  		Column("test_varchar", VarChar).Length(255).Null().
    91  		Column("test_bool", Bool).Null().
    92  		Column("test_text", Text).Null().
    93  		Column("test_blob", Blob).Null().
    94  		MakeSQL(f))
    95  
    96  	// case sensitive
    97  	runSQL(b.Reset().
    98  		CreateTable("table_cs").
    99  		Column("table_cs_id", VarCharPK).PrimaryKey().
   100  		Column("test_varchar_cs", VarChar).CaseSensitive().
   101  		Column("test_text_cs", Text).CaseSensitive().
   102  		MakeSQL(f))
   103  
   104  	// -- drop table
   105  	runSQL(b.Reset().
   106  		DropTable("table_cs").
   107  		MakeSQL(f))
   108  
   109  	// -- rename table
   110  	runSQL(b.Reset().
   111  		AlterTableRename("table_null", "table_null2").
   112  		MakeSQL(f))
   113  
   114  	// -- add column
   115  	runSQL(b.Reset().
   116  		AlterTableAdd("table_existential").
   117  		Column("other_cool_field", VarChar).Null().Default("mozdef").CaseSensitive().
   118  		MakeSQL(f))
   119  
   120  	// -- create index
   121  	runSQL(b.Reset().
   122  		CreateIndex("table_existential_other", "table_existential").Columns("other_cool_field").
   123  		MakeSQL(f))
   124  
   125  	// -- drop index
   126  	runSQL(b.Reset().
   127  		DropIndex("table_existential_other", "table_existential").
   128  		MakeSQL(f))
   129  
   130  	// -- more create index
   131  
   132  	// unique
   133  	runSQL(b.Reset().
   134  		CreateIndex("table_existential_other", "table_existential").
   135  		Unique().
   136  		Columns("other_cool_field").
   137  		MakeSQL(f))
   138  
   139  	// if not exists
   140  	runSQL(b.Reset().
   141  		CreateIndex("table_existential_other", "table_existential").
   142  		IfNotExists().
   143  		Columns("other_cool_field").
   144  		MakeSQL(f))
   145  
   146  }