OracleSqlParser.cs source code in C# .NET

Source code for the .NET framework in C#

                        

Code:

/ Dotnetfx_Win7_3.5.1 / Dotnetfx_Win7_3.5.1 / 3.5.1 / DEVDIV / depot / DevDiv / releases / whidbey / NetFXspW7 / ndp / fx / src / DataOracleClient / System / Data / OracleClient / OracleSqlParser.cs / 1 / OracleSqlParser.cs

                            //------------------------------------------------------------------------------ 
// 
//      Copyright (c) Microsoft Corporation.  All rights reserved.
// 
// [....] 
//-----------------------------------------------------------------------------
 
namespace System.Data.OracleClient 
{
 
    using System;
    using System.Collections;
    using System.Data.Common;
    using System.Diagnostics; 
    using System.Globalization;
    using System.Text; 
 
    //---------------------------------------------------------------------
    // OracleSqlParser 
    //
    //  The overrides to DbSqlParser to create an Oracle-specific SQL
    //  parser.
    // 
    sealed internal class OracleSqlParser : DbSqlParser {
        private const string SynonymQueryBegin = 
            "select" 
            +" table_owner,"
            +" table_name " 
            +"from"
            +" all_synonyms "
            +"where"
            ; 

        private const string SynonymQueryNoSchema = 
            " owner in ('PUBLIC', user)" 
            ;
 
        private const string SynonymQuerySchema =
            " owner = '"
            ;
 
        private const string SynonymQueryTable =
            " and synonym_name = '" 
            ; 

        // @devnote If there are both public and private synonyms, Oracle will 
        //          use the private one, so we need to do that too.
        private const string SynonymQueryEnd =
            "' order by decode(owner, 'PUBLIC', 2, 1)"
            ; 

 
        static private readonly string ConstraintOwnerParameterName = "OwnerName"; 
        static private readonly string ConstraintTableParameterName = "TableName";
 
        static private readonly string ConstraintQuery1a =
            "select"
                +" ac.constraint_name key_name,"
                +" acc.column_name key_col," 
                +((int)DbSqlParserColumn.ConstraintType.PrimaryKey).ToString(CultureInfo.InvariantCulture) + " "
                +"from" 
                +" all_cons_columns acc," 
                +" all_constraints ac "
                +"where acc.owner = ac.owner" 
                +" and acc.constraint_name = ac.constraint_name"
                +" and acc.table_name = ac.table_name"
                +" and ac.constraint_type = 'P'"
                ; 

        static private readonly string ConstraintQuery1b_ownerDefault = 
                " and ac.owner = user" 
                ;
 
        static private readonly string ConstraintQuery1b_ownerIsKnown =
                " and ac.owner = :OwnerName"
                ;
 
        static private readonly string ConstraintQuery1c =
                 " and ac.table_name = :TableName " 
                +"order by acc.constraint_name" 
                ;
 
        static private readonly string ConstraintQuery2a =
            "select"
                +" aic.index_name key_name,"
                +" aic.column_name key_col," 
                +((int)DbSqlParserColumn.ConstraintType.UniqueConstraint).ToString(CultureInfo.InvariantCulture) + " "
                +"from" 
                +" all_ind_columns aic," 
                +" all_indexes ai "
                +"where aic.table_owner = ai.table_owner" 
                +" and aic.table_name = ai.table_name"
                +" and aic.index_name = ai.index_name"
                +" and ai.uniqueness = 'UNIQUE'"
                ; 

        static private readonly string ConstraintQuery2b_ownerDefault = 
                " and ai.owner = user" 
                ;
 
        static private readonly string ConstraintQuery2b_ownerIsKnown =
                " and ai.owner = :OwnerName"
                ;
 
        static private readonly string ConstraintQuery2c =
                 " and ai.table_name = :TableName " 
                +"order by aic.index_name" 
                ;
 

        private OracleConnection            _connection;
        static private readonly string      _quoteCharacter = "\"";
 
        // \\p{Lo} = OtherLetter
        // \\p{Lu} = UppercaseLetter 
        // \\p{Ll} = LowercaseLetter 
        // \\p{Lm} = ModifierLetter
        // \\p{Nd} = DecimalDigitNumber 

        // the Oracle meta data XML creation code in mdstest.cs uses copies of the strings in the call below
        // to build reqular expressions included in data source information collection
        // if they are changed here coordinated changes are needed in mdstest.cs and the oracle 
        // xml files must be rebuilt.
        static private readonly string _regexPattern = DbSqlParser.CreateRegexPattern( 
                                            "[\\p{Lo}\\p{Lu}\\p{Ll}\\p{Lm}\uff3f_#$]", 
                                            "[\\p{Lo}\\p{Lu}\\p{Ll}\\p{Lm}\\p{Nd}\uff3f_#$]",
                                            _quoteCharacter, 
                                            "([^\"]|\"\")*",
                                            _quoteCharacter,
                                            "(" + "'" + "([^']|'')*" + "'" + ")"
                                            ); 

        internal OracleSqlParser() : base ( _quoteCharacter, _quoteCharacter, _regexPattern ) {} 
 
        static internal string CatalogCase(string value) {
            //  Converts the specified value to the correct case for the catalog 
            //  entries, using quoting rules

            if (ADP.IsEmpty(value))
                return String.Empty; 

            if ('"' == value[0]) 
                return value.Substring(1, value.Length-2); 

            return value.ToUpper(CultureInfo.CurrentCulture); 
        }

        protected override bool CatalogMatch(string valueA, string valueB) {
 
            //  compares the two values in catalog order, taking into account
            //  quoting rules, and case-sensitivity rules, and returns true if 
            //  they match. 

            // the values are equal if they're both null or empty. 
            if (ADP.IsEmpty(valueA) && ADP.IsEmpty(valueB)) {
                return true;
            }
 
            // the values are not equal if only one is null or empty
            if (ADP.IsEmpty(valueA) || ADP.IsEmpty(valueB)) { 
                return false; 
            }
 
            // Now, we have two non-null, non-empty values; adjust for possible quotes

            bool isSensitiveA = ('"' == valueA[0]);
            int  offsetA = 0; 
            int  lengthA = valueA.Length;
 
            bool isSensitiveB = ('"' == valueB[0]); 
            int  offsetB = 0;
            int  lengthB = valueB.Length; 

            if (isSensitiveA) {
                offsetA++;
                lengthA -= 2; 
            }
 
            if (isSensitiveB) { 
                offsetB++;
                lengthB -= 2; 
            }

            CompareOptions  opt = CompareOptions.IgnoreKanaType | CompareOptions.IgnoreWidth;
 
            if (!isSensitiveA || !isSensitiveB)
                opt |= CompareOptions.IgnoreCase; 
 
            int result = CultureInfo.CurrentCulture.CompareInfo.Compare(valueA, offsetA, lengthA,
                                                                        valueB, offsetB, lengthB, 
                                                                        opt);

            return (0 == result);
        } 

        private DbSqlParserColumn FindConstraintColumn(string schemaName, string tableName, string columnName) { 
 
            //  Searches the collection of parsed column information for the
            //  specific column in the specific table, and returns the parsed 
            //  column information if it's found.  If not found then it returns
            //  null instead.

            DbSqlParserColumnCollection columns = Columns; 
            int                         columnCount = columns.Count;
 
            for (int i = 0; i < columnCount; ++i) { 
                DbSqlParserColumn column = columns[i];
 
                if (CatalogMatch(column.SchemaName,   schemaName)
                 && CatalogMatch(column.TableName,    tableName)
                 && CatalogMatch(column.ColumnName,   columnName)) {
                    return column; 
                }
            } 
            return null; 
        }
 
        private sealed class ConstraintColumn {
            internal string columnName;
            internal DbSqlParserColumn.ConstraintType constraintType;
            internal DbSqlParserColumn parsedColumn; 
        }
 
 
        protected override void GatherKeyColumns (DbSqlParserTable table) {
            //  Called after the table and column information is completed to 
            //  identify which columns in the select-list are key columns for
            //  their table.

            using (OracleCommand cmd = _connection.CreateCommand()) { 

                cmd.Transaction = _connection.Transaction; // must set the transaction context to be the same as the command, or we'll throw when we execute. 
 
                string schemaName = CatalogCase(table.SchemaName);
                string tableName  = CatalogCase(table.TableName); 
                string synonymSchemaName = schemaName;  // if there isn't a synonym, just use the original values.
                string synonymTableName  = tableName;   // if there isn't a synonym, just use the original values.

                // First, we have to "dereference" a synonym, if it was specified, because 
                // synonyms don't have catalog items stored for them, they're for the table
                // or view that the synonym references. 
 
                cmd.CommandText = GetSynonymQueryStatement(schemaName, tableName);
 
                using (OracleDataReader rdr = cmd.ExecuteReader()) {
                    if (rdr.Read()) {
                        synonymSchemaName   = rdr.GetString(0);
                        synonymTableName    = rdr.GetString(1); 
                    }
                } 
 
                // Now we have the real schema name and table name, go and derive the key
                // columns 


                StringBuilder sb1 = new StringBuilder(ConstraintQuery1a);
                StringBuilder sb2 = new StringBuilder(ConstraintQuery2a); 

                // First build the user or schema string so we can re-use it. 
                if (ADP.IsEmpty(synonymSchemaName)) { 
                    sb1.Append(ConstraintQuery1b_ownerDefault);
                    sb2.Append(ConstraintQuery2b_ownerDefault); 
                }
                else {
                    cmd.Parameters.Add(new OracleParameter(ConstraintOwnerParameterName, DbType.String)).Value = synonymSchemaName;
                    sb1.Append(ConstraintQuery1b_ownerIsKnown); 
                    sb2.Append(ConstraintQuery2b_ownerIsKnown);
                } 
 
                cmd.Parameters.Add(new OracleParameter(ConstraintTableParameterName, DbType.String)).Value = synonymTableName;
                sb1.Append(ConstraintQuery1c); 
                sb2.Append(ConstraintQuery2c);

                string[] constraintQueries = new string[] { sb1.ToString(), sb2.ToString() };
 
                foreach (string constraintQuery in constraintQueries) {
                    cmd.CommandText = constraintQuery; 
 
                    using (OracleDataReader rdr = cmd.ExecuteReader()) {
                        ConstraintColumn constraintColumn; 

                        ArrayList constraintColumns = new ArrayList();

                        bool moreConstraints = rdr.Read(); 
                        bool foundAllColumns = false;
 
                        while (moreConstraints) { 
                            constraintColumns.Clear();
                            string constraintName = rdr.GetString(0); 

                            // Read all the columns for a single constraint from the
                            // data reader.
 
                            do {
                                constraintColumn = new ConstraintColumn(); 
                                constraintColumn.columnName     = rdr.GetString(1); 
                                constraintColumn.constraintType = (DbSqlParserColumn.ConstraintType)((int)rdr.GetDecimal(2));
                                constraintColumn.parsedColumn   = null; // found below 

    //Debug.WriteLine(String.Format("ConstraintName={0} ColumnName={1} Type={2}", constraintName, constraintColumn.columnName, constraintColumn.constraintType));
                                constraintColumns.Add(constraintColumn);
 
                                moreConstraints = rdr.Read();
                            } while (moreConstraints && constraintName == rdr.GetString(0)); 
 
                            foundAllColumns = true;
 
                            // Now determine if all the constraint's columns are in
                            // the projection list for the query.  We don't want to
                            // update the key information in the column information
                            // unless all of the columns that comprise a key are in 
                            // the projection list.
 
                            // NOTE: we expect that uniqueness constraints are returned 
                            // as single row constraints, so if the column is unique,
                            // we'll be able to mark it. 

                            for (int i = 0; i < constraintColumns.Count; i++) {
                                constraintColumn = (ConstraintColumn)constraintColumns[i];
 
                                constraintColumn.parsedColumn = FindConstraintColumn(
                                                                            schemaName, 
                                                                            tableName, 
                                                                            constraintColumn.columnName
                                                                            ); 

                                if (null == constraintColumn.parsedColumn) {
                                    foundAllColumns = false;
                                    break; 
                                }
                            } 
 
                            // If we've found all the constraint's columns in the query,
                            // it's OK to update the constraint information in the parsed 
                            // column information

                            if (foundAllColumns) {
                                for (int i = 0; i < constraintColumns.Count; i++) { 
                                    constraintColumn = (ConstraintColumn)constraintColumns[i];
                                    constraintColumn.parsedColumn.SetConstraint(constraintColumn.constraintType); 
                                } 
                                break;
                            } 
                        }

                        if (foundAllColumns) {
                            break; 
                        }
                    } 
                } 
            }
        } 

        override protected DbSqlParserColumnCollection GatherTableColumns (DbSqlParserTable table) {
            //  Called to get a column list for the table specified.
 
            OciStatementHandle      statementHandle = new OciStatementHandle(_connection.ServiceContextHandle);
            OciErrorHandle       errorHandle = _connection.ErrorHandle; 
            StringBuilder   sb = new StringBuilder(); 
            string          schemaName      = table.SchemaName;
            string          tableName       = table.TableName; 
            string          columnName;
            int             tableColumnCount;
            int             rc;
            string          tempStatement; 

            DbSqlParserColumnCollection columns = new DbSqlParserColumnCollection(); 
 
            Debug.Assert (ADP.IsEmpty(table.DatabaseName), "oracle doesn't support 4 part names!");
 
            sb.Append("select * from ");

            if (!ADP.IsEmpty(schemaName)) {
                sb.Append(schemaName); 
                sb.Append(".");
            } 
 
            sb.Append(tableName);
 
            tempStatement = sb.ToString();

            rc = TracedNativeMethods.OCIStmtPrepare(
                                    statementHandle, 
                                    errorHandle,
                                    tempStatement, 
                                    OCI.SYNTAX.OCI_NTV_SYNTAX, 
                                    OCI.MODE.OCI_DEFAULT,
                                    _connection 
                                    );

            if (0 == rc) {
                rc = TracedNativeMethods.OCIStmtExecute( 
                                        _connection.ServiceContextHandle,
                                        statementHandle, 
                                        errorHandle, 
                                        0,                          // iters
                                        OCI.MODE.OCI_DESCRIBE_ONLY  // mode 
                                        );

                if (0 == rc) {
                    // Build the column list for the table 
                    statementHandle.GetAttribute(OCI.ATTR.OCI_ATTR_PARAM_COUNT, out tableColumnCount, errorHandle);
 
                    for (int j = 0; j < tableColumnCount; j++) { 
                        OciParameterDescriptor describeHandle = statementHandle.GetDescriptor(j, errorHandle);
                        describeHandle.GetAttribute(OCI.ATTR.OCI_ATTR_NAME, out columnName, errorHandle, _connection); 
                        OciHandle.SafeDispose(ref describeHandle);

                        columnName = QuotePrefixCharacter + columnName + QuoteSuffixCharacter;
 
                        columns.Add(null, schemaName, tableName, columnName, null);
                    } 
 
                    // Now, derive the key information for the statement and update the column list
                    // with it. 
                }
            }

            // Clean up and return; 
            OciHandle.SafeDispose(ref statementHandle);
 
            return columns; 
        }
 
        private string GetSynonymQueryStatement (string schemaName, string tableName) {
            //  Returns the statement text necessary to determine the base table
            //  behind a synonym.
 
            StringBuilder   sb = new StringBuilder();
 
            sb.Append(SynonymQueryBegin); 
            if (ADP.IsEmpty(schemaName)) {
                sb.Append(SynonymQueryNoSchema); 
            }
            else {
                sb.Append(SynonymQuerySchema);
                sb.Append(schemaName); 
                sb.Append("'");
            } 
 
            sb.Append(SynonymQueryTable);
            sb.Append(tableName); 
            sb.Append(SynonymQueryEnd);

            return sb.ToString();
        } 

        internal void Parse (string statementText, OracleConnection connection) { 
            //  Oracle specific Parse method, to allow us to capture a connection 
            //  for use by the schema gathering methods later.
 
            _connection = connection;
            Parse(statementText);
        }
    } 
}

// File provided for Reference Use Only by Microsoft Corporation (c) 2007.
//------------------------------------------------------------------------------ 
// 
//      Copyright (c) Microsoft Corporation.  All rights reserved.
// 
// [....] 
//-----------------------------------------------------------------------------
 
namespace System.Data.OracleClient 
{
 
    using System;
    using System.Collections;
    using System.Data.Common;
    using System.Diagnostics; 
    using System.Globalization;
    using System.Text; 
 
    //---------------------------------------------------------------------
    // OracleSqlParser 
    //
    //  The overrides to DbSqlParser to create an Oracle-specific SQL
    //  parser.
    // 
    sealed internal class OracleSqlParser : DbSqlParser {
        private const string SynonymQueryBegin = 
            "select" 
            +" table_owner,"
            +" table_name " 
            +"from"
            +" all_synonyms "
            +"where"
            ; 

        private const string SynonymQueryNoSchema = 
            " owner in ('PUBLIC', user)" 
            ;
 
        private const string SynonymQuerySchema =
            " owner = '"
            ;
 
        private const string SynonymQueryTable =
            " and synonym_name = '" 
            ; 

        // @devnote If there are both public and private synonyms, Oracle will 
        //          use the private one, so we need to do that too.
        private const string SynonymQueryEnd =
            "' order by decode(owner, 'PUBLIC', 2, 1)"
            ; 

 
        static private readonly string ConstraintOwnerParameterName = "OwnerName"; 
        static private readonly string ConstraintTableParameterName = "TableName";
 
        static private readonly string ConstraintQuery1a =
            "select"
                +" ac.constraint_name key_name,"
                +" acc.column_name key_col," 
                +((int)DbSqlParserColumn.ConstraintType.PrimaryKey).ToString(CultureInfo.InvariantCulture) + " "
                +"from" 
                +" all_cons_columns acc," 
                +" all_constraints ac "
                +"where acc.owner = ac.owner" 
                +" and acc.constraint_name = ac.constraint_name"
                +" and acc.table_name = ac.table_name"
                +" and ac.constraint_type = 'P'"
                ; 

        static private readonly string ConstraintQuery1b_ownerDefault = 
                " and ac.owner = user" 
                ;
 
        static private readonly string ConstraintQuery1b_ownerIsKnown =
                " and ac.owner = :OwnerName"
                ;
 
        static private readonly string ConstraintQuery1c =
                 " and ac.table_name = :TableName " 
                +"order by acc.constraint_name" 
                ;
 
        static private readonly string ConstraintQuery2a =
            "select"
                +" aic.index_name key_name,"
                +" aic.column_name key_col," 
                +((int)DbSqlParserColumn.ConstraintType.UniqueConstraint).ToString(CultureInfo.InvariantCulture) + " "
                +"from" 
                +" all_ind_columns aic," 
                +" all_indexes ai "
                +"where aic.table_owner = ai.table_owner" 
                +" and aic.table_name = ai.table_name"
                +" and aic.index_name = ai.index_name"
                +" and ai.uniqueness = 'UNIQUE'"
                ; 

        static private readonly string ConstraintQuery2b_ownerDefault = 
                " and ai.owner = user" 
                ;
 
        static private readonly string ConstraintQuery2b_ownerIsKnown =
                " and ai.owner = :OwnerName"
                ;
 
        static private readonly string ConstraintQuery2c =
                 " and ai.table_name = :TableName " 
                +"order by aic.index_name" 
                ;
 

        private OracleConnection            _connection;
        static private readonly string      _quoteCharacter = "\"";
 
        // \\p{Lo} = OtherLetter
        // \\p{Lu} = UppercaseLetter 
        // \\p{Ll} = LowercaseLetter 
        // \\p{Lm} = ModifierLetter
        // \\p{Nd} = DecimalDigitNumber 

        // the Oracle meta data XML creation code in mdstest.cs uses copies of the strings in the call below
        // to build reqular expressions included in data source information collection
        // if they are changed here coordinated changes are needed in mdstest.cs and the oracle 
        // xml files must be rebuilt.
        static private readonly string _regexPattern = DbSqlParser.CreateRegexPattern( 
                                            "[\\p{Lo}\\p{Lu}\\p{Ll}\\p{Lm}\uff3f_#$]", 
                                            "[\\p{Lo}\\p{Lu}\\p{Ll}\\p{Lm}\\p{Nd}\uff3f_#$]",
                                            _quoteCharacter, 
                                            "([^\"]|\"\")*",
                                            _quoteCharacter,
                                            "(" + "'" + "([^']|'')*" + "'" + ")"
                                            ); 

        internal OracleSqlParser() : base ( _quoteCharacter, _quoteCharacter, _regexPattern ) {} 
 
        static internal string CatalogCase(string value) {
            //  Converts the specified value to the correct case for the catalog 
            //  entries, using quoting rules

            if (ADP.IsEmpty(value))
                return String.Empty; 

            if ('"' == value[0]) 
                return value.Substring(1, value.Length-2); 

            return value.ToUpper(CultureInfo.CurrentCulture); 
        }

        protected override bool CatalogMatch(string valueA, string valueB) {
 
            //  compares the two values in catalog order, taking into account
            //  quoting rules, and case-sensitivity rules, and returns true if 
            //  they match. 

            // the values are equal if they're both null or empty. 
            if (ADP.IsEmpty(valueA) && ADP.IsEmpty(valueB)) {
                return true;
            }
 
            // the values are not equal if only one is null or empty
            if (ADP.IsEmpty(valueA) || ADP.IsEmpty(valueB)) { 
                return false; 
            }
 
            // Now, we have two non-null, non-empty values; adjust for possible quotes

            bool isSensitiveA = ('"' == valueA[0]);
            int  offsetA = 0; 
            int  lengthA = valueA.Length;
 
            bool isSensitiveB = ('"' == valueB[0]); 
            int  offsetB = 0;
            int  lengthB = valueB.Length; 

            if (isSensitiveA) {
                offsetA++;
                lengthA -= 2; 
            }
 
            if (isSensitiveB) { 
                offsetB++;
                lengthB -= 2; 
            }

            CompareOptions  opt = CompareOptions.IgnoreKanaType | CompareOptions.IgnoreWidth;
 
            if (!isSensitiveA || !isSensitiveB)
                opt |= CompareOptions.IgnoreCase; 
 
            int result = CultureInfo.CurrentCulture.CompareInfo.Compare(valueA, offsetA, lengthA,
                                                                        valueB, offsetB, lengthB, 
                                                                        opt);

            return (0 == result);
        } 

        private DbSqlParserColumn FindConstraintColumn(string schemaName, string tableName, string columnName) { 
 
            //  Searches the collection of parsed column information for the
            //  specific column in the specific table, and returns the parsed 
            //  column information if it's found.  If not found then it returns
            //  null instead.

            DbSqlParserColumnCollection columns = Columns; 
            int                         columnCount = columns.Count;
 
            for (int i = 0; i < columnCount; ++i) { 
                DbSqlParserColumn column = columns[i];
 
                if (CatalogMatch(column.SchemaName,   schemaName)
                 && CatalogMatch(column.TableName,    tableName)
                 && CatalogMatch(column.ColumnName,   columnName)) {
                    return column; 
                }
            } 
            return null; 
        }
 
        private sealed class ConstraintColumn {
            internal string columnName;
            internal DbSqlParserColumn.ConstraintType constraintType;
            internal DbSqlParserColumn parsedColumn; 
        }
 
 
        protected override void GatherKeyColumns (DbSqlParserTable table) {
            //  Called after the table and column information is completed to 
            //  identify which columns in the select-list are key columns for
            //  their table.

            using (OracleCommand cmd = _connection.CreateCommand()) { 

                cmd.Transaction = _connection.Transaction; // must set the transaction context to be the same as the command, or we'll throw when we execute. 
 
                string schemaName = CatalogCase(table.SchemaName);
                string tableName  = CatalogCase(table.TableName); 
                string synonymSchemaName = schemaName;  // if there isn't a synonym, just use the original values.
                string synonymTableName  = tableName;   // if there isn't a synonym, just use the original values.

                // First, we have to "dereference" a synonym, if it was specified, because 
                // synonyms don't have catalog items stored for them, they're for the table
                // or view that the synonym references. 
 
                cmd.CommandText = GetSynonymQueryStatement(schemaName, tableName);
 
                using (OracleDataReader rdr = cmd.ExecuteReader()) {
                    if (rdr.Read()) {
                        synonymSchemaName   = rdr.GetString(0);
                        synonymTableName    = rdr.GetString(1); 
                    }
                } 
 
                // Now we have the real schema name and table name, go and derive the key
                // columns 


                StringBuilder sb1 = new StringBuilder(ConstraintQuery1a);
                StringBuilder sb2 = new StringBuilder(ConstraintQuery2a); 

                // First build the user or schema string so we can re-use it. 
                if (ADP.IsEmpty(synonymSchemaName)) { 
                    sb1.Append(ConstraintQuery1b_ownerDefault);
                    sb2.Append(ConstraintQuery2b_ownerDefault); 
                }
                else {
                    cmd.Parameters.Add(new OracleParameter(ConstraintOwnerParameterName, DbType.String)).Value = synonymSchemaName;
                    sb1.Append(ConstraintQuery1b_ownerIsKnown); 
                    sb2.Append(ConstraintQuery2b_ownerIsKnown);
                } 
 
                cmd.Parameters.Add(new OracleParameter(ConstraintTableParameterName, DbType.String)).Value = synonymTableName;
                sb1.Append(ConstraintQuery1c); 
                sb2.Append(ConstraintQuery2c);

                string[] constraintQueries = new string[] { sb1.ToString(), sb2.ToString() };
 
                foreach (string constraintQuery in constraintQueries) {
                    cmd.CommandText = constraintQuery; 
 
                    using (OracleDataReader rdr = cmd.ExecuteReader()) {
                        ConstraintColumn constraintColumn; 

                        ArrayList constraintColumns = new ArrayList();

                        bool moreConstraints = rdr.Read(); 
                        bool foundAllColumns = false;
 
                        while (moreConstraints) { 
                            constraintColumns.Clear();
                            string constraintName = rdr.GetString(0); 

                            // Read all the columns for a single constraint from the
                            // data reader.
 
                            do {
                                constraintColumn = new ConstraintColumn(); 
                                constraintColumn.columnName     = rdr.GetString(1); 
                                constraintColumn.constraintType = (DbSqlParserColumn.ConstraintType)((int)rdr.GetDecimal(2));
                                constraintColumn.parsedColumn   = null; // found below 

    //Debug.WriteLine(String.Format("ConstraintName={0} ColumnName={1} Type={2}", constraintName, constraintColumn.columnName, constraintColumn.constraintType));
                                constraintColumns.Add(constraintColumn);
 
                                moreConstraints = rdr.Read();
                            } while (moreConstraints && constraintName == rdr.GetString(0)); 
 
                            foundAllColumns = true;
 
                            // Now determine if all the constraint's columns are in
                            // the projection list for the query.  We don't want to
                            // update the key information in the column information
                            // unless all of the columns that comprise a key are in 
                            // the projection list.
 
                            // NOTE: we expect that uniqueness constraints are returned 
                            // as single row constraints, so if the column is unique,
                            // we'll be able to mark it. 

                            for (int i = 0; i < constraintColumns.Count; i++) {
                                constraintColumn = (ConstraintColumn)constraintColumns[i];
 
                                constraintColumn.parsedColumn = FindConstraintColumn(
                                                                            schemaName, 
                                                                            tableName, 
                                                                            constraintColumn.columnName
                                                                            ); 

                                if (null == constraintColumn.parsedColumn) {
                                    foundAllColumns = false;
                                    break; 
                                }
                            } 
 
                            // If we've found all the constraint's columns in the query,
                            // it's OK to update the constraint information in the parsed 
                            // column information

                            if (foundAllColumns) {
                                for (int i = 0; i < constraintColumns.Count; i++) { 
                                    constraintColumn = (ConstraintColumn)constraintColumns[i];
                                    constraintColumn.parsedColumn.SetConstraint(constraintColumn.constraintType); 
                                } 
                                break;
                            } 
                        }

                        if (foundAllColumns) {
                            break; 
                        }
                    } 
                } 
            }
        } 

        override protected DbSqlParserColumnCollection GatherTableColumns (DbSqlParserTable table) {
            //  Called to get a column list for the table specified.
 
            OciStatementHandle      statementHandle = new OciStatementHandle(_connection.ServiceContextHandle);
            OciErrorHandle       errorHandle = _connection.ErrorHandle; 
            StringBuilder   sb = new StringBuilder(); 
            string          schemaName      = table.SchemaName;
            string          tableName       = table.TableName; 
            string          columnName;
            int             tableColumnCount;
            int             rc;
            string          tempStatement; 

            DbSqlParserColumnCollection columns = new DbSqlParserColumnCollection(); 
 
            Debug.Assert (ADP.IsEmpty(table.DatabaseName), "oracle doesn't support 4 part names!");
 
            sb.Append("select * from ");

            if (!ADP.IsEmpty(schemaName)) {
                sb.Append(schemaName); 
                sb.Append(".");
            } 
 
            sb.Append(tableName);
 
            tempStatement = sb.ToString();

            rc = TracedNativeMethods.OCIStmtPrepare(
                                    statementHandle, 
                                    errorHandle,
                                    tempStatement, 
                                    OCI.SYNTAX.OCI_NTV_SYNTAX, 
                                    OCI.MODE.OCI_DEFAULT,
                                    _connection 
                                    );

            if (0 == rc) {
                rc = TracedNativeMethods.OCIStmtExecute( 
                                        _connection.ServiceContextHandle,
                                        statementHandle, 
                                        errorHandle, 
                                        0,                          // iters
                                        OCI.MODE.OCI_DESCRIBE_ONLY  // mode 
                                        );

                if (0 == rc) {
                    // Build the column list for the table 
                    statementHandle.GetAttribute(OCI.ATTR.OCI_ATTR_PARAM_COUNT, out tableColumnCount, errorHandle);
 
                    for (int j = 0; j < tableColumnCount; j++) { 
                        OciParameterDescriptor describeHandle = statementHandle.GetDescriptor(j, errorHandle);
                        describeHandle.GetAttribute(OCI.ATTR.OCI_ATTR_NAME, out columnName, errorHandle, _connection); 
                        OciHandle.SafeDispose(ref describeHandle);

                        columnName = QuotePrefixCharacter + columnName + QuoteSuffixCharacter;
 
                        columns.Add(null, schemaName, tableName, columnName, null);
                    } 
 
                    // Now, derive the key information for the statement and update the column list
                    // with it. 
                }
            }

            // Clean up and return; 
            OciHandle.SafeDispose(ref statementHandle);
 
            return columns; 
        }
 
        private string GetSynonymQueryStatement (string schemaName, string tableName) {
            //  Returns the statement text necessary to determine the base table
            //  behind a synonym.
 
            StringBuilder   sb = new StringBuilder();
 
            sb.Append(SynonymQueryBegin); 
            if (ADP.IsEmpty(schemaName)) {
                sb.Append(SynonymQueryNoSchema); 
            }
            else {
                sb.Append(SynonymQuerySchema);
                sb.Append(schemaName); 
                sb.Append("'");
            } 
 
            sb.Append(SynonymQueryTable);
            sb.Append(tableName); 
            sb.Append(SynonymQueryEnd);

            return sb.ToString();
        } 

        internal void Parse (string statementText, OracleConnection connection) { 
            //  Oracle specific Parse method, to allow us to capture a connection 
            //  for use by the schema gathering methods later.
 
            _connection = connection;
            Parse(statementText);
        }
    } 
}

// File provided for Reference Use Only by Microsoft Corporation (c) 2007.
                        

Link Menu

Network programming in C#, Network Programming in VB.NET, Network Programming in .NET
This book is available now!
Buy at Amazon US or
Buy at Amazon UK