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

     2 **

     3 ** Copyright (C) 2010 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     bool skippedStatus; // the status of the fetchNext() that's skipped

   114     bool skipRow; // skip the next fetchNext()?

   115     bool utf8;

   116     QSqlRecord rInf;

   117     QVector<QVariant> firstRow;

   118 };

   119 

   120 static const uint initial_cache_size = 128;

   121 

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

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

   124 {

   125 }

   126 

   127 void QSQLite2ResultPrivate::cleanup()

   128 {

   129     finalize();

   130     rInf.clear();

   131     currentTail = 0;

   132     currentMachine = 0;

   133     skippedStatus = false;

   134     skipRow = false;

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

   136     q->setActive(false);

   137     q->cleanup();

   138 }

   139 

   140 void QSQLite2ResultPrivate::finalize()

   141 {

   142     if (!currentMachine)

   143         return;

   144 

   145     char* err = 0;

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

   147     if (err) {

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

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

   150                                   QSqlError::StatementError, res));

   151         sqlite_freemem(err);

   152     }

   153     currentMachine = 0;

   154 }

   155 

   156 // called on first fetch

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

   158 {

   159     if (!cnames)

   160         return;

   161 

   162     rInf.clear();

   163     if (numCols <= 0)

   164         return;

   165     q->init(numCols);

   166 

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

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

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

   170         

   171         //remove quotations around the field name if any

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

   173         QLatin1Char quote('\"');

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

   175             fieldStr = fieldStr.mid(1);

   176             fieldStr.chop(1);

   177         }

   178         rInf.append(QSqlField(fieldStr,

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

   180     }

   181 }

   182 

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

   184 {

   185     // may be caching.

   186     const char **fvals;

   187     const char **cnames;

   188     int colNum;

   189     int res;

   190     int i;

   191 

   192     if (skipRow) {

   193         // already fetched

   194         Q_ASSERT(!initialFetch);

   195         skipRow = false;

   196         for(int i=0;i<firstRow.count(); i++)

   197             values[i] = firstRow[i];

   198         return skippedStatus;

   199     }

   200     skipRow = initialFetch;

   201 

   202     if (!currentMachine)

   203         return false;

   204 

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

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

   207         // sleep instead requesting result again immidiately.

   208 #if defined Q_WS_WIN32

   209         Sleep(1000);

   210 #else

   211         sleep(1);

   212 #endif

   213     }

   214 

   215     if(initialFetch) {

   216         firstRow.clear();

   217         firstRow.resize(colNum);

   218     }

   219     

   220     switch(res) {

   221     case SQLITE_ROW:

   222         // check to see if should fill out columns

   223         if (rInf.isEmpty())

   224             // must be first call.

   225             init(cnames, colNum);

   226         if (!fvals)

   227             return false;

   228         if (idx < 0 && !initialFetch)

   229             return true;

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

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

   232         return true;

   233     case SQLITE_DONE:

   234         if (rInf.isEmpty())

   235             // must be first call.

   236             init(cnames, colNum);

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

   238         return false;

   239     case SQLITE_ERROR:

   240     case SQLITE_MISUSE:

   241     default:

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

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

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

   245         return false;

   246     }

   247     return false;

   248 }

   249 

   250 QSQLite2Result::QSQLite2Result(const QSQLite2Driver* db)

   251 : QSqlCachedResult(db)

   252 {

   253     d = new QSQLite2ResultPrivate(this);

   254     d->access = db->d->access;

   255     d->utf8 = db->d->utf8;

   256 }

   257 

   258 QSQLite2Result::~QSQLite2Result()

   259 {

   260     d->cleanup();

   261     delete d;

   262 }

   263 

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

   265 {

   266     switch (id) {

   267     case QSqlResult::DetachFromResultSet:

   268         d->finalize();

   269         break;

   270     default:

   271         QSqlCachedResult::virtual_hook(id, data);

   272     }

   273 }

   274 

   275 /*

   276    Execute \a query.

   277 */

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

   279 {

   280     // this is where we build a query.

   281     if (!driver())

   282         return false;

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

   284         return false;

   285 

   286     d->cleanup();

   287 

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

   289     setSelect(false);

   290     char *err = 0;

   291     int res = sqlite_compile(d->access,

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

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

   294                                 &(d->currentTail),

   295                                 &(d->currentMachine),

   296                                 &err);

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

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

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

   300                      QSqlError::StatementError, res));

   301         sqlite_freemem(err);

   302     }

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

   304     if (!d->currentMachine) {

   305         setActive(false);

   306         return false;

   307     }

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

   309     // the structure of the result set

   310     d->skippedStatus = d->fetchNext(d->firstRow, 0, true);

   311     if (lastError().isValid()) {

   312         setSelect(false);

   313         setActive(false);

   314         return false;

   315     }

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

   317     setActive(true);

   318     return true;

   319 }

   320 

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

   322 {

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

   324 }

   325 

   326 int QSQLite2Result::size()

   327 {

   328     return -1;

   329 }

   330 

   331 int QSQLite2Result::numRowsAffected()

   332 {

   333     return sqlite_changes(d->access);

   334 }

   335 

   336 QSqlRecord QSQLite2Result::record() const

   337 {

   338     if (!isActive() || !isSelect())

   339         return QSqlRecord();

   340     return d->rInf;

   341 }

   342 

   343 QVariant QSQLite2Result::handle() const

   344 {

   345     return qVariantFromValue(d->currentMachine);

   346 }

   347 

   348 /////////////////////////////////////////////////////////

   349 

   350 QSQLite2Driver::QSQLite2Driver(QObject * parent)

   351     : QSqlDriver(parent)

   352 {

   353     d = new QSQLite2DriverPrivate();

   354 }

   355 

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

   357     : QSqlDriver(parent)

   358 {

   359     d = new QSQLite2DriverPrivate();

   360     d->access = connection;

   361     setOpen(true);

   362     setOpenError(false);

   363 }

   364 

   365 

   366 QSQLite2Driver::~QSQLite2Driver()

   367 {

   368     delete d;

   369 }

   370 

   371 bool QSQLite2Driver::hasFeature(DriverFeature f) const

   372 {

   373     switch (f) {

   374     case Transactions:

   375     case SimpleLocking:

   376         return true;

   377     case Unicode:

   378         return d->utf8;

   379     default:

   380         return false;

   381     }

   382 }

   383 

   384 /*

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

   386    just file names.

   387 */

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

   389 {

   390     if (isOpen())

   391         close();

   392 

   393     if (db.isEmpty())

   394         return false;

   395 

   396     char* err = 0;

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

   398     if (err) {

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

   400                      QSqlError::ConnectionError));

   401         sqlite_freemem(err);

   402         err = 0;

   403     }

   404 

   405     if (d->access) {

   406         setOpen(true);

   407         setOpenError(false);

   408         return true;

   409     }

   410     setOpenError(true);

   411     return false;

   412 }

   413 

   414 void QSQLite2Driver::close()

   415 {

   416     if (isOpen()) {

   417         sqlite_close(d->access);

   418         d->access = 0;

   419         setOpen(false);

   420         setOpenError(false);

   421     }

   422 }

   423 

   424 QSqlResult *QSQLite2Driver::createResult() const

   425 {

   426     return new QSQLite2Result(this);

   427 }

   428 

   429 bool QSQLite2Driver::beginTransaction()

   430 {

   431     if (!isOpen() || isOpenError())

   432         return false;

   433 

   434     char* err;

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

   436 

   437     if (res == SQLITE_OK)

   438         return true;

   439 

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

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

   442     sqlite_freemem(err);

   443     return false;

   444 }

   445 

   446 bool QSQLite2Driver::commitTransaction()

   447 {

   448     if (!isOpen() || isOpenError())

   449         return false;

   450 

   451     char* err;

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

   453 

   454     if (res == SQLITE_OK)

   455         return true;

   456 

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

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

   459     sqlite_freemem(err);

   460     return false;

   461 }

   462 

   463 bool QSQLite2Driver::rollbackTransaction()

   464 {

   465     if (!isOpen() || isOpenError())

   466         return false;

   467 

   468     char* err;

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

   470 

   471     if (res == SQLITE_OK)

   472         return true;

   473 

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

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

   476     sqlite_freemem(err);

   477     return false;

   478 }

   479 

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

   481 {

   482     QStringList res;

   483     if (!isOpen())

   484         return res;

   485 

   486     QSqlQuery q(createResult());

   487     q.setForwardOnly(true);

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

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

   490     else if (type & QSql::Tables)

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

   492     else if (type & QSql::Views)

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

   494 

   495     if (q.isActive()) {

   496         while(q.next())

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

   498     }

   499 

   500     if (type & QSql::SystemTables) {

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

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

   503     }

   504 

   505     return res;

   506 }

   507 

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

   509 {

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

   511 

   512     if (!isOpen())

   513         return QSqlIndex();

   514 

   515     QSqlQuery q(createResult());

   516     q.setForwardOnly(true);

   517     QString table = tblname;

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

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

   520     // finrst find a UNIQUE INDEX

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

   522     QString indexname;

   523     while(q.next()) {

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

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

   526             break;

   527         }

   528     }

   529     if (indexname.isEmpty())

   530         return QSqlIndex();

   531 

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

   533 

   534     QSqlIndex index(table, indexname);

   535     while(q.next()) {

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

   537         QVariant::Type type = QVariant::Invalid;

   538         if (rec.contains(name))

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

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

   541     }

   542     return index;

   543 }

   544 

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

   546 {

   547     if (!isOpen())

   548         return QSqlRecord();

   549     QString table = tbl;

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

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

   552 

   553     QSqlQuery q(createResult());

   554     q.setForwardOnly(true);

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

   556     return q.record();

   557 }

   558 

   559 QVariant QSQLite2Driver::handle() const

   560 {

   561     return qVariantFromValue(d->access);

   562 }

   563 

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

   565 {

   566     QString res = identifier;

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

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

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

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

   571     }

   572     return res;

   573 }

   574 

   575 QT_END_NAMESPACE