API tools faq
paste
Advertisement
Guest User

Untitled

a guest
May 8th, 2017
82
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
C 9.95 KB | None | 0 0
raw download clone embed print report
  1. #include "wet.h"
  2. #include <stdio.h>
  3. #include <libpq-fe.h>
  4.  
  5. // ===================== HELPER FUNCTIONS =============================
  6. int cmpDates (char* date1, char* date2) {
  7.     //returns -1 if date1 < date2, +1 if date1>date2, 0 if date1==date2.
  8.     char yr1[4], yr2[4];
  9.     char mon1[2], mon2[2];
  10.     int i;
  11.     for (i=0; i<4;i++ ) {
  12.         yr1[i] = date1[i];
  13.         yr2[i] = date2[i];
  14.     }
  15.     if (atoi(yr1) < atoi(yr2))
  16.         return -1;
  17.     else if (atoi(yr1) > atoi(yr2))
  18.         return 1;
  19.    
  20.     //else if years are equal
  21.     for (i=0; i<2;i++ ) {
  22.         mon1[i] = date1[i+5];
  23.         mon2[i] = date2[i+5];
  24.     }
  25.     if (atoi(mon1) < atoi(mon2))
  26.         return -1;
  27.     else if (atoi(mon1) > atoi(mon2))
  28.         return 1;
  29.     //else if months are equal
  30.     for (i=0; i<2;i++ ) {
  31.         mon1[i] = date1[i+8];
  32.         mon2[i] = date2[i+8];
  33.     }
  34.     if (atoi(mon1) < atoi(mon2))
  35.         return -1;
  36.     else if (atoi(mon1) > atoi(mon2))
  37.         return 1;
  38.     else
  39.         return 0;
  40. }
  41.  
  42.  
  43. void  checkQuery(PGresult* res)
  44. {
  45.     if(!res || PQresultStatus(res) != PGRES_TUPLES_OK){
  46.         fprintf(stdout, "Error executing query: %s\n",
  47.             PQresultErrorMessage(res));
  48.         PQclear(res);
  49.         exit(1);
  50.     }
  51. }
  52.  
  53.  
  54. void  checkCmd(PGresult* res)   {
  55.  
  56.     if(!res || PQresultStatus(res) != PGRES_COMMAND_OK) {
  57.         fprintf(stdout, "Error executing query: %s\n",
  58.             PQresultErrorMessage(res));
  59.         PQclear(res);
  60.         return;
  61.     }
  62. }
  63.    
  64. void  checkConn(PGconn* conn)   {
  65.    
  66.     if (!conn || PQstatus(conn) == CONNECTION_BAD) {
  67.         fprintf(stdout, "Connection to server failed: %s\n",
  68.             PQerrorMessage(conn));
  69.         PQfinish(conn);
  70.         return;
  71.     }
  72. }
  73. // ============================ MAIN FUNCTIONS ================================
  74.    
  75. void * addUser(int id, char * name)
  76. {
  77.     char cmd[200];
  78.     PGresult *res;
  79.     PGconn *conn;
  80.     conn = PQconnectdb("host=pgsql.cs.technion.ac.il" "dbname=USERNAME user=USERNAME password=PASSWORD");
  81.     checkConn(conn);
  82.     sprintf(cmd, "SELECT * FROM users
  83.                     WHERE id=%d", id);
  84.     res = PQexec(conn, cmd);
  85.     checkQuery(res);
  86.     if (PQntuples(res)!=0){
  87.         printf (ILL_PARAMS);
  88.         return NULL;
  89.     }
  90.     cmd[0] = '\0';
  91.     sprintf(cmd, "INSERT INTO users VALUES %d %s", id, name);
  92.     res = PQexec(conn, cmd);
  93.     checkCmd(res);
  94.     PQfinish(conn);
  95.     return NULL;
  96. }
  97.  
  98. void * addEditor(int id)
  99. {
  100.     char cmd[200];
  101.     PGresult *res;
  102.     PGconn *conn;
  103.     conn = PQconnectdb("host=pgsql.cs.technion.ac.il" "dbname=USERNAME user=USERNAME password=PASSWORD");
  104.     checkConn(conn);
  105.     sprintf(cmd, "SELECT * FROM users
  106.                     WHERE id=%d", id);
  107.     res = PQexec(conn, cmd);
  108.     checkQuery(res);
  109.     if (PQntuples(res)==0){
  110.         printf (ILL_PARAMS);
  111.         return NULL;
  112.     }
  113.    
  114.     sprintf(cmd, "SELECT * FROM editor
  115.                     WHERE id=%d", id);
  116.     res = PQexec(conn, cmd);
  117.     checkQuery(res);
  118.     if (PQntuples(res)!=0){
  119.         printf (ILL_PARAMS);
  120.         return NULL;
  121.     }
  122.    
  123.     sprintf(cmd, "INSERT INTO editor VALUES %d", id);
  124.     res = PQexec(conn, cmd);
  125.     checkCmd(res);
  126.     PQfinish(conn);
  127.     return NULL;
  128. }
  129.  
  130. void * addAdmin(int id)
  131. {
  132.     char cmd[200];
  133.     PGresult *res;
  134.     PGconn *conn;
  135.     conn = PQconnectdb("host=pgsql.cs.technion.ac.il" "dbname=USERNAME user=USERNAME password=PASSWORD");
  136.     checkConn(conn);
  137.     sprintf(cmd, "SELECT * FROM users
  138.                     WHERE id=%d", id);
  139.     res = PQexec(conn, cmd);
  140.     checkQuery(res);
  141.     if (PQntuples(res)==0){
  142.         printf (ILL_PARAMS);
  143.         return NULL;
  144.     }
  145.    
  146.     sprintf(cmd, "SELECT * FROM admin
  147.                     WHERE id=%d", id);
  148.     res = PQexec(conn, cmd);
  149.     checkQuery(res);
  150.     if (PQntuples(res)!=0){
  151.         printf (ILL_PARAMS);
  152.         return NULL;
  153.     }
  154.    
  155.     sprintf(cmd, "SELECT * FROM editor
  156.                     WHERE id=%d", id);
  157.     res = PQexec(conn, cmd);
  158.     checkQuery(res);
  159.     if (PQntuples(res)==0){
  160.            
  161.             sprintf(cmd, "INSERT INTO editor VALUES %d", id);
  162.             res = PQexec(conn, cmd);
  163.             checkCmd(res);
  164.     }
  165.    
  166.     sprintf(cmd, "INSERT INTO admin VALUES %d", id);
  167.     res = PQexec(conn, cmd);
  168.     checkCmd(res);
  169.     PQfinish(conn);
  170.     return NULL;
  171. }
  172. void * addLink(char * fromTitle, char * toTitle)
  173. {
  174.     char cmd[200];
  175.     PGresult *res1, *res2;
  176.     PGconn *conn;
  177.     conn = PQconnectdb("host=pgsql.cs.technion.ac.il" "dbname=USERNAME user=USERNAME password=PASSWORD");
  178.     checkConn(conn);
  179.     sprintf(cmd, "SELECT * FROM article
  180.                     WHERE title=%s", fromTitle);
  181.     res1 = PQexec(conn, cmd);
  182.     checkQuery(res1);
  183.    
  184.     sprintf(cmd, "SELECT * FROM article
  185.                     WHERE title=%s", toTitle);
  186.     res2 = PQexec(conn, cmd);
  187.     checkQuery(res2);
  188.     if ((PQntuples(res1)==0) || (PQntuples(res2)==0)) {
  189.         printf(ILL_PARAMS);
  190.         return NULL;
  191.     }
  192.    
  193.     sprintf(cmd, "INSERT INTO link VALUES %s %s", fromTitle, toTitle);
  194.     res1 = PQexec(conn, cmd);
  195.     checkCmd(res1);
  196.     PQfinish(conn);
  197.     return NULL;
  198. }
  199.  
  200. void * addViewed(int id, char * title, char * date)
  201. {
  202.     char cmd[200];
  203.     PGresult *res1, *res2;
  204.     PGconn *conn;
  205.     conn = PQconnectdb("host=pgsql.cs.technion.ac.il" "dbname=USERNAME user=USERNAME password=PASSWORD");
  206.     checkConn(conn);
  207.     sprintf(cmd, "SELECT * FROM users
  208.                     WHERE id=%d", id);
  209.     res1 = PQexec(conn, cmd);
  210.     checkQuery(res1);
  211.    
  212.     sprintf(cmd, "SELECT * FROM version
  213.                     WHERE title=%s", title);
  214.     res2 = PQexec(conn, cmd);
  215.     checkQuery(res2);
  216.     if ((PQntuples(res1)==0) || (PQntuples(res2)==0)) {
  217.         printf(ILL_PARAMS);
  218.         return NULL;
  219.     }
  220.    
  221.     sprintf(cmd, "INSERT INTO viewed VALUES %d %s DATE(%s)", id, title, date);
  222.     res1 = PQexec(conn, cmd);
  223.     checkCmd(res1);
  224.     PQfinish(conn);
  225.     return NULL;
  226. }
  227.  
  228. void * addVersion(int id, char * date, char * title, char * content)
  229. {
  230.     char cmd[200];
  231.     PGresult *res;
  232.     PGconn *conn;
  233.     conn = PQconnectdb("host=pgsql.cs.technion.ac.il" "dbname=USERNAME user=USERNAME password=PASSWORD");
  234.     checkConn(conn);
  235.     sprintf(cmd, "SELECT * FROM editor WHERE id=%d", id);
  236.     res = PQexec(conn, cmd);
  237.     checkQuery(res);
  238.     if (PQntuples(res)==0) {
  239.         printf(ILL_PARAMS);
  240.         return NULL;
  241.     }
  242.    
  243.     sprintf(cmd, "SELECT * FROM article WHERE title=%s", title);
  244.     res = PQexec(conn, cmd);
  245.     checkQuery(res);
  246.     if (PQntuples(res)==0) {
  247.         sprintf(cmd, "INSERT INTO article VALUES %s ", title);
  248.         res = PQexec(conn, cmd);
  249.         checkCmd(res);
  250.     }
  251.    
  252.     sprintf(cmd, "INSERT INTO version VALUES %d DATE (%s) %s %s", id, date, title, content);
  253.     res = PQexec(conn, cmd);
  254.     checkCmd(res);
  255.     PQfinish(conn);
  256.     return NULL;
  257. }
  258.  
  259. void * addAccepted(int eid, int aid, char * title, char * vdate, char * adate)
  260. {
  261.     char cmd[200];
  262.     PGresult *res1, *res2;
  263.     PGconn *conn;
  264.     conn = PQconnectdb("host=pgsql.cs.technion.ac.il" "dbname=USERNAME user=USERNAME password=PASSWORD");
  265.     checkConn(conn);
  266.     sprintf(cmd, "SELECT * FROM admin
  267.                     WHERE id=%d", aid);
  268.     res1 = PQexec(conn, cmd);
  269.     checkQuery(res1);
  270.    
  271.     sprintf(cmd, "SELECT * FROM editor
  272.                     WHERE id=%d", eid);
  273.     res2 = PQexec(conn, cmd);
  274.     if(!res2 || PQresultStatus(res2) != PGRES_TUPLES_OK){
  275.         fprintf(stdout, "Error executing query: %s\n",
  276.         PQresultErrorMessage(res2));
  277.         PQclear(res2);
  278.         return NULL;
  279.     }
  280.     if ((PQntuples(res1)==0) || (PQntuples(res2)==0)) {
  281.         printf(ILL_PARAMS);
  282.         return NULL;
  283.     }
  284.    
  285.     sprintf(cmd, "SELECT * FROM version
  286.                     WHERE title=%s AND date= %s", title, vdate);
  287.     res1 = PQexec(conn, cmd);
  288.     checkQuery(res1);
  289.     if ((PQntuples(res1)==0) || (cmpDates(adate,vdate)==-1)) {
  290.         printf(ILL_PARAMS);
  291.         return NULL;
  292.     }
  293.    
  294.     sprintf(cmd, "INSERT INTO accepted VALUES %d, %d, %s, DATE(%s), DATE(%s)",eid, aid, title,vdate,adate);
  295.     res1 = PQexec(conn, cmd);
  296.     checkCmd(res1);
  297.     PQfinish(conn);
  298.     return NULL;
  299. }
  300.  
  301. void * removeUser(int id, int opt, int newId)
  302. {
  303.     char cmd[200];
  304.     PGresult *res,*res1,*res2;
  305.     PGconn *conn;
  306.     conn = PQconnectdb("host=pgsql.cs.technion.ac.il" "dbname=USERNAME user=USERNAME password=PASSWORD");
  307.     checkConn(conn);
  308.     sprintf(cmd, "SELECT * FROM users
  309.                     WHERE id=%d", id);
  310.     res = PQexec(conn, cmd);
  311.     checkQuery(res);
  312.     if (PQntuples(res)==0){
  313.         printf (ILL_PARAMS);
  314.         return NULL;
  315.     }
  316.    
  317.     sprintf(cmd, "SELECT * FROM viewed
  318.                 WHERE id=%d", id);
  319.     res1 = PQexec(conn, cmd);
  320.     checkCmd(res1);
  321.    
  322.     sprintf(cmd, "SELECT * FROM accepted
  323.                 WHERE aid=%d", id);
  324.     res2 = PQexec(conn, cmd);
  325.     checkCmd(res2);
  326.     if ( (PQntuples(res1)!=0) && (PQntuples(res2)!=0) && (opt==REJECT)) {
  327.         printf(ILL_PARAMS);
  328.         return NULL;
  329.     }
  330.     switch (opt)    {
  331.         case DELETE:
  332.            
  333.             sprintf(cmd, "DELETE FROM viewed WHERE id=%d", id);
  334.             res = PQexec(conn, cmd);
  335.             checkCmd(res);
  336.            
  337.             sprintf(cmd, "DELETE FROM accepted WHERE aid=%d", id);
  338.             res = PQexec(conn, cmd);
  339.             checkCmd(res);
  340.            
  341.             sprintf(cmd, "DELETE FROM accepted WHERE eid=%d",id);
  342.             res = PQexec(conn, cmd);
  343.             checkCmd(res);
  344.            
  345.             sprintf(cmd,"DELETE FROM viewed WHERE title, date IN
  346.                     SELECT title, date FROM viewed
  347.                     WHERE date < ALL(SELECT adate FROM accepted WHERE accepted.title=viewed.title)");
  348.             res = PQexec(conn,cmd);
  349.             checkCmd(res);
  350.            
  351.             break;
  352.    
  353.         case CHANGE:
  354.            
  355.             sprintf(cmd, "SELECT * FROM editor WHERE id=%d", id);
  356.             res1 = PQexec(conn, cmd);
  357.             checkQuery (res1);
  358.             if (PQntuples(res1) >0) {
  359.                
  360.                 sprintf(cmd, "SELECT * FROM editor WHERE id=%d", newId);
  361.                 res2 = PQexec(conn, cmd);
  362.                 checkQuery (res2);
  363.                 if (PQntuples(res2)==0)  {
  364.                     printf(ILL_PARAMS);
  365.                     return NULL;
  366.                 }
  367.             }
  368.            
  369.             sprintf(cmd, "SELECT * FROM accepted WHERE aid=%d", id);
  370.             res1 = PQexec(conn, cmd);
  371.             checkQuery (res1);
  372.             if (PQntuples(res1) >0) {
  373.                
  374.                 sprintf(cmd, "SELECT * FROM admin WHERE id=%d", newId);
  375.                 res2 = PQexec(conn, cmd);
  376.                 checkQuery (res2);
  377.                 if (PQntuples(res2)==0)  {
  378.                     printf(ILL_PARAMS);
  379.                     return NULL;
  380.                 }
  381.             }
  382.            
  383.             sprintf(cmd, "UPDATE version SET id=%d WHERE id=%d", newId, id);
  384.             res = PQexec(conn, cmd);
  385.             checkCmd (res);
  386.            
  387.             sprintf(cmd, "UPDATE accepted SET eid=%d WHERE eid=%d", newId, id);
  388.             res = PQexec(conn, cmd);
  389.             checkCmd (res);
  390.            
  391.             sprintf(cmd, "UPDATE accepted SET aid=%d WHERE aid=%d", newId, id);
  392.             res = PQexec(conn, cmd);
  393.             checkCmd (res);
  394.         break;
  395.         default: printf("error! default case reached in removeUSer()!.\n");
  396.             exit(1);   
  397.             break;
  398.     }
  399.     sprintf(cmd, "DELETE FROM users WHERE id=%d", id);
  400.     res = PQexec(conn, cmd);
  401.     checkCmd(res);
  402.    
  403.    
  404.     sprintf(cmd, "DELETE FROM editor WHERE id=%d", id);
  405.     res = PQexec(conn, cmd);
  406.     checkCmd(res);
  407.    
  408.     sprintf(cmd, "DELETE FROM admin WHERE id=%d", id);
  409.     res = PQexec(conn, cmd);
  410.     checkCmd(res);
  411. }
  412.  
  413. int main()
  414. {
  415. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!