KDb

SqliteConnection.cpp
1/* This file is part of the KDE project
2 Copyright (C) 2003-2016 Jarosław Staniek <staniek@kde.org>
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
52SqliteConnection::~SqliteConnection()
53{
54 destroy();
55 delete d;
56}
57
58void 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 static const 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
100bool 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
127bool 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
134bool 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
240void 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
360
361bool SqliteConnection::findAndLoadExtension(const QString & name)
362{
363 QStringList pluginPaths;
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
379bool 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}
#define futureTr
Macros for marking future Qt tr() translations.
Definition KDbGlobal.h:160
Database specific connection data, e.g. host, port.
Generic options for a single connection. The options are accessible using key/value pairs....
void setCaption(const QByteArray &name, const QString &caption)
Sets caption for option name to caption.
void insert(const QByteArray &name, const QVariant &value, const QString &caption=QString())
Inserts option with a given name, value and caption.
Provides database connection, allowing queries and data modification.
virtual KDbEscapedString escapeString(const QString &str) const
KDbCursor * executeQuery(const KDbEscapedString &sql, KDbCursor::Options options=KDbCursor::Option::None)
KDbConnectionData data() const
bool deleteCursor(KDbCursor *cursor)
tristate resultExists(const KDbEscapedString &sql, QueryRecordOptions options=QueryRecordOption::Default)
KDbConnectionOptions * options()
Provides database cursor functionality.
Definition KDbCursor.h:69
virtual bool moveNext()
virtual QVariant value(int i)=0
bool moveFirst()
bool eof() const
Definition KDbCursor.h:147
Database driver's abstraction.
Definition KDbDriver.h:50
Specialized string for escaping.
Prepared statement interface for backend-dependent implementations.
KDbQuerySchema provides information about database query.
bool isError() const
Definition KDbResult.cpp:64
void setServerErrorCode(int errorCode)
Sets an implementation-specific error code of server-side operation.
Definition KDbResult.cpp:74
The KDbSqlResult class abstracts result of a raw SQL query preparation by KDbConnection::prepareSql()
Property property(const QByteArray &name) const
Definition KDbUtils.cpp:699
A single property.
Definition KDbUtils.h:446
bool drv_executeSql(const KDbEscapedString &sql) override
Executes query for a raw SQL statement sql without returning resulting records.
KDbSqlResult * drv_prepareSql(const KDbEscapedString &sql) override
Prepares query for a raw SQL statement sql with possibility of returning records.
KDbCursor * prepareQuery(const KDbEscapedString &sql, KDbCursor::Options options=KDbCursor::Option::None) override
tristate drv_containsTable(const QString &tableName) override
KDbPreparedStatementInterface * prepareStatementInternal() override
bool drv_createDatabase(const QString &dbName=QString()) override
bool drv_closeDatabase() override
QString serverResultName() const override
Implemented for KDbResultable.
bool drv_connect() override
SqliteConnection(KDbDriver *driver, const KDbConnectionData &connData, const KDbConnectionOptions &options)
bool drv_disconnect() override
bool drv_getServerVersion(KDbServerVersionInfo *version) override
bool drv_getDatabasesList(QStringList *list) override
bool drv_useDatabase(const QString &dbName=QString(), bool *cancelled=nullptr, KDbMessageHandler *msgHandler=nullptr) override
bool drv_dropDatabase(const QString &dbName=QString()) override
3-state logical type with three values: true, false and cancelled and convenient operators.
KDB_EXPORT QStringList libraryPaths()
Definition KDb.cpp:2043
QString path(const QString &relativePath)
KIOCORE_EXPORT QStringList list(const QString &fileClass)
QCA_EXPORT QStringList pluginPaths()
const char * constData() const const
QString fromNativeSeparators(const QString &pathName)
QString toNativeSeparators(const QString &pathName)
bool exists() const const
bool remove()
void append(QList< T > &&value)
void clear()
QRegularExpressionMatch match(QStringView subjectView, qsizetype offset, MatchType matchType, MatchOptions matchOptions) const const
QByteArray toUtf8() const const
QString toString() const const
QStringList toStringList() const const
This file is part of the KDE documentation.
Documentation copyright © 1996-2024 The KDE developers.
Generated on Tue Mar 26 2024 11:20:59 by doxygen 1.10.0 written by Dimitri van Heesch, © 1997-2006

KDE's Doxygen guidelines are available online.