private void AddTableConstraint(QueryOptimizer optimizer, TableName tableName, Expression constraint) { // The id of the table we are adding this constraint for long table_id = transaction.GetTableId(tableName); // The name of the constraint or null if no label defined for the // constraint. string constraint_name = (string)constraint.GetArgument("constraint_name"); // The constraint function type string constraint_fun_type = (string)constraint.GetArgument("name"); // If it's a primary key constraint, if (constraint_fun_type.Equals("constraint_primary_key")) { // The basic var list Expression var_list = (Expression) constraint.GetArgument("arg0"); // Constraint check control Expression deferrability = (Expression) constraint.GetArgument("arg1"); Expression init_check = (Expression) constraint.GetArgument("arg2"); // Make sure the var list qualifies var_list = optimizer.QualifyAgainstTable(var_list, tableName); // Check a primary key isn't already defined IDbCommand command = CreateDbCommand( " SELECT * \n " + " FROM " + SystemTableNames.ConstraintsUnique + " \n" + " WHERE object_id = ? \n" + " AND primary_key = true \n"); command.Parameters.Add(tableName.Schema); command.Parameters.Add(tableName.Name); IDataReader result = command.ExecuteReader(); // Already a primary key defined on the object if (result.Read()) throw new ApplicationException("PRIMARY KEY constraint already defined on " + tableName); // Update the table QueryResult uTable = TableUpdatable(SystemTableNames.ConstraintsUnique); uTable.BeginInsertRow(); uTable.Update("table_schema", tableName.Schema); uTable.Update("table_name", tableName.Name); uTable.Update("name", constraint_name); uTable.Update("deferred", IsDeferred(init_check)); uTable.Update("deferrable", IsDeferrable(deferrability)); uTable.Update("primary_key", true); uTable.InsertRow(); // Add the var list to the column set AddToConstraintColumns(tableName, constraint_name, var_list, null); } // If it's a foreign key constraint, else if (constraint_fun_type.Equals("constraint_foreign_key")) { // The var list Expression var_list = (Expression) constraint.GetArgument("arg0"); // The table the foreign key references Expression ref_table = (Expression) constraint.GetArgument("arg1"); // Either 6 or 7 parameters int param_count = (int)constraint.GetArgument("param_count"); // The var list on the referenced table Expression foreign_var_list; int n; if (param_count == 6) { n = 2; foreign_var_list = null; } else if (param_count == 7) { n = 3; foreign_var_list = (Expression) constraint.GetArgument("arg2"); } else { throw new ApplicationException("Unexpected parameter count"); } // Trigger actions Expression updateAction = (Expression) constraint.GetArgument("arg" + n + 0); Expression deleteAction = (Expression) constraint.GetArgument("arg" + n + 1); // Constraint check control Expression deferrability = (Expression) constraint.GetArgument("arg" + n + 2); Expression init_check = (Expression) constraint.GetArgument("arg" + n + 3); // Make sure the var list qualifies var_list = optimizer.QualifyAgainstTable(var_list, tableName); // Qualify the reference table ref_table = optimizer.Qualify(ref_table); TableName refTableName = (TableName) ref_table.GetArgument("name"); // Walk the table reference graph and ensure no table is visited more // than once (there are no circular dependancies created). List<TableName> table_set = new List<TableName>(); table_set.Add(tableName); CheckNoCircularDependancy(table_set, refTableName); // The number of parameters in the foreign key int var_param_count = (int)var_list.GetArgument("param_count"); // If there's a foreign variable list if (foreign_var_list != null) { // Qualify it against the reference table foreign_var_list = optimizer.QualifyAgainstTable(foreign_var_list, refTableName); // Check the number of keys are equal if (((int)foreign_var_list.GetArgument("param_count")) != var_param_count) { throw new ApplicationException("Key element count mismatch in FOREIGN KEY constraint"); } AddToConstraintColumns(refTableName, constraint_name, foreign_var_list, "REFERENCE"); } else { // Foreign list is null, so we need to look up the primary key IDbCommand command = CreateDbCommand( " SELECT s.column_name \n " + " FROM " + SystemTableNames.ConstraintsForeign + " c, \n" + " " + SystemTableNames.ColumnSet + " s \n" + " WHERE c.table_schema = ? \n" + " AND c.table_name = ? \n" + " AND c.primary_key = true \n" + " AND c.table_schema = s.table_schema \n " + " AND c.table_name = s.table_name \n" + " AND c.name = s.constraint_name \n" + " AND c.name = ? \n" + "ORDER BY s.seq_no" ); command.Parameters.Add(refTableName.Schema); command.Parameters.Add(refTableName.Name); command.Parameters.Add(constraint_name); IDataReader result = command.ExecuteReader(); if (!result.Read()) { throw new ApplicationException("Referenced table '" + refTableName + "' does not have a primary key defined"); } List<string> ref_col_names = new List<string>(); ref_col_names.Add(result.GetString(0)); while (result.Read()) { ref_col_names.Add(result.GetString(1)); } // Check the number of keys are equal if (ref_col_names.Count != var_param_count) { throw new ApplicationException("Key element count mismatch in FOREIGN KEY constraint"); } // Add the referenced column names to the column set AddToConstraintColumns(refTableName, constraint_name, ref_col_names, "REFERENCE"); } // Add the var list to the column set long col_set_id = AddToColumnsSystemTable(var_list); // Update the table QueryResult uTable = TableUpdatable(SystemTableNames.ConstraintsForeign); uTable.BeginInsertRow(); uTable.Update("table_schema", tableName.Schema); uTable.Update("table_name", tableName.Name); uTable.Update("name", constraint_name); uTable.Update("column_set_id", col_set_id); uTable.Update("ref_schema", refTableName.Schema); uTable.Update("ref_table_name", refTableName.Name); uTable.Update("update_action", GetActionString(updateAction)); uTable.Update("delete_action", GetActionString(deleteAction)); uTable.Update("deferred", IsDeferred(init_check)); uTable.Update("deferrable", IsDeferrable(deferrability)); uTable.InsertRow(); } // If it's a unique constraint else if (constraint_fun_type.Equals("constraint_unique")) { // The basic var list Expression var_list = (Expression)constraint.GetArgument("arg0"); // Constraint check control Expression deferrability = (Expression)constraint.GetArgument("arg1"); Expression init_check = (Expression)constraint.GetArgument("arg2"); // Make sure the var list qualifies var_list = optimizer.QualifyAgainstTable(var_list, tableName); // Add the var list to the column set long col_set_id = AddToColumnsSystemTable(var_list); // Update the table QueryResult uTable = TableUpdatable(SystemTableNames.ConstraintsUnique); uTable.BeginInsertRow(); uTable.Update("object_id", table_id); uTable.Update("name", constraint_name); uTable.Update("column_set_id", col_set_id); uTable.Update("deferred", IsDeferred(init_check)); uTable.Update("deferrable", IsDeferrable(deferrability)); uTable.Update("primary_key", false); uTable.InsertRow(); } // If it's a generic check else if (constraint_fun_type.Equals("constraint_check")) { // The check expression, Expression check_expr = (Expression)constraint.GetArgument("arg0"); // Constraint check control Expression deferrability = (Expression)constraint.GetArgument("arg1"); Expression init_check = (Expression)constraint.GetArgument("arg2"); // Qualify the check expression check_expr = optimizer.QualifyAgainstTable(check_expr, tableName); // The source string representation of the expression String source_str = (String)constraint.GetArgument("source"); if (source_str == null) throw new NullReferenceException(); // Serialize the expression graph to a binary object SqlValue serializedExpression = SqlValue.Serialize(check_expr); // Update the check constraint table QueryResult uTable = TableUpdatable(SystemTableNames.ConstraintsCheck); uTable.BeginInsertRow(); uTable.Update("name", constraint_name); uTable.Update("check_source", source_str); uTable.Update("check_bin", serializedExpression.ToBinary()); uTable.Update("deferred", IsDeferred(init_check)); uTable.Update("deferrable", IsDeferrable(deferrability)); uTable.InsertRow(); } else { throw new ApplicationException("Unknown constraint type: " + constraint_fun_type); } }