J. Correia Lopes

FEUP/DEI & INESC TEC

User Tools

Site Tools


teach:lbaw:medialib:a9

Main Accesses to the database and transactions (A9)

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.

1. Main Accesses

Main accesses to the database.

1.1 M01: Authentication and Individual Profile

SQL101 Creates a new user in the platform
Web Resource R105
 INSERT INTO "user" (email, name, password, img, is_admin) 
  VALUES ($email, $name, $password, $image, FALSE);  

1.2 M02: Works

SQL201 Search works by title and description
Web Resource R202
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;

1.3 M03: Reviews and Wish list

SQL301 Review a work
Web Resource R302
 INSERT INTO review (id_user, id_work, comment, rating) 
  VALUES ($user_id, $work_id, $comment, $rate);   

1.4 M04: Loans

SQL401 Retrieve user's current loans
Web Resource R401
 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; 

2. Transactions

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;

LBAW

[MediaLibrary]

teach/lbaw/medialib/a9.txt · Last modified: 04/04/2019 10:48 by Correia Lopes