internal static string GetForeignKeyActionString(ForeignKeyAction action)
{
string result = string.Empty;
switch (action)
{
case ForeignKeyAction.Cascade:
result = "CASCADE";
break;
case ForeignKeyAction.NoAction:
result = "NO ACTION";
break;
case ForeignKeyAction.Restrict:
result = "RESTRICT";
break;
case ForeignKeyAction.SetDefault:
result = "SET DEFAULT";
break;
case ForeignKeyAction.SetNull:
result = "SET NULL";
break;
}
return result;
}
public static void AddForeignKey(this ITransaction transaction, ObjectName table, string[] columns,
ObjectName refTable, string[] refColumns,
ForeignKeyAction deleteRule, ForeignKeyAction updateRule, String constraintName)
{
AddForeignKey(transaction, table, columns, refTable, refColumns, deleteRule, updateRule,
ConstraintDeferrability.InitiallyImmediate, constraintName);
}
public void Create()
{
// SYSTEM.ROUTINE
var tableInfo = new TableInfo(SystemSchema.RoutineTableName);
tableInfo.AddColumn("schema", PrimitiveTypes.String());
tableInfo.AddColumn("name", PrimitiveTypes.String());
tableInfo.AddColumn("type", PrimitiveTypes.String());
tableInfo.AddColumn("location", PrimitiveTypes.String());
tableInfo.AddColumn("return_type", PrimitiveTypes.String());
tableInfo.AddColumn("username", PrimitiveTypes.String());
transaction.CreateTable(tableInfo);
// SYSTEM.ROUTINE_PARAM
tableInfo = new TableInfo(SystemSchema.RoutineParameterTableName);
tableInfo.AddColumn("schema", PrimitiveTypes.String());
tableInfo.AddColumn("name", PrimitiveTypes.String());
tableInfo.AddColumn("arg_name", PrimitiveTypes.String());
tableInfo.AddColumn("arg_type", PrimitiveTypes.String());
tableInfo.AddColumn("in_out", PrimitiveTypes.String());
tableInfo.AddColumn("offset", PrimitiveTypes.Integer());
transaction.CreateTable(tableInfo);
var fkCol = new[] { "routine_schema", "routine_name" };
var refCol = new[] { "schema", "name" };
const ForeignKeyAction onUpdate = ForeignKeyAction.NoAction;
const ForeignKeyAction onDelete = ForeignKeyAction.Cascade;
transaction.AddForeignKey(SystemSchema.RoutineParameterTableName, fkCol, SystemSchema.RoutineTableName, refCol,
onDelete, onUpdate, "ROUTINE_PARAMS_FK");
}
public void AddForeignKey(ObjectName table, string[] columns,
ObjectName refTable, string[] refColumns,
ForeignKeyAction deleteRule, ForeignKeyAction updateRule, string constraintName)
{
AddForeignKey(table, columns, refTable, refColumns, deleteRule, updateRule,
ConstraintDeferrability.InitiallyImmediate, constraintName);
}
internal static string GetForeignKeyActionString(ForeignKeyAction action)
{
string result = string.Empty;
switch (action)
{
case ForeignKeyAction.Cascade:
result = "CASCADE";
break;
case ForeignKeyAction.NoAction:
result = "NO ACTION";
break;
case ForeignKeyAction.Restrict:
result = "RESTRICT";
break;
case ForeignKeyAction.SetDefault:
result = "SET DEFAULT";
break;
case ForeignKeyAction.SetNull:
result = "SET NULL";
break;
}
return(result);
}
public void AddForeignKey(ObjectName table, string[] columns,
ObjectName refTable, string[] refColumns,
ForeignKeyAction deleteRule, ForeignKeyAction updateRule, ConstraintDeferrability deferred, string constraintName)
{
Session.Transaction.AddForeignKey(table, columns, refTable, refColumns, deleteRule, updateRule, deferred,
constraintName);
}
public virtual string GetConstraintActionString(ForeignKeyAction action)
{
switch (action)
{
case ForeignKeyAction.None:
return(" NO ACTION ");
case ForeignKeyAction.Cascade:
return(" CASCADE ");
case ForeignKeyAction.Restrict:
return(" RESTRICT ");
case ForeignKeyAction.NoAction:
return(" NO ACTION ");
case ForeignKeyAction.SetDefault:
return(" SET DEFAULT ");
case ForeignKeyAction.SetNull:
return(" SET NULL ");
default:
throw new NotImplementedException();
}
}
// constructors for class attributes...
public ForeignKeyAttribute(string name, Type referencedType, ForeignKeyAction onUpdate, ForeignKeyAction onDelete)
{
ConstraintName = name;
ReferencedType = referencedType;
OnUpdate = onUpdate;
OnDelete = onDelete;
}
// constructors for class attributes...
public ForeignKeyAttribute(string name, Type referencedType, ForeignKeyAction onUpdate, ForeignKeyAction onDelete)
{
ConstraintName = name;
ReferencedType = referencedType;
OnUpdate = onUpdate;
OnDelete = onDelete;
}
public ForeignKeyConstraintSchema (ForeignKeyConstraintSchema constraint)
: base (constraint)
{
referenceTable = constraint.referenceTable;
referenceColumns = new ColumnSchemaCollection (constraint.referenceColumns);
deleteAction = constraint.deleteAction;
updateAction = constraint.updateAction;
}
public ForeignKeyConstraintSchema(ForeignKeyConstraintSchema constraint)
: base(constraint)
{
referenceTable = constraint.referenceTable;
referenceColumns = new ColumnSchemaCollection(constraint.referenceColumns);
deleteAction = constraint.deleteAction;
updateAction = constraint.updateAction;
}
public static string Identifier(this ForeignKeyAction action)
{
string res = action.SqlName();
if (res == null)
{
return(null);
}
return(res.ToLower().Trim().Replace(" ", ""));
}
public ForeignKey(string name, Table parentTable, Table targetTable, IReadOnlyList <Column> fromColumns, IReadOnlyList <Column> toColumns, ForeignKeyAction updateAction, ForeignKeyAction deleteAction)
{
Name = name;
ParentTable = parentTable;
TargetTable = targetTable;
FromColumns = fromColumns;
ToColumns = toColumns;
UpdateAction = updateAction;
DeleteAction = deleteAction;
}
public static void AddForeignKey(this IQueryContext context, ObjectName table, string[] columns, ObjectName refTable,
string[] refColumns, ForeignKeyAction deleteRule, ForeignKeyAction updateRule, ConstraintDeferrability deferred,
String constraintName)
{
// TODO: throw a specialized exception
if (!context.UserCanAlterTable(table))
throw new InvalidOperationException();
context.Session.AddForeignKey(table, columns, refTable, refColumns, deleteRule, updateRule, deferred, constraintName);
}
private static void AddForeignKeys(IQuery systemQuery)
{
var fkCol = new[] { "routine_id" };
var refCol = new[] { "id" };
const ForeignKeyAction onUpdate = ForeignKeyAction.NoAction;
const ForeignKeyAction onDelete = ForeignKeyAction.Cascade;
systemQuery.Access().AddForeignKey(RoutineManager.RoutineParameterTableName, fkCol, RoutineManager.RoutineTableName, refCol,
onDelete, onUpdate, "ROUTINE_PARAMS_FK");
}
public ForeignKey(string name, Table parentTable, Table targetTable, ForeignKeyAction updateAction, ForeignKeyAction deleteAction)
{
Name = name;
ParentTable = parentTable;
TargetTable = targetTable;
FromColumns = new MetadataCollection <Column>(OnFromColumnAdd, null);
ToColumns = new MetadataCollection <Column>(OnToColumnsAdd, null);
UpdateAction = updateAction;
DeleteAction = deleteAction;
}
public static SqlTableConstraint ForeignKey(string constraintName, string[] columns, string refTable,
string[] refcolumns, ForeignKeyAction onDelete, ForeignKeyAction onUpdate)
{
return(new SqlTableConstraint(constraintName, ConstraintType.ForeignKey, columns)
{
ReferenceTable = refTable,
ReferenceColumns = refcolumns,
OnDelete = onDelete,
OnUpdate = onUpdate
});
}
public static void AddForeignKey(this IQuery query, ObjectName table, string[] columns, ObjectName refTable,
string[] refColumns, ForeignKeyAction deleteRule, ForeignKeyAction updateRule, ConstraintDeferrability deferred,
String constraintName)
{
if (!query.UserCanAlterTable(table))
throw new MissingPrivilegesException(query.UserName(), table, Privileges.Alter);
if (!query.UserCanReferenceTable(refTable))
throw new MissingPrivilegesException(query.UserName(), refTable, Privileges.References);
query.Session.AddForeignKey(table, columns, refTable, refColumns, deleteRule, updateRule, deferred, constraintName);
}
public ForeignKey(
string name, IUniqueIndex <TField> primaryKey, TableSnapshot <TRecord> childTable, IField <TRecord, TField> childColumn, ForeignKeyAction action, bool allowsDefault
)
{
this.Name = name;
this.primaryKey = primaryKey;
this.childTable = childTable;
this.childColumn = childColumn;
this.action = action;
this.allowsDefault = allowsDefault;
}
protected void SetFkAction(ForeignKey fk, string ev, ForeignKeyAction action)
{
if (ev.ToLower() == "delete")
{
fk.OnDeleteAction = action;
}
if (ev.ToLower() == "update")
{
fk.OnUpdateAction = action;
}
}
public ForeignKey(IForeignKey src)
: base(src)
{
PrimaryKeyTable = src.PrimaryKeyTable;
//Table = src.Table;
foreach (var col in src.PrimaryKeyColumns)
{
PrimaryKeyColumns.Add(new ColumnReference(col));
}
OnUpdateAction = src.OnUpdateAction;
OnDeleteAction = src.OnDeleteAction;
}
public ForeignKeyModel(string name,
ImmutableArray <ColumnModel> keyColumns,
PropertyInfo referenceTable,
ImmutableArray <PropertyInfo> referenceColumns,
ForeignKeyAction updateAction,
ForeignKeyAction deleteAction)
{
Name = name;
KeyColumns = keyColumns;
_referenceTable = referenceTable;
_referenceColumns = referenceColumns;
UpdateAction = updateAction;
DeleteAction = deleteAction;
}
public static void AddForeignKey(this IQuery query, ObjectName table, string[] columns, ObjectName refTable,
string[] refColumns, ForeignKeyAction deleteRule, ForeignKeyAction updateRule, ConstraintDeferrability deferred,
String constraintName)
{
if (!query.UserCanAlterTable(table))
{
throw new MissingPrivilegesException(query.UserName(), table, Privileges.Alter);
}
if (!query.UserCanReferenceTable(refTable))
{
throw new MissingPrivilegesException(query.UserName(), refTable, Privileges.References);
}
query.Session.AddForeignKey(table, columns, refTable, refColumns, deleteRule, updateRule, deferred, constraintName);
}
public static string SqlName(this ForeignKeyAction action)
{
switch (action)
{
case ForeignKeyAction.Cascade: return("CASCADE");
case ForeignKeyAction.Restrict: return("RESTRICT");
case ForeignKeyAction.SetNull: return("SET NULL");
case ForeignKeyAction.NoAction: return("NO ACTION");
default: return(null);
}
}
public static string AsSqlString(this ForeignKeyAction action)
{
if (action == ForeignKeyAction.SetNull)
{
return("SET NULL");
}
if (action == ForeignKeyAction.SetDefault)
{
return("SET DEFAULT");
}
if (action == ForeignKeyAction.NoAction)
{
return("NO ACTION");
}
return("CASCADE");
}
private static void CreateTables(IQuery context)
{
var tableInfo = new TableInfo(UserManager.UserTableName);
tableInfo.AddColumn("name", PrimitiveTypes.String());
// TODO: User table must be completed ...
tableInfo = tableInfo.AsReadOnly();
context.Access().CreateTable(tableInfo);
context.Access().AddPrimaryKey(UserManager.UserTableName, new[] { "name" }, "SYSTEM_USER_PK");
tableInfo = new TableInfo(UserManager.PasswordTableName);
tableInfo.AddColumn("user", PrimitiveTypes.String());
tableInfo.AddColumn("method", PrimitiveTypes.String());
tableInfo.AddColumn("method_args", PrimitiveTypes.Binary());
tableInfo.AddColumn("identifier", PrimitiveTypes.String());
tableInfo = tableInfo.AsReadOnly();
context.Access().CreateTable(tableInfo);
tableInfo = new TableInfo(UserManager.UserRoleTableName);
tableInfo.AddColumn("user", PrimitiveTypes.String());
tableInfo.AddColumn("role", PrimitiveTypes.String());
tableInfo.AddColumn("admin", PrimitiveTypes.Boolean());
tableInfo = tableInfo.AsReadOnly();
context.Access().CreateTable(tableInfo);
tableInfo = new TableInfo(UserManager.RoleTableName);
tableInfo.AddColumn("name", PrimitiveTypes.String(), true);
tableInfo = tableInfo.AsReadOnly();
context.Access().CreateTable(tableInfo);
context.Access().AddPrimaryKey(UserManager.RoleTableName, new[] { "name" }, "SYSTEM_ROLE_PK");
var fkCol = new[] { "user" };
var rfkCol = new[] { "role" };
var refCol = new[] { "name" };
const ForeignKeyAction onUpdate = ForeignKeyAction.NoAction;
const ForeignKeyAction onDelete = ForeignKeyAction.Cascade;
context.Access().AddForeignKey(UserManager.PasswordTableName, fkCol, UserManager.UserTableName, refCol, onDelete,
onUpdate, "USER_PASSWORD_FK");
context.Access().AddForeignKey(UserManager.UserRoleTableName, fkCol, UserManager.UserTableName, refCol, onDelete,
onUpdate, "USER_PRIV_FK");
context.Access().AddForeignKey(UserManager.UserRoleTableName, rfkCol, UserManager.RoleTableName, refCol, onDelete,
onUpdate, "USER_ROLE_FK");
}
public ForeignKeys On(ForeignKeyOnMode mode, ForeignKeyAction action)
{
_components.Add(new StringComponent(Constants.QueryComponents.ON));
switch (mode)
{
case ForeignKeyOnMode.Delete:
_components.Add(new StringComponent(Constants.QueryComponents.DELETE));
break;
case ForeignKeyOnMode.Update:
_components.Add(new StringComponent(Constants.QueryComponents.UPDATE));
break;
default:
throw new ArgumentOutOfRangeException(nameof(mode), mode, null);
}
switch (action)
{
case ForeignKeyAction.SetNull:
_components.Add(new StringComponent(Constants.QueryComponents.SET_NULL));
break;
case ForeignKeyAction.SetDefault:
_components.Add(new StringComponent(Constants.QueryComponents.SET_DEFAULT));
break;
case ForeignKeyAction.Cascade:
_components.Add(new StringComponent(Constants.QueryComponents.CASCADE));
break;
case ForeignKeyAction.Restrict:
_components.Add(new StringComponent(Constants.QueryComponents.RESTRICT));
break;
case ForeignKeyAction.NoAction:
_components.Add(new StringComponent(Constants.QueryComponents.NO_ACTION));
break;
default:
throw new ArgumentOutOfRangeException(nameof(action), action, null);
}
return(this);
}
private string GetBehavior(ForeignKeyAction fkDeleteAction)
{
switch (fkDeleteAction)
{
case ForeignKeyAction.NoAction:
return("Restrict");
case ForeignKeyAction.Cascade:
return("Cascade");
case ForeignKeyAction.SetNull:
return("SetNull");
case ForeignKeyAction.SetDefault:
return("ClientSetNull");
default:
throw new ArgumentOutOfRangeException(nameof(fkDeleteAction), fkDeleteAction, null);
}
}
static string GetForeignKeyAction(ForeignKeyAction action)
{
switch (action)
{
case ForeignKeyAction.NoAction:
return("NO ACTION");
case ForeignKeyAction.Cascade:
return("CASCADE");
case ForeignKeyAction.SetNull:
return("SET NULL");
case ForeignKeyAction.SetDefault:
return("SET DEFAULT");
default:
throw new NotImplementedException();
}
}
public EditorForeignKeyStructure(TableEditFrame frame, ForeignKey src)
: base(frame)
{
m_originalGroupId = src.GroupId;
Source = src;
m_name = src.Name;
m_table = src.Table.FullName;
m_onDelete = src.OnDeleteAction;
m_onUpdate = src.OnUpdateAction;
PrimaryKeyTable = src.PrimaryKeyTable;
for (int i = 0; i < src.Columns.Count; i++)
{
EditorForeignColumnStructure col = new EditorForeignColumnStructure();
col.SrcName = new ColumnReference(src.Columns[i]);
col.Fk = this;
if (src.PrimaryKeyColumns != null && i < src.PrimaryKeyColumns.Count)
{
col.DstName = new ColumnReference(src.PrimaryKeyColumns[i]);
}
Columns.Add(col);
}
}
protected virtual string FkActionSqlName(ForeignKeyAction action)
{
return(action.SqlName());
}
public IColumnForeignKeyBuilder OnUpdate(ForeignKeyAction action)
{
onUpdate = action;
return(this);
}
public IColumnForeignKeyBuilder OnUpdate(ForeignKeyAction action)
{
onUpdate = action;
return this;
}
public static void AddForeignKey(this ISession session, ObjectName table, string[] columns,
ObjectName refTable, string[] refColumns,
ForeignKeyAction deleteRule, ForeignKeyAction updateRule, ConstraintDeferrability deferred, String constraintName)
{
session.Transaction.AddForeignKey(table, columns, refTable, refColumns, deleteRule, updateRule, deferred, constraintName);
}
public static SqlTableConstraint ForeignKey(string constraintName, string[] columns, string refTable,
string[] refcolumns, ForeignKeyAction onDelete, ForeignKeyAction onUpdate)
{
return new SqlTableConstraint(constraintName, ConstraintType.ForeignKey, columns) {
ReferenceTable = refTable,
ReferenceColumns = refcolumns,
OnDelete = onDelete,
OnUpdate = onUpdate
};
}
public static void AddForeignKey(this ITransaction transaction, ObjectName table, string[] columns,
ObjectName refTable, string[] refColumns,
ForeignKeyAction deleteRule, ForeignKeyAction updateRule, ConstraintDeferrability deferred, String constraintName)
{
var t = transaction.GetMutableTable(SystemSchema.ForeignKeyInfoTableName);
var tcols = transaction.GetMutableTable(SystemSchema.ForeignKeyColumnsTableName);
try {
// If 'ref_columns' empty then set to primary key for referenced table,
// ISSUE: What if primary key changes after the fact?
if (refColumns.Length == 0) {
var set = transaction.QueryTablePrimaryKey(refTable);
if (set == null)
throw new Exception(String.Format("No primary key defined for referenced table '{0}'", refTable));
refColumns = set.ColumnNames;
}
if (columns.Length != refColumns.Length) {
throw new Exception(String.Format("Foreign key reference '{0}' -> '{1}' does not have an equal number of " +
"column terms.", table, refTable));
}
// If delete or update rule is 'SET NULL' then check the foreign key
// columns are not constrained as 'NOT NULL'
if (deleteRule == ForeignKeyAction.SetNull ||
updateRule == ForeignKeyAction.SetNull) {
var tableInfo = transaction.GetTableInfo(table);
for (int i = 0; i < columns.Length; ++i) {
var columnInfo = tableInfo[tableInfo.IndexOfColumn(columns[i])];
if (columnInfo.IsNotNull) {
throw new Exception(String.Format("Foreign key reference '{0}' -> '{1}' update or delete triggered " +
"action is SET NULL for columns that are constrained as " +
"NOT NULL.", table, refTable));
}
}
}
// Insert a value into ForeignInfoTable
var row = t.NewRow();
var uniqueId = transaction.NextTableId(SystemSchema.ForeignKeyInfoTableName);
constraintName = MakeUniqueConstraintName(constraintName, uniqueId);
row.SetValue(0, uniqueId);
row.SetValue(1, constraintName);
row.SetValue(2, table.Parent.Name);
row.SetValue(3, table.Name);
row.SetValue(4, refTable.Parent.Name);
row.SetValue(5, refTable.Name);
row.SetValue(6, ((int) updateRule));
row.SetValue(7, ((int) deleteRule));
row.SetValue(8, ((short) deferred));
t.AddRow(row);
// Insert the columns
for (int i = 0; i < columns.Length; ++i) {
row = tcols.NewRow();
row.SetValue(0, uniqueId); // unique id
row.SetValue(1, columns[i]); // column name
row.SetValue(2, refColumns[i]); // ref column name
row.SetValue(3, i); // sequence number
tcols.AddRow(row);
}
} catch (ConstraintViolationException e) {
// Constraint violation when inserting the data. Check the type and
// wrap around an appropriate error message.
if (e.ErrorCode == SqlModelErrorCodes.UniqueViolation)
// This means we gave a constraint name that's already being used
// for a primary key.
throw new Exception(String.Format("Foreign key constraint name '{0}' is already being used.", constraintName));
throw;
}
}
public virtual string GetConstraintActionString (ForeignKeyAction action)
{
switch (action) {
case ForeignKeyAction.None:
return " NO ACTION ";
case ForeignKeyAction.Cascade:
return " CASCADE ";
case ForeignKeyAction.Restrict:
return " RESTRICT ";
case ForeignKeyAction.NoAction:
return " NO ACTION ";
case ForeignKeyAction.SetDefault:
return " SET DEFAULT ";
case ForeignKeyAction.SetNull:
return " SET NULL ";
default:
throw new NotImplementedException ();
}
}
public virtual string OnDeleteSqlName(ForeignKeyAction action)
{
return(FkActionSqlName(action));
}
/// <summary>
/// Creates foreign key
/// </summary>
/// <typeparam name="TField"></typeparam>
/// <param name="name"></param>
/// <param name="parentTable"></param>
/// <param name="foreignColumn"></param>
/// <param name="action"></param>
/// <param name="allowsDefault"></param>
public void CreateForeignKey <TField>(string name, ITableSnapshot parentTable, IField <TRecord, TField> foreignColumn, ForeignKeyAction action, bool allowsDefault)
{
this.StoreSnapshot.SnapStore.CheckNotFrozen();
if (string.IsNullOrEmpty(name))
{
throw new ArgumentNullException(nameof(name));
}
if (parentTable == null)
{
throw new ArgumentNullException(nameof(parentTable));
}
IPrimaryKeyHolder parent = (IPrimaryKeyHolder)parentTable;
if (parent.PrimaryKey <TField>() == null)
{
throw new ArgumentException(Properties.Resources.ErrorPrimaryKeyMissing(this.Name), nameof(parentTable));
}
this.table.ValidateField(foreignColumn);
if (!Enum.IsDefined(typeof(ForeignKeyAction), action))
{
throw new ArgumentOutOfRangeException(nameof(action));
}
if (this.table.ForeignKeys[foreignColumn.Order] != null)
{
throw new ArgumentException(Properties.Resources.ErrorForeignKeyExists(this.Name, foreignColumn.Name));
}
foreach (IForeignKey fk in this.table.ForeignKeys)
{
if (fk != null && fk.Name == name)
{
throw new ArgumentException(Properties.Resources.ErrorForeignKeyNameExists(this.Name, name), nameof(name));
}
}
ForeignKey <TField> foreignKey = new ForeignKey <TField>(name, parent.PrimaryKey <TField>(), this, foreignColumn, action, allowsDefault);
this.table.ForeignKeys[foreignColumn.Order] = foreignKey;
parent.Children.Add(foreignKey);
}
public ForeignKeyAttribute(Type referencedType, ForeignKeyAction onUpdate, ForeignKeyAction onDelete)
: this(null, referencedType, onUpdate, onDelete)
{
}
/// <summary>
/// Create foreign keys with disabled defaults
/// </summary>
/// <typeparam name="TField"></typeparam>
/// <param name="name"></param>
/// <param name="parentTable"></param>
/// <param name="foreignColumn"></param>
/// <param name="action"></param>
public void CreateForeignKey <TField>(string name, ITableSnapshot parentTable, IField <TRecord, TField> foreignColumn, ForeignKeyAction action)
{
this.CreateForeignKey <TField>(name, parentTable, foreignColumn, action, false);
}
public override string OnUpdateSqlName(ForeignKeyAction action)
{
return(null);
}
