parser 2.0 (старая версия)	FAQ	авторы	ДОКУМЕНТАЦИЯ	в действии	куда говорить
архив новостей	полезные ссылки	хостинги	скачать	примеры	форум

О переносимости SQL запросов

Misha v.3 [16 февраля 2007]

Можно долго говорить о том, насколько необходимо писать код, работающий на разных платформах, много спорить, насколько он должен быть переносим и т.д.

В идеале, хотелось бы, чтобы написанное один раз работало везде и всегда, но... такое бывает не часто :)

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

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

Безусловно, переносимость не должна являться самоцелью. Если сервер, для которого вы пишите код, позволяет строить древовидные структуры одним запросом (Oracle например), то делать это парсером — глупо. SQL сервер сделает это быстрее, с меньшими требованиями к памяти, ваш код будет более компактным и т.д., но в любом случае стОит вынести процесс построения этого дерева в отдельный метод, чтобы если вдруг придется переносить код на сервер, не обладающий такими возможностями, то переделка осуществлялась бы в одном определенном, логически завершенном фрагменте программы.

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

MySQL-ный LIMIT настоятельно не рекомендуется писать непосредственно в запросах, т.к. такой функции нет в некоторых других серверах (oracle, informix). Ничего не мешает следующий запрос:

$tNews[^table::sql{
	SELECT
		id,
		name
	FROM
		news
	ORDER BY
		dt DESC
	LIMIT 5
}]

написать так:

$tNews[^table::sql{
	SELECT
		id,
		name
	FROM
		news
	ORDER BY
		dt DESC
}[
	$.limit(5)
]]

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

ПРАВИЛО 1: забудьте про LIMIT/OFFSET в запросах.

Далее. Когда вы пишете запрос к базе данных, и если вы используете какую-либо функцию сервера, которая не описана в SQL92 — выносите её в метод класса, предназначенного для работы с данным сервером баз данных.

В настоящий момент у меня написано несколько классов для SQL серверов, которые я использую. В каждом из этих классов описаны методы с одинаковыми именами, поэтому при перенесении кода, написанного для работы с MySQL на MSSQL я всего лишь подключаю другой класс и всё.

ПРАВИЛО 2: SQL специфические функции выносите в отдельные методы/классы.

Некоторые методы из класса mysql.p для MySQL:

@today[]
$result[CURDATE()]
#end @today[]

@now[]
$result[NOW()]
#end @now[]

@year[source]
$result[YEAR($source)]
#end @year[]

@month[source]
$result[DATE_FORMAT($source,'%m')]
#end @month[]

@ymd[source]
$result[DATE_FORMAT($source,'%Y-%m-%d')]
#end @ymd[]

<p>Методы с аналогичной функциональностью для MSSQL:
<code>@today[]
$result[CONVERT(char, GETDATE(), 111)]
#end @today[]

@now[]
$result[CONVERT(char, GETDATE(), 20)]
#end @now[]

@year[source]
$result[YEAR($source)]
#end @year[]

@month[source]
$result[MONTH($source)]
#end @month[]

@ymd[source]
$result[CONVERT(char, $source, 111)]
#end @ymd[]

Безусловно, у SQL серверов есть функции, которые очень хочется использовать и которых нет у других серверов БД, в этом случае я ищу варианты использовать немного другие, переносимые алгоритмы.

Например: если я пишу код форума с использованием MySQL и хочу в запросе устанавливать флажки — сегодня ли было добавлено сообщение, то я могу написать запрос следующим образом:

$tMessage[^table::sql{
	SELECT
		forum_message_id,
		name,
		....
		IF(^oSql.dateFormat[dt_published;'%Y-%m-%d'] = ^oSql.today[], 1, 0) AS new
	FROM
		forum_message
	...
}]

Однако при попытке перенести данный запрос на MSSQL я столкнусь со следующей проблемой: у MSSQL нет возможности отформатировать дату произвольным образом (или я не знаю этого, кто знает как это сделать — поделитесь)

Если же я перепишу этот запрос так:

$tMessage[^table::sql{
	SELECT
		forum_message_id,
		name,
		....
		IF(dt_published >= ^oSql.today[], 1, 0) AS new
	FROM
		forum_message
	...
}]

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

Еще пример:
Когда я писал код для MySQL и не знал об отсутствии у MSSQL форматных строк я писал следующие конструкции:

$tNews[^table::sql{
	SELECT
		id,
		name,
	FROM
		news
	WHERE
		^oSql.dateFormat[dt;%Y-%m] = '${year}-$month'
}]

Однако потом я сильно расстроился, когда узнал, что подобного форматирования у MSSQL нет, и мне пришлось переписывать код. Я мог бы переписать его только для MSSQL в конкретном проекте, но тогда, возможно, в будущем мне пришлось бы делать это ещё и ещё. Я дописал в sql-ные классы методы ^year[], ^month[], ^date[], ^ymd[] и изменил запрос так:

$tNews[^table::sql{
	SELECT
		id,
		name,
	FROM
		news
	WHERE
		^oSql.year[dt] = '$year'
		AND ^oSql.month[dt] = '$month'
}]

И для MSSQL этот запрос стал выглядеть абсолютно аналогично.

ВНИМАНИЕ!
Данный пример запроса очень плох с точки зрения производительности, т.к. SQL серверу приходится для каждой записи в таблице вычислять функции YEAR и MONTH, и лишь потом отбирать по её результатам то, что нужно. При таком запросе сервер не будет использовать индексы. Как написать запрос с подобным функционалом, но который будет использовать индексы вы можете узнать в примере новостной раздел для чайников.

Правда иногда не стОит извращаться с переделыванием логики запроса, а можно просто вынести запрос целиком в отдельный метод и при адаптации кода для работы с другим сервером БД просто переписать метод целиком.

ПРАВИЛО 3: забудьте о SELECT * FROM... как о страшном сне. Всегда перечисляйте требуемые вам поля даже когда пишите «временный» код.

Не только потому, что код незаметно из «временного» станет постоянным (плюс часто копируемым) и впоследствии у таблицы увеличится количество полей, из-за чего запрос будет доставать кучу совершенно не нужных данных (отъедая память), но ещё и потому, что после подобного запроса совершенно неочевидно, какие поля извлеклись, и кто-либо после вас (да и вы сами через некоторое время) разобраться c подобным кодом сможет с трудом.

Так уж сложилось что меня не совсем устраивал стандартный функционал ^table::sql{}, ^hash::sql{} & Co. Например мне хотелось периодически получать информацию о времени выполнения запросов, количестве запросов выполненных при формировании документа, собирать в лог информацию о «медленных» запросах, кешировать результаты сложных запросов, выполнять connect автоматически и т.д.

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

Для подключения соответствующего класса я в методе @auto[] корневого auto.p добавляю например строку (не забыв подключить соответствующий класс с помощью @USE):

$oSql[^MySql::create[$SQL.connect-string;
	$.sCacheDir[/../data/sql_cache]
]]

И у меня в $oSql создается экземпляр класса MySql, к методам которого я могу обращаться из любого места кода:

@main[]
<html>
^oSql.server{
^rem{ *** достали часть данных в парсерную таблицу $page *** }
<head>
	<title>$hPage.title</title>
</head>
	...
	^rem{ *** вызываем методы, который могут делать запросы к базе данных, т.к. соединение уже установлено. *** }
	^printPageMenu[]

	^rem{ *** добавляем запись в таблицу info *** }
	^oSql.void{INSERT INTO info (name) VALUES ('$form:name')}

	^rem{ *** достаем last_insert_id() и записываем его в поле sort_order последней добавленной записи *** }
	$iLastInsertedId(^oSql.setLastInsertId[info;sort_order])
	...
}
</html>

При использовании sql классов помещать запросы внутрь connect (который находится внутри метода server) стало не обязательно. При создании sql-объекта вы указываете строку подключения к SQL серверу, соответственно класс сам сможет установить соединение, если вы напишите запрос вне метода server. Необходимо помнить, что в этом случае каждый запрос будет выполнен как отдельная транзакция и если вам этого не нужно - то писать вокруг группы запросов, которые должны быть выполнены как одна транзакция вам придется.

Процедура соединения с SQL сервером (connect) для большинства серверов достаточно медленная операция, поэтому часто возникают опасения, не сильно-ли множетсвенные connect-ы замедляют процесс выполнения кода? Нет, сильного замедления не происходит, т.к. парсер кеширует единожды открытое соединение, и повторно его не выполняет, так что пользуйтесь на здоровье (тем не менее по привычке я использую один connect на страницу, если мне не требуются транзакции).

Для того, чтобы получать debug информацию нужно немного иначе делать инициализацию объекта:

$oSql[^MySql::create[$SQL.connect-string;
	$.bDebug(1)
	$.sCacheDir[/../data/sql_cache]
	^rem{ *** описание всех опций вы можете посмотреть в Sql.p перед конструктором create *** }
]]

и в методе @postprocess[] можно вызывать метод получения статистики:

@postprocess[sBody][oSqlLog]
$result[$sBody]
^if($oSql is "Sql"){
	^use[SqlLog.p]
	$oSqlLog[^SqlLog::create[$oSql]]
	^oSqlLog.log[
		$.iQueryTimeLimit(500)
		$.iQueriesLimit(25)
		$.iQueryRowsLimit(3000)
#		$.bExpandExceededQueriesToLog(1)
		^if(def $form:mode && ^form:tables.mode.locate[field;debug]){
			^rem{ *** если обратились с ?mode=debug то получаем и сохраняем информацию обо всех sql запросах на странице *** }
			$.sFile[/../data/sql.txt]
			$.bAll(1)
		}{
			^rem{ *** а по умолчанию в другой лог-файл пишем только информацию о проблемных страницах *** }
			$.sFile[/../data/sql.log]
		}
	]
}

При этом если к документу будет обращение с параметром ?mode=debug то в файле sql.txt будет сохранена информация обо всех запросах сделанных через объект $oSql для формировании этого документа, а в случае обычной работы сайта в файл sql.log будет записываться информация о запросах, время выполнения которых превышает 500 ms или если при генерации страницы было сделано запросов больше, чем задано в iQueriesLimit.

Соответственно я всегда использую $.bDebug(1) инициализацию и все sql запросы делаю через объект $oSql, а также периодически проверяю sql.log и вижу, какие запросы были написаны плохо и требуют исправлений.

Следует добавить, что при работе с mysql в случае вывода debug информации для каждого запроса делается explain и его результаты также пишутся в лог, т.е. при анализе логов сразу становится ясно на что нужно обращать внимание. Следует отметить, что explain делается только проблеммных запросов и лишь при выводе статистики, а не всех подряд запросов, и $.bDebug(1) для всех запросов лишь измеряет время/память, затрачиваемую на каждый запрос (т.е. это достаточно быстро и не сильно рессурсоемко).

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

Пару слов о кешировании результатов запросов (если вы не хотите его использовать, можно не указывать параметр sCacheDir при создании объекта).

Работать с SQL сервером очень удобно: информация хранится в структурированном виде и может быть получена произвольными частями, но иногда так получается, что оптимизировать сложных запрос без внесения избыточности сложно или невозможно. И если подобный запрос оперирует редкоизменяемыми данными, то простейший выход — кешировать его результаты.

В принципе SQL сервера имеют механизмы кеширования запросов, но птицеводы далеко не всегда имеют доступ к их настройкам, поэтому и в данном случае опять могут выручить данные sql классы, использовать которые достаточно просто:

$tResult[^oSql.table{
	SELECT
		...
	FROM
		table_1,
		table_2,
		table_3
	WHERE
		...
	GROUP BY
		...
	HAVING
		...
	ORDER BY
		...
}[
	$.limit(10)
][
	$.sFile[hardcore_query.txt]
	$.dInterval(1/24)
]]

В этом случае будет выполнен сложный sql запрос и его результаты будут сохранены в файле hardcore_query.txt. При повторном обращении (другой посетитель пришел смотреть страницу) sql запрос выполняться не будет, а результаты запроса сразу будут взяты из сохраненного файла.

Параметр кеширования говорит, что файл с результатами кеширования будет устаревать раз в час: после устаревания он будет удаляться и первое же обращение после его устаревания будет осуществлять sql запрос, а затем опять будет работать файловый кеш.

Доступны ещё несколько параметров: $.dtExpirationTime[время] очистит кеш-файл при первом обращении после указанного времени. Данный параметр можно указать совместно с dInterval, в этом случае кеш файл будет удаляться раз в указанный промежуток времени и при наступлении указанного времени.

Новые версии sql классов имеют функцию автокеширования: вы можете не указывать имя файла, а просто указать параметр $.bAuto(1), в этом случае имя файла будет сгенерировано автоматически из тела запроса и параметров limit/offset (void-запросы не кешируются). Однако имейте в виду, что кешировать запросы с параметрами, вводимыми пользователями (например поиск) не имеет особого смысла: вы получите большую кучу кеш-файлов, однако повторные запросы с такими-же условиями маловероятны.

В случае, если вы вдруг хотите включить автокеширование для всех запросов, и при этом не хотите писать параметр $.bAuto(1) кучу раз, вы можете указать опцию $.bCacheAuto(1) при создании объекта, и для всех запросов будет включена функция автокеширования, отключить которую вы сможете указав параметр запроса $.bAuto(0).

Для принудительного удаления кеш-файла можно использовать метод clear:

^oSql.clear[hardcore_query.txt]

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

В случае, если необходимо очистить все кеш-файлы — можно сделать это вызвав метод clear без параметров.

А теперь дошла очередь до ложки дегтя: новые классы не совместимы со старыми. Если вы хотите перейти на них, то вы должны переписать код создания объектов sql-классов и код вывода информации в лог-файлы. Параметры sql запросов имеют обратную совместимость со старым кодом, хотя для того, чтобы иметь обратною совместимость имен методов нужно использовать классы MySqlComp & Co вместо MySql & Co (более подробные инструкции по апгрейду вы сможете найти в архиве).

Скачать:

Sql.zip (30.10.2007  15,6 КБ) Классы для работы с MySQL, Oracle, MSSQL и PgSQL