[prev in list] [next in list] [prev in thread] [next in thread] 

List:       kde-commits
Subject:    [akonadi] server/src/storage: Implement DbInspector::foreignKeyCheck() for PostgreSQL
From:       Dan_Vrátil <dvratil () redhat ! com>
Date:       2013-09-30 17:35:28
Message-ID: E1VQhNY-0006Jx-Lq () scm ! kde ! org
[Download RAW message or body]

Git commit 274b94f86d324d4c34140f54e590a1fb67d5de3d by Dan Vrátil.
Committed on 30/09/2013 at 16:15.
Pushed by dvratil into branch 'master'.

Implement DbInspector::foreignKeyCheck() for PostgreSQL

M  +73   -1    server/src/storage/dbintrospector_impl.cpp
M  +1    -0    server/src/storage/dbintrospector_impl.h

http://commits.kde.org/akonadi/274b94f86d324d4c34140f54e590a1fb67d5de3d

diff --git a/server/src/storage/dbintrospector_impl.cpp \
b/server/src/storage/dbintrospector_impl.cpp index 871bd19..db42d8f 100644
--- a/server/src/storage/dbintrospector_impl.cpp
+++ b/server/src/storage/dbintrospector_impl.cpp
@@ -96,11 +96,83 @@ DbIntrospectorPostgreSql::DbIntrospectorPostgreSql(const \
QSqlDatabase& database)  {
 }
 
+QVector<DbIntrospector::ForeignKey> \
DbIntrospectorPostgreSql::foreignKeyConstraints( const QString &tableName ) \
+{ +  #define TABLE_CONSTRAINTS "information_schema.table_constraints"
+  #define KEY_COLUMN_USAGE "information_schema.key_column_usage"
+  #define REFERENTIAL_CONSTRAINTS \
"information_schema.referential_constraints" +  #define \
CONSTRAINT_COLUMN_USAGE "information_schema.constraint_column_usage" +
+  Query::Condition keyColumnUsageCondition( Query::And );
+  keyColumnUsageCondition.addColumnCondition( QLatin1String( \
TABLE_CONSTRAINTS ".constraint_catalog" ), Query::Equals, +                 \
QLatin1String( KEY_COLUMN_USAGE ".constraint_catalog" ) ); +  \
keyColumnUsageCondition.addColumnCondition( QLatin1String( \
TABLE_CONSTRAINTS ".constraint_schema" ), Query::Equals, +                  \
QLatin1String( KEY_COLUMN_USAGE ".constraint_schema" ) ); +  \
keyColumnUsageCondition.addColumnCondition( QLatin1String( \
TABLE_CONSTRAINTS ".constraint_name" ), Query::Equals, +                    \
QLatin1String( KEY_COLUMN_USAGE ".constraint_name" ) ); +
+  Query::Condition referentialConstraintsCondition( Query::And );
+  referentialConstraintsCondition.addColumnCondition( QLatin1String( \
TABLE_CONSTRAINTS ".constraint_catalog" ), Query::Equals, +                 \
QLatin1String( REFERENTIAL_CONSTRAINTS ".constraint_catalog" ) ); +  \
referentialConstraintsCondition.addColumnCondition( QLatin1String( \
TABLE_CONSTRAINTS ".constraint_schema" ), Query::Equals, +                  \
QLatin1String( REFERENTIAL_CONSTRAINTS ".constraint_schema" ) ); +  \
referentialConstraintsCondition.addColumnCondition( QLatin1String \
(TABLE_CONSTRAINTS ".constraint_name" ), Query::Equals, +                   \
QLatin1String( REFERENTIAL_CONSTRAINTS ".constraint_name" ) ); +
+  Query::Condition constraintColumnUsageCondition( Query::And );
+  constraintColumnUsageCondition.addColumnCondition( QLatin1String( \
REFERENTIAL_CONSTRAINTS ".unique_constraint_catalog" ), Query::Equals, +    \
QLatin1String( CONSTRAINT_COLUMN_USAGE ".constraint_catalog" ) ); +  \
constraintColumnUsageCondition.addColumnCondition( QLatin1String( \
REFERENTIAL_CONSTRAINTS ".unique_constraint_schema" ), Query::Equals, +     \
QLatin1String( CONSTRAINT_COLUMN_USAGE ".constraint_schema" ) ); +  \
constraintColumnUsageCondition.addColumnCondition( QLatin1String( \
REFERENTIAL_CONSTRAINTS ".unique_constraint_name" ), Query::Equals, +       \
QLatin1String( CONSTRAINT_COLUMN_USAGE ".constraint_name" ) ); +
+  QueryBuilder qb( QLatin1String( TABLE_CONSTRAINTS ), \
QueryBuilder::Select ); +  qb.addColumn( QLatin1String( TABLE_CONSTRAINTS \
".constraint_name" ) ); +  qb.addColumn( QLatin1String( KEY_COLUMN_USAGE \
".column_name" ) ); +  qb.addColumn( QLatin1String( CONSTRAINT_COLUMN_USAGE \
".table_name AS referenced_table" ) ); +  qb.addColumn( QLatin1String( \
CONSTRAINT_COLUMN_USAGE ".column_name AS referenced_column" ) ); +  \
qb.addColumn( QLatin1String( REFERENTIAL_CONSTRAINTS ".update_rule" ) ); +  \
qb.addColumn( QLatin1String( REFERENTIAL_CONSTRAINTS ".delete_rule" ) ); +  \
qb.addJoin( QueryBuilder::LeftJoin, QLatin1String( KEY_COLUMN_USAGE ), \
keyColumnUsageCondition ); +  qb.addJoin( QueryBuilder::LeftJoin, \
QLatin1String( REFERENTIAL_CONSTRAINTS ), referentialConstraintsCondition \
); +  qb.addJoin( QueryBuilder::LeftJoin, QLatin1String( \
CONSTRAINT_COLUMN_USAGE ), constraintColumnUsageCondition ); +  \
qb.addValueCondition( QLatin1String( TABLE_CONSTRAINTS ".constraint_type" \
), +                        Query::Equals, QLatin1String( "FOREIGN KEY" ) \
); +  qb.addValueCondition( QLatin1String( TABLE_CONSTRAINTS \
".table_name"), +                        Query::Equals, tableName.toLower() \
); +
+  #undef TABLE_CONSTRAINTS
+  #undef KEY_COLUMN_USAGE
+  #undef REFERENTIAL_CONSTRAINTS
+  #undef CONSTRAINT_COLUMN_USAGE
+
+  if ( !qb.exec() ) {
+    throw DbException( qb.query() );
+  }
+
+  QVector<ForeignKey> result;
+  while ( qb.query().next() ) {
+    ForeignKey fk;
+    fk.name = qb.query().value( 0 ).toString();
+    fk.column = qb.query().value( 1 ).toString();
+    fk.refTable = qb.query().value( 2 ).toString();
+    fk.refColumn = qb.query().value( 3 ).toString();
+    fk.onUpdate = qb.query().value( 4 ).toString();
+    fk.onDelete = qb.query().value( 5 ).toString();
+    result.push_back( fk );
+  }
+
+  return result;
+}
+
 QString DbIntrospectorPostgreSql::hasIndexQuery(const QString& tableName, \
const QString& indexName)  {
   QString query = QLatin1String( "SELECT indexname FROM \
pg_catalog.pg_indexes" );  query += QString::fromLatin1( " WHERE tablename \
                ilike '%1'" ).arg( tableName );
-  query += QString::fromLatin1( " AND  indexname ilike '%1';" ).arg( \
indexName ); +  query += QString::fromLatin1( " AND  indexname ilike '%1'" \
).arg( indexName ); +  query += QString::fromLatin1( " UNION SELECT conname \
FROM pg_catalog.pg_constraint "); +  query += QString::fromLatin1( " WHERE \
conname ilike '%1'" ).arg( indexName );  return query;
 }
 
diff --git a/server/src/storage/dbintrospector_impl.h \
b/server/src/storage/dbintrospector_impl.h index cbd1e14..ccc4813 100644
--- a/server/src/storage/dbintrospector_impl.h
+++ b/server/src/storage/dbintrospector_impl.h
@@ -42,6 +42,7 @@ class DbIntrospectorPostgreSql : public DbIntrospector
 {
   public:
     DbIntrospectorPostgreSql( const QSqlDatabase& database );
+    virtual QVector<ForeignKey> foreignKeyConstraints( const QString \
                &tableName );
     QString hasIndexQuery(const QString& tableName, const QString& \
indexName);  };
 


[prev in list] [next in list] [prev in thread] [next in thread]