db.c

text/plain

Download

#include <pgsql/libpq-fe.h>
#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include <time.h>
#include "db.h"

static PGconn *conn;
static PGresult *res;

static int connectDB() {
  char * dbName = "some_db";
  char * pguser = "some_user";
  char * pgpasswd = "some_passwd";
  
  conn = PQsetdbLogin(NULL, NULL, NULL, NULL, dbName, pguser, pgpasswd);
  
  if (PQstatus(conn) == CONNECTION_BAD) {
    fprintf(stderr, "Connection to database '%s' failed.\n", dbName);
    fprintf(stderr, "%s", PQerrorMessage(conn));
    return -1;
  }
  
  return 1; 
}

static void disconnectDB() {
  PQfinish(conn);
}

static int startTransaction() {
  int ret = 1;

  res = PQexec(conn, "BEGIN");
  if (PQresultStatus(res) != PGRES_COMMAND_OK) {
    fprintf(stderr, "BEGIN command failed\n");
    ret = -1;
  }                                         
  PQclear(res);
  
  return ret;
}

static void commitTransaction() {
  res = PQexec(conn, "CLOSE mc");
  PQclear(res);
  res = PQexec(conn, "COMMIT");
  PQclear(res);          
}

static char * getString(int tup, int field) {
  char * ret;
  int n = PQgetlength(res, tup, field);
  ret = strncpy(malloc(n+1), PQgetvalue(res, tup, field), n);
  ret[n] = 0;
  
  return ret;
}

static int execCountSelect(const char * sql) {
  char runSQL[1024];
  int ret = 0;
  
  if (startTransaction()) {
    strcpy(runSQL, "DECLARE mc CURSOR FOR ");
    strcat(runSQL, sql);
    res = PQexec(conn, runSQL);
    if (PQresultStatus(res) == PGRES_COMMAND_OK) {       
      PQclear(res);
      res = PQexec(conn, "FETCH ALL in mc");
      if (PQresultStatus(res) == PGRES_TUPLES_OK)
        ret = atoi(PQgetvalue(res, 0, 0));
    }
  }
  PQclear(res);
  commitTransaction();
  
  return ret;
}

static int execSQL(const char * sql) {
  int ret = -1;
  char runSQL[1024];
  
  if (startTransaction()) {
    strcpy(runSQL, "DECLARE mc CURSOR FOR ");
    strcat(runSQL, sql);
    PQexec(conn, runSQL);
    if (PQresultStatus(res) == PGRES_COMMAND_OK)
      ret = 1;
  }
  PQclear(res);
  
  return ret;
}

static int fetchNext() {
  res = PQexec(conn, "FETCH FORWARD 1 IN mc");
  if (PQresultStatus(res) == PGRES_TUPLES_OK) return 1;
  else return -1;  
}

static char * getCurTime() {
  time_t curTime;
  char * ret;

  ret = (char *)malloc(6);

  time(&curTime);
   
  strftime(ret, 6, "%H:%M", localtime(&curTime));

  return ret;
}

testStruct * getAllowTest(const char * loginName, int * num) {
  testStruct * ret;
  char sql[500], * desc, * curTime;
  int i = 0;

  ret = NULL;
  *num = 0;
  curTime = getCurTime();

  if (connectDB()) {
    sprintf(sql,

"SELECT Count(Id_Test) FROM Run_Test \
 WHERE Run_Test.Id_Student=Student.Id \
 AND Student.Name='%s' AND \
 Run_Test.startTime <= '%s' AND \
 Run_Test.stopTime >= '%s'", 

    loginName, curTime, curTime);
    *num = execCountSelect(sql);
    if (*num != 0) {
    
      sprintf(sql, 
"SELECT * FROM Test \
 WHERE Test.Id IN \
   (SELECT Id_Test FROM Run_Test \
      WHERE Run_Test.Id_Student=Student.Id AND \
 Student.Name='%s' AND Run_Test.startTime <= '%s' AND \
 Run_Test.stopTime >= '%s')", 
      loginName, curTime, curTime);
      
      if (execSQL(sql)) {
        ret = (testStruct *)calloc(sizeof(testStruct), *num);
        while (i < *num)
          if (fetchNext()) {
            ret[i].id = atoi(PQgetvalue(res, 0, 0));
            ret[i].testNum = atoi(PQgetvalue(res, 0, 2));
            desc = PQgetvalue(res, 0, 3);
            ret[i++].Description = strcpy(malloc(strlen(desc)+1), desc);
            PQclear(res);
          }
          else break;        
        PQclear(res);
        commitTransaction();
      }
    }
  }

 free(curTime);
 disconnectDB();
 return ret;
}

questionStruct * getQuestion(const int idTest, int * num) {
  questionStruct * ret;
  int i = 0, j;
  char sql[500], * buf;
  
  ret = 0;
  *num = 0;
  
  if (connectDB()) {
  
    sprintf(sql, "SELECT Count(id) FROM Question WHERE Id_Test = %d", idTest);
    *num = execCountSelect(sql);
    
    if (*num != 0) {
      sprintf(sql, "SELECT * FROM Question WHERE Id_Test=%d GROUP BY Id", idTest);
      if (execSQL(sql)) {
        ret = (questionStruct *)calloc(sizeof(questionStruct), *num);
        while (i < *num)
          if (fetchNext()) {
            ret[i].id = atoi(PQgetvalue(res, 0, 0));
            ret[i].text = getString(0, 2);
            ret[i].question = getString(0, 3);            
            ret[i++].bonus = atoi(PQgetvalue(res, 0, 4));
            //PQclear(res); /* Something not wrong here??? */
          }
          else i = *num;
        commitTransaction();
      }
      
      for (i = 0; i < *num; i++) {
        sprintf(sql, "SELECT * FROM Answer WHERE Id_Question=%d GROUP BY Id", 
                ret[i].id);
        if (execSQL(sql)) {
          res = PQexec(conn, "FETCH ALL IN mc");
          if (PQresultStatus(res) == PGRES_TUPLES_OK) {
            ret[i].numAnswers = PQntuples(res);
            ret[i].answers = (answerStruct *)calloc(sizeof(answerStruct), ret[i].numAnswers);
            for (j = 0; j < ret[i].numAnswers; j++) {
              ret[i].answers[j].text = getString(j, 2);
              buf = getString(j, 3);
              ret[i].answers[j].answer = *buf == 't' ? 1 : 0;
              free(buf);
            }          
          }
          PQclear(res);
          commitTransaction();
        }
      }
    }
  }
  
  disconnectDB();
  return ret;
}

void writeResult(const int idTest, const char *loginName, const int result) {
}