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