/// <summary> /// Defines a new use case in which the given <see cref="AggregateConfiguration"/> will be turned into an SQL query and used to generate rows /// that will be released into the pipeline. The source is fixed the destination and middle components are open. /// </summary> /// <param name="aggregateConfiguration">The aggregate query that will be run to generate the rows</param> /// <param name="constrainByCohort">Only applies if <see cref="AggregateConfiguration"/> is a patient index table, specifying a cohort will only commit rows /// in which the patient id appears in the cohort</param> /// <param name="table">The destination table in which to put the matched records. /// <para> (table does not have to exist yet, you can use <see cref="DiscoveredDatabase.ExpectTable"/> to obtain a reference to a non existant table)</para></param> public CreateTableFromAggregateUseCase(AggregateConfiguration aggregateConfiguration, ExtractableCohort constrainByCohort, DiscoveredTable table) { if (constrainByCohort == null) { var src = new AggregateConfigurationTableSource(); src.PreInitialize(aggregateConfiguration, new ThrowImmediatelyDataLoadEventListener()); src.TableName = table.GetRuntimeName(); ExplicitSource = src; } else { AddInitializationObject(constrainByCohort); var src = new PatientIndexTableSource(); src.PreInitialize(aggregateConfiguration, new ThrowImmediatelyDataLoadEventListener()); src.PreInitialize(constrainByCohort, new ThrowImmediatelyDataLoadEventListener()); src.TableName = table.GetRuntimeName(); ExplicitSource = src; } AddInitializationObject(aggregateConfiguration); AddInitializationObject(aggregateConfiguration.Repository); AddInitializationObject(table.Database); GenerateContext(); }
public override void DropTable(DbConnection connection, DiscoveredTable tableToDrop) { SqlCommand cmd; switch (tableToDrop.TableType) { case TableType.View: if (connection.Database != tableToDrop.Database.GetRuntimeName()) { connection.ChangeDatabase(tableToDrop.GetRuntimeName()); } if (!connection.Database.ToLower().Equals(tableToDrop.Database.GetRuntimeName().ToLower())) { throw new NotSupportedException("Cannot drop view " + tableToDrop + " because it exists in database " + tableToDrop.Database.GetRuntimeName() + " while the current current database connection is pointed at database:" + connection.Database + " (use .ChangeDatabase on the connection first) - SQL Server does not support cross database view dropping"); } cmd = new SqlCommand("DROP VIEW " + tableToDrop.GetRuntimeName(), (SqlConnection)connection); break; case TableType.Table: cmd = new SqlCommand("DROP TABLE " + tableToDrop.GetFullyQualifiedName(), (SqlConnection)connection); break; case TableType.TableValuedFunction: DropFunction(connection, (DiscoveredTableValuedFunction)tableToDrop); return; default: throw new ArgumentOutOfRangeException(); } using (cmd) cmd.ExecuteNonQuery(); }
public void TestTableInfo(bool usenamer) { string filename = Path.Combine(LoadDirectory.ForLoading.FullName, "bob.csv"); var sw = new StreamWriter(filename); sw.WriteLine("name,name2"); sw.WriteLine("Bob,Munchousain"); sw.WriteLine("Franky,Hollyw9ood"); sw.Flush(); sw.Close(); sw.Dispose(); TableInfo ti; ColumnInfo[] cols; Import(_table, out ti, out cols); var attacher = new AnySeparatorFileAttacher(); attacher.Initialize(LoadDirectory, _database); attacher.Separator = ","; attacher.FilePattern = "bob*"; attacher.TableToLoad = ti; INameDatabasesAndTablesDuringLoads namer = null; if (usenamer) { _table.Rename("AAA"); namer = RdmpMockFactory.Mock_INameDatabasesAndTablesDuringLoads(_database, "AAA"); } var job = new ThrowImmediatelyDataLoadJob(new HICDatabaseConfiguration(_database.Server, namer), ti); var exitCode = attacher.Attach(job, new GracefulCancellationToken()); Assert.AreEqual(ExitCodeType.Success, exitCode); using (var con = _database.Server.GetConnection()) { con.Open(); var r = _database.Server.GetCommand("Select name,name2 from " + _table.GetRuntimeName(), con).ExecuteReader(); Assert.IsTrue(r.Read()); Assert.AreEqual("Bob", r["name"]); Assert.AreEqual("Munchousain", r["name2"]); Assert.IsTrue(r.Read()); Assert.AreEqual("Franky", r["name"]); Assert.AreEqual("Hollyw9ood", r["name2"]); } attacher.LoadCompletedSoDispose(ExitCodeType.Success, new ThrowImmediatelyDataLoadEventListener()); File.Delete(filename); }
public override DiscoveredColumn[] DiscoverColumns(DiscoveredTable discoveredTable, IManagedConnection connection, string database) { List <DiscoveredColumn> columns = new List <DiscoveredColumn>(); var tableName = discoveredTable.GetRuntimeName(); using (DbCommand cmd = discoveredTable.Database.Server.Helper.GetCommand( @"SELECT * FROM information_schema.`COLUMNS` WHERE table_schema = @db AND table_name = @tbl", connection.Connection)) { cmd.Transaction = connection.Transaction; var p = new MySqlParameter("@db", MySqlDbType.String); p.Value = discoveredTable.Database.GetRuntimeName(); cmd.Parameters.Add(p); p = new MySqlParameter("@tbl", MySqlDbType.String); p.Value = discoveredTable.GetRuntimeName(); cmd.Parameters.Add(p); using (DbDataReader r = cmd.ExecuteReader()) { if (!r.HasRows) { throw new Exception("Could not find any columns for table " + tableName + " in database " + database); } while (r.Read()) { var toAdd = new DiscoveredColumn(discoveredTable, (string)r["COLUMN_NAME"], YesNoToBool(r["IS_NULLABLE"])); if (r["COLUMN_KEY"].Equals("PRI")) { toAdd.IsPrimaryKey = true; } toAdd.IsAutoIncrement = r["Extra"] as string == "auto_increment"; toAdd.Collation = r["COLLATION_NAME"] as string; //todo the only way to know if something in MySql is unicode is by r["character_set_name"] toAdd.DataType = new DiscoveredDataType(r, TrimIntDisplayValues(r["COLUMN_TYPE"].ToString()), toAdd); columns.Add(toAdd); } r.Close(); } } return(columns.ToArray()); }
/// <summary> /// Returns true if the TableInfo is a reference to the discovered live table (same database, same table name, same server) /// <para>By default servername is not checked since you can have server aliases e.g. localhost\sqlexpress could be the same as 127.0.0.1\sqlexpress</para> /// </summary> /// <param name="discoveredTable">Pass true to also check the servername is EXACTLY the same (dangerous due to the fact that servers can be accessed by hostname or IP etc)</param> /// <param name="alsoCheckServer"></param> /// <returns></returns> public bool Is(DiscoveredTable discoveredTable, bool alsoCheckServer = false) { return(GetRuntimeName().Equals(discoveredTable.GetRuntimeName(), StringComparison.CurrentCultureIgnoreCase) && GetDatabaseRuntimeName().Equals(discoveredTable.Database.GetRuntimeName(), StringComparison.CurrentCultureIgnoreCase) && DatabaseType == discoveredTable.Database.Server.DatabaseType && (!alsoCheckServer || discoveredTable.Database.Server.Name.Equals(Server, StringComparison.CurrentCultureIgnoreCase))); }
/// <summary> /// Creates the <paramref name="tableTemplate"/> in the database location <paramref name="expectedTable"/>. /// </summary> /// <param name="expectedTable"></param> /// <param name="tableTemplate"></param> public void CreateTable(DiscoveredTable expectedTable, ImageTableTemplate tableTemplate) { expectedTable.Database.CreateTable(expectedTable.GetRuntimeName(), tableTemplate.GetColumns(expectedTable.Database.Server.DatabaseType)); if (!expectedTable.Exists()) { throw new Exception("Table did not exist after issuing create statement!"); } }
public TriggerImplementer(DiscoveredTable table, bool createDataLoadRunIDAlso = true) { _server = table.Database.Server; _table = table; _archiveTable = _table.Database.ExpectTable(table.GetRuntimeName() + "_Archive", table.Schema); _columns = table.DiscoverColumns(); _primaryKeys = _columns.Where(c => c.IsPrimaryKey).ToArray(); _createDataLoadRunIdAlso = createDataLoadRunIDAlso; }
/// <summary> /// Returns the table name suitable for being passed into OBJECT_ID including schema if any /// </summary> /// <param name="table"></param> /// <returns></returns> private string GetObjectName(DiscoveredTable table) { var objectName = table.GetRuntimeName(); if (table.Schema != null) { return(table.Schema + "." + objectName); } return(objectName); }
protected override string GetRenameTableSql(DiscoveredTable discoveredTable, string newName) { string oldName = "[" + discoveredTable.GetRuntimeName() + "]"; if (!string.IsNullOrWhiteSpace(discoveredTable.Schema)) { oldName = "[" + discoveredTable.Schema + "]." + oldName; } return(string.Format("exec sp_rename '{0}', '{1}'", oldName, newName)); }
/// <summary> /// Returns the table name suitable for being passed into OBJECT_ID including schema if any /// </summary> /// <param name="table"></param> /// <returns></returns> private string GetObjectName(DiscoveredTable table) { var syntax = table.GetQuerySyntaxHelper(); var objectName = syntax.EnsureWrapped(table.GetRuntimeName()); if (table.Schema != null) { return(syntax.EnsureWrapped(table.Schema) + "." + objectName); } return(objectName); }
public void Add(DiscoveredTable discoveredTable) { if (items.Any(i => i.Tag.Equals(discoveredTable))) { return; } var snip = new SubstringAutocompleteItem(discoveredTable.GetRuntimeName()); snip.MenuText = discoveredTable.GetRuntimeName(); //name of table snip.Text = discoveredTable.GetFullyQualifiedName(); //full SQL snip.Tag = discoveredTable; //record object for future reference snip.ImageIndex = GetIndexFor(discoveredTable, RDMPConcept.TableInfo.ToString()); AddUnlessDuplicate(snip); DiscoveredColumn[] columns = null; try { if (discoveredTable.Exists()) { columns = discoveredTable.DiscoverColumns(); } } catch (Exception) { //couldn't load nevermind } if (columns != null) { foreach (var col in columns) { Add(col); } } }
public ArbitraryTableExtractionUICollection(DiscoveredTable table) : this() { _table = table; _arguments.Add(ServerKey, _table.Database.Server.Name); _arguments.Add(DatabaseKey, _table.Database.GetRuntimeName()); _arguments.Add(TableKey, _table.GetRuntimeName()); DatabaseType = table.Database.Server.DatabaseType; _arguments.Add(DatabaseTypeKey, DatabaseType.ToString()); Username = table.Database.Server.ExplicitUsernameIfAny; Password = table.Database.Server.ExplicitPasswordIfAny; }
/// <summary> /// Prepares to import a reference to the <paramref name="table"/> as <see cref="TableInfo"/> and <see cref="ColumnInfo"/> in the RDMP <paramref name="catalogueRepository"/> /// </summary> /// <param name="catalogueRepository"></param> /// <param name="table"></param> /// <param name="usageContext"></param> public TableInfoImporter(ICatalogueRepository catalogueRepository, DiscoveredTable table, DataAccessContext usageContext = DataAccessContext.Any) : this(catalogueRepository, table.Database.Server.Name, table.Database.GetRuntimeName(), table.GetRuntimeName(), table.Database.Server.DatabaseType, table.Database.Server.ExplicitUsernameIfAny, table.Database.Server.ExplicitPasswordIfAny, usageContext, table.Schema, table.TableType) { _usageContext = DataAccessContext.Any; InitializeBuilder(); }
private void CreateIsolationTable(DiscoveredTable toCreate, TableInfo tableInfo) { var from = tableInfo.Discover(DataAccessContext.DataLoad); //create a RAW table schema called TableName_Isolation var cloner = new TableInfoCloneOperation(null, null, LoadBubble.Live); cloner.CloneTable(from.Database, toCreate.Database, from, toCreate.GetRuntimeName(), true, true, true, tableInfo.PreLoadDiscardedColumns); if (!toCreate.Exists()) { throw new Exception(string.Format("Table '{0}' did not exist after issuing create command", toCreate)); } //Add the data load run id toCreate.AddColumn(SpecialFieldNames.DataLoadRunID, new DatabaseTypeRequest(typeof(int)), false, 10); }
private void CreateIsolationTable(DiscoveredTable toCreate, TableInfo tableInfo) { var from = tableInfo.Discover(DataAccessContext.DataLoad); //create a RAW table schema called TableName_Isolation var cloner = new TableInfoCloneOperation(new HICDatabaseConfiguration(toCreate.Database.Server), tableInfo, LoadBubble.Live, _job ?? (IDataLoadEventListener) new ThrowImmediatelyDataLoadEventListener()); cloner.CloneTable(from.Database, toCreate.Database, from, toCreate.GetRuntimeName(), true, true, true, tableInfo.PreLoadDiscardedColumns); if (!toCreate.Exists()) { throw new Exception($"Table '{toCreate}' did not exist after issuing create command"); } //Add the data load run id toCreate.AddColumn(SpecialFieldNames.DataLoadRunID, new DatabaseTypeRequest(typeof(int)), false, 10); }
public override void WriteItems(DataTable items) { StripWhiteSpace(items); items.TableName = _reportName; if (!_tbl.Exists()) { _tbl.Database.CreateTable(_tbl.GetRuntimeName(), items); } else { using (var insert = _tbl.BeginBulkInsert()) { insert.Upload(items); } } }
public void DeleteRows(DiscoveredTable tbl, ColumnInfo[] primaryKeys, IDataLoadEventListener listener) { string join = string.Join(" AND ", primaryKeys.Select(k => "t1." + k.GetRuntimeName() + "=t2." + k.GetRuntimeName())); string t1DotColumn = "t1." + ColumnToResolveOn.GetRuntimeName(_loadStage); string t2DotColumn = "t2." + ColumnToResolveOn.GetRuntimeName(_loadStage); //FYI - we are considering whether to delete records from table {0} string deleteConditional = PreferNulls? //delete rows {0} where {0} is not null and {1} is null - leaving only the null records {1} string.Format("({0} IS NOT NULL AND {1} IS NULL)", t1DotColumn, t2DotColumn): string.Format("({1} IS NOT NULL AND {0} IS NULL)", t1DotColumn, t2DotColumn); deleteConditional += " OR "; deleteConditional += PreferLargerValues //delete rows {0} where {0} less than {1} - leaving only the greater records {1} ? string.Format("({0} <> {1} AND {0} < {1})", t1DotColumn, t2DotColumn) : string.Format("({0} <> {1} AND {1} < {0})", t1DotColumn, t2DotColumn); var sql = string.Format(@"DELETE t1 FROM {0} t1 JOIN {0} t2 ON {1} AND ({2})", tbl.GetRuntimeName(), join, deleteConditional); using (var con = tbl.Database.Server.GetConnection()) { con.Open(); using (var cmd = tbl.Database.Server.GetCommand(sql, con)) { cmd.CommandTimeout = Timeout; int affectedRows = cmd.ExecuteNonQuery(); listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Deleted " + affectedRows + " rows")); } } }
private void CreateIndex(DiscoveredTable table, DiscoveredColumn onColumn, string configurationName) { string pkCreationSql = "ALTER TABLE " + table.GetRuntimeName() + " ADD CONSTRAINT PK_" + table.GetRuntimeName() + " PRIMARY KEY CLUSTERED (" + onColumn.GetRuntimeName() + ")"; try { var server = table.Database.Server; using (var con = server.GetConnection()) { con.Open(); var cmd = server.GetCommand(pkCreationSql, con); cmd.CommandTimeout = Timeout; cmd.ExecuteNonQuery(); } } catch (Exception e) { throw new Exception("Failed to create unique primary key on the results of AggregateConfiguration " + configurationName + ". The SQL that failed was:" + Environment.NewLine + pkCreationSql, e); } }
public override DiscoveredColumn[] DiscoverColumns(DiscoveredTable discoveredTable, IManagedConnection connection, string database) { List <DiscoveredColumn> columns = new List <DiscoveredColumn>(); var tableName = discoveredTable.GetRuntimeName(); DbCommand cmd = discoveredTable.Database.Server.Helper.GetCommand("SHOW FULL COLUMNS FROM `" + database + "`.`" + tableName + "`", connection.Connection); cmd.Transaction = connection.Transaction; using (DbDataReader r = cmd.ExecuteReader()) { if (!r.HasRows) { throw new Exception("Could not find any columns for table " + tableName + " in database " + database); } while (r.Read()) { var toAdd = new DiscoveredColumn(discoveredTable, (string)r["Field"], YesNoToBool(r["Null"])); if (r["Key"].Equals("PRI")) { toAdd.IsPrimaryKey = true; } toAdd.IsAutoIncrement = r["Extra"] as string == "auto_increment"; toAdd.Collation = r["Collation"] as string; toAdd.DataType = new DiscoveredDataType(r, TrimIntDisplayValues(r["Type"].ToString()), toAdd); columns.Add(toAdd); } r.Close(); } return(columns.ToArray()); }
public override DiscoveredRelationship[] DiscoverRelationships(DiscoveredTable table, DbConnection connection, IManagedTransaction transaction = null) { string sql = @"SELECT u.CONSTRAINT_NAME, u.TABLE_SCHEMA, u.TABLE_NAME, u.COLUMN_NAME, u.REFERENCED_TABLE_SCHEMA, u.REFERENCED_TABLE_NAME, u.REFERENCED_COLUMN_NAME, c.DELETE_RULE FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE u INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c ON c.CONSTRAINT_NAME = u.CONSTRAINT_NAME WHERE u.REFERENCED_TABLE_SCHEMA = @db AND u.REFERENCED_TABLE_NAME = @tbl"; var cmd = new MySqlCommand(sql, (MySqlConnection)connection); var p = new MySqlParameter("@db", MySqlDbType.String); p.Value = table.Database.GetRuntimeName(); cmd.Parameters.Add(p); p = new MySqlParameter("@tbl", MySqlDbType.String); p.Value = table.GetRuntimeName(); cmd.Parameters.Add(p); Dictionary <string, DiscoveredRelationship> toReturn = new Dictionary <string, DiscoveredRelationship>(); using (var r = cmd.ExecuteReader()) { while (r.Read()) { var fkName = r["CONSTRAINT_NAME"].ToString(); DiscoveredRelationship current; //could be a 2+ columns foreign key? if (toReturn.ContainsKey(fkName)) { current = toReturn[fkName]; } else { var pkDb = r["REFERENCED_TABLE_SCHEMA"].ToString(); var pkTableName = r["REFERENCED_TABLE_NAME"].ToString(); var fkDb = r["TABLE_SCHEMA"].ToString(); var fkTableName = r["TABLE_NAME"].ToString(); var pktable = table.Database.Server.ExpectDatabase(pkDb).ExpectTable(pkTableName); var fktable = table.Database.Server.ExpectDatabase(fkDb).ExpectTable(fkTableName); //https://dev.mysql.com/doc/refman/8.0/en/referential-constraints-table.html var deleteRuleString = r["DELETE_RULE"].ToString(); CascadeRule deleteRule = CascadeRule.Unknown; if (deleteRuleString == "CASCADE") { deleteRule = CascadeRule.Delete; } else if (deleteRuleString == "NO ACTION") { deleteRule = CascadeRule.NoAction; } else if (deleteRuleString == "RESTRICT") { deleteRule = CascadeRule.NoAction; } else if (deleteRuleString == "SET NULL") { deleteRule = CascadeRule.SetNull; } else if (deleteRuleString == "SET DEFAULT") { deleteRule = CascadeRule.SetDefault; } current = new DiscoveredRelationship(fkName, pktable, fktable, deleteRule); toReturn.Add(current.Name, current); } current.AddKeys(r["REFERENCED_COLUMN_NAME"].ToString(), r["COLUMN_NAME"].ToString(), transaction); } } return(toReturn.Values.ToArray()); }
public string GetCode() { var columns = _table.DiscoverColumns(); if (!columns.Any(c => c.GetRuntimeName().Equals("ID"))) { throw new CodeGenerationException("Table must have an ID automnum column to become an IMapsDirectlyToDatabaseTable class"); } StringBuilder classStart = new StringBuilder(); classStart.Append("public class " + _table.GetRuntimeName() + ": DatabaseEntity"); bool isINamed = columns.Any(c => c.GetRuntimeName() == "Name"); if (isINamed) { classStart.Append(",INamed"); } classStart.AppendLine(); classStart.AppendLine("{"); StringBuilder databaseFields = new StringBuilder(); databaseFields.AppendLine("\t#region Database Properties"); databaseFields.AppendLine(); StringBuilder databaseProperties = new StringBuilder(); StringBuilder constructors = new StringBuilder(); constructors.AppendLine("\tpublic " + _table.GetRuntimeName() + "(IRepository repository/*, TODO Required Construction Properties For NEW*/)"); constructors.AppendLine(@" { repository.InsertAndHydrate(this,new Dictionary<string, object>() { //TODO Any parameters here as key value pairs }); if (ID == 0 || Repository != repository) throw new ArgumentException(""Repository failed to properly hydrate this class""); }"); constructors.AppendLine("\tpublic " + _table.GetRuntimeName() + "(IRepository repository, DbDataReader r): base(repository, r)"); constructors.AppendLine("\t{"); foreach (var col in columns.Where(c => c.GetRuntimeName() != "ID")) { string setCode; var type = GetCSharpTypeFor(col, out setCode); var propertyName = col.GetRuntimeName(); var fieldString = col.GetRuntimeName(); //cammel case it fieldString = "_" + fieldString.Substring(0, 1).ToLower() + fieldString.Substring(1); databaseFields.AppendLine("\tprivate " + type + " " + fieldString + ";"); databaseProperties.AppendLine("\tpublic " + type + " " + propertyName); databaseProperties.AppendLine("\t{"); databaseProperties.AppendLine("\t\tget { return " + fieldString + ";}"); databaseProperties.AppendLine("\t\tset { SetField(ref " + fieldString + ", value);}"); databaseProperties.AppendLine("\t}"); constructors.AppendLine("\t\t" + propertyName + " = " + setCode); } databaseFields.AppendLine("\t#endregion"); databaseFields.AppendLine(); constructors.AppendLine("\t}"); if (isINamed) { constructors.AppendLine("\t" + @"public override string ToString() { return Name; }"); } return(classStart.ToString() + databaseFields + databaseProperties + constructors + "}"); }
public override DiscoveredRelationship[] DiscoverRelationships(DiscoveredTable table, DbConnection connection, IManagedTransaction transaction = null) { var toReturn = new Dictionary <string, DiscoveredRelationship>(); string sql = "exec sp_fkeys @pktable_name = @table, @pktable_qualifier=@database, @pktable_owner=@schema"; using (DbCommand cmd = table.GetCommand(sql, connection)) { if (transaction != null) { cmd.Transaction = transaction.Transaction; } var p = cmd.CreateParameter(); p.ParameterName = "@table"; p.Value = table.GetRuntimeName(); p.DbType = DbType.String; cmd.Parameters.Add(p); p = cmd.CreateParameter(); p.ParameterName = "@schema"; p.Value = table.Schema ?? "dbo"; p.DbType = DbType.String; cmd.Parameters.Add(p); p = cmd.CreateParameter(); p.ParameterName = "@database"; p.Value = table.Database.GetRuntimeName(); p.DbType = DbType.String; cmd.Parameters.Add(p); using (DataTable dt = new DataTable()) { var da = table.Database.Server.GetDataAdapter(cmd); da.Fill(dt); foreach (DataRow r in dt.Rows) { var fkName = r["FK_NAME"].ToString(); DiscoveredRelationship current; //could be a 2+ columns foreign key? if (toReturn.ContainsKey(fkName)) { current = toReturn[fkName]; } else { var pkdb = r["PKTABLE_QUALIFIER"].ToString(); var pkschema = r["PKTABLE_OWNER"].ToString(); var pktableName = r["PKTABLE_NAME"].ToString(); var pktable = table.Database.Server.ExpectDatabase(pkdb).ExpectTable(pktableName, pkschema); var fkdb = r["FKTABLE_QUALIFIER"].ToString(); var fkschema = r["FKTABLE_OWNER"].ToString(); var fktableName = r["FKTABLE_NAME"].ToString(); var fktable = table.Database.Server.ExpectDatabase(fkdb).ExpectTable(fktableName, fkschema); var deleteRuleInt = Convert.ToInt32(r["DELETE_RULE"]); CascadeRule deleteRule = CascadeRule.Unknown; if (deleteRuleInt == 0) { deleteRule = CascadeRule.Delete; } else if (deleteRuleInt == 1) { deleteRule = CascadeRule.NoAction; } else if (deleteRuleInt == 2) { deleteRule = CascadeRule.SetNull; } else if (deleteRuleInt == 3) { deleteRule = CascadeRule.SetDefault; } /* * https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-fkeys-transact-sql?view=sql-server-2017 * * 0=CASCADE changes to foreign key. * 1=NO ACTION changes if foreign key is present. * 2 = set null * 3 = set default*/ current = new DiscoveredRelationship(fkName, pktable, fktable, deleteRule); toReturn.Add(current.Name, current); } current.AddKeys(r["PKCOLUMN_NAME"].ToString(), r["FKCOLUMN_NAME"].ToString(), transaction); } } } return(toReturn.Values.ToArray()); }
public override DiscoveredColumn[] DiscoverColumns(DiscoveredTable discoveredTable, IManagedConnection connection, string database) { List <DiscoveredColumn> toReturn = new List <DiscoveredColumn>(); using (DbCommand cmd = discoveredTable.GetCommand("use [" + database + @"]; SELECT sys.columns.name AS COLUMN_NAME, sys.types.name AS TYPE_NAME, sys.columns.collation_name AS COLLATION_NAME, sys.columns.max_length as LENGTH, sys.columns.scale as SCALE, sys.columns.is_identity, sys.columns.is_nullable, sys.columns.precision as PRECISION, sys.columns.collation_name from sys.columns join sys.types on sys.columns.user_type_id = sys.types.user_type_id where object_id = OBJECT_ID(@tableName)", connection.Connection, connection.Transaction)) { var p = cmd.CreateParameter(); p.ParameterName = "@tableName"; p.Value = GetObjectName(discoveredTable); cmd.Parameters.Add(p); using (var r = cmd.ExecuteReader()) while (r.Read()) { bool isNullable = Convert.ToBoolean(r["is_nullable"]); //if it is a table valued function prefix the column name with the table valued function name string columnName = discoveredTable is DiscoveredTableValuedFunction ? discoveredTable.GetRuntimeName() + "." + r["COLUMN_NAME"] : r["COLUMN_NAME"].ToString(); var toAdd = new DiscoveredColumn(discoveredTable, columnName, isNullable); toAdd.IsAutoIncrement = Convert.ToBoolean(r["is_identity"]); toAdd.DataType = new DiscoveredDataType(r, GetSQLType_FromSpColumnsResult(r), toAdd); toAdd.Collation = r["collation_name"] as string; toReturn.Add(toAdd); } } if (!toReturn.Any()) { throw new Exception("Could not find any columns in table " + discoveredTable); } //don't bother looking for pks if it is a table valued function if (discoveredTable is DiscoveredTableValuedFunction) { return(toReturn.ToArray()); } var pks = ListPrimaryKeys(connection, discoveredTable); foreach (DiscoveredColumn c in toReturn) { if (pks.Any(pk => pk.Equals(c.GetRuntimeName()))) { c.IsPrimaryKey = true; } } return(toReturn.ToArray()); }
protected override void MutilateTable(IDataLoadEventListener job, ITableInfo tableInfo, DiscoveredTable table) { var server = table.Database.Server; job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "About to run Coalese on table " + table)); var allCols = table.DiscoverColumns(); var pkColumnInfos = tableInfo.ColumnInfos.Where(c => c.IsPrimaryKey).Select(c => c.GetRuntimeName()).ToArray(); var nonPks = allCols.Where(c => !pkColumnInfos.Contains(c.GetRuntimeName())).ToArray(); var pks = allCols.Except(nonPks).ToArray(); if (!pkColumnInfos.Any()) { throw new Exception("Table '" + tableInfo + "' has no IsPrimaryKey columns"); } if (allCols.Length == pkColumnInfos.Length) { job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Warning, "Skipping Coalesce on table " + table + " because it has no non primary key columns")); return; } //Get an update command for each non primary key column Dictionary <string, Task <int> > sqlCommands = new Dictionary <string, Task <int> >(); foreach (DiscoveredColumn nonPk in nonPks) { sqlCommands.Add(GetCommand(table, pks, nonPk), null); } server.EnableAsync(); using (var con = table.Database.Server.GetConnection()) { con.Open(); if (CreateIndex) { var idxCmd = server.GetCommand(string.Format(@"CREATE INDEX IX_PK_{0} ON {0}({1});", table.GetRuntimeName(), string.Join(",", pks.Select(p => p.GetRuntimeName()))), con); idxCmd.CommandTimeout = Timeout; idxCmd.ExecuteNonQuery(); } foreach (var sql in sqlCommands.Keys.ToArray()) { var cmd = server.GetCommand(sql, con); cmd.CommandTimeout = Timeout; sqlCommands[sql] = cmd.ExecuteNonQueryAsync(); } Task.WaitAll(sqlCommands.Values.ToArray()); } int affectedRows = sqlCommands.Values.Sum(t => t.Result); job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Coalesce on table '" + table + "' completed (" + affectedRows + " rows affected)")); }
public override DiscoveredRelationship[] DiscoverRelationships(DiscoveredTable table, DbConnection connection, IManagedTransaction transaction = null) { string sql = @" SELECT a.table_name , a.column_name , a.constraint_name , c.owner , c.delete_rule , c.r_owner , c_pk.table_name r_table_name , c_pk.constraint_name r_pk , cc_pk.column_name r_column_name FROM all_cons_columns a JOIN all_constraints c ON (a.owner = c.owner AND a.constraint_name = c.constraint_name ) JOIN all_constraints c_pk ON (c.r_owner = c_pk.owner AND c.r_constraint_name = c_pk.constraint_name ) JOIN all_cons_columns cc_pk on (cc_pk.constraint_name = c_pk.constraint_name AND cc_pk.owner = c_pk.owner AND cc_pk.position = a.position) WHERE c.constraint_type = 'R' AND UPPER(c.r_owner) = UPPER(:DatabaseName) AND UPPER(c_pk.table_name) = UPPER(:TableName)"; var cmd = new OracleCommand(sql, (OracleConnection)connection); var p = new OracleParameter(":DatabaseName", OracleDbType.Varchar2); p.Value = table.Database.GetRuntimeName(); cmd.Parameters.Add(p); p = new OracleParameter(":TableName", OracleDbType.Varchar2); p.Value = table.GetRuntimeName(); cmd.Parameters.Add(p); Dictionary <string, DiscoveredRelationship> toReturn = new Dictionary <string, DiscoveredRelationship>(); using (var r = cmd.ExecuteReader()) { while (r.Read()) { var fkName = r["constraint_name"].ToString(); DiscoveredRelationship current; //could be a 2+ columns foreign key? if (toReturn.ContainsKey(fkName)) { current = toReturn[fkName]; } else { var pkDb = r["r_owner"].ToString(); var pkTableName = r["r_table_name"].ToString(); var fkDb = r["owner"].ToString(); var fkTableName = r["table_name"].ToString(); var pktable = table.Database.Server.ExpectDatabase(pkDb).ExpectTable(pkTableName); var fktable = table.Database.Server.ExpectDatabase(fkDb).ExpectTable(fkTableName); //https://dev.mysql.com/doc/refman/8.0/en/referential-constraints-table.html var deleteRuleString = r["delete_rule"].ToString(); CascadeRule deleteRule = CascadeRule.Unknown; if (deleteRuleString == "CASCADE") { deleteRule = CascadeRule.Delete; } else if (deleteRuleString == "NO ACTION") { deleteRule = CascadeRule.NoAction; } else if (deleteRuleString == "RESTRICT") { deleteRule = CascadeRule.NoAction; } else if (deleteRuleString == "SET NULL") { deleteRule = CascadeRule.SetNull; } else if (deleteRuleString == "SET DEFAULT") { deleteRule = CascadeRule.SetDefault; } current = new DiscoveredRelationship(fkName, pktable, fktable, deleteRule); toReturn.Add(current.Name, current); } current.AddKeys(r["r_column_name"].ToString(), r["column_name"].ToString(), transaction); } } return(toReturn.Values.ToArray()); }
public override DiscoveredColumn[] DiscoverColumns(DiscoveredTable discoveredTable, IManagedConnection connection, string database) { var server = discoveredTable.Database.Server; List <DiscoveredColumn> columns = new List <DiscoveredColumn>(); var tableName = discoveredTable.GetRuntimeName(); DbCommand cmd = server.Helper.GetCommand(@"SELECT * FROM all_tab_cols WHERE table_name = :table_name AND owner =:owner AND HIDDEN_COLUMN <> 'YES' ", connection.Connection); cmd.Transaction = connection.Transaction; DbParameter p = new OracleParameter("table_name", OracleDbType.Varchar2); p.Value = tableName; cmd.Parameters.Add(p); DbParameter p2 = new OracleParameter("owner", OracleDbType.Varchar2); p2.Value = database; cmd.Parameters.Add(p2); using (var r = cmd.ExecuteReader()) { if (!r.HasRows) { throw new Exception("Could not find any columns for table " + tableName + " in database " + database); } while (r.Read()) { var toAdd = new DiscoveredColumn(discoveredTable, (string)r["COLUMN_NAME"], r["NULLABLE"].ToString() != "N") { Format = r["CHARACTER_SET_NAME"] as string }; toAdd.DataType = new DiscoveredDataType(r, GetSQLType_From_all_tab_cols_Result(r), toAdd); columns.Add(toAdd); } } //get auto increment information cmd = new OracleCommand("select table_name,column_name from ALL_TAB_IDENTITY_COLS WHERE table_name = :table_name AND owner =:owner", (OracleConnection)connection.Connection); cmd.Transaction = connection.Transaction; p = new OracleParameter("table_name", OracleDbType.Varchar2); p.Value = tableName; cmd.Parameters.Add(p); p2 = new OracleParameter("owner", OracleDbType.Varchar2); p2.Value = database; cmd.Parameters.Add(p2); using (var r = cmd.ExecuteReader()) { while (r.Read()) { var colName = r["column_name"].ToString(); var match = columns.Single(c => c.GetRuntimeName().Equals(colName, StringComparison.CurrentCultureIgnoreCase)); match.IsAutoIncrement = true; } } //get primary key information cmd = new OracleCommand(@"SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner FROM all_constraints cons, all_cons_columns cols WHERE cols.table_name = :table_name AND cols.owner = :owner AND cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cons.owner = cols.owner ORDER BY cols.table_name, cols.position", (OracleConnection)connection.Connection); cmd.Transaction = connection.Transaction; p = new OracleParameter("table_name", OracleDbType.Varchar2); p.Value = tableName; cmd.Parameters.Add(p); p2 = new OracleParameter("owner", OracleDbType.Varchar2); p2.Value = database; cmd.Parameters.Add(p2); using (var r = cmd.ExecuteReader()) { while (r.Read()) { columns.Single(c => c.GetRuntimeName().Equals(r["COLUMN_NAME"])).IsPrimaryKey = true; //mark all primary keys as primary } } return(columns.ToArray()); }
public override DiscoveredRelationship[] DiscoverRelationships(DiscoveredTable table, DbConnection connection, IManagedTransaction transaction = null) { string sql = $@"select c.constraint_name , x.table_schema as foreign_table_schema , x.table_name as foreign_table_name , x.column_name as foreign_column_name , y.table_schema , y.table_name , y.column_name , delete_rule from information_schema.referential_constraints c join information_schema.key_column_usage x on x.constraint_name = c.constraint_name join information_schema.key_column_usage y on y.ordinal_position = x.position_in_unique_constraint and y.constraint_name = c.unique_constraint_name where y.table_name='{table.GetRuntimeName()}' AND y.table_schema='{table.Schema??PostgreSqlSyntaxHelper.DefaultPostgresSchema}' order by c.constraint_name, x.ordinal_position"; Dictionary <string, DiscoveredRelationship> toReturn = new Dictionary <string, DiscoveredRelationship>(); using (var cmd = table.GetCommand(sql, connection, transaction?.Transaction)) { //fill data table to avoid multiple active readers using (DataTable dt = new DataTable()) { using (var da = new NpgsqlDataAdapter((NpgsqlCommand)cmd)) da.Fill(dt); foreach (DataRow r in dt.Rows) { var fkName = r["constraint_name"].ToString(); DiscoveredRelationship current; //could be a 2+ columns foreign key? if (toReturn.ContainsKey(fkName)) { current = toReturn[fkName]; } else { var pkDb = table.Database.GetRuntimeName(); var pkSchema = r["table_schema"].ToString(); var pkTableName = r["table_name"].ToString(); var fkDb = pkDb; var fkSchema = r["foreign_table_schema"].ToString(); var fkTableName = r["foreign_table_name"].ToString(); var pktable = table.Database.Server.ExpectDatabase(pkDb).ExpectTable(pkTableName, pkSchema); var fktable = table.Database.Server.ExpectDatabase(fkDb).ExpectTable(fkTableName, fkSchema); CascadeRule deleteRule = CascadeRule.Unknown; var deleteRuleString = r["delete_rule"].ToString(); if (deleteRuleString == "CASCADE") { deleteRule = CascadeRule.Delete; } else if (deleteRuleString == "NO ACTION") { deleteRule = CascadeRule.NoAction; } else if (deleteRuleString == "RESTRICT") { deleteRule = CascadeRule.NoAction; } else if (deleteRuleString == "SET NULL") { deleteRule = CascadeRule.SetNull; } else if (deleteRuleString == "SET DEFAULT") { deleteRule = CascadeRule.SetDefault; } current = new DiscoveredRelationship(fkName, pktable, fktable, deleteRule); toReturn.Add(current.Name, current); } current.AddKeys(r["column_name"].ToString(), r["foreign_column_name"].ToString(), transaction); } } } return(toReturn.Values.ToArray()); }
public override DiscoveredColumn[] DiscoverColumns(DiscoveredTable discoveredTable, IManagedConnection connection, string database) { List <DiscoveredColumn> toReturn = new List <DiscoveredColumn>(); string sqlColumns = @"SELECT * FROM information_schema.columns WHERE table_schema = @schemaName AND table_name = @tableName;"; using (DbCommand cmd = discoveredTable.GetCommand(sqlColumns, connection.Connection, connection.Transaction)) { var p = cmd.CreateParameter(); p.ParameterName = "@tableName"; p.Value = discoveredTable.GetRuntimeName(); cmd.Parameters.Add(p); var p2 = cmd.CreateParameter(); p2.ParameterName = "@schemaName"; p2.Value = string.IsNullOrWhiteSpace(discoveredTable.Schema) ? PostgreSqlSyntaxHelper.DefaultPostgresSchema : discoveredTable.Schema; cmd.Parameters.Add(p2); using (var r = cmd.ExecuteReader()) while (r.Read()) { bool isNullable = string.Equals(r["is_nullable"], "YES"); //if it is a table valued function prefix the column name with the table valued function name string columnName = discoveredTable is DiscoveredTableValuedFunction ? discoveredTable.GetRuntimeName() + "." + r["column_name"] : r["column_name"].ToString(); var toAdd = new DiscoveredColumn(discoveredTable, columnName, isNullable); toAdd.IsAutoIncrement = string.Equals(r["is_identity"], "YES"); toAdd.DataType = new DiscoveredDataType(r, GetSQLType_FromSpColumnsResult(r), toAdd); toAdd.Collation = r["collation_name"] as string; toReturn.Add(toAdd); } } if (!toReturn.Any()) { throw new Exception("Could not find any columns in table " + discoveredTable); } //don't bother looking for pks if it is a table valued function if (discoveredTable is DiscoveredTableValuedFunction) { return(toReturn.ToArray()); } var pks = ListPrimaryKeys(connection, discoveredTable); foreach (DiscoveredColumn c in toReturn) { if (pks.Any(pk => pk.Equals(c.GetRuntimeName()))) { c.IsPrimaryKey = true; } } return(toReturn.ToArray()); }
protected override string GetRenameTableSql(DiscoveredTable discoveredTable, string newName) { return(string.Format("RENAME TABLE `{0}` TO `{1}`;", discoveredTable.GetRuntimeName(), newName)); }
public string GetTabName() { return("View " + _table.GetRuntimeName()); }