|
0
|
1 DROP PROCEDURE Person_SelectByKey; COMMIT;
|
|
|
2 DROP PROCEDURE Person_SelectAll; COMMIT;
|
|
|
3 DROP PROCEDURE Person_SelectByName; COMMIT;
|
|
|
4 DROP PROCEDURE Person_Insert; COMMIT;
|
|
|
5 DROP PROCEDURE Person_Insert_OutputParameter; COMMIT;
|
|
|
6 DROP PROCEDURE Person_Update; COMMIT;
|
|
|
7 DROP PROCEDURE Person_Delete; COMMIT;
|
|
|
8 DROP PROCEDURE Patient_SelectAll; COMMIT;
|
|
|
9 DROP PROCEDURE Patient_SelectByName; COMMIT;
|
|
|
10 DROP PROCEDURE OutRefTest; COMMIT;
|
|
|
11 DROP PROCEDURE OutRefEnumTest; COMMIT;
|
|
|
12 DROP PROCEDURE Scalar_DataReader; COMMIT;
|
|
|
13 DROP PROCEDURE Scalar_OutputParameter; COMMIT;
|
|
|
14 DROP PROCEDURE Scalar_ReturnParameter; COMMIT;
|
|
|
15
|
|
|
16 DROP TRIGGER CREATE_BinaryDataID; COMMIT;
|
|
|
17 DROP TRIGGER CHANGE_BinaryData; COMMIT;
|
|
|
18 DROP TRIGGER CREATE_PersonID; COMMIT;
|
|
|
19 DROP TRIGGER CREATE_DataTypeTest; COMMIT;
|
|
|
20
|
|
|
21 DROP GENERATOR DataTypeID; COMMIT;
|
|
|
22 DROP GENERATOR PersonID; COMMIT;
|
|
|
23 DROP GENERATOR TimestampGen; COMMIT;
|
|
|
24
|
|
|
25 DROP VIEW PersonView; COMMIT;
|
|
|
26
|
|
|
27 DROP TABLE Dual; COMMIT;
|
|
|
28 DROP TABLE DataTypeTest; COMMIT;
|
|
|
29 DROP TABLE BinaryData; COMMIT;
|
|
|
30 DROP TABLE Doctor; COMMIT;
|
|
|
31 DROP TABLE Patient; COMMIT;
|
|
|
32 DROP TABLE Person; COMMIT;
|
|
|
33
|
|
|
34 DROP EXTERNAL FUNCTION rtrim; COMMIT;
|
|
|
35 DROP EXTERNAL FUNCTION ltrim; COMMIT;
|
|
|
36
|
|
|
37
|
|
|
38 DECLARE EXTERNAL FUNCTION ltrim
|
|
|
39 CSTRING(255) NULL
|
|
|
40 RETURNS CSTRING(255) FREE_IT
|
|
|
41 ENTRY_POINT 'IB_UDF_ltrim' MODULE_NAME 'ib_udf';
|
|
|
42 COMMIT;
|
|
|
43
|
|
|
44 DECLARE EXTERNAL FUNCTION rtrim
|
|
|
45 CSTRING(255) NULL
|
|
|
46 RETURNS CSTRING(255) FREE_IT
|
|
|
47 ENTRY_POINT 'IB_UDF_rtrim' MODULE_NAME 'ib_udf';
|
|
|
48 COMMIT;
|
|
|
49
|
|
|
50
|
|
|
51 /*
|
|
|
52 Dual table FOR supporting queryies LIKE:
|
|
|
53 SELECT 1 AS id => SELECT 1 AS "id" *FROM Dual*
|
|
|
54 */
|
|
|
55 CREATE TABLE Dual (Dummy VARCHAR(10));
|
|
|
56 COMMIT;
|
|
|
57 INSERT INTO Dual (Dummy) VALUES ('X');
|
|
|
58 COMMIT;
|
|
|
59
|
|
|
60 -- Person Table
|
|
|
61
|
|
|
62 CREATE TABLE Person
|
|
|
63 (
|
|
|
64 PersonID INTEGER NOT NULL PRIMARY KEY,
|
|
|
65 FirstName VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
|
|
|
66 LastName VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
|
|
|
67 MiddleName VARCHAR(50),
|
|
|
68 Gender CHAR(1) NOT NULL CHECK (Gender in ('M', 'F', 'U', 'O'))
|
|
|
69 );
|
|
|
70 COMMIT;
|
|
|
71
|
|
|
72 CREATE GENERATOR PersonID;
|
|
|
73 COMMIT;
|
|
|
74
|
|
|
75 CREATE GENERATOR TimestampGen;
|
|
|
76 COMMIT;
|
|
|
77
|
|
|
78 CREATE TRIGGER CREATE_PersonID FOR Person
|
|
|
79 BEFORE INSERT POSITION 0
|
|
|
80 AS BEGIN
|
|
|
81 NEW.PersonID = GEN_ID(PersonID, 1);
|
|
|
82 END
|
|
|
83 COMMIT;
|
|
|
84
|
|
|
85 INSERT INTO Person (FirstName, LastName, Gender) VALUES ('John', 'Pupkin', 'M');
|
|
|
86 COMMIT;
|
|
|
87 INSERT INTO Person (FirstName, LastName, Gender) VALUES ('Tester', 'Testerson', 'M');
|
|
|
88 COMMIT;
|
|
|
89
|
|
|
90 -- Doctor Table Extension
|
|
|
91
|
|
|
92 CREATE TABLE Doctor
|
|
|
93 (
|
|
|
94 PersonID INTEGER NOT NULL,
|
|
|
95 Taxonomy VARCHAR(50) NOT NULL,
|
|
|
96 FOREIGN KEY (PersonID) REFERENCES Person (PersonID)
|
|
|
97 ON DELETE CASCADE
|
|
|
98 )
|
|
|
99 COMMIT;
|
|
|
100
|
|
|
101 INSERT INTO Doctor (PersonID, Taxonomy) VALUES (1, 'Psychiatry');
|
|
|
102 COMMIT;
|
|
|
103
|
|
|
104 -- Patient Table Extension
|
|
|
105
|
|
|
106 CREATE TABLE Patient
|
|
|
107 (
|
|
|
108 PersonID int NOT NULL,
|
|
|
109 Diagnosis VARCHAR(256) NOT NULL,
|
|
|
110 FOREIGN KEY (PersonID) REFERENCES Person (PersonID)
|
|
|
111 ON DELETE CASCADE
|
|
|
112 );
|
|
|
113 COMMIT;
|
|
|
114
|
|
|
115 INSERT INTO Patient (PersonID, Diagnosis) VALUES (2, 'Hallucination with Paranoid Bugs'' Delirium of Persecution');
|
|
|
116 COMMIT;
|
|
|
117
|
|
|
118 -- Person_SelectByKey
|
|
|
119
|
|
|
120 CREATE PROCEDURE Person_SelectByKey(id INTEGER)
|
|
|
121 RETURNS (
|
|
|
122 PersonID INTEGER,
|
|
|
123 FirstName VARCHAR(50),
|
|
|
124 LastName VARCHAR(50),
|
|
|
125 MiddleName VARCHAR(50),
|
|
|
126 Gender CHAR(1)
|
|
|
127 )
|
|
|
128 AS
|
|
|
129 BEGIN
|
|
|
130 SELECT PersonID, FirstName, LastName, MiddleName, Gender FROM Person
|
|
|
131 WHERE PersonID = :id
|
|
|
132 INTO
|
|
|
133 :PersonID,
|
|
|
134 :FirstName,
|
|
|
135 :LastName,
|
|
|
136 :MiddleName,
|
|
|
137 :Gender;
|
|
|
138 SUSPEND;
|
|
|
139 END
|
|
|
140 COMMIT;
|
|
|
141
|
|
|
142 -- Person_SelectAll
|
|
|
143
|
|
|
144 CREATE PROCEDURE Person_SelectAll
|
|
|
145 RETURNS (
|
|
|
146 PersonID INTEGER,
|
|
|
147 FirstName VARCHAR(50),
|
|
|
148 LastName VARCHAR(50),
|
|
|
149 MiddleName VARCHAR(50),
|
|
|
150 Gender CHAR(1)
|
|
|
151 )
|
|
|
152 AS
|
|
|
153 BEGIN
|
|
|
154 FOR
|
|
|
155 SELECT PersonID, FirstName, LastName, MiddleName, Gender FROM Person
|
|
|
156 INTO
|
|
|
157 :PersonID,
|
|
|
158 :FirstName,
|
|
|
159 :LastName,
|
|
|
160 :MiddleName,
|
|
|
161 :Gender
|
|
|
162 DO SUSPEND;
|
|
|
163 END
|
|
|
164 COMMIT;
|
|
|
165
|
|
|
166 -- Person_SelectByName
|
|
|
167
|
|
|
168 CREATE PROCEDURE Person_SelectByName (
|
|
|
169 in_FirstName VARCHAR(50),
|
|
|
170 in_LastName VARCHAR(50)
|
|
|
171 )
|
|
|
172 RETURNS (
|
|
|
173 PersonID int,
|
|
|
174 FirstName VARCHAR(50),
|
|
|
175 LastName VARCHAR(50),
|
|
|
176 MiddleName VARCHAR(50),
|
|
|
177 Gender CHAR(1)
|
|
|
178 )
|
|
|
179 AS
|
|
|
180 BEGIN
|
|
|
181
|
|
|
182 FOR SELECT PersonID, FirstName, LastName, MiddleName, Gender FROM Person
|
|
|
183 WHERE FirstName LIKE :in_FirstName and LastName LIKE :in_LastName
|
|
|
184 INTO
|
|
|
185 :PersonID,
|
|
|
186 :FirstName,
|
|
|
187 :LastName,
|
|
|
188 :MiddleName,
|
|
|
189 :Gender
|
|
|
190 DO SUSPEND;
|
|
|
191 END
|
|
|
192 COMMIT;
|
|
|
193
|
|
|
194 -- Person_Insert
|
|
|
195
|
|
|
196 CREATE PROCEDURE Person_Insert(
|
|
|
197 FirstName VARCHAR(50),
|
|
|
198 LastName VARCHAR(50),
|
|
|
199 MiddleName VARCHAR(50),
|
|
|
200 Gender CHAR(1)
|
|
|
201 )
|
|
|
202 RETURNS (PersonID INTEGER)
|
|
|
203 AS
|
|
|
204 BEGIN
|
|
|
205 INSERT INTO Person
|
|
|
206 ( LastName, FirstName, MiddleName, Gender)
|
|
|
207 VALUES
|
|
|
208 (:LastName, :FirstName, :MiddleName, :Gender);
|
|
|
209
|
|
|
210 SELECT MAX(PersonID) FROM person
|
|
|
211 INTO :PersonID;
|
|
|
212 SUSPEND;
|
|
|
213 END
|
|
|
214 COMMIT;
|
|
|
215
|
|
|
216 -- Person_Insert_OutputParameter
|
|
|
217
|
|
|
218 CREATE PROCEDURE Person_Insert_OutputParameter(
|
|
|
219 FirstName VARCHAR(50),
|
|
|
220 LastName VARCHAR(50),
|
|
|
221 MiddleName VARCHAR(50),
|
|
|
222 Gender CHAR(1)
|
|
|
223 )
|
|
|
224 RETURNS (PersonID INTEGER)
|
|
|
225 AS
|
|
|
226 BEGIN
|
|
|
227 INSERT INTO Person
|
|
|
228 ( LastName, FirstName, MiddleName, Gender)
|
|
|
229 VALUES
|
|
|
230 (:LastName, :FirstName, :MiddleName, :Gender);
|
|
|
231
|
|
|
232 SELECT max(PersonID) FROM person
|
|
|
233 INTO :PersonID;
|
|
|
234 SUSPEND;
|
|
|
235 END
|
|
|
236 COMMIT;
|
|
|
237
|
|
|
238 -- Person_Update
|
|
|
239
|
|
|
240 CREATE PROCEDURE Person_Update(
|
|
|
241 PersonID INTEGER,
|
|
|
242 FirstName VARCHAR(50),
|
|
|
243 LastName VARCHAR(50),
|
|
|
244 MiddleName VARCHAR(50),
|
|
|
245 Gender CHAR(1)
|
|
|
246 )
|
|
|
247 AS
|
|
|
248 BEGIN
|
|
|
249 UPDATE
|
|
|
250 Person
|
|
|
251 SET
|
|
|
252 LastName = :LastName,
|
|
|
253 FirstName = :FirstName,
|
|
|
254 MiddleName = :MiddleName,
|
|
|
255 Gender = :Gender
|
|
|
256 WHERE
|
|
|
257 PersonID = :PersonID;
|
|
|
258 END
|
|
|
259 COMMIT;
|
|
|
260
|
|
|
261 -- Person_Delete
|
|
|
262
|
|
|
263 CREATE PROCEDURE Person_Delete(
|
|
|
264 PersonID INTEGER
|
|
|
265 )
|
|
|
266 AS
|
|
|
267 BEGIN
|
|
|
268 DELETE FROM Person WHERE PersonID = :PersonID;
|
|
|
269 END
|
|
|
270 COMMIT;
|
|
|
271
|
|
|
272 -- Patient_SelectAll
|
|
|
273
|
|
|
274 CREATE PROCEDURE Patient_SelectAll
|
|
|
275 RETURNS (
|
|
|
276 PersonID int,
|
|
|
277 FirstName VARCHAR(50),
|
|
|
278 LastName VARCHAR(50),
|
|
|
279 MiddleName VARCHAR(50),
|
|
|
280 Gender CHAR(1),
|
|
|
281 Diagnosis VARCHAR(256)
|
|
|
282 )
|
|
|
283 AS
|
|
|
284 BEGIN
|
|
|
285 FOR
|
|
|
286 SELECT
|
|
|
287 Person.PersonID,
|
|
|
288 FirstName,
|
|
|
289 LastName,
|
|
|
290 MiddleName,
|
|
|
291 Gender,
|
|
|
292 Patient.Diagnosis
|
|
|
293 FROM
|
|
|
294 Patient, Person
|
|
|
295 WHERE
|
|
|
296 Patient.PersonID = Person.PersonID
|
|
|
297 INTO
|
|
|
298 :PersonID,
|
|
|
299 :FirstName,
|
|
|
300 :LastName,
|
|
|
301 :MiddleName,
|
|
|
302 :Gender,
|
|
|
303 :Diagnosis
|
|
|
304 DO SUSPEND;
|
|
|
305 END
|
|
|
306 COMMIT;
|
|
|
307
|
|
|
308 -- Patient_SelectByName
|
|
|
309
|
|
|
310 CREATE PROCEDURE Patient_SelectByName(
|
|
|
311 FirstName VARCHAR(50),
|
|
|
312 LastName VARCHAR(50)
|
|
|
313 )
|
|
|
314 RETURNS (
|
|
|
315 PersonID int,
|
|
|
316 MiddleName VARCHAR(50),
|
|
|
317 Gender CHAR(1),
|
|
|
318 Diagnosis VARCHAR(256)
|
|
|
319 )
|
|
|
320 AS
|
|
|
321 BEGIN
|
|
|
322 FOR
|
|
|
323 SELECT
|
|
|
324 Person.PersonID,
|
|
|
325 MiddleName,
|
|
|
326 Gender,
|
|
|
327 Patient.Diagnosis
|
|
|
328 FROM
|
|
|
329 Patient, Person
|
|
|
330 WHERE
|
|
|
331 Patient.PersonID = Person.PersonID
|
|
|
332 and FirstName = :FirstName and LastName = :LastName
|
|
|
333 INTO
|
|
|
334 :PersonID,
|
|
|
335 :MiddleName,
|
|
|
336 :Gender,
|
|
|
337 :Diagnosis
|
|
|
338 DO SUSPEND;
|
|
|
339 END
|
|
|
340 COMMIT;
|
|
|
341
|
|
|
342 -- BinaryData Table
|
|
|
343
|
|
|
344 CREATE TABLE BinaryData
|
|
|
345 (
|
|
|
346 BinaryDataID INTEGER NOT NULL PRIMARY KEY,
|
|
|
347 Stamp INTEGER NOT NULL,
|
|
|
348 Data BLOB NOT NULL
|
|
|
349 );
|
|
|
350 COMMIT;
|
|
|
351
|
|
|
352 CREATE TRIGGER CREATE_BinaryDataID FOR BinaryData
|
|
|
353 BEFORE INSERT POSITION 0
|
|
|
354 AS BEGIN
|
|
|
355 NEW.BinaryDataID = GEN_ID(PersonID, 1);
|
|
|
356 NEW.Stamp = GEN_ID(TimestampGen, 1);
|
|
|
357 END
|
|
|
358 COMMIT;
|
|
|
359
|
|
|
360 CREATE TRIGGER CHANGE_BinaryData FOR BinaryData
|
|
|
361 beFORe update
|
|
|
362 AS BEGIN
|
|
|
363 NEW.Stamp = GEN_ID(TimestampGen, 1);
|
|
|
364 END
|
|
|
365 COMMIT;
|
|
|
366
|
|
|
367 -- OutRefTest
|
|
|
368
|
|
|
369 /*
|
|
|
370 Fake input parameters are used to "emulate" input/output parameters.
|
|
|
371 Each inout parameter should be defined in RETURNS(...) section
|
|
|
372 and allso have a "mirror" in input section, mirror name shoul be:
|
|
|
373 FdpDataProvider.InOutInputParameterPrefix + [parameter name]
|
|
|
374 ex:
|
|
|
375 in_inputOutputID is input mirror FOR inout parameter inputOutputID
|
|
|
376 */
|
|
|
377 CREATE PROCEDURE OutRefTest(
|
|
|
378 ID INTEGER,
|
|
|
379 in_inputOutputID INTEGER,
|
|
|
380 str VARCHAR(50),
|
|
|
381 in_inputOutputStr VARCHAR(50)
|
|
|
382 )
|
|
|
383 RETURNS(
|
|
|
384 inputOutputID INTEGER,
|
|
|
385 inputOutputStr VARCHAR(50),
|
|
|
386 outputID INTEGER,
|
|
|
387 outputStr VARCHAR(50)
|
|
|
388 )
|
|
|
389 AS
|
|
|
390 BEGIN
|
|
|
391 outputID = ID;
|
|
|
392 inputOutputID = ID + in_inputOutputID;
|
|
|
393 outputStr = str;
|
|
|
394 inputOutputStr = str || in_inputOutputStr;
|
|
|
395 SUSPEND;
|
|
|
396 END
|
|
|
397 COMMIT;
|
|
|
398
|
|
|
399 -- OutRefEnumTest
|
|
|
400
|
|
|
401 CREATE PROCEDURE OutRefEnumTest(
|
|
|
402 str VARCHAR(50),
|
|
|
403 in_inputOutputStr VARCHAR(50)
|
|
|
404 )
|
|
|
405 RETURNS (
|
|
|
406 inputOutputStr VARCHAR(50),
|
|
|
407 outputStr VARCHAR(50)
|
|
|
408 )
|
|
|
409 AS
|
|
|
410 BEGIN
|
|
|
411 outputStr = str;
|
|
|
412 inputOutputStr = str || in_inputOutputStr;
|
|
|
413 SUSPEND;
|
|
|
414 END
|
|
|
415 COMMIT;
|
|
|
416
|
|
|
417 -- ExecuteScalarTest
|
|
|
418
|
|
|
419 CREATE PROCEDURE Scalar_DataReader
|
|
|
420 RETURNS(
|
|
|
421 intField INTEGER,
|
|
|
422 stringField VARCHAR(50)
|
|
|
423 )
|
|
|
424 AS
|
|
|
425 BEGIN
|
|
|
426 intField = 12345;
|
|
|
427 stringField = '54321';
|
|
|
428 SUSPEND;
|
|
|
429 END
|
|
|
430 COMMIT;
|
|
|
431
|
|
|
432 CREATE PROCEDURE Scalar_OutputParameter
|
|
|
433 RETURNS (
|
|
|
434 outputInt INTEGER,
|
|
|
435 outputString VARCHAR(50)
|
|
|
436 )
|
|
|
437 AS
|
|
|
438 BEGIN
|
|
|
439 outputInt = 12345;
|
|
|
440 outputString = '54321';
|
|
|
441 SUSPEND;
|
|
|
442 END
|
|
|
443 COMMIT;
|
|
|
444
|
|
|
445 /*
|
|
|
446 "Return_Value" is the name for ReturnValue "emulating"
|
|
|
447 may be changed: FdpDataProvider.ReturnParameterName
|
|
|
448 */
|
|
|
449 CREATE PROCEDURE Scalar_ReturnParameter
|
|
|
450 RETURNS (Return_Value INTEGER)
|
|
|
451 AS
|
|
|
452 BEGIN
|
|
|
453 Return_Value = 12345;
|
|
|
454 SUSPEND;
|
|
|
455 END
|
|
|
456 COMMIT;
|
|
|
457
|
|
|
458 -- Data Types test
|
|
|
459
|
|
|
460 /*
|
|
|
461 Data definitions according to:
|
|
|
462 http://www.firebirdsql.org/manual/migration-mssql-data-types.html
|
|
|
463
|
|
|
464 BUT! BLOB is ised for BINARY data! not CHAR
|
|
|
465 */
|
|
|
466
|
|
|
467 CREATE TABLE DataTypeTest
|
|
|
468 (
|
|
|
469 DataTypeID INTEGER NOT NULL PRIMARY KEY,
|
|
|
470 Binary_ BLOB,
|
|
|
471 Boolean_ CHAR(1),
|
|
|
472 Byte_ SMALLINT,
|
|
|
473 Bytes_ BLOB,
|
|
|
474 CHAR_ CHAR(1),
|
|
|
475 DateTime_ TIMESTAMP,
|
|
|
476 Decimal_ DECIMAL(10, 2),
|
|
|
477 Double_ DOUBLE PRECISION,
|
|
|
478 Guid_ CHAR(38),
|
|
|
479 Int16_ SMALLINT,
|
|
|
480 Int32_ INTEGER,
|
|
|
481 Int64_ NUMERIC(11),
|
|
|
482 Money_ DECIMAL(18, 4),
|
|
|
483 SByte_ SMALLINT,
|
|
|
484 Single_ FLOAT,
|
|
|
485 Stream_ BLOB,
|
|
|
486 String_ VARCHAR(50) CHARACTER SET UNICODE_FSS,
|
|
|
487 UInt16_ SMALLINT,
|
|
|
488 UInt32_ INTEGER,
|
|
|
489 UInt64_ NUMERIC(11),
|
|
|
490 Xml_ CHAR(1000)
|
|
|
491 )
|
|
|
492 COMMIT;
|
|
|
493
|
|
|
494 CREATE GENERATOR DataTypeID;
|
|
|
495 COMMIT;
|
|
|
496
|
|
|
497 CREATE TRIGGER CREATE_DataTypeTest FOR DataTypeTest
|
|
|
498 BEFORE INSERT POSITION 0
|
|
|
499 AS BEGIN
|
|
|
500 NEW.DataTypeID = GEN_ID(DataTypeID, 1);
|
|
|
501 END
|
|
|
502 COMMIT;
|
|
|
503
|
|
|
504 INSERT INTO DataTypeTest
|
|
|
505 (Binary_, Boolean_, Byte_, Bytes_, CHAR_, DateTime_, Decimal_,
|
|
|
506 Double_, Guid_, Int16_, Int32_, Int64_, Money_, SByte_,
|
|
|
507 Single_, Stream_, String_, UInt16_, UInt32_, UInt64_, Xml_)
|
|
|
508 VALUES
|
|
|
509 ( NULL, NULL, NULL, NULL, NULL, NULL, NULL,
|
|
|
510 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
|
|
|
511 NULL, NULL, NULL, NULL, NULL, NULL, NULL);
|
|
|
512 COMMIT;
|
|
|
513
|
|
|
514 INSERT INTO DataTypeTest
|
|
|
515 (Binary_, Boolean_, Byte_, Bytes_, CHAR_, DateTime_, Decimal_,
|
|
|
516 Double_, Guid_, Int16_, Int32_, Int64_, Money_, SByte_,
|
|
|
517 Single_, Stream_, String_, UInt16_, UInt32_, UInt64_,
|
|
|
518 Xml_)
|
|
|
519 VALUES
|
|
|
520 ('dddddddddddddddd', 1, 255,'dddddddddddddddd', 'B', 'NOW', 12345.67,
|
|
|
521 1234.567, 'dddddddddddddddddddddddddddddddd', 32767, 32768, 1000000, 12.3456, 127,
|
|
|
522 1234.123, 'dddddddddddddddd', 'string', 32767, 32768, 200000000,
|
|
|
523 '<root><element strattr="strvalue" intattr="12345"/></root>');
|
|
|
524 COMMIT;
|
|
|
525
|
|
|
526
|
|
|
527
|
|
|
528 DROP TABLE Parent COMMIT;
|
|
|
529 DROP TABLE Child COMMIT;
|
|
|
530 DROP TABLE GrandChild COMMIT;
|
|
|
531
|
|
|
532 CREATE TABLE Parent (ParentID int, Value1 int) COMMIT;
|
|
|
533 CREATE TABLE Child (ParentID int, ChildID int) COMMIT;
|
|
|
534 CREATE TABLE GrandChild (ParentID int, ChildID int, GrandChildID int) COMMIT;
|
|
|
535
|
|
|
536
|
|
|
537 DROP TABLE LinqDataTypes COMMIT;
|
|
|
538
|
|
|
539 CREATE TABLE LinqDataTypes
|
|
|
540 (
|
|
|
541 ID int,
|
|
|
542 MoneyValue decimal(10,4),
|
|
|
543 DateTimeValue timestamp,
|
|
|
544 DateTimeValue2 timestamp,
|
|
|
545 BoolValue char(1),
|
|
|
546 GuidValue char(38),
|
|
|
547 BinaryValue blob,
|
|
|
548 SmallIntValue smallint,
|
|
|
549 IntValue int,
|
|
|
550 BigIntValue bigint
|
|
|
551 )
|
|
|
552 COMMIT;
|
|
|
553
|
|
|
554 DROP GENERATOR SequenceTestSeq
|
|
|
555 COMMIT;
|
|
|
556
|
|
|
557 CREATE GENERATOR SequenceTestSeq
|
|
|
558 COMMIT;
|
|
|
559
|
|
|
560 DROP TABLE SequenceTest COMMIT;
|
|
|
561
|
|
|
562 CREATE TABLE SequenceTest
|
|
|
563 (
|
|
|
564 ID int NOT NULL PRIMARY KEY,
|
|
|
565 Value_ VARCHAR(50) NOT NULL
|
|
|
566 )
|
|
|
567 COMMIT;
|
|
|
568
|
|
|
569
|
|
|
570 DROP TRIGGER CREATE_ID
|
|
|
571 COMMIT;
|
|
|
572
|
|
|
573 DROP GENERATOR TestIdentityID
|
|
|
574 COMMIT;
|
|
|
575
|
|
|
576 DROP TABLE TestIdentity
|
|
|
577 COMMIT;
|
|
|
578
|
|
|
579 CREATE TABLE TestIdentity (
|
|
|
580 ID INTEGER NOT NULL PRIMARY KEY
|
|
|
581 )
|
|
|
582 COMMIT;
|
|
|
583
|
|
|
584 CREATE GENERATOR TestIdentityID;
|
|
|
585 COMMIT;
|
|
|
586
|
|
|
587 CREATE TRIGGER CREATE_ID FOR TestIdentity
|
|
|
588 BEFORE INSERT POSITION 0
|
|
|
589 AS BEGIN
|
|
|
590 NEW.ID = GEN_ID(TestIdentityID, 1);
|
|
|
591 END
|
|
|
592 COMMIT;
|
|
|
593
|
|
|
594
|
|
|
595 CREATE VIEW PersonView
|
|
|
596 AS
|
|
|
597 SELECT * FROM Person
|
|
|
598 COMMIT;
|
