· 7 years ago · Sep 09, 2018, 05:24 PM
1/*
2Deployment script for Chats_1
3
4This code was generated by a tool.
5Changes to this file may cause incorrect behavior and will be lost if
6the code is regenerated.
7*/
8
9GO
10SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
11
12SET NUMERIC_ROUNDABORT OFF;
13
14
15GO
16:setvar DatabaseName "ChatsSample"
17:setvar DefaultFilePrefix "ChatsSample"
18:setvar DefaultDataPath ""
19:setvar DefaultLogPath ""
20
21GO
22:on error exit
23GO
24/*
25Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
26To re-enable the script after enabling SQLCMD mode, execute the following:
27SET NOEXEC OFF;
28*/
29:setvar __IsSqlCmdEnabled "True"
30GO
31IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
32 BEGIN
33 PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
34 SET NOEXEC ON;
35 END
36
37
38GO
39USE [master];
40
41
42GO
43
44IF (DB_ID(N'$(DatabaseName)') IS NOT NULL)
45BEGIN
46 ALTER DATABASE [$(DatabaseName)]
47 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
48 DROP DATABASE [$(DatabaseName)];
49END
50
51GO
52PRINT N'Creating $(DatabaseName)...'
53GO
54CREATE DATABASE [$(DatabaseName)] COLLATE SQL_Latin1_General_CP1_CI_AS
55GO
56USE [$(DatabaseName)];
57
58
59GO
60IF EXISTS (SELECT 1
61 FROM [master].[dbo].[sysdatabases]
62 WHERE [name] = N'$(DatabaseName)')
63 BEGIN
64 ALTER DATABASE [$(DatabaseName)]
65 SET ANSI_NULLS ON,
66 ANSI_PADDING ON,
67 ANSI_WARNINGS ON,
68 ARITHABORT ON,
69 CONCAT_NULL_YIELDS_NULL ON,
70 NUMERIC_ROUNDABORT OFF,
71 QUOTED_IDENTIFIER ON,
72 ANSI_NULL_DEFAULT ON,
73 CURSOR_DEFAULT LOCAL,
74 RECOVERY FULL,
75 CURSOR_CLOSE_ON_COMMIT OFF,
76 AUTO_CREATE_STATISTICS ON,
77 AUTO_SHRINK OFF,
78 AUTO_UPDATE_STATISTICS ON,
79 RECURSIVE_TRIGGERS OFF
80 WITH ROLLBACK IMMEDIATE;
81 ALTER DATABASE [$(DatabaseName)]
82 SET AUTO_CLOSE OFF
83 WITH ROLLBACK IMMEDIATE;
84 END
85
86
87GO
88IF EXISTS (SELECT 1
89 FROM [master].[dbo].[sysdatabases]
90 WHERE [name] = N'$(DatabaseName)')
91 BEGIN
92 ALTER DATABASE [$(DatabaseName)]
93 SET ALLOW_SNAPSHOT_ISOLATION OFF;
94 END
95
96
97GO
98IF EXISTS (SELECT 1
99 FROM [master].[dbo].[sysdatabases]
100 WHERE [name] = N'$(DatabaseName)')
101 BEGIN
102 ALTER DATABASE [$(DatabaseName)]
103 SET READ_COMMITTED_SNAPSHOT OFF
104 WITH ROLLBACK IMMEDIATE;
105 END
106
107
108GO
109IF EXISTS (SELECT 1
110 FROM [master].[dbo].[sysdatabases]
111 WHERE [name] = N'$(DatabaseName)')
112 BEGIN
113 ALTER DATABASE [$(DatabaseName)]
114 SET AUTO_UPDATE_STATISTICS_ASYNC OFF,
115 PAGE_VERIFY CHECKSUM,
116 DATE_CORRELATION_OPTIMIZATION OFF,
117 DISABLE_BROKER,
118 PARAMETERIZATION SIMPLE,
119 SUPPLEMENTAL_LOGGING OFF
120 WITH ROLLBACK IMMEDIATE;
121 END
122
123
124GO
125IF IS_SRVROLEMEMBER(N'sysadmin') = 1
126 BEGIN
127 IF EXISTS (SELECT 1
128 FROM [master].[dbo].[sysdatabases]
129 WHERE [name] = N'$(DatabaseName)')
130 BEGIN
131 EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
132 SET TRUSTWORTHY OFF,
133 DB_CHAINING OFF
134 WITH ROLLBACK IMMEDIATE';
135 END
136 END
137ELSE
138 BEGIN
139 PRINT N'The database settings cannot be modified. You must be a SysAdmin to apply these settings.';
140 END
141
142
143GO
144IF IS_SRVROLEMEMBER(N'sysadmin') = 1
145 BEGIN
146 IF EXISTS (SELECT 1
147 FROM [master].[dbo].[sysdatabases]
148 WHERE [name] = N'$(DatabaseName)')
149 BEGIN
150 EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
151 SET HONOR_BROKER_PRIORITY OFF
152 WITH ROLLBACK IMMEDIATE';
153 END
154 END
155ELSE
156 BEGIN
157 PRINT N'The database settings cannot be modified. You must be a SysAdmin to apply these settings.';
158 END
159
160
161GO
162ALTER DATABASE [$(DatabaseName)]
163 SET TARGET_RECOVERY_TIME = 60 SECONDS
164 WITH ROLLBACK IMMEDIATE;
165
166
167GO
168IF EXISTS (SELECT 1
169 FROM [master].[dbo].[sysdatabases]
170 WHERE [name] = N'$(DatabaseName)')
171 BEGIN
172 ALTER DATABASE [$(DatabaseName)]
173 SET FILESTREAM(NON_TRANSACTED_ACCESS = OFF),
174 CONTAINMENT = NONE
175 WITH ROLLBACK IMMEDIATE;
176 END
177
178
179GO
180IF EXISTS (SELECT 1
181 FROM [master].[dbo].[sysdatabases]
182 WHERE [name] = N'$(DatabaseName)')
183 BEGIN
184 ALTER DATABASE [$(DatabaseName)]
185 SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF),
186 MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = OFF,
187 DELAYED_DURABILITY = DISABLED
188 WITH ROLLBACK IMMEDIATE;
189 END
190
191
192GO
193IF EXISTS (SELECT 1
194 FROM [master].[dbo].[sysdatabases]
195 WHERE [name] = N'$(DatabaseName)')
196 BEGIN
197 ALTER DATABASE [$(DatabaseName)]
198 SET QUERY_STORE (QUERY_CAPTURE_MODE = ALL, DATA_FLUSH_INTERVAL_SECONDS = 900, INTERVAL_LENGTH_MINUTES = 60, MAX_PLANS_PER_QUERY = 200, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), MAX_STORAGE_SIZE_MB = 100)
199 WITH ROLLBACK IMMEDIATE;
200 END
201
202
203GO
204IF EXISTS (SELECT 1
205 FROM [master].[dbo].[sysdatabases]
206 WHERE [name] = N'$(DatabaseName)')
207 BEGIN
208 ALTER DATABASE [$(DatabaseName)]
209 SET QUERY_STORE = OFF
210 WITH ROLLBACK IMMEDIATE;
211 END
212
213
214GO
215IF EXISTS (SELECT 1
216 FROM [master].[dbo].[sysdatabases]
217 WHERE [name] = N'$(DatabaseName)')
218 BEGIN
219 ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
220 ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
221 ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
222 ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
223 ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
224 ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;
225 ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
226 ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;
227 END
228
229
230GO
231IF EXISTS (SELECT 1
232 FROM [master].[dbo].[sysdatabases]
233 WHERE [name] = N'$(DatabaseName)')
234 BEGIN
235 ALTER DATABASE [$(DatabaseName)]
236 SET TEMPORAL_HISTORY_RETENTION ON
237 WITH ROLLBACK IMMEDIATE;
238 END
239
240
241GO
242IF fulltextserviceproperty(N'IsFulltextInstalled') = 1
243 EXECUTE sp_fulltext_database 'enable';
244
245
246GO
247PRINT N'Creating [dbo].[Chats]...';
248
249
250GO
251CREATE TABLE [dbo].[Chats] (
252 [ID] UNIQUEIDENTIFIER NOT NULL,
253 [Author] BIGINT NOT NULL,
254 [To] BIGINT NOT NULL,
255 [Content] NVARCHAR (4000) NOT NULL,
256 [Sent] DATETIMEOFFSET (7) NOT NULL,
257 PRIMARY KEY CLUSTERED ([ID] ASC)
258);
259
260
261GO
262PRINT N'Creating [dbo].[People]...';
263
264
265GO
266CREATE TABLE [dbo].[People] (
267 [ID] BIGINT IDENTITY (1, 1) NOT NULL,
268 [Name] NVARCHAR (50) NOT NULL,
269 [Tag] NVARCHAR (8) NULL,
270 PRIMARY KEY CLUSTERED ([ID] ASC)
271);
272
273
274GO
275PRINT N'Creating unnamed constraint on [dbo].[Chats]...';
276
277
278GO
279ALTER TABLE [dbo].[Chats]
280 ADD DEFAULT (newid()) FOR [ID];
281
282
283GO
284PRINT N'Creating [dbo].[FK_Messages_People_From]...';
285
286
287GO
288ALTER TABLE [dbo].[Chats]
289 ADD CONSTRAINT [FK_Messages_People_From] FOREIGN KEY ([Author]) REFERENCES [dbo].[People] ([ID]);
290
291
292GO
293PRINT N'Creating [dbo].[FK_Messages_People_To]...';
294
295
296GO
297ALTER TABLE [dbo].[Chats]
298 ADD CONSTRAINT [FK_Messages_People_To] FOREIGN KEY ([To]) REFERENCES [dbo].[People] ([ID]);
299
300
301GO
302PRINT N'Creating [dbo].[MessageToSelf]...';
303
304
305GO
306ALTER TABLE [dbo].[Chats]
307 ADD CONSTRAINT [MessageToSelf] CHECK ([author]<>[To]);
308
309
310GO
311PRINT N'Creating [dbo].[IsAuthor]...';
312
313
314GO
315CREATE FUNCTION [dbo].IsAuthor
316(
317 @userID bigint,
318 @MessageID UNIQUEIDENTIFIER
319)
320RETURNS bit
321AS
322BEGIN
323 DECLARE @auth BIGINT;
324 SELECT @auth = Author FROM Chats WHERE @MessageID =ID;
325 DECLARE @result BIT;
326
327 SELECT @result = CASE WHEN @auth IS NOT NULL AND @auth = @userID THEN 1 ELSE 0 END;
328 RETURN @result;
329
330END
331GO
332PRINT N'Creating [dbo].[GetMessages]...';
333
334
335GO
336CREATE PROCEDURE [dbo].GetMessages
337 @userID bigint
338AS
339 SELECT c.ID, c.Content, pf.Name AS Author, pt.Name AS [To], dbo.IsAuthor(@userID,c.ID) AS Mine FROM Chats c
340 INNER JOIN People pf ON pf.ID=c.Author
341 INNER JOIN People pt ON pt.ID=c.[To]
342
343
344RETURN 0
345GO
346/*
347Post-Deployment Script Template
348--------------------------------------------------------------------------------------
349 This file contains SQL statements that will be appended to the build script.
350 Use SQLCMD syntax to include a file in the post-deployment script.
351 Example: :r .\myfile.sql
352 Use SQLCMD syntax to reference a variable in the post-deployment script.
353 Example: :setvar TableName MyTable
354 SELECT * FROM [$(TableName)]
355--------------------------------------------------------------------------------------
356*/
357SET IDENTITY_INSERT [dbo].[People] ON
358INSERT INTO [dbo].[People] ([ID], [Name], [Tag]) VALUES (1, N'Jay Asbury', N'Test')
359INSERT INTO [dbo].[People] ([ID], [Name], [Tag]) VALUES (2, N'John Smith', N'Test2')
360SET IDENTITY_INSERT [dbo].[People] OFF
361
362INSERT INTO [dbo].[Chats] ([ID], [Author], [To], [Content], [Sent]) VALUES (N'de2e6aa2-fdd0-48d2-b258-0739a7f26222', 1, 2, N'Hi', N'9/9/2018 12:42:00 PM -04:00')
363INSERT INTO [dbo].[Chats] ([ID], [Author], [To], [Content], [Sent]) VALUES (N'd4391970-ece9-4061-af11-e4c8bd844a87', 2, 1, N'Hi', N'9/9/2018 12:43:37 PM -04:00')
364GO
365
366GO
367DECLARE @VarDecimalSupported AS BIT;
368
369SELECT @VarDecimalSupported = 0;
370
371IF ((ServerProperty(N'EngineEdition') = 3)
372 AND (((@@microsoftversion / power(2, 24) = 9)
373 AND (@@microsoftversion & 0xffff >= 3024))
374 OR ((@@microsoftversion / power(2, 24) = 10)
375 AND (@@microsoftversion & 0xffff >= 1600))))
376 SELECT @VarDecimalSupported = 1;
377
378IF (@VarDecimalSupported > 0)
379 BEGIN
380 EXECUTE sp_db_vardecimal_storage_format N'$(DatabaseName)', 'ON';
381 END
382
383
384GO
385ALTER DATABASE [$(DatabaseName)]
386 SET MULTI_USER
387 WITH ROLLBACK IMMEDIATE;
388
389
390GO
391PRINT N'Update complete.';
392
393
394GO