API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Mar 25th, 2017
132
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
C 13.72 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 <stdio.h>
  5. int max=20;
  6. void makeHTML(PGconn *conn,int argc,char *arg[]){
  7.   int i;
  8.   PGresult *query;
  9.   char *messg;
  10.   char sql[200];
  11.   printf("<!DOCTYPE html>\n<html>\n<head>\n<meta charset=\"utf-8\">\n<title>Baza</title>\n");
  12.   printf("<style>tr:nth-child(even){background: #F0F0F0;}body{background:rgb(0,152,251);font-family: 'Advent Pro', sans-serif;} th{font-weight:normal;font-size:20px;} .title{background: rgb(26,31,37); color: white;border-bottom: 2px solid #737373; text-align:center;}  th,td {padding:15px;} .a{background:white;color:#4D4D4D;font-size:18px;}  .val:hover{background: #BFBFBF;} .a:hover{background: #CCCCCC;} td{margin-left:2px;margin-right:2px;} body{text-align: center;} table{border-radius:10px;border: 0px solid black;margin: 0 auto; text-align: left;} h2{font-size: 30px;  text-align:center; background: background:rgb(0,152,251);;border-bottom: 3px solid white;color: #454545;}</style>");
  13.   printf("<link href='http://fonts.googleapis.com/css?family=Advent+Pro&subset=latin,latin-ext' rel='stylesheet' type='text/css'>");
  14.   printf("</head>\n<body>\n");
  15.  
  16.   for(i=2;i<argc;i++){
  17.     strcpy(sql,"SELECT * FROM ");
  18.     strcat(sql,arg[i]);strcat(sql,";");
  19.  
  20.     query=PQexec(conn,sql);
  21.     messg = PQresultErrorMessage(query);
  22.  
  23.     // wystapil plad
  24.     if(strlen(messg)>0) printf("<div>BŁĄD: %s</div>\n",messg);
  25.  
  26.     if(PQresultStatus(query) == PGRES_TUPLES_OK){
  27.       int n,m;
  28.       int NoRows = PQntuples(query);
  29.       int NoFields = PQnfields(query);
  30.       printf("<h2>%s</h2>\n",arg[i]);
  31.       printf("<table>\n");
  32.       printf("<tr class=\"title\">\n");
  33.       for(n = 0; n < NoFields; n++){
  34.         printf("<th>%s</th>", PQfname(query,n));
  35.       }
  36.       printf("</tr>\n");
  37.  
  38.       for(m = 0; m < NoRows; m++){
  39.         printf("<tr class=\"a\">\n");
  40.         for(n = 0; n < NoFields; n++){
  41.           printf("<td class=\"val\">%s</td>\n", PQgetvalue(query,m,n));
  42.         }
  43.         printf("</tr>\n");
  44.       }
  45.       printf("</table>\n");
  46.     }
  47.     PQclear(query);
  48.   }//end of for
  49.  
  50.   printf("</body>\n</html>\n");
  51. }//end of function
  52. void doSQL(PGconn *conn, char *command)
  53. {
  54.   PGresult *result;
  55.  
  56.   printf("%s\n", command);
  57.  
  58.   result = PQexec(conn, command);
  59.   printf("\tstatus is     : %s\n", PQresStatus(PQresultStatus(result)));
  60.   printf("\t#rows affected: %s\n", PQcmdTuples(result));
  61.   printf("\tresult message: %s\n", PQresultErrorMessage(result));
  62.  
  63.   switch(PQresultStatus(result)) {
  64.   case PGRES_TUPLES_OK:
  65.     {
  66.       int n = 0, m = 0;
  67.       int nrows   = PQntuples(result);
  68.       int nfields = PQnfields(result);
  69.       printf("number of rows returned   = %d\n", nrows);
  70.       printf("number of fields returned = %d\n", nfields);
  71.       for(m = 0; m < nrows; m++) {
  72.     for(n = 0; n < nfields; n++)
  73.       printf(" %s = %s", PQfname(result, n),PQgetvalue(result,m,n));
  74.     printf("\n");
  75.       }
  76.     }
  77.   }
  78.   PQclear(result);
  79. }
  80.  
  81. int arlen(char *ar){
  82.     int a=0;
  83.     while(ar[a]!='\0')
  84.         a++;
  85.     return a;
  86.     }
  87. int main(int argc, char **argv)
  88. {
  89. //================================================================================================================================
  90. //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++MENU+
  91. //================================================================================================================================
  92.     if(argc==2 && strcmp(argv[1],"load")==0)
  93.         printf("Enter file name!\n");
  94.     if(argc==1){
  95.         printf("\n+------------------------------------------------------+\n\t\tPOSGRESQL DATABASE FROM FILE\n+------------------------------------------------------+\n");
  96.         printf(">show option list:\n\t~$./dbo\n");
  97.         printf(">load table from file_name files:\n\t~$./dbo load dbname file_name\n");
  98.         printf(">add record to table:\n\t$./dbo add dname tableName\n");
  99.         printf(">show tables in html page\n\t~$./dbo dbname table_name1 table_name2 > file.html\n\n");
  100.         printf("+------------------------------------------------------+\n");
  101.     }
  102. //================================================================================================================================
  103. //+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++LOAD+
  104. //================================================================================================================================
  105.  
  106.     else{
  107.     if(argc>2 && strcmp(argv[1],"load")==0){
  108.     int filesCounter=0,i=0,j=0,k=0;
  109.     char fileName[300]={'\0'};
  110.     char columnNamesLine[300]={'\0'};
  111. for(filesCounter=3;filesCounter<argc;filesCounter++){
  112.     for(i=0;i<300;i++)
  113.         fileName[i]='\0';
  114.     strcat(fileName,argv[filesCounter]);
  115.     j=0;
  116.      while(fileName[j]!='.' && fileName!='\0')
  117.         j++;
  118.     char tableName[50];
  119.     for(i=0;i<50;i++)tableName[i]='\0';
  120.     for(i=0;i<j;i++)
  121.         tableName[i]=fileName[i];
  122.     FILE *plik = fopen(fileName, "r");
  123.     if (plik == NULL){
  124.     printf("\nCannot open \"%s\" file.\n",fileName);
  125.     exit(-1);    // trzeba dołączyć <stdlib.h>
  126.     }
  127.     else{
  128.     system("clear");
  129.     printf("\t\tPOSGRESQL DATABASE FROM FILE\n--------------------------------------------------------\n");
  130.     printf("File \"%s\" opened succesfully.",fileName);
  131.     printf("\n--------------------------------------------------------\n");
  132.     fgets(columnNamesLine,300,plik);
  133.     }
  134.     int columnNamesLineLength= arlen(columnNamesLine);
  135.     int columnsCounter=1;
  136.     for(i=0;i<columnNamesLineLength;i++)
  137.         if(columnNamesLine[i]==';')
  138.             columnsCounter++;
  139.     char columnNames[columnsCounter][50];
  140.     for(i=0;i<columnsCounter;i++)
  141.         for(j=0;j<50;j++)
  142.             columnNames[i][j]='\0';
  143.     j=0;
  144.     k=0;
  145.  
  146.     for(i=0;i<(columnNamesLineLength-1);i++){
  147.         if(columnNamesLine[i]!=';'){
  148.             columnNames[j][k]=columnNamesLine[i];
  149.             k++;
  150.         }
  151.         else{
  152.             k=0;
  153.             j++;
  154.         }
  155.     }
  156.  
  157.     char tableId[30]={'\0'};
  158.     strcat(tableId,"id_");
  159.     strcat(tableId,tableName);
  160.     printf("Tablename: %s \n",tableName);
  161.     printf("Columns[%d]:\n",columnsCounter+1);
  162.     printf("1. %s [PRIMARY KEY]\n",tableId);
  163.     for(i=0;i<columnsCounter;i++){
  164.         printf("%d. %s\n",i+2,columnNames[i]);
  165.     }
  166.     //---------------------------------------------------------------------------
  167. printf("\n--------------------------------------------------------\n");
  168.     char logData[150]="host=localhost port=5432 dbname=";
  169.     char password[50]={'\0'};
  170.     char dbname[50]={'\0'};
  171.     char user[50]={'\0'};
  172.     strcat(dbname,argv[2]);
  173.     printf("DBname: %s\n",dbname);
  174.     strcat(logData,dbname);
  175.     char createdb[30]={'\0'};
  176.     strcat(createdb,"createdb ");
  177.     strcat(createdb,dbname);
  178.     strcat(createdb," 2>/dev/null");
  179.     system(createdb);
  180.     printf("User: ");
  181.     scanf("%s",&user);
  182.     strcat(logData," user=");
  183.     strcat(logData,user);
  184.     printf("Password: ");
  185.     system("stty -echo");
  186.     scanf("%s",&password);
  187.     system("stty echo");
  188.     strcat(logData," password=");
  189.     strcat(logData,password);
  190. //-----------------------------------------
  191.     system("clear");
  192.   PGconn *conn = PQconnectdb(logData);
  193.  
  194.   if(PQstatus(conn) == CONNECTION_OK) {
  195.     printf(">Tables loaded succesfully!\n");
  196.  
  197. //----------------------------------------DROP
  198.     char dropTable[200]={'\0'};
  199.     strcat(dropTable,"DROP TABLE ");
  200.     strcat(dropTable, tableName);
  201.     strcat(dropTable, ";");
  202.     PQexec(conn, dropTable);
  203.  
  204. //----------------------------------------CREATE
  205.  
  206.     char createTable[200]={'\0'};
  207.     strcat(createTable,"CREATE TABLE ");
  208.     strcat(createTable, tableName);
  209.     strcat(createTable, "(");
  210.     strcat(createTable, tableId);
  211.     strcat(createTable," SERIAL PRIMARY KEY ");
  212.     for(i=0;i<columnsCounter;i++){
  213.         strcat(createTable,", ");
  214.         strcat(createTable,columnNames[i]);
  215.         strcat(createTable," VARCHAR(20)");
  216.     }
  217.     strcat(createTable,");");
  218.     PQexec(conn, createTable);
  219. //----------------------------------------INSERT FrOM FILE
  220.     char valuesLine[500]={'\0'};
  221.     char values[columnsCounter][50];
  222.     int valuesLineLength=0;
  223.     int valueLength=0;
  224.     char insert[500]={'\0'};
  225.     char alterTable[500]={'\0'};
  226.     char numb[100]={'\0'};
  227.     int wtf=0;
  228. do{
  229.     for(i=0;i<columnsCounter;i++)
  230.         for(j=0;j<50;j++)
  231.             values[i][j]='\0';
  232.     for(i=0;i<500;i++)
  233.         valuesLine[i]='\0';
  234.     fgets(valuesLine,500,plik);
  235.         valuesLineLength=arlen(valuesLine);
  236.     j=0;
  237.     k=0;
  238.     for(i=0;i<(valuesLineLength-1);i++){
  239.         if(valuesLine[i]!=';'){
  240.             values[j][k]=valuesLine[i];
  241.             k++;
  242.         }
  243.         else{
  244.             k=0;
  245.             j++;
  246.         }
  247.     }
  248.     for(i=0;i<columnsCounter;i++){
  249.         valueLength=arlen(values[i]);
  250.         if(valueLength>=20){
  251.             for(int alte=0;alte<500;alte++)
  252.                 alterTable[alte]='\0';
  253.             strcat(alterTable,"ALTER TABLE ");
  254.             strcat(alterTable,tableName);
  255.        
  256.             strcat(alterTable," ALTER COLUMN ");
  257.             strcat(alterTable,columnNames[i]);
  258.             strcat(alterTable," TYPE VARCHAR(");
  259.             sprintf(numb, "%d", (valueLength+2));
  260.             strcat(alterTable,numb);
  261.             strcat(alterTable,");");
  262.             PQexec(conn,alterTable);
  263.         }
  264.     }
  265.  
  266.     if((j+1)!=columnsCounter){
  267.         for(i=(j+1);i<columnsCounter;i++)
  268.             strcat(values[i],"NULL");
  269.     }
  270.     for(i=0;i<500;i++)
  271.         insert[i]='\0';
  272.     strcat(insert, "INSERT INTO ");
  273.     strcat(insert,tableName);
  274.     strcat(insert,"(");
  275.     strcat(insert,columnNames[0]);
  276.     for(wtf=1;wtf<columnsCounter;wtf++){
  277.         strcat(insert,",");
  278.         strcat(insert,columnNames[wtf]);
  279.     }
  280.     strcat(insert,") VALUES(");
  281.     strcat(insert,"'");
  282.     strcat(insert,values[0]);
  283.     strcat(insert,"'");
  284.     for(i=1;i<columnsCounter;i++){
  285.         strcat(insert,",'");
  286.         strcat(insert,values[i]);
  287.         strcat(insert,"'");
  288.     }
  289.     strcat(insert,");");
  290.     PQexec(conn, insert);
  291. }while(feof(plik)!=1);
  292.  
  293.  
  294. }
  295.  
  296.   else
  297.     printf(">CONNECTION FAILED:\n %s\n", PQerrorMessage(conn));
  298.  
  299.   PQfinish(conn);
  300. }
  301.   return EXIT_SUCCESS;
  302.  
  303. }
  304.  
  305. //================================================================================================================================
  306. //+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ADD+
  307. //================================================================================================================================
  308. if(argc<=3 && strcmp(argv[1],"add")==0){
  309.     printf("Enter dbname and table name!\n");
  310. }
  311. if(argc>3 && strcmp(argv[1],"add")==0){
  312.  
  313.     char tableName[50]={'\0'};
  314.     strcat(tableName,argv[3]);
  315.     int columnsCounter=0;
  316.     char logData[150]="host=localhost port=5432 dbname=";
  317.     char password[50]={'\0'};
  318.     char dbname[50]={'\0'};
  319.     char user[50]={'\0'};
  320.    
  321.     strcat(dbname,argv[2]);
  322.     strcat(logData,dbname);
  323.     char createdb[30]={'\0'};
  324.     strcat(createdb,"createdb ");
  325.     strcat(createdb,dbname);
  326.     strcat(createdb," 2>/dev/null");
  327.     system(createdb);
  328.     printf("DBname: %s\n",dbname);
  329.     printf("User: ");
  330.     scanf("%s",&user);
  331.     strcat(logData," user=");
  332.     strcat(logData,user);
  333.     printf("Password: ");
  334.     system("stty -echo");
  335.     scanf("%s",&password);
  336.     system("stty echo");
  337.     strcat(logData," password=");
  338.     strcat(logData,password);
  339.   PGconn *conn = PQconnectdb(logData);
  340.   char select[500]={'\0'};
  341.   strcat(select,"SELECT * FROM ");
  342.   strcat(select,tableName);
  343.   strcat(select,";");
  344.  
  345.   PGresult *result;
  346.   result=PQexec(conn,select);
  347.     char insert[500]={'\0'};
  348.     int i,j,k,wtf;
  349.   if(PQstatus(conn) == CONNECTION_OK) {
  350.       columnsCounter=PQnfields(result)-1;
  351.       system("clear");
  352.       printf("\nIlosc kolumn: %d\n",columnsCounter);
  353.        printf("%s\n",select);
  354.         printf("\t\tPOSGRESQL DATABASE FROM LINE\n--------------------------------------------------------\n");
  355.         printf("Enter data: \n");
  356.         char columnNames[columnsCounter+1][500];
  357.         char insertData[columnsCounter+1][500];
  358.         char tempColumnName[50]={'\0'};
  359.         for(i=0;i<=columnsCounter;i++)
  360.             for(j=0;j<500;j++){
  361.                 columnNames[i][j]='\0';
  362.                 insertData[i][j]='\0';
  363.             }
  364.         for(i=0;i<=columnsCounter;i++){
  365.             for(j=0;j<50;j++)
  366.                 tempColumnName[j]='\0';
  367.             char *szajz = PQfname(result,i);
  368.             strcat(columnNames[i],szajz);
  369.         }
  370.         for(i=1;i<=columnsCounter;i++){
  371.             printf("\n%d. %s: ",i,columnNames[i]);
  372.             scanf("%s",&insertData[i-1]);
  373.         }
  374.         strcat(insert, "INSERT INTO ");
  375.     strcat(insert,tableName);
  376.     strcat(insert,"(");
  377.     strcat(insert,columnNames[1]);
  378.     for(wtf=2;wtf<=columnsCounter;wtf++){
  379.         strcat(insert,",");
  380.         strcat(insert,columnNames[wtf]);
  381.     }
  382.     strcat(insert,") VALUES(");
  383.     strcat(insert,"'");
  384.     strcat(insert,insertData[0]);
  385.     strcat(insert,"'");
  386.     for(i=1;i<columnsCounter;i++){
  387.         strcat(insert,",'");
  388.         strcat(insert,insertData[i]);
  389.         strcat(insert,"'");
  390.     }
  391.     strcat(insert,");");
  392.     printf("Record added!\n");
  393.     PQexec(conn, insert);
  394.     }
  395.   else
  396.         printf(">CONNECTION FAILED:\n %s\n", PQerrorMessage(conn));
  397.   PQfinish(conn);
  398.   return EXIT_SUCCESS;
  399. }
  400.  
  401. //================================================================================================================================
  402. //+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++HTML+
  403. //================================================================================================================================
  404.  
  405. if(argc>=2 && strcmp(argv[1],"load")!=0 && strcmp(argv[1],"add")!=0){
  406.             int temporary;
  407.     int saved_stdout=dup(1);
  408.     char logData[150]="host=localhost port=5432 dbname=";
  409.     char password[50]={'\0'};
  410.     char dbname[50]={'\0'};
  411.     char user[50]={'\0'};
  412.     strcat(dbname, argv[1]);
  413.     strcat(logData,dbname);
  414.     scanf("%s",&user);
  415.     strcat(logData," user=");
  416.     strcat(logData,user);
  417.     system("stty -echo");
  418.     scanf("%s",&password);
  419.     system("stty echo");
  420.     strcat(logData," password=");
  421.     strcat(logData,password);
  422.  
  423.  
  424.   PGconn *conn = PQconnectdb(logData);
  425.     dup2(saved_stdout,1);
  426.     close(saved_stdout);
  427.     if(PQstatus(conn) == CONNECTION_OK) {
  428.  
  429.     dup2(temporary,1); //ustawienienie stdout na terminal
  430.  
  431.             makeHTML(conn,argc,argv);
  432.  
  433.     }
  434.             else
  435.         printf(">CONNECTION FAILED:\n %s\n", PQerrorMessage(conn));
  436.   PQfinish(conn);
  437.   return EXIT_SUCCESS;
  438. }
  439. }
  440.  
  441. }
  442. /*
  443. 1N73LL1G3NC3
  444. 15 7H3
  445. 4B1L17Y
  446. 70 4D4P7 70
  447. CH4NG3
  448.  */
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!