API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Mar 20th, 2017
94
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
C 9.79 KB | None | 0 0
raw download clone embed print report
  1. #include <stdlib.h>
  2. #include <libpq-fe.h>
  3. #include <string.h>
  4. #include <unistd.h>
  5. #include <limits.h>
  6. #include <stdio.h>
  7. #include <sys/types.h>
  8. #include <sys/stat.h>
  9. #include "csvparser.h"
  10. #define ClearScreen() printf("\033[H\033[J")
  11. #define BUFFER 256
  12. #define VARCHAR_LEN 20
  13. #define HTML_HEADER  \
  14. "<!doctype html>\
  15. <html lang=\"en\">\
  16. <head>\
  17.   <meta charset=\"utf-8\">\
  18.   <title>Select output</title>\
  19.   <meta name=\"description\" content=\"Select output\">\
  20.   <meta name=\"author\" content=\"SitePoint\">\
  21.   <style>\
  22.   table {\
  23.       border-collapse: collapse;\
  24.       width: 100%;\
  25.       text-align: center;\
  26.   }\
  27.   table, th {\
  28.       border: 1px solid black;\
  29.   }\
  30.   </style>\
  31. </head>\
  32. <body>\
  33. <table>"
  34. #define HTML_FOOT \
  35. "</table>\
  36. </body>\
  37. </html>"
  38.  
  39. PGconn *conn;
  40. char csvFileName[BUFFER];
  41. char databaseName[BUFFER];
  42.  
  43. char *RemoveExt(char* mystr) {
  44.     char *retstr;
  45.     char *lastdot;
  46.     if (mystr == NULL)
  47.          return NULL;
  48.     if ((retstr = malloc (strlen (mystr) + 1)) == NULL)
  49.         return NULL;
  50.     strcpy (retstr, mystr);
  51.     lastdot = strrchr (retstr, '.');
  52.     if (lastdot != NULL)
  53.         *lastdot = '\0';
  54.     return retstr;
  55. }
  56. void ConnectToDatabse()
  57. {
  58.   char connectionString[BUFFER] = "";
  59.   char login[32];
  60.   fprintf(stderr, "%s", "Login: ");
  61.   scanf("%s", login);
  62.   char *password= getpass("Password:");
  63.   sprintf(connectionString, "host=localhost port=5432 dbname=%s user=%s password=%s", databaseName, login, password);
  64.   conn = PQconnectdb(connectionString);
  65.   if(!(PQstatus(conn) == CONNECTION_OK))
  66.   {
  67.     printf("Connection failed: %s\n", PQerrorMessage(conn));
  68.     exit(0);
  69.   }
  70. }
  71. void doSQL(PGconn *conn, char *command)
  72. {
  73.   PGresult *result;
  74.  
  75.   printf("%s\n", command);
  76.  
  77.   result = PQexec(conn, command);
  78.   printf("status is     : %s\n", PQresStatus(PQresultStatus(result)));
  79.   printf("#rows affected: %s\n", PQcmdTuples(result));
  80.   printf("result message: %s\n", PQresultErrorMessage(result));
  81.   PQclear(result);
  82. }
  83. void DisplayMenu()
  84. {
  85.   ClearScreen();
  86.   printf("[ Baza danych: %s ]\n", databaseName);
  87.   printf("[ Plik csv: %s ]\n\n", csvFileName);
  88.   printf("[1] - Wczytaj csv. \n");
  89.   printf("[2] - Stworz tabele. \n");
  90.   printf("[3] - Dodaj rzad do tabeli. \n");
  91.   printf("[4] - Drukuj do HTML. \n");
  92.   printf("[x] - Zakoncz program. \n\n");
  93.   printf("Wybierz opcje: ");
  94. }
  95. void PromptToContinue()
  96. {
  97.     char c;
  98.     printf("\nKontynuuj...");
  99.     while(((c=getchar()) != '\n') && c != EOF);
  100.     getchar();
  101. }
  102. void ExtendColumn(int lenght, const char *columnName)
  103. {
  104.     printf("\n==> Rozszerzanie istniejącej kolumny...\n\n");
  105.     char tempString[BUFFER];
  106.     sprintf(tempString, "ALTER TABLE %s ALTER COLUMN %s TYPE VARCHAR(%i)", RemoveExt(csvFileName), columnName, lenght);
  107.     doSQL(conn, tempString);
  108.     printf("\n");
  109. }
  110. void CreateTable()
  111. {
  112.     char commandString[2048] = "";
  113.     char tempString[BUFFER] = "";
  114.     char c;
  115.     // DELETE OLD TABLE
  116.     printf("\n=> Usuwanie starej tabeli...\n\n");
  117.     sprintf(commandString, "DROP TABLE %s", RemoveExt(csvFileName));
  118.     doSQL(conn, commandString);
  119.     // CREATE NEW TABLE
  120.     printf("\n=> Tworzenia nowej tabeli...\n\n");
  121.     CsvParser *csvparser = CsvParser_new(csvFileName, ";", 1);
  122.     CsvRow *row;
  123.     const CsvRow *header = CsvParser_getHeader(csvparser);
  124.     sprintf(commandString, "CREATE TABLE %s(", RemoveExt(csvFileName));
  125.     const char **headerFields = CsvParser_getFields(header);
  126.     for (int i = 0 ; i < CsvParser_getNumFields(header) ; i++)
  127.     {
  128.         if(i == 0) sprintf(tempString, "%s VARCHAR(%i) UNIQUE", headerFields[i],VARCHAR_LEN);
  129.         else sprintf(tempString, "%s VARCHAR(%i)", headerFields[i],VARCHAR_LEN);
  130.         if(i != CsvParser_getNumFields(header)-1 ) strcat(tempString, ",");
  131.         strcat(commandString, tempString);
  132.     }
  133.     strcat(commandString, ");");
  134.     doSQL(conn, commandString);
  135.     // INSERT DATA
  136.     printf("\n=> Dodawanie nowych pozycji do tabeli...\n\n");
  137.     sprintf(commandString, "INSERT INTO %s(", RemoveExt(csvFileName));
  138.     for (int i = 0 ; i < CsvParser_getNumFields(header) ; i++)
  139.     {
  140.         sprintf(tempString, "%s", headerFields[i]);
  141.         if(i != CsvParser_getNumFields(header)-1 ) strcat(tempString, ",");
  142.         strcat(commandString, tempString);
  143.     }
  144.     strcat(commandString, ") VALUES (");
  145.     while ((row = CsvParser_getRow(csvparser))) {
  146.         const char **rowFields = CsvParser_getFields(row);
  147.         for (int i = 0 ; i < CsvParser_getNumFields(row) ; i++) {
  148.             if(strlen(rowFields[i]) > VARCHAR_LEN) ExtendColumn((int)strlen(rowFields[i]), headerFields[i]);
  149.             sprintf(tempString, "'%s'", rowFields[i]);
  150.             if(i != CsvParser_getNumFields(row)-1 ) strcat(tempString, ",");
  151.             strcat(commandString, tempString);
  152.         }
  153.         CsvParser_destroy_row(row);
  154.         strcat(commandString, "),(");
  155.     }
  156.     commandString[strlen(commandString)-2] = 0;
  157.     strcat(commandString, ";");
  158.     doSQL(conn, commandString);
  159.     CsvParser_destroy(csvparser);
  160.     PromptToContinue();
  161.     DisplayMenu();
  162. }
  163. void AddRow()
  164. {
  165.     printf("\n");
  166.     char commandString[2048] = "";
  167.     char tempString[BUFFER] = "";
  168.     char inputString[BUFFER] = "";
  169.     CsvParser *csvparser = CsvParser_new(csvFileName, ";", 1);
  170.     CsvRow *row;
  171.     const CsvRow *header = CsvParser_getHeader(csvparser);
  172.     const char **headerFields = CsvParser_getFields(header);
  173.     sprintf(commandString, "INSERT INTO %s(", RemoveExt(csvFileName));
  174.     for (int i = 0 ; i < CsvParser_getNumFields(header) ; i++)
  175.     {
  176.         sprintf(tempString, "%s", headerFields[i]);
  177.         if(i != CsvParser_getNumFields(header)-1 ) strcat(tempString, ",");
  178.         strcat(commandString, tempString);
  179.     }
  180.     strcat(commandString, ") VALUES (");
  181.     for (int i = 0 ; i < CsvParser_getNumFields(header) ; i++)
  182.     {
  183.         printf("Podaj wartosc dla kolumny %s: ", headerFields[i]);
  184.         scanf("%s", inputString);
  185.         if(strlen(inputString) > VARCHAR_LEN) ExtendColumn((int)strlen(inputString), headerFields[i]);
  186.         sprintf(tempString, "'%s'", inputString);
  187.         if(i != CsvParser_getNumFields(header)-1 ) strcat(tempString, ",");
  188.         strcat(commandString, tempString);
  189.     }
  190.     printf("\n=> Dodawanie nowej pozycji do tabeli...\n\n");
  191.     strcat(commandString, ");");
  192.     doSQL(conn, commandString);
  193.     CsvParser_destroy(csvparser);
  194.     PromptToContinue();
  195.     DisplayMenu();
  196. }
  197. void GetCsv()
  198. {
  199.     char filename[BUFFER];
  200.     printf("Podaj nazwe pliku: ");
  201.     scanf("%s", filename);
  202.     while(access(filename, F_OK ))
  203.     {
  204.       printf("Taki plik nie istnieje.\n");
  205.       printf("Podaj nazwe pliku: ");
  206.       scanf("%s", filename);
  207.     }
  208.     strcpy(csvFileName, filename);
  209.     DisplayMenu();
  210. }
  211. void PrintSelectToHtml(char* outputFileName, char *arguments[], int argumentsCount, int mode)
  212. {
  213.   char html[4096] = HTML_HEADER;
  214.   char tempString[BUFFER] = "";
  215.   char commandString[BUFFER] = "";
  216.   PGresult *result;
  217.   for(int i = 0; i<argumentsCount; i++)
  218.   {
  219.     sprintf(commandString, "SELECT * FROM %s", arguments[i]);
  220.     result = PQexec(conn, commandString);
  221.     if (mode != 1)
  222.     {
  223.       printf("\n%s\n", commandString);
  224.       printf("status is     : %s\n", PQresStatus(PQresultStatus(result)));
  225.       printf("#rows affected: %s\n", PQcmdTuples(result));
  226.       printf("result message: %s\n", PQresultErrorMessage(result));
  227.     }
  228.     switch(PQresultStatus(result))
  229.     {
  230.       case PGRES_TUPLES_OK:
  231.       {
  232.         int m, n;
  233.         int nrows = PQntuples(result);
  234.         int nfields = PQnfields(result);
  235.         strcat(html, "<tr>");
  236.         for(n = 0; n < nfields; n++)
  237.         {
  238.           sprintf(tempString, "<th>%s</th>", PQfname(result, n));
  239.           strcat(html, tempString);
  240.         }
  241.         strcat(html, "</tr>");
  242.         for(m = 0; m < nrows; m++)
  243.         {
  244.         strcat(html, "<tr>");
  245.         for(n = 0; n < nfields; n++)
  246.         {
  247.           sprintf(tempString, "<td>%s</td>", PQgetvalue(result, m, n));
  248.           strcat(html, tempString);
  249.         }
  250.         strcat(html, "</tr>");
  251.         }
  252.       }
  253.     }
  254.   }
  255.   strcat(html, HTML_FOOT);
  256.   PQclear(result);
  257.   if(mode != 1)
  258.   {
  259.     FILE *f = fopen(outputFileName, "w");
  260.     fprintf(f, "%s", html);
  261.     fclose(f);
  262.     printf("Wynik zapytania zapisany w pliku %s.\n", outputFileName);
  263.     PromptToContinue();
  264.   }
  265.   else printf("%s", html);
  266. }
  267. int main(int argc, char *argv[])
  268. {
  269.   if(argc >= 2)
  270.   {
  271.     strcpy(databaseName, argv[1]);
  272.     ConnectToDatabse();
  273.     char tempString[BUFFER];
  274.     sprintf(tempString, "%s.html", argv[1]);
  275.     PrintSelectToHtml(tempString, argv+2, argc-1, 1);
  276.   }
  277.   else
  278.   {
  279.     strcpy(csvFileName, "brak");
  280.     ClearScreen();
  281.     printf("[ Connecting to database ] \n");
  282.     fprintf(stderr, "%s", "Nazwa bazy: ");
  283.     scanf("%s", databaseName);
  284.     ConnectToDatabse();
  285.     char c;
  286.     DisplayMenu();
  287.     while ((c = fgetc(stdin)) != 'x')
  288.     {
  289.       if(c == '1') GetCsv();
  290.       else if (c == '2')
  291.       {
  292.         if(strcmp(csvFileName, "brak") == 0)
  293.         {
  294.           printf("Najpierw wybierz plik csv!\n");
  295.           printf("Wybierz opcje: ");
  296.         }
  297.         else CreateTable();
  298.       }
  299.       else if (c == '3')
  300.       {
  301.         if(strcmp(csvFileName, "brak") == 0)
  302.         {
  303.           printf("Najpierw wybierz plik csv!\n");
  304.           printf("Wybierz opcje: ");
  305.         }
  306.         else AddRow();
  307.       }
  308.       else if (c == '4')
  309.       {
  310.         char tableName[BUFFER];
  311.         char outputFileName[BUFFER];
  312.         printf("Podaj nazwe tabeli: ");
  313.         scanf("%s", tableName);
  314.         printf("Podaj nazwe pliku docelowego: ");
  315.         scanf("%s", outputFileName);
  316.         char *arguments[] = {tableName};
  317.         PrintSelectToHtml(outputFileName, arguments, 1, 0);
  318.         DisplayMenu();
  319.       }
  320.     }
  321.     PQfinish(conn);
  322.     return EXIT_SUCCESS;
  323.   }
  324. }
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!