# 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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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.key AS key_value, 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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
UPDATE regular_user
SET rating = $new_rating
WHERE id = $user_id
```
---
| **Query reference** | UPDATE07 |
|-------------------|--------------------|
| **Query description** | Edit product information |
| **Query frequency** | Units per month|
```sql
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|
```sql
UPDATE report
SET statues=TRUE
WHERE id = $report_id
```
---
| **Query reference** | INSERT01 |
|-------------------|--------------------|
| **Query description** | Sign up |
| **Query frequency** | Dozens per day |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
INSERT INTO platform (name) VALUES ($name)
```
---
| **Query reference** | INSERT06 |
|-------------------|--------------------|
| **Query description** | Add genre |
| **Query frequency** | Units per year |
```sql
INSERT INTO genres (name) VALUES ($name)
```
---
| **Query reference** | INSERT07 |
|-------------------|--------------------|
| **Query description** | Add category |
| **Query frequency** | Units per year |
```sql
INSERT INTO category (name) VALUES ($name)
```
---
| **Query reference** | INSERT08 |
|-------------------|--------------------|
| **Query description** | Ban a user |
| **Query frequency** | Units per month |
```sql
INSERT INTO banned_user (regular_user) VALUES ($user_id)
```
---
| **Query reference** | INSERT09 |
|-------------------|--------------------|
| **Query description** | Give feedback to seller |
| **Query frequency** | Hundreds a day |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
DELETE FROM cart WHERE id = $id AND buyer = $buyer_id;
```
---
| **Query reference** | DELETE02 |
|-------------------|--------------------|
| **Query description** | User deleting his account |
| **Query frequency** | Units per month |
```sql
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 |
```sql
DELETE FROM banned_user WHERE id=$user_id
```
---
| **Query reference** | DELETE04 |
|-------------------|--------------------|
| **Query description** | Delete an image |
| **Query frequency** | Units per month |
```sql
DELETE FROM image WHERE id=$image_id
```
---
| **Query reference** | DELETE04 |
|-------------------|--------------------|
| **Query description** | Delete am image |
| **Query frequency** | Units per month |
```sql
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 |
```sql
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 |
```sql
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.|
```sql
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. |
```sql
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. |
```sql
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.|
|
```sql
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. |
|
```sql
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. |
|
```sql
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.|
```sql
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.|
```sql
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|
```sql
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|
```sql
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|
```sql
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|
```sql
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 |
```sql
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|
```sql
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|
```sql
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|
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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. |
```sql
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. |
```sql
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. |
```sql
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. |
```sql
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. |
```sql
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. |
```sql
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. |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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 |
```sql
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](https://git.fe.up.pt/lbaw/lbaw1920/lbaw2043/-/blob/master/database/schema.sql)
### 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](https://git.fe.up.pt/lbaw/lbaw1920/lbaw2043/-/blob/master/database/populate.sql)
## 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, up201706253@fe.up.pt
* José Guerra, up201706421@fe.up.pt
* Martim Silva, up201705205@fe.up.pt
* Ruben Almeida, up201704618@fe.up.pt (Editor)
Google Drive
Gist
Clipboard
Sign in with Wallet
