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 override SqlStatement VisitBlock(PlSqlParser.BlockContext context)
{
var declarations = context.declaration().Select(Visit);
var block = new PlSqlBlockStatement();
foreach (var declaration in declarations)
{
block.Declarations.Add(declaration);
}
var body = Visit(context.body());
if (body is PlSqlBody)
{
block = ((PlSqlBody)body).AsPlSqlStatement();
}
else if (body is SequenceOfStatements)
{
var seq = (SequenceOfStatements)body;
foreach (var statement in seq.Statements)
{
block.Statements.Add(statement);
}
}
return(block);
}
public static void TriggerWithBody_InitiallyDisabled()
{
var body = new PlSqlBlockStatement();
body.Statements.Add(new OpenStatement("c1"));
body.Statements.Add(new CursorForLoopStatement("i", "c1"));
body.Statements.Add(new ReturnStatement());
var statement = new CreateTriggerStatement(ObjectName.Parse("APP.trig1"), new ObjectName("tab1"), body,
TriggerEventTime.Before, TriggerEventType.Insert | TriggerEventType.Update) {
Status = TriggerStatus.Disabled,
ReplaceIfExists = true
};
var expected = new StringBuilder();
expected.AppendLine("CREATE OR REPLACE TRIGGER APP.trig1 BEFORE INSERT OR UPDATE ON tab1 DISABLE");
expected.AppendLine(" BEGIN");
expected.AppendLine(" OPEN c1");
expected.AppendLine(" FOR i IN c1");
expected.AppendLine(" LOOP");
expected.AppendLine(" END LOOP");
expected.AppendLine(" RETURN");
expected.Append(" END");
Assert.AreEqual(expected.ToString(), statement.ToString());
}
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 static void CreateProcedure(this IQuery query, ObjectName procedureName,
RoutineParameter[] parameters, PlSqlBlockStatement body, bool replace)
{
query.ExecuteStatement(new CreateProcedureStatement(procedureName, parameters, body)
{
ReplaceIfExists = replace
});
}
public PlSqlTriggerInfo(ObjectName triggerName, ObjectName tabbleName, TriggerEventTime eventTime, TriggerEventType eventType, PlSqlBlockStatement body)
: base(triggerName, TriggerType.Procedural, tabbleName, eventTime, eventType)
{
if (body == null)
throw new ArgumentNullException("body");
Body = body;
}
private void CreateFunction(IQuery query)
{
var functionName = ObjectName.Parse("APP.func1");
var body = new PlSqlBlockStatement();
body.Statements.Add(new ReturnStatement(SqlExpression.Constant(200)));
var functionInfo = new PlSqlFunctionInfo(functionName, new RoutineParameter[0], PrimitiveTypes.Integer(), body);
query.Access().CreateRoutine(functionInfo);
}
public PlSqlTriggerInfo(ObjectName triggerName, ObjectName tabbleName, TriggerEventTime eventTime, TriggerEventType eventType, PlSqlBlockStatement body)
: base(triggerName, TriggerType.Procedural, tabbleName, eventTime, eventType)
{
if (body == null)
{
throw new ArgumentNullException("body");
}
Body = body;
}
private void CreateFunction(IQuery query)
{
var functionName = ObjectName.Parse("APP.func1");
var body = new PlSqlBlockStatement();
body.Statements.Add(new ReturnStatement(SqlExpression.Constant(200)));
var functionInfo = new PlSqlFunctionInfo(functionName, new RoutineParameter[0], PrimitiveTypes.Integer(), body);
query.Access().CreateRoutine(functionInfo);
}
protected override bool OnSetUp(string testName, IQuery query)
{
var procName = ObjectName.Parse("APP.proc1");
var body = new PlSqlBlockStatement();
body.Statements.Add(new AssignVariableStatement(SqlExpression.VariableReference("a"), SqlExpression.Constant(34)));
var funtionInfo = new PlSqlProcedureInfo(procName, new RoutineParameter[0], body);
query.Access().CreateObject(funtionInfo);
return true;
}
public void RaiseSystemError()
{
var block = new PlSqlBlockStatement();
block.Statements.Add(new RaiseStatement("ZERO_DIVIDE"));
var expected = Is.InstanceOf <StatementException>()
.And.Property("ErrorCode").EqualTo(100067);
Assert.Throws(expected, () => AdminQuery.ExecuteStatement(block));
}
public void ExceptionInitAndRaise()
{
var block = new PlSqlBlockStatement();
block.Declarations.Add(new DeclareExceptionInitStatement("MY_ERROR", 340059));
block.Statements.Add(new RaiseStatement("MY_ERROR"));
var expected = Is.InstanceOf<StatementException>()
.And.Property("ErrorCode").EqualTo(340059);
Assert.Throws(expected, () => AdminQuery.ExecuteStatement(block));
}
public void ExceptionInitAndRaise()
{
var block = new PlSqlBlockStatement();
block.Declarations.Add(new DeclareExceptionInitStatement("MY_ERROR", 340059));
block.Statements.Add(new RaiseStatement("MY_ERROR"));
var expected = Is.InstanceOf <StatementException>()
.And.Property("ErrorCode").EqualTo(340059);
Assert.Throws(expected, () => AdminQuery.ExecuteStatement(block));
}
protected override bool OnSetUp(string testName, IQuery query)
{
var funcName = ObjectName.Parse("APP.func1");
var returnType = PrimitiveTypes.String();
var body = new PlSqlBlockStatement();
body.Statements.Add(new ReturnStatement(SqlExpression.Constant("Hello!")));
var funtionInfo = new PlSqlFunctionInfo(funcName, new RoutineParameter[0], returnType, body);
query.Access().CreateObject(funtionInfo);
return true;
}
public static void CodeBlock_Nolabel()
{
var block = new PlSqlBlockStatement();
block.Statements.Add(new AssignVariableStatement(SqlExpression.VariableReference("a"), SqlExpression.Constant(2)));
var sql = block.ToString();
var expected = new StringBuilder();
expected.AppendLine("BEGIN");
expected.AppendLine(" :a := 2");
expected.Append("END");
Assert.AreEqual(expected.ToString(), sql);
}
private void CreateTriggers(IQuery query, ObjectName tableName)
{
var triggerName = ObjectName.Parse("APP.trigger1");
var eventTime = TriggerEventTime.Before;
var eventType = TriggerEventType.Insert | TriggerEventType.Update;
var body = new PlSqlBlockStatement();
body.Declarations.Add(new DeclareVariableStatement("a", PrimitiveTypes.Integer()));
body.Statements.Add(new AssignVariableStatement(SqlExpression.VariableReference("a"), SqlExpression.Constant(33)));
var triggerInfo = new PlSqlTriggerInfo(triggerName, tableName, eventTime, eventType, body);
query.Access().CreateObject(triggerInfo);
}
protected override bool OnSetUp(string testName, IQuery query)
{
var procName = ObjectName.Parse("APP.proc1");
var body = new PlSqlBlockStatement();
body.Statements.Add(new AssignVariableStatement(SqlExpression.VariableReference("a"), SqlExpression.Constant(34)));
var funtionInfo = new PlSqlProcedureInfo(procName, new RoutineParameter[0], body);
query.Access().CreateObject(funtionInfo);
return(true);
}
public void DeclareAndOpenCursor()
{
var query = (SqlQueryExpression) SqlExpression.Parse("SELECT * FROM test_table");
var block = new PlSqlBlockStatement();
block.Declarations.Add(new DeclareCursorStatement("c1", query));
block.Statements.Add(new OpenStatement("c1"));
AdminQuery.ExecuteStatement(block);
Assert.IsTrue(AdminQuery.Context.CursorExists("c1"));
var cursor = AdminQuery.Context.FindCursor("c1");
Assert.AreEqual(CursorStatus.Open, cursor.Status);
}
protected override bool OnSetUp(string testName, IQuery query)
{
var funcName = ObjectName.Parse("APP.func1");
var returnType = PrimitiveTypes.String();
var body = new PlSqlBlockStatement();
body.Statements.Add(new ReturnStatement(SqlExpression.Constant("Hello!")));
var funtionInfo = new PlSqlFunctionInfo(funcName, new RoutineParameter[0], returnType, body);
query.Access().CreateObject(funtionInfo);
return(true);
}
private void CreateProcedure1(IQuery query)
{
var procName = ObjectName.Parse("APP.proc1");
var args = new[] { new RoutineParameter("a", PrimitiveTypes.String()) };
var body = new PlSqlBlockStatement();
body.Declarations.Add(new DeclareVariableStatement("b", PrimitiveTypes.String()));
body.Statements.Add(new AssignVariableStatement(SqlExpression.VariableReference("b"),
SqlExpression.VariableReference("a")));
var procInfo = new PlSqlProcedureInfo(procName, args, body);
query.Access().CreateObject(procInfo);
}
public void PlSqlTrigger()
{
var body = new PlSqlBlockStatement();
body.Statements.Add(new CallStatement(ObjectName.Parse("system.output"), new[] {
new InvokeArgument(SqlExpression.Constant("One row was inserted"))
}));
var triggerName = new ObjectName("trigger1");
var tableName = ObjectName.Parse("APP.test_table");
AdminQuery.CreateTrigger(triggerName, tableName, body, TriggerEventTime.After, TriggerEventType.Insert);
var exists = AdminQuery.Access().TriggerExists(ObjectName.Parse("APP.trigger1"));
Assert.IsTrue(exists);
}
public CreateTriggerStatement(ObjectName triggerName, ObjectName tableName, PlSqlBlockStatement body, TriggerEventTime eventTime, TriggerEventType eventType)
{
if (triggerName == null)
throw new ArgumentNullException("triggerName");
if (tableName == null)
throw new ArgumentNullException("tableName");
if (body == null)
throw new ArgumentNullException("body");
TriggerName = triggerName;
TableName = tableName;
Body = body;
EventTime = eventTime;
EventType = eventType;
}
public void PlSqlTrigger()
{
var body = new PlSqlBlockStatement();
body.Statements.Add(new CallStatement(ObjectName.Parse("system.output"), new[] {
new InvokeArgument(SqlExpression.Constant("One row was inserted"))
}));
var triggerName = new ObjectName("trigger1");
var tableName = ObjectName.Parse("APP.test_table");
AdminQuery.CreateTrigger(triggerName, tableName, body, TriggerEventTime.After, TriggerEventType.Insert);
var exists = AdminQuery.Access().TriggerExists(ObjectName.Parse("APP.trigger1"));
Assert.IsTrue(exists);
}
protected override bool OnSetUp(string testName, IQuery query)
{
procName = ObjectName.Parse("APP.proc1");
var body = new PlSqlBlockStatement();
body.Statements.Add(new CallStatement(ObjectName.Parse("proc2")));
var procInfo = new PlSqlProcedureInfo(procName, new RoutineParameter[0], body);
query.Access().CreateObject(procInfo);
funcName = ObjectName.Parse("APP.func1");
body = new PlSqlBlockStatement();
body.Statements.Add(new ReturnStatement(SqlExpression.Constant(22)));
var funcInfo = new PlSqlFunctionInfo(funcName, new RoutineParameter[0], PrimitiveTypes.Integer(), body);
query.Access().CreateObject(funcInfo);
return true;
}
public PlSqlBlockStatement AsPlSqlStatement()
{
var statement = new PlSqlBlockStatement {
Label = Label
};
foreach (var sqlStatement in Statements) {
statement.Statements.Add(sqlStatement);
}
foreach (var handler in ExceptionHandlers) {
statement.ExceptionHandlers.Add(handler);
}
return statement;
}
public static void WithArguments()
{
var body = new PlSqlBlockStatement();
body.Statements.Add(new AssignVariableStatement(SqlExpression.VariableReference("a"), SqlExpression.Constant(3)));
var statement = new CreateProcedureStatement(ObjectName.Parse("SYS.proc1"), new[] {
new RoutineParameter("a", PrimitiveTypes.Integer()),
}, body);
var sql = statement.ToString();
var expected = new SqlStringBuilder();
expected.AppendLine("CREATE PROCEDURE SYS.proc1(a INTEGER IN NOT NULL) IS");
expected.AppendLine(" BEGIN");
expected.AppendLine(" :a := 3");
expected.Append(" END");
Assert.AreEqual(expected.ToString(), sql);
}
public static void CodeBlock_WithLabel()
{
var block = new PlSqlBlockStatement();
block.Label = "stmt";
block.Statements.Add(new CallStatement(ObjectName.Parse("proc1"), new[] {
SqlExpression.Constant(33)
}));
var sql = block.ToString();
var expected = new StringBuilder();
expected.AppendLine("<<stmt>>");
expected.AppendLine("BEGIN");
expected.AppendLine(" CALL proc1(33)");
expected.Append("END");
Assert.AreEqual(expected.ToString(), sql);
}
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 PlSqlBlockStatement AsPlSqlStatement()
{
var statement = new PlSqlBlockStatement {
Label = Label
};
foreach (var sqlStatement in Statements)
{
statement.Statements.Add(sqlStatement);
}
foreach (var handler in ExceptionHandlers)
{
statement.ExceptionHandlers.Add(handler);
}
return(statement);
}
protected override bool OnSetUp(string testName, IQuery query)
{
procName = ObjectName.Parse("APP.proc1");
var body = new PlSqlBlockStatement();
body.Statements.Add(new CallStatement(ObjectName.Parse("proc2")));
var procInfo = new PlSqlProcedureInfo(procName, new RoutineParameter[0], body);
query.Access().CreateObject(procInfo);
funcName = ObjectName.Parse("APP.func1");
body = new PlSqlBlockStatement();
body.Statements.Add(new ReturnStatement(SqlExpression.Constant(22)));
var funcInfo = new PlSqlFunctionInfo(funcName, new RoutineParameter[0], PrimitiveTypes.Integer(), body);
query.Access().CreateObject(funcInfo);
return(true);
}
private void CreateProcedureTrigger(IQuery query, ObjectName tableName)
{
var procedurName = ObjectName.Parse("APP.proc1");
var body = new PlSqlBlockStatement();
body.Declarations.Add(new DeclareVariableStatement("a", PrimitiveTypes.Integer()));
body.Statements.Add(new AssignVariableStatement(SqlExpression.VariableReference("a"), SqlExpression.Constant(33)));
var procedureInfo = new PlSqlProcedureInfo(procedurName, new RoutineParameter[0], body);
query.Access().CreateObject(procedureInfo);
var triggerName = ObjectName.Parse("APP.trigger2");
var eventTime = TriggerEventTime.After;
var eventType = TriggerEventType.Insert | TriggerEventType.Update;
var triggerInfo = new ProcedureTriggerInfo(triggerName, tableName, eventTime, eventType, procedurName);
query.Access().CreateObject(triggerInfo);
}
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 void SimpleReturn()
{
var body = new PlSqlBlockStatement();
body.Declarations.Add(new DeclareVariableStatement("a", PrimitiveTypes.Integer()));
body.Statements.Add(new ReturnStatement(SqlExpression.VariableReference("a")));
var funName = ObjectName.Parse("APP.fun1");
AdminQuery.CreateFunction(funName, PrimitiveTypes.Numeric(), body);
var exists = AdminQuery.Access().RoutineExists(funName);
Assert.IsTrue(exists);
var function = AdminQuery.Access().GetObject(DbObjectType.Routine, funName);
Assert.IsNotNull(function);
Assert.IsInstanceOf<PlSqlFunction>(function);
var userFunction = (PlSqlFunction) function;
Assert.IsNotNull(userFunction.Body);
}
public static void WithNoParameters()
{
var body = new PlSqlBlockStatement();
body.Declarations.Add(new DeclareVariableStatement("a", PrimitiveTypes.Integer()));
body.Statements.Add(new AssignVariableStatement(SqlExpression.VariableReference("a"), SqlExpression.Constant(3)));
body.Statements.Add(new ReturnStatement(SqlExpression.VariableReference("a")));
var statement = new CreateFunctionStatement(ObjectName.Parse("SYS.func1"), PrimitiveTypes.Integer(), body);
var sql = statement.ToString();
var expected = new SqlStringBuilder();
expected.Append("CREATE FUNCTION SYS.func1() ");
expected.AppendLine("RETURN INTEGER IS");
expected.AppendLine(" DECLARE");
expected.AppendLine(" a INTEGER");
expected.AppendLine(" BEGIN");
expected.AppendLine(" :a := 3");
expected.AppendLine(" RETURN :a");
expected.Append(" END");
Assert.AreEqual(expected.ToString(), sql);
}
public void CreateTrigger()
{
var body = new PlSqlBlockStatement();
body.Statements.Add(new OpenStatement("c1"));
body.Statements.Add(new CursorForLoopStatement("i", "c1"));
body.Statements.Add(new ReturnStatement());
var statement = new CreateTriggerStatement(ObjectName.Parse("APP.trig1"), new ObjectName("tab1"), body,
TriggerEventTime.Before, TriggerEventType.Insert | TriggerEventType.Update);
SerializeAndAssert(statement, (serialized, deserialized) => {
Assert.IsNotNull(deserialized);
Assert.IsNotNull(deserialized.TriggerName);
Assert.IsNotNull(deserialized.TableName);
Assert.IsNotNull(deserialized.Body);
Assert.AreEqual("APP.trig1", deserialized.TriggerName.FullName);
Assert.AreEqual("tab1", deserialized.TableName.FullName);
Assert.AreEqual(TriggerEventTime.Before, deserialized.EventTime);
});
}
public void WhileLoop()
{
var loop = new WhileLoopStatement(SqlExpression.SmallerThan(SqlExpression.VariableReference("a"), SqlExpression.Constant(33)));
loop.Statements.Add(new DeclareVariableStatement("b", PrimitiveTypes.Integer()));
loop.Statements.Add(new AssignVariableStatement(SqlExpression.VariableReference("b"),
SqlExpression.Add(SqlExpression.VariableReference("a"), SqlExpression.Constant(40))));
loop.Statements.Add(new AssignVariableStatement(SqlExpression.VariableReference("a"),
SqlExpression.Add(SqlExpression.VariableReference("a"), SqlExpression.Constant(1))));
var block = new PlSqlBlockStatement();
block.Declarations.Add(new DeclareVariableStatement("a", PrimitiveTypes.Integer()));
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 SimpleReturn()
{
var body = new PlSqlBlockStatement();
body.Declarations.Add(new DeclareVariableStatement("a", PrimitiveTypes.Integer()));
body.Statements.Add(new ReturnStatement(SqlExpression.VariableReference("a")));
var funName = ObjectName.Parse("APP.fun1");
AdminQuery.CreateFunction(funName, PrimitiveTypes.Numeric(), body);
var exists = AdminQuery.Access().RoutineExists(funName);
Assert.IsTrue(exists);
var function = AdminQuery.Access().GetObject(DbObjectType.Routine, funName);
Assert.IsNotNull(function);
Assert.IsInstanceOf <PlSqlFunction>(function);
var userFunction = (PlSqlFunction)function;
Assert.IsNotNull(userFunction.Body);
}
public static void CreateProcedure(this IQuery query, ObjectName procedureName, PlSqlBlockStatement body)
{
CreateProcedure(query, procedureName, body, false);
}
public static void CreateTrigger(this IQuery query, ObjectName triggerName, ObjectName tableName, PlSqlBlockStatement body, TriggerEventTime eventTime, TriggerEventType eventType)
{
query.ExecuteStatement(new CreateTriggerStatement(triggerName, tableName, body, eventTime, eventType));
}
protected virtual SqlStatement VisitPlSqlBlock(PlSqlBlockStatement statement)
{
var block = new PlSqlBlockStatement();
foreach (var declaration in statement.Declarations) {
block.Declarations.Add(VisitStatement(declaration));
}
foreach (var child in statement.Statements) {
block.Statements.Add(VisitStatement(child));
}
foreach (var handler in statement.ExceptionHandlers) {
block.ExceptionHandlers.Add(handler);
}
return block;
}
private void CreateProcedureTrigger(IQuery query, ObjectName tableName)
{
var procedurName = ObjectName.Parse("APP.proc1");
var body = new PlSqlBlockStatement();
body.Declarations.Add(new DeclareVariableStatement("a", PrimitiveTypes.Integer()));
body.Statements.Add(new AssignVariableStatement(SqlExpression.VariableReference("a"), SqlExpression.Constant(33)));
var procedureInfo = new PlSqlProcedureInfo(procedurName, new RoutineParameter[0], body);
query.Access().CreateObject(procedureInfo);
var triggerName = ObjectName.Parse("APP.trigger2");
var eventTime = TriggerEventTime.After;
var eventType = TriggerEventType.Insert | TriggerEventType.Update;
var triggerInfo = new ProcedureTriggerInfo(triggerName, tableName, eventTime, eventType, procedurName);
query.Access().CreateObject(triggerInfo);
}
public void WhileLoop()
{
var loop = new WhileLoopStatement(SqlExpression.SmallerThan(SqlExpression.VariableReference("a"), SqlExpression.Constant(33)));
loop.Statements.Add(new DeclareVariableStatement("b", PrimitiveTypes.Integer()));
loop.Statements.Add(new AssignVariableStatement(SqlExpression.VariableReference("b"),
SqlExpression.Add(SqlExpression.VariableReference("a"), SqlExpression.Constant(40))));
loop.Statements.Add(new AssignVariableStatement(SqlExpression.VariableReference("a"),
SqlExpression.Add(SqlExpression.VariableReference("a"), SqlExpression.Constant(1))));
var block = new PlSqlBlockStatement();
block.Declarations.Add(new DeclareVariableStatement("a", PrimitiveTypes.Integer()));
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 static void CreateOrReplaceProcedure(this IQuery query, ObjectName procedureName, PlSqlBlockStatement body)
{
CreateOrReplaceProcedure(query, procedureName, null, body);
}
public static void CreateOrReplaceProcedure(this IQuery query, ObjectName procedureName,
RoutineParameter[] parameters, PlSqlBlockStatement body)
{
query.CreateProcedure(procedureName, parameters, body, true);
}
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);
}
private void CreateProcedure3(IQuery query)
{
var procName = ObjectName.Parse("APP.proc3");
var args = new[] {
new RoutineParameter("a", PrimitiveTypes.String()),
new RoutineParameter("b", PrimitiveTypes.String(), ParameterDirection.Output)
};
var body = new PlSqlBlockStatement();
body.Declarations.Add(new DeclareVariableStatement("c", PrimitiveTypes.String()));
body.Statements.Add(new AssignVariableStatement(SqlExpression.VariableReference("c"),
SqlExpression.VariableReference("a")));
body.Statements.Add(new AssignVariableStatement(SqlExpression.VariableReference("b"),
SqlExpression.VariableReference("c")));
var procInfo = new PlSqlProcedureInfo(procName, args, body);
query.Access().CreateObject(procInfo);
}
public void CreateTrigger()
{
var body = new PlSqlBlockStatement();
body.Statements.Add(new OpenStatement("c1"));
body.Statements.Add(new CursorForLoopStatement("i", "c1"));
body.Statements.Add(new ReturnStatement());
var statement = new CreateTriggerStatement(ObjectName.Parse("APP.trig1"), new ObjectName("tab1"), body,
TriggerEventTime.Before, TriggerEventType.Insert | TriggerEventType.Update);
SerializeAndAssert(statement, (serialized, deserialized) => {
Assert.IsNotNull(deserialized);
Assert.IsNotNull(deserialized.TriggerName);
Assert.IsNotNull(deserialized.TableName);
Assert.IsNotNull(deserialized.Body);
Assert.AreEqual("APP.trig1", deserialized.TriggerName.FullName);
Assert.AreEqual("tab1", deserialized.TableName.FullName);
Assert.AreEqual(TriggerEventTime.Before, deserialized.EventTime);
});
}
private void CreateTriggers(IQuery query, ObjectName tableName)
{
var triggerName = ObjectName.Parse("APP.trigger1");
var eventTime = TriggerEventTime.Before;
var eventType = TriggerEventType.Insert | TriggerEventType.Update;
var body = new PlSqlBlockStatement();
body.Declarations.Add(new DeclareVariableStatement("a", PrimitiveTypes.Integer()));
body.Statements.Add(new AssignVariableStatement(SqlExpression.VariableReference("a"), SqlExpression.Constant(33)));
var triggerInfo = new PlSqlTriggerInfo(triggerName, tableName, eventTime, eventType, body);
query.Access().CreateObject(triggerInfo);
}
public static void CreateProcedure(this IQuery query, ObjectName procedureName,
RoutineParameter[] parameters, PlSqlBlockStatement body)
{
CreateProcedure(query, procedureName, parameters, body, false);
}