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