Mercurial > pub > bltoolkit

comparison Data/Create Scripts/Access.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 DROP Procedure Person_SelectByKey
2 GO
3 DROP Procedure Person_SelectAll
4 GO
5 DROP Procedure Person_SelectByName
6 GO
7 DROP Procedure Person_SelectListByName
8 GO
9 DROP Procedure Person_Insert
10 GO
11 DROP Procedure Person_Update
12 GO
13 DROP Procedure Person_Delete
14 GO
15 DROP Procedure Patient_SelectAll
16 GO
17 DROP Procedure Patient_SelectByName
18 GO
19 DROP Procedure Scalar_DataReader
20 GO
21 DROP TABLE Dual
22 GO
23 DROP TABLE BinaryData
24 GO
25 DROP TABLE DataTypeTest
26 GO
27 DROP TABLE Doctor
28 GO
29 DROP TABLE Patient
30 GO
31 DROP TABLE Person
32 GO
33
34 CREATE TABLE Person (
35 PersonID Int IDENTITY,
36 FirstName Text(50) NOT NULL,
37 LastName Text(50) NOT NULL,
38 MiddleName Text(50),
39 Gender Text(1) NOT NULL,
40
41 CONSTRAINT PK_Peson PRIMARY KEY (PersonID)
42 )
43 GO
44
45 CREATE TABLE Doctor (
46 PersonID Int NOT NULL,
47 Taxonomy Text(50) NOT NULL,
48
49 CONSTRAINT OK_Doctor PRIMARY KEY (PersonID)
50 )
51 GO
52
53 CREATE TABLE Patient (
54 PersonID Int NOT NULL,
55 Diagnosis Text(255) NOT NULL,
56
57 CONSTRAINT PK_Patient PRIMARY KEY (PersonID)
58 )
59 GO
60
61 ALTER TABLE Doctor
62 ADD CONSTRAINT PersonDoctor FOREIGN KEY (PersonID) REFERENCES Person ON UPDATE CASCADE ON DELETE CASCADE;
63 GO
64
65 ALTER TABLE Patient
66 ADD CONSTRAINT PersonPatient FOREIGN KEY (PersonID) REFERENCES Person ON UPDATE CASCADE ON DELETE CASCADE;
67 GO
68
69 CREATE TABLE BinaryData (
70 BinaryDataID AutoIncrement,
71 Data Image NOT NULL,
72
73 CONSTRAINT PrimaryKey PRIMARY KEY (BinaryDataID)
74 );
75 GO
76
77 CREATE TABLE DataTypeTest (
78 DataTypeID AutoIncrement,
79 Binary_ Image,
80 Boolean_ Long,
81 Byte_ Byte DEFAULT 0,
82 Bytes_ Image,
83 Char_ Text(1),
84 DateTime_ DateTime,
85 Decimal_ Currency DEFAULT 0,
86 Double_ Double DEFAULT 0,
87 Guid_ Uniqueidentifier,
88 Int16_ SmallInt DEFAULT 0,
89 Int32_ Long DEFAULT 0,
90 Int64_ Long DEFAULT 0,
91 Money_ Currency DEFAULT 0,
92 SByte_ Byte DEFAULT 0,
93 Single_ Single DEFAULT 0,
94 Stream_ Image,
95 String_ Text(50) WITH COMP,
96 UInt16_ SmallInt DEFAULT 0,
97 UInt32_ Long DEFAULT 0,
98 UInt64_ Long DEFAULT 0,
99 Xml_ Text WITH COMP,
100
101 CONSTRAINT PrimaryKey PRIMARY KEY (DataTypeID)
102 );
103 GO
104
105 CREATE TABLE Dual (Dummy Text(10));
106 GO
107
108 INSERT INTO Person (FirstName, LastName, Gender) VALUES ("John", "Pupkin", "M")
109 GO
110 INSERT INTO Person (FirstName, LastName, Gender) VALUES ("Tester", "Testerson", "M")
111 GO
112 INSERT INTO Doctor (PersonID, Taxonomy) VALUES (1, "Psychiatry")
113 GO
114 INSERT INTO Patient (PersonID, Diagnosis) VALUES (2, "Hallucination with Paranoid Bugs' Delirium of Persecution")
115 GO
116
117 INSERT INTO DataTypeTest
118 (Binary_, Boolean_, Byte_, Bytes_, Char_, DateTime_, Decimal_,
119 Double_, Guid_, Int16_, Int32_, Int64_, Money_, SByte_,
120 Single_, Stream_, String_, UInt16_, UInt32_, UInt64_, Xml_)
121 VALUES
122 ( NULL, NULL, NULL, NULL, NULL, NULL, NULL,
123 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
124 NULL, NULL, NULL, NULL, NULL, NULL, NULL)
125 GO
126
127 INSERT INTO DataTypeTest
128 (Binary_, Boolean_, Byte_, Bytes_, Char_, DateTime_, Decimal_,
129 Double_, Guid_, Int16_, Int32_, Int64_, Money_, SByte_,
130 Single_, Stream_, String_, UInt16_, UInt32_, UInt64_,
131 Xml_)
132 VALUES
133 (1, True, 255, 1, "B", Now(), 12345.67,
134 1234.567, 1, 32767, 32768, 1000000, 12.3456, 127,
135 1234.123, "12345678", "string", 32767, 32768, 2000000000,
136 "<root><element strattr='strvalue' intattr='12345'/></root>")
137 GO
138
139 INSERT INTO Dual (Dummy) VALUES ('X')
140 GO
141
142 CREATE Procedure Person_SelectByKey(
143 [@id] Long)
144 AS
145 SELECT * FROM Person WHERE PersonID = [@id];
146 GO
147
148 CREATE Procedure Person_SelectAll
149 AS
150 SELECT * FROM Person;
151 GO
152
153 CREATE Procedure Person_SelectByName(
154 [@firstName] Text(50),
155 [@lastName] Text(50))
156 AS
157 SELECT
158 *
159 FROM
160 Person
161 WHERE
162 FirstName = [@firstName] AND LastName = [@lastName];
163 GO
164
165 CREATE Procedure Person_SelectListByName(
166 [@firstName] Text(50),
167 [@lastName] Text(50))
168 AS
169 SELECT
170 *
171 FROM
172 Person
173 WHERE
174 FirstName like [@firstName] AND LastName like [@lastName];
175 GO
176
177 CREATE Procedure Person_Insert(
178 [@FirstName] Text(50),
179 [@MiddleName] Text(50),
180 [@LastName] Text(50),
181 [@Gender] Text(1))
182 AS
183 INSERT INTO Person
184 (FirstName, MiddleName, LastName, Gender)
185 VALUES
186 ([@FirstName], [@MiddleName], [@LastName], [@Gender]);
187 GO
188
189 CREATE Procedure Person_Update(
190 [@id] Long,
191 [@PersonID] Long,
192 [@FirstName] Text(50),
193 [@MiddleName] Text(50),
194 [@LastName] Text(50),
195 [@Gender] Text(1))
196 AS
197 UPDATE
198 Person
199 SET
200 LastName = [@LastName],
201 FirstName = [@FirstName],
202 MiddleName = [@MiddleName],
203 Gender = [@Gender]
204 WHERE
205 PersonID = [@id];
206 GO
207
208 CREATE Procedure Person_Delete(
209 [@PersonID] Long)
210 AS
211 DELETE FROM Person WHERE PersonID = [@PersonID];
212 GO
213
214 CREATE Procedure Patient_SelectAll
215 AS
216 SELECT
217 Person.*, Patient.Diagnosis
218 FROM
219 Patient, Person
220 WHERE
221 Patient.PersonID = Person.PersonID;
222 GO
223
224 CREATE Procedure Patient_SelectByName(
225 [@firstName] Text(50),
226 [@lastName] Text(50))
227 AS
228 SELECT
229 Person.*, Patient.Diagnosis
230 FROM
231 Patient, Person
232 WHERE
233 Patient.PersonID = Person.PersonID
234 AND FirstName = [@firstName] AND LastName = [@lastName];
235 GO
236
237 CREATE Procedure Scalar_DataReader
238 AS
239 SELECT 12345 AS intField, "54321" AS stringField;
240 GO
241
242
243 DROP TABLE Parent
244 GO
245 DROP TABLE Child
246 GO
247 DROP TABLE GrandChild
248 GO
249
250 CREATE TABLE Parent (ParentID int, Value1 int NULL)
251 GO
252 CREATE TABLE Child (ParentID int, ChildID int)
253 GO
254 CREATE TABLE GrandChild (ParentID int, ChildID int, GrandChildID int)
255 GO
256 DROP TABLE LinqDataTypes
257 GO
258
259 CREATE TABLE LinqDataTypes
260 (
261 ID int,
262 MoneyValue decimal(10,4),
263 DateTimeValue datetime,
264 DateTimeValue2 datetime,
265 BoolValue bit,
266 GuidValue uniqueidentifier,
267 BinaryValue OleObject NULL,
268 SmallIntValue smallint,
269 IntValue int NULL,
270 BigIntValue long NULL
271 )
272 GO
273
274 DROP TABLE TestIdentity
275 GO
276
277 CREATE TABLE TestIdentity (
278 ID Int IDENTITY,
279 CONSTRAINT PK_TestIdentity PRIMARY KEY (ID)
280 )
281 GO