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;
}
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 PlSqlProcedure(PlSqlProcedureInfo procedureInfo)
: base(procedureInfo)
{
}
public IRoutine GetRoutine(ObjectName routineName)
{
IRoutine result;
if (!routinesCache.TryGet(routineName, out result))
{
var t = FindEntry(routineName);
if (t == null || t.RowCount == 0)
{
return(null);
}
var id = t.GetValue(0, 0);
var schemaName = t.GetValue(0, 1).Value.ToString();
var name = t.GetValue(0, 2).Value.ToString();
var fullName = new ObjectName(ObjectName.Parse(schemaName), name);
var t2 = GetParameters(id);
var parameters = CreateParameters(t2);
var routineType = t.GetValue(0, 3).Value.ToString();
var returnTypeString = t.GetValue(0, 6).Value.ToString();
var owner = t.GetValue(0, 7).Value.ToString();
RoutineInfo info;
SqlType returnType = null;
if (routineType == FunctionType ||
routineType == ExtrernalFunctionType)
{
returnType = transaction.Context.ResolveType(returnTypeString);
}
SqlStatement body = null;
ExternalRef externalRef = null;
if (routineType == FunctionType ||
routineType == ProcedureType)
{
var bodyBin = (SqlBinary)t.GetValue(0, 5).Value;
body = bodyBin.ToObject <PlSqlBlockStatement>();
}
else if (routineType == ExtrernalFunctionType ||
routineType == ExternalProcedureType)
{
var location = t.GetValue(0, 4).Value.ToString();
if (!ExternalRef.TryParse(location, out externalRef))
{
throw new InvalidOperationException(String.Format("The location stored for function '{0}' is invalid: {1}.",
routineName, location));
}
}
if (routineType == FunctionType)
{
info = new PlSqlFunctionInfo(fullName, parameters, returnType, body);
}
else if (routineType == ProcedureType)
{
info = new PlSqlProcedureInfo(fullName, parameters, body);
}
else if (routineType == ExtrernalFunctionType)
{
info = new ExternalFunctionInfo(fullName, parameters, returnType, externalRef);
}
else if (routineType == ExternalProcedureType)
{
info = new ExternalProcedureInfo(fullName, parameters, externalRef);
}
else
{
throw new InvalidOperationException(String.Format("Invalid routine type '{0}' found in database", routineType));
}
info.Owner = owner;
if (info is PlSqlFunctionInfo)
{
result = new PlSqlFunction((PlSqlFunctionInfo)info);
}
else if (info is PlSqlProcedureInfo)
{
result = new PlSqlProcedure((PlSqlProcedureInfo)info);
}
else if (info is ExternalFunctionInfo)
{
result = new ExternalFunction((ExternalFunctionInfo)info);
}
else
{
result = new ExternalProcedure((ExternalProcedureInfo)info);
}
routinesCache.Set(fullName, result);
}
return(result);
}
public IRoutine GetRoutine(ObjectName routineName)
{
IRoutine result;
if (!routinesCache.TryGet(routineName, out result)) {
var t = FindEntry(routineName);
if (t == null || t.RowCount == 0)
return null;
var id = t.GetValue(0, 0);
var schemaName = t.GetValue(0, 1).Value.ToString();
var name = t.GetValue(0, 2).Value.ToString();
var fullName = new ObjectName(ObjectName.Parse(schemaName), name);
var t2 = GetParameters(id);
var parameters = CreateParameters(t2);
var routineType = t.GetValue(0, 3).Value.ToString();
var returnTypeString = t.GetValue(0, 6).Value.ToString();
var owner = t.GetValue(0, 7).Value.ToString();
RoutineInfo info;
SqlType returnType = null;
if (routineType == FunctionType ||
routineType == ExtrernalFunctionType) {
returnType = transaction.Context.ResolveType(returnTypeString);
}
SqlStatement body = null;
ExternalRef externalRef = null;
if (routineType == FunctionType ||
routineType == ProcedureType) {
var bodyBin = (SqlBinary) t.GetValue(0, 5).Value;
body = bodyBin.ToObject<PlSqlBlockStatement>();
} else if (routineType == ExtrernalFunctionType ||
routineType == ExternalProcedureType) {
var location = t.GetValue(0, 4).Value.ToString();
if (!ExternalRef.TryParse(location, out externalRef))
throw new InvalidOperationException(String.Format("The location stored for function '{0}' is invalid: {1}.",
routineName, location));
}
if (routineType == FunctionType) {
info = new PlSqlFunctionInfo(fullName, parameters, returnType, body);
} else if (routineType == ProcedureType) {
info = new PlSqlProcedureInfo(fullName, parameters, body);
} else if (routineType == ExtrernalFunctionType) {
info = new ExternalFunctionInfo(fullName, parameters, returnType, externalRef);
} else if (routineType == ExternalProcedureType) {
info = new ExternalProcedureInfo(fullName, parameters, externalRef);
} else {
throw new InvalidOperationException(String.Format("Invalid routine type '{0}' found in database", routineType));
}
info.Owner = owner;
if (info is PlSqlFunctionInfo) {
result = new PlSqlFunction((PlSqlFunctionInfo) info);
} else if (info is PlSqlProcedureInfo) {
result = new PlSqlProcedure((PlSqlProcedureInfo) info);
} else if (info is ExternalFunctionInfo) {
result = new ExternalFunction((ExternalFunctionInfo) info);
} else {
result = new ExternalProcedure((ExternalProcedureInfo) info);
}
routinesCache.Set(fullName, result);
}
return result;
}
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 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);
}
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 PlSqlProcedure(PlSqlProcedureInfo procedureInfo)
: base(procedureInfo)
{
}
