public void CreateExternalProcedure()
{
var procName = ObjectName.Parse("APP.proc1");
var parameters = new RoutineParameter[] {
new RoutineParameter("a", PrimitiveTypes.Integer()),
new RoutineParameter("b", PrimitiveTypes.Integer()),
};
var externRef = ExternalRef.MakeRef(typeof(Test), "Procedure(int, int)");
AdminQuery.CreateExternProcedure(procName, parameters, externRef.ToString());
var exists = AdminQuery.Access().RoutineExists(procName);
Assert.IsTrue(exists);
var procedure = AdminQuery.Access().GetObject(DbObjectType.Routine, procName);
Assert.IsNotNull(procedure);
Assert.IsInstanceOf <ExternalProcedure>(procedure);
var externFunction = (ExternalProcedure)procedure;
Assert.IsNotNull(externFunction.ExternalRef);
Assert.AreEqual(typeof(Test), externFunction.ExternalRef.Type);
}
public void ShowSchema()
{
var result = AdminQuery.ShowSchema();
Assert.IsNotNull(result);
Row row = null;
Assert.DoesNotThrow(() => row = result.ElementAt(0));
Assert.IsNotNull(row);
var schemaName = row.GetValue(0).Value.ToString();
var schemaType = row.GetValue(1).Value.ToString();
Assert.AreEqual("APP", schemaName);
Assert.AreEqual("DEFAULT", schemaType);
Assert.DoesNotThrow(() => row = result.ElementAt(1));
Assert.IsNotNull(row);
schemaName = row.GetValue(0).Value.ToString();
schemaType = row.GetValue(1).Value.ToString();
Assert.AreEqual("INFORMATION_SCHEMA", schemaName);
Assert.AreEqual("SYSTEM", schemaType);
}
public void Callback()
{
var tableName = ObjectName.Parse("APP.test_table");
AdminQuery.Access().CreateCallbackTrigger("callback1", tableName, TriggerEventTime.After, TriggerEventType.Insert);
var reset = new AutoResetEvent(false);
TriggerEvent firedEvent = null;
AdminQuery.Context.RouteImmediate <TriggerEvent>(e => {
firedEvent = e;
reset.Set();
}, e => e.TriggerType == TriggerType.Callback &&
e.TriggerName.Name.Equals("callback1"));
AdminQuery.Insert(tableName, new[] { "id", "name" },
new SqlExpression[] { SqlExpression.Constant(2), SqlExpression.Constant("The Name") });
reset.WaitOne(300);
Assert.IsNotNull(firedEvent);
Assert.AreEqual(TriggerEventTime.After, firedEvent.EventTime);
Assert.AreEqual(TriggerEventType.Insert, firedEvent.EventType);
}
public void SelectBio()
{
var exp = (SqlQueryExpression)SqlExpression.Parse("SELECT * FROM test_table");
var result = AdminQuery.Select(exp);
Assert.IsNotNull(result);
Assert.IsTrue(result.Any());
var row = result.FirstOrDefault();
Assert.IsNotNull(row);
var bio = row["bio"];
Assert.IsFalse(Field.IsNullField(bio));
Assert.IsInstanceOf <StringType>(bio.Type);
Assert.AreEqual(SqlTypeCode.Clob, bio.Type.TypeCode);
Assert.IsInstanceOf <SqlLongString>(bio.Value);
var textReader = ((SqlLongString)bio.Value).GetInput(Encoding.ASCII);
string text = null;
Assert.DoesNotThrow(() => text = textReader.ReadToEnd());
Assert.IsNotNullOrEmpty(text);
Assert.AreEqual(testBio, text);
}
public void SelectSingleVariable()
{
const string sql = "SELECT * FROM system.vars WHERE var = 'a'";
var query = (SqlQueryExpression)SqlExpression.Parse(sql);
var result = AdminQuery.Select(query);
Assert.IsNotNull(result);
Row row = null;
Assert.DoesNotThrow(() => row = result.Fetch(FetchDirection.Next, -1));
Assert.IsNotNull(row);
var name = row.GetValue("var");
var type = row.GetValue("type");
var value = row.GetValue("value");
var constant = row.GetValue("constant");
Assert.IsFalse(Field.IsNullField(name));
Assert.IsFalse(Field.IsNullField(type));
Assert.IsTrue(value.IsNull);
Assert.IsFalse(Field.IsNullField(constant));
}
public void UnderNotExistingType()
{
var typeName = ObjectName.Parse("APP.type1");
var parentTypeName = ObjectName.Parse("APP.parentType");
Assert.Throws <StatementException>(() => AdminQuery.CreateType(typeName, parentTypeName, new UserTypeMember("age", PrimitiveTypes.Integer())));
}
public void FormMessageAndReadMeta()
{
var reset = new AutoResetEvent(false);
Event info = null;
System.Context.RouteImmediate <InformationEvent>(e => {
info = e;
reset.Set();
}, e => e.Level == InformationLevel.Verbose);
AdminQuery.OnVerbose("Testing Messages");
reset.WaitOne(300);
Assert.IsNotNull(info);
Assert.IsInstanceOf <InformationEvent>(info);
var message = info.AsMessage();
Assert.IsNotNull(message);
var databaseName = message.DatabaseName();
Assert.IsNotNullOrEmpty(databaseName);
Assert.AreEqual(DatabaseName, databaseName);
}
public void RouteOnlyOnce()
{
var reset1 = new AutoResetEvent(false);
var reset2 = new AutoResetEvent(false);
IEvent systemFired = null;
System.Context.Route <InformationEvent>(e => {
systemFired = e;
reset1.Set();
});
IEvent sessionFired = null;
AdminSession.Context.Route <ErrorEvent>(e => {
sessionFired = e;
reset2.Set();
});
AdminQuery.OnVerbose("Test Message");
reset1.WaitOne(300);
reset2.WaitOne(300);
Assert.IsNotNull(systemFired);
Assert.IsNull(sessionFired);
}
public void RouteOneRegisteredMany()
{
var reset = new AutoResetEvent(false);
QueryEvent a = null, b = null;
System.Context.Route <QueryEvent>(e => a = e);
System.Context.Route <QueryEvent>(e => b = e);
IEvent fired = null;
System.Context.Route <InformationEvent>(e => {
fired = e;
reset.Set();
});
AdminQuery.OnVerbose("Test Message");
reset.WaitOne(300);
Assert.IsNotNull(fired);
Assert.IsInstanceOf <InformationEvent>(fired);
Assert.IsNull(a);
Assert.IsNull(b);
var infoEvent = (InformationEvent)fired;
Assert.AreEqual(InformationLevel.Verbose, infoEvent.Level);
Assert.AreEqual("Test Message", infoEvent.Message);
}
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 void SelectData()
{
var exp = (SqlQueryExpression)SqlExpression.Parse("SELECT * FROM test_table");
var result = AdminQuery.Select(exp);
Assert.IsNotNull(result);
Assert.IsTrue(result.Any());
var row = result.FirstOrDefault();
Assert.IsNotNull(row);
var data = row["data"];
Assert.IsFalse(Field.IsNullField(data));
Assert.IsInstanceOf <BinaryType>(data.Type);
Assert.AreEqual(SqlTypeCode.Blob, data.Type.TypeCode);
Assert.IsInstanceOf <SqlLongBinary>(data.Value);
var stream = ((SqlLongBinary)data.Value).GetInput();
var content = new byte[2048];
Assert.DoesNotThrow(() => stream.Read(content, 0, content.Length));
Assert.IsNotEmpty(content);
Assert.AreEqual(testData, content);
}
protected override bool OnTearDown(string testName, IQuery query)
{
var functionName = ObjectName.Parse("APP.func1");
AdminQuery.Access().DropObject(DbObjectType.Routine, functionName);
return(true);
}
public void WithNamedArgument()
{
var procName = ObjectName.Parse("APP.proc1");
var arg = new InvokeArgument("a", SqlExpression.Constant("Hello!"));
AdminQuery.Call(procName, arg);
}
public void CreateExternalFunction()
{
var funName = ObjectName.Parse("APP.fun1");
var parameters = new RoutineParameter[] {
new RoutineParameter("a", PrimitiveTypes.Integer()),
new RoutineParameter("b", PrimitiveTypes.Integer()),
};
var externRef = ExternalRef.MakeRef(typeof(Test), "Function(int, int)");
AdminQuery.CreateExternFunction(funName, PrimitiveTypes.Integer(), parameters, externRef.ToString());
var exists = AdminQuery.Access().RoutineExists(funName);
Assert.IsTrue(exists);
var function = AdminQuery.Access().GetObject(DbObjectType.Routine, funName);
Assert.IsNotNull(function);
Assert.IsInstanceOf <ExternalFunction>(function);
var externFunction = (ExternalFunction)function;
Assert.IsNotNull(externFunction.ExternalRef);
Assert.AreEqual(typeof(Test), externFunction.ExternalRef.Type);
}
protected override void OnAfterSetup(string testName)
{
AdminQuery.Access().CreateTable(table => table
.Named("APP.test_table")
.WithColumn("a", PrimitiveTypes.Integer())
.WithColumn("b", PrimitiveTypes.String()));
}
public void OneValueWithLob()
{
const string testBio = "A simple test string that can span several characters, " +
"that is trying to be the longest possible, just to prove" +
"the capacity of a LONG VARCHAR to handle very long strings. " +
"Anyway it is virtually impossible to reach the maximum size " +
"of a long object, that is organized in 64k byte pages and " +
"spans within the local system without any constraint of size. " +
"For sake of memory anyway, the maximum size of the test object " +
"is set to just 2048 bytes.";
var tableName = ObjectName.Parse("APP.test_table");
var columns = new[] { "first_name", "last_name", "active", "bio" };
var bio = CreateBio(testBio);
var values = new List <SqlExpression[]> {
new[] {
SqlExpression.Constant("Antonello"),
SqlExpression.Constant("Provenzano"),
SqlExpression.Constant(true),
SqlExpression.Constant(bio)
},
};
var count = AdminQuery.Insert(tableName, columns, values.ToArray());
Assert.AreEqual(1, count);
var table = AdminQuery.Access().GetTable(tableName);
Assert.IsNotNull(table);
Assert.AreEqual(1, table.RowCount);
}
public void InsertWithUserType()
{
var tableName = ObjectName.Parse("APP.test_table");
var columns = new[] { "first_name", "last_name", "active", "user_obj" };
var values = new List <SqlExpression[]> {
new SqlExpression[] {
SqlExpression.Constant("Antonello"),
SqlExpression.Constant("Provenzano"),
SqlExpression.Constant(true),
SqlExpression.FunctionCall("type1", new SqlExpression[] {
SqlExpression.Constant("test1"),
SqlExpression.Constant(1),
})
},
new SqlExpression[] {
SqlExpression.Constant("Mart"),
SqlExpression.Constant("Roosmaa"),
SqlExpression.Constant(false),
SqlExpression.FunctionCall("type1", new SqlExpression[] {
SqlExpression.Constant("test2"),
SqlExpression.Constant(3),
})
}
};
var count = AdminQuery.Insert(tableName, columns, values.ToArray());
Assert.AreEqual(2, count);
var table = AdminQuery.Access().GetTable(tableName);
Assert.IsNotNull(table);
Assert.AreEqual(2, table.RowCount);
}
public void TwoValues_NoColumnes()
{
var tableName = ObjectName.Parse("APP.test_table");
var values = new List <SqlExpression[]> {
new SqlExpression[] {
SqlExpression.Constant("Antonello"),
SqlExpression.Constant("Provenzano"),
SqlExpression.Constant(true)
},
new SqlExpression[] {
SqlExpression.Constant("Mart"),
SqlExpression.Constant("Roosmaa"),
SqlExpression.Constant(false)
}
};
var count = AdminQuery.Insert(tableName, values.ToArray());
Assert.AreEqual(2, count);
var table = AdminQuery.Access().GetTable(tableName);
Assert.IsNotNull(table);
Assert.AreEqual(2, table.RowCount);
}
public void RoleFromUser()
{
AdminQuery.RevokeRole("test_user", "test_role");
var inRole = AdminQuery.Access().UserIsInRole("test_user", "test_role");
Assert.IsFalse(inRole);
}
public void WithNamedArguments()
{
var procName = ObjectName.Parse("APP.proc2");
var arg1 = new InvokeArgument("a", SqlExpression.Constant("Hello"));
var arg2 = new InvokeArgument("b", SqlExpression.Constant("World!"));
AdminQuery.Call(procName, arg1, arg2);
}
public void Disable()
{
AdminQuery.DisableTrigger(triggerName);
var trigger = (Trigger)AdminQuery.Access().GetObject(DbObjectType.Trigger, triggerName);
Assert.AreEqual(TriggerStatus.Disabled, trigger.TriggerInfo.Status);
}
public void NewRole()
{
AdminQuery.CreateRole("db_admin");
var exists = AdminQuery.Access().RoleExists("db_admin");
Assert.IsTrue(exists);
}
public void TypicalClode()
{
AdminQuery.Close("c1");
var cursor = (Cursor)AdminQuery.Access().FindObject(new ObjectName("c1"));
Assert.AreEqual(CursorStatus.Closed, cursor.Status);
}
public void GrantRoleToUser()
{
AdminQuery.GrantRole("test_user", "test_role");
var inRole = AdminQuery.Access().UserIsInRole("test_user", "test_role");
Assert.IsTrue(inRole);
}
private ITable Execute(string s)
{
var query = (SqlQueryExpression)SqlExpression.Parse(s);
var result = AdminQuery.Select(query);
result.GetEnumerator().MoveNext();
return(result.Source);
}
public void Existing()
{
AdminQuery.DropCallbackTrigger(triggerName);
var exists = AdminQuery.Access().TriggerExists(new ObjectName(triggerName));
Assert.IsFalse(exists);
}
public void SystemRole()
{
Assert.Throws <SecurityException>(() => AdminQuery.DropRole(SystemRoles.SecureAccessRole));
var exists = AdminQuery.Access().RoleExists(SystemRoles.SecureAccessRole);
Assert.IsTrue(exists);
}
protected override void OnAfterSetup(string testName)
{
AdminQuery.Access().CreateObject(new VariableInfo("a", PrimitiveTypes.Integer(), false));
AdminQuery.Access().CreateObject(new VariableInfo("b", PrimitiveTypes.Integer(), true)
{
DefaultExpression = SqlExpression.Constant(56)
});
}
public void ExistingRole()
{
AdminQuery.DropRole("test_role");
var exists = AdminQuery.Access().RoleExists("test_role");
Assert.IsFalse(exists);
}
public void DropEmptySchema()
{
AdminQuery.DropSchema("test");
var exists = AdminQuery.Session.Access().SchemaExists("test");
Assert.IsFalse(exists);
}
