public void AssignFieldsForProcessing(DiscoveredColumn field, List <DiscoveredColumn> fieldsToDiff, List <DiscoveredColumn> fieldsToUpdate)
{
if (IgnoreColumnInfo(field))
{
return;
}
if (Ignore(field))
{
return;
}
//it is a hic internal field but not one of the overwritten, standard ones
if (SpecialFieldNames.IsHicPrefixed(field)
||
UpdateOnly(field))
{
fieldsToUpdate.Add(field);
}
else
{
//it is not a hic internal field
fieldsToDiff.Add(field);
fieldsToUpdate.Add(field);
}
}
public override Guesser GetGuesserFor(DiscoveredColumn discoveredColumn)
{
var guesser = base.GetGuesserFor(discoveredColumn);
guesser.ExtraLengthPerNonAsciiCharacter = ExtraLengthPerNonAsciiCharacter;
return(guesser);
}
public virtual Guesser GetGuesserFor(DiscoveredColumn discoveredColumn)
{
var reqType = GetDataTypeRequestForSQLDBType(discoveredColumn.DataType.SQLType);
var req = new Guesser(reqType);
return(req);
}
public override void DropColumn(DbConnection connection, DiscoveredColumn columnToDrop)
{
using (var cmd = new NpgsqlCommand(
string.Format(
@"ALTER TABLE {0}
DROP COLUMN ""{1}"";", columnToDrop.Table.GetFullyQualifiedName(),
columnToDrop.GetRuntimeName()
), (NpgsqlConnection)connection))
cmd.ExecuteNonQuery();
}
private void CreateIndex(DiscoveredTable table, DiscoveredColumn onColumn, string configurationName)
{
try
{
table.CreatePrimaryKey(onColumn);
}
catch (Exception e)
{
throw new Exception("Failed to create unique primary key on the results of AggregateConfiguration " + configurationName, e);
}
}
public void TestBulkInsert_AlterColumn_MidTransaction(DatabaseType type)
{
var db = GetTestDatabase(type);
var tbl = db.CreateTable("MyBulkInsertTest",
new[]
{
new DatabaseColumnRequest("Name", new DatabaseTypeRequest(typeof(string), 10)),
new DatabaseColumnRequest("Age", new DatabaseTypeRequest(typeof(int)))
});
Assert.AreEqual(0, tbl.GetRowCount());
using (var dt = new DataTable())
{
dt.Columns.Add("Name");
dt.Columns.Add("Age");
dt.Rows.Add("Dave", 50);
dt.Rows.Add("Jamie", 60);
using (var transaction = tbl.Database.Server.BeginNewTransactedConnection())
{
using (var bulk = tbl.BeginBulkInsert(transaction.ManagedTransaction))
{
bulk.Timeout = 30;
bulk.Upload(dt);
//inside transaction the count is 2
Assert.AreEqual(2, tbl.GetRowCount(transaction.ManagedTransaction));
//New row is too long for the data type
dt.Rows.Clear();
dt.Rows.Add("Frankyyyyyyyyyyyyyyyyyyyyyy", 100);
//So alter the data type to handle up to string lengths of 100
//Find the column
DiscoveredColumn col = tbl.DiscoverColumn("Name", transaction.ManagedTransaction);
//Make it bigger
col.DataType.Resize(100, transaction.ManagedTransaction);
bulk.Upload(dt);
//inside transaction the count is 3
Assert.AreEqual(3, tbl.GetRowCount(transaction.ManagedTransaction));
}
transaction.ManagedTransaction.CommitAndCloseConnection();
}
}
//We abandoned transaction so final rowcount should be 0
Assert.AreEqual(3, tbl.GetRowCount());
}
public string GetAlterColumnToSql(DiscoveredColumn column, string newType, bool allowNulls)
{
StringBuilder sb = new StringBuilder("ALTER TABLE " + column.Table.GetFullyQualifiedName() + " MODIFY " + column.GetRuntimeName() + " " + newType + " ");
//If you are already null then Oracle will complain (https://www.techonthenet.com/oracle/errors/ora01451.php)
if (allowNulls != column.AllowNulls)
{
sb.Append(allowNulls ? "NULL" : "NOT NULL");
}
return(sb.ToString());
}
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>
/// Anonymisation with an <see cref="ANOTable"/> happens during data load. This means that the column goes from identifiable in RAW to anonymous in STAGING/LIVE. This means
/// that the datatype of the column changes depending on the <see cref="LoadStage"/>.
///
/// <para>Returns the appropriate datatype for the <see cref="LoadStage"/>. This is done by connecting to the mapping table and retrieving the mapping table types</para>
/// </summary>
/// <param name="loadStage"></param>
/// <returns></returns>
public string GetRuntimeDataType(LoadStage loadStage)
{
//cache answers
if (_identifiableDataType == null)
{
var server = DataAccessPortal.GetInstance().ExpectServer(Server, DataAccessContext.DataLoad);
DiscoveredColumn[] columnsFoundInANO = server.GetCurrentDatabase().ExpectTable(TableName).DiscoverColumns();
string expectedIdentifiableName = TableName.Substring("ANO".Length);
DiscoveredColumn anonymous = columnsFoundInANO.SingleOrDefault(c => c.GetRuntimeName().Equals(TableName));
DiscoveredColumn identifiable = columnsFoundInANO.SingleOrDefault(c => c.GetRuntimeName().Equals(expectedIdentifiableName));
if (anonymous == null)
{
throw new Exception("Could not find a column called " + TableName + " in table " + TableName + " on server " + Server + " (Columns found were " + string.Join(",", columnsFoundInANO.Select(c => c.GetRuntimeName()).ToArray()) + ")");
}
if (identifiable == null)
{
throw new Exception("Could not find a column called " + expectedIdentifiableName + " in table " + TableName + " on server " + Server + " (Columns found were " + string.Join(",", columnsFoundInANO.Select(c => c.GetRuntimeName()).ToArray()) + ")");
}
_identifiableDataType = identifiable.DataType.SQLType;
_anonymousDataType = anonymous.DataType.SQLType;
}
//return cached answer
switch (loadStage)
{
case LoadStage.GetFiles:
return(_identifiableDataType);
case LoadStage.Mounting:
return(_identifiableDataType);
case LoadStage.AdjustRaw:
return(_identifiableDataType);
case LoadStage.AdjustStaging:
return(_anonymousDataType);
case LoadStage.PostLoad:
return(_anonymousDataType);
default:
throw new ArgumentOutOfRangeException("loadStage");
}
}
/// <inheritdoc/>
public ColumnInfo CreateNewColumnInfo(ITableInfo parent, DiscoveredColumn discoveredColumn)
{
var toAdd =
new ColumnInfo((ICatalogueRepository)parent.Repository, discoveredColumn.GetFullyQualifiedName(),
discoveredColumn.DataType.SQLType, parent);
toAdd.Format = discoveredColumn.Format;
toAdd.Collation = discoveredColumn.Collation;
toAdd.IsPrimaryKey = discoveredColumn.IsPrimaryKey;
toAdd.IsAutoIncrement = discoveredColumn.IsAutoIncrement;
toAdd.SaveToDatabase();
return(toAdd);
}
private string GetCommand(DiscoveredTable table, DiscoveredColumn[] pks, DiscoveredColumn nonPk)
{
List <CustomLine> sqlLines = new List <CustomLine>();
sqlLines.Add(new CustomLine(string.Format("(t1.{0} is null AND t2.{0} is not null)", nonPk.GetRuntimeName()), QueryComponent.WHERE));
sqlLines.Add(new CustomLine(string.Format("t1.{0} = COALESCE(t1.{0},t2.{0})", nonPk.GetRuntimeName()), QueryComponent.SET));
sqlLines.AddRange(pks.Select(p => new CustomLine(string.Format("t1.{0} = t2.{0}", p.GetRuntimeName()), QueryComponent.JoinInfoJoin)));
var updateHelper = table.Database.Server.GetQuerySyntaxHelper().UpdateHelper;
return(updateHelper.BuildUpdate(
table,
table,
sqlLines));
}
public void AssignFieldsForProcessing(DiscoveredColumn field, List <DiscoveredColumn> fieldsToDiff, List <DiscoveredColumn> fieldsToUpdate)
{
//it is a hic internal field but not one of the overwritten, standard ones
if (SpecialFieldNames.IsHicPrefixed(field)
||
IsSupplementalMatch(field))
{
fieldsToUpdate.Add(field);
}
else
{
//it is not a hic internal field
fieldsToDiff.Add(field);
fieldsToUpdate.Add(field);
}
}
private void Add(DiscoveredColumn discoveredColumn)
{
if (items.Any(i => i.Tag.Equals(discoveredColumn)))
{
return;
}
var snip = new SubstringAutocompleteItem(discoveredColumn.GetRuntimeName());
snip.MenuText = discoveredColumn.GetRuntimeName(); //name of table
snip.Text = discoveredColumn.GetFullyQualifiedName(); //full SQL
snip.Tag = discoveredColumn; //record object for future reference
snip.ImageIndex = GetIndexFor(discoveredColumn, RDMPConcept.ColumnInfo.ToString());
AddUnlessDuplicate(snip);
}
public string GetAlterColumnToSql(DiscoveredColumn column, string newType, bool allowNulls)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine(
$@"ALTER TABLE ""{column.Table.GetRuntimeName()}"" ALTER COLUMN ""{column.GetRuntimeName()}"" TYPE {newType};");
var newNullability = allowNulls ? "NULL" : "NOT NULL";
if (allowNulls != column.AllowNulls)
{
sb.AppendFormat(
$@"ALTER TABLE ""{column.Table.GetRuntimeName()}"" ALTER COLUMN ""{column.GetRuntimeName()}"" SET {newNullability}");
}
return(sb.ToString());
}
/// <inheritdoc/>
public ColumnInfo CreateNewColumnInfo(ITableInfo parent, DiscoveredColumn discoveredColumn)
{
var col = new ColumnInfo((ICatalogueRepository)parent.Repository, discoveredColumn.GetFullyQualifiedName(), discoveredColumn.DataType.SQLType, parent);
//if it has an explicitly specified format (Collation)
col.Format = discoveredColumn.Format;
//if it is a primary key
col.IsPrimaryKey = discoveredColumn.IsPrimaryKey;
col.IsAutoIncrement = discoveredColumn.IsAutoIncrement;
col.Collation = discoveredColumn.Collation;
col.SaveToDatabase();
return(col);
}
private bool Ignore(DiscoveredColumn field)
{
if (_ignore == null)
{
return(false);
}
//its a global ignore based on regex ignore pattern?
bool match = _ignore.IsMatch(field.GetRuntimeName());
if (match && field.IsPrimaryKey)
{
throw new NotSupportedException("Ignore Pattern " + _ignore + " matched Primary Key column '" + field.GetRuntimeName() + "' this is not permitted");
}
return(match);
}
private bool IsSupplementalMatch(DiscoveredColumn field)
{
if (_updateButDoNotDiffExtended == null)
{
return(false);
}
//its a supplemental ignore e.g. MessageGuid
bool match = _updateButDoNotDiffExtended.IsMatch(field.GetRuntimeName());
if (match && field.IsPrimaryKey)
{
throw new NotSupportedException("UpdateButDoNotDiff Pattern " + _updateButDoNotDiffExtended + " matched Primary Key column '" + field.GetRuntimeName() + "' this is not permitted");
}
return(match);
}
public string GetAlterColumnToSql(DiscoveredColumn column, string newType, bool allowNulls)
{
var syntax = column.Table.Database.Server.GetQuerySyntaxHelper();
StringBuilder sb = new StringBuilder();
sb.AppendLine(
$@"ALTER TABLE {column.Table.GetFullyQualifiedName()} ALTER COLUMN {syntax.EnsureWrapped(column.GetRuntimeName())} TYPE {newType};");
var newNullability = allowNulls ? "NULL" : "NOT NULL";
if (allowNulls != column.AllowNulls)
{
sb.AppendFormat(
$@"ALTER TABLE {column.Table.GetFullyQualifiedName()} ALTER COLUMN {syntax.EnsureWrapped(column.GetRuntimeName())} SET {newNullability}");
}
return(sb.ToString());
}
/// <summary>
/// Returns the SQL string <paramref name="col"/>=<paramref name="value"/>
/// </summary>
/// <param name="col">LHS argument</param>
/// <param name="value">RHS argument, if null then string literal "null" is used</param>
/// <param name="op">The SQL operator to use, if null "=" is used</param>
/// <returns></returns>
protected string GetFieldEqualsValueExpression(DiscoveredColumn col, string value, string op)
{
StringBuilder builder = new StringBuilder();
builder.Append(col.GetFullyQualifiedName());
builder.Append(" ");
builder.Append(op ?? "=");
builder.Append(" ");
if (string.IsNullOrWhiteSpace(value))
{
builder.Append("null");
}
else
{
builder.Append(value);
}
return(builder.ToString());
}
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);
}
}
private bool IgnoreColumnInfo(DiscoveredColumn field)
{
if (_alsoIgnore == null)
{
return(false);
}
// TODO: if a load targets 2 tables with the same name in different databases and one has a column marked ignore it could be ignored in both during load. Note that `field` parameter is the from column not the to column
//its a column we were told to ignore
ColumnInfo match = _alsoIgnore.FirstOrDefault(c =>
c.GetRuntimeName().Equals(field.GetRuntimeName(), StringComparison.CurrentCultureIgnoreCase) &&
c.TableInfo.GetRuntimeName().Equals(field.Table.GetRuntimeName(), StringComparison.CurrentCultureIgnoreCase)
);
if (match != null && field.IsPrimaryKey)
{
throw new NotSupportedException($"ColumnInfo {match} is marked {nameof(ColumnInfo.IgnoreInLoads)} but is a Primary Key column this is not permitted");
}
return(match != null);
}
public string GetAlterColumnToSql(DiscoveredColumn column, string newType, bool allowNulls)
{
if (column.DataType.SQLType == "bit" && newType != "bit")
{
StringBuilder sb = new StringBuilder();
//go via string because SQL server cannot handle turning bit to int (See test BooleanResizingTest)
//Fails on Sql Server even when column is all null or there are no rows
/*
* DROP TABLE T
* CREATE TABLE T (A bit NULL)
* alter table T alter column A datetime2 null
*/
sb.AppendLine("ALTER TABLE " + column.Table.GetRuntimeName() + " ALTER COLUMN " + column.GetRuntimeName() + " varchar(4000) " + (allowNulls ? "NULL" : "NOT NULL"));
sb.AppendLine("ALTER TABLE " + column.Table.GetRuntimeName() + " ALTER COLUMN " + column.GetRuntimeName() + " " + newType + " " + (allowNulls ? "NULL" : "NOT NULL"));
return(sb.ToString());
}
return("ALTER TABLE " + column.Table.GetRuntimeName() + " ALTER COLUMN " + column.GetRuntimeName() + " " + newType + " " + (allowNulls ? "NULL" : "NOT NULL"));
}
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 void BadNames_DiscoverRelationships(DatabaseType dbType)
{
var db = GetTestDatabase(dbType);
var tbl1 = db.CreateTable(BadTableName, new[]
{
new DatabaseColumnRequest(BadColumnName, new DatabaseTypeRequest(typeof(string), 100))
{
IsPrimaryKey = true
},
new DatabaseColumnRequest("Frrrrr ##' ank", new DatabaseTypeRequest(typeof(int)))
});
DiscoveredColumn pk = tbl1.DiscoverColumns().Single(c => c.IsPrimaryKey);
DatabaseColumnRequest fk;
var tbl2 = db.CreateTable(new CreateTableArgs(db, BadTableName + "2", null)
{
ExplicitColumnDefinitions = new [] { fk = new DatabaseColumnRequest(BadColumnName + "2", new DatabaseTypeRequest(typeof(string), 100)) },
ForeignKeyPairs = new Dictionary <DatabaseColumnRequest, DiscoveredColumn> {
{ fk, pk }
}
});
var r = tbl1.DiscoverRelationships().Single();
Assert.AreEqual(tbl1, r.PrimaryKeyTable);
Assert.AreEqual(tbl2, r.ForeignKeyTable);
Assert.AreEqual(pk, r.Keys.Single().Key);
Assert.AreEqual(tbl2.DiscoverColumn(BadColumnName + "2"), r.Keys.Single().Value);
tbl2.Drop();
tbl1.Drop();
}
private string GetCSharpTypeFor(DiscoveredColumn col, out string setCode)
{
var r = "r[\"" + col.GetRuntimeName() + "\"]";
if (col.DataType.GetLengthIfString() != -1)
{
if (col.AllowNulls)
{
setCode = r + " as string;";
}
else
{
setCode = r + ".ToString();";
}
return("string");
}
if (col.DataType.SQLType.Contains("date"))
{
if (col.AllowNulls)
{
setCode = "ObjectToNullableDateTime(" + r + ");";
return("DateTime?");
}
else
{
setCode = "Convert.ToDateTime(" + r + ");";
return("DateTime");
}
}
if (col.DataType.SQLType.Contains("int"))
{
if (col.AllowNulls)
{
setCode = "ObjectToNullableInt(" + r + ");";
return("int?");
}
else
{
setCode = "Convert.ToInt32(" + r + ");";
return("int");
}
}
if (col.DataType.SQLType.Contains("bit"))
{
if (col.AllowNulls)
{
setCode = "ObjectToNullableBool(" + r + ");//TODO: Confirm you actually mean true/false/null?";
return("bool?");
}
else
{
setCode = "Convert.ToBoolean(" + r + ");";
return("bool");
}
}
setCode = "TODO Unrecognised Type";
return("TODO Unrecognised Type");
}
private string GetORLine(DiscoveredColumn c, IQuerySyntaxHelper syntax)
{
return(string.Format("(t1.{0} <> t2.{0} OR (t1.{0} is null AND t2.{0} is not null) OR (t2.{0} is null AND t1.{0} is not null))", syntax.EnsureWrapped(c.GetRuntimeName())));
}
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());
}
private void ConfirmNullability(DiscoveredColumn column, bool expectedNullability, ICheckNotifier notifier)
{
bool nullability = column.AllowNulls;
notifier.OnCheckPerformed(new CheckEventArgs("Nullability of " + column + " is AllowNulls=" + nullability + ", (expected " + expectedNullability + ")", nullability == expectedNullability ? CheckResult.Success : CheckResult.Fail, null));
}
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 string GetAlterColumnToSql(DiscoveredColumn column, string newType, bool allowNulls)
{
var syntax = column.Table.Database.Server.GetQuerySyntaxHelper();
return("ALTER TABLE " + column.Table.GetFullyQualifiedName() + " MODIFY COLUMN " + syntax.EnsureWrapped(column.GetRuntimeName()) + " " + newType + " " + (allowNulls ? "NULL" : "NOT NULL"));
}
