1 /**********************************************************************
2 * Copyright (c) 2004, Leo Seib, Hannover
4 * Project:SQLiteDataset C++ Dynamic Library
5 * Module: SQLiteDataset class realisation file
6 * Author: Leo Seib E-Mail: leoseib@web.de
9 * Permission is hereby granted, free of charge, to any person obtaining a copy
10 * of this software and associated documentation files (the "Software"), to deal
11 * in the Software without restriction, including without limitation the rights
12 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
13 * copies of the Software, and to permit persons to whom the Software is
14 * furnished to do so, subject to the following conditions:
16 * The above copyright notice and this permission notice shall be included in
17 * all copies or substantial portions of the Software.
19 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
20 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
21 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
22 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
23 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
24 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
27 **********************************************************************/
32 #include "sqlitedataset.h"
33 #include "utils/log.h"
34 #include "system.h" // for Sleep(), OutputDebugString() and GetLastError()
35 #include "utils/URIUtils.h"
38 #pragma comment(lib, "sqlite3.lib")
44 //************* Callback function ***************************
46 int callback(void* res_ptr,int ncol, char** reslt,char** cols)
48 result_set* r = (result_set*)res_ptr;
50 if (!r->record_header.size())
52 r->record_header.reserve(ncol);
53 for (int i=0; i < ncol; i++) {
55 header.name = cols[i];
56 r->record_header.push_back(header);
62 sql_record *rec = new sql_record;
64 for (int i=0; i<ncol; i++)
66 field_value &v = rec->at(i);
74 v.set_asString(reslt[i]);
77 r->records.push_back(rec);
82 static int busy_callback(void*, int busyCount)
85 OutputDebugString("SQLite collision\n");
89 //************* SqliteDatabase implementation ***************
91 SqliteDatabase::SqliteDatabase() {
94 _in_transaction = false; // for transaction
96 error = "Unknown database error";//S_NO_CONNECTION;
104 SqliteDatabase::~SqliteDatabase() {
109 Dataset* SqliteDatabase::CreateDataset() const {
110 return new SqliteDataset((SqliteDatabase*)this);
113 void SqliteDatabase::setHostName(const char *newHost) {
116 // hostname is the relative folder to the database, ensure it's slash terminated
117 if (host[host.length()-1] != '/' && host[host.length()-1] != '\\')
120 // ensure the fully qualified path has slashes in the correct direction
121 if ( (host[1] == ':') && isalpha(host[0]))
124 while ( (pos = host.find("/", pos)) != string::npos )
125 host.replace(pos++, 1, "\\");
130 while ( (pos = host.find("\\", pos)) != string::npos )
131 host.replace(pos++, 1, "/");
135 void SqliteDatabase::setDatabase(const char *newDb) {
138 // db is the filename for the database, ensure it's not slash prefixed
139 if (newDb[0] == '/' || newDb[0] == '\\')
142 // ensure the ".db" extension is appended to the end
143 if ( db.find(".db") != (db.length()-3) )
147 int SqliteDatabase::status(void) {
148 if (active == false) return DB_CONNECTION_NONE;
149 return DB_CONNECTION_OK;
152 int SqliteDatabase::setErr(int err_code, const char * qry){
154 case SQLITE_OK: error ="Successful result";
156 case SQLITE_ERROR: error = "SQL error or missing database";
158 case SQLITE_INTERNAL: error = "An internal logic error in SQLite";
160 case SQLITE_PERM: error ="Access permission denied";
162 case SQLITE_ABORT: error = "Callback routine requested an abort";
164 case SQLITE_BUSY: error = "The database file is locked";
166 case SQLITE_LOCKED: error = "A table in the database is locked";
168 case SQLITE_NOMEM: error = "A malloc() failed";
170 case SQLITE_READONLY: error = "Attempt to write a readonly database";
172 case SQLITE_INTERRUPT: error = "Operation terminated by sqlite_interrupt()";
174 case SQLITE_IOERR: error = "Some kind of disk I/O error occurred";
176 case SQLITE_CORRUPT: error = "The database disk image is malformed";
178 case SQLITE_NOTFOUND: error = "(Internal Only) Table or record not found";
180 case SQLITE_FULL: error = "Insertion failed because database is full";
182 case SQLITE_CANTOPEN: error = "Unable to open the database file";
184 case SQLITE_PROTOCOL: error = "Database lock protocol error";
186 case SQLITE_EMPTY: error = "(Internal Only) Database table is empty";
188 case SQLITE_SCHEMA: error = "The database schema changed";
190 case SQLITE_TOOBIG: error = "Too much data for one row of a table";
192 case SQLITE_CONSTRAINT: error = "Abort due to constraint violation";
194 case SQLITE_MISMATCH: error = "Data type mismatch";
196 default : error = "Undefined SQLite error";
198 error += "\nQuery: ";
204 const char *SqliteDatabase::getErrorMsg() {
205 return error.c_str();
208 int SqliteDatabase::connect(bool create) {
209 if (host.empty() || db.empty())
210 return DB_CONNECTION_NONE;
212 //CLog::Log(LOGDEBUG, "Connecting to sqlite:%s:%s", host.c_str(), db.c_str());
214 CStdString db_fullpath = URIUtils::AddFileToFolder(host, db);
219 int flags = SQLITE_OPEN_READWRITE;
221 flags |= SQLITE_OPEN_CREATE;
222 if (sqlite3_open_v2(db_fullpath.c_str(), &conn, flags, NULL)==SQLITE_OK)
224 sqlite3_busy_handler(conn, busy_callback, NULL);
226 if (setErr(sqlite3_exec(getHandle(),"PRAGMA empty_result_callbacks=ON",NULL,NULL,&err),"PRAGMA empty_result_callbacks=ON") != SQLITE_OK)
228 throw DbErrors(getErrorMsg());
231 return DB_CONNECTION_OK;
234 return DB_CONNECTION_NONE;
239 return DB_CONNECTION_NONE;
242 bool SqliteDatabase::exists(void)
245 if (!active) return bRet;
249 // performing a select all on the sqlite_master will return rows if there are tables
250 // defined indicating it's not empty and therefore must "exist".
251 sprintf(sqlcmd,"SELECT * FROM sqlite_master");
252 if ((last_err = sqlite3_exec(getHandle(),sqlcmd, &callback, &res,NULL)) == SQLITE_OK)
254 bRet = (res.records.size() > 0);
260 void SqliteDatabase::disconnect(void) {
261 if (active == false) return;
266 int SqliteDatabase::create() {
267 return connect(true);
270 int SqliteDatabase::copy(const char *backup_name) {
272 throw DbErrors("Can't copy database: no active connection...");
274 CLog::Log(LOGDEBUG, "Copying from %s to %s at %s", db.c_str(), backup_name, host.c_str());
277 string backup_db = backup_name;
279 sqlite3 *pFile; /* Database connection opened on zFilename */
280 sqlite3_backup *pBackup; /* Backup object used to copy data */
283 if (backup_name[0] == '/' || backup_name[0] == '\\')
284 backup_db = backup_db.substr(1);
286 // ensure the ".db" extension is appended to the end
287 if ( backup_db.find(".db") != (backup_db.length()-3) )
290 string backup_path = host + backup_db;
292 /* Open the database file identified by zFilename. Exit early if this fails
293 ** for any reason. */
294 rc = sqlite3_open(backup_path.c_str(), &pFile);
297 pBackup = sqlite3_backup_init(pFile, "main", getHandle(), "main");
301 (void)sqlite3_backup_step(pBackup, -1);
302 (void)sqlite3_backup_finish(pBackup);
305 rc = sqlite3_errcode(pFile);
308 (void)sqlite3_close(pFile);
310 if( rc != SQLITE_OK )
311 throw DbErrors("Can't copy database. (%d)", rc);
316 int SqliteDatabase::drop_analytics(void) {
317 // SqliteDatabase::copy used a full database copy, so we have a new version
318 // with all the analytics stuff. We should clean database from everything but data
320 throw DbErrors("Can't drop extras database: no active connection...");
325 CLog::Log(LOGDEBUG, "Cleaning indexes from database %s at %s", db.c_str(), host.c_str());
326 sprintf(sqlcmd, "SELECT name FROM sqlite_master WHERE type == 'index'");
327 if ((last_err = sqlite3_exec(conn, sqlcmd, &callback, &res, NULL)) != SQLITE_OK) return DB_UNEXPECTED_RESULT;
329 for (size_t i=0; i < res.records.size(); i++) {
330 sprintf(sqlcmd,"DROP INDEX '%s'", res.records[i]->at(0).get_asString().c_str());
331 if ((last_err = sqlite3_exec(conn, sqlcmd, NULL, NULL, NULL) != SQLITE_OK)) return DB_UNEXPECTED_RESULT;
335 CLog::Log(LOGDEBUG, "Cleaning views from database %s at %s", db.c_str(), host.c_str());
336 sprintf(sqlcmd, "SELECT name FROM sqlite_master WHERE type == 'view'");
337 if ((last_err = sqlite3_exec(conn, sqlcmd, &callback, &res, NULL)) != SQLITE_OK) return DB_UNEXPECTED_RESULT;
339 for (size_t i=0; i < res.records.size(); i++) {
340 sprintf(sqlcmd,"DROP VIEW '%s'", res.records[i]->at(0).get_asString().c_str());
341 if ((last_err = sqlite3_exec(conn, sqlcmd, NULL, NULL, NULL) != SQLITE_OK)) return DB_UNEXPECTED_RESULT;
345 CLog::Log(LOGDEBUG, "Cleaning triggers from database %s at %s", db.c_str(), host.c_str());
346 sprintf(sqlcmd, "SELECT name FROM sqlite_master WHERE type == 'trigger'");
347 if ((last_err = sqlite3_exec(conn, sqlcmd, &callback, &res, NULL)) != SQLITE_OK) return DB_UNEXPECTED_RESULT;
349 for (size_t i=0; i < res.records.size(); i++) {
350 sprintf(sqlcmd,"DROP TRIGGER '%s'", res.records[i]->at(0).get_asString().c_str());
351 if ((last_err = sqlite3_exec(conn, sqlcmd, NULL, NULL, NULL) != SQLITE_OK)) return DB_UNEXPECTED_RESULT;
353 // res would be cleared on destruct
355 return DB_COMMAND_OK;
358 int SqliteDatabase::drop() {
359 if (active == false) throw DbErrors("Can't drop database: no active connection...");
361 if (!unlink(db.c_str())) {
362 throw DbErrors("Can't drop database: can't unlink the file %s,\nError: %s",db.c_str(),strerror(errno));
364 return DB_COMMAND_OK;
368 long SqliteDatabase::nextid(const char* sname) {
369 if (!active) return DB_UNEXPECTED_RESULT;
370 int id;/*,nrow,ncol;*/
373 sprintf(sqlcmd,"select nextid from %s where seq_name = '%s'",sequence_table.c_str(), sname);
374 if ((last_err = sqlite3_exec(getHandle(),sqlcmd,&callback,&res,NULL)) != SQLITE_OK) {
375 return DB_UNEXPECTED_RESULT;
377 if (res.records.size() == 0) {
379 sprintf(sqlcmd,"insert into %s (nextid,seq_name) values (%d,'%s')",sequence_table.c_str(),id,sname);
380 if ((last_err = sqlite3_exec(conn,sqlcmd,NULL,NULL,NULL)) != SQLITE_OK) return DB_UNEXPECTED_RESULT;
384 id = res.records[0]->at(0).get_asInt()+1;
385 sprintf(sqlcmd,"update %s set nextid=%d where seq_name = '%s'",sequence_table.c_str(),id,sname);
386 if ((last_err = sqlite3_exec(conn,sqlcmd,NULL,NULL,NULL) != SQLITE_OK)) return DB_UNEXPECTED_RESULT;
389 return DB_UNEXPECTED_RESULT;
393 // methods for transactions
394 // ---------------------------------------------
395 void SqliteDatabase::start_transaction() {
397 sqlite3_exec(conn,"begin IMMEDIATE",NULL,NULL,NULL);
398 _in_transaction = true;
402 void SqliteDatabase::commit_transaction() {
404 sqlite3_exec(conn,"commit",NULL,NULL,NULL);
405 _in_transaction = false;
409 void SqliteDatabase::rollback_transaction() {
411 sqlite3_exec(conn,"rollback",NULL,NULL,NULL);
412 _in_transaction = false;
417 // methods for formatting
418 // ---------------------------------------------
419 string SqliteDatabase::vprepare(const char *format, va_list args)
421 string strFormat = format;
422 string strResult = "";
426 // %q is the sqlite format string for %s.
427 // Any bad character, like "'", will be replaced with a proper one
429 while ( (pos = strFormat.find("%s", pos)) != string::npos )
430 strFormat.replace(pos++, 2, "%q");
432 // the %I64 enhancement is not supported by sqlite3_vmprintf
433 // must be %ll instead
435 while ( (pos = strFormat.find("%I64", pos)) != string::npos )
436 strFormat.replace(pos++, 4, "%ll");
438 p = sqlite3_vmprintf(strFormat.c_str(), args);
449 //************* SqliteDataset implementation ***************
451 SqliteDataset::SqliteDataset():Dataset() {
459 SqliteDataset::SqliteDataset(SqliteDatabase *newDb):Dataset(newDb) {
466 SqliteDataset::~SqliteDataset(){
467 if (errmsg) sqlite3_free(errmsg);
471 void SqliteDataset::set_autorefresh(bool val){
477 //--------- protected functions implementation -----------------//
479 sqlite3* SqliteDataset::handle(){
481 return static_cast<SqliteDatabase*>(db)->getHandle();
486 void SqliteDataset::make_query(StringList &_sql) {
488 if (db == NULL) throw DbErrors("No Database Connection");
492 if (autocommit) db->start_transaction();
495 for (list<string>::iterator i =_sql.begin(); i!=_sql.end(); i++) {
498 Dataset::parse_sql(query);
499 if (db->setErr(sqlite3_exec(this->handle(),query.c_str(),NULL,NULL,&err),query.c_str())!=SQLITE_OK) {
500 throw DbErrors(db->getErrorMsg());
505 if (db->in_transaction() && autocommit) db->commit_transaction();
514 if (db->in_transaction()) db->rollback_transaction();
521 void SqliteDataset::make_insert() {
522 make_query(insert_sql);
527 void SqliteDataset::make_edit() {
528 make_query(update_sql);
532 void SqliteDataset::make_deletion() {
533 make_query(delete_sql);
537 void SqliteDataset::fill_fields() {
538 //cout <<"rr "<<result.records.size()<<"|" << frecno <<"\n";
539 if ((db == NULL) || (result.record_header.size() == 0) || (result.records.size() < (unsigned int)frecno)) return;
541 if (fields_object->size() == 0) // Filling columns name
543 const unsigned int ncols = result.record_header.size();
544 fields_object->resize(ncols);
545 for (unsigned int i = 0; i < ncols; i++)
546 (*fields_object)[i].props = result.record_header[i];
550 if (result.records.size() != 0)
552 const sql_record *row = result.records[frecno];
555 const unsigned int ncols = row->size();
556 fields_object->resize(ncols);
557 for (unsigned int i = 0; i < ncols; i++)
558 (*fields_object)[i].val = row->at(i);
562 const unsigned int ncols = result.record_header.size();
563 fields_object->resize(ncols);
564 for (unsigned int i = 0; i < ncols; i++)
565 (*fields_object)[i].val = "";
569 //------------- public functions implementation -----------------//
570 bool SqliteDataset::dropIndex(const char *table, const char *index)
574 sql = static_cast<SqliteDatabase*>(db)->prepare("DROP INDEX IF EXISTS %s", index);
576 return (exec(sql) == SQLITE_OK);
580 int SqliteDataset::exec(const string &sql) {
581 if (!handle()) throw DbErrors("No Database Connection");
586 // Strip size constraints from indexes (not supported in sqlite)
589 // before: CREATE UNIQUE INDEX ixPath ON path ( strPath(255) )
590 // after: CREATE UNIQUE INDEX ixPath ON path ( strPath )
592 // NOTE: unexpected results occur if brackets are not matched
593 if ( qry.find("CREATE UNIQUE INDEX") != string::npos ||
594 (qry.find("CREATE INDEX") != string::npos))
599 if ( (pos = qry.find("(")) != string::npos )
602 while ( (pos = qry.find("(", pos)) != string::npos )
604 if ( (pos2 = qry.find(")", pos)) != string::npos )
606 qry.replace(pos, pos2-pos+1, "");
612 // Strip ON table from DROP INDEX statements:
613 // before: DROP INDEX foo ON table
614 // after: DROP INDEX foo
615 size_t pos = qry.find("DROP INDEX ");
616 if ( pos != string::npos )
618 pos = qry.find(" ON ", pos+1);
620 if ( pos != string::npos )
621 qry = qry.substr(0, pos);
624 if((res = db->setErr(sqlite3_exec(handle(),qry.c_str(),&callback,&exec_res,&errmsg),qry.c_str())) == SQLITE_OK)
628 throw DbErrors(db->getErrorMsg());
632 int SqliteDataset::exec() {
636 const void* SqliteDataset::getExecRes() {
641 bool SqliteDataset::query(const char *query) {
642 if(!handle()) throw DbErrors("No Database Connection");
643 std::string qry = query;
644 int fs = qry.find("select");
645 int fS = qry.find("SELECT");
646 if (!( fs >= 0 || fS >=0))
647 throw DbErrors("MUST be select SQL!");
651 sqlite3_stmt *stmt = NULL;
652 if (db->setErr(sqlite3_prepare_v2(handle(),query,-1,&stmt, NULL),query) != SQLITE_OK)
653 throw DbErrors(db->getErrorMsg());
656 const unsigned int numColumns = sqlite3_column_count(stmt);
657 result.record_header.resize(numColumns);
658 for (unsigned int i = 0; i < numColumns; i++)
659 result.record_header[i].name = sqlite3_column_name(stmt, i);
662 while (sqlite3_step(stmt) == SQLITE_ROW)
663 { // have a row of data
664 sql_record *res = new sql_record;
665 res->resize(numColumns);
666 for (unsigned int i = 0; i < numColumns; i++)
668 field_value &v = res->at(i);
669 switch (sqlite3_column_type(stmt, i))
672 v.set_asInt64(sqlite3_column_int64(stmt, i));
675 v.set_asDouble(sqlite3_column_double(stmt, i));
678 v.set_asString((const char *)sqlite3_column_text(stmt, i));
681 v.set_asString((const char *)sqlite3_column_text(stmt, i));
690 result.records.push_back(res);
692 if (db->setErr(sqlite3_finalize(stmt),query) == SQLITE_OK)
701 throw DbErrors(db->getErrorMsg());
705 bool SqliteDataset::query(const string &q){
706 return query(q.c_str());
709 void SqliteDataset::open(const string &sql) {
714 void SqliteDataset::open() {
715 if (select_sql.size()) {
716 query(select_sql.c_str());
719 ds_state = dsInactive;
724 void SqliteDataset::close() {
727 edit_object->clear();
728 fields_object->clear();
729 ds_state = dsInactive;
734 void SqliteDataset::cancel() {
735 if ((ds_state == dsInsert) || (ds_state==dsEdit)) {
736 if (result.record_header.size())
739 ds_state = dsInactive;
744 int SqliteDataset::num_rows() {
745 return result.records.size();
749 bool SqliteDataset::eof() {
754 bool SqliteDataset::bof() {
759 void SqliteDataset::first() {
764 void SqliteDataset::last() {
769 void SqliteDataset::prev(void) {
774 void SqliteDataset::next(void) {
780 void SqliteDataset::free_row(void)
782 if (frecno < 0 || (unsigned int)frecno >= result.records.size())
785 sql_record *row = result.records[frecno];
789 result.records[frecno] = NULL;
793 bool SqliteDataset::seek(int pos) {
794 if (ds_state == dsSelect) {
802 int64_t SqliteDataset::lastinsertid()
804 if(!handle()) throw DbErrors("No Database Connection");
805 return sqlite3_last_insert_rowid(handle());
809 long SqliteDataset::nextid(const char *seq_name) {
810 if (handle()) return db->nextid(seq_name);
811 else return DB_UNEXPECTED_RESULT;
814 void SqliteDataset::interrupt() {
815 sqlite3_interrupt(handle());