Index: SQL/constrains/general_constrains.sql =================================================================== --- SQL/constrains/general_constrains.sql (revision 4601414ae521dc9a8e83440aeed21680b149e9fd) +++ SQL/constrains/general_constrains.sql (revision e0242b026f70749276fa4a002d90d6c1be4d4287) @@ -74,6 +74,2 @@ SELECT setval(pg_get_serial_sequence('playlist', 'playlist_id'), (SELECT MAX(playlist_id) FROM playlist)); - - - - Index: L/procedures/insert_track_with_price.sql =================================================================== --- SQL/procedures/insert_track_with_price.sql (revision 4601414ae521dc9a8e83440aeed21680b149e9fd) +++ (revision ) @@ -1,35 +1,0 @@ -CREATE OR REPLACE PROCEDURE insert_track_with_price( - p_name VARCHAR, - p_album_id INT, - p_media_type_id INT, - p_genre_id INT, - p_composer VARCHAR, - p_milliseconds INT, - p_bytes INT, - p_price NUMERIC(10,2) -) -LANGUAGE plpgsql -AS $$ -DECLARE - new_track_id INT; -BEGIN - INSERT INTO track ( - name, album_id, media_type_id, genre_id, composer, milliseconds, bytes - ) - VALUES ( - p_name, p_album_id, p_media_type_id, p_genre_id, p_composer, p_milliseconds, p_bytes - ) - RETURNING track_id INTO new_track_id; - - INSERT INTO price ( - track_id, value, date - ) - VALUES ( - new_track_id, p_price, NOW() - ); -END; -$$; - -GRANT EXECUTE ON PROCEDURE insert_track_with_price( - VARCHAR, INT, INT, INT, VARCHAR, INT, INT, NUMERIC -) TO PUBLIC; Index: SQL/transactions/insert_track_with_price.sql =================================================================== --- SQL/transactions/insert_track_with_price.sql (revision e0242b026f70749276fa4a002d90d6c1be4d4287) +++ SQL/transactions/insert_track_with_price.sql (revision e0242b026f70749276fa4a002d90d6c1be4d4287) @@ -0,0 +1,35 @@ +CREATE OR REPLACE PROCEDURE insert_track_with_price( + p_name VARCHAR, + p_album_id INT, + p_media_type_id INT, + p_genre_id INT, + p_composer VARCHAR, + p_milliseconds INT, + p_bytes INT, + p_price NUMERIC(10,2) +) +LANGUAGE plpgsql +AS $$ +DECLARE + new_track_id INT; +BEGIN + INSERT INTO track ( + name, album_id, media_type_id, genre_id, composer, milliseconds, bytes + ) + VALUES ( + p_name, p_album_id, p_media_type_id, p_genre_id, p_composer, p_milliseconds, p_bytes + ) + RETURNING track_id INTO new_track_id; + + INSERT INTO price ( + track_id, value, date + ) + VALUES ( + new_track_id, p_price, NOW() + ); +END; +$$; + +GRANT EXECUTE ON PROCEDURE insert_track_with_price( + VARCHAR, INT, INT, INT, VARCHAR, INT, INT, NUMERIC +) TO PUBLIC; Index: SQL/triggers/update_invoice_total_after_delete.sql =================================================================== --- SQL/triggers/update_invoice_total_after_delete.sql (revision 4601414ae521dc9a8e83440aeed21680b149e9fd) +++ SQL/triggers/update_invoice_total_after_delete.sql (revision e0242b026f70749276fa4a002d90d6c1be4d4287) @@ -19,2 +19,8 @@ END; $$ LANGUAGE plpgsql; + + +CREATE TRIGGER trg_update_invoice_total_after_delete +AFTER INSERT ON invoice_line +FOR EACH ROW +EXECUTE FUNCTION update_invoice_total_after_delete(); Index: SQL/triggers/update_invoice_total_after_insert.sql =================================================================== --- SQL/triggers/update_invoice_total_after_insert.sql (revision 4601414ae521dc9a8e83440aeed21680b149e9fd) +++ SQL/triggers/update_invoice_total_after_insert.sql (revision e0242b026f70749276fa4a002d90d6c1be4d4287) @@ -1,1 +1,26 @@ +CREATE OR REPLACE FUNCTION update_invoice_total_after_insert() +RETURNS TRIGGER AS $$ +BEGIN + UPDATE invoice + SET total = ( + SELECT COALESCE(SUM(il.quantity * lp.value), 0) + FROM invoice_line il + JOIN ( + SELECT DISTINCT ON (track_id) + track_id, value + FROM price + ORDER BY track_id, date DESC + ) lp ON il.track_id = lp.track_id + WHERE il.invoice_id = NEW.invoice_id + ) + WHERE invoice_id = NEW.invoice_id; + RETURN NEW; +END; +$$ LANGUAGE plpgsql; + + +CREATE TRIGGER trg_update_invoice_total_after_insert +AFTER INSERT ON invoice_line +FOR EACH ROW +EXECUTE FUNCTION update_invoice_total_after_insert(); Index: SQL/views/avg_price_per_artist.sql =================================================================== --- SQL/views/avg_price_per_artist.sql (revision 4601414ae521dc9a8e83440aeed21680b149e9fd) +++ SQL/views/avg_price_per_artist.sql (revision e0242b026f70749276fa4a002d90d6c1be4d4287) @@ -1,17 +1,14 @@ CREATE VIEW avg_price_per_artist AS -WITH latest_price AS ( - SELECT DISTINCT ON (track_id) - track_id, - value - FROM price - ORDER BY track_id, date DESC -) -SELECT - ar.name, - COALESCE(ROUND(AVG(lp.value), 2)::text, 'not enough data') AS avg_price_per_track +WITH latest_price AS (SELECT DISTINCT ON (track_id) track_id, + value + FROM price + ORDER BY track_id, date DESC) +SELECT ar.name, + COALESCE(ROUND(AVG(lp.value), 2)::text, 'not enough data') AS avg_price_per_track FROM artist ar -LEFT JOIN album a ON ar.artist_id = a.artist_id -LEFT JOIN track t ON a.album_id = t.album_id -LEFT JOIN latest_price lp ON t.track_id = lp.track_id + LEFT JOIN album a ON ar.artist_id = a.artist_id + LEFT JOIN track t ON a.album_id = t.album_id + LEFT JOIN latest_price lp ON t.track_id = lp.track_id +where a.deleted_at is null GROUP BY ar.name ORDER BY ar.name; Index: SQL/views/avg_song_duration_per_artist.sql =================================================================== --- SQL/views/avg_song_duration_per_artist.sql (revision 4601414ae521dc9a8e83440aeed21680b149e9fd) +++ SQL/views/avg_song_duration_per_artist.sql (revision e0242b026f70749276fa4a002d90d6c1be4d4287) @@ -4,4 +4,5 @@ left join album al on ar.artist_id = al.album_id left join track tr on al.album_id = tr.album_id +where ar.deleted_at is null group by ar.name order by ar.name Index: SQL/views/media_type_percentage.sql =================================================================== --- SQL/views/media_type_percentage.sql (revision 4601414ae521dc9a8e83440aeed21680b149e9fd) +++ SQL/views/media_type_percentage.sql (revision e0242b026f70749276fa4a002d90d6c1be4d4287) @@ -9,4 +9,5 @@ left join track tr on mt.media_type_id = tr.media_type_id cross join cte +where tr.deleted_at is null and mt.deleted_at is null group by mt.media_type_id, mt.name, cte.total order by percentage desc Index: SQL/views/most_listened_genre_per_customer.sql =================================================================== --- SQL/views/most_listened_genre_per_customer.sql (revision 4601414ae521dc9a8e83440aeed21680b149e9fd) +++ SQL/views/most_listened_genre_per_customer.sql (revision e0242b026f70749276fa4a002d90d6c1be4d4287) @@ -7,4 +7,5 @@ left join track tr on il.track_id = tr.track_id left join genre g on tr.genre_id = g.genre_id + where c.deleted_at is null group by c.customer_id, c.first_name, c.last_name, g.genre_id, g.name order by c.first_name, num_songs_per_genre desc), Index: SQL/views/rang_list_most_active_customers_view.sql =================================================================== --- SQL/views/rang_list_most_active_customers_view.sql (revision 4601414ae521dc9a8e83440aeed21680b149e9fd) +++ SQL/views/rang_list_most_active_customers_view.sql (revision e0242b026f70749276fa4a002d90d6c1be4d4287) @@ -6,4 +6,5 @@ from customer c LEFT JOIN invoice i on c.customer_id = i.customer_id +where c.deleted_at is null GROUP BY c.first_name, c.last_name order by total_money_spent desc Index: SQL/views/rank_list_artists.sql =================================================================== --- SQL/views/rank_list_artists.sql (revision 4601414ae521dc9a8e83440aeed21680b149e9fd) +++ SQL/views/rank_list_artists.sql (revision e0242b026f70749276fa4a002d90d6c1be4d4287) @@ -8,4 +8,5 @@ LEFT JOIN invoice_line il ON tr.track_id = il.track_id LEFT JOIN invoice i on il.invoice_id = i.invoice_id +where ar.deleted_at is null GROUP BY ar.name ORDER BY money_earned DESC; Index: templates/avg_price_per_artist.html =================================================================== --- templates/avg_price_per_artist.html (revision 4601414ae521dc9a8e83440aeed21680b149e9fd) +++ templates/avg_price_per_artist.html (revision e0242b026f70749276fa4a002d90d6c1be4d4287) @@ -20,4 +20,13 @@

Name - Average Track Price

+
+ {% csrf_token %} + +
+ + +
+
{% if not data %} {% else %} -
- {% csrf_token %} - -
- - -
-