SQL-доступ к NoSQL-данным: реализация SQL-процедуры в Caché с динамическим определением возвращаемых метаданных

в 15:57, , рубрики: cache, cos, intersystems cache, jdbc, nosql, odbc, sql, Блог компании InterSystems, метки: , , , , , ,

Как известно, Caché можно использовать как реляционную СУБД, в том числе через JDBC/ODBC драйверы, с возможностью исполнения произвольных SQL-запросов и вызова SQL-процедур.
Известно также, что все данные в Caché хранятся в многомерных разреженных массивах — глобалах. Это позволяет в случае недостаточной производительности отдельно взятой SQL-процедуры не использовать стандартный CachéSQL-движок, а переписать ее код исполнения на языке серверной бизнес-логики Caché ObjectScript (COS), в котором можно реализовать оптимальный алгоритм выполнения SQL-процедуры, часто используя более оптимальные NoSQL-структуры данных (глобалы).
Однако в стандартной библиотеке классов Caché существует одно ограничение: для SQL-процедур, в которых отбор выполняется самописным COS-кодом, необходимо определять набор возвращаемых полей на этапе компиляции — т.е. нет возможности динамически задать метаданные для SQL-процедуры, работающей с NoSQL структурами.

О том, как снять это ограничение, рассказано под катом.

Работа с SQL-процедурами в Caché

Запросы через JDBC/ODBC к нереляционным структурам Caché реализуются с использованием хранимых процедур по следующей схеме:

image

Такая хранимая процедура может возвращать один или несколько наборов записей (ResultSet'ов), либо скалярное значение.

В качестве примера вызовем хранимую процедуру sample.SP_Sample_By_Name из области Samples, используя одно из средств для работы с ODBC:

image

По сигнатуре SQL-процедуры неизвестно, что она может вернуть, это становится известно только во время выполнения процедуры.

Caché позволяет делать методы класса хранимыми SQL-процедурами как возвращающими значение, так и возвращающими ResultSet. Например, так объявляется хранимая процедура, возвращающая ResultSet:

ClassMethod SomeSqlProc( p1 As %Integer 0) [ ReturnResultsetsSqlProc ]

С помощью этой конструкции можно написать код на Caché ObjectScript, который можно вызывать через ODBC как хранимую процедуру, которая вернет ResultSet (или несколько).

В Caché существует два стандартных способа формирования NoSQL-данных, возвращаемых в виде ResultSet:

Первый способ. Использование class queries

Использование class queries

ClassMethod SomeSqlProc( p1 As %Integer 0) [ ReturnResultsetsSqlProc ]
{
  
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

Использование %SQL.CustomResultSet

ClassMethod SomeSqlProc( p1 As %Integer 0) [ ReturnResultsetsSqlProc ]
{
  
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-данные:

Получение SQL-данных

ClassMethod SomeSqlProc( p1 As %Integer 0) [ ReturnResultsetsSqlProc ]
{
  
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.Query

    Class 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(scsc
        
      
    }
      
    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(scsc
      
    }
      
    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.DynamicQuery

    Class User.DynamicQuery Abstract ]
    {

    Query Query(p1 As %IntegerAs User.Query
    {
    }

    ClassMethod QueryExecute(ByRef qHandle As %Binaryp1 As %IntegerAs %Status
    {
      
    /// Делаем все приготовления
      ;…
      /// Формируем ROWSPEC
      
    RowSpec "ID:%Integer,date:%TimeStamp,Info:%String"
      
      
    qHandle("rowspec")=RowSpec
      
    /// Формируем строку формальных параметров, константа
      
    qHandle("params")="p1:%Integer"
      
      
    q $$$OK
    }

    ClassMethod QueryClose(ByRef qHandle As %BinaryAs %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) [ ReturnResultsetsSqlProc ]
    {
      
    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.Queries

    Class User.Queries Abstract ]
    {

    Query NoSQL(ColCount As %IntegerAs User.Query
    {
    }

    ClassMethod NoSQLExecute(ByRef qHandle As %BinaryColCount As %IntegerAs %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 %BinaryAs %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.Procedures

    Class User.Procedures Extends %Persistent
    {

    ClassMethod ProcNoSQL(p1 As %Integer) [ ReturnResultsetsSqlName proc_nosqlSqlProc ]
    {
      
    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 "") [ ReturnResultsetsSqlName proc_sqlSqlProc ]
    {
      
    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:

    image

    Заключение

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

Автор: ARechitsky

Источник

* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js