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; }