[pgsqlclient-checkins] SF.net SVN: pgsqlclient: [46] trunk/pgsqlclient/source/PostgreSql/Data/Schema/PgForeignKeys.cs

[<car...@us...>]

Revision: 46
Author:   carlosga_fb
Date:     2006-03-22 06:39:13 -0800 (Wed, 22 Mar 2006)
ViewCVS:  http://svn.sourceforge.net/pgsqlclient/?rev=46&view=rev

Log Message:
-----------
Added support for the Foreign Key schema

Modified Paths:
--------------
    trunk/pgsqlclient/source/PostgreSql/Data/Schema/PgForeignKeys.cs
Modified: trunk/pgsqlclient/source/PostgreSql/Data/Schema/PgForeignKeys.cs
===================================================================
--- trunk/pgsqlclient/source/PostgreSql/Data/Schema/PgForeignKeys.cs	2006-03-22 12:10:58 UTC (rev 45)
+++ trunk/pgsqlclient/source/PostgreSql/Data/Schema/PgForeignKeys.cs	2006-03-22 14:39:13 UTC (rev 46)
@@ -25,7 +25,75 @@
 
         protected override string BuildSql(string[] restrictions)
         {
-            throw new NotImplementedException();
+            string sql =
+                "SELECT " +
+                    "null AS CONSTRAINT_CATALOG, " +
+                    "pg_namespace.nspname AS CONSTRAINT_SCHEMA, " +
+                    "pg_constraint.conname AS CONSTAINT_NAME, " +
+                    "null AS TABLE_CATALOG, " +
+                    "constraint_table_namespace.nspname AS TABLE_SCHEMA, " +
+                    "constraint_table.relname AS TABLE_NAME, " +
+                    "null AS REFERENCED_TABLE_CATALOG, " +
+                    "referenced_table_namespace.nspname AS REFERENCED_TABLE_SCHEMA, " +
+                    "referenced_table.relname AS REFERENCED_TABLE_NAME, " +
+                    "case pg_constraint.confupdtype " +
+                        "when 'a' then 'NO ACTION' " +
+                        "when 'r' then 'RESTRICT' " +
+                        "when 'c' then 'CASCADE' " +
+                        "when 'd' then 'SET DEFAULT' " +
+                        "when 'n' then 'SET NULL'  " +
+                    "END AS UPDATE_RULE, " +
+                    "case pg_constraint.confdeltype " +
+                        "when 'a' then 'NO ACTION' " +
+                        "when 'r' then 'RESTRICT' " +
+                        "when 'c' then 'CASCADE' " +
+                        "when 'd' then 'SET DEFAULT' " +
+                        "when 'n' then 'SET NULL' " +
+                    "end AS DELETE_RULE, " +
+                    "pg_constraint.condeferrable AS DEFERRABILITY, " +
+                    "pg_constraint.condeferred AS IS_DEFERRED, " +
+                    "pg_description.description AS DESCRIPTION " +
+                "FROM " +
+                    "pg_constraint " +
+                        "left join pg_namespace ON pg_constraint.connamespace = pg_namespace.oid " +
+                        "left join pg_class as constraint_table ON pg_constraint.conrelid = constraint_table.oid " +
+                        "left join pg_namespace as constraint_table_namespace ON constraint_table.relnamespace = constraint_table_namespace.oid " +
+                        "right join pg_class as referenced_table ON pg_constraint.confrelid = referenced_table.oid " +
+                        "left join pg_namespace as referenced_table_namespace ON referenced_table.relnamespace = referenced_table_namespace.oid " +
+                        "left join pg_description ON pg_constraint.oid = pg_description.objoid " +
+                "WHERE " +
+                    "pg_constraint.contype = 'f' ";
+
+                if (restrictions != null && restrictions.Length > 0)
+                {
+
+                    /* CONSTRAINT_CATALOG	*/
+                    if (restrictions.Length > 0 && restrictions[0] != null)
+                    {
+                    }
+
+                    /* CONSTRAINT_SCHEMA */
+                    if (restrictions.Length > 1 && restrictions[1] != null)
+                    {
+                        sql += String.Format(" and pg_namespace.nspname = '{0}'", restrictions[1]);
+                    }
+
+                    /* TABLE_NAME */
+                    if (restrictions.Length >= 3 && restrictions[2] != null)
+                    {
+                        sql += String.Format(" and constraint_table.relname = '{0}'", restrictions[2]);
+                    }
+
+                    /* CONSTRAINT_NAME */
+                    if (restrictions.Length >= 4 && restrictions[3] != null)
+                    {
+                        sql += String.Format(" and pg_constraint.conname = '{0}'", restrictions[3]);
+                    }
+                }
+
+                sql += "ORDER BY pg_namespace.nspname, constraint_table.relname, pg_constraint.conname";
+
+            return sql;
         }
 
         #endregion


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.