server/storage/dbintrospector_impl.cpp

0001 /*
0002     SPDX-FileCopyrightText: 2006 Tobias Koenig <tokoe@kde.org>
0003     SPDX-FileCopyrightText: 2012 Volker Krause <vkrause@kde.org>
0004
0005     SPDX-License-Identifier: LGPL-2.0-or-later
0006 */
0007
0008 #include "dbintrospector_impl.h"
0009 #include "datastore.h"
0010 #include "dbexception.h"
0011 #include "querybuilder.h"
0012
0013 #include "akonadiserver_debug.h"
0014
0015 using namespace Akonadi::Server;
0016
0017 // BEGIN MySql
0018
0019 DbIntrospectorMySql::DbIntrospectorMySql(const QSqlDatabase &database)
0020     : DbIntrospector(database)
0021 {
0022 }
0023
0024 QString DbIntrospectorMySql::hasIndexQuery(const QString &tableName, const QString &indexName)
0025 {
0026     return QStringLiteral("SHOW INDEXES FROM %1 WHERE `Key_name` = '%2'").arg(tableName, indexName);
0027 }
0028
0029 QList<DbIntrospector::ForeignKey> DbIntrospectorMySql::foreignKeyConstraints(const QString &tableName)
0030 {
0031     auto store = DataStore::dataStoreForDatabase(m_database);
0032     QueryBuilder qb(store, QStringLiteral("information_schema.REFERENTIAL_CONSTRAINTS"), QueryBuilder::Select);
0033     qb.addJoin(QueryBuilder::InnerJoin,
0034                QStringLiteral("information_schema.KEY_COLUMN_USAGE"),
0035                QStringLiteral("information_schema.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME"),
0036                QStringLiteral("information_schema.KEY_COLUMN_USAGE.CONSTRAINT_NAME"));
0037     qb.addColumn(QStringLiteral("information_schema.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME"));
0038     qb.addColumn(QStringLiteral("information_schema.KEY_COLUMN_USAGE.COLUMN_NAME"));
0039     qb.addColumn(QStringLiteral("information_schema.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME"));
0040     qb.addColumn(QStringLiteral("information_schema.KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME"));
0041     qb.addColumn(QStringLiteral("information_schema.REFERENTIAL_CONSTRAINTS.UPDATE_RULE"));
0042     qb.addColumn(QStringLiteral("information_schema.REFERENTIAL_CONSTRAINTS.DELETE_RULE"));
0043
0044     qb.addValueCondition(QStringLiteral("information_schema.KEY_COLUMN_USAGE.TABLE_SCHEMA"), Query::Equals, m_database.databaseName());
0045     qb.addValueCondition(QStringLiteral("information_schema.KEY_COLUMN_USAGE.TABLE_NAME"), Query::Equals, tableName);
0046
0047     if (!qb.exec()) {
0048         throw DbException(qb.query());
0049     }
0050
0051     QList<ForeignKey> result;
0052     while (qb.query().next()) {
0053         ForeignKey fk;
0054         fk.name = qb.query().value(0).toString();
0055         fk.column = qb.query().value(1).toString();
0056         fk.refTable = qb.query().value(2).toString();
0057         fk.refColumn = qb.query().value(3).toString();
0058         fk.onUpdate = qb.query().value(4).toString();
0059         fk.onDelete = qb.query().value(5).toString();
0060         result.push_back(fk);
0061     }
0062     qb.query().finish();
0063
0064     return result;
0065 }
0066
0067 QString DbIntrospectorMySql::getAutoIncrementValueQuery(const QString &tableName, const QString &)
0068 {
0069     return QStringLiteral("SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_NAME = '%1'").arg(tableName);
0070 }
0071
0072 QString DbIntrospectorMySql::updateAutoIncrementValueQuery(const QString &tableName, const QString &, qint64 value)
0073 {
0074     return QStringLiteral("ALTER TABLE %1 AUTO_INCREMENT = %2").arg(tableName).arg(value);
0075 }
0076
0077 // END MySql
0078
0079 // BEGIN Sqlite
0080
0081 DbIntrospectorSqlite::DbIntrospectorSqlite(const QSqlDatabase &database)
0082     : DbIntrospector(database)
0083 {
0084 }
0085
0086 QList<DbIntrospector::ForeignKey> DbIntrospectorSqlite::foreignKeyConstraints(const QString &tableName)
0087 {
0088     QSqlQuery query(m_database);
0089     if (!query.exec(QStringLiteral("PRAGMA foreign_key_list(%1)").arg(tableName))) {
0090         throw DbException(query);
0091     }
0092
0093     QList<ForeignKey> result;
0094     while (query.next()) {
0095         ForeignKey fk;
0096         fk.column = query.value(3).toString();
0097         fk.refTable = query.value(2).toString();
0098         fk.refColumn = query.value(4).toString();
0099         fk.onUpdate = query.value(5).toString();
0100         fk.onDelete = query.value(6).toString();
0101         fk.name = tableName + fk.column + QLatin1Char('_') + fk.refTable + fk.refColumn + QStringLiteral("_fk");
0102         result.push_back(fk);
0103     }
0104
0105     return result;
0106 }
0107
0108 QString DbIntrospectorSqlite::hasIndexQuery(const QString &tableName, const QString &indexName)
0109 {
0110     return QStringLiteral("SELECT * FROM sqlite_master WHERE type='index' AND tbl_name='%1' AND name='%2';").arg(tableName, indexName);
0111 }
0112
0113 QString DbIntrospectorSqlite::getAutoIncrementValueQuery(const QString &tableName, const QString &)
0114 {
0115     return QStringLiteral("SELECT seq FROM sqlite_sequence WHERE name = '%1'").arg(tableName);
0116 }
0117
0118 QString DbIntrospectorSqlite::updateAutoIncrementValueQuery(const QString &tableName, const QString &, qint64 value)
0119 {
0120     return QStringLiteral("UPDATE sqlite_sequence SET seq = %1 WHERE name = '%2'").arg(value).arg(tableName);
0121 }
0122
0123 // END Sqlite
0124
0125 // BEGIN PostgreSql
0126
0127 DbIntrospectorPostgreSql::DbIntrospectorPostgreSql(const QSqlDatabase &database)
0128     : DbIntrospector(database)
0129 {
0130 }
0131
0132 QList<DbIntrospector::ForeignKey> DbIntrospectorPostgreSql::foreignKeyConstraints(const QString &tableName)
0133 {
0134 #define TABLE_CONSTRAINTS "information_schema.table_constraints"
0135 #define KEY_COLUMN_USAGE "information_schema.key_column_usage"
0136 #define REFERENTIAL_CONSTRAINTS "information_schema.referential_constraints"
0137 #define CONSTRAINT_COLUMN_USAGE "information_schema.constraint_column_usage"
0138
0139     Query::Condition keyColumnUsageCondition(Query::And);
0140     keyColumnUsageCondition.addColumnCondition(QStringLiteral(TABLE_CONSTRAINTS ".constraint_catalog"),
0141                                                Query::Equals,
0142                                                QStringLiteral(KEY_COLUMN_USAGE ".constraint_catalog"));
0143     keyColumnUsageCondition.addColumnCondition(QStringLiteral(TABLE_CONSTRAINTS ".constraint_schema"),
0144                                                Query::Equals,
0145                                                QStringLiteral(KEY_COLUMN_USAGE ".constraint_schema"));
0146     keyColumnUsageCondition.addColumnCondition(QStringLiteral(TABLE_CONSTRAINTS ".constraint_name"),
0147                                                Query::Equals,
0148                                                QStringLiteral(KEY_COLUMN_USAGE ".constraint_name"));
0149
0150     Query::Condition referentialConstraintsCondition(Query::And);
0151     referentialConstraintsCondition.addColumnCondition(QStringLiteral(TABLE_CONSTRAINTS ".constraint_catalog"),
0152                                                        Query::Equals,
0153                                                        QStringLiteral(REFERENTIAL_CONSTRAINTS ".constraint_catalog"));
0154     referentialConstraintsCondition.addColumnCondition(QStringLiteral(TABLE_CONSTRAINTS ".constraint_schema"),
0155                                                        Query::Equals,
0156                                                        QStringLiteral(REFERENTIAL_CONSTRAINTS ".constraint_schema"));
0157     referentialConstraintsCondition.addColumnCondition(QStringLiteral(TABLE_CONSTRAINTS ".constraint_name"),
0158                                                        Query::Equals,
0159                                                        QStringLiteral(REFERENTIAL_CONSTRAINTS ".constraint_name"));
0160
0161     Query::Condition constraintColumnUsageCondition(Query::And);
0162     constraintColumnUsageCondition.addColumnCondition(QStringLiteral(REFERENTIAL_CONSTRAINTS ".unique_constraint_catalog"),
0163                                                       Query::Equals,
0164                                                       QStringLiteral(CONSTRAINT_COLUMN_USAGE ".constraint_catalog"));
0165     constraintColumnUsageCondition.addColumnCondition(QStringLiteral(REFERENTIAL_CONSTRAINTS ".unique_constraint_schema"),
0166                                                       Query::Equals,
0167                                                       QStringLiteral(CONSTRAINT_COLUMN_USAGE ".constraint_schema"));
0168     constraintColumnUsageCondition.addColumnCondition(QStringLiteral(REFERENTIAL_CONSTRAINTS ".unique_constraint_name"),
0169                                                       Query::Equals,
0170                                                       QStringLiteral(CONSTRAINT_COLUMN_USAGE ".constraint_name"));
0171
0172     auto store = DataStore::dataStoreForDatabase(m_database);
0173     QueryBuilder qb(store, QStringLiteral(TABLE_CONSTRAINTS), QueryBuilder::Select);
0174     qb.addColumn(QStringLiteral(TABLE_CONSTRAINTS ".constraint_name"));
0175     qb.addColumn(QStringLiteral(KEY_COLUMN_USAGE ".column_name"));
0176     qb.addColumn(QStringLiteral(CONSTRAINT_COLUMN_USAGE ".table_name AS referenced_table"));
0177     qb.addColumn(QStringLiteral(CONSTRAINT_COLUMN_USAGE ".column_name AS referenced_column"));
0178     qb.addColumn(QStringLiteral(REFERENTIAL_CONSTRAINTS ".update_rule"));
0179     qb.addColumn(QStringLiteral(REFERENTIAL_CONSTRAINTS ".delete_rule"));
0180     qb.addJoin(QueryBuilder::LeftJoin, QStringLiteral(KEY_COLUMN_USAGE), keyColumnUsageCondition);
0181     qb.addJoin(QueryBuilder::LeftJoin, QStringLiteral(REFERENTIAL_CONSTRAINTS), referentialConstraintsCondition);
0182     qb.addJoin(QueryBuilder::LeftJoin, QStringLiteral(CONSTRAINT_COLUMN_USAGE), constraintColumnUsageCondition);
0183     qb.addValueCondition(QStringLiteral(TABLE_CONSTRAINTS ".constraint_type"), Query::Equals, QLatin1StringView("FOREIGN KEY"));
0184     qb.addValueCondition(QStringLiteral(TABLE_CONSTRAINTS ".table_name"), Query::Equals, tableName.toLower());
0185
0186 #undef TABLE_CONSTRAINTS
0187 #undef KEY_COLUMN_USAGE
0188 #undef REFERENTIAL_CONSTRAINTS
0189 #undef CONSTRAINT_COLUMN_USAGE
0190
0191     if (!qb.exec()) {
0192         throw DbException(qb.query());
0193     }
0194
0195     QList<ForeignKey> result;
0196     while (qb.query().next()) {
0197         ForeignKey fk;
0198         fk.name = qb.query().value(0).toString();
0199         fk.column = qb.query().value(1).toString();
0200         fk.refTable = qb.query().value(2).toString();
0201         fk.refColumn = qb.query().value(3).toString();
0202         fk.onUpdate = qb.query().value(4).toString();
0203         fk.onDelete = qb.query().value(5).toString();
0204         result.push_back(fk);
0205     }
0206     qb.query().finish();
0207
0208     return result;
0209 }
0210
0211 QString DbIntrospectorPostgreSql::hasIndexQuery(const QString &tableName, const QString &indexName)
0212 {
0213     QString query = QStringLiteral("SELECT indexname FROM pg_catalog.pg_indexes");
0214     query += QStringLiteral(" WHERE tablename ilike '%1'").arg(tableName);
0215     query += QStringLiteral(" AND  indexname ilike '%1'").arg(indexName);
0216     query += QStringLiteral(" UNION SELECT conname FROM pg_catalog.pg_constraint ");
0217     query += QStringLiteral(" WHERE conname ilike '%1'").arg(indexName);
0218     return query;
0219 }
0220
0221 QString DbIntrospectorPostgreSql::getAutoIncrementValueQuery(const QString &tableName, const QString &idColumn)
0222 {
0223     return QStringLiteral("SELECT nextval(pg_get_serial_sequence('%1', '%2'))").arg(tableName, idColumn);
0224 }
0225
0226 QString DbIntrospectorPostgreSql::updateAutoIncrementValueQuery(const QString &tableName, const QString &idColumn, qint64 value)
0227 {
0228     // Can't use ALTER SEQUENCE, because it doesn't support expressions (like pg_get_serial_sequence())
0229     return QStringLiteral("SELECT setval(pg_get_serial_sequence('%1', '%2'), %3) FROM %1").arg(tableName, idColumn).arg(value);
0230 }
0231
0232 // END PostgreSql