[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]