public void CreateTableWithMultiColumnPrimaryKey()
{
const string ColumnName1 = "Id";
const string ColumnName2 = "Id2";
const string ColumnName3 = "Id3";
const string TableName = nameof(CreateTableWithMultiColumnPrimaryKey);
Table table = new Table(TableName);
Column column = new Column(ColumnName1, DataType.Int());
Column column2 = new Column(ColumnName2, DataType.Int());
Column column3 = new Column(ColumnName3, DataType.Int());
table.Columns.AddAll(column, column2, column3);
PrimaryKeyConstraint primaryKeyConstraint = new PrimaryKeyConstraint();
primaryKeyConstraint.AddColumns
(
Tuple.Create(column, ColumnSort.ASC),
Tuple.Create(column2, ColumnSort.DESC),
Tuple.Create(column3, ColumnSort.ASC)
);
table.Constraints.Add(primaryKeyConstraint);
VerifyPrimaryKey(ColumnName1, TableName, table);
}
public void CreateStatement_StatementIsCorrect()
{
var primaryKeyConstraint = new PrimaryKeyConstraint();
string output = primaryKeyConstraint.CreateStatement();
Assert.AreEqual(output, "PRIMARY KEY");
}
public BaseIndexConstraint GetCreatedKey()
{
BaseIndexConstraint index = null;
if (tabControl.SelectedTab == tabPageFK)
index = new ForeignKeyConstraint();
else if (rbPrimaryKey.Checked)
index = new PrimaryKeyConstraint();
else if (rbUnique.Checked)
index = new UniqueConstraint();
else if (rbIndex.Checked)
index = new Index();
else
throw new Exception("Please select an index/constraint type");
index.ColumnNames = new List<string>();
index.Name = txtKeyName.Text.MakeFileName();
if (index is ForeignKeyConstraint)
{
ForeignKeyConstraint fk = index as ForeignKeyConstraint;
foreach (Panel panel in pnlForeignTableColumns.Controls)
fk.ColumnNames.Add(((panel.Controls[1] as ComboBox).SelectedItem as Column).Name);
fk.RefConstraintName = (comboForeignTableKeys.SelectedItem as BaseIndexConstraint).Name;
fk.RefTableName = (comboForeignTableKeys.SelectedItem as BaseIndexConstraint).Table.Name;
}
else
{
foreach (KeyColumn fd in keyColumnBindingSource.DataSource as List<KeyColumn>)
if (fd.Selected)
index.ColumnNames.Add(fd.Name);
}
return index;
}
/// <summary>
/// Enumerates the SQL commands that are necessary to create
/// the specified primary key constraint (<paramref name="pk"/>).
/// </summary>
/// <param name="pk">The primary key constraint.</param>
/// <returns>The SQL commands.</returns>
protected virtual IEnumerable <string> AddPrimaryKeyConstraint(PrimaryKeyConstraint pk)
{
yield return(string.Format(
"ALTER TABLE {0} ADD CONSTRAINT {1} PRIMARY KEY ({2});",
EscapeTableName(pk.Table.Name),
EscapeConstraintName(pk.Name),
string.Join(", ", EscapeColumnNames(pk.ColumnNames))
));
}
public void CreateStatement_StatementIsCorrect_WithAutoincrement()
{
var primaryKeyConstraint = new PrimaryKeyConstraint {
Autoincrement = true
};
string output = primaryKeyConstraint.CreateStatement();
Assert.AreEqual(output, "PRIMARY KEY AUTOINCREMENT");
}
public void CreateTableWithMultipleConstraints()
{
const string PrimaryKeyColumnName = "PK";
const string UniqueColumnName1 = "UQ1";
const string UniqueColumnName2 = "UQ2";
string tableName = nameof(CreateTableWithMultipleConstraints);
Table table = new Table(tableName);
Column column1 = new Column(PrimaryKeyColumnName, DataType.Int());
Column column2 = new Column(UniqueColumnName1, DataType.Char(10));
Column column3 = new Column(UniqueColumnName2, DataType.Money());
table.Columns.AddAll(column1, column2, column3);
PrimaryKeyConstraint primaryKeyConstraint = new PrimaryKeyConstraint();
primaryKeyConstraint.IndexType = IndexType.NONCLUSTERED;
primaryKeyConstraint.AddColumn(column1);
UniqueConstraint uniqueConstraint = new UniqueConstraint();
uniqueConstraint.IndexType = IndexType.CLUSTERED;
uniqueConstraint.AddColumns(Tuple.Create(column2, ColumnSort.DESC), Tuple.Create(column3, ColumnSort.ASC));
table.Constraints.AddAll(primaryKeyConstraint, uniqueConstraint);
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
Assert.False(table.IsTablePresentInDatabase(sqlConnection));
table.Create(sqlConnection);
Assert.True(table.IsTablePresentInDatabase(sqlConnection));
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
string sql = $@"
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
AND TABLE_NAME = '{tableName}'";
sqlCommand.CommandText = sql;
string primaryKeycolumnNameResult = (string)sqlCommand.ExecuteScalar();
Assert.Equal(PrimaryKeyColumnName, primaryKeycolumnNameResult);
sql = $"SELECT COUNT(COLUMN_NAME) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsUniqueCnst') = 1 AND TABLE_NAME = '{tableName}'";
sqlCommand.CommandText = sql;
int columnCount = (int)sqlCommand.ExecuteScalar();
Assert.Equal(2, columnCount);
}
table.Drop(sqlConnection);
Assert.False(table.IsTablePresentInDatabase(sqlConnection));
}
}
public void AddPrimaryKeyConstraintTest()
{
PrimaryKeyConstraint pk = Target.AddPrimaryKeyConstraint("MyConstraint", "MyColumn");
Assert.AreEqual(2 /* table + constraint */, Target.Database.MigrationSteps.Count, "invalid number of migration steps");
Assert.AreEqual(pk, Target.Database.MigrationSteps.ToArray()[1]);
Assert.AreEqual("MyConstraint", pk.Name);
Assert.AreEqual(1, pk.ColumnNames.Length);
Assert.AreEqual("MyColumn", pk.ColumnNames[0]);
Assert.AreEqual(Modifier.Add, pk.Modifier);
}
protected override void VisitPrimaryKeyConstraint(PrimaryKeyConstraint item)
{
if (IsVisitingCandidateIdColumn)
{
// we found the primary key!
this.HasFoundPrimaryKey = true;
}
else
{
throw new NotSupportedException("Primary Key Constraint can only be present on a single column of datatype UNIQUEIDENTITIFER.");
}
}
public static TableConstraint Create(string name)
{
TableConstraint constraint = null;
if (name == "PRIMARY KEY")
constraint = new PrimaryKeyConstraint();
else if (name == "FOREIGN KEY")
constraint = new ForeignKeyConstraint();
else if (name == "UNIQUE")
constraint = new UniqueConstraint();
else if (name == "CHECK")
constraint = new CheckConstraint();
return constraint;
}
private void AddPrimaryKeyConstraintAndAdjustTypeIfNecessary(EdmProperty property, ColumnStatement columnStatement)
{
// Only handle a single primary key this way.
if (keyMembers.Count() != 1 || !property.Equals(keyMembers.Single()))
{
return;
}
ConvertIntegerType(columnStatement);
var primaryKeyConstraint = new PrimaryKeyConstraint();
primaryKeyConstraint.Autoincrement = property.GetCustomAnnotation <AutoincrementAttribute>() != null;
columnStatement.ColumnConstraints.Add(primaryKeyConstraint);
}
public void CreateTableWithSingleColumnPrimaryKey()
{
const string ColumnName = "Id";
const string TableName = nameof(CreateTableWithSingleColumnPrimaryKey);
Table table = new Table(TableName);
Column column = new Column(ColumnName, DataType.Int());
table.Columns.Add(column);
PrimaryKeyConstraint primaryKeyConstraint = new PrimaryKeyConstraint("PrimaryKeyConstraint");
primaryKeyConstraint.AddColumn(column);
table.Constraints.Add(primaryKeyConstraint);
VerifyPrimaryKey(ColumnName, TableName, table);
}
/// <summary>
/// Builds the SQL fragment that describes a primary key constraint
/// within a CREATE TABLE statement.
/// </summary>
/// <param name="pk">The primary key constraint.</param>
/// <returns>The SQL fragment.</returns>
protected virtual string BuildCreateTablePrimaryKeyConstraint(PrimaryKeyConstraint pk)
{
if (pk.Name != null)
{
return(string.Format("CONSTRAINT {0} PRIMARY KEY ({1})",
EscapeConstraintName(pk.Name),
string.Join(", ", EscapeColumnNames(pk.ColumnNames))
));
}
else
{
return(string.Format("PRIMARY KEY ({0})",
string.Join(", ", EscapeColumnNames(pk.ColumnNames))
));
}
}
/// <summary>
/// Enumerates the SQL commands that are necessary to drop
/// the specified primary key constraint (<paramref name="pk"/>).
/// </summary>
/// <param name="pk">The primary key constraint.</param>
/// <returns>The SQL commands.</returns>
protected override IEnumerable <string> DropPrimaryKeyConstraint(PrimaryKeyConstraint pk)
{
if (string.IsNullOrEmpty(pk.Name))
{
return(new string[] {
string.Format("ALTER TABLE {0} ALTER COLUMN {1} DROP IDENTITY;",
EscapeTableName(pk.Table.Name),
EscapeConstraintName(pk.Name)
)
});
}
else
{
return(base.DropPrimaryKeyConstraint(pk));
}
}
public void CreateMultiColumnForeignKeyConstraints()
{
const string ForeignKeyColumnName1 = "PersonId";
const string ForeignKeyColumnName2 = "PersonName";
Table primaryTable = new Table(nameof(CreateMultiColumnForeignKeyConstraints) + "Persons");
Column id = new Column("Id", DataType.Int())
{
Nullable = false
};
Column lastName = new Column("LastName", DataType.VarChar(255))
{
Nullable = false
};
Column firstName = new Column("FirstName", DataType.VarChar(255));
Column age = new Column("Age", DataType.Int());
primaryTable.Columns.AddAll(id, lastName, firstName, age);
PrimaryKeyConstraint primaryTablePrimaryKeyConstraint = new PrimaryKeyConstraint();
primaryTablePrimaryKeyConstraint.AddColumns(id, lastName);
primaryTable.Constraints.Add(primaryTablePrimaryKeyConstraint);
Table foreignTable = new Table(nameof(CreateMultiColumnForeignKeyConstraints) + "Orders");
Column orderId = new Column("OrderId", DataType.Int())
{
Nullable = false
};
Column orderNumber = new Column("OrderNumber", DataType.Int())
{
Nullable = false
};
Column personId = new Column(ForeignKeyColumnName1, DataType.Int());
Column personLastName = new Column(ForeignKeyColumnName2, DataType.VarChar(255));
foreignTable.Columns.AddAll(orderId, orderNumber, personId, personLastName);
PrimaryKeyConstraint foreignTableprimaryKeyConstraint = new PrimaryKeyConstraint(orderId);
ForeignKeyConstraint foreignKeyConstraint = new ForeignKeyConstraint("ForeignKeyConstraint");
foreignKeyConstraint.AddColumns(personId, personLastName)
.References(primaryTable)
.AddReferenceColumns(id, lastName);
foreignTable.Constraints.AddAll(foreignTableprimaryKeyConstraint, foreignKeyConstraint);
VerifyForeignKey(primaryTable, foreignTable);
}
private void ProcessConstraints(EntityData entity, IKey key)
{
IConstraint constraint = null;
if (key is ForeignKey foreignKey)
{
var referencedTableName = dbTableToPropertyName_[foreignKey.Relationship.RelatedEntity];
constraint = new ForeignKeyConstraint(key, entity.PropertyName, referencedTableName);
}
else if (key is PrimaryKey primaryKey)
{
constraint = new PrimaryKeyConstraint(key, entity.PropertyName);
}
var query = constraintTranslator_.Translate(constraint);
var executionResult = database_.ExecuteCommand(database_.CreateCommand(b => b.WithCommandText(query).WithConnectionString(options_.ConnectionString)));
}
public void CreateTableWithMultiColumnPrimaryKeyWithColumnSort()
{
const string ColumnName1 = "Id";
const string ColumnName2 = "Id2";
const string TableName = nameof(CreateTableWithMultiColumnPrimaryKeyWithColumnSort);
Table table = new Table(TableName);
Column column = new Column(ColumnName1, DataType.Int());
Column column2 = new Column(ColumnName2, DataType.Int());
table.Columns.AddAll(column, column2);
PrimaryKeyConstraint primaryKeyConstraint = new PrimaryKeyConstraint("PrimaryKeyConstraint", column, ColumnSort.DESC);
primaryKeyConstraint.AddColumns(column2);
table.Constraints.Add(primaryKeyConstraint);
VerifyPrimaryKey(TableName, table);
}
/// <summary>
/// Enumerates the SQL commands that are necessary to drop
/// the specified primary key constraint (<paramref name="pk"/>).
/// </summary>
/// <param name="pk">The primary key constraint.</param>
/// <returns>The SQL commands.</returns>
protected virtual IEnumerable <string> DropPrimaryKeyConstraint(PrimaryKeyConstraint pk)
{
IEnumerable <string> sql = null;
if (!string.IsNullOrEmpty(pk.Name))
{
sql = DropConstraint(pk);
}
else
{
sql = new string[] {
string.Format("ALTER TABLE {0} ALTER COLUMN {1} DROP PRIMARY KEY;",
EscapeTableName(pk.Table.Name),
EscapeConstraintName(pk.Name)
)
};
}
return(sql);
}
public string GetSQLConstraintRemove(PrimaryKeyConstraint constraint)
{
return string.Format("ALTER TABLE [{0}] DROP CONSTRAINT [{1}]", constraint.Table.Name, constraint.Name);
}
public string GetSQLConstraintAdd(PrimaryKeyConstraint constraint)
{
return string.Format("ALTER TABLE [{0}] ADD CONSTRAINT [{1}] PRIMARY KEY ([{2}])", constraint.Table.Name, constraint.Name, string.Join("],[", constraint.ColumnNames.ToArray()));
}
/// <summary>
/// Enumerates the SQL commands that are necessary to drop
/// the specified primary key constraint (<paramref name="pk"/>).
/// </summary>
/// <param name="pk">The primary key constraint.</param>
/// <returns>The SQL commands.</returns>
protected override IEnumerable<string> DropPrimaryKeyConstraint(PrimaryKeyConstraint pk)
{
if (string.IsNullOrEmpty(pk.Name))
{
return new string[] {
string.Format("ALTER TABLE {0} ALTER COLUMN {1} DROP IDENTITY;",
EscapeTableName(pk.Table.Name),
EscapeConstraintName(pk.Name)
)
};
}
else
{
return base.DropPrimaryKeyConstraint(pk);
}
}
/// <summary>
/// Metadata okuma iþini yapan asýl metod. Sýrayla bütün veritabaný nesnelerini okur.
/// </summary>
public void ReadDatabaseMetadata(bool readAllMetadata)
{
#region tables and views
db.Tables = new TableCollection(db);
bool connectionAlreadyOpen = connection.State == ConnectionState.Open;
// columns
if(!connectionAlreadyOpen) connection.Open();
DataTable dtTables = ((DbConnection)connection).GetSchema("Tables");
if (!connectionAlreadyOpen) connection.Close();
foreach (DataRow drTable in dtTables.Rows)
{
Table tbl = new Table();
tbl.Name = drTable["TABLE_NAME"].ToString();
tbl.IsView = drTable["TABLE_TYPE"].ToString() == "VIEW";
db.Tables.Add(tbl);
}
if (!connectionAlreadyOpen) connection.Open();
DataTable dtColumns = ((DbConnection)connection).GetSchema("Columns");
if (!connectionAlreadyOpen) connection.Close();
foreach (DataRow drColumn in dtColumns.Rows)
{
Table tbl = db.Tables[drColumn["TABLE_NAME"].ToString()];
if (tbl==null)
continue;
Column f = new Column();
f.DefaultValue = drColumn["COLUMN_DEFAULT"].ToString();
if (f.DefaultValue == "\0") f.DefaultValue = "";
f.ColumnTypeOriginal = drColumn["DATA_TYPE"].ToString().ToUpperInvariant();
f.ColumnType = StringToDbType(f.ColumnTypeOriginal);
f.Length = drColumn.IsNull("CHARACTER_MAXIMUM_LENGTH") ? 0 : Convert.ToInt64(drColumn["CHARACTER_MAXIMUM_LENGTH"]);
f.IsNullable = drColumn["IS_NULLABLE"].ToString() != "NO";
f.Name = drColumn["COLUMN_NAME"].ToString();
f.IsAutoIncrement = drColumn["AUTOINCREMENT"].ToString() == "True";
tbl.Columns.Add(f);
}
#endregion
//if (!readAllMetadata) return; //***
#region indices
if (!connectionAlreadyOpen) connection.Open();
DataTable dtIndexes = ((DbConnection)connection).GetSchema("Indexes");
if (!connectionAlreadyOpen) connection.Close();
foreach (DataRow drIndex in dtIndexes.Rows)
{
bool primary = (bool)drIndex["PRIMARY_KEY"];
bool unique = (bool)drIndex["UNIQUE"];
if(primary && unique)
{
PrimaryKeyConstraint pk = new PrimaryKeyConstraint();
pk.Name = drIndex["INDEX_NAME"].ToString();
db.Tables[drIndex["TABLE_NAME"].ToString()].Constraints.Add(pk);
}
else if (unique)
{
UniqueConstraint uc = new UniqueConstraint();
uc.Name = drIndex["INDEX_NAME"].ToString();
db.Tables[drIndex["TABLE_NAME"].ToString()].Constraints.Add(uc);
}
else
{
Index index = new Index();
index.Name = drIndex["INDEX_NAME"].ToString();
db.Tables[drIndex["TABLE_NAME"].ToString()].Indices.Add(index);
}
}
if (!connectionAlreadyOpen) connection.Open();
DataTable dtIndexColumns = ((DbConnection)connection).GetSchema("IndexColumns");
if (!connectionAlreadyOpen) connection.Close();
foreach (DataRow drCol in dtIndexColumns.Rows)
{
BaseIndexConstraint index = db.GetConstraint(drCol["CONSTRAINT_NAME"].ToString());
if(index==null)
index = db.Tables[drCol["TABLE_NAME"].ToString()].Indices[drCol["CONSTRAINT_NAME"].ToString()];
if (index == null) continue; //***
index.ColumnNames.Add(drCol["COLUMN_NAME"].ToString());
}
#endregion
}
public string GetSQLConstraintRemove(PrimaryKeyConstraint constraint)
{
return string.Format("ALTER TABLE `{0}` DROP PRIMARY KEY", constraint.Table.Name);
}
public void AddTable <T>(string tag = null) where T : class
{
if (string.IsNullOrEmpty(tag))
{
tag = default;
}
var table = new Table(typeof(T).Name)
{
Tag = tag
};
var tableMapping = new TableMapping(typeof(T), table);
_tableMappings.Add(typeof(T), tableMapping);
Schema.Add(table);
foreach (var commentAttribute in typeof(T).GetCustomAttributes <CommentAttribute>(true))
{
table.Comment += commentAttribute.Comment + "\n";
}
// TODO: GetMembers, not only the properties
var propertyInfos = typeof(T)
.GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.FlattenHierarchy)
.ToList();
var primaryKey = new PrimaryKeyConstraint(table, "pk_" + table.SqlName, Enumerable.Empty <TableColumn>())
{
Tag = tag,
IsInline = true
};
foreach (var prop in propertyInfos)
{
int?size = prop.GetCustomAttribute <MaxLengthAttribute>()?.Length;
var typeMapping = GetOrAddTypeMapping(prop.PropertyType, size);
bool isForeignKey = typeMapping.SqlType.IsReference;
var column = new TableColumn(table, (isForeignKey ? "id_" : "") + prop.Name, typeMapping.SqlType);
table.Columns.Add(column);
tableMapping.Columns.Add(prop.Name, column);
bool isNullableType = (!prop.PropertyType.IsValueType) || (Nullable.GetUnderlyingType(prop.PropertyType) != null);
if (!isNullableType)
{
table.Constraints.Add(new NotNullConstraint(table, $"nn_{table.SqlName}_{column.SqlName}", column)
{
Tag = tag
});
}
var primaryKeyAttribute = prop.GetCustomAttribute <PrimaryKeyAttribute>(true);
if (primaryKeyAttribute != null)
{
primaryKey.Columns.Add(column);
if (primaryKeyAttribute.DatabaseGenerated)
{
tableMapping.DatabaseGenerated = true;
var sequence = new Sequence($"seq_{table.SqlName}")
{
Tag = tag
};
tableMapping.PrimaryKeySequence = sequence;
Schema.Add(sequence);
var trigger = new Trigger($"trigger_{table.SqlName}")
{
Tag = tag,
SqlCode = $" BEFORE INSERT ON {table.SqlName}\n" +
$" FOR EACH ROW\n" +
$"BEGIN\n" +
$" IF :NEW.{column.SqlName} IS NULL THEN\n" +
$" SELECT {sequence.SqlName}.NEXTVAL INTO :NEW.{column.SqlName} FROM SYS.DUAL;\n" +
$" END IF;\n" +
$"END;"
};
Schema.Add(trigger);
}
}
// TODO: Check, Unique, Default Value
if (prop.GetCustomAttribute <UniqueAttribute>(true) != null)
{
table.Constraints.Add(new UniqueConstraint(table, $"uq_{table.SqlName}_{column.SqlName}", column)
{
Tag = tag
});
}
column.Comment += prop.GetCustomAttributes <CommentAttribute>(true)
.Select(prop => prop.Comment)
.ToDelimitedString("\n");
if (typeMapping is EnumTypeMapping enumTypeMapping)
{
var checkInValues = enumTypeMapping.EnumSqlNames
.Select(name => $"'{name}'")
.ToDelimitedString(", ");
table.Constraints.Add(new CheckConstraint(table, $"ck_{table.SqlName}_{column.SqlName}", $"{column.SqlName} IN ({checkInValues})")
{
Tag = tag
});
}
if (isForeignKey)
{
Schema.OnInitialise += () =>
{
// Initialize the foreign key constraints
var referencedColumn = _tableMappings[prop.PropertyType].Table
.Constraints
.OfType <PrimaryKeyConstraint>()
.Single()
.Columns
.Single();
table.Constraints.Add(new ForeignKeyConstraint(table, $"fk_{table.SqlName}_{column.SqlName}", column, referencedColumn)
{
Tag = tag
});
};
}
}
if (primaryKey.Columns.Count > 0)
{
table.Constraints.Add(primaryKey);
}
}
private void cmdAlterTable(string arg)
{
FormCreateTable fct = new FormCreateTable();
Table tbl = SelectedObject as Table;
if (tbl == null) {
MessageBox.Show("Select table first", "Cinar Database Tools");
return;
}
fct.SetTable(tbl);
while (true)
{
if (fct.ShowDialog() == DialogResult.OK)
{
TableDef tblNew = fct.GetAlteredTable();
try
{
StringBuilder sb = new StringBuilder();
foreach (ColumnDef colNew in tblNew.Columns)
{
// field yeni eklenmişse
if (colNew.OriginalColumn == null)
{
Column f = new Column()
{
Name = colNew.Name,
ColumnTypeOriginal = colNew.ColumnType,
ColumnType = Provider.Database.StringToDbType(colNew.ColumnType),
Length = colNew.Length,
DefaultValue = colNew.DefaultValue,
IsNullable = colNew.IsNullable,
IsAutoIncrement = colNew.IsAutoIncrement
};
tbl.Columns.Add(f);
sb.AppendLine(Provider.Database.GetSQLColumnAdd(tbl.Name, f) + ";");
if (colNew.IsPrimaryKey)
{
PrimaryKeyConstraint k = new PrimaryKeyConstraint();
tbl.Constraints.Add(k);
k.ColumnNames.Add(f.Name);
k.Name = "PK_" + tbl.Name;
sb.AppendLine(Provider.Database.GetSQLConstraintAdd(k) + ";");
}
continue;
}
// field'ın adı değiştirilmişse
if (colNew.OriginalColumn.Name != colNew.Name)
{
string oldName = colNew.OriginalColumn.Name;
colNew.OriginalColumn.Name = colNew.Name;
sb.AppendLine(Provider.Database.GetSQLColumnRename(oldName, colNew.OriginalColumn) + ";");
}
// default value'su değiştirilmişse
if (colNew.OriginalColumn.DefaultValue != colNew.DefaultValue)
{
colNew.OriginalColumn.DefaultValue = colNew.DefaultValue;
sb.AppendLine(Provider.Database.GetSQLColumnChangeDefault(colNew.OriginalColumn) + ";");
}
// field type, length veya nullable değiştirilmişse
if (colNew.OriginalColumn.ColumnTypeOriginal != colNew.ColumnType || colNew.OriginalColumn.Length != colNew.Length || colNew.OriginalColumn.IsNullable != colNew.IsNullable)
{
var dependentConstraints = colNew.OriginalColumn.Table.Constraints.Where(cons => cons.ColumnNames.Contains(colNew.OriginalColumn.Name)).ToList();
foreach(var cons in dependentConstraints)
sb.AppendLine(Provider.Database.GetSQLConstraintRemove(cons) + ";");
colNew.OriginalColumn.ColumnTypeOriginal = colNew.ColumnType;
colNew.OriginalColumn.ColumnType = Provider.Database.StringToDbType(colNew.ColumnType);
colNew.OriginalColumn.Length = colNew.Length;
colNew.OriginalColumn.IsNullable = colNew.IsNullable;
sb.AppendLine(Provider.Database.GetSQLColumnChangeDataType(colNew.OriginalColumn) + ";");
foreach (var cons in dependentConstraints)
sb.AppendLine(Provider.Database.GetSQLConstraintAdd(cons) + ";");
}
// primary key kaldırılmışsa
if (colNew.OriginalColumn.IsPrimaryKey == true && colNew.IsPrimaryKey == false)
{
var k = colNew.OriginalColumn.Table.Constraints.FirstOrDefault(cons => cons is PrimaryKeyConstraint);
if (k != null)
{
colNew.OriginalColumn.Table.Constraints.Remove(k);
sb.AppendLine(Provider.Database.GetSQLConstraintRemove(k) + ";");
}
}
// primary key yapılmışsa
if (colNew.OriginalColumn.IsPrimaryKey == false && colNew.IsPrimaryKey == true)
{
PrimaryKeyConstraint k = new PrimaryKeyConstraint();
tbl.Constraints.Add(k);
k.ColumnNames.Add(colNew.Name);
k.Name = "PK_" + tbl.Name;
sb.AppendLine(Provider.Database.GetSQLConstraintAdd(k) + ";");
}
}
List<Column> deletedColumns = new List<Column>();
foreach (Column c in tbl.Columns)
if (!tblNew.Columns.Any(nc => nc.Name == c.Name))
deletedColumns.Add(c);
foreach (Column c in deletedColumns)
sb.AppendLine(Provider.Database.GetSQLColumnRemove(c) + ";");
if (tbl.Name != tblNew.Name)
{
sb.AppendLine(Provider.Database.GetSQLTableRename(tbl.Name, tblNew.Name) + ";");
tbl.Name = tblNew.Name;
}
string sql = sb.ToString();
SQLInputDialog sid = new SQLInputDialog(sql, false);
if (sid.ShowDialog() == DialogResult.OK)
{
Provider.Database.ExecuteNonQuery(sid.SQL);
try
{
findNode(tbl).Remove();
populateTreeNodesFor(null, tbl);
if (ObjectChanged != null)
ObjectChanged(this, new DbObjectChangedArgs { Object = tbl });
}
catch { }
}
else
{
tblNew.UndoChanges();
}
break;
}
catch (Exception ex)
{
tblNew.UndoChanges();
MessageBox.Show(ex.Message, "Cinar Database Tools");
fct.DialogResult = DialogResult.None;
}
}
else
break;
}
}
public Table GetCreatedTable()
{
Table tbl = new Table();
tbl.Name = txtTableName.Text.MakeFileName();
if (!(columnCollectionBindingSource.DataSource is Type))
foreach (ColumnDef fd in columnCollectionBindingSource.DataSource as List<ColumnDef>)
{
Column f = new Column()
{
Name = fd.Name,
ColumnTypeOriginal = fd.ColumnType,
ColumnType = Provider.Database.StringToDbType(fd.ColumnType),
Length = fd.Length,
DefaultValue = fd.DefaultValue,
IsNullable = fd.IsNullable,
IsAutoIncrement = fd.IsAutoIncrement
};
tbl.Columns.Add(f);
if (fd.IsPrimaryKey)
{
PrimaryKeyConstraint k = new PrimaryKeyConstraint();
tbl.Constraints.Add(k);
k.ColumnNames.Add(f.Name);
k.Name = "PK_" + tbl.Name;
}
}
return tbl;
}
public override string Fix(Panel p)
{
ComboBox cb = p.Controls[1] as ComboBox;
if (cb.SelectedItem is Column)
{
Column f = (Column)cb.SelectedItem;
var pk = new PrimaryKeyConstraint() { ColumnNames = new List<string>() { f.Name }, Name = "PK_" + Table.Name};
f.Table.Constraints.Add(pk);
var res = "";
res += Provider.Database.GetSQLConstraintAdd(pk);
f.Table.Constraints.Remove(pk);
return res;
}
return "";
}
/// <summary>
/// Builds the SQL fragment that describes a primary key constraint
/// within a CREATE TABLE statement.
/// </summary>
/// <param name="pk">The primary key constraint.</param>
/// <returns>The SQL fragment.</returns>
protected virtual string BuildCreateTablePrimaryKeyConstraint(PrimaryKeyConstraint pk)
{
if (pk.Name != null)
{
return string.Format("CONSTRAINT {0} PRIMARY KEY ({1})",
EscapeConstraintName(pk.Name),
string.Join(", ", EscapeColumnNames(pk.ColumnNames))
);
}
else
{
return string.Format("PRIMARY KEY ({0})",
string.Join(", ", EscapeColumnNames(pk.ColumnNames))
);
}
}
/// <summary>
/// Metadata okuma iþini yapan asýl metod. Sýrayla bütün veritabaný nesnelerini okur.
/// </summary>
public void ReadDatabaseMetadata(bool readAllMetadata)
{
#region tables and views
db.Tables = new TableCollection(db);
// columns
DataTable dtTables = db.GetDataTable(GetSQLTableList());
foreach (DataRow drTable in dtTables.Rows)
{
Table tbl = new Table();
tbl.Name = drTable["TABLE_NAME"].ToString();
tbl.IsView = drTable["TABLE_TYPE"].ToString() == "VIEW";
db.Tables.Add(tbl);
DataTable dtColumns = db.GetDataTable(GetSQLColumnList(tbl.Name));
foreach (DataRow drColumn in dtColumns.Rows)
{
Column f = new Column();
f.DefaultValue = drColumn["COLUMN_DEFAULT"].ToString();
f.ColumnTypeOriginal = drColumn["DATA_TYPE"].ToString().ToUpperInvariant();
f.ColumnType = StringToDbType(f.ColumnTypeOriginal);
f.Length = drColumn.IsNull("CHARACTER_MAXiMUM_LENGTH") ? 0 : Convert.ToInt64(drColumn["CHARACTER_MAXiMUM_LENGTH"]);
f.IsNullable = drColumn["iS_NULLABLE"].ToString() != "NO";
f.Name = drColumn["COLUMN_NAME"].ToString();
f.IsAutoIncrement = !string.IsNullOrEmpty(drColumn["iS_AUTO_iNCREMENT"].ToString());
tbl.Columns.Add(f);
}
}
#endregion
#region constraints
// Con.Name, Con.TableName, Con.Type, Col.ColumnName, Con.RefConstraintName, Con.UpdateRule, Con.DeleteRule
DataTable dtCons = db.GetDataTable(this.GetSQLConstraintList());
foreach (DataRow drCon in dtCons.Rows)
{
Constraint con = db.Tables[drCon["TableName"].ToString()].Constraints[drCon["Name"].ToString()];
if (con != null)
{
con.ColumnNames.Add(drCon["ColumnName"].ToString());
continue;
}
switch (drCon["Type"].ToString())
{
case "FOREIGN KEY":
con = new ForeignKeyConstraint();
(con as ForeignKeyConstraint).RefConstraintName = drCon["RefConstraintName"].ToString();
//(con as ForeignKeyConstraint).RefTableName = drCon["RefConstraintName"].ToString();
(con as ForeignKeyConstraint).DeleteRule = drCon["DeleteRule"].ToString();
(con as ForeignKeyConstraint).UpdateRule = drCon["UpdateRule"].ToString();
break;
case "PRIMARY KEY":
con = new PrimaryKeyConstraint();
break;
case "UNIQUE":
con = new UniqueConstraint();
break;
default:
throw new Exception("Unknown constraint type: " + drCon["Type"].ToString());
}
con.Name = drCon["Name"].ToString();
con.ColumnNames.Add(drCon["ColumnName"].ToString());
db.Tables[drCon["TableName"].ToString()].Constraints.Add(con);
}
foreach (Table tbl in db.Tables)
foreach (ForeignKeyConstraint fk in tbl.Constraints.Where(c => c is ForeignKeyConstraint))
fk.RefTableName = db.GetConstraint(fk.RefConstraintName).Table.Name;
#endregion
if (!readAllMetadata) return; //***
#region indices
foreach (Table tbl in db.Tables)
{
DataTable dtKeys = db.GetDataTable(@"select
t.relname as table_name,
i.relname as index_name,
array_to_string(array_agg(a.attname), ', ') as index_keys
from
pg_class t,
pg_class i,
pg_index ix,
pg_attribute a
where
t.oid = ix.indrelid
and i.oid = ix.indexrelid
and a.attrelid = t.oid
and a.attnum = ANY(ix.indkey)
and t.relkind = 'r'
and t.relname like '" + tbl.Name + @"%'
group by
t.relname,
i.relname
order by
t.relname,
i.relname;
");
if (dtKeys != null)
foreach (DataRow drKey in dtKeys.Rows)
{
if (db.GetConstraint(drKey["index_name"].ToString()) != null)
continue;
Index index = new Index();
index.Name = drKey["index_name"].ToString();
index.ColumnNames = new List<string>();
foreach (string columnName in drKey["index_keys"].ToString().Split(','))
index.ColumnNames.Add(columnName.Trim().Replace("(-)", ""));
tbl.Indices.Add(index);
}
}
#endregion
}
public string GetSQLConstraintRemove(PrimaryKeyConstraint constraint)
{
throw new NotImplementedException();
}
/// <summary>
/// Enumerates the SQL commands that are necessary to drop
/// the specified primary key constraint (<paramref name="pk"/>).
/// </summary>
/// <param name="pk">The primary key constraint.</param>
/// <returns>The SQL commands.</returns>
protected virtual IEnumerable<string> DropPrimaryKeyConstraint(PrimaryKeyConstraint pk)
{
IEnumerable<string> sql = null;
if (!string.IsNullOrEmpty(pk.Name))
{
sql = DropConstraint(pk);
}
else
{
sql = new string[] {
string.Format("ALTER TABLE {0} ALTER COLUMN {1} DROP PRIMARY KEY;",
EscapeTableName(pk.Table.Name),
EscapeConstraintName(pk.Name)
)
};
}
return sql;
}
/// <summary>
/// Metadata okuma işini yapan asıl metod. Sırayla bütün veritabanı nesnelerini okur.
/// </summary>
public void ReadDatabaseMetadata(bool readAllMetadata)
{
#region tables and views
db.Tables = new TableCollection(db);
// columns
DataTable dtTables = db.GetDataTable(GetSQLTableList());
foreach (DataRow drTable in dtTables.Rows)
{
//if (drTable["TABLE_TYPE"].ToString() == "VIEW") continue;
Table tbl = new Table();
tbl.Name = drTable["TABLE_NAME"].ToString();
tbl.IsView = drTable["TABLE_TYPE"].ToString() == "VIEW";
db.Tables.Add(tbl);
DataTable dtColumns = db.GetDataTable(GetSQLColumnList(tbl.Name));
foreach (DataRow drColumn in dtColumns.Rows)
{
Column f = new Column();
f.DefaultValue = drColumn["COLUMN_DEFAULT"].ToString();
f.ColumnTypeOriginal = drColumn["DATA_TYPE"].ToString().ToUpperInvariant();
f.ColumnType = StringToDbType(f.ColumnTypeOriginal);
f.Length = drColumn.IsNull("CHARACTER_MAXIMUM_LENGTH") ? 0 : Convert.ToInt32(drColumn["CHARACTER_MAXIMUM_LENGTH"]);
f.IsNullable = drColumn["IS_NULLABLE"].ToString() != "NO";
f.Name = drColumn["COLUMN_NAME"].ToString();
f.IsAutoIncrement = drColumn["IS_AUTO_INCREMENT"].ToString() == "1";
tbl.Columns.Add(f);
}
}
#endregion
#region constraints
// Con.Name, Con.TableName, Con.Type, Col.ColumnName, Con.RefConstraintName, Con.UpdateRule, Con.DeleteRule
DataTable dtCons = db.GetDataTable(this.GetSQLConstraintList());
foreach (DataRow drCon in dtCons.Rows)
{
Constraint con = db.Tables[drCon["TableName"].ToString()].Constraints[drCon["Name"].ToString()];
if (con != null)
{
con.ColumnNames.Add(drCon["ColumnName"].ToString());
continue;
}
switch (drCon["Type"].ToString())
{
case "FOREIGN KEY":
con = new ForeignKeyConstraint();
(con as ForeignKeyConstraint).RefConstraintName = drCon["RefConstraintName"].ToString();
//(con as ForeignKeyConstraint).RefTableName = drCon["RefConstraintName"].ToString();
(con as ForeignKeyConstraint).DeleteRule = drCon["DeleteRule"].ToString();
(con as ForeignKeyConstraint).UpdateRule = drCon["UpdateRule"].ToString();
break;
case "PRIMARY KEY":
con = new PrimaryKeyConstraint();
break;
case "UNIQUE":
con = new UniqueConstraint();
break;
default:
throw new Exception("Unknown constraint type: " + drCon["Type"].ToString());
}
con.Name = drCon["Name"].ToString();
con.ColumnNames.Add(drCon["ColumnName"].ToString());
db.Tables[drCon["TableName"].ToString()].Constraints.Add(con);
}
foreach (Table tbl in db.Tables)
foreach (ForeignKeyConstraint fk in tbl.Constraints.Where(c => c is ForeignKeyConstraint))
fk.RefTableName = db.GetConstraint(fk.RefConstraintName).Table.Name;
#endregion
if (!readAllMetadata) return; //***
#region indices
foreach (Table tbl in db.Tables)
{
DataTable dtKeys = db.GetDataTable("EXEC sp_helpindex [" + tbl.Name + "]");
if (dtKeys != null)
foreach (DataRow drKey in dtKeys.Rows)
{
if (db.GetConstraint(drKey["index_name"].ToString()) != null)
continue;
Index index = new Index();
index.Name = drKey["index_name"].ToString();
index.ColumnNames = new List<string>();
foreach (string columnName in drKey["index_keys"].ToString().Split(','))
index.ColumnNames.Add(columnName.Trim().Replace("(-)", ""));
tbl.Indices.Add(index);
}
}
#endregion
}
public override void ApplyToMetadata(Panel p)
{
ComboBox cb = p.Controls[1] as ComboBox;
if (cb.SelectedItem is Column)
{
Column f = (Column)cb.SelectedItem;
var pk = new PrimaryKeyConstraint() { ColumnNames = new List<string>() { f.Name }, Name = "PK_" + Table.Name };
f.Table.Constraints.Add(pk);
}
}
/// <summary>
/// Enumerates the SQL commands that are necessary to create
/// the specified primary key constraint (<paramref name="pk"/>).
/// </summary>
/// <param name="pk">The primary key constraint.</param>
/// <returns>The SQL commands.</returns>
protected virtual IEnumerable<string> AddPrimaryKeyConstraint(PrimaryKeyConstraint pk)
{
yield return string.Format(
"ALTER TABLE {0} ADD CONSTRAINT {1} PRIMARY KEY ({2});",
EscapeTableName(pk.Table.Name),
EscapeConstraintName(pk.Name),
string.Join(", ", EscapeColumnNames(pk.ColumnNames))
);
}
public string GetSQLConstraintAdd(PrimaryKeyConstraint constraint)
{
return string.Format("ALTER TABLE `{0}` ADD PRIMARY KEY (`{1}`)", constraint.Table.Name, string.Join("`,`", constraint.ColumnNames.ToArray()));
}
public abstract void Visit(PrimaryKeyConstraint constraint);
public string GetSQLConstraintAdd(PrimaryKeyConstraint constraint)
{
return string.Format("ALTER TABLE \"{0}\" ADD CONSTRAINT \"{1}\" PRIMARY KEY (\"{2}\")", constraint.Table.Name, constraint.Name, string.Join("\",\"", constraint.ColumnNames.ToArray()));
}
/// <summary>
/// Metadata okuma iþini yapan asýl metod. Sýrayla bütün veritabaný nesnelerini okur.
/// </summary>
/// <param name="db"></param>
public void ReadDatabaseMetadata(bool readAllMetadata)
{
#region tables and views
db.Tables = new TableCollection(db);
// columns
DataTable dtTables = db.GetDataTable(GetSQLTableList());
foreach (DataRow drTable in dtTables.Rows)
{
Table tbl = new Table();
tbl.Name = drTable["TABLE_NAME"].ToString();
tbl.IsView = drTable["TABLE_TYPE"].ToString() == "VIEW";
db.Tables.Add(tbl);
DataTable dtColumns = db.GetDataTable(GetSQLColumnList(tbl.Name));
foreach (DataRow drColumn in dtColumns.Rows)
{
Column f = new Column();
f.DefaultValue = drColumn["COLUMN_DEFAULT"].ToString();
if (f.DefaultValue == "\0") f.DefaultValue = "";
f.ColumnTypeOriginal = drColumn["COLUMN_TYPE"].ToString()=="tinyint(1)" ? "BOOL" : drColumn["DATA_TYPE"].ToString().ToUpperInvariant();
f.ColumnType = StringToDbType(f.ColumnTypeOriginal);
f.Length = drColumn.IsNull("CHARACTER_MAXIMUM_LENGTH") ? 0 : Convert.ToInt64(drColumn["CHARACTER_MAXIMUM_LENGTH"]);
f.IsNullable = drColumn["IS_NULLABLE"].ToString() != "NO";
f.Name = drColumn["COLUMN_NAME"].ToString();
f.IsAutoIncrement = drColumn["IS_AUTO_INCREMENT"].ToString() == "1";
if (drColumn["COLUMN_KEY"].ToString() == "PRI")
{
var pk = new PrimaryKeyConstraint();
pk.Name = "PRIMARY";
pk.ColumnNames.Add(f.Name);
tbl.Constraints.Add(pk);
}
tbl.Columns.Add(f);
}
}
#endregion
#region constraints
try
{
// Con.Name, Con.TableName, Con.Type, Col.ColumnName, Con.RefConstraintName, Con.UpdateRule, Con.DeleteRule
DataTable dtCons = db.GetDataTable(this.GetSQLConstraintList());
foreach (DataRow drCon in dtCons.Rows)
{
Constraint con = db.Tables[drCon["TableName"].ToString()].Constraints[drCon["Name"].ToString()];
if (con != null)
{
if (!con.ColumnNames.Any(cn => cn.ToLowerInvariant() == drCon["ColumnName"].ToString().ToLowerInvariant()))
con.ColumnNames.Add(drCon["ColumnName"].ToString());
continue;
}
switch (drCon["Type"].ToString())
{
case "FOREIGN KEY":
con = new ForeignKeyConstraint();
(con as ForeignKeyConstraint).RefConstraintName = drCon["RefConstraintName"].ToString();
(con as ForeignKeyConstraint).RefTableName = drCon["RefTableName"].ToString();
(con as ForeignKeyConstraint).DeleteRule = drCon["DeleteRule"].ToString();
(con as ForeignKeyConstraint).UpdateRule = drCon["UpdateRule"].ToString();
break;
case "PRIMARY KEY":
con = new PrimaryKeyConstraint();
break;
case "UNIQUE":
con = new UniqueConstraint();
break;
default:
throw new Exception("Unknown constraint type: " + drCon["Type"].ToString());
}
con.Name = drCon["Name"].ToString();
con.ColumnNames.Add(drCon["ColumnName"].ToString());
db.Tables[drCon["TableName"].ToString()].Constraints.Add(con);
}
}
catch (MySqlException ex) // demek ki MySQL versiyonu < 5.1.16
{
// foreign keys
string sql = @" SELECT
TBL1.CONSTRAINT_NAME,
TBL2.TABLE_NAME as TABLE_NAME_1,
TBL2.COLUMN_NAME as COLUMN_NAME_1,
TBL2.REFERENCED_TABLE_NAME as TABLE_NAME_2,
TBL2.REFERENCED_COLUMN_NAME as COLUMN_NAME_2
FROM
(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA='{0}' AND CONSTRAINT_TYPE='FOREIGN KEY') AS TBL1,
(select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA='{0}') AS TBL2
WHERE
TBL2.CONSTRAINT_NAME = TBL1.CONSTRAINT_NAME";
DataTable dtForeigns = db.GetDataTable(String.Format(sql, connection.Database));
foreach (DataRow drForeign in dtForeigns.Rows)
{
try
{
ForeignKeyConstraint con = new ForeignKeyConstraint();
con.Name = drForeign["CONSTRAINT_NAME"].ToString();
con.RefConstraintName = db.Tables[drForeign["TABLE_NAME_1"].ToString()].Constraints.FirstOrDefault(c => c is PrimaryKeyConstraint).Name;
con.RefTableName = drForeign["TABLE_NAME_2"].ToString();
//con.DeleteRule = drForeign["DeleteRule"].ToString();
//con.UpdateRule = drForeign["UpdateRule"].ToString();
con.ColumnNames.Add(drForeign["COLUMN_NAME_1"].ToString());
db.Tables[drForeign["TABLE_NAME_1"].ToString()].Constraints.Add(con);
}
catch { }
}
}
#endregion
if (!readAllMetadata) return; //***
#region indices
foreach (Table tbl in db.Tables)
{
DataTable dtKeys = db.GetDataTable("SHOW INDEXES FROM `" + tbl.Name + "`");
if (dtKeys != null)
for (int i = 0; i < dtKeys.Rows.Count; i++)
{
DataRow drKey = dtKeys.Rows[i];
if (db.GetConstraint(drKey["Key_name"].ToString()) != null)
continue;
Index index = tbl.Indices[drKey["Key_name"].ToString()] ?? new Index();
index.Name = drKey["Key_name"].ToString();
index.ColumnNames.Add(drKey["Column_name"].ToString());
if (tbl.Indices[index.Name] == null)
tbl.Indices.Add(index);
}
}
#endregion
}
/// <summary>
/// Create a new PrimaryKeyConstraint object.
/// </summary>
/// <param name="id">Initial value of Id.</param>
/// <param name="name">Initial value of Name.</param>
/// <param name="isDeferrable">Initial value of IsDeferrable.</param>
/// <param name="isInitiallyDeferred">Initial value of IsInitiallyDeferred.</param>
public static PrimaryKeyConstraint CreatePrimaryKeyConstraint(string id, string name, bool isDeferrable, bool isInitiallyDeferred)
{
PrimaryKeyConstraint primaryKeyConstraint = new PrimaryKeyConstraint();
primaryKeyConstraint.Id = id;
primaryKeyConstraint.Name = name;
primaryKeyConstraint.IsDeferrable = isDeferrable;
primaryKeyConstraint.IsInitiallyDeferred = isInitiallyDeferred;
return primaryKeyConstraint;
}
public string GetSQLConstraintRemove(PrimaryKeyConstraint constraint)
{
return GetSQLConstraintRemove(constraint);
}
