API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Feb 6th, 2016
117
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
C 10.67 KB | None | 0 0
raw download clone embed print report
  1. #define BUFF_SIZE 1024
  2.  
  3. extern void parseInput();
  4.  
  5. PGconn *conn = NULL;
  6.  
  7. static bool checkConn(PGconn *conn) {
  8.     if (!conn || PQstatus(conn) == CONNECTION_BAD) {
  9.         fprintf(stderr, "Connection to server failed: %s\n" ,
  10.         PQerrorMessage(conn));
  11.         PQfinish(conn);
  12.         return false;
  13.     }
  14.     return true;
  15. }
  16.  
  17. static bool checkResCmdOK(PGresult *res) {
  18.     if(!res || PQresultStatus(res) != PGRES_COMMAND_OK) {
  19.         fprintf(stderr, "Error executing query: %s\n",
  20.         PQresultErrorMessage(res));
  21.         PQclear(res);
  22.         return false;
  23.     }
  24.     return true;
  25. }
  26.  
  27. static bool checkResTupOK(PGresult *res) {
  28.     if(!res || PQresultStatus(res) != PGRES_TUPLES_OK) {
  29.         fprintf(stderr, "Error executing query: %s\n",
  30.         PQresultErrorMessage(res));
  31.         PQclear(res);
  32.         return false;
  33.     }
  34.     return true;
  35. }
  36.  
  37. int main() {
  38.     char connect_param[BUFF_SIZE];
  39.     sprintf(connect_param, "host=csl2.cs.technion.ac.il dbname=%s user=%s password=%s", USERNAME, USERNAME, PASSWORD);
  40.     conn = PQconnectdb(connect_param);
  41.     if(!checkConn(conn)) return 1;
  42.     parseInput();
  43.     PQfinish(conn);
  44.     return 0;
  45. }
  46.  
  47. void* addUser           (const char*    name) {
  48.     PGresult *res = NULL;
  49.     char cmd[BUFF_SIZE] = { 0 };
  50.  
  51.     // add max user
  52.     sprintf(cmd, "INSERT INTO users SELECT COALESCE((SELECT MAX(id) from users) + 1, 0) ,'%s' RETURNING id", name);
  53.     res = PQexec(conn, cmd);
  54.     if(!checkResTupOK(res))     return NULL;
  55.  
  56.     // print result
  57.     printf(ADD_USER, PQgetvalue(res,0,0));
  58.  
  59.     PQclear(res);
  60.     return (void*)1;
  61. }
  62.  
  63. void* addUserMin        (const char*    name) {
  64.     PGresult *res = NULL;
  65.     char cmd[BUFF_SIZE] = { 0 };
  66.     int i = 0;
  67.  
  68.     // add min user
  69.     sprintf(cmd, "INSERT INTO users SELECT COALESCE((SELECT MIN(id) FROM users WHERE id+1 NOT IN (SELECT id FROM users))+1,0), '%s'", name);
  70.     res = PQexec(conn, cmd);
  71.     if(!checkResCmdOK(res))     return NULL;
  72.     PQclear(res);
  73.  
  74.     // query db to get all users with 'name'
  75.     sprintf(cmd, "SELECT id, name FROM users WHERE name = '%s' ORDER BY id", name);
  76.     res = PQexec(conn, cmd);
  77.     if(!checkResTupOK(res))     return NULL;
  78.  
  79.     // print result
  80.     printf(USER_HEADER);
  81.     for(; i < PQntuples(res); i++){
  82.         printf(USER_RESULT, PQgetvalue(res,i,0), PQgetvalue(res,i,1));
  83.     }
  84.  
  85.     PQclear(res);
  86.     return (void*)1;
  87. }
  88.  
  89. void* removeUser        (const char*    id) {
  90.     PGresult *res = NULL;
  91.     char cmd[BUFF_SIZE] = { 0 };
  92.  
  93.     // query the DB if the user_id exists
  94.     sprintf(cmd, "SELECT id FROM users WHERE id = %s", id);
  95.     res = PQexec(conn, cmd);
  96.     if(!checkResTupOK(res))     return NULL;
  97.     if(PQntuples(res) == 0) {
  98.         printf(ILL_PARAMS);
  99.         PQclear(res);
  100.         return NULL;
  101.     }
  102.     PQclear(res);
  103.  
  104.     // remove the user id from the users
  105.     sprintf(cmd, "DELETE FROM users WHERE id = %s", id);
  106.     res = PQexec(conn, cmd);
  107.     if(!checkResCmdOK(res))     return NULL;
  108.     PQclear(res);
  109.  
  110.     // remove users photos
  111.     sprintf(cmd, "DELETE FROM photos WHERE user_id = %s", id);
  112.     res = PQexec(conn, cmd);
  113.     if(!checkResCmdOK(res))     return NULL;
  114.     PQclear(res);
  115.  
  116.     // remove users tags
  117.     sprintf(cmd, "DELETE FROM tags WHERE user_id = %s", id);
  118.     res = PQexec(conn, cmd);
  119.     if(!checkResCmdOK(res))     return NULL;
  120.     PQclear(res);
  121.  
  122.     return (void*)1;
  123. }
  124.  
  125. void* addPhoto          (const char*    user_id, const char*    photo_id) {
  126.     PGresult *res = NULL;
  127.     char cmd[BUFF_SIZE] = { 0 };
  128.  
  129.     // query the DB if the user_id exists
  130.     sprintf(cmd, "SELECT id FROM users WHERE id = %s", user_id);
  131.     res = PQexec(conn, cmd);
  132.     if(!checkResTupOK(res))     return NULL;
  133.     if(PQntuples(res) == 0) {
  134.         printf(ILL_PARAMS);
  135.         PQclear(res);
  136.         return NULL;
  137.     }
  138.     PQclear(res);
  139.  
  140.     // query the DB if the photo_id already exists
  141.     sprintf(cmd, "SELECT * FROM photos WHERE id = %s AND user_id = %s", photo_id, user_id);
  142.     res = PQexec(conn, cmd);
  143.     if(!checkResTupOK(res))     return NULL;
  144.     if(PQntuples(res) != 0) {
  145.         printf(EXISTING_RECORD);
  146.         PQclear(res);
  147.         return NULL;
  148.     }
  149.     PQclear(res);
  150.  
  151.     // add the photo with the requested photo_id to the user_id
  152.     sprintf(cmd, "INSERT INTO photos SELECT %s, %s", photo_id, user_id);
  153.     res = PQexec(conn, cmd);
  154.     if(!checkResCmdOK(res))     return NULL;
  155.     PQclear(res);
  156.  
  157.     return (void*)1;
  158. }
  159.  
  160. void* tagPhoto          (const char*    user_id, const char*    photo_id, const char*    info) {
  161.     PGresult *res = NULL;
  162.     char cmd[BUFF_SIZE] = { 0 };
  163.  
  164.     // query the DB if the user_id exists
  165.     sprintf(cmd, "SELECT id FROM users WHERE id = %s", user_id);
  166.     res = PQexec(conn, cmd);
  167.     if(!checkResTupOK(res))     return NULL;
  168.     if(PQntuples(res) == 0) {
  169.         printf(ILL_PARAMS);
  170.         PQclear(res);
  171.         return NULL;
  172.     }
  173.     PQclear(res);
  174.  
  175.     // query the DB if the exact tag already exists
  176.     sprintf(cmd, "SELECT * FROM tags WHERE photo_id = %s AND user_id = %s AND info = '%s'", photo_id, user_id, info);
  177.     res = PQexec(conn, cmd);
  178.     if(!checkResTupOK(res))     return NULL;
  179.     if(PQntuples(res) != 0) {
  180.         printf(EXISTING_RECORD);
  181.         PQclear(res);
  182.         return NULL;
  183.     }
  184.     PQclear(res);
  185.  
  186.     // add the photo with the requested photo_id to the user_id
  187.     sprintf(cmd, "INSERT INTO tags SELECT %s, %s, '%s'", photo_id, user_id, info);
  188.     res = PQexec(conn, cmd);
  189.     if(!checkResCmdOK(res))     return NULL;
  190.     PQclear(res);
  191.  
  192.     return (void*)1;
  193. }
  194.  
  195. void* photosTags        () {
  196.     PGresult *res = NULL;
  197.     char cmd[BUFF_SIZE] = { 0 };
  198.     int i = 0;
  199.  
  200.     // query the DB for number of tags for each photo
  201.     sprintf(cmd, "SELECT photos.user_id, id, count(info) FROM photos LEFT OUTER JOIN tags ON photos.id = tags.photo_id AND photos.user_id = tags.user_id GROUP BY id, photos.user_id ORDER BY count(info) DESC, photos.user_id ASC, id ASC");
  202.     res = PQexec(conn, cmd);
  203.     if(!checkResTupOK(res))     return NULL;
  204.     if(PQntuples(res) == 0) {
  205.         printf(EMPTY);
  206.         PQclear(res);
  207.         return NULL;
  208.     }
  209.  
  210.     // print the appropriate output
  211.     printf(PHOTOS_HEADER);
  212.     for(; i < PQntuples(res); i++){
  213.         printf(PHOTOS_RESULT, PQgetvalue(res,i,0), PQgetvalue(res,i,1), PQgetvalue(res,i,2));
  214.     }
  215.     PQclear(res);
  216.  
  217.     return (void*)1;
  218. }
  219.  
  220. void* search            (const char*    word) {
  221.     PGresult *res = NULL;
  222.     char cmd[BUFF_SIZE] = { 0 };
  223.     int i = 0;
  224.  
  225.     // query the DB for number of tags for each photo that exists in photos containing the string word
  226.     sprintf(cmd, "SELECT photos.user_id, photos.id, count(tags) FROM photos LEFT OUTER JOIN tags ON photos.id = tags.photo_id AND photos.user_id = tags.user_id WHERE info LIKE '%%%s%%' GROUP BY photos.id, photos.user_id ORDER BY count(tags) DESC ,photos.user_id ASC ,photos.id DESC", word);
  227.  
  228.     res = PQexec(conn, cmd);
  229.     if(!checkResTupOK(res))     return NULL;
  230.     if(PQntuples(res) == 0) {
  231.         printf(EMPTY);
  232.         PQclear(res);
  233.         return NULL;
  234.     }
  235.  
  236.     // print the appropriate output
  237.     printf(PHOTOS_HEADER);
  238.     for(; i < PQntuples(res); i++){
  239.         printf(PHOTOS_RESULT, PQgetvalue(res,i,0), PQgetvalue(res,i,1), PQgetvalue(res,i,2));
  240.     }
  241.     PQclear(res);
  242.  
  243.     return (void*)1;
  244. }
  245.  
  246. void* commonTags        (const char*    k) {
  247.     PGresult *res = NULL;
  248.     char cmd[BUFF_SIZE] = { 0 };
  249.     int i = 0;
  250.  
  251.     // query the DB for common tags, at least k common tags
  252.     sprintf(cmd, "SELECT info, count(photo_id) FROM tags GROUP BY info HAVING count(photo_id) >= %s ORDER BY count(photo_id) DESC ,info ASC", k);
  253.  
  254.     res = PQexec(conn, cmd);
  255.     if(!checkResTupOK(res))     return NULL;
  256.     if(PQntuples(res) == 0) {
  257.         printf(EMPTY);
  258.         PQclear(res);
  259.         return NULL;
  260.     }
  261.  
  262.     // print the appropriate output
  263.     printf(COMMON_HEADER);
  264.     for(; i < PQntuples(res); i++){
  265.         printf(COMMON_LINE, PQgetvalue(res,i,0), PQgetvalue(res,i,1));
  266.     }
  267.     PQclear(res);
  268.  
  269.     return (void*)1;
  270. }
  271.  
  272. void* mostCommonTags    (const char*    k) {
  273.     PGresult *res = NULL;
  274.     char cmd[BUFF_SIZE] = { 0 };
  275.     int i = 0;
  276.     // query the DB for common tags, max k tuples
  277.     sprintf(cmd, "SELECT info, C FROM (SELECT count(*), MC1.info, MC1.C FROM (SELECT info, count(photo_id) AS C FROM tags GROUP BY info) AS MC1, (SELECT info, count(photo_id) AS C FROM tags GROUP BY info) AS MC2 WHERE MC1.C < MC2.C OR MC1.info >= MC2.info AND MC1.C = MC2.C GROUP BY MC1.info, MC1.C) AS SQ WHERE count <= %s ORDER BY C DESC, info ASC",k);
  278.  
  279.     res = PQexec(conn, cmd);
  280.     if(!checkResTupOK(res))     return NULL;
  281.     if(PQntuples(res) == 0) {
  282.         printf(EMPTY);
  283.         PQclear(res);
  284.         return NULL;
  285.     }
  286.  
  287.     // print the appropriate output
  288.     printf(COMMON_HEADER);
  289.     for(; i < PQntuples(res); i++){
  290.         printf(COMMON_LINE, PQgetvalue(res,i,0), PQgetvalue(res,i,1));
  291.     }
  292.     PQclear(res);
  293.  
  294.     return (void*)1;
  295. }
  296.  
  297. void* similarPhotos     (const char*    k, const char*    j) {
  298.     PGresult *res = NULL;
  299.     char cmd[BUFF_SIZE] = { 0 };
  300.     int i = 0;
  301.  
  302.     // query the DB for common tags, at least k common tags
  303.     sprintf(cmd, "SELECT SQ2.t1_user_id, U1.name, SQ2.t1_photo_id FROM (SELECT t1_photo_id, t1_user_id, COUNT(t2_photo_id::text || t2_user_id::text) AS C2 FROM (SELECT DISTINCT T1.photo_id AS T1_photo_id, T1.user_id AS T1_user_id, T2.photo_id AS T2_photo_id, T2.user_id AS T2_user_id, COUNT(T2.info) FROM tags T1, tags T2 WHERE T1.info = T2.info AND T1.photo_id <> T2.photo_id OR T1.info = T2.info AND T1.user_id <> T2.user_id GROUP BY T1.photo_id, T1.user_id, T2.photo_id, T2.user_id HAVING COUNT(T2.info) >= %s) AS SQ GROUP BY t1_photo_id, t1_user_id HAVING COUNT(t2_photo_id::text || t2_user_id::text) >= %s) AS SQ2 LEFT OUTER JOIN users U1 ON SQ2.t1_user_id = U1.id ORDER BY t1_user_id ASC, t1_photo_id ASC;", j, k);
  304.  
  305.     res = PQexec(conn, cmd);
  306.     if(!checkResTupOK(res))     return NULL;
  307.     if(PQntuples(res) == 0) {
  308.         printf(EMPTY);
  309.         PQclear(res);
  310.         return NULL;
  311.     }
  312.  
  313.     // print the appropriate output
  314.     printf(SIMILAR_HEADER);
  315.     for(; i < PQntuples(res); i++){
  316.         printf(SIMILAR_RESULT, PQgetvalue(res,i,0), PQgetvalue(res,i,1), PQgetvalue(res,i,2));
  317.     }
  318.     PQclear(res);
  319.  
  320.     return (void*)1;
  321. }
  322.  
  323. void* autoPhotoOnTagOn  () {
  324.     PGresult *res = NULL;
  325.     char cmd[BUFF_SIZE] = { 0 };
  326.  
  327.     // create function to add missing photos on tag insertion
  328.     sprintf(cmd, "  CREATE OR REPLACE FUNCTION add_missing_photos() \
  329.             RETURNS TRIGGER AS $$ \
  330.             BEGIN \
  331.                 IF((New.photo_id::text || NEW.user_id::text) NOT IN (SELECT (id::text || user_id::text) FROM photos)) THEN \
  332.                     INSERT INTO photos VALUES(NEW.photo_id, new.user_id); \
  333.                 END IF; \
  334.                 RETURN NEW; \
  335.             END; \
  336.             $$ LANGUAGE plpgsql;");
  337.  
  338.     res = PQexec(conn, cmd);
  339.     if(!checkResCmdOK(res))     return NULL;
  340.     PQclear(res);
  341.  
  342.     // create trigger to activate function
  343.     sprintf(cmd, "CREATE TRIGGER tag_trigger BEFORE INSERT ON tags FOR EACH ROW EXECUTE PROCEDURE add_missing_photos();");
  344.     res = PQexec(conn, cmd);
  345.     if(!checkResCmdOK(res))     return NULL;
  346.     PQclear(res);
  347.  
  348.     return (void*)1;
  349. }
  350.  
  351. void* autoPhotoOnTagOFF () {
  352.     PGresult *res = NULL;
  353.     char cmd[BUFF_SIZE] = { 0 };
  354.  
  355.     // destroy trigger
  356.     sprintf(cmd, "DROP TRIGGER tag_trigger ON tags;");
  357.     res = PQexec(conn, cmd);
  358.     if(!checkResCmdOK(res))     return NULL;
  359.     PQclear(res);
  360.  
  361.     // destroy function
  362.     sprintf(cmd, "DROP FUNCTION add_missing_photos();");
  363.     res = PQexec(conn, cmd);
  364.     if(!checkResCmdOK(res))     return NULL;
  365.     PQclear(res);
  366.  
  367.     return (void*)1;
  368. }
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!