This artefact shows the main accesses to the database, including the transactions.
For each transaction, the isolation level is explicitly stated and read-only transactions are identified to improve global performance. For each identified access, the SQL code and the reference of web resources (A7) are provided.
Main accesses to the database.
|SQL101||Creates a new user in the platform|
INSERT INTO "user" (email, name, password, img, is_admin) VALUES ($email, $name, $password, $image, FALSE);
|SQL201||Search works by title and description|
SELECT id, title, obs, img, YEAR, ts_rank_cd(textsearch, query) AS rank FROM WORK, to_tsquery($query) AS query, to_tsvector(title || ' ' || obs) AS textsearch WHERE query @@ textsearch ORDER BY rank DESC;
|SQL301||Review a work|
INSERT INTO review (id_user, id_work, comment, rating) VALUES ($user_id, $work_id, $comment, $rate);
|SQL401||Retrieve user's current loans|
SELECT WORK.id, WORK.title, WORK.obs, WORK.img, WORK.year, loan."end", loan."start" FROM WORK, "user", loan, item WHERE loan.id_user = "user".id AND loan.id_work = WORK.id AND loan."end" > now() AND loan.id_user = $userId;
Transactions needed to assure the integrity of the data, with a proper justification.
|T01||Get current loans as well as information about the items|
|Justification||In the middle of the transaction, the insertion of new rows in the loan table can occur, which implies that the information retrieved in both selects is different, consequently resulting in a Phantom Read. It's READ ONLY because it only uses Selects.|
|Isolation level||SERIALIZABLE READ ONLY|
BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY -- Get number of current loans SELECT COUNT(*) FROM LOAN WHERE now() < "end" -- Get ending loans (limit 10) SELECT loan."end", loan."start", item.*, WORK.*, "user".id, "user".name FROM loan INNER JOIN item ON item.id = loan.id_item INNER JOIN WORK ON WORK.id = item.id_work INNER JOIN "user" ON "user".id = loan.id_user WHERE now () < loan."end" ORDER BY loan."end" ASC LIMIT 10; COMMIT;
|T02||Insert a new book|
|Justification||In order to maintain consistency, it's necessary to use a transaction to ensure that the all the code executes without errors. If an error occurs, a ROLLBACK is issued (when the insertion of a book fails, per example). The isolation level is Repeatable Read, because, otherwise, an update of work_id_seq could happen, due to an insert in the table work committed by a concurrent transaction, and as a result, inconsistent data would be stored.|
|Isolation level||REPEATABLE READ|
BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ -- Insert work INSERT INTO WORK (title, obs, img, YEAR, id_user, id_collection) VALUES ($title, $obs, $img, $year, $id_user, $id_collection); -- Insert book INSERT INTO BOOK (id_work, edition, isbn, id_publisher) VALUES (currval('work_id_seq'), $edition, $isbn, $id_publisher); COMMIT;