public void LoopAndExitWithNoReturn()
{
var loop = new LoopStatement();
loop.Statements.Add(new ReturnStatement(SqlExpression.Constant(45)));
var result = AdminQuery.ExecuteStatement(loop);
Assert.IsNotNull(result);
Assert.AreEqual(StatementResultType.Result, result.Type);
}
public void UniqueKeyViolation()
{
TestQuery.Insert(new ObjectName("a"),
new SqlExpression[] { SqlExpression.Constant(2), SqlExpression.Constant("Antonello Provenzano") });
var expected = Is.InstanceOf <ConstraintViolationException>()
.And.TypeOf <UniqueKeyViolationException>()
.And.Property("TableName").EqualTo(ObjectName.Parse("APP.a"))
.And.Property("ColumnNames").EqualTo(new [] { "name" });
Assert.Throws(expected, () => TestQuery.Commit());
}
public static void OpenCursorWithArguments()
{
var statement = new OpenStatement("c1", new SqlExpression[] {
SqlExpression.Constant(45),
SqlExpression.VariableReference("a")
});
var sql = statement.ToString();
var expected = "OPEN c1(45, :a)";
Assert.AreEqual(expected, sql);
}
public void SimpleConditional()
{
var condition = SqlExpression.Equal(SqlExpression.VariableReference("a"), SqlExpression.Constant(34));
var ifTrue = new SqlStatement[] {
new DeclareVariableStatement("b", PrimitiveTypes.String()),
new AssignVariableStatement(SqlExpression.VariableReference("b"), SqlExpression.Constant(21)),
};
var result = AdminQuery.If(condition, ifTrue);
Assert.IsNotNull(result);
}
public void NextDay()
{
var date = new SqlDateTime(1980, 02, 05, 18, 20, 11, 32);
var value = Select("NEXT_DAY", SqlExpression.Constant(date), SqlExpression.Constant("Wednesday"));
Assert.IsNotNull(value);
Assert.IsFalse(value.IsNull);
Assert.IsInstanceOf <DateType>(value.Type);
Assert.IsInstanceOf <SqlDateTime>(value.Value);
// TODO: Assert result
}
public static void CreateInvokeWithMixedArguments(string methodName, string argName1, object value1, object value2)
{
var name = ObjectName.Parse(methodName);
var arg1 = SqlExpression.Constant(SqlObject.New(SqlValueUtil.FromObject(value1)));
var arg2 = SqlExpression.Constant(SqlObject.New(SqlValueUtil.FromObject(value2)));
var invoke = new Invoke(name);
invoke.Arguments.Add(new InvokeArgument(argName1, arg1));
Assert.Throws <ArgumentException>(() => invoke.Arguments.Add(new InvokeArgument(arg2)));
}
public void GetMetadata()
{
var assign = new AssignStatement("a", SqlExpression.Constant(SqlObject.Bit(true)));
var statementContext = new StatementContext(context, assign);
var meta = (statementContext as IEventSource).Metadata.ToDictionary(x => x.Key, y => y.Value);
Assert.NotNull(meta);
Assert.NotEmpty(meta);
Assert.True(meta.ContainsKey("statement.var"));
Assert.True(meta.ContainsKey("statement.value"));
}
public void UpdateOneRow()
{
var tableName = ObjectName.Parse("APP.test_table");
var whereExp = SqlExpression.Parse("id = 2");
var assignments = new[] {
new SqlColumnAssignment("birth_date", SqlExpression.Constant(Field.Date(new SqlDateTime(1970, 01, 20))))
};
var count = AdminQuery.Update(tableName, whereExp, assignments);
Assert.AreEqual(1, count);
}
public void ToString_TimeStamp_NoFormat()
{
var value = SqlExpression.Constant(Field.TimeStamp(new SqlDateTime(2015, 02, 10, 17, 15, 01, 00)));
var result = Select("TOSTRING", value);
Assert.IsNotNull(result);
Assert.IsInstanceOf <StringType>(result.Type);
var stringResult = result.Value.ToString();
Assert.AreEqual("2015-02-10T17:15:01.000 +00:00", stringResult);
}
public void ToString_Date()
{
var value = SqlExpression.Constant(Field.Date(new SqlDateTime(2015, 02, 10)));
var result = Select("TOSTRING", value);
Assert.IsNotNull(result);
Assert.IsInstanceOf <StringType>(result.Type);
var stringResult = result.Value.ToString();
Assert.AreEqual("2015-02-10", stringResult);
}
public void ToString_Integer()
{
var value = SqlExpression.Constant(Field.Integer(455366));
var result = Select("TOSTRING", value);
Assert.IsNotNull(result);
Assert.IsInstanceOf <StringType>(result.Type);
var stringResult = result.Value.ToString();
Assert.AreEqual("455366", stringResult);
}
public void WithSimplePassword()
{
const string userName = "******";
var password = SqlExpression.Constant(DataObject.VarChar("12345"));
var statement = new CreateUserStatement(userName, password);
statement.Execute(Query);
var exists = Query.UserExists(userName);
Assert.IsTrue(exists);
}
public async void WhileTrueExit()
{
var loop = new WhileLoopStatement(SqlExpression.Constant(SqlObject.Boolean(true)));
loop.Statements.Add(new ExitStatement());
var statement = loop.Prepare(context);
var result = await statement.ExecuteAsync(context);
Assert.Null(result);
}
public void SerializeSimpleLoop()
{
var loop = new WhileLoopStatement(SqlExpression.Constant(SqlObject.Boolean(true)));
loop.Statements.Add(new ExitStatement());
var result = BinarySerializeUtil.Serialize(loop);
Assert.NotNull(result);
Assert.NotNull(result.Condition);
Assert.IsType <SqlConstantExpression>(result.Condition);
}
public void CurrentValue()
{
var result = Select("CURVAL", SqlExpression.Constant("APP.seq1"));
Assert.IsNotNull(result);
Assert.IsInstanceOf <NumericType>(result.Type);
Assert.IsInstanceOf <SqlNumber>(result.Value);
var value = (SqlNumber)result.Value;
Assert.AreEqual(new SqlNumber(0), value);
}
public void Extract_Day()
{
var date = new SqlDateTime(1980, 02, 05, 18, 20, 11, 32);
var value = Select("EXTRACT", SqlExpression.Constant(date), SqlExpression.Constant("day"));
Assert.IsNotNull(value);
Assert.IsFalse(value.IsNull);
Assert.IsInstanceOf <NumericType>(value.Type);
Assert.IsInstanceOf <SqlNumber>(value.Value);
Assert.AreEqual(new SqlNumber(05), value.Value);
}
private void RevokeAllGrantsFrom(DbObjectType objectType, ObjectName objectName, string revoker, string grantee,
bool withOption = false)
{
using (var query = Session.CreateQuery()) {
var grantTable = query.Access().GetMutableTable(SystemSchema.GrantsTableName);
var objectCol = grantTable.GetResolvedColumnName(1);
var paramCol = grantTable.GetResolvedColumnName(2);
var granteeCol = grantTable.GetResolvedColumnName(3);
var grantOptionCol = grantTable.GetResolvedColumnName(4);
var granterCol = grantTable.GetResolvedColumnName(5);
ITable t1 = grantTable;
// All that match the given object parameter
// It's most likely this will reduce the search by the most so we do
// it first.
t1 = t1.SimpleSelect(query, paramCol, SqlExpressionType.Equal,
SqlExpression.Constant(Field.String(objectName.FullName)));
// The next is a single exhaustive select through the remaining records.
// It finds all grants that match either public or the grantee is the
// user or role, and that match the object type.
// Expression: ("grantee_col" = grantee)
var userCheck = SqlExpression.Equal(SqlExpression.Reference(granteeCol),
SqlExpression.Constant(Field.String(grantee)));
// Expression: ("object_col" = object AND
// "grantee_col" = grantee)
// All that match the given grantee or public and given object
var expr =
SqlExpression.And(
SqlExpression.Equal(SqlExpression.Reference(objectCol),
SqlExpression.Constant(Field.BigInt((int)objectType))), userCheck);
// Are we only searching for grant options?
var grantOptionCheck = SqlExpression.Equal(SqlExpression.Reference(grantOptionCol),
SqlExpression.Constant(Field.Boolean(withOption)));
expr = SqlExpression.And(expr, grantOptionCheck);
// Make sure the granter matches up also
var granterCheck = SqlExpression.Equal(SqlExpression.Reference(granterCol),
SqlExpression.Constant(Field.String(revoker)));
expr = SqlExpression.And(expr, granterCheck);
t1 = t1.ExhaustiveSelect(query, expr);
// Remove these rows from the table
grantTable.Delete(t1);
}
}
public void CheckViolation()
{
TestQuery.Insert(new ObjectName("a"), new SqlExpression[] {
SqlExpression.Constant(101),
SqlExpression.Constant("Sebastiano Provenzano"),
});
var expected = Is.InstanceOf <ConstraintViolationException>()
.And.TypeOf <CheckViolationException>()
.And.Property("TableName").EqualTo(ObjectName.Parse("APP.a"));
Assert.Throws(expected, () => TestQuery.Commit());
}
public void DeclareInsensitiveCursor()
{
var query = new SqlQueryExpression(new [] { new SelectColumn(SqlExpression.Constant("*")) });
query.FromClause = new FromClause();
query.FromClause.AddTable("test_table");
Assert.DoesNotThrow(() => Query.DeclareInsensitiveCursor("c1", query));
var exists = Query.ObjectExists(DbObjectType.Cursor, new ObjectName("c1"));
Assert.IsTrue(exists);
}
public static void BigIntWithDefault()
{
var statement = new DeclareVariableStatement("c", PrimitiveTypes.BigInt())
{
DefaultExpression =
SqlExpression.Multiply(SqlExpression.Constant(56), SqlExpression.Reference(new ObjectName("test.a")))
};
var sql = statement.ToString();
var expected = "c BIGINT := 56 * test.a";
Assert.AreEqual(expected, sql);
}
internal MethodContext(IContext context, SqlMethod method, Invoke invoke)
: base(context, method.Type == MethodType.Function ? "function" : "procedure")
{
Invoke = invoke;
Method = method;
namedArgs = BuildArguments(method.MethodInfo, invoke);
ResultValue = SqlExpression.Constant(SqlObject.Null);
output = new Dictionary <string, SqlExpression>();
Metadata = new Dictionary <string, object>();
}
public void AddDate_Second()
{
var date = new SqlDateTime(1980, 02, 05, 18, 20, 11, 32);
var value = Select("ADD_DATE", SqlExpression.Constant(date), SqlExpression.Constant("second"),
SqlExpression.Constant(2));
Assert.IsNotNull(value);
Assert.IsFalse(value.IsNull);
Assert.IsInstanceOf <DateType>(value.Type);
Assert.IsInstanceOf <SqlDateTime>(value.Value);
// TODO: Assert result
}
internal MethodContext(IContext context, SqlMethod method, Invoke invoke)
: base(context, $"Method({method.MethodInfo.MethodName})")
{
Invoke = invoke;
Method = method;
namedArgs = BuildArguments(method.MethodInfo, invoke);
ResultValue = SqlExpression.Constant(SqlObject.Null);
output = new Dictionary <string, SqlExpression>();
Metadata = new Dictionary <string, object>();
}
public void CountAll()
{
var result = SelectAggregate("COUNT", SqlExpression.Constant("*"));
Assert.IsNotNull(result);
Assert.IsFalse(result.IsNull);
Assert.IsInstanceOf <NumericType>(result.Type);
Assert.IsInstanceOf <SqlNumber>(result.Value);
var value = (SqlNumber)result.Value;
Assert.AreEqual(new SqlNumber(15), value);
}
public async void ForwardForLoop()
{
var loop = new ForLoopStatement("i",
SqlExpression.Constant(SqlObject.BigInt(0)),
SqlExpression.Constant(SqlObject.BigInt(5)));
loop.Statements.Add(new ContinueStatement());
var statement = loop.Prepare(context);
var result = await statement.ExecuteAsync(context);
Assert.Null(result);
}
public void ForeignKeyViolation()
{
TestQuery.Insert(new ObjectName("b"), new SqlExpression[] { SqlExpression.Constant(1), SqlExpression.Constant(2) });
var expected = Is.InstanceOf <ConstraintViolationException>()
.And.TypeOf(typeof(ForeignKeyViolationException))
.And.Property("TableName").EqualTo(ObjectName.Parse("APP.b"))
.And.Property("ColumnNames").EqualTo(new[] { "other_id" })
.And.Property("LinkedTableName").EqualTo(ObjectName.Parse("APP.a"))
.And.Property("LinkedColumnNames").EqualTo(new[] { "id" });
Assert.Throws(expected, () => TestQuery.Commit());
}
public void InsertRegular()
{
var tableName = ObjectName.Parse("APP.people");
var assignments = new SqlAssignExpression[] {
SqlExpression.Assign(SqlExpression.Reference(new ObjectName(tableName, "id")),
SqlExpression.Constant(1)),
SqlExpression.Assign(SqlExpression.Reference(new ObjectName(tableName, "first_name")),
SqlExpression.Constant("Antonello")),
SqlExpression.Assign(SqlExpression.Reference(new ObjectName(tableName, "last_name")),
SqlExpression.Constant("Provenzano"))
};
Query.InsertIntoTable(tableName, assignments);
}
public async void IfTrueReturn()
{
var test = SqlExpression.Constant(SqlObject.Boolean(true));
var condition = new ConditionalStatement(test);
condition.Statements.Add(new ReturnStatement(SqlExpression.Constant(SqlObject.BigInt(33))));
var statement = condition.Prepare(context);
var result = await statement.ExecuteAsync(context);
Assert.NotNull(result);
Assert.IsType <StatementExpressionResult>(result);
Assert.Equal((SqlNumber)33L, ((SqlConstantExpression)((StatementExpressionResult)result).Value).Value.Value);
}
public void Conditional()
{
var test = SqlExpression.FunctionCall("test");
var trueExp = SqlExpression.Constant(45);
var falseExp = SqlExpression.FunctionCall("testReturn");
var condition = SqlExpression.Conditional(test, trueExp, falseExp);
SerializeAndAssert(condition, (serialized, deserialized) => {
Assert.AreEqual(SqlExpressionType.Conditional, deserialized.ExpressionType);
Assert.IsInstanceOf <SqlFunctionCallExpression>(deserialized.TestExpression);
Assert.IsInstanceOf <SqlConstantExpression>(deserialized.TrueExpression);
Assert.IsInstanceOf <SqlFunctionCallExpression>(deserialized.FalseExpression);
});
}
public void CallExternalWithArguments()
{
var procName = ObjectName.Parse("APP.extProc");
var arg1 = new InvokeArgument("a", SqlExpression.Constant("Hello"));
var arg2 = new InvokeArgument("b", SqlExpression.Constant("World!"));
var result = AdminQuery.Call(procName, arg1, arg2);
Assert.IsNotNull(result);
Assert.AreEqual(1, result.Count);
Assert.IsNotNullOrEmpty(ExternProcedureResult);
Assert.AreEqual("Hello World!", ExternProcedureResult);
}
