private TableInfo CreateTableInfo(IRequest context)
{
var tableName = context.Access().ResolveTableName(TableName);
var idColumnCount = Columns.Count(x => x.IsIdentity);
if (idColumnCount > 1)
{
throw new InvalidOperationException("More than one IDENTITY column specified.");
}
bool ignoreCase = context.Query.IgnoreIdentifiersCase();
var columnChecker = new TableColumnChecker(Columns, ignoreCase);
var tableInfo = new TableInfo(tableName);
foreach (var column in Columns)
{
var columnInfo = CreateColumnInfo(context, tableName.Name, column, columnChecker);
if (column.IsIdentity)
{
columnInfo.DefaultExpression = SqlExpression.FunctionCall("UNIQUEKEY", new SqlExpression[] {
SqlExpression.Constant(tableName.ToString())
});
}
tableInfo.AddColumn(columnInfo);
}
return(tableInfo);
}
private static void CreateTestTable(IQuery query, string testName)
{
var tableName = ObjectName.Parse("APP.test_table");
query.Access().CreateTable(table => {
table
.Named(tableName)
.WithColumn(column => column.Named("id")
.HavingType(PrimitiveTypes.Integer())
.WithDefault(SqlExpression.FunctionCall("UNIQUEKEY",
new SqlExpression[] { SqlExpression.Constant(tableName.FullName) })))
.WithColumn("first_name", PrimitiveTypes.String())
.WithColumn("last_name", PrimitiveTypes.String())
.WithColumn("birth_date", PrimitiveTypes.DateTime())
.WithColumn(column => column
.Named("active")
.HavingType(PrimitiveTypes.Boolean())
.NotNull(testName.Equals("NotNullColumnViolation")));
if (testName.EndsWith("WithLob"))
{
table.WithColumn("bio", PrimitiveTypes.Clob(2048));
}
else if (testName.EndsWith("WithUserType"))
{
var userType = query.Access().ResolveUserType("type1");
table.WithColumn("user_obj", userType);
}
});
query.Access().AddPrimaryKey(tableName, "id", "PK_TEST_TABLE");
}
private static void CreateTestTable(IQuery query)
{
var tableName1 = ObjectName.Parse("APP.test_table");
query.Access().CreateTable(table => table
.Named(tableName1)
.WithColumn(column => column
.Named("id")
.HavingType(PrimitiveTypes.Integer())
.WithDefault(SqlExpression.FunctionCall("UNIQUEKEY",
new SqlExpression[] { SqlExpression.Constant(tableName1.FullName) }))
.NotNull())
.WithColumn("first_name", PrimitiveTypes.String())
.WithColumn("last_name", PrimitiveTypes.String())
.WithColumn("birth_date", PrimitiveTypes.DateTime())
.WithColumn("active", PrimitiveTypes.Boolean()));
query.Session.Access().AddPrimaryKey(tableName1, "id", "PK_TEST_TABLE");
query.Access().CreateTable(table => table
.Named("APP.test_table2")
.WithColumn("person_id", PrimitiveTypes.Integer())
.WithColumn("value", PrimitiveTypes.Boolean()));
var body = new PlSqlBlockStatement();
body.Statements.Add(new CallStatement(ObjectName.Parse("system.output"), new[] {
new InvokeArgument(SqlExpression.Constant("One row was inserted"))
}));
var procedureInfo = new PlSqlProcedureInfo(ObjectName.Parse("APP.proc1"), new RoutineParameter[0], body);
query.Access().CreateObject(procedureInfo);
}
public void SimpleCursorForLoop()
{
var query = (SqlQueryExpression)SqlExpression.Parse("SELECT * FROM table1");
var block = new PlSqlBlockStatement();
block.Declarations.Add(new DeclareCursorStatement("c1", query));
var loop = new CursorForLoopStatement("i", "c1");
loop.Statements.Add(new DeclareVariableStatement("a", PrimitiveTypes.String()));
loop.Statements.Add(new AssignVariableStatement(SqlExpression.VariableReference("a"),
SqlExpression.FunctionCall("cast",
new SqlExpression[] { SqlExpression.VariableReference("i"), SqlExpression.Constant("varchar") })));
loop.Statements.Add(
new ConditionStatement(SqlExpression.Equal(SqlExpression.VariableReference("i"), SqlExpression.Constant(50)),
new SqlStatement[] { new ReturnStatement(SqlExpression.VariableReference("a")) }));
block.Statements.Add(new OpenStatement("c1"));
block.Statements.Add(loop);
var result = AdminQuery.ExecuteStatement(block);
Assert.IsNotNull(result);
Assert.AreEqual(StatementResultType.Result, result.Type);
var value = result.Result.GetValue(0, 0);
Assert.IsNotNull(value);
Assert.IsFalse(Field.IsNullField(value));
}
public void InsertWithUserType()
{
var tableName = ObjectName.Parse("APP.test_table");
var columns = new[] { "first_name", "last_name", "active", "user_obj" };
var values = new List <SqlExpression[]> {
new SqlExpression[] {
SqlExpression.Constant("Antonello"),
SqlExpression.Constant("Provenzano"),
SqlExpression.Constant(true),
SqlExpression.FunctionCall("type1", new SqlExpression[] {
SqlExpression.Constant("test1"),
SqlExpression.Constant(1),
})
},
new SqlExpression[] {
SqlExpression.Constant("Mart"),
SqlExpression.Constant("Roosmaa"),
SqlExpression.Constant(false),
SqlExpression.FunctionCall("type1", new SqlExpression[] {
SqlExpression.Constant("test2"),
SqlExpression.Constant(3),
})
}
};
var count = AdminQuery.Insert(tableName, columns, values.ToArray());
Assert.AreEqual(2, count);
var table = AdminQuery.Access().GetTable(tableName);
Assert.IsNotNull(table);
Assert.AreEqual(2, table.RowCount);
}
public IExpressionBuilder Function(ObjectName functionName, params SqlExpression[] args)
{
expression = SqlExpression.FunctionCall(functionName, args);
VerifyUnary();
return(this);
}
public void FunctionCall_NoArgs()
{
var func = SqlExpression.FunctionCall("test");
SerializeAndAssert(func, (serialized, deserialized) => {
Assert.AreEqual(SqlExpressionType.FunctionCall, deserialized.ExpressionType);
Assert.IsNotNull(deserialized.FunctioName);
Assert.IsEmpty(deserialized.Arguments);
});
}
private static SqlExpression VisitFunctionCall(SqlFunctionCallExpressionNode node)
{
var args = new List <SqlExpression>();
if (node.Arguments != null)
{
args.AddRange(node.Arguments.Select(Build));
}
return(SqlExpression.FunctionCall(node.FunctionName, args.ToArray()));
}
public void Conditional()
{
var test = SqlExpression.FunctionCall("test");
var trueExp = SqlExpression.Constant(45);
var falseExp = SqlExpression.FunctionCall("testReturn");
var condition = SqlExpression.Conditional(test, trueExp, falseExp);
SerializeAndAssert(condition, (serialized, deserialized) => {
Assert.AreEqual(SqlExpressionType.Conditional, deserialized.ExpressionType);
Assert.IsInstanceOf <SqlFunctionCallExpression>(deserialized.TestExpression);
Assert.IsInstanceOf <SqlConstantExpression>(deserialized.TrueExpression);
Assert.IsInstanceOf <SqlFunctionCallExpression>(deserialized.FalseExpression);
});
}
private void CreateTestTable(IQuery context)
{
var tableInfo = new TableInfo(ObjectName.Parse("APP.people"));
var idColumn = tableInfo.AddColumn("id", PrimitiveTypes.Integer());
idColumn.DefaultExpression = SqlExpression.FunctionCall("UNIQUEKEY",
new SqlExpression[] { SqlExpression.Constant(tableInfo.TableName.FullName) });
tableInfo.AddColumn("first_name", PrimitiveTypes.String());
tableInfo.AddColumn("last_name", PrimitiveTypes.String());
tableInfo.AddColumn("birth_date", PrimitiveTypes.DateTime());
tableInfo.AddColumn("active", PrimitiveTypes.Boolean());
context.CreateTable(tableInfo);
context.AddPrimaryKey(tableInfo.TableName, "id", "PK_PEOPLE_TABLE");
}
public static Field SelectFunction(this IRequest request, ObjectName functionName, params SqlExpression[] args)
{
var funcExp = SqlExpression.FunctionCall(functionName, args);
var query = new SqlQueryExpression(new [] { new SelectColumn(funcExp) });
var result = request.Select(query);
var row = result.FirstOrDefault();
if (row == null)
{
throw new InvalidOperationException();
}
return(row.GetValue(0));
}
private void CreateTestTable(IQuery query)
{
var tableName = ObjectName.Parse("APP.test_table");
query.Access().CreateTable(table => table
.Named(tableName)
.WithColumn(column => column
.Named("id")
.HavingType(PrimitiveTypes.Integer())
.WithDefault(SqlExpression.FunctionCall("UNIQUEKEY", new SqlExpression[] {
SqlExpression.Constant("APP.test_table")
})))
.WithColumn("name", PrimitiveTypes.String()));
query.Access().AddPrimaryKey(tableName, "id");
}
private void CreateTable(IQuery query)
{
var tableName = ObjectName.Parse("APP.persons");
query.Access().CreateTable(table => table
.Named(tableName)
.WithColumn(column => column
.Named("id")
.HavingType(PrimitiveTypes.BigInt())
.NotNull()
.WithDefault(SqlExpression.FunctionCall("UNIQUEKEY", new SqlExpression[] {
SqlExpression.Constant(tableName.FullName)
})))
.WithColumn("name", PrimitiveTypes.String())
.WithColumn("age", PrimitiveTypes.Integer()));
}
private static void CreateTestTable(IQuery context)
{
var tableName = ObjectName.Parse("APP.test_table");
context.Access().CreateTable(table => table
.Named(tableName)
.WithColumn(column => column
.Named("id")
.HavingType(PrimitiveTypes.Integer())
.WithDefault(SqlExpression.FunctionCall("UNIQUEKEY",
new SqlExpression[] { SqlExpression.Constant(tableName.FullName) })))
.WithColumn("first_name", PrimitiveTypes.String())
.WithColumn("last_name", PrimitiveTypes.String())
.WithColumn("birth_date", PrimitiveTypes.DateTime())
.WithColumn("active", PrimitiveTypes.Boolean()));
context.Session.Access().AddPrimaryKey(tableName, "id", "PK_TEST_TABLE");
}
private Field SelectAggregate(string functionName, params SqlExpression[] args)
{
var column = new SelectColumn(SqlExpression.FunctionCall(functionName, args));
var query = new SqlQueryExpression(new[] { column });
query.FromClause.AddTable("APP.test_table");
var result = AdminQuery.Select(query);
var row = result.FirstOrDefault();
if (row == null)
{
throw new InvalidOperationException();
}
return(row.GetValue(0));
}
public void SimpleForLoop()
{
var loop = new ForLoopStatement("i", SqlExpression.Constant(0), SqlExpression.Constant(200));
loop.Statements.Add(new DeclareVariableStatement("a", PrimitiveTypes.String()));
loop.Statements.Add(new AssignVariableStatement(SqlExpression.VariableReference("a"),
SqlExpression.FunctionCall("cast",
new SqlExpression[] { SqlExpression.VariableReference("i"), SqlExpression.Constant("varchar") })));
loop.Statements.Add(
new ConditionStatement(SqlExpression.Equal(SqlExpression.VariableReference("i"), SqlExpression.Constant(200)),
new SqlStatement[] { new ReturnStatement(SqlExpression.VariableReference("a")) }));
var result = AdminQuery.ExecuteStatement(loop);
Assert.IsNotNull(result);
Assert.AreEqual(StatementResultType.Result, result.Type);
var value = result.Result.GetValue(0, 0);
Assert.IsNotNull(value);
Assert.IsFalse(Field.IsNullField(value));
// TODO: the context should return the value of RETURN statement
}
protected override bool OnSetUp(string testName, IQuery query)
{
var tableName1 = ObjectName.Parse("APP.test_table");
query.Access().CreateTable(table => table
.Named(tableName1)
.WithColumn(column => column
.Named("id")
.HavingType(PrimitiveTypes.Integer())
.WithDefault(SqlExpression.FunctionCall("UNIQUEKEY",
new SqlExpression[] { SqlExpression.Constant(tableName1.FullName) })))
.WithColumn("first_name", PrimitiveTypes.String())
.WithColumn("last_name", PrimitiveTypes.String())
.WithColumn("birth_date", PrimitiveTypes.DateTime())
.WithColumn("active", PrimitiveTypes.Boolean()));
query.Access().AddPrimaryKey(tableName1, "id", "PK_TEST_TABLE");
var tableName2 = ObjectName.Parse("APP.test_table2");
query.Access().CreateTable(table => table
.Named(tableName2)
.WithColumn(column => column
.Named("person_id")
.HavingType(PrimitiveTypes.Integer())
.NotNull(testName.Equals("SetNullOnDeleteViolation")))
.WithColumn("value", PrimitiveTypes.Boolean()));
if (testName == "DropConstraint" ||
testName == "DropReferencedColumn")
{
query.Session.Access().AddForeignKey(tableName2, new string[] { "person_id" }, tableName1,
new[] { "id" }, ForeignKeyAction.Cascade, ForeignKeyAction.Cascade, "FK_1");
}
return(true);
}
private static void CreateTestTable(string testName, IQuery context)
{
var tableName1 = ObjectName.Parse("APP.test_table");
context.Access().CreateTable(table => table
.Named(tableName1)
.WithColumn(column => column
.Named("id")
.HavingType(PrimitiveTypes.Integer())
.WithDefault(SqlExpression.FunctionCall("UNIQUEKEY",
new SqlExpression[] { SqlExpression.Constant(tableName1.FullName) })))
.WithColumn("first_name", PrimitiveTypes.String())
.WithColumn("last_name", PrimitiveTypes.String())
.WithColumn("birth_date", PrimitiveTypes.DateTime())
.WithColumn("active", PrimitiveTypes.Boolean()));
context.Session.Access().AddPrimaryKey(tableName1, "id", "PK_TEST_TABLE");
if (testName.EndsWith("ConstraintCheck") ||
testName.EndsWith("Violation"))
{
var tableName2 = ObjectName.Parse("APP.test_table2");
context.Access().CreateTable(table => {
table
.Named(tableName2)
.WithColumn(column => column
.Named("id")
.HavingType(PrimitiveTypes.Integer())
.WithDefault(SqlExpression.FunctionCall("UNIQUEKEY",
new SqlExpression[] { SqlExpression.Constant(tableName2.FullName) })));
if (testName.StartsWith("SetDefault"))
{
table.WithColumn(column => column
.Named("person_id")
.HavingType(PrimitiveTypes.Integer())
.WithDefault(SqlExpression.Constant(0)));
}
else
{
table.WithColumn(column => column
.Named("person_id")
.HavingType(PrimitiveTypes.Integer())
.NotNull(testName.EndsWith("Violation")));
}
table.WithColumn("dept_no", PrimitiveTypes.Integer());
});
context.Access().AddPrimaryKey(tableName2, "id", "PK_TEST_TABLE2");
ForeignKeyAction?onUpdate = null;
if (testName.StartsWith("SetNull"))
{
onUpdate = ForeignKeyAction.SetNull;
}
else if (testName.StartsWith("SetDefault"))
{
onUpdate = ForeignKeyAction.SetDefault;
}
else if (testName.StartsWith("Cascade"))
{
onUpdate = ForeignKeyAction.Cascade;
}
if (onUpdate != null)
{
context.Access()
.AddForeignKey(tableName2, new[] { "person_id" }, tableName1, new[] { "id" }, ForeignKeyAction.NoAction,
onUpdate.Value, "FKEY_TEST_TABLE2");
}
}
}
public void SimpleCallFunction()
{
var block = new PlSqlBlockStatement();
block.Declarations.Add(new DeclareVariableStatement("a", PrimitiveTypes.String()));
block.Statements.Add(new AssignVariableStatement(SqlExpression.VariableReference("a"), SqlExpression.FunctionCall("user")));
var procName = ObjectName.Parse("APP.proc1");
AdminQuery.CreateProcedure(procName, block);
var exists = AdminQuery.Access().RoutineExists(procName);
Assert.IsTrue(exists);
var procedure = AdminQuery.Access().GetObject(DbObjectType.Routine, procName);
Assert.IsNotNull(procedure);
Assert.IsInstanceOf <Procedure>(procedure);
}
public static IQueryExpressionBuilder Function(this IQueryExpressionBuilder builder, ObjectName functionName,
SqlExpression[] args, string alias)
{
return(builder.Expression(SqlExpression.FunctionCall(functionName, args), alias));
}
public SqlTableColumn BuildColumn(ITypeResolver typeResolver, string tableName, IList <SqlTableConstraint> constraints)
{
var dataType = DataTypeBuilder.Build(typeResolver, DataType);
var columnInfo = new SqlTableColumn(ColumnName, dataType);
if (Default != null)
{
columnInfo.DefaultExpression = ExpressionBuilder.Build(Default);
}
if (IsIdentity)
{
columnInfo.DefaultExpression = SqlExpression.FunctionCall("UNIQUEKEY",
new[] { SqlExpression.Constant(tableName) });
columnInfo.IsIdentity = true;
}
foreach (var constraint in Constraints)
{
if (String.Equals(ConstraintTypeNames.Check, constraint.ConstraintType, StringComparison.OrdinalIgnoreCase))
{
var exp = ExpressionBuilder.Build(constraint.CheckExpression);
constraints.Add(SqlTableConstraint.Check(null, exp));
}
else if (String.Equals(ConstraintTypeNames.ForeignKey, constraint.ConstraintType, StringComparison.OrdinalIgnoreCase))
{
var fTable = constraint.ReferencedTable.Name;
var fColumn = constraint.ReferencedColumn.Text;
var onDelete = ForeignKeyAction.NoAction;
var onUpdate = ForeignKeyAction.NoAction;
if (!String.IsNullOrEmpty(constraint.OnDeleteAction))
{
onDelete = SqlCodeObjectBuilder.GetForeignKeyAction(constraint.OnDeleteAction);
}
if (!String.IsNullOrEmpty(constraint.OnUpdateAction))
{
onUpdate = SqlCodeObjectBuilder.GetForeignKeyAction(constraint.OnUpdateAction);
}
constraints.Add(SqlTableConstraint.ForeignKey(null, new[] { ColumnName }, fTable, new[] { fColumn }, onDelete, onUpdate));
}
else if (String.Equals(ConstraintTypeNames.PrimaryKey, constraint.ConstraintType, StringComparison.OrdinalIgnoreCase))
{
constraints.Add(SqlTableConstraint.PrimaryKey(null, new[] { ColumnName }));
}
else if (String.Equals(ConstraintTypeNames.UniqueKey, constraint.ConstraintType, StringComparison.OrdinalIgnoreCase))
{
constraints.Add(SqlTableConstraint.UniqueKey(null, new[] { ColumnName }));
}
else if (String.Equals(ConstraintTypeNames.NotNull, constraint.ConstraintType, StringComparison.OrdinalIgnoreCase))
{
columnInfo.IsNotNull = true;
}
else if (String.Equals(ConstraintTypeNames.Null, constraint.ConstraintType, StringComparison.OrdinalIgnoreCase))
{
columnInfo.IsNotNull = false;
}
}
return(columnInfo);
}
