@@ -109,23 +109,23 @@ func TestParseTable_foreign(t *testing.T) {
109109
110110func TestParseTable_upstream (t * testing.T ) {
111111 tests := []string {
112- " CREATE TABLE t(a INT, b TEXT)" ,
112+ ` CREATE TABLE t(a INT, b TEXT)` ,
113113 `CREATE TABLE t1(
114114 id INTEGER PRIMARY KEY ASC,
115115 name TEXT DEFAULT (upper('x')),
116116 c TEXT CHECK((a+(b))) -- col comment
117117 ) -- table comment` ,
118118 `CREATE TEMP TABLE IF NOT EXISTS [w"eird]]t] ("q""q" INT)` ,
119- " ALTER TABLE t RENAME COLUMN a TO b;" ,
120- " ALTER TABLE main.t ADD COLUMN z INTEGER DEFAULT (1+(2*(3)))" ,
121- " /* cstyle */ CREATE TABLE x(y INT); -- tail" ,
122- " CREATE TABLE ct (d INT DEFAULT ( (1+2) ), e TEXT DEFAULT '))')" ,
123-
124- " CREATE TABLE foo (col1 INTEGER PRIMARY KEY AUTOINCREMENT, col2 TEXT, col3 TEXT);" ,
125- " CREATE TABLE tcpkai (col INTEGER, PRIMARY KEY (col AUTOINCREMENT));" ,
126- " CREATE TABLE t1(x INTEGER PRIMARY KEY, y);" ,
127- " create table employee(first varchar(15),last varchar(20),age number(3),address varchar(30),city varchar(20),state varchar(20));" ,
128- " CREATE TEMP TABLE IF NOT EXISTS main.foo /* This is the main table */ (col1 INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, col2 TEXT DEFAULT CURRENT_TIMESTAMP, col3 FLOAT(8.12), col4 BLOB COLLATE BINARY /* Use this column for storing pictures */, CONSTRAINT tbl1 UNIQUE (col1 COLLATE c1 ASC, col2 COLLATE c2 DESC)) WITHOUT ROWID; -- this is a line comment" ,
119+ ` ALTER TABLE t RENAME COLUMN a TO b;` ,
120+ ` ALTER TABLE main.t ADD COLUMN z INTEGER DEFAULT (1+(2*(3)))` ,
121+ ` /* cstyle */ CREATE TABLE x(y INT); -- tail` ,
122+ ` CREATE TABLE ct (d INT DEFAULT ( (1+2) ), e TEXT DEFAULT '))')` ,
123+
124+ ` CREATE TABLE foo (col1 INTEGER PRIMARY KEY AUTOINCREMENT, col2 TEXT, col3 TEXT);` ,
125+ ` CREATE TABLE tcpkai (col INTEGER, PRIMARY KEY (col AUTOINCREMENT));` ,
126+ ` CREATE TABLE t1(x INTEGER PRIMARY KEY, y);` ,
127+ ` create table employee(first varchar(15),last varchar(20),age number(3),address varchar(30),city varchar(20),state varchar(20));` ,
128+ ` CREATE TEMP TABLE IF NOT EXISTS main.foo /* This is the main table */ (col1 INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, col2 TEXT DEFAULT CURRENT_TIMESTAMP, col3 FLOAT(8.12), col4 BLOB COLLATE BINARY /* Use this column for storing pictures */, CONSTRAINT tbl1 UNIQUE (col1 COLLATE c1 ASC, col2 COLLATE c2 DESC)) WITHOUT ROWID; -- this is a line comment` ,
129129 `CREATE TABLE "BalancesTbl2" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, "checkingBal" REAL DEFAULT 0, "cashBal" REAL DEFAULT .0, "defitCardBal" REAL DEFAULT 1.0, "creditCardBal" REAL DEFAULT +1.5, testValue TEXT DEFAULT 'Hello World', testValue2 TEXT DEFAULT 'Hello''s World', testValue3 TEXT DEFAULT "Hello''s World", testValue4 TEXT DEFAULT "Hello"" World") WITHOUT ROWID, STRICT;` ,
130130 `CREATE TABLE User
131131 -- A table comment
@@ -142,20 +142,99 @@ func TestParseTable_upstream(t *testing.T) {
142142 , test -- test 123
143143 INTEGER, UNIQUE (flags /* Hello World*/, test) -- This is another table comment
144144 );` ,
145- " CREATE TABLE Sales(Price INT, Qty INT, Total INT GENERATED ALWAYS AS (Price*Qty) VIRTUAL, Item TEXT);" ,
145+ ` CREATE TABLE Sales(Price INT, Qty INT, Total INT GENERATED ALWAYS AS (Price*Qty) VIRTUAL, Item TEXT);` ,
146146 `CREATE TABLE Constraints(
147147 PK INTEGER CONSTRAINT 'PrimaryKey' PRIMARY KEY CONSTRAINT 'NotNull' NOT NULL CONSTRAINT 'Unique' UNIQUE
148148 CONSTRAINT 'Check' CHECK (PK>0) CONSTRAINT 'Default' DEFAULT 2 CONSTRAINT 'Collate' COLLATE NOCASE,
149149 FK INTEGER CONSTRAINT 'ForeignKey' REFERENCES ForeignTable (Id),
150150 GEN INTEGER CONSTRAINT 'Generated' AS (abs(PK)));` ,
151+ `CREATE TABLE ColumnChecks(Num INT CONSTRAINT 'GT' CHECK (Num>0) CONSTRAINT 'LT' CHECK (Num<10) CONSTRAINT 'NE' CHECK(Num<>5));` ,
152+ // GENERATED ALWAYS AS ... STORED
153+ `CREATE TABLE Inventory(Price REAL, Qty INT, Total REAL GENERATED ALWAYS AS (Price*Qty) STORED);` ,
154+ // Table-level CHECK constraint
155+ `CREATE TABLE RangeCheck(lo INT, hi INT, CHECK (lo < hi));` ,
156+ // Table-level CHECK constraint with name
157+ `CREATE TABLE NamedCheck(val INT, CONSTRAINT 'ValidRange' CHECK (val >= 0 AND val <= 100));` ,
158+ // Composite PRIMARY KEY (table-level)
159+ `CREATE TABLE CompositePK(a INT, b TEXT, c REAL, PRIMARY KEY (a, b));` ,
160+ // Composite PRIMARY KEY with ordering and collation
161+ `CREATE TABLE CompositePKOrder(x INT, y TEXT, PRIMARY KEY (x DESC, y COLLATE NOCASE ASC));` ,
162+ // Table-level FOREIGN KEY with ON DELETE CASCADE and ON UPDATE SET NULL
163+ `CREATE TABLE Orders(id INTEGER PRIMARY KEY, customer_id INT, FOREIGN KEY (customer_id) REFERENCES Customers(id) ON DELETE CASCADE ON UPDATE SET NULL);` ,
164+ // Table-level FOREIGN KEY with ON DELETE SET DEFAULT and ON UPDATE RESTRICT
165+ `CREATE TABLE LineItems(id INTEGER PRIMARY KEY, order_id INT, FOREIGN KEY (order_id) REFERENCES Orders(id) ON DELETE SET DEFAULT ON UPDATE RESTRICT);` ,
166+ // Table-level FOREIGN KEY with ON DELETE NO ACTION and DEFERRABLE INITIALLY DEFERRED
167+ `CREATE TABLE Payments(id INTEGER PRIMARY KEY, order_id INT, FOREIGN KEY (order_id) REFERENCES Orders(id) ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED);` ,
168+ // Table-level FOREIGN KEY with NOT DEFERRABLE INITIALLY IMMEDIATE
169+ `CREATE TABLE Shipments(id INTEGER PRIMARY KEY, order_id INT, FOREIGN KEY (order_id) REFERENCES Orders(id) ON DELETE RESTRICT NOT DEFERRABLE INITIALLY IMMEDIATE);` ,
170+ // Column-level REFERENCES with ON DELETE CASCADE ON UPDATE SET NULL
171+ `CREATE TABLE Detail(id INTEGER PRIMARY KEY, parent_id INT REFERENCES Parent(id) ON DELETE CASCADE ON UPDATE SET NULL);` ,
172+ // Multiple table constraints (PK + UNIQUE + CHECK + FK)
173+ `CREATE TABLE Multi(a INT, b INT, c INT, d INT,
174+ PRIMARY KEY (a, b),
175+ UNIQUE (c),
176+ CHECK (d > 0),
177+ FOREIGN KEY (d) REFERENCES Other(id) ON DELETE CASCADE);` ,
178+ // ON CONFLICT clauses on column constraints
179+ `CREATE TABLE ConflictTest(
180+ a INT PRIMARY KEY ON CONFLICT ROLLBACK,
181+ b INT NOT NULL ON CONFLICT ABORT,
182+ c INT UNIQUE ON CONFLICT REPLACE);` ,
183+ // ON CONFLICT clause on table-level PRIMARY KEY
184+ `CREATE TABLE ConflictPK(a INT, b INT, PRIMARY KEY (a, b) ON CONFLICT IGNORE);` ,
185+ // ON CONFLICT clause on table-level UNIQUE
186+ `CREATE TABLE ConflictUniq(a INT, CONSTRAINT 'uniq1' UNIQUE (a) ON CONFLICT FAIL);` ,
187+ // STRICT only (without WITHOUT ROWID)
188+ `CREATE TABLE StrictOnly(id INTEGER PRIMARY KEY, val TEXT) STRICT;` ,
189+ // Backtick-quoted identifiers
190+ "CREATE TABLE `my table`(`col 1` INT, `col 2` TEXT);" ,
191+ // DEFAULT with negative number
192+ `CREATE TABLE Defaults(a INT DEFAULT -42, b REAL DEFAULT -3.14, c INT DEFAULT +0);` ,
193+ // DEFAULT with various keywords
194+ `CREATE TABLE DefaultKeywords(a TEXT DEFAULT CURRENT_DATE, b TEXT DEFAULT CURRENT_TIME, c TEXT DEFAULT CURRENT_TIMESTAMP);` ,
195+ // Single column table
196+ `CREATE TABLE Single(only_col INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL);` ,
197+ // Column with no type
198+ `CREATE TABLE NoTypes(a, b, c);` ,
199+ // PRIMARY KEY DESC with AUTOINCREMENT
200+ `CREATE TABLE PKDesc(id INTEGER PRIMARY KEY DESC);` ,
201+ // Column-level foreign key with multiple columns in referenced table
202+ `CREATE TABLE FKMultiCol(id INTEGER PRIMARY KEY, a INT, b INT,
203+ FOREIGN KEY (a, b) REFERENCES Other(x, y) ON DELETE SET NULL ON UPDATE CASCADE);` ,
204+ // DEFERRABLE without INITIALLY clause
205+ `CREATE TABLE DeferSimple(id INTEGER PRIMARY KEY, ref INT,
206+ FOREIGN KEY (ref) REFERENCES Parent(id) DEFERRABLE);` ,
207+ // NOT DEFERRABLE INITIALLY DEFERRED
208+ `CREATE TABLE DeferNotDef(id INTEGER PRIMARY KEY, ref INT,
209+ FOREIGN KEY (ref) REFERENCES Parent(id) NOT DEFERRABLE INITIALLY DEFERRED);` ,
210+ // Named table-level constraints
211+ `CREATE TABLE NamedConstraints(a INT, b INT, c INT,
212+ CONSTRAINT pk_named PRIMARY KEY (a),
213+ CONSTRAINT uq_named UNIQUE (b),
214+ CONSTRAINT ck_named CHECK (c != 0));` ,
215+ // All constraint types combined on a single column
216+ `CREATE TABLE FullCol(x INTEGER PRIMARY KEY ON CONFLICT ABORT NOT NULL ON CONFLICT FAIL UNIQUE ON CONFLICT IGNORE
217+ CHECK (x > 0) DEFAULT 1 COLLATE BINARY REFERENCES Other(id));` ,
218+ // Generated column shorthand (AS without GENERATED ALWAYS)
219+ `CREATE TABLE GenShort(a INT, b INT, c INT AS (a + b) STORED, d TEXT AS (a || b) VIRTUAL);` ,
220+ // Mixed quoting styles
221+ "CREATE TABLE \" Mixed\" ([col1] INT, `col2` TEXT, col3 BLOB);" ,
222+ // Table with many columns and types
223+ `CREATE TABLE AllTypes(a INTEGER, b REAL, c TEXT, d BLOB, e NUMERIC, f BOOLEAN, g DATE, h DATETIME, i DECIMAL(10,2), j VARCHAR(255));` ,
224+ // Empty string and NULL defaults
225+ `CREATE TABLE EmptyDefaults(a TEXT DEFAULT '', b TEXT DEFAULT NULL, c INT DEFAULT 0);` ,
226+ // Case insensitivity
227+ `create temporary table if not exists Foo(Bar integer primary key autoincrement, Baz text not null unique);` ,
228+ // ALTER TABLE with schema-qualified name and ADD COLUMN with all constraints
229+ `ALTER TABLE main.foo ADD COLUMN new_col INTEGER NOT NULL DEFAULT 0 REFERENCES Other(id)` ,
151230
152231 // https://www.sqlite.org/lang_altertable.html
153- " ALTER TABLE foo RENAME TO bar" ,
154- " ALTER TABLE temp.foo RENAME TO bar" ,
155- " ALTER TABLE foo RENAME COLUMN col1 TO col2" ,
156- " ALTER TABLE foo RENAME col1 TO col2" ,
157- " ALTER TABLE foo DROP COLUMN col1" ,
158- " ALTER TABLE foo ADD COLUMN col1 TEXT DEFAULT 'Hello' COLLATE NOCASE" ,
232+ ` ALTER TABLE foo RENAME TO bar` ,
233+ ` ALTER TABLE temp.foo RENAME TO bar` ,
234+ ` ALTER TABLE foo RENAME COLUMN col1 TO col2` ,
235+ ` ALTER TABLE foo RENAME col1 TO col2` ,
236+ ` ALTER TABLE foo DROP COLUMN col1` ,
237+ ` ALTER TABLE foo ADD COLUMN col1 TEXT DEFAULT 'Hello' COLLATE NOCASE` ,
159238 }
160239
161240 for _ , tt := range tests {
0 commit comments