О переносимости 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 подобным кодом сможет с трудом.

В итоге появились SQL классы, позволящие помимо унификации получать информацию о времени выполнения запросов, количестве запросов выполненных при формировании документа, собирать в лог информацию о «медленных» запросах, кешировать результаты сложных запросов, выполнять connect автоматически и т.д.