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)); }