Menu
▾
▴
[pgsqlclient-checkins] SF.net SVN: pgsqlclient: [96] trunk/PostgreSqlClient/source/PostgreSql/Data/Schema
|
From: <car...@us...> - 2006-04-11 19:44:01
|
Revision: 96 Author: carlosga_fb Date: 2006-04-11 12:43:43 -0700 (Tue, 11 Apr 2006) ViewCVS: http://svn.sourceforge.net/pgsqlclient/?rev=96&view=rev Log Message: ----------- ?\194?\183 Tons of Schema changes and bug fixes Modified Paths: -------------- trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/MetaData.xml trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgColumns.cs trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgForeignKeyColumns.cs trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgFunctions.cs trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgPrimaryKeys.cs trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgSchema.cs trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgSchemaFactory.cs trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgViewColumns.cs Added Paths: ----------- trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgUniqueKeys.cs Modified: trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/MetaData.xml =================================================================== --- trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/MetaData.xml 2006-04-11 17:25:08 UTC (rev 95) +++ trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/MetaData.xml 2006-04-11 19:43:43 UTC (rev 96) @@ -189,7 +189,7 @@ </MetaDataCollections> <MetaDataCollections> <CollectionName>PrimaryKeys</CollectionName> - <NumberOfRestrictions>4</NumberOfRestrictions> + <NumberOfRestrictions>3</NumberOfRestrictions> <NumberOfIdentifierParts>0</NumberOfIdentifierParts> <PopulationMechanism>PrepareCollection</PopulationMechanism> </MetaDataCollections> @@ -212,7 +212,7 @@ <NumberOfRestrictions>0</NumberOfRestrictions> <NumberOfIdentifierParts>0</NumberOfIdentifierParts> <PopulationMechanism>SQLCommand</PopulationMechanism> - <PopulationString>SELECT current_database() AS SCHEMA_CATALOG, pg_namespace.nspname AS SCHEMA_NAME, pg_shadow.usename AS SCHEMA_OWNER, pg_description.description AS DESCRIPTION FROM pg_namespace left join pg_shadow ON pg_namespace.nspowner = pg_shadow.usesysid left join pg_description ON pg_namespace.oid = pg_description.objoid ORDER BY pg_namespace.nspname, pg_shadow.usename</PopulationString> + <PopulationString>SELECT current_database() AS SCHEMA_CATALOG, pg_namespace.nspname AS SCHEMA_NAME, pg_shadow.usename AS SCHEMA_OWNER, pg_description.description AS DESCRIPTION, CASE WHEN nspname LIKE 'pg\\_temp\\_%%' THEN 1 WHEN (nspname LIKE 'pg\\_%' OR nspname = 'information_schema') THEN 0 ELSE 3 END AS SCHEMA_TYPE FROM pg_namespace left join pg_shadow ON pg_namespace.nspowner = pg_shadow.usesysid left join pg_description ON pg_namespace.oid = pg_description.objoid ORDER BY pg_namespace.nspname, pg_shadow.usename</PopulationString> </MetaDataCollections> <MetaDataCollections> <CollectionName>Sequences</CollectionName> @@ -252,14 +252,20 @@ <NumberOfIdentifierParts>0</NumberOfIdentifierParts> <PopulationMechanism>PrepareCollection</PopulationMechanism> </MetaDataCollections> - <MetaDataCollections> - <CollectionName>Users</CollectionName> - <NumberOfRestrictions>0</NumberOfRestrictions> - <NumberOfIdentifierParts>0</NumberOfIdentifierParts> - <PopulationMechanism>SQLCommand</PopulationMechanism> - <PopulationString>SELECT pg_shadow.usename AS USER_NAME, pg_shadow.usecreatedb AS CREATE_DATABASE, pg_shadow.usesuper AS IS_SUPERUSER, pg_shadow.usecatupd AS UPDATE_SYSCATALOGS, pg_shadow.passwd AS PASSWORD, pg_shadow.useconfig AS CONFIGURATION FROM pg_shadow ORDER BY pg_shadow.usename</PopulationString> - </MetaDataCollections> - <MetaDataCollections> + <MetaDataCollections> + <CollectionName>UniqueKeys</CollectionName> + <NumberOfRestrictions>3</NumberOfRestrictions> + <NumberOfIdentifierParts>0</NumberOfIdentifierParts> + <PopulationMechanism>PrepareCollection</PopulationMechanism> + </MetaDataCollections> + <MetaDataCollections> + <CollectionName>Users</CollectionName> + <NumberOfRestrictions>0</NumberOfRestrictions> + <NumberOfIdentifierParts>0</NumberOfIdentifierParts> + <PopulationMechanism>SQLCommand</PopulationMechanism> + <PopulationString>SELECT pg_shadow.usename AS USER_NAME, pg_shadow.usecreatedb AS CREATE_DATABASE, pg_shadow.usesuper AS IS_SUPERUSER, pg_shadow.usecatupd AS UPDATE_SYSCATALOGS, pg_shadow.passwd AS PASSWORD, pg_shadow.useconfig AS CONFIGURATION FROM pg_shadow ORDER BY pg_shadow.usename</PopulationString> + </MetaDataCollections> + <MetaDataCollections> <CollectionName>Views</CollectionName> <NumberOfRestrictions>3</NumberOfRestrictions> <NumberOfIdentifierParts>0</NumberOfIdentifierParts> @@ -313,12 +319,6 @@ <RestrictionDefault>column_name</RestrictionDefault> <RestrictionNumber>4</RestrictionNumber> </Restrictions> - <Restrictions> - <CollectionName>PrimaryKeys</CollectionName> - <RestrictionName>Catalog</RestrictionName> - <RestrictionDefault>table_catalog</RestrictionDefault> - <RestrictionNumber>1</RestrictionNumber> - </Restrictions> <Restrictions> <CollectionName>Indexes</CollectionName> <RestrictionName>Catalog</RestrictionName> @@ -536,6 +536,24 @@ <RestrictionNumber>4</RestrictionNumber> </Restrictions> <Restrictions> + <CollectionName>UniqueKeys</CollectionName> + <RestrictionName>Schema</RestrictionName> + <RestrictionDefault>table_catalog</RestrictionDefault> + <RestrictionNumber>1</RestrictionNumber> + </Restrictions> + <Restrictions> + <CollectionName>UniqueKeys</CollectionName> + <RestrictionName>Schema</RestrictionName> + <RestrictionDefault>table_schema</RestrictionDefault> + <RestrictionNumber>2</RestrictionNumber> + </Restrictions> + <Restrictions> + <CollectionName>UniqueKeys</CollectionName> + <RestrictionName>Table</RestrictionName> + <RestrictionDefault>table_name</RestrictionDefault> + <RestrictionNumber>3</RestrictionNumber> + </Restrictions> + <Restrictions> <CollectionName>Views</CollectionName> <RestrictionName>Catalog</RestrictionName> <RestrictionDefault>view_catalog</RestrictionDefault> Modified: trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgColumns.cs =================================================================== --- trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgColumns.cs 2006-04-11 17:25:08 UTC (rev 95) +++ trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgColumns.cs 2006-04-11 19:43:43 UTC (rev 96) @@ -133,6 +133,8 @@ } } + schema.AcceptChanges(); + return schema; } Modified: trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgForeignKeyColumns.cs =================================================================== --- trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgForeignKeyColumns.cs 2006-04-11 17:25:08 UTC (rev 95) +++ trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgForeignKeyColumns.cs 2006-04-11 19:43:43 UTC (rev 96) @@ -145,11 +145,13 @@ } } - // CleanUp - selectColumn.Dispose(); + foreignKeyColumns.AcceptChanges(); foreignKeyColumns.Columns.Remove("CONSTRAINT_TABLE_COLUMNS"); foreignKeyColumns.Columns.Remove("REFERENCED_TABLE_COLUMNS"); + // CleanUp + selectColumn.Dispose(); + return foreignKeyColumns; } Modified: trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgFunctions.cs =================================================================== --- trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgFunctions.cs 2006-04-11 17:25:08 UTC (rev 95) +++ trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgFunctions.cs 2006-04-11 19:43:43 UTC (rev 96) @@ -25,6 +25,7 @@ protected override string BuildSql(string[] restrictions) { + string where = ""; string sql = "SELECT " + "current_database() AS FUNCTION_CATALOG, " + @@ -42,7 +43,8 @@ "pg_proc.proretset AS RETURNS_SET, " + "pg_proc.prorettype AS RETURN_TYPE, " + "pg_proc.pronargs AS ARGUMENT_NUMBER, " + - "pg_proc.proargtypes AS ARGUMENTS, " + + "pg_proc.proargtypes AS ARGUMENT_TYPES, " + + "pg_proc.proargtypes AS ARGUMENT_NAMES, " + "pg_proc.prosrc AS SOURCE, " + "pg_description.description AS DESCRIPTION " + "FROM " + @@ -61,17 +63,25 @@ // FUNCTION_SCHEMA if (restrictions.Length > 1 && restrictions[1] != null) { - sql += String.Format(" and pg_namespace.nspname = '{0}'", restrictions[1]); + if (where.Length > 0) + { + where += " and "; + } + where += String.Format("pg_namespace.nspname = '{0}'", restrictions[1]); } // FUNCTION_NAME if (restrictions.Length > 2 && restrictions[2] != null) { - sql += String.Format(" and pg_proc.proname = '{0}'", restrictions[2]); + if (where.Length > 0) + { + where += " and "; + } + where += String.Format(" and pg_proc.proname = '{0}'", restrictions[2]); } } - sql += " ORDER BY pg_namespace.nspname, pg_proc.proname"; + sql += "WHERE " + where + " ORDER BY pg_namespace.nspname, pg_proc.proname"; return sql; } Modified: trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgPrimaryKeys.cs =================================================================== --- trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgPrimaryKeys.cs 2006-04-11 17:25:08 UTC (rev 95) +++ trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgPrimaryKeys.cs 2006-04-11 19:43:43 UTC (rev 96) @@ -125,6 +125,8 @@ { // CleanUp selectColumn.Dispose(); + + primaryKeyColumns.AcceptChanges(); primaryKeyColumns.Columns.Remove("PK_COLUMNS"); } Modified: trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgSchema.cs =================================================================== --- trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgSchema.cs 2006-04-11 17:25:08 UTC (rev 95) +++ trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgSchema.cs 2006-04-11 19:43:43 UTC (rev 96) @@ -58,7 +58,7 @@ adapter.Fill(dataTable); - this.ProcessResult(connection, dataTable); + dataTable = this.ProcessResult(connection, dataTable); } catch (PgException) { Modified: trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgSchemaFactory.cs =================================================================== --- trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgSchemaFactory.cs 2006-04-11 17:25:08 UTC (rev 95) +++ trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgSchemaFactory.cs 2006-04-11 19:43:43 UTC (rev 96) @@ -138,6 +138,10 @@ schema = new PgTriggers(); break; + case "uniquekeys": + schema = new PgUniqueKeys(); + break; + case "views": schema = new PgViews(); break; Added: trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgUniqueKeys.cs =================================================================== --- trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgUniqueKeys.cs (rev 0) +++ trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgUniqueKeys.cs 2006-04-11 19:43:43 UTC (rev 96) @@ -0,0 +1,138 @@ +/* + * PgSqlClient - ADO.NET Data Provider for PostgreSQL 7.4+ + * + * The contents of this file are subject to the Initial + * Developer's Public License Version 1.0 (the "License"); + * you may not use this file except in compliance with the + * License. + * + * Software distributed under the License is distributed on + * an "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either + * express or implied. See the License for the specific + * language governing rights and limitations under the License. + * + * Copyright (c) 2003, 2006 Carlos Guzman Alvarez + * All Rights Reserved. + */ + +using System; +using System.Data; + +using PostgreSql.Data.PostgreSqlClient; + +namespace PostgreSql.Data.Schema +{ + internal sealed class PgUniqueKeys : PgSchema + { + #region \xB7 Protected Methods \xB7 + + protected override string BuildSql(string[] restrictions) + { + string sql = + "SELECT " + + "current_database() AS TABLE_CATALOG, " + + "pg_namespace.nspname AS TABLE_SCHEMA, " + + "pg_class.relname AS TABLE_NAME, " + + "null AS COLUMN_NAME, " + + "pg_constraint.conname AS UK_NAME, " + + "pg_constraint.conkey AS UK_COLUMNS, " + + "pg_description.description AS DESCRIPTION " + + "FROM pg_constraint " + + "left join pg_class ON pg_constraint.conrelid = pg_class.oid " + + "left join pg_namespace ON pg_constraint.connamespace = pg_namespace.oid " + + "left join pg_description ON pg_constraint.oid = pg_description.objoid " + + "WHERE " + + "pg_constraint.contype = 'u' "; + + if (restrictions != null && restrictions.Length > 0) + { + // TABLE_CATALOG + if (restrictions.Length > 0 && restrictions[0] != null) + { + } + + // TABLE_SCHEMA + if (restrictions.Length > 1 && restrictions[1] != null) + { + sql += String.Format(" and pg_namespace.nspname = '{0}'", restrictions[1]); + } + + // TABLE_NAME + if (restrictions.Length > 2 && restrictions[2] != null) + { + sql += String.Format(" and pg_class.relname = '{0}'", restrictions[2]); + } + } + + sql += " ORDER BY pg_namespace.nspname, pg_class.relname, pg_constraint.conname"; + + return sql; + } + + protected override System.Data.DataTable ProcessResult(PostgreSql.Data.PostgreSqlClient.PgConnection connection, System.Data.DataTable schema) + { + DataTable uniqueKeyColumns = schema.Clone(); + string sql = + "SELECT " + + "column_name " + + "FROM information_schema.columns " + + "WHERE " + + "table_catalog=current_database() AND " + + "table_schema=@tableSchema AND " + + "table_name=@tableName AND " + + "ordinal_position=@ordinalPosition"; + + PgCommand selectColumn = new PgCommand(sql, connection); + selectColumn.Parameters.Add("@tableSchema", PgDbType.Text); + selectColumn.Parameters.Add("@tableName", PgDbType.Text); + selectColumn.Parameters.Add("@ordinalPosition", PgDbType.Text); + + try + { + selectColumn.Prepare(); + + foreach (DataRow row in schema.Rows) + { + Array pkColumns = (Array)row["UK_COLUMNS"]; + + for (int i = 0; i < pkColumns.Length; i++) + { + DataRow primaryKeyColumn = uniqueKeyColumns.NewRow(); + + // Grab the table column name + selectColumn.Parameters["@tableSchema"].Value = row["TABLE_SCHEMA"]; + selectColumn.Parameters["@tableName"].Value = row["TABLE_NAME"]; + selectColumn.Parameters["@ordinalPosition"].Value = Convert.ToInt16(pkColumns.GetValue(i + 1)); + + string pkColumnName = (string)selectColumn.ExecuteScalar(); + + // Create the new primary key column info + primaryKeyColumn["TABLE_CATALOG"] = row["TABLE_CATALOG"]; + primaryKeyColumn["TABLE_SCHEMA"] = row["TABLE_SCHEMA"]; + primaryKeyColumn["TABLE_NAME"] = row["TABLE_NAME"]; + primaryKeyColumn["COLUMN_NAME"] = pkColumnName; + primaryKeyColumn["DESCRIPTION"] = row["DESCRIPTION"]; + + uniqueKeyColumns.Rows.Add(primaryKeyColumn); + } + } + } + catch + { + throw; + } + finally + { + // CleanUp + selectColumn.Dispose(); + + uniqueKeyColumns.AcceptChanges(); + uniqueKeyColumns.Columns.Remove("UK_COLUMNS"); + } + + return uniqueKeyColumns; + } + + #endregion + } +} Modified: trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgViewColumns.cs =================================================================== --- trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgViewColumns.cs 2006-04-11 17:25:08 UTC (rev 95) +++ trunk/PostgreSqlClient/source/PostgreSql/Data/Schema/PgViewColumns.cs 2006-04-11 19:43:43 UTC (rev 96) @@ -30,7 +30,8 @@ "current_database() AS VIEW_CATALOG, " + "pg_namespace.nspname AS VIEW_SCHEMA, " + "pg_class.relname AS VIEW_NAME, " + - "pg_attribute.attname AS COLUMN_NAME " + + "pg_attribute.attname AS COLUMN_NAME, " + + "pg_attribute.attnum AS ORDINAL_POSITION " + "FROM " + "pg_class " + "left join pg_namespace ON pg_class.relnamespace = pg_namespace.oid " + This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
