· 6 years ago · May 02, 2019, 06:14 PM
1/*
2-=-=-=-=-=-=-=-
3SETUP SCRIPT
4-=-=-=-=-=-=-=-
5
6Setup script for the Cofano information application
7Created by group 12
8
9 */
10
11/*
12General explanation about versioning:
13Our client wanted to have all versions of all entities to be saved.
14Then, they wanted to be able to restore the previous versions, including "deleted" entries.
15
16The "normal" tables like Company, Employee and Product contain all versions of all object of that type.
17If they are different versions for the same real-life entity, they have the same ID, but their timestamps will differ.
18By default, the newest version is picked. If such a version is marked as "deleted" (a boolean), the next version is picked, etc.
19
20This presents a problem for making foreign keys. As example, I will pick Company-Employee (1-to-many).
21Normally, the Employee would have an attribute containing the primary key of the Company.
22In versioning tables, that's not desirable, since that would lock a specific version of an Employee to
23a specific version of a Company, while one would rather bind the Employee to an (existing) Company with
24the given ID.
25In such a case, you don't care to which version the foreign key as, as long as the ID always exists,
26and is not deleted.
27
28In short (for this example): you don't want the foreign key to be the primary key of another table,
29but to the newest version of that company.
30
31In order to accomplish this, the initial idea was to use views, which would be a filtered view of the
32versioned tables, such that the View would only contain the "newest" version of each real-life entity.
33Unfortunately, foreign keys have to point to tables, not views, so even a Materialized View wouldn't work
34in this scenario.
35
36To circumvent this issue, we have created "newest"-tables, which contains data as described above, while also
37being "complete"-types (so that all relevant data is sent in one go). Since it's a table, and not a View,
38it can have Foreign Key restraints to other "newest"-tables. Each "newest"-table is updated
39by using Triggers on the versioned tables.
40
41These triggers look at which ID changed (either marked "deleted" or added), and remove the current
42entry with that ID. Then, they pick the (potentially different) new "newest version" from the versioned tables,
43according to the timestamps and the deleted booleans.
44
45Since these triggers first delete and then insert, the foreign key restraints have to be declared slightly
46somewhat differently. They are now deferrable, meaning that they have to be satisfied after each transaction,
47instead of after each statement. This thus allows them to be temporarily invalid during a transaction.
48
49Views, however, are used. When converting from the "barebones" type to the complete-type, a View is used.
50This allows us to query complete, archived types. Thus, the views and the newest-types have exactly the
51same attributes.
52
53Unfortunately, a table can only inherit from another table, so the attributes have to be repeated for the
54newest-table. It is important that the order and naming remains the same.
55The order has to be the same in order to use the "company.*"-notation.
56The naming has to be the same in order to be compatible with the RowMappers used in Spring.
57 */
58
59/*
60Drops all the tables, views and functions if they exist in the database
61 */
62DROP TABLE IF EXISTS Description, Document, UsageInformation, Product, Employee, Company, newest_company, newest_employee, newest_product, newest_usageinformation, newest_document, Image CASCADE;
63DROP VIEW IF EXISTS complete_company, complete_employee, complete_product, complete_usageinformation, complete_document CASCADE;
64DROP FUNCTION IF EXISTS getShortDescription(INTEGER) CASCADE;
65DROP FUNCTION IF EXISTS getdescription(INTEGER) CASCADE;
66
67/*
68Creates function that returns the short description of a given descriptionID
69 */
70CREATE OR REPLACE FUNCTION getShortDescription(did INTEGER) RETURNS TEXT AS '
71 BEGIN
72 IF did IS NOT NULL AND did IN (SELECT description.desid FROM description) THEN
73 RETURN (SELECT description.shortDescription FROM description WHERE desid = did);
74 ELSE
75 RETURN NULL;
76 END IF;
77 END;
78' LANGUAGE plpgsql;
79
80/*
81Creates function that returns the description of a given descriptionID
82 */
83CREATE OR REPLACE FUNCTION getDescription(did INTEGER) RETURNS TEXT AS '
84 BEGIN
85 IF did IS NOT NULL AND did IN (SELECT description.desid FROM description) THEN
86 RETURN (SELECT description.description FROM description WHERE desid = did);
87 ELSE
88 RETURN NULL;
89 END IF;
90 END;
91' LANGUAGE plpgsql;
92
93
94/*
95Creates Description table with columns for descriptionID, shortDescription, description and tsvector
96 */
97CREATE TABLE Description (
98 desID SERIAL,
99 shortDescription text,
100 description text,
101 ts tsvector,
102 PRIMARY KEY (desID)
103);
104
105/*
106Creates Image table with columns for imageID, name, image and tsvector
107 */
108CREATE TABLE Image (
109 iID SERIAL NOT NULL,
110 name text NOT NULL,
111 image bytea NOT NULL,
112 ts tsvector,
113 PRIMARY KEY (iID)
114);
115
116/*
117Creates Company table with columns for companyID, name, phoneNumber, website,
118address, descriptionID, imageID, timestamp, editReason, author, tsvector and deleted boolean
119 */
120CREATE TABLE Company (
121 cID SERIAL,
122 name text NOT NULL,
123 phoneNumber VARCHAR(20),
124 website text,
125 address text,
126 descriptionID INT,
127 imageID INT,
128 -- Looks like: "2004-10-19 10:23:54"
129 timestamp TIMESTAMP DEFAULT (current_timestamp) UNIQUE,
130 editReason text,
131 author text,
132 deleted BOOLEAN DEFAULT FALSE,
133 ts tsvector,
134 PRIMARY KEY (cID, timestamp),
135 FOREIGN KEY (descriptionID) REFERENCES Description(desID),
136 FOREIGN KEY (imageID) REFERENCES Image(iID)
137);
138
139/*
140Creates view for a complete Company, which includes the short description and description instead of descriptionID
141 */
142CREATE VIEW complete_company AS
143 SELECT DISTINCT
144 c.cid AS cID,
145 c.name AS cName,
146 c.imageid AS cImageID,
147 cast(c.phonenumber AS TEXT) AS cPhoneNumber,
148 c.website AS cWebsite,
149 c.address AS cAddress,
150 c.descriptionid AS cDescriptionID,
151 getShortDescription(c.descriptionID) AS cShortDescription,
152 getDescription(c.descriptionid) AS cDescription,
153 c.timestamp AS cEditTime,
154 c.editReason AS cEditReason,
155 c.author AS cAuthor,
156 c.deleted AS cDeleted
157 FROM Company as c;
158
159/*
160Creates table for the newest Company, which includes the (Short) description instead of just descriptionID
161 */
162CREATE TABLE newest_company (
163 cID INTEGER,
164 cName TEXT,
165 cImageID INTEGER,
166 cPhoneNumber TEXT,
167 cWebsite TEXT,
168 cAddress TEXT,
169 cDescriptionID INTEGER,
170 cShortDescription TEXT,
171 cDescription TEXT,
172 cEditTime TIMESTAMP,
173 cEditReason TEXT,
174 cAuthor TEXT,
175 cDeleted BOOLEAN,
176 PRIMARY KEY (cID),
177 FOREIGN KEY (cImageID) REFERENCES Image(iID),
178 FOREIGN KEY (cDescriptionID) REFERENCES Description(desID)
179);
180
181/*
182Creates a trigger function that updates the newest company table when there's a newer version available
183 */
184CREATE OR REPLACE FUNCTION update_company_newest()
185 RETURNS TRIGGER AS '
186DECLARE
187 company complete_company;
188BEGIN
189 IF NEW.cID IN (SELECT cID FROM newest_company) THEN
190 DELETE FROM newest_company WHERE cID = NEW.cID;
191 END IF;
192 company := (SELECT c1 FROM complete_company AS c1 WHERE c1.cID = NEW.cID AND c1.cDeleted = FALSE AND NOT EXISTS(
193 SELECT * FROM complete_company AS c2 WHERE c1.cID = c2.cID
194 AND c2.cDeleted = FALSE
195 AND c2.cEditTime > c1.cEditTime));
196 IF company.cID IS NOT NULL THEN
197 INSERT INTO newest_company VALUES (company.*);
198 END IF;
199 RETURN NULL;
200END;
201' LANGUAGE plpgsql;
202
203/*
204Creates a trigger that executes update_company_newest() for every row
205 */
206CREATE TRIGGER company_insert
207AFTER INSERT OR UPDATE ON Company
208FOR EACH ROW
209EXECUTE PROCEDURE update_company_newest();
210
211/*
212Creates a function that checks if the id exists in the company table
213 */
214CREATE OR REPLACE FUNCTION FindInCompany(INT) RETURNS BOOLEAN AS '
215 BEGIN
216 IF exists(SELECT cID FROM Company WHERE cID = $1) THEN RETURN TRUE;
217 ELSE RETURN FALSE;
218 END IF;
219 END;
220' LANGUAGE 'plpgsql';
221
222/*
223Creates Employee table, with columns for employeeID, companyID, name, phoneNumber1 & 2,
224email1 & 2, position, notesID, imageID, timestamp, editReason, author, tsvector and deleted boolean
225 */
226CREATE TABLE Employee (
227 eID SERIAL NOT NULL,
228 cID INT NOT NULL,
229 name text NOT NULL ,
230 phoneNumber1 VARCHAR(20),
231 phoneNumber2 VARCHAR(20),
232 email1 text,
233 email2 text,
234 position text,
235 notesID INT,
236 imageID INT,
237 timestamp TIMESTAMP NOT NULL DEFAULT (current_timestamp) UNIQUE,
238 editReason text,
239 author text,
240 deleted BOOLEAN DEFAULT FALSE,
241 ts tsvector,
242 PRIMARY KEY (eID, timestamp),
243 FOREIGN KEY (notesID) REFERENCES Description(desID),
244 FOREIGN KEY (imageID) REFERENCES Image(iID),
245 UNIQUE (eID, cID, timestamp)
246);
247
248/*
249Creates a view for a complete employee, which includes the (short) description instead of just description id
250and the complete company the employee works at
251 */
252CREATE VIEW complete_employee AS
253 SELECT DISTINCT
254 e.eid AS eID,
255 e.name AS eName,
256 e.imageid AS eImageID,
257 cast(e.phonenumber1 AS TEXT) AS ePhoneNumber1,
258 cast(e.phonenumber2 AS TEXT) AS ePhoneNumber2,
259 e.email1 AS eEmail1,
260 e.email2 AS eEmail2,
261 e.position AS ePosition,
262 e.notesid AS eNotesID,
263 getShortDescription(e.notesID) AS eShortNotes,
264 getDescription(e.notesid) AS eNotes,
265 e.timestamp AS eEditTime,
266 e.editreason AS eEditReason,
267 e.author AS eAuthor,
268 e.deleted AS eDeleted,
269 c.cID AS cID,
270 c.cName AS cName,
271 c.cImageID AS cImageID,
272 cast(c.cPhoneNumber AS TEXT) AS cPhoneNumber,
273 c.cWebsite AS cWebsite,
274 c.cAddress AS cAddress,
275 c.cDescriptionID AS cDescriptionID,
276 getShortDescription(c.cDescriptionID) AS cShortDescription,
277 getDescription(c.cDescriptionID) AS cDescription,
278 c.cEditTime AS cEditTime,
279 c.cEditReason AS cEditReason,
280 c.cAuthor AS cAuthor,
281 c.cDeleted AS cDeleted
282 FROM Employee AS e, newest_company as c
283 WHERE e.cid = c.cid;
284
285/*
286Creates table for the newest employee, which includes the (short) description instead of just description id
287and the complete company the employee works at
288 */
289CREATE TABLE newest_employee (
290 eID INTEGER,
291 eName TEXT,
292 eImageID INTEGER,
293 ePhoneNumber1 TEXT,
294 ePhoneNumber2 TEXT,
295 eEmail1 TEXT,
296 eEmail2 TEXT,
297 ePosition TEXT,
298 eNotesID INTEGER,
299 eShortNotes TEXT,
300 eNotes TEXT,
301 eEditTime TIMESTAMP,
302 eEditReason TEXT,
303 eAuthor TEXT,
304 eDeleted BOOLEAN,
305 cID INTEGER,
306 cName TEXT,
307 cImageID INTEGER,
308 cPhoneNumber TEXT,
309 cWebsite TEXT,
310 cAddress TEXT,
311 cDescriptionID INTEGER,
312 cShortDescription TEXT,
313 cDescription TEXT,
314 cEditTime TIMESTAMP,
315 cEditReason TEXT,
316 cAuthor TEXT,
317 cDeleted BOOLEAN,
318 PRIMARY KEY (eID),
319 FOREIGN KEY (cID) REFERENCES newest_company(cID) DEFERRABLE INITIALLY DEFERRED,
320 FOREIGN KEY (eNotesID) REFERENCES Description(desID),
321 FOREIGN KEY (cDescriptionID) REFERENCES Description(desID),
322 FOREIGN KEY (eImageID) REFERENCES Image(iID),
323 FOREIGN KEY (cImageID) REFERENCES Image(iID)
324);
325
326/*
327Creates a trigger function that updates the newest employee table when there's a newer version available
328 */
329CREATE OR REPLACE FUNCTION update_employee_newest()
330 RETURNS TRIGGER AS '
331DECLARE
332 employee complete_employee;
333BEGIN
334 IF NEW.eID IN (SELECT eID FROM newest_employee) THEN
335 DELETE FROM newest_employee WHERE eID = NEW.eID;
336 END IF;
337 employee := (SELECT e1 FROM complete_employee AS e1 WHERE new.eID = e1.eID AND e1.eDeleted = FALSE AND NOT EXISTS(
338 SELECT * FROM complete_employee AS e2 WHERE e2.eID = e1.eID AND e2.eDeleted = FALSE AND e2.eEditTime > e1.eEditTime
339 ));
340 IF employee.eID IS NOT NULL THEN
341 INSERT INTO newest_employee VALUES (employee.*);
342 END IF;
343 RETURN NULL;
344END;
345' LANGUAGE plpgsql;
346
347/*
348Creates a trigger that executes update_employee_newest() for every row
349 */
350CREATE TRIGGER employee_insert
351AFTER INSERT OR UPDATE ON Employee
352FOR EACH ROW
353EXECUTE PROCEDURE update_employee_newest();
354
355/*
356Creates Product table, with columns for ProductID, name, descriptionID, imageID, timestamp,
357editReason, author, tsvector and deleted boolean
358 */
359CREATE TABLE Product (
360 pID SERIAL NOT NULL,
361 name text NOT NULL,
362 descriptionID INT,
363 imageID INT,
364 timestamp TIMESTAMP NOT NULL DEFAULT (current_timestamp) UNIQUE,
365 editReason text,
366 author text,
367 deleted BOOLEAN DEFAULT FALSE,
368 ts tsvector,
369 PRIMARY KEY (pID, timestamp),
370 FOREIGN KEY (descriptionID) REFERENCES Description(desID),
371 FOREIGN KEY (imageID) REFERENCES Image(iID)
372);
373
374/*
375Creates view for a complete product which includes (short) description instead of just descriptionID
376 */
377CREATE OR REPLACE VIEW complete_product AS
378 SELECT pID,
379 name AS pName,
380 imageID AS pImageID,
381 editReason AS pEditReason,
382 author AS pAuthor,
383 descriptionID AS pDescriptionID,
384 getShortDescription(descriptionID) AS pShortDescription,
385 getDescription(descriptionID) AS pDescription,
386 timestamp AS pEditTime,
387 deleted as pDeleted
388 FROM Product;
389
390/*
391Creates table for the newest Product, which includes the (short) description instead of descriptionID
392 */
393CREATE TABLE newest_product(
394 pID INTEGER,
395 pName TEXT,
396 pImageID INTEGER,
397 pEditReason TEXT,
398 pAuthor TEXT,
399 pDescriptionID INTEGER,
400 pShortDescription TEXT,
401 pDescription TEXT,
402 pEditTime TIMESTAMP,
403 pDeleted BOOLEAN,
404 PRIMARY KEY (pID),
405 FOREIGN KEY (pImageID) REFERENCES Image(iID),
406 FOREIGN KEY (pDescriptionID) REFERENCES Description(desID)
407);
408
409/*
410Creates a trigger function that updates the newest product table when there's a newer version available
411 */
412CREATE OR REPLACE FUNCTION update_product_newest()
413 RETURNS TRIGGER AS '
414DECLARE
415 product complete_product;
416BEGIN
417 IF NEW.pID IN (SELECT pID FROM newest_product) THEN
418 DELETE FROM newest_product WHERE pID = NEW.pID;
419 END IF;
420 product := (SELECT p1 FROM complete_product AS p1 WHERE new.pID = p1.pID AND p1.pDeleted = FALSE AND NOT EXISTS(
421 SELECT * FROM complete_product AS p2 WHERE p2.pID = p1.pID AND p2.pDeleted = FALSE AND p2.pEditTime > p1.pEditTime
422 ));
423 IF product.pID IS NOT NULL THEN
424 INSERT INTO newest_product VALUES (product.*);
425 END IF;
426 RETURN NULL;
427END;
428' LANGUAGE plpgsql;
429
430/*
431Creates a trigger that executes update_product_newest() for every row
432 */
433CREATE TRIGGER product_insert
434AFTER INSERT OR UPDATE ON Product
435FOR EACH ROW
436EXECUTE PROCEDURE update_product_newest();
437
438/*
439Creates a function that checks if the id exists in the product table
440 */
441CREATE OR REPLACE FUNCTION FindInProduct(INT) RETURNS BOOLEAN AS '
442 BEGIN
443 IF exists(SELECT pID FROM Product WHERE pID = $1) THEN RETURN TRUE;
444 ELSE RETURN FALSE;
445 END IF;
446 END;
447' LANGUAGE 'plpgsql';
448
449/*
450Creates usageinformation table, with columns for companyID, productID,
451descriptionID, timestamp, editReason, author, tsvector and deleted boolean
452 */
453CREATE TABLE UsageInformation (
454 cID INT NOT NULL CHECK (FindInCompany(cID)),
455 pID INT NOT NULL CHECK (FindInProduct(pID)),
456 descriptionID INT,
457 timestamp TIMESTAMP NOT NULL DEFAULT (current_timestamp),
458 editReason text,
459 author text,
460 deleted BOOLEAN DEFAULT FALSE,
461 ts tsvector,
462 PRIMARY KEY (cID, pID, timestamp),
463 FOREIGN KEY (descriptionID) REFERENCES Description(desID)
464);
465
466/*
467Creates view for a complete usageinformation which includes (short) description instead of just descriptionID,
468a complete company and complete product.
469 */
470CREATE VIEW complete_usageinformation AS
471 SELECT DISTINCT
472 c.cID,
473 c.cName,
474 c.cImageID,
475 cast(c.cPhoneNumber AS TEXT) AS cPhoneNumber,
476 c.cWebsite,
477 c.cAddress,
478 c.cDescriptionID,
479 getShortDescription(c.cDescriptionID) AS cShortDescription,
480 getDescription(c.cDescriptionID) AS cDescription,
481 c.cEditTime,
482 c.cEditReason,
483 c.cAuthor,
484 c.cDeleted,
485 p.pid,
486 p.pName,
487 p.pImageID,
488 p.pDescriptionID,
489 getShortDescription(p.pDescriptionID) AS pShortDescription,
490 getDescription(p.pDescriptionID) AS pDescription,
491 p.pEditTime,
492 p.pEditReason,
493 p.pAuthor,
494 p.pDeleted,
495 u.descriptionid,
496 getShortDescription(u.descriptionID) AS uShortDescription,
497 getDescription(u.descriptionid) AS uDescription,
498 u.timestamp AS uEditTime,
499 u.editreason AS uEditReason,
500 u.author AS uAuthor,
501 u.deleted AS uDeleted
502 FROM UsageInformation AS u,newest_product AS p, newest_company AS c
503 WHERE u.pid = p.pid
504 AND u.cid = c.cid;
505
506/*
507Creates table for the newest usageinformation which includes (short) description instead of just descriptionID,
508a complete company and complete product.
509 */
510CREATE TABLE newest_usageinformation(
511 cID INTEGER,
512 cName TEXT,
513 cImageID INTEGER,
514 cPhoneNumber TEXT,
515 cWebsite TEXT,
516 cAddress TEXT,
517 cDescriptionID INTEGER,
518 cShortDescription TEXT,
519 cDescription TEXT,
520 cEditTime TIMESTAMP,
521 cEditReason TEXT,
522 cAuthor TEXT,
523 cDeleted BOOLEAN,
524 pID INTEGER,
525 pName TEXT,
526 pImageID INTEGER,
527 pDescriptionID INTEGER,
528 pShortDescription TEXT,
529 pDescription TEXT,
530 pEditTime TIMESTAMP,
531 pEditReason TEXT,
532 pAuthor TEXT,
533 pDeleted BOOLEAN,
534 uDescriptionID INTEGER,
535 uShortDescription TEXT,
536 uDescription TEXT,
537 uEditTime TIMESTAMP,
538 uEditReason TEXT,
539 uAuthor TEXT,
540 uDeleted BOOLEAN,
541 PRIMARY KEY (cID, pID),
542 FOREIGN KEY (cID) REFERENCES newest_company(cID) DEFERRABLE INITIALLY DEFERRED,
543 FOREIGN KEY (pID) REFERENCES newest_product(pID) DEFERRABLE INITIALLY DEFERRED,
544 FOREIGN KEY (pImageID) REFERENCES Image(iID),
545 FOREIGN KEY (cImageID) REFERENCES Image(iID),
546 FOREIGN KEY (cDescriptionID) REFERENCES Description(desID),
547 FOREIGN KEY (pDescriptionID) REFERENCES Description(desID),
548 FOREIGN KEY (uDescriptionID) REFERENCES Description(desID)
549);
550
551/*
552Creates a trigger function that updates the newest usageinformation table when there's a newer version available
553 */
554CREATE OR REPLACE FUNCTION update_usageinformation_newest()
555 RETURNS TRIGGER AS '
556DECLARE
557 uu complete_usageinformation;
558BEGIN
559 DELETE FROM newest_usageinformation WHERE pID = NEW.pID AND cID = NEW.cID;
560 uu := (SELECT u1 FROM complete_usageinformation AS u1 WHERE new.pID = u1.pID AND new.cID = u1.cID AND u1.uDeleted = FALSE AND NOT EXISTS(
561 SELECT * FROM complete_usageinformation AS u2 WHERE u2.pID = u1.pID AND u2.cID = u1.cID AND u2.uDeleted = FALSE AND u2.uEditTime > u1.uEditTime
562 ));
563 IF uu.cID IS NOT NULL AND uu.pID IS NOT NULL THEN
564 INSERT INTO newest_usageinformation VALUES (uu.*);
565 END IF;
566 RETURN NULL;
567END;
568' LANGUAGE plpgsql;
569
570/*
571Creates a trigger that executes update_usageinformation_newest() for every row
572 */
573CREATE TRIGGER usageinformation_insert
574AFTER INSERT OR UPDATE ON UsageInformation
575FOR EACH ROW
576EXECUTE PROCEDURE update_usageinformation_newest();
577
578/*
579Creates document table, with columns for documentID, productID, name, descriptionID, imageID, timestamp, editReason, author, tsvector and deleted boolean
580 */
581CREATE TABLE Document (
582 dID SERIAL NOT NULL,
583 pID INT NOT NULL CHECK (FindInProduct(pID)),
584 name text NOT NULL,
585 descriptionID INT,
586 imageID INT,
587 timestamp TIMESTAMP NOT NULL DEFAULT (current_timestamp) UNIQUE,
588 editReason text,
589 author text,
590 deleted BOOLEAN DEFAULT FALSE,
591 ts tsvector,
592 PRIMARY KEY (dID, timestamp),
593 FOREIGN KEY (descriptionID) REFERENCES Description(desID),
594 FOREIGN KEY (imageID) REFERENCES Image(iID),
595 UNIQUE (dID, pID, timestamp)
596);
597
598/*
599Creates view for a complete document which includes (short) description instead of just descriptionID and complete product
600 */
601CREATE VIEW complete_document AS
602 SELECT DISTINCT
603 p.pid,
604 p.pName,
605 p.pImageID,
606 p.pDescriptionID,
607 getShortDescription(p.pDescriptionID) AS pShortDescription,
608 getDescription(p.pDescriptionID) AS pDescription,
609 p.pEditReason,
610 p.pEditTime,
611 p.pAuthor,
612 p.pDeleted,
613 doc.descriptionid AS dDescriptionID,
614 getShortDescription(doc.descriptionID) AS dShortDescription,
615 getDescription(doc.descriptionid) AS dDescription,
616 doc.name AS dName,
617 doc.did,
618 doc.timestamp AS dEditTime,
619 doc.editreason AS dEditReason,
620 doc.author AS dAuthor,
621 doc.deleted AS dDeleted
622 FROM Document AS doc, newest_product AS p
623 WHERE p.pid = doc.pid;
624
625/*
626Creates table for the newest document which includes (short) description instead of just descriptionID and commplete product
627 */
628CREATE TABLE newest_document (
629 pID INTEGER,
630 pName TEXT,
631 pImageID INTEGER,
632 pDescriptionID INTEGER,
633 pShortDescription TEXT,
634 pDescription TEXT,
635 pEditReason TEXT,
636 pEditTime TIMESTAMP,
637 pAuthor TEXT,
638 pDeleted BOOLEAN,
639 dDescriptionID INTEGER,
640 dShortDescription TEXT,
641 dDescription TEXT,
642 dName TEXT,
643 dID INTEGER,
644 dEditTime TIMESTAMP,
645 dEditReason TEXT,
646 dAuthor TEXT,
647 dDeleted BOOLEAN,
648 PRIMARY KEY (dID),
649 FOREIGN KEY (pID) REFERENCES newest_product(pID) DEFERRABLE INITIALLY DEFERRED,
650 FOREIGN KEY (pDescriptionID) REFERENCES Description(desID),
651 FOREIGN KEY (dDescriptionID) REFERENCES Description(desID),
652 FOREIGN KEY (pImageID) REFERENCES Image(iID)
653);
654
655/*
656Creates a trigger function that updates the newest document table when there's a newer version available
657 */
658CREATE OR REPLACE FUNCTION update_document_newest()
659 RETURNS TRIGGER AS '
660DECLARE
661 doc complete_document;
662BEGIN
663 DELETE FROM newest_document WHERE dID = NEW.dID;
664 doc := (SELECT d1 FROM complete_document AS d1 WHERE new.dID = d1.dID AND d1.dDeleted = FALSE AND NOT EXISTS(
665 SELECT * FROM complete_document AS d2 WHERE d2.dID = d1.dID AND d2.dDeleted = FALSE AND d2.dEditTime > d1.dEditTime
666 ));
667 IF doc.dID IS NOT NULL THEN
668 INSERT INTO newest_document VALUES (doc.*);
669 END IF;
670 RETURN NULL;
671END;
672' LANGUAGE plpgsql;
673
674/*
675Creates a trigger that executes update_document_newest() for every row
676 */
677CREATE TRIGGER document_insert
678AFTER INSERT OR UPDATE ON Document
679FOR EACH ROW
680EXECUTE PROCEDURE update_document_newest();
681
682/*
683Creates a trigger that updates the tsvector for the description table before a new version description is added
684 */
685CREATE TRIGGER description_tsupdate
686BEFORE INSERT OR UPDATE ON Description
687FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(ts, 'pg_catalog.english', description);
688
689/*
690Creates a trigger that updates the tsvector for the image table before a new version description is added
691 */
692CREATE TRIGGER description_tsupdate
693BEFORE INSERT OR UPDATE ON Image
694FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(ts, 'pg_catalog.english', name);
695
696/*
697Creates a trigger that updates the tsvector for the company table before a new version description is added
698 */
699CREATE TRIGGER description_tsupdate
700BEFORE INSERT OR UPDATE ON Company
701FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(ts, 'pg_catalog.english', name, phoneNumber, website, address, author);
702
703/*
704Creates a trigger that updates the tsvector for the employee table before a new version is added
705 */
706CREATE TRIGGER description_tsupdate
707BEFORE INSERT OR UPDATE ON Employee
708FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(ts, 'pg_catalog.english', name, phoneNumber1, phoneNumber2, email1, email2, position, author);
709
710/*
711Creates a trigger that updates the tsvector for the product table before a new version is added
712 */
713CREATE TRIGGER description_tsupdate
714BEFORE INSERT OR UPDATE ON Product
715FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(ts, 'pg_catalog.english', name, author);
716
717/*
718Creates a trigger that updates the tsvector for the document table before a new version is added
719 */
720CREATE TRIGGER description_tsupdate
721BEFORE INSERT OR UPDATE ON Document
722FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(ts, 'pg_catalog.english', name, author);