Changes between Version 7 and Version 8 of AdvancedDatabaseDevelopment
- Timestamp:
- 08/24/26 17:04:56 (13 days ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
AdvancedDatabaseDevelopment
v7 v8 41 41 u.user_name, 42 42 u.surname, 43 s t.status,43 s.status, 44 44 COUNT(DISTINCT ch.chapter_id) AS total_chapters, 45 45 COALESCE(SUM(ch.view_count), 0) AS total_views, … … 52 52 JOIN writer w ON s.user_id = w.user_id 53 53 JOIN users u ON w.user_id = u.user_id 54 JOIN "status" st ON s.story_id = st.story_id55 54 LEFT JOIN chapter ch ON s.story_id = ch.story_id 56 55 LEFT JOIN likes l ON s.story_id = l.story_id … … 61 60 s.story_id, s.short_description, s.mature_content, 62 61 s.image, s.story_created_at, 63 u.username, u.user_name, u.surname, s t.status;62 u.username, u.user_name, u.surname, s.status; 64 63 }}} 65 64 ==== Top 10 most popular stories by likes count … … 70 69 s.short_description, 71 70 u.username AS writer, 72 s t.status,71 s.status, 73 72 COUNT(DISTINCT l.user_id) AS total_likes, 74 73 COUNT(DISTINCT c.comment_id) AS total_comments, … … 77 76 JOIN writer w ON s.user_id = w.user_id 78 77 JOIN users u ON w.user_id = u.user_id 79 JOIN "status" st ON s.story_id = st.story_id80 AND st.status = 'published'81 78 LEFT JOIN likes l ON s.story_id = l.story_id 82 79 LEFT JOIN "comment" c ON s.story_id = c.story_id 83 80 LEFT JOIN chapter ch ON s.story_id = ch.story_id 81 WHERE s.status = 'published' 84 82 GROUP BY 85 83 s.story_id, s.short_description, 86 u.username, s t.status84 u.username, s.status 87 85 ORDER BY total_likes DESC 88 86 LIMIT 10; … … 122 120 total_likes BIGINT 123 121 ); 124 }}} 125 ==== Saves the most liked story of the previous day 126 {{{ 122 127 123 CREATE OR REPLACE PROCEDURE get_daily_top_story() 128 124 LANGUAGE plpgsql … … 132 128 BEGIN 133 129 yesterday := CURRENT_DATE - 1; 134 135 130 IF EXISTS ( 136 131 SELECT 1 FROM likes 137 WHERE like _created_at BETWEEN yesterday AND CURRENT_DATE132 WHERE liked_at BETWEEN yesterday AND CURRENT_DATE 138 133 ) THEN 139 134 INSERT INTO daily_top_stories (day, story_id, total_likes) … … 144 139 FROM story s 145 140 JOIN likes l ON s.story_id = l.story_id 146 WHERE l.like _created_at BETWEEN yesterday AND CURRENT_DATE141 WHERE l.liked_at BETWEEN yesterday AND CURRENT_DATE 147 142 GROUP BY s.story_id 148 143 ORDER BY total_likes DESC … … 160 155 p_story_id INTEGER, 161 156 p_message TEXT, 162 p_ typeVARCHAR(50),157 p_content_type VARCHAR(50), 163 158 p_link VARCHAR(500) 164 159 ) … … 175 170 INSERT INTO notification ( 176 171 notification_content, 172 content_type, 177 173 is_read, 178 recipient_user_id,179 type,174 user_id, 175 story_id, 180 176 link, 181 177 notification_created_at … … 183 179 VALUES ( 184 180 p_message, 181 p_content_type, 185 182 FALSE, 186 183 collab.user_id, 187 p_ type,184 p_story_id, 188 185 p_link, 189 186 CURRENT_TIMESTAMP … … 245 242 FROM story s 246 243 WHERE s.story_id = NEW.story_id; 247 248 244 SELECT username INTO v_liker_username 249 245 FROM users 250 246 WHERE user_id = NEW.user_id; 251 252 247 IF v_writer_id != NEW.user_id THEN 253 248 INSERT INTO notification ( 254 249 notification_content, 250 content_type, 255 251 is_read, 256 recipient_user_id,257 type,252 user_id, 253 story_id, 258 254 link, 259 255 notification_created_at … … 261 257 VALUES ( 262 258 v_liker_username || ' liked your story "' || v_story_desc || '"', 259 'like', 263 260 FALSE, 264 261 v_writer_id, 265 'like',262 NEW.story_id, 266 263 '/story/' || NEW.story_id::VARCHAR, 267 264 CURRENT_TIMESTAMP 268 265 ); 269 266 END IF; 270 271 267 RETURN NEW; 272 268 END; 273 269 $$; 274 275 270 CREATE OR REPLACE TRIGGER on_story_liked 276 271 AFTER INSERT ON likes … … 287 282 BEGIN 288 283 IF NOT EXISTS ( 289 SELECT 1 FROM "status"284 SELECT 1 FROM story 290 285 WHERE story_id = NEW.story_id 291 AND status = 'published'286 AND status = 'published' 292 287 ) THEN 293 288 RAISE EXCEPTION … … 298 293 END; 299 294 $$; 300 301 295 CREATE OR REPLACE TRIGGER enforce_published_before_list_add 302 296 BEFORE INSERT ON reading_list_items
