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