protected override void ExecuteStatement(ExecutionContext context)
{
//if (!context.User.CanCreateInSchema(ProcedureName.ParentName))
// throw new SecurityException();
if (context.DirectAccess.RoutineExists(ProcedureName))
{
if (!ReplaceIfExists)
{
throw new StatementException(String.Format("A routine named '{0}' already exists in the database.", ProcedureName));
}
context.DirectAccess.DeleteRoutine(ProcedureName);
}
var parameters = new RoutineParameter[0];
if (Parameters != null)
{
parameters = Parameters.ToArray();
}
var functionInfo = new PlSqlProcedureInfo(ProcedureName, parameters, Body)
{
Owner = context.User.Name
};
context.DirectAccess.CreateRoutine(functionInfo);
//context.DirectAccess.GrantOn(DbObjectType.Routine, ProcedureName, context.User.Name, Privileges.Execute, 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);
}
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);
}
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);
}
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);
}
