forked from tpolyak/Sharp-Architecture
/
UnitTestGeneratedSchema.sql
152 lines (112 loc) · 5.41 KB
/
UnitTestGeneratedSchema.sql
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
if exists (select 1 from sys.objects where object_id = OBJECT_ID(N'[FK_Messages_For_Users]') AND parent_object_id = OBJECT_ID('Messages'))
alter table Messages drop constraint FK_Messages_For_Users
if exists (select 1 from sys.objects where object_id = OBJECT_ID(N'[FK_PaymentSchedules_For_Accounts]') AND parent_object_id = OBJECT_ID('PaymentSchedules'))
alter table PaymentSchedules drop constraint FK_PaymentSchedules_For_Accounts
if exists (select 1 from sys.objects where object_id = OBJECT_ID(N'[FK_Transactions_For_Accounts]') AND parent_object_id = OBJECT_ID('Transactions'))
alter table Transactions drop constraint FK_Transactions_For_Accounts
if exists (select 1 from sys.objects where object_id = OBJECT_ID(N'[FK_Children_Join_Users]') AND parent_object_id = OBJECT_ID('Children'))
alter table Children drop constraint FK_Children_Join_Users
if exists (select 1 from sys.objects where object_id = OBJECT_ID(N'[FK_Children_Ref_Accounts]') AND parent_object_id = OBJECT_ID('Children'))
alter table Children drop constraint FK_Children_Ref_Accounts
if exists (select 1 from sys.objects where object_id = OBJECT_ID(N'[FK_Childrens_For_Parents]') AND parent_object_id = OBJECT_ID('Children'))
alter table Children drop constraint FK_Childrens_For_Parents
if exists (select 1 from sys.objects where object_id = OBJECT_ID(N'[FK_Parents_Join_Users]') AND parent_object_id = OBJECT_ID('Parents'))
alter table Parents drop constraint FK_Parents_Join_Users
if exists (select * from dbo.sysobjects where id = object_id(N'Accounts') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table Accounts
if exists (select * from dbo.sysobjects where id = object_id(N'Announcements') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table Announcements
if exists (select * from dbo.sysobjects where id = object_id(N'Messages') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table Messages
if exists (select * from dbo.sysobjects where id = object_id(N'PaymentSchedules') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table PaymentSchedules
if exists (select * from dbo.sysobjects where id = object_id(N'Transactions') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table Transactions
if exists (select * from dbo.sysobjects where id = object_id(N'Users') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table Users
if exists (select * from dbo.sysobjects where id = object_id(N'Children') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table Children
if exists (select * from dbo.sysobjects where id = object_id(N'Parents') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table Parents
if exists (select * from dbo.sysobjects where id = object_id(N'hibernate_unique_key') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table hibernate_unique_key
create table Accounts (
AccountId INT not null,
OldTransactionsBalance DECIMAL(19,5) null,
primary key (AccountId)
)
create table Announcements (
AnnouncementId INT not null,
LastModifiedUtc DATETIME null,
Date DATETIME null,
Title NVARCHAR(255) null,
Content NVARCHAR(255) null,
primary key (AnnouncementId)
)
create table Messages (
MessageId INT not null,
Date DATETIME null,
Text NVARCHAR(255) null,
HasBeenRead BIT null,
UserId INT null,
primary key (MessageId)
)
create table PaymentSchedules (
PaymentScheduleId INT not null,
NextRun DATETIME null,
Interval NVARCHAR(255) null,
Amount DECIMAL(19,5) null,
Description NVARCHAR(255) null,
AccountId INT null,
primary key (PaymentScheduleId)
)
create table Transactions (
TransactionId INT not null,
Description NVARCHAR(255) null,
Amount DECIMAL(19,5) null,
Date DATETIME null,
AccountId INT null,
primary key (TransactionId)
)
create table Users (
UserId INT not null,
Name NVARCHAR(255) null,
UserName NVARCHAR(255) null,
Password NVARCHAR(255) null,
IsActive BIT null,
primary key (UserId)
)
create table Children (
UserId INT not null,
ParentId INT null,
AccountId INT null,
primary key (UserId)
)
create table Parents (
UserId INT not null,
ActivationKey NVARCHAR(255) null,
primary key (UserId)
)
alter table Messages
add constraint FK_Messages_For_Users
foreign key (UserId)
references Users
alter table PaymentSchedules
add constraint FK_PaymentSchedules_For_Accounts
foreign key (AccountId)
references Accounts
alter table Transactions
add constraint FK_Transactions_For_Accounts
foreign key (AccountId)
references Accounts
alter table Children
add constraint FK_Children_Join_Users
foreign key (UserId)
references Users
alter table Children
add constraint FK_Children_Ref_Accounts
foreign key (AccountId)
references Accounts
alter table Children
add constraint FK_Childrens_For_Parents
foreign key (ParentId)
references Parents
alter table Parents
add constraint FK_Parents_Join_Users
foreign key (UserId)
references Users
create table hibernate_unique_key (
next_hi INT
)
insert into hibernate_unique_key values ( 1 )