hello / pg / trans.h

// $ gcc -lpq -I /opt/local/include

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <string>
using std::string;

#include <config.h>

// #ifndef __gnu_linux__ //cpp -dM /dev/null
// //どっちかが正解
// //#ifdef HAVE_LIBPQ_FE_H
//#include <libpq-fe.h> //macだとこっち -I/opt/local/includeとかあるとベター
// #else
// #include <postgresql/libpq-fe.h> //linuxだとこっち
// #endif
#include <postgresql/libpq-fe.h> //linuxだとこっち

class connection{
  const char *conninfo_;
  PGconn     *conn_;
  PGresult   *res_;
  PGconn * operator()(){ return conn_; };
  connection(const char * s){
    if( s == NULL )
      //conninfo = "dbname=starfish hostaddr= port=5432 user=www";
      //      conninfo = "dbname=gizmo_test hostaddr= port=5432 user=gizmo_test";
      conninfo_ = "";// "host=localhost ";//dbname=kuenishi"; // port=5432 "; //host=localhost ";
    //depending on environment, you have to change this char*
      conninfo_ = s;
    conn_ = PQconnectdb(conninfo_);
    if (PQstatus(conn_) != CONNECTION_OK) {    /* バックエンドとの接続確立に成功したかを確認する */
      fprintf(stderr, "Connection to database failed: %s",  PQerrorMessage(conn_));
      PQfinish(conn_); //exit_nicely(conn);
  void begin_tx(){
    res_ = PQexec(conn_, "BEGIN");
    check_error( "BEGIN");
  void end_tx(){
    res_ = PQexec(conn_, "END");
    check_error( "END" );
  int check_error(const char* msg){
    if (PQresultStatus(res_) != PGRES_COMMAND_OK)      {
      fprintf(stderr, "%s command failed: %s", msg, PQerrorMessage(conn_));
      //      exit( -1 );
      return -1;
    return 0;
  void exec(const char * sql){
    res_ = PQexec(conn_, sql );
  void exec(const string& sql){
  void clear_res(){

typedef struct {
  int id;
  char* key;
  char* value;
} doc;
class doc{
  int id;
  string key;
  string value;
  doc(int i, const string& k, const string& v):id(i),key(k),value(v){};

#define ALLOC_NEW_DOC ( (doc*)malloc(sizeof(doc)) )
#define DEL_DOC(x)  { if(!(x)->key)free( (x)->key ); if(!(x)->value)free( (x)->value ); free(x); }

doc* select_doc(int id);//get doc tuple 
//doc* select_doc(const char * key);
int select_docid(const char * key);
//int* select_tagids(int doc_id); //access tags table and get tag_ids
//int* select_docids(int tag_id); //access tags table and get doc_ids

int simple_insert(char* key, char* value);
int insert_test(char* key, char* value);
int insert_with(char* key, char* value);
int insert_tag(int id);
//int update_test(char* s);
int update_test(int id, char* value);
int delete_test(int id);
int creat_conn(char* s = NULL);
int begin_transaction();
int commit_transaction();
int fin_conn();
int check_error(const char * msg, PGresult* res);
int get_max_id();
int get_new_id();

int comp( const int * l, const int * r );

//struct FORMATS_{
const char sel[] = "SELECT * FROM documents WHERE id=%d";
const char sel2[] = "SELECT * FROM TAGS WHERE document_id='%d'";
const char sel3[] = //"SELECT * FROM documents WHERE key='%s'";
  "SELECT max(id) FROM documents";
//``Before you do an insert, you can select the next available ID, and this ID can NEVER be used by anybody else (selecting it basically generates it for use).  Then use that ID in your insert.''
const char sel4[] = "select nextval('document_seq');";

const char ins0[] = "insert into documents ( key, value ) values( '%s', '%s' )";
const char ins[] = "insert into documents ( id, key, value ) values( '%d', '%s', '%s' )";
const char ins2[] = "insert into documents ( key, value ) values( '%s', '%s' ); "
  " insert into tags (tag, document_id) "
  " values( '%s', (select id from documents where key='%s') ),"
  " ( '%s', (select id from documents where key='%s') );";
const char ins4[] = "insert into documents values( '%d', '%s', '%s' );";
const char ins5[] = "insert into tags(tag, document_id) values('tag', 4), ('tag2', 4)";
const char ins6[] = "insert into tags(tag, document_id) values('%s', %d), ('%s', %d);";
const char upd[] = "update documents set value='%s' where key='%s'";
const char upd2[] = "update documents set value='%s' where id='%d'";
const char del[] = "delete from documents where key='%s'";
const char del2[] = "delete from documents where id='%d'";
//} sql_formats;
$ID = SELECT * FROM NEXTVAL(‘key_seq’)
INSERT INTO log_list (date, text_id) \
VALUES ( today(), $ID )
UPDATE last_raw SET new = TRUE 
WHERE log_id = $ID
END */