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);
}
}
public ITable Execute(Query query, Expression expression)
{
// Create the QueryProcessor
QueryProcessor processor = new QueryProcessor(transaction);
// If it's a select,
if (expression is SelectExpression) {
QueryOptimizer optimizer = new QueryOptimizer(transaction);
expression = optimizer.SubstituteParameters(expression, query);
expression = optimizer.Qualify(expression);
expression = optimizer.Optimize(expression);
// Execute the query,
return processor.Execute(expression);
}
// Set the parameter as the base table, and the base rowid (the
// parameters table only has 1 row).
processor.PushTable(new QueryParametersTable(query));
processor.UpdateTableRow(new RowId(0));
// Otherwise it must be an interpretable function
if (expression is FunctionExpression) {
string fun_name = (string)expression.GetArgument("name");
if (fun_name.Equals("create_table"))
return CreateTable(expression);
/*
TODO:
if (fun_name.Equals("drop_table"))
return DropTable(processor, expression);
if (fun_name.Equals("create_index"))
return CreateIndex(processor, expression);
if (fun_name.Equals("drop_index"))
return DropIndex(processor, expression);
if (fun_name.Equals("explain_expression"))
return ExplainExpression(expression);
*/
}
throw new NotSupportedException();
}
private ITable CreateTable(Expression expression)
{
// create_table has the following arguments,
// ( table_name, declarations (columns and constraints),
// [ check_expression ] )
// The table name
TableName tableName = (TableName)((Expression)expression.GetArgument("arg0")).GetArgument("name");
tableName = QualifyTableName(tableName);
// Does the table exist already?
if (transaction.TableExists(tableName)) {
// If the 'if_not_exists' argument is present, we don't generate an
// error, simply returning '0'. Otherwise we generate an error.
if (expression.GetArgument("if_not_exists") != null)
return DMLResult(0);
// Otherwise generate an exception
throw new ApplicationException("Table '" + tableName + "' already exists");
}
// We aren't allowed to create tables in the system schema
if (tableName.Schema.Equals(SystemTableNames.SystemSchema)) {
throw new ApplicationException("Unable to create table in the " + SystemTableNames.SystemSchema + " schema");
}
// The declarations op
Expression declarations = (Expression)expression.GetArgument("arg1");
// An optimizer for qualifying operations
QueryOptimizer optimizer = new QueryOptimizer(transaction);
// Split out the column declarations
List<Expression> columnDeclares = new List<Expression>();
List<Expression> constraintDeclares = new List<Expression>();
int arg_count = (int)declarations.GetArgument("param_count");
for (int i = 0; i < arg_count; ++i) {
Expression decl = (Expression)declarations.GetArgument("arg" + i);
// Is this a column declaration?
if (decl.GetArgument("name").Equals("column_declaration")) {
columnDeclares.Add(decl);
} else {
constraintDeclares.Add(decl);
}
}
// Create the table
SystemTable table = transaction.CreateTable(tableName);
try {
// For each column declare
foreach (Expression col in columnDeclares) {
// Column name
Variable colName = (Variable)((Expression)col.GetArgument("arg0")).GetArgument("var");
if (colName.TableName != null &&
!colName.TableName.Equals(tableName)) {
throw new ApplicationException("Invalid column name " + colName);
}
// Declared type
SqlType colType = (SqlType)col.GetArgument("arg1");
// Does the column have a default expression?
Expression defaultExpr = null;
int n = 2;
if (col.GetArgument("has_default_exp") != null) {
defaultExpr = (Expression)col.GetArgument("arg2");
++n;
}
// Any remaining arguments will be constraints
bool notNull = false;
bool unique = false;
while (true) {
Expression cons = (Expression)col.GetArgument("arg" + n);
if (cons == null)
break;
SqlObject constraint = (SqlObject)cons.GetArgument("static");
string constraint_str = constraint.ToString();
if (constraint_str.Equals("NOT NULL")) {
notNull = true;
} else if (constraint_str.Equals("NULL")) {
notNull = false;
} else if (constraint_str.Equals("UNIQUE")) {
unique = true;
} else {
throw new ApplicationException("Unknown column constraint '" + constraint_str + "'");
}
++n;
}
// If we have a unique constraint, add it to the constraint list
if (unique) {
FunctionExpression uniqueConstraint = new FunctionExpression("constraint_unique");
FunctionExpression basic_ref = new FunctionExpression("basic_var_list");
basic_ref.Parameters.Add(new FetchVariableExpression(colName));
uniqueConstraint.Parameters.Add(basic_ref);
uniqueConstraint.Parameters.Add(new FetchStaticExpression("deferrable"));
uniqueConstraint.Parameters.Add(new FetchStaticExpression("initially immediate"));
constraintDeclares.Add(uniqueConstraint);
}
// Add the column to the table
table.Columns.Add(colName.Name, colType, notNull);
// Insert the default expression into the system table for defaults
if (defaultExpr != null) {
// Qualify the default expression. This will generate an error if
// the expression can not be qualified.
defaultExpr = optimizer.Qualify(defaultExpr);
// The source string representation of the expression
string sourceStr = (string)defaultExpr.GetArgument("source");
if (sourceStr == null)
throw new NullReferenceException();
// Serialize the expression graph to a byte object
SqlValue serializedExpression = SqlValue.Serialize(defaultExpr);
// Update the default expression table
QueryResult defaultTable = TableUpdatable(SystemTableNames.DefaultColumnExpression);
defaultTable.BeginInsertRow();
defaultTable.Update("table_schema", table.Name.Schema);
defaultTable.Update("table_name", table.Name.Name);
defaultTable.Update("column", colName.Name);
defaultTable.Update("default_source", sourceStr);
defaultTable.Update("default_bin", serializedExpression.ToBinary());
defaultTable.InsertRow();
}
}
// For each constraint declaration,
foreach (Expression constraint in constraintDeclares) {
// Add the table constraint
AddTableConstraint(optimizer, tableName, constraint);
}
} catch (Exception) {
// Drop the table and cleanup if an sql exception was generated.
transaction.DropTable(tableName);
throw;
}
// Success,
return DMLResult(1);
}