public DatabaseColumnStructure[] GetDatabaseTableStructure(DatabaseContext dc, string tableName)
{
var primaryKeys = dc.ExecuteReader("SELECT name FROM syscolumns WHERE id in (SELECT id FROM sysobjects WHERE name = @table_name) AND colid in (SELECT SIK.colid FROM sysindexkeys SIK JOIN sysobjects SO ON SIK.id = so.id WHERE SIK.indid = 1 AND SO.name = @table_name);", tableName);
var rows = dc.ExecuteStoredProcedure("sp_columns", "table_name", tableName);
var result = new DatabaseColumnStructure[rows[0].Length];
for (var i = 0; i < rows[0].Length; i++)
{
var columnName = Convert.ToString(rows[0][i, 3]);
var length = Convert.ToInt32(rows[0][i, 7]);
var type = Convert.ToString(rows[0][i, 5]).Remove(" identity").ToUpper();
if (type == "NVARCHAR" || type == "NTEXT")
{
if (type == "NTEXT" && length == Int32.MaxValue - 1)
{
type = "NVARCHAR";
}
length /= 2;
if (length > 8000)
{
length = 8000;
}
}
var identity = IsIdentity(primaryKeys, columnName);
var nullable = Convert.ToInt32(rows[0][i, 10]) == 1;
var defaultValue = rows[0][i, 12] == null ? null : Convert.ToString(rows[0][i, 12]).TrimStart('(').TrimEnd(')');
result[i] = new DatabaseColumnStructure(columnName, type, nullable, identity, 1, 1, defaultValue, length, identity);
}
return(result);
}
public void ModifyColumnStructure(DatabaseContext dc, string tableName, DatabaseColumnStructure destinationDcs)
{
if (this == destinationDcs)
{
return;
}
// WARNING - This will recreate the original column
//var def_value_str = DefaultValue.ConvertToString();
//var def_value = def_value_str.FirstChar() == '\'' && def_value_str.LastChar() == '\'';
if (DefaultValue != destinationDcs.DefaultValue)
//if (destination_dcs.DefaultValue != null)
{
var constraintNames = DatabaseUtils.GetDefaultConstraintNames(dc, tableName, ColumnName);
foreach (var constraintName in constraintNames)
{
dc.ExecuteNonQuery($"ALTER TABLE {tableName} DROP CONSTRAINT {constraintName}");
}
// Delete old column, create new with default values
DeleteColumn(dc, tableName);
CreateColumn(dc, tableName, destinationDcs.ToString());
}
else // Modify column type
{
ModifyColumn(dc, tableName, destinationDcs);
}
}
public bool ModifyColumn(DatabaseContext dc, string tableName, DatabaseColumnStructure newColumnStructure)
{
if (ToString() != newColumnStructure.ToString())
{
// FIXME - We want to add IDENTITY(1,1) property to column
if (ToString().IndexOf("IDENTITY(1,1) ") == NotFound && newColumnStructure.ToString().IndexOf("IDENTITY(1,1)") > NotFound)
{
/*CREATE TABLE dbo.Tmp_Events (ID bigint NOT NULL IDENTITY (1, 1), language_element_id bigint NOT NULL, note nvarchar(200) NULL, checksum nvarchar(400) NULL) ON [PRIMARY]
* ALTER TABLE dbo.Tmp_Events SET (LOCK_ESCALATION = TABLE)
* SET IDENTITY_INSERT dbo.Tmp_Events ON
* IF EXISTS(SELECT * FROM dbo.Events)
* EXEC('INSERT INTO dbo.Tmp_Events (ID, language_element_id, note, checksum)
* SELECT ID, language_element_id, note, checksum FROM dbo.Events WITH (HOLDLOCK TABLOCKX)')
* SET IDENTITY_INSERT dbo.Tmp_Events OFF
* DROP TABLE dbo.Events
* EXECUTE sp_rename N'dbo.Tmp_Events', N'Events', 'OBJECT'
* ALTER TABLE dbo.Events ADD CONSTRAINT PK__Events__3214EC2707020F21 PRIMARY KEY CLUSTERED (ID) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]*/
return(false);
//return dc.ExecuteNonQuery(String.Format("SET IDENTITY_INSERT {0} ON"), table_name);
//dc.ExecuteQuery(String.Format("ALTER TABLE {0} ALTER COLUMN {1};", table_name, new_column_structure.ToString()));
}
// FIXME - We want to remove IDENTITY(1,1) property from column
if (ToString().IndexOf("IDENTITY(1,1) ") > NotFound && newColumnStructure.ToString().IndexOf("IDENTITY(1,1)") == NotFound)
{
/*CREATE TABLE dbo.Tmp_Events (ID bigint NOT NULL, language_element_id bigint NOT NULL, note nvarchar(200) NULL, checksum nvarchar(400) NULL) ON [PRIMARY]
* ALTER TABLE dbo.Tmp_Events SET (LOCK_ESCALATION = TABLE)
* IF EXISTS(SELECT * FROM dbo.Events)
* EXEC('INSERT INTO dbo.Tmp_Events (ID, language_element_id, note, checksum)
* SELECT ID, language_element_id, note, checksum FROM dbo.Events WITH (HOLDLOCK TABLOCKX)')
* DROP TABLE dbo.Events
* EXECUTE sp_rename N'dbo.Tmp_Events', N'Events', 'OBJECT'
* ALTER TABLE dbo.Events ADD CONSTRAINT PK__Events__3214EC2707020F21 PRIMARY KEY CLUSTERED (ID) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]*/
// Create temporary column
/*string column_specification = GetToString("_temporary_column_");
*
* if (this.default_value != null)
* {
* if (this.type == "NVARCHAR") column_specification += String.Format(" DEFAULT '{0}'", this.default_value.ToString());
* else column_specification += String.Format(" DEFAULT {0}", this.default_value.ToString());
* }
*
* CreateColumn(dc, table_name, column_specification);
* dc.ExecuteNonQuery(String.Format("UPDATE Events SET _temporary_column_ = {0};", this.column_name));
*/
return(false);
//return dc.ExecuteNonQuery(String.Format("SET IDENTITY_INSERT {0} OFF", table_name));
}
return(dc.ExecuteNonQuery($"ALTER TABLE {tableName} ALTER COLUMN {newColumnStructure};"));
}
return(false);
}
public void ModifyTableStructure(DatabaseContext dc, DatabaseTableStructure destinationDts)
{
foreach (var destinationColumnStructure in destinationDts.Columns)
{
var dcs = this[destinationColumnStructure.ColumnName];
if (dcs == null) // This database column must be created
{
DatabaseColumnStructure.CreateColumn(dc, TableName, destinationColumnStructure.ToString());
}
else // Check column structures
{
dcs.ModifyColumnStructure(dc, TableName, destinationColumnStructure);
}
}
foreach (var columnStructure in Columns)
{
if (destinationDts[columnStructure.ColumnName] == null) // This database column does not exists anymore
{
columnStructure.DeleteColumn(dc, TableName);
}
}
if (!NeedToRecreatePrimaryIds(destinationDts))
{
return;
}
for (var i = 0; i < PrimaryIds.Count; i++)
{
dc.ExecuteNonQuery("DECLARE @CMD VARCHAR(MAX); SET @CMD = 'ALTER TABLE Options DROP CONSTRAINT ' + (SELECT constraint_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 AND TABLE_NAME = @C AND COLUMN_NAME = @C2); EXEC(@CMD);", TableName, PrimaryIds[i].ColumnName);
}
var primaryKeys = GetPrimaryIdsInStringFormat(destinationDts.PrimaryIds);
var alterTable = $"ALTER TABLE {TableName} ADD PRIMARY KEY ({primaryKeys});";
dc.ExecuteNonQuery(alterTable);
}
public static DatabaseTableStructure GetDatabaseTableStructure(string tableDescriptor)
{
if (String.IsNullOrEmpty(tableDescriptor))
{
return(null);
}
//table_descriptor = table_descriptor.ToUpper();
var tableName = tableDescriptor.Substring("CREATE TABLE ", " (");
if (tableName.IndexOf('[') == 0 && tableName.IndexOf(']') == tableName.Length - 1)
{
tableName = tableName.Substring(1, tableName.Length - 2);
}
var columns = new List <DatabaseColumnStructure>();
var primaryKeys = tableDescriptor.Substring("PRIMARY KEY(", ")").Split(',');
for (var i = 0; i < primaryKeys.Length; i++)
{
primaryKeys[i] = primaryKeys[i].Trim();
}
var index = tableDescriptor.IndexOf('(') + 1;
var end = false;
while (!end)
{
var index2 = tableDescriptor.IndexOf(", ", index + 1);
if (index2 == NotFound)
{
end = true;
index2 = tableDescriptor.IndexOf(" PRIMARY KEY", index + 1);
}
if (index2 != NotFound)
{
var columnDescriptor = tableDescriptor.Substring(index, index2 - index);
if (columnDescriptor.IndexOf(", ") == 0)
{
columnDescriptor = columnDescriptor.Substring(2);
}
var cs = DatabaseColumnStructure.GetDatabaseColumnStructure(columnDescriptor, primaryKeys);
columns.Add(cs);
}
index = index2;
}
//var columnsArray = (DatabaseColumnStructure[])columns.ToArray(typeof(DatabaseColumnStructure));
var id = DatabaseColumnStructure.GetPrimaryId(columns);
if (!id.Any())
{
var ids = tableDescriptor.Substring("PRIMARY KEY(", ")").Split(',');
for (var i = 0; i < ids.Length; i++)
{
for (var j = 0; j < columns.Count; j++)
{
if (!String.Equals(columns[j].ColumnName, ids[i].Trim(), StringComparison.CurrentCultureIgnoreCase))
{
continue;
}
id.Add(columns[j]); // TODO: Why has only one key?
break;
}
}
}
return(new DatabaseTableStructure(tableName, columns, id));
}
public IEnumerable <DatabaseColumnStructure> GetPrimaryId()
{
return(DatabaseColumnStructure.GetPrimaryId(Columns));
}
