c语言调用sqlite

db sqlite c语言

#ifndef _DB_PLUGIN_H_
#define _DB_PLUGIN_H_
#pragma warning(disable: 4996)
//#include "mongoose.h"


//typedef struct mg_keyvalue
//{
//	char *key;
//	int klen ;
//	char *value;
//	int vlen;
//}mg_keyvalue;


void *db_open(const char *db_path);
void db_close(void **db_handle);

enum { API_OP_GET, API_OP_SET, API_OP_DEL };

char *db_op_select_content(void *db, const char *tablename ,const char *id);
//void db_op(const char *key, void *db, int op);

#endif /* CS_MONGOOSE_EXAMPLES_API_SERVER_DB_PLUGIN_H_ */




#include "db_plugin.h"
#include "sqlite3.h"
//#include "time.h"
#include 
#include 
//void time_get(char * buf)
//{
//	time_t rawtime;
//	struct tm * timeinfo;
//	time(&rawtime);
//	timeinfo = localtime(&rawtime);
//	strcpy(buf, asctime(timeinfo));
//}


void *db_open(const char *db_path) {
  sqlite3 *db = NULL;
  if (sqlite3_open_v2(db_path, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE |
                                        SQLITE_OPEN_FULLMUTEX,
                      NULL) == SQLITE_OK) {
	  char *sql = "CREATE TABLE IF NOT EXISTS log([key] TEXT, [val] TEXT,[category] TEXT, [infotime] TimeStamp NOT NULL DEFAULT(datetime('now', 'localtime'))";
	  sqlite3_exec(db,sql, 0, 0, 0);
  }
  return db;
}

void db_close(void **db_handle) {
  if (db_handle != NULL && *db_handle != NULL) {
    sqlite3_close(*db_handle);
    *db_handle = NULL;
  }
}

char *db_op_select_content(void *db,const char *tablename , const char *id)
{
	sqlite3_stmt *stmt = NULL;
	const char *data = NULL;
	int result;
	char buffer[128];
	sprintf(buffer, "SELECT * FROM %s WHERE id = ?;", tablename);
	if (sqlite3_prepare_v2(db, buffer, -1, &stmt,
		NULL) == SQLITE_OK) {
		sqlite3_bind_text(stmt, 1, id, strlen(id), SQLITE_STATIC);
		result = sqlite3_step(stmt);
		data = (char *)sqlite3_column_text(stmt, 0);
		if ((result == SQLITE_OK || result == SQLITE_ROW) && data != NULL) {
			char * content = malloc(strlen(data));

			/* mg_printf(nc,
			"HTTP/1.1 200 OK\r\n"
			"Content-Type: text/plain\r\n"
			"Content-Length: %d\r\n\r\n%s",
			(int) strlen(data), data);*/
		}
		else {
			//mg_printf(nc, "%s",
			//          "HTTP/1.1 404 Not Found\r\n"
			//          "Content-Length: 0\r\n\r\n");
		}
		sqlite3_finalize(stmt);
	}
	else {
		//mg_printf(nc, "%s",
		//          "HTTP/1.1 500 Server Error\r\n"
		//          "Content-Length: 0\r\n\r\n");
	}

}


static void op_set(	const char *param, void *db) {
  sqlite3_stmt *stmt = NULL;
  //char value[200];
  //const struct mg_str *body =
   //   hm->query_string.len > 0 ? &hm->query_string : &hm->body;

  char key[64];
  char value[512];
  char time[32];
  //mg_get_http_var(&hm->body, "key", key, sizeof(key));
  //mg_get_http_var(&hm->body, "value", value, sizeof(value));
//  time_get(time);

  if (sqlite3_prepare_v2(db, "INSERT OR REPLACE INTO log VALUES (?, ?, ?);", -1,
                         &stmt, NULL) == SQLITE_OK) {
    sqlite3_bind_text(stmt, 1, key, strlen(key), SQLITE_STATIC);
    sqlite3_bind_text(stmt, 2, value, strlen(value), SQLITE_STATIC);
	sqlite3_bind_text(stmt, 3, time, strlen(time), SQLITE_STATIC);

    sqlite3_step(stmt);
    sqlite3_finalize(stmt);
  }
  //mg_printf(nc, "%s", "HTTP/1.1 200 OK\r\nContent-Length: 0\r\n\r\n");
  //printf("test is ok");
 // mg_printf_http_chunk(nc, "{ \"result\": %ld }", 1);
  //mg_send_http_chunk(nc, "", 0); /* Send empty chunk, the end of response */

}

static void op_get(const char *id, void *db) {
  sqlite3_stmt *stmt = NULL;
  const char *data = NULL;
  int result;
  //(void) hm;

  if (sqlite3_prepare_v2(db, "SELECT * FROM log WHERE key = ?;", -1, &stmt,
                         NULL) == SQLITE_OK) {
    sqlite3_bind_text(stmt, 1, id, strlen(id), SQLITE_STATIC);
    result = sqlite3_step(stmt);
    data = (char *) sqlite3_column_text(stmt, 0);
    if ((result == SQLITE_OK || result == SQLITE_ROW) && data != NULL) {
     /* mg_printf(nc,
                "HTTP/1.1 200 OK\r\n"
                "Content-Type: text/plain\r\n"
                "Content-Length: %d\r\n\r\n%s",
                (int) strlen(data), data);*/
    } else {
      //mg_printf(nc, "%s",
      //          "HTTP/1.1 404 Not Found\r\n"
      //          "Content-Length: 0\r\n\r\n");
    }
    sqlite3_finalize(stmt);
  } else {
    //mg_printf(nc, "%s",
    //          "HTTP/1.1 500 Server Error\r\n"
    //          "Content-Length: 0\r\n\r\n");
  }
}

static void op_del(struct mg_connection *nc, const struct http_message *hm,
                   const struct mg_str *key, void *db) {
  //sqlite3_stmt *stmt = NULL;
//  int result;

  //(void) hm;


  //if (sqlite3_prepare_v2(db, "DELETE FROM log WHERE key = ?;", -1, &stmt,
  //                       NULL) == SQLITE_OK) {
  //  sqlite3_bind_text(stmt, 1, key->p, key->len, SQLITE_STATIC);
  //  result = sqlite3_step(stmt);
  //  if (result == SQLITE_OK || result == SQLITE_ROW) {
  //    mg_printf(nc, "%s", "HTTP/1.1 200 OK\r\nContent-Length: 0\r\n\r\n");
  //  } else {
  //    mg_printf(nc, "%s",
  //              "HTTP/1.1 404 Not Found\r\n"
  //              "Content-Length: 0\r\n\r\n");
  //  }
  //  sqlite3_finalize(stmt);
  //} else {
  //  mg_printf(nc, "%s",
  //            "HTTP/1.1 500 Server Error\r\n"
  //            "Content-Length: 0\r\n\r\n");
  //}
}

void db_op(const char *key, void *db, int op) {
  switch (op) {
    case API_OP_GET:
      op_get(key, db);
      break;
    case API_OP_SET:
      //op_set(nc, hm, key, db);
      break;
    case API_OP_DEL:
      //op_del(nc, hm, key, db);
      break;
    default:
      //mg_printf(nc, "%s",
      //          "HTTP/1.0 501 Not Implemented\r\n"
      //          "Content-Length: 0\r\n\r\n");
      break;
  }
}