Где в интернете можно обсудить dynamic sql automatic checking?

6yrop

SergeRRRRRR

hprt

6yrop

hprt

  

sql = "select a.*";



if (b) 

	sql += ", b.*";



sql += " from ";



if (c)

	sql += "c as a";

else

	sql += "a";



if (b)

	sql += " inner join b on b.id = a.id ";



sql += " where 1 = 1";



if (d) 

	sql += " and a.type_id = 1";

// Добавь сюда еще пяток условий

6yrop

var b = bool.Parse(Console.ReadLine;

var c = bool.Parse(Console.ReadLine;

var d = bool.Parse(Console.ReadLine;

foreach (var record in QExecutor.MaterializeReader(

    command => new {b, c, d},

    p => {

        var q = new Q(@"from ");



        if (p.c)

            q._("c as a");

        else

            q._("a");



        if (p.b)

            q._(" inner join b on b.id = a.id ");



        q._(" where 1 = 1");



        if (p.d)

            q._(" and a.type_id = 1");



        return q.Select(_ => new {

                               id = _._<int>("a."

                               type_id = _._<int>("a.")

                           });

    }

    Console.WriteLine(record.id + ", " + record.type_id);

6yrop

var b = bool.Parse(Console.ReadLine;

var c = bool.Parse(Console.ReadLine;

var d = bool.Parse(Console.ReadLine;

foreach (var record in QExecutor.MaterializeReader(

    command => new {b, c, d},

    p => BuildQuery(p.b, p.c, p.d).Select(_ => new {

                                                       id = _._<int>("a."

                                                       type_id = _._<int>("a.")

                                                   }

    Console.WriteLine(record.id + ", " + record.type_id);



if (b)

    foreach (var record in QExecutor.MaterializeReader(

        command => new {c, d},

        p => BuildQuery(true, p.c, p.d).Select(_ => new {

                                                            id = _._<int>("a."

                                                            type_id = _._<int>("a."

                                                            id_b = _._<int>("b.id as ")

                                                        }

        Console.WriteLine(record.id + ", " + record.type_id + ", " + record.id_b);



static Q BuildQuery(bool b, bool c, bool d)

{

    var q = new Q(@"from ");



    if (c)

        q._("c as a");

    else

        q._("a");



    if (b)

        q._(" inner join b on b.id = a.id "



    q._(" where 1 = 1");



    if (d)

        q._(" and a.type_id = 1");

    return q;

}

hprt

6yrop

FMTONLY сломается, если ты работаешь с временными таблицами

6yrop

придется писать метод, перебирающий варианты.

hprt

6yrop

Это простой случай, когда у тебя есть, условно, 5 переменных, которые влияют на возможный запрос. И то, тебе надо писать перебор значений, чтобы все посмотреть.

Добавится новое условие - тебе придется метод переписывать.

Это можно на NUnit сделать том же, даже ничего писать самому не надо.

hprt

6yrop

Если набор колонок варьируется, то либо надо применять C# dynamic, либо вот так

6yrop

Если я насчет NUnit неправ - подскажи, в чем могучее отличие твоего кода от тех же Values

hprt

if (sort_by == "Amount" && dir == "asc")

    sql += "order by Amount";



if (sort_by == "Amount" && dir == "desc")

    sql += "order by Amount desc";



if (sort_by == "Date" && dir == "asc")

    sql += "order by Date asc, Amount desc";



if (sort_by == "Date" && dir == "desc")

    sql += "order by Date desc, Amount desc";

6yrop

например, сортировка

На практике в большинстве случаев оказывается достаточно булевских параметров, параметров типа перечисления и параметров типа IOption. Писать такие классы как RedSwitch требуется в небольшом проценте случаев.

И потом, перебирать ВСЕ варианты - слишком накладно.

Т.е., скажем, все условия where можно проверить разом

6yrop

Более того тест на запрос не надо писать.

ava3443

из source code-а получить полный список собранных запросов

6yrop

kokoc88

все кто попробовал с таким кодом работать очень довольны и просят добавки перевести его под .NET 2.0, чтобы использовать в старых проектах

6yrop

Это они тебе в лицо говорят, сходи с ними хоть на одну пьянку...

6yrop

Если эти проверки требуют такого ужасного (нечитаемого) кода, как ты привёл выше, то может ну нах эти проверки? Имхо не стоит оно того. Жизнь одна, зачем её тратить на работу с таким кодом?

kokoc88

кстати, хорошим тоном будет, если ты запостишь свой вариант кода. Будет о чем говорить.

6yrop

Тебе уже говорили

kokoc88

а кода нет

6yrop

kokoc88

где взять эталонный код на ADO.NET?

kokoc88

регулярно устраиваем, и часто обсуждаем эти темы

6yrop

var modelCode = Console.ReadLine;



//чистый ADO.NET

using (var connection = new SqlConnection(QExecutor.ConnectionString

{

    connection.Open;

    using (var command = new SqlCommand

    {

        command.Connection = connection;

        command.CommandText = "SELECT ModelId FROM Model WRE ModelCode = @ModelCode";

        command.Parameters.Add(new SqlParameter("@ModelCode", SqlDbType.NVarChar) { Size = -1, Value = modelCode });

        using (var reader = command.ExecuteReader

            while (reader.Read

                Console.WriteLineint)reader["ModelId"] + 1);

    }

}



//ControllableQuery

using (var connection = new SqlConnection(QExecutor.ConnectionString

{

    connection.Open;

    using (var command = new SqlCommand

    {

        command.Connection = connection;

        var query = Func.Invoke(

            new { modelCode = command.Param(modelCode) },

            p => new Q(@"FROM Model WRE ModelCode = ")._(p.modelCode).Select(_ => new {ModelId = _._<int>};

        command.CommandText = query.CommandText;

        using (var reader = command.ExecuteReader

            while (reader.Read

                Console.WriteLine(query.Materialize(reader).ModelId + 1);

    }

}

  

kokoc88

ок, попробую, но шансов мало на конструктивное обсуждение ...

        command.Parameters.Add(new SqlParameter("@ModelCode", SqlDbType.NVarChar) { Size = -1, Value = modelCode });

command.CommandText = query.CommandText;
     using (var reader = command.ExecuteReader
     while (reader.Read
     Console.WriteLine(query.Materialize(reader).ModelId + 1);

kokoc88

без простого рефакторинга базы

id = _._<int>("a."

type_id = _._<int>("a."

id_b = _._<int>("b.id as ")

6yrop

Почему не AddWithValue?

Don't Forget to Specify the Length!
When the parameter has any of the data types char, varchar, nchar, nvarchar, binary or varbinary, you need to specify the length of the parameter, whereas for fixed-length data types you leave it out.
Well, if you try, you may find that you always can leave out the length. You may also get the idea to use the method AddWithValue, which permits you to define the parameter and value in a single call. Do not fall into the trap of using these shortcuts!

kokoc88

http://www.sommarskog.se/dyn-search-2005.html#CLR
Специалист по высоконагруженным системам не знал этого?

6yrop

Не знал чего, простите? У меня в проекте с планами запросов со строками всё в порядке, ЧЯДНТ?

kokoc88

проблема не в самих планах, а в кэше планов. Там же всё ясно написано.

Я начинаю сомневаться в твоей способности читать.

6yrop

6yrop

Сделай скринкаст

kokoc88

я на твой вопрос про AddWithValue ответил? Ты согласен, что при прочих равных лучше не использовать AddWithValue?

kokoc88

о , оказывается этот формат передачи информации не безнадежен

6yrop

kokoc88

для вот этого кода?

6yrop

Сделай скринкаст того, как ты в коде сверху переименовываешь алиас таблицы с "a" на "e".

SELECT  *

FROM    Model a

WRE   ModelId IN ( SELECT ModelId

                     FROM   Model a

                     WRE  a.ModelId = 0 )

  

SELECT  *

FROM    Model a

WRE   ModelId IN ( SELECT ModelId

                     FROM   Model e

                     WRE  a.ModelId = 0 )

  

kokoc88

Сейчас, да, это не решается. Но в принципе проблема решается через более тесную интеграцию с Visual Studio через Roslyn. А где гарантия, что "смысловые" unit-тесты такое отловят?

6yrop

2. Сделай скринкаст того, как ты в коде сверху переименовываешь алиас таблицы с "a" на "e".

6yrop

3. Почему в примере сравнения с ADO.NET ты не полностью использовал свою библиотеку?

public static IEnumerable<TRecordset> MaterializeReader<TParams, TRecordset>(

    Func<SqlCommand, TParams> paramsFunc, Func<TParams, IQuery<TRecordset>> recordsetFunc)

{

    using (var sqlCommand = new SqlCommand

    {

        var query = recordsetFunc(paramsFunc(sqlCommand;

        sqlCommand.CommandText = query.TransformText;

        using (var connection = new SqlConnection(ConnectionString

        {

            connection.Open;

            sqlCommand.Connection = connection;

            using (var sqlDataReader = sqlCommand.ExecuteReader

                while (sqlDataReader.Read

                    yield return query.GetReader(sqlDataReader).Materialize;

        }

    }

}

  

6yrop

1. Какой вывод о своём поделии и о себе ты делаешь из исключительно негативной реакции на твои потуги посетителей этого раздела?

hprt

6yrop

4. Cпециалист по базам данных не знал, что при проектировании столбцы типа ModelCode никто не делает NVARCHAR?

5. Где в твоём сравнении с ADO.NET валидация длины строки при использовании твоего решения? Добавь это в пример.

6yrop

Sql Server Data Tools видел?

Да, динамический сиквел вроде не парсит, но может быть он и не нужен?

6yrop

рефакторинг, причем не на уровне "поменяли колонку, смотрим, что сломалось",

kokoc88

http://www.screencast.com/t/vKknGEffN6O

Какую технология для работы с SQL СУБД мы будем использовать завтра в новом проекте? В 5-ом разделе есть некоторый список технологий, смотрим на него, сравниваем. ... Упс, а Controllable Query то нам лучше подходит.

Пока "посетители этого раздела" не предложат свои варианты, разговаривать не о чем.

Я ответил на все твои вопросы. Теперь твоя очередь. Лучше всего, если будет код.

6yrop

Задача: считать modelCode с консоли и вывести ModelId + 1 в консоль.

6yrop

При этом твоя же библиотека отсылала тебя к номерам строк на C#, которые не связаны с ошибкой. Она же не смогла определить, что SQL ошибка теперь в клаузе FROM, оставшись на SELECT. Особенно порадовало изменение INNER JOIN и WRE, которые на самом деле на строке 40 и 47, а твоя библиотека показывает на строку 15.

kokoc88

, ты смешен, ты просто не хочешь понимать те сообщение, которые выдает моя тулза. Ну это твои личные проблемы. Вменяемым людям сообщения и номера строк понятны.

Dasar

И как мне тут переименовать столбец базы и алиас таблицы в запросе?

hprt

kokoc88

и эту цель сейчас достигает.

Потерял, конечно, на простоте синтаксиса, но по другому на универсальном языке едва ли получится.

Dasar

мы все давно убедились, что без юнит тестов всё равно ничего не получается.

6yrop

Могу написать стандартный код для вызова процедуры, конечно. Он будет понятнее твоего

kokoc88

Так же как статическая типизация не отменяет юнит-тесты, но здорово уменьшает их кол-во, и позволяет получить вменяемую гарантию на работоспособность кода при всех вариантах

имхо, обычно над качеством кода никто не заморачивается (пользователи особо не пищат, ну и достаточно поэтому большинству и не понятно зачем какие-то телодвижения делает..

hprt

hprt

6yrop

может тебя просто в мсдн послать?

Dasar

При таком усложнении синтаксиса процент ошибок намного выше, чем при использовании простых юнит тестов.

hprt

            using (SqlConnection conn = new SqlConnection("connection string"

            {

                SqlCommand cmd = new SqlCommand("dummy_procedure", conn);

                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@dummy", "Special for ");

                cmd.ExecuteNonQuery;



            }

kokoc88

например, самое простое - это клеить sql-строки руками, но при таком способе большинство ошибок приводят к утечке данных клиентов на сторону.

в данном случае, решается стандартная задача: ошибки из runtime-а перетаскиваются на этап разработки, а то что при этом суммарно ошибок становится больше (за счет более навороченного синтаксиса) - это не критично. Главное, что сильно падает кол-во ошибок уровня runtime.

kokoc88

Ты просто запутаешься где-нибудь в дебрях этого говна

Dasar

Очевидно, что в данном случае эта задача не решается: проверки типов для этого не достаточно.

kokoc88

Необходимо лишь, чтобы инструменты кардинально уменьшали вероятность пролезания в runtime основных ограничений человека: забывчивость, леность, неточность и т.д.

Dasar

Очевидно, предлагаемое поделие кардинально увеличивают эту вероятность, особенно ленивость.

kokoc88

если за каждую найденную plain-текст склейку вставляют, то леность сама проходят.

Dasar

Dasar

kokoc88

легко автоматизировать поиск "некошерного" доступа к БД

kokoc88

данный подход нужен когда sql-запросов в коде десятки тысяч, что нормально для бизнес-приложений, но редкость для других приложений.

Dasar

Каким именно образом?

kokoc88

есть еще всякие приблизительные методы.

Dasar

6yrop

using (SqlConnection conn = new SqlConnection("connection string"
2{
3 SqlCommand cmd = new SqlCommand("dummy_procedure", conn);
4 cmd.CommandType = System.Data.CommandType.StoredProcedure;
5 cmd.Parameters.AddWithValue("@dummy", "Special for ");
6 cmd.ExecuteNonQuery;
7}

Задача: считать modelCode с консоли и вывести ModelId + 1 в консоль.

6yrop

Допустим, заебало меня делать всякие q_Q, и я написал вместо одного:
q._(" where 1 = 1");
    if (d)
     q._(" and a.type_id = 1");
другое:
q._(" where 1 = 1" + (d ? " and a.type_id = 1" : "";

    public class Q

    {

        public readonly StringBuilder Builder;



        public Q(string value)

        {

            Builder = new StringBuilder(value);

        }



        public Q _(string value)

        {

            Builder.Append(value);

            return this;

        }



        public Q _<T>(IParam<T> param)

        {

            return _(param.ToSql;

        }

    }

  

kokoc88

Подходят оба варианта.

6yrop

Нет, в нашем примере второй вариант не подходит.

hprt

6yrop

Вот когда ты оплатишь, тогда тебе и будет решение твоей задачи.

hprt

luna89

postgres=# create table test as select id from generate_series(1,1000000) t(id);

SELECT 1000000

postgres=# create index testi on test(id);

CREATE INDEX

postgres=# prepare testq(integer) as select * from test where ($1 is null or id = $1);

PREPARE

postgres=# explain execute testq(1234456);

                               QUERY PLAN

------------------------------------------------------------------------

 Bitmap Heap Scan on test  (cost=96.10..4793.98 rows=5000 width=4)

   Recheck Cond: (id = 1234456)

   ->  Bitmap Index Scan on testi  (cost=0.00..94.85 rows=5000 width=0)

         Index Cond: (id = 1234456)

(4 rows)





postgres=# explain execute testq(null);

                          QUERY PLAN

--------------------------------------------------------------

 Seq Scan on test  (cost=0.00..14425.00 rows=1000000 width=4)

(1 row)

Dasar

Как видим, в не самом продвинутом постгресе все хорошо с планами такого рода препаренных запросов.

luna89

Чуть запрос усложнишь и хороший план летит к черту.

6yrop

Шурика волновало, что СУБД не делает constant folding для препаренных запросов.

Dasar

create table test as select id from generate_series(1,1000000) t(id);

6yrop

Dasar

declare @rank int

set @Rank =12



select id, rank, data 

from temp

where @rank is null or Rank = @Rank

declare @rank int

set @Rank =12



select id, rank, data 

from temp

where Rank = @Rank

Dasar

Кстати, разработчики BLToolkit говорили, что подставляют константы при генерации SQL из LINQ

6yrop

это вроде палка о двух концах. если подставлять константы, то каждый запрос становится уникальный, и у sql bd не получается использовать авто-кэширование планов запросов.

luna89

postgres=# drop table test;

DROP TABLE

postgres=# create table test as

postgres-# select

postgres-#      id,

postgres-#      md5(id||'foo') as foo,

postgres-#      md5(id||'bar') as bar,

postgres-#      lpad('',100,md5(id||'baz' as baz

postgres-# from generate_series(1,1000000) t(id);

SELECT 1000000

postgres=# create index testi on test(id,bar);

CREATE INDEX

postgres=# prepare testq(integer,text) as select * from test where ($1 is null or id = $1) and ($2 is null or bar = $2);

ERROR:  prepared statement "testq" already exists

postgres=# deallocate testq;

DEALLOCATE

postgres=# prepare testq(integer,text) as select * from test where ($1 is null or id = $1) and ($2 is null or bar = $2);

PREPARE

postgres=# explain execute testq(100500,'qq');

                             QUERY PLAN

---------------------------------------------------------------------

 Bitmap Heap Scan on test  (cost=7.39..105.39 rows=25 width=100)

   Recheck Cond: id = 100500) AND (bar = 'qq'::text

   ->  Bitmap Index Scan on testi  (cost=0.00..7.38 rows=25 width=0)

         Index Cond: id = 100500) AND (bar = 'qq'::text

(4 rows)

luna89

у меня вот уже такой запрос хочет table scan

ava3443

постгрес как-то это делает

Dasar

6yrop

в очередной раз сделал для себя вывод - держаться подальше от MSSQL

luna89

получение колонок id, foo, bar, baz не может быть выполнено только из индекса testi, т.к. там хранятся только колонки id, bar. Обязательно еще должен быть запрос к кластерному индексу для получения колонок foo, baz.
Либо testi является кластерным индексом, но тогда понятно почему план запросов всегда делается через него.

kokoc88

Обязательно еще должен быть запрос к кластерному индексу для получения колонок foo, baz.

Dasar

в первой строке плана heap scan. Heap это и есть сама таблица.

Dasar

kokoc88

можешь дальше продолжать верить в магию.

ava3443

Andbar

1) select * from test where (:id is null or id = :id) and (:bar is null or bar = :bar);

6yrop

Query.New(

    p => {

        var q = new Q("select a.*");



        IQuery<IRecord> result;

        if (p.b)

        {

            q._(", b.id as id_b");

            result = q.Query<IBRecord>

        }

        else

            result = q.Query<IARecord>



        q._(" from ");



        if (p.c)

            q._("c as a");

        else

            q._("a");



        if (p.b)

            q._(" inner join b on b.id = a.id ");



        q._(" where 1 = 1");



        if (p.d)

            q._(" and a.type_id = 1");

        return result;

    },

    new {b, c, d}

    ).Execute;



interface IRecord

{

    T Match<T>(Func<IARecord, T> a, Func<IBRecord, T> b);

}



private interface IARecord : IRecord

{

    int id { get; }

    int type_id { get; }

}



private interface IBRecord : IARecord

{

    int id_b { get; }

}

mbolik1

Сегодня я проверил это на постгресе:

This is another situation where EXECUTE can be used to force a new plan to be generated for each execution.

6yrop

Долго думал как может работать эта чёрная магия.

luna89

This is another situation where EXECUTE can be used to force a new plan to be generated for each execution.

6yrop

6yrop

luna89

специально для вас всё расписали, как раз PostgreSQL

Allow the planner to generate custom plans for specific parameter values even when using prepared statements (Tom Lane)
In the past, a prepared statement always had a single "generic" plan that was used for all parameter values, which was frequently much inferior to the plans used for non-prepared statements containing explicit constant values. Now, the planner attempts to generate custom plans for specific parameter values. A generic plan will only be used after custom plans have repeatedly proven to provide no benefit. This change should eliminate the performance penalties formerly seen from use of prepared statements (including non-dynamic statements in PL/pgSQL).

6yrop

Это старая статья. Мой пример работает начиная с версии 9.2.

6yrop

проблема не разрешима в принципе

6yrop

explain execute testq(1234456);