· 9 years ago · Jan 27, 2017, 12:22 AM
1/*
2Deployment script for Money Trans DB
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 "Money Trans DB"
17:setvar DefaultFilePrefix "Money Trans DB"
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
56IF EXISTS (SELECT 1
57 FROM [master].[dbo].[sysdatabases]
58 WHERE [name] = N'$(DatabaseName)')
59 BEGIN
60 ALTER DATABASE [$(DatabaseName)]
61 SET ANSI_NULLS OFF,
62 ANSI_PADDING OFF,
63 ANSI_WARNINGS OFF,
64 ARITHABORT OFF,
65 CONCAT_NULL_YIELDS_NULL OFF,
66 NUMERIC_ROUNDABORT OFF,
67 QUOTED_IDENTIFIER OFF,
68 ANSI_NULL_DEFAULT OFF,
69 CURSOR_DEFAULT GLOBAL,
70 RECOVERY SIMPLE,
71 CURSOR_CLOSE_ON_COMMIT OFF,
72 AUTO_CREATE_STATISTICS ON,
73 AUTO_SHRINK ON,
74 AUTO_UPDATE_STATISTICS ON,
75 RECURSIVE_TRIGGERS OFF
76 WITH ROLLBACK IMMEDIATE;
77 ALTER DATABASE [$(DatabaseName)]
78 SET AUTO_CLOSE ON
79 WITH ROLLBACK IMMEDIATE;
80 END
81
82
83GO
84IF EXISTS (SELECT 1
85 FROM [master].[dbo].[sysdatabases]
86 WHERE [name] = N'$(DatabaseName)')
87 BEGIN
88 ALTER DATABASE [$(DatabaseName)]
89 SET ALLOW_SNAPSHOT_ISOLATION OFF;
90 END
91
92
93GO
94IF EXISTS (SELECT 1
95 FROM [master].[dbo].[sysdatabases]
96 WHERE [name] = N'$(DatabaseName)')
97 BEGIN
98 ALTER DATABASE [$(DatabaseName)]
99 SET READ_COMMITTED_SNAPSHOT OFF
100 WITH ROLLBACK IMMEDIATE;
101 END
102
103
104GO
105IF EXISTS (SELECT 1
106 FROM [master].[dbo].[sysdatabases]
107 WHERE [name] = N'$(DatabaseName)')
108 BEGIN
109 ALTER DATABASE [$(DatabaseName)]
110 SET AUTO_UPDATE_STATISTICS_ASYNC OFF,
111 PAGE_VERIFY CHECKSUM,
112 DATE_CORRELATION_OPTIMIZATION OFF,
113 ENABLE_BROKER,
114 PARAMETERIZATION SIMPLE,
115 SUPPLEMENTAL_LOGGING OFF
116 WITH ROLLBACK IMMEDIATE;
117 END
118
119
120GO
121IF IS_SRVROLEMEMBER(N'sysadmin') = 1
122 BEGIN
123 IF EXISTS (SELECT 1
124 FROM [master].[dbo].[sysdatabases]
125 WHERE [name] = N'$(DatabaseName)')
126 BEGIN
127 EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
128 SET TRUSTWORTHY OFF,
129 DB_CHAINING OFF
130 WITH ROLLBACK IMMEDIATE';
131 END
132 END
133ELSE
134 BEGIN
135 PRINT N'The database settings cannot be modified. You must be a SysAdmin to apply these settings.';
136 END
137
138
139GO
140IF IS_SRVROLEMEMBER(N'sysadmin') = 1
141 BEGIN
142 IF EXISTS (SELECT 1
143 FROM [master].[dbo].[sysdatabases]
144 WHERE [name] = N'$(DatabaseName)')
145 BEGIN
146 EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
147 SET HONOR_BROKER_PRIORITY OFF
148 WITH ROLLBACK IMMEDIATE';
149 END
150 END
151ELSE
152 BEGIN
153 PRINT N'The database settings cannot be modified. You must be a SysAdmin to apply these settings.';
154 END
155
156
157GO
158ALTER DATABASE [$(DatabaseName)]
159 SET TARGET_RECOVERY_TIME = 0 SECONDS
160 WITH ROLLBACK IMMEDIATE;
161
162
163GO
164IF EXISTS (SELECT 1
165 FROM [master].[dbo].[sysdatabases]
166 WHERE [name] = N'$(DatabaseName)')
167 BEGIN
168 ALTER DATABASE [$(DatabaseName)]
169 SET FILESTREAM(NON_TRANSACTED_ACCESS = OFF),
170 CONTAINMENT = NONE
171 WITH ROLLBACK IMMEDIATE;
172 END
173
174
175GO
176IF EXISTS (SELECT 1
177 FROM [master].[dbo].[sysdatabases]
178 WHERE [name] = N'$(DatabaseName)')
179 BEGIN
180 ALTER DATABASE [$(DatabaseName)]
181 SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF),
182 MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = OFF,
183 DELAYED_DURABILITY = DISABLED
184 WITH ROLLBACK IMMEDIATE;
185 END
186
187
188GO
189USE [$(DatabaseName)];
190
191
192GO
193IF fulltextserviceproperty(N'IsFulltextInstalled') = 1
194 EXECUTE sp_fulltext_database 'enable';
195
196
197GO
198PRINT N'Creating [dbo].[Transactions]...';
199
200
201GO
202CREATE TABLE [dbo].[Transactions] (
203 [TransactionID] BIGINT IDENTITY (1, 1) NOT NULL,
204 [LoanId] BIGINT NOT NULL,
205 [Amount] MONEY NOT NULL,
206 [Occured] DATE NOT NULL,
207 PRIMARY KEY CLUSTERED ([TransactionID] ASC)
208);
209
210
211GO
212PRINT N'Creating [dbo].[Loans]...';
213
214
215GO
216CREATE TABLE [dbo].[Loans] (
217 [LoanId] BIGINT IDENTITY (1, 1) NOT NULL,
218 [Amount] MONEY NOT NULL,
219 [CustomerId] UNIQUEIDENTIFIER NOT NULL,
220 [LoanDate] DATE NOT NULL,
221 PRIMARY KEY CLUSTERED ([LoanId] ASC)
222);
223
224
225GO
226PRINT N'Creating [dbo].[Customers]...';
227
228
229GO
230CREATE TABLE [dbo].[Customers] (
231 [CustomerId] UNIQUEIDENTIFIER NOT NULL,
232 [Name] NVARCHAR (30) NOT NULL,
233 PRIMARY KEY CLUSTERED ([CustomerId] ASC)
234);
235
236
237GO
238PRINT N'Creating unnamed constraint on [dbo].[Transactions]...';
239
240
241GO
242ALTER TABLE [dbo].[Transactions]
243 ADD DEFAULT (getdate()) FOR [Occured];
244
245
246GO
247PRINT N'Creating unnamed constraint on [dbo].[Loans]...';
248
249
250GO
251ALTER TABLE [dbo].[Loans]
252 ADD DEFAULT (getdate()) FOR [LoanDate];
253
254
255GO
256PRINT N'Creating unnamed constraint on [dbo].[Customers]...';
257
258
259GO
260ALTER TABLE [dbo].[Customers]
261 ADD DEFAULT (newid()) FOR [CustomerId];
262
263
264GO
265PRINT N'Creating [dbo].[FK_Transactions_Loans]...';
266
267
268GO
269ALTER TABLE [dbo].[Transactions]
270 ADD CONSTRAINT [FK_Transactions_Loans] FOREIGN KEY ([LoanId]) REFERENCES [dbo].[Loans] ([LoanId]);
271
272
273GO
274PRINT N'Creating [dbo].[FK_Loans_Customers]...';
275
276
277GO
278ALTER TABLE [dbo].[Loans]
279 ADD CONSTRAINT [FK_Loans_Customers] FOREIGN KEY ([CustomerId]) REFERENCES [dbo].[Customers] ([CustomerId]);
280
281
282GO
283PRINT N'Creating [dbo].[CK_Transactions_Amount]...';
284
285
286GO
287ALTER TABLE [dbo].[Transactions]
288 ADD CONSTRAINT [CK_Transactions_Amount] CHECK ([Amount]>(0.0));
289
290
291GO
292PRINT N'Creating [dbo].[IsTransactionDateValid]...';
293
294
295GO
296CREATE FUNCTION [dbo].IsTransactionDateValid
297(
298 @LoanId bigint,
299 @TransDate date
300
301)
302RETURNS bit
303AS
304BEGIN
305declare @dt date;
306
307select @dt=LoanDate from Loans
308 where Loans.LoanId=@LoanId;
309
310 if @dt<=@TransDate
311 return 1;
312 return 0
313END
314GO
315PRINT N'Creating [dbo].[CK_Transactions_Occured]...';
316
317
318GO
319ALTER TABLE [dbo].[Transactions]
320 ADD CONSTRAINT [CK_Transactions_Occured] CHECK ([dbo].[IsTransactionDateValid]([LoanId],[Occured])=(1));
321
322
323GO
324PRINT N'Creating [dbo].[LoanStatus]...';
325
326
327GO
328CREATE VIEW [dbo].LoanStatus
329 AS
330 Select L.LoanId, L.LoanDate, L.CustomerId, L.Amount,C.Name as CustomerName,
331 coalesce( (Select SUM(T.Amount) from Transactions as T where T.LoanId=L.LoanId),0.0) TotalPaid,
332 l.Amount- coalesce( (Select SUM(T.Amount) from Transactions as T where T.LoanId=L.LoanId),0.0) as LeftToPay
333 From Loans as L
334 Inner Join Customers as C
335 ON L.CustomerId=C.CustomerId
336GO
337PRINT N'Creating [dbo].[CustomerDebt]...';
338
339
340GO
341CREATE VIEW [dbo].CustomerDebt
342 AS SELECT CustomerName, Sum([LeftToPay]) as Debt, CustomerId
343 from LoanStatus
344 Group by CustomerId, CustomerName
345GO
346DECLARE @VarDecimalSupported AS BIT;
347
348SELECT @VarDecimalSupported = 0;
349
350IF ((ServerProperty(N'EngineEdition') = 3)
351 AND (((@@microsoftversion / power(2, 24) = 9)
352 AND (@@microsoftversion & 0xffff >= 3024))
353 OR ((@@microsoftversion / power(2, 24) = 10)
354 AND (@@microsoftversion & 0xffff >= 1600))))
355 SELECT @VarDecimalSupported = 1;
356
357IF (@VarDecimalSupported > 0)
358 BEGIN
359 EXECUTE sp_db_vardecimal_storage_format N'$(DatabaseName)', 'ON';
360 END
361
362
363GO
364ALTER DATABASE [$(DatabaseName)]
365 SET MULTI_USER
366 WITH ROLLBACK IMMEDIATE;
367
368
369GO
370PRINT N'Update complete.';
371
372
373GO