diff --git a/autotests/parser/SqlParserTest.cpp b/autotests/parser/SqlParserTest.cpp index 59133a12..192b0ccb 100644 --- a/autotests/parser/SqlParserTest.cpp +++ b/autotests/parser/SqlParserTest.cpp @@ -1,368 +1,368 @@ /* This file is part of the KDE project Copyright (C) 2012 Jarosław Staniek This library is free software; you can redistribute it and/or modify it under the terms of the GNU Library General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Library General Public License for more details. You should have received a copy of the GNU Library General Public License along with this library; see the file COPYING.LIB. If not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, * Boston, MA 02110-1301, USA. */ #include "SqlParserTest.h" #include #include #include #include #include #include Q_DECLARE_METATYPE(KDbEscapedString) QTEST_GUILESS_MAIN(SqlParserTest) void SqlParserTest::initTestCase() { QString dir(QFile::decodeName(OUTPUT_DIR)); QString fname("errors.txt"); m_errorFile.setFileName(dir + QDir::separator() + fname); QVERIFY2(m_errorFile.open(QFile::WriteOnly | QFile::Text), qPrintable(QString("Cannot open %1 file").arg(m_errorFile.fileName()))); m_errorStream.setDevice(&m_errorFile); } bool SqlParserTest::openDatabase(const QString &path) { KDbConnectionData cdata; cdata.setDatabaseName(path); if (!m_utils.testConnect(cdata) || !m_utils.connection) { qDebug() << m_utils.driver->result(); return false; } m_parser.reset(new KDbParser(m_utils.connection.data())); #if 0 if (m_conn->databaseExists(dbName)) { if (!m_conn->dropDatabase(dbName)) { m_conn->disconnect(); return false; } qDebug() << "Database" << dbName << "dropped."; } if (!m_conn->createDatabase(dbName)) { qDebug() << m_conn->result(); m_conn->disconnect(); return false; } #endif if (!m_utils.testUse() || !m_utils.connection->isDatabaseUsed()) { qDebug() << m_utils.connection->result(); bool result = m_utils.testDisconnect(); Q_UNUSED(result); return false; } return true; } KDbEscapedString SqlParserTest::parse(const KDbEscapedString& sql, bool *ok) { KDbParser *parser = m_parser.data(); *ok = parser->parse(sql); if (!*ok) { //qDebug() << parser->error(); return KDbEscapedString(); } QScopedPointer q(parser->query()); if (!q) { //qDebug() << parser->error(); *ok = false; return KDbEscapedString(); } //qDebug() << *q.data(); QList params; - KDbNativeStatementBuilder builder(m_utils.connection.data()); + KDbNativeStatementBuilder builder(m_utils.connection.data(), KDb::DriverEscaping); KDbEscapedString querySql; *ok = builder.generateSelectStatement(&querySql, q.data(), params); //qDebug() << querySql; return querySql; } static void eatComment(QString* string) { if (!string->startsWith("--")) { return; } int i = 0; for (; i < string->length() && string->at(i) == '-'; ++i) ; QString result = string->mid(i).trimmed(); *string = result; } static void eatEndLines(QString* string) { if (!string->endsWith("--")) { return; } int i = string->length() - 1; for (; i >= 0 && string->at(i) == '-'; --i) ; *string = string->left(i+1).trimmed(); } static void eatEndComment(QString* string) { int pos = string->indexOf("; --"); if (pos == -1) { return; } string->truncate(pos); *string = string->trimmed() + ';'; } void SqlParserTest::testParse_data() { QTest::addColumn("fname"); QTest::addColumn("lineNum"); QTest::addColumn("sql"); QTest::addColumn("expectError"); QString dir(QFile::decodeName(FILES_DATA_DIR)); QString fname("statements.txt"); QFile input(dir + QDir::separator() + fname); bool ok = input.open(QFile::ReadOnly | QFile::Text); QVERIFY2(ok, qPrintable(QString("Could not open data file %1").arg(input.fileName()))); QTextStream in(&input); QString category; QString testName; bool expectError = false; int lineNum = 1; QString dbPath; bool clearTestName = false; for (; !in.atEnd(); ++lineNum) { QString line(in.readLine()); if (line.startsWith("--")) { // comment eatComment(&line); eatEndLines(&line); if (line.startsWith("TODO:")) { continue; } else if (line.startsWith("CATEGORY: ")) { if (clearTestName) { expectError = false; clearTestName = false; testName.clear(); } category = line.mid(QString("CATEGORY: ").length()).trimmed(); //qDebug() << "CATEGORY:" << category; } else if (line == "QUIT") { break; } else if (line.startsWith("SQLITEFILE: ")) { if (clearTestName) { expectError = false; clearTestName = false; testName.clear(); } ok = dbPath.isEmpty(); QVERIFY2(ok, qPrintable(QString("Error at line %1: SQLite was file already specified (%2)") .arg(lineNum).arg(dbPath))); dbPath = line.mid(QString("SQLITEFILE: ").length()).trimmed(); dbPath = dir + QDir::separator() + dbPath; ok = openDatabase(dbPath); QVERIFY2(ok, qPrintable(QString("Error at line %1: Could not open SQLite file %2") .arg(lineNum).arg(dbPath))); } else if (line.startsWith("ERROR: ")) { if (clearTestName) { clearTestName = false; testName.clear(); } expectError = true; testName = line.mid(QString("ERROR: ").length()).trimmed(); } else { if (clearTestName) { expectError = false; clearTestName = false; testName.clear(); } if (!testName.isEmpty()) { testName.append(" "); } testName.append(line); } } else { eatEndComment(&line); KDbEscapedString sql(line.trimmed()); clearTestName = true; if (sql.isEmpty()) { expectError = false; continue; } ok = !dbPath.isEmpty(); QVERIFY2(ok, qPrintable(QString("Error at line %1: SQLite was file not specified, " "could not execute statement").arg(lineNum))); QTest::newRow(qPrintable(QString("File: %1:%2; Category: \"%3\"; Test: \"%4\"%5") .arg(fname).arg(lineNum).arg(category, testName, expectError ? "; Error expected" : ""))) << fname << lineNum << sql << expectError; } } input.close(); } void SqlParserTest::testParse() { QFETCH(QString, fname); QFETCH(int, lineNum); QFETCH(KDbEscapedString, sql); QFETCH(bool, expectError); QString message; if (!sql.endsWith(';')) { message = QString("%1:%2: Missing ';' at the end of line").arg(fname).arg(lineNum); m_errorStream << fname << ':' << lineNum << ' ' << message << endl; QVERIFY2(sql.endsWith(';'), qPrintable(message)); } sql.chop(1); //qDebug() << "SQL:" << sql.toString() << expectError; bool ok; KDbEscapedString result = parse(sql, &ok); KDbParser *parser = m_parser.data(); if (ok) { // sucess, so error cannot be expected ok = !expectError; message = QString("Unexpected success in SQL statement: \"%1\"; Result: %2") .arg(sql.toString(), result.toString()); if (ok) { qDebug() << "Result:" << result.toString(); } else { m_errorStream << fname << ':' << lineNum << ' ' << message << endl; if (parser->query()) { const KDbConnectionAndQuerySchema connQuery(parser->connection(), *parser->query()); qDebug() << connQuery; m_errorStream << KDbUtils::debugString(connQuery) << endl; } } QVERIFY2(ok, qPrintable(message)); } else { // failure, so error should be expected ok = expectError; message = QString("%1; Failed SQL Statement:\n\"%2\"\n %3^\n") .arg(KDbUtils::debugString(parser->error()), sql.toString(), QString(parser->error().position() - 1, QChar(' '))); if (ok) { qDebug() << parser->error(); } else { m_errorStream << fname << ':' << lineNum << message << endl; } QVERIFY2(ok, qPrintable(message)); } } void SqlParserTest::testTokens() { KDbToken t = KDbToken::LEFT; qDebug() << t << t.toChar() << t.value() << t.isValid(); t = '+'; qDebug() << t << t.toChar() << t.value() << t.isValid(); t = KDbToken(); qDebug() << t << t.toChar() << t.value() << t.isValid(); QCOMPARE(KDbToken::SQL_TYPE.value(), 258); QCOMPARE(KDbToken::AS.value(), 259); QCOMPARE(KDbToken::AS_EMPTY.value(), 260); QCOMPARE(KDbToken::ASC.value(), 261); QCOMPARE(KDbToken::AUTO_INCREMENT.value(), 262); QCOMPARE(KDbToken::BIT.value(), 263); QCOMPARE(KDbToken::BITWISE_SHIFT_LEFT.value(), 264); QCOMPARE(KDbToken::BITWISE_SHIFT_RIGHT.value(), 265); QCOMPARE(KDbToken::BY.value(), 266); QCOMPARE(KDbToken::CHARACTER_STRING_LITERAL.value(), 267); QCOMPARE(KDbToken::CONCATENATION.value(), 268); QCOMPARE(KDbToken::CREATE.value(), 269); QCOMPARE(KDbToken::DESC.value(), 270); QCOMPARE(KDbToken::DISTINCT.value(), 271); QCOMPARE(KDbToken::DOUBLE_QUOTED_STRING.value(), 272); QCOMPARE(KDbToken::FROM.value(), 273); QCOMPARE(KDbToken::JOIN.value(), 274); QCOMPARE(KDbToken::KEY.value(), 275); QCOMPARE(KDbToken::LEFT.value(), 276); QCOMPARE(KDbToken::LESS_OR_EQUAL.value(), 277); QCOMPARE(KDbToken::GREATER_OR_EQUAL.value(), 278); QCOMPARE(KDbToken::SQL_NULL.value(), 279); QCOMPARE(KDbToken::SQL_IS.value(), 280); QCOMPARE(KDbToken::SQL_IS_NULL.value(), 281); QCOMPARE(KDbToken::SQL_IS_NOT_NULL.value(), 282); QCOMPARE(KDbToken::ORDER.value(), 283); QCOMPARE(KDbToken::PRIMARY.value(), 284); QCOMPARE(KDbToken::SELECT.value(), 285); QCOMPARE(KDbToken::INTEGER_CONST.value(), 286); QCOMPARE(KDbToken::REAL_CONST.value(), 287); QCOMPARE(KDbToken::RIGHT.value(), 288); QCOMPARE(KDbToken::SQL_ON.value(), 289); QCOMPARE(KDbToken::DATE_CONST.value(), 290); QCOMPARE(KDbToken::DATETIME_CONST.value(), 291); QCOMPARE(KDbToken::TIME_CONST.value(), 292); QCOMPARE(KDbToken::TABLE.value(), 293); QCOMPARE(KDbToken::IDENTIFIER.value(), 294); QCOMPARE(KDbToken::IDENTIFIER_DOT_ASTERISK.value(), 295); QCOMPARE(KDbToken::QUERY_PARAMETER.value(), 296); QCOMPARE(KDbToken::VARCHAR.value(), 297); QCOMPARE(KDbToken::WHERE.value(), 298); QCOMPARE(KDbToken::SQL.value(), 299); QCOMPARE(KDbToken::SQL_TRUE.value(), 300); QCOMPARE(KDbToken::SQL_FALSE.value(), 301); QCOMPARE(KDbToken::UNION.value(), 302); QCOMPARE(KDbToken::SCAN_ERROR.value(), 303); QCOMPARE(KDbToken::AND.value(), 304); QCOMPARE(KDbToken::BETWEEN.value(), 305); QCOMPARE(KDbToken::NOT_BETWEEN.value(), 306); QCOMPARE(KDbToken::EXCEPT.value(), 307); QCOMPARE(KDbToken::SQL_IN.value(), 308); QCOMPARE(KDbToken::INTERSECT.value(), 309); QCOMPARE(KDbToken::LIKE.value(), 310); QCOMPARE(KDbToken::ILIKE.value(), 311); QCOMPARE(KDbToken::NOT_LIKE.value(), 312); QCOMPARE(KDbToken::NOT.value(), 313); QCOMPARE(KDbToken::NOT_EQUAL.value(), 314); QCOMPARE(KDbToken::NOT_EQUAL2.value(), 315); QCOMPARE(KDbToken::OR.value(), 316); QCOMPARE(KDbToken::SIMILAR_TO.value(), 317); QCOMPARE(KDbToken::NOT_SIMILAR_TO.value(), 318); QCOMPARE(KDbToken::XOR.value(), 319); QCOMPARE(KDbToken::UMINUS.value(), 320); //! @todo add extra tokens: BETWEEN_AND, NOT_BETWEEN_AND } void SqlParserTest::cleanupTestCase() { QVERIFY(m_utils.testDisconnect()); m_errorFile.close(); #if 0 if (!m_conn->dropDatabase()) { qDebug() << m_conn->result(); } qDebug() << "Database" << m_conn->data().databaseName() << "dropped."; #endif } diff --git a/src/KDbConnection.cpp b/src/KDbConnection.cpp index 4b29622a..f1faae5b 100644 --- a/src/KDbConnection.cpp +++ b/src/KDbConnection.cpp @@ -1,3489 +1,3489 @@ /* This file is part of the KDE project Copyright (C) 2003-2017 Jarosław Staniek This program is free software; you can redistribute it and/or modify it under the terms of the GNU Library General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Library General Public License for more details. You should have received a copy of the GNU Library General Public License along with this program; see the file COPYING. If not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, * Boston, MA 02110-1301, USA. */ #include "KDbConnection.h" #include "KDbConnection_p.h" #include "KDbCursor.h" #include "KDbDriverBehavior.h" #include "KDbDriverMetaData.h" #include "KDbDriver_p.h" #include "KDbLookupFieldSchema.h" #include "KDbNativeStatementBuilder.h" #include "KDbQuerySchema.h" #include "KDbQuerySchema_p.h" #include "KDbRecordData.h" #include "KDbRecordEditBuffer.h" #include "KDbRelationship.h" #include "KDbSqlRecord.h" #include "KDbSqlResult.h" #include "KDbTableOrQuerySchema.h" #include "KDbTableSchemaChangeListener.h" #include "KDbTransactionData.h" #include "KDbTransactionGuard.h" #include "kdb_debug.h" #include #include #include /*! Version number of extended table schema. List of changes: * 2: (Kexi 2.5.0) Added maxLengthIsDefault property (type: bool, if true, KDbField::maxLengthStrategy() == KDbField::DefaultMaxLength) * 1: (Kexi 1.x) Initial version */ #define KDB_EXTENDED_TABLE_SCHEMA_VERSION 2 KDbConnectionInternal::KDbConnectionInternal(KDbConnection *conn) : connection(conn) { } class CursorDeleter { public: explicit CursorDeleter(KDbCursor *cursor) { delete cursor; } }; //================================================ class Q_DECL_HIDDEN KDbConnectionOptions::Private { public: Private() : connection(nullptr) {} Private(const Private &other) { copy(other); } #define KDbConnectionOptionsPrivateArgs(o) std::tie(o.connection) void copy(const Private &other) { KDbConnectionOptionsPrivateArgs((*this)) = KDbConnectionOptionsPrivateArgs(other); } bool operator==(const Private &other) const { return KDbConnectionOptionsPrivateArgs((*this)) == KDbConnectionOptionsPrivateArgs(other); } KDbConnection *connection; }; KDbConnectionOptions::KDbConnectionOptions() : d(new Private) { KDbUtils::PropertySet::insert("readOnly", false, tr("Read only", "Read only connection")); } KDbConnectionOptions::KDbConnectionOptions(const KDbConnectionOptions &other) : KDbUtils::PropertySet(other) , d(new Private(*other.d)) { } KDbConnectionOptions::~KDbConnectionOptions() { delete d; } KDbConnectionOptions& KDbConnectionOptions::operator=(const KDbConnectionOptions &other) { if (this != &other) { KDbUtils::PropertySet::operator=(other); d->copy(*other.d); } return *this; } bool KDbConnectionOptions::operator==(const KDbConnectionOptions &other) const { return KDbUtils::PropertySet::operator==(other) && *d == *other.d; } bool KDbConnectionOptions::isReadOnly() const { return property("readOnly").value().toBool(); } void KDbConnectionOptions::insert(const QByteArray &name, const QVariant &value, const QString &caption) { if (name == "readOnly") { setReadOnly(value.toBool()); return; } QString realCaption; if (property(name).caption().isEmpty()) { // don't allow to change the caption realCaption = caption; } KDbUtils::PropertySet::insert(name, value, realCaption); } void KDbConnectionOptions::setCaption(const QByteArray &name, const QString &caption) { if (name == "readOnly") { return; } KDbUtils::PropertySet::setCaption(name, caption); } void KDbConnectionOptions::setValue(const QByteArray &name, const QVariant &value) { if (name == "readOnly") { setReadOnly(value.toBool()); return; } KDbUtils::PropertySet::setValue(name, value); } void KDbConnectionOptions::remove(const QByteArray &name) { if (name == "readOnly") { return; } KDbUtils::PropertySet::remove(name); } void KDbConnectionOptions::setReadOnly(bool set) { if (d->connection && d->connection->isConnected()) { return; //sanity } KDbUtils::PropertySet::setValue("readOnly", set); } void KDbConnectionOptions::setConnection(KDbConnection *connection) { d->connection = connection; } //================================================ KDbConnectionPrivate::KDbConnectionPrivate(KDbConnection* const conn, KDbDriver *drv, const KDbConnectionData& _connData, const KDbConnectionOptions &_options) : conn(conn) , connData(_connData) , options(_options) , driver(drv) , dbProperties(conn) { options.setConnection(conn); } KDbConnectionPrivate::~KDbConnectionPrivate() { options.setConnection(nullptr); deleteAllCursors(); delete m_parser; qDeleteAll(tableSchemaChangeListeners); qDeleteAll(obsoleteQueries); } void KDbConnectionPrivate::deleteAllCursors() { QSet cursorsToDelete(cursors); cursors.clear(); for(KDbCursor* c : cursorsToDelete) { CursorDeleter deleter(c); } } void KDbConnectionPrivate::errorInvalidDBContents(const QString& details) { conn->m_result = KDbResult(ERR_INVALID_DATABASE_CONTENTS, KDbConnection::tr("Invalid database contents. %1").arg(details)); } QString KDbConnectionPrivate::strItIsASystemObject() const { return KDbConnection::tr("It is a system object."); } void KDbConnectionPrivate::setupKDbSystemSchema() { if (!m_internalKDbTables.isEmpty()) { return; //already set up } { KDbInternalTableSchema *t_objects = new KDbInternalTableSchema(QLatin1String("kexi__objects")); t_objects->addField(new KDbField(QLatin1String("o_id"), KDbField::Integer, KDbField::PrimaryKey | KDbField::AutoInc, KDbField::Unsigned)); t_objects->addField(new KDbField(QLatin1String("o_type"), KDbField::Byte, nullptr, KDbField::Unsigned)); t_objects->addField(new KDbField(QLatin1String("o_name"), KDbField::Text)); t_objects->addField(new KDbField(QLatin1String("o_caption"), KDbField::Text)); t_objects->addField(new KDbField(QLatin1String("o_desc"), KDbField::LongText)); //kdbDebug() << *t_objects; insertTable(t_objects); } { KDbInternalTableSchema *t_objectdata = new KDbInternalTableSchema(QLatin1String("kexi__objectdata")); t_objectdata->addField(new KDbField(QLatin1String("o_id"), KDbField::Integer, KDbField::NotNull, KDbField::Unsigned)); t_objectdata->addField(new KDbField(QLatin1String("o_data"), KDbField::LongText)); t_objectdata->addField(new KDbField(QLatin1String("o_sub_id"), KDbField::Text)); insertTable(t_objectdata); } { KDbInternalTableSchema *t_fields = new KDbInternalTableSchema(QLatin1String("kexi__fields")); t_fields->addField(new KDbField(QLatin1String("t_id"), KDbField::Integer, nullptr, KDbField::Unsigned)); t_fields->addField(new KDbField(QLatin1String("f_type"), KDbField::Byte, nullptr, KDbField::Unsigned)); t_fields->addField(new KDbField(QLatin1String("f_name"), KDbField::Text)); t_fields->addField(new KDbField(QLatin1String("f_length"), KDbField::Integer)); t_fields->addField(new KDbField(QLatin1String("f_precision"), KDbField::Integer)); t_fields->addField(new KDbField(QLatin1String("f_constraints"), KDbField::Integer)); t_fields->addField(new KDbField(QLatin1String("f_options"), KDbField::Integer)); t_fields->addField(new KDbField(QLatin1String("f_default"), KDbField::Text)); //these are additional properties: t_fields->addField(new KDbField(QLatin1String("f_order"), KDbField::Integer)); t_fields->addField(new KDbField(QLatin1String("f_caption"), KDbField::Text)); t_fields->addField(new KDbField(QLatin1String("f_help"), KDbField::LongText)); insertTable(t_fields); } { KDbInternalTableSchema *t_db = new KDbInternalTableSchema(QLatin1String("kexi__db")); t_db->addField(new KDbField(QLatin1String("db_property"), KDbField::Text, KDbField::NoConstraints, KDbField::NoOptions, 32)); t_db->addField(new KDbField(QLatin1String("db_value"), KDbField::LongText)); insertTable(t_db); } } void KDbConnectionPrivate::insertTable(KDbTableSchema* tableSchema) { KDbInternalTableSchema* internalTable = dynamic_cast(tableSchema); if (internalTable) { m_internalKDbTables.insert(internalTable); } else { m_tables.insert(tableSchema->id(), tableSchema); } m_tablesByName.insert(tableSchema->name(), tableSchema); } void KDbConnectionPrivate::removeTable(const KDbTableSchema& tableSchema) { m_tablesByName.remove(tableSchema.name()); KDbTableSchema *toDelete = m_tables.take(tableSchema.id()); delete toDelete; } void KDbConnectionPrivate::takeTable(KDbTableSchema* tableSchema) { if (m_tables.isEmpty()) { return; } m_tables.take(tableSchema->id()); m_tablesByName.take(tableSchema->name()); } void KDbConnectionPrivate::renameTable(KDbTableSchema* tableSchema, const QString& newName) { m_tablesByName.take(tableSchema->name()); tableSchema->setName(newName); m_tablesByName.insert(tableSchema->name(), tableSchema); } void KDbConnectionPrivate::changeTableId(KDbTableSchema* tableSchema, int newId) { m_tables.take(tableSchema->id()); m_tables.insert(newId, tableSchema); } void KDbConnectionPrivate::clearTables() { m_tablesByName.clear(); qDeleteAll(m_internalKDbTables); m_internalKDbTables.clear(); QHash tablesToDelete(m_tables); m_tables.clear(); qDeleteAll(tablesToDelete); } void KDbConnectionPrivate::insertQuery(KDbQuerySchema* query) { m_queries.insert(query->id(), query); m_queriesByName.insert(query->name(), query); } void KDbConnectionPrivate::removeQuery(KDbQuerySchema* querySchema) { m_queriesByName.remove(querySchema->name()); m_queries.remove(querySchema->id()); delete querySchema; } void KDbConnectionPrivate::setQueryObsolete(KDbQuerySchema* query) { obsoleteQueries.insert(query); m_queriesByName.take(query->name()); m_queries.take(query->id()); } void KDbConnectionPrivate::clearQueries() { qDeleteAll(m_queries); m_queries.clear(); } KDbTableSchema* KDbConnectionPrivate::setupTableSchema(KDbTableSchema *table) { Q_ASSERT(table); QScopedPointer newTable(table); KDbCursor *cursor; if (!(cursor = conn->executeQuery( KDbEscapedString("SELECT t_id, f_type, f_name, f_length, f_precision, f_constraints, " "f_options, f_default, f_order, f_caption, f_help " "FROM kexi__fields WHERE t_id=%1 ORDER BY f_order") .arg(driver->valueToSql(KDbField::Integer, table->id()))))) { return nullptr; } if (!cursor->moveFirst()) { if (!cursor->result().isError() && cursor->eof()) { conn->m_result = KDbResult(tr("Table has no fields defined.")); } conn->deleteCursor(cursor); return nullptr; } // For each field: load its schema KDbRecordData fieldData; bool ok = true; while (!cursor->eof()) { // kdbDebug()<<"@@@ f_name=="<value(2).asCString(); if (!cursor->storeCurrentRecord(&fieldData)) { ok = false; break; } KDbField *f = conn->setupField(fieldData); if (!f || !table->addField(f)) { ok = false; break; } cursor->moveNext(); } if (!ok) {//error: conn->deleteCursor(cursor); return nullptr; } if (!conn->deleteCursor(cursor)) { return nullptr; } if (!conn->loadExtendedTableSchemaData(table)) { return nullptr; } //store locally: insertTable(table); return newTable.take(); } KDbQuerySchema* KDbConnectionPrivate::setupQuerySchema(KDbQuerySchema *query) { Q_ASSERT(query); QScopedPointer newQuery(query); QString sql; if (!conn->loadDataBlock(query->id(), &sql, QLatin1String("sql"))) { conn->m_result = KDbResult( ERR_OBJECT_NOT_FOUND, tr("Could not find definition for query \"%1\". Deleting this query is recommended.") .arg(query->name())); return nullptr; } if (!parser()->parse(KDbEscapedString(sql), query)) { conn->m_result = KDbResult( ERR_SQL_PARSE_ERROR, tr("

Could not load definition for query \"%1\". " "SQL statement for this query is invalid:
%2

\n" "

This query can be edited only in Text View.

") .arg(query->name(), sql)); return nullptr; } insertQuery(query); return newQuery.take(); } KDbQuerySchemaFieldsExpanded *KDbConnectionPrivate::fieldsExpanded(const KDbQuerySchema *query) { return m_fieldsExpandedCache[query]; } void KDbConnectionPrivate::insertFieldsExpanded(const KDbQuerySchema *query, KDbQuerySchemaFieldsExpanded *cache) { m_fieldsExpandedCache.insert(query, cache); } //================================================ namespace { //! @internal static: list of internal KDb system table names class SystemTables : public QStringList { public: SystemTables() : QStringList({ QLatin1String("kexi__objects"), QLatin1String("kexi__objectdata"), QLatin1String("kexi__fields"), QLatin1String("kexi__db")}) {} }; } Q_GLOBAL_STATIC(SystemTables, g_kdbSystemTableNames) KDbConnection::KDbConnection(KDbDriver *driver, const KDbConnectionData& connData, const KDbConnectionOptions &options) : d(new KDbConnectionPrivate(this, driver, connData, options)) { if (d->connData.driverId().isEmpty()) { d->connData.setDriverId(d->driver->metaData()->id()); } } void KDbConnection::destroy() { disconnect(); //do not allow the driver to touch me: I will kill myself. d->driver->d->connections.remove(this); } KDbConnection::~KDbConnection() { KDbConnectionPrivate *thisD = d; d = nullptr; // make sure d is nullptr before destructing delete thisD; } KDbConnectionData KDbConnection::data() const { return d->connData; } KDbDriver* KDbConnection::driver() const { return d->driver; } bool KDbConnection::connect() { clearResult(); if (d->isConnected) { m_result = KDbResult(ERR_ALREADY_CONNECTED, tr("Connection already established.")); return false; } d->serverVersion.clear(); if (!(d->isConnected = drv_connect())) { if (m_result.code() == ERR_NONE) { m_result.setCode(ERR_OTHER); } m_result.setMessage(d->driver->metaData()->isFileBased() ? tr("Could not open \"%1\" project file.") .arg(QDir::fromNativeSeparators(QFileInfo(d->connData.databaseName()).fileName())) : tr("Could not connect to \"%1\" database server.") .arg(d->connData.toUserVisibleString())); } if (d->isConnected && !d->driver->behavior()->USING_DATABASE_REQUIRED_TO_CONNECT) { if (!drv_getServerVersion(&d->serverVersion)) return false; } return d->isConnected; } bool KDbConnection::isDatabaseUsed() const { return !d->usedDatabase.isEmpty() && d->isConnected && drv_isDatabaseUsed(); } void KDbConnection::clearResult() { KDbResultable::clearResult(); } bool KDbConnection::disconnect() { clearResult(); if (!d->isConnected) return true; if (!closeDatabase()) return false; bool ok = drv_disconnect(); if (ok) d->isConnected = false; return ok; } bool KDbConnection::isConnected() const { return d->isConnected; } bool KDbConnection::checkConnected() { if (d->isConnected) { clearResult(); return true; } m_result = KDbResult(ERR_NO_CONNECTION, tr("Not connected to the database server.")); return false; } bool KDbConnection::checkIsDatabaseUsed() { if (isDatabaseUsed()) { clearResult(); return true; } m_result = KDbResult(ERR_NO_DB_USED, tr("Currently no database is used.")); return false; } QStringList KDbConnection::databaseNames(bool also_system_db) { //kdbDebug() << also_system_db; if (!checkConnected()) return QStringList(); QString tmpdbName; //some engines need to have opened any database before executing "create database" if (!useTemporaryDatabaseIfNeeded(&tmpdbName)) return QStringList(); QStringList list; bool ret = drv_getDatabasesList(&list); if (!tmpdbName.isEmpty()) { //whatever result is - now we have to close temporary opened database: if (!closeDatabase()) return QStringList(); } if (!ret) return QStringList(); if (also_system_db) return list; //filter system databases: for (QMutableListIterator it(list); it.hasNext();) { if (d->driver->isSystemDatabaseName(it.next())) { it.remove(); } } return list; } bool KDbConnection::drv_getDatabasesList(QStringList* list) { list->clear(); return true; } bool KDbConnection::drv_databaseExists(const QString &dbName, bool ignoreErrors) { QStringList list = databaseNames(true);//also system if (m_result.isError()) { return false; } if (list.indexOf(dbName) == -1) { if (!ignoreErrors) m_result = KDbResult(ERR_OBJECT_NOT_FOUND, tr("The database \"%1\" does not exist.").arg(dbName)); return false; } return true; } bool KDbConnection::databaseExists(const QString &dbName, bool ignoreErrors) { // kdbDebug() << dbName << ignoreErrors; if (d->driver->behavior()->CONNECTION_REQUIRED_TO_CHECK_DB_EXISTENCE && !checkConnected()) return false; clearResult(); if (d->driver->metaData()->isFileBased()) { //for file-based db: file must exists and be accessible QFileInfo file(d->connData.databaseName()); if (!file.exists() || (!file.isFile() && !file.isSymLink())) { if (!ignoreErrors) m_result = KDbResult(ERR_OBJECT_NOT_FOUND, tr("The database file \"%1\" does not exist.") .arg(QDir::fromNativeSeparators(QFileInfo(d->connData.databaseName()).fileName()))); return false; } if (!file.isReadable()) { if (!ignoreErrors) m_result = KDbResult(ERR_ACCESS_RIGHTS, tr("Database file \"%1\" is not readable.") .arg(QDir::fromNativeSeparators(QFileInfo(d->connData.databaseName()).fileName()))); return false; } if (!d->options.isReadOnly() && !file.isWritable()) { if (!ignoreErrors) m_result = KDbResult(ERR_ACCESS_RIGHTS, tr("Database file \"%1\" is not writable.") .arg(QDir::fromNativeSeparators(QFileInfo(d->connData.databaseName()).fileName()))); return false; } return true; } QString tmpdbName; //some engines need to have opened any database before executing "create database" const bool orig_skipDatabaseExistsCheckInUseDatabase = d->skipDatabaseExistsCheckInUseDatabase; d->skipDatabaseExistsCheckInUseDatabase = true; bool ret = useTemporaryDatabaseIfNeeded(&tmpdbName); d->skipDatabaseExistsCheckInUseDatabase = orig_skipDatabaseExistsCheckInUseDatabase; if (!ret) return false; ret = drv_databaseExists(dbName, ignoreErrors); if (!tmpdbName.isEmpty()) { //whatever result is - now we have to close temporary opened database: if (!closeDatabase()) return false; } return ret; } #define createDatabase_CLOSE \ { if (!closeDatabase()) { \ m_result = KDbResult(KDbConnection::tr("Database \"%1\" has been created but " \ "could not be closed after creation.").arg(dbName)); \ return false; \ } } #define createDatabase_ERROR \ { createDatabase_CLOSE; return false; } bool KDbConnection::createDatabase(const QString &dbName) { if (d->driver->behavior()->CONNECTION_REQUIRED_TO_CREATE_DB && !checkConnected()) return false; if (databaseExists(dbName)) { m_result = KDbResult(ERR_OBJECT_EXISTS, tr("Database \"%1\" already exists.").arg(dbName)); return false; } if (d->driver->isSystemDatabaseName(dbName)) { m_result = KDbResult(ERR_SYSTEM_NAME_RESERVED, tr("Could not create database \"%1\". This name is reserved for system database.").arg(dbName)); return false; } if (d->driver->metaData()->isFileBased()) { //update connection data if filename differs if (QFileInfo(dbName).isAbsolute()) { d->connData.setDatabaseName(dbName); } else { d->connData.setDatabaseName( QFileInfo(d->connData.databaseName()).absolutePath() + QDir::separator() + QFileInfo(dbName).fileName()); } } QString tmpdbName; //some engines need to have opened any database before executing "create database" if (!useTemporaryDatabaseIfNeeded(&tmpdbName)) return false; //low-level create if (!drv_createDatabase(dbName)) { m_result.prependMessage(tr("Error creating database \"%1\" on the server.").arg(dbName)); (void)closeDatabase();//sanity return false; } if (!tmpdbName.isEmpty()) { //whatever result is - now we have to close temporary opened database: if (!closeDatabase()) return false; } if (!tmpdbName.isEmpty() || !d->driver->behavior()->IS_DB_OPEN_AFTER_CREATE) { //db need to be opened if (!useDatabase(dbName, false/*not yet kexi compatible!*/)) { m_result = KDbResult(tr("Database \"%1\" has been created but could not be opened.").arg(dbName)); return false; } } else { //just for the rule d->usedDatabase = dbName; d->isConnected = true; } KDbTransaction trans; if (d->driver->transactionsSupported()) { trans = beginTransaction(); if (!trans.isActive()) return false; } //-create system tables schema objects d->setupKDbSystemSchema(); //-physically create internal KDb tables foreach(KDbInternalTableSchema* t, d->internalKDbTables()) { if (!drv_createTable(*t)) createDatabase_ERROR; } //-insert KDb version info: // (for compatibility with Kexi expect the legacy kexidb_major_ver/kexidb_minor_ver values) KDbTableSchema *table = d->table(QLatin1String("kexi__db")); if (!table) createDatabase_ERROR; if (!insertRecord(table, QLatin1String("kexidb_major_ver"), KDb::version().major()) || !insertRecord(table, QLatin1String("kexidb_minor_ver"), KDb::version().minor())) createDatabase_ERROR; if (trans.isActive() && !commitTransaction(trans)) createDatabase_ERROR; createDatabase_CLOSE; return true; } #undef createDatabase_CLOSE #undef createDatabase_ERROR bool KDbConnection::useDatabase(const QString &dbName, bool kexiCompatible, bool *cancelled, KDbMessageHandler* msgHandler) { if (cancelled) *cancelled = false; //kdbDebug() << dbName << kexiCompatible; if (!checkConnected()) return false; QString my_dbName; if (dbName.isEmpty()) my_dbName = d->connData.databaseName(); else my_dbName = dbName; if (my_dbName.isEmpty()) return false; if (d->usedDatabase == my_dbName) return true; //already used if (!d->skipDatabaseExistsCheckInUseDatabase) { if (!databaseExists(my_dbName, false /*don't ignore errors*/)) return false; //database must exist } if (!d->usedDatabase.isEmpty() && !closeDatabase()) //close db if already used return false; d->usedDatabase.clear(); if (!drv_useDatabase(my_dbName, cancelled, msgHandler)) { if (cancelled && *cancelled) return false; QString msg(tr("Opening database \"%1\" failed.").arg(my_dbName)); m_result.prependMessage(msg); return false; } if (d->serverVersion.isNull() && d->driver->behavior()->USING_DATABASE_REQUIRED_TO_CONNECT) { // get version just now, it was not possible earlier if (!drv_getServerVersion(&d->serverVersion)) return false; } //-create system tables schema objects d->setupKDbSystemSchema(); if (kexiCompatible && my_dbName.compare(anyAvailableDatabaseName(), Qt::CaseInsensitive) != 0) { //-get global database information bool ok; const int major = d->dbProperties.value(QLatin1String("kexidb_major_ver")).toInt(&ok); if (!ok) { m_result = d->dbProperties.result(); return false; } const int minor = d->dbProperties.value(QLatin1String("kexidb_minor_ver")).toInt(&ok); if (!ok) { m_result = d->dbProperties.result(); return false; } d->databaseVersion.setMajor(major); d->databaseVersion.setMinor(minor); } d->usedDatabase = my_dbName; return true; } bool KDbConnection::closeDatabase() { if (d->usedDatabase.isEmpty()) return true; //no db used if (!checkConnected()) return true; bool ret = true; /*! @todo (js) add CLEVER algorithm here for nested transactions */ if (d->driver->transactionsSupported()) { //rollback all transactions d->dontRemoveTransactions = true; //lock! foreach(const KDbTransaction& tr, d->transactions) { if (!rollbackTransaction(tr)) {//rollback as much as you can, don't stop on prev. errors ret = false; } else { kdbDebug() << "transaction rolled back!"; kdbDebug() << "trans.refcount==" << (tr.m_data ? QString::number(tr.m_data->refcount()) : QLatin1String("(null)")); } } d->dontRemoveTransactions = false; //unlock! d->transactions.clear(); //free trans. data } //delete own cursors: d->deleteAllCursors(); //delete own schemas d->clearTables(); d->clearQueries(); if (!drv_closeDatabase()) return false; d->usedDatabase.clear(); return ret; } QString KDbConnection::currentDatabase() const { return d->usedDatabase; } bool KDbConnection::useTemporaryDatabaseIfNeeded(QString* name) { if (d->driver->behavior()->USE_TEMPORARY_DATABASE_FOR_CONNECTION_IF_NEEDED && !isDatabaseUsed()) { //we have no db used, but it is required by engine to have used any! *name = anyAvailableDatabaseName(); if (name->isEmpty()) { m_result = KDbResult(ERR_NO_DB_USED, tr("Could not find any database for temporary connection.")); return false; } const bool orig_skipDatabaseExistsCheckInUseDatabase = d->skipDatabaseExistsCheckInUseDatabase; d->skipDatabaseExistsCheckInUseDatabase = true; bool ret = useDatabase(*name, false); d->skipDatabaseExistsCheckInUseDatabase = orig_skipDatabaseExistsCheckInUseDatabase; if (!ret) { m_result = KDbResult(m_result.code(), tr("Error during starting temporary connection using \"%1\" database name.").arg(*name)); return false; } } return true; } bool KDbConnection::dropDatabase(const QString &dbName) { if (d->driver->behavior()->CONNECTION_REQUIRED_TO_DROP_DB && !checkConnected()) return false; QString dbToDrop; if (dbName.isEmpty() && d->usedDatabase.isEmpty()) { if (!d->driver->metaData()->isFileBased() || (d->driver->metaData()->isFileBased() && d->connData.databaseName().isEmpty())) { m_result = KDbResult(ERR_NO_NAME_SPECIFIED, tr("Could not delete database. Name is not specified.")); return false; } //this is a file driver so reuse previously passed filename dbToDrop = d->connData.databaseName(); } else { if (dbName.isEmpty()) { dbToDrop = d->usedDatabase; } else { if (d->driver->metaData()->isFileBased()) //lets get full path dbToDrop = QFileInfo(dbName).absoluteFilePath(); else dbToDrop = dbName; } } if (dbToDrop.isEmpty()) { m_result = KDbResult(ERR_NO_NAME_SPECIFIED, tr("Could not delete database. Name is not specified.")); return false; } if (d->driver->isSystemDatabaseName(dbToDrop)) { m_result = KDbResult(ERR_SYSTEM_NAME_RESERVED, tr("Could not delete system database \"%1\".").arg(dbToDrop)); return false; } if (isDatabaseUsed() && d->usedDatabase == dbToDrop) { //we need to close database because cannot drop used this database if (!closeDatabase()) return false; } QString tmpdbName; //some engines need to have opened any database before executing "drop database" if (!useTemporaryDatabaseIfNeeded(&tmpdbName)) return false; //ok, now we have access to dropping bool ret = drv_dropDatabase(dbToDrop); if (!tmpdbName.isEmpty()) { //whatever result is - now we have to close temporary opened database: if (!closeDatabase()) return false; } return ret; } QStringList KDbConnection::objectNames(int objectType, bool* ok) { if (!checkIsDatabaseUsed()) { if (ok) { *ok = false; } return QStringList(); } KDbEscapedString sql; if (objectType == KDb::AnyObjectType) { sql = "SELECT o_name FROM kexi__objects ORDER BY o_id"; } else { sql = KDbEscapedString("SELECT o_name FROM kexi__objects WHERE o_type=%1" " ORDER BY o_id").arg(d->driver->valueToSql(KDbField::Integer, objectType)); } QStringList list; const bool success = queryStringListInternal(&sql, &list, nullptr, nullptr, 0, KDb::isIdentifier); if (ok) { *ok = success; } if (!success) { m_result.prependMessage(tr("Could not retrieve list of object names.")); } return list; } QStringList KDbConnection::tableNames(bool alsoSystemTables, bool* ok) { bool success; QStringList list = objectNames(KDb::TableObjectType, &success); if (ok) { *ok = success; } if (!success) { m_result.prependMessage(tr("Could not retrieve list of table names.")); } if (alsoSystemTables && success) { list += kdbSystemTableNames(); } return list; } tristate KDbConnection::containsTable(const QString &tableName) { return drv_containsTable(tableName); } QStringList KDbConnection::kdbSystemTableNames() { return *g_kdbSystemTableNames; } KDbServerVersionInfo KDbConnection::serverVersion() const { return isConnected() ? d->serverVersion : KDbServerVersionInfo(); } KDbVersionInfo KDbConnection::databaseVersion() const { return isDatabaseUsed() ? d->databaseVersion : KDbVersionInfo(); } KDbProperties KDbConnection::databaseProperties() const { return d->dbProperties; } QList KDbConnection::tableIds(bool* ok) { return objectIds(KDb::TableObjectType, ok); } QList KDbConnection::queryIds(bool* ok) { return objectIds(KDb::QueryObjectType, ok); } QList KDbConnection::objectIds(int objectType, bool* ok) { if (!checkIsDatabaseUsed()) return QList(); KDbEscapedString sql; if (objectType == KDb::AnyObjectType) sql = "SELECT o_id, o_name FROM kexi__objects ORDER BY o_id"; else sql = "SELECT o_id, o_name FROM kexi__objects WHERE o_type=" + QByteArray::number(objectType) + " ORDER BY o_id"; KDbCursor *c = executeQuery(sql); if (!c) { if (ok) { *ok = false; } m_result.prependMessage(tr("Could not retrieve list of object identifiers.")); return QList(); } QList list; for (c->moveFirst(); !c->eof(); c->moveNext()) { QString tname = c->value(1).toString(); //kexi__objects.o_name if (KDb::isIdentifier(tname)) { list.append(c->value(0).toInt()); //kexi__objects.o_id } } deleteCursor(c); if (ok) { *ok = true; } return list; } //yeah, it is very efficient: #define C_A(a) , const QVariant& c ## a #define V_A0 d->driver->valueToSql( tableSchema->field(0), c0 ) #define V_A(a) + ',' + d->driver->valueToSql( \ tableSchema->field(a) ? tableSchema->field(a)->type() : KDbField::Text, c ## a ) // kdbDebug() << "******** " << QString("INSERT INTO ") + // escapeIdentifier(tableSchema->name()) + // " VALUES (" + vals + ")"; QSharedPointer KDbConnection::insertRecordInternal(const QString &tableSchemaName, KDbFieldList *fields, const KDbEscapedString &sql) { QSharedPointer res; if (!drv_beforeInsert(tableSchemaName,fields )) { return res; } res = prepareSql(sql); if (!res || res->lastResult().isError()) { res.clear(); return res; } if (!drv_afterInsert(tableSchemaName, fields)) { res.clear(); return res; } { // Fetching is needed to perform real execution at least for some backends. // Also we are not expecting record but let's delete if there's any. QSharedPointer record = res->fetchRecord(); Q_UNUSED(record) } if (res->lastResult().isError()) { res.clear(); } return res; } #define C_INS_REC(args, vals) \ QSharedPointer KDbConnection::insertRecord(KDbTableSchema* tableSchema args) { \ return insertRecordInternal(tableSchema->name(), tableSchema, \ KDbEscapedString("INSERT INTO ") + escapeIdentifier(tableSchema->name()) \ + " (" \ + tableSchema->sqlFieldsList(this) \ + ") VALUES (" + vals + ')'); \ } #define C_INS_REC_ALL \ C_INS_REC( C_A(0), V_A0 ) \ C_INS_REC( C_A(0) C_A(1), V_A0 V_A(1) ) \ C_INS_REC( C_A(0) C_A(1) C_A(2), V_A0 V_A(1) V_A(2) ) \ C_INS_REC( C_A(0) C_A(1) C_A(2) C_A(3), V_A0 V_A(1) V_A(2) V_A(3) ) \ C_INS_REC( C_A(0) C_A(1) C_A(2) C_A(3) C_A(4), V_A0 V_A(1) V_A(2) V_A(3) V_A(4) ) \ C_INS_REC( C_A(0) C_A(1) C_A(2) C_A(3) C_A(4) C_A(5), V_A0 V_A(1) V_A(2) V_A(3) V_A(4) V_A(5) ) \ C_INS_REC( C_A(0) C_A(1) C_A(2) C_A(3) C_A(4) C_A(5) C_A(6), V_A0 V_A(1) V_A(2) V_A(3) V_A(4) V_A(5) V_A(6) ) \ C_INS_REC( C_A(0) C_A(1) C_A(2) C_A(3) C_A(4) C_A(5) C_A(6) C_A(7), V_A0 V_A(1) V_A(2) V_A(3) V_A(4) V_A(5) V_A(6) V_A(7) ) C_INS_REC_ALL #undef V_A0 #undef V_A #undef C_INS_REC #define V_A0 value += d->driver->valueToSql( it.next(), c0 ); #define V_A( a ) value += (',' + d->driver->valueToSql( it.next(), c ## a )); #define C_INS_REC(args, vals) \ QSharedPointer KDbConnection::insertRecord(KDbFieldList* fields args) \ { \ KDbEscapedString value; \ const KDbField::List *flist = fields->fields(); \ QListIterator it(*flist); \ vals \ it.toFront(); \ QString tableName((it.hasNext() && it.peekNext()->table()) ? it.next()->table()->name() : QLatin1String("??")); \ return insertRecordInternal(tableName, fields, \ KDbEscapedString(QLatin1String("INSERT INTO ") + escapeIdentifier(tableName)) \ + " (" + fields->sqlFieldsList(this) \ + ") VALUES (" + value + ')'); \ } C_INS_REC_ALL #undef C_A #undef V_A #undef V_ALAST #undef C_INS_REC #undef C_INS_REC_ALL QSharedPointer KDbConnection::insertRecord(KDbTableSchema *tableSchema, const QList &values) { // Each SQL identifier needs to be escaped in the generated query. QSharedPointer res; const KDbField::List *flist = tableSchema->fields(); if (flist->isEmpty()) { return res; } KDbField::ListIterator fieldsIt(flist->constBegin()); QList::ConstIterator it = values.constBegin(); KDbEscapedString sql; sql.reserve(4096); while (fieldsIt != flist->constEnd() && (it != values.end())) { KDbField *f = *fieldsIt; if (sql.isEmpty()) { sql = KDbEscapedString("INSERT INTO ") + escapeIdentifier(tableSchema->name()) + " VALUES ("; } else { sql += ','; } sql += d->driver->valueToSql(f, *it); // kdbDebug() << "val" << i++ << ": " << d->driver->valueToSql( f, *it ); ++it; ++fieldsIt; } sql += ')'; m_result.setSql(sql); res = insertRecordInternal(tableSchema->name(), tableSchema, sql); return res; } QSharedPointer KDbConnection::insertRecord(KDbFieldList *fields, const QList &values) { // Each SQL identifier needs to be escaped in the generated query. QSharedPointer res; const KDbField::List *flist = fields->fields(); if (flist->isEmpty()) { return res; } KDbField::ListIterator fieldsIt(flist->constBegin()); KDbEscapedString sql; sql.reserve(4096); QList::ConstIterator it = values.constBegin(); const QString tableName(flist->first()->table()->name()); while (fieldsIt != flist->constEnd() && it != values.constEnd()) { KDbField *f = *fieldsIt; if (sql.isEmpty()) { sql = KDbEscapedString("INSERT INTO ") + escapeIdentifier(tableName) + '(' + fields->sqlFieldsList(this) + ") VALUES ("; } else { sql += ','; } sql += d->driver->valueToSql(f, *it); // kdbDebug() << "val" << i++ << ": " << d->driver->valueToSql( f, *it ); ++it; ++fieldsIt; if (fieldsIt == flist->constEnd()) break; } sql += ')'; m_result.setSql(sql); res = insertRecordInternal(tableName, fields, sql); return res; } inline static bool checkSql(const KDbEscapedString& sql, KDbResult* result) { Q_ASSERT(result); if (!sql.isValid()) { *result = KDbResult(ERR_SQL_EXECUTION_ERROR, KDbConnection::tr("SQL statement for execution is invalid or empty.")); result->setErrorSql(sql); //remember for error handling return false; } return true; } QSharedPointer KDbConnection::prepareSql(const KDbEscapedString& sql) { m_result.setSql(sql); return QSharedPointer(drv_prepareSql(sql)); } bool KDbConnection::executeSql(const KDbEscapedString& sql) { m_result.setSql(sql); if (!checkSql(sql, &m_result)) { return false; } if (!drv_executeSql(sql)) { m_result.setMessage(QString()); //clear as this could be most probably just "Unknown error" string. m_result.setErrorSql(sql); m_result.prependMessage(ERR_SQL_EXECUTION_ERROR, tr("Error while executing SQL statement.")); kdbWarning() << m_result; return false; } return true; } KDbField* KDbConnection::findSystemFieldName(const KDbFieldList& fieldlist) { for (KDbField::ListIterator it(fieldlist.fieldsIterator()); it != fieldlist.fieldsIteratorConstEnd(); ++it) { if (d->driver->isSystemFieldName((*it)->name())) return *it; } return nullptr; } //! Creates a KDbField list for kexi__fields, for sanity. Used by createTable() static KDbFieldList* createFieldListForKexi__Fields(KDbTableSchema *kexi__fieldsSchema) { if (!kexi__fieldsSchema) return nullptr; return kexi__fieldsSchema->subList( QList() << "t_id" << "f_type" << "f_name" << "f_length" << "f_precision" << "f_constraints" << "f_options" << "f_default" << "f_order" << "f_caption" << "f_help" ); } static QVariant buildLengthValue(const KDbField &f) { if (f.isFPNumericType()) { return f.scale(); } return f.maxLength(); } //! builds a list of values for field's @a f properties. Used by createTable(). static void buildValuesForKexi__Fields(QList& vals, KDbField* f) { const KDbField::Type type = f->type(); // cache: evaluating type of expressions can be expensive vals.clear(); vals << QVariant(f->table()->id()) << QVariant(type) << QVariant(f->name()) << buildLengthValue(*f) << QVariant(KDbField::isFPNumericType(type) ? f->precision() : 0) << QVariant(f->constraints()) << QVariant(f->options()) // KDb::variantToString() is needed here because the value can be of any QVariant type, // depending on f->type() << (f->defaultValue().isNull() ? QVariant() : QVariant(KDb::variantToString(f->defaultValue()))) << QVariant(f->order()) << QVariant(f->caption()) << QVariant(f->description()); } bool KDbConnection::storeMainFieldSchema(KDbField *field) { if (!field || !field->table()) return false; KDbFieldList *fl = createFieldListForKexi__Fields(d->table(QLatin1String("kexi__fields"))); if (!fl) return false; QList vals; buildValuesForKexi__Fields(vals, field); QList::ConstIterator valsIt = vals.constBegin(); bool first = true; KDbEscapedString sql("UPDATE kexi__fields SET "); foreach(KDbField *f, *fl->fields()) { sql.append((first ? QString() : QLatin1String(", ")) + f->name() + QLatin1Char('=') + d->driver->valueToSql(f, *valsIt)); if (first) first = false; ++valsIt; } delete fl; sql.append(KDbEscapedString(" WHERE t_id=%1 AND f_name=%2") .arg(d->driver->valueToSql(KDbField::Integer, field->table()->id())) .arg(escapeString(field->name()))); return executeSql(sql); } #define createTable_ERR \ { kdbDebug() << "ERROR!"; \ m_result.prependMessage(KDbConnection::tr("Creating table failed.")); \ rollbackAutoCommitTransaction(tg.transaction()); \ return false; } bool KDbConnection::createTable(KDbTableSchema* tableSchema, CreateTableOptions options) { if (!tableSchema || !checkIsDatabaseUsed()) return false; //check if there are any fields if (tableSchema->fieldCount() < 1) { clearResult(); m_result = KDbResult(ERR_CANNOT_CREATE_EMPTY_OBJECT, tr("Could not create table without fields.")); return false; } KDbInternalTableSchema* internalTable = dynamic_cast(tableSchema); const QString tableName(tableSchema->name()); if (!internalTable) { if (d->driver->isSystemObjectName(tableName)) { clearResult(); m_result = KDbResult(ERR_SYSTEM_NAME_RESERVED, tr("System name \"%1\" cannot be used as table name.") .arg(tableSchema->name())); return false; } KDbField *sys_field = findSystemFieldName(*tableSchema); if (sys_field) { clearResult(); m_result = KDbResult(ERR_SYSTEM_NAME_RESERVED, tr("System name \"%1\" cannot be used as one of fields in \"%2\" table.") .arg(sys_field->name(), tableName)); return false; } } bool previousSchemaStillKept = false; KDbTableSchema *existingTable = nullptr; if (options & CreateTableOption::DropDestination) { //get previous table (do not retrieve, though) existingTable = this->tableSchema(tableName); if (existingTable) { if (existingTable == tableSchema) { clearResult(); m_result = KDbResult(ERR_OBJECT_EXISTS, tr("Could not create the same table \"%1\" twice.").arg(tableSchema->name())); return false; } //! @todo (js) update any structure (e.g. queries) that depend on this table! if (existingTable->id() > 0) tableSchema->setId(existingTable->id()); //copy id from existing table previousSchemaStillKept = true; if (!dropTableInternal(existingTable, false /*alsoRemoveSchema*/)) return false; } } else { if (!internalTable && this->tableSchema(tableSchema->name())) { clearResult(); m_result = KDbResult(ERR_OBJECT_EXISTS, tr("Table \"%1\" already exists.").arg(tableSchema->name())); return false; } } KDbTransactionGuard tg; if (!beginAutoCommitTransaction(&tg)) return false; if (internalTable) { if (!drv_containsTable(internalTable->name())) { // internal table may exist if (!drv_createTable(*tableSchema)) { createTable_ERR; } } } else { if (!drv_createTable(*tableSchema)) { createTable_ERR; } } //add the object data to kexi__* tables if (!internalTable) { //update kexi__objects if (!storeNewObjectData(tableSchema)) createTable_ERR; KDbTableSchema *ts = d->table(QLatin1String("kexi__fields")); if (!ts) return false; //for sanity: remove field info (if any) for this table id if (!KDb::deleteRecords(this, *ts, QLatin1String("t_id"), tableSchema->id())) return false; KDbFieldList *fl = createFieldListForKexi__Fields(ts); if (!fl) return false; foreach(KDbField *f, *tableSchema->fields()) { QList vals; buildValuesForKexi__Fields(vals, f); if (!insertRecord(fl, vals)) createTable_ERR; } delete fl; if (!storeExtendedTableSchemaData(tableSchema)) createTable_ERR; } bool res = commitAutoCommitTransaction(tg.transaction()); if (res) { if (!internalTable) { if (previousSchemaStillKept) { //remove previous table schema d->removeTable(*tableSchema); } } //store locally d->insertTable(tableSchema); //ok, this table is not created by the connection tableSchema->setConnection(this); } return res; } KDbTableSchema *KDbConnection::copyTable(const KDbTableSchema &tableSchema, const KDbObject &newData) { clearResult(); if (this->tableSchema(tableSchema.name()) != &tableSchema) { m_result = KDbResult(ERR_OBJECT_NOT_FOUND, tr("Table \"%1\" does not exist.").arg(tableSchema.name())); return nullptr; } KDbTableSchema *copiedTable = new KDbTableSchema(tableSchema, false /* !copyId*/); // copy name, caption, description copiedTable->setName(newData.name()); copiedTable->setCaption(newData.caption()); copiedTable->setDescription(newData.description()); // copy the structure and data if (!createTable(copiedTable, CreateTableOptions(CreateTableOption::Default) & ~CreateTableOptions(CreateTableOption::DropDestination))) { delete copiedTable; return nullptr; } if (!drv_copyTableData(tableSchema, *copiedTable)) { dropTable(copiedTable); delete copiedTable; return nullptr; } return copiedTable; } KDbTableSchema *KDbConnection::copyTable(const QString &tableName, const KDbObject &newData) { clearResult(); KDbTableSchema* ts = tableSchema(tableName); if (!ts) { m_result = KDbResult(ERR_OBJECT_NOT_FOUND, tr("Table \"%1\" does not exist.").arg(tableName)); return nullptr; } return copyTable(*ts, newData); } bool KDbConnection::drv_copyTableData(const KDbTableSchema &tableSchema, const KDbTableSchema &destinationTableSchema) { KDbEscapedString sql = KDbEscapedString("INSERT INTO %1 SELECT * FROM %2") .arg(escapeIdentifier(destinationTableSchema.name())) .arg(escapeIdentifier(tableSchema.name())); return executeSql(sql); } bool KDbConnection::removeObject(int objId) { clearResult(); //remove table schema from kexi__* tables KDbTableSchema *kexi__objects = d->table(QLatin1String("kexi__objects")); KDbTableSchema *kexi__objectdata = d->table(QLatin1String("kexi__objectdata")); if (!kexi__objects || !kexi__objectdata || !KDb::deleteRecords(this, *kexi__objects, QLatin1String("o_id"), objId) //schema entry || !KDb::deleteRecords(this, *kexi__objectdata, QLatin1String("o_id"), objId)) //data blocks { m_result = KDbResult(ERR_DELETE_SERVER_ERROR, tr("Could not delete object's data.")); return false; } return true; } bool KDbConnection::drv_dropTable(const QString& tableName) { return executeSql(KDbEscapedString("DROP TABLE %1").arg(escapeIdentifier(tableName))); } tristate KDbConnection::dropTable(KDbTableSchema* tableSchema) { return dropTableInternal(tableSchema, true); } tristate KDbConnection::dropTableInternal(KDbTableSchema* tableSchema, bool alsoRemoveSchema) { // Each SQL identifier needs to be escaped in the generated query. clearResult(); if (!tableSchema) return false; //be sure that we handle the correct KDbTableSchema object: if (tableSchema->id() < 0 || this->tableSchema(tableSchema->name()) != tableSchema || this->tableSchema(tableSchema->id()) != tableSchema) { m_result = KDbResult(ERR_OBJECT_NOT_FOUND, tr("Could not delete table \"%1\". %2") .arg(tr("Unexpected name or identifier."), tableSchema->name())); return false; } tristate res = KDbTableSchemaChangeListener::closeListeners(this, tableSchema); if (true != res) return res; //sanity checks: if (d->driver->isSystemObjectName(tableSchema->name())) { m_result = KDbResult(ERR_SYSTEM_NAME_RESERVED, tr("Could not delete table \"%1\". %2") .arg(tableSchema->name(), d->strItIsASystemObject())); return false; } KDbTransactionGuard tg; if (!beginAutoCommitTransaction(&tg)) return false; //for sanity we're checking if this table exists physically const tristate result = drv_containsTable(tableSchema->name()); if (~result) { return cancelled; } if (result == true) { if (!drv_dropTable(tableSchema->name())) return false; } KDbTableSchema *ts = d->table(QLatin1String("kexi__fields")); if (!ts || !KDb::deleteRecords(this, *ts, QLatin1String("t_id"), tableSchema->id())) //field entries return false; //remove table schema from kexi__objects table if (!removeObject(tableSchema->id())) { return false; } if (alsoRemoveSchema) { //! @todo js: update any structure (e.g. queries) that depend on this table! tristate res = removeDataBlock(tableSchema->id(), QLatin1String("extended_schema")); if (!res) return false; d->removeTable(*tableSchema); } return commitAutoCommitTransaction(tg.transaction()); } tristate KDbConnection::dropTable(const QString& tableName) { clearResult(); KDbTableSchema* ts = tableSchema(tableName); if (!ts) { m_result = KDbResult(ERR_OBJECT_NOT_FOUND, tr("Table \"%1\" does not exist.").arg(tableName)); return false; } return dropTable(ts); } tristate KDbConnection::alterTable(KDbTableSchema* tableSchema, KDbTableSchema* newTableSchema) { clearResult(); tristate res = KDbTableSchemaChangeListener::closeListeners(this, tableSchema); if (true != res) return res; if (tableSchema == newTableSchema) { m_result = KDbResult(ERR_OBJECT_THE_SAME, tr("Could not alter table \"%1\" using the same table as destination.") .arg(tableSchema->name())); return false; } //! @todo (js) implement real altering //! @todo (js) update any structure (e.g. query) that depend on this table! bool ok = true; bool empty; #if 0 //! @todo uncomment: empty = isEmpty(tableSchema, ok) && ok; #else empty = true; #endif if (empty) { ok = createTable(newTableSchema, KDbConnection::CreateTableOption::Default | KDbConnection::CreateTableOption::DropDestination); } return ok; } bool KDbConnection::alterTableName(KDbTableSchema* tableSchema, const QString& newName, AlterTableNameOptions options) { clearResult(); if (tableSchema != this->tableSchema(tableSchema->id())) { m_result = KDbResult(ERR_OBJECT_NOT_FOUND, tr("Unknown table \"%1\".").arg(tableSchema->name())); return false; } if (newName.isEmpty() || !KDb::isIdentifier(newName)) { m_result = KDbResult(ERR_INVALID_IDENTIFIER, tr("Invalid table name \"%1\".").arg(newName)); return false; } const QString oldTableName = tableSchema->name(); const QString newTableName = newName.trimmed(); if (oldTableName.trimmed() == newTableName) { m_result = KDbResult(ERR_OBJECT_THE_SAME, tr("Could not rename table \"%1\" using the same name.") .arg(newTableName)); return false; } //! @todo alter table name for server DB backends! //! @todo what about objects (queries/forms) that use old name? KDbTableSchema *tableToReplace = this->tableSchema(newName); const bool destTableExists = tableToReplace != nullptr; const int origID = destTableExists ? tableToReplace->id() : -1; //will be reused in the new table if (!(options & AlterTableNameOption::DropDestination) && destTableExists) { m_result = KDbResult(ERR_OBJECT_EXISTS, tr("Could not rename table \"%1\" to \"%2\". Table \"%3\" already exists.") .arg(tableSchema->name(), newName, newName)); return false; } //helper: #define alterTableName_ERR \ tableSchema->setName(oldTableName) //restore old name KDbTransactionGuard tg; if (!beginAutoCommitTransaction(&tg)) return false; // drop the table replaced (with schema) if (destTableExists) { if (!dropTable(newName)) { return false; } // the new table owns the previous table's id: if (!executeSql( KDbEscapedString("UPDATE kexi__objects SET o_id=%1 WHERE o_id=%2 AND o_type=%3") .arg(d->driver->valueToSql(KDbField::Integer, origID)) .arg(d->driver->valueToSql(KDbField::Integer, tableSchema->id())) .arg(d->driver->valueToSql(KDbField::Integer, int(KDb::TableObjectType))))) { return false; } if (!executeSql(KDbEscapedString("UPDATE kexi__fields SET t_id=%1 WHERE t_id=%2") .arg(d->driver->valueToSql(KDbField::Integer, origID)) .arg(d->driver->valueToSql(KDbField::Integer, tableSchema->id())))) { return false; } //maintain table ID d->changeTableId(tableSchema, origID); tableSchema->setId(origID); } if (!drv_alterTableName(tableSchema, newTableName)) { alterTableName_ERR; return false; } // Update kexi__objects //! @todo if (!executeSql(KDbEscapedString("UPDATE kexi__objects SET o_name=%1 WHERE o_id=%2") .arg(escapeString(tableSchema->name())) .arg(d->driver->valueToSql(KDbField::Integer, tableSchema->id())))) { alterTableName_ERR; return false; } //! @todo what about caption? //restore old name: it will be changed soon! tableSchema->setName(oldTableName); if (!commitAutoCommitTransaction(tg.transaction())) { alterTableName_ERR; return false; } //update tableSchema: d->renameTable(tableSchema, newTableName); return true; } bool KDbConnection::drv_alterTableName(KDbTableSchema* tableSchema, const QString& newName) { const QString oldTableName = tableSchema->name(); tableSchema->setName(newName); if (!executeSql(KDbEscapedString("ALTER TABLE %1 RENAME TO %2") .arg(KDbEscapedString(escapeIdentifier(oldTableName)), KDbEscapedString(escapeIdentifier(newName))))) { tableSchema->setName(oldTableName); //restore old name return false; } return true; } bool KDbConnection::dropQuery(KDbQuerySchema* querySchema) { clearResult(); if (!querySchema) return false; KDbTransactionGuard tg; if (!beginAutoCommitTransaction(&tg)) return false; //remove query schema from kexi__objects table if (!removeObject(querySchema->id())) { return false; } //! @todo update any structure that depend on this table! d->removeQuery(querySchema); return commitAutoCommitTransaction(tg.transaction()); } bool KDbConnection::dropQuery(const QString& queryName) { clearResult(); KDbQuerySchema* qs = querySchema(queryName); if (!qs) { m_result = KDbResult(ERR_OBJECT_NOT_FOUND, tr("Query \"%1\" does not exist.").arg(queryName)); return false; } return dropQuery(qs); } bool KDbConnection::drv_createTable(const KDbTableSchema& tableSchema) { - const KDbNativeStatementBuilder builder(this); + const KDbNativeStatementBuilder builder(this, KDb::DriverEscaping); KDbEscapedString sql; if (!builder.generateCreateTableStatement(&sql,tableSchema)) { return false; } //kdbDebug() << "******** " << sql; return executeSql(sql); } bool KDbConnection::drv_createTable(const QString& tableName) { KDbTableSchema *ts = tableSchema(tableName); if (!ts) return false; return drv_createTable(*ts); } bool KDbConnection::beginAutoCommitTransaction(KDbTransactionGuard* tg) { if ((d->driver->behavior()->features & KDbDriver::IgnoreTransactions) || !d->autoCommit) { tg->setTransaction(KDbTransaction()); return true; } // commit current transaction (if present) for drivers // that allow single transaction per connection if (d->driver->behavior()->features & KDbDriver::SingleTransactions) { if (d->defaultTransactionStartedInside) //only commit internally started transaction if (!commitTransaction(d->default_trans, KDbTransaction::CommitOption::IgnoreInactive)) { tg->setTransaction(KDbTransaction()); return false; //we have a real error } d->defaultTransactionStartedInside = d->default_trans.isNull(); if (!d->defaultTransactionStartedInside) { tg->setTransaction(d->default_trans); tg->doNothing(); return true; //reuse externally started transaction } } else if (!(d->driver->behavior()->features & KDbDriver::MultipleTransactions)) { tg->setTransaction(KDbTransaction()); return true; //no trans. supported at all - just return } tg->setTransaction(beginTransaction()); return !m_result.isError(); } bool KDbConnection::commitAutoCommitTransaction(const KDbTransaction& trans) { if (d->driver->behavior()->features & KDbDriver::IgnoreTransactions) return true; if (trans.isNull() || !d->driver->transactionsSupported()) return true; if (d->driver->behavior()->features & KDbDriver::SingleTransactions) { if (!d->defaultTransactionStartedInside) //only commit internally started transaction return true; //give up } return commitTransaction(trans, KDbTransaction::CommitOption::IgnoreInactive); } bool KDbConnection::rollbackAutoCommitTransaction(const KDbTransaction& trans) { if (trans.isNull() || !d->driver->transactionsSupported()) return true; return rollbackTransaction(trans); } #define SET_ERR_TRANS_NOT_SUPP \ { m_result = KDbResult(ERR_UNSUPPORTED_DRV_FEATURE, \ KDbConnection::tr("Transactions are not supported for \"%1\" driver.").arg( d->driver->metaData()->name() )); } #define SET_BEGIN_TR_ERROR \ { if (!m_result.isError()) \ m_result = KDbResult(ERR_ROLLBACK_OR_COMMIT_TRANSACTION, \ KDbConnection::tr("Begin transaction failed.")); } KDbTransaction KDbConnection::beginTransaction() { if (!checkIsDatabaseUsed()) return KDbTransaction(); KDbTransaction trans; if (d->driver->behavior()->features & KDbDriver::IgnoreTransactions) { //we're creating dummy transaction data here, //so it will look like active trans.m_data = new KDbTransactionData(this); d->transactions.append(trans); return trans; } if (d->driver->behavior()->features & KDbDriver::SingleTransactions) { if (d->default_trans.isActive()) { m_result = KDbResult(ERR_TRANSACTION_ACTIVE, tr("Transaction already started.")); return KDbTransaction(); } if (!(trans.m_data = drv_beginTransaction())) { SET_BEGIN_TR_ERROR; return KDbTransaction(); } d->default_trans = trans; d->transactions.append(trans); return d->default_trans; } if (d->driver->behavior()->features & KDbDriver::MultipleTransactions) { if (!(trans.m_data = drv_beginTransaction())) { SET_BEGIN_TR_ERROR; return KDbTransaction(); } d->transactions.append(trans); return trans; } SET_ERR_TRANS_NOT_SUPP; return KDbTransaction(); } bool KDbConnection::commitTransaction(const KDbTransaction trans, KDbTransaction::CommitOptions options) { if (!isDatabaseUsed()) return false; if (!d->driver->transactionsSupported() && !(d->driver->behavior()->features & KDbDriver::IgnoreTransactions)) { SET_ERR_TRANS_NOT_SUPP; return false; } KDbTransaction t = trans; if (!t.isActive()) { //try default tr. if (!d->default_trans.isActive()) { if (options & KDbTransaction::CommitOption::IgnoreInactive) { return true; } clearResult(); m_result = KDbResult(ERR_NO_TRANSACTION_ACTIVE, tr("Transaction not started.")); return false; } t = d->default_trans; d->default_trans = KDbTransaction(); //now: no default tr. } bool ret = true; if (!(d->driver->behavior()->features & KDbDriver::IgnoreTransactions)) ret = drv_commitTransaction(t.m_data); if (t.m_data) t.m_data->setActive(false); //now this transaction if inactive if (!d->dontRemoveTransactions) //true=transaction obj will be later removed from list d->transactions.removeAt(d->transactions.indexOf(t)); if (!ret && !m_result.isError()) m_result = KDbResult(ERR_ROLLBACK_OR_COMMIT_TRANSACTION, tr("Error on commit transaction.")); return ret; } bool KDbConnection::rollbackTransaction(const KDbTransaction trans, KDbTransaction::CommitOptions options) { if (!isDatabaseUsed()) return false; if (!d->driver->transactionsSupported() && !(d->driver->behavior()->features & KDbDriver::IgnoreTransactions)) { SET_ERR_TRANS_NOT_SUPP; return false; } KDbTransaction t = trans; if (!t.isActive()) { //try default tr. if (!d->default_trans.isActive()) { if (options & KDbTransaction::CommitOption::IgnoreInactive) { return true; } clearResult(); m_result = KDbResult(ERR_NO_TRANSACTION_ACTIVE, tr("Transaction not started.")); return false; } t = d->default_trans; d->default_trans = KDbTransaction(); //now: no default tr. } bool ret = true; if (!(d->driver->behavior()->features & KDbDriver::IgnoreTransactions)) ret = drv_rollbackTransaction(t.m_data); if (t.m_data) t.m_data->setActive(false); //now this transaction if inactive if (!d->dontRemoveTransactions) //true=transaction obj will be later removed from list d->transactions.removeAt(d->transactions.indexOf(t)); if (!ret && !m_result.isError()) m_result = KDbResult(ERR_ROLLBACK_OR_COMMIT_TRANSACTION, tr("Error on rollback transaction.")); return ret; } #undef SET_ERR_TRANS_NOT_SUPP #undef SET_BEGIN_TR_ERROR /*bool KDbConnection::duringTransaction() { return drv_duringTransaction(); }*/ KDbTransaction KDbConnection::defaultTransaction() const { return d->default_trans; } void KDbConnection::setDefaultTransaction(const KDbTransaction& trans) { if (!isDatabaseUsed()) return; if (!(d->driver->behavior()->features & KDbDriver::IgnoreTransactions) && (!trans.isActive() || !d->driver->transactionsSupported())) { return; } d->default_trans = trans; } QList KDbConnection::transactions() { return d->transactions; } bool KDbConnection::autoCommit() const { return d->autoCommit; } bool KDbConnection::setAutoCommit(bool on) { if (d->autoCommit == on || d->driver->behavior()->features & KDbDriver::IgnoreTransactions) return true; if (!drv_setAutoCommit(on)) return false; d->autoCommit = on; return true; } KDbTransactionData* KDbConnection::drv_beginTransaction() { if (!executeSql(KDbEscapedString("BEGIN"))) return nullptr; return new KDbTransactionData(this); } bool KDbConnection::drv_commitTransaction(KDbTransactionData *) { return executeSql(KDbEscapedString("COMMIT")); } bool KDbConnection::drv_rollbackTransaction(KDbTransactionData *) { return executeSql(KDbEscapedString("ROLLBACK")); } bool KDbConnection::drv_setAutoCommit(bool /*on*/) { return true; } KDbCursor* KDbConnection::executeQuery(const KDbEscapedString& sql, KDbCursor::Options options) { if (sql.isEmpty()) return nullptr; KDbCursor *c = prepareQuery(sql, options); if (!c) return nullptr; if (!c->open()) {//err - kill that m_result = c->result(); CursorDeleter deleter(c); return nullptr; } return c; } KDbCursor* KDbConnection::executeQuery(KDbQuerySchema* query, const QList& params, KDbCursor::Options options) { KDbCursor *c = prepareQuery(query, params, options); if (!c) return nullptr; if (!c->open()) {//err - kill that m_result = c->result(); CursorDeleter deleter(c); return nullptr; } return c; } KDbCursor* KDbConnection::executeQuery(KDbQuerySchema* query, KDbCursor::Options options) { return executeQuery(query, QList(), options); } KDbCursor* KDbConnection::executeQuery(KDbTableSchema* table, KDbCursor::Options options) { return executeQuery(table->query(), options); } KDbCursor* KDbConnection::prepareQuery(KDbTableSchema* table, KDbCursor::Options options) { return prepareQuery(table->query(), options); } KDbCursor* KDbConnection::prepareQuery(KDbQuerySchema* query, const QList& params, KDbCursor::Options options) { KDbCursor* cursor = prepareQuery(query, options); if (cursor) cursor->setQueryParameters(params); return cursor; } bool KDbConnection::deleteCursor(KDbCursor *cursor) { if (!cursor) return false; if (cursor->connection() != this) {//illegal call kdbWarning() << "Could not delete the cursor not owned by the same connection!"; return false; } const bool ret = cursor->close(); CursorDeleter deleter(cursor); return ret; } //! @todo IMPORTANT: fix KDbConnection::setupObjectData() after refactoring bool KDbConnection::setupObjectData(const KDbRecordData &data, KDbObject *object) { if (data.count() < 5) { kdbWarning() << "Aborting, object data should have at least 5 elements, found" << data.count(); return false; } bool ok; const int id = data[0].toInt(&ok); if (!ok) return false; object->setId(id); const QString name(data[2].toString()); if (!KDb::isIdentifier(name)) { m_result = KDbResult(ERR_INVALID_IDENTIFIER, tr("Invalid object name \"%1\".").arg(name)); return false; } object->setName(name); object->setCaption(data[3].toString()); object->setDescription(data[4].toString()); // kdbDebug()<<"@@@ KDbConnection::setupObjectData() == " << sdata.schemaDataDebugString(); return true; } tristate KDbConnection::loadObjectData(int type, int id, KDbObject* object) { KDbRecordData data; if (type == KDb::AnyObjectType) { if (true != querySingleRecord(KDbEscapedString("SELECT o_id, o_type, o_name, o_caption, " "o_desc FROM kexi__objects WHERE o_id=%1") .arg(d->driver->valueToSql(KDbField::Integer, id)), &data)) { return cancelled; } } else { if (true != querySingleRecord(KDbEscapedString("SELECT o_id, o_type, o_name, o_caption, o_desc " "FROM kexi__objects WHERE o_type=%1 AND o_id=%1") .arg(d->driver->valueToSql(KDbField::Integer, type)) .arg(d->driver->valueToSql(KDbField::Integer, id)), &data)) { return cancelled; } } return setupObjectData(data, object); } tristate KDbConnection::loadObjectData(int type, const QString& name, KDbObject* object) { KDbRecordData data; if (true != querySingleRecord( KDbEscapedString("SELECT o_id, o_type, o_name, o_caption, o_desc " "FROM kexi__objects WHERE o_type=%1 AND o_name=%2") .arg(d->driver->valueToSql(KDbField::Integer, type)) .arg(escapeString(name)), &data)) { return cancelled; } return setupObjectData(data, object); } bool KDbConnection::storeObjectDataInternal(KDbObject* object, bool newObject) { KDbTableSchema *ts = d->table(QLatin1String("kexi__objects")); if (!ts) return false; if (newObject) { int existingID; if (true == querySingleNumber( KDbEscapedString("SELECT o_id FROM kexi__objects WHERE o_type=%1 AND o_name=%2") .arg(d->driver->valueToSql(KDbField::Integer, object->type())) .arg(escapeString(object->name())), &existingID)) { //we already have stored an object data with the same name and type: //just update it's properties as it would be existing object object->setId(existingID); newObject = false; } } if (newObject) { if (object->id() <= 0) {//get new ID QScopedPointer fl(ts->subList( QList() << "o_type" << "o_name" << "o_caption" << "o_desc")); if (!fl) { return false; } QSharedPointer result = insertRecord(fl.data(), QVariant(object->type()), QVariant(object->name()), QVariant(object->caption()), QVariant(object->description())); if (!result) { return false; } //fetch newly assigned ID //! @todo safe to cast it? quint64 obj_id = KDb::lastInsertedAutoIncValue(result, QLatin1String("o_id"), *ts); //kdbDebug() << "NEW obj_id == " << obj_id; if (obj_id == std::numeric_limits::max()) { return false; } object->setId(obj_id); return true; } else { QScopedPointer fl(ts->subList( QList() << "o_id" << "o_type" << "o_name" << "o_caption" << "o_desc")); return fl && insertRecord(fl.data(), QVariant(object->id()), QVariant(object->type()), QVariant(object->name()), QVariant(object->caption()), QVariant(object->description())); } } //existing object: return executeSql( KDbEscapedString("UPDATE kexi__objects SET o_type=%2, o_caption=%3, o_desc=%4 WHERE o_id=%1") .arg(d->driver->valueToSql(KDbField::Integer, object->id())) .arg(d->driver->valueToSql(KDbField::Integer, object->type())) .arg(escapeString(object->caption())) .arg(escapeString(object->description()))); } bool KDbConnection::storeObjectData(KDbObject* object) { return storeObjectDataInternal(object, false); } bool KDbConnection::storeNewObjectData(KDbObject* object) { return storeObjectDataInternal(object, true); } QString KDbConnection::escapeIdentifier(const QString& id, KDb::IdentifierEscapingType escapingType) const { return escapingType == KDb::KDbEscaping ? KDb::escapeIdentifier(id) : escapeIdentifier(id); } KDbCursor* KDbConnection::executeQueryInternal(const KDbEscapedString& sql, KDbQuerySchema* query, const QList* params) { Q_ASSERT(!sql.isEmpty() || query); clearResult(); if (!sql.isEmpty()) { return executeQuery(sql); } if (!query) { return nullptr; } if (params) { return executeQuery(query, *params); } return executeQuery(query); } tristate KDbConnection::querySingleRecordInternal(KDbRecordData *data, const KDbEscapedString *sql, KDbQuerySchema *query, const QList *params, QueryRecordOptions options) { Q_ASSERT(sql || query); if (sql) { //! @todo does not work with non-SQL data sources m_result.setSql(d->driver->addLimitTo1(*sql, options & QueryRecordOption::AddLimitTo1)); } KDbCursor *cursor = executeQueryInternal(m_result.sql(), query, params); if (!cursor) { kdbWarning() << "!querySingleRecordInternal() " << m_result.sql(); return false; } if (!cursor->moveFirst() || cursor->eof() || !cursor->storeCurrentRecord(data)) { const tristate result = cursor->result().isError() ? tristate(false) : tristate(cancelled); // kdbDebug() << "!cursor->moveFirst() || cursor->eof() || cursor->storeCurrentRecord(data) // " // "m_result.sql()=" << m_result.sql(); m_result = cursor->result(); deleteCursor(cursor); return result; } return deleteCursor(cursor); } tristate KDbConnection::querySingleRecord(const KDbEscapedString &sql, KDbRecordData *data, QueryRecordOptions options) { return querySingleRecordInternal(data, &sql, nullptr, nullptr, options); } tristate KDbConnection::querySingleRecord(KDbQuerySchema *query, KDbRecordData *data, QueryRecordOptions options) { return querySingleRecordInternal(data, nullptr, query, nullptr, options); } tristate KDbConnection::querySingleRecord(KDbQuerySchema *query, KDbRecordData *data, const QList ¶ms, QueryRecordOptions options) { return querySingleRecordInternal(data, nullptr, query, ¶ms, options); } bool KDbConnection::checkIfColumnExists(KDbCursor *cursor, int column) { if (column >= cursor->fieldCount()) { m_result = KDbResult(ERR_CURSOR_RECORD_FETCHING, tr("Column \"%1\" does not exist in the query.").arg(column)); return false; } return true; } tristate KDbConnection::querySingleStringInternal(const KDbEscapedString *sql, QString *value, KDbQuerySchema *query, const QList *params, int column, QueryRecordOptions options) { Q_ASSERT(sql || query); if (sql) { //! @todo does not work with non-SQL data sources m_result.setSql(d->driver->addLimitTo1(*sql, options & QueryRecordOption::AddLimitTo1)); } KDbCursor *cursor = executeQueryInternal(m_result.sql(), query, params); if (!cursor) { kdbWarning() << "!querySingleStringInternal()" << m_result.sql(); return false; } if (!cursor->moveFirst() || cursor->eof()) { const tristate result = cursor->result().isError() ? tristate(false) : tristate(cancelled); // kdbDebug() << "!cursor->moveFirst() || cursor->eof()" << m_result.sql(); deleteCursor(cursor); return result; } if (!checkIfColumnExists(cursor, column)) { deleteCursor(cursor); return false; } if (value) { *value = cursor->value(column).toString(); } return deleteCursor(cursor); } tristate KDbConnection::querySingleString(const KDbEscapedString &sql, QString *value, int column, QueryRecordOptions options) { return querySingleStringInternal(&sql, value, nullptr, nullptr, column, options); } tristate KDbConnection::querySingleString(KDbQuerySchema *query, QString *value, int column, QueryRecordOptions options) { return querySingleStringInternal(nullptr, value, query, nullptr, column, options); } tristate KDbConnection::querySingleString(KDbQuerySchema *query, QString *value, const QList ¶ms, int column, QueryRecordOptions options) { return querySingleStringInternal(nullptr, value, query, ¶ms, column, options); } tristate KDbConnection::querySingleNumberInternal(const KDbEscapedString *sql, int *number, KDbQuerySchema *query, const QList *params, int column, QueryRecordOptions options) { QString str; const tristate result = querySingleStringInternal(sql, &str, query, params, column, options); if (result != true) return result; bool ok; const int _number = str.toInt(&ok); if (!ok) return false; if (number) { *number = _number; } return true; } tristate KDbConnection::querySingleNumber(const KDbEscapedString &sql, int *number, int column, QueryRecordOptions options) { return querySingleNumberInternal(&sql, number, nullptr, nullptr, column, options); } tristate KDbConnection::querySingleNumber(KDbQuerySchema *query, int *number, int column, QueryRecordOptions options) { return querySingleNumberInternal(nullptr, number, query, nullptr, column, options); } tristate KDbConnection::querySingleNumber(KDbQuerySchema *query, int *number, const QList ¶ms, int column, QueryRecordOptions options) { return querySingleNumberInternal(nullptr, number, query, ¶ms, column, options); } bool KDbConnection::queryStringListInternal(const KDbEscapedString *sql, QStringList *list, KDbQuerySchema *query, const QList *params, int column, bool (*filterFunction)(const QString &)) { if (sql) { m_result.setSql(*sql); } KDbCursor *cursor = executeQueryInternal(m_result.sql(), query, params); if (!cursor) { kdbWarning() << "!queryStringListInternal() " << m_result.sql(); return false; } cursor->moveFirst(); if (cursor->result().isError()) { m_result = cursor->result(); deleteCursor(cursor); return false; } if (!cursor->eof() && !checkIfColumnExists(cursor, column)) { deleteCursor(cursor); return false; } if (list) { list->clear(); } QStringList listResult; while (!cursor->eof()) { const QString str(cursor->value(column).toString()); if (!filterFunction || filterFunction(str)) { listResult.append(str); } if (!cursor->moveNext() && cursor->result().isError()) { m_result = cursor->result(); deleteCursor(cursor); return false; } } if (list) { *list = listResult; } return deleteCursor(cursor); } bool KDbConnection::queryStringList(const KDbEscapedString& sql, QStringList* list, int column) { return queryStringListInternal(&sql, list, nullptr, nullptr, column, nullptr); } bool KDbConnection::queryStringList(KDbQuerySchema* query, QStringList* list, int column) { return queryStringListInternal(nullptr, list, query, nullptr, column, nullptr); } bool KDbConnection::queryStringList(KDbQuerySchema* query, QStringList* list, const QList& params, int column) { return queryStringListInternal(nullptr, list, query, ¶ms, column, nullptr); } tristate KDbConnection::resultExists(const KDbEscapedString &sql, QueryRecordOptions options) { // optimization if (d->driver->behavior()->SELECT_1_SUBQUERY_SUPPORTED) { // this is at least for sqlite if ((options & QueryRecordOption::AddLimitTo1) && sql.left(6).toUpper() == "SELECT") { m_result.setSql(d->driver->addLimitTo1("SELECT 1 FROM (" + sql + ')')); } else { m_result.setSql(sql); } } else { if ((options & QueryRecordOption::AddLimitTo1) && sql.startsWith("SELECT")) { m_result.setSql(d->driver->addLimitTo1(sql)); } else { m_result.setSql(sql); } } KDbCursor *cursor = executeQuery(m_result.sql()); if (!cursor) { kdbWarning() << "!executeQuery()" << m_result.sql(); return cancelled; } if (!cursor->moveFirst() || cursor->eof()) { kdbWarning() << "!cursor->moveFirst() || cursor->eof()" << m_result.sql(); m_result = cursor->result(); deleteCursor(cursor); return m_result.isError() ? cancelled : tristate(false); } return deleteCursor(cursor) ? tristate(true) : cancelled; } tristate KDbConnection::isEmpty(KDbTableSchema* table) { - const KDbNativeStatementBuilder builder(this); + const KDbNativeStatementBuilder builder(this, KDb::DriverEscaping); KDbEscapedString sql; if (!builder.generateSelectStatement(&sql, table)) { return cancelled; } const tristate result = resultExists(sql); if (~result) { return cancelled; } return result == false; } //! Used by addFieldPropertyToExtendedTableSchemaData() static void createExtendedTableSchemaMainElementIfNeeded( QDomDocument* doc, QDomElement* extendedTableSchemaMainEl, bool* extendedTableSchemaStringIsEmpty) { if (!*extendedTableSchemaStringIsEmpty) return; //init document *extendedTableSchemaMainEl = doc->createElement(QLatin1String("EXTENDED_TABLE_SCHEMA")); doc->appendChild(*extendedTableSchemaMainEl); extendedTableSchemaMainEl->setAttribute(QLatin1String("version"), QString::number(KDB_EXTENDED_TABLE_SCHEMA_VERSION)); *extendedTableSchemaStringIsEmpty = false; } //! Used by addFieldPropertyToExtendedTableSchemaData() static void createExtendedTableSchemaFieldElementIfNeeded(QDomDocument* doc, QDomElement* extendedTableSchemaMainEl, const QString& fieldName, QDomElement* extendedTableSchemaFieldEl, bool append = true) { if (!extendedTableSchemaFieldEl->isNull()) return; *extendedTableSchemaFieldEl = doc->createElement(QLatin1String("field")); if (append) extendedTableSchemaMainEl->appendChild(*extendedTableSchemaFieldEl); extendedTableSchemaFieldEl->setAttribute(QLatin1String("name"), fieldName); } /*! @internal used by storeExtendedTableSchemaData() Creates DOM node for @a propertyName and @a propertyValue. Creates enclosing EXTENDED_TABLE_SCHEMA element if EXTENDED_TABLE_SCHEMA is true. Updates extendedTableSchemaStringIsEmpty and extendedTableSchemaMainEl afterwards. If extendedTableSchemaFieldEl is null, creates element (with optional "custom" attribute is @a custom is false). */ static void addFieldPropertyToExtendedTableSchemaData( const KDbField& f, const QByteArray &propertyName, const QVariant& propertyValue, QDomDocument* doc, QDomElement* extendedTableSchemaMainEl, QDomElement* extendedTableSchemaFieldEl, bool* extendedTableSchemaStringIsEmpty, bool custom = false) { createExtendedTableSchemaMainElementIfNeeded(doc, extendedTableSchemaMainEl, extendedTableSchemaStringIsEmpty); createExtendedTableSchemaFieldElementIfNeeded( doc, extendedTableSchemaMainEl, f.name(), extendedTableSchemaFieldEl); //create QDomElement extendedTableSchemaFieldPropertyEl = doc->createElement(QLatin1String("property")); extendedTableSchemaFieldEl->appendChild(extendedTableSchemaFieldPropertyEl); if (custom) extendedTableSchemaFieldPropertyEl.setAttribute(QLatin1String("custom"), QLatin1String("true")); extendedTableSchemaFieldPropertyEl.setAttribute(QLatin1String("name"), QLatin1String(propertyName)); QDomElement extendedTableSchemaFieldPropertyValueEl; switch (propertyValue.type()) { case QVariant::String: extendedTableSchemaFieldPropertyValueEl = doc->createElement(QLatin1String("string")); break; case QVariant::ByteArray: extendedTableSchemaFieldPropertyValueEl = doc->createElement(QLatin1String("cstring")); break; case QVariant::Int: case QVariant::Double: case QVariant::UInt: case QVariant::LongLong: case QVariant::ULongLong: extendedTableSchemaFieldPropertyValueEl = doc->createElement(QLatin1String("number")); break; case QVariant::Bool: extendedTableSchemaFieldPropertyValueEl = doc->createElement(QLatin1String("bool")); break; default: //! @todo add more QVariant types kdbCritical() << "addFieldPropertyToExtendedTableSchemaData(): impl. error"; } extendedTableSchemaFieldPropertyEl.appendChild(extendedTableSchemaFieldPropertyValueEl); extendedTableSchemaFieldPropertyValueEl.appendChild( doc->createTextNode(propertyValue.toString())); } bool KDbConnection::storeExtendedTableSchemaData(KDbTableSchema* tableSchema) { //! @todo future: save in older versions if neeed QDomDocument doc(QLatin1String("EXTENDED_TABLE_SCHEMA")); QDomElement extendedTableSchemaMainEl; bool extendedTableSchemaStringIsEmpty = true; //for each field: foreach(KDbField* f, *tableSchema->fields()) { QDomElement extendedTableSchemaFieldEl; const KDbField::Type type = f->type(); // cache: evaluating type of expressions can be expensive if (f->visibleDecimalPlaces() >= 0/*nondefault*/ && KDb::supportsVisibleDecimalPlacesProperty(type)) { addFieldPropertyToExtendedTableSchemaData( *f, "visibleDecimalPlaces", f->visibleDecimalPlaces(), &doc, &extendedTableSchemaMainEl, &extendedTableSchemaFieldEl, &extendedTableSchemaStringIsEmpty); } if (type == KDbField::Text) { if (f->maxLengthStrategy() == KDbField::DefaultMaxLength) { addFieldPropertyToExtendedTableSchemaData( *f, "maxLengthIsDefault", true, &doc, &extendedTableSchemaMainEl, &extendedTableSchemaFieldEl, &extendedTableSchemaStringIsEmpty); } } // boolean field with "not null" // add custom properties const KDbField::CustomPropertiesMap customProperties(f->customProperties()); for (KDbField::CustomPropertiesMap::ConstIterator itCustom = customProperties.constBegin(); itCustom != customProperties.constEnd(); ++itCustom) { addFieldPropertyToExtendedTableSchemaData( *f, itCustom.key(), itCustom.value(), &doc, &extendedTableSchemaMainEl, &extendedTableSchemaFieldEl, &extendedTableSchemaStringIsEmpty, /*custom*/true); } // save lookup table specification, if present KDbLookupFieldSchema *lookupFieldSchema = tableSchema->lookupFieldSchema(*f); if (lookupFieldSchema) { createExtendedTableSchemaFieldElementIfNeeded( &doc, &extendedTableSchemaMainEl, f->name(), &extendedTableSchemaFieldEl, false/* !append */); lookupFieldSchema->saveToDom(&doc, &extendedTableSchemaFieldEl); if (extendedTableSchemaFieldEl.hasChildNodes()) { // this element provides the definition, so let's append it now createExtendedTableSchemaMainElementIfNeeded(&doc, &extendedTableSchemaMainEl, &extendedTableSchemaStringIsEmpty); extendedTableSchemaMainEl.appendChild(extendedTableSchemaFieldEl); } } } // Store extended schema information (see ExtendedTableSchemaInformation in Kexi Wiki) if (extendedTableSchemaStringIsEmpty) { #ifdef KDB_DEBUG_GUI KDb::alterTableActionDebugGUI(QLatin1String("** Extended table schema REMOVED.")); #endif if (!removeDataBlock(tableSchema->id(), QLatin1String("extended_schema"))) return false; } else { #ifdef KDB_DEBUG_GUI KDb::alterTableActionDebugGUI( QLatin1String("** Extended table schema set to:\n") + doc.toString(4)); #endif if (!storeDataBlock(tableSchema->id(), doc.toString(1), QLatin1String("extended_schema"))) return false; } return true; } bool KDbConnection::loadExtendedTableSchemaData(KDbTableSchema* tableSchema) { #define loadExtendedTableSchemaData_ERR \ { m_result = KDbResult(tr("Error while loading extended table schema.", \ "Extended schema for a table: loading error")); \ return false; } #define loadExtendedTableSchemaData_ERR2(details) \ { m_result = KDbResult(details); \ m_result.setMessageTitle(tr("Error while loading extended table schema.", \ "Extended schema for a table: loading error")); \ return false; } #define loadExtendedTableSchemaData_ERR3(data) \ { m_result = KDbResult(tr("Invalid XML data: %1").arg(data.left(1024))); \ m_result.setMessageTitle(tr("Error while loading extended table schema.", \ "Extended schema for a table: loading error")); \ return false; } // Load extended schema information, if present (see ExtendedTableSchemaInformation in Kexi Wiki) QString extendedTableSchemaString; tristate res = loadDataBlock(tableSchema->id(), &extendedTableSchemaString, QLatin1String("extended_schema")); if (!res) loadExtendedTableSchemaData_ERR; // extendedTableSchemaString will be just empty if there is no such data block if (extendedTableSchemaString.isEmpty()) return true; QDomDocument doc; QString errorMsg; int errorLine, errorColumn; if (!doc.setContent(extendedTableSchemaString, &errorMsg, &errorLine, &errorColumn)) { loadExtendedTableSchemaData_ERR2( tr("Error in XML data: \"%1\" in line %2, column %3.\nXML data: %4") .arg(errorMsg).arg(errorLine).arg(errorColumn).arg(extendedTableSchemaString.left(1024))); } //! @todo look at the current format version (KDB_EXTENDED_TABLE_SCHEMA_VERSION) if (doc.doctype().name() != QLatin1String("EXTENDED_TABLE_SCHEMA")) loadExtendedTableSchemaData_ERR3(extendedTableSchemaString); QDomElement docEl = doc.documentElement(); if (docEl.tagName() != QLatin1String("EXTENDED_TABLE_SCHEMA")) loadExtendedTableSchemaData_ERR3(extendedTableSchemaString); for (QDomNode n = docEl.firstChild(); !n.isNull(); n = n.nextSibling()) { QDomElement fieldEl = n.toElement(); if (fieldEl.tagName() == QLatin1String("field")) { KDbField *f = tableSchema->field(fieldEl.attribute(QLatin1String("name"))); if (f) { //set properties of the field: //! @todo more properties for (QDomNode propNode = fieldEl.firstChild(); !propNode.isNull(); propNode = propNode.nextSibling()) { const QDomElement propEl = propNode.toElement(); bool ok; int intValue; if (propEl.tagName() == QLatin1String("property")) { QByteArray propertyName = propEl.attribute(QLatin1String("name")).toLatin1(); if (propEl.attribute(QLatin1String("custom")) == QLatin1String("true")) { //custom property const QVariant v(KDb::loadPropertyValueFromDom(propEl.firstChild(), &ok)); if (ok) { f->setCustomProperty(propertyName, v); } } else if (propertyName == "visibleDecimalPlaces") { if (KDb::supportsVisibleDecimalPlacesProperty(f->type())) { intValue = KDb::loadIntPropertyValueFromDom(propEl.firstChild(), &ok); if (ok) f->setVisibleDecimalPlaces(intValue); } } else if (propertyName == "maxLengthIsDefault") { if (f->type() == KDbField::Text) { const bool maxLengthIsDefault = KDb::loadPropertyValueFromDom(propEl.firstChild(), &ok).toBool(); if (ok) { f->setMaxLengthStrategy( maxLengthIsDefault ? KDbField::DefaultMaxLength : KDbField::DefinedMaxLength); } } } //! @todo more properties... } else if (propEl.tagName() == QLatin1String("lookup-column")) { KDbLookupFieldSchema *lookupFieldSchema = KDbLookupFieldSchema::loadFromDom(propEl); if (lookupFieldSchema) { kdbDebug() << f->name() << *lookupFieldSchema; tableSchema->setLookupFieldSchema(f->name(), lookupFieldSchema); } } } } else { kdbWarning() << "no such field:" << fieldEl.attribute(QLatin1String("name")) << "in table:" << tableSchema->name(); } } } return true; } KDbField* KDbConnection::setupField(const KDbRecordData &data) { bool ok = true; int f_int_type = data.at(1).toInt(&ok); if (f_int_type <= KDbField::InvalidType || f_int_type > KDbField::LastType) ok = false; if (!ok) return nullptr; KDbField::Type f_type = (KDbField::Type)f_int_type; const int f_len = qMax(0, data.at(3).toInt(&ok)); // defined limit if (!ok) { return nullptr; } //! @todo load maxLengthStrategy info to see if the maxLength is the default int f_prec = data.at(4).toInt(&ok); if (!ok) return nullptr; KDbField::Constraints f_constr = (KDbField::Constraints)data.at(5).toInt(&ok); if (!ok) return nullptr; KDbField::Options f_opts = (KDbField::Options)data.at(6).toInt(&ok); if (!ok) return nullptr; QString name(data.at(2).toString()); if (!KDb::isIdentifier(name)) { name = KDb::stringToIdentifier(name); } KDbField *f = new KDbField( name, f_type, f_constr, f_opts, f_len, f_prec); QVariant defaultVariant = data.at(7); if (defaultVariant.isValid()) { defaultVariant = KDb::stringToVariant(defaultVariant.toString(), KDbField::variantType(f_type), &ok); if (ok) { f->setDefaultValue(defaultVariant); } else { kdbWarning() << "problem with KDb::stringToVariant(" << defaultVariant << ')'; ok = true; //problem with defaultValue is not critical } } f->setCaption(data.at(9).toString()); f->setDescription(data.at(10).toString()); return f; } KDbTableSchema* KDbConnection::tableSchema(const QString& tableName) { KDbTableSchema *t = d->table(tableName); if (t || tableName.isEmpty()) { return t; } //not found: retrieve schema QScopedPointer newTable(new KDbTableSchema); clearResult(); if (true != loadObjectData(KDb::TableObjectType, tableName, newTable.data())) { return nullptr; } return d->setupTableSchema(newTable.take()); } KDbTableSchema* KDbConnection::tableSchema(int tableId) { KDbTableSchema *t = d->table(tableId); if (t) return t; //not found: retrieve schema QScopedPointer newTable(new KDbTableSchema); clearResult(); if (true != loadObjectData(KDb::TableObjectType, tableId, newTable.data())) { return nullptr; } return d->setupTableSchema(newTable.take()); } tristate KDbConnection::loadDataBlock(int objectID, QString* dataString, const QString& dataID) { if (objectID <= 0) return false; return querySingleString( KDbEscapedString("SELECT o_data FROM kexi__objectdata WHERE o_id=%1 AND ") .arg(d->driver->valueToSql(KDbField::Integer, objectID)) + KDbEscapedString(KDb::sqlWhere(d->driver, KDbField::Text, QLatin1String("o_sub_id"), dataID.isEmpty() ? QVariant() : QVariant(dataID))), dataString); } bool KDbConnection::storeDataBlock(int objectID, const QString &dataString, const QString& dataID) { if (objectID <= 0) return false; KDbEscapedString sql( KDbEscapedString("SELECT kexi__objectdata.o_id FROM kexi__objectdata WHERE o_id=%1") .arg(d->driver->valueToSql(KDbField::Integer, objectID))); KDbEscapedString sql_sub(KDb::sqlWhere(d->driver, KDbField::Text, QLatin1String("o_sub_id"), dataID.isEmpty() ? QVariant() : QVariant(dataID))); const tristate result = resultExists(sql + " AND " + sql_sub); if (~result) { return false; } if (result == true) { return executeSql(KDbEscapedString("UPDATE kexi__objectdata SET o_data=%1 WHERE o_id=%2 AND ") .arg(d->driver->valueToSql(KDbField::LongText, dataString)) .arg(d->driver->valueToSql(KDbField::Integer, objectID)) + sql_sub); } return executeSql( KDbEscapedString("INSERT INTO kexi__objectdata (o_id, o_data, o_sub_id) VALUES (") + KDbEscapedString::number(objectID) + ',' + d->driver->valueToSql(KDbField::LongText, dataString) + ',' + d->driver->valueToSql(KDbField::Text, dataID) + ')'); } bool KDbConnection::copyDataBlock(int sourceObjectID, int destObjectID, const QString &dataID) { if (sourceObjectID <= 0 || destObjectID <= 0) return false; if (sourceObjectID == destObjectID) return true; if (!removeDataBlock(destObjectID, dataID)) // remove before copying return false; KDbEscapedString sql = KDbEscapedString( "INSERT INTO kexi__objectdata SELECT %1, t.o_data, t.o_sub_id " "FROM kexi__objectdata AS t WHERE o_id=%2") .arg(d->driver->valueToSql(KDbField::Integer, destObjectID)) .arg(d->driver->valueToSql(KDbField::Integer, sourceObjectID)); if (!dataID.isEmpty()) { sql += KDbEscapedString(" AND ") + KDb::sqlWhere(d->driver, KDbField::Text, QLatin1String("o_sub_id"), dataID); } return executeSql(sql); } bool KDbConnection::removeDataBlock(int objectID, const QString& dataID) { if (objectID <= 0) return false; if (dataID.isEmpty()) return KDb::deleteRecords(this, QLatin1String("kexi__objectdata"), QLatin1String("o_id"), QString::number(objectID)); else return KDb::deleteRecords(this, QLatin1String("kexi__objectdata"), QLatin1String("o_id"), KDbField::Integer, objectID, QLatin1String("o_sub_id"), KDbField::Text, dataID); } KDbQuerySchema* KDbConnection::querySchema(const QString& aQueryName) { QString queryName = aQueryName.toLower(); KDbQuerySchema *q = d->query(queryName); if (q || queryName.isEmpty()) { return q; } //not found: retrieve schema QScopedPointer newQuery(new KDbQuerySchema); clearResult(); if (true != loadObjectData(KDb::QueryObjectType, aQueryName, newQuery.data())) { return nullptr; } return d->setupQuerySchema(newQuery.take()); } KDbQuerySchema* KDbConnection::querySchema(int queryId) { KDbQuerySchema *q = d->query(queryId); if (q) return q; //not found: retrieve schema QScopedPointer newQuery(new KDbQuerySchema); clearResult(); if (true != loadObjectData(KDb::QueryObjectType, queryId, newQuery.data())) { return nullptr; } return d->setupQuerySchema(newQuery.take()); } bool KDbConnection::setQuerySchemaObsolete(const QString& queryName) { KDbQuerySchema* oldQuery = querySchema(queryName); if (!oldQuery) return false; d->setQueryObsolete(oldQuery); return true; } QString KDbConnection::escapeIdentifier(const QString& id) const { return d->driver->escapeIdentifier(id); } bool KDbConnection::isInternalTableSchema(const QString& tableName) { KDbTableSchema* schema = d->table(tableName); return (schema && schema->isInternal()) // these are here for compatiblility because we're no longer instantiate // them but can exist in projects created with previous Kexi versions: || tableName == QLatin1String("kexi__final") || tableName == QLatin1String("kexi__useractions"); } void KDbConnection::removeMe(KDbTableSchema *table) { if (table && d) { d->takeTable(table); } } QString KDbConnection::anyAvailableDatabaseName() { if (!d->availableDatabaseName.isEmpty()) { return d->availableDatabaseName; } return d->driver->behavior()->ALWAYS_AVAILABLE_DATABASE_NAME; } void KDbConnection::setAvailableDatabaseName(const QString& dbName) { d->availableDatabaseName = dbName; } //! @internal used in updateRecord(), insertRecord(), inline static void updateRecordDataWithNewValues( KDbConnection *conn, KDbQuerySchema* query, KDbRecordData* data, const KDbRecordEditBuffer::DbHash& b, QHash* columnsOrderExpanded) { *columnsOrderExpanded = query->columnsOrder(conn, KDbQuerySchema::ColumnsOrderMode::ExpandedList); QHash::ConstIterator columnsOrderExpandedIt; for (KDbRecordEditBuffer::DbHash::ConstIterator it = b.constBegin();it != b.constEnd();++it) { columnsOrderExpandedIt = columnsOrderExpanded->constFind(it.key()); if (columnsOrderExpandedIt == columnsOrderExpanded->constEnd()) { kdbWarning() << "(KDbConnection) \"now also assign new value in memory\" step" "- could not find item" << it.key()->aliasOrName(); continue; } (*data)[ columnsOrderExpandedIt.value() ] = it.value(); } } bool KDbConnection::updateRecord(KDbQuerySchema* query, KDbRecordData* data, KDbRecordEditBuffer* buf, bool useRecordId) { // Each SQL identifier needs to be escaped in the generated query. // kdbDebug() << *query; clearResult(); //--get PKEY if (buf->dbBuffer().isEmpty()) { kdbDebug() << " -- NO CHANGES DATA!"; return true; } KDbTableSchema *mt = query->masterTable(); if (!mt) { kdbWarning() << " -- NO MASTER TABLE!"; m_result = KDbResult(ERR_UPDATE_NO_MASTER_TABLE, tr("Could not update record because there is no master table defined.")); return false; } KDbIndexSchema *pkey = (mt->primaryKey() && !mt->primaryKey()->fields()->isEmpty()) ? mt->primaryKey() : nullptr; if (!useRecordId && !pkey) { kdbWarning() << " -- NO MASTER TABLE's PKEY!"; m_result = KDbResult(ERR_UPDATE_NO_MASTER_TABLES_PKEY, tr("Could not update record because master table has no primary key defined.")); //! @todo perhaps we can try to update without using PKEY? return false; } //update the record: KDbEscapedString sql; sql.reserve(4096); sql = KDbEscapedString("UPDATE ") + escapeIdentifier(mt->name()) + " SET "; KDbEscapedString sqlset, sqlwhere; sqlset.reserve(1024); sqlwhere.reserve(1024); KDbRecordEditBuffer::DbHash b = buf->dbBuffer(); //gather the fields which are updated ( have values in KDbRecordEditBuffer) KDbFieldList affectedFields; for (KDbRecordEditBuffer::DbHash::ConstIterator it = b.constBegin();it != b.constEnd();++it) { if (it.key()->field()->table() != mt) continue; // skip values for fields outside of the master table (e.g. a "visible value" of the lookup field) if (!sqlset.isEmpty()) sqlset += ','; KDbField* currentField = it.key()->field(); const bool affectedFieldsAddOk = affectedFields.addField(currentField); Q_ASSERT(affectedFieldsAddOk); sqlset += KDbEscapedString(escapeIdentifier(currentField->name())) + '=' + d->driver->valueToSql(currentField, it.value()); } if (pkey) { const QVector pkeyFieldsOrder(query->pkeyFieldsOrder(this)); //kdbDebug() << pkey->fieldCount() << " ? " << query->pkeyFieldCount(); if (pkey->fieldCount() != query->pkeyFieldCount(this)) { //sanity check kdbWarning() << " -- NO ENTIRE MASTER TABLE's PKEY SPECIFIED!"; m_result = KDbResult(ERR_UPDATE_NO_ENTIRE_MASTER_TABLES_PKEY, tr("Could not update record because it does not contain entire primary key of master table.")); return false; } if (!pkey->fields()->isEmpty()) { int i = 0; foreach(KDbField *f, *pkey->fields()) { if (!sqlwhere.isEmpty()) sqlwhere += " AND "; QVariant val(data->at(pkeyFieldsOrder.at(i))); if (val.isNull() || !val.isValid()) { m_result = KDbResult(ERR_UPDATE_NULL_PKEY_FIELD, tr("Primary key's field \"%1\" cannot be empty.").arg(f->name())); //js todo: pass the field's name somewhere! return false; } sqlwhere += KDbEscapedString(escapeIdentifier(f->name())) + '=' + d->driver->valueToSql(f, val); i++; } } } else { //use RecordId sqlwhere = KDbEscapedString(escapeIdentifier(d->driver->behavior()->ROW_ID_FIELD_NAME)) + '=' + d->driver->valueToSql(KDbField::BigInteger, (*data)[data->size() - 1]); } sql += (sqlset + " WHERE " + sqlwhere); //kdbDebug() << " -- SQL == " << ((sql.length() > 400) ? (sql.left(400) + "[.....]") : sql); // preprocessing before update if (!drv_beforeUpdate(mt->name(), &affectedFields)) return false; bool res = executeSql(sql); // postprocessing after update if (!drv_afterUpdate(mt->name(), &affectedFields)) return false; if (!res) { m_result = KDbResult(ERR_UPDATE_SERVER_ERROR, tr("Record updating on the server failed.")); return false; } //success: now also assign new values in memory: QHash columnsOrderExpanded; updateRecordDataWithNewValues(this, query, data, b, &columnsOrderExpanded); return true; } bool KDbConnection::insertRecord(KDbQuerySchema* query, KDbRecordData* data, KDbRecordEditBuffer* buf, bool getRecordId) { // Each SQL identifier needs to be escaped in the generated query. clearResult(); //--get PKEY /*disabled: there may be empty records (with autoinc) if (buf.dbBuffer().isEmpty()) { kdbDebug() << " -- NO CHANGES DATA!"; return true; }*/ KDbTableSchema *mt = query->masterTable(); if (!mt) { kdbWarning() << " -- NO MASTER TABLE!"; m_result = KDbResult(ERR_INSERT_NO_MASTER_TABLE, tr("Could not insert record because there is no master table specified.")); return false; } KDbIndexSchema *pkey = (mt->primaryKey() && !mt->primaryKey()->fields()->isEmpty()) ? mt->primaryKey() : nullptr; if (!getRecordId && !pkey) { kdbWarning() << " -- WARNING: NO MASTER TABLE's PKEY"; } KDbEscapedString sqlcols, sqlvals; sqlcols.reserve(1024); sqlvals.reserve(1024); //insert the record: KDbEscapedString sql; sql.reserve(4096); sql = KDbEscapedString("INSERT INTO ") + escapeIdentifier(mt->name()) + " ("; KDbRecordEditBuffer::DbHash b = buf->dbBuffer(); // add default values, if available (for any column without value explicitly set) const KDbQueryColumnInfo::Vector fieldsExpanded( query->fieldsExpanded(this, KDbQuerySchema::FieldsExpandedMode::Unique)); int fieldsExpandedCount = fieldsExpanded.count(); for (int i = 0; i < fieldsExpandedCount; i++) { KDbQueryColumnInfo *ci = fieldsExpanded.at(i); if (ci->field() && KDb::isDefaultValueAllowed(*ci->field()) && !ci->field()->defaultValue().isNull() && !b.contains(ci)) { //kdbDebug() << "adding default value" << ci->field->defaultValue().toString() << "for column" << ci->field->name(); b.insert(ci, ci->field()->defaultValue()); } } //collect fields which have values in KDbRecordEditBuffer KDbFieldList affectedFields; if (b.isEmpty()) { // empty record inserting requested: if (!getRecordId && !pkey) { kdbWarning() << "MASTER TABLE's PKEY REQUIRED FOR INSERTING EMPTY RECORDS: INSERT CANCELLED"; m_result = KDbResult(ERR_INSERT_NO_MASTER_TABLES_PKEY, tr("Could not insert record because master table has no primary key specified.")); return false; } if (pkey) { const QVector pkeyFieldsOrder(query->pkeyFieldsOrder(this)); // kdbDebug() << pkey->fieldCount() << " ? " << query->pkeyFieldCount(); if (pkey->fieldCount() != query->pkeyFieldCount(this)) { // sanity check kdbWarning() << "NO ENTIRE MASTER TABLE's PKEY SPECIFIED!"; m_result = KDbResult(ERR_INSERT_NO_ENTIRE_MASTER_TABLES_PKEY, tr("Could not insert record because it does not contain " "entire master table's primary key.")); return false; } } //at least one value is needed for VALUES section: find it and set to NULL: KDbField *anyField = mt->anyNonPKField(); if (!anyField) { if (!pkey) { kdbWarning() << "WARNING: NO FIELD AVAILABLE TO SET IT TO NULL"; return false; } else { //try to set NULL in pkey field (could not work for every SQL engine!) anyField = pkey->fields()->first(); } } sqlcols += escapeIdentifier(anyField->name()); sqlvals += d->driver->valueToSql(anyField, QVariant()/*NULL*/); const bool affectedFieldsAddOk = affectedFields.addField(anyField); Q_ASSERT(affectedFieldsAddOk); } else { // non-empty record inserting requested: for (KDbRecordEditBuffer::DbHash::ConstIterator it = b.constBegin();it != b.constEnd();++it) { if (it.key()->field()->table() != mt) continue; // skip values for fields outside of the master table (e.g. a "visible value" of the lookup field) if (!sqlcols.isEmpty()) { sqlcols += ','; sqlvals += ','; } KDbField* currentField = it.key()->field(); const bool affectedFieldsAddOk = affectedFields.addField(currentField); Q_ASSERT(affectedFieldsAddOk); sqlcols += escapeIdentifier(currentField->name()); sqlvals += d->driver->valueToSql(currentField, it.value()); } } sql += (sqlcols + ") VALUES (" + sqlvals + ')'); // kdbDebug() << " -- SQL == " << sql; // low-level insert QSharedPointer result = insertRecordInternal(mt->name(), &affectedFields, sql); if (!result) { m_result = KDbResult(ERR_INSERT_SERVER_ERROR, tr("Record inserting on the server failed.")); return false; } //success: now also assign a new value in memory: QHash columnsOrderExpanded; updateRecordDataWithNewValues(this, query, data, b, &columnsOrderExpanded); //fetch autoincremented values KDbQueryColumnInfo::List *aif_list = query->autoIncrementFields(this); quint64 recordId = 0; if (pkey && !aif_list->isEmpty()) { //! @todo now only if PKEY is present, this should also work when there's no PKEY KDbQueryColumnInfo *id_columnInfo = aif_list->first(); //! @todo safe to cast it? quint64 last_id = KDb::lastInsertedAutoIncValue(result, id_columnInfo->field()->name(), id_columnInfo->field()->table()->name(), &recordId); if (last_id == std::numeric_limits::max()) { //! @todo show error //! @todo remove just inserted record. How? Using ROLLBACK? return false; } KDbRecordData aif_data; KDbEscapedString getAutoIncForInsertedValue("SELECT " + query->autoIncrementSqlFieldsList(this) + " FROM " + escapeIdentifier(id_columnInfo->field()->table()->name()) + " WHERE " + escapeIdentifier(id_columnInfo->field()->name()) + '=' + QByteArray::number(last_id)); if (true != querySingleRecord(getAutoIncForInsertedValue, &aif_data)) { //! @todo show error return false; } int i = 0; foreach(KDbQueryColumnInfo *ci, *aif_list) { // kdbDebug() << "AUTOINCREMENTED FIELD" << fi->field->name() << "==" << aif_data[i].toInt(); ((*data)[ columnsOrderExpanded.value(ci)] = aif_data.value(i)).convert(ci->field()->variantType()); //cast to get proper type i++; } } else { recordId = result->lastInsertRecordId(); // kdbDebug() << "new recordId ==" << recordId; if (d->driver->behavior()->ROW_ID_FIELD_RETURNS_LAST_AUTOINCREMENTED_VALUE) { kdbWarning() << "d->driver->behavior()->ROW_ID_FIELD_RETURNS_LAST_AUTOINCREMENTED_VALUE"; return false; } } if (getRecordId && /*sanity check*/data->size() > fieldsExpanded.size()) { // kdbDebug() << "new ROWID ==" << ROWID; (*data)[data->size() - 1] = recordId; } return true; } bool KDbConnection::deleteRecord(KDbQuerySchema* query, KDbRecordData* data, bool useRecordId) { // Each SQL identifier needs to be escaped in the generated query. clearResult(); KDbTableSchema *mt = query->masterTable(); if (!mt) { kdbWarning() << " -- NO MASTER TABLE!"; m_result = KDbResult(ERR_DELETE_NO_MASTER_TABLE, tr("Could not delete record because there is no master table specified.")); return false; } KDbIndexSchema *pkey = (mt->primaryKey() && !mt->primaryKey()->fields()->isEmpty()) ? mt->primaryKey() : nullptr; //! @todo allow to delete from a table without pkey if (!useRecordId && !pkey) { kdbWarning() << " -- WARNING: NO MASTER TABLE's PKEY"; m_result = KDbResult(ERR_DELETE_NO_MASTER_TABLES_PKEY, tr("Could not delete record because there is no primary key for master table specified.")); return false; } //update the record: KDbEscapedString sql; sql.reserve(4096); sql = KDbEscapedString("DELETE FROM ") + escapeIdentifier(mt->name()) + " WHERE "; KDbEscapedString sqlwhere; sqlwhere.reserve(1024); if (pkey) { const QVector pkeyFieldsOrder(query->pkeyFieldsOrder(this)); //kdbDebug() << pkey->fieldCount() << " ? " << query->pkeyFieldCount(); if (pkey->fieldCount() != query->pkeyFieldCount(this)) { //sanity check kdbWarning() << " -- NO ENTIRE MASTER TABLE's PKEY SPECIFIED!"; m_result = KDbResult(ERR_DELETE_NO_ENTIRE_MASTER_TABLES_PKEY, tr("Could not delete record because it does not contain entire master table's primary key.")); return false; } int i = 0; foreach(KDbField *f, *pkey->fields()) { if (!sqlwhere.isEmpty()) sqlwhere += " AND "; QVariant val(data->at(pkeyFieldsOrder.at(i))); if (val.isNull() || !val.isValid()) { m_result = KDbResult(ERR_DELETE_NULL_PKEY_FIELD, tr("Primary key's field \"%1\" cannot be empty.").arg(f->name())); //js todo: pass the field's name somewhere! return false; } sqlwhere += KDbEscapedString(escapeIdentifier(f->name())) + '=' + d->driver->valueToSql(f, val); i++; } } else {//use RecordId sqlwhere = KDbEscapedString(escapeIdentifier(d->driver->behavior()->ROW_ID_FIELD_NAME)) + '=' + d->driver->valueToSql(KDbField::BigInteger, (*data)[data->size() - 1]); } sql += sqlwhere; //kdbDebug() << " -- SQL == " << sql; if (!executeSql(sql)) { m_result = KDbResult(ERR_DELETE_SERVER_ERROR, tr("Record deletion on the server failed.")); return false; } return true; } bool KDbConnection::deleteAllRecords(KDbQuerySchema* query) { clearResult(); KDbTableSchema *mt = query->masterTable(); if (!mt) { kdbWarning() << " -- NO MASTER TABLE!"; return false; } KDbIndexSchema *pkey = mt->primaryKey(); if (!pkey || pkey->fields()->isEmpty()) { kdbWarning() << "-- WARNING: NO MASTER TABLE's PKEY"; } KDbEscapedString sql = KDbEscapedString("DELETE FROM ") + escapeIdentifier(mt->name()); //kdbDebug() << "-- SQL == " << sql; if (!executeSql(sql)) { m_result = KDbResult(ERR_DELETE_SERVER_ERROR, tr("Record deletion on the server failed.")); return false; } return true; } int KDbConnection::recordCount(const KDbEscapedString& sql) { int count = -1; //will be changed only on success of querySingleNumber() const tristate result = querySingleNumber( KDbEscapedString("SELECT COUNT() FROM (") + sql + ") AS kdb__subquery", &count); if (~result) { count = 0; } return count; } int KDbConnection::recordCount(const KDbTableSchema& tableSchema) { //! @todo does not work with non-SQL data sources int count = -1; // will be changed only on success of querySingleNumber() const tristate result = querySingleNumber(KDbEscapedString("SELECT COUNT(*) FROM ") + tableSchema.connection()->escapeIdentifier(tableSchema.name()), &count); if (~result) { count = 0; } return count; } int KDbConnection::recordCount(KDbQuerySchema* querySchema, const QList& params) { //! @todo does not work with non-SQL data sources int count = -1; //will be changed only on success of querySingleNumber() - KDbNativeStatementBuilder builder(this); + KDbNativeStatementBuilder builder(this, KDb::DriverEscaping); KDbEscapedString subSql; if (!builder.generateSelectStatement(&subSql, querySchema, params)) { return -1; } const tristate result = querySingleNumber( KDbEscapedString("SELECT COUNT(*) FROM (") + subSql + ") AS kdb__subquery", &count); if (~result) { count = 0; } return count; } int KDbConnection::recordCount(KDbTableOrQuerySchema* tableOrQuery, const QList& params) { if (tableOrQuery) { if (tableOrQuery->table()) return recordCount(*tableOrQuery->table()); if (tableOrQuery->query()) return recordCount(tableOrQuery->query(), params); } return -1; } KDbConnectionOptions* KDbConnection::options() { return &d->options; } void KDbConnection::addCursor(KDbCursor* cursor) { d->cursors.insert(cursor); } void KDbConnection::takeCursor(KDbCursor* cursor) { if (d && !d->cursors.isEmpty()) { // checking because this may be called from ~KDbConnection() d->cursors.remove(cursor); } } KDbPreparedStatement KDbConnection::prepareStatement(KDbPreparedStatement::Type type, KDbFieldList* fields, const QStringList& whereFieldNames) { //! @todo move to ConnectionInterface just like we moved execute() and prepare() to KDbPreparedStatementInterface... KDbPreparedStatementInterface *iface = prepareStatementInternal(); if (!iface) return KDbPreparedStatement(); return KDbPreparedStatement(iface, type, fields, whereFieldNames); } KDbEscapedString KDbConnection::recentSqlString() const { return result().errorSql().isEmpty() ? m_result.sql() : result().errorSql(); } KDbEscapedString KDbConnection::escapeString(const QString& str) const { return d->driver->escapeString(str); } //! @todo extraMessages #if 0 static const char *extraMessages[] = { QT_TRANSLATE_NOOP("KDbConnection", "Unknown error.") }; #endif diff --git a/src/KDbCursor.cpp b/src/KDbCursor.cpp index 9b6504c0..f6433bbc 100644 --- a/src/KDbCursor.cpp +++ b/src/KDbCursor.cpp @@ -1,622 +1,622 @@ /* This file is part of the KDE project Copyright (C) 2003-2016 Jarosław Staniek This program is free software; you can redistribute it and/or modify it under the terms of the GNU Library General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Library General Public License for more details. You should have received a copy of the GNU Library General Public License along with this program; see the file COPYING. If not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, * Boston, MA 02110-1301, USA. */ #include "KDbCursor.h" #include "KDbConnection.h" #include "KDbDriver.h" #include "KDbDriverBehavior.h" #include "KDbError.h" #include "KDb.h" #include "KDbNativeStatementBuilder.h" #include "KDbQuerySchema.h" #include "KDbRecordData.h" #include "KDbRecordEditBuffer.h" #include "kdb_debug.h" class Q_DECL_HIDDEN KDbCursor::Private { public: Private() : opened(false) , atLast(false) , readAhead(false) , validRecord(false) , atBuffer(false) { } ~Private() { } bool containsRecordIdInfo; //!< true if result contains extra column for record id; //!< used only for PostgreSQL now //! @todo IMPORTANT: use something like QPointer conn; KDbConnection *conn; KDbEscapedString rawSql; bool opened; bool atLast; bool readAhead; bool validRecord; //!< true if valid record is currently retrieved @ current position //! Used by setOrderByColumnList() KDbQueryColumnInfo::Vector orderByColumnList; QList queryParameters; // bool atBuffer; //!< true if we already point to the buffer with curr_coldata // }; KDbCursor::KDbCursor(KDbConnection* conn, const KDbEscapedString& sql, Options options) : m_query(nullptr) , m_options(options) , d(new Private) { #ifdef KDB_DEBUG_GUI KDb::debugGUI(QLatin1String("Create cursor for raw SQL: ") + sql.toString()); #endif init(conn); d->rawSql = sql; } KDbCursor::KDbCursor(KDbConnection* conn, KDbQuerySchema* query, Options options) : m_query(query) , m_options(options) , d(new Private) { #ifdef KDB_DEBUG_GUI KDb::debugGUI(QString::fromLatin1("Create cursor for query \"%1\":\n") .arg(KDb::iifNotEmpty(query->name(), QString::fromLatin1(""))) + KDbUtils::debugString(query)); #endif init(conn); } void KDbCursor::init(KDbConnection* conn) { Q_ASSERT(conn); d->conn = conn; d->conn->addCursor(this); m_afterLast = false; m_at = 0; m_records_in_buf = 0; m_buffering_completed = false; m_fetchResult = FetchResult::Invalid; d->containsRecordIdInfo = (m_query && m_query->masterTable()) && d->conn->driver()->behavior()->ROW_ID_FIELD_RETURNS_LAST_AUTOINCREMENTED_VALUE == false; if (m_query) { //get list of all fields m_visibleFieldsExpanded = new KDbQueryColumnInfo::Vector(); *m_visibleFieldsExpanded = m_query->visibleFieldsExpanded(conn, d->containsRecordIdInfo ? KDbQuerySchema::FieldsExpandedMode::WithInternalFieldsAndRecordId : KDbQuerySchema::FieldsExpandedMode::WithInternalFields); m_logicalFieldCount = m_visibleFieldsExpanded->count() - m_query->internalFields(conn).count() - (d->containsRecordIdInfo ? 1 : 0); m_fieldCount = m_visibleFieldsExpanded->count(); m_fieldsToStoreInRecord = m_fieldCount; } else { m_visibleFieldsExpanded = nullptr; m_logicalFieldCount = 0; m_fieldCount = 0; m_fieldsToStoreInRecord = 0; } } KDbCursor::~KDbCursor() { #ifdef KDB_DEBUG_GUI #if 0 // too many details if (m_query) KDb::debugGUI(QLatin1String("~ Delete cursor for query")); else KDb::debugGUI(QLatin1String("~ Delete cursor: ") + m_rawSql.toString()); #endif #endif /* if (!m_query) kdbDebug() << "KDbCursor::~KDbCursor() '" << m_rawSql.toLatin1() << "'"; else kdbDebug() << "KDbCursor::~KDbCursor() ";*/ d->conn->takeCursor(this); delete m_visibleFieldsExpanded; delete d; } bool KDbCursor::readAhead() const { return d->readAhead; } KDbConnection* KDbCursor::connection() { return d->conn; } const KDbConnection* KDbCursor::connection() const { return d->conn; } KDbQuerySchema *KDbCursor::query() const { return m_query; } KDbEscapedString KDbCursor::rawSql() const { return d->rawSql; } KDbCursor::Options KDbCursor::options() const { return m_options; } bool KDbCursor::isOpened() const { return d->opened; } bool KDbCursor::containsRecordIdInfo() const { return d->containsRecordIdInfo; } KDbRecordData* KDbCursor::storeCurrentRecord() const { KDbRecordData* data = new KDbRecordData(m_fieldsToStoreInRecord); if (!drv_storeCurrentRecord(data)) { delete data; return nullptr; } return data; } bool KDbCursor::storeCurrentRecord(KDbRecordData* data) const { if (!data) { return false; } data->resize(m_fieldsToStoreInRecord); return drv_storeCurrentRecord(data); } bool KDbCursor::open() { if (d->opened) { if (!close()) return false; } if (!d->rawSql.isEmpty()) { m_result.setSql(d->rawSql); } else { if (!m_query) { kdbDebug() << "no query statement (or schema) defined!"; m_result = KDbResult(ERR_SQL_EXECUTION_ERROR, tr("No query statement or schema defined.")); return false; } KDbSelectStatementOptions options; options.setAlsoRetrieveRecordId(d->containsRecordIdInfo); /*get record Id if needed*/ - KDbNativeStatementBuilder builder(d->conn); + KDbNativeStatementBuilder builder(d->conn, KDb::DriverEscaping); KDbEscapedString sql; if (!builder.generateSelectStatement(&sql, m_query, options, d->queryParameters) || sql.isEmpty()) { kdbDebug() << "no statement generated!"; m_result = KDbResult(ERR_SQL_EXECUTION_ERROR, tr("Could not generate query statement.")); return false; } m_result.setSql(sql); #ifdef KDB_DEBUG_GUI KDb::debugGUI(QString::fromLatin1("SQL for query \"%1\": ") .arg(KDb::iifNotEmpty(m_query->name(), QString::fromLatin1(""))) + m_result.sql().toString()); #endif } d->opened = drv_open(m_result.sql()); m_afterLast = false; //we are not @ the end m_at = 0; //we are before 1st rec if (!d->opened) { m_result.setCode(ERR_SQL_EXECUTION_ERROR); m_result.setMessage(tr("Error opening database cursor.")); return false; } d->validRecord = false; if (d->conn->driver()->behavior()->_1ST_ROW_READ_AHEAD_REQUIRED_TO_KNOW_IF_THE_RESULT_IS_EMPTY) { // kdbDebug() << "READ AHEAD:"; d->readAhead = getNextRecord(); //true if any record in this query // kdbDebug() << "READ AHEAD = " << d->readAhead; } m_at = 0; //we are still before 1st rec return !m_result.isError(); } bool KDbCursor::close() { if (!d->opened) { return true; } bool ret = drv_close(); clearBuffer(); d->opened = false; m_afterLast = false; d->readAhead = false; m_fieldCount = 0; m_fieldsToStoreInRecord = 0; m_logicalFieldCount = 0; m_at = -1; // kdbDebug() << ret; return ret; } bool KDbCursor::reopen() { if (!d->opened) { return open(); } return close() && open(); } bool KDbCursor::moveFirst() { if (!d->opened) { return false; } if (!d->readAhead) { if (m_options & KDbCursor::Option::Buffered) { if (m_records_in_buf == 0 && m_buffering_completed) { //eof and bof should now return true: m_afterLast = true; m_at = 0; return false; //buffering completed and there is no records! } if (m_records_in_buf > 0) { //set state as we would be before first rec: d->atBuffer = false; m_at = 0; //..and move to next, i.e. 1st record m_afterLast = !getNextRecord(); return !m_afterLast; } } else if (!(d->conn->driver()->behavior()->_1ST_ROW_READ_AHEAD_REQUIRED_TO_KNOW_IF_THE_RESULT_IS_EMPTY)) { // not buffered m_at = 0; m_afterLast = !getNextRecord(); return !m_afterLast; } if (m_afterLast && m_at == 0) //failure if already no records return false; if (!reopen()) //try reopen return false; if (m_afterLast) //eof return false; } else { //we have a record already read-ahead: we now point @ that: m_at = 1; } //get first record m_afterLast = false; d->readAhead = false; //1st record had been read return d->validRecord; } bool KDbCursor::moveLast() { if (!d->opened) { return false; } if (m_afterLast || d->atLast) { return d->validRecord; //we already have valid last record retrieved } if (!getNextRecord()) { //at least next record must be retrieved m_afterLast = true; d->validRecord = false; d->atLast = false; return false; //no records } while (getNextRecord()) //move after last rec. ; m_afterLast = false; //cursor shows last record data d->atLast = true; return true; } bool KDbCursor::moveNext() { if (!d->opened || m_afterLast) { return false; } if (getNextRecord()) { return true; } return false; } bool KDbCursor::movePrev() { if (!d->opened /*|| m_beforeFirst*/ || !(m_options & KDbCursor::Option::Buffered)) { return false; } //we're after last record and there are records in the buffer //--let's move to last record if (m_afterLast && (m_records_in_buf > 0)) { drv_bufferMovePointerTo(m_records_in_buf - 1); m_at = m_records_in_buf; d->atBuffer = true; //now current record is stored in the buffer d->validRecord = true; m_afterLast = false; return true; } //we're at first record: go BOF if ((m_at <= 1) || (m_records_in_buf <= 1/*sanity*/)) { m_at = 0; d->atBuffer = false; d->validRecord = false; return false; } m_at--; if (d->atBuffer) {//we already have got a pointer to buffer drv_bufferMovePointerPrev(); //just move to prev record in the buffer } else {//we have no pointer //compute a place in the buffer that contain next record's data drv_bufferMovePointerTo(m_at - 1); d->atBuffer = true; //now current record is stored in the buffer } d->validRecord = true; m_afterLast = false; return true; } bool KDbCursor::isBuffered() const { return m_options & KDbCursor::Option::Buffered; } void KDbCursor::setBuffered(bool buffered) { if (!d->opened) { return; } if (isBuffered() == buffered) return; m_options ^= KDbCursor::Option::Buffered; } void KDbCursor::clearBuffer() { if (!isBuffered() || m_fieldCount == 0) return; drv_clearBuffer(); m_records_in_buf = 0; d->atBuffer = false; } bool KDbCursor::getNextRecord() { m_fetchResult = FetchResult::Invalid; //by default: invalid result of record fetching if (m_options & KDbCursor::Option::Buffered) {//this cursor is buffered: // kdbDebug() << "m_at < m_records_in_buf :: " << (long)m_at << " < " << m_records_in_buf; if (m_at < m_records_in_buf) {//we have next record already buffered: if (d->atBuffer) {//we already have got a pointer to buffer drv_bufferMovePointerNext(); //just move to next record in the buffer } else {//we have no pointer //compute a place in the buffer that contain next record's data drv_bufferMovePointerTo(m_at - 1 + 1); d->atBuffer = true; //now current record is stored in the buffer } } else {//we are after last retrieved record: we need to physically fetch next record: if (!d->readAhead) {//we have no record that was read ahead if (!m_buffering_completed) { //retrieve record only if we are not after //the last buffer's item (i.e. when buffer is not fully filled): // kdbDebug()<<"==== buffering: drv_getNextRecord() ===="; drv_getNextRecord(); } if (m_fetchResult != FetchResult::Ok) {//there is no record m_buffering_completed = true; //no more records for buffer // kdbDebug()<<"m_fetchResult != FetchResult::Ok ********"; d->validRecord = false; m_afterLast = true; m_at = -1; //position is invalid now and will not be used if (m_fetchResult == FetchResult::Error) { m_result = KDbResult(ERR_CURSOR_RECORD_FETCHING, tr("Could not fetch next record.")); return false; } return false; // in case of m_fetchResult = FetchResult::End or m_fetchResult = FetchInvalid } //we have a record: store this record's values in the buffer drv_appendCurrentRecordToBuffer(); m_records_in_buf++; } else //we have a record that was read ahead: eat this d->readAhead = false; } } else {//we are after last retrieved record: we need to physically fetch next record: if (!d->readAhead) {//we have no record that was read ahead // kdbDebug()<<"==== no prefetched record ===="; drv_getNextRecord(); if (m_fetchResult != FetchResult::Ok) {//there is no record // kdbDebug()<<"m_fetchResult != FetchResult::Ok ********"; d->validRecord = false; m_afterLast = true; m_at = -1; if (m_fetchResult == FetchResult::End) { return false; } m_result = KDbResult(ERR_CURSOR_RECORD_FETCHING, tr("Could not fetch next record.")); return false; } } else { //we have a record that was read ahead: eat this d->readAhead = false; } } m_at++; // if (m_data->curr_colname && m_data->curr_coldata) // for (int i=0;icurr_cols;i++) { // kdbDebug()<curr_colname[i]<<" == "<< m_data->curr_coldata[i]; // } // kdbDebug()<<"m_at == "<<(long)m_at; d->validRecord = true; return true; } bool KDbCursor::updateRecord(KDbRecordData* data, KDbRecordEditBuffer* buf, bool useRecordId) { //! @todo doesn't update cursor's buffer YET! clearResult(); if (!m_query) return false; return d->conn->updateRecord(m_query, data, buf, useRecordId); } bool KDbCursor::insertRecord(KDbRecordData* data, KDbRecordEditBuffer* buf, bool useRecordId) { //! @todo doesn't update cursor's buffer YET! if (!m_query) { clearResult(); return false; } return d->conn->insertRecord(m_query, data, buf, useRecordId); } bool KDbCursor::deleteRecord(KDbRecordData* data, bool useRecordId) { //! @todo doesn't update cursor's buffer YET! clearResult(); if (!m_query) return false; return d->conn->deleteRecord(m_query, data, useRecordId); } bool KDbCursor::deleteAllRecords() { //! @todo doesn't update cursor's buffer YET! clearResult(); if (!m_query) return false; return d->conn->deleteAllRecords(m_query); } QDebug debug(QDebug dbg, KDbCursor& cursor, bool buildSql) { dbg.nospace() << "CURSOR("; if (!cursor.query()) { dbg.nospace() << "RAW SQL STATEMENT:" << cursor.rawSql().toString() << "\n"; } else if (buildSql) { - KDbNativeStatementBuilder builder(cursor.connection()); + KDbNativeStatementBuilder builder(cursor.connection(), KDb::DriverEscaping); KDbEscapedString sql; QString sqlString; if (builder.generateSelectStatement(&sql, cursor.query())) { sqlString = sql.toString(); } else { sqlString = QLatin1String(""); } dbg.nospace() << "KDbQuerySchema:" << sqlString << "\n"; } if (cursor.isOpened()) { dbg.space() << "OPENED"; } else { dbg.space() << "NOT_OPENED"; } if (cursor.isBuffered()) { dbg.space() << "BUFFERED"; } else { dbg.space() << "NOT_BUFFERED"; } dbg.nospace() << "AT=" << cursor.at() << ")"; return dbg.space(); } QDebug operator<<(QDebug dbg, KDbCursor &cursor) { return debug(dbg, cursor, true /*buildSql*/); } QDebug operator<<(QDebug dbg, const KDbCursor &cursor) { return debug(dbg, const_cast(cursor), false /* !buildSql*/); } void KDbCursor::setOrderByColumnList(const QStringList& columnNames) { Q_UNUSED(columnNames); //! @todo implement this: all field names should be found, exit otherwise // OK //! @todo if (!d->orderByColumnList) } /*! Convenience method, similar to setOrderBy(const QStringList&). */ void KDbCursor::setOrderByColumnList(const QString& column1, const QString& column2, const QString& column3, const QString& column4, const QString& column5) { Q_UNUSED(column1); Q_UNUSED(column2); Q_UNUSED(column3); Q_UNUSED(column4); Q_UNUSED(column5); //! @todo implement this, like above //! @todo add ORDER BY info to debugString() } KDbQueryColumnInfo::Vector KDbCursor::orderByColumnList() const { return d->orderByColumnList; } QList KDbCursor::queryParameters() const { return d->queryParameters; } void KDbCursor::setQueryParameters(const QList& params) { d->queryParameters = params; } //! @todo extraMessages #if 0 static const char *extraMessages[] = { QT_TRANSLATE_NOOP("KDbCursor", "No connection for cursor open operation specified.") }; #endif diff --git a/src/KDbNativeStatementBuilder.cpp b/src/KDbNativeStatementBuilder.cpp index 2acf8a59..898283a7 100644 --- a/src/KDbNativeStatementBuilder.cpp +++ b/src/KDbNativeStatementBuilder.cpp @@ -1,508 +1,509 @@ /* This file is part of the KDE project Copyright (C) 2003-2016 Jarosław Staniek This program is free software; you can redistribute it and/or modify it under the terms of the GNU Library General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Library General Public License for more details. You should have received a copy of the GNU Library General Public License along with this program; see the file COPYING. If not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, * Boston, MA 02110-1301, USA. */ #include "KDbNativeStatementBuilder.h" #include "KDbConnection.h" #include "kdb_debug.h" #include "KDbDriverBehavior.h" #include "KDbDriver_p.h" #include "KDbExpression.h" #include "KDbLookupFieldSchema.h" #include "KDbOrderByColumn.h" #include "KDbQueryAsterisk.h" #include "KDbQuerySchema.h" #include "KDbQuerySchemaParameter.h" #include "KDbRelationship.h" KDbSelectStatementOptions::~KDbSelectStatementOptions() { } //================================================ class Q_DECL_HIDDEN KDbNativeStatementBuilder::Private { public: Private() {} //! @todo use equivalent of QPointer KDbConnection *connection; + KDb::IdentifierEscapingType dialect; - inline KDbDriver *driver() { return connection ? connection->driver() : nullptr; } private: Q_DISABLE_COPY(Private) }; //================================================ -KDbNativeStatementBuilder::KDbNativeStatementBuilder(KDbConnection *connection) +KDbNativeStatementBuilder::KDbNativeStatementBuilder(KDbConnection *connection, + KDb::IdentifierEscapingType dialect) : d(new Private) { d->connection = connection; + d->dialect = dialect; } KDbNativeStatementBuilder::~KDbNativeStatementBuilder() { delete d; } static bool selectStatementInternal(KDbEscapedString *target, KDbConnection *connection, + KDb::IdentifierEscapingType dialect, KDbQuerySchema* querySchema, const KDbSelectStatementOptions& options, const QList& parameters) { Q_ASSERT(target); Q_ASSERT(querySchema); //"SELECT FROM ..." is theoretically allowed " //if (querySchema.fieldCount()<1) // return QString(); // Each SQL identifier needs to be escaped in the generated query. - const KDbDriver *driver = connection ? connection->driver() : nullptr; + const KDbDriver *driver = dialect == KDb::DriverEscaping ? connection->driver() : nullptr; if (!querySchema->statement().isEmpty()) { //! @todo replace with KDbNativeQuerySchema? It shouldn't be here. *target = querySchema->statement(); return true; } //! @todo looking at singleTable is visually nice but a field name can conflict //! with function or variable name... int number = 0; bool singleTable = querySchema->tables()->count() <= 1; if (singleTable) { //make sure we will have single table: foreach(KDbField *f, *querySchema->fields()) { if (querySchema->isColumnVisible(number) && f->table() && f->table()->lookupFieldSchema(*f)) { //uups, no, there's at least one left join singleTable = false; break; } number++; } } KDbEscapedString sql; //final sql string sql.reserve(4096); KDbEscapedString s_additional_joins; //additional joins needed for lookup fields KDbEscapedString s_additional_fields; //additional fields to append to the fields list int internalUniqueTableAliasNumber = 0; //used to build internalUniqueTableAliases int internalUniqueQueryAliasNumber = 0; //used to build internalUniqueQueryAliases number = 0; QList subqueries_for_lookup_data; // subqueries will be added to FROM section KDbEscapedString kdb_subquery_prefix("__kdb_subquery_"); KDbQuerySchemaParameterValueListIterator paramValuesIt(parameters); KDbQuerySchemaParameterValueListIterator *paramValuesItPtr = parameters.isEmpty() ? nullptr : ¶mValuesIt; foreach(KDbField *f, *querySchema->fields()) { if (querySchema->isColumnVisible(number)) { if (!sql.isEmpty()) sql += ", "; if (f->isQueryAsterisk()) { if (!singleTable && static_cast(f)->isSingleTableAsterisk()) { //single-table * sql.append(KDb::escapeIdentifier(driver, f->table()->name())).append(".*"); } else { //all-tables * (or simplified table.* when there's only one table) sql += '*'; } } else { if (f->isExpression()) { sql += f->expression().toString(driver, paramValuesItPtr); } else { if (!f->table()) {//sanity check return false; } QString tableName; int tablePosition = querySchema->tableBoundToColumn(number); if (tablePosition >= 0) { tableName = KDb::iifNotEmpty(querySchema->tableAlias(tablePosition), f->table()->name()); } if (options.addVisibleLookupColumns()) { // try to find table/alias name harder if (tableName.isEmpty()) { tableName = querySchema->tableAlias(f->table()->name()); } if (tableName.isEmpty()) { tableName = f->table()->name(); } } if (!singleTable && !tableName.isEmpty()) { sql.append(KDb::escapeIdentifier(driver, tableName)).append('.'); } sql += KDb::escapeIdentifier(driver, f->name()); } const QString aliasString(querySchema->columnAlias(number)); if (!aliasString.isEmpty()) { sql.append(" AS ").append(aliasString); } //! @todo add option that allows to omit "AS" keyword } KDbLookupFieldSchema *lookupFieldSchema = (options.addVisibleLookupColumns() && f->table()) ? f->table()->lookupFieldSchema(*f) : nullptr; if (lookupFieldSchema && lookupFieldSchema->boundColumn() >= 0) { // Lookup field schema found // Now we also need to fetch "visible" value from the lookup table, not only the value of binding. // -> build LEFT OUTER JOIN clause for this purpose (LEFT, not INNER because the binding can be broken) // "LEFT OUTER JOIN lookupTable ON thisTable.thisField=lookupTable.boundField" KDbLookupFieldSchemaRecordSource recordSource = lookupFieldSchema->recordSource(); if (recordSource.type() == KDbLookupFieldSchemaRecordSource::Type::Table) { KDbTableSchema *lookupTable = connection->tableSchema(recordSource.name()); KDbFieldList* visibleColumns = nullptr; KDbField *boundField = nullptr; if (lookupTable && lookupFieldSchema->boundColumn() < lookupTable->fieldCount() && (visibleColumns = lookupTable->subList(lookupFieldSchema->visibleColumns())) && (boundField = lookupTable->field(lookupFieldSchema->boundColumn()))) { //add LEFT OUTER JOIN if (!s_additional_joins.isEmpty()) s_additional_joins += ' '; const QString internalUniqueTableAlias( QLatin1String("__kdb_") + lookupTable->name() + QLatin1Char('_') + QString::number(internalUniqueTableAliasNumber++)); s_additional_joins += KDbEscapedString("LEFT OUTER JOIN %1 AS %2 ON %3.%4=%5.%6") .arg(KDb::escapeIdentifier(driver, lookupTable->name())) .arg(internalUniqueTableAlias) .arg(KDb::escapeIdentifier(driver, querySchema->tableAliasOrName(f->table()->name()))) .arg(KDb::escapeIdentifier(driver, f->name())) .arg(internalUniqueTableAlias) .arg(KDb::escapeIdentifier(driver, boundField->name())); //add visibleField to the list of SELECTed fields //if it is not yet present there if (!s_additional_fields.isEmpty()) s_additional_fields += ", "; //! @todo Add lookup schema option for separator other than ' ' or even option for placeholders like "Name ? ?" //! @todo Add possibility for joining the values at client side. s_additional_fields += visibleColumns->sqlFieldsList( connection, QLatin1String(" || ' ' || "), internalUniqueTableAlias, - driver ? KDb::DriverEscaping : KDb::KDbEscaping); + dialect); } delete visibleColumns; } else if (recordSource.type() == KDbLookupFieldSchemaRecordSource::Type::Query) { KDbQuerySchema *lookupQuery = connection->querySchema(recordSource.name()); if (!lookupQuery) { kdbWarning() << "!lookupQuery"; return false; } const KDbQueryColumnInfo::Vector fieldsExpanded( lookupQuery->fieldsExpanded(connection)); if (lookupFieldSchema->boundColumn() >= fieldsExpanded.count()) { kdbWarning() << "lookupFieldSchema->boundColumn() >= fieldsExpanded.count()"; return false; } KDbQueryColumnInfo *boundColumnInfo = fieldsExpanded.at(lookupFieldSchema->boundColumn()); if (!boundColumnInfo) { kdbWarning() << "!boundColumnInfo"; return false; } KDbField *boundField = boundColumnInfo->field(); if (!boundField) { kdbWarning() << "!boundField"; return false; } //add LEFT OUTER JOIN if (!s_additional_joins.isEmpty()) s_additional_joins += ' '; - KDbEscapedString internalUniqueQueryAlias - = kdb_subquery_prefix + KDb::escapeString(connection, lookupQuery->name()) + '_' - + QString::number(internalUniqueQueryAliasNumber++); - KDbNativeStatementBuilder builder(connection); + KDbEscapedString internalUniqueQueryAlias = kdb_subquery_prefix + + KDb::escapeString(driver ? connection : nullptr, lookupQuery->name()) + + '_' + QString::number(internalUniqueQueryAliasNumber++); + KDbNativeStatementBuilder builder(connection, dialect); KDbEscapedString subSql; if (!builder.generateSelectStatement(&subSql, lookupQuery, options, parameters)) { return false; } s_additional_joins += KDbEscapedString("LEFT OUTER JOIN (%1) AS %2 ON %3.%4=%5.%6") .arg(subSql) .arg(internalUniqueQueryAlias) .arg(KDb::escapeIdentifier(driver, f->table()->name())) .arg(KDb::escapeIdentifier(driver, f->name())) .arg(internalUniqueQueryAlias) .arg(KDb::escapeIdentifier(driver, boundColumnInfo->aliasOrName())); if (!s_additional_fields.isEmpty()) s_additional_fields += ", "; const QList visibleColumns(lookupFieldSchema->visibleColumns()); KDbEscapedString expression; foreach(int visibleColumnIndex, visibleColumns) { //! @todo Add lookup schema option for separator other than ' ' or even option for placeholders like "Name ? ?" //! @todo Add possibility for joining the values at client side. if (fieldsExpanded.count() <= visibleColumnIndex) { kdbWarning() << "fieldsExpanded.count() <= (*visibleColumnsIt) : " << fieldsExpanded.count() << " <= " << visibleColumnIndex; return false; } if (!expression.isEmpty()) expression += " || ' ' || "; expression += ( internalUniqueQueryAlias + '.' + KDb::escapeIdentifier(driver, fieldsExpanded.value(visibleColumnIndex)->aliasOrName()) ); } s_additional_fields += expression; } else { kdbWarning() << "unsupported record source type" << recordSource.typeName(); return false; } } } number++; } //add lookup fields if (!s_additional_fields.isEmpty()) sql += (", " + s_additional_fields); if (driver && options.alsoRetrieveRecordId()) { //append rowid column KDbEscapedString s; if (!sql.isEmpty()) s = ", "; if (querySchema->masterTable()) s += KDbEscapedString(querySchema->tableAliasOrName(querySchema->masterTable()->name())) + '.'; s += KDbDriverPrivate::behavior(driver)->ROW_ID_FIELD_NAME; sql += s; } sql.prepend("SELECT "); QList* tables = querySchema->tables(); if ((tables && !tables->isEmpty()) || !subqueries_for_lookup_data.isEmpty()) { sql += " FROM "; KDbEscapedString s_from; if (tables) { number = 0; foreach(KDbTableSchema *table, *tables) { if (!s_from.isEmpty()) s_from += ", "; s_from += KDb::escapeIdentifier(driver, table->name()); const QString aliasString(querySchema->tableAlias(number)); if (!aliasString.isEmpty()) s_from.append(" AS ").append(aliasString); number++; } } // add subqueries for lookup data int subqueries_for_lookup_data_counter = 0; foreach(KDbQuerySchema* subQuery, subqueries_for_lookup_data) { if (!s_from.isEmpty()) s_from += ", "; KDbEscapedString subSql; - if (!selectStatementInternal(&subSql, connection, subQuery, options, parameters)) { + if (!selectStatementInternal(&subSql, connection, dialect, subQuery, options, parameters)) { return false; } s_from += '(' + subSql + ") AS " + kdb_subquery_prefix + KDbEscapedString::number(subqueries_for_lookup_data_counter++); } sql += s_from; } KDbEscapedString s_where; s_where.reserve(4096); //JOINS if (!s_additional_joins.isEmpty()) { sql += ' ' + s_additional_joins + ' '; } //! @todo: we're using WHERE for joins now; use INNER/LEFT/RIGHT JOIN later //WHERE bool wasWhere = false; //for later use foreach(KDbRelationship *rel, *querySchema->relationships()) { if (s_where.isEmpty()) { wasWhere = true; } else s_where += " AND "; KDbEscapedString s_where_sub; foreach(const KDbField::Pair &pair, *rel->fieldPairs()) { if (!s_where_sub.isEmpty()) s_where_sub += " AND "; s_where_sub += KDbEscapedString(KDb::escapeIdentifier(driver, pair.first->table()->name())) + '.' + KDb::escapeIdentifier(driver, pair.first->name()) + " = " + KDb::escapeIdentifier(driver, pair.second->table()->name()) + '.' + KDb::escapeIdentifier(driver, pair.second->name()); } if (rel->fieldPairs()->count() > 1) { s_where_sub.prepend('('); s_where_sub += ')'; } s_where += s_where_sub; } //EXPLICITLY SPECIFIED WHERE EXPRESSION if (!querySchema->whereExpression().isNull()) { if (wasWhere) { //! @todo () are not always needed s_where = '(' + s_where + ") AND (" + querySchema->whereExpression().toString(driver, paramValuesItPtr) + ')'; } else { s_where = querySchema->whereExpression().toString(driver, paramValuesItPtr); } } if (!s_where.isEmpty()) sql += " WHERE " + s_where; //! @todo (js) add other sql parts //(use wasWhere here) // ORDER BY - KDbEscapedString orderByString( - querySchema->orderByColumnList()->toSqlString( - !singleTable/*includeTableName*/, connection, driver ? KDb::DriverEscaping : KDb::KDbEscaping) - ); + KDbEscapedString orderByString(querySchema->orderByColumnList()->toSqlString( + !singleTable /*includeTableName*/, connection, dialect)); const QVector pkeyFieldsOrder(querySchema->pkeyFieldsOrder(connection)); if (orderByString.isEmpty() && !pkeyFieldsOrder.isEmpty()) { //add automatic ORDER BY if there is no explicitly defined (especially helps when there are complex JOINs) KDbOrderByColumnList automaticPKOrderBy; const KDbQueryColumnInfo::Vector fieldsExpanded(querySchema->fieldsExpanded(connection)); foreach(int pkeyFieldsIndex, pkeyFieldsOrder) { if (pkeyFieldsIndex < 0) // no field mentioned in this query continue; if (pkeyFieldsIndex >= fieldsExpanded.count()) { kdbWarning() << "ORDER BY: (*it) >= fieldsExpanded.count() - " << pkeyFieldsIndex << " >= " << fieldsExpanded.count(); continue; } KDbQueryColumnInfo *ci = fieldsExpanded[ pkeyFieldsIndex ]; automaticPKOrderBy.appendColumn(ci); } - orderByString = automaticPKOrderBy.toSqlString(!singleTable/*includeTableName*/, - connection, driver ? KDb::DriverEscaping : KDb::KDbEscaping); + orderByString = automaticPKOrderBy.toSqlString(!singleTable /*includeTableName*/, + connection, dialect); } if (!orderByString.isEmpty()) sql += (" ORDER BY " + orderByString); //kdbDebug() << sql; *target = sql; return true; } bool KDbNativeStatementBuilder::generateSelectStatement(KDbEscapedString *target, KDbQuerySchema* querySchema, const KDbSelectStatementOptions& options, const QList& parameters) const { - return selectStatementInternal(target, d->connection, querySchema, options, parameters); + return selectStatementInternal(target, d->connection, d->dialect, querySchema, options, parameters); } bool KDbNativeStatementBuilder::generateSelectStatement(KDbEscapedString *target, KDbQuerySchema* querySchema, const QList& parameters) const { - return selectStatementInternal(target, d->connection, querySchema, KDbSelectStatementOptions(), + return selectStatementInternal(target, d->connection, d->dialect, querySchema, KDbSelectStatementOptions(), parameters); } bool KDbNativeStatementBuilder::generateSelectStatement(KDbEscapedString *target, KDbTableSchema* tableSchema, const KDbSelectStatementOptions& options) const { return generateSelectStatement(target, tableSchema->query(), options); } bool KDbNativeStatementBuilder::generateCreateTableStatement(KDbEscapedString *target, const KDbTableSchema& tableSchema) const { if (!target) { return false; } // Each SQL identifier needs to be escaped in the generated query. - const KDbDriver *driver = d->connection ? d->connection->driver() : nullptr; + const KDbDriver *driver = d->dialect == KDb::DriverEscaping ? d->connection->driver() : nullptr; KDbEscapedString sql; sql.reserve(4096); sql = KDbEscapedString("CREATE TABLE ") + KDb::escapeIdentifier(driver, tableSchema.name()) + " ("; bool first = true; for (const KDbField *field : *tableSchema.fields()) { if (first) first = false; else sql += ", "; KDbEscapedString v = KDbEscapedString(KDb::escapeIdentifier(driver, field->name())) + ' '; const bool autoinc = field->isAutoIncrement(); const bool pk = field->isPrimaryKey() || (autoinc && driver && driver->behavior()->AUTO_INCREMENT_REQUIRES_PK); //! @todo warning: ^^^^^ this allows only one autonumber per table when AUTO_INCREMENT_REQUIRES_PK==true! const KDbField::Type type = field->type(); // cache: evaluating type of expressions can be expensive - if (autoinc && d->driver()->behavior()->SPECIAL_AUTO_INCREMENT_DEF) { + if (autoinc && d->connection->driver()->behavior()->SPECIAL_AUTO_INCREMENT_DEF) { if (pk) - v.append(d->driver()->behavior()->AUTO_INCREMENT_TYPE).append(' ') - .append(d->driver()->behavior()->AUTO_INCREMENT_PK_FIELD_OPTION); + v.append(d->connection->driver()->behavior()->AUTO_INCREMENT_TYPE).append(' ') + .append(d->connection->driver()->behavior()->AUTO_INCREMENT_PK_FIELD_OPTION); else - v.append(d->driver()->behavior()->AUTO_INCREMENT_TYPE).append(' ') - .append(d->driver()->behavior()->AUTO_INCREMENT_FIELD_OPTION); + v.append(d->connection->driver()->behavior()->AUTO_INCREMENT_TYPE).append(' ') + .append(d->connection->driver()->behavior()->AUTO_INCREMENT_FIELD_OPTION); } else { - if (autoinc && !d->driver()->behavior()->AUTO_INCREMENT_TYPE.isEmpty()) - v += d->driver()->behavior()->AUTO_INCREMENT_TYPE; + if (autoinc && !d->connection->driver()->behavior()->AUTO_INCREMENT_TYPE.isEmpty()) + v += d->connection->driver()->behavior()->AUTO_INCREMENT_TYPE; else - v += d->driver()->sqlTypeName(type, *field); + v += d->connection->driver()->sqlTypeName(type, *field); if (KDbField::isIntegerType(type) && field->isUnsigned()) { - v.append(' ').append(d->driver()->behavior()->UNSIGNED_TYPE_KEYWORD); + v.append(' ').append(d->connection->driver()->behavior()->UNSIGNED_TYPE_KEYWORD); } if (KDbField::isFPNumericType(type) && field->precision() > 0) { if (field->scale() > 0) v += QString::fromLatin1("(%1,%2)").arg(field->precision()).arg(field->scale()); else v += QString::fromLatin1("(%1)").arg(field->precision()); } else if (type == KDbField::Text) { int realMaxLen; - if (d->driver()->behavior()->TEXT_TYPE_MAX_LENGTH == 0) { + if (d->connection->driver()->behavior()->TEXT_TYPE_MAX_LENGTH == 0) { realMaxLen = field->maxLength(); // allow to skip (N) } else { // max length specified by driver if (field->maxLength() == 0) { // as long as possible - realMaxLen = d->driver()->behavior()->TEXT_TYPE_MAX_LENGTH; + realMaxLen = d->connection->driver()->behavior()->TEXT_TYPE_MAX_LENGTH; } else { // not longer than specified by driver - realMaxLen = qMin(d->driver()->behavior()->TEXT_TYPE_MAX_LENGTH, field->maxLength()); + realMaxLen = qMin(d->connection->driver()->behavior()->TEXT_TYPE_MAX_LENGTH, field->maxLength()); } } if (realMaxLen > 0) { v += QString::fromLatin1("(%1)").arg(realMaxLen); } } if (autoinc) { - v.append(' ').append(pk ? d->driver()->behavior()->AUTO_INCREMENT_PK_FIELD_OPTION - : d->driver()->behavior()->AUTO_INCREMENT_FIELD_OPTION); + v.append(' ').append(pk ? d->connection->driver()->behavior()->AUTO_INCREMENT_PK_FIELD_OPTION + : d->connection->driver()->behavior()->AUTO_INCREMENT_FIELD_OPTION); } else { //! @todo here is automatically a single-field key created if (pk) v += " PRIMARY KEY"; } if (!pk && field->isUniqueKey()) v += " UNIQUE"; ///@todo IS this ok for all engines?: if (!autoinc && !field->isPrimaryKey() && field->isNotNull()) if (!autoinc && !pk && field->isNotNull()) v += " NOT NULL"; //only add not null option if no autocommit is set - if (d->driver()->supportsDefaultValue(*field) && field->defaultValue().isValid()) { - KDbEscapedString valToSql(d->driver()->valueToSql(field, field->defaultValue())); + if (d->connection->driver()->supportsDefaultValue(*field) && field->defaultValue().isValid()) { + KDbEscapedString valToSql(d->connection->driver()->valueToSql(field, field->defaultValue())); if (!valToSql.isEmpty()) //for sanity v += " DEFAULT " + valToSql; } } sql += v; } sql += ')'; *target = sql; return true; } diff --git a/src/KDbNativeStatementBuilder.h b/src/KDbNativeStatementBuilder.h index 0e40795b..bbe33274 100644 --- a/src/KDbNativeStatementBuilder.h +++ b/src/KDbNativeStatementBuilder.h @@ -1,88 +1,83 @@ /* This file is part of the KDE project Copyright (C) 2003-2016 Jarosław Staniek This program is free software; you can redistribute it and/or modify it under the terms of the GNU Library General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Library General Public License for more details. You should have received a copy of the GNU Library General Public License along with this program; see the file COPYING. If not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, * Boston, MA 02110-1301, USA. */ #ifndef KDB_KDBNATIVESTATEMENTBUILDER_H #define KDB_KDBNATIVESTATEMENTBUILDER_H +#include "KDb.h" #include "KDbSelectStatementOptions.h" -#include -#include - -#include "kdb_export.h" - -class KDbConnection; -class KDbEscapedString; -class KDbQuerySchema; -class KDbTableSchema; //! A builder for generating various types of native SQL statements /*! The statement strings can be specific for the used connection and database driver, and thus generally not portable across connections. */ class KDB_EXPORT KDbNativeStatementBuilder { public: - //! Creates a new native builder object. If @a connection is nullptr, - //! generated statement strings are of KDbSQL dialect, else they are specific - //! to database connection or connection's database driver. - explicit KDbNativeStatementBuilder(KDbConnection *connection = nullptr); + /** + * Creates a new native builder object. @a connection is required. + * + * If @a dialect is KDbEscaping generated statement strings will be of KDbSQL dialect, + * else they will be specific to database connection or connection's database driver. + */ + KDbNativeStatementBuilder(KDbConnection *connection, KDb::IdentifierEscapingType dialect); ~KDbNativeStatementBuilder(); /*! Generates a native "SELECT ..." statement string that can be used for executing query defined by @a querySchema, @a params and @a options. @a target and @a querySchema must not be 0. The statement is written to @ref *target on success. @return true on success. */ bool generateSelectStatement(KDbEscapedString *target, KDbQuerySchema* querySchema, const KDbSelectStatementOptions& options, const QList& parameters = QList()) const; /*! @overload generateSelectStatement(KDbEscapedString *target, KDbQuerySchema* querySchema, const KDbSelectStatementOptions& options, const QList& parameters) const. */ bool generateSelectStatement(KDbEscapedString *target, KDbQuerySchema* querySchema, const QList& parameters = QList()) const; /*! Generates a native "SELECT ..." statement string that can be used for executing query defined by an functional equivalent of a "SELECT * FROM table_name" statement where table_name is @a tableSchema's name. @a params and @a options are used like in the @ref toSelectStatement(KDbEscapedString*, KDbQuerySchema*, const KDbSelectStatementOptions&, const QList&) variant. @a target and @a querySchema must not be 0. @return true on success. */ bool generateSelectStatement(KDbEscapedString *target, KDbTableSchema* tableSchema, const KDbSelectStatementOptions& options = KDbSelectStatementOptions()) const; /*! Generates a native "CREATE TABLE ..." statement string that can be used for creation of @a tableSchema in the database. The statement is written to @ref *target on success. @return true on success. If @a target is @c nullptr, @c false is returned. */ bool generateCreateTableStatement(KDbEscapedString *target, const KDbTableSchema& tableSchema) const; private: Q_DISABLE_COPY(KDbNativeStatementBuilder) class Private; Private * const d; }; #endif diff --git a/tests/features/parser_test.h b/tests/features/parser_test.h index 985bb67c..919649d6 100644 --- a/tests/features/parser_test.h +++ b/tests/features/parser_test.h @@ -1,70 +1,70 @@ /* This file is part of the KDE project Copyright (C) 2003-2004 Jarosław Staniek This library is free software; you can redistribute it and/or modify it under the terms of the GNU Library General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Library General Public License for more details. You should have received a copy of the GNU Library General Public License along with this library; see the file COPYING.LIB. If not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, * Boston, MA 02110-1301, USA. */ #ifndef PARSER_TEST_H #define PARSER_TEST_H #include #include #include int parserTest(const KDbEscapedString &st, const QStringList ¶ms) { int r = 0; if (!conn->useDatabase()) { qDebug() << conn->result(); return 1; } KDbParser parser(conn); const bool ok = parser.parse(st); KDbQuerySchema *q = parser.query(); QList variantParams; for(const QString ¶m : params) { variantParams.append(param.toLocal8Bit()); } if (ok && q) { cout << qPrintable(KDbUtils::debugString(KDbConnectionAndQuerySchema(conn, *q))) << '\n'; - KDbNativeStatementBuilder builder(conn); + KDbNativeStatementBuilder builder(conn, KDb::DriverEscaping); KDbEscapedString sql; if (builder.generateSelectStatement(&sql, q, variantParams)) { cout << "-STATEMENT:\n" << sql.toByteArray().constData() << '\n'; } else { cout << "-CANNOT GENERATE STATEMENT\n"; } } else { qDebug() << parser.error(); r = 1; } delete q; q = nullptr; if (!conn->closeDatabase()) { qDebug() << conn->result(); return 1; } return r; } #endif