· 9 years ago · May 08, 2017, 01:20 AM
1#include "wet.h"
2#include <stdio.h>
3#include <string.h>
4#include <stdlib.h>
5#include <libpq-fe.h>
6
7PGconn* conn;
8
9// FUNCTION PROTOTYPES
10// ---- Helper Functions ---
11
12//cmpDates() -- receives two dates as strings in the YYYY-MM-DD format.
13// returns -1 if date1 < date2, +1 if date1>date2, 0 if date1==date2.
14int cmpDates (char* date1, char* date2);
15
16//checkConn() - checks if the connection to the server was established succesfuly. If not, outputs an error message and terminates.
17void checkConn(PGconn* conn);
18
19//checkQuery() -- receives a result returned from an SQL query.
20//If the query failed, outputs and error message and terminates the program.
21void checkQuery(PGresult* res);
22
23//checkCmd() -- receives a result returned from an SQL update command (UPDATE / INSERT / DELETE).
24//If the command failed, outputs and error message and terminates the program.
25void checkCmd(PGresult* res);
26
27//dropView() - receives a DROP VIEW command and executes it. USed for cleanup of temporary commands within chechCmdPop()
28// and checkCmdDropViews().
29int dropView (PGconn* conn, char* cmd, char* viewName, int viewsToDrop);
30
31//checkQueryPop() -- Similiar to the checkQuery(), but used only within the popularVersions() function.
32//if the relevant query has failed, additionaly drops all the temporary views used so far.
33void checkQueryPop(PGconn* conn, PGresult* res, int viewsToDrop, int aboveAvg);
34
35//checkCmdPop() - Similiar to checkCmd(), but used only within the popularVersions() function.
36//if the relevant command has failed, or if the 'drop' flag is raised, drops all the temporary views used so far.
37//Used at the end of the popularVersions() function to cleanup all the views created.
38void checkCmdPop(PGconn* conn, PGresult* res, int viewsToDrop, int drop, int inf);
39
40//checkCmdDropViews() - Similiar to checkCmd(), but used only within the hostileEditors() function.
41//if the relevant command has failed, or if the 'drop' flag is raised, drops all the temporary views used so far.
42//Used at the end of the popularVersions() function to cleanup all the views created.
43void checkCmdDropViews(PGconn* conn, PGresult* res, int viewsToDrop, int drop);
44
45
46// ===================== HELPER FUNCTIONS IMPLEMENTATION =============================
47int cmpDates (char* date1, char* date2) {
48 //returns -1 if date1 < date2, +1 if date1>date2, 0 if date1==date2.
49 char yr1[5], yr2[5];
50 char mon1[3], mon2[3];
51 int i;
52 for (i=0; i<4;i++ ) {
53 yr1[i] = date1[i];
54 yr2[i] = date2[i];
55 }
56 yr1[4] = '\0';
57 yr2[4] = '\0';
58
59 if (atoi(yr1) < atoi(yr2))
60 return -1;
61 else if (atoi(yr1) > atoi(yr2))
62 return 1;
63
64 //else if years are equal
65 for (i=0; i<2;i++ ) {
66 mon1[i] = date1[i+5];
67 mon2[i] = date2[i+5];
68 }
69 mon1[2] = '\0';
70 mon2[2] = '\0';
71
72 if (atoi(mon1) < atoi(mon2))
73 return -1;
74 else if (atoi(mon1) > atoi(mon2))
75 return 1;
76 //else if months are equal
77 for (i=0; i<2;i++ ) {
78 mon1[i] = date1[i+8];
79 mon2[i] = date2[i+8];
80 }
81 if (atoi(mon1) < atoi(mon2))
82 return -1;
83 else if (atoi(mon1) > atoi(mon2))
84 return 1;
85 else
86 return 0;
87}
88
89
90void checkQuery(PGresult* res)
91{
92 if(!res || PQresultStatus(res) != PGRES_TUPLES_OK){
93 fprintf(stdout, "Error executing query: %s\n",
94 PQresultErrorMessage(res));
95 PQclear(res);
96 PQfinish(conn);
97 exit(1);
98 }
99}
100
101void checkQueryPop(PGconn* conn, PGresult* res, int viewsToDrop, int aboveAvg)
102{
103 if(!res || PQresultStatus(res) != PGRES_TUPLES_OK){
104 fprintf(stdout, "Error executing query: %s\n",
105 PQresultErrorMessage(res));
106 PQclear(res);
107 checkCmdPop(conn, res, viewsToDrop, 1, ((aboveAvg==INFINITY) ? 1 : 0)); //Drop views, flag is up
108 PQfinish(conn);
109 exit(1);
110 }
111}
112
113int dropView (PGconn* conn, char* cmd, char* viewName, int viewsToDrop) {
114 if (0 > viewsToDrop--) {
115 return viewsToDrop;
116 }
117 sprintf(cmd,viewName);
118 PQexec(conn,cmd);
119 return viewsToDrop;
120}
121
122void checkCmd(PGresult* res) {
123
124 if(!res || PQresultStatus(res) != PGRES_COMMAND_OK) {
125 fprintf(stdout, "Error executing query: %s\n",
126 PQresultErrorMessage(res));
127 PQclear(res);
128 PQfinish(conn);
129 }
130}
131
132void checkCmdDropViews(PGconn* conn, PGresult* res, int viewsToDrop, int drop) {
133
134 if(!res || PQresultStatus(res) != PGRES_COMMAND_OK || drop == 1) {
135 char cmd[50];
136 if (!drop)
137 fprintf(stdout, "Error executing query: %s\n",
138 PQresultErrorMessage(res));
139 PQclear(res);
140
141 //Drop all the temp views created. viewsToDrop indicates how many have been created,
142 //in case we're dropping because of an error (drop flag == 0). Otherwise, we're dropping
143 //because the function terminated (drop flag == 1).
144 viewsToDrop = dropView (conn, cmd, "DROP VIEW loser_versions", viewsToDrop);
145 viewsToDrop = dropView (conn, cmd, "DROP VIEW tempview1", viewsToDrop);
146 viewsToDrop = dropView (conn, cmd, "DROP VIEW before_first", viewsToDrop);
147 viewsToDrop = dropView (conn, cmd, "DROP VIEW first_update", viewsToDrop);
148 viewsToDrop = dropView (conn, cmd, "DROP VIEW hostiles", viewsToDrop);
149 viewsToDrop = dropView (conn, cmd, "DROP VIEW hos_count", viewsToDrop);
150 viewsToDrop = dropView (conn, cmd, "DROP VIEW usersusers", viewsToDrop);
151 viewsToDrop = dropView (conn, cmd, "DROP VIEW lazy", viewsToDrop);
152 viewsToDrop = dropView (conn, cmd, "DROP VIEW almost_there", viewsToDrop);
153 if (!drop) {
154 PQfinish(conn);
155 exit (1);
156 }
157
158 }
159}
160
161void checkCmdPop(PGconn* conn, PGresult* res, int viewsToDrop, int drop, int inf) {
162
163//drop == 1 -> force dropping of views, otherwise drop only if error occured.
164//inf == 1 -> aboveAvg==infinity.
165
166 if(!res || PQresultStatus(res) != PGRES_COMMAND_OK || drop == 1) {
167 char cmd[50];
168 if (!drop)
169 fprintf(stdout, "Error executing query: %s\n",
170 PQresultErrorMessage(res));
171 PQclear(res);
172
173 //Drop all the temp views created. viewsToDrop indicates how many have been created,
174 //in case we're dropping because of an error (drop flag == 0). Otherwise, we're dropping
175 //because the function terminated (drop flag == 1).
176 viewsToDrop = dropView (conn, cmd, "DROP VIEW filter_dates", viewsToDrop);
177 viewsToDrop = dropView (conn, cmd, "DROP VIEW latest_version", viewsToDrop);
178 viewsToDrop = dropView (conn, cmd, "DROP VIEW versions_views", viewsToDrop);
179 viewsToDrop = dropView (conn, cmd, "DROP VIEW titles_views", viewsToDrop);
180 viewsToDrop = dropView (conn, cmd, "DROP VIEW mostviews", viewsToDrop);
181 viewsToDrop = dropView (conn, cmd, "DROP VIEW popularity_rep", viewsToDrop);
182 viewsToDrop = dropView (conn, cmd, "DROP VIEW almost_there", viewsToDrop);
183 if (inf) {
184 viewsToDrop = dropView (conn, cmd, "DROP VIEW no_views", viewsToDrop);
185 viewsToDrop = dropView (conn, cmd, "DROP VIEW no_accept", viewsToDrop);
186 viewsToDrop = dropView (conn, cmd, "DROP VIEW add1", viewsToDrop);
187 viewsToDrop = dropView (conn, cmd, "DROP VIEW add2", viewsToDrop);
188 }
189 if (!drop) {
190 PQfinish(conn);
191 exit (1);
192 }
193
194 }
195}
196
197void checkConn(PGconn* conn) {
198
199 if (!conn || PQstatus(conn) == CONNECTION_BAD) {
200 fprintf(stdout, "Connection to server failed: %s\n",
201 PQerrorMessage(conn));
202 PQfinish(conn);
203 exit(1);
204 }
205}
206// ============================ MAIN FUNCTIONS IMPLEMENTATION================================
207
208void * addUser(int id, char * name)
209{
210 char cmd[200];
211 PGresult *res;
212
213 //Check whether user is already in the database.
214 sprintf(cmd, "SELECT * FROM users WHERE id=%d", id);
215 res = PQexec(conn, cmd);
216 checkQuery(res);
217 if (PQntuples(res)!=0){
218 printf(ILL_PARAMS); printf("\n");
219 return NULL;
220 }
221
222 //Insert user into the database.
223 sprintf(cmd, "INSERT INTO users VALUES (%d,'%s')", id, name);
224 res = PQexec(conn, cmd);
225 checkCmd(res);
226
227 return NULL;
228}
229
230void * addEditor(int id)
231{
232 char cmd[200];
233 PGresult *res;
234
235 // Check whether user is in the database, fail otherwise.
236 sprintf(cmd, "SELECT * FROM users WHERE id=%d", id);
237 res = PQexec(conn, cmd);
238 checkQuery(res);
239 if (PQntuples(res)==0){
240 printf(ILL_PARAMS); printf("\n");
241 return NULL;
242 }
243
244 // Check whether user is already an editor, fail if so.
245 sprintf(cmd, "SELECT * FROM editor WHERE id=%d", id);
246 res = PQexec(conn, cmd);
247 checkQuery(res);
248 if (PQntuples(res)!=0){
249 printf(ILL_PARAMS); printf("\n");
250 return NULL;
251 }
252
253 //Add user as editor.
254 sprintf(cmd, "INSERT INTO editor VALUES (%d)", id);
255 res = PQexec(conn, cmd);
256 checkCmd(res);
257 return NULL;
258}
259
260void * addAdmin(int id)
261{
262 char cmd[200];
263 PGresult *res;
264
265 // Check whether user is in the database, fail otherwise.
266 sprintf(cmd, "SELECT * FROM users WHERE id=%d", id);
267 res = PQexec(conn, cmd);
268 checkQuery(res);
269 if (PQntuples(res)==0){
270 printf(ILL_PARAMS); printf("\n");
271 return NULL;
272 }
273
274 // Check whether user is already an admin, if so, fail.
275 sprintf(cmd, "SELECT * FROM admin WHERE id=%d", id);
276 res = PQexec(conn, cmd);
277 checkQuery(res);
278 if (PQntuples(res)!=0){
279 printf(ILL_PARAMS); printf("\n");
280 return NULL;
281 }
282
283 // Check whether user is an editor; if not, add him as one.
284 sprintf(cmd, "SELECT * FROM editor WHERE id=%d", id);
285 res = PQexec(conn, cmd);
286 checkQuery(res);
287 if (PQntuples(res)==0){
288
289 sprintf(cmd, "INSERT INTO editor VALUES (%d)", id);
290 res = PQexec(conn, cmd);
291 checkCmd(res);
292 }
293
294 //Add user as an admin.
295 sprintf(cmd, "INSERT INTO admin VALUES (%d)", id);
296 res = PQexec(conn, cmd);
297 checkCmd(res);
298 return NULL;
299}
300void * addLink(char * fromTitle, char * toTitle)
301{
302 char cmd[200];
303 PGresult *res1, *res2;
304
305 //Check that both articles exist.
306 sprintf(cmd, "SELECT * FROM article WHERE title='%s'", fromTitle);
307 res1 = PQexec(conn, cmd);
308 checkQuery(res1);
309
310 sprintf(cmd, "SELECT * FROM article WHERE title='%s'", toTitle);
311 res2 = PQexec(conn, cmd);
312 checkQuery(res2);
313 if ((PQntuples(res1)==0) || (PQntuples(res2)==0)) {
314 printf(ILL_PARAMS); printf("\n");
315 return NULL;
316 }
317
318 //Add a link between the articles.
319 sprintf(cmd, "INSERT INTO link VALUES ('%s','%s')", fromTitle, toTitle);
320 res1 = PQexec(conn, cmd);
321 checkCmd(res1);
322 return NULL;
323}
324
325void * addViewed(int id, char * title, char * date)
326{
327 char cmd[200];
328 PGresult *res1, *res2;
329
330 //Check that the user and the articles exist.
331 sprintf(cmd, "SELECT * FROM users WHERE id=%d", id);
332 res1 = PQexec(conn, cmd);
333 checkQuery(res1);
334
335 sprintf(cmd, "SELECT * FROM version WHERE title='%s'", title);
336 res2 = PQexec(conn, cmd);
337 checkQuery(res2);
338 if ((PQntuples(res1)==0) || (PQntuples(res2)==0)) {
339 printf(ILL_PARAMS); printf("\n");
340 return NULL;
341 }
342
343 //Add a viewing.
344 sprintf(cmd, "INSERT INTO viewed VALUES (%d,'%s','%s')", id, title, date);
345 res1 = PQexec(conn, cmd);
346 checkCmd(res1);
347 return NULL;
348}
349
350void * addVersion(int id, char * date, char * title, char * content)
351{
352 char cmd[200];
353 PGresult *res;
354
355 //Check that the editor exists.
356 sprintf(cmd, "SELECT * FROM editor WHERE id=%d", id);
357 res = PQexec(conn, cmd);
358 checkQuery(res);
359 if (PQntuples(res)==0) {
360 printf(ILL_PARAMS); printf("\n");
361 return NULL;
362 }
363
364 //Check that the article exists; otherwise, add it to the database.
365 sprintf(cmd, "SELECT * FROM article WHERE title='%s'", title);
366 res = PQexec(conn, cmd);
367 checkQuery(res);
368 if (PQntuples(res)==0) {
369 sprintf(cmd, "INSERT INTO article VALUES ('%s') ", title);
370 res = PQexec(conn, cmd);
371 checkCmd(res);
372 }
373
374 //Add a new versio.
375 sprintf(cmd, "INSERT INTO version VALUES (%d,'%s','%s','%s')", id, date, title, content);
376 res = PQexec(conn, cmd);
377 checkCmd(res);
378 return NULL;
379}
380
381void * addAccepted(int eid, int aid, char * title, char * vdate, char * adate)
382{
383 char cmd[200];
384 PGresult *res1, *res2;
385
386 //Check that the admin exists.
387 sprintf(cmd, "SELECT * FROM admin WHERE id=%d", aid);
388 res1 = PQexec(conn, cmd);
389 checkQuery(res1);
390
391 //Check that the editor exists.
392 sprintf(cmd, "SELECT * FROM editor WHERE id=%d", eid);
393 res2 = PQexec(conn, cmd);
394 checkQuery(res2);
395 if ((PQntuples(res1)==0) || (PQntuples(res2)==0)) {
396 printf(ILL_PARAMS); printf("\n");
397 return NULL;
398 }
399
400 //Check that the version exists, and that the acceptance date is later than the edit date.
401 sprintf(cmd, "SELECT * FROM version WHERE title='%s' AND date='%s'", title, vdate);
402 res1 = PQexec(conn, cmd);
403 checkQuery(res1);
404 if ((PQntuples(res1)==0) || (cmpDates(adate,vdate)==-1)) {
405 printf(ILL_PARAMS); printf("\n");
406 return NULL;
407 }
408
409 //Check that this version wasn't already accepted.
410 sprintf(cmd, "SELECT * FROM accepted WHERE title='%s' AND vdate='%s'", title, vdate);
411 res1 = PQexec(conn, cmd);
412 checkQuery(res1);
413 if (PQntuples(res1)!=0) {
414 printf(ILL_PARAMS); printf("\n");
415 return NULL;
416 }
417
418 //Accept the version.
419 sprintf(cmd, "INSERT INTO accepted VALUES (%d,%d,'%s','%s','%s')",eid, aid, title,vdate,adate);
420 res1 = PQexec(conn, cmd);
421 checkCmd(res1);
422 return NULL;
423}
424
425void * removeUser(int id, int opt, int newId)
426{
427 char cmd[200];
428 PGresult *res,*res1,*res2;
429
430 //Check that the user exists.
431 sprintf(cmd, "SELECT * FROM users WHERE id=%d", id);
432 res = PQexec(conn, cmd);
433 checkQuery(res);
434 if (PQntuples(res)==0){
435 printf(ILL_PARAMS); printf("\n");
436 return NULL;
437 }
438
439 //Check that the user didn't view or accept and articles; if so, and if the paramater wasn't REJECT, fail.
440 sprintf(cmd, "SELECT * FROM viewed WHERE id=%d", id);
441 res1 = PQexec(conn, cmd);
442 checkQuery(res1);
443
444 sprintf(cmd, "SELECT * FROM accepted WHERE aid=%d", id);
445 res2 = PQexec(conn, cmd);
446 checkQuery(res2);
447 if ( (PQntuples(res1)!=0) && (PQntuples(res2)!=0) && (opt==REJECT)) {
448 printf(ILL_PARAMS); printf("\n");
449 return NULL;
450 }
451
452 //Switch according to the second paramter.
453 switch (opt) {
454 case DELETE:
455
456 //Delete all the views of the user.
457 sprintf(cmd, "DELETE FROM viewed WHERE id=%d", id);
458 res = PQexec(conn, cmd);
459 checkCmd(res);
460
461 //Delete all the versions the user edited.
462 sprintf(cmd, "DELETE FROM version WHERE id=%d", id);
463 res = PQexec(conn, cmd);
464 checkCmd(res);
465
466 //Delete all the acceptances by the user.
467 sprintf(cmd, "DELETE FROM accepted WHERE aid=%d", id);
468 res = PQexec(conn, cmd);
469 checkCmd(res);
470
471 //Delete all the acceptance of the article versions edited by the user.
472 sprintf(cmd, "DELETE FROM accepted WHERE eid=%d",id);
473 res = PQexec(conn, cmd);
474 checkCmd(res);
475
476 // Creating a joined table for all viewed values and the first acception date for each
477 sprintf(cmd, "CREATE VIEW viewsnaccepts AS "
478 "SELECT V.title, V.date AS vdate, min(A.adate) AS first_accept "
479 "FROM viewed V, accepted A WHERE V.title = A.title "
480 "GROUP BY V.title, v.date ");
481 res = PQexec(conn,cmd);
482 checkCmd(res);
483
484 //Deleting all values in the joined table where there is a view before the earliest update
485 sprintf(cmd,"DELETE FROM viewed WHERE EXISTS (SELECT * FROM viewsnaccepts VNA "
486 "WHERE (DATE(VNA.vdate)<DATE(first_accept)) AND "
487 "(DATE(Viewed.date) = DATE(VNA.vdate)) AND "
488 "(Viewed.title=VNA.title))");
489 res = PQexec(conn,cmd);
490 checkCmd(res);
491
492 //Delete temporary view.
493 sprintf(cmd, "DROP VIEW viewsnaccepts");
494 res = PQexec(conn,cmd);
495 checkCmd(res);
496
497 break;
498
499 case CHANGE:
500
501 //Check if the user is an editor.
502 sprintf(cmd, "SELECT * FROM editor WHERE id=%d", id);
503 res1 = PQexec(conn, cmd);
504 checkQuery (res1);
505 if (PQntuples(res1) >0) {
506
507 //If the user is an editor, check whether the new user is also an editor; otherwise, fail.
508 sprintf(cmd, "SELECT * FROM editor WHERE id=%d", newId);
509 res2 = PQexec(conn, cmd);
510 checkQuery (res2);
511 if (PQntuples(res2)==0) {
512 printf(ILL_PARAMS); printf("\n");
513 return NULL;
514 }
515 }
516
517 //Check if the user accepted any article versions.
518 sprintf(cmd, "SELECT * FROM accepted WHERE aid=%d", id);
519 res1 = PQexec(conn, cmd);
520 checkQuery (res1);
521 if (PQntuples(res1) >0) {
522
523 //If the user accepted any versions, check that the new user is an admin; otherwise, fail.
524 sprintf(cmd, "SELECT * FROM admin WHERE id=%d", newId);
525 res2 = PQexec(conn, cmd);
526 checkQuery (res2);
527 if (PQntuples(res2)==0) {
528 printf(ILL_PARAMS); printf("\n");
529 return NULL;
530 }
531 }
532
533 //Set all the edits by the deleted user to be by the new user.
534 sprintf(cmd, "UPDATE version SET id=%d WHERE id=%d", newId, id);
535 res = PQexec(conn, cmd);
536 checkCmd (res);
537
538
539 sprintf(cmd, "UPDATE accepted SET eid=%d WHERE eid=%d", newId, id);
540 res = PQexec(conn, cmd);
541 checkCmd (res);
542
543 //Set all the acceptances by the deleted user to be by the new user.
544 sprintf(cmd, "UPDATE accepted SET aid=%d WHERE aid=%d", newId, id);
545 res = PQexec(conn, cmd);
546 checkCmd (res);
547 break;
548 case REJECT : break;
549 default: printf("error! default case reached in removeUser()!.\n");
550 PQfinish(conn);
551 exit(1);
552 break;
553 }
554
555 //Delete the old user from the users table.
556 sprintf(cmd, "DELETE FROM users WHERE id=%d", id);
557 res = PQexec(conn, cmd);
558 checkCmd(res);
559
560 //Delete the old user from the editor table (if present there).
561 sprintf(cmd, "DELETE FROM editor WHERE id=%d", id);
562 res = PQexec(conn, cmd);
563 checkCmd(res);
564
565 //Delete the old user from the admin table (if present there).
566 sprintf(cmd, "DELETE FROM admin WHERE id=%d", id);
567 res = PQexec(conn, cmd);
568 checkCmd(res);
569
570 return NULL;
571}
572void * popularVersions(char * fromDate, char * toDate, int distinct, int aboveAvg)
573{
574 char cmd[400];
575 PGresult *res;
576 int i,nTuples1,nTuples2,nTuples;
577 int viewsToDrop = 1;
578
579 //Check that there are articles in the DB; otherwise, print EMPTY and return.
580 sprintf(cmd,"SELECT * FROM article");
581 res = PQexec(conn,cmd);
582 checkQuery(res);
583 nTuples=PQntuples(res);
584 if (!nTuples) {
585 printf(EMPTY);
586 printf("\n");
587 return NULL;
588 }
589
590//Creating a table of viewed JOIN accepted (by title) with views only between the relevant dates
591 sprintf(cmd, "CREATE VIEW filter_dates AS SELECT V.id, A.eid, V.title, V.date, A.adate, A.vdate "
592 "FROM viewed V, accepted A WHERE V.title=A.title "
593 "AND V.date BETWEEN DATE('%s') AND DATE('%s')", fromDate, toDate);
594 PQclear(res);
595 res = PQexec(conn,cmd);
596 checkCmdPop(conn, res, viewsToDrop++, 0,0);
597
598//filtering only the latest version for each view
599 sprintf(cmd, "CREATE VIEW latest_version AS SELECT * FROM filter_dates FD "
600 "WHERE FD.adate= (SELECT MAX(accepted.adate) "
601 "WHERE FD.title=accepted.title AND DATE(accepted.adate)<=DATE(FD.date))");
602 PQclear(res);
603 res = PQexec(conn,cmd);
604 checkCmdPop(conn, res, viewsToDrop++, 0,0);
605
606 if (distinct == TRUE)
607 {
608//Making a list of each version and the number of distinct viewers it has
609 sprintf(cmd, "CREATE VIEW versions_views AS "
610 "SELECT title, COUNT(DISTINCT id) AS ver_num, vdate, eid "
611 "FROM latest_version GROUP BY title, vdate, eid");
612 PQclear(res);
613 res = PQexec(conn,cmd);
614 checkCmdPop(conn, res, viewsToDrop++, 0,0);
615//Making a list of each title and the number of distinct viewers it has
616 sprintf(cmd, "CREATE VIEW titles_views AS "
617 "SELECT title, COUNT(DISTINCT id) AS title_num "
618 "FROM latest_version GROUP BY title");
619 PQclear(res);
620 res = PQexec(conn,cmd);
621 checkCmdPop(conn, res, viewsToDrop++, 0,0);
622 }
623 else if (distinct == FALSE)
624 {
625//Making a list of each version and the number of views it has
626 sprintf(cmd, "CREATE VIEW versions_views AS "
627 "SELECT title, COUNT(vdate) AS ver_num, vdate, eid "
628 "FROM latest_version GROUP BY title, vdate, eid");
629 PQclear(res);
630 res = PQexec(conn,cmd);
631 checkCmdPop(conn, res, viewsToDrop++, 0,0);
632
633//Making a list of each title and the number of views it has
634 sprintf(cmd, "CREATE VIEW titles_views AS "
635 "SELECT title, COUNT(vdate) AS title_num "
636 "FROM latest_version GROUP BY title");
637 PQclear(res);
638 res = PQexec(conn,cmd);
639 checkCmdPop(conn, res, viewsToDrop++, 0,0);
640 }
641//Picking the version with most views for each title
642 sprintf(cmd,"CREATE VIEW mostviews AS "
643 "SELECT VV.title, VV.ver_num, TV.title_num, VV.vdate, VV.eid AS editor "
644 "FROM versions_views VV,titles_views TV "
645 "WHERE VV.ver_num=(SELECT MAX(versions_views.ver_num) "
646 "WHERE VV.title=versions_views.title) AND VV.title=TV.title");
647 PQclear(res);
648 res = PQexec(conn,cmd);
649 checkCmdPop(conn, res, viewsToDrop++, 0,0);
650
651//Calculating the popularity percentage for each "popular" version
652 sprintf(cmd, "CREATE VIEW popularity_rep AS "
653 "SELECT title, editor, vdate, ((ver_num*100.00)/title_num) AS popularity "
654 "FROM mostviews");
655 PQclear(res);
656 res = PQexec(conn,cmd);
657 checkCmdPop(conn, res, viewsToDrop++, 0,0);
658
659//adding names to the popularity report
660 sprintf(cmd, "CREATE VIEW almost_there AS "
661 "SELECT PR.title, PR.editor, U.name, PR.vdate, PR.popularity "
662 "FROM popularity_rep PR, users U WHERE PR.editor = U.id");
663 PQclear(res);
664 res = PQexec(conn,cmd);
665 checkCmdPop(conn, res, viewsToDrop++, 0,0);
666
667
668 if (aboveAvg==INFINITY)
669 {
670//Filtering all titles that have no views between the selected dates
671 sprintf(cmd,"CREATE VIEW no_views AS "
672 "SELECT title FROM article A WHERE NOT EXISTS "
673 "(SELECT * FROM viewed "
674 "WHERE A.title=viewed.title AND "
675 "DATE(viewed.date) BETWEEN DATE('%s') AND DATE ('%s'))",fromDate,toDate);
676 PQclear(res);
677 res = PQexec(conn,cmd);
678 checkCmdPop(conn, res, viewsToDrop++, 0,1);
679
680//Filtering all titles that have no accepted versions before the toDate.
681 sprintf(cmd,"CREATE VIEW no_accept AS "
682 "SELECT title FROM article A WHERE NOT EXISTS "
683 "(SELECT * FROM latest_version "
684 "WHERE A.title=latest_version.title AND "
685 "DATE(latest_version.adate)<DATE('%s'))",toDate);
686 PQclear(res);
687 res = PQexec(conn,cmd);
688 checkCmdPop(conn, res, viewsToDrop++, 0,1);
689//Combining the two previous tables
690 sprintf(cmd, "CREATE VIEW add1 AS "
691 "SELECT DISTINCT article.title FROM article "
692 "WHERE title IN (SELECT title FROM no_views) OR title IN (SELECT title FROM no_accept)");
693 PQclear(res);
694 res = PQexec(conn,cmd);
695 checkCmdPop(conn, res, viewsToDrop++, 0,1);
696
697//The Final Output (in case there is no AboveAverage
698 sprintf(cmd, "SELECT * FROM almost_there ORDER BY popularity");
699 }
700 else
701 {
702
703//The Final Output (in case there is AboveAverage
704 sprintf(cmd, "SELECT * FROM almost_there "
705 "WHERE (popularity > ((SELECT AVG(popularity) FROM almost_there)*(1+0.01*%d))) "
706 "ORDER BY popularity",aboveAvg);
707 }
708 PQclear(res);
709 res = PQexec(conn,cmd);
710
711 checkQueryPop(conn, res, viewsToDrop, aboveAvg);
712
713 nTuples1=PQntuples(res);
714
715 //Print the report.
716 if (nTuples1)
717 printf(POPULAR_VERSIONS_HEAD);
718 for (i=0;i<nTuples1;i++) {
719 char value0[16];
720 char value1[10];
721 char value2[16];
722 char value3[11];
723 float value4;
724
725 //Extract the values from the current row in the query to local vars.
726 strcpy(value0, PQgetvalue(res, i,0));
727 if (!PQgetisnull(res, i, 1))
728 strcpy(value1, PQgetvalue(res, i,1));
729 else
730 strcpy(value1, "null");
731 if (!PQgetisnull(res, i, 2))
732 strcpy(value2, PQgetvalue(res, i,2));
733 else
734 strcpy(value2, "null");
735 if (!PQgetisnull(res, i, 3))
736 strcpy(value3, PQgetvalue(res, i,3));
737 else
738 strcpy(value3, "null");
739 if (!PQgetisnull(res, i, 4))
740 value4=atof(PQgetvalue(res, i,4));
741 else
742 value4=-1;
743
744 //Print the current row.
745 printf(POPULAR_VERSIONS_REC,value0,value1,value2,value3,value4);
746 }
747 if (aboveAvg==INFINITY)
748 {
749//In case there is no aboveAverage we add all titles that have no views or no accepted versions between the two
750//given dates in the end, with popularity -1.00
751 sprintf(cmd,"SELECT add1.title, AN.editor, AN.name, AN.vdate, AN.popularity "
752 "FROM add1 LEFT OUTER JOIN almost_there AN ON (AN.title=add1.title)");
753 PQclear(res);
754 res = PQexec(conn,cmd);
755 checkQueryPop(conn, res, viewsToDrop, 1);
756 nTuples2=PQntuples(res);
757 if (!nTuples1 && !nTuples2) { //If the report is empty, prnt EMPTY and return.
758 printf(EMPTY);
759 printf("\n");
760 return NULL;
761 }
762 else if (!nTuples) //if the first part of the reoprt was empty, we need to print the header now.
763 printf(POPULAR_VERSIONS_HEAD);
764
765 //print the second part of the report.
766 for (i=0;i<nTuples2;i++) {
767 char value0[16];
768 char value1[10];
769 char value2[16];
770 char value3[11];
771 float value4;
772 strcpy(value0, PQgetvalue(res, i,0));
773 if (!PQgetisnull(res, i, 1))
774 strcpy(value1, PQgetvalue(res, i,1));
775 else
776 strcpy(value1, "null");
777 if (!PQgetisnull(res, i, 2))
778 strcpy(value2, PQgetvalue(res, i,2));
779 else
780 strcpy(value2, "null");
781 if (!PQgetisnull(res, i, 3))
782 strcpy(value3, PQgetvalue(res, i,3));
783 else
784 strcpy(value3, "null");
785 if (!PQgetisnull(res, i, 4))
786 value4=atof(PQgetvalue(res, i,4));
787 else
788 value4=-1;
789 printf(POPULAR_VERSIONS_REC,value0,value1,value2,value3,value4);
790 }
791 }
792 checkCmdPop(conn, res, viewsToDrop++, 1, ((aboveAvg==INFINITY) ? 1 : 0)); //Drop temp views
793
794 PQclear(res);
795 return NULL;
796}
797void * hostileEditors()
798{
799 char cmd[400];
800 PGresult *res;
801 int i,nTuples;
802 int viewsToDrop = 1;
803
804 //If the aren't any editors, print EMPTY and return.
805 sprintf(cmd,"SELECT * FROM editor");
806 res = PQexec(conn,cmd);
807 checkQuery(res);
808 nTuples=PQntuples(res);
809 if (!nTuples) {
810 printf(EMPTY);
811 printf("\n");
812 return NULL;
813 }
814
815//creating a table with all the version that have at least one version
816//written by editors that dont have any accepted version
817 sprintf(cmd, "CREATE VIEW loser_versions AS SELECT id, title, date FROM version "
818 "WHERE title IN (SELECT title FROM version WHERE "
819 "id IN( SELECT id FROM editor WHERE id NOT IN (SELECT eid FROM accepted)))");
820 res = PQexec(conn, cmd);
821 checkCmdDropViews(conn, res, viewsToDrop++, 0);
822//creating a table of the versions written by those with no accepted version,
823//along with all updates made to these versions within a week
824 sprintf(cmd,"CREATE VIEW TempView1 AS "
825 "SELECT N.id AS O_Editor, V.id AS Editor, V.title AS title, N.date AS o_date, V.date AS e_date "
826 "FROM loser_versions N ,Version V "
827 "WHERE (N.title = V.title) AND (V.date - N.date BETWEEN 0 AND 7) AND (V.id <> N.id)");
828 PQclear(res);
829 res = PQexec(conn,cmd);
830 checkCmdDropViews(conn, res, viewsToDrop++, 0);
831
832//creating a table of titles, original editors and the earliest update made
833 sprintf(cmd,"CREATE VIEW before_first AS "
834 "SELECT o_editor, title, min(e_date) AS update_date, o_date AS orig_date "
835 "FROM TempView1 WHERE o_editor IN "
836 "(SELECT id FROM editor WHERE id NOT IN (SELECT eid FROM accepted)) "
837 "GROUP BY o_Editor,title,o_date");
838 PQclear(res);
839 res = PQexec(conn,cmd);
840 checkCmdDropViews(conn, res, viewsToDrop++, 0);
841
842//adding the details of the first person who updated the title to the previous table
843 sprintf(cmd,"CREATE VIEW first_update AS "
844 "SELECT BF.o_editor, BF.orig_date, BF.title, V.id AS editor ,BF.update_date "
845 "FROM before_first BF , version V "
846 "WHERE V.date=BF.update_date AND V.title=BF.title");
847 PQclear(res);
848 res = PQexec(conn,cmd);
849 checkCmdDropViews(conn, res, viewsToDrop++, 0);
850
851//Creating a table of editor id and those who changed their versions first.
852 sprintf(cmd,"CREATE VIEW hostiles AS "
853 "SELECT DISTINCT T1.Editor AS hostile_id, F.o_editor AS loser_id "
854 "FROM TempView1 T1, first_update F "
855 "WHERE (T1.editor = F.editor) AND (T1.o_date = F.orig_date)");
856 PQclear(res);
857 res = PQexec(conn,cmd);
858 checkCmdDropViews(conn, res, viewsToDrop++, 0);
859
860//Filtering only those who have one person that changes all their versions
861 sprintf(cmd,"CREATE VIEW hos_count AS "
862 "SELECT * FROM hostiles WHERE loser_id IN "
863 "(SELECT loser_id FROM hostiles GROUP BY loser_id HAVING count(hostile_id)=1)");
864 PQclear(res);
865 res = PQexec(conn,cmd);
866 checkCmdDropViews(conn, res, viewsToDrop++, 0);
867
868//creating a temporary joined table of the users table with itself
869 sprintf(cmd,"CREATE VIEW usersusers AS "
870 "SELECT U1.id AS u1_id, U1.name AS u1_name, U2.id AS u2_id, U2.name AS u2_name "
871 "FROM users U1, users U2");
872 PQclear(res);
873 res = PQexec(conn,cmd);
874 checkCmdDropViews(conn, res, viewsToDrop++, 0);
875
876//making a list of all editors which dont have any versions accepted along with their names
877 sprintf(cmd,"CREATE VIEW lazy AS "
878 "SELECT id ,name FROM users "
879 "WHERE id NOT IN (SELECT eid FROM accepted) AND id IN (SELECT id FROM editor)");
880 PQclear(res);
881 res = PQexec(conn,cmd);
882 checkCmdDropViews(conn, res, viewsToDrop++, 0);
883
884//Adding names to the id columns in hos_count
885 sprintf(cmd,"CREATE VIEW almost_there AS "
886 "SELECT u1_id,u1_name,u2_id,u2_name "
887 "FROM hos_count LEFT OUTER JOIN usersusers ON "
888 "(u1_id=hos_count.loser_id) AND (u2_id=hos_count.hostile_id)");
889 PQclear(res);
890 res = PQexec(conn,cmd);
891 checkCmdDropViews(conn, res, viewsToDrop++, 0);
892
893//The Final Output
894 sprintf(cmd,"SELECT lazy.id,lazy.name,u2_id,u2_name "
895 "FROM lazy LEFT OUTER JOIN almost_there ON (lazy.id=almost_there.u1_id)");
896 PQclear(res);
897 res = PQexec(conn,cmd);
898 checkQuery(res);
899 nTuples=PQntuples(res);
900
901 if (!nTuples) { //if the report is empty, print EMPTY and return.
902 printf(EMPTY);
903 printf("\n");
904 return NULL;
905 }
906
907 printf(HOSTILE_EDITOR_HEAD);
908
909 //extract each row's values into local vars, print the report.
910 for (i=0;i<nTuples;i++) {
911 char value1[11];
912 char value2[16];
913 char value3[11];
914 char value4[16];
915 strcpy(value1, PQgetvalue(res, i,0));
916 strcpy(value2, PQgetvalue(res, i,1));
917 if (!PQgetisnull(res, i, 2))
918 strcpy(value3, PQgetvalue(res, i,2));
919 else
920 strcpy(value3, "null");
921 if (!PQgetisnull(res, i, 3))
922 strcpy(value4, PQgetvalue(res, i,3));
923 else
924 strcpy(value4, "null");
925 printf(HOSTILE_EDITOR_REC,value1,value2,value3,value4);
926
927 }
928
929 checkCmdDropViews(conn, res, viewsToDrop++, 1); //Drop views, flag is up
930
931 PQclear(res);
932 return NULL;
933}
934
935int main()
936{
937 char connect_param[80];
938
939 // Connect to the database
940 sprintf(connect_param,
941 "host=pgsql.cs.technion.ac.il dbname=%s user=%s password=%s",
942 USERNAME, USERNAME, PASSWORD);
943 conn = PQconnectdb(connect_param);
944 checkConn(conn);
945
946 //Start receiving commands.
947 startParsing();
948
949 //Close connection to the DB.
950 PQfinish(conn);
951 return 0;
952}