parser
НовостиFAQАвторыДокументацияВ действииБиблиотека
ИнструментыПолезные ссылкиХостингиСкачатьПримерыФорум

Классы для работы с MySQL, Oracle, MSSQL и PgSQL

Автор: Misha v.3 [28 января 2013]
Версия: 2.8
Тэги: SQL

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

Так уж сложилось что меня не совсем устраивал стандартный функционал ^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 (13.01.2014  16 КБ)
Классы для работы с MySQL, Oracle, MSSQL и PgSQL


Электропочта:
Форма обратной связи