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); }