9sg6ESES

1DROP TABLE IF EXISTS [users];
2DROP TABLE IF EXISTS [roles];
3DROP TABLE IF EXISTS [roleAssignementsLink];
4DROP TABLE IF EXISTS [rolesPermissionLink];
5DROP TABLE IF EXISTS [permissions];
6DROP TABLE IF EXISTS [events];
7DROP TABLE IF EXISTS [eventsAttendance];
8DROP TABLE IF EXISTS [eventsClassesInvolvedLink];
9DROP TABLE IF EXISTS [classes];
10DROP TABLE IF EXISTS [classReprLink];
11DROP TABLE IF EXISTS [documents];
12DROP TABLE IF EXISTS [eventDocumentsAttachmentLink];
13DROP TABLE IF EXISTS [feedbacks];
14DROP TABLE IF EXISTS [feedbackModelsFields];
15DROP TABLE IF EXISTS [feedbackModels];
16DROP TABLE IF EXISTS [feedbackAnswers];
17DROP TABLE IF EXISTS [feedbackAnswersFields];
18DROP TABLE IF EXISTS [feedbackStats];
19DROP TABLE IF EXISTS [feedbackDetailAverageStats];
20DROP TABLE IF EXISTS [feedbackDetailCountTable];
21DROP TABLE IF EXISTS [feedbackFrozenModels];
22DROP TABLE IF EXISTS [freezedFeedbackModelFields];
23DROP TABLE IF EXISTS [feedbackFieldsOccurrence];
24DROP TABLE IF EXISTS [peopleInteractions];
25DROP TABLE IF EXISTS [interactionsTypes];
26DROP TABLE IF EXISTS [communications];
27DROP TABLE IF EXISTS [sendNotifyTo];
28DROP TABLE IF EXISTS [projects];
29DROP TABLE IF EXISTS [projectSteps];
30DROP TABLE IF EXISTS [questions];
31DROP TABLE IF EXISTS [general];
32
33
34CREATE TABLE [users] (
35[id] INT PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
36[email] VARCHAR (255) NOT NULL,
37[name] VARCHAR (100) NOT NULL,
38[surname] VARCHAR (100) DEFAULT 'surname' NOT NULL,
39[birthDate] INT NOT NULL,
40[authWithGoogle] BOOLEAN NOT NULL,
41[username] INT (50),
42[passwordHash] VARCHAR (255),
43[googleToken] VARCHAR (255),
44[lastLogin] DATETIME,
45[signupDate] DATETIME,
46[roleId] INT NOT NULL,
47[profilePhotoFilename] VARCHAR (100),
48[nfcTagId] INT UNIQUE,
49[sessionToken] VARCHAR (32) UNIQUE,
50[emailConfirmedDate] DATETIME UNIQUE,
51FOREIGN KEY([roleId]) REFERENCES [roles]([id]));
52
53CREATE TABLE [roles] (
54[id] INT PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
55[title] VARCHAR DEFAULT 200 NOT NULL,
56[description] VARCHAR (255) NOT NULL);
57
58CREATE TABLE [roleAssignementsLink] (
59[assignedRoleId] INT NOT NULL,
60[assignerRoleId] INT NOT NULL,
61FOREIGN KEY([assignedRoleId]) REFERENCES [roles]([id]),
62FOREIGN KEY([assignerRoleId]) REFERENCES [roles]([id]));
63
64CREATE TABLE [rolesPermissionLink] (
65[roleId] INT NOT NULL,
66[permissionId] INT NOT NULL,
67FOREIGN KEY([roleId]) REFERENCES [roles]([id]),
68FOREIGN KEY([permissionId]) REFERENCES [permissions]([id]));
69
70CREATE TABLE [permissions] (
71[id] INT PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
72[name] VARCHAR (200) NOT NULL,
73[description] VARCHAR (500) NOT NULL,
74[globalDisabled] BOOLEAN NOT NULL,
75[tag] VARCHAR (250) NOT NULL UNIQUE);
76
77CREATE TABLE [events] (
78[id] INT PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
79[title] VARCHAR (1000) NOT NULL,
80[shortDescription] INT (200) NOT NULL,
81[description] TEXT NOT NULL,
82[insertDate] DATETIME NOT NULL,
83[publishDate] DATETIME NOT NULL,
84[startDate] DATETIME NOT NULL,
85[endDate] DATETIME,
86[thumbnailPhotoFilename] VARCHAR (100) NOT NULL,
87[attendanceRequired] BOOLEAN NOT NULL,
88[attendanceDeadlineDate] DATETIME NOT NULL,
89[paymentDeadlineDate] INT NOT NULL);
90
91CREATE TABLE [eventsAttendance] (
92[id] INT PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
93[userId] INT NOT NULL,
94[eventId] INT NOT NULL,
95[sentEventEmail] DATETIME,
96[openedEventEmailLink] DATETIME,
97[clickedNoAttend] DATETIME,
98[clickYesAttend] DATETIME,
99[payedMoneyRepr] DATETIME,
100[payedCreditCard] DATETIME,
101[reprPayedMoney] DATETIME,
102[reprPayedCreditVard] DATETIME,
103FOREIGN KEY([userId]) REFERENCES [users]([id]),
104FOREIGN KEY([eventId]) REFERENCES [events]([id]));
105
106CREATE TABLE [eventsClassesInvolvedLink] (
107[eventId] INT NOT NULL,
108[classId] INT NOT NULL,
109FOREIGN KEY([eventId]) REFERENCES [events]([id]),
110FOREIGN KEY([classId]) REFERENCES [classes]([id]));
111
112CREATE TABLE [classes] (
113[id] INT PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
114[className] INT NOT NULL UNIQUE);
115
116CREATE TABLE [classReprLink] (
117[classId] INT NOT NULL,
118[representativeId] INT NOT NULL,
119FOREIGN KEY([classId]) REFERENCES [classes]([id]),
120FOREIGN KEY([representativeId]) REFERENCES [users]([id]));
121
122CREATE TABLE [documents] (
123[id] INT PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
124[title] VARCHAR (800) NOT NULL,
125[description] INT (500),
126[thumbnailPhotoFilename] VARCHAR DEFAULT 100,
127[documentFile] INT NOT NULL,
128[uploadedBy] INT NOT NULL,
129[showUploadedBy] BOOLEAN NOT NULL,
130FOREIGN KEY([uploadedBy]) REFERENCES [users]([id]));
131
132CREATE TABLE [eventDocumentsAttachmentLink] (
133[eventId] INT NOT NULL,
134[documentId] INT NOT NULL,
135FOREIGN KEY([eventId]) REFERENCES [events]([id]),
136FOREIGN KEY([documentId]) REFERENCES [documents]([id]));
137
138CREATE TABLE [feedbacks] (
139[id] INT PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
140[eventId] INT,
141[startDate] DATETIME,
142[endDate] DATETIME,
143[publishResultsDate] INT NOT NULL,
144[modelId] INT NOT NULL,
145[submissions] INT,
146FOREIGN KEY([eventId]) REFERENCES [events]([id]),
147FOREIGN KEY([modelId]) REFERENCES [feedbackFrozenModels]([id]));
148
149CREATE TABLE [feedbackModelsFields] (
150[id] INT NOT NULL UNIQUE,
151[fieldId] INT NOT NULL,
152[fieldName] VARCHAR (500) NOT NULL,
153[fieldType] VARCHAR (100) NOT NULL,
154[fieldData] TEXT,
155[fieldDefault] TEXT,
156[fieldRequired] BOOLEAN NOT NULL,
157FOREIGN KEY([id]) REFERENCES [feedbackModels]([id]));
158
159CREATE TABLE [feedbackModels] (
160[id] INT PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
161[name] VARCHAR (500) NOT NULL,
162[descriptions] VARCHAR (1000) NOT NULL,
163[createdDate] DATETIME NOT NULL,
164[lastEditedDate] DATETIME NOT NULL,
165[modelHash] VARCHAR DEFAULT 500 NOT NULL);
166
167CREATE TABLE [feedbackAnswers] (
168[id] INT PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
169[feedbackId] INT NOT NULL,
170FOREIGN KEY([feedbackId]) REFERENCES [feedbacks]([id]));
171
172CREATE TABLE [feedbackAnswersFields] (
173[answerId] INT NOT NULL,
174[fieldName] VARCHAR (500) NOT NULL,
175[DieldValue] TEXT NOT NULL,
176FOREIGN KEY([answerId]) REFERENCES [feedbackAnswers]([id]));
177
178CREATE TABLE [feedbackStats] (
179[id] INT PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
180[feedbackId] INT NOT NULL,
181FOREIGN KEY([feedbackId]) REFERENCES [feedbacks]([id]));
182
183CREATE TABLE [feedbackDetailAverageStats] (
184[statId] INT NOT NULL,
185[fieldId] INT NOT NULL,
186[mean] DECIMAL,
187[mode] DECIMAL NOT NULL,
188[standardDeviation] DECIMAL NOT NULL,
189FOREIGN KEY([statId]) REFERENCES [feedbackStats]([id]));
190
191CREATE TABLE [feedbackDetailCountTable] (
192[id] INT PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
193[statId] INT NOT NULL,
194[FieldId] INT NOT NULL,
195FOREIGN KEY([statId]) REFERENCES [feedbackStats]([id]));
196
197CREATE TABLE [feedbackFrozenModels] (
198[id] INT PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
199[name] VARCHAR (500) NOT NULL,
200[description] VARCHAR (1000) NOT NULL,
201[originalModelId] INT NOT NULL,
202[modelHash] VARCHAR (500) NOT NULL,
203[createdDate] DATETIME NOT NULL,
204[lastEditedDate] DATETIME NOT NULL,
205[originalCloneDate] DATETIME NOT NULL,
206[lastClonedDate] DATETIME NOT NULL,
207[freezedModelId] INT NOT NULL,
208FOREIGN KEY([originalModelId]) REFERENCES [feedbackModels]([id]),
209FOREIGN KEY([freezedModelId]) REFERENCES [freezedFeedbackModelFields]([id]));
210
211CREATE TABLE [freezedFeedbackModelFields] (
212[id] INT PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
213[FieldId] INT NOT NULL,
214[fieldName] VARCHAR (500) NOT NULL,
215[fieldType] VARCHAR (1000) NOT NULL,
216[fieldData] TEXT NOT NULL,
217[fieldDefault] TEXT NOT NULL,
218[fieldRequired] BOOLEAN NOT NULL);
219
220CREATE TABLE [feedbackFieldsOccurrence] (
221[id] INT NOT NULL,
222[value] TEXT NOT NULL,
223[occurrenceTotal] INT NOT NULL,
224[occurrencePercent] DECIMAL NOT NULL,
225FOREIGN KEY([id]) REFERENCES [feedbackDetailCountTable]([id]));
226
227CREATE TABLE [peopleInteractions] (
228[id] INT PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
229[interactionTypeId] INT NOT NULL,
230[started] DATETIME NOT NULL,
231[ended] DATETIME,
232[byuserId] INT NOT NULL,
233[eventId] INT,
234[documentId] INT NOT NULL,
235[frozenFeedbackId] INT NOT NULL,
236[communicationId] INT NOT NULL,
237[projectId] INT,
238[projectStepId] INT NOT NULL,
239[questionId] INT,
240FOREIGN KEY([interactionTypeId]) REFERENCES [interactionsTypes]([id]),
241FOREIGN KEY([byuserId]) REFERENCES [users]([id]),
242FOREIGN KEY([eventId]) REFERENCES [events]([id]),
243FOREIGN KEY([documentId]) REFERENCES [documents]([id]),
244FOREIGN KEY([frozenFeedbackId]) REFERENCES [feedbackFrozenModels]([id]),
245FOREIGN KEY([communicationId]) REFERENCES [communications]([id]),
246FOREIGN KEY([projectId]) REFERENCES [projects]([id]),
247FOREIGN KEY([projectStepId]) REFERENCES [projectSteps]([id]),
248FOREIGN KEY([questionId]) REFERENCES [questions]([id]));
249
250CREATE TABLE [interactionsTypes] (
251[id] INT PRIMARY KEY NOT NULL UNIQUE,
252[name] VARCHAR (500) NOT NULL,
253[description] VARCHAR (1000) NOT NULL);
254
255CREATE TABLE [communications] (
256[id] INT PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
257[name] VARCHAR (500) NOT NULL,
258[text] TEXT NOT NULL);
259
260CREATE TABLE [sendNotifyTo] (
261[documentId] INT,
262[feedbackFrozenId] INT,
263[eventId] INT,
264[projectId] INT,
265[projectStepid] INT,
266[communicationId] INT,
267[questionId] INT,
268[class] VARCHAR (10),
269[classGrade] INT,
270[userId] INT,
271[sendOn] DATETIME NOT NULL,
272FOREIGN KEY([documentId]) REFERENCES [documents]([id]),
273FOREIGN KEY([feedbackFrozenId]) REFERENCES [feedbackFrozenModels]([id]),
274FOREIGN KEY([eventId]) REFERENCES [events]([id]),
275FOREIGN KEY([projectId]) REFERENCES [projects]([id]),
276FOREIGN KEY([projectStepid]) REFERENCES [projectSteps]([id]),
277FOREIGN KEY([communicationId]) REFERENCES [communications]([id]),
278FOREIGN KEY([questionId]) REFERENCES [questions]([id]),
279FOREIGN KEY([userId]) REFERENCES [users]([id]));
280
281CREATE TABLE [projects] (
282[id] INT PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
283[name] VARCHAR (500) NOT NULL,
284[description] VARCHAR (1000) NOT NULL,
285[percentComplete] DECIMAL NOT NULL,
286[starts] DATETIME,
287[ends] INT,
288[lastUpdate] DATETIME);
289
290CREATE TABLE [projectSteps] (
291[id] INT PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
292[projectId] INT NOT NULL,
293[name] VARCHAR (500) NOT NULL,
294[description] VARCHAR (1000) NOT NULL,
295[percentComplete] DECIMAL NOT NULL,
296[start] DATETIME NOT NULL,
297[end] DATETIME NOT NULL,
298[lastUpdate] DATETIME,
299FOREIGN KEY([projectId]) REFERENCES [projects]([id]));
300
301CREATE TABLE [questions] (
302[id] INT PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
303[ask] INT NOT NULL,
304[reply] INT NOT NULL,
305[question] TEXT NOT NULL,
306[answer] TEXT NOT NULL,
307[created] DATETIME NOT NULL,
308[answered] DATETIME NOT NULL,
309[published] DATETIME,
310FOREIGN KEY([ask]) REFERENCES [users]([id]),
311FOREIGN KEY([reply]) REFERENCES [users]([id]));
312
313CREATE TABLE [general] (
314[name] VARCHAR (100) NOT NULL,
315[value] TEXT NOT NULL);
316
317