Mercurial > pub > bltoolkit

comparison Data/Create Scripts/Firebird2.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; 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;