public static void AddConstraint(this IQueryContext context, ObjectName tableName, ConstraintInfo constraintInfo)
{
if (constraintInfo.ConstraintType == ConstraintType.PrimaryKey) {
var columnNames = constraintInfo.ColumnNames;
if (columnNames.Length > 1)
throw new ArgumentException();
context.AddPrimaryKey(tableName, columnNames[0], constraintInfo.ConstraintName);
} else if (constraintInfo.ConstraintType == ConstraintType.Unique) {
context.AddUniqueKey(tableName, constraintInfo.ColumnNames, constraintInfo.ConstraintName);
} else if (constraintInfo.ConstraintType == ConstraintType.Check) {
context.AddCheck(tableName, constraintInfo.CheckExpression, constraintInfo.ConstraintName);
} else if (constraintInfo.ConstraintType == ConstraintType.ForeignKey) {
context.AddForeignKey(tableName, constraintInfo.ColumnNames, constraintInfo.ForeignTable,
constraintInfo.ForeignColumnNames, constraintInfo.OnDelete, constraintInfo.OnUpdate, constraintInfo.ConstraintName);
}
}
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 = DataObject.String(tableName.Name);
var objSchemaName = DataObject.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 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 = DataObject.String(refTableName.Name);
var objRefSchema = DataObject.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;
}
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 = DataObject.String(tableName.Name);
var objSchemaName = DataObject.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 e) {
// 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;
}
public static void AddConstraint(this IQueryContext context, ObjectName tableName, ConstraintInfo constraintInfo)
{
throw new NotImplementedException();
}
public static ConstraintInfo ForeignKey(string constraintName, ObjectName tableName, string[] columnNames,
ObjectName refTable, string[] refColumns)
{
if (tableName == null)
throw new ArgumentNullException("tableName");
if (refTable == null)
throw new ArgumentNullException("refTable");
if (columnNames == null || columnNames.Length == 0)
throw new ArgumentException("At least one column is required", "columnNames");
if (refColumns == null || refColumns.Length == 0)
throw new ArgumentException("At least one referenced column is required.", "refColumns");
if (columnNames.Length != refColumns.Length)
throw new ArgumentException("The number of columns in the constraint must match the number of columns referenced.");
var constraint = new ConstraintInfo(constraintName, ConstraintType.ForeignKey, tableName, columnNames);
constraint.ForeignTable = refTable;
constraint.ForeignColumnNames = refColumns;
return constraint;
}
