1 /****************************************************************************

     2 **

     3 ** Copyright (C) 2009 Nokia Corporation and/or its subsidiary(-ies).

     4 ** All rights reserved.

     5 ** Contact: Nokia Corporation (qt-info@nokia.com)

     6 **

     7 ** This file is part of the QtSql module of the Qt Toolkit.

     8 **

     9 ** $QT_BEGIN_LICENSE:LGPL$

    10 ** No Commercial Usage

    11 ** This file contains pre-release code and may not be distributed.

    12 ** You may use this file in accordance with the terms and conditions

    13 ** contained in the Technology Preview License Agreement accompanying

    14 ** this package.

    15 **

    16 ** GNU Lesser General Public License Usage

    17 ** Alternatively, this file may be used under the terms of the GNU Lesser

    18 ** General Public License version 2.1 as published by the Free Software

    19 ** Foundation and appearing in the file LICENSE.LGPL included in the

    20 ** packaging of this file.  Please review the following information to

    21 ** ensure the GNU Lesser General Public License version 2.1 requirements

    22 ** will be met: http://www.gnu.org/licenses/old-licenses/lgpl-2.1.html.

    23 **

    24 ** In addition, as a special exception, Nokia gives you certain additional

    25 ** rights.  These rights are described in the Nokia Qt LGPL Exception

    26 ** version 1.1, included in the file LGPL_EXCEPTION.txt in this package.

    27 **

    28 ** If you have questions regarding the use of this file, please contact

    29 ** Nokia at qt-info@nokia.com.

    30 **

    31 **

    32 **

    33 **

    34 **

    35 **

    36 **

    37 **

    38 ** $QT_END_LICENSE$

    39 **

    40 ****************************************************************************/

    41 

    42 #include "qsql_sqlite2.h"

    43 

    44 #include <qcoreapplication.h>

    45 #include <qvariant.h>

    46 #include <qdatetime.h>

    47 #include <qfile.h>

    48 #include <qregexp.h>

    49 #include <qsqlerror.h>

    50 #include <qsqlfield.h>

    51 #include <qsqlindex.h>

    52 #include <qsqlquery.h>

    53 #include <qstringlist.h>

    54 #include <qvector.h>

    55 

    56 #if !defined Q_WS_WIN32

    57 # include <unistd.h>

    58 #endif

    59 #include <sqlite.h>

    60 

    61 typedef struct sqlite_vm sqlite_vm;

    62 

    63 Q_DECLARE_METATYPE(sqlite_vm*)

    64 Q_DECLARE_METATYPE(sqlite*)

    65 

    66 QT_BEGIN_NAMESPACE

    67 

    68 static QVariant::Type nameToType(const QString& typeName)

    69 {

    70     QString tName = typeName.toUpper();

    71     if (tName.startsWith(QLatin1String("INT")))

    72         return QVariant::Int;

    73     if (tName.startsWith(QLatin1String("FLOAT")) || tName.startsWith(QLatin1String("NUMERIC")))

    74         return QVariant::Double;

    75     if (tName.startsWith(QLatin1String("BOOL")))

    76         return QVariant::Bool;

    77     // SQLite is typeless - consider everything else as string

    78     return QVariant::String;

    79 }

    80 

    81 class QSQLite2DriverPrivate

    82 {

    83 public:

    84     QSQLite2DriverPrivate();

    85     sqlite *access;

    86     bool utf8;

    87 };

    88 

    89 QSQLite2DriverPrivate::QSQLite2DriverPrivate() : access(0)

    90 {

    91     utf8 = (qstrcmp(sqlite_encoding, "UTF-8") == 0);

    92 }

    93 

    94 class QSQLite2ResultPrivate

    95 {

    96 public:

    97     QSQLite2ResultPrivate(QSQLite2Result *res);

    98     void cleanup();

    99     bool fetchNext(QSqlCachedResult::ValueCache &values, int idx, bool initialFetch);

   100     bool isSelect();

   101     // initializes the recordInfo and the cache

   102     void init(const char **cnames, int numCols);

   103     void finalize();

   104 

   105     QSQLite2Result* q;

   106     sqlite *access;

   107 

   108     // and we have too keep our own struct for the data (sqlite works via

   109     // callback.

   110     const char *currentTail;

   111     sqlite_vm *currentMachine;

   112 

   113     uint skippedStatus: 1; // the status of the fetchNext() that's skipped

   114     uint skipRow: 1; // skip the next fetchNext()?

   115     uint utf8: 1;

   116     QSqlRecord rInf;

   117 };

   118 

   119 static const uint initial_cache_size = 128;

   120 

   121 QSQLite2ResultPrivate::QSQLite2ResultPrivate(QSQLite2Result* res) : q(res), access(0), currentTail(0),

   122     currentMachine(0), skippedStatus(false), skipRow(false), utf8(false)

   123 {

   124 }

   125 

   126 void QSQLite2ResultPrivate::cleanup()

   127 {

   128     finalize();

   129     rInf.clear();

   130     currentTail = 0;

   131     currentMachine = 0;

   132     skippedStatus = false;

   133     skipRow = false;

   134     q->setAt(QSql::BeforeFirstRow);

   135     q->setActive(false);

   136     q->cleanup();

   137 }

   138 

   139 void QSQLite2ResultPrivate::finalize()

   140 {

   141     if (!currentMachine)

   142         return;

   143 

   144     char* err = 0;

   145     int res = sqlite_finalize(currentMachine, &err);

   146     if (err) {

   147         q->setLastError(QSqlError(QCoreApplication::translate("QSQLite2Result",

   148                                   "Unable to fetch results"), QString::fromAscii(err),

   149                                   QSqlError::StatementError, res));

   150         sqlite_freemem(err);

   151     }

   152     currentMachine = 0;

   153 }

   154 

   155 // called on first fetch

   156 void QSQLite2ResultPrivate::init(const char **cnames, int numCols)

   157 {

   158     if (!cnames)

   159         return;

   160 

   161     rInf.clear();

   162     if (numCols <= 0)

   163         return;

   164     q->init(numCols);

   165 

   166     for (int i = 0; i < numCols; ++i) {

   167         const char* lastDot = strrchr(cnames[i], '.');

   168         const char* fieldName = lastDot ? lastDot + 1 : cnames[i];

   169         

   170         //remove quotations around the field name if any

   171         QString fieldStr = QString::fromAscii(fieldName);

   172         QLatin1Char quote('\"');

   173         if ( fieldStr.length() > 2 && fieldStr.startsWith(quote) && fieldStr.endsWith(quote)) {

   174             fieldStr = fieldStr.mid(1);

   175             fieldStr.chop(1);

   176         }

   177         rInf.append(QSqlField(fieldStr,

   178                               nameToType(QString::fromAscii(cnames[i+numCols]))));

   179     }

   180 }

   181 

   182 bool QSQLite2ResultPrivate::fetchNext(QSqlCachedResult::ValueCache &values, int idx, bool initialFetch)

   183 {

   184     // may be caching.

   185     const char **fvals;

   186     const char **cnames;

   187     int colNum;

   188     int res;

   189     int i;

   190 

   191     if (skipRow) {

   192         // already fetched

   193         Q_ASSERT(!initialFetch);

   194         skipRow = false;

   195         return skippedStatus;

   196     }

   197     skipRow = initialFetch;

   198 

   199     if (!currentMachine)

   200         return false;

   201 

   202     // keep trying while busy, wish I could implement this better.

   203     while ((res = sqlite_step(currentMachine, &colNum, &fvals, &cnames)) == SQLITE_BUSY) {

   204         // sleep instead requesting result again immidiately.

   205 #if defined Q_WS_WIN32

   206         Sleep(1000);

   207 #else

   208         sleep(1);

   209 #endif

   210     }

   211 

   212     switch(res) {

   213     case SQLITE_ROW:

   214         // check to see if should fill out columns

   215         if (rInf.isEmpty())

   216             // must be first call.

   217             init(cnames, colNum);

   218         if (!fvals)

   219             return false;

   220         if (idx < 0 && !initialFetch)

   221             return true;

   222         for (i = 0; i < colNum; ++i)

   223             values[i + idx] = utf8 ? QString::fromUtf8(fvals[i]) : QString::fromAscii(fvals[i]);

   224         return true;

   225     case SQLITE_DONE:

   226         if (rInf.isEmpty())

   227             // must be first call.

   228             init(cnames, colNum);

   229         q->setAt(QSql::AfterLastRow);

   230         return false;

   231     case SQLITE_ERROR:

   232     case SQLITE_MISUSE:

   233     default:

   234         // something wrong, don't get col info, but still return false

   235         finalize(); // finalize to get the error message.

   236         q->setAt(QSql::AfterLastRow);

   237         return false;

   238     }

   239     return false;

   240 }

   241 

   242 QSQLite2Result::QSQLite2Result(const QSQLite2Driver* db)

   243 : QSqlCachedResult(db)

   244 {

   245     d = new QSQLite2ResultPrivate(this);

   246     d->access = db->d->access;

   247     d->utf8 = db->d->utf8;

   248 }

   249 

   250 QSQLite2Result::~QSQLite2Result()

   251 {

   252     d->cleanup();

   253     delete d;

   254 }

   255 

   256 void QSQLite2Result::virtual_hook(int id, void *data)

   257 {

   258     switch (id) {

   259     case QSqlResult::DetachFromResultSet:

   260         d->finalize();

   261         break;

   262     default:

   263         QSqlCachedResult::virtual_hook(id, data);

   264     }

   265 }

   266 

   267 /*

   268    Execute \a query.

   269 */

   270 bool QSQLite2Result::reset (const QString& query)

   271 {

   272     // this is where we build a query.

   273     if (!driver())

   274         return false;

   275     if (!driver()-> isOpen() || driver()->isOpenError())

   276         return false;

   277 

   278     d->cleanup();

   279 

   280     // Um, ok.  callback based so.... pass private static function for this.

   281     setSelect(false);

   282     char *err = 0;

   283     int res = sqlite_compile(d->access,

   284                                 d->utf8 ? query.toUtf8().constData()

   285                                         : query.toAscii().constData(),

   286                                 &(d->currentTail),

   287                                 &(d->currentMachine),

   288                                 &err);

   289     if (res != SQLITE_OK || err) {

   290         setLastError(QSqlError(QCoreApplication::translate("QSQLite2Result",

   291                      "Unable to execute statement"), QString::fromAscii(err),

   292                      QSqlError::StatementError, res));

   293         sqlite_freemem(err);

   294     }

   295     //if (*d->currentTail != '\000' then there is more sql to eval

   296     if (!d->currentMachine) {

   297         setActive(false);

   298         return false;

   299     }

   300     // we have to fetch one row to find out about

   301     // the structure of the result set

   302     d->skippedStatus = d->fetchNext(cache(), 0, true);

   303     if (lastError().isValid()) {

   304         setSelect(false);

   305         setActive(false);

   306         return false;

   307     }

   308     setSelect(!d->rInf.isEmpty());

   309     setActive(true);

   310     return true;

   311 }

   312 

   313 bool QSQLite2Result::gotoNext(QSqlCachedResult::ValueCache& row, int idx)

   314 {

   315     return d->fetchNext(row, idx, false);

   316 }

   317 

   318 int QSQLite2Result::size()

   319 {

   320     return -1;

   321 }

   322 

   323 int QSQLite2Result::numRowsAffected()

   324 {

   325     return sqlite_changes(d->access);

   326 }

   327 

   328 QSqlRecord QSQLite2Result::record() const

   329 {

   330     if (!isActive() || !isSelect())

   331         return QSqlRecord();

   332     return d->rInf;

   333 }

   334 

   335 QVariant QSQLite2Result::handle() const

   336 {

   337     return qVariantFromValue(d->currentMachine);

   338 }

   339 

   340 /////////////////////////////////////////////////////////

   341 

   342 QSQLite2Driver::QSQLite2Driver(QObject * parent)

   343     : QSqlDriver(parent)

   344 {

   345     d = new QSQLite2DriverPrivate();

   346 }

   347 

   348 QSQLite2Driver::QSQLite2Driver(sqlite *connection, QObject *parent)

   349     : QSqlDriver(parent)

   350 {

   351     d = new QSQLite2DriverPrivate();

   352     d->access = connection;

   353     setOpen(true);

   354     setOpenError(false);

   355 }

   356 

   357 

   358 QSQLite2Driver::~QSQLite2Driver()

   359 {

   360     delete d;

   361 }

   362 

   363 bool QSQLite2Driver::hasFeature(DriverFeature f) const

   364 {

   365     switch (f) {

   366     case Transactions:

   367     case SimpleLocking:

   368         return true;

   369     case Unicode:

   370         return d->utf8;

   371     default:

   372         return false;

   373     }

   374 }

   375 

   376 /*

   377    SQLite dbs have no user name, passwords, hosts or ports.

   378    just file names.

   379 */

   380 bool QSQLite2Driver::open(const QString & db, const QString &, const QString &, const QString &, int, const QString &)

   381 {

   382     if (isOpen())

   383         close();

   384 

   385     if (db.isEmpty())

   386         return false;

   387 

   388     char* err = 0;

   389     d->access = sqlite_open(QFile::encodeName(db), 0, &err);

   390     if (err) {

   391         setLastError(QSqlError(tr("Error opening database"), QString::fromAscii(err),

   392                      QSqlError::ConnectionError));

   393         sqlite_freemem(err);

   394         err = 0;

   395     }

   396 

   397     if (d->access) {

   398         setOpen(true);

   399         setOpenError(false);

   400         return true;

   401     }

   402     setOpenError(true);

   403     return false;

   404 }

   405 

   406 void QSQLite2Driver::close()

   407 {

   408     if (isOpen()) {

   409         sqlite_close(d->access);

   410         d->access = 0;

   411         setOpen(false);

   412         setOpenError(false);

   413     }

   414 }

   415 

   416 QSqlResult *QSQLite2Driver::createResult() const

   417 {

   418     return new QSQLite2Result(this);

   419 }

   420 

   421 bool QSQLite2Driver::beginTransaction()

   422 {

   423     if (!isOpen() || isOpenError())

   424         return false;

   425 

   426     char* err;

   427     int res = sqlite_exec(d->access, "BEGIN", 0, this, &err);

   428 

   429     if (res == SQLITE_OK)

   430         return true;

   431 

   432     setLastError(QSqlError(tr("Unable to begin transaction"),

   433                            QString::fromAscii(err), QSqlError::TransactionError, res));

   434     sqlite_freemem(err);

   435     return false;

   436 }

   437 

   438 bool QSQLite2Driver::commitTransaction()

   439 {

   440     if (!isOpen() || isOpenError())

   441         return false;

   442 

   443     char* err;

   444     int res = sqlite_exec(d->access, "COMMIT", 0, this, &err);

   445 

   446     if (res == SQLITE_OK)

   447         return true;

   448 

   449     setLastError(QSqlError(tr("Unable to commit transaction"),

   450                 QString::fromAscii(err), QSqlError::TransactionError, res));

   451     sqlite_freemem(err);

   452     return false;

   453 }

   454 

   455 bool QSQLite2Driver::rollbackTransaction()

   456 {

   457     if (!isOpen() || isOpenError())

   458         return false;

   459 

   460     char* err;

   461     int res = sqlite_exec(d->access, "ROLLBACK", 0, this, &err);

   462 

   463     if (res == SQLITE_OK)

   464         return true;

   465 

   466     setLastError(QSqlError(tr("Unable to rollback transaction"),

   467                            QString::fromAscii(err), QSqlError::TransactionError, res));

   468     sqlite_freemem(err);

   469     return false;

   470 }

   471 

   472 QStringList QSQLite2Driver::tables(QSql::TableType type) const

   473 {

   474     QStringList res;

   475     if (!isOpen())

   476         return res;

   477 

   478     QSqlQuery q(createResult());

   479     q.setForwardOnly(true);

   480     if ((type & QSql::Tables) && (type & QSql::Views))

   481         q.exec(QLatin1String("SELECT name FROM sqlite_master WHERE type='table' OR type='view'"));

   482     else if (type & QSql::Tables)

   483         q.exec(QLatin1String("SELECT name FROM sqlite_master WHERE type='table'"));

   484     else if (type & QSql::Views)

   485         q.exec(QLatin1String("SELECT name FROM sqlite_master WHERE type='view'"));

   486 

   487     if (q.isActive()) {

   488         while(q.next())

   489             res.append(q.value(0).toString());

   490     }

   491 

   492     if (type & QSql::SystemTables) {

   493         // there are no internal tables beside this one:

   494         res.append(QLatin1String("sqlite_master"));

   495     }

   496 

   497     return res;

   498 }

   499 

   500 QSqlIndex QSQLite2Driver::primaryIndex(const QString &tblname) const

   501 {

   502     QSqlRecord rec(record(tblname)); // expensive :(

   503 

   504     if (!isOpen())

   505         return QSqlIndex();

   506 

   507     QSqlQuery q(createResult());

   508     q.setForwardOnly(true);

   509     QString table = tblname;

   510     if (isIdentifierEscaped(table, QSqlDriver::TableName))

   511         table = stripDelimiters(table, QSqlDriver::TableName);

   512     // finrst find a UNIQUE INDEX

   513     q.exec(QLatin1String("PRAGMA index_list('") + table + QLatin1String("');"));

   514     QString indexname;

   515     while(q.next()) {

   516         if (q.value(2).toInt()==1) {

   517             indexname = q.value(1).toString();

   518             break;

   519         }

   520     }

   521     if (indexname.isEmpty())

   522         return QSqlIndex();

   523 

   524     q.exec(QLatin1String("PRAGMA index_info('") + indexname + QLatin1String("');"));

   525 

   526     QSqlIndex index(table, indexname);

   527     while(q.next()) {

   528         QString name = q.value(2).toString();

   529         QVariant::Type type = QVariant::Invalid;

   530         if (rec.contains(name))

   531             type = rec.field(name).type();

   532         index.append(QSqlField(name, type));

   533     }

   534     return index;

   535 }

   536 

   537 QSqlRecord QSQLite2Driver::record(const QString &tbl) const

   538 {

   539     if (!isOpen())

   540         return QSqlRecord();

   541     QString table = tbl;

   542     if (isIdentifierEscaped(tbl, QSqlDriver::TableName))

   543         table = stripDelimiters(table, QSqlDriver::TableName);

   544 

   545     QSqlQuery q(createResult());

   546     q.setForwardOnly(true);

   547     q.exec(QLatin1String("SELECT * FROM ") + tbl + QLatin1String(" LIMIT 1"));

   548     return q.record();

   549 }

   550 

   551 QVariant QSQLite2Driver::handle() const

   552 {

   553     return qVariantFromValue(d->access);

   554 }

   555 

   556 QString QSQLite2Driver::escapeIdentifier(const QString &identifier, IdentifierType /*type*/) const

   557 {

   558     QString res = identifier;

   559     if(!identifier.isEmpty() && !identifier.startsWith(QLatin1Char('"')) && !identifier.endsWith(QLatin1Char('"')) ) {

   560         res.replace(QLatin1Char('"'), QLatin1String("\"\""));

   561         res.prepend(QLatin1Char('"')).append(QLatin1Char('"'));

   562         res.replace(QLatin1Char('.'), QLatin1String("\".\""));

   563     }

   564     return res;

   565 }

   566 

   567 QT_END_NAMESPACE