KDb

SqliteConnection.cpp
1 /* This file is part of the KDE project
2  Copyright (C) 2003-2016 JarosÅ‚aw Staniek <[email protected]>
3 
4  This program is free software; you can redistribute it and/or
5  modify it under the terms of the GNU Library General Public
6  License as published by the Free Software Foundation; either
7  version 2 of the License, or (at your option) any later version.
8 
9  This program is distributed in the hope that it will be useful,
10  but WITHOUT ANY WARRANTY; without even the implied warranty of
11  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
12  Library General Public License for more details.
13 
14  You should have received a copy of the GNU Library General Public License
15  along with this program; see the file COPYING. If not, write to
16  the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
17  * Boston, MA 02110-1301, USA.
18 */
19 
20 #include "SqliteConnection.h"
21 #include "SqliteConnection_p.h"
22 #include "SqliteCursor.h"
23 #include "SqlitePreparedStatement.h"
24 #include "SqliteFunctions.h"
25 #include "sqlite_debug.h"
26 
27 #include <sqlite3.h>
28 
29 #include "KDbConnectionData.h"
30 #include "KDbConnectionOptions.h"
31 #include "KDbUtils.h"
32 #include "KDbUtils_p.h"
33 #include "KDbVersionInfo.h"
34 
35 #include <QFile>
36 #include <QDir>
37 #include <QRegularExpression>
38 
40  const KDbConnectionOptions &options)
41  : KDbConnection(driver, connData, options)
42  , d(new SqliteConnectionInternal(this))
43 {
44  QByteArray propertyName = "extraSqliteExtensionPaths";
45  KDbUtils::Property extraSqliteExtensionPathsProperty = this->options()->property(propertyName);
46  if (extraSqliteExtensionPathsProperty.isNull()) {
47  this->options()->insert(propertyName, QStringList());
48  }
49  this->options()->setCaption(propertyName, SqliteConnection::tr("Extra paths for SQLite plugins"));
50 }
51 
52 SqliteConnection::~SqliteConnection()
53 {
54  destroy();
55  delete d;
56 }
57 
58 void SqliteConnection::storeResult()
59 {
60  d->storeResult(&m_result);
61 }
62 
64 {
65  return true;
66 }
67 
69 {
70  version->setString(QLatin1String(SQLITE_VERSION)); //defined in sqlite3.h
71  QRegularExpression re(QLatin1String("^(\\d+)\\.(\\d+)\\.(\\d+)$"));
72  QRegularExpressionMatch match = re.match(version->string());
73  if (match.hasMatch()) {
74  version->setMajor(match.captured(1).toInt());
75  version->setMinor(match.captured(2).toInt());
76  version->setRelease(match.captured(3).toInt());
77  }
78  return true;
79 }
80 
82 {
83  return true;
84 }
85 
87 {
88  //this is one-db-per-file database
89  list->append(data().databaseName());
90  return true;
91 }
92 
94 {
95  return resultExists(KDbEscapedString("SELECT name FROM sqlite_master WHERE type='table' AND name LIKE %1")
96  .arg(escapeString(tableName)));
97 }
98 
99 #if 0 // TODO
100 bool SqliteConnection::drv_getTablesList(QStringList* list)
101 {
102  KDbCursor *cursor;
103  if (!(cursor = executeQuery(KDbEscapedString("SELECT name FROM sqlite_master WHERE type='table'")))) {
104  sqliteWarning() << "!executeQuery()";
105  return false;
106  }
107  list->clear();
108  cursor->moveFirst();
109  while (!cursor->eof() && !cursor->result().isError()) {
110  *list += cursor->value(0).toString();
111  cursor->moveNext();
112  }
113  if (cursor->result().isError()) {
114  deleteCursor(cursor);
115  return false;
116  }
117  return deleteCursor(cursor);
118 }
119 #endif
120 
122 {
123  Q_UNUSED(dbName);
124  return drv_useDatabaseInternal(nullptr, nullptr, true/*create if missing*/);
125 }
126 
127 bool SqliteConnection::drv_useDatabase(const QString &dbName, bool *cancelled,
128  KDbMessageHandler* msgHandler)
129 {
130  Q_UNUSED(dbName);
131  return drv_useDatabaseInternal(cancelled, msgHandler, false/*do not create if missing*/);
132 }
133 
134 bool SqliteConnection::drv_useDatabaseInternal(bool *cancelled,
135  KDbMessageHandler* msgHandler, bool createIfMissing)
136 {
137 //! @todo add option (command line or in kdbrc?)
138 //! @todo int exclusiveFlag = KDbConnection::isReadOnly() ? SQLITE_OPEN_READONLY : SQLITE_OPEN_WRITE_LOCKED; // <-- shared read + (if !r/o): exclusive write
139  int openFlags = 0;
140  if (options()->isReadOnly()) {
141  openFlags |= SQLITE_OPEN_READONLY;
142  }
143  else {
144  openFlags |= SQLITE_OPEN_READWRITE;
145  if (createIfMissing) {
146  openFlags |= SQLITE_OPEN_CREATE;
147  }
148  }
149 
150 //! @todo add option
151 // int allowReadonly = 1;
152 // const bool wasReadOnly = KDbConnection::isReadOnly();
153 
154  //sqliteDebug() << data().databaseName();
155  int res = sqlite3_open_v2(
156  /* unicode expected since SQLite 3.1 */
157  QDir::toNativeSeparators(data().databaseName()).toUtf8().constData(),
158  &d->data,
159  openFlags, /*exclusiveFlag,
160  allowReadonly *//* If 1 and locking fails, try opening in read-only mode */
161  nullptr
162  );
163  if (res != SQLITE_OK) {
164  m_result.setServerErrorCode(res);
165  }
166  storeResult();
167 
168  if (!m_result.isError()) {
169  // Set the secure-delete on, so SQLite overwrites deleted content with zeros.
170  // The default setting is determined by the SQLITE_SECURE_DELETE compile-time option but we overwrite it here.
171  // Works with 3.6.23. Earlier version just ignore this pragma.
172  // See https://www.sqlite.org/pragma.html#pragma_secure_delete
173 //! @todo add connection flags to the driver and global setting to control the "secure delete" pragma
174  if (!drv_executeSql(KDbEscapedString("PRAGMA secure_delete = on"))) {
175  drv_closeDatabaseSilently();
176  return false;
177  }
178  // Load ICU extension for unicode collations
179  if (!findAndLoadExtension(QLatin1String("kdb_sqlite_icu"))) {
180  drv_closeDatabaseSilently();
181  return false;
182  }
183  // load ROOT collation for use as default collation
184  if (!drv_executeSql(KDbEscapedString("SELECT icu_load_collation('', '')"))) {
185  drv_closeDatabaseSilently();
186  return false;
187  }
188  if (!createCustomSQLiteFunctions(d->data)) {
189  drv_closeDatabaseSilently();
190  return false;
191  }
192  }
193 
194 //! @todo check exclusive status
195  Q_UNUSED(cancelled);
196  Q_UNUSED(msgHandler);
197 //! @todo removed in kdb - reenable?
198 /*
199  if (d->res == SQLITE_OK && cancelled && !wasReadOnly && allowReadonly && isReadOnly()) {
200  //opened as read only, ask
201  if (KDbMessageHandler::Continue !=
202  askQuestion(
203  KDbMessageHandler::WarningContinueCancel,
204  futureTr("Do you want to open file \"%1\" as read-only?\n\n"
205  "The file is probably already open on this or another computer. "
206  "Could not gain exclusive access for writing the file.")
207  .arg(QDir::fromNativeSeparators(data()->databaseName())),
208  futureTr("Opening As Read-Only"),
209  KDbMessageHandler::Continue,
210  KDbMessageHandler::KDbGuiItem()
211  .setProperty("text", futureTr("Open As Read-Only"))
212  .setProperty("icon", "document-open"),
213  KDbMessageHandler::KDbGuiItem(),
214  "askBeforeOpeningFileReadOnly",
215  KDbMessageHandler::Notify,
216  msgHandler)
217  {
218  clearError();
219  if (!drv_closeDatabase())
220  return false;
221  *cancelled = true;
222  return false;
223  }
224  }
225 
226  if (d->res == SQLITE_CANTOPEN_WITH_LOCKED_READWRITE) {
227  setError(ERR_ACCESS_RIGHTS,
228  futureTr("The file is probably already open on this or another computer.\n\n"
229  "Could not gain exclusive access for reading and writing the file. "
230  "Check the file's permissions and whether it is already opened and locked by another application."));
231  } else if (d->res == SQLITE_CANTOPEN_WITH_LOCKED_WRITE) {
232  setError(ERR_ACCESS_RIGHTS,
233  futureTr("The file is probably already open on this or another computer.\n\n"
234  "Could not gain exclusive access for writing the file. "
235  "Check the file's permissions and whether it is already opened and locked by another application."));
236  }*/
237  return res == SQLITE_OK;
238 }
239 
240 void SqliteConnection::drv_closeDatabaseSilently()
241 {
242  KDbResult result = this->result(); // save
244  m_result = result;
245 }
246 
248 {
249  if (!d->data)
250  return false;
251 
252  const int res = sqlite3_close(d->data);
253  if (SQLITE_OK == res) {
254  d->data = nullptr;
255  return true;
256  }
257  if (SQLITE_BUSY == res) {
258 #if 0 //this is ANNOYING, needs fixing (by closing cursors or waiting)
259  setError(ERR_CLOSE_FAILED, futureTr("Could not close busy database."));
260 #else
261  return true;
262 #endif
263  }
264  return false;
265 }
266 
268 {
269  Q_UNUSED(dbName); // Each database is one single SQLite file.
270  const QString filename = data().databaseName();
271  if (QFile::exists(filename) && !QFile::remove(filename)) {
272  m_result = KDbResult(ERR_ACCESS_RIGHTS,
273  SqliteConnection::tr("Could not delete file \"%1\". "
274  "Check the file's permissions and whether it is already "
275  "opened and locked by another application.")
276  .arg(QDir::fromNativeSeparators(filename)));
277  return false;
278  }
279  return true;
280 }
281 
283 {
284  return new SqliteCursor(this, sql, options);
285 }
286 
288 {
289  return new SqliteCursor(this, query, options);
290 }
291 
293 {
294 #ifdef KDB_DEBUG_GUI
295  KDb::debugGUI(QLatin1String("PrepareSQL (SQLite): ") + sql.toString());
296 #endif
297 
298  sqlite3_stmt *prepared_st = nullptr;
299  const int res = sqlite3_prepare(
300  d->data, /* Database handle */
301  sql.constData(), /* SQL statement, UTF-8 encoded */
302  sql.length(), /* Length of zSql in bytes. */
303  &prepared_st, /* OUT: Statement handle */
304  nullptr/*const char **pzTail*/ /* OUT: Pointer to unused portion of zSql */
305  );
306  if (res != SQLITE_OK) {
307  m_result.setServerErrorCode(res);
308  storeResult();
309 #ifdef KDB_DEBUG_GUI
310  KDb::debugGUI(QLatin1String(" Failure"));
311 #endif
312  return nullptr;
313  }
314 
315 #ifdef KDB_DEBUG_GUI
316  KDb::debugGUI(QLatin1String(" Success"));
317 #endif
318  return new SqliteSqlResult(this, prepared_st);
319 }
320 
322 {
323 #ifdef KDB_DEBUG_GUI
324  KDb::debugGUI(QLatin1String("ExecuteSQL (SQLite): ") + sql.toString());
325 #endif
326 
327  char *errmsg_p = nullptr;
328  const int res = sqlite3_exec(
329  d->data,
330  sql.constData(),
331  nullptr/*callback*/,
332  nullptr,
333  &errmsg_p);
334  if (res != SQLITE_OK) {
335  m_result.setServerErrorCode(res);
336  }
337  if (errmsg_p) {
338  clearResult();
339  m_result.setServerMessage(QLatin1String(errmsg_p));
340  sqlite3_free(errmsg_p);
341  } else {
342  storeResult();
343  }
344 
345 #ifdef KDB_DEBUG_GUI
346  KDb::debugGUI(QLatin1String( res == SQLITE_OK ? " Success" : " Failure"));
347 #endif
348  return res == SQLITE_OK;
349 }
350 
352 {
353  return SqliteConnectionInternal::serverResultName(m_result.serverErrorCode());
354 }
355 
357 {
358  return new SqlitePreparedStatement(d);
359 }
360 
361 bool SqliteConnection::findAndLoadExtension(const QString & name)
362 {
364  foreach (const QString& path, KDb::libraryPaths()) {
365  pluginPaths += path + QLatin1String("/sqlite3");
366  }
367  pluginPaths += options()->property("extraSqliteExtensionPaths").value().toStringList();
368  foreach (const QString& path, pluginPaths) {
369  if (loadExtension(path + QLatin1Char('/') + name + QLatin1String(KDB_SHARED_LIB_EXTENSION))) {
370  return true;
371  }
372  }
373  clearResult();
374  m_result = KDbResult(ERR_CANNOT_LOAD_OBJECT,
375  SqliteConnection::tr("Could not load SQLite plugin \"%1\".").arg(name));
376  return false;
377 }
378 
379 bool SqliteConnection::loadExtension(const QString& path)
380 {
381  bool tempEnable = false;
382  clearResult();
383  QFileInfo fileInfo(path);
384  if (!fileInfo.exists()) {
385  m_result = KDbResult(ERR_OBJECT_NOT_FOUND,
386  SqliteConnection::tr("Could not find SQLite plugin file \"%1\".").arg(path));
387  //sqliteWarning() << "Could not find SQLite extension";
388  return false;
389  }
390  if (!d->extensionsLoadingEnabled()) {
391  tempEnable = true;
392  d->setExtensionsLoadingEnabled(true);
393  }
394  char *errmsg_p = nullptr;
395  int res = sqlite3_load_extension(d->data, QDir::toNativeSeparators(path).toUtf8().constData(),
396  nullptr, &errmsg_p);
397  bool ok = res == SQLITE_OK;
398  if (!ok) {
399  m_result.setServerErrorCode(res);
400  m_result = KDbResult(ERR_CANNOT_LOAD_OBJECT,
401  SqliteConnection::tr("Could not load SQLite extension \"%1\".").arg(path));
402  sqliteWarning() << "SqliteConnection::loadExtension(): Could not load SQLite extension"
403  << path << ":" << errmsg_p;
404  if (errmsg_p) {
405  m_result.setServerMessage(QLatin1String(errmsg_p));
406  sqlite3_free(errmsg_p);
407  }
408  }
409  if (tempEnable) {
410  d->setExtensionsLoadingEnabled(false);
411  }
412  return ok;
413 }
void append(const T &value)
bool deleteCursor(KDbCursor *cursor)
Provides database cursor functionality.
Definition: KDbCursor.h:68
virtual bool moveNext()
Definition: KDbCursor.cpp:351
The KDbSqlResult class abstracts result of a raw SQL query preparation by KDbConnection::prepareSql()
Definition: KDbSqlResult.h:44
bool remove()
bool moveFirst()
Definition: KDbCursor.cpp:285
Specialized string for escaping.
bool drv_createDatabase(const QString &dbName=QString()) override
Database driver's abstraction.
Definition: KDbDriver.h:49
KIOFILEWIDGETS_EXPORT QStringList list(const QString &fileClass)
bool exists() const const
KDB_EXPORT QStringList libraryPaths()
Definition: KDb.cpp:2051
bool eof() const
Definition: KDbCursor.h:147
bool drv_executeSql(const KDbEscapedString &sql) override
Executes query for a raw SQL statement sql without returning resulting records.
bool drv_getDatabasesList(QStringList *list) override
KDbConnectionData data() const
QCA_EXPORT QStringList pluginPaths()
bool drv_disconnect() override
A single property.
Definition: KDbUtils.h:446
QString toNativeSeparators(const QString &pathName)
QByteArray toUtf8() const const
Property property(const QByteArray &name) const
Definition: KDbUtils.cpp:698
bool drv_connect() override
QString fromNativeSeparators(const QString &pathName)
bool isError() const
Definition: KDbResult.cpp:64
3-state logical type with three values: true, false and cancelled and convenient operators.
Definition: KDbTristate.h:100
KDbCursor * prepareQuery(const KDbEscapedString &sql, KDbCursor::Options options=KDbCursor::Option::None) override
KDbCursor * executeQuery(const KDbEscapedString &sql, KDbCursor::Options options=KDbCursor::Option::None)
void insert(const QByteArray &name, const QVariant &value, const QString &caption=QString())
Inserts option with a given name, value and caption.
QRegularExpressionMatch match(const QString &subject, int offset, QRegularExpression::MatchType matchType, QRegularExpression::MatchOptions matchOptions) const const
tristate resultExists(const KDbEscapedString &sql, QueryRecordOptions options=QueryRecordOption::Default)
bool drv_closeDatabase() override
virtual KDbEscapedString escapeString(const QString &str) const
#define futureTr
Macros for marking future Qt tr() translations.
Definition: KDbGlobal.h:160
bool drv_useDatabase(const QString &dbName=QString(), bool *cancelled=nullptr, KDbMessageHandler *msgHandler=nullptr) override
void setCaption(const QByteArray &name, const QString &caption)
Sets caption for option name to caption.
Prepared statement interface for backend-dependent implementations.
KDbQuerySchema provides information about database query.
const char * constData() const const
unsigned int version()
Definition: KDb.cpp:336
KDbPreparedStatementInterface * prepareStatementInternal() override
tristate drv_containsTable(const QString &tableName) override
virtual QVariant value(int i)=0
KDbConnectionOptions * options()
Database specific connection data, e.g. host, port.
QString serverResultName() const override
Implemented for KDbResultable.
bool drv_getServerVersion(KDbServerVersionInfo *version) override
void clear()
Generic options for a single connection. The options are accessible using key/value pairs....
SqliteConnection(KDbDriver *driver, const KDbConnectionData &connData, const KDbConnectionOptions &options)
KDbSqlResult * drv_prepareSql(const KDbEscapedString &sql) override
Prepares query for a raw SQL statement sql with possibility of returning records.
QStringList toStringList() const const
Provides database connection, allowing queries and data modification.
Definition: KDbConnection.h:51
bool drv_dropDatabase(const QString &dbName=QString()) override
void setServerErrorCode(int errorCode)
Sets an implementation-specific error code of server-side operation.
Definition: KDbResult.cpp:74
QString toString() const const
This file is part of the KDE documentation.
Documentation copyright © 1996-2022 The KDE developers.
Generated on Sat Jun 25 2022 06:21:34 by doxygen 1.8.17 written by Dimitri van Heesch, © 1997-2006

KDE's Doxygen guidelines are available online.