public void LoadColumns(SqlDataConnection connection, params DBTable[] tables)
{
foreach (DBTable table in tables)
{
if (table.Name == "Categories" && table.Columns.Count == 0)
{
DBColumn categoryIdColumn = new DBColumn {
Name = "CategoryID", ColumnType = DBColumnType.Int32
};
table.AddColumn(categoryIdColumn);
DBColumn categoryNameColumn = new DBColumn {
Name = "CategoryName", ColumnType = DBColumnType.String
};
table.AddColumn(categoryNameColumn);
}
if (table.Name == "Products" && table.Columns.Count == 0)
{
DBColumn categoryIdColumn = new DBColumn {
Name = "CategoryID", ColumnType = DBColumnType.Int32
};
table.AddColumn(categoryIdColumn);
DBColumn productNameColumn = new DBColumn {
Name = "ProductName", ColumnType = DBColumnType.String
};
table.AddColumn(productNameColumn);
DBForeignKey foreignKey = new DBForeignKey(
new[] { categoryIdColumn },
"Categories",
CustomDBSchemaProvider.CreatePrimaryKeys("CategoryID"));
table.ForeignKeys.Add(foreignKey);
}
}
}
static Action <ConnectionProviderSql> CreateForeighKey(XPMemberInfo xpMemberInfo)
{
return(sql => {
var dbForeignKey = new DBForeignKey(new StringCollection {
xpMemberInfo.Name
}, xpMemberInfo.ReferenceType.TableName, new StringCollection {
xpMemberInfo.ReferenceType.KeyProperty.Name
});
sql.CreateForeignKey(xpMemberInfo.Owner.Table, dbForeignKey);
});
}
public void LoadColumns(SqlDataConnection connection, params DBTable[] tables)
{
foreach (DBTable table in tables)
{
if (table.Name == "Categories" && table.Columns.Count == 0)
{
DBColumn categoryIdColumn = new DBColumn {
Name = "CategoryID"
};
table.AddColumn(categoryIdColumn);
DBColumn categoryNameColumn = new DBColumn {
Name = "CategoryName"
};
table.AddColumn(categoryNameColumn);
}
if (table.Name == "Products" && table.Columns.Count == 0)
{
DBColumn categoryIdColumn = new DBColumn {
Name = "CategoryID"
};
table.AddColumn(categoryIdColumn);
DBColumn supplierIdColumn = new DBColumn {
Name = "SupplierID"
};
table.AddColumn(supplierIdColumn);
DBColumn productNameColumn = new DBColumn {
Name = "ProductName"
};
table.AddColumn(productNameColumn);
DBForeignKey foreignKey1 = new DBForeignKey(
new[] { categoryIdColumn },
"Categories",
CustomDBSchemaProvider.CreatePrimaryKeys("CategoryID"));
table.ForeignKeys.Add(foreignKey1);
DBForeignKey foreignKey2 = new DBForeignKey(
new[] { supplierIdColumn },
"Suppliers",
CustomDBSchemaProvider.CreatePrimaryKeys("SupplierID"));
table.ForeignKeys.Add(foreignKey2);
}
if (table.Name == "Suppliers" && table.Columns.Count == 0)
{
DBColumn supplierIdColumn = new DBColumn {
Name = "SupplierID"
};
table.AddColumn(supplierIdColumn);
DBColumn companyNameColumn = new DBColumn {
Name = "CompanyName"
};
table.AddColumn(companyNameColumn);
}
}
}
TemplateType GetTemplateType(TemplateType refTemplateType, ClassGeneratorInfo classGeneratorInfo)
{
bool selfReference = classGeneratorInfo.DbTable.Name == _dbTable.Name;
if (!selfReference)
{
DBForeignKey oneToOne = classGeneratorInfo.DbTable.ForeignKeys.FirstOrDefault(key => key.PrimaryKeyTable == _dbTable.Name);
if (oneToOne != null)
{
return(TemplateType.XPOneToOnePropertyMember);
}
}
return(refTemplateType);
}
public TableEditorReferenceEventArgs(DBForeignKey relation)
{
this.relation = relation;
}
/// <summary>
/// Создание ссылки на другую таблицу
/// </summary>
/// <param name="table">Таблица со ссылкой</param>
/// <param name="fk">Ссылка на другую таблицу</param>
public override void CreateForeignKey(DBTable table, DBForeignKey fk)
{
// Если ссылка на таблицу с настраиваемой модификацией, то пропускаем
DBTableEx foreignTableEx = XPDictionaryInformer.Schema.GetTable(fk.PrimaryKeyTable);
if (foreignTableEx.IsCustom) return;
// Если ссылка на таблицу из другой схемы, то необходимо дать разрешение
string tableSchema = FormatRealSchemaName(table.Name);
string foreignSchema = FormatRealSchemaName(fk.PrimaryKeyTable);
if (tableSchema != foreignSchema)
{
string foreignTable = FormatTable(foreignSchema, ComposeSafeTableName(fk.PrimaryKeyTable));
ExecuteSqlSchemaUpdate("ForeignKey", GetForeignKeyName(fk, table), table.Name,
String.Format(CultureInfo.InvariantCulture, "grant references on {0} to {1}", foreignTable, tableSchema));
}
base.CreateForeignKey(table, fk);
}
private bool IsForeignKeyExists(DBTable table, DBForeignKey foreignKey)
{
foreach (DBForeignKey fk in table.ForeignKeys)
{
if (string.Compare(ComposeSafeTableName(foreignKey.PrimaryKeyTable), ComposeSafeTableName(fk.PrimaryKeyTable), true) == 0 &&
string.Compare(ComposeSafeSchemaName(foreignKey.PrimaryKeyTable), ComposeSafeSchemaName(fk.PrimaryKeyTable), true) == 0 &&
IsColumnsEqual(fk.Columns, foreignKey.Columns) &&
IsColumnsEqual(fk.PrimaryKeyTableKeyColumns, foreignKey.PrimaryKeyTableKeyColumns))
return true;
}
return false;
}
private void GetForeignKeys(DBTable table)
{
string schema = ComposeSafeSchemaName(table.Name);
string safeTableName = ComposeSafeTableName(table.Name);
Query query;
if (schema == string.Empty)
query = new Query(
"select tc.position, tc.column_name, fc.column_name, fc.table_name from user_constraints c " +
"inner join user_cons_columns tc on tc.constraint_name = c.constraint_name and tc.table_name = c.table_name " +
"inner join user_cons_columns fc on c.r_constraint_name = fc.constraint_name and tc.position = fc.position " +
"where c.table_name = :p0 order by c.constraint_name, tc.position",
new QueryParameterCollection(new OperandValue(safeTableName)), new string[] { ":p0" });
else
query = new Query(
"select tc.position, tc.column_name, fc.column_name, fc.owner||'.'||fc.table_name from all_constraints c " +
"inner join all_cons_columns tc on tc.constraint_name = c.constraint_name and tc.owner = c.owner and tc.table_name = c.table_name " +
"inner join all_cons_columns fc on c.r_constraint_name = fc.constraint_name and tc.position = fc.position and fc.owner = c.r_owner " +
"where c.owner = :p0 and c.table_name = :p1 order by c.constraint_name, tc.position",
new QueryParameterCollection(new OperandValue(schema), new OperandValue(safeTableName)), new string[] { ":p0", ":p1" });
SelectStatementResult data = SelectData(query);
DBForeignKey fk = null;
foreach (SelectStatementResultRow row in data.Rows)
{
if (Convert.ToDecimal(row.Values[0]) == decimal.One)
{
StringCollection pkc = new StringCollection();
StringCollection fkc = new StringCollection();
pkc.Add((string)row.Values[2]);
fkc.Add((string)row.Values[1]);
fk = new DBForeignKey(fkc, (string)row.Values[3], pkc);
table.ForeignKeys.Add(fk);
}
else
{
fk.Columns.Add((string)row.Values[1]);
fk.PrimaryKeyTableKeyColumns.Add((string)row.Values[2]);
}
}
}
/// <inheritdoc/>
protected override string GetForeignKeyName(DBForeignKey cons, DBTable table)
{
if (cons.Name != null) return cons.Name;
StringBuilder sb = new StringBuilder();
sb.Append("fk_");
sb.Append(ComposeSafeTableName(table.Name));
foreach (string col in cons.Columns) sb.Append(col);
return sb.ToString();
}
public PostOfficeClient()
{
this.DataStore = new InMemoryDataStore(AutoCreateOption.DatabaseAndSchema, false);
this.Mappings = new Dictionary <Type, DataStoreMapping>();
var mAccount = new DataStoreMapping();
mAccount.Table = new DBTable("Accounts");
mAccount.Table.AddColumn(new DBColumn("UserName", true, null, 255, DBColumnType.String));
mAccount.Table.AddColumn(new DBColumn("PublicName", false, null, 1024, DBColumnType.String));
mAccount.Create = () => new Account();
mAccount.Load = (obj, values, omap) => {
((Account)obj).SetKey((string)values[0]);
((Account)obj).PublicName = (string)values[1];
};
mAccount.Save = (obj, values) => {
values[0] = ((Account)obj).UserName;
values[1] = ((Account)obj).PublicName;
};
mAccount.GetKey = (obj) => ((Account)obj).UserName;
mAccount.RefColumns = Enumerable.Empty <DataStoreMapping.Column>();
Mappings.Add(typeof(Account), mAccount);
var mMessage = new DataStoreMapping();
mMessage.Table = new DBTable("Messages");
var mMessageKey = new DBColumn("ID", true, null, 0, DBColumnType.Int32);
mMessageKey.IsIdentity = true;
mMessage.Table.AddColumn(mMessageKey);
mMessage.Table.AddColumn(new DBColumn("Subject", false, null, 1024, DBColumnType.String));
mMessage.Table.AddColumn(new DBColumn("Body", false, null, -1, DBColumnType.String));
mMessage.Table.AddColumn(new DBColumn("Sender", false, null, 255, DBColumnType.String));
mMessage.Table.AddColumn(new DBColumn("Recepient", false, null, 255, DBColumnType.String));
mMessage.Table.PrimaryKey = new DBPrimaryKey(new object[] { mMessageKey });
var fkSender = new DBForeignKey();
fkSender.Columns.Add("Sender");
fkSender.PrimaryKeyTable = "Accounts";
fkSender.PrimaryKeyTableKeyColumns.Add("UserName");
mMessage.Table.AddForeignKey(fkSender);
var fkRecepient = new DBForeignKey();
fkRecepient.Columns.Add("Recepient");
fkRecepient.PrimaryKeyTable = "Accounts";
fkRecepient.PrimaryKeyTableKeyColumns.Add("UserName");
mMessage.Table.AddForeignKey(fkRecepient);
mMessage.Create = () => new Message();
mMessage.SetKey = (obj, key) => {
((Message)obj).SetKey((int)key);
};
mMessage.GetKey = (obj) => ((Message)obj).ID;
mMessage.Load = (obj, values, omap) => {
var o = (Message)obj;
o.SetKey((int)values[0]);
o.Subject = (string)values[1];
o.Body = (string)values[2];
o.Sender = GetReference <Account>(omap, values[3]);
o.Recepient = GetReference <Account>(omap, values[4]);
};
mMessage.Save = (obj, values) => {
var o = (Message)obj;
values[0] = o.ID;
values[1] = o.Subject;
values[2] = o.Body;
values[3] = o.Sender?.UserName;
values[4] = o.Recepient?.UserName;
};
mMessage.RefColumns = new DataStoreMapping.Column[] {
new DataStoreMapping.Column()
{
Index = 3, Type = typeof(Account)
},
new DataStoreMapping.Column()
{
Index = 4, Type = typeof(Account)
}
};
Mappings.Add(typeof(Message), mMessage);
DataStore.UpdateSchema(false, mAccount.Table, mMessage.Table);
CreateDemoData((InMemoryDataStore)DataStore);
}
static Action<ConnectionProviderSql> CreateForeighKey(XPMemberInfo xpMemberInfo) {
return sql => {
var dbForeignKey = new DBForeignKey(new StringCollection { xpMemberInfo.Name }, xpMemberInfo.ReferenceType.TableName, new StringCollection { xpMemberInfo.ReferenceType.KeyProperty.Name });
sql.CreateForeignKey(xpMemberInfo.Owner.Table, dbForeignKey);
};
}
public ActionResult AddForeignKey(string appName, DBForeignKey model)
{
DBApp app = new DBApp()
{
Name = appName,
ConnectionString = (new Entities()).Database.Connection.ConnectionString
};
DBTable sTable = app.GetTable(model.sourceTable.tableName);
foreach (DBForeignKey foreignKey in sTable.foreignKeys) //constraint name control in table
{
if (foreignKey.name == "FK_" + appName + "_" + model.sourceTable.tableName + "_" + model.name)
{
TempData["message-error"] = "Foreign key with name " + foreignKey.name + " is already exist.";
return RedirectToAction("Index", new { @appName = appName });
}
}
DBTable tTable = app.GetTable(model.targetTable.tableName);
DBColumn sColumn = sTable.columns.SingleOrDefault(x => x.Name == model.sourceColumn);
DBColumn tColumn = tTable.columns.SingleOrDefault(x => x.Name == model.targetColumn);
if (sColumn.type != tColumn.type) //columns must have equal data types
{
TempData["message-error"] = "Keys have different data types.";
return RedirectToAction("CreateForeignKey", new { @appName = appName, @tableName = sTable.tableName });
}
sTable.foreignKeys.AddToDB(model);
app.SaveChanges();
TempData["message-success"] = "Foreign key " + model.name + " of table " + sTable.tableName + " was successfully created.";
return RedirectToAction("Index", new { @appName = appName });
}