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);
        }
Exemple #2
0
        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);
        }
Exemple #4
0
        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);
        }
Exemple #5
0
        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
        }
Exemple #6
0
        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)));
        }
Exemple #7
0
        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);
        }
Exemple #13
0
        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);
        }
Exemple #14
0
        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);
        }
Exemple #15
0
        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);
        }
Exemple #16
0
        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);
            }
        }
Exemple #18
0
        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());
        }
Exemple #19
0
        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);
        }
Exemple #21
0
        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>();
        }
Exemple #22
0
        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);
        }
Exemple #25
0
        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);
        }
Exemple #26
0
        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());
        }
Exemple #27
0
        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);
        }