Mercurial > pub > bltoolkit

comparison Data/Create Scripts/SQLite.sql @ 0:f990fcb411a9

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Копия текущей версии из github
author cin
date Thu, 27 Mar 2014 21:46:09 +0400
parents
children
comparison
equal deleted inserted replaced
-1:000000000000 0:f990fcb411a9
1 --
2 -- Helper table
3 --
4 DROP TABLE IF EXISTS Dual;
5 CREATE TABLE Dual (Dummy VARCHAR(10));
6 INSERT INTO Dual (Dummy) VALUES ('X');
7
8 --
9 -- Person Table
10 --
11 DROP TABLE IF EXISTS Person;
12 CREATE TABLE Person
13 (
14 PersonID integer NOT NULL CONSTRAINT PK_Person PRIMARY KEY AUTOINCREMENT,
15 FirstName nvarchar(50) NOT NULL,
16 LastName nvarchar(50) NOT NULL,
17 MiddleName nvarchar(50) NULL,
18 Gender char(1) NOT NULL CONSTRAINT CK_Person_Gender CHECK (Gender in ('M', 'F', 'U', 'O'))
19 );
20
21 INSERT INTO Person (FirstName, LastName, Gender) VALUES ('John', 'Pupkin', 'M');
22 INSERT INTO Person (FirstName, LastName, Gender) VALUES ('Tester', 'Testerson', 'M');
23
24 --
25 -- Doctor Table Extension
26 --
27 DROP TABLE IF EXISTS Doctor;
28 CREATE TABLE Doctor
29 (
30 PersonID integer NOT NULL CONSTRAINT PK_Doctor PRIMARY KEY,
31 Taxonomy nvarchar(50) NOT NULL
32 );
33
34 INSERT INTO Doctor (PersonID, Taxonomy) VALUES (1, 'Psychiatry');
35
36 --
37 -- Patient Table Extension
38 --
39 DROP TABLE IF EXISTS Patient;
40 CREATE TABLE Patient
41 (
42 PersonID integer NOT NULL CONSTRAINT PK_Patient PRIMARY KEY,
43 Diagnosis nvarchar(256) NOT NULL
44 );
45 INSERT INTO Patient (PersonID, Diagnosis) VALUES (2, 'Hallucination with Paranoid Bugs'' Delirium of Persecution');
46
47 --
48 -- BinaryData Table
49 --
50 DROP TABLE IF EXISTS BinaryData;
51 CREATE TABLE BinaryData
52 (
53 BinaryDataID integer NOT NULL CONSTRAINT PK_BinaryData PRIMARY KEY AUTOINCREMENT,
54 Stamp timestamp NOT NULL,
55 Data blob(1024) NOT NULL
56 );
57
58 --
59 -- Babylon test
60 --
61 DROP TABLE IF EXISTS DataTypeTest;
62 CREATE TABLE DataTypeTest
63 (
64 DataTypeID integer NOT NULL CONSTRAINT PK_DataType PRIMARY KEY AUTOINCREMENT,
65 Binary_ binary(50) NULL,
66 Boolean_ bit NULL,
67 Byte_ tinyint NULL,
68 Bytes_ varbinary(50) NULL,
69 Char_ char(1) NULL,
70 DateTime_ datetime NULL,
71 Decimal_ decimal(20,2) NULL,
72 Double_ float NULL,
73 Guid_ uniqueidentifier NULL,
74 Int16_ smallint NULL,
75 Int32_ int NULL,
76 Int64_ bigint NULL,
77 Money_ money NULL,
78 SByte_ tinyint NULL,
79 Single_ real NULL,
80 Stream_ varbinary(50) NULL,
81 String_ nvarchar(50) NULL,
82 UInt16_ smallint NULL,
83 UInt32_ int NULL,
84 UInt64_ bigint NULL,
85 Xml_ text NULL
86 );
87
88 INSERT INTO DataTypeTest
89 (Binary_, Boolean_, Byte_, Bytes_, Char_, DateTime_, Decimal_,
90 Double_, Guid_, Int16_, Int32_, Int64_, Money_, SByte_,
91 Single_, Stream_, String_, UInt16_, UInt32_, UInt64_, Xml_)
92 VALUES
93 ( NULL, NULL, NULL, NULL, NULL, NULL, NULL,
94 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
95 NULL, NULL, NULL, NULL, NULL, NULL, NULL);
96
97 INSERT INTO DataTypeTest
98 (Binary_, Boolean_, Byte_, Bytes_, Char_, DateTime_, Decimal_,
99 Double_, Guid_, Int16_, Int32_, Int64_, Money_, SByte_,
100 Single_, Stream_, String_, UInt16_, UInt32_, UInt64_,
101 Xml_)
102 VALUES
103 (randomblob(16), 1, 255, zeroblob(16), 'B', DATETIME('NOW'), 12345.67,
104 1234.567, '{64e145a3-0077-4335-b2c6-ea19c9f464f8}', 32767, 32768, 1000000, 12.3456, 127,
105 1234.123, randomblob(64), 'string', 32767, 32768, 200000000,
106 '<root><element strattr="strvalue" intattr="12345"/></root>');
107
108
109 DROP TABLE IF EXISTS Parent;
110 DROP TABLE IF EXISTS Child;
111 DROP TABLE IF EXISTS GrandChild;
112
113 CREATE TABLE Parent (ParentID int, Value1 int);
114 CREATE TABLE Child (ParentID int, ChildID int);
115 CREATE TABLE GrandChild (ParentID int, ChildID int, GrandChildID int);
116
117 DROP TABLE IF EXISTS LinqDataTypes;
118 CREATE TABLE LinqDataTypes
119 (
120 ID int,
121 MoneyValue decimal(10,4),
122 DateTimeValue datetime,
123 DateTimeValue2 datetime2,
124 BoolValue boolean,
125 GuidValue uniqueidentifier,
126 BinaryValue binary(5000) NULL,
127 SmallIntValue smallint,
128 IntValue int NULL,
129 BigIntValue bigint NULL
130 );
131
132 DROP TABLE TestIdentity
133 GO
134
135 CREATE TABLE TestIdentity (
136 ID integer NOT NULL CONSTRAINT PK_TestIdentity PRIMARY KEY AUTOINCREMENT
137 )
138 GO