On 2016/03/02 9:51 AM, Marco Bambini wrote:
> Hi all,
> I developed a CREATE TABLE parser in C that is able to extract every details
> about an sqlite table (table and columns constraints, foreign keys, flags and
> so on).
> So far it seems to work pretty well but I would like to stress test it before
> releasing it as open source on GitHub.
>
> Anyone can send me or help me find out some CREATE TABLE sql statements to
> add to my internal tests?
> Thanks.
Hi Marco,
I used the following when developing the table parser for SQLitespeed
which included most of the headaches and does execute correctly in
SQLite as shown below. You should add a "USING" clause to test a virtual
tables too (which I didn't add here since not sure which VT's your
implementation might support)
-- Processing SQL in: E:\Documents\SQLiteAutoScript.sql
-- SQLite version 3.9.2 [ Release: 2015-11-02 ] on SQLitespeed
version 2.0.2.4.
-- Script Items: 3 Parameter Count: 0
-- 2016-03-02 10:47:04.200 | [Info] Script Initialized,
Started executing...
--
================================================================================================
CREATE TABLE IF NOT EXISTS main.[t1] ( /* Parser Test Table */
"ID" TEXT CHECK (ID<>'') NOT NULL,
`V2` VARCHAR(64) DEFAULT "New,[`'Test",
[V,3] NUMERIC NOT NULL DEFAULT (CURRENT_TIME),
V4 DATETIME DEFAULT 5 NOT NULL,
"v/*5" TEXT COLATE NOCASE NULL,-- Note misspelling of COLLATE
CONSTRAINT `aPK` PRIMARY KEY ([ID],"V2"),
UNIQUE (`V,3`,[v/*5]) ON CONFLICT ROLLBACK
) /* NoRowID */ WITHOUT ROWID /* End of Parser test */
;
CREATE TEMPORARY TABLE [t2] ( /* Parser Test Table */
"ID" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ON CONFLICT FAIL,
`t2.V2` VARCHAR DEFAULT "New,]`'Test",
[V~/*%,^"@`(3)] NUMERIC NOT NULL DEFAULT (datetime('now',/* Def to
local */ 'localtime')),
V4 DATETIME DEFAULT '2016-01-02 00:00:00' COLLATE BINARY,
"v/*5" TEXT COLLATE NOCASE NULL,
CONSTRAINT `aKEY` FOREIGN KEY ([ID]) REFERENCES t1(ID) ON DELETE SET
NULL DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT [uID] UNIQUE ([V~/*%,^"@`(3)],[v/*5]) ON CONFLICT ABORT
)/* End of Parser test */
;
CREATE TEMP TABLE t3 AS SELECT * FROM `main`.[t1] WHERE "v/*5" IS NOT NULL;
-- Script Stats: Total Script Execution Time: 0d 00h 00m and
00.015s
-- Total Script Query Time: -- --- --- ---
--.----
-- Total Database Rows Changed: 0
-- Total Virtual-Machine Steps: 158
-- Last executed Item Index: 3
-- Last Script Error:
--
------------------------------------------------------------------------------------------------
-- 2016-03-02 10:47:04.200 | [Success] Script Success.
-- 2016-03-02 10:47:04.200 | [Success] Transaction Rolled back.
-- ------- DB-Engine Logs (Contains logged information from all DB
connections during run) ------
-- [2016-03-02 10:47:04.169] APPLICATION : Script
E:\Documents\SQLiteAutoScript.sql started at 10:47:04.169 on 02 March.
--
================================================================================================
