Akonadi

dbintrospector_impl.cpp
1/*
2 SPDX-FileCopyrightText: 2006 Tobias Koenig <tokoe@kde.org>
3 SPDX-FileCopyrightText: 2012 Volker Krause <vkrause@kde.org>
4
5 SPDX-License-Identifier: LGPL-2.0-or-later
6*/
7
8#include "dbintrospector_impl.h"
9#include "datastore.h"
10#include "dbexception.h"
11#include "querybuilder.h"
12
13#include "akonadiserver_debug.h"
14
15using namespace Akonadi::Server;
16
17// BEGIN MySql
18
19DbIntrospectorMySql::DbIntrospectorMySql(const QSqlDatabase &database)
20 : DbIntrospector(database)
21{
22}
23
24QString DbIntrospectorMySql::hasIndexQuery(const QString &tableName, const QString &indexName)
25{
26 return QStringLiteral("SHOW INDEXES FROM %1 WHERE `Key_name` = '%2'").arg(tableName, indexName);
27}
28
29QList<DbIntrospector::ForeignKey> DbIntrospectorMySql::foreignKeyConstraints(const QString &tableName)
30{
31 auto store = DataStore::dataStoreForDatabase(m_database);
32 QueryBuilder qb(store, QStringLiteral("information_schema.REFERENTIAL_CONSTRAINTS"), QueryBuilder::Select);
33 qb.addJoin(QueryBuilder::InnerJoin,
34 QStringLiteral("information_schema.KEY_COLUMN_USAGE"),
35 QStringLiteral("information_schema.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME"),
36 QStringLiteral("information_schema.KEY_COLUMN_USAGE.CONSTRAINT_NAME"));
37 qb.addColumn(QStringLiteral("information_schema.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME"));
38 qb.addColumn(QStringLiteral("information_schema.KEY_COLUMN_USAGE.COLUMN_NAME"));
39 qb.addColumn(QStringLiteral("information_schema.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME"));
40 qb.addColumn(QStringLiteral("information_schema.KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME"));
41 qb.addColumn(QStringLiteral("information_schema.REFERENTIAL_CONSTRAINTS.UPDATE_RULE"));
42 qb.addColumn(QStringLiteral("information_schema.REFERENTIAL_CONSTRAINTS.DELETE_RULE"));
43
44 qb.addValueCondition(QStringLiteral("information_schema.KEY_COLUMN_USAGE.TABLE_SCHEMA"), Query::Equals, m_database.databaseName());
45 qb.addValueCondition(QStringLiteral("information_schema.KEY_COLUMN_USAGE.TABLE_NAME"), Query::Equals, tableName);
46
47 if (!qb.exec()) {
48 throw DbException(qb.query());
49 }
50
51 QList<ForeignKey> result;
52 while (qb.query().next()) {
53 ForeignKey fk;
54 fk.name = qb.query().value(0).toString();
55 fk.column = qb.query().value(1).toString();
56 fk.refTable = qb.query().value(2).toString();
57 fk.refColumn = qb.query().value(3).toString();
58 fk.onUpdate = qb.query().value(4).toString();
59 fk.onDelete = qb.query().value(5).toString();
60 result.push_back(fk);
61 }
62 qb.query().finish();
63
64 return result;
65}
66
67QString DbIntrospectorMySql::getAutoIncrementValueQuery(const QString &tableName, const QString &)
68{
69 return QStringLiteral("SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_NAME = '%1'").arg(tableName);
70}
71
72QString DbIntrospectorMySql::updateAutoIncrementValueQuery(const QString &tableName, const QString &, qint64 value)
73{
74 return QStringLiteral("ALTER TABLE %1 AUTO_INCREMENT = %2").arg(tableName).arg(value);
75}
76
77// END MySql
78
79// BEGIN Sqlite
80
81DbIntrospectorSqlite::DbIntrospectorSqlite(const QSqlDatabase &database)
82 : DbIntrospector(database)
83{
84}
85
86QList<DbIntrospector::ForeignKey> DbIntrospectorSqlite::foreignKeyConstraints(const QString &tableName)
87{
88 QSqlQuery query(m_database);
89 if (!query.exec(QStringLiteral("PRAGMA foreign_key_list(%1)").arg(tableName))) {
90 throw DbException(query);
91 }
92
93 QList<ForeignKey> result;
94 while (query.next()) {
95 ForeignKey fk;
96 fk.column = query.value(3).toString();
97 fk.refTable = query.value(2).toString();
98 fk.refColumn = query.value(4).toString();
99 fk.onUpdate = query.value(5).toString();
100 fk.onDelete = query.value(6).toString();
101 fk.name = tableName + fk.column + QLatin1Char('_') + fk.refTable + fk.refColumn + QStringLiteral("_fk");
102 result.push_back(fk);
103 }
104
105 return result;
106}
107
108QString DbIntrospectorSqlite::hasIndexQuery(const QString &tableName, const QString &indexName)
109{
110 return QStringLiteral("SELECT * FROM sqlite_master WHERE type='index' AND tbl_name='%1' AND name='%2';").arg(tableName, indexName);
111}
112
113QString DbIntrospectorSqlite::getAutoIncrementValueQuery(const QString &tableName, const QString &)
114{
115 return QStringLiteral("SELECT seq FROM sqlite_sequence WHERE name = '%1'").arg(tableName);
116}
117
118QString DbIntrospectorSqlite::updateAutoIncrementValueQuery(const QString &tableName, const QString &, qint64 value)
119{
120 return QStringLiteral("UPDATE sqlite_sequence SET seq = %1 WHERE name = '%2'").arg(value).arg(tableName);
121}
122
123// END Sqlite
124
125// BEGIN PostgreSql
126
127DbIntrospectorPostgreSql::DbIntrospectorPostgreSql(const QSqlDatabase &database)
128 : DbIntrospector(database)
129{
130}
131
132QList<DbIntrospector::ForeignKey> DbIntrospectorPostgreSql::foreignKeyConstraints(const QString &tableName)
133{
134#define TABLE_CONSTRAINTS "information_schema.table_constraints"
135#define KEY_COLUMN_USAGE "information_schema.key_column_usage"
136#define REFERENTIAL_CONSTRAINTS "information_schema.referential_constraints"
137#define CONSTRAINT_COLUMN_USAGE "information_schema.constraint_column_usage"
138
139 Query::Condition keyColumnUsageCondition(Query::And);
140 keyColumnUsageCondition.addColumnCondition(QStringLiteral(TABLE_CONSTRAINTS ".constraint_catalog"),
141 Query::Equals,
142 QStringLiteral(KEY_COLUMN_USAGE ".constraint_catalog"));
143 keyColumnUsageCondition.addColumnCondition(QStringLiteral(TABLE_CONSTRAINTS ".constraint_schema"),
144 Query::Equals,
145 QStringLiteral(KEY_COLUMN_USAGE ".constraint_schema"));
146 keyColumnUsageCondition.addColumnCondition(QStringLiteral(TABLE_CONSTRAINTS ".constraint_name"),
147 Query::Equals,
148 QStringLiteral(KEY_COLUMN_USAGE ".constraint_name"));
149
150 Query::Condition referentialConstraintsCondition(Query::And);
151 referentialConstraintsCondition.addColumnCondition(QStringLiteral(TABLE_CONSTRAINTS ".constraint_catalog"),
152 Query::Equals,
153 QStringLiteral(REFERENTIAL_CONSTRAINTS ".constraint_catalog"));
154 referentialConstraintsCondition.addColumnCondition(QStringLiteral(TABLE_CONSTRAINTS ".constraint_schema"),
155 Query::Equals,
156 QStringLiteral(REFERENTIAL_CONSTRAINTS ".constraint_schema"));
157 referentialConstraintsCondition.addColumnCondition(QStringLiteral(TABLE_CONSTRAINTS ".constraint_name"),
158 Query::Equals,
159 QStringLiteral(REFERENTIAL_CONSTRAINTS ".constraint_name"));
160
161 Query::Condition constraintColumnUsageCondition(Query::And);
162 constraintColumnUsageCondition.addColumnCondition(QStringLiteral(REFERENTIAL_CONSTRAINTS ".unique_constraint_catalog"),
163 Query::Equals,
164 QStringLiteral(CONSTRAINT_COLUMN_USAGE ".constraint_catalog"));
165 constraintColumnUsageCondition.addColumnCondition(QStringLiteral(REFERENTIAL_CONSTRAINTS ".unique_constraint_schema"),
166 Query::Equals,
167 QStringLiteral(CONSTRAINT_COLUMN_USAGE ".constraint_schema"));
168 constraintColumnUsageCondition.addColumnCondition(QStringLiteral(REFERENTIAL_CONSTRAINTS ".unique_constraint_name"),
169 Query::Equals,
170 QStringLiteral(CONSTRAINT_COLUMN_USAGE ".constraint_name"));
171
172 auto store = DataStore::dataStoreForDatabase(m_database);
173 QueryBuilder qb(store, QStringLiteral(TABLE_CONSTRAINTS), QueryBuilder::Select);
174 qb.addColumn(QStringLiteral(TABLE_CONSTRAINTS ".constraint_name"));
175 qb.addColumn(QStringLiteral(KEY_COLUMN_USAGE ".column_name"));
176 qb.addColumn(QStringLiteral(CONSTRAINT_COLUMN_USAGE ".table_name AS referenced_table"));
177 qb.addColumn(QStringLiteral(CONSTRAINT_COLUMN_USAGE ".column_name AS referenced_column"));
178 qb.addColumn(QStringLiteral(REFERENTIAL_CONSTRAINTS ".update_rule"));
179 qb.addColumn(QStringLiteral(REFERENTIAL_CONSTRAINTS ".delete_rule"));
180 qb.addJoin(QueryBuilder::LeftJoin, QStringLiteral(KEY_COLUMN_USAGE), keyColumnUsageCondition);
181 qb.addJoin(QueryBuilder::LeftJoin, QStringLiteral(REFERENTIAL_CONSTRAINTS), referentialConstraintsCondition);
182 qb.addJoin(QueryBuilder::LeftJoin, QStringLiteral(CONSTRAINT_COLUMN_USAGE), constraintColumnUsageCondition);
183 qb.addValueCondition(QStringLiteral(TABLE_CONSTRAINTS ".constraint_type"), Query::Equals, QLatin1StringView("FOREIGN KEY"));
184 qb.addValueCondition(QStringLiteral(TABLE_CONSTRAINTS ".table_name"), Query::Equals, tableName.toLower());
185
186#undef TABLE_CONSTRAINTS
187#undef KEY_COLUMN_USAGE
188#undef REFERENTIAL_CONSTRAINTS
189#undef CONSTRAINT_COLUMN_USAGE
190
191 if (!qb.exec()) {
192 throw DbException(qb.query());
193 }
194
195 QList<ForeignKey> result;
196 while (qb.query().next()) {
197 ForeignKey fk;
198 fk.name = qb.query().value(0).toString();
199 fk.column = qb.query().value(1).toString();
200 fk.refTable = qb.query().value(2).toString();
201 fk.refColumn = qb.query().value(3).toString();
202 fk.onUpdate = qb.query().value(4).toString();
203 fk.onDelete = qb.query().value(5).toString();
204 result.push_back(fk);
205 }
206 qb.query().finish();
207
208 return result;
209}
210
211QString DbIntrospectorPostgreSql::hasIndexQuery(const QString &tableName, const QString &indexName)
212{
213 QString query = QStringLiteral("SELECT indexname FROM pg_catalog.pg_indexes");
214 query += QStringLiteral(" WHERE tablename ilike '%1'").arg(tableName);
215 query += QStringLiteral(" AND indexname ilike '%1'").arg(indexName);
216 query += QStringLiteral(" UNION SELECT conname FROM pg_catalog.pg_constraint ");
217 query += QStringLiteral(" WHERE conname ilike '%1'").arg(indexName);
218 return query;
219}
220
221QString DbIntrospectorPostgreSql::getAutoIncrementValueQuery(const QString &tableName, const QString &idColumn)
222{
223 return QStringLiteral("SELECT nextval(pg_get_serial_sequence('%1', '%2'))").arg(tableName, idColumn);
224}
225
226QString DbIntrospectorPostgreSql::updateAutoIncrementValueQuery(const QString &tableName, const QString &idColumn, qint64 value)
227{
228 // Can't use ALTER SEQUENCE, because it doesn't support expressions (like pg_get_serial_sequence())
229 return QStringLiteral("SELECT setval(pg_get_serial_sequence('%1', '%2'), %3) FROM %1").arg(tableName, idColumn).arg(value);
230}
231
232// END PostgreSql
Akonadi::Server::DataStore::dataStoreForDatabase
static DataStore * dataStoreForDatabase(const QSqlDatabase &db)
Returns DataStore associated with the given database connection.
Definition datastore.cpp:98
Akonadi::Server::DbException
Exception for reporting SQL errors.
Definition dbexception.h:19
Akonadi::Server::DbIntrospector
Methods for introspecting the current state of a database schema.
Definition dbintrospector.h:25
Akonadi::Server::DbIntrospector::m_database
QSqlDatabase m_database
The database connection we are introspecting.
Definition dbintrospector.h:111
Akonadi::Server::QueryBuilder
Helper class to construct arbitrary SQL queries.
Definition querybuilder.h:33
Akonadi::Server::QueryBuilder::InnerJoin
@ InnerJoin
NOTE: only supported for UPDATE and SELECT queries.
Definition querybuilder.h:51
Akonadi::Server::QueryBuilder::LeftJoin
@ LeftJoin
NOTE: only supported for SELECT queries.
Definition querybuilder.h:53
Akonadi::Server::Query::Condition
Represents a WHERE condition tree.
Definition query.h:62
KApplicationTrader::query
KSERVICE_EXPORT KService::List query(FilterFunc filterFunc)
QList::push_back
void push_back(parameter_type value)
QList::value
T value(qsizetype i) const const
QSqlDatabase::databaseName
QString databaseName() const const
QString::arg
QString arg(Args &&... args) const const
QString::toLower
QString toLower() const const
This file is part of the KDE documentation.
Documentation copyright © 1996-2024 The KDE developers.
Generated on Sat Dec 21 2024 17:01:42 by doxygen 1.12.0 written by Dimitri van Heesch, © 1997-2006

KDE's Doxygen guidelines are available online.