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);
}
protected DBSqlParserColumn FindCompletedColumn(
DBSqlParserTable table,
DBSqlParserColumn searchColumn
)
{
DBSqlParserColumnCollection columns = table.Columns;
int columnsCount = columns.Count;
for (int i = 0; i < columnsCount; ++i)
{
DBSqlParserColumn column = columns[i];
// Compare each part of the name (if they exist) with the
// table and pick the one that matches all the parts that exist.
if (CatalogMatch(column.ColumnName, searchColumn.ColumnName))
{
return(column);
}
}
// MDAC 87152: ROWID and ROWNUM shouldn't fire an assert here:
//Debug.Assert(false, "Why didn't we find a match for the search column?");
return(null);
}
private void Parse2(
string statementText
)
{
// Debug.WriteLine(statementText);
PARSERSTATE parserState = PARSERSTATE.NOTHINGYET;
Token[] namePart = new Token[4];
int currentPart = 0;
Token alias = Token.Null;
TokenType lastTokenType = TokenType.Null;
int parenLevel = 0;
// bool distinctFound;
_columns = null;
_tables = null;
Match match = SqlTokenParser.Match(statementText);
Token token = TokenFromMatch(match);
while (true)
{
// Debug.WriteLine(String.Format("{0,-15} {1,-17} {2} {3}", parserState, token.Type.ToString(), parenLevel, token.Value));
switch (parserState)
{
case PARSERSTATE.DONE:
return;
case PARSERSTATE.NOTHINGYET:
switch (token.Type)
{
case TokenType.Keyword_SELECT:
parserState = PARSERSTATE.SELECT;
break;
default:
Debug.Assert(false, "no state defined!!!!we should never be here!!!");
throw ADP.InvalidOperation(Res.GetString(Res.ADP_SQLParserInternalError));
}
break;
case PARSERSTATE.SELECT:
switch (token.Type)
{
case TokenType.Identifier:
case TokenType.QuotedIdentifier:
parserState = PARSERSTATE.COLUMN;
currentPart = 0;
namePart[0] = token;
break;
case TokenType.Keyword_FROM:
parserState = PARSERSTATE.FROM;
break;
case TokenType.Other_Star:
parserState = PARSERSTATE.COLUMNALIAS;
currentPart = 0;
namePart[0] = token;
break;
case TokenType.Keyword_DISTINCT:
// distinctFound = true;
break;
case TokenType.Keyword_ALL:
break;
case TokenType.Other_LeftParen:
parserState = PARSERSTATE.EXPRESSION;
parenLevel++;
break;
case TokenType.Other_RightParen:
throw ADP.SyntaxErrorMissingParenthesis();
default:
parserState = PARSERSTATE.EXPRESSION;
break;
}
break;
case PARSERSTATE.COLUMN:
switch (token.Type)
{
case TokenType.Identifier:
case TokenType.QuotedIdentifier:
if (TokenType.Other_Period != lastTokenType)
{
parserState = PARSERSTATE.COLUMNALIAS;
alias = token;
}
else
{
namePart[++currentPart] = token;
}
break;
case TokenType.Other_Period:
if (currentPart > 3)
{
throw ADP.SyntaxErrorTooManyNameParts();
}
break;
case TokenType.Other_Star:
parserState = PARSERSTATE.COLUMNALIAS;
namePart[++currentPart] = token;
break;
case TokenType.Keyword_AS:
break;
case TokenType.Keyword_FROM:
case TokenType.Other_Comma:
parserState = (token.Type == TokenType.Keyword_FROM) ? PARSERSTATE.FROM : PARSERSTATE.SELECT;
AddColumn(currentPart, namePart, alias);
currentPart = -1;
alias = Token.Null;
break;
case TokenType.Other_LeftParen:
parserState = PARSERSTATE.EXPRESSION;
parenLevel++;
currentPart = -1;
break;
case TokenType.Other_RightParen:
throw ADP.SyntaxErrorMissingParenthesis();
default:
parserState = PARSERSTATE.EXPRESSION;
currentPart = -1;
break;
}
break;
case PARSERSTATE.COLUMNALIAS:
switch (token.Type)
{
case TokenType.Keyword_FROM:
case TokenType.Other_Comma:
parserState = (token.Type == TokenType.Keyword_FROM) ? PARSERSTATE.FROM : PARSERSTATE.SELECT;
AddColumn(currentPart, namePart, alias);
currentPart = -1;
alias = Token.Null;
break;
default:
throw ADP.SyntaxErrorExpectedCommaAfterColumn();
}
break;
case PARSERSTATE.EXPRESSION:
switch (token.Type)
{
case TokenType.Identifier:
case TokenType.QuotedIdentifier:
if (0 == parenLevel)
{
alias = token;
}
break;
case TokenType.Keyword_FROM:
case TokenType.Other_Comma:
if (0 == parenLevel)
{
parserState = (token.Type == TokenType.Keyword_FROM) ? PARSERSTATE.FROM : PARSERSTATE.SELECT;
AddColumn(currentPart, namePart, alias);
currentPart = -1;
alias = Token.Null;
}
else
{
if (token.Type == TokenType.Keyword_FROM)
{
throw ADP.SyntaxErrorUnexpectedFrom();
}
}
break;
case TokenType.Other_LeftParen:
parenLevel++;
break;
case TokenType.Other_RightParen:
parenLevel--;
break;
}
break;
case PARSERSTATE.FROM:
switch (token.Type)
{
case TokenType.Identifier:
case TokenType.QuotedIdentifier:
parserState = PARSERSTATE.TABLE;
currentPart = 0;
namePart[0] = token;
break;
default:
throw ADP.SyntaxErrorExpectedIdentifier();
}
break;
case PARSERSTATE.TABLE:
switch (token.Type)
{
case TokenType.Identifier:
case TokenType.QuotedIdentifier:
if (TokenType.Other_Period != lastTokenType)
{
parserState = PARSERSTATE.TABLEALIAS;
alias = token;
}
else
{
namePart[++currentPart] = token;
}
break;
case TokenType.Other_Period:
if (currentPart > 2)
{
throw ADP.SyntaxErrorTooManyNameParts();
}
break;
case TokenType.Keyword_AS:
break;
case TokenType.Keyword_COMPUTE:
case TokenType.Keyword_FOR:
case TokenType.Keyword_GROUP:
case TokenType.Keyword_HAVING:
case TokenType.Keyword_INTERSECT:
case TokenType.Keyword_MINUS:
case TokenType.Keyword_ORDER:
case TokenType.Keyword_UNION:
case TokenType.Keyword_WHERE:
case TokenType.Null:
case TokenType.Other_Comma:
parserState = (TokenType.Other_Comma == token.Type) ? PARSERSTATE.FROM : PARSERSTATE.DONE;
AddTable(currentPart, namePart, alias);
currentPart = -1;
alias = Token.Null;
break;
default:
throw ADP.SyntaxErrorExpectedNextPart();
}
break;
case PARSERSTATE.TABLEALIAS:
switch (token.Type)
{
case TokenType.Keyword_COMPUTE:
case TokenType.Keyword_FOR:
case TokenType.Keyword_GROUP:
case TokenType.Keyword_HAVING:
case TokenType.Keyword_INTERSECT:
case TokenType.Keyword_MINUS:
case TokenType.Keyword_ORDER:
case TokenType.Keyword_UNION:
case TokenType.Keyword_WHERE:
case TokenType.Null:
case TokenType.Other_Comma:
parserState = (TokenType.Other_Comma == token.Type) ? PARSERSTATE.FROM : PARSERSTATE.DONE;
AddTable(currentPart, namePart, alias);
currentPart = -1;
alias = Token.Null;
break;
default:
throw ADP.SyntaxErrorExpectedCommaAfterTable();
}
break;
default:
Debug.Assert(false, "no state defined!!!!we should never be here!!!");
throw ADP.InvalidOperation(Res.GetString(Res.ADP_SQLParserInternalError));
}
lastTokenType = token.Type;
match = match.NextMatch();
token = TokenFromMatch(match);
}
}
private void CompleteSchemaInformation()
{
DBSqlParserColumnCollection columns = Columns;
DBSqlParserTableCollection tables = Tables;
int columnCount = columns.Count;
int tableCount = tables.Count;
// First, derive all of the information we can about each table
for (int i = 0; i < tableCount; i++)
{
DBSqlParserTable table = tables[i];
DBSqlParserColumnCollection tableColumns = GatherTableColumns(table);
table.Columns = tableColumns;
}
// Next, derive all of the column information we can.
for (int i = 0; i < columnCount; i++)
{
DBSqlParserColumn column = columns[i];
DBSqlParserTable table = FindTableForColumn(column);
if (!column.IsExpression)
{
// If this is a '*' column, then we have to expand the '*' into
// its component parts.
if ("*" == column.ColumnName)
{
// Remove the existing "*" column entry and replace it with the
// complete list of columns in the table.
columns.RemoveAt(i);
// If this is a tablename.* column, then add references to the
// all columns in the specified table, otherwise add references
// to all columns in all tables.
if (String.Empty != column.TableName)
{
DBSqlParserColumnCollection tableColumns = table.Columns;
int tableColumnCount = tableColumns.Count;
for (int j = 0; j < tableColumnCount; ++j)
{
columns.Insert(i + j, tableColumns[j]);
}
columnCount += tableColumnCount - 1; // don't forget to adjust our loop end
i += tableColumnCount - 1;
}
else
{
for (int k = 0; k < tableCount; k++)
{
table = tables[k];
DBSqlParserColumnCollection tableColumns = table.Columns;
int tableColumnCount = tableColumns.Count;
for (int j = 0; j < tableColumnCount; ++j)
{
columns.Insert(i + j, tableColumns[j]);
}
columnCount += tableColumnCount - 1; // don't forget to adjust our loop end
i += tableColumnCount;
}
}
}
else
{
// if this isn't a '*' column, we find the table that the column belongs
// to, and ask it's column collection for the completed column info (that
// contains information about key values, etc.)
DBSqlParserColumn completedColumn = FindCompletedColumn(table, column);
if (null != completedColumn) // MDAC 87152
{
column.CopySchemaInfoFrom(completedColumn);
}
else
{
column.CopySchemaInfoFrom(table);
}
}
}
}
// Finally, derive the key column information for each table
for (int i = 0; i < tableCount; i++)
{
DBSqlParserTable table = tables[i];
GatherKeyColumns(table);
}
}
override protected DBSqlParserColumnCollection GatherTableColumns(
DBSqlParserTable table
)
{
// Called to get a column list for the table specified.
OciHandle statementHandle = _connection.EnvironmentHandle.CreateOciHandle(OCI.HTYPE.OCI_HTYPE_STMT);
OciHandle 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(string.Empty == table.DatabaseName, "oracle doesn't support 4 part names!");
sb.Append("select * from ");
if (String.Empty != schemaName)
{
sb.Append(schemaName);
sb.Append(".");
}
sb.Append(tableName);
tempStatement = sb.ToString();
rc = TracedNativeMethods.OCIStmtPrepare(
statementHandle,
errorHandle,
tempStatement,
tempStatement.Length,
OCI.SYNTAX.OCI_NTV_SYNTAX,
OCI.MODE.OCI_DEFAULT,
_connection
);
if (0 == rc)
{
rc = TracedNativeMethods.OCIStmtExecute(
_connection.ServiceContextHandle,
statementHandle,
errorHandle,
0, // iters
0, // rowoff
ADP.NullHandleRef, // snap_in
ADP.NullHandleRef, // snap_out
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++)
{
OciHandle 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 void BuildSchemaTable()
{
Debug.Assert(null == _schemaTable, "BuildSchemaTable: schema table already exists");
Debug.Assert(null != _columnInfo, "BuildSchemaTable: no columnInfo");
int columnCount = FieldCount;
OracleSqlParser parser;
DBSqlParserColumnCollection parsedColumns = null;
int parsedColumnsCount = 0;
if (_keyInfoRequested)
{
parser = new OracleSqlParser();
parser.Parse(_statementText, _connection);
parsedColumns = parser.Columns;
parsedColumnsCount = parsedColumns.Count;
}
DataTable schemaTable = new DataTable("SchemaTable");
schemaTable.MinimumCapacity = columnCount;
DataColumn name = new DataColumn("ColumnName", typeof(System.String));
DataColumn ordinal = new DataColumn("ColumnOrdinal", typeof(System.Int32));
DataColumn size = new DataColumn("ColumnSize", typeof(System.Int32));
DataColumn precision = new DataColumn("NumericPrecision", typeof(System.Int16));
DataColumn scale = new DataColumn("NumericScale", typeof(System.Int16));
DataColumn dataType = new DataColumn("DataType", typeof(System.Type));
DataColumn oracleType = new DataColumn("ProviderType", typeof(System.Int32));
DataColumn isLong = new DataColumn("IsLong", typeof(System.Boolean));
DataColumn isNullable = new DataColumn("AllowDBNull", typeof(System.Boolean));
DataColumn isAliased = new DataColumn("IsAliased", typeof(System.Boolean));
DataColumn isExpression = new DataColumn("IsExpression", typeof(System.Boolean));
DataColumn isKey = new DataColumn("IsKey", typeof(System.Boolean));
DataColumn isUnique = new DataColumn("IsUnique", typeof(System.Boolean));
DataColumn baseSchemaName = new DataColumn("BaseSchemaName", typeof(System.String));
DataColumn baseTableName = new DataColumn("BaseTableName", typeof(System.String));
DataColumn baseColumnName = new DataColumn("BaseColumnName", typeof(System.String));
ordinal.DefaultValue = 0;
isLong.DefaultValue = false;
DataColumnCollection columns = schemaTable.Columns;
columns.Add(name);
columns.Add(ordinal);
columns.Add(size);
columns.Add(precision);
columns.Add(scale);
columns.Add(dataType);
columns.Add(oracleType);
columns.Add(isLong);
columns.Add(isNullable);
columns.Add(isAliased);
columns.Add(isExpression);
columns.Add(isKey);
columns.Add(isUnique);
columns.Add(baseSchemaName);
columns.Add(baseTableName);
columns.Add(baseColumnName);
for (int i = 0; i < columnCount; ++i)
{
OracleColumn column = _columnInfo[i];
DataRow newRow = schemaTable.NewRow();
newRow[name] = column.ColumnName;
newRow[ordinal] = column.Ordinal;
if (column.IsLong | column.IsLob)
{
newRow[size] = Int32.MaxValue; //MDAC 82554
}
else
{
newRow[size] = column.Size;
}
newRow[precision] = column.Precision;
newRow[scale] = column.Scale;
newRow[dataType] = column.GetFieldType();
newRow[oracleType] = column.OracleType;
newRow[isLong] = column.IsLong | column.IsLob;
newRow[isNullable] = column.IsNullable;
if (_keyInfoRequested && parsedColumnsCount == columnCount)
{
DBSqlParserColumn parsedColumn = parsedColumns[i];
newRow[isAliased] = parsedColumn.IsAliased;
newRow[isExpression] = parsedColumn.IsExpression;
newRow[isKey] = parsedColumn.IsKey;
newRow[isUnique] = parsedColumn.IsUnique;
newRow[baseSchemaName] = SetSchemaValue(OracleSqlParser.CatalogCase(parsedColumn.SchemaName));
newRow[baseTableName] = SetSchemaValue(OracleSqlParser.CatalogCase(parsedColumn.TableName));
newRow[baseColumnName] = SetSchemaValue(OracleSqlParser.CatalogCase(parsedColumn.ColumnName));
}
else
{
newRow[isAliased] = DBNull.Value; // don't know
newRow[isExpression] = DBNull.Value; // don't know
newRow[isKey] = DBNull.Value; // don't know
newRow[isUnique] = DBNull.Value; // don't know
newRow[baseSchemaName] = DBNull.Value; // don't know
newRow[baseTableName] = DBNull.Value; // don't know
newRow[baseColumnName] = DBNull.Value; // don't know
}
schemaTable.Rows.Add(newRow);
newRow.AcceptChanges();
}
// mark all columns as readonly
for (int i = 0; i < columns.Count; i++)
{
columns[i].ReadOnly = true;
}
// DataSet dataset = new DataSet();
// dataset.Tables.Add(schemaTable);
// Debug.WriteLine(dataset.GetXml());
// dataset.Tables.Remove(schemaTable);
_schemaTable = schemaTable;
}