Example #1
0
        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);
            }
        }