protected HashSet <string> GetAutoGeneratedColumns(TableSourceInfo tableSource)
{
HashSet <string> columns = new HashSet <string>();
IDMLIdentifiers identifiers = DatabaseServices.DMLService.Identifiers;
string paramPrefix = DatabaseServices.ExecutionService.ParameterPrefix;
string tableOwnerParam = paramPrefix + "table_owner";
string tableNameParam = paramPrefix + "table_name";
string sql = string.Format(sqlFormatAutoGeneratedColumns,
(tableSource.Database.IsLinkedServer ? DMLIdentifiers.EscapeIdentifierInner(tableSource.Database.LinkedServer) + "." : "")
+ DMLIdentifiers.EscapeIdentifierInner(tableSource.Database.Catalog), tableOwnerParam, tableNameParam);
using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection()) {
using (IDbCommand cmd = DatabaseServices.ExecutionService.CreateCommand(conn, sql)) {
DatabaseServices.ExecutionService.CreateParameter(cmd, tableOwnerParam, DbType.String, tableSource.Schema);
DatabaseServices.ExecutionService.CreateParameter(cmd, tableNameParam, DbType.String, tableSource.Name);
cmd.CommandTimeout = QueryTimeout;
using (IDataReader reader = cmd.ExecuteReader()) {
while (reader.Read())
{
columns.Add(Convert.ToString(reader["COLUMN_NAME"]));
}
return(columns);
}
}
}
}
private IEnumerable <IPlatformTableSourceIndexInfo> GetIndexes(TableSourceInfo tableSource, IEnumerable <IPlatformTableSourceColumnInfo> columns)
{
IDictionary <string, IPlatformTableSourceIndexInfo> indexes = new Dictionary <string, IPlatformTableSourceIndexInfo>();
using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection()) {
using (IDataReader reader = GetTableSourceIndexes(conn, tableSource)) {
while (reader.Read())
{
string indexName = Convert.ToString(reader["idxName"]);
if (indexName.StartsWith("_WA_SYS_")) // _WA_SYS_* are statistics, not indexes
{
continue;
}
bool isUnique = (bool)reader["isUnique"];
bool isPrimaryKey = (bool)reader["isPrimaryKey"];
string columnName = Convert.ToString(reader["colName"]);
if (!indexName.IsNullOrEmpty())
{
IPlatformTableSourceIndexInfo index;
if (!indexes.TryGetValue(indexName, out index))
{
index = new PlatformTableSourceIndexInfo(tableSource, indexName, isUnique, isPrimaryKey);
indexes.Add(indexName, index);
}
IPlatformTableSourceColumnInfo column = columns.Single(c => c.Name.EqualsIgnoreCase(columnName));
((PlatformTableSourceIndexInfo)index).AddColumn(column);
}
}
return(indexes.Values.ToList());
}
}
}
private IEnumerable <IPlatformTableSourceColumnInfo> GetColumns(TableSourceInfo tableSource)
{
HashSet <string> primaryKeyColumns = GetPrimaryKeyColumns(tableSource);
HashSet <string> autoGeneratedColumns = GetAutoGeneratedColumns(tableSource);
using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection()) {
using (IDataReader reader = GetTableSourceColumns(conn, tableSource)) {
while (reader.Read())
{
string columnName = Convert.ToString(reader["COLUMN_NAME"]);
string dataType = Convert.ToString(reader["DATA_TYPE"]);
int decimalDigits = Convert.ToInt32(reader["NUMERIC_SCALE"]);
int length = Convert.ToInt32(reader["CHARACTER_MAXIMUM_LENGTH"]);
int precision = Convert.ToInt32(reader["NUMERIC_PRECISION"]);
IPlatformDataTypeInfo type = CreateDataTypeInfo(dataType, decimalDigits, length, precision);
bool isMandatory = !Convert.ToString(reader["IS_NULLABLE"]).EqualsIgnoreCase("YES");
bool isPrimaryKey = primaryKeyColumns.Contains(columnName);
bool isAutoGenerated = autoGeneratedColumns.Contains(columnName);
yield return(new PlatformTableSourceColumnInfo(tableSource, columnName, type, isMandatory, isPrimaryKey, isAutoGenerated));
}
}
}
}
private PlatformTableSourceInfo GetTableInfoColumnsAndKeys(TableSourceInfo tableSource, DatabaseInfo dbInfo)
{
// All versions use this code with the exception of 10g that uses GetTableInfo above.
var tableSourceDetails = new PlatformTableSourceInfo(tableSource);
tableSourceDetails.Columns = GetColumns(tableSource.ToEnumerable(), dbInfo);
tableSourceDetails.ForeignKeys = GetForeignKeys(tableSource.ToEnumerable(), dbInfo);
return(tableSourceDetails);
}
protected IEnumerable <ITableSourceForeignKeyInfo> GetForeignKeys(TableSourceInfo tableSource)
{
using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection()) {
IDbCommand cmd = null;
try {
string paramPrefix = DatabaseServices.ExecutionService.ParameterPrefix;
if (!tableSource.Database.IsLinkedServer)
{
string tableNameParam = paramPrefix + "tableName";
string sql = string.Format(sqlFormatForeignKeys,
DMLIdentifiers.EscapeIdentifierInner(tableSource.Database.Catalog), tableNameParam);
cmd = DatabaseServices.ExecutionService.CreateCommand(conn, sql);
DatabaseServices.ExecutionService.CreateParameter(cmd, tableNameParam, DbType.String, tableSource.Name);
}
else
{
cmd = DatabaseServices.ExecutionService.CreateCommand(conn, "sp_foreignkeys");
cmd.CommandType = CommandType.StoredProcedure;
DatabaseServices.ExecutionService.CreateParameter(cmd, paramPrefix + "table_server", DbType.String, tableSource.Database.LinkedServer);
DatabaseServices.ExecutionService.CreateParameter(cmd, paramPrefix + "fktab_name", DbType.String, tableSource.Name);
DatabaseServices.ExecutionService.CreateParameter(cmd, paramPrefix + "fktab_schema", DbType.String, tableSource.Schema);
DatabaseServices.ExecutionService.CreateParameter(cmd, paramPrefix + "fktab_catalog", DbType.String, tableSource.Database.Catalog);
}
cmd.CommandTimeout = QueryTimeout;
using (IDataReader reader = DatabaseServices.ExecutionService.ExecuteReader(cmd)) {
while (reader.Read())
{
string referencedTableName = Convert.ToString(reader["PKTABLE_NAME"]);
string referencedTableSchema = Convert.ToString(reader["PKTABLE_SCHEM"]);
string referencedColumnName = Convert.ToString(reader["PKCOLUMN_NAME"]);
string columnName = Convert.ToString(reader["FKCOLUMN_NAME"]);
string constraintName = Convert.ToString(reader["FK_NAME"]);
object deleteRule = reader["DELETE_RULE"];
bool isCascadeDelete;
if (tableSource.Database.IsLinkedServer)
{
isCascadeDelete = Convert.ToInt32(deleteRule) == 0; // CASCADE
}
else
{
isCascadeDelete = Convert.ToString(deleteRule).EqualsIgnoreCase("CASCADE");
}
string qualifiedName = GetQualifiedTableName(tableSource.Database, referencedTableName, referencedTableSchema);
ITableSourceInfo referencedTableSource = new TableSourceInfo(DatabaseServices, tableSource.Database, referencedTableName, referencedTableSchema, qualifiedName);
yield return(new TableSourceForeignKeyInfo(tableSource, constraintName, columnName, referencedTableSource, referencedColumnName, isCascadeDelete));
}
}
} finally {
if (cmd != null)
{
cmd.Dispose();
}
}
}
}
private IDataReader GetTableSourceColumns(IDbConnection conn, TableSourceInfo tableSource)
{
string paramPrefix = DatabaseServices.ExecutionService.ParameterPrefix;
string sql = string.Format(@"select vCols.column_name, vCols.data_type, isnull(vCols.CHARACTER_MAXIMUM_LENGTH, 0) CHARACTER_MAXIMUM_LENGTH,
isnull(vCols.NUMERIC_PRECISION, 0) NUMERIC_PRECISION, isnull(vCols.NUMERIC_SCALE, 0) NUMERIC_SCALE,
vCols.is_nullable
from {0}.INFORMATION_SCHEMA.COLUMNS vCols
where vCols.TABLE_SCHEMA = 'dbo' AND vCols.TABLE_NAME=" + paramPrefix + @"tableName
order by vCols.column_name asc", DMLIdentifiers.EscapeIdentifierInner(tableSource.Database.Catalog));
IDbCommand cmd = DatabaseServices.ExecutionService.CreateCommand(conn, sql);
cmd.CommandTimeout = QueryTimeout;
DatabaseServices.ExecutionService.CreateParameter(cmd, paramPrefix + "tableName", DbType.String, tableSource.Name);
return(cmd.ExecuteReader());
}
private PlatformTableSourceInfo GetTableInfo(ITableSourceInfo tableSource)
{
TableSourceInfo tableSourceInfo = tableSource as TableSourceInfo;
if (tableSourceInfo == null)
{
return(null);
}
PlatformTableSourceInfo tableSourceDetails = new PlatformTableSourceInfo(tableSourceInfo);
tableSourceDetails.Columns = GetColumns(tableSourceInfo);
tableSourceDetails.ForeignKeys = GetForeignKeys(tableSourceInfo);
tableSourceDetails.Indexes = GetIndexes(tableSourceInfo, tableSourceDetails.Columns);
tableSourceDetails.EventTrigger = Triggers.GetValueOrDefault(tableSourceInfo.Name.ToUpperInvariant());
return(tableSourceDetails);
}
private IDataReader GetTableSourceIndexes(IDbConnection conn, TableSourceInfo tableSource)
{
string paramPrefix = DatabaseServices.ExecutionService.ParameterPrefix;
string sql = String.Format(@"SELECT TheTable.name, TheIndex.name idxName, TheIndex.is_unique isUnique, TheIndex.is_primary_key isPrimaryKey, Cols.name colName
FROM {0}.sys.indexes TheIndex
INNER JOIN {0}.sys.index_columns vIdxK ON TheIndex.index_id = vIdxK.index_id AND TheIndex.object_id = vIdxK.object_id
INNER JOIN {0}.sys.objects TheTable ON TheTable.object_id = TheIndex.object_id
INNER JOIN {0}.sys.columns Cols ON vIdxK.column_id = Cols.column_id and vIdxK.object_id = Cols.object_id
WHERE TheIndex.is_unique_constraint = 0
AND TheTable.name = " + paramPrefix + "tableName", DMLIdentifiers.EscapeIdentifierInner(tableSource.Database.Catalog));
IDbCommand cmd = DatabaseServices.ExecutionService.CreateCommand(conn, sql);
cmd.CommandTimeout = QueryTimeout;
DatabaseServices.ExecutionService.CreateParameter(cmd, paramPrefix + "tableName", DbType.String, tableSource.Name);
return(DatabaseServices.ExecutionService.ExecuteReader(cmd));
}
private IEnumerable <ITableSourceColumnInfo> GetColumns(TableSourceInfo tableSource)
{
HashSet <string> primaryKeyColumns = GetPrimaryKeyColumns(tableSource);
HashSet <string> autoGeneratedColumns = GetAutoGeneratedColumns(tableSource);
using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection()) {
using (IDbCommand cmd = DatabaseServices.ExecutionService.CreateCommand(conn,
tableSource.Database.IsLinkedServer ? "sp_columns_ex" : string.Format("{0}.dbo.sp_columns", DMLIdentifiers.EscapeIdentifierInner(tableSource.Database.Catalog)))) {
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = QueryTimeout;
foreach (var param in tableSource.Database.IsLinkedServer ? spLinkedServerParams : spParams)
{
DatabaseServices.ExecutionService.CreateParameter(cmd,
DatabaseServices.ExecutionService.ParameterPrefix + param.Key, DbType.String, param.Value(tableSource));
}
using (IDataReader reader = DatabaseServices.ExecutionService.ExecuteReader(cmd)) {
string colDecimals = tableSource.Database.IsLinkedServer ? "DECIMAL_DIGITS" : "SCALE";
string colBufferLength = tableSource.Database.IsLinkedServer ? "BUFFER_LENGTH" : "LENGTH";
string colSize = tableSource.Database.IsLinkedServer ? "COLUMN_SIZE" : "PRECISION";
while (reader.Read())
{
string columnName = Convert.ToString(reader["COLUMN_NAME"]);
int dataTypeCode = Convert.ToInt32(reader["DATA_TYPE"]);
string dataTypeName = Convert.ToString(reader["TYPE_NAME"]);
int sqlDataTypeCode = Convert.ToInt32(reader["SQL_DATA_TYPE"]);
int decimalDigits = reader[colDecimals] == DBNull.Value ? 255 : Convert.ToInt32(reader[colDecimals]);
int bufferLength = reader[colBufferLength] == DBNull.Value ? -1 : Convert.ToInt32(reader[colBufferLength]);
int size = Convert.ToInt32(reader[colSize]);
IDataTypeInfo type = CreateDataTypeInfo(tableSource.Database.IsLinkedServer, dataTypeName, dataTypeCode,
sqlDataTypeCode, decimalDigits, bufferLength, size);
bool isMandatory = !Convert.ToString(reader["IS_NULLABLE"]).EqualsIgnoreCase("YES");
bool isPrimaryKey = primaryKeyColumns.Contains(columnName);
bool isAutoGenerated = autoGeneratedColumns.Contains(columnName);
yield return(new TableSourceColumnInfo(tableSource, columnName, type, isMandatory, isPrimaryKey, isAutoGenerated));
}
}
}
}
}
protected IEnumerable <ITableSourceForeignKeyInfo> GetForeignKeys(IEnumerable <TableSourceInfo> tableSources, DatabaseInfo dbInfo)
{
var result = new List <ITableSourceForeignKeyInfo>();
using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection()) {
var execService = DatabaseServices.ExecutionService as ExecutionService.ExecutionService;
if (execService != null)
{
execService.ForceCSASSetting(conn);
}
IEnumerable <TableSourceInfo> tableSourceList = tableSources.ToList();
using (IDataReader reader = GetForeignKeysBulk(conn, tableSourceList, dbInfo)) {
while (reader.Read())
{
string foreignKeyName = Convert.ToString(reader["constraintname"]);
string columnName = Convert.ToString(reader["columnname"]);
string tableName = Convert.ToString(reader["tablename"]);
string referencedColumnName = Convert.ToString(reader["referencedcolumn"]);
string referencedTableName = Convert.ToString(reader["referencedtable"]);
bool isCascadeDelete = Convert.ToString(reader["delete_rule"]).EqualsIgnoreCase("CASCADE");
TableSourceInfo tableSource = tableSourceList.First(ts => ts.Name == tableName);
if (tableSource != null)
{
ITableSourceInfo referencedTableSource = new TableSourceInfo(DatabaseServices, tableSource.Database,
referencedTableName, GetQualifiedTableName(tableSource.Database, referencedTableName));
ITableSourceForeignKeyInfo foreignKeyInfo = new TableSourceForeignKeyInfo(tableSource, foreignKeyName,
columnName, referencedTableSource, referencedColumnName, isCascadeDelete);
result.Add(foreignKeyInfo);
}
}
}
}
return(result);
}
protected HashSet <string> GetPrimaryKeyColumns(TableSourceInfo tableSource)
{
HashSet <string> columns = new HashSet <string>();
using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection()) {
using (IDbCommand cmd = DatabaseServices.ExecutionService.CreateCommand(conn,
tableSource.Database.IsLinkedServer ? "sp_primarykeys" : string.Format("{0}.dbo.sp_pkeys", DMLIdentifiers.EscapeIdentifierInner(tableSource.Database.Catalog)))) {
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = QueryTimeout;
foreach (var param in tableSource.Database.IsLinkedServer ? spLinkedServerParams : spParams)
{
DatabaseServices.ExecutionService.CreateParameter(cmd,
DatabaseServices.ExecutionService.ParameterPrefix + param.Key, DbType.String, param.Value(tableSource));
}
using (IDataReader reader = DatabaseServices.ExecutionService.ExecuteReader(cmd)) {
while (reader.Read())
{
columns.Add(Convert.ToString(reader["COLUMN_NAME"]));
}
return(columns);
}
}
}
}
private static TableSourceColumnInfo GetColumnInfo(TableSourceInfo tableSource, string columnName, DataTypeInfo dataType, bool isMandatory,
bool isPrimaryKey, AutoNumberColumnInfo autoNumberInfo)
{
return(new TableSourceColumnInfo(tableSource, columnName, dataType, isMandatory, isPrimaryKey, autoNumberInfo != null));
}
protected IEnumerable <ITableSourceColumnInfo> GetColumns(IEnumerable <TableSourceInfo> tableSources, DatabaseInfo dbInfo,
CreateAutoNumberInfo createAutoNumber, CreateDataTypeInfo createDataType, CreateColumnInfo createColumnInfo)
{
var result = new List <ITableSourceColumnInfo>();
using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection()) {
var execService = DatabaseServices.ExecutionService as ExecutionService.ExecutionService;
// #417717 - In these queries we don't want to use the CIAI settings
if (execService != null)
{
execService.ForceCSASSetting(conn);
}
IDictionary <string, AutoNumberColumnInfo> autoGeneratedColumns = new Dictionary <string, AutoNumberColumnInfo>();
IList <TableSourceInfo> tableSourceList = tableSources.ToList();
using (IDataReader reader = GetTriggersBulk(conn, tableSourceList, /*onlyAutoNumbers*/ true, String.Empty, dbInfo)) {
while (reader.Read())
{
string tableName = Convert.ToString(reader["TABLE_NAME"]);
if (autoGeneratedColumns.ContainsKey(tableName))
{
break;
}
string triggerName = Convert.ToString(reader["trigger_name"]).ToUpperInvariant();
string triggerBody = Convert.ToString(reader["trigger_body"]).ToUpperInvariant();
string triggerType = Convert.ToString(reader["trigger_type"]).ToUpperInvariant();
string triggeringEvent = Convert.ToString(reader["triggering_event"]).ToUpperInvariant();
var autoNumberInfo = createAutoNumber(triggerType, triggeringEvent, triggerName, triggerBody);
if (autoNumberInfo != null)
{
autoGeneratedColumns[GetAutoNumberColumnKey(tableName, autoNumberInfo.ColumnName)] = autoNumberInfo;
}
}
}
using (IDataReader reader = GetColumnsBulk(conn, tableSourceList, dbInfo)) {
while (reader.Read())
{
// WARNING:
// Do not change the order of these variables. In Java, columns of type Long and Long Raw must be read before the others (DATA_DEFAULT must be first).
// Read more here: https://blog.jooq.org/2015/12/30/oracle-long-and-long-raw-causing-stream-has-already-been-closed-exception/
string dataDefault = Convert.ToString(reader["DATA_DEFAULT"]);
string tableName = Convert.ToString(reader["TABLE_NAME"]);
string columnName = Convert.ToString(reader["COLUMN_NAME"]);
object constraintType = reader["CONSTRAINT_TYPE"];
object dataPrecision = reader["DATA_PRECISION"];
object dataScale = reader["DATA_SCALE"];
bool isMandatory = Convert.ToString(reader["NULLABLE"]).ToUpper() != "Y";
bool isPrimaryKey = !constraintType.Equals(DBNull.Value) && Convert.ToString(constraintType) == "P";
int precision = dataPrecision.Equals(DBNull.Value) ? 0 : Convert.ToInt32(dataPrecision);
int scale = dataScale.Equals(DBNull.Value) ? 0 : Convert.ToInt32(dataScale);
DataTypeInfo dataType = createDataType(Convert.ToString(reader["DATA_TYPE"]), Convert.ToInt32(reader["CHAR_LENGTH"]),
precision, scale);
AutoNumberColumnInfo autoNumberInfo;
autoGeneratedColumns.TryGetValue(GetAutoNumberColumnKey(tableName, columnName), out autoNumberInfo);
if (autoNumberInfo == null) // For versions above 12c (12.1) autonumbers can be generated via identity or sequences
{
if (IsSequenceValue(dataDefault))
{
autoNumberInfo = new AutoNumberColumnInfo()
{
ColumnName = columnName
};
}
}
TableSourceInfo tableSource = tableSourceList.First(ts => ts.Name.EqualsIgnoreCase(tableName));
result.Add(createColumnInfo(tableSource, columnName, dataType, isMandatory, isPrimaryKey, autoNumberInfo));
}
}
}
return(result);
}
private static PlatformTableSourceColumnInfo GetPlatformColumnInfo(TableSourceInfo tableSource, string columnName, DataTypeInfo dataType,
bool isMandatory, bool isPrimaryKey, AutoNumberColumnInfo autoNumberInfo)
{
return(new PlatformTableSourceColumnInfo(tableSource, columnName, (PlatformDataTypeInfo)dataType, isMandatory, isPrimaryKey, autoNumberInfo));
}
public override IEnumerable <ITableSourceColumnInfo> GetTableSourceColumns(ITableSourceInfo tableSource)
{
TableSourceInfo ts = tableSource as TableSourceInfo;
return((ts == null) ? null : GetColumns(ts));
}
public PlatformTableSourceInfo(TableSourceInfo tableSource)
: base(tableSource.DatabaseServices, tableSource.Database, tableSource.Name, tableSource.QualifiedName)
{
}
public override IEnumerable <ITableSourceForeignKeyInfo> GetTableSourceForeignKeys(ITableSourceInfo tableSource)
{
TableSourceInfo ts = tableSource as TableSourceInfo;
return((ts == null) ? null : GetForeignKeys(ts));
}