Bj9igLUF

1CREATE DATABASE CustomerSupport;
2GO
3USE CustomerSupport;
4CREATE FULLTEXT CATALOG SearchCatalog AS DEFAULT;
5GO
6CREATE TABLE UserPrincipal (
7 UserId BIGINT NOT NULL PRIMARY KEY IDENTITY(1,1),
8 Username VARCHAR(30) NOT NULL CONSTRAINT UserPrincipal_Username UNIQUE,
9 HashedPassword BINARY(60) NOT NULL,
10 AccountNonExpired BIT NOT NULL,
11 AccountNonLocked BIT NOT NULL,
12 CredentialsNonExpired BIT NOT NULL,
13 Enabled BIT NOT NULL
14);
15-- Run these statements if the UserPrincipal table doesn't contain these columns
16-- ALTER TABLE UserPrincipal
17-- ADD COLUMN AccountNonExpired BOOLEAN NOT NULL DEFAULT TRUE,
18-- ADD COLUMN AccountNonLocked BOOLEAN NOT NULL DEFAULT TRUE,
19-- ADD COLUMN CredentialsNonExpired BOOLEAN NOT NULL DEFAULT TRUE,
20-- ADD COLUMN Enabled BOOLEAN NOT NULL DEFAULT TRUE;
21-- ALTER TABLE UserPrincipal
22-- MODIFY COLUMN AccountNonExpired BOOLEAN NOT NULL,
23-- MODIFY COLUMN AccountNonLocked BOOLEAN NOT NULL,
24-- MODIFY COLUMN CredentialsNonExpired BOOLEAN NOT NULL,
25-- MODIFY COLUMN Enabled BOOLEAN NOT NULL;
26CREATE TABLE UserPrincipal_Authority (
27 UserId BIGINT NOT NULL,
28 Authority VARCHAR(100) NOT NULL,
29 CONSTRAINT UserPrincipal_Authority_User_Authority UNIQUE (UserId, Authority),
30 CONSTRAINT UserPrincipal_Authority_UserId FOREIGN KEY (UserId)
31 REFERENCES UserPrincipal (UserId) ON DELETE CASCADE
32);
33CREATE TABLE WebServiceClient (
34 WebServiceClientId BIGINT NOT NULL PRIMARY KEY IDENTITY(1,1),
35 ClientId VARCHAR(50) NOT NULL,
36 ClientSecret VARCHAR(60) NOT NULL,
37 CONSTRAINT WebServiceClient_ClientId UNIQUE (ClientId)
38);
39CREATE TABLE WebServiceClient_Scope (
40 WebServiceClientId BIGINT NOT NULL,
41 Scope VARCHAR(100) NOT NULL,
42 CONSTRAINT WebServiceClient_Scopes_Client_Scope UNIQUE (WebServiceClientId, Scope),
43 CONSTRAINT WebServiceClient_Scopes_ClientId FOREIGN KEY (WebServiceClientId)
44 REFERENCES WebServiceClient (WebServiceClientId) ON DELETE CASCADE
45);
46CREATE TABLE WebServiceClient_Grant (
47 WebServiceClientId BIGINT NOT NULL,
48 GrantName VARCHAR(100) NOT NULL,
49 CONSTRAINT WebServiceClient_Grants_Client_Grant UNIQUE (WebServiceClientId, GrantName),
50 CONSTRAINT WebServiceClient_Grants_ClientId FOREIGN KEY (WebServiceClientId)
51 REFERENCES WebServiceClient (WebServiceClientId) ON DELETE CASCADE
52);
53CREATE TABLE WebServiceClient_RedirectUri (
54 WebServiceClientId BIGINT NOT NULL,
55 Uri VARCHAR(1024) NOT NULL,
56 CONSTRAINT WebServiceClient_Uris_ClientId FOREIGN KEY (WebServiceClientId)
57 REFERENCES WebServiceClient (WebServiceClientId) ON DELETE CASCADE
58);
59CREATE TABLE OAuthAccessToken (
60 OAuthAccessTokenId BIGINT NOT NULL PRIMARY KEY IDENTITY(1,1),
61 TokenKey VARCHAR(50) NOT NULL,
62 Value VARCHAR(50) NOT NULL,
63 Expiration smalldatetime NULL,
64 Authentication Image NOT NULL,
65 CONSTRAINT OAuthAccessToken_TokenKey UNIQUE(TokenKey),
66 CONSTRAINT OAuthAccessToken_Value UNIQUE (Value)
67);
68CREATE TABLE OAuthAccessToken_Scope (
69 OAuthAccessTokenId BIGINT NOT NULL,
70 Scope VARCHAR(100) NOT NULL,
71 CONSTRAINT OAuthAccessToken_Scopes_Token_Scope UNIQUE (OAuthAccessTokenId, Scope),
72 CONSTRAINT OAuthAccessToken_Scopes_TokenId FOREIGN KEY (OAuthAccessTokenId)
73 REFERENCES OAuthAccessToken (OAuthAccessTokenId) ON DELETE CASCADE
74);
75CREATE TABLE OAuthNonce (
76 OAuthNonceId BIGINT NOT NULL PRIMARY KEY IDENTITY(1,1),
77 Value VARCHAR(50),
78 NonceTimestamp BIGINT NOT NULL,
79 CONSTRAINT OAuthNonce_Value_Timestamp UNIQUE (Value, NonceTimestamp)
80);
81CREATE TABLE Ticket (
82 TicketId BIGINT NOT NULL IDENTITY(1,1) CONSTRAINT Ordered_TicketId PRIMARY KEY ,
83 UserId BIGINT NOT NULL,
84 Subject VARCHAR(255) NOT NULL,
85 Body VARCHAR(640),
86 DateCreated smalldatetime NULL,
87 CONSTRAINT Ticket_UserId FOREIGN KEY (UserId)
88 REFERENCES UserPrincipal (UserId) ON DELETE CASCADE
89);
90--CREATE INDEX Ordered_TicketId on Ticket (TicketId);
91--CREATE FULLTEXT INDEX on Ticket (Subject, Body) KEY INDEX Ordered_TicketId;
92--CREATE INDEX SearchTicket_Index on Ticket (Subject, Body);
93CREATE TABLE TicketComment (
94 CommentId BIGINT NOT NULL IDENTITY(1,1) CONSTRAINT Ordered_CommentId PRIMARY KEY ,
95 TicketId BIGINT NOT NULL,
96 UserId BIGINT NOT NULL,
97 Body VARCHAR(640),
98 DateCreated smalldatetime NULL,
99 CONSTRAINT TicketComment_UserId FOREIGN KEY (UserId)
100 REFERENCES UserPrincipal (UserId) ON DELETE NO ACTION,
101 CONSTRAINT TicketComment_TicketId FOREIGN KEY (TicketId)
102 REFERENCES Ticket (TicketId) ON DELETE NO ACTION
103);
104--ALTER TABLE TicketComment ADD FULLTEXT INDEX TicketComment_Search (Body);
105--CREATE INDEX Ordered_CommentId on TicketComment (CommentId);
106CREATE FULLTEXT INDEX on TicketComment (Body) KEY INDEX Ordered_CommentId;
107--CREATE INDEX SearchTicketComment_Index on TicketComment (Body);
108CREATE TABLE Attachment (
109 AttachmentId BIGINT NOT NULL PRIMARY KEY IDENTITY(1,1),
110 AttachmentName VARCHAR(255) NULL,
111 MimeContentType VARCHAR(255) NOT NULL,
112 Contents Image NOT NULL
113) ;
114CREATE TABLE Ticket_Attachment (
115 SortKey SMALLINT NOT NULL,
116 TicketId BIGINT NOT NULL,
117 AttachmentId BIGINT NOT NULL,
118 CONSTRAINT Ticket_Attachment_Ticket FOREIGN KEY (TicketId)
119 REFERENCES Ticket (TicketId) ON DELETE CASCADE,
120 CONSTRAINT Ticket_Attachment_Attachment FOREIGN KEY (AttachmentId)
121 REFERENCES Attachment (AttachmentId) ON DELETE CASCADE
122);
123CREATE INDEX Ticket_OrderedAttachments on Ticket_Attachment (TicketId, SortKey, AttachmentId);
124CREATE TABLE TicketComment_Attachment (
125 SortKey SMALLINT NOT NULL,
126 CommentId BIGINT NOT NULL,
127 AttachmentId BIGINT NOT NULL,
128 CONSTRAINT TicketComment_Attachment_Comment FOREIGN KEY (CommentId)
129 REFERENCES TicketComment (CommentId) ON DELETE CASCADE,
130 CONSTRAINT TicketComment_Attachment_Attachment FOREIGN KEY (AttachmentId)
131 REFERENCES Attachment (AttachmentId) ON DELETE CASCADE
132);
133CREATE INDEX TicketComment_OrderedAttachments on TicketComment_Attachment (CommentId, SortKey, AttachmentId);
134-- Run these statements if the Attachment table already exists with a TicketId column
135-- INSERT INTO Ticket_Attachment (SortKey, TicketId, AttachmentId)
136-- SELECT @rn := @rn + 1, TicketId, AttachmentId
137-- FROM Attachment, (SELECT @rn:=0) x
138-- ORDER BY TicketId, AttachmentName;
139-- CREATE TEMPORARY TABLE $minSortKeys ENGINE = Memory (
140-- SELECT min(SortKey) as SortKey, TicketId FROM Ticket_Attachment GROUP BY TicketId
141-- );
142-- UPDATE Ticket_Attachment a SET a.SortKey = a.SortKey - (
143-- SELECT x.SortKey FROM $minSortKeys x WHERE x.TicketId = a.TicketId
144-- ) WHERE TicketId > 0;
145-- DROP TABLE $minSortKeys;
146-- ALTER TABLE Attachment DROP FOREIGN KEY Attachment_TicketId;
147-- ALTER TABLE Attachment DROP COLUMN TicketId;
148-- Run these statements to create the default users
149INSERT INTO UserPrincipal (Username, HashedPassword, AccountNonExpired,
150 AccountNonLocked, CredentialsNonExpired, Enabled)
151VALUES ( -- password
152 'Nicholas', CONVERT(BINARY(60), '$2a$10$x0k/yA5qN8SP8JD5CEN.6elEBFxVVHeKZTdyv.RPra4jzRR5SlKSC'),
153 1, 1, 1, 1
154);
155INSERT INTO UserPrincipal_Authority (UserId, Authority)
156 VALUES (1, 'VIEW_TICKETS'), (1, 'VIEW_TICKET'), (1, 'CREATE_TICKET'),
157 (1, 'EDIT_OWN_TICKET'), (1, 'VIEW_COMMENTS'), (1, 'CREATE_COMMENT'),
158 (1, 'EDIT_OWN_COMMENT'), (1, 'VIEW_ATTACHMENT'), (1, 'CREATE_CHAT_REQUEST'),
159 (1, 'CHAT');
160INSERT INTO UserPrincipal (Username, HashedPassword, AccountNonExpired,
161 AccountNonLocked, CredentialsNonExpired, Enabled)
162VALUES ( -- drowssap
163 'Sarah', CONVERT(BINARY(60), '$2a$10$JSxmYO.JOb4TT42/4RFzguaTuYkZLCfeND1bB0rzoy7wH0RQFEq8y'),
164 1, 1, 1, 1
165);
166INSERT INTO UserPrincipal_Authority (UserId, Authority)
167 VALUES (2, 'VIEW_TICKETS'), (2, 'VIEW_TICKET'), (2, 'CREATE_TICKET'),
168 (2, 'EDIT_OWN_TICKET'), (2, 'VIEW_COMMENTS'), (2, 'CREATE_COMMENT'),
169 (2, 'EDIT_OWN_COMMENT'), (2, 'VIEW_ATTACHMENT'), (2, 'CREATE_CHAT_REQUEST'),
170 (2, 'CHAT');
171INSERT INTO UserPrincipal (Username, HashedPassword, AccountNonExpired,
172 AccountNonLocked, CredentialsNonExpired, Enabled)
173VALUES ( -- wordpass
174 'Mike', CONVERT(BINARY(60), '$2a$10$Lc0W6stzND.9YnFRcfbOt.EaCVO9aJ/QpbWnfjJLcMovdTx5s4i3G'),
175 1, 1, 1, 1
176);
177INSERT INTO UserPrincipal_Authority (UserId, Authority)
178 VALUES (3, 'VIEW_TICKETS'), (3, 'VIEW_TICKET'), (3, 'CREATE_TICKET'),
179 (3, 'EDIT_OWN_TICKET'), (3, 'VIEW_COMMENTS'), (3, 'CREATE_COMMENT'),
180 (3, 'EDIT_OWN_COMMENT'), (3, 'VIEW_ATTACHMENT'), (3, 'CREATE_CHAT_REQUEST'),
181 (3, 'CHAT');
182INSERT INTO UserPrincipal (Username, HashedPassword, AccountNonExpired,
183 AccountNonLocked, CredentialsNonExpired, Enabled)
184VALUES ( -- green
185 'John', CONVERT(BINARY(60), '$2a$10$vacuqbDw9I7rr6RRH8sByuktOzqTheQMfnK3XCT2WlaL7vt/3AMby'),
186 1, 1, 1, 1
187);
188INSERT INTO UserPrincipal_Authority (UserId, Authority)
189 VALUES (4, 'VIEW_TICKETS'), (4, 'VIEW_TICKET'), (4, 'CREATE_TICKET'),
190 (4, 'EDIT_OWN_TICKET'), (4, 'VIEW_COMMENTS'), (4, 'CREATE_COMMENT'),
191 (4, 'EDIT_OWN_COMMENT'), (4, 'VIEW_ATTACHMENT'), (4, 'CREATE_CHAT_REQUEST'),
192 (4, 'CHAT'), (4, 'EDIT_ANY_TICKET'), (4, 'DELETE_TICKET'),
193 (4, 'EDIT_ANY_COMMENT'), (4, 'DELETE_COMMENT'), (4, 'VIEW_USER_SESSIONS'),
194 (4, 'VIEW_CHAT_REQUESTS'), (4, 'START_CHAT');
195-- Run these statements to add the default OAuth data to the database
196INSERT INTO UserPrincipal_Authority (UserId, Authority)
197VALUES (1, 'USE_WEB_SERVICES'), (4, 'USE_WEB_SERVICES');
198INSERT INTO WebServiceClient (ClientId, ClientSecret) VALUES ( -- y471l12D2y55U5558rd2
199 'TestClient', '$2a$10$elDBcfb/ZKyuNgOPK5.70Oi4gN2EuhU2yONPsoF3avx9.Hd/b8BTa'
200);
201INSERT INTO WebServiceClient_Scope (WebServiceClientId, Scope)
202 VALUES (1, 'READ'), (1, 'WRITE'), (1, 'TRUST');
203INSERT INTO WebServiceClient_Grant (WebServiceClientId, GrantName)
204 VALUES (1, 'authorization_code');
205INSERT INTO WebServiceClient_RedirectUri (WebServiceClientId, Uri)
206 VALUES (1, 'http://localhost:8080/client/support');
207GO