MailAlertServer/setup_db.sql at main · AppendAutomation/MailAlertServer · GitHub

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
-- MailAlertServer Database Setup Script
-- Used by the installer to create the database, tables, and application user.
-- Requires sqlcmd variables: DbName, AppUser, AppPass

-- Create database if it doesn't exist
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'$(DbName)')
BEGIN
    CREATE DATABASE [$(DbName)]
END
GO

USE [$(DbName)]
GO

-- Create MailAlerts table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MailAlerts]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[MailAlerts](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [Enabled] [bit] NOT NULL CONSTRAINT [DF_MailAlerts_Enabled] DEFAULT ((1)),
        [Query] [varchar](8000) NOT NULL,
        [Subject] [varchar](100) NOT NULL,
        [Message] [varchar](3000) NULL,
        [Recipients] [varchar](1000) NOT NULL,
        [RecipientsBCC] [varchar](1000) NULL,
        [RecipientsCC] [varchar](1000) NULL,
        [LastTrue] [bit] NOT NULL CONSTRAINT [DF_MailAlerts_LastTrue] DEFAULT ((0)),
        [LastTrueTime] [datetime] NULL,
        [LastCheckTime] [datetime] NULL,
        [MinutesResend] [int] NULL,
        [MessageQuery] [varchar](8000) NULL,
        [LastStatus] [varchar](300) NULL
    ) ON [PRIMARY]
END
GO

-- Create MailConfig table
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MailConfig]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[MailConfig](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [ServerURL] [varchar](100) NOT NULL,
        [ServerPort] [int] NOT NULL,
        [Username] [varchar](50) NOT NULL,
        [Password] [varchar](150) NOT NULL,
        [SenderName] [varchar](50) NOT NULL,
        [SenderAddress] [varchar](50) NOT NULL,
        [ServiceCommand] [varchar](50) NULL
    ) ON [PRIMARY]
END
GO

-- Create MailRequests table
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MailRequests]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[MailRequests](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [Subject] [varchar](100) NOT NULL,
        [Message] [varchar](max) NULL,
        [RecipientTo] [varchar](1000) NOT NULL,
        [RecipientBCC] [varchar](1000) NULL,
        [RecipientCC] [varchar](1000) NULL,
        [Sent] [bit] NOT NULL CONSTRAINT [DF_MailRequests_Sent] DEFAULT ((0)),
        [CreatedTime] [datetime] NOT NULL CONSTRAINT [DF_MailRequests_CreatedTime] DEFAULT (getdate()),
        [SentTime] [datetime] NULL,
        [LastStatus] [varchar](500) NULL CONSTRAINT [DF_MailRequests_LastStatus] DEFAULT ('Request Send')
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO

-- Insert sample alerts if the table is empty
IF NOT EXISTS (SELECT TOP 1 * FROM [dbo].[MailAlerts])
BEGIN
    SET IDENTITY_INSERT [dbo].[MailAlerts] ON

    INSERT [dbo].[MailAlerts] ([ID], [Enabled], [Query], [Subject], [Message], [Recipients], [RecipientsCC], [RecipientsBCC], [LastTrue], [LastTrueTime], [LastCheckTime], [MinutesResend], [MessageQuery], [LastStatus])
    VALUES (1, 0, N'SELECT 1 AS SendMail', N'Test Mail', N'Test Mail', N'user@example.com', N'', N'', 0, NULL, NULL, NULL, NULL, NULL)

    INSERT [dbo].[MailAlerts] ([ID], [Enabled], [Query], [Subject], [Message], [Recipients], [RecipientsCC], [RecipientsBCC], [LastTrue], [LastTrueTime], [LastCheckTime], [MinutesResend], [MessageQuery], [LastStatus])
    VALUES (2, 0, N'SELECT CONVERT(varchar(5), getdate(), 108) As Time,
        CASE
            WHEN CONVERT(varchar(4), getdate(), 108) = ''09:0'' THEN 1
            ELSE 0
        END AS SendMail
    ', N'24 Hour Mail Service Check In', N'Mail service is running.', N'user@example.com', N'', N'', 0, NULL, NULL, NULL, NULL, NULL)

    SET IDENTITY_INSERT [dbo].[MailAlerts] OFF
END
GO

-- Create SQL Server login for the application
IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name = N'$(AppUser)')
BEGIN
    CREATE LOGIN [$(AppUser)] WITH PASSWORD = N'$(AppPass)', DEFAULT_DATABASE = [$(DbName)], CHECK_POLICY = OFF
END
GO

USE [$(DbName)]
GO

-- Create database user mapped to the login
IF NOT EXISTS (SELECT name FROM sys.database_principals WHERE name = N'$(AppUser)')
BEGIN
    CREATE USER [$(AppUser)] FOR LOGIN [$(AppUser)]
END
GO

-- Grant read/write access to the application user
ALTER ROLE [db_datareader] ADD MEMBER [$(AppUser)]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [$(AppUser)]
GO