Changes between Version 1 and Version 2 of AdvancedDatabaseDevelopment
- Timestamp:
- 05/28/26 03:51:07 (6 hours ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
AdvancedDatabaseDevelopment
v1 v2 28 28 IF NEW.status = 'Confirmed' THEN 29 29 IF EXISTS ( 30 SELECT 1 FROM bookings b30 SELECT 1 FROM project.bookings b 31 31 WHERE b.sitter_id = NEW.sitter_id 32 32 AND b.status = 'Confirmed' … … 45 45 DROP TRIGGER IF EXISTS trg_booking_validation ON bookings; 46 46 CREATE TRIGGER trg_booking_validation 47 BEFORE INSERT OR UPDATE ON bookings47 BEFORE INSERT OR UPDATE ON project.bookings 48 48 FOR EACH ROW 49 49 EXECUTE FUNCTION petsitter_trg_booking_validation(); … … 73 73 -- Get booking status 74 74 SELECT status INTO v_booking_status 75 FROM bookings75 FROM project.bookings 76 76 WHERE booking_id = NEW.booking_id; 77 77 … … 91 91 DROP TRIGGER IF EXISTS trg_review_integrity ON reviews; 92 92 CREATE TRIGGER trg_review_integrity 93 BEFORE INSERT ON reviews93 BEFORE INSERT ON project.reviews 94 94 FOR EACH ROW 95 95 EXECUTE FUNCTION petsitter_trg_review_integrity(); … … 107 107 DROP TRIGGER IF EXISTS trg_reviews_no_update ON reviews; 108 108 CREATE TRIGGER trg_reviews_no_update 109 BEFORE UPDATE ON reviews109 BEFORE UPDATE ON project.reviews 110 110 FOR EACH ROW 111 111 EXECUTE FUNCTION petsitter_trg_reviews_no_update(); … … 133 133 COUNT(b.booking_id) AS total_completed_jobs, 134 134 SUM(p.amount) AS total_revenue 135 FROM users u136 JOIN bookings b ON u.user_id = b.sitter_id137 JOIN p ayments p ON b.booking_id = p.booking_id135 FROM project.users u 136 JOIN project.bookings b ON u.user_id = b.sitter_id 137 JOIN project.payments p ON b.booking_id = p.booking_id 138 138 WHERE b.status = 'Completed' 139 139 GROUP BY u.user_id, u.first_name, u.last_name, date_trunc('month', b.date_to) … … 147 147 COUNT(r.review_id) AS total_reviews, 148 148 ROUND(AVG(r.rating)::numeric, 2) AS average_rating 149 FROM users u150 JOIN bookings b ON u.user_id = b.sitter_id151 JOIN reviews r ON b.booking_id = r.booking_id149 FROM project.users u 150 JOIN project.bookings b ON u.user_id = b.sitter_id 151 JOIN project.reviews r ON b.booking_id = r.booking_id 152 152 GROUP BY u.user_id, u.first_name, u.last_name 153 153 HAVING COUNT(r.review_id) > 0 … … 176 176 AS $$ 177 177 BEGIN 178 UPDATE bookings178 UPDATE project.bookings 179 179 SET status = 'Expired' 180 180 WHERE status = 'Pending' … … 188 188 CREATE OR REPLACE VIEW v_expired_pending_bookings AS 189 189 SELECT * 190 FROM bookings190 FROM project.bookings 191 191 WHERE status = 'Expired'; 192 192 }}}
