a6

A6: Indexes, triggers, user functions, transactions and population

The project consists in developing a global marketplace which specializes in the sale of gaming related digital products using redemption keys.

By the end of this sixth artefact we have a clear definiton of every aspect releated with the data base and the date workload expected by the platform and their effects:

There is a definition of indexes in order to power the performance of the database,
Triggers to enforce business rules are defined,
The main queries are already defined by the definition of adequate of stored procedures in the Postgres system.

1. Database Workload

1.1. Tuple Estimation

Relation reference	Relation Name	Order of magnitude	Estimated growth
R01	product	Hundreds	Dozens per month
R02	category	Units	Units per year
R03	genre	Dozens	Units per year
R04	platform	Units	Units per year
R05	product_has_genre	Thousands	Hundreds per month
R06	offer	Thousands	Dozens per day
R07	discount	Hundreds	Dozens per month
R08	image	Thousands	Hundreds per day
R09	regular_user	Thousands	Dozens per day
R010	banned_user	Dozens	Units per month
R011	admin	Units	Units per year
R012	user_order	Thousands	Hundreds per day
R013	feedback	Thousands	Hundreds per day
R014	message	Thousands	Dozens per day
R015	report	Hundreds	Dozens per month
R016	key	Dozens of Thousands	Hundreds per day
R017	ban_appeal	Dozens	Units per month
R019	about_us	Units	no growth
R020	faq	Dozens	Units per year

1.2. Frequent Queries

Query reference	SELECT01
Query description	User Login
Query frequency	Hundreds per day

SELECT id 
FROM regular_user 
WHERE username = $username AND password = $hashedPassword;

Query reference	SELECT02
Query description	Get most popular products and the best available offer for it
Query frequency	Thousands per day

SELECT product.name AS product_name,platform.name, min(offer.price) AS min_price, max(num_sells) AS num_sells, max(discount.rate) AS discount_rate 
FROM active_products JOIN product ON active_products.product_id=product.id
	JOIN offer ON offer.product=product.id
	JOIN active_offers ON offer.id=active_offers.offer_id
	LEFT OUTER JOIN discount ON discount.offer=offer.id
	JOIN product_has_platform pf ON pf.product=product.id
	JOIN platform ON platform.id=pf.platform
WHERE (discount.start_date IS NULL OR (discount.start_date<now() AND discount.end_date > now()))	
GROUP BY product_name,platform.name
ORDER BY num_sells DESC
LIMIT $number_results;

Query reference	SELECT03
Query description	Get most recent products
Query frequency	Thousands per day

SELECT product.name AS product_name, platform.name, min(offer.price) AS min_price, max(num_sells) AS num_sells, max(discount.rate) AS discount_rate, max(product.launch_date)  AS launch_date
FROM active_products JOIN product On active_products.product_id=product.id
	JOIN offer ON offer.product=product.id
	JOIN active_offers ON offer.id=active_offers.offer_id
	LEFT OUTER JOIN discount ON discount.offer=offer.id
    JOIN product_has_platform pf ON pf.product=product.id
	JOIN platform ON platform.id=pf.platform
	WHERE (discount.start_date IS NULL OR (discount.start_date<now() AND discount.end_date > now()))
GROUP BY product_name,platform.name
ORDER BY launch_date DESC
LIMIT $number_results;

Query reference	SELECT04
Query description	Get all products that have certain categories, genres and platforms
Query frequency	Thousands per day

SELECT product.name AS product_name, min(offer.price) AS min_price, max(num_sells) AS num_sells, max(discount.rate) AS discount_rate, max(product.launch_date)  AS launch_date
FROM active_products JOIN product ON product.id=active_products.product_id
	JOIN offer ON offer.product=product.id
	JOIN active_offers ON offer.id=active_offers.offer_id
	LEFT OUTER JOIN discount ON discount.offer=offer.id
	JOIN product_has_genre pg ON pg.product=product.id
	JOIN genre ON pg.genre=genre.id
	JOIN product_has_platform pf ON pf.product=product.id
	JOIN platform ON platform.id=pf.platform
	JOIN category ON category.id=product.category
WHERE (discount.start_date IS NULL OR (discount.start_date<now() AND discount.end_date > now()))AND
		category.name=$category AND 
        platform.name=$platform AND 
        genre.name=$genre
GROUP BY product_name
ORDER BY launch_date DESC

Query reference	SELECT05
Query description	Sort product's offers by lowest price
Query frequency	Hundreds per day

SELECT seller.username, seller.rating AS seller_rating, seller.num_sells, offer.stock, min(offer.price) AS offer_price, max(discount.rate) AS discount_rate
FROM active_products JOIN product ON product.id=active_products.product_id
	JOIN offer ON offer.product=product.id
	JOIN active_offers ON offer.id=active_offers.offer_id
	LEFT OUTER JOIN discount ON discount.offer=offer.id
	JOIN regular_user AS seller ON seller.id=offer.seller
	JOIN platform ON platform.id=offer.platform
WHERE (discount.start_date IS NULL OR (discount.start_date<now() AND discount.end_date > now()))AND
		product.id=$product_id AND platform.id=$platform_id
GROUP BY seller.username, seller.rating, seller.num_sells, offer.stock
ORDER BY offer_price ASC

Query reference	SELECT06
Query description	Sort product's offers by seller feedback rating
Query frequency	Hundreds per day

SELECT seller.username, seller.rating AS seller_rating, seller.num_sells, offer.stock, min(offer.price) AS offer_price, max(discount.rate) AS discount_rate
FROM active_products JOIN product ON product.id=active_products.product_id
	JOIN offer ON offer.product=product.id
	JOIN active_offers ON offer.id=active_offers.offer_id
	LEFT OUTER JOIN discount ON discount.offer=offer.id
	JOIN regular_user AS seller ON seller.id=offer.seller
	JOIN platform ON platform.id=offer.platform
WHERE (discount.start_date IS NULL OR (discount.start_date<now() AND discount.end_date > now()))AND
		product.id=$product_id AND platform.id=$platform_id
GROUP BY seller.username, seller.rating, seller.num_sells, offer.stock
ORDER BY seller_rating DESC

Query reference	SELECT07
Query description	Sort seller's feedback by date
Query frequency	Hundreds per day

SELECT seller.username AS buyer, feedback.evaluation AS evaluation, feedback.comment AS comment, feedback.evaluation_date AS feedback_date
FROM feedback JOIN key ON feedback.key=key.id
    JOIN offer ON offer.id=key.offer 
    JOIN regular_user AS buyer ON buyer.id=feedback.buyer
WHERE offer.seller = $id
ORDER BY feedback_date DESC;

Query reference	SELECT08
Query description	Order a list of products by the highest price of the cheapest available offer for that product
Query frequency	Thousands per day

SELECT product.name AS product_name, platform.name, min(offer.price) AS min_price, max(num_sells) AS num_sells, max(discount.rate) AS discount_rate, max(product.launch_date) AS launch_date,image.url AS img_path
FROM active_products JOIN product ON product.id=active_products.product_id
	JOIN offer ON offer.product=product.id
	JOIN active_offers ON offer.id=active_offers.offer_id
	LEFT OUTER JOIN discount ON discount.offer=offer.id
	JOIN platform ON platform.id=offer.platform
	JOIN image ON image.id=product.image
WHERE (discount.start_date IS NULL OR (discount.start_date<now() AND discount.end_date > now()))
GROUP BY product_name,platform.name,img_path
ORDER BY min_price DESC

Query reference	SELECT09
Query description	Select products by lowest price (in relation to the lowest offer)
Query frequency	Hundreds per day

SELECT product.name AS product_name, platform.name, min(offer.price) AS min_price, max(num_sells) AS num_sells, max(discount.rate) AS discount_rate, max(product.launch_date) AS launch_date,image.url AS img_path
FROM active_products JOIN product ON product.id=active_products.product_id
	JOIN offer ON offer.product=product.id
	JOIN active_offers ON offer.id=active_offers.offer_id
	LEFT OUTER JOIN discount ON discount.offer=offer.id
	JOIN platform ON platform.id=offer.platform
	JOIN image ON image.id=product.image
WHERE (discount.start_date IS NULL OR (discount.start_date<now() AND discount.end_date > now()))
GROUP BY product_name,platform.name,img_path
ORDER BY min_price ASC

Query reference	SELECT10
Query description	Select products whose cheapest offer is in a range of prices smaller than a max value defined by the user
Query frequency	Hundreds per day

SELECT product.name AS product_name, platform.name, min(offer.price) AS min_price, max(num_sells) AS num_sells, max(discount.rate) AS discount_rate, max(product.launch_date) AS launch_date,image.url AS img_path
FROM active_products JOIN product ON product.id=active_products.product_id
	JOIN offer ON offer.product=product.id
	JOIN active_offers ON offer.id=active_offers.offer_id
	LEFT OUTER JOIN discount ON discount.offer=offer.id
    JOIN product_has_platform pf ON pf.product=product.id
	JOIN platform ON platform.id=pf.platform
	JOIN image ON image.id=product.image
WHERE (discount.start_date IS NULL OR (discount.start_date<now() AND discount.end_date > now()))
		AND offer.final_date IS NULL
        AND offer.price < $max_price
GROUP BY product_name,platform.name,product.num_sells,img_path
ORDER BY product.num_sells DESC

Query reference	SELECT011
Query description	Get the past purchase history of an user order by date
Query frequency	Hundreds per day

SELECT product.name AS product_name, seller.username AS seller_username, orders.date AS buying_date, key.price_sold AS price, seller.id AS seller_id, key.id AS key_id, feedback.id AS feedback_id,report.id AS report_id
FROM orders JOIN key ON key.orders=orders.number
	JOIN offer ON key.offer=offer.id
	JOIN product ON product.id=offer.product
	JOIN platform ON platform.id=offer.platform
	JOIN image ON product.image=image.id
	JOIN regular_user AS seller ON seller.id=offer.seller
	LEFT OUTER JOIN feedback ON feedback.key=key.id
    LEFT OUTER JOIN report ON report.key=key.id
WHERE orders.buyer=$user_id
ORDER BY buying_date DESC

Query reference	SELECT012
Query description	Get the user current offers order by date
Query frequency	Hundreds per day

SELECT offer.id AS offer_id, product.name AS product_name, offer.stock, platform.name AS platform, offer.init_date AS start_date, offer.price AS offer_price,discount.rate AS discount_rate
FROM offer JOIN active_offers ON offer.id=active_offers.offer_id
    JOIN platform ON platform.id=offer.platform
	JOIN product ON product.id=offer.product
	LEFT OUTER JOIN discount ON discount.offer=offer.id
WHERE offer.seller=$user_id 

ORDER BY start_date DESC

Query reference	SELECT013
Query description	Get the user past offers order by date
Query frequency	Hundreds per day

SELECT offer.id AS offer_id, product.name AS product_name, offer.stock, platform.name AS platform, offer.init_date AS start_date, offer.price AS offer_price,discount.rate AS discount_rate
FROM offer JOIN platform ON platform.id=offer.platform
	JOIN product ON product.id=offer.product
	LEFT OUTER JOIN discount ON discount.offer=offer.id
WHERE offer.seller=$user_id AND offer.id NOT IN( SELECT *FROM active_offers)
ORDER BY start_date DESC

Query reference	SELECT014
Query description	Get the cart content
Query frequency	Hundreds per day

SELECT offer.id AS offer_id, product.name AS product_name, platform.name AS platform, seller.username AS seller, offer.price AS price, image.url AS image_path, max(discount.rate) AS discount_rate,count(*) AS number_keys_buying
FROM 
	cart JOIN offer ON offer.id=cart.offer
	JOIN active_offers ON active_offers.offer_id=offer.id
	JOIN product ON offer.product=product.id
	JOIN platform ON platform.id=offer.platform
	JOIN image ON image.id=product.image
	LEFT OUTER JOIN discount ON discount.offer=offer.id
	JOIN regular_user AS seller ON seller.id=offer.seller
	JOIN regular_user AS buyer ON buyer.id=cart.buyer	
WHERE cart.buyer=$buyer_id AND (discount.rate IS NULL OR (discount.start_date <now() AND discount.end_date >now()))
GROUP BY offer.id,product_name,platform.name,seller.username,image.url
ORDER BY product_name ASC

Query reference	SELECT015
Query description	Get the archived reports
Query frequency	Hundreds per day

SELECT report.id, reporter.username AS reporter_username, reportee.username AS reportee_username
FROM report JOIN regular_user AS reporter ON reporter.id=report.reporter
    JOIN regular_user AS reportee ON reportee.id=report.reportee
WHERE report.status=TRUE

Query reference	SELECT016
Query description	Get the active reports
Query frequency	Hundreds per day

SELECT report.id, reporter.username AS reporter_username, reportee.username AS reportee_username
FROM report JOIN regular_user AS reporter ON reporter.id=report.reporter
    JOIN regular_user AS reportee ON reportee.id=report.reportee
WHERE report.status=FALSE

Query reference	SELECT017
Query description	Fuction that gets the number of sells for a product. This query will be used by a trigger after a sell is made
Query frequency	Hundreds per day

DROP FUNCTION IF EXISTS count_number_sales_product(key_offer INTEGER);
CREATE OR REPLACE FUNCTION count_number_sales_product(key_offer INTEGER)
RETURNS INTEGER AS $counter$
DECLARE 
    counter INTEGER;
BEGIN
    counter:= (SELECT count(*)
                FROM key JOIN offer ON key.offer=offer.id 
                JOIN product ON product.id=offer.product
                WHERE product.id IN 
                (SELECT product.id
                    FROM key JOIN offer ON key.offer=offer.id 
                    JOIN product ON product.id=offer.product		  
                    WHERE key.offer=key_offer	
			    )
             );
    RETURN counter;
END; $counter$ LANGUAGE plpgsql;

Query reference	SELECT018
Query description	Function that returns the seller through the id of a key. It is used in the trigger to update the number sales of a seller after a transaction is compeleted
Query frequency	Hundreds per day

DROP FUNCTION IF EXISTS get_seller_through_key(integer) CASCADE;
CREATE OR REPLACE FUNCTION get_seller_through_key(key_id integer)
RETURNS INTEGER AS $seller_id$
DECLARE
    seller_id integer;
BEGIN
    SELECT u.id INTO seller_id
    FROM key k JOIN offer o ON k.offer = o.id
    JOIN regular_user u ON o.seller = u.id
    WHERE k.id = key_id;
	
    RETURN seller_id;
END;
$seller_id$ LANGUAGE plpgsql;

Query reference	SELECT019
Query description	search products
Query frequency	Hundreds per day

SELECT *
FROM active_products JOIN product ON active_products.product_id = product.id
WHERE name_tsvector @@ plainto_tsquery($searched)
ORDER BY ts_rank(name_tsvector, plainto_tsquery($searched));

Query reference	SELECT020
Query description	Search users
Query frequency	Hundreds per day

SELECT id, username, email, description, password, rating, birth_date, paypal, image, num_sells  
FROM regular_user 
WHERE name_tsvector @@ plainto_tsquery($searched)
ORDER BY ts_rank(name_tsvector, plainto_tsquery($searched))

1.3. Views

Query reference	VIEW01
Query description	View with active products
Query frequency	Thousands per day

DROP MATERIALIZED VIEW IF EXISTS active_products;
CREATE MATERIALIZED VIEW active_products AS 
    SELECT product.id AS product_id
	FROM product
    WHERE product.deleted = FALSE;

Query reference	VIEW02
Query description	View with active offers
Query frequency	Thousands per day

DROP MATERIALIZED VIEW IF EXISTS active_offers;
CREATE MATERIALIZED VIEW active_offers AS 
    SELECT offer.id AS offer_id
	FROM offer
    WHERE final_date IS NULL;

1.4. Most frequent modifications

Query reference	UPDATE01
Query description	Delete a product
Query frequency	Units per month

UPDATE product SET deleted = true WHERE id = $product_id

Query reference	UPDATE02
Query description	After an order is complete associate a key with that order
Query frequency	Hundreds per day

UPDATE key SET orders = $order_id, price_sold = $price_sold 
WHERE id = $id

Query reference	UPDATE03
Query description	Close an active offer after an user deletes it or the stock of keys reaches 0
Query frequency	Hundreds a day

UPDATE offer 
SET finalDate = NOW(), stock = 0
WHERE id = $offer_id_deleted

UPDATE offer 
SET final_date = NOW()
WHERE stock = 0

Query reference	UPDATE04
Query description	User updating personal information
Query frequency	Thousands a day

UPDATE regular_user 
SET email = $email, description = $description, password = $hashed_password, paypal = $paypal_email, image = $image
WHERE id = $userId

Query reference	UPDATE05
Query description	Updating a user number of sells
Query frequency	Thousands a day

UPDATE regular_user 
SET num_sells = $num_sells
WHERE id = $user_id

Query reference	UPDATE06
Query description	Updating a user rating
Query frequency	Thousands a day

UPDATE regular_user 
SET rating = $new_rating
WHERE id = $user_id

Query reference	UPDATE07
Query description	Edit product information
Query frequency	Units per month

UPDATE product
SET name = $new_name, description = $new_description, category = $new_category, launch_date = $new_lauch_date, image=$new_image
WHERE id = $product_id

UPDATE image
SET url = $new_image_path
WHERE id = $id_image_product_changed

Query reference	UPDATE08
Query description	Archive a report
Query frequency	Units per week

UPDATE report
SET statues=TRUE
WHERE id = $report_id

Query reference	INSERT01
Query description	Sign up
Query frequency	Dozens per day

INSERT INTO regular_user (username, email, password, birth_date) VALUES ($username, $email, $hashed_password, $birth_date)

Query reference	INSERT02
Query description	Add offer and keys of that offer
Query frequency	Dozens per day

INSERT INTO offer (price, init_date, platform, seller, product, stock) VALUES ($price, $init_date, $platform, $seller, $product, $stock)

INSERT INTO key (key, price, offer)
VALUES ($key, $price, $offer_id)

Query reference	INSERT03
Query description	Insert product
Query frequency	Dozens per month

INSERT INTO product (name, description, category, image, launch_date) VALUES ($name, $description, $category_id, $image_id, $launch_date)

INSERT INTO product_has_genre(genre, product) VALUES ($genre_id, $product_id)

INSERT INTO product_has_platform(platform, product) VALUES ($platform_id, $product_id)

INSERT INTO image(url) values ($url)

Query reference	INSERT05
Query description	Add platform
Query frequency	Units per year

INSERT INTO platform (name) VALUES ($name)

Query reference	INSERT06
Query description	Add genre
Query frequency	Units per year

INSERT INTO genres (name) VALUES ($name)

Query reference	INSERT07
Query description	Add category
Query frequency	Units per year

INSERT INTO category (name) VALUES ($name)

Query reference	INSERT08
Query description	Ban a user
Query frequency	Units per month

INSERT INTO banned_user (regular_user) VALUES ($user_id)

Query reference	INSERT09
Query description	Give feedback to seller
Query frequency	Hundreds a day

INSERT INTO feedback (evaluation, comment, regular_user, key) VALUES ($evaluation, $comment, $regular_user_id, $key_id)

Query reference	INSERT10
Query description	Report a seller
Query frequency	Dozens per month

INSERT INTO report (date, description, title, key, reported, reportee) VALUES ($date, $description, $title, $keyId, $reportedId, $reportee)


INSERT INTO message(date, description, regular_user) VALUES ($date, $description, $reporter_id)

Query reference	INSERT11
Query description	Write message
Query frequency	Hundreds per week

INSERT INTO message (date, description, regular_user) VALUES ($date, $description, $regular_user_id)

Query reference	INSERT12
Query description	Request a ban appeal
Query frequency	Dozens per week

INSERT INTO ban_appeal(banned_user, admin, ban_appeal, date) VALUES($banned_user_id, $admin_id, $ban_appeal, $date)

Query reference	INSERT13
Query description	Perform an order
Query frequency	Hundreds a day

INSERT INTO orders (order_number, date, regular_user) VALUES ($order_number, $date, $regular_user)

Query reference	INSERT14
Query description	Add an item to the cart
Query frequency	Hundreds a day

INSERT INTO cart (buyer, offer) VALUES ($buyer_id, $offer_id)

Query reference	INSERT15
Query description	Add a new discount for a certain offer
Query frequency	Dozens a day

INSERT INTO discount (rate, start_date, end_date, offer) VALUES ($rate, $start_date, $end_date, $offer_id);

Query reference	DELETE01
Query description	Remove an item to the cart
Query frequency	Hundreds a day

DELETE FROM cart WHERE id = $id AND buyer = $buyer_id;

Query reference	DELETE02
Query description	User deleting his account
Query frequency	Units per month

DELETE FROM user WHERE id=$userId 

DELETE FROM image WHERE id=$user_image_id

Query reference	DELETE03
Query description	Unbanned an user
Query frequency	Units per month

DELETE FROM banned_user WHERE id=$user_id

Query reference	DELETE04
Query description	Delete an image
Query frequency	Units per month

DELETE FROM image WHERE id=$image_id

Query reference	DELETE04
Query description	Delete am image
Query frequency	Units per month

DELETE FROM image WHERE id=$image_id

Query reference	DELETE05
Query description	Delete not sold keys from a canceled order
Query frequency	Duzens per week

DELETE FROM key WHERE offer=$id_offer_canceled

Query reference	DELETE06
Query description	Remove a certain discount entry for a specific offer
Query frequency	Duzens per week

DELETE FROM discount WHERE start_date<$new_start_date AND end_date >$new_end_date

2. Proposed Indices

2.1. Performance Indices

Index	IDX01
Related queries	SELECTs:2, 3, 4, 5, 6, 8, 9, 10,11,12,12,13,14,17,18
Relation	offer
Attribute	product(FK)
Type	Hash(implemented as B-Tree)
Cardinality	Medium
Clustering	no
Justification	Since most of the queries made in the website as all online shops are intend to deal with offers avaiable to a user to buy them, the relation between products and the offers associated with that product are made plenty of times and require those accesses to be fast to get. It's a index type of Hash since we are dealing with an equality. The clustering is not an option since this table has one of the greastest loads in the system, and clustering would take a huge effort.

DROP INDEX IF EXISTS
CREATE INDEX offer_product_idx ON offer (product);

Index	IDX02
Related queries	SELECTs: 7, 11, 14
Relation	offer
Attribute	seller(FK)
Type	Hash(implemented as B-Tree)
Cardinality	medium
Clustering	no
Justification	There are some queries that the platform need to respond with low delay such as the cart content, or the purchases made by an user.It's a index type of Hash since we are dealing with an equality. The clustering is not an option since this table has one of the greastest loads in the system, and clustering would take a huge effort.

DROP INDEX IF EXISTS
CREATE INDEX offer_seller_idx ON offer (seller);

Index	IDX03
Related queries	SELECTs: 2, 3, 4, 5, 6, 8, 9, 10, 12, 13, 14, 15
Relation	discount
Attribute	offer(FK)
Type	Hash(implemented as B-Tree)
Cardinality	Medium
Clustering	No
Justification	By the same reason of the IDX01, we implement an index on the FK associated with the association between offer and discount. An online shop has in the discounts one of the primary features, being the association between offers and their diferent discounts almost inseparable. Hash since we are dealing with FK equality. Clustering isn't necessary since this relation will be highly dynamic and the cardinality is considerably low for that kind of operation.

DROP INDEX IF EXISTS
CREATE INDEX disocunt_offer_idx ON discount (offer);

Index	IDX04
Related queries	SELECTs: 7,11,17,18
Relation	key
Attribute	offer(FK)
Type	Hash(implemented as B-Tree)
Cardinality	Medium
Clustering	no
Justification	After a purchase is made the platform will work with the information in the key relation. The access to the personal information associated with the key must be fast. Hash since we are dealing with FK equality. Clustering is not necessary since after inserted in the database a key won't be deleted or updated.

|

DROP INDEX IF EXISTS
CREATE INDEX key_offer_idx ON key (offer);

Index	IDX05
Related queries	SELECTs: 2,3,4,5,6,8,9,10,12
Relation	discount
Attribute	start_date, end_date
Type	B-tree
Cardinality	Medium
Clustering	no
Justification	All queries that deal with discounts, which are one of the main features of every online shop. The primary way to search using discount is in a range of dates testing, according with the current moment if those discounts are active. Since we are searching by range the index is of type B-tree. Although the cardinality is medium, the discount tables become easily without action, heavy operations of clustering are this way too much expensive.

|

DROP INDEX IF EXISTS
CREATE INDEX discount_date_idx ON discount (start_date, end_date);

Index	IDX06
Related queries	SELECTs: 14
Relation	cart
Attribute	buyer(FK)
Type	Hash(implemented as B-Tree)
Cardinality	Medium
Clustering	Yes
Justification	Every regular user in the platform has his personal shopping cart, and it is one of the fundamental features of every online shop. The access to this information must be fast in order to empower sells.Hash since we are dealing with FK equality. The cardinality and the purpose behind this attribute make him a great candidate to cluster the table where it belongs.

|

DROP INDEX IF EXISTS
CREATE INDEX cart_buyer_idx ON cart (buyer);

2.2. Full-text Search Indices

Index	IDX07
Related queries	SELECT20
Relation	products
Attribute	name_tsvector
Type	GiST
Clustering	No
Justification	The purpose of the kind of indexs are to improve the FTS perfomance. The index type associated with this FTS is type GiST since a database for this kind of platform has a frequent insert of the new games. No clustering since the name of a game has an high cardinality and we there is a description field with large information, making the ratio of tuples in each disk blocks low.

DROP INDEX IF EXISTS product_name_idx;
CREATE INDEX product_name_idx 
ON product
USING GIST(name_tsvector);

Index	IDX08
Related queries	SELECT 021
Relation	regular_user
Attribute	name_tsvector
Type	GiST
Clustering	No
Justification	The purpose of the kind of indexs are to improve the FTS perfomance. The index type associated with it is purely GiST type since the platform expects high activity of insertions and even deletion for this relation daily. No clustering since the cardinality associated with the username is a key for that relation and is cardinality is high.

DROP INDEX IF EXISTS user_username_idx;
CREATE INDEX user_username_idx 
ON regular_user
USING GIST (name_tsvector);

3. Triggers

Trigger	TRIGGER01
Query description	Inserts the computed value for the name_tsvector and weight_tsvector that vectorizes the products name and sets the search weights for both elements

CREATE OR REPLACE FUNCTION insert_product_tsvector()
RETURNS TRIGGER AS $$
BEGIN
    NEW.name_tsvector := to_tsvector(NEW.name || coalesce(NEW.description, ''));
	NEW.weight_tsvector := setweight(to_tsvector(NEW.name), 'A') || 
            setweight(to_tsvector(coalesce(NEW.description, '')), 'B');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS insert_product_tsvector_tg ON product;
CREATE TRIGGER insert_product_tsvector_tg 
BEFORE INSERT ON product
FOR EACH ROW 
EXECUTE PROCEDURE insert_product_tsvector();

Trigger	TRIGGER02
Query description	Updates the computed value for the name_tsvector and weight_tsvector that vectorizes the products name and sets the search weights for both elements

CREATE OR REPLACE FUNCTION update_product_tsvector()
RETURNS TRIGGER AS $$
BEGIN
    NEW.name_tsvector := to_tsvector(NEW.name || coalesce(NEW.description, ''));
    NEW.weight_tsvector := setweight(to_tsvector(NEW.name), 'A') || 
        setweight(to_tsvector(coalesce(NEW.description, '')), 'B');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS update_product_tsvector_tg ON product;
CREATE TRIGGER update_product_tsvector_tg 
BEFORE UPDATE ON product
FOR EACH ROW 
WHEN (NEW.name <> OLD.name or NEW.description <> OLD.description)
EXECUTE PROCEDURE update_product_tsvector();

Trigger	TRIGGER03
Query description	Inserts the computed value for the name_tsvector and weight_tsvector that vectorizes the user name and sets the search weights for both elements

CREATE OR REPLACE FUNCTION insert_user_tsvector()
RETURNS TRIGGER AS $$
BEGIN
    NEW.name_tsvector := (to_tsvector('english',NEW.username) || to_tsvector('english',NEW.description));
    NEW.weight_tsvector := setweight(to_tsvector('english',NEW.username), 'A') || 
        setweight(to_tsvector('english',NEW.description), 'B');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


DROP TRIGGER IF EXISTS insert_user_tsvector_tg ON regular_user;
CREATE TRIGGER insert_user_tsvector_tg 
BEFORE INSERT ON regular_user
FOR EACH ROW 
EXECUTE PROCEDURE insert_user_tsvector();

Trigger	TRIGGER04
Query description	Updates the computed value for the name_tsvector and weight_tsvector that vectorizes the user name and sets the search weights for both elements

CREATE OR REPLACE FUNCTION update_user_tsvector()
RETURNS TRIGGER AS $$
BEGIN
   NEW.name_tsvector := (to_tsvector('english',NEW.username) || to_tsvector('english',NEW.description));
    NEW.weight_tsvector := setweight(to_tsvector('english',NEW.username), 'A') || 
        setweight(to_tsvector('english',NEW.description), 'B');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS update_user_tsvector_tg ON regular_user;
CREATE TRIGGER update_user_tsvector_tg 
BEFORE UPDATE ON regular_user
FOR EACH ROW 
WHEN (NEW.username <> OLD.username or NEW.description <> OLD.description)
EXECUTE PROCEDURE update_user_tsvector();

Trigger	TRIGGER05
Query description	After a order transaction, updates the number of sells of a certain product

CREATE OR REPLACE FUNCTION product_num_sells() 
RETURNS TRIGGER AS $$
DECLARE 
    sells INTEGER;
    product_id INTEGER;
BEGIN
    SELECT COUNT(product.id), product.id 
    INTO sells, product_id
    FROM offer JOIN product ON product.id = offer.product        
    WHERE offer.id = NEW.offer
    GROUP BY(product.id);

    UPDATE product 
    SET num_sells = sells
    WHERE id = product_id;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS product_num_sales_tg ON key CASCADE;
CREATE TRIGGER product_num_sales_tg
AFTER INSERT OR UPDATE OF orders ON key
FOR EACH ROW 
EXECUTE PROCEDURE product_num_sells();

Trigger	TRIGGER06
Query description	After a order transaction, updates the total number of sells of a user

CREATE OR REPLACE FUNCTION user_num_sells()
RETURNS TRIGGER AS $$
DECLARE 
    sells INTEGER;
    user_id INTEGER;
BEGIN    
    user_id := get_seller_through_key(NEW.id);
    
    sells := (
        SELECT COUNT(key.id)
        FROM key JOIN offer ON key.offer = offer.id 
        JOIN regular_user AS seller ON seller.id = offer.seller 
        WHERE seller.id = user_id
        GROUP BY(seller.id)
    );
    
    UPDATE regular_user
    SET num_sells = sells
    WHERE id = user_id;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS user_num_sells_tg ON key CASCADE;
CREATE TRIGGER user_num_sells_tg
AFTER INSERT OR UPDATE OF orders ON key
FOR EACH ROW 
EXECUTE PROCEDURE user_num_sells();

Trigger	TRIGGER07
Query description	Updates the user's rating after a new review is made to it's profile

CREATE OR REPLACE FUNCTION update_seller_feedback()
RETURNS TRIGGER AS $$
DECLARE
    seller_id integer;
    positive_reviews integer;
    num_reviews integer;
    total_feedback float;
BEGIN
    seller_id := get_seller_through_key(NEW.key);

    -- Number of positive reviews of seller with id seller_id
    SELECT COUNT(u.id) INTO positive_reviews
    FROM feedback f JOIN key k ON f.key = k.id
    JOIN offer o ON k.offer = o.id
    JOIN regular_user u ON o.seller = u.id
    WHERE f.evaluation = true and u.id = seller_id
    GROUP BY u.id;
	
    IF positive_reviews IS NULL THEN
        positive_reviews := 0;
    END IF;	
    
    -- Number of reviews of seller with id seller_id
    SELECT COUNT(u.id) INTO num_reviews
    FROM feedback f JOIN key k ON f.key = k.id
    JOIN offer o ON k.offer = o.id
    JOIN regular_user u ON o.seller = u.id
    WHERE u.id = seller_id
    GROUP BY u.id;
	
    IF num_reviews IS NULL THEN
        num_reviews := 0;
    END IF;

    total_feedback := 100 * (positive_reviews / num_reviews); -- PROB DA COR E DAQUI
	
    UPDATE regular_user 
    SET rating = total_feedback
    WHERE regular_user.id = seller_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS update_seller_feedback_tg ON feedback CASCADE;
CREATE TRIGGER update_seller_feedback_tg 
AFTER INSERT OR UPDATE OR DELETE ON feedback
FOR EACH ROW 
EXECUTE PROCEDURE update_seller_feedback();

Trigger	TRIGGER08
Query description	Enforces that a user cannot review a seller he did not buy from

CREATE OR REPLACE FUNCTION check_user_bought_product()
RETURNS TRIGGER AS $$
BEGIN
    IF NOT EXISTS (
        SELECT *
        FROM orders AS o JOIN key AS k ON o.number = k.orders
        WHERE NEW.key = k.id AND o.buyer = NEW.buyer
    ) THEN 
        RAISE EXCEPTION 'Cannot review a product that you did not buy';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


DROP TRIGGER IF EXISTS check_user_bought_product_tg ON feedback CASCADE;
CREATE TRIGGER check_user_bought_product_tg 
BEFORE INSERT
ON feedback
FOR EACH ROW 
EXECUTE PROCEDURE check_user_bought_product();

Trigger	TRIGGER09
Query description	Reduces the stock of keys in an offer after each sell is completed with success

CREATE OR REPLACE FUNCTION update_product_stock()
RETURNS TRIGGER AS $$
DECLARE
    stock_quantity INTEGER;
BEGIN
  
    SELECT COUNT(key.id) into stock_quantity
    FROM key
    WHERE key.orders IS NULL AND key.offer = NEW.offer
    GROUP BY(key.id);

	
    IF stock_quantity IS NULL THEN
        stock_quantity := 0;
    END IF;
	
    UPDATE offer
    SET stock = stock_quantity
    WHERE id = NEW.offer;    
	
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS update_product_stock_tg ON key CASCADE;
CREATE TRIGGER update_product_stock_tg 
AFTER INSERT OR DELETE OR UPDATE OF orders ON key
FOR EACH ROW 
EXECUTE PROCEDURE update_product_stock();

Trigger	TRIGGER10
Query description	Removes a deleted product from all carts

CREATE OR REPLACE FUNCTION delete_from_cart()
RETURNS TRIGGER AS $$
DECLARE
    deleted_var BOOLEAN;
BEGIN
    DELETE FROM cart
    WHERE offer IN (
        SELECT offer.id 
        FROM offer 
        WHERE offer.product = NEW.id
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS delete_from_cart_tg ON product CASCADE;
CREATE TRIGGER delete_from_cart_tg 
AFTER INSERT OR UPDATE OF deleted ON product
FOR EACH ROW 
WHEN (NEW.deleted = true)
EXECUTE PROCEDURE delete_from_cart();

Trigger	TRIGGER11
Query description	Enforces that an user cannot buy from himself. Avoiding him to add offers to his cart that are sold by himself

DROP FUNCTION IF EXISTS check_not_self_buying() CASCADE;
CREATE OR REPLACE FUNCTION check_not_self_buying()
RETURNS TRIGGER AS $$
DECLARE
    seller_id INTEGER;    
BEGIN
    seller_id := (
        SELECT offer.seller
        FROM offer 
        WHERE offer.id = NEW.offer
    );
                
    IF seller_id = NEW.buyer THEN
        RAISE EXCEPTION 'You cannot buy product that you are already selling!';
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS check_not_self_buying_tg ON cart CASCADE;
CREATE TRIGGER check_not_self_buying_tg 
AFTER INSERT ON cart
FOR EACH ROW 
EXECUTE PROCEDURE check_not_self_buying();

Trigger	TRIGGER12
Query description	Deletes all keys that weren't sold in an offer that was canceled

CREATE OR REPLACE FUNCTION delete_keys_from_canceled_offers()
RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM key
    WHERE key.offer = NEW.id AND key.orders IS NULL;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS delete_keys_from_canceled_offers_tg ON offer CASCADE;
CREATE TRIGGER delete_keys_from_canceled_offers_tg 
AFTER UPDATE OF final_date ON offer
FOR EACH ROW 
WHEN(NEW.final_date IS NOT NULL)
EXECUTE PROCEDURE delete_keys_from_canceled_offers();

Trigger	TRIGGER13
Query description	Enforces that is not possible to buy a product marked as deleted by the admin

CREATE OR REPLACE FUNCTION rollback_offer_of_deleted_products()
RETURNS TRIGGER AS $$
BEGIN
    IF EXISTS( 
        SELECT *
        FROM product
        WHERE NEW.product = product.id AND product.deleted = TRUE
     ) THEN
        RAISE EXCEPTION 'You cannot insert an offer of a product that was deleted by the admin';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS rollback_offer_of_deleted_products_tg ON offer CASCADE;
CREATE TRIGGER rollback_offer_of_deleted_products_tg 
BEFORE INSERT ON offer
FOR EACH ROW 
EXECUTE PROCEDURE rollback_offer_of_deleted_products();

Trigger	TRIGGER14
Query description	Sets the offer's end_date when it is canceled or runs out of stock.

CREATE OR REPLACE FUNCTION update_offer_final_date()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE offer
    SET final_date = now()
    WHERE id = NEW.id;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS update_offer_final_date_tg ON offer CASCADE;
CREATE TRIGGER update_offer_final_date_tg 
AFTER UPDATE OF stock ON offer
FOR EACH ROW
WHEN(NEW.final_date IS NULL AND NEW.stock=0)
EXECUTE PROCEDURE update_offer_final_date();

Trigger	TRIGGER15
Query description	Enforces there isn't discount date overlaping for the same offer.

CREATE OR REPLACE FUNCTION check_discount_date_overlap()
RETURNS TRIGGER AS $$
BEGIN
    IF EXISTS( 
        SELECT *
        FROM discount
        WHERE start_date IS NOT NULL 
			AND start_date <= NEW.end_date 
			AND end_date >= NEW.start_date 
			AND NEW.offer = discount.offer
    ) THEN
        RAISE EXCEPTION 'There is already a discount for that offer during the same time period';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS overlap_discount_dates_tg ON discount CASCADE;
CREATE TRIGGER overlap_discount_dates_tg 
BEFORE INSERT OR UPDATE ON discount
FOR EACH ROW
EXECUTE PROCEDURE check_discount_date_overlap();

Trigger	TRIGGER16
Query description	Updates the materialized view that stores the active products.

CREATE OR REPLACE FUNCTION refresh_active_products_view()
RETURNS TRIGGER AS $$
BEGIN
    REFRESH MATERIALIZED VIEW active_products;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS refresh_active_products_view_tg ON product CASCADE;
CREATE TRIGGER refresh_active_products_view_tg 
AFTER INSERT OR DELETE OR UPDATE ON product
FOR EACH ROW
EXECUTE PROCEDURE refresh_active_products_view();

Trigger	TRIGGER17
Query description	Updates the materialized view that stores the active offers.

CREATE OR REPLACE FUNCTION refresh_active_offers_view()
RETURNS TRIGGER AS $$
BEGIN
    REFRESH MATERIALIZED VIEW active_offers;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS refresh_active_offers_view_tg ON offer CASCADE;
CREATE TRIGGER refresh_active_offers_view_tg 
AFTER INSERT OR DELETE OR UPDATE OF final_date ON offer
FOR EACH ROW
EXECUTE PROCEDURE refresh_active_offers_view();

Trigger	TRIGGER18
Query description	Verifies that the user of that is purchasing is not banned.

CREATE OR REPLACE FUNCTION verify_banned_user_orders()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.buyer IN (SELECT regular_user FROM banned_user) THEN
        RAISE EXCEPTION 'User with ID % is banned and cannot make purchases', NEW.buyer;
    END IF;
        
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS verify_banned_user_orders_tg ON orders CASCADE;
CREATE TRIGGER verify_banned_user_orders_tg 
BEFORE INSERT ON orders
FOR EACH ROW
EXECUTE PROCEDURE verify_banned_user_orders();

Trigger	TRIGGER19
Query description	Updates the value of a offer profit after every modification on keys.

CREATE OR REPLACE FUNCTION update_offer_profit()
RETURNS TRIGGER AS $$
DECLARE
    rate REAL;
    offer_profit REAL;

BEGIN

    SELECT SUM(key.price_sold) into offer_profit
    FROM key
    WHERE key.offer = NEW.offer
        AND key.price_sold IS NOT NULL
    GROUP BY key.offer;

    UPDATE offer
    SET profit = profit + offer_profit
    WHERE id = NEW.offer;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;  

DROP TRIGGER IF EXISTS update_offer_profit_tg ON key CASCADE;
CREATE TRIGGER update_offer_profit_tg 
AFTER INSERT OR DELETE OR UPDATE OF price_sold ON key
FOR EACH ROW
EXECUTE PROCEDURE update_offer_profit();

Trigger	TRIGGER20
Query description	Verifies that the user of that is purchasing is not banned.

CREATE OR REPLACE FUNCTION verify_banned_user_offer()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.seller IN (SELECT regular_user FROM banned_user) THEN
        RAISE EXCEPTION 'User with ID % is banned and cannot make offers', NEW.seller;
    END IF;
        
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS verify_banned_user_offer_tg ON offer CASCADE;
CREATE TRIGGER verify_banned_user_offer_tg 
BEFORE INSERT ON offer
FOR EACH ROW
EXECUTE PROCEDURE verify_banned_user_offer();

4. Transactions

Transactions needed to assure the integrity of the data, with a proper justification.

TP01	Insert a new order, effectively buy the items on a user's cart
Justification	When an user complete it payment, the platform need to add a new order in the database. Furthermore, the platform need to associate a key of that offer with the buyer profile. This operation is critical, since we need to ensure atomicity because this is a two step operation, but also associated with concurrence concerns since we cannot accept inconsistencies like the same key being associated to different offers because there are two different checkouts to the same offer working.
Isolation level	Serializable

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;

    DO $$
	DECLARE
        new_order_id INTEGER;
        offer_id INTEGER;
        number_offer_cart INTEGER :=0;
        price_key FLOAT :=0;
        discount_rate INTEGER:=0;
	BEGIN
        
        --Number of offers for that user in his cart
		number_offer_cart := (
            SELECT count(*) 
            FROM cart 
            WHERE cart.buyer = $buyer_id
        );
        		
	    INSERT INTO orders(buyer) VALUES($buyer_id) RETURNING id INTO new_order_id;		
        LOOP 
			EXIT WHEN number_offer_cart<=0;
                
                -- Remove one key from cart
                DELETE
				FROM cart
				WHERE id IN( 
                        SELECT id 
                        FROM cart 
                        WHERE buyer = $buyer_id 
                        LIMIT 1 
                        ) 
                RETURNING offer INTO offer_id;
                
                IF NOT EXISTS (
                    SELECT key.id 
                    FROM active_offers JOIN key 
                        ON key.offer = active_offers.offer_id
                    WHERE active_offers.offer_id = offer_id 
                        AND orders IS NULL
                    LIMIT 1
                    ) THEN
                        RAISE EXCEPTION 'Offer with ID: % does not have enough stock', offer_id;
                END IF;
                
                
                --Get the list price(no discounts)
                price_key := (
                            SELECT offer.price
                            FROM offer 
                            WHERE offer.id = offer_id
                            );
                
                --Calculates if there is any active discount
                discount_rate := (
                                SELECT discount.rate
                                FROM active_offers LEFT OUTER JOIN discount 
                                    ON discount.offer = active_offers.offer_id
                                WHERE 
                                    (discount.start_date IS NULL 
                                        OR (discount.start_date < now() 
                                            AND discount.end_date > now()))
                                    AND active_offers.offer_id = offer_id
								LIMIT 1
                                );
                
                IF NOT NULL discount_rate THEN
                    price_key := price_key * (1 - discount_rate / 100);
                END IF;


                --Associate the key to the buyer
			    UPDATE key
				SET orders = new_order_id, price_sold =price_key
				WHERE orders IS NULL AND 
                    id IN ( SELECT key.id
                            FROM active_offers JOIN key ON key.offer = active_offers.offer_id
                            WHERE active_offers.offer_id = offer_id AND orders IS NULL
                            LIMIT 1
                        );

                --iterator decrement                           
                number_offer_cart := number_offer_cart - 1;
		END LOOP;
	END $$;
COMMIT;

TP02	When an admin ban an user, we want to ensure that his active offers become unavaiable too
Justification	The action of banning a user should start a secure mechanism where that user offers are automaticlly excluded of sale. We choose the greastest level of isolation in order to avoid that after ban an user in a concurrent scenario he is trying to add a new offer, that new offer could be added to the system being a phantom for this transaction.
Isolation level	Serializable

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;

DELETE FROM offer
WHERE seller=$banned_user_id;

INSERT INTO banned_user(regular_user) VALUES($banned_user_id);

COMMIT;

TP03	Canceling an offer is a complex operation, since as defined in the platform's business rules, if an offer already sold at least one of it's keys the offer is considered deleted and not deleted from the database
Justification	When a seller wants to delete it's offer. The system should decide if it should be canceled or deleted. Furthermore, canceling an offer requires also to delete the keys associated with that offer that weren't already sold. There might be checkouts for that offer in process requiring an serializable behaviour
Isolation level	Serializable

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;

DO $$

BEGIN

    IF NOT EXISTS ( SELECT key.id
                    FROM key JOIN active_offers ON key.offer=offer_id
                    WHERE offer.id=$offer_to_delete AND key.offer IS NULL
                  ) 
    THEN
        DELETE FROM offer
        WHERE id=$offer_to_delete;

    ELSE
        UPDATE offer
        SET deleted=TRUE
        WHERE id=$offer_to_delete;

    END IF;

END $$;

COMMIT;

TP04	When we list all current offers a user has, we want to avoid dirty reads that may mistake the seller about the state of his offers
Justification	When a seller open his profile to check the status of his offers. We don't want to compromised performance but we want to avoid dirty reads
Isolation level	READ COMMITTED

SET TRANSACTION ISOLATION LEVEL READ COMMITTED READ ONLY;
BEGIN TRANSACTION;

SELECT offer.id AS offer_id, product.name AS product_name, offer.stock, platform.name AS platform, offer.init_date AS start_date, offer.price AS offer_price,discount.rate AS discount_rate
FROM offer JOIN active_offers ON offer.id=active_offers.offer_id
    JOIN platform ON platform.id=offer.platform
	JOIN product ON product.id=offer.product
	LEFT OUTER JOIN discount ON discount.offer=offer.id
WHERE offer.seller=$user_id 

ORDER BY start_date DESC;

COMMIT;

TP05	When we list all offers for a product, we want to ensure maximum performance on running the query. If there is a dirty read we ensure the consistence later
Justification	When a seller open his profile to check the status of his offers. We don't want to compromised performance but we want to avoid dirty reads
Isolation level	READ COMMITTED

SET TRANSACTION ISOLATION LEVEL READ COMMITTED READ ONLY;
BEGIN TRANSACTION;


SELECT seller.username, seller.rating AS seller_rating, seller.num_sells, offer.stock, min(offer.price) AS offer_price, max(discount.rate) AS discount_rate
FROM active_products JOIN product ON product.id=active_products.product_id
	JOIN offer ON offer.product=product.id
	JOIN active_offers ON offer.id=active_offers.offer_id
	LEFT OUTER JOIN discount ON discount.offer=offer.id
	JOIN regular_user AS seller ON seller.id=offer.seller
	JOIN platform ON platform.id=offer.platform
WHERE (discount.start_date IS NULL OR (discount.start_date<now() AND discount.end_date > now()))AND
		product.id=$product_id AND platform.id=$platform_id
GROUP BY seller.username, seller.rating, seller.num_sells, offer.stock;

COMMIT;

TP06	Insert offer and it's keys for that offer must be done as a whole
Justification	When a seller set it's offer, we won't find many concurrency issues, so the isolation level may be relaxed
Isolation level	READ COMMITTED

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;

INSERT INTO offer (price, init_date, platform, seller, product, stock) VALUES ($price, $init_date, $platform, $seller, $product, $stock)

INSERT INTO key (key, price, offer)
VALUES ($key, $price, $offer_id)

COMMIT;

TP07	While inserting a new product entry, the admin must also define which platform,genres and category the game belong. It must be done as a whole
Justification	When an admin add a new product to the database we won't find many concurrency problems, so the isolation may be relaxed
Isolation level	READ COMMITTED

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;

INSERT INTO product (name, description, category, image, launch_date) VALUES ($name, $description, $category_id, $image_id, $launch_date)

INSERT INTO product_has_genre(genre, product) VALUES ($genre_id, $product_id)

INSERT INTO product_has_platform(platform, product) VALUES ($platform_id, $product_id)

INSERT INTO image(url) values ($url)

COMMIT;

5. SQL Code

5.1. Database schema

Access the database schema sql script on github

5.2. Database population

In order to test and see the benefits of the indexes conceived the script created to populate the database ended up having a considerable size.

Access the database population sql script on github

Revision history

Changes made to the first submission:

Stored procedures were converted to materialized views.
Added Triggers.
Added FTS with TS_ranking and GiST indexes. Fixed the queries using FTS.
Inserted the transactions
Implementation of derived elements rather than complex queries
Update the SQL schema
Added general purpose Indexes and the justifications associated with it

GROUP2043, 25/03/2020

Luís Ramos, [email protected]
José Guerra, [email protected]
Martim Silva, [email protected]
Ruben Almeida, [email protected] (Editor)

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

a6