Esempio n. 1
0
        public void Update_ImplicitSets_EqualsExpected()
        {
            var filter = SqlFilter.Construct(new
            {
                Id          = Guid.Parse("b07d1437-597c-47f5-8d13-453a85f0b681"),
                Auto        = Guid.Parse("b07d1437-597c-47f5-8d13-453a85f0b681"),
                DoNotChange = (DateTime?)null
            });
            var table      = new SqlTable <Class1>();
            var sqlBuilder = SqlBuilder.Update(table)
                             .Values(new Dictionary <SqlColumn, IOperable>
            {
                { table[t => t.Auto], filter[t => t.Auto].Coalesce(table[f => f.Auto]) },
                { table[t => t.DoNotChange], filter[t => t.DoNotChange].Coalesce(table[f => f.DoNotChange]) }
            })
                             .Where(table[t => t.Id].EqualsOne(filter[f => f.Id]));
            var sqlOptions = new SqlOptions {
                Dialect = SqlDialect.Postgres95
            };
            var actual   = sqlBuilder.BuildSql(sqlOptions);
            var expected = @"
            UPDATE foo.class1
            SET
                auto = COALESCE(@Auto, class1.auto),
                do_not_change = COALESCE(@DoNotChange, class1.do_not_change)
            WHERE
                class1.id = @Id
            ";

            Check(expected, actual);
        }
Esempio n. 2
0
        public void Select_EqualsExpected()
        {
            var table = new SqlTable <Class1>();

            var order = new
            {
                Id = new OrderItem {
                    Direction = OrderDirection.Asc, Index = 2
                },
                Value1 = new OrderItem {
                    Direction = OrderDirection.Desc, Index = 1
                },
                DoNotChange = (OrderItem)null
            };
            var orderMap  = OrderMap.FromOrder(table, order);
            var sqlFilter = SqlFilter.Construct(
                new { Ids = new[] { 1, 2 }, Value1 = "foo", Auto = (int?)null }
                );

            var whereSql = new[]
            {
                Conditions.Or(sqlFilter[f => f.Ids].IsNull(), table[m => m.Id].EqualsAny(sqlFilter[f => f.Ids])),
                Conditions.Or(sqlFilter[f => f.Value1].IsNull(),
                              table[m => m.Value1].EqualsOne(sqlFilter[f => f.Value1]))
            };
            var sqlOptions = new SqlOptions {
                Dialect = SqlDialect.Postgres95
            };
            var sqlBuilder = SqlBuilder.Select
                             .Values(table)
                             .From(table)
                             .Where(whereSql)
                             .Order(orderMap)
                             .Offset(5)
                             .LimitBy(10);


            var actual   = sqlBuilder.BuildSql(sqlOptions);
            var expected = @"
            SELECT
              class1.id AS Id,
              class1.auto AS Auto,
              class1.value1 AS Value1,
              class1.value2 AS Value2,
              class1.do_not_change AS DoNotChange
            FROM foo.class1
            WHERE
                   (@Ids IS NULL OR class1.id = ANY(@Ids))
               AND (@Value1 IS NULL OR class1.value1 = @Value1)
            ORDER BY
              class1.value1 DESC,
              class1.id ASC
            OFFSET 5
            LIMIT 10
            ";

            Check(expected, actual);
            Assert.AreEqual("Id", sqlBuilder.SplitOn);
        }
Esempio n. 3
0
        public void SelectMultiple_Generic_EqualsExpected()
        {
            var sqlFilter = SqlFilter.Construct(new
            {
                Ids            = new[] { 1, 2 },
                Value1         = "foo",
                Auto           = (int?)null,
                Key            = Guid.Empty,
                DbGeneratedKey = 2
            });
            var sqlBuilder = SqlBuilder <Class1, Class2> .Select()
                             .CustomSql("/* SELECT */")
                             .From((table1, table2) => table1)
                             .CustomSql("/* FROM */")
                             .InnerJoin((table1, table2) => table2,
                                        (table1, table2) => table2[m => m.Key].EqualsOne(table1[m => m.Id]))
                             .CustomSql("/* JOIN */")
                             .Where((table1, table2) => Conditions.And(
                                        Conditions.Or(sqlFilter[f => f.Ids].IsNull(), table1[m => m.Id].EqualsAny(sqlFilter[f => f.Ids])),
                                        Conditions.Or(sqlFilter[f => f.Value1].IsNull(),
                                                      table1[m => m.Value1].EqualsOne(sqlFilter[f => f.Value1])),
                                        Conditions.Or(sqlFilter[f => f.Key].IsNull(), table2[m => m.Key].EqualsOne(sqlFilter[f => f.Key])),
                                        sqlFilter[f => f.DbGeneratedKey]
                                        .IsNull()
                                        .Or(table2[m => m.DbGeneratedKey].EqualsOne(sqlFilter[f => f.DbGeneratedKey])),
                                        table2[m => m.Key].ILike(sqlFilter[f => f.Key]), table2[m => m.Key].Like(sqlFilter[f => f.Key]),
                                        table2[m => m.Key].Lower().Like(sqlFilter[f => f.Key].Lower())))
                             .CustomSql("/* WHERE */")
                             .OrderBy((table1, table2) => table1[m => m.Id], OrderDirection.Asc)
                             .CustomSql("/* ORDER */")
                             .Offset(5)
                             .CustomSql("/* OFFSET */")
                             .LimitBy(10)
                             .CustomSql("/* LIMIT */")
                             .CustomSql("/* START */", SqlSelectPosition.Start);

            var sqlOptions = new SqlOptions {
                Dialect = SqlDialect.Postgres95
            };

            var actual   = sqlBuilder.BuildSql(sqlOptions);
            var expected = @"
            /* START */
            SELECT
	            class1.id AS Id,
	            class1.auto AS Auto,
	            class1.value1 AS Value1,
	            class1.value2 AS Value2,
	            class1.do_not_change AS DoNotChange,
	            class2.key AS Key,
	            class2.db_generated_key AS DbGeneratedKey,
	            class2.db_generated AS DbGenerated,
	            class2.value1 AS Value1,
	            class2.value2 AS Value2,
	            class2.code_generated AS CodeGenerated,
	            class2.do_not_change AS DoNotChange
            /* SELECT */
            FROM foo.class1
            /* FROM */
            INNER JOIN foo.class2 ON class2.key = class1.id
            /* JOIN */
            WHERE
	                (@Ids IS NULL OR class1.id = ANY(@Ids))
	            AND (@Value1 IS NULL OR class1.value1 = @Value1)
	            AND (@Key IS NULL OR class2.key = @Key)
	            AND (@DbGeneratedKey IS NULL OR class2.db_generated_key = @DbGeneratedKey)
	            AND class2.key ILIKE @Key
	            AND class2.key LIKE @Key
	            AND LOWER(class2.key) LIKE LOWER(@Key)
            /* WHERE */
            ORDER BY
	            class1.id ASC
            /* ORDER */
            OFFSET 5
            /* OFFSET */
            LIMIT 10
            /* LIMIT */
            ";

            Check(expected, actual);
            Assert.AreEqual("Id,Key", sqlBuilder.SplitOn);
        }