summaryrefslogtreecommitdiff
path: root/tests/unit/SQLMap/scripts/mysql/DataBase.sql
blob: b6542cdbff8cd2f6ee8ff3f6252f690d943ae1da (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
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;