· 7 years ago · Sep 26, 2018, 01:58 PM
1USE [Telligent]
2GO
3/****** Object: Schema [sd] Script Date: 2012.08.23 16:29:37 ******/
4CREATE SCHEMA [sd]
5GO
6/****** Object: StoredProcedure [sd].[spGetPlayer] Script Date: 2012.08.23 16:29:37 ******/
7SET ANSI_NULLS ON
8GO
9SET QUOTED_IDENTIFIER ON
10GO
11CREATE PROCEDURE [sd].[spGetPlayer]
12 @UserID int
13AS
14
15IF NOT EXISTS (SELECT * FROM [sd].[Players] WHERE UserID = @UserID)
16 INSERT INTO sd.Players(UserID, Number, Height, Weight, Class, BaseScore, HeatScore, HighSchoolID, PlayerPositionID)
17 VALUES
18 (
19 @UserID,
20 sd.fGetRandomInt(0, 100),
21 sd.fGetRandomInt(65, 80),
22 sd.fGetRandomInt(130, 260),
23 sd.fGetRandomInt(2010, 2013),
24 sd.fGetRandomInt(0, 100),
25 sd.fGetRandomInt(0, 100),
26 sd.fGetRandomInt(1, 10),
27 sd.fGetRandomInt(1, 14)
28 )
29
30SELECT
31 p.Number,
32 p.Height,
33 p.Weight,
34 p.Class,
35 p.BaseScore,
36 p.HeatScore,
37 hs.Name AS HighSchool,
38 s.Name AS HighSchoolState,
39 pp.Position
40FROM
41 sd.Players p
42 LEFT OUTER JOIN sd.PlayerPositions pp ON p.PlayerPositionID = pp.ID
43 LEFT OUTER JOIN sd.HighSchools hs ON p.HighSchoolID = hs.ID
44 LEFT OUTER JOIN sd.States s ON hs.State = s.State
45WHERE
46 p.UserID = @UserID
47GO
48/****** Object: StoredProcedure [sd].[spSaveClientDevice] Script Date: 2012.08.23 16:29:37 ******/
49SET ANSI_NULLS ON
50GO
51SET QUOTED_IDENTIFIER ON
52GO
53CREATE PROCEDURE [sd].[spSaveClientDevice]
54 @UserID int,
55 @DeviceName nvarchar(256),
56 @DeviceOS varchar(50),
57 @DeviceOSVersion varchar(50),
58 @DeviceToken varchar(256),
59 @Enabled bit = 1
60AS
61
62IF NOT EXISTS (SELECT * FROM [sd].[ClientDevices] WHERE DeviceToken = @DeviceToken)
63
64 INSERT INTO [sd].[ClientDevices] (UserID, DeviceName, DeviceOS, DeviceOSVersion, DeviceToken, [Enabled])
65 VALUES (@UserID, @DeviceName, @DeviceOS, @DeviceOSVersion, @DeviceToken, @Enabled)
66GO
67/****** Object: UserDefinedFunction [sd].[fGetRandomInt] Script Date: 2012.08.23 16:29:37 ******/
68SET ANSI_NULLS ON
69GO
70SET QUOTED_IDENTIFIER ON
71GO
72CREATE FUNCTION [sd].[fGetRandomInt]
73(
74 @Min int,
75 @Max int
76)
77RETURNS INT
78AS
79BEGIN
80 RETURN CONVERT(INT, @Min + (SELECT RandomNumber FROM sd.vRandomNumber) * (@Max - @Min))
81END
82GO
83/****** Object: Table [sd].[ClientDevices] Script Date: 2012.08.23 16:29:37 ******/
84SET ANSI_NULLS ON
85GO
86SET QUOTED_IDENTIFIER ON
87GO
88SET ANSI_PADDING ON
89GO
90CREATE TABLE [sd].[ClientDevices](
91 [ID] [int] IDENTITY(1,1) NOT NULL,
92 [UserID] [int] NOT NULL,
93 [DeviceName] [nvarchar](256) NOT NULL,
94 [DeviceOS] [varchar](50) NOT NULL,
95 [DeviceOSVersion] [varchar](50) NULL,
96 [DeviceToken] [varchar](256) NOT NULL,
97 [Enabled] [bit] NOT NULL,
98 CONSTRAINT [PK_ClientDevice] PRIMARY KEY CLUSTERED
99(
100 [ID] ASC
101)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
102) ON [PRIMARY]
103
104GO
105SET ANSI_PADDING ON
106GO
107/****** Object: Table [sd].[Conferences] Script Date: 2012.08.23 16:29:37 ******/
108SET ANSI_NULLS ON
109GO
110SET QUOTED_IDENTIFIER ON
111GO
112CREATE TABLE [sd].[Conferences](
113 [ID] [int] NOT NULL,
114 [Name] [nvarchar](128) NOT NULL,
115 CONSTRAINT [PK_Conference] PRIMARY KEY CLUSTERED
116(
117 [ID] ASC
118)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
119) ON [PRIMARY]
120
121GO
122/****** Object: Table [sd].[HighSchools] Script Date: 2012.08.23 16:29:37 ******/
123SET ANSI_NULLS ON
124GO
125SET QUOTED_IDENTIFIER ON
126GO
127SET ANSI_PADDING ON
128GO
129CREATE TABLE [sd].[HighSchools](
130 [ID] [int] IDENTITY(1,1) NOT NULL,
131 [UserID] [int] NULL,
132 [Name] [nvarchar](128) NOT NULL,
133 [State] [char](2) NOT NULL,
134 CONSTRAINT [PK_HighSchool] PRIMARY KEY CLUSTERED
135(
136 [ID] ASC
137)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
138) ON [PRIMARY]
139
140GO
141SET ANSI_PADDING ON
142GO
143/****** Object: Table [sd].[MessageQueue] Script Date: 2012.08.23 16:29:37 ******/
144SET ANSI_NULLS ON
145GO
146SET QUOTED_IDENTIFIER ON
147GO
148CREATE TABLE [sd].[MessageQueue](
149 [ConversationId] [uniqueidentifier] NOT NULL,
150 [MessageId] [uniqueidentifier] NOT NULL,
151 [Created] [datetime2](7) NOT NULL
152) ON [PRIMARY]
153
154GO
155/****** Object: Table [sd].[PlayerPositions] Script Date: 2012.08.23 16:29:37 ******/
156SET ANSI_NULLS ON
157GO
158SET QUOTED_IDENTIFIER ON
159GO
160SET ANSI_PADDING ON
161GO
162CREATE TABLE [sd].[PlayerPositions](
163 [ID] [int] NOT NULL,
164 [Position] [varchar](10) NOT NULL,
165 [Name] [varchar](50) NOT NULL,
166 [Type] [char](1) NOT NULL,
167 CONSTRAINT [PK_PlayerPosition] PRIMARY KEY CLUSTERED
168(
169 [ID] ASC
170)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
171) ON [PRIMARY]
172
173GO
174SET ANSI_PADDING ON
175GO
176/****** Object: Table [sd].[Players] Script Date: 2012.08.23 16:29:37 ******/
177SET ANSI_NULLS ON
178GO
179SET QUOTED_IDENTIFIER ON
180GO
181CREATE TABLE [sd].[Players](
182 [ID] [int] IDENTITY(1,1) NOT NULL,
183 [UserID] [int] NOT NULL,
184 [Number] [int] NULL,
185 [Height] [int] NULL,
186 [Weight] [int] NULL,
187 [Class] [int] NULL,
188 [BaseScore] [decimal](3, 1) NULL,
189 [HeatScore] [decimal](3, 1) NULL,
190 [HighSchoolID] [int] NULL,
191 [PlayerPositionID] [int] NULL,
192 CONSTRAINT [PK_Player] PRIMARY KEY CLUSTERED
193(
194 [ID] ASC
195)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
196) ON [PRIMARY]
197
198GO
199/****** Object: Table [sd].[States] Script Date: 2012.08.23 16:29:37 ******/
200SET ANSI_NULLS ON
201GO
202SET QUOTED_IDENTIFIER ON
203GO
204SET ANSI_PADDING ON
205GO
206CREATE TABLE [sd].[States](
207 [State] [char](2) NOT NULL,
208 [Name] [varchar](20) NOT NULL,
209 CONSTRAINT [PK_State] PRIMARY KEY CLUSTERED
210(
211 [State] ASC
212)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
213) ON [PRIMARY]
214
215GO
216SET ANSI_PADDING ON
217GO
218/****** Object: View [sd].[vMessageQueue] Script Date: 2012.08.23 16:29:37 ******/
219SET ANSI_NULLS ON
220GO
221SET QUOTED_IDENTIFIER ON
222GO
223CREATE VIEW [sd].[vMessageQueue]
224 AS
225
226SELECT
227 u.UserName AS Author,
228 mm.Body AS MessageBody,
229 (
230 select count(*)
231 from dbo.cs_Messaging_ConversationParticipants mcp
232 where mcp.ParticipantId = cp.ParticipantId and mcp.IsRead = 0
233 ) AS UnreadConversations,
234 cd.DeviceToken
235FROM
236 sd.MessageQueue mq,
237 dbo.cs_Messaging_Messages mm,
238 dbo.cs_Messaging_ConversationParticipants cp,
239 sd.ClientDevices cd,
240 dbo.cs_Users u
241WHERE
242 mq.MessageId = mm.MessageId AND
243 mq.ConversationId = cp.ConversationId AND
244 cp.IsRead = 0 AND
245 cp.ParticipantId = cd.UserID AND
246 mm.AuthorId = u.UserID
247GO
248/****** Object: View [sd].[vRandomNumber] Script Date: 2012.08.23 16:29:37 ******/
249SET ANSI_NULLS ON
250GO
251SET QUOTED_IDENTIFIER ON
252GO
253CREATE VIEW [sd].[vRandomNumber]
254 AS SELECT RAND() AS RandomNumber
255GO
256ALTER TABLE [sd].[MessageQueue] ADD DEFAULT (getdate()) FOR [Created]
257GO
258ALTER TABLE [sd].[ClientDevices] WITH CHECK ADD CONSTRAINT [FK_ClientDeviceUser] FOREIGN KEY([UserID])
259REFERENCES [dbo].[cs_Users] ([UserID])
260ON DELETE CASCADE
261GO
262ALTER TABLE [sd].[ClientDevices] CHECK CONSTRAINT [FK_ClientDeviceUser]
263GO
264ALTER TABLE [sd].[HighSchools] WITH CHECK ADD CONSTRAINT [FK_HighSchoolState] FOREIGN KEY([State])
265REFERENCES [sd].[States] ([State])
266ON DELETE CASCADE
267GO
268ALTER TABLE [sd].[HighSchools] CHECK CONSTRAINT [FK_HighSchoolState]
269GO
270ALTER TABLE [sd].[HighSchools] WITH CHECK ADD CONSTRAINT [FK_HighSchoolUser] FOREIGN KEY([UserID])
271REFERENCES [dbo].[cs_Users] ([UserID])
272ON DELETE CASCADE
273GO
274ALTER TABLE [sd].[HighSchools] CHECK CONSTRAINT [FK_HighSchoolUser]
275GO
276ALTER TABLE [sd].[Players] WITH CHECK ADD CONSTRAINT [FK_PlayerHighSchool] FOREIGN KEY([HighSchoolID])
277REFERENCES [sd].[HighSchools] ([ID])
278ON DELETE CASCADE
279GO
280ALTER TABLE [sd].[Players] CHECK CONSTRAINT [FK_PlayerHighSchool]
281GO
282ALTER TABLE [sd].[Players] WITH NOCHECK ADD CONSTRAINT [FK_PlayerPosition] FOREIGN KEY([PlayerPositionID])
283REFERENCES [sd].[PlayerPositions] ([ID])
284ON DELETE CASCADE
285GO
286ALTER TABLE [sd].[Players] CHECK CONSTRAINT [FK_PlayerPosition]
287GO
288ALTER TABLE [sd].[Players] WITH CHECK ADD CONSTRAINT [FK_PlayerUser] FOREIGN KEY([UserID])
289REFERENCES [dbo].[cs_Users] ([UserID])
290GO
291ALTER TABLE [sd].[Players] CHECK CONSTRAINT [FK_PlayerUser]
292GO