Skip to content

Commit ed7dab3

Browse files
committed
More sqlite-createtable-parser.
1 parent a68b5f4 commit ed7dab3

3 files changed

Lines changed: 103 additions & 23 deletions

File tree

‎sqlite3/tools.sh‎

Lines changed: 6 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -26,14 +26,15 @@ elif [[ "$OSTYPE" == "darwin"* ]]; then
2626
fi
2727
fi
2828

29-
WASI_SDK="https://github.com/WebAssembly/wasi-sdk/releases/download/wasi-sdk-29/wasi-sdk-29.0-$WASI_SDK.tar.gz"
29+
WASI_SDK="https://github.com/WebAssembly/wasi-sdk/releases/download/wasi-sdk-30/wasi-sdk-30.0-$WASI_SDK.tar.gz"
3030
BINARYEN="https://github.com/WebAssembly/binaryen/releases/download/version_125/binaryen-version_125-$BINARYEN.tar.gz"
3131

3232
# Download tools
33+
rm -rf "$ROOT/tools"
3334
mkdir -p "$ROOT/tools"
34-
[ -d "$ROOT/tools/wasi-sdk" ] || curl -#L "$WASI_SDK" | tar xzC "$ROOT/tools" &
35-
[ -d "$ROOT/tools/binaryen" ] || curl -#L "$BINARYEN" | tar xzC "$ROOT/tools" &
35+
curl -#L "$WASI_SDK" | tar xzC "$ROOT/tools" &
36+
curl -#L "$BINARYEN" | tar xzC "$ROOT/tools" &
3637
wait
3738

38-
[ -d "$ROOT/tools/wasi-sdk" ] || mv "$ROOT/tools/wasi-sdk"* "$ROOT/tools/wasi-sdk"
39-
[ -d "$ROOT/tools/binaryen" ] || mv "$ROOT/tools/binaryen"* "$ROOT/tools/binaryen"
39+
mv "$ROOT/tools/wasi-sdk"* "$ROOT/tools/wasi-sdk"
40+
mv "$ROOT/tools/binaryen"* "$ROOT/tools/binaryen"

‎util/sql3util/parse_test.go‎

Lines changed: 97 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -109,23 +109,23 @@ func TestParseTable_foreign(t *testing.T) {
109109

110110
func 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 {
187 Bytes
Binary file not shown.

0 commit comments

Comments
 (0)