Mercurial > pub > bltoolkit
view Data/Create Scripts/Firebird2.sql @ 5:f7d63a092920
Исправлено условие Where в тех случаях, когда репозитарий не является генериком
author | cin |
---|---|
date | Tue, 10 Mar 2015 16:02:11 +0300 |
parents | f990fcb411a9 |
children |
line wrap: on
line source
DROP PROCEDURE Person_SelectByKey; COMMIT; DROP PROCEDURE Person_SelectAll; COMMIT; DROP PROCEDURE Person_SelectByName; COMMIT; DROP PROCEDURE Person_Insert; COMMIT; DROP PROCEDURE Person_Insert_OutputParameter; COMMIT; DROP PROCEDURE Person_Update; COMMIT; DROP PROCEDURE Person_Delete; COMMIT; DROP PROCEDURE Patient_SelectAll; COMMIT; DROP PROCEDURE Patient_SelectByName; COMMIT; DROP PROCEDURE OutRefTest; COMMIT; DROP PROCEDURE OutRefEnumTest; COMMIT; DROP PROCEDURE Scalar_DataReader; COMMIT; DROP PROCEDURE Scalar_OutputParameter; COMMIT; DROP PROCEDURE Scalar_ReturnParameter; COMMIT; DROP TRIGGER CREATE_BinaryDataID; COMMIT; DROP TRIGGER CHANGE_BinaryData; COMMIT; DROP TRIGGER CREATE_PersonID; COMMIT; DROP TRIGGER CREATE_DataTypeTest; COMMIT; DROP GENERATOR DataTypeID; COMMIT; DROP GENERATOR PersonID; COMMIT; DROP GENERATOR TimestampGen; COMMIT; DROP VIEW PersonView; COMMIT; DROP TABLE Dual; COMMIT; DROP TABLE DataTypeTest; COMMIT; DROP TABLE BinaryData; COMMIT; DROP TABLE Doctor; COMMIT; DROP TABLE Patient; COMMIT; DROP TABLE Person; COMMIT; DROP EXTERNAL FUNCTION rtrim; COMMIT; DROP EXTERNAL FUNCTION ltrim; COMMIT; DECLARE EXTERNAL FUNCTION ltrim CSTRING(255) NULL RETURNS CSTRING(255) FREE_IT ENTRY_POINT 'IB_UDF_ltrim' MODULE_NAME 'ib_udf'; COMMIT; DECLARE EXTERNAL FUNCTION rtrim CSTRING(255) NULL RETURNS CSTRING(255) FREE_IT ENTRY_POINT 'IB_UDF_rtrim' MODULE_NAME 'ib_udf'; COMMIT; /* Dual table FOR supporting queryies LIKE: SELECT 1 AS id => SELECT 1 AS "id" *FROM Dual* */ CREATE TABLE Dual (Dummy VARCHAR(10)); COMMIT; INSERT INTO Dual (Dummy) VALUES ('X'); COMMIT; -- Person Table CREATE TABLE Person ( PersonID INTEGER NOT NULL PRIMARY KEY, FirstName VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL, LastName VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL, MiddleName VARCHAR(50), Gender CHAR(1) NOT NULL CHECK (Gender in ('M', 'F', 'U', 'O')) ); COMMIT; CREATE GENERATOR PersonID; COMMIT; CREATE GENERATOR TimestampGen; COMMIT; CREATE TRIGGER CREATE_PersonID FOR Person BEFORE INSERT POSITION 0 AS BEGIN NEW.PersonID = GEN_ID(PersonID, 1); END COMMIT; INSERT INTO Person (FirstName, LastName, Gender) VALUES ('John', 'Pupkin', 'M'); COMMIT; INSERT INTO Person (FirstName, LastName, Gender) VALUES ('Tester', 'Testerson', 'M'); COMMIT; -- Doctor Table Extension CREATE TABLE Doctor ( PersonID INTEGER NOT NULL, Taxonomy VARCHAR(50) NOT NULL, FOREIGN KEY (PersonID) REFERENCES Person (PersonID) ON DELETE CASCADE ) COMMIT; INSERT INTO Doctor (PersonID, Taxonomy) VALUES (1, 'Psychiatry'); COMMIT; -- Patient Table Extension CREATE TABLE Patient ( PersonID int NOT NULL, Diagnosis VARCHAR(256) NOT NULL, FOREIGN KEY (PersonID) REFERENCES Person (PersonID) ON DELETE CASCADE ); COMMIT; INSERT INTO Patient (PersonID, Diagnosis) VALUES (2, 'Hallucination with Paranoid Bugs'' Delirium of Persecution'); COMMIT; -- Person_SelectByKey CREATE PROCEDURE Person_SelectByKey(id INTEGER) RETURNS ( PersonID INTEGER, FirstName VARCHAR(50), LastName VARCHAR(50), MiddleName VARCHAR(50), Gender CHAR(1) ) AS BEGIN SELECT PersonID, FirstName, LastName, MiddleName, Gender FROM Person WHERE PersonID = :id INTO :PersonID, :FirstName, :LastName, :MiddleName, :Gender; SUSPEND; END COMMIT; -- Person_SelectAll CREATE PROCEDURE Person_SelectAll RETURNS ( PersonID INTEGER, FirstName VARCHAR(50), LastName VARCHAR(50), MiddleName VARCHAR(50), Gender CHAR(1) ) AS BEGIN FOR SELECT PersonID, FirstName, LastName, MiddleName, Gender FROM Person INTO :PersonID, :FirstName, :LastName, :MiddleName, :Gender DO SUSPEND; END COMMIT; -- Person_SelectByName CREATE PROCEDURE Person_SelectByName ( in_FirstName VARCHAR(50), in_LastName VARCHAR(50) ) RETURNS ( PersonID int, FirstName VARCHAR(50), LastName VARCHAR(50), MiddleName VARCHAR(50), Gender CHAR(1) ) AS BEGIN FOR SELECT PersonID, FirstName, LastName, MiddleName, Gender FROM Person WHERE FirstName LIKE :in_FirstName and LastName LIKE :in_LastName INTO :PersonID, :FirstName, :LastName, :MiddleName, :Gender DO SUSPEND; END COMMIT; -- Person_Insert CREATE PROCEDURE Person_Insert( FirstName VARCHAR(50), LastName VARCHAR(50), MiddleName VARCHAR(50), Gender CHAR(1) ) RETURNS (PersonID INTEGER) AS BEGIN INSERT INTO Person ( LastName, FirstName, MiddleName, Gender) VALUES (:LastName, :FirstName, :MiddleName, :Gender); SELECT MAX(PersonID) FROM person INTO :PersonID; SUSPEND; END COMMIT; -- Person_Insert_OutputParameter CREATE PROCEDURE Person_Insert_OutputParameter( FirstName VARCHAR(50), LastName VARCHAR(50), MiddleName VARCHAR(50), Gender CHAR(1) ) RETURNS (PersonID INTEGER) AS BEGIN INSERT INTO Person ( LastName, FirstName, MiddleName, Gender) VALUES (:LastName, :FirstName, :MiddleName, :Gender); SELECT max(PersonID) FROM person INTO :PersonID; SUSPEND; END COMMIT; -- Person_Update CREATE PROCEDURE Person_Update( PersonID INTEGER, FirstName VARCHAR(50), LastName VARCHAR(50), MiddleName VARCHAR(50), Gender CHAR(1) ) AS BEGIN UPDATE Person SET LastName = :LastName, FirstName = :FirstName, MiddleName = :MiddleName, Gender = :Gender WHERE PersonID = :PersonID; END COMMIT; -- Person_Delete CREATE PROCEDURE Person_Delete( PersonID INTEGER ) AS BEGIN DELETE FROM Person WHERE PersonID = :PersonID; END COMMIT; -- Patient_SelectAll CREATE PROCEDURE Patient_SelectAll RETURNS ( PersonID int, FirstName VARCHAR(50), LastName VARCHAR(50), MiddleName VARCHAR(50), Gender CHAR(1), Diagnosis VARCHAR(256) ) AS BEGIN FOR SELECT Person.PersonID, FirstName, LastName, MiddleName, Gender, Patient.Diagnosis FROM Patient, Person WHERE Patient.PersonID = Person.PersonID INTO :PersonID, :FirstName, :LastName, :MiddleName, :Gender, :Diagnosis DO SUSPEND; END COMMIT; -- Patient_SelectByName CREATE PROCEDURE Patient_SelectByName( FirstName VARCHAR(50), LastName VARCHAR(50) ) RETURNS ( PersonID int, MiddleName VARCHAR(50), Gender CHAR(1), Diagnosis VARCHAR(256) ) AS BEGIN FOR SELECT Person.PersonID, MiddleName, Gender, Patient.Diagnosis FROM Patient, Person WHERE Patient.PersonID = Person.PersonID and FirstName = :FirstName and LastName = :LastName INTO :PersonID, :MiddleName, :Gender, :Diagnosis DO SUSPEND; END COMMIT; -- BinaryData Table CREATE TABLE BinaryData ( BinaryDataID INTEGER NOT NULL PRIMARY KEY, Stamp INTEGER NOT NULL, Data BLOB NOT NULL ); COMMIT; CREATE TRIGGER CREATE_BinaryDataID FOR BinaryData BEFORE INSERT POSITION 0 AS BEGIN NEW.BinaryDataID = GEN_ID(PersonID, 1); NEW.Stamp = GEN_ID(TimestampGen, 1); END COMMIT; CREATE TRIGGER CHANGE_BinaryData FOR BinaryData beFORe update AS BEGIN NEW.Stamp = GEN_ID(TimestampGen, 1); END COMMIT; -- OutRefTest /* Fake input parameters are used to "emulate" input/output parameters. Each inout parameter should be defined in RETURNS(...) section and allso have a "mirror" in input section, mirror name shoul be: FdpDataProvider.InOutInputParameterPrefix + [parameter name] ex: in_inputOutputID is input mirror FOR inout parameter inputOutputID */ CREATE PROCEDURE OutRefTest( ID INTEGER, in_inputOutputID INTEGER, str VARCHAR(50), in_inputOutputStr VARCHAR(50) ) RETURNS( inputOutputID INTEGER, inputOutputStr VARCHAR(50), outputID INTEGER, outputStr VARCHAR(50) ) AS BEGIN outputID = ID; inputOutputID = ID + in_inputOutputID; outputStr = str; inputOutputStr = str || in_inputOutputStr; SUSPEND; END COMMIT; -- OutRefEnumTest CREATE PROCEDURE OutRefEnumTest( str VARCHAR(50), in_inputOutputStr VARCHAR(50) ) RETURNS ( inputOutputStr VARCHAR(50), outputStr VARCHAR(50) ) AS BEGIN outputStr = str; inputOutputStr = str || in_inputOutputStr; SUSPEND; END COMMIT; -- ExecuteScalarTest CREATE PROCEDURE Scalar_DataReader RETURNS( intField INTEGER, stringField VARCHAR(50) ) AS BEGIN intField = 12345; stringField = '54321'; SUSPEND; END COMMIT; CREATE PROCEDURE Scalar_OutputParameter RETURNS ( outputInt INTEGER, outputString VARCHAR(50) ) AS BEGIN outputInt = 12345; outputString = '54321'; SUSPEND; END COMMIT; /* "Return_Value" is the name for ReturnValue "emulating" may be changed: FdpDataProvider.ReturnParameterName */ CREATE PROCEDURE Scalar_ReturnParameter RETURNS (Return_Value INTEGER) AS BEGIN Return_Value = 12345; SUSPEND; END COMMIT; -- Data Types test /* Data definitions according to: http://www.firebirdsql.org/manual/migration-mssql-data-types.html BUT! BLOB is ised for BINARY data! not CHAR */ CREATE TABLE DataTypeTest ( DataTypeID INTEGER NOT NULL PRIMARY KEY, Binary_ BLOB, Boolean_ CHAR(1), Byte_ SMALLINT, Bytes_ BLOB, CHAR_ CHAR(1), DateTime_ TIMESTAMP, Decimal_ DECIMAL(10, 2), Double_ DOUBLE PRECISION, Guid_ CHAR(38), Int16_ SMALLINT, Int32_ INTEGER, Int64_ NUMERIC(11), Money_ DECIMAL(18, 4), SByte_ SMALLINT, Single_ FLOAT, Stream_ BLOB, String_ VARCHAR(50) CHARACTER SET UNICODE_FSS, UInt16_ SMALLINT, UInt32_ INTEGER, UInt64_ NUMERIC(11), Xml_ CHAR(1000) ) COMMIT; CREATE GENERATOR DataTypeID; COMMIT; CREATE TRIGGER CREATE_DataTypeTest FOR DataTypeTest BEFORE INSERT POSITION 0 AS BEGIN NEW.DataTypeID = GEN_ID(DataTypeID, 1); END COMMIT; INSERT INTO DataTypeTest (Binary_, Boolean_, Byte_, Bytes_, CHAR_, DateTime_, Decimal_, Double_, Guid_, Int16_, Int32_, Int64_, Money_, SByte_, Single_, Stream_, String_, UInt16_, UInt32_, UInt64_, Xml_) VALUES ( NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL); COMMIT; INSERT INTO DataTypeTest (Binary_, Boolean_, Byte_, Bytes_, CHAR_, DateTime_, Decimal_, Double_, Guid_, Int16_, Int32_, Int64_, Money_, SByte_, Single_, Stream_, String_, UInt16_, UInt32_, UInt64_, Xml_) VALUES ('dddddddddddddddd', 1, 255,'dddddddddddddddd', 'B', 'NOW', 12345.67, 1234.567, 'dddddddddddddddddddddddddddddddd', 32767, 32768, 1000000, 12.3456, 127, 1234.123, 'dddddddddddddddd', 'string', 32767, 32768, 200000000, '<root><element strattr="strvalue" intattr="12345"/></root>'); COMMIT; DROP TABLE Parent COMMIT; DROP TABLE Child COMMIT; DROP TABLE GrandChild COMMIT; CREATE TABLE Parent (ParentID int, Value1 int) COMMIT; CREATE TABLE Child (ParentID int, ChildID int) COMMIT; CREATE TABLE GrandChild (ParentID int, ChildID int, GrandChildID int) COMMIT; DROP TABLE LinqDataTypes COMMIT; CREATE TABLE LinqDataTypes ( ID int, MoneyValue decimal(10,4), DateTimeValue timestamp, DateTimeValue2 timestamp, BoolValue char(1), GuidValue char(38), BinaryValue blob, SmallIntValue smallint, IntValue int, BigIntValue bigint ) COMMIT; DROP GENERATOR SequenceTestSeq COMMIT; CREATE GENERATOR SequenceTestSeq COMMIT; DROP TABLE SequenceTest COMMIT; CREATE TABLE SequenceTest ( ID int NOT NULL PRIMARY KEY, Value_ VARCHAR(50) NOT NULL ) COMMIT; DROP TRIGGER CREATE_ID COMMIT; DROP GENERATOR TestIdentityID COMMIT; DROP TABLE TestIdentity COMMIT; CREATE TABLE TestIdentity ( ID INTEGER NOT NULL PRIMARY KEY ) COMMIT; CREATE GENERATOR TestIdentityID; COMMIT; CREATE TRIGGER CREATE_ID FOR TestIdentity BEFORE INSERT POSITION 0 AS BEGIN NEW.ID = GEN_ID(TestIdentityID, 1); END COMMIT; CREATE VIEW PersonView AS SELECT * FROM Person COMMIT;