public static void DropConstraint(this IAlterProcessor proc, ConstraintInfo cnt)
{
var pk = cnt as PrimaryKeyInfo;
if (pk != null)
{
proc.DropPrimaryKey(pk);
}
var fk = cnt as ForeignKeyInfo;
if (fk != null)
{
proc.DropForeignKey(fk);
}
var uq = cnt as UniqueInfo;
if (uq != null)
{
proc.DropUnique(uq);
}
var ix = cnt as IndexInfo;
if (ix != null)
{
proc.DropIndex(ix);
}
var ch = cnt as CheckInfo;
if (ch != null)
{
proc.DropCheck(ch);
}
}
public AddConstraintAction(ConstraintInfo constraint)
{
if (constraint == null)
throw new ArgumentNullException("constraint");
Constraint = constraint;
}
public static ConstraintInfo[] QueryTableUniqueKeys(this ITransaction transaction, ObjectName tableName)
{
var t = transaction.GetTable(SystemSchema.UniqueKeyInfoTableName);
var t2 = transaction.GetTable(SystemSchema.UniqueKeyColumnsTableName);
// Returns the list indexes where column 3 = table name
// and column 2 = schema name
var objTableName = Field.String(tableName.Name);
var objSchemaName = Field.String(tableName.Parent.Name);
var data = t.SelectRowsEqual(3, objTableName, 2, objSchemaName).ToList();
var constraints = new ConstraintInfo[data.Count];
for (int i = 0; i < data.Count; ++i)
{
var id = t.GetValue(data[i], 0);
// Select all records with equal id
var cols = t2.SelectRowsEqual(0, id);
var name = t.GetValue(data[i], 1).Value.ToString();
var columns = ToColumns(t2, cols); // the list of columns
var deferred = (ConstraintDeferrability)((SqlNumber)t.GetValue(data[i], 4).Value).ToInt16();
var constraint = ConstraintInfo.Unique(name, tableName, columns);
constraint.Deferred = deferred;
constraints[i] = constraint;
}
return(constraints);
}
public void ConstraintInfoTest()
{
IBooleanOperator trueExpression = new GreaterThanOperator(Three, Two);
var trueTree = new BooleanExpressionTree("true", trueExpression);
var constraintOne = new ConstraintInfo(trueTree);
Console.Write("Created constraint: ");
Console.WriteLine(constraintOne);
Console.WriteLine("ConstraintInfoTest: testing name of anonymous constraint...");
Expect(constraintOne.Name == string.Empty);
Console.WriteLine("ConstraintInfoTest: testing Predicate property (constraintOne)...");
Expect(constraintOne.Predicate == trueTree);
const string constraintName = "ConstraintTwo";
IBooleanOperator falseExpression = new LessThanOperator(Three, Two);
var falseTree = new BooleanExpressionTree("false", falseExpression);
var constraintTwo = new ConstraintInfo(constraintName, falseTree);
Console.Write("Created constraint: ");
Console.WriteLine(constraintTwo);
Console.WriteLine("ConstraintInfoTest: testing Name of named constraint...");
Expect(constraintTwo.Name == constraintName);
Console.WriteLine("ConstraintInfoTest: testing Predicate property (constraintTwo)...");
Expect(constraintTwo.Predicate == falseTree);
}
public void AddConstraint(ObjectName tableName, ConstraintInfo constraintInfo)
{
if (constraintInfo.ConstraintType == ConstraintType.PrimaryKey)
{
var columnNames = constraintInfo.ColumnNames;
if (columnNames.Length > 1)
{
throw new ArgumentException();
}
AddPrimaryKey(tableName, columnNames[0], constraintInfo.ConstraintName);
}
else if (constraintInfo.ConstraintType == ConstraintType.Unique)
{
AddUniqueKey(tableName, constraintInfo.ColumnNames, constraintInfo.ConstraintName);
}
else if (constraintInfo.ConstraintType == ConstraintType.Check)
{
AddCheck(tableName, constraintInfo.CheckExpression, constraintInfo.ConstraintName);
}
else if (constraintInfo.ConstraintType == ConstraintType.ForeignKey)
{
AddForeignKey(tableName, constraintInfo.ColumnNames, constraintInfo.ForeignTable,
constraintInfo.ForeignColumnNames, constraintInfo.OnDelete, constraintInfo.OnUpdate, constraintInfo.ConstraintName);
}
}
public void DropConstraint(ConstraintInfo constraint, PlanPosition pos = PlanPosition.End)
{
ConstraintInfo cnt = Structure.FindOrCreateConstraint(constraint);
AddOperation(new AlterOperation_DropConstraint {
ParentTable = cnt.OwnerTable, OldObject = cnt
}, pos);
}
public void SetConstraintInfo(ConstraintInfo info)
{
m_RotateAxis = info.m_RotateAxis;
m_IKRootRelDir = info.m_LimbRootRefDir;
m_SkeleRootRelDir = info.m_SkeleRootRefDir;
m_SkeleRootJoint = info.m_SkeleRootJoint;
m_BoneAxisAngleThres = info.m_AngleThres;
}
public void CreateConstraint(TableInfo table, ConstraintInfo newcnt, PlanPosition pos = PlanPosition.End)
{
TableInfo tbl = Structure.FindOrCreateTable(table.FullName);
AddOperation(new AlterOperation_CreateConstraint {
ParentTable = tbl, NewObject = newcnt.CloneConstraint()
}, pos);
}
public void ChangeConstraint(ConstraintInfo constraint, ConstraintInfo newconstraint)
{
ConstraintInfo cnt = Structure.FindOrCreateConstraint(constraint);
AddOperation(new AlterOperation_ChangeConstraint {
OldObject = cnt, ParentTable = cnt.OwnerTable, NewObject = newconstraint.CloneConstraint()
});
}
public void RenameConstraint(ConstraintInfo constraint, string newname)
{
ConstraintInfo cnt = Structure.FindOrCreateConstraint(constraint);
AddOperation(new AlterOperation_RenameConstraint {
OldObject = cnt, ParentTable = cnt.OwnerTable, NewName = new NameWithSchema(newname)
});
}
public override void RenameConstraint(ConstraintInfo cnt, string newname)
{
if (cnt.ObjectType == DatabaseObjectType.Index)
{
PutCmd("^execute sp_rename '%f.%i', '%s', 'INDEX'", cnt.OwnerTable.FullName, cnt.ConstraintName, newname);
}
else
{
PutCmd("^execute sp_rename '%f', '%s', 'OBJECT'", new NameWithSchema(cnt.OwnerTable.FullName.Schema, cnt.ConstraintName), newname);
}
}
public static ConstraintInfo[] QueryTableCheckExpressions(this ITransaction transaction, ObjectName tableName)
{
var t = transaction.GetTable(SystemSchema.CheckInfoTableName);
// Returns the list indexes where column 3 = table name
// and column 2 = schema name
var objTableName = Field.String(tableName.Name);
var objSchemaName = Field.String(tableName.Parent.Name);
var data = t.SelectRowsEqual(3, objTableName, 2, objSchemaName).ToList();
var checks = new ConstraintInfo[data.Count];
for (int i = 0; i < checks.Length; ++i)
{
int rowIndex = data[i];
string name = t.GetValue(rowIndex, 1).Value.ToString();
var deferred = (ConstraintDeferrability)((SqlNumber)t.GetValue(rowIndex, 5).Value).ToInt16();
SqlExpression expression = null;
// Is the deserialized version available?
if (t.TableInfo.ColumnCount > 6)
{
var sexp = (SqlBinary)t.GetValue(rowIndex, 6).Value;
if (!sexp.IsNull)
{
try {
// Deserialize the expression
// TODO: expression = (SqlExpression)ObjectTranslator.Deserialize(sexp);
throw new NotImplementedException();
} catch (Exception) {
// We weren't able to deserialize the expression so report the
// error to the log
// TODO:
}
}
}
// Otherwise we need to parse it from the string
if (expression == null)
{
expression = SqlExpression.Parse(t.GetValue(rowIndex, 4).Value.ToString());
}
var check = ConstraintInfo.Check(name, tableName, expression);
check.Deferred = deferred;
checks[i] = check;
}
return(checks);
}
private static int CompareConstraints(ConstraintInfo lft, ConstraintInfo rgt)
{
var nl = lft.ConstraintName;
var nr = rgt.ConstraintName;
if (lft.GetType() != rgt.GetType())
{
return(String.Compare(lft.GetType().FullName, rgt.GetType().FullName));
}
if (nl != null && nr != null)
{
return(String.Compare(nl, nr));
}
return(0);
}
private void GetPrimarykey(TableInfo table)
{
string _sqltext = $@"SELECT COLUMN_NAME,CONSTRAINT_NAME FROM information_schema.KEY_COLUMN_USAGE where TABLE_SCHEMA = '{table.Schema}' and TABLE_NAME = '{table.Name}' AND CONSTRAINT_NAME = 'PRIMARY'";
dbContext.Execute.ExecuteDataReader(dr =>
{
var constaint = new ConstraintInfo
{
Field = dr["COLUMN_NAME"].ToString(),
Name = dr["CONSTRAINT_NAME"].ToString(),
Type = ConstraintType.PK
};
table.Constraints.Add(constaint);
table.Fields.Where(f => f.Name == constaint.Field).First().PrimaryKey = true;
}, CommandType.Text, _sqltext);
}
private void GetPrimarykey(TableInfo table)
{
string _sqltext = $@"select a.constraint_name,b.column_name
from information_schema.table_constraints a
inner join information_schema.constraint_column_usage b on a.constraint_name=b.constraint_name
where a.table_schema || '.' || a.table_name='{table.Schema}.{table.Name}' and a.constraint_type='PRIMARY KEY'";
dbContext.Execute.ExecuteDataReader(dr =>
{
var constaint = new ConstraintInfo
{
Field = dr["column_name"].ToString(),
Name = dr["constraint_name"].ToString(),
Type = ConstraintType.PK
};
table.Constraints.Add(constaint);
table.Fields.Where(f => f.Name == constaint.Field).First().PrimaryKey = true;
}, CommandType.Text, _sqltext);
}
public static ConstraintInfo QueryTablePrimaryKey(this ITransaction transaction, ObjectName tableName)
{
var t = transaction.GetTable(SystemSchema.PrimaryKeyInfoTableName);
var t2 = transaction.GetTable(SystemSchema.PrimaryKeyColumnsTableName);
// Returns the list indexes where column 3 = table name
// and column 2 = schema name
var objTableName = Field.String(tableName.Name);
var objSchemaName = Field.String(tableName.Parent.Name);
var data = t.SelectRowsEqual(3, objTableName, 2, objSchemaName).ToList();
if (data.Count > 1)
{
throw new InvalidOperationException("Assertion failed: multiple primary key for: " + tableName);
}
if (data.Count == 0)
{
return(null);
}
int rowIndex = data[0];
var id = t.GetValue(rowIndex, 0);
// All columns with this id
var list = t2.SelectRowsEqual(0, id);
// Make it in to a columns object
var name = t.GetValue(rowIndex, 1).AsVarChar().Value.ToString();
string[] columns = ToColumns(t2, list);
var deferred = (ConstraintDeferrability)((SqlNumber)t.GetValue(rowIndex, 4).Value).ToInt16();
var constraint = ConstraintInfo.PrimaryKey(name, tableName, columns);
constraint.Deferred = deferred;
return(constraint);
}
public static ConstraintInfo[] QueryTableUniqueKeys(this ITransaction transaction, ObjectName tableName)
{
var t = transaction.GetTable(SystemSchema.UniqueKeyInfoTableName);
var t2 = transaction.GetTable(SystemSchema.UniqueKeyColumnsTableName);
// Returns the list indexes where column 3 = table name
// and column 2 = schema name
var objTableName = Field.String(tableName.Name);
var objSchemaName = Field.String(tableName.Parent.Name);
var data = t.SelectRowsEqual(3, objTableName, 2, objSchemaName).ToList();
var constraints = new ConstraintInfo[data.Count];
for (int i = 0; i < data.Count; ++i) {
var id = t.GetValue(data[i], 0);
// Select all records with equal id
var cols = t2.SelectRowsEqual(0, id);
var name = t.GetValue(data[i], 1).Value.ToString();
var columns = ToColumns(t2, cols); // the list of columns
var deferred = (ConstraintDeferrability) ((SqlNumber) t.GetValue(data[i], 4).Value).ToInt16();
var constraint = ConstraintInfo.Unique(name, tableName, columns);
constraint.Deferred = deferred;
constraints[i] = constraint;
}
return constraints;
}
public AddUniqueConstraintChange(TableInfo table, ConstraintInfo constraint)
: base(table)
{
_constraint = constraint;
}
public void RenameConstraint(ConstraintInfo constraint, string newname)
{
_database.FindConstraint(constraint).ConstraintName = newname;
}
public static ConstraintInfo[] QueryTableImportedForeignKeys(this ITransaction transaction, ObjectName refTableName)
{
var t = transaction.GetTable(SystemSchema.ForeignKeyInfoTableName);
var t2 = transaction.GetTable(SystemSchema.ForeignKeyColumnsTableName);
// Returns the list indexes where column 5 = ref table name
// and column 4 = ref schema name
var objRefTableName = Field.String(refTableName.Name);
var objRefSchema = Field.String(refTableName.Parent.Name);
var data = t.SelectRowsEqual(5, objRefTableName, 4, objRefSchema).ToArray();
var groups = new ConstraintInfo[data.Length];
for (int i = 0; i < data.Length; ++i) {
int rowIndex = data[i];
// The foreign key id
var id = t.GetValue(rowIndex, 0);
// The referencee table
var schemaNamePart = t.GetValue(rowIndex, 2).AsVarChar().Value.ToString();
var tableNamePart = t.GetValue(rowIndex, 3).AsVarChar().Value.ToString();
var tableName = new ObjectName(new ObjectName(schemaNamePart), tableNamePart);
// Select all records with equal id
var cols = t2.SelectRowsEqual(0, id).ToArray();
var name = t.GetValue(rowIndex, 1).AsVarChar().Value.ToString();
var updateRule = (ForeignKeyAction) ((SqlNumber) t.GetValue(rowIndex, 6).AsBigInt().Value).ToInt32();
var deleteRule = (ForeignKeyAction) ((SqlNumber) t.GetValue(rowIndex, 7).AsBigInt().Value).ToInt32();
var deferred = (ConstraintDeferrability) ((SqlNumber) t.GetValue(rowIndex, 8).AsBigInt().Value).ToInt16();
int colsSize = cols.Length;
string[] keyCols = new string[colsSize];
string[] refCols = new string[colsSize];
for (int n = 0; n < colsSize; ++n) {
for (int p = 0; p < colsSize; ++p) {
int colsIndex = cols[p];
if (t2.GetValue(colsIndex, 3) == n) {
keyCols[n] = t2.GetValue(colsIndex, 1);
refCols[n] = t2.GetValue(colsIndex, 2);
break;
}
}
}
var constraint = ConstraintInfo.ForeignKey(name, tableName, keyCols, refTableName, refCols);
constraint.OnDelete = deleteRule;
constraint.OnUpdate = updateRule;
constraint.Deferred = deferred;
groups[i] = constraint;
}
return groups;
}
/// <summary>
/// Gets the primary key definition string.
/// </summary>
/// <param name="pk"></param>
/// <returns></returns>
protected string GetPrimaryKeyString(ConstraintInfo pk)
{
return string.Format(" primary key ({0})", StringUtilities.Combine(pk.Columns, ", "));
}
protected override void ExecuteStatement(ExecutionContext context)
{
//if (!context.User.CanAlterTable(TableName))
// throw new InvalidAccessException(context.Request.UserName(), TableName);
var table = context.Request.Access().GetTable(TableName);
if (table == null)
{
throw new ObjectNotFoundException(TableName);
}
var tableInfo = table.TableInfo;
var newTableInfo = new TableInfo(tableInfo.TableName);
var checker = ColumnChecker.Default(context.Request, TableName);
bool tableAltered = false;
bool markDropped = false;
for (int n = 0; n < tableInfo.ColumnCount; ++n)
{
var column = tableInfo[n];
string columnName = column.ColumnName;
var columnType = column.ColumnType;
var defaultExpression = column.DefaultExpression;
if (Action.ActionType == AlterTableActionType.SetDefault &&
CheckColumnNamesMatch(context.Request, ((SetDefaultAction)Action).ColumnName, columnName))
{
var exp = ((SetDefaultAction)Action).DefaultExpression;
exp = checker.CheckExpression(exp);
defaultExpression = exp;
tableAltered = true;
}
else if (Action.ActionType == AlterTableActionType.DropDefault &&
CheckColumnNamesMatch(context.Request, ((DropDefaultAction)Action).ColumnName, columnName))
{
defaultExpression = null;
tableAltered = true;
}
else if (Action.ActionType == AlterTableActionType.DropColumn &&
CheckColumnNamesMatch(context.Request, ((DropColumnAction)Action).ColumnName, columnName))
{
// Check there are no referential links to this column
var refs = context.Request.Access().QueryTableImportedForeignKeys(TableName);
foreach (var reference in refs)
{
CheckColumnConstraint(columnName, reference.ForeignColumnNames, reference.ForeignTable, reference.ConstraintName);
}
// Or from it
refs = context.Request.Access().QueryTableForeignKeys(TableName);
foreach (var reference in refs)
{
CheckColumnConstraint(columnName, reference.ColumnNames, reference.TableName, reference.ConstraintName);
}
// Or that it's part of a primary key
var primaryKey = context.Request.Access().QueryTablePrimaryKey(TableName);
if (primaryKey != null)
{
CheckColumnConstraint(columnName, primaryKey.ColumnNames, TableName, primaryKey.ConstraintName);
}
// Or that it's part of a unique set
var uniques = context.Request.Access().QueryTableUniqueKeys(TableName);
foreach (var unique in uniques)
{
CheckColumnConstraint(columnName, unique.ColumnNames, TableName, unique.ConstraintName);
}
markDropped = true;
tableAltered = true;
}
var newColumn = new ColumnInfo(columnName, columnType);
if (defaultExpression != null)
{
newColumn.DefaultExpression = defaultExpression;
}
newColumn.IndexType = column.IndexType;
newColumn.IsNotNull = column.IsNotNull;
// If not dropped then add to the new table definition.
if (!markDropped)
{
newTableInfo.AddColumn(newColumn);
}
}
if (Action.ActionType == AlterTableActionType.AddColumn)
{
var col = ((AddColumnAction)Action).Column;
checker.CheckExpression(col.DefaultExpression);
var columnName = col.ColumnName;
var columnType = col.ColumnType;
// If column name starts with [table_name]. then strip it off
columnName = checker.StripTableName(TableName.Name, columnName);
if (tableInfo.IndexOfColumn(col.ColumnName) != -1)
{
throw new InvalidOperationException("The column '" + col.ColumnName + "' is already in the table '" + tableInfo.TableName + "'.");
}
var newColumn = new ColumnInfo(columnName, columnType)
{
IsNotNull = col.IsNotNull,
DefaultExpression = col.DefaultExpression
};
newTableInfo.AddColumn(newColumn);
tableAltered = true;
}
if (Action.ActionType == AlterTableActionType.DropConstraint)
{
string constraintName = ((DropConstraintAction)Action).ConstraintName;
int dropCount = context.Request.Access().DropTableConstraint(TableName, constraintName);
if (dropCount == 0)
{
throw new InvalidOperationException("Named constraint to drop on table " + TableName + " was not found: " + constraintName);
}
}
else if (Action.ActionType == AlterTableActionType.DropPrimaryKey)
{
if (!context.Request.Access().DropTablePrimaryKey(TableName, null))
{
throw new InvalidOperationException("No primary key to delete on table " + TableName);
}
}
if (Action.ActionType == AlterTableActionType.AddConstraint)
{
var constraint = ((AddConstraintAction)Action).Constraint;
bool foreignConstraint = (constraint.ConstraintType == ConstraintType.ForeignKey);
ObjectName refTname = null;
if (foreignConstraint)
{
refTname = context.Request.Access().ResolveTableName(constraint.ReferenceTable);
}
var columnNames = checker.StripColumnList(TableName.FullName, constraint.Columns);
columnNames = checker.StripColumnList(constraint.ReferenceTable, columnNames);
var expression = checker.CheckExpression(constraint.CheckExpression);
columnNames = checker.CheckColumns(columnNames);
IEnumerable <string> refCols = null;
if (foreignConstraint && constraint.ReferenceColumns != null)
{
var referencedChecker = ColumnChecker.Default(context.Request, refTname);
refCols = referencedChecker.CheckColumns(constraint.ReferenceColumns);
}
var newConstraint = new ConstraintInfo(constraint.ConstraintName, constraint.ConstraintType, TableName, columnNames.ToArray());
if (foreignConstraint)
{
if (refCols == null)
{
throw new InvalidOperationException("Could not create a Foreign Key constraint with no reference columns");
}
newConstraint.ForeignTable = refTname;
newConstraint.ForeignColumnNames = refCols.ToArray();
newConstraint.OnDelete = constraint.OnDelete;
newConstraint.OnUpdate = constraint.OnUpdate;
}
if (constraint.ConstraintType == ConstraintType.Check)
{
newConstraint.CheckExpression = expression;
}
context.Request.Access().AddConstraint(TableName, newConstraint);
}
// Alter the existing table to the new format...
if (tableAltered)
{
if (newTableInfo.ColumnCount == 0)
{
throw new InvalidOperationException("Can not ALTER table to have 0 columns.");
}
context.DirectAccess.AlterObject(newTableInfo);
}
else
{
// If the table wasn't physically altered, check the constraints.
// Calling this method will also make the transaction check all
// deferred constraints during the next commit.
context.Request.Access().CheckConstraintViolations(TableName);
}
}
/// <summary>
/// Gets the unique constraint definition string.
/// </summary>
/// <param name="uk"></param>
/// <returns></returns>
protected string GetUniqueConstraintString(ConstraintInfo uk)
{
return(string.Format(" unique ({0})", StringUtilities.Combine(uk.Columns, ", ")));
}
protected virtual void DropConstraint(ConstraintInfo cnt)
{
PutCmd("^alter ^table %f ^drop ^constraint %i", cnt.OwnerTable.FullName, cnt.ConstraintName);
}
private static SqlStatement MakeAlterTableAddConstraint(string tableName, ConstraintInfo constraint)
{
var action = new AddConstraintAction(constraint);
return new AlterTableStatement(tableName, action);
}
/// <summary>
/// Gets the primary key definition string.
/// </summary>
/// <param name="pk"></param>
/// <returns></returns>
protected string GetPrimaryKeyString(ConstraintInfo pk)
{
return(string.Format(" primary key ({0})", StringUtilities.Combine(pk.Columns, ", ")));
}
public AddUniqueConstraintChange(TableInfo table, ConstraintInfo constraint)
: base(table)
{
_constraint = constraint;
}
public DropPrimaryKeyChange(TableInfo table, ConstraintInfo pk)
:base(table)
{
_pk = pk;
}
public void AddConstraint <TEvent, TConstraint>(ConstraintInfo <TEvent, TConstraint> constraintInfo)
{
constraintInfo.Id = GenerateConstraintId <TEvent>(constraintInfo);
}
public override void OnConstraintBreak(ConstraintInfo[] constraints)
{
var joint = constraints[0].ExternalReference as PhysX.Joint;
BrokenJoints.Add(joint);
}
public PgDatabaseDef(NpgsqlConnection connection)
{
try {
using (var cmd = connection.CreateCommand()) {
// データベース名の取得
cmd.CommandText = "SELECT * FROM current_catalog;";
using (var dr = cmd.ExecuteReader()) {
while (dr.Read())
{
this.Name = dr[0] as string;
}
}
// テーブル一覧と列の取得
var tables = new Dictionary <string, PgTableDef>();
cmd.CommandText = @"
SELECT
table_name
,column_name
,ordinal_position
,column_default
,udt_name
FROM
information_schema.columns
WHERE
table_catalog=@0 AND table_schema='public'
ORDER BY
table_name, ordinal_position
;
";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@0", this.Name);
using (var dr = cmd.ExecuteReader()) {
while (dr.Read())
{
var table_name = dr[0] as string;
var column_name = dr[1] as string;
var ordinal_position = dr[2] as string;
var column_default = dr[3] as string;
var udt_name = dr[4] as string;
ColumnFlags flags = 0;
if (!string.IsNullOrEmpty(column_default))
{
if (column_default.StartsWith("nextval('"))
{
flags |= ColumnFlags.Serial;
}
if (column_default == "CURRENT_TIMESTAMP")
{
flags |= ColumnFlags.DefaultCurrentTimestamp;
}
}
PgTableDef tableDef;
if (!tables.TryGetValue(table_name, out tableDef))
{
tables[table_name] = tableDef = new PgTableDef(table_name);
}
tableDef.ColumnDefs.Add(new PgColumnDef(column_name, new PgDbType(udt_name), flags));
}
}
// プライマリキーとインデックス、ユニークキーの取得
cmd.CommandText = @"
SELECT
-- t.relname,
i.relname AS index_name,
ix.indisprimary AS is_primary_key,
ix.indisunique AS is_unique_key,
ix.indkey AS combination,
att.attname AS column_name,
att.attnum AS attnum,
a.amname AS index_type
FROM
pg_class t
INNER JOIN pg_index ix ON ix.indrelid=t.oid
INNER JOIN pg_class i ON i.oid=ix.indexrelid
INNER JOIN pg_am a ON a.oid=i.relam
INNER JOIN pg_attribute att ON att.attrelid=t.oid AND att.attnum=ANY(ix.indkey)
WHERE
t.relname=@0 AND t.relkind='r';
";
foreach (var table in tables.Values)
{
var indicesDic = new Dictionary <string, ConstraintInfo>();
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@0", table.Name);
using (var dr = cmd.ExecuteReader()) {
while (dr.Read())
{
var index_name = dr[0] as string;
var is_primary_key = (bool)dr[1];
var is_unique_key = (bool)dr[2];
var combination = dr[3] as short[];
var column_name = dr[4] as string;
var attnum = Convert.ToInt32(dr[5]);
var index_type = dr[6] as string;
ConstraintInfo info;
if (!indicesDic.TryGetValue(index_name, out info))
{
indicesDic[index_name] = info = new ConstraintInfo(index_name, is_primary_key, is_unique_key, combination, index_type);
}
var column = (from c in table.ColumnDefs where c.Name == column_name select c).FirstOrDefault();
info.Columns[attnum] = column ?? throw new ApplicationException();
}
}
var indices = new List <Tuple <string, string, PgColumnDef[]> >();
var uniques = new List <Tuple <string, PgColumnDef[]> >();
foreach (var kvp in indicesDic)
{
var info = kvp.Value;
var columns = (from i in info.Combination select info.Columns[i]).ToArray();
if (info.IsPrimaryKey)
{
table.PrimaryKey = new PrimaryKeyDef(kvp.Key, columns);
}
else if (info.IsUniqueKey)
{
uniques.Add(new Tuple <string, PgColumnDef[]>(kvp.Key, columns));
}
else
{
indices.Add(new Tuple <string, string, PgColumnDef[]>(kvp.Key, info.IndexType, columns));
}
}
table.Indices = (from i in indices select new IndexDef(i.Item1, i.Item2 == "gin" ? IndexFlags.Gin : 0, i.Item3)).ToArray();
table.Uniques = (from u in uniques select new UniqueDef(u.Item1, u.Item2)).ToArray();
}
this.Tables = (from t in tables.Values select t).ToArray();
}
} catch (PostgresException ex) {
throw new PgEnvironmentException(ex);
}
}
public AddPrimaryKeyChange(TableInfo table, ConstraintInfo pk)
: base(table)
{
_pk = pk;
}
private static void PopulateConstraints(ColumnInfo[] allColumns, ConstraintInfo[] allConstraints)
{
foreach (var c in allConstraints)
{
var column = allColumns.FirstOrDefault(p => p.Schema == c.Schema && p.TableName == c.TableName && c.ColumnName == p.ColumnName);
if (column != null)
{
switch (c.Type)
{
case ConstraintType.PrimaryKey:
column.IsPrimaryKey = true;
break;
case ConstraintType.Uniqule:
column.IsUniqule = true;
break;
case ConstraintType.Check:
column.IsCheck = true;
break;
}
}
}
}
public virtual void RenameConstraint(ConstraintInfo constraint, string newname)
{
throw new System.NotImplementedException();
}
public void ModelInfoTest()
{
const string modelName = "test model";
var builder = new ModelInfo.ModelBuilder(modelName);
const string constraintName = "test constraint";
const string constraintPredicateName = "constraint predicate";
var constraintExpression = new EqualToOperator(Two, Two);
var constraintPredicate = new BooleanExpressionTree(constraintPredicateName, constraintExpression);
var constraint = new ConstraintInfo(constraintName, constraintPredicate);
builder.AddConstraint(constraint);
/*
* const string eventName = "test event";
* const string eventPredicateName = "event predicate";
* var eventExpression = new GreaterThanOperator(Three, Two);
* var eventPredicate = new BooleanExpressionTree(eventPredicateName, eventExpression);
* int eventCount = 0;
* var testEvent = new EventInfo(eventName, eventPredicate, () => { eventCount++; });
* builder.AddEvent(testEvent);
*/
const string expressionName = "test expression";
var expression = new AddOperator(Three, Two);
var expressionTree = new NumericExpressionTree(expressionName, expression);
builder.AddExpression(expressionTree);
const string localeName = "test locale";
var locale = new LocaleInfo(localeName);
builder.AddLocale(locale);
const string observableName = "test observable";
const string observableExpressionName = "observable expression";
var rootOperator = new PowerOperator(Two, Three);
var observableExpressionTree = new NumericExpressionTree(observableExpressionName, rootOperator);
var observable = new ObservableInfo(observableName, observableExpressionTree);
builder.AddObservable(observable);
const string parameterName = "test parameter";
var parameter = new ParameterInfo(parameterName, 3.14159265f);
builder.AddParameter(parameter);
const string predicateName = "test predicate";
var predicateExpression = new LessThanOperator(Two, Three);
var predicate = new BooleanExpressionTree(predicateName, predicateExpression);
builder.AddPredicate(predicate);
const string speciesName = "reactant";
var reactant = new SpeciesDescription(speciesName, 2012, locale);
builder.AddSpecies(reactant);
const string reactionName = "test reaction";
var rBuilder = new ReactionInfo.ReactionBuilder(reactionName);
rBuilder.AddReactant(reactant);
var rateExpression = new MultiplyOperator(Ten, new SymbolReference(speciesName));
var reactionRate = new NumericExpressionTree(null, rateExpression);
rBuilder.SetRate(reactionRate);
var reaction = rBuilder.Reaction;
builder.AddReaction(reaction);
var modelInfo = builder.Model;
Console.WriteLine("ModelInfoTests: checking model name...");
Expect(modelInfo.Name == modelName);
Console.WriteLine("ModelInfoTests: checking constraint count (1)...");
Expect(modelInfo.Constraints.Count() == 1);
Console.WriteLine("ModelInfoTests: checking constraint...");
Expect(modelInfo.Constraints.First() == constraint);
/*
* Console.WriteLine("ModelInfoTests: checking event count (1)...");
* Expect(modelInfo.Events.Count() == 1);
* Console.WriteLine("ModelInfoTests: checking event...");
* Expect(modelInfo.Events.First() == testEvent);
*/
Console.WriteLine("ModelInfoTests: checking expression count (3)...");
Expect(modelInfo.Expressions.Count() == 3);
Console.WriteLine("ModelInfoTests: checking expressions...");
var expressions = modelInfo.Expressions.Where(net => net.Name == expressionName).ToList();
Expect(expressions.Count == 1);
Expect(expressions[0] == expressionTree);
Console.WriteLine("ModelInfoTests: checking locale count (1)...");
Expect(modelInfo.Locales.Count() == 1);
Console.WriteLine("ModelInfoTests: checking locale...");
Expect(modelInfo.Locales.First() == locale);
Console.WriteLine("ModelInfoTests: checking observable count (1)...");
Expect(modelInfo.Observables.Count() == 1);
Console.WriteLine("ModelInfoTests: checking observable...");
Expect(modelInfo.Observables.First() == observable);
Console.WriteLine("ModelInfoTests: checking parameter count (1)...");
Expect(modelInfo.Parameters.Count() == 1);
Console.WriteLine("ModelInfoTests: checking parameter...");
Expect(modelInfo.Parameters.First() == parameter);
/*
* Console.WriteLine("ModelInfoTests: checking predicate count (3)...");
* Expect(modelInfo.Predicates.Count() == 3);
*/
Console.WriteLine("ModelInfoTests: checking predicate count (2)...");
Expect(modelInfo.Predicates.Count() == 2);
Console.WriteLine("ModelInfoTests: checking predicate...");
var predicates = modelInfo.Predicates.Where(pi => pi.Name == predicateName).ToList();
Expect(predicates.Count == 1);
Expect(predicates[0] == predicate);
Console.WriteLine("ModelInfoTests: checking reaction count (1)...");
Expect(modelInfo.Reactions.Count() == 1);
Console.WriteLine("ModelInfoTests: checking reaction...");
Expect(modelInfo.Reactions.First() == reaction);
Console.WriteLine("ModelInfoTests: checking species count (1)...");
Expect(modelInfo.Species.Count() == 1);
Console.WriteLine("ModelInfoTests: checking species...");
Expect(modelInfo.Species.First() == reactant);
}
public Constraint(ConstraintInfo info, IDictionary <string, IBoolean> bmap, IDictionary <string, IValue> nmap)
{
_info = info;
_predicate = info.Predicate.ResolveReferences(bmap, nmap);
}
public override ITable Evaluate(IQueryContext context)
{
if (!context.UserCanAlterTable(TableName))
throw new InvalidAccessException(TableName);
var table = context.GetTable(TableName);
if (table == null)
throw new ObjectNotFoundException(TableName);
var tableInfo = table.TableInfo;
var newTableInfo = new TableInfo(tableInfo.TableName);
var checker = ColumnChecker.Default(context, TableName);
bool tableAltered = false;
bool markDropped = false;
for (int n = 0; n < tableInfo.ColumnCount; ++n) {
var column = tableInfo[n];
string columnName = column.ColumnName;
var columnType = column.ColumnType;
var defaultExpression = column.DefaultExpression;
if (Action.ActionType == AlterTableActionType.SetDefault &&
CheckColumnNamesMatch(context, ((SetDefaultAction) Action).ColumnName, columnName)) {
var exp = ((SetDefaultAction) Action).DefaultExpression;
exp = checker.CheckExpression(exp);
defaultExpression = exp;
tableAltered = true;
} else if (Action.ActionType == AlterTableActionType.DropDefault &&
CheckColumnNamesMatch(context, ((DropDefaultAction) Action).ColumnName, columnName)) {
defaultExpression = null;
tableAltered = true;
} else if (Action.ActionType == AlterTableActionType.DropColumn &&
CheckColumnNamesMatch(context, ((DropColumnAction) Action).ColumnName, columnName)) {
// Check there are no referential links to this column
var refs = context.GetTableImportedForeignKeys(TableName);
foreach (var reference in refs) {
CheckColumnConstraint(columnName, reference.ForeignColumnNames, reference.ForeignTable, reference.ConstraintName);
}
// Or from it
refs = context.GetTableForeignKeys(TableName);
foreach (var reference in refs) {
CheckColumnConstraint(columnName, reference.ColumnNames, reference.TableName, reference.ConstraintName);
}
// Or that it's part of a primary key
var primaryKey = context.GetTablePrimaryKey(TableName);
if (primaryKey != null)
CheckColumnConstraint(columnName, primaryKey.ColumnNames, TableName, primaryKey.ConstraintName);
// Or that it's part of a unique set
var uniques = context.GetTableUniqueKeys(TableName);
foreach (var unique in uniques) {
CheckColumnConstraint(columnName, unique.ColumnNames, TableName, unique.ConstraintName);
}
markDropped = true;
tableAltered = true;
}
var newColumn = new ColumnInfo(columnName, columnType);
if (defaultExpression != null)
newColumn.DefaultExpression = defaultExpression;
newColumn.IndexType = column.IndexType;
newColumn.IsNotNull = column.IsNotNull;
// If not dropped then add to the new table definition.
if (!markDropped) {
newTableInfo.AddColumn(newColumn);
}
}
if (Action.ActionType == AlterTableActionType.AddColumn) {
var col = ((AddColumnAction)Action).Column;
checker.CheckExpression(col.DefaultExpression);
var columnName = col.ColumnName;
var columnType = col.ColumnType;
// If column name starts with [table_name]. then strip it off
columnName = checker.StripTableName(TableName.Name, columnName);
if (tableInfo.IndexOfColumn(col.ColumnName) != -1)
throw new InvalidOperationException("The column '" + col.ColumnName + "' is already in the table '" + tableInfo.TableName + "'.");
var newColumn = new ColumnInfo(columnName, columnType) {
IsNotNull = col.IsNotNull,
DefaultExpression = col.DefaultExpression
};
newTableInfo.AddColumn(newColumn);
tableAltered = true;
}
if (Action.ActionType == AlterTableActionType.DropConstraint) {
string constraintName = ((DropConstraintAction)Action).ConstraintName;
int dropCount = context.DropConstraint(TableName, constraintName);
if (dropCount == 0)
throw new InvalidOperationException("Named constraint to drop on table " + TableName + " was not found: " + constraintName);
} else if (Action.ActionType == AlterTableActionType.DropPrimaryKey) {
if (!context.DropPrimaryKey(TableName))
throw new InvalidOperationException("No primary key to delete on table " + TableName);
}
if (Action.ActionType == AlterTableActionType.AddConstraint) {
var constraint = ((AddConstraintAction)Action).Constraint;
bool foreignConstraint = (constraint.ConstraintType == ConstraintType.ForeignKey);
ObjectName refTname = null;
if (foreignConstraint) {
refTname = context.ResolveTableName(constraint.ForeignTable);
}
var columnNames = checker.StripColumnList(TableName.FullName, constraint.ColumnNames);
columnNames = checker.StripColumnList(constraint.ForeignTable.FullName, columnNames);
var expression = checker.CheckExpression(constraint.CheckExpression);
columnNames = checker.CheckColumns(columnNames);
IEnumerable<string> refCols = null;
if (foreignConstraint && constraint.ForeignColumnNames != null) {
var referencedChecker = ColumnChecker.Default(context, refTname);
refCols = referencedChecker.CheckColumns(constraint.ForeignColumnNames);
}
var newConstraint = new ConstraintInfo(constraint.ConstraintType, constraint.TableName, columnNames.ToArray());
if (foreignConstraint) {
newConstraint.ForeignTable = refTname;
newConstraint.ForeignColumnNames = refCols.ToArray();
}
if (constraint.ConstraintType == ConstraintType.Check)
newConstraint.CheckExpression = expression;
context.AddConstraint(TableName, newConstraint);
}
// Alter the existing table to the new format...
if (tableAltered) {
if (newTableInfo.ColumnCount == 0)
throw new InvalidOperationException("Can not ALTER table to have 0 columns.");
context.AlterTable(newTableInfo);
} else {
// If the table wasn't physically altered, check the constraints.
// Calling this method will also make the transaction check all
// deferred constraints during the next commit.
context.CheckConstraints(TableName);
}
// Return '0' if everything successful.
return FunctionTable.ResultTable(context, 0);
}
public static bool EqualsConstraints(ConstraintInfo csrc, ConstraintInfo cdst, DbDiffOptions options, bool checkNames, DbObjectPairing pairing)
{
if (checkNames && !options.IgnoreConstraintNames)
{
if (!EqualNames(csrc.ConstraintName, cdst.ConstraintName, options))
{
return(false);
//if (csrc is PrimaryKeyInfo && cdst is PrimaryKeyInfo) // && (pairing.Source.Dialect.DialectCaps.AnonymousPrimaryKey || pairing.Target.Dialect.DialectCaps.AnonymousPrimaryKey))
//{
// // do nothing
//}
//else
//{
// return false;
//}
}
}
if (csrc.GetType() != cdst.GetType())
{
return(false);
}
if (csrc is IndexInfo srcIndex && cdst is IndexInfo dstIndex)
{
if (srcIndex.IsUnique != dstIndex.IsUnique)
{
return(false);
}
if (srcIndex.IndexType != dstIndex.IndexType)
{
return(false);
}
}
if (csrc is ColumnsConstraintInfo)
{
TableInfo tsrc = pairing.Source.FindTable(csrc.OwnerTable.FullName);
TableInfo tdst = pairing.Target.FindTable(cdst.OwnerTable.FullName);
if (!EqualsColumnRefs(tsrc, tdst, ((ColumnsConstraintInfo)csrc).Columns, ((ColumnsConstraintInfo)cdst).Columns))
{
return(false);
}
//if (!((ColumnsConstraint)csrc).Columns.EqualSequence(((ColumnsConstraint)cdst).Columns)) return false;
if (csrc is ForeignKeyInfo)
{
var fsrc = (ForeignKeyInfo)csrc;
var fdst = (ForeignKeyInfo)cdst;
if (!EqualFullNames(fsrc.RefTableFullName, fdst.RefTableFullName, options))
{
return(false);
}
TableInfo psrc = pairing.Source.FindTable(fsrc.RefTableFullName);
TableInfo pdst = pairing.Target.FindTable(fdst.RefTableFullName);
if (!EqualsColumnRefs(psrc, pdst, fsrc.RefColumns, fdst.RefColumns))
{
return(false);
}
if (fsrc.OnDeleteAction != fdst.OnDeleteAction)
{
return(false);
}
if (fsrc.OnUpdateAction != fdst.OnUpdateAction)
{
return(false);
}
}
//if (csrc is IIndex)
//{
// var isrc = (IndexConstraint)csrc;
// var idst = (IndexConstraint)cdst;
// if (isrc.IsUnique != idst.IsUnique) return false;
//}
}
//if (csrc is CheckConstraint)
//{
// if (((CheckConstraint)csrc).Expression != ((CheckConstraint)cdst).Expression) return false;
//}
return(true);
}
public static ConstraintInfo[] QueryTableCheckExpressions(this ITransaction transaction, ObjectName tableName)
{
var t = transaction.GetTable(SystemSchema.CheckInfoTableName);
// Returns the list indexes where column 3 = table name
// and column 2 = schema name
var objTableName = Field.String(tableName.Name);
var objSchemaName = Field.String(tableName.Parent.Name);
var data = t.SelectRowsEqual(3, objTableName, 2, objSchemaName).ToList();
var checks = new ConstraintInfo[data.Count];
for (int i = 0; i < checks.Length; ++i) {
int rowIndex = data[i];
string name = t.GetValue(rowIndex, 1).Value.ToString();
var deferred = (ConstraintDeferrability) ((SqlNumber) t.GetValue(rowIndex, 5).Value).ToInt16();
SqlExpression expression = null;
// Is the deserialized version available?
if (t.TableInfo.ColumnCount > 6) {
var sexp = (SqlBinary) t.GetValue(rowIndex, 6).Value;
if (!sexp.IsNull) {
try {
// Deserialize the expression
// TODO: expression = (SqlExpression)ObjectTranslator.Deserialize(sexp);
throw new NotImplementedException();
} catch (Exception) {
// We weren't able to deserialize the expression so report the
// error to the log
// TODO:
}
}
}
// Otherwise we need to parse it from the string
if (expression == null) {
expression = SqlExpression.Parse(t.GetValue(rowIndex, 4).Value.ToString());
}
var check = ConstraintInfo.Check(name, tableName, expression);
check.Deferred = deferred;
checks[i] = check;
}
return checks;
}
private void OnGUI()
{
if ((ParticleComponent != null))
{
GUILayout.Label("Cloth Simulation Constraints for VertID " + ParticleComponent.name, EditorStyles.boldLabel);
ClothSimEntity clothSimEntity = ParticleComponent.ClothSimEntity;
Dictionary <string, DynamicPropertiesDef> constraintProperties = clothSimEntity.GetConstraintDefinitions();
Event currentEvent = Event.current;
List <ConstraintInfo> constraintInfo = ParticleComponent.ConstraintParticles;
List <ConstraintDef> defs = ParticleComponent.ParticleInfo.VertInfo.ConstraintsTable.ConstraintsDefs;
Dictionary <string, DynamicPropertiesDef> dynamicProperties = clothSimEntity.GetConstraintDefinitions();
List <string> keys = dynamicProperties.Keys.ToList();
string[] options = keys.ToArray();
int defIndex = 0;
foreach (ConstraintDef def in defs)
{
EditorGUILayout.BeginHorizontal();
m_selected[defIndex] = EditorGUILayout.Toggle(m_selected[defIndex]);
int index = keys.IndexOf(def.ConstraintType);
int newIndex = EditorGUILayout.Popup(index, options, EditorStyles.popup);
if (newIndex != index)
{
def.ConstraintType = options[newIndex];
DynamicPropertiesDef newDef = constraintProperties[def.ConstraintType];
ConstraintInfo info = ParticleComponent.ConstraintParticles.FirstOrDefault(c => def.TargetVert == c.ConstraintParticle.ParticleInfo.VertInfo.VertID);
info.DynamicProperties = newDef;
}
def.TargetVert = EditorGUILayout.IntField("Target Vert ID", def.TargetVert);
EditorGUILayout.EndHorizontal();
++defIndex;
}
ParticleComponent.Selected = m_selected;
EditorGUILayout.BeginHorizontal();
if (GUILayout.Button("Delete"))
{
int index = 0;
List <bool> isSelectedSet = new List <bool>(m_selected);
foreach (bool isSelected in isSelectedSet)
{
if (isSelected)
{
ConstraintDef def = defs[index];
defs.Remove(def);
constraintInfo.Remove(
constraintInfo.First(i => i.ConstraintParticle.ParticleInfo.VertInfo.VertID == def.TargetVert));
m_selected.RemoveAt(index);
}
++index;
}
}
if (GUILayout.Button("Add"))
{
foreach (GameObject gameObject in Selection.gameObjects)
{
DynamicParticleComponent dynamicParticle = gameObject.GetComponent <DynamicParticleComponent>();
ConstraintDef def = new ConstraintDef
{
ConstraintType = options.Count() > 0 ? options[0] : "Invalid",
TargetVert = dynamicParticle.ParticleInfo.VertInfo.VertID
};
defs.Add(def);
ConstraintInfo info = new ConstraintInfo
{
ConstraintParticle = dynamicParticle
};
if (dynamicProperties.TryGetValue(def.ConstraintType, out DynamicPropertiesDef dynamicPropertiesDef))
{
info.DynamicProperties = dynamicPropertiesDef;
}
constraintInfo.Add(info);
m_selected.Add(false);
}
}
if (GUILayout.Button("Change"))
{
int index = 0;
List <bool> isSelectedSet = new List <bool>(m_selected);
foreach (bool isSelected in isSelectedSet)
{
if (isSelected)
{
ConstraintDef def = defs[index];
ConstraintInfo info = constraintInfo.First(i => i.ConstraintParticle.ParticleInfo.VertInfo.VertID == def.TargetVert);
GameObject gameObject = Selection.activeGameObject;
DynamicParticleComponent particle = gameObject.GetComponent <DynamicParticleComponent>();
def.TargetVert = particle.ParticleInfo.VertInfo.VertID;
info.ConstraintParticle = particle;
}
++index;
}
}
EditorGUILayout.EndHorizontal();
}
}
/// <summary>
/// Gets the unique constraint definition string.
/// </summary>
/// <param name="uk"></param>
/// <returns></returns>
protected string GetUniqueConstraintString(ConstraintInfo uk)
{
return string.Format(" unique ({0})", StringUtilities.Combine(uk.Columns, ", "));
}
public List <ConstraintInfo> getConstraintsFromTable(string tablename)
{
String sqlstring =
"SELECT tc.CONSTRAINT_NAME, tc.CONSTRAINT_TYPE, cc.CHECK_CLAUSE, ic.COLUMN_NAME, tc2.TABLE_NAME, ccu.COLUMN_NAME " +
"FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc " +
"LEFT OUTER JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc ON tc.CONSTRAINT_NAME = cc.CONSTRAINT_NAME " +
"LEFT OUTER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ic ON tc.CONSTRAINT_NAME = ic.CONSTRAINT_NAME " +
"LEFT OUTER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc ON tc.CONSTRAINT_NAME = rc.CONSTRAINT_NAME " +
"LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON rc.UNIQUE_CONSTRAINT_NAME = tc2.CONSTRAINT_NAME " +
"LEFT OUTER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc2.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME " +
"WHERE tc.TABLE_NAME = '" + tablename + "' AND tc.CONSTRAINT_TYPE != 'PRIMARY KEY'; ";
sqlcommand = new SqlCommand(sqlstring, conn);
SqlDataReader reader = sqlcommand.ExecuteReader();
String column_name;
String constraint_name;
String condition;
String type;
List <ConstraintInfo> constraints = new List <ConstraintInfo>();
List <String> constraintscol = new List <String>();
while (reader.Read())
{
try
{
type = reader.GetString(1);
if (type.Equals("FOREIGN KEY"))
{
if (!constraintscol.Contains(
reader.GetString(3) + reader.GetString(5) + reader.GetString(4)))
{
constraintscol.Add(reader.GetString(3) + reader.GetString(5) + reader.GetString(4));
try
{
int index = constraints.FindIndex(ConstraintInfo =>
ConstraintInfo.constraintName == reader.GetString(0));
ConstraintInfo ci = constraints[index];
constraints.RemoveAt(index);
if (!ci.columnName.Contains(reader.GetString(3)))
{
ci.columnName = ci.columnName + "," + reader.GetString(3);
}
if (!ci.FKcolumnName.Contains(reader.GetString(5)))
{
ci.FKcolumnName = ci.FKcolumnName + "," + reader.GetString(5);
}
constraints.Add(ci);
}
catch (ArgumentOutOfRangeException)
{
constraints.Add(new ConstraintInfo(ConstraintType.ForeignKey, reader.GetString(0),
"", reader.GetString(3), reader.GetString(4), reader.GetString(5)));
}
}
}
else if (type.Equals("UNIQUE"))
{
constraints.Add(new ConstraintInfo(ConstraintType.UniqueKey, reader.GetString(0), "",
reader.GetString(3), "", ""));
}
else if (type.Equals("CHECK"))
{
constraints.Add(new ConstraintInfo(ConstraintType.Check, reader.GetString(0),
reader.GetString(2), reader.GetString(3), "", ""));
}
}
catch (System.Data.SqlTypes.SqlNullValueException)
{
//break;
}
}
reader.Close();
return(constraints);
}
public static ConstraintInfo[] QueryTableForeignKeys(this ITransaction transaction, ObjectName tableName)
{
var t = transaction.GetTable(SystemSchema.ForeignKeyInfoTableName);
var t2 = transaction.GetTable(SystemSchema.ForeignKeyColumnsTableName);
// Returns the list indexes where column 3 = table name
// and column 2 = schema name
var objTableName = DataObject.String(tableName.Name);
var objSchema = DataObject.String(tableName.Parent.Name);
var data = t.SelectRowsEqual(3, objTableName, 2, objSchema).ToList();
var groups = new ConstraintInfo[data.Count];
for (int i = 0; i < data.Count; ++i) {
int rowIndex = data[i];
// The foreign key id
var id = t.GetValue(rowIndex, 0);
// The referenced table
var refTableName = new ObjectName(
new ObjectName(t.GetValue(rowIndex, 4).Value.ToString()),
t.GetValue(rowIndex, 5).Value.ToString());
// Select all records with equal id
var cols = t2.SelectRowsEqual(0, id).ToList();
var name = t.GetValue(rowIndex, 1).Value.ToString();
var updateRule = (ForeignKeyAction) ((SqlNumber) t.GetValue(rowIndex, 6).Value).ToInt32();
var deleteRule = (ForeignKeyAction) ((SqlNumber) t.GetValue(rowIndex, 7).Value).ToInt32();
var deferred = (ConstraintDeferrability) ((SqlNumber) t.GetValue(rowIndex, 8).Value).ToInt16();
;
int colsSize = cols.Count;
string[] keyCols = new string[colsSize];
string[] refCols = new string[colsSize];
for (int n = 0; n < colsSize; ++n) {
for (int p = 0; p < colsSize; ++p) {
int colsIndex = cols[p];
if (t2.GetValue(colsIndex, 3) == n) {
keyCols[n] = t2.GetValue(colsIndex, 1).Value.ToString();
refCols[n] = t2.GetValue(colsIndex, 2).Value.ToString();
break;
}
}
}
var constraint = ConstraintInfo.ForeignKey(name, tableName, keyCols, refTableName, refCols);
constraint.OnDelete = deleteRule;
constraint.OnUpdate = updateRule;
constraint.Deferred = deferred;
groups[i] = constraint;
}
return groups;
}
