COOPY » Guide  version 0.6.5
/home/paulfitz/cvs/coopy_scm/coopy/src/libsqlite/SqliteTextBook.cpp
Go to the documentation of this file.
00001 #include <coopy/SqliteTextBook.h>
00002 #include <coopy/SqliteSheet.h>
00003 #include <coopy/MergeOutputSqlDiff.h>
00004 #include <coopy/FormatSniffer.h>
00005 #include <coopy/OS.h>
00006 
00007 #include <sqlite3.h>
00008 #include <stdio.h>
00009 
00010 #define WANT_VECTOR2STRING
00011 #include <coopy/Stringer.h>
00012 
00013 #include <algorithm>
00014 
00015 using namespace std;
00016 using namespace coopy::store;
00017 using namespace coopy::store::sqlite;
00018 using namespace coopy::os;
00019 using namespace coopy::format;
00020 
00021 #define DB(x) ((sqlite3 *)(x))
00022 
00023 void coopy_set_function(sqlite3_context *context, int argc, 
00024                         sqlite3_value **argv){
00025   coopy::store::Sha1Generator& hasher = *((coopy::store::Sha1Generator*)sqlite3_user_data(context));
00026 
00027   COOPY_ASSERT( argc==1 );
00028   std::string result = "start";
00029   if (sqlite3_value_int64(argv[0])>0) {
00030     result = hasher.finish();
00031   } else {
00032     hasher.finish();
00033   }
00034   //printf("HASHER %s\n", result.c_str());
00035   sqlite3_result_text(context, result.c_str(), result.length()+1, 
00036                       SQLITE_TRANSIENT);
00037 }
00038 
00039 void coopy_add_function(sqlite3_context *context, int argc, 
00040                         sqlite3_value **argv){
00041   coopy::store::Sha1Generator& hasher = *((coopy::store::Sha1Generator*)sqlite3_user_data(context));
00042 
00043   //printf("HALLO?? %d\n", argc);
00044   for (int i=0; i<argc; i++) {
00045     char *txt = (char *)sqlite3_value_text(argv[i]);
00046     //printf("Adding %s\n", txt);
00047     if (!txt) {
00048       hasher.add("N",1);
00049     } else {
00050       hasher.add("X",1);
00051       int len = sqlite3_value_bytes(argv[i]);
00052       hasher.add(txt,len);
00053     }
00054   }
00055   sqlite3_result_null(context);
00056 }
00057 
00058 SqliteTextBook::SqliteTextBook(bool textual) {
00059   implementation = NULL;
00060   this->textual = textual;
00061   memory = false;
00062   prewrite = false;
00063   postwrite = false;
00064 }
00065 
00066 SqliteTextBook::~SqliteTextBook() {
00067   clear();
00068 }
00069 
00070 void SqliteTextBook::clear() {
00071   if (implementation!=NULL) {
00072     bool skip = false;
00073     if (implementation_count.isValid()) {
00074       if (implementation_count->getReferenceCount()>1) {
00075         skip = true;
00076       }
00077     }
00078     if (!skip) {
00079       sqlite3_close(DB(implementation));
00080     }
00081     implementation = NULL;
00082   }
00083   implementation_count.clear();
00084   if (hold_temp!="") {
00085     if (postwrite) {
00086       FILE *fin = fopen(hold_temp.c_str(),"rb");
00087       char buf[10000];
00088       if (fin) {
00089         size_t r = 0;
00090         do {
00091           r = fread(buf,1,sizeof(buf),fin);
00092           if (r>0) {
00093             fwrite(buf,1,r,stdout);
00094           }
00095         } while (r>0);
00096         fclose(fin);
00097       }
00098       fflush(stdout);
00099       postwrite = false;
00100     }
00101     OS::deleteFile(hold_temp);
00102     hold_temp = "";
00103   }
00104 }
00105 
00106 bool SqliteTextBook::read(const char *fname, bool can_create,
00107                           const Property& config,
00108                           SqliteTextBook *base) {
00109   dbg_printf("SqliteTextBook: reading %s\n", fname);
00110   clear();
00111 
00112   string alt_fname = fname;
00113   bool done = false;
00114   if (textual) {
00115     const Property& pout = config.get("output_info").asMap();
00116     if (pout.check("type")) {
00117       if (pout.get("type").asString()=="sqlite") {
00118         alt_fname = "";
00119         if (pout.check("database")) {
00120           alt_fname = pout.get("database").asString();
00121         }
00122         if (alt_fname == "") {
00123           alt_fname = pout.get("file").asString();
00124         }
00125         done = true;
00126         prewrite = true;
00127         if (alt_fname=="-") {
00128           //fprintf(stderr,"Cannot write raw sqlite to stdout\n");
00129           alt_fname = OS::getTemporaryFilename();
00130           hold_temp = alt_fname;
00131           postwrite = true;
00132           //printf("Stored %s\n", hold_temp.c_str());
00133         }
00134       }
00135     }
00136     if (!done) {
00137       alt_fname = ":memory:";
00138       memory = true;
00139     }
00140   } 
00141   if (string(alt_fname)=="-") {
00142     alt_fname = ":memory:";
00143     memory = true;
00144   }
00145 
00146   string txt = "";
00147   bool console = string(fname)=="-";
00148   if (textual||console) {
00149     FILE *fin = stdin;
00150     if (!console) {
00151       fin = fopen(fname,"rb");
00152     }
00153     if (fin==NULL && !can_create) {
00154         fprintf(stderr,"Failed to read database %s\n", fname);
00155         return false;
00156     }
00157     if (fin!=NULL) {
00158       while (!feof(fin)) {
00159         char buf[1000];
00160         int len = fread(buf,1,sizeof(buf),fin);
00161         if (len<0) { 
00162           fprintf(stderr,"Glitch in database %s\n", fname);
00163           clear();
00164           return false;
00165         }
00166         txt += string(buf,len);
00167       }
00168     }
00169     if (fin!=NULL && !console) {
00170       fclose(fin); fin = NULL;
00171     }
00172   }
00173 
00174   if (txt!="") {
00175       FormatSniffer f;
00176       f.setString(txt.substr(0,40).c_str());
00177       if (f.getFormat().id == FORMAT_BOOK_SQLITE) {
00178         if (alt_fname!="") {
00179           if (alt_fname!="-") {
00180             if (memory) {
00181               alt_fname = OS::getTemporaryFilename();
00182               hold_temp = alt_fname;
00183               //printf("Stored %s\n", hold_temp.c_str());
00184               memory = false;
00185             }
00186             FILE *fout = fopen(alt_fname.c_str(),"wb");
00187             if (fout) {
00188               fwrite(txt.c_str(),1,txt.length(),fout);
00189               fclose(fout);
00190             }
00191           }
00192         }
00193         txt = "";
00194       }
00195   }
00196   
00197   if (base) {
00198     if (base->implementation) {
00199       implementation = base->implementation;
00200       base->implementation_count = Poly<RefCount>(new RefCount(), true);
00201       implementation_count = base->implementation_count;
00202 
00203       sqlite3 *db = DB(implementation);
00204       string pre = "__coopy_peer__";
00205       char *query = sqlite3_mprintf("ATTACH %Q AS %s",
00206                                     alt_fname.c_str(), pre.c_str());
00207       int result = sqlite3_exec(db, query, NULL, NULL, NULL);
00208       if (result!=SQLITE_OK) {
00209         clear();
00210         base = NULL;
00211         return false;
00212       } else {
00213         dbg_printf("Attached sqlite database to existing one as: %s\n",
00214                    pre.c_str());
00215         prefix = pre;
00216         prefix_dot = prefix + ".";
00217       }
00218     }
00219   } 
00220 
00221   if (!base) {
00222 
00223     int result = sqlite3_open_v2(alt_fname.c_str(),
00224                                  (sqlite3**)(&implementation),
00225                                  SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE|
00226                                  SQLITE_OPEN_NOMUTEX,
00227                                  NULL);
00228     if (result!=SQLITE_OK) {
00229       fprintf(stderr,"Failed to open database %s\n", fname);
00230       clear();
00231       return false;
00232     }
00233 
00234     sqlite3_create_function((sqlite3*)implementation, 
00235                             "coopy_set", 1, SQLITE_UTF8, (void*)&hasher,
00236                             &coopy_set_function, NULL, NULL);
00237     sqlite3_create_function((sqlite3*)implementation, 
00238                             "coopy_add", -1, SQLITE_UTF8, (void*)&hasher,
00239                             &coopy_add_function, NULL, NULL);
00240   }
00241 
00242   const char *query = "PRAGMA synchronous = 0;";
00243   sqlite3_exec((sqlite3*)implementation, query, NULL, NULL, NULL);
00244 
00245   if (txt!="") {
00246     sqlite3_exec((sqlite3*)implementation, txt.c_str(), NULL, NULL, NULL);
00247   }
00248 
00249   if (config.check("table")) {
00250     names.clear();
00251     names.push_back(config.get("table").asString());
00252   } else {
00253     names = getNamesSql();
00254   }
00255 
00256   return true;
00257 }
00258 
00259 bool SqliteTextBook::save(const char *fname, const char *format,
00260                           bool itextual) {
00261   if (!itextual) {
00262     fprintf(stderr,
00263             "Sorry, cannot write an sqlite database this way just yet.\n");
00264     return false;
00265   }
00266   sqlite3 *db = DB(implementation);
00267   if (db==NULL) return false;
00268   bool console = string(fname)=="-";
00269   FILE *fout = stdout;
00270   if (!console) fout = fopen(fname,"w");
00271   if (fout==NULL) return false;
00272 
00273   fprintf(fout,"PRAGMA foreign_keys=OFF;\n");
00274   fprintf(fout,"BEGIN TRANSACTION;\n");
00275 
00276 
00277   sqlite3_stmt *statement = NULL;
00278   string cmd_get_tables = "SELECT name, sql, type FROM sqlite_master;";
00279   int iresult = sqlite3_prepare_v2(db, cmd_get_tables.c_str(), -1, 
00280                                    &statement, NULL);
00281   if (iresult!=SQLITE_OK) {
00282     const char *msg = sqlite3_errmsg(db);
00283     if (msg!=NULL) {
00284       fprintf(stderr,"Error: %s\n", msg);
00285     }
00286     clear();
00287     return false;
00288   }
00289   while (sqlite3_step(statement) == SQLITE_ROW) {
00290     char *name = (char *)sqlite3_column_text(statement,0);
00291     char *sql = (char *)sqlite3_column_text(statement,1);
00292     char *type = (char *)sqlite3_column_text(statement,2);
00293     if (sql!=NULL) {
00294       fprintf(fout,"%s;\n", sql);
00295     }
00296 
00297 
00298     if (string(type)=="table") {
00299       sqlite3_stmt *statement = NULL;
00300       char *query = NULL;
00301 
00302       query = sqlite3_mprintf("SELECT * FROM %Q ORDER BY ROWID",
00303                               name);
00304       
00305       int iresult = sqlite3_prepare_v2(db, query, -1, 
00306                                        &statement, NULL);
00307       if (iresult!=SQLITE_OK) {
00308         const char *msg = sqlite3_errmsg(db);
00309         if (msg!=NULL) {
00310           fprintf(stderr,"Error: %s\n", msg);
00311           fprintf(stderr,"Query was: %s\n", query);
00312         }
00313         sqlite3_finalize(statement);
00314         sqlite3_free(query);
00315       }
00316 
00317       string prefix = "INSERT INTO ";
00318       prefix += SqliteSheet::_quoted(name,'\"', true);
00319       prefix += " VALUES(";
00320       string postfix = ");";
00321 
00322       int w = sqlite3_column_count(statement);
00323       while (sqlite3_step(statement) == SQLITE_ROW) {
00324         string txt = "";
00325         for (int xx=0; xx<w; xx++) {
00326           if (xx>0) txt += ",";
00327           int t = sqlite3_column_type(statement,xx);
00328           if (t==SQLITE_NULL) {
00329             txt += "NULL";
00330           } else if (t==SQLITE_INTEGER) {
00331             int r = sqlite3_column_int(statement,xx);
00332             txt += stringer_encoder(r);
00333           } else if (t==SQLITE_FLOAT) {
00334             const char *r = (const char *)sqlite3_column_text(statement,xx);
00335             txt += r;
00336           } else {
00337             const char *r = (const char *)sqlite3_column_text(statement,xx);
00338             txt += SqliteSheet::_quoted(r,'\'', true);
00339           }
00340         }
00341         fprintf(fout,"%s%s%s\n",prefix.c_str(),txt.c_str(),postfix.c_str());
00342         }
00343       sqlite3_finalize(statement);
00344       sqlite3_free(query);
00345     }
00346   }
00347   sqlite3_finalize(statement);
00348 
00349 
00350   
00351   fprintf(fout,"COMMIT;\n");
00352   if (!console) {
00353     fclose(fout); fout = NULL;
00354   }
00355   return true;
00356 }
00357 
00358 bool SqliteTextBook::open(const Property& config, SqliteTextBook *base) {
00359   clear();
00360   if (!config.check("file")) {
00361     fprintf(stderr,"file parameter needed\n");
00362     return false;
00363   }
00364   if (!read(config.get("file").asString().c_str(),true,config,base)) {
00365     fprintf(stderr,"failed to read %s\n", config.get("file").asString().c_str());
00366     return false;
00367   }
00368   if (!config.check("table")) {
00369     dbg_printf("Loaded sqlite workbook\n");
00370     return true;
00371   }
00372   names.clear();
00373   names.push_back(config.get("table").asString().c_str());
00374   dbg_printf("Loaded sqlite workbook, table: %s\n",
00375              vector2string(names).c_str());
00376   return true;
00377 }
00378 
00379 
00380 vector<string> SqliteTextBook::getNamesSql() {
00381   vector<string> result;
00382   sqlite3 *db = DB(implementation);
00383   if (db==NULL) return result;
00384 
00385   sqlite3_stmt *statement = NULL;
00386   string cmd_get_tables = "SELECT name FROM " + prefix_dot + "sqlite_master WHERE type='table' ORDER BY name;";
00387   int iresult = sqlite3_prepare_v2(db, cmd_get_tables.c_str(), -1, 
00388                                    &statement, NULL);
00389   if (iresult!=SQLITE_OK) {
00390     const char *msg = sqlite3_errmsg(db);
00391     if (msg!=NULL) {
00392       fprintf(stderr,"Error: %s\n", msg);
00393     }
00394     clear();
00395     return result;
00396   }
00397   while (sqlite3_step(statement) == SQLITE_ROW) {
00398     char *name = (char *)sqlite3_column_text(statement,0);
00399     //printf("Got name %s\n", name);
00400     if (string(name)!="SQLITE_SEQUENCE"&&string(name)!="sqlite_sequence") {
00401       result.push_back(name);
00402     }
00403   }
00404   sqlite3_finalize(statement);
00405 
00406   return result;
00407 }
00408 
00409 PolySheet SqliteTextBook::readSheet(const std::string& name) {
00410   getNames();
00411   if (find(names.begin(),names.end(),name)==names.end()) {
00412     return PolySheet();
00413   }
00414   SqliteSheet *sheet = new SqliteSheet(implementation,name.c_str(),
00415                                        prefix.c_str());
00416   if (sheet!=NULL) sheet->connect();
00417   return PolySheet(sheet,true);
00418 }
00419 
00420 bool SqliteTextBook::addSheet(const SheetSchema& schema) {
00421   dbg_printf("sqlitetextbook::addsheet %s\n", schema.getSheetName().c_str());
00422   string name = schema.getSheetName();
00423   getNames();
00424   if (find(names.begin(),names.end(),name)!=names.end()) {
00425     fprintf(stderr,"Tried to add a sheet that already exists\n");
00426     return false;
00427   }
00428   sqlite3 *db = DB(implementation);
00429   if (db==NULL) {
00430     fprintf(stderr,"No database available.\n");
00431     return false;
00432   }
00433   SqliteSheet sheet(db,schema.getSheetName().c_str(),prefix.c_str());
00434   bool ok = sheet.create(schema);
00435   if (!ok) {
00436     fprintf(stderr,"Cannot create sheet with given structure.\n");
00437     return false;
00438   }
00439   names.push_back(name);
00440   return ok;
00441 }
00442 
 All Data Structures Namespaces Files Functions Variables Typedefs Enumerations Enumerator Defines