--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/Data/Create Scripts/Firebird2.sql	Thu Mar 27 21:46:09 2014 +0400
@@ -0,0 +1,598 @@
+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;