From f6a5e7589396854e10e023c25237b47e512ff047 Mon Sep 17 00:00:00 2001 From: wei <> Date: Fri, 14 Apr 2006 11:23:56 +0000 Subject: Adding SQLMap unit tests. Allow sqlmap to use Prado's caching module to cache records. --- tests/unit/SQLMap/scripts/mssql/DBCreation.sql | 89 ++++++++ tests/unit/SQLMap/scripts/mssql/DataBase.sql | 179 +++++++++++++++ .../SQLMap/scripts/mssql/README-embed-param.txt | 8 + tests/unit/SQLMap/scripts/mssql/account-init.sql | 47 ++++ .../SQLMap/scripts/mssql/account-procedure.sql | 12 + tests/unit/SQLMap/scripts/mssql/category-init.sql | 17 ++ .../SQLMap/scripts/mssql/category-procedure.sql | 10 + tests/unit/SQLMap/scripts/mssql/documents-init.sql | 34 +++ .../scripts/mssql/embed-param-setup-init.sql | 94 ++++++++ .../SQLMap/scripts/mssql/embed-param-test-init.sql | 32 +++ .../unit/SQLMap/scripts/mssql/enumeration-init.sql | 30 +++ tests/unit/SQLMap/scripts/mssql/line-item-init.sql | 53 +++++ .../SQLMap/scripts/mssql/more-account-records.sql | 11 + tests/unit/SQLMap/scripts/mssql/order-init.sql | 54 +++++ tests/unit/SQLMap/scripts/mssql/other-init.sql | 145 ++++++++++++ .../unit/SQLMap/scripts/mssql/ps_SelectAccount.sql | 10 + tests/unit/SQLMap/scripts/mssql/swap-procedure.sql | 34 +++ tests/unit/SQLMap/scripts/mssql/user-init.sql | 17 ++ tests/unit/SQLMap/scripts/mysql/DataBase.sql | 214 ++++++++++++++++++ tests/unit/SQLMap/scripts/mysql/account-init.sql | 20 ++ .../SQLMap/scripts/mysql/account-procedure.sql | 2 + tests/unit/SQLMap/scripts/mysql/category-init.sql | 12 + .../SQLMap/scripts/mysql/category-procedure.sql | 2 + tests/unit/SQLMap/scripts/mysql/documents-init.sql | 20 ++ .../unit/SQLMap/scripts/mysql/enumeration-init.sql | 18 ++ tests/unit/SQLMap/scripts/mysql/line-item-init.sql | 37 ++++ .../SQLMap/scripts/mysql/more-account-records.sql | 7 + tests/unit/SQLMap/scripts/mysql/order-init.sql | 30 +++ tests/unit/SQLMap/scripts/mysql/other-init.sql | 91 ++++++++ tests/unit/SQLMap/scripts/mysql/swap-procedure.sql | 2 + tests/unit/SQLMap/scripts/mysql/user-init.sql | 14 ++ tests/unit/SQLMap/scripts/sqlite/database.sql | 242 +++++++++++++++++++++ 32 files changed, 1587 insertions(+) create mode 100644 tests/unit/SQLMap/scripts/mssql/DBCreation.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/DataBase.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/README-embed-param.txt create mode 100644 tests/unit/SQLMap/scripts/mssql/account-init.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/account-procedure.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/category-init.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/category-procedure.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/documents-init.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/embed-param-setup-init.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/embed-param-test-init.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/enumeration-init.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/line-item-init.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/more-account-records.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/order-init.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/other-init.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/ps_SelectAccount.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/swap-procedure.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/user-init.sql create mode 100644 tests/unit/SQLMap/scripts/mysql/DataBase.sql create mode 100644 tests/unit/SQLMap/scripts/mysql/account-init.sql create mode 100644 tests/unit/SQLMap/scripts/mysql/account-procedure.sql create mode 100644 tests/unit/SQLMap/scripts/mysql/category-init.sql create mode 100644 tests/unit/SQLMap/scripts/mysql/category-procedure.sql create mode 100644 tests/unit/SQLMap/scripts/mysql/documents-init.sql create mode 100644 tests/unit/SQLMap/scripts/mysql/enumeration-init.sql create mode 100644 tests/unit/SQLMap/scripts/mysql/line-item-init.sql create mode 100644 tests/unit/SQLMap/scripts/mysql/more-account-records.sql create mode 100644 tests/unit/SQLMap/scripts/mysql/order-init.sql create mode 100644 tests/unit/SQLMap/scripts/mysql/other-init.sql create mode 100644 tests/unit/SQLMap/scripts/mysql/swap-procedure.sql create mode 100644 tests/unit/SQLMap/scripts/mysql/user-init.sql create mode 100644 tests/unit/SQLMap/scripts/sqlite/database.sql (limited to 'tests/unit/SQLMap/scripts') diff --git a/tests/unit/SQLMap/scripts/mssql/DBCreation.sql b/tests/unit/SQLMap/scripts/mssql/DBCreation.sql new file mode 100644 index 00000000..c5ed9517 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/DBCreation.sql @@ -0,0 +1,89 @@ +-- MSQL DATABASE + +IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'IBatisNet') + DROP DATABASE [IBatisNet] +GO + +CREATE DATABASE [IBatisNet] + COLLATE Latin1_General_CI_AS +GO + +exec sp_dboption N'IBatisNet', N'autoclose', N'true' +GO + +exec sp_dboption N'IBatisNet', N'bulkcopy', N'false' +GO + +exec sp_dboption N'IBatisNet', N'trunc. log', N'true' +GO + +exec sp_dboption N'IBatisNet', N'torn page detection', N'true' +GO + +exec sp_dboption N'IBatisNet', N'read only', N'false' +GO + +exec sp_dboption N'IBatisNet', N'dbo use', N'false' +GO + +exec sp_dboption N'IBatisNet', N'single', N'false' +GO + +exec sp_dboption N'IBatisNet', N'autoshrink', N'true' +GO + +exec sp_dboption N'IBatisNet', N'ANSI null default', N'false' +GO + +exec sp_dboption N'IBatisNet', N'recursive triggers', N'false' +GO + +exec sp_dboption N'IBatisNet', N'ANSI nulls', N'false' +GO + +exec sp_dboption N'IBatisNet', N'concat null yields null', N'false' +GO + +exec sp_dboption N'IBatisNet', N'cursor close on commit', N'false' +GO + +exec sp_dboption N'IBatisNet', N'default to local cursor', N'false' +GO + +exec sp_dboption N'IBatisNet', N'quoted identifier', N'false' +GO + +exec sp_dboption N'IBatisNet', N'ANSI warnings', N'false' +GO + +exec sp_dboption N'IBatisNet', N'auto create statistics', N'true' +GO + +exec sp_dboption N'IBatisNet', N'auto update statistics', N'true' +GO + +if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) ) + exec sp_dboption N'IBatisNet', N'db chaining', N'false' +GO + +if exists (select * from master.dbo.syslogins where loginname = N'IBatisNet') + exec sp_droplogin N'IBatisNet' +GO + +use [IBatisNet] +GO + +if not exists (select * from master.dbo.syslogins where loginname = N'IBatisNet') +BEGIN + declare @logindb nvarchar(132), @loginpass nvarchar(132), @loginlang nvarchar(132) + select @logindb = N'IBatisNet', @loginpass=N'test', @loginlang = N'us_english' + exec sp_addlogin N'IBatisNet', @loginpass, @logindb, @loginlang +END +GO + +if not exists (select * from dbo.sysusers where name = N'IBatisNet' and uid < 16382) + EXEC sp_grantdbaccess N'IBatisNet', N'IBatisNet' +GO + +exec sp_addrolemember N'db_owner', N'IBatisNet' +GO \ No newline at end of file diff --git a/tests/unit/SQLMap/scripts/mssql/DataBase.sql b/tests/unit/SQLMap/scripts/mssql/DataBase.sql new file mode 100644 index 00000000..733e2f99 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/DataBase.sql @@ -0,0 +1,179 @@ +-- MSQL DATABASE 'IBatisNet' + +IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'IBatisNet') + DROP DATABASE [IBatisNet] +GO + +CREATE DATABASE [IBatisNet] + COLLATE Latin1_General_CI_AS +GO + +exec sp_dboption N'IBatisNet', N'autoclose', N'true' +GO + +exec sp_dboption N'IBatisNet', N'bulkcopy', N'false' +GO + +exec sp_dboption N'IBatisNet', N'trunc. log', N'true' +GO + +exec sp_dboption N'IBatisNet', N'torn page detection', N'true' +GO + +exec sp_dboption N'IBatisNet', N'read only', N'false' +GO + +exec sp_dboption N'IBatisNet', N'dbo use', N'false' +GO + +exec sp_dboption N'IBatisNet', N'single', N'false' +GO + +exec sp_dboption N'IBatisNet', N'autoshrink', N'true' +GO + +exec sp_dboption N'IBatisNet', N'ANSI null default', N'false' +GO + +exec sp_dboption N'IBatisNet', N'recursive triggers', N'false' +GO + +exec sp_dboption N'IBatisNet', N'ANSI nulls', N'false' +GO + +exec sp_dboption N'IBatisNet', N'concat null yields null', N'false' +GO + +exec sp_dboption N'IBatisNet', N'cursor close on commit', N'false' +GO + +exec sp_dboption N'IBatisNet', N'default to local cursor', N'false' +GO + +exec sp_dboption N'IBatisNet', N'quoted identifier', N'false' +GO + +exec sp_dboption N'IBatisNet', N'ANSI warnings', N'false' +GO + +exec sp_dboption N'IBatisNet', N'auto create statistics', N'true' +GO + +exec sp_dboption N'IBatisNet', N'auto update statistics', N'true' +GO + +if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) ) + exec sp_dboption N'IBatisNet', N'db chaining', N'false' +GO + +if exists (select * from master.dbo.syslogins where loginname = N'IBatisNet') + exec sp_droplogin N'IBatisNet' +GO + +use [IBatisNet] +GO + +if not exists (select * from master.dbo.syslogins where loginname = N'IBatisNet') +BEGIN + declare @logindb nvarchar(132), @loginpass nvarchar(132), @loginlang nvarchar(132) + select @logindb = N'IBatisNet', @loginpass=N'test', @loginlang = N'us_english' + exec sp_addlogin N'IBatisNet', @loginpass, @logindb, @loginlang +END +GO + +if not exists (select * from dbo.sysusers where name = N'IBatisNet' and uid < 16382) + EXEC sp_grantdbaccess N'IBatisNet', N'IBatisNet' +GO + +exec sp_addrolemember N'db_owner', N'IBatisNet' +GO + +-- MSQL DATABASE 'NHibernate' + +IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'NHibernate') + DROP DATABASE [NHibernate] +GO + +CREATE DATABASE [NHibernate] + COLLATE Latin1_General_CI_AS +GO + +exec sp_dboption N'NHibernate', N'autoclose', N'true' +GO + +exec sp_dboption N'NHibernate', N'bulkcopy', N'false' +GO + +exec sp_dboption N'NHibernate', N'trunc. log', N'true' +GO + +exec sp_dboption N'NHibernate', N'torn page detection', N'true' +GO + +exec sp_dboption N'NHibernate', N'read only', N'false' +GO + +exec sp_dboption N'NHibernate', N'dbo use', N'false' +GO + +exec sp_dboption N'NHibernate', N'single', N'false' +GO + +exec sp_dboption N'NHibernate', N'autoshrink', N'true' +GO + +exec sp_dboption N'NHibernate', N'ANSI null default', N'false' +GO + +exec sp_dboption N'NHibernate', N'recursive triggers', N'false' +GO + +exec sp_dboption N'NHibernate', N'ANSI nulls', N'false' +GO + +exec sp_dboption N'NHibernate', N'concat null yields null', N'false' +GO + +exec sp_dboption N'NHibernate', N'cursor close on commit', N'false' +GO + +exec sp_dboption N'NHibernate', N'default to local cursor', N'false' +GO + +exec sp_dboption N'NHibernate', N'quoted identifier', N'false' +GO + +exec sp_dboption N'NHibernate', N'ANSI warnings', N'false' +GO + +exec sp_dboption N'NHibernate', N'auto create statistics', N'true' +GO + +exec sp_dboption N'NHibernate', N'auto update statistics', N'true' +GO + +if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) ) + exec sp_dboption N'NHibernate', N'db chaining', N'false' +GO + +if exists (select * from master.dbo.syslogins where loginname = N'NHibernate') + exec sp_droplogin N'NHibernate' +GO + +use [NHibernate] +GO + +if not exists (select * from master.dbo.syslogins where loginname = N'NHibernate') +BEGIN + declare @logindb nvarchar(132), @loginpass nvarchar(132), @loginlang nvarchar(132) + select @logindb = N'NHibernate', @loginpass=N'test', @loginlang = N'us_english' + exec sp_addlogin N'NHibernate', @loginpass, @logindb, @loginlang +END +GO + +if not exists (select * from dbo.sysusers where name = N'NHibernate' and uid < 16382) + EXEC sp_grantdbaccess N'NHibernate', N'NHibernate' +GO + +exec sp_addrolemember N'db_owner', N'NHibernate' +GO \ No newline at end of file diff --git a/tests/unit/SQLMap/scripts/mssql/README-embed-param.txt b/tests/unit/SQLMap/scripts/mssql/README-embed-param.txt new file mode 100644 index 00000000..355ebf42 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/README-embed-param.txt @@ -0,0 +1,8 @@ +Technique for creating large sample test data from: + +http://www.sql-server-performance.com/jc_large_data_operations.asp + +Make sure you have enough space and have either enough processing power or +enough patience to run the Embed Parameters in Statement tests. + +Run embed-parameters-setup-init.sql prior to running tests. diff --git a/tests/unit/SQLMap/scripts/mssql/account-init.sql b/tests/unit/SQLMap/scripts/mssql/account-init.sql new file mode 100644 index 00000000..8334798b --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/account-init.sql @@ -0,0 +1,47 @@ +-- Creating Table + +use [IBatisNet] + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Accounts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +BEGIN + if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Orders_Accounts]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) + ALTER TABLE [dbo].[Orders] DROP CONSTRAINT FK_Orders_Accounts + + drop table [dbo].[Accounts] +END + +CREATE TABLE [dbo].[Accounts] ( + [Account_ID] [int] NOT NULL , + [Account_FirstName] [varchar] (32) NOT NULL , + [Account_LastName] [varchar] (32) NOT NULL , + [Account_Email] [varchar] (128) NULL, + [Account_Banner_Option] [varchar] (255), + [Account_Cart_Option] [int] +) ON [PRIMARY] + +ALTER TABLE [dbo].[Accounts] WITH NOCHECK ADD + CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED + ( + [Account_ID] + ) ON [PRIMARY] + +-- Creating Test Data + +INSERT INTO [dbo].[Accounts] VALUES(1,'Joe', 'Dalton', 'Joe.Dalton@somewhere.com', 'Oui', 200); +INSERT INTO [dbo].[Accounts] VALUES(2,'Averel', 'Dalton', 'Averel.Dalton@somewhere.com', 'Oui', 200); +INSERT INTO [dbo].[Accounts] VALUES(3,'William', 'Dalton', null, 'Non', 100); +INSERT INTO [dbo].[Accounts] VALUES(4,'Jack', 'Dalton', 'Jack.Dalton@somewhere.com', 'Non', 100); +INSERT INTO [dbo].[Accounts] VALUES(5,'Gilles', 'Bayon', null, 'Oui', 100); + +-- Store procedure + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ps_InsertAccount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) +drop procedure [dbo].[ps_InsertAccount] + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ps_SelectAccount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) +drop procedure [dbo].[ps_SelectAccount] + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ps_swap_email_address]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) +drop procedure [dbo].[ps_swap_email_address] + + diff --git a/tests/unit/SQLMap/scripts/mssql/account-procedure.sql b/tests/unit/SQLMap/scripts/mssql/account-procedure.sql new file mode 100644 index 00000000..a18e1522 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/account-procedure.sql @@ -0,0 +1,12 @@ +CREATE PROCEDURE dbo.[ps_InsertAccount] +@Account_ID [int], +@Account_FirstName [nvarchar] (40), +@Account_LastName [varchar] (32), +@Account_Email [varchar] (128), +@Account_Banner_Option [varchar] (255), +@Account_Cart_Option [int] +AS +insert into Accounts + (Account_ID, Account_FirstName, Account_LastName, Account_Email, Account_Banner_Option, Account_Cart_Option) +values + (@Account_ID, @Account_FirstName, @Account_LastName, @Account_Email, @Account_Banner_Option, @Account_Cart_Option) diff --git a/tests/unit/SQLMap/scripts/mssql/category-init.sql b/tests/unit/SQLMap/scripts/mssql/category-init.sql new file mode 100644 index 00000000..c0d20603 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/category-init.sql @@ -0,0 +1,17 @@ +-- Creating Table + +use [IBatisNet] + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Categories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +drop table [dbo].[Categories] + +CREATE TABLE [dbo].[Categories] ( + [Category_Id] [int] IDENTITY (1, 1) NOT NULL , + [Category_Name] [varchar] (32) NULL, + [Category_Guid] [uniqueidentifier] NULL +) ON [PRIMARY] + +-- Store procedure + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ps_InsertCategorie]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) +drop procedure [dbo].[ps_InsertCategorie] diff --git a/tests/unit/SQLMap/scripts/mssql/category-procedure.sql b/tests/unit/SQLMap/scripts/mssql/category-procedure.sql new file mode 100644 index 00000000..45fcda2c --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/category-procedure.sql @@ -0,0 +1,10 @@ +CREATE PROCEDURE dbo.[ps_InsertCategorie] +@Category_Id [int] output, +@Category_Name [varchar] (32), +@Category_Guid [uniqueidentifier] +AS +insert into Categories + (Category_Name, Category_Guid ) +values + (@Category_Name, @Category_Guid) +SELECT @Category_Id = SCOPE_IDENTITY() \ No newline at end of file diff --git a/tests/unit/SQLMap/scripts/mssql/documents-init.sql b/tests/unit/SQLMap/scripts/mssql/documents-init.sql new file mode 100644 index 00000000..686a80ad --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/documents-init.sql @@ -0,0 +1,34 @@ +-- Creating Table + +use [IBatisNet] + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Documents]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +BEGIN + if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_LineItems_Orders]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) + ALTER TABLE [dbo].[LineItems] DROP CONSTRAINT FK_LineItems_Orders + + drop table [dbo].[Documents] +END + +CREATE TABLE [dbo].[Documents] ( + [Document_ID] [int] NOT NULL , + [Document_Title] [varchar] (32) NULL , + [Document_Type] [varchar] (32) NULL , + [Document_PageNumber] [int] NULL , + [Document_City] [varchar] (32) NULL +) ON [PRIMARY] + +ALTER TABLE [dbo].[Documents] WITH NOCHECK ADD + CONSTRAINT [PK_Documents] PRIMARY KEY CLUSTERED + ( + [Document_ID] + ) ON [PRIMARY] + +-- Creating Test Data + +INSERT INTO [dbo].[Documents] VALUES (1, 'The World of Null-A', 'Book', 55, null); +INSERT INTO [dbo].[Documents] VALUES (2, 'Le Progres de Lyon', 'Newspaper', null , 'Lyon'); +INSERT INTO [dbo].[Documents] VALUES (3, 'Lord of the Rings', 'Book', 3587, null); +INSERT INTO [dbo].[Documents] VALUES (4, 'Le Canard enchaine', 'Tabloid', null , 'Paris'); +INSERT INTO [dbo].[Documents] VALUES (5, 'Le Monde', 'Broadsheet', null , 'Paris'); +INSERT INTO [dbo].[Documents] VALUES (6, 'Foundation', 'Monograph', 557, null); diff --git a/tests/unit/SQLMap/scripts/mssql/embed-param-setup-init.sql b/tests/unit/SQLMap/scripts/mssql/embed-param-setup-init.sql new file mode 100644 index 00000000..de2c3f7b --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/embed-param-setup-init.sql @@ -0,0 +1,94 @@ +-- Technique for creating large sample test data from +-- http://www.sql-server-performance.com/jc_large_data_operations.asp + +use [IBatisNet] + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ManyRecords]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +drop table [dbo].[ManyRecords] + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ManyRecordsTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +drop table [dbo].[ManyRecordsTest] + + + +-- Create Data Storage Table +CREATE TABLE [dbo].[ManyRecords] ( + [Many_FirstID] [int] NOT NULL, + [Many_SecondID] [int] NOT NULL, + [Many_ThirdID] [int] NOT NULL, + [Many_FourthID] [int] NOT NULL, + [Many_FifthID] [int] NOT NULL, + [Many_SequenceID] [int] NOT NULL, + [Many_DistributedID] [int] NOT NULL, + [Many_SampleCharValue] [char] (10) NOT NULL, + [Many_SampleDecimal] [decimal] (9,4) NOT NULL, + [Many_SampleMoney] [money] NOT NULL, + [Many_SampleDate] [datetime] NOT NULL, + [Many_SequenceDate] [datetime] NOT NULL ) +ON [PRIMARY] + + + +-- Create Sample Data of 1 million records (increase if needed) +BEGIN TRANSACTION + DECLARE @intIndex int, @rowCount int, @seqCount int, @distValue int + SELECT @intIndex = 1, @rowCount = 1000000, @seqCount = 10000 + SELECT @distValue = @rowCount/10000 + + WHILE @intIndex <= @rowCount + BEGIN + INSERT INTO [dbo].[ManyRecords] ( + [Many_FirstID], + [Many_SecondID], + [Many_ThirdID], + [Many_FourthID], + [Many_FifthID], + [Many_SequenceID], + [Many_DistributedID], + [Many_SampleCharValue], + [Many_SampleDecimal], + [Many_SampleMoney], + [Many_SampleDate], + [Many_SequenceDate] ) + VALUES ( + @intIndex, -- First + @intIndex/2, -- Second + @intIndex/4, -- Third + @intIndex/10, -- Fourth + @intIndex/20, -- Fifth + (@intIndex-1)/@seqCount + 1, -- Sequential value + (@intIndex-1)%(@distValue) + 1, -- Distributed value + CHAR(65 + 26*rand())+CHAR(65 + 26*rand())+CHAR(65 + 26*rand())+CONVERT(char(6),CONVERT(int,100000*(9.0*rand()+1.0)))+CHAR(65 + 26*rand()), -- Char Value + 10000*rand(), -- Decimal value + 10000*rand(), -- Money value + DATEADD(hour,100000*rand(),'1990-01-01'), -- Date value + DATEADD(hour,@intIndex/5,'1990-01-01') ) -- Sequential date value + + SET @intIndex = @intIndex + 1 + END +COMMIT TRANSACTION + + + +-- Create Test table using storage table sample data +SELECT + [Many_FirstID], + [Many_SecondID], + [Many_ThirdID], + [Many_FourthID], + [Many_FifthID], + [Many_SequenceID], + [Many_DistributedID], + [Many_SampleCharValue], + [Many_SampleDecimal], + [Many_SampleMoney], + [Many_SampleDate], + [Many_SequenceDate] +INTO [dbo].[ManyRecordsTest] +FROM [dbo].[ManyRecords] + + + +-- Create Test table indexes +CREATE INDEX [IDX_ManyRecordsTest_Seq] ON [dbo].[ManyRecordsTest] ([Many_SequenceID]) WITH SORT_IN_TEMPDB +CREATE INDEX [IDX_ManyRecordsTest_Dist] ON [dbo].[ManyRecordsTest] ([Many_DistributedID]) WITH SORT_IN_TEMPDB \ No newline at end of file diff --git a/tests/unit/SQLMap/scripts/mssql/embed-param-test-init.sql b/tests/unit/SQLMap/scripts/mssql/embed-param-test-init.sql new file mode 100644 index 00000000..17affd8e --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/embed-param-test-init.sql @@ -0,0 +1,32 @@ +-- Technique for creating large sample test data from +-- http://www.sql-server-performance.com/jc_large_data_operations.asp + +use [IBatisNet] + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ManyRecordsTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +drop table [dbo].[ManyRecordsTest] + + + +-- Create Test table using storage table sample data +SELECT + [Many_FirstID], + [Many_SecondID], + [Many_ThirdID], + [Many_FourthID], + [Many_FifthID], + [Many_SequenceID], + [Many_DistributedID], + [Many_SampleCharValue], + [Many_SampleDecimal], + [Many_SampleMoney], + [Many_SampleDate], + [Many_SequenceDate] +INTO [dbo].[ManyRecordsTest] +FROM [dbo].[ManyRecords] + + + +-- Create Test table indexes +CREATE INDEX [IDX_ManyRecordsTest_Seq] ON [dbo].[ManyRecordsTest] ([Many_SequenceID]) WITH SORT_IN_TEMPDB +CREATE INDEX [IDX_ManyRecordsTest_Dist] ON [dbo].[ManyRecordsTest] ([Many_DistributedID]) WITH SORT_IN_TEMPDB \ No newline at end of file diff --git a/tests/unit/SQLMap/scripts/mssql/enumeration-init.sql b/tests/unit/SQLMap/scripts/mssql/enumeration-init.sql new file mode 100644 index 00000000..f5ed9af1 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/enumeration-init.sql @@ -0,0 +1,30 @@ +-- Creating Table + +use [IBatisNet] + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Enumerations]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +BEGIN + drop table [dbo].[Enumerations] +END + +CREATE TABLE [dbo].[Enumerations] ( + [Enum_ID] [int] NOT NULL , + [Enum_Day] [int] NOT NULL , + [Enum_Color] [int] NOT NULL, + [Enum_Month] [int] NULL +) ON [PRIMARY] + +ALTER TABLE [dbo].[Enumerations] WITH NOCHECK ADD + CONSTRAINT [PK_Enum] PRIMARY KEY CLUSTERED + ( + [Enum_ID] + ) ON [PRIMARY] + +-- Creating Test Data + +INSERT INTO [dbo].[Enumerations] VALUES(1, 1, 1, 128); +INSERT INTO [dbo].[Enumerations] VALUES(2, 2, 2, 2048); +INSERT INTO [dbo].[Enumerations] VALUES(3, 3, 4, 256); +INSERT INTO [dbo].[Enumerations] VALUES(4, 4, 8, null); + + diff --git a/tests/unit/SQLMap/scripts/mssql/line-item-init.sql b/tests/unit/SQLMap/scripts/mssql/line-item-init.sql new file mode 100644 index 00000000..c823667b --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/line-item-init.sql @@ -0,0 +1,53 @@ +-- Creating Table + +use [IBatisNet] + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LineItems]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +drop table [dbo].[LineItems] + +CREATE TABLE [dbo].[LineItems] ( + [LineItem_ID] [int] NOT NULL , + [Order_ID] [int] NOT NULL , + [LineItem_Code] [varchar] (32) NOT NULL , + [LineItem_Quantity] [int] NOT NULL , + [LineItem_Price] [decimal](18, 2) NULL, + [LineItem_Picture] [image] null +) ON [PRIMARY] + +ALTER TABLE [dbo].[LineItems] WITH NOCHECK ADD + CONSTRAINT [PK_LinesItem] PRIMARY KEY CLUSTERED + ( + [LineItem_ID], + [Order_ID] + ) ON [PRIMARY] + +ALTER TABLE [dbo].[LineItems] ADD + CONSTRAINT [FK_LineItems_Orders] FOREIGN KEY + ( + [Order_ID] + ) REFERENCES [dbo].[Orders] ( + [Order_ID] + ) +-- Creating Test Data + +INSERT INTO [dbo].[LineItems] VALUES (1, 10, 'ESM-34', 1, 45.43, null); +INSERT INTO [dbo].[LineItems] VALUES (2, 10, 'QSM-98', 8, 8.40, null); +INSERT INTO [dbo].[LineItems] VALUES (1, 9, 'DSM-78', 2, 45.40, null); +INSERT INTO [dbo].[LineItems] VALUES (2, 9, 'TSM-12', 2, 32.12, null); +INSERT INTO [dbo].[LineItems] VALUES (1, 8, 'DSM-16', 4, 41.30, null); +INSERT INTO [dbo].[LineItems] VALUES (2, 8, 'GSM-65', 1, 2.20, null); +INSERT INTO [dbo].[LineItems] VALUES (1, 7, 'WSM-27', 7, 52.10, null); +INSERT INTO [dbo].[LineItems] VALUES (2, 7, 'ESM-23', 2, 123.34, null); +INSERT INTO [dbo].[LineItems] VALUES (1, 6, 'QSM-39', 9, 12.12, null); +INSERT INTO [dbo].[LineItems] VALUES (2, 6, 'ASM-45', 6, 78.77, null); +INSERT INTO [dbo].[LineItems] VALUES (1, 5, 'ESM-48', 3, 43.87, null); +INSERT INTO [dbo].[LineItems] VALUES (2, 5, 'WSM-98', 7, 5.40, null); +INSERT INTO [dbo].[LineItems] VALUES (1, 4, 'RSM-57', 2, 78.90, null); +INSERT INTO [dbo].[LineItems] VALUES (2, 4, 'XSM-78', 9, 2.34, null); +INSERT INTO [dbo].[LineItems] VALUES (1, 3, 'DSM-59', 3, 5.70, null); +INSERT INTO [dbo].[LineItems] VALUES (2, 3, 'DSM-53', 3, 98.78, null); +INSERT INTO [dbo].[LineItems] VALUES (1, 2, 'DSM-37', 4, 7.80, null); +INSERT INTO [dbo].[LineItems] VALUES (2, 2, 'FSM-12', 2, 55.78, null); +INSERT INTO [dbo].[LineItems] VALUES (1, 1, 'ESM-48', 8, 87.60, null); +INSERT INTO [dbo].[LineItems] VALUES (2, 1, 'ESM-23', 1, 55.40, null); + diff --git a/tests/unit/SQLMap/scripts/mssql/more-account-records.sql b/tests/unit/SQLMap/scripts/mssql/more-account-records.sql new file mode 100644 index 00000000..d4b2199f --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/more-account-records.sql @@ -0,0 +1,11 @@ + + + +-- Creating Test Data + +INSERT INTO [dbo].[Accounts] VALUES(6,'Jane', 'Calamity', 'Jane.Calamity@somewhere.com', 'Oui', 200); +INSERT INTO [dbo].[Accounts] VALUES(7,'Lucky', 'Luke', 'Lucky.Luke@somewhere.com', 'Oui', 200); +INSERT INTO [dbo].[Accounts] VALUES(8,'Ming', 'Li Foo', null, 'Non', 100); +INSERT INTO [dbo].[Accounts] VALUES(9,'O''Hara', 'Steve', 'Jack.OHara@somewhere.com', 'Oui', 200); +INSERT INTO [dbo].[Accounts] VALUES(10,'Robert', 'O''Timmins', null, 'Non', 100); + diff --git a/tests/unit/SQLMap/scripts/mssql/order-init.sql b/tests/unit/SQLMap/scripts/mssql/order-init.sql new file mode 100644 index 00000000..b81b16ad --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/order-init.sql @@ -0,0 +1,54 @@ +-- Creating Table + +use [IBatisNet] + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Orders]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +BEGIN + if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_LineItems_Orders]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) + ALTER TABLE [dbo].[LineItems] DROP CONSTRAINT FK_LineItems_Orders + + drop table [dbo].[Orders] +END + +CREATE TABLE [dbo].[Orders] ( + [Order_ID] [int] NOT NULL , + [Account_ID] [int] NULL , + [Order_Date] [datetime] NULL , + [Order_CardType] [varchar] (32) NULL , + [Order_CardNumber] [varchar] (32) NULL , + [Order_CardExpiry] [varchar] (32) NULL , + [Order_Street] [varchar] (32) NULL , + [Order_City] [varchar] (32) NULL , + [Order_Province] [varchar] (32) NULL , + [Order_PostalCode] [varchar] (32) NULL , + [Order_FavouriteLineItem] [int] NULL +) ON [PRIMARY] + +ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD + CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED + ( + [Order_ID] + ) ON [PRIMARY] + + +ALTER TABLE [dbo].[Orders] ADD + CONSTRAINT [FK_Orders_Accounts] FOREIGN KEY + ( + [Account_ID] + ) REFERENCES [dbo].[Accounts] ( + [Account_ID] + ) +-- Creating Test Data -- 2003-02-15 8:15:00/ 2003-02-15 8:15:00 + +INSERT INTO [dbo].[Orders] VALUES (1, 1, '2003-02-15 8:15:00', 'VISA', '999999999999', '05/03', '11 This Street', 'Victoria', 'BC', 'C4B 4F4',2); +INSERT INTO [dbo].[Orders] VALUES (2, 4, '2003-02-15 8:15:00', 'MC', '888888888888', '06/03', '222 That Street', 'Edmonton', 'AB', 'X4K 5Y4',1); +INSERT INTO [dbo].[Orders] VALUES (3, 3, '2003-02-15 8:15:00', 'AMEX', '777777777777', '07/03', '333 Other Street', 'Regina', 'SK', 'Z4U 6Y4',2); +INSERT INTO [dbo].[Orders] VALUES (4, 2, '2003-02-15 8:15:00', 'MC', '666666666666', '08/03', '444 His Street', 'Toronto', 'ON', 'K4U 3S4',1); +INSERT INTO [dbo].[Orders] VALUES (5, 5, '2003-02-15 8:15:00', 'VISA', '555555555555', '09/03', '555 Her Street', 'Calgary', 'AB', 'J4J 7S4',2); +INSERT INTO [dbo].[Orders] VALUES (6, 5, '2003-02-15 8:15:00', 'VISA', '999999999999', '10/03', '6 Their Street', 'Victoria', 'BC', 'T4H 9G4',1); +INSERT INTO [dbo].[Orders] VALUES (7, 4, '2003-02-15 8:15:00', 'MC', '888888888888', '11/03', '77 Lucky Street', 'Edmonton', 'AB', 'R4A 0Z4',2); +INSERT INTO [dbo].[Orders] VALUES (8, 3, '2003-02-15 8:15:00', 'AMEX', '777777777777', '12/03', '888 Our Street', 'Regina', 'SK', 'S4S 7G4',1); +INSERT INTO [dbo].[Orders] VALUES (9, 2, '2003-02-15 8:15:00', 'MC', '666666666666', '01/04', '999 Your Street', 'Toronto', 'ON', 'G4D 9F4',2); +INSERT INTO [dbo].[Orders] VALUES (10, 1, '2003-02-15 8:15:00', 'VISA', '555555555555', '02/04', '99 Some Street', 'Calgary', 'AB', 'W4G 7A4',1); +INSERT INTO [dbo].[Orders] VALUES (11, null, '2003-02-15 8:15:00', 'VISA', '555555555555', '02/04', 'Null order', 'Calgary', 'ZZ', 'XXX YYY',1); + diff --git a/tests/unit/SQLMap/scripts/mssql/other-init.sql b/tests/unit/SQLMap/scripts/mssql/other-init.sql new file mode 100644 index 00000000..93ad5201 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/other-init.sql @@ -0,0 +1,145 @@ +-- Creating Table + +use [IBatisNet] + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Others]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +BEGIN + drop table [dbo].[Others] +END + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[A]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +BEGIN + drop table [dbo].[A] +END +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[B]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +BEGIN + drop table [dbo].[B] +END +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[C]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +BEGIN + drop table [dbo].[C] +END +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[D]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +BEGIN + drop table [dbo].[D] +END +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[E]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +BEGIN + drop table [dbo].[E] +END +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[F]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +BEGIN + drop table [dbo].[F] +END + + +CREATE TABLE [dbo].[Others] ( + [Other_Int] [int] NULL , + [Other_Long] [BigInt] NULL, + [Other_Bit] [Bit] NOT NULL DEFAULT (0), + [Other_String] [varchar] (32) NOT NULL +) ON [PRIMARY] + +CREATE TABLE [dbo].[F] ( + [ID] [varchar] (50) NOT NULL , + [F_Libelle] [varchar] (50) NULL , + CONSTRAINT [PK_F] PRIMARY KEY CLUSTERED + ( + [ID] + ) ON [PRIMARY] +) ON [PRIMARY] + +CREATE TABLE [dbo].[E] ( + [ID] [varchar] (50) NOT NULL , + [E_Libelle] [varchar] (50) NULL , + CONSTRAINT [PK_E] PRIMARY KEY CLUSTERED + ( + [ID] + ) ON [PRIMARY] +) ON [PRIMARY] + +CREATE TABLE [dbo].[D] ( + [ID] [varchar] (50) NOT NULL , + [D_Libelle] [varchar] (50) NULL , + CONSTRAINT [PK_D] PRIMARY KEY CLUSTERED + ( + [ID] + ) ON [PRIMARY] +) ON [PRIMARY] + +CREATE TABLE [dbo].[C] ( + [ID] [varchar] (50) NOT NULL , + [C_Libelle] [varchar] (50) NULL , + CONSTRAINT [PK_C] PRIMARY KEY CLUSTERED + ( + [ID] + ) ON [PRIMARY] +) ON [PRIMARY] + + +CREATE TABLE [dbo].[B] ( + [ID] [varchar] (50) NOT NULL , + [C_ID] [varchar] (50) NULL , + [D_ID] [varchar] (50) NULL , + [B_Libelle] [varchar] (50) NULL , + CONSTRAINT [PK_B] PRIMARY KEY CLUSTERED + ( + [ID] + ) ON [PRIMARY] , + CONSTRAINT [FK_B_C] FOREIGN KEY + ( + [C_ID] + ) REFERENCES [C] ( + [ID] + ), + CONSTRAINT [FK_B_D] FOREIGN KEY + ( + [D_ID] + ) REFERENCES [D] ( + [ID] + ) +) ON [PRIMARY] + + +CREATE TABLE [dbo].[A] ( + [Id] [varchar] (50) NOT NULL , + [B_ID] [varchar] (50) NULL , + [E_ID] [varchar] (50) NULL , + [F_ID] [varchar] (50) NULL , + [A_Libelle] [varchar] (50) NULL + CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED + ( + [Id] + ) ON [PRIMARY] , + CONSTRAINT [FK_A_B] FOREIGN KEY + ( + [B_ID] + ) REFERENCES [B] ( + [ID] + ), + CONSTRAINT [FK_A_E] FOREIGN KEY + ( + [E_ID] + ) REFERENCES [E] ( + [ID] + ), + CONSTRAINT [FK_A_F] FOREIGN KEY + ( + [F_ID] + ) REFERENCES [F] ( + [ID] + ) +) ON [PRIMARY] + + +-- Creating Test Data + +INSERT INTO [dbo].[Others] VALUES(1, 8888888, 0, 'Oui'); +INSERT INTO [dbo].[Others] VALUES(2, 9999999999, 1, 'Non'); + +INSERT INTO [dbo].[F] VALUES('f', 'fff'); +INSERT INTO [dbo].[E] VALUES('e', 'eee'); +INSERT INTO [dbo].[D] VALUES('d', 'ddd'); +INSERT INTO [dbo].[C] VALUES('c', 'ccc'); +INSERT INTO [dbo].[B] VALUES('b', 'c', null, 'bbb'); +INSERT INTO [dbo].[A] VALUES('a', 'b', 'e', null, 'aaa'); \ No newline at end of file diff --git a/tests/unit/SQLMap/scripts/mssql/ps_SelectAccount.sql b/tests/unit/SQLMap/scripts/mssql/ps_SelectAccount.sql new file mode 100644 index 00000000..668913ce --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/ps_SelectAccount.sql @@ -0,0 +1,10 @@ +CREATE PROCEDURE dbo.[ps_SelectAccount] +@Account_ID [int] +AS +select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress +from Accounts +where Account_ID = @Account_ID \ No newline at end of file diff --git a/tests/unit/SQLMap/scripts/mssql/swap-procedure.sql b/tests/unit/SQLMap/scripts/mssql/swap-procedure.sql new file mode 100644 index 00000000..203ab60d --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/swap-procedure.sql @@ -0,0 +1,34 @@ +CREATE PROCEDURE dbo.[ps_swap_email_address] +@First_Email [nvarchar] (64) output, +@Second_Email [nvarchar] (64) output +AS + +Declare @ID1 int +Declare @ID2 int + +Declare @Email1 [nvarchar] (64) +Declare @Email2 [nvarchar] (64) + + SELECT @ID1 = Account_ID, @Email1 = Account_Email + from Accounts + where Account_Email = @First_Email + + SELECT @ID2 = Account_ID, @Email2 = Account_Email + from Accounts + where Account_Email = @Second_Email + + UPDATE Accounts + set Account_Email = @Email2 + where Account_ID = @ID1 + + UPDATE Accounts + set Account_Email = @Email1 + where Account_ID = @ID2 + + SELECT @First_Email = Account_Email + from Accounts + where Account_ID = @ID1 + + SELECT @Second_Email = Account_Email + from Accounts + where Account_ID = @ID2 diff --git a/tests/unit/SQLMap/scripts/mssql/user-init.sql b/tests/unit/SQLMap/scripts/mssql/user-init.sql new file mode 100644 index 00000000..02268f3c --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/user-init.sql @@ -0,0 +1,17 @@ +-- Creating Table + +use [NHibernate] + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +BEGIN + drop table [dbo].[Users] +END + +CREATE TABLE [dbo].[Users] ( + LogonID nvarchar(20) NOT NULL default '0', + Name nvarchar(40) default NULL, + Password nvarchar(20) default NULL, + EmailAddress nvarchar(40) default NULL, + LastLogon datetime default NULL, + PRIMARY KEY (LogonID) +) diff --git a/tests/unit/SQLMap/scripts/mysql/DataBase.sql b/tests/unit/SQLMap/scripts/mysql/DataBase.sql new file mode 100644 index 00000000..b6542cdb --- /dev/null +++ b/tests/unit/SQLMap/scripts/mysql/DataBase.sql @@ -0,0 +1,214 @@ +use mysql; + +drop database IBatisNet; +create database IBatisNet; + +drop database NHibernate; +create database NHibernate; + +grant all privileges on IBatisNet.* to IBatisNet@'%' identified by 'test'; +grant all privileges on IBatisNet.* to IBatisNet@localhost identified by 'test'; +grant all privileges on IBatisNet.* to IBatisNet@localhost.localdomain identified by 'test'; + +grant all privileges on NHibernate.* to NHibernate@'%' identified by 'test'; +grant all privileges on NHibernate.* to NHibernate@localhost identified by 'test'; +grant all privileges on NHibernate.* to NHibernate@localhost.localdomain identified by 'test'; + + +/*==============================================================*/ +/* Nom de la base : MYSQL */ +/* Nom de SGBD : MySQL 3.23 */ +/* Date de cr閍tion : 27/05/2004 20:51:40 */ +/*==============================================================*/ + +use IBatisNet; + +drop table if exists Accounts; + +drop table if exists Categories; + +drop table if exists Enumerations; + +drop table if exists LineItems; + +drop table if exists Orders; + +drop table if exists Others; + +drop table if exists Documents; + +/*==============================================================*/ +/* Table : Accounts */ +/*==============================================================*/ +create table Accounts +( + Account_Id int not null, + Account_FirstName varchar(32) not null, + Account_LastName varchar(32) not null, + Account_Email varchar(128), + Account_Banner_Option varchar(255), + Account_Cart_Option int, + primary key (Account_Id) +) TYPE=INNODB; + +/*==============================================================*/ +/* Table : Categories */ +/*==============================================================*/ +create table Categories +( + Category_Id int not null AUTO_INCREMENT, + Category_Name varchar(32), + Category_Guid varchar(36), + primary key (Category_Id) +) TYPE=INNODB; + +/*==============================================================*/ +/* Table : Enumerations */ +/*==============================================================*/ +create table Enumerations +( + Enum_Id int not null, + Enum_Day int not null, + Enum_Color int not null, + Enum_Month int, + primary key (Enum_Id) +) TYPE=INNODB; + +/*==============================================================*/ +/* Table : LineItems */ +/*==============================================================*/ +create table LineItems +( + LineItem_Id int not null, + Order_Id int not null, + LineItem_Code varchar(32) not null, + LineItem_Quantity int not null, + LineItem_Price decimal(18,2), + LineItem_Picture blob, + primary key (Order_Id, LineItem_Id) +) TYPE=INNODB; + +/*==============================================================*/ +/* Table : Orders */ +/*==============================================================*/ +create table Orders +( + Order_Id int not null, + Account_Id int null, + Order_Date datetime, + Order_CardType varchar(32), + Order_CardNumber varchar(32), + Order_CardExpiry varchar(32), + Order_Street varchar(32), + Order_City varchar(32), + Order_Province varchar(32), + Order_PostalCode varchar(32), + Order_FavouriteLineItem int, + primary key (Order_Id) +) TYPE=INNODB; + +/*==============================================================*/ +/* Table : Others */ +/*==============================================================*/ +create table Others +( + Other_Int int, + Other_Long bigint, + Other_Bit bit not null default 0, + Other_String varchar(32) not null +) TYPE=INNODB; + +CREATE TABLE F ( + ID varchar(50) NOT NULL , + F_Libelle varchar(50) NULL , + primary key (ID) +) TYPE=INNODB; + +CREATE TABLE E ( + ID varchar(50) NOT NULL , + E_Libelle varchar(50) NULL , + primary key (ID) +) TYPE=INNODB; + +CREATE TABLE D ( + ID varchar(50) NOT NULL , + D_Libelle varchar(50) NULL , + primary key (ID) +) TYPE=INNODB; + +CREATE TABLE C ( + ID varchar(50) NOT NULL , + C_Libelle varchar(50) NULL , + primary key (ID) +) TYPE=INNODB; + + +CREATE TABLE B ( + ID varchar(50) NOT NULL , + C_ID varchar(50) NULL , + D_ID varchar(50) NULL , + B_Libelle varchar(50) NULL , + primary key (ID) +) TYPE=INNODB; + +ALTER TABLE B ADD CONSTRAINT FK_B_C FOREIGN KEY FK_B_C (C_ID) + REFERENCES C (ID) + ON DELETE RESTRICT + ON UPDATE RESTRICT, + ADD CONSTRAINT FK_B_D FOREIGN KEY FK_B_D (D_ID) + REFERENCES D (ID) + ON DELETE RESTRICT + ON UPDATE RESTRICT; + +CREATE TABLE A ( + ID varchar(50) NOT NULL , + B_ID varchar(50) NULL , + E_ID varchar(50) NULL , + F_ID varchar(50) NULL , + A_Libelle varchar(50) NULL , + primary key (ID) +) TYPE=INNODB; + +ALTER TABLE A ADD CONSTRAINT FK_A_B FOREIGN KEY FK_A_B (B_ID) + REFERENCES B (ID) + ON DELETE RESTRICT + ON UPDATE RESTRICT, + ADD CONSTRAINT FK_A_E FOREIGN KEY FK_A_E (E_ID) + REFERENCES E (ID) + ON DELETE RESTRICT + ON UPDATE RESTRICT, + ADD CONSTRAINT FK_A_F FOREIGN KEY FK_A_F (F_ID) + REFERENCES F (ID) + ON DELETE RESTRICT; + +/*==============================================================*/ +/* Table : Documents */ +/*==============================================================*/ +create table Documents +( + Document_Id int not null, + Document_Title varchar(32), + Document_Type varchar(32), + Document_PageNumber int, + Document_City varchar(32), + primary key (DOCUMENT_ID) +) TYPE=INNODB; + + + +use NHibernate; + +drop table if exists Users; + +/*==============================================================*/ +/* Table : Users */ +/*==============================================================*/ +create table Users +( + LogonId varchar(20) not null default '0', + Name varchar(40) default null, + Password varchar(20) default null, + EmailAddress varchar(40) default null, + LastLogon datetime default null, + primary key (LogonId) +) TYPE=INNODB; diff --git a/tests/unit/SQLMap/scripts/mysql/account-init.sql b/tests/unit/SQLMap/scripts/mysql/account-init.sql new file mode 100644 index 00000000..51b315f4 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mysql/account-init.sql @@ -0,0 +1,20 @@ +use IBatisNet; + +drop table if exists Accounts; + +create table Accounts +( + Account_Id int not null, + Account_FirstName varchar(32) not null, + Account_LastName varchar(32) not null, + Account_Email varchar(128), + Account_Banner_Option varchar(255), + Account_Cart_Option int, + primary key (Account_Id) +) TYPE=INNODB; + +INSERT INTO Accounts VALUES(1,'Joe', 'Dalton', 'Joe.Dalton@somewhere.com', 'Oui', 200); +INSERT INTO Accounts VALUES(2,'Averel', 'Dalton', 'Averel.Dalton@somewhere.com', 'Oui', 200); +INSERT INTO Accounts VALUES(3,'William', 'Dalton', null, 'Non', 100); +INSERT INTO Accounts VALUES(4,'Jack', 'Dalton', 'Jack.Dalton@somewhere.com', 'Non', 100); +INSERT INTO Accounts VALUES(5,'Gilles', 'Bayon', null, 'Oui', 100); \ No newline at end of file diff --git a/tests/unit/SQLMap/scripts/mysql/account-procedure.sql b/tests/unit/SQLMap/scripts/mysql/account-procedure.sql new file mode 100644 index 00000000..03b65b13 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mysql/account-procedure.sql @@ -0,0 +1,2 @@ + +use IBatisNet; \ No newline at end of file diff --git a/tests/unit/SQLMap/scripts/mysql/category-init.sql b/tests/unit/SQLMap/scripts/mysql/category-init.sql new file mode 100644 index 00000000..2f50ff24 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mysql/category-init.sql @@ -0,0 +1,12 @@ + +use IBatisNet; + +drop table if exists Categories; + +create table Categories +( + Category_Id int not null AUTO_INCREMENT, + Category_Name varchar(32), + Category_Guid varchar(36), + primary key (Category_Id) +) TYPE=INNODB; diff --git a/tests/unit/SQLMap/scripts/mysql/category-procedure.sql b/tests/unit/SQLMap/scripts/mysql/category-procedure.sql new file mode 100644 index 00000000..03b65b13 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mysql/category-procedure.sql @@ -0,0 +1,2 @@ + +use IBatisNet; \ No newline at end of file diff --git a/tests/unit/SQLMap/scripts/mysql/documents-init.sql b/tests/unit/SQLMap/scripts/mysql/documents-init.sql new file mode 100644 index 00000000..c254ae4d --- /dev/null +++ b/tests/unit/SQLMap/scripts/mysql/documents-init.sql @@ -0,0 +1,20 @@ +use IBatisNet; + +drop table if exists Documents; + +create table Documents +( + Document_Id int not null, + Document_Title varchar(32), + Document_Type varchar(32), + Document_PageNumber int, + Document_City varchar(32), + primary key (DOCUMENT_ID) +) TYPE=INNODB; + +INSERT INTO Documents VALUES (1, 'The World of Null-A', 'Book', 55, null); +INSERT INTO Documents VALUES (2, 'Le Progres de Lyon', 'Newspaper', null , 'Lyon'); +INSERT INTO Documents VALUES (3, 'Lord of the Rings', 'Book', 3587, null); +INSERT INTO Documents VALUES (4, 'Le Canard enchaine', 'Tabloid', null , 'Paris'); +INSERT INTO Documents VALUES (5, 'Le Monde', 'Broadsheet', null , 'Paris'); +INSERT INTO Documents VALUES (6, 'Foundation', 'Monograph', 557, null); diff --git a/tests/unit/SQLMap/scripts/mysql/enumeration-init.sql b/tests/unit/SQLMap/scripts/mysql/enumeration-init.sql new file mode 100644 index 00000000..a194b636 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mysql/enumeration-init.sql @@ -0,0 +1,18 @@ + +use IBatisNet; + +drop table if exists Enumerations; + +create table Enumerations +( + Enum_Id int not null, + Enum_Day int not null, + Enum_Color int not null, + Enum_Month int, + primary key (Enum_Id) +) TYPE=INNODB; + +INSERT INTO Enumerations VALUES(1, 1, 1, 128); +INSERT INTO Enumerations VALUES(2, 2, 2, 2048); +INSERT INTO Enumerations VALUES(3, 3, 4, 256); +INSERT INTO Enumerations VALUES(4, 4, 8, null); diff --git a/tests/unit/SQLMap/scripts/mysql/line-item-init.sql b/tests/unit/SQLMap/scripts/mysql/line-item-init.sql new file mode 100644 index 00000000..cb800835 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mysql/line-item-init.sql @@ -0,0 +1,37 @@ + +use IBatisNet; + +drop table if exists LineItems; + +create table LineItems +( + LineItem_Id int not null, + Order_Id int not null, + LineItem_Code varchar(32) not null, + LineItem_Quantity int not null, + LineItem_Price decimal(18,2), + LineItem_Picture blob, + primary key (Order_Id, LineItem_Id) +) TYPE=INNODB; + +INSERT INTO LineItems VALUES (1, 10, 'ESM-34', 1, 45.43, null); +INSERT INTO LineItems VALUES (2, 10, 'QSM-98', 8, 8.40, null); +INSERT INTO LineItems VALUES (1, 9, 'DSM-78', 2, 45.40, null); +INSERT INTO LineItems VALUES (2, 9, 'TSM-12', 2, 32.12, null); +INSERT INTO LineItems VALUES (1, 8, 'DSM-16', 4, 41.30, null); +INSERT INTO LineItems VALUES (2, 8, 'GSM-65', 1, 2.20, null); +INSERT INTO LineItems VALUES (1, 7, 'WSM-27', 7, 52.10, null); +INSERT INTO LineItems VALUES (2, 7, 'ESM-23', 2, 123.34, null); +INSERT INTO LineItems VALUES (1, 6, 'QSM-39', 9, 12.12, null); +INSERT INTO LineItems VALUES (2, 6, 'ASM-45', 6, 78.77, null); +INSERT INTO LineItems VALUES (1, 5, 'ESM-48', 3, 43.87, null); +INSERT INTO LineItems VALUES (2, 5, 'WSM-98', 7, 5.40, null); +INSERT INTO LineItems VALUES (1, 4, 'RSM-57', 2, 78.90, null); +INSERT INTO LineItems VALUES (2, 4, 'XSM-78', 9, 2.34, null); +INSERT INTO LineItems VALUES (1, 3, 'DSM-59', 3, 5.70, null); +INSERT INTO LineItems VALUES (2, 3, 'DSM-53', 3, 98.78, null); +INSERT INTO LineItems VALUES (1, 2, 'DSM-37', 4, 7.80, null); +INSERT INTO LineItems VALUES (2, 2, 'FSM-12', 2, 55.78, null); +INSERT INTO LineItems VALUES (1, 1, 'ESM-48', 8, 87.60, null); +INSERT INTO LineItems VALUES (2, 1, 'ESM-23', 1, 55.40, null); + diff --git a/tests/unit/SQLMap/scripts/mysql/more-account-records.sql b/tests/unit/SQLMap/scripts/mysql/more-account-records.sql new file mode 100644 index 00000000..e9fd4ac2 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mysql/more-account-records.sql @@ -0,0 +1,7 @@ + +INSERT INTO Accounts VALUES(6,'Jane', 'Calamity', 'Jane.Calamity@somewhere.com', 'Oui', 200); +INSERT INTO Accounts VALUES(7,'Lucky', 'Luke', 'Lucky.Luke@somewhere.com', 'Oui', 200); +INSERT INTO Accounts VALUES(8,'Ming', 'Li Foo', null, 'Non', 100); +INSERT INTO Accounts VALUES(9,'O''Hara', 'Steve', 'Jack.OHara@somewhere.com', 'Oui', 200); +INSERT INTO Accounts VALUES(10,'Robert', 'O''Timmins', null, 'Non', 100); + diff --git a/tests/unit/SQLMap/scripts/mysql/order-init.sql b/tests/unit/SQLMap/scripts/mysql/order-init.sql new file mode 100644 index 00000000..e83a4be3 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mysql/order-init.sql @@ -0,0 +1,30 @@ +drop table if exists Orders; + +create table Orders +( + Order_Id int not null, + Account_Id int null, + Order_Date datetime, + Order_CardType varchar(32), + Order_CardNumber varchar(32), + Order_CardExpiry varchar(32), + Order_Street varchar(32), + Order_City varchar(32), + Order_Province varchar(32), + Order_PostalCode varchar(32), + Order_FavouriteLineItem int, + primary key (Order_Id) +) TYPE=INNODB; + +INSERT INTO Orders VALUES (1, 1, '2003-02-15 8:15:00', 'VISA', '999999999999', '05/03', '11 This Street', 'Victoria', 'BC', 'C4B 4F4',2); +INSERT INTO Orders VALUES (2, 4, '2003-02-15 8:15:00', 'MC', '888888888888', '06/03', '222 That Street', 'Edmonton', 'AB', 'X4K 5Y4',1); +INSERT INTO Orders VALUES (3, 3, '2003-02-15 8:15:00', 'AMEX', '777777777777', '07/03', '333 Other Street', 'Regina', 'SK', 'Z4U 6Y4',2); +INSERT INTO Orders VALUES (4, 2, '2003-02-15 8:15:00', 'MC', '666666666666', '08/03', '444 His Street', 'Toronto', 'ON', 'K4U 3S4',1); +INSERT INTO Orders VALUES (5, 5, '2003-02-15 8:15:00', 'VISA', '555555555555', '09/03', '555 Her Street', 'Calgary', 'AB', 'J4J 7S4',2); +INSERT INTO Orders VALUES (6, 5, '2003-02-15 8:15:00', 'VISA', '999999999999', '10/03', '6 Their Street', 'Victoria', 'BC', 'T4H 9G4',1); +INSERT INTO Orders VALUES (7, 4, '2003-02-15 8:15:00', 'MC', '888888888888', '11/03', '77 Lucky Street', 'Edmonton', 'AB', 'R4A 0Z4',2); +INSERT INTO Orders VALUES (8, 3, '2003-02-15 8:15:00', 'AMEX', '777777777777', '12/03', '888 Our Street', 'Regina', 'SK', 'S4S 7G4',1); +INSERT INTO Orders VALUES (9, 2, '2003-02-15 8:15:00', 'MC', '666666666666', '01/04', '999 Your Street', 'Toronto', 'ON', 'G4D 9F4',2); +INSERT INTO Orders VALUES (10, 1, '2003-02-15 8:15:00', 'VISA', '555555555555', '02/04', '99 Some Street', 'Calgary', 'AB', 'W4G 7A4',1); +INSERT INTO Orders VALUES (11, null, '2003-02-15 8:15:00', 'VISA', '555555555555', '02/04', 'Null order', 'Calgary', 'ZZ', 'XXX YYY',1); + diff --git a/tests/unit/SQLMap/scripts/mysql/other-init.sql b/tests/unit/SQLMap/scripts/mysql/other-init.sql new file mode 100644 index 00000000..0281527c --- /dev/null +++ b/tests/unit/SQLMap/scripts/mysql/other-init.sql @@ -0,0 +1,91 @@ + +use IBatisNet; + +drop table if exists Others; +drop table if exists A; +drop table if exists B; +drop table if exists C; +drop table if exists D; +drop table if exists E; +drop table if exists F; + +create table Others +( + Other_Int int, + Other_Long bigint, + Other_Bit bit not null default 0, + Other_String varchar(32) not null +) TYPE=INNODB; + +CREATE TABLE F ( + ID varchar(50) NOT NULL , + F_Libelle varchar(50) NULL , + primary key (ID) +) TYPE=INNODB; + +CREATE TABLE E ( + ID varchar(50) NOT NULL , + E_Libelle varchar(50) NULL , + primary key (ID) +) TYPE=INNODB; + +CREATE TABLE D ( + ID varchar(50) NOT NULL , + D_Libelle varchar(50) NULL , + primary key (ID) +) TYPE=INNODB; + +CREATE TABLE C ( + ID varchar(50) NOT NULL , + C_Libelle varchar(50) NULL , + primary key (ID) +) TYPE=INNODB; + + +CREATE TABLE B ( + ID varchar(50) NOT NULL , + C_ID varchar(50) NULL , + D_ID varchar(50) NULL , + B_Libelle varchar(50) NULL , + primary key (ID) +) TYPE=INNODB; + +ALTER TABLE B ADD CONSTRAINT FK_B_C FOREIGN KEY FK_B_C (C_ID) + REFERENCES C (ID) + ON DELETE RESTRICT + ON UPDATE RESTRICT, + ADD CONSTRAINT FK_B_D FOREIGN KEY FK_B_D (D_ID) + REFERENCES D (ID) + ON DELETE RESTRICT + ON UPDATE RESTRICT; + +CREATE TABLE A ( + ID varchar(50) NOT NULL , + B_ID varchar(50) NULL , + E_ID varchar(50) NULL , + F_ID varchar(50) NULL , + A_Libelle varchar(50) NULL , + primary key (ID) +) TYPE=INNODB; + +ALTER TABLE A ADD CONSTRAINT FK_A_B FOREIGN KEY FK_A_B (B_ID) + REFERENCES B (ID) + ON DELETE RESTRICT + ON UPDATE RESTRICT, + ADD CONSTRAINT FK_A_E FOREIGN KEY FK_A_E (E_ID) + REFERENCES E (ID) + ON DELETE RESTRICT + ON UPDATE RESTRICT, + ADD CONSTRAINT FK_A_F FOREIGN KEY FK_A_F (F_ID) + REFERENCES F (ID) + ON DELETE RESTRICT; + +INSERT INTO Others VALUES(1, 8888888, 0, 'Oui'); +INSERT INTO Others VALUES(2, 9999999999, 1, 'Non'); + +INSERT INTO F VALUES('f', 'fff'); +INSERT INTO E VALUES('e', 'eee'); +INSERT INTO D VALUES('d', 'ddd'); +INSERT INTO C VALUES('c', 'ccc'); +INSERT INTO B VALUES('b', 'c', null, 'bbb'); +INSERT INTO A VALUES('a', 'b', 'e', null, 'aaa'); \ No newline at end of file diff --git a/tests/unit/SQLMap/scripts/mysql/swap-procedure.sql b/tests/unit/SQLMap/scripts/mysql/swap-procedure.sql new file mode 100644 index 00000000..03b65b13 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mysql/swap-procedure.sql @@ -0,0 +1,2 @@ + +use IBatisNet; \ No newline at end of file diff --git a/tests/unit/SQLMap/scripts/mysql/user-init.sql b/tests/unit/SQLMap/scripts/mysql/user-init.sql new file mode 100644 index 00000000..c124fc2b --- /dev/null +++ b/tests/unit/SQLMap/scripts/mysql/user-init.sql @@ -0,0 +1,14 @@ + +use NHibernate; + +drop table if exists Users; + +create table Users +( + LogonId varchar(20) not null default '0', + Name varchar(40) default null, + Password varchar(20) default null, + EmailAddress varchar(40) default null, + LastLogon datetime default null, + primary key (LogonId) +) TYPE=INNODB; diff --git a/tests/unit/SQLMap/scripts/sqlite/database.sql b/tests/unit/SQLMap/scripts/sqlite/database.sql new file mode 100644 index 00000000..477b4b16 --- /dev/null +++ b/tests/unit/SQLMap/scripts/sqlite/database.sql @@ -0,0 +1,242 @@ +# +# : A +# +DROP TABLE A; + +CREATE TABLE A +( + ID VARCHAR(50) NOT NULL PRIMARY KEY, + B_ID VARCHAR(50), + E_ID VARCHAR(50), + F_ID VARCHAR(50), + A_Libelle VARCHAR(50) +); + +INSERT INTO A VALUES ('a', 'b', 'e', NULL, 'aaa'); + + +# +# : Accounts +# +DROP TABLE Accounts; +CREATE TABLE Accounts +( + Account_Id INTEGER NOT NULL PRIMARY KEY, + Account_FirstName VARCHAR(32) NOT NULL, + Account_LastName VARCHAR(32) NOT NULL, + Account_Email VARCHAR(128), + Account_Banner_Option VARCHAR(255), + Account_Cart_Option INT +); + +INSERT INTO Accounts VALUES ('1', 'Joe', 'Dalton', 'Joe.Dalton@somewhere.com', 'Oui', '200'); +INSERT INTO Accounts VALUES ('2', 'Averel', 'Dalton', 'Averel.Dalton@somewhere.com', 'Oui', '200'); +INSERT INTO Accounts VALUES ('3', 'William', 'Dalton', NULL, 'Non', '100'); +INSERT INTO Accounts VALUES ('4', 'Jack', 'Dalton', 'Jack.Dalton@somewhere.com', 'Non', '100'); +INSERT INTO Accounts VALUES ('5', 'Gilles', 'Bayon', NULL, 'Oui', '100'); + + +# +# : B +# +DROP TABLE B; +CREATE TABLE B +( + ID VARCHAR(50) NOT NULL PRIMARY KEY, + C_ID VARCHAR(50), + D_ID VARCHAR(50), + B_Libelle VARCHAR(50) +); + +INSERT INTO B VALUES ('b', 'c', NULL, 'bbb'); + + +# +# : C +# +DROP TABLE C; +CREATE TABLE C +( + ID VARCHAR(50) NOT NULL PRIMARY KEY, + C_Libelle VARCHAR(50) +); + +INSERT INTO C VALUES ('c', 'ccc'); + + +# +# : Categories +# +DROP TABLE Categories; +create table Categories +( + Category_Id INTEGER NOT NULL PRIMARY KEY, + Category_Name varchar(32), + Category_Guid varchar(36) +); + + +# +# : D +# +DROP TABLE D; +CREATE TABLE D +( + ID VARCHAR(50) NOT NULL PRIMARY KEY, + D_Libelle VARCHAR(50) +); + +INSERT INTO D VALUES ('d', 'ddd'); + + +# +# : Documents +# +DROP TABLE Documents; +CREATE TABLE Documents +( + Document_Id INT NOT NULL PRIMARY KEY, + Document_Title VARCHAR(32), + Document_Type VARCHAR(32), + Document_PageNumber INT, + Document_City VARCHAR(32) +); + +INSERT INTO Documents VALUES ('1', 'The World of Null-A', 'Book', '55', NULL); +INSERT INTO Documents VALUES ('2', 'Le Progres de Lyon', 'Newspaper', NULL, 'Lyon'); +INSERT INTO Documents VALUES ('3', 'Lord of the Rings', 'Book', '3587', NULL); +INSERT INTO Documents VALUES ('4', 'Le Canard enchaine', 'Tabloid', NULL, 'Paris'); +INSERT INTO Documents VALUES ('5', 'Le Monde', 'Broadsheet', NULL, 'Paris'); +INSERT INTO Documents VALUES ('6', 'Foundation', 'Monograph', '557', NULL); + + +# +# : E +# +DROP TABLE E; +CREATE TABLE E +( + ID VARCHAR(50) NOT NULL PRIMARY KEY, + E_Libelle VARCHAR(50) +); + + +INSERT INTO E VALUES ('e', 'eee'); + + +# +# : Enumerations +# +DROP TABLE Enumerations; +create table Enumerations +( + Enum_Id int not null, + Enum_Day int not null, + Enum_Color int not null, + Enum_Month int +); + + +INSERT INTO Enumerations VALUES ('1', '1', '1', '128'); +INSERT INTO Enumerations VALUES ('2', '2', '2', '2048'); +INSERT INTO Enumerations VALUES ('3', '3', '4', '256'); +INSERT INTO Enumerations VALUES ('4', '4', '8', NULL); + + +# +# : F +# +DROP TABLE F; +CREATE TABLE F +( + ID VARCHAR(50) NOT NULL PRIMARY KEY, + F_Libelle VARCHAR(50) +); + +INSERT INTO F VALUES ('f', 'fff'); + + +# +# : LineItems +# +DROP TABLE LineItems; +CREATE TABLE LineItems +( + LineItem_Id INTEGER NOT NULL, + Order_Id INT NOT NULL, + LineItem_Code VARCHAR(32) NOT NULL, + LineItem_Quantity INT NOT NULL, + LineItem_Price DECIMAL(18,2), + LineItem_Picture BLOB +); + + +INSERT INTO LineItems VALUES ('1', '10', 'ESM-34', '1', '45.43', NULL); +INSERT INTO LineItems VALUES ('2', '10', 'QSM-98', '8', '8.40', NULL); +INSERT INTO LineItems VALUES ('1', '9', 'DSM-78', '2', '45.40', NULL); +INSERT INTO LineItems VALUES ('2', '9', 'TSM-12', '2', '32.12', NULL); +INSERT INTO LineItems VALUES ('1', '8', 'DSM-16', '4', '41.30', NULL); +INSERT INTO LineItems VALUES ('2', '8', 'GSM-65', '1', '2.20', NULL); +INSERT INTO LineItems VALUES ('1', '7', 'WSM-27', '7', '52.10', NULL); +INSERT INTO LineItems VALUES ('2', '7', 'ESM-23', '2', '123.34', NULL); +INSERT INTO LineItems VALUES ('1', '6', 'QSM-39', '9', '12.12', NULL); +INSERT INTO LineItems VALUES ('2', '6', 'ASM-45', '6', '78.77', NULL); +INSERT INTO LineItems VALUES ('1', '5', 'ESM-48', '3', '43.87', NULL); +INSERT INTO LineItems VALUES ('2', '5', 'WSM-98', '7', '5.40', NULL); +INSERT INTO LineItems VALUES ('1', '4', 'RSM-57', '2', '78.90', NULL); +INSERT INTO LineItems VALUES ('2', '4', 'XSM-78', '9', '2.34', NULL); +INSERT INTO LineItems VALUES ('1', '3', 'DSM-59', '3', '5.70', NULL); +INSERT INTO LineItems VALUES ('2', '3', 'DSM-53', '3', '98.78', NULL); +INSERT INTO LineItems VALUES ('1', '2', 'DSM-37', '4', '7.80', NULL); +INSERT INTO LineItems VALUES ('2', '2', 'FSM-12', '2', '55.78', NULL); +INSERT INTO LineItems VALUES ('1', '1', 'ESM-48', '8', '87.60', NULL); +INSERT INTO LineItems VALUES ('2', '1', 'ESM-23', '1', '55.40', NULL); + + +# +# : Orders +# +DROP TABLE Orders; +CREATE TABLE Orders +( + Order_Id INTEGER NOT NULL PRIMARY KEY, + Account_Id INT, + Order_Date DATETIME, + Order_CardType VARCHAR(32), + Order_CardNumber VARCHAR(32), + Order_CardExpiry VARCHAR(32), + Order_Street VARCHAR(32), + Order_City VARCHAR(32), + Order_Province VARCHAR(32), + Order_PostalCode VARCHAR(32), + Order_FavouriteLineItem INT +); + +INSERT INTO Orders VALUES ('1', '1', '2003-02-15 8:15:00', 'VISA', '999999999999', '05/03', '11 This Street', 'Victoria', 'BC', 'C4B 4F4', '2'); +INSERT INTO Orders VALUES ('2', '4', '2003-02-15 8:15:00', 'MC', '888888888888', '06/03', '222 That Street', 'Edmonton', 'AB', 'X4K 5Y4', '1'); +INSERT INTO Orders VALUES ('3', '3', '2003-02-15 8:15:00', 'AMEX', '777777777777', '07/03', '333 Other Street', 'Regina', 'SK', 'Z4U 6Y4', '2'); +INSERT INTO Orders VALUES ('4', '2', '2003-02-15 8:15:00', 'MC', '666666666666', '08/03', '444 His Street', 'Toronto', 'ON', 'K4U 3S4', '1'); +INSERT INTO Orders VALUES ('5', '5', '2003-02-15 8:15:00', 'VISA', '555555555555', '09/03', '555 Her Street', 'Calgary', 'AB', 'J4J 7S4', '2'); +INSERT INTO Orders VALUES ('6', '5', '2003-02-15 8:15:00', 'VISA', '999999999999', '10/03', '6 Their Street', 'Victoria', 'BC', 'T4H 9G4', '1'); +INSERT INTO Orders VALUES ('7', '4', '2003-02-15 8:15:00', 'MC', '888888888888', '11/03', '77 Lucky Street', 'Edmonton', 'AB', 'R4A 0Z4', '2'); +INSERT INTO Orders VALUES ('8', '3', '2003-02-15 8:15:00', 'AMEX', '777777777777', '12/03', '888 Our Street', 'Regina', 'SK', 'S4S 7G4', '1'); +INSERT INTO Orders VALUES ('9', '2', '2003-02-15 8:15:00', 'MC', '666666666666', '01/04', '999 Your Street', 'Toronto', 'ON', 'G4D 9F4', '2'); +INSERT INTO Orders VALUES ('10', '1', '2003-02-15 8:15:00', 'VISA', '555555555555', '02/04', '99 Some Street', 'Calgary', 'AB', 'W4G 7A4', '1'); +INSERT INTO Orders VALUES ('11', NULL, '2003-02-15 8:15:00', 'VISA', '555555555555', '02/04', 'Null order', 'Calgary', 'ZZ', 'XXX YYY', '1'); + + +# +# : Others +# +DROP TABLE Others; +create table Others +( + Other_Int int, + Other_Long bigint, + Other_Bit bit not null default 0, + Other_String varchar(32) not null +); + +INSERT INTO Others VALUES ('1', '8888888', '0', 'Oui'); +INSERT INTO Others VALUES ('2', '9999999999', '1', 'Non'); + -- cgit v1.2.3