Класс для динамического формирования SQL запросов

[2NetFly]

Класс для динамического формирования SQL запросов

Данный материал будет полезен в первую очередь тем, кто серьезно занимается веб-программированием и имеет отношение к разработке СУК и схожих приложений. Уже довольно долгое время использую описанную методику в своих разработках, и пришел момент, когда ее нужно доработать и усовершенствовать. Принимая во внимание тот факт, что n голов в n раз лучше, чем одна, решил выложить все это дело на всеобщее обозрение и обсуждение %)

Материал находится здесь: http://perldoc.info/forum/index.php?showtopic=3, обсудить и прокомментировать можно там же, либо в этой ветке.

Я использую perl, но реализовать и применять класс можно в любом языке.

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

 

[Crazy]

2NetFly, этот раздел называется "PHP и базы данных", а не "Perl и базы данных".

Ты что-то про PHP сказать хотел? Говори, не стесняйся.

 

[2NetFly]

Я повторюсь: "я использую perl, но реализовать и применять класс можно в любом языке" (уточню – в любом ОО языке). Я могу то же самое написать на php и выложить в тему – это как-то повлияет на ход дискуссии?

Я предлагаю обсудить не конкретную реализацию, а механизм в общем. Почему написал именно на этом форуме? Ответ прост – это один из наиболее посещаемых ресурсов по php, и, следовательно, по веб-программированию.

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

-~{}~ 14.11.04 23:41:

Постскриптум. Убедительная просьба не устраивать фанатских войн и придерживаться сабжа.

 

[tony2001]

легко заметить, что приведенный в посте #1 код значительно меньше, чем код класса + код в посте #6, соответственно, изначально декларированная цель

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

не достигнута.
в результате мы получаем еще большее усложнение кода и его поддержки.

 

[PNS]

Originally posted by 2NetFly
Постскриптум. Убедительная просьба не устраивать
фанатских войн и придерживаться сабжа.

Действительно, проблема стоящая - (это не только к веб-программированию имеет отношение.)
Посмотрю, обдумаю...

 

[2NetFly]

Originally posted by tony2001
не достигнута.
в результате мы получаем еще большее усложнение кода и его поддержки.

Принимая во внимание тот факт, что SQLC – это метод класса, описываемый один раз, а DBAL_IGNORE_UNDEF | DBAL_IGNORE_BLANK можно записать как DBAL_I_ALL (неудачные имена для констант – признаю), получим:
$self->SQLC({
From => ["user"],
Where =>[
[user_login, "=", $login, DBAL_I_ALL],
[user_email, "=", $email, DBAL_I_ALL]
],
Limit => [0, $limit]
});

что кажется громоздким исключительно из-за удобного форматрирования.

И давайте смотреть глубже. Я описал лишь самый простой случай. С одной стороны это дает всем возможность понять, что я имею в виду, с другой – не дает возможности оценить преимущества класса.

В реальном приложении таких запросов приходится конструировать как минимум десятки и описывать их первым способом как минимум нерационально.

Используя класс мы получаем более мощное, гибкое, простое и модернизируемое решение. Неудобство чтения и отладки запросов – есть такое дело. Однако, нужно понимать, что класс используется только для "динамических" запросов т.е. тех, в которых фигурируют переменные значения (в частном случае полученные от пользователя), а для "статических" нужно применять обычный синтаксис.

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

 

[tony2001]

>И давайте смотреть глубже. Я описал лишь самый простой случай.
лично мне хватает и его - ты еще раз привел пример абсолютно нечитаемого кода.

 

[Фанат]

а чем не устраивают плейсхолдеры в PEAR DB или mysqli?

 

[tony2001]

Фанат
это не совсем то.
плейсхолдеры - это просто параметры запроса, с их помощью нельзя добавить еще одно условие в WHERE или убрать LIMIT.

 

[Фанат]

а, понял.
и понял, в чем проблема.
Товарищ хочет упростить и так весьма простой механизм SQL.
изобретает механическое устройство для облегчения поднесения ложки ко рту.
Пожелаем ему удачи.

SQLC - неудачное название
SSQL будет правильнее.

 

[Макс]

Сам как-то обдумывал такой конструктор.
Решил пока остановится на следующем :
1. автоматом строятся лишь простые INSERT/UPDATE/DELETE - запросы - это упрщает написание кода
2. Ограничения (LIMIT в MySQL) для SELECT-запросов также делаются автоматом - чтобы в случае перехода на другую СУБД немного упростить себе жизнь
3. Также иногда автоматически генерируется оператор ORDER BY ... - удобно, если на странице может быть несколько вариантов сортироки

Все остальное предпочитаю писать ручками

 

[2NetFly]

Originally posted by Макс
Сам как-то обдумывал такой конструктор.
Решил пока остановится на следующем :
1. автоматом строятся лишь простые INSERT/UPDATE/DELETE.
...
Все остальное предпочитаю писать ручками

INSERT/UPDATE/DELETE – и у меня есть такое. SimpleInsert / ... / ... . Упрощает жизнь, когда в наличии есть хех необходимых параметров, но это немного другая история.

Я тоже предпочитал все писать ручками, пока не пришлось создавать десятки поисковых веб форм с 10-15 полями. Я хочу акцентировать внимание вот на каком моменте (в предыдущее сообщения я его добавил, посему, возможно, не все прочли):

Нужно понимать, что класс используется только для "динамических" запросов т.е. тех, в которых фигурируют переменные значения (в частном случае полученные от пользователя), а для "статических" нужно применять обычный синтаксис. Действительно, при создании обычных запросов не получаем ровным счетом никакой выгоды.

-~{}~ 15.11.04 01:15:

Originally posted by tony2001
>И давайте смотреть глубже. Я описал лишь самый простой случай.
лично мне хватает и его - ты еще раз привел пример абсолютно нечитаемого кода.

Как по мне, это не более читабельно и удобно. А если добавить сюда order – и подавно.

my (@where, $query, $where);

push(@where, "user_login = '$login'") if $login;
push(@where, "user_email = '$email'") if $email;

$where = join(' AND ', @where);
$query = "SELECT * FROM user " 
. ($where ? " WHERE $where " : '')
. ($limit ? " LIMIT $limit" : '');

 

[Фанат]

Так и писать надо, что, мол, класс для динамического формирования оператора WHERE

 

[2NetFly]

Originally posted by Фанат
а, понял.
и понял, в чем проблема.
Товарищ хочет упростить и так весьма простой механизм SQL.
изобретает механическое устройство для облегчения поднесения ложки ко рту.
Пожелаем ему удачи.

SQLC - неудачное название
SSQL будет правильнее.

Я хочу не упростить механизм, а избавить себя от решения тривиальных задач. Еще одно применение класса я описал в документе. Пример (для тех кто не захотел полностью читать):

Существует таблица, в которой хранятся документы. Существует таблица, в которой хранятся источники (источник указывается не для каждого документа). Я хочу вывести просто список документов и список документов с указанием источника (при этом условий в запросе может быть десятки). С обычным подходом я либо создаю два разных запроса (а ведь у меня много одинаковых условий), либо создаю универсальный запрос и имею потери в производительности (там, где мне не нужен источник). Опять же, тривиальная задача, я могу привести десятки _реальных_ примеров, где возникают подобные проблемы.

Обсуждаемым классом все решается в две строки.

-~{}~ 15.11.04 01:28:

Originally posted by Фанат
Так и писать надо, что, мол, класс для динамического формирования оператора WHERE

Почему только where?

Искать что:
Сортировать по:
Порядок сортировки:
Лимит от:
Лимит до:

From, Addit, GroupBy были добавлены для того, чтоб можно было применять технологию со сборкой запроса на основе маски (читайте выше). Тем более, формируются они одним методом.

 

[Alexandre]

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

ну почему нет, у меня в активе есть похожий класс. Сознаюсь, что автором является Дмитрий Прокудин.
Сам класс - является частью CMS - по этому его отдельное представление будет не совсем логично. Тем более, что на мой взгляд, вся CMS должна быть переделана, по этому класс требует полнейшей переработки.

Извините - начал флеймить ):
2NetFly я веду это к тому, что идея не нова, но полезна!
Функциональность:
реализация режимов:
INSERT
SELETE
REPLACE (UPDATE)

конструкции:
WEHRE (критерии)
ORDER BY (критерии)

короче - полная реализация работы с БД,
динамическая реализация большинства SQL запросов
даже есть возможность работы с реализацией выборки: master ->detail

 

[Frol]

http://pear.php.net/package/DB_QueryTool

 

[2NetFly]

Originally posted by Alexandre
ну почему нет, у меня в активе есть похожий класс. Сознаюсь, что автором является Дмитрий Прокудин.
Сам класс - является частью CMS - по этому его отдельное представление будет не совсем логично. Тем более, что на мой взгляд, вся CMS должна быть переделана, по этому класс требует полнейшей переработки.

Извините - начал флеймить ):
2NetFly я веду это к тому, что идея не нова, но полезна!
Функциональность:
реализация режимов:
INSERT
SELETE
REPLACE (UPDATE)

конструкции:
WEHRE (критерии)
ORDER BY (критерии)

короче - полная реализация работы с БД,
динамическая реализация большинства SQL запросов
даже есть возможность работы с реализацией выборки: master ->detail

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

Я не утверждаю, что идея нова и ее авторство принадлежит мне %) Я призываю делиться опытом по данному вопросу и совместно реализовывать полезный класс.

Внимание необходимо акцентировать на запросе SELECT. Во-первых, это наиболее используемый запрос. Во-вторых, "динамические" запросы бывают зачастую данного типа.

Для остальных запросов все достаточно просто:

Update(table(s), params, where);
Delete(table, where);
Insert(table, params);

Где table – это таблица (массив таблиц), params – хеш (ключ – имя поля, значение – значение), where – соответствующая структура в общем случае, либо поле=>значение в частном. Очень удобно работать с данными методами имея хеш параметров плюс ко всему можно навсегда забыть про квоттинг.

Но это так, оффтопик %) Лучше поговорим о сабже.

-~{}~ 15.11.04 17:35:

Originally posted by Frol
http://pear.php.net/package/DB_QueryTool

Смотрел еще до того, как создать данную тему. Почему мне не подходит:
1. Громоздко.
2. Неудобно (для моих задач) и не предназначено для создания запросов из шаблонов в виду необходимость последовательного вызова методов для каждого оператора. Специальная структура подходит для данной задачи гораздо лучше.
3. Не решает поставленных задач. Например, в том же DB_DataObject проблема пропуска "пустых" where остается актуальной:
$obj>whereAdd("field = $undef_value");
С DB_QueryTool та же ситуация.

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

-~{}~ 15.11.04 17:53:

Почитал про MDB_QueryTool. Лень приводит к одинаковым результатом %) Уже два года использую специальный хеш для описания "коротких имен" полей таблицы:

(
     resource => 
    {
        '_' => 'resource',
        id => 'resource_id',
        mask => 'resource_mask',
        name => 'resource_name',
        value => 'resource_value',
        type => 'resource_type',
        ext => 'resource_ext'
    }
...
);

Приятно, что я не одинок %) Только вот в документации к модулю написано, что сделано это для того, чтоб не править код при изменении имен полей, а на самом деле такой хеш может найти более широкое применение и позволяет решить очень многие задачи.

 

[svetasmirnova]

Originally posted by 2NetFly
Я призываю делиться опытом по данному вопросу и совместно реализовывать полезный класс.

Запоздало возникла мысль как сделать public интерфейс обсуждаемого класса читабельнее.
(Хотя насколько читабельнее - это вопрос, конечно )

$obj - объект обсуждаемого класса

$obj->rows[n]->column           //выбрать строку n в колонке column
$obj->rows->column('some')      //выбрать строки, у которых column='some'
$obj->rows->ne->column('some')  //выбрать строки, у которых column!='some'
//имитация AND
$obj->rows->column1('some')->column2('other') или
$obj->rows->columns($obj->column1->some(), $obj->column2->other()) или
$obj->rows->columns($obj->column1('some'), $obj->column2('other'))
//имитация OR
$obj->rows->column1('some')->or->column2('other') или
$obj->rows->columns($obj->column1->some(), $obj->or->column2->other()) или
$obj->rows->columns($obj->column1('some'), $obj->or->column2('other'))
//смешанные
$obj->rows->column1('some')->column2('other')->or->column3('yet_another') или
$obj->rows->columns($obj->column1->some(), $obj->column2->other(), $obj->or->column3->other()) или
$obj->rows->columns($obj->column1('some'), $obj->column2('other'), $obj->or->column3('yet_another'))

Реализацию представляю пока только при помощи перегрузки методов и достаточно тяжеловесную.

IMHO
Несмотря на критику и удобство использования языка SQL независимо (в командной строке, например), идеи подобных классов будут возникать до тех пор, пока выборка из базы не станет прозрачно встроенной в другие языки программирования.

 

[_RVK_]

2NetFly
Динамические запросы это действительно полезно. Некоторое время назад тоже написал класс, в котором реализованно нечно подобное.
Например запрос вида:
SELECT :field FROM :table WHERE :field1=:val1 {AND :field2=:val2} ....

То что в фигурных скобках это блоки. Любой блок можно включить/отключить. Блоки нумеруются с 0 попорядку.
В запросе присутствуют так же параметры, начинающиеся со знака ":". Их значение можно так же изменить. Вот пример кода:

$sql = 'SELECT :field FROM :table WHERE :field1=:val1 {AND :field2=:val2}{ AND :field3=:val3}'; 
$fields=array('field'=>$field,'table'=>$table,'field1'=>$f1,'val1'=>$val1,'field2'=>$f2,'val2'=>$val2,'field3'=>$f3,'val3'=>$val3);
$off = array(1); //Отключаем блок "AND :field3=:val3"
$res = $q->exec_sql($sql,$fields,$off)
while ($data = $res->fetch_field($field))  {
    print $data;
}

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