resources/sql/base-schema.sql

   1 --
   2 -- base-schema.sql - The baseline database schema for Giyusit (revision 3)
   3 --
   4 PRAGMA encoding = 'UTF-8';
   5
   6 --
   7 -- System tables
   8 --
   9 CREATE TABLE FileParams
  10 (
  11         Key             VARCHAR UNIQUE NOT NULL,
  12         Value   VARCHAR
  13 );
  14
  15 INSERT INTO FileParams (Key, Value) VALUES ('SchemaRevision', '3');
  16 INSERT INTO FileParams (Key, Value) VALUES ('DefaultCandidateStatus', '1');
  17 INSERT INTO FileParams (Key, Value) VALUES ('DefaultAttendaceType', '2');
  18
  19 --
  20 CREATE TABLE DataViews
  21 (
  22         Title                   VARCHAR NOT NULL,
  23         CategoryID              INTEGER NOT NULL,
  24         Query                   VARCHAR NOT NULL,
  25         Ruler                   VARCHAR NOT NULL,
  26         UserInd         BOOLEAN DEFAULT 'false'
  27 );
  28
  29 CREATE TABLE DataViewCategories
  30 (
  31     ID          INTEGER PRIMARY KEY,
  32     Name        VARCHAR NOT NULL
  33 );
  34
  35 INSERT INTO DataViewCategories (ID, Name) VALUES (1, 'מועמדים');
  36 INSERT INTO DataViewCategories (ID, Name) VALUES (2, 'אירועים');
  37
  38 --
  39 CREATE TABLE RulerLibrary
  40 (
  41         Name    VARCHAR NOT NULL,
  42         Ruler   VARCHAR NOT NULL
  43 );
  44
  45 --
  46 CREATE TABLE BooleanValues
  47 (
  48         Key             VARCHAR,
  49         Value   VARCHAR
  50 );
  51
  52 INSERT INTO BooleanValues (Key, Value) VALUES ('true', 'כן');
  53 INSERT INTO BooleanValues (Key, Value) VALUES ('false', 'לא');
  54
  55 --
  56 -- Candidate tables
  57 --
  58 --
  59 CREATE TABLE Candidates
  60 (
  61         ID                              INTEGER PRIMARY KEY,
  62         NationalID              INTEGER,
  63         FirstName               VARCHAR NOT NULL ON CONFLICT IGNORE,
  64         LastName                VARCHAR,
  65         Gender                  VARCHAR,
  66         Address                 VARCHAR,
  67         City                    VARCHAR,
  68         ZipCode                 VARCHAR,
  69         HomePhone               VARCHAR,
  70         CellPhone               VARCHAR,
  71         EMail                   VARCHAR,
  72         WrongDetailsInd BOOLEAN,
  73
  74         Origin                  VARCHAR,
  75         School                  VARCHAR,
  76         Notes                   VARCHAR,
  77         OwnerID                 INTEGER,
  78         RecruiterID     INTEGER,
  79         StatusID                INTEGER,
  80
  81         CanceledDahashInd       BOOLEAN,
  82         SignedDahashInd         BOOLEAN
  83 );
  84
  85 CREATE TRIGGER OnCandidateInsert AFTER INSERT ON Candidates
  86 WHEN NEW.StatusID IS NULL
  87 BEGIN
  88         UPDATE Candidates SET StatusID =
  89                 (SELECT Value FROM FileParams WHERE Key = 'DefaultCandidateStatus')
  90                 WHERE ID = NEW.ID;
  91
  92         INSERT INTO CandidateStatuses (CandidateID, PriStatusID)
  93                 VALUES (NEW.ID, (SELECT Value FROM FileParams WHERE Key = 'DefaultCandidateStatus'));
  94 END;
  95
  96 CREATE TRIGGER OnCandidateDelete AFTER DELETE ON Candidates
  97 BEGIN
  98         DELETE FROM CandidateStatuses WHERE CandidateID = OLD.ID;
  99         DELETE FROM EventAttendance WHERE CandidateID = OLD.ID;
 100 END;
 101
 102 --
 103 CREATE TABLE CandidateStatuses
 104 (
 105         ID                              INTEGER PRIMARY KEY,
 106         CandidateID     INTEGER NOT NULL,
 107         PriStatusID             INTEGER NOT NULL,
 108         SecStatusID             INTEGER,
 109         StartTime               TIMESTAMP DEFAULT CURRENT_DATE,
 110
 111         UNIQUE(CandidateID, StartTime)
 112 );
 113
 114 CREATE TRIGGER OnCStatusInsert AFTER INSERT ON CandidateStatuses
 115 BEGIN
 116         UPDATE Candidates SET StatusID =
 117                 (SELECT PriStatusID FROM CandidateStatuses
 118                         WHERE CandidateID = NEW.CandidateID ORDER BY StartTime DESC)
 119                 WHERE ID = NEW.CandidateID;
 120 END;
 121
 122 CREATE TRIGGER OnCStatusDelete AFTER DELETE ON CandidateStatuses
 123 BEGIN
 124         UPDATE Candidates SET StatusID =
 125                 (SELECT PriStatusID FROM CandidateStatuses
 126                         WHERE CandidateID = OLD.CandidateID ORDER BY StartTime DESC)
 127                 WHERE ID = OLD.CandidateID;
 128 END;
 129
 130 --
 131 CREATE TABLE CandidateStatusValues
 132 (
 133         ID                      INTEGER PRIMARY KEY,
 134         Name            VARCHAR NOT NULL,
 135
 136         ActiveInd       BOOLEAN DEFAULT 'true',
 137         EndDate         TIMESTAMP
 138 );
 139
 140
 141 INSERT INTO CandidateStatusValues (ID, Name) VALUES (1, 'חדש');
 142
 143 --
 144 -- Event tables
 145 --
 146 --
 147 CREATE TABLE Events
 148 (
 149         ID                      INTEGER PRIMARY KEY,
 150         TypeID      INTEGER NOT NULL,
 151         OwnerID     INTEGER,
 152
 153         Name            VARCHAR NOT NULL,
 154         Location        VARCHAR,
 155         StartDate       DATE,
 156     EndDate     DATE,
 157     Notes       VARCHAR
 158 );
 159
 160 CREATE TRIGGER OnEventDelete AFTER DELETE ON Events
 161 BEGIN
 162         DELETE FROM EventAttendance WHERE EventID = OLD.ID;
 163 END;
 164
 165 --
 166 CREATE TABLE EventTypes
 167 (
 168         ID              INTEGER PRIMARY KEY,
 169         Name    VARCHAR,
 170
 171         EndDate         TIMESTAMP
 172 );
 173
 174 INSERT INTO EventTypes (Name) VALUES ('שבת פתוחה');
 175 INSERT INTO EventTypes (Name) VALUES ('אורחות');
 176 INSERT INTO EventTypes (Name) VALUES ('כנס מחויבות');
 177 INSERT INTO EventTypes (Name) VALUES ('טיול פסח');
 178 INSERT INTO EventTypes (Name) VALUES ('שבת גיבוש');
 179 INSERT INTO EventTypes (Name) VALUES ('אחר');
 180
 181 --
 182 CREATE TABLE EventAttendance
 183 (
 184         ID                      INTEGER PRIMARY KEY,
 185         CandidateID     INTEGER NOT NULL,
 186         EventID         INTEGER NOT NULL,
 187         AttTypeID       INTEGER,
 188         Notes           VARCHAR,
 189
 190         UNIQUE (CandidateID, EventID) ON CONFLICT IGNORE
 191 );
 192
 193 CREATE TRIGGER OnEAttendanceInsert AFTER INSERT ON EventAttendance
 194 WHEN NEW.AttTypeID IS NULL
 195 BEGIN
 196         UPDATE EventAttendance SET AttTypeID =
 197                 (SELECT Value FROM FileParams WHERE Key = 'DefaultAttendaceType')
 198                 WHERE ID = NEW.ID;
 199 END;
 200
 201 --
 202 CREATE TABLE AttendanceTypes
 203 (
 204         ID                      INTEGER PRIMARY KEY,
 205         Name            VARCHAR NOT NULL,
 206         ActiveInd       BOOLEAN DEFAULT 'true'
 207 );
 208
 209 INSERT INTO AttendanceTypes (ID, Name, ActiveInd) VALUES (1, 'פוטנציאל מחושב', 'true');
 210 INSERT INTO AttendanceTypes (ID, Name, ActiveInd) VALUES (2, 'מעוניין להשתתף', 'true');
 211 INSERT INTO AttendanceTypes (ID, Name, ActiveInd) VALUES (3, 'אישר השתתפות', 'true');
 212 INSERT INTO AttendanceTypes (ID, Name, ActiveInd) VALUES (4, 'השתתף בפועל', 'true');
 213 INSERT INTO AttendanceTypes (ID, Name, ActiveInd) VALUES (5, 'לא מעוניין להשתתף', 'false');
 214 INSERT INTO AttendanceTypes (ID, Name, ActiveInd) VALUES (6, 'כנראה לא ישתתף', 'false');
 215 INSERT INTO AttendanceTypes (ID, Name, ActiveInd) VALUES (7, 'לא השתתף בפועל', 'false');
 216 INSERT INTO AttendanceTypes (ID, Name, ActiveInd) VALUES (8, 'ביטל השתתפות', 'false');
 217
 218 --
 219 -- Staff tables
 220 --
 221 --
 222 CREATE TABLE Staff
 223 (
 224         ID                      INTEGER PRIMARY KEY,
 225         ParentID        INTEGER,
 226         Name            VARCHAR NOT NULL,
 227         Role            VARCHAR,
 228         RealInd         BOOLEAN DEFAULT 'true'
 229 );
 230
 231 CREATE TRIGGER OnStaffDelete AFTER DELETE ON Staff
 232 BEGIN
 233         -- Set owner of owned candidates and events to null
 234         UPDATE Candidates SET OwnerID = NULL WHERE OwnerID = OLD.ID;
 235         UPDATE Candidates SET RecruiterID = NULL WHERE RecruiterID = OLD.ID;
 236
 237         UPDATE Events SET OwnerID = NULL WHERE OwnerID = OLD.ID;
 238
 239         -- Re-parent children
 240         UPDATE Staff SET ParentID = OLD.ParentID WHERE ParentID = OLD.ID;
 241 END;
 242
 243 --
 244 -- Helper views
 245 --
 246 CREATE VIEW AllCandidates AS SELECT
 247                                 C.*,
 248                                 CSV.Name AS Status,
 249                                 CSV.ActiveInd AS ActiveInd,
 250                                 Staff.Name AS Owner,
 251                                 (C.FirstName || coalesce(' ' || C.LastName, '')) AS FullName
 252                         FROM Candidates C
 253                         LEFT OUTER JOIN CandidateStatusValues CSV ON C.StatusID = CSV.ID
 254                         LEFT OUTER JOIN Staff ON C.OwnerID = Staff.ID;
 255
 256 CREATE VIEW StatusesView AS SELECT
 257                 CS.*,
 258                 strftime('%d/%m/%Y', CS.StartTime) AS StartDate,
 259                 CSV.Name AS StatusName
 260         FROM CandidateStatuses CS, CandidateStatusValues CSV
 261         WHERE CS.PriStatusID = CSV.ID
 262         ORDER BY CS.StartTime DESC;
 263
 264 CREATE VIEW EventAttendanceCount AS SELECT
 265                 EventID,
 266                 count(*) AS Attendants
 267         FROM EventAttendance
 268         GROUP BY EventID;
 269
 270 CREATE VIEW EventActiveAttendanceCount AS SELECT
 271                 EventID,
 272                 count(*) AS Attendants
 273         FROM EventAttendance
 274         WHERE AttTypeID IN (SELECT ID FROM AttendanceTypes WHERE ActiveInd = 'true')
 275         GROUP BY EventID;
 276
 277 CREATE VIEW AllEvents AS SELECT
 278                 E.ID AS ID,
 279                 E.TypeID AS TypeID,
 280                 E.OwnerID AS OwnerID,
 281                 E.Name AS Name,
 282                 E.Location AS Location,
 283                 E.Notes AS Notes,
 284                 E.StartDate AS ISOStartDate,
 285                 E.EndDate AS ISOEndDate,
 286                 --
 287                 strftime('%d/%m/%Y', E.StartDate) AS StartDate,
 288                 strftime('%d/%m/%Y', E.EndDate) AS EndDate,
 289                 coalesce(EAAC.Attendants, 0) AS ActiveAttendants,
 290                 EventTypes.Name AS Type,
 291                 Staff.Name AS Owner
 292         FROM Events E
 293         LEFT OUTER JOIN EventActiveAttendanceCount EAAC ON E.ID = EAAC.EventID
 294         LEFT OUTER JOIN EventTypes ON E.TypeID = EventTypes.ID
 295         LEFT OUTER JOIN Staff ON E.OwnerID = Staff.ID;
 296
 297 CREATE VIEW EventCandidatesView AS SELECT
 298                 EA.CandidateID AS ID,
 299                 EA.EventID,
 300                 EA.Notes,
 301                 C.FirstName,
 302                 C.LastName,
 303                 C.Gender,
 304                 C.Address,
 305                 C.City,
 306                 C.ZipCode,
 307                 C.EMail,
 308                 AT.Name AS AttType,
 309                 AT.ActiveInd,
 310                 BV.Value AS Active
 311         FROM EventAttendance EA
 312         LEFT OUTER JOIN Candidates C ON EA.CandidateID = C.ID
 313         LEFT OUTER JOIN AttendanceTypes AT ON EA.AttTypeID = AT.ID
 314         LEFT OUTER JOIN BooleanValues BV ON AT.ActiveInd = BV.Key;
 315
 316 CREATE VIEW CandidateEventsView AS SELECT
 317                 EA.EventID AS ID,
 318                 EA.CandidateID,
 319                 EA.Notes,
 320                 E.Name,
 321                 strftime('%d/%m/%Y', E.StartDate) AS StartDate,
 322                 strftime('%d/%m/%Y', E.EndDate) AS EndDate,
 323                 ET.Name AS Type,
 324                 AT.Name AS AttType,
 325                 AT.ActiveInd,
 326                 BV.Value AS Active
 327         FROM EventAttendance EA
 328         LEFT OUTER JOIN Events E ON EA.EventID = E.ID
 329         LEFT OUTER JOIN AttendanceTypes AT ON EA.AttTypeID = AT.ID
 330         LEFT OUTER JOIN EventTypes ET ON E.TypeID = ET.ID
 331         LEFT OUTER JOIN BooleanValues BV ON AT.ActiveInd = BV.Key;
 332