Akonadi

dbintrospector_impl.cpp
1 /*
2  SPDX-FileCopyrightText: 2006 Tobias Koenig <[email protected]>
3  SPDX-FileCopyrightText: 2012 Volker Krause <[email protected]>
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 
15 using namespace Akonadi::Server;
16 
17 // BEGIN MySql
18 
19 DbIntrospectorMySql::DbIntrospectorMySql(const QSqlDatabase &database)
20  : DbIntrospector(database)
21 {
22 }
23 
24 QString 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 
29 QVector<DbIntrospector::ForeignKey> DbIntrospectorMySql::foreignKeyConstraints(const QString &tableName)
30 {
31  QueryBuilder qb(QStringLiteral("information_schema.REFERENTIAL_CONSTRAINTS"), QueryBuilder::Select);
32  qb.addJoin(QueryBuilder::InnerJoin,
33  QStringLiteral("information_schema.KEY_COLUMN_USAGE"),
34  QStringLiteral("information_schema.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME"),
35  QStringLiteral("information_schema.KEY_COLUMN_USAGE.CONSTRAINT_NAME"));
36  qb.addColumn(QStringLiteral("information_schema.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME"));
37  qb.addColumn(QStringLiteral("information_schema.KEY_COLUMN_USAGE.COLUMN_NAME"));
38  qb.addColumn(QStringLiteral("information_schema.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME"));
39  qb.addColumn(QStringLiteral("information_schema.KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME"));
40  qb.addColumn(QStringLiteral("information_schema.REFERENTIAL_CONSTRAINTS.UPDATE_RULE"));
41  qb.addColumn(QStringLiteral("information_schema.REFERENTIAL_CONSTRAINTS.DELETE_RULE"));
42 
43  qb.addValueCondition(QStringLiteral("information_schema.KEY_COLUMN_USAGE.TABLE_SCHEMA"), Query::Equals, m_database.databaseName());
44  qb.addValueCondition(QStringLiteral("information_schema.KEY_COLUMN_USAGE.TABLE_NAME"), Query::Equals, tableName);
45 
46  if (!qb.exec()) {
47  throw DbException(qb.query());
48  }
49 
50  QVector<ForeignKey> result;
51  while (qb.query().next()) {
52  ForeignKey fk;
53  fk.name = qb.query().value(0).toString();
54  fk.column = qb.query().value(1).toString();
55  fk.refTable = qb.query().value(2).toString();
56  fk.refColumn = qb.query().value(3).toString();
57  fk.onUpdate = qb.query().value(4).toString();
58  fk.onDelete = qb.query().value(5).toString();
59  result.push_back(fk);
60  }
61  qb.query().finish();
62 
63  return result;
64 }
65 
66 // END MySql
67 
68 // BEGIN Sqlite
69 
70 DbIntrospectorSqlite::DbIntrospectorSqlite(const QSqlDatabase &database)
71  : DbIntrospector(database)
72 {
73 }
74 
75 QVector<DbIntrospector::ForeignKey> DbIntrospectorSqlite::foreignKeyConstraints(const QString &tableName)
76 {
77  QSqlQuery query(DataStore::self()->database());
78  if (!query.exec(QStringLiteral("PRAGMA foreign_key_list(%1)").arg(tableName))) {
79  throw DbException(query);
80  }
81 
82  QVector<ForeignKey> result;
83  while (query.next()) {
84  ForeignKey fk;
85  fk.column = query.value(3).toString();
86  fk.refTable = query.value(2).toString();
87  fk.refColumn = query.value(4).toString();
88  fk.onUpdate = query.value(5).toString();
89  fk.onDelete = query.value(6).toString();
90  fk.name = tableName + fk.column + QLatin1Char('_') + fk.refTable + fk.refColumn + QStringLiteral("_fk");
91  result.push_back(fk);
92  }
93 
94  return result;
95 }
96 
97 QString DbIntrospectorSqlite::hasIndexQuery(const QString &tableName, const QString &indexName)
98 {
99  return QStringLiteral("SELECT * FROM sqlite_master WHERE type='index' AND tbl_name='%1' AND name='%2';").arg(tableName, indexName);
100 }
101 
102 // END Sqlite
103 
104 // BEGIN PostgreSql
105 
106 DbIntrospectorPostgreSql::DbIntrospectorPostgreSql(const QSqlDatabase &database)
107  : DbIntrospector(database)
108 {
109 }
110 
111 QVector<DbIntrospector::ForeignKey> DbIntrospectorPostgreSql::foreignKeyConstraints(const QString &tableName)
112 {
113 #define TABLE_CONSTRAINTS "information_schema.table_constraints"
114 #define KEY_COLUMN_USAGE "information_schema.key_column_usage"
115 #define REFERENTIAL_CONSTRAINTS "information_schema.referential_constraints"
116 #define CONSTRAINT_COLUMN_USAGE "information_schema.constraint_column_usage"
117 
118  Query::Condition keyColumnUsageCondition(Query::And);
119  keyColumnUsageCondition.addColumnCondition(QStringLiteral(TABLE_CONSTRAINTS ".constraint_catalog"),
120  Query::Equals,
121  QStringLiteral(KEY_COLUMN_USAGE ".constraint_catalog"));
122  keyColumnUsageCondition.addColumnCondition(QStringLiteral(TABLE_CONSTRAINTS ".constraint_schema"),
123  Query::Equals,
124  QStringLiteral(KEY_COLUMN_USAGE ".constraint_schema"));
125  keyColumnUsageCondition.addColumnCondition(QStringLiteral(TABLE_CONSTRAINTS ".constraint_name"),
126  Query::Equals,
127  QStringLiteral(KEY_COLUMN_USAGE ".constraint_name"));
128 
129  Query::Condition referentialConstraintsCondition(Query::And);
130  referentialConstraintsCondition.addColumnCondition(QStringLiteral(TABLE_CONSTRAINTS ".constraint_catalog"),
131  Query::Equals,
132  QStringLiteral(REFERENTIAL_CONSTRAINTS ".constraint_catalog"));
133  referentialConstraintsCondition.addColumnCondition(QStringLiteral(TABLE_CONSTRAINTS ".constraint_schema"),
134  Query::Equals,
135  QStringLiteral(REFERENTIAL_CONSTRAINTS ".constraint_schema"));
136  referentialConstraintsCondition.addColumnCondition(QStringLiteral(TABLE_CONSTRAINTS ".constraint_name"),
137  Query::Equals,
138  QStringLiteral(REFERENTIAL_CONSTRAINTS ".constraint_name"));
139 
140  Query::Condition constraintColumnUsageCondition(Query::And);
141  constraintColumnUsageCondition.addColumnCondition(QStringLiteral(REFERENTIAL_CONSTRAINTS ".unique_constraint_catalog"),
142  Query::Equals,
143  QStringLiteral(CONSTRAINT_COLUMN_USAGE ".constraint_catalog"));
144  constraintColumnUsageCondition.addColumnCondition(QStringLiteral(REFERENTIAL_CONSTRAINTS ".unique_constraint_schema"),
145  Query::Equals,
146  QStringLiteral(CONSTRAINT_COLUMN_USAGE ".constraint_schema"));
147  constraintColumnUsageCondition.addColumnCondition(QStringLiteral(REFERENTIAL_CONSTRAINTS ".unique_constraint_name"),
148  Query::Equals,
149  QStringLiteral(CONSTRAINT_COLUMN_USAGE ".constraint_name"));
150 
151  QueryBuilder qb(QStringLiteral(TABLE_CONSTRAINTS), QueryBuilder::Select);
152  qb.addColumn(QStringLiteral(TABLE_CONSTRAINTS ".constraint_name"));
153  qb.addColumn(QStringLiteral(KEY_COLUMN_USAGE ".column_name"));
154  qb.addColumn(QStringLiteral(CONSTRAINT_COLUMN_USAGE ".table_name AS referenced_table"));
155  qb.addColumn(QStringLiteral(CONSTRAINT_COLUMN_USAGE ".column_name AS referenced_column"));
156  qb.addColumn(QStringLiteral(REFERENTIAL_CONSTRAINTS ".update_rule"));
157  qb.addColumn(QStringLiteral(REFERENTIAL_CONSTRAINTS ".delete_rule"));
158  qb.addJoin(QueryBuilder::LeftJoin, QStringLiteral(KEY_COLUMN_USAGE), keyColumnUsageCondition);
159  qb.addJoin(QueryBuilder::LeftJoin, QStringLiteral(REFERENTIAL_CONSTRAINTS), referentialConstraintsCondition);
160  qb.addJoin(QueryBuilder::LeftJoin, QStringLiteral(CONSTRAINT_COLUMN_USAGE), constraintColumnUsageCondition);
161  qb.addValueCondition(QStringLiteral(TABLE_CONSTRAINTS ".constraint_type"), Query::Equals, QLatin1String("FOREIGN KEY"));
162  qb.addValueCondition(QStringLiteral(TABLE_CONSTRAINTS ".table_name"), Query::Equals, tableName.toLower());
163 
164 #undef TABLE_CONSTRAINTS
165 #undef KEY_COLUMN_USAGE
166 #undef REFERENTIAL_CONSTRAINTS
167 #undef CONSTRAINT_COLUMN_USAGE
168 
169  if (!qb.exec()) {
170  throw DbException(qb.query());
171  }
172 
173  QVector<ForeignKey> result;
174  while (qb.query().next()) {
175  ForeignKey fk;
176  fk.name = qb.query().value(0).toString();
177  fk.column = qb.query().value(1).toString();
178  fk.refTable = qb.query().value(2).toString();
179  fk.refColumn = qb.query().value(3).toString();
180  fk.onUpdate = qb.query().value(4).toString();
181  fk.onDelete = qb.query().value(5).toString();
182  result.push_back(fk);
183  }
184  qb.query().finish();
185 
186  return result;
187 }
188 
189 QString DbIntrospectorPostgreSql::hasIndexQuery(const QString &tableName, const QString &indexName)
190 {
191  QString query = QStringLiteral("SELECT indexname FROM pg_catalog.pg_indexes");
192  query += QStringLiteral(" WHERE tablename ilike '%1'").arg(tableName);
193  query += QStringLiteral(" AND indexname ilike '%1'").arg(indexName);
194  query += QStringLiteral(" UNION SELECT conname FROM pg_catalog.pg_constraint ");
195  query += QStringLiteral(" WHERE conname ilike '%1'").arg(indexName);
196  return query;
197 }
198 
199 // END PostgreSql
static DataStore * self()
Per thread singleton.
Definition: datastore.cpp:215
@ InnerJoin
NOTE: only supported for UPDATE and SELECT queries.
Definition: querybuilder.h:48
void push_back(const T &value)
@ LeftJoin
NOTE: only supported for SELECT queries.
Definition: querybuilder.h:50
KSERVICE_EXPORT KService::List query(FilterFunc filterFunc)
T value(int i) const const
Exception for reporting SQL errors.
Definition: dbexception.h:18
QString toLower() const const
QString arg(qlonglong a, int fieldWidth, int base, QChar fillChar) const const
Represents a WHERE condition tree.
Definition: query.h:61
Methods for introspecting the current state of a database schema.
T value(int i) const const
Helper class to construct arbitrary SQL queries.
Definition: querybuilder.h:31
This file is part of the KDE documentation.
Documentation copyright © 1996-2022 The KDE developers.
Generated on Sat Jun 25 2022 06:00:32 by doxygen 1.8.17 written by Dimitri van Heesch, © 1997-2006

KDE's Doxygen guidelines are available online.