-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathpdb_saas_01.sql
More file actions
172 lines (157 loc) · 10.1 KB
/
pdb_saas_01.sql
File metadata and controls
172 lines (157 loc) · 10.1 KB
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
use master;
begin
declare @sql nvarchar(max);
select @sql = coalesce(@sql,'') + 'kill ' + convert(varchar, spid) + ';'
from master..sysprocesses
where dbid in (db_id('SaaSGate'),db_id('AtlanticDB'),db_id('CaribbeanDB'),db_id('MonacoDB')) and cmd = 'AWAITING COMMAND' and spid <> @@spid;
exec(@sql);
end;
go
if db_id('SaaSGate') is not null drop database SaaSGate;
if db_id('AtlanticDB') is not null drop database AtlanticDB;
if db_id('CaribbeanDB') is not null drop database CaribbeanDB;
if db_id('MonacoDB') is not null drop database MonacoDB;
create database AtlanticDB;
create database CaribbeanDB;
create database MonacoDB;
use PdbLogic;
exec Pdbinstall 'SaaSGate',@ColumnName='BrandId';
go
use SaaSGate;
exec PdbcreatePartition 'SaaSGate','AtlanticDB',1;
exec PdbcreatePartition 'SaaSGate','CaribbeanDB',2;
exec PdbcreatePartition 'SaaSGate','MonacoDB',3;
create table Brands
( BrandId PartitionDBType not null primary key
, Name nvarchar(128) not null unique
);
create table Games
( BrandId PartitionDBType not null references Brands (BrandId)
, Id tinyint not null primary key
, Name nvarchar(128) not null unique
, Rules nvarchar(500)
);
create table Dealers
( BrandId PartitionDBType not null references Brands (BrandId)
, Id smallint identity(1,1) not null primary key
, DealerNumber nvarchar(16) not null unique
, FirstName nvarchar(128) not null
, LastName nvarchar(128) not null
);
create table Players
( BrandId PartitionDBType not null references Brands (BrandId)
, Id smallint identity(1,1) not null primary key
, PlayerNumber nvarchar(16) not null unique
, FirstName nvarchar(128) not null
, LastName nvarchar(128) not null
, EMail nvarchar(128)
, PhoneNumber nvarchar(64)
, Country nvarchar(2)
, City nvarchar(128)
, Address nvarchar(256)
, PostalCode nvarchar(8)
, Birthdate date
, NationalNumber nvarchar(16)
, ChipsPurchased smallint not null
, ChipsBet smallint not null
, ChipsWon smallint not null
, ChipsCashed smallint not null
);
create table Tables
( BrandId PartitionDBType not null references Brands (BrandId)
, Id smallint identity(1,1) not null primary key
, TableNumber nvarchar(16) not null unique
, GameId tinyint not null references Games (Id)
, NextRoundDate smalldatetime
, MinimumPlayers tinyint
, MaximumPlayers tinyint
, MinimumPlayerChips smallint
, MinimumTableChips smallint
, MinimumRoundChips smallint
);
create table Rounds
( BrandId PartitionDBType not null references Brands (BrandId)
, Id bigint identity(1,1) not null primary key
, TableId smallint not null references Tables (Id)
, DealerId smallint not null references Dealers (Id)
, WinValue nvarchar(512)
);
create table Bets
( BrandId PartitionDBType not null references Brands (BrandId)
, Id bigint identity(1,1) not null primary key
, RoundId bigint not null references Rounds (Id)
, PlayerId smallint not null references Players (Id)
, Value nvarchar(512) not null
, ChipsBet smallint not null
);
create table Wins
( BrandId PartitionDBType not null references Brands (BrandId)
, Id bigint identity(1,1) not null primary key
, BetId bigint not null references Bets (Id)
, ChipsWon smallint not null
);
insert into PdbBrands (BrandId,Name) values (1,'Atlantic');
insert into PdbBrands (BrandId,Name) values (2,'Caribbean');
insert into PdbBrands (BrandId,Name) values (3,'Monaco');
insert into PdbGames (Id,Name) values (1,'Black Jack');
insert into PdbGames (Id,Name) values (2,'Texas Holdem');
insert into PdbGames (Id,Name) values (3,'Roulette');
insert into PdbGames (Id,Name) values (4,'Spades');
insert into PdbGames (Id,Name) values (5,'Craps');
insert into PdbDealers (BrandId,DealerNumber,FirstName,LastName) values (1,'00001','Jessica','Rabbit');
insert into PdbDealers (BrandId,DealerNumber,FirstName,LastName) values (1,'00002','Betty','Rubble');
insert into PdbDealers (BrandId,DealerNumber,FirstName,LastName) values (1,'00003','Lois','Griffin');
insert into PdbDealers (BrandId,DealerNumber,FirstName,LastName) values (1,'00004','Lola','Bunny');
insert into PdbDealers (BrandId,DealerNumber,FirstName,LastName) values (2,'00005','Daisy','Duck');
insert into PdbDealers (BrandId,DealerNumber,FirstName,LastName) values (2,'00006','Tinker','Bell');
insert into PdbDealers (BrandId,DealerNumber,FirstName,LastName) values (2,'00007','Lara','Croft');
insert into PdbDealers (BrandId,DealerNumber,FirstName,LastName) values (2,'00008','Betty','Boop');
insert into PdbDealers (BrandId,DealerNumber,FirstName,LastName) values (3,'00009','Aeon','Flux');
insert into PdbDealers (BrandId,DealerNumber,FirstName,LastName) values (3,'00010','Harley','Quinn');
insert into PdbDealers (BrandId,DealerNumber,FirstName,LastName) values (3,'00011','Princess','Jasmine');
insert into PdbDealers (BrandId,DealerNumber,FirstName,LastName) values (3,'00012','Marge','Simpson');
insert into PdbPlayers (BrandId,PlayerNumber,FirstName,LastName,ChipsPurchased,ChipsBet,ChipsWon,ChipsCashed) values (1,'00001','Daniel','Negreanu',0,0,0,0);
insert into PdbPlayers (BrandId,PlayerNumber,FirstName,LastName,ChipsPurchased,ChipsBet,ChipsWon,ChipsCashed) values (1,'00002','Jack','McClelland',0,0,0,0);
insert into PdbPlayers (BrandId,PlayerNumber,FirstName,LastName,ChipsPurchased,ChipsBet,ChipsWon,ChipsCashed) values (1,'00003','Tom','McEvoy',0,0,0,0);
insert into PdbPlayers (BrandId,PlayerNumber,FirstName,LastName,ChipsPurchased,ChipsBet,ChipsWon,ChipsCashed) values (1,'00004','Bryan','Roberts',0,0,0,0);
insert into PdbPlayers (BrandId,PlayerNumber,FirstName,LastName,ChipsPurchased,ChipsBet,ChipsWon,ChipsCashed) values (1,'00005','Eric','Drache',0,0,0,0);
insert into PdbPlayers (BrandId,PlayerNumber,FirstName,LastName,ChipsPurchased,ChipsBet,ChipsWon,ChipsCashed) values (1,'00006','Barry','Greenstein',0,0,0,0);
insert into PdbPlayers (BrandId,PlayerNumber,FirstName,LastName,ChipsPurchased,ChipsBet,ChipsWon,ChipsCashed) values (1,'00007','Erik','Seidel',0,0,0,0);
insert into PdbPlayers (BrandId,PlayerNumber,FirstName,LastName,ChipsPurchased,ChipsBet,ChipsWon,ChipsCashed) values (2,'00008','Dan','Harrington',0,0,0,0);
insert into PdbPlayers (BrandId,PlayerNumber,FirstName,LastName,ChipsPurchased,ChipsBet,ChipsWon,ChipsCashed) values (2,'00009','Mike','Sexton',0,0,0,0);
insert into PdbPlayers (BrandId,PlayerNumber,FirstName,LastName,ChipsPurchased,ChipsBet,ChipsWon,ChipsCashed) values (2,'00010','Henry','Orenstein',0,0,0,0);
insert into PdbPlayers (BrandId,PlayerNumber,FirstName,LastName,ChipsPurchased,ChipsBet,ChipsWon,ChipsCashed) values (2,'00011','Dewey','Tomko',0,0,0,0);
insert into PdbPlayers (BrandId,PlayerNumber,FirstName,LastName,ChipsPurchased,ChipsBet,ChipsWon,ChipsCashed) values (2,'00012','Phil','Hellmuth',0,0,0,0);
insert into PdbPlayers (BrandId,PlayerNumber,FirstName,LastName,ChipsPurchased,ChipsBet,ChipsWon,ChipsCashed) values (2,'00013','Billy','Baxter',0,0,0,0);
insert into PdbPlayers (BrandId,PlayerNumber,FirstName,LastName,ChipsPurchased,ChipsBet,ChipsWon,ChipsCashed) values (2,'00014','Jack','Binion',0,0,0,0);
insert into PdbPlayers (BrandId,PlayerNumber,FirstName,LastName,ChipsPurchased,ChipsBet,ChipsWon,ChipsCashed) values (3,'00015','Berry','Johnston',0,0,0,0);
insert into PdbPlayers (BrandId,PlayerNumber,FirstName,LastName,ChipsPurchased,ChipsBet,ChipsWon,ChipsCashed) values (3,'00016','Bobby','Baldwin',0,0,0,0);
insert into PdbPlayers (BrandId,PlayerNumber,FirstName,LastName,ChipsPurchased,ChipsBet,ChipsWon,ChipsCashed) values (3,'00017','Johnny','Chan',0,0,0,0);
insert into PdbPlayers (BrandId,PlayerNumber,FirstName,LastName,ChipsPurchased,ChipsBet,ChipsWon,ChipsCashed) values (3,'00018','Lyle','Berman',0,0,0,0);
insert into PdbPlayers (BrandId,PlayerNumber,FirstName,LastName,ChipsPurchased,ChipsBet,ChipsWon,ChipsCashed) values (3,'00019','Roger','Moore',0,0,0,0);
insert into PdbPlayers (BrandId,PlayerNumber,FirstName,LastName,ChipsPurchased,ChipsBet,ChipsWon,ChipsCashed) values (3,'00020','Jack','Keller',0,0,0,0);
insert into PdbPlayers (BrandId,PlayerNumber,FirstName,LastName,ChipsPurchased,ChipsBet,ChipsWon,ChipsCashed) values (3,'00021','Thomas','Preston',0,0,0,0);
insert into PdbTables (BrandId,TableNumber,GameId) values (1,'001-0001-00001',1);
insert into PdbTables (BrandId,TableNumber,GameId) values (1,'001-0001-00002',1);
insert into PdbTables (BrandId,TableNumber,GameId) values (1,'001-0002-00003',2);
insert into PdbTables (BrandId,TableNumber,GameId) values (1,'001-0002-00004',2);
insert into PdbTables (BrandId,TableNumber,GameId) values (1,'001-0003-00005',3);
insert into PdbTables (BrandId,TableNumber,GameId) values (1,'001-0003-00006',3);
insert into PdbTables (BrandId,TableNumber,GameId) values (1,'001-0004-00007',4);
insert into PdbTables (BrandId,TableNumber,GameId) values (1,'001-0005-00008',5);
insert into PdbTables (BrandId,TableNumber,GameId) values (2,'002-0001-00009',1);
insert into PdbTables (BrandId,TableNumber,GameId) values (2,'002-0001-00010',1);
insert into PdbTables (BrandId,TableNumber,GameId) values (2,'002-0002-00011',2);
insert into PdbTables (BrandId,TableNumber,GameId) values (2,'002-0002-00012',2);
insert into PdbTables (BrandId,TableNumber,GameId) values (2,'002-0003-00013',3);
insert into PdbTables (BrandId,TableNumber,GameId) values (2,'002-0003-00014',3);
insert into PdbTables (BrandId,TableNumber,GameId) values (2,'002-0004-00015',4);
insert into PdbTables (BrandId,TableNumber,GameId) values (2,'002-0005-00016',5);
insert into PdbTables (BrandId,TableNumber,GameId) values (3,'003-0001-00017',1);
insert into PdbTables (BrandId,TableNumber,GameId) values (3,'003-0001-00018',1);
insert into PdbTables (BrandId,TableNumber,GameId) values (3,'003-0002-00019',2);
insert into PdbTables (BrandId,TableNumber,GameId) values (3,'003-0002-00020',2);
insert into PdbTables (BrandId,TableNumber,GameId) values (3,'003-0003-00021',3);
insert into PdbTables (BrandId,TableNumber,GameId) values (3,'003-0003-00022',3);
insert into PdbTables (BrandId,TableNumber,GameId) values (3,'003-0004-00023',4);
insert into PdbTables (BrandId,TableNumber,GameId) values (3,'003-0005-00024',5);