Как известно, Caché можно использовать как реляционную СУБД, в том числе через JDBC/ODBC драйверы, с возможностью исполнения произвольных SQL-запросов и вызова SQL-процедур.
Известно также, что все данные в Caché хранятся в многомерных разреженных массивах — глобалах. Это позволяет в случае недостаточной производительности отдельно взятой SQL-процедуры не использовать стандартный CachéSQL-движок, а переписать ее код исполнения на языке серверной бизнес-логики Caché ObjectScript (COS), в котором можно реализовать оптимальный алгоритм выполнения SQL-процедуры, часто используя более оптимальные NoSQL-структуры данных (глобалы).
Однако в стандартной библиотеке классов Caché существует одно ограничение: для SQL-процедур, в которых отбор выполняется самописным COS-кодом, необходимо определять набор возвращаемых полей на этапе компиляции — т.е. нет возможности динамически задать метаданные для SQL-процедуры, работающей с NoSQL структурами.
О том, как снять это ограничение, рассказано под катом.
Работа с SQL-процедурами в Caché
Запросы через JDBC/ODBC к нереляционным структурам Caché реализуются с использованием хранимых процедур по следующей схеме:
Такая хранимая процедура может возвращать один или несколько наборов записей (ResultSet'ов), либо скалярное значение.
В качестве примера вызовем хранимую процедуру sample.SP_Sample_By_Name из области Samples, используя одно из средств для работы с ODBC:
По сигнатуре SQL-процедуры неизвестно, что она может вернуть, это становится известно только во время выполнения процедуры.
Caché позволяет делать методы класса хранимыми SQL-процедурами как возвращающими значение, так и возвращающими ResultSet. Например, так объявляется хранимая процедура, возвращающая ResultSet:
ClassMethod SomeSqlProc( p1 As %Integer = 0) [ ReturnResultsets, SqlProc ]
С помощью этой конструкции можно написать код на Caché ObjectScript, который можно вызывать через ODBC как хранимую процедуру, которая вернет ResultSet (или несколько).
В Caché существует два стандартных способа формирования NoSQL-данных, возвращаемых в виде ResultSet:
Первый способ. Использование class queries
{
if '$isobject($Get(%sqlcontext)) { set %sqlcontext = ##class(%ProcedureContext).%New() }
Set query = ##class(%ResultSet).%New("User.SomeClass:Query")
Do query.Execute(p1)
do %sqlcontext.AddResultSet(query)
}
Подробно смотреть здесь
Этот способ позволяет написать произвольный код для формирования данных на Caché ObjectScript, но метаданные возвращаемого ResultSet’а создаются компилятором на основе параметра %Query.#ROWSPEC, т.е. во время компиляции.
Второй способ. Использование %SQL.CustomResultSet
{
if '$isobject($Get(%sqlcontext)) { set %sqlcontext = ##class(%ProcedureContext).%New() }
Set query = ##class(User.MyResultSet).%New(,p1)
do %sqlcontext.AddResultSet(query)
}
Подробнее об %SQL.CustomResultSet, пример реализации.
Способ аналогичен предыдущему, но метаданные формируются на основе определения класса-наследника %SQL.CustomResultSet — также, как и в предыдущем случае, во время компиляции.
Примечание: Аналогичным образом можно получать и SQL-данные:
{
s sqltext="SELECT * FROM dbo.Classname" ##; Подготавливаем текст запроса
if '$isobject($Get(%sqlcontext)) { set %sqlcontext = ##class(%ProcedureContext).%New() }
Set query = ##class(%ResultSet).%New("%DynamicQuery:SQL")
Do query.Prepare(sqltext)
Do query.Execute()
do %sqlcontext.AddResultSet(query)
}
При этом метаданные генерируются во время выполнения, но данные можно получить только из SQL.
Таким образом, если мы хотим формировать метаданные результата в рантайме и использовать произвольный Caché ObjectScript для формирования данных, то как видно — имеющихся в поставке Caché средств недостаточно.
Решение задачи
Есть 4 варианта решения проблемы:
- во время выполнения создавать класс, содержащий class query со сгенерированным “на лету” ROWSPEC;
- во время выполнения создавать класс, унаследованный от %SQL.CustomResultSet с необходимым набором полей;
- реализовать альтернативу %SQL.CustomResultSet, которая будет формировать метаданные во время выполнения основываясь на параметрах вызова, а не во время компиляции;
- реализовать альтернативу %Query, которая будет формировать метаданные во время выполнения.
Я выбрал последний способ — он показался мне наиболее элегантным (забегая вперед, без костылей обойтись все же не удалось).
Для начала создадим класс User.Query и унаследуем его от %Query – чтобы не переписывать реализацию всего %Query. При использовании %Query потребителем (%ResultSet’ом) запрашиваются метаданные через два класс-метода: GetInfo и GetODBCInfo. В классе-наследнике необходимо написать альтернативные реализации этих методов. Путем нескольких экспериментов (это проще чем разбираться в генераторах) я выяснил про параметры GetInfo(.colinfo, .parminfo, .idinfo, .qHandle, extoption, .extinfo):
- colinfo — в него надо сложить $lb( $lb(name,typeid,caption), … ), где name — внутреннее имя поля, typeid — идентификатор типа Caché, caption — заголовок столбца;
- parminfo — в него надо сложить $lb( $lb(name,typeid), … ) — тот же формат, что и в предыдущем пункте, но без заголовка;
- idinfo — в него можно сложить $lb(0,0) (системная информация, что-то связанное с индексом, будем считать, что его нет);
- qHandle — многомерный локальный массив, формируется программистом;
- остальное можно не трогать (кажется, для объектных ссылок, в отсутствие объектов необязательно).
С GetODBCInfo все аналогично, там чуть больше полей, а результат надо складывать в одноуровневые списки, но в целом все так же.
Для того чтобы вернуть из GetInfo и GetODBCInfo правильные метаданные нужно мной найдены несколько не вполне очевидных приемов, которые в основном приведены ниже:
- Чтобы получить идентификатор типа Caché (typeid), нужно вызвать $$externaltype^%apiOLE(ctype,.type,«0»), где ctype — имя типа в Caché (например %String[ссылка на класс %стринг]). Функция положит идентификатор в type.
Прежде чем узнавать идентификатор, тип (ctype) нужно нормализовать (привести к виду Package.Class), это можно сделать макросом $$$NormalizeClassname(ctype)
Чтобы получить информацию для GetODBCInfo, надо вызвать
GetODBCColInfo^%ourODBC(ctype, .colParms, .colODBCTypeName, .colODBCType, .maxLen, .precision, .scale),
где ctype – имя типа в Caché, не обязательно нормализованное.Поскольку мы хотим сформировать метаданные (имена полей и типы) динамически, нашему Query необходимо передать информацию о них. Самый очевидный способ для этого – параметр qHandle. Через него и будем передавать информацию о ResultSet’е. Для этого программист в своей реализации выполнения запроса (QueryExecute) должен сформировать строку ROWSPEC для требуемых полей и строку формальных параметров запроса (по аналогии с ROWSPEC) и положить их в qHandle(“rowspec”) и qHandle(“params”) соответственно.
В итоге получаем следующую реализацию класса User.Query:
Класс User.QueryClass User.Query Extends %Query
{ ClassMethod GetInfo(ByRef colinfo As %List, ByRef parminfo As %List, ByRef idinfo As %List, ByRef qHandle As %Binary, extoption As %Integer = 0, ByRef extinfo As %List) As %Status
{
if $get(qHandle("colinfo"))=""
{
set RowSpec = qHandle("rowspec")
set qHandle("colinfo")=""
set sc=$$$OK
for i=1:1:$length(RowSpec,",")
{
set col=$piece(RowSpec,",",i)
set name="p"_i
set ctype=$$$NormalizeClassname($select($piece(col,":",2)'="":$piece(col,":",2),1:"%String"))
set sc = $$externaltype^%apiOLE(ctype,.type,"0")
quit:$$$ISERR(sc)
set caption=$piece(col,":",1)
set qHandle("colinfo")=qHandle("colinfo")_$listbuild($listbuild(name,type,caption))
}
quit:$$$ISERR(sc) sc
}
if $get(qHandle("parminfo"))=""
{
set Params = qHandle("params")
set qHandle("parminfo")=""
set sc=$$$OK
for i=1:1:$length(Params,",")
{
set col=$piece(Params,",",i)
set name="p"_i
set ctype=$$$NormalizeClassname($select($piece(col,":",2)'="":$piece(col,":",2),1:"%String"))
set sc = $$externaltype^%apiOLE(ctype,.type,"0")
quit:$$$ISERR(sc)
set qHandle("parminfo")=qHandle("parminfo")_$listbuild($listbuild(name,type))
}
quit:$$$ISERR(sc) sc
}
set colinfo = qHandle("colinfo")
set parminfo = qHandle("parminfo")
set idinfo = $listbuild(0,0)
quit $$$OK
} ClassMethod GetODBCInfo(ByRef colinfo As %List, ByRef parminfo As %List, ByRef qHandle As %Binary)
{ if $get(qHandle("colinfoodbc"))=""
{
set RowSpec = qHandle("rowspec")
set qHandle("colinfoodbc")=$listbuild($LENGTH(RowSpec,","))
for i=1:1:$length(RowSpec,",")
{
set col=$piece(RowSpec,",",i)
set ctype=$select($piece(col,":",2)'="":$piece(col,":",2),1:"%String")
Do GetODBCColInfo^%ourODBC(ctype,.colParms,.colODBCTypeName,.colODBCType,.maxLen,.precision,.scale)
set bstr = "$Char(0,0,0,0,0,0,0,0,0,0,0,0)"
set name = $piece(col,":",1)
set qHandle("colinfoodbc")=qHandle("colinfoodbc")_$listbuild(name,colODBCType,precision,scale,2,name,"Query","%Library","",bstr)
}
}
if $get(qHandle("parminfoodbc"))=""
{
set Params = qHandle("params")
set qHandle("parminfoodbc")=$listbuild($LENGTH(Params,","))
for i=1:1:$length(RowSpec,",")
{
set col=$piece(Params,",",i)
set ctype=$select($piece(col,":",2)'="":$piece(col,":",2),1:"%String")
Do GetODBCColInfo^%ourODBC(ctype,.colParms,.colODBCTypeName,.colODBCType,.maxLen,.precision,.scale)
set name="p"_i
set qHandle("parminfoodbc")=qHandle("parminfoodbc")_$listbuild(colODBCType,precision,scale,2,name,1)
}
}
set colinfo = qHandle("colinfoodbc")
set parminfo = qHandle("parminfoodbc")
quit $$$OK
}}
Как применять класс User.Query
Использование User.Query аналогично использованию %Query, но при инициализации необходимо передать ему информацию для генерации метаданных.
Класс, использующий User.Query, должен выглядеть примерно так:Класс User.DynamicQueryClass User.DynamicQuery [ Abstract ]
{ Query Query(p1 As %Integer) As User.Query
{
} ClassMethod QueryExecute(ByRef qHandle As %Binary, p1 As %Integer) As %Status
{
/// Делаем все приготовления
;…
/// Формируем ROWSPEC
s RowSpec = "ID:%Integer,date:%TimeStamp,Info:%String"
s qHandle("rowspec")=RowSpec
/// Формируем строку формальных параметров, константа
s qHandle("params")="p1:%Integer"
q $$$OK
} ClassMethod QueryClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = QueryExecute ]
{
Quit $$$OK
} ClassMethod QueryFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = QueryExecute ]
{
/// Пишем обычный QueryFetch, как описано в документации по class queries
}
}///Код хранимой процедуры, вызывающей User.Query:
ClassMethod DynamicProc(p1 As %Integer = 0) [ ReturnResultsets, SqlProc ]
{
if '$isobject($Get(%sqlcontext)) { set %sqlcontext = ##class(%ProcedureContext).%New() }
Set query = ##class(%ResultSet).%New("User.DynamicQuery:Query")
Do query.Execute(p1)
do %sqlcontext.AddResultSet(query)
}Пример использования
Создадим в области Samples класс Queries. Он будет содержать только один запрос, так что его можно сделать абстрактным
Класс User.QueriesClass User.Queries [ Abstract ]
{ Query NoSQL(ColCount As %Integer) As User.Query
{
} ClassMethod NoSQLExecute(ByRef qHandle As %Binary, ColCount As %Integer) As %Status
{
set RowSpec = "Id:%Integer"
for colNum = 1:1:ColCount
{
set RowSpec=RowSpec_",p"_colNum_":%Integer"
}
set qHandle("rowspec")=RowSpec
set qHandle("params")="ColCount:%Integer"
kill ^||MyData(+##this)
for rowNum = 1:1:100 {
for colNum = 1:1:ColCount
{
set $list(^||MyData(+##this,rowNum),colNum)=$R(1000)
}
}
set qHandle("colcount") = ColCount
set qHandle("cursor") = $order(^||MyData(+##this,""))
quit $$$OK
} ClassMethod NoSQLClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = NoSQLExecute ]
{
kill ^||MyData(+##this), qHandle
Quit $$$OK
} ClassMethod NoSQLFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = NoSQLExecute ]
{
if qHandle("cursor") = ""
{
set Row = "", AtEnd = 1
quit $$$OK
}
set rowNum = qHandle("cursor")
set Row = $listbuild(rowNum)_^||MyData(+##this,rowNum)
set qHandle("cursor") = $order(^||MyData(+##this,rowNum))
Quit $$$OK
}}
Наш query принимает кол-во колонок, и возвращает 100 записей, заполненных случайными числами. Теперь напишем класс Procedures, который будет содержать метод класса-хранимую процедуру, использующую наш query.
Класс User.ProceduresClass User.Procedures Extends %Persistent
{ ClassMethod ProcNoSQL(p1 As %Integer) [ ReturnResultsets, SqlName = proc_nosql, SqlProc ]
{
if '$isobject($Get(%sqlcontext)) { set %sqlcontext = ##class(%ProcedureContext).%New() }
Set query = ##class(%ResultSet).%New("User.Queries:NoSQL")
Do query.Execute(p1)
do %sqlcontext.AddResultSet(query)
} ClassMethod ProcSQL(p1 As %String = "") [ ReturnResultsets, SqlName = proc_sql, SqlProc ]
{
set sqltext="SELECT ID, Name, DOB, SSN"
set sqltext=sqltext_" FROM Sample.Person"
set sqltext=sqltext_" WHERE (Name %STARTSWITH '"_p1_"')"
set sqltext=sqltext_" ORDER BY Name"
if '$isobject($Get(%sqlcontext)) { set %sqlcontext = ##class(%ProcedureContext).%New() }
Set query = ##class(%ResultSet).%New("%DynamicQuery:SQL")
Do query.Prepare(sqltext)
Do query.Execute()
do %sqlcontext.AddResultSet(query)
}}
Теперь созданную SQL-процедуру, выполняющую NoSQL запрос, можно вызывать через xDBC:
Заключение
Надеюсь предложенный мной способ создания NoSQL-запросов для SQL-процедур с динамическим определением окажется кому-то полезным, как он оказался полезным мне, при реализации конкретной практической задачи по улучшению производительности SQL- процедур, о которой я возможно расскажу в следующей статье.
Автор: ARechitsky
