private string[] ListPrimaryKeys(IManagedConnection con, DiscoveredTable table)
{
string query = $@"SELECT
pg_attribute.attname,
format_type(pg_attribute.atttypid, pg_attribute.atttypmod)
FROM pg_index, pg_class, pg_attribute
WHERE
pg_class.oid = '{table.GetFullyQualifiedName()}'::regclass AND
indrelid = pg_class.oid AND
pg_attribute.attrelid = pg_class.oid AND
pg_attribute.attnum = any(pg_index.indkey)
AND indisprimary";
List <string> toReturn = new List <string>();
using (DbCommand cmd = table.GetCommand(query, con.Connection))
{
cmd.Transaction = con.Transaction;
using (DbDataReader r = cmd.ExecuteReader())
{
while (r.Read())
{
toReturn.Add((string)r["attname"]);
}
r.Close();
}
}
return(toReturn.ToArray());
}
private static HashSet <string> FetchTable(ColumnInfo columnInfo)
{
var logger = LogManager.GetCurrentClassLogger();
HashSet <string> toReturn = new HashSet <string>(StringComparer.CurrentCultureIgnoreCase);
var qb = new QueryBuilder(limitationSQL: null, hashingAlgorithm: null);
qb.AddColumn(new ColumnInfoToIColumn(new MemoryRepository(), columnInfo));
string sql = qb.SQL;
logger.Info("Running PatientID fetch SQL:" + sql);
DiscoveredTable server = columnInfo.TableInfo.Discover(DataAccessContext.DataExport);
using (DbConnection con = server.Database.Server.GetConnection())
{
con.Open();
DbCommand cmd = server.GetCommand(sql, con);
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
toReturn.Add(reader[0].ToString());
}
}
logger.Info($"Found {toReturn.Count} patients in the reject list");
return(toReturn);
}
private string[] ListPrimaryKeys(IManagedConnection con, DiscoveredTable table)
{
List <string> toReturn = new List <string>();
string query = String.Format(@"SELECT i.name AS IndexName,
OBJECT_NAME(ic.OBJECT_ID) AS TableName,
COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName,
c.is_identity
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE (i.is_primary_key = 1) AND ic.OBJECT_ID = OBJECT_ID('{0}')
ORDER BY OBJECT_NAME(ic.OBJECT_ID), ic.key_ordinal", GetObjectName(table));
using (DbCommand cmd = table.GetCommand(query, con.Connection))
{
cmd.Transaction = con.Transaction;
using (DbDataReader r = cmd.ExecuteReader())
{
while (r.Read())
{
toReturn.Add((string)r["ColumnName"]);
}
r.Close();
}
}
return(toReturn.ToArray());
}
public bool UseOldDateTimeDefaultMethod(DiscoveredTable table)
{
using (var con = table.Database.Server.GetConnection())
{
con.Open();
return(UseOldDateTimeDefaultMethod(table.GetCommand("SELECT VERSION()", con).ExecuteScalar()?.ToString()));
}
}
public override void CreatePrimaryKey(DiscoveredTable table, DiscoveredColumn[] discoverColumns, IManagedConnection connection, int timeoutInSeconds = 0)
{
try
{
var columnHelper = GetColumnHelper();
foreach (var col in discoverColumns.Where(dc => dc.AllowNulls))
{
var alterSql = columnHelper.GetAlterColumnToSql(col, col.DataType.SQLType, false);
var alterCmd = table.GetCommand(alterSql, connection.Connection, connection.Transaction);
alterCmd.CommandTimeout = timeoutInSeconds;
alterCmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
throw new Exception("Failed to create primary key on table " + table + " using columns (" + string.Join(",", discoverColumns.Select(c => c.GetRuntimeName())) + ")", e);
}
base.CreatePrimaryKey(table, discoverColumns, connection, timeoutInSeconds);
}
public override void CreatePrimaryKey(DatabaseOperationArgs args, DiscoveredTable table, DiscoveredColumn[] discoverColumns)
{
try
{
using (var connection = args.GetManagedConnection(table))
{
var columnHelper = GetColumnHelper();
foreach (var col in discoverColumns.Where(dc => dc.AllowNulls))
{
var alterSql = columnHelper.GetAlterColumnToSql(col, col.DataType.SQLType, false);
using (var alterCmd = table.GetCommand(alterSql, connection.Connection, connection.Transaction))
args.ExecuteNonQuery(alterCmd);
}
}
}
catch (Exception e)
{
throw new AlterFailedException(string.Format(FAnsiStrings.DiscoveredTableHelper_CreatePrimaryKey_Failed_to_create_primary_key_on_table__0__using_columns___1__, table, string.Join(",", discoverColumns.Select(c => c.GetRuntimeName()))), e);
}
base.CreatePrimaryKey(args, table, discoverColumns);
}
public IEnumerable <string> GetWhitelist()
{
var colName = _column.GetRuntimeName();
using (var con = _discoveredTable.Database.Server.GetConnection())
{
con.Open();
var cmd = _discoveredTable.GetCommand("Select DISTINCT " + _column.GetFullyQualifiedName() + " FROM " + _discoveredTable.GetFullyQualifiedName(), con);
var r = cmd.ExecuteReader();
while (r.Read())
{
var o = r[colName] as string;
if (o != null)
{
yield return(o.Trim());
}
}
}
}
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());
}
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 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());
}
