Akonadi

dbintrospector_impl.cpp
1 /*
2  Copyright (C) 2006 by Tobias Koenig <[email protected]>
3  Copyright (c) 2012 Volker Krause <[email protected]>
4 
5  This library is free software; you can redistribute it and/or modify it
6  under the terms of the GNU Library General Public License as published by
7  the Free Software Foundation; either version 2 of the License, or (at your
8  option) any later version.
9 
10  This library is distributed in the hope that it will be useful, but WITHOUT
11  ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
12  FITNESS FOR A PARTICULAR PURPOSE. See the GNU Library General Public
13  License for more details.
14 
15  You should have received a copy of the GNU Library General Public License
16  along with this library; see the file COPYING.LIB. If not, write to the
17  Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
18  02110-1301, USA.
19 */
20 
21 #include "dbintrospector_impl.h"
22 #include "dbexception.h"
23 #include "querybuilder.h"
24 #include "datastore.h"
25 
26 #include "akonadiserver_debug.h"
27 
28 using namespace Akonadi::Server;
29 
30 //BEGIN MySql
31 
32 DbIntrospectorMySql::DbIntrospectorMySql(const QSqlDatabase &database)
33  : DbIntrospector(database)
34 {
35 }
36 
37 QString DbIntrospectorMySql::hasIndexQuery(const QString &tableName, const QString &indexName)
38 {
39  return QStringLiteral("SHOW INDEXES FROM %1 WHERE `Key_name` = '%2'")
40  .arg(tableName, indexName);
41 }
42 
43 QVector< DbIntrospector::ForeignKey > DbIntrospectorMySql::foreignKeyConstraints(const QString &tableName)
44 {
45  QueryBuilder qb(QStringLiteral("information_schema.REFERENTIAL_CONSTRAINTS"), QueryBuilder::Select);
46  qb.addJoin(QueryBuilder::InnerJoin, QStringLiteral("information_schema.KEY_COLUMN_USAGE"),
47  QStringLiteral("information_schema.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME"),
48  QStringLiteral("information_schema.KEY_COLUMN_USAGE.CONSTRAINT_NAME"));
49  qb.addColumn(QStringLiteral("information_schema.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME"));
50  qb.addColumn(QStringLiteral("information_schema.KEY_COLUMN_USAGE.COLUMN_NAME"));
51  qb.addColumn(QStringLiteral("information_schema.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME"));
52  qb.addColumn(QStringLiteral("information_schema.KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME"));
53  qb.addColumn(QStringLiteral("information_schema.REFERENTIAL_CONSTRAINTS.UPDATE_RULE"));
54  qb.addColumn(QStringLiteral("information_schema.REFERENTIAL_CONSTRAINTS.DELETE_RULE"));
55 
56  qb.addValueCondition(QStringLiteral("information_schema.KEY_COLUMN_USAGE.TABLE_SCHEMA"), Query::Equals, m_database.databaseName());
57  qb.addValueCondition(QStringLiteral("information_schema.KEY_COLUMN_USAGE.TABLE_NAME"), Query::Equals, tableName);
58 
59  if (!qb.exec()) {
60  throw DbException(qb.query());
61  }
62 
63  QVector<ForeignKey> result;
64  while (qb.query().next()) {
65  ForeignKey fk;
66  fk.name = qb.query().value(0).toString();
67  fk.column = qb.query().value(1).toString();
68  fk.refTable = qb.query().value(2).toString();
69  fk.refColumn = qb.query().value(3).toString();
70  fk.onUpdate = qb.query().value(4).toString();
71  fk.onDelete = qb.query().value(5).toString();
72  result.push_back(fk);
73  }
74  qb.query().finish();
75 
76  return result;
77 }
78 
79 //END MySql
80 
81 //BEGIN Sqlite
82 
83 DbIntrospectorSqlite::DbIntrospectorSqlite(const QSqlDatabase &database)
84  : DbIntrospector(database)
85 {
86 }
87 
88 QVector<DbIntrospector::ForeignKey> DbIntrospectorSqlite::foreignKeyConstraints(const QString &tableName)
89 {
90  QSqlQuery query(DataStore::self()->database());
91  if (!query.exec(QStringLiteral("PRAGMA foreign_key_list(%1)").arg(tableName))) {
92  throw DbException(query);
93  }
94 
95  QVector<ForeignKey> result;
96  while (query.next()) {
97  ForeignKey fk;
98  fk.column = query.value(3).toString();
99  fk.refTable = query.value(2).toString();
100  fk.refColumn = query.value(4).toString();
101  fk.onUpdate = query.value(5).toString();
102  fk.onDelete = query.value(6).toString();
103  fk.name = tableName + fk.column + QLatin1Char('_') + fk.refTable + fk.refColumn + QStringLiteral("_fk");
104  result.push_back(fk);
105  }
106 
107  return result;
108 }
109 
110 QString DbIntrospectorSqlite::hasIndexQuery(const QString &tableName, const QString &indexName)
111 {
112  return QStringLiteral("SELECT * FROM sqlite_master WHERE type='index' AND tbl_name='%1' AND name='%2';")
113  .arg(tableName, indexName);
114 }
115 
116 //END Sqlite
117 
118 //BEGIN PostgreSql
119 
120 DbIntrospectorPostgreSql::DbIntrospectorPostgreSql(const QSqlDatabase &database)
121  : DbIntrospector(database)
122 {
123 }
124 
125 QVector<DbIntrospector::ForeignKey> DbIntrospectorPostgreSql::foreignKeyConstraints(const QString &tableName)
126 {
127 #define TABLE_CONSTRAINTS "information_schema.table_constraints"
128 #define KEY_COLUMN_USAGE "information_schema.key_column_usage"
129 #define REFERENTIAL_CONSTRAINTS "information_schema.referential_constraints"
130 #define CONSTRAINT_COLUMN_USAGE "information_schema.constraint_column_usage"
131 
132  Query::Condition keyColumnUsageCondition(Query::And);
133  keyColumnUsageCondition.addColumnCondition(QStringLiteral(TABLE_CONSTRAINTS ".constraint_catalog"), Query::Equals,
134  QStringLiteral(KEY_COLUMN_USAGE ".constraint_catalog"));
135  keyColumnUsageCondition.addColumnCondition(QStringLiteral(TABLE_CONSTRAINTS ".constraint_schema"), Query::Equals,
136  QStringLiteral(KEY_COLUMN_USAGE ".constraint_schema"));
137  keyColumnUsageCondition.addColumnCondition(QStringLiteral(TABLE_CONSTRAINTS ".constraint_name"), Query::Equals,
138  QStringLiteral(KEY_COLUMN_USAGE ".constraint_name"));
139 
140  Query::Condition referentialConstraintsCondition(Query::And);
141  referentialConstraintsCondition.addColumnCondition(QStringLiteral(TABLE_CONSTRAINTS ".constraint_catalog"), Query::Equals,
142  QStringLiteral(REFERENTIAL_CONSTRAINTS ".constraint_catalog"));
143  referentialConstraintsCondition.addColumnCondition(QStringLiteral(TABLE_CONSTRAINTS ".constraint_schema"), Query::Equals,
144  QStringLiteral(REFERENTIAL_CONSTRAINTS ".constraint_schema"));
145  referentialConstraintsCondition.addColumnCondition(QStringLiteral(TABLE_CONSTRAINTS ".constraint_name"), Query::Equals,
146  QStringLiteral(REFERENTIAL_CONSTRAINTS ".constraint_name"));
147 
148  Query::Condition constraintColumnUsageCondition(Query::And);
149  constraintColumnUsageCondition.addColumnCondition(QStringLiteral(REFERENTIAL_CONSTRAINTS ".unique_constraint_catalog"), Query::Equals,
150  QStringLiteral(CONSTRAINT_COLUMN_USAGE ".constraint_catalog"));
151  constraintColumnUsageCondition.addColumnCondition(QStringLiteral(REFERENTIAL_CONSTRAINTS ".unique_constraint_schema"), Query::Equals,
152  QStringLiteral(CONSTRAINT_COLUMN_USAGE ".constraint_schema"));
153  constraintColumnUsageCondition.addColumnCondition(QStringLiteral(REFERENTIAL_CONSTRAINTS ".unique_constraint_name"), Query::Equals,
154  QStringLiteral(CONSTRAINT_COLUMN_USAGE ".constraint_name"));
155 
156  QueryBuilder qb(QStringLiteral(TABLE_CONSTRAINTS), QueryBuilder::Select);
157  qb.addColumn(QStringLiteral(TABLE_CONSTRAINTS ".constraint_name"));
158  qb.addColumn(QStringLiteral(KEY_COLUMN_USAGE ".column_name"));
159  qb.addColumn(QStringLiteral(CONSTRAINT_COLUMN_USAGE ".table_name AS referenced_table"));
160  qb.addColumn(QStringLiteral(CONSTRAINT_COLUMN_USAGE ".column_name AS referenced_column"));
161  qb.addColumn(QStringLiteral(REFERENTIAL_CONSTRAINTS ".update_rule"));
162  qb.addColumn(QStringLiteral(REFERENTIAL_CONSTRAINTS ".delete_rule"));
163  qb.addJoin(QueryBuilder::LeftJoin, QStringLiteral(KEY_COLUMN_USAGE), keyColumnUsageCondition);
164  qb.addJoin(QueryBuilder::LeftJoin, QStringLiteral(REFERENTIAL_CONSTRAINTS), referentialConstraintsCondition);
165  qb.addJoin(QueryBuilder::LeftJoin, QStringLiteral(CONSTRAINT_COLUMN_USAGE), constraintColumnUsageCondition);
166  qb.addValueCondition(QStringLiteral(TABLE_CONSTRAINTS ".constraint_type"),
167  Query::Equals, QLatin1String("FOREIGN KEY"));
168  qb.addValueCondition(QStringLiteral(TABLE_CONSTRAINTS ".table_name"),
169  Query::Equals, tableName.toLower());
170 
171 #undef TABLE_CONSTRAINTS
172 #undef KEY_COLUMN_USAGE
173 #undef REFERENTIAL_CONSTRAINTS
174 #undef CONSTRAINT_COLUMN_USAGE
175 
176  if (!qb.exec()) {
177  throw DbException(qb.query());
178  }
179 
180  QVector<ForeignKey> result;
181  while (qb.query().next()) {
182  ForeignKey fk;
183  fk.name = qb.query().value(0).toString();
184  fk.column = qb.query().value(1).toString();
185  fk.refTable = qb.query().value(2).toString();
186  fk.refColumn = qb.query().value(3).toString();
187  fk.onUpdate = qb.query().value(4).toString();
188  fk.onDelete = qb.query().value(5).toString();
189  result.push_back(fk);
190  }
191  qb.query().finish();
192 
193  return result;
194 }
195 
196 QString DbIntrospectorPostgreSql::hasIndexQuery(const QString &tableName, const QString &indexName)
197 {
198  QString query = QStringLiteral("SELECT indexname FROM pg_catalog.pg_indexes");
199  query += QStringLiteral(" WHERE tablename ilike '%1'").arg(tableName);
200  query += QStringLiteral(" AND indexname ilike '%1'").arg(indexName);
201  query += QStringLiteral(" UNION SELECT conname FROM pg_catalog.pg_constraint ");
202  query += QStringLiteral(" WHERE conname ilike '%1'").arg(indexName);
203  return query;
204 }
205 
206 //END PostgreSql
std::optional< QSqlQuery > query(const QString &queryStatement)
Returns the cached (and prepared) query for queryStatement.
Definition: querycache.cpp:108
NOTE: only supported for UPDATE and SELECT queries.
Definition: querybuilder.h:62
Exception for reporting SQL errors.
Definition: dbexception.h:33
T value(int i) const const
NOTE: only supported for SELECT queries.
Definition: querybuilder.h:64
QSqlDatabase m_database
The database connection we are introspecting.
void push_back(QChar ch)
QString toLower() const const
static DataStore * self()
Per thread singleton.
Definition: datastore.cpp:235
QString databaseName() const const
Definition: item.h:44
QString arg(qlonglong a, int fieldWidth, int base, QChar fillChar) const const
void push_back(const T &value)
Represents a WHERE condition tree.
Definition: query.h:77
Methods for introspecting the current state of a database schema.
Helper class to construct arbitrary SQL queries.
Definition: querybuilder.h:45
This file is part of the KDE documentation.
Documentation copyright © 1996-2020 The KDE developers.
Generated on Fri Jun 5 2020 23:08:54 by doxygen 1.8.11 written by Dimitri van Heesch, © 1997-2006

KDE's Doxygen guidelines are available online.