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

Как известно, 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

Подробно смотреть здесь

Этот способ позволяет написать произвольный код для формирования данных на Caché ObjectScript, но метаданные возвращаемого ResultSet’а создаются компилятором на основе параметра %Query.#ROWSPEC, т.е. во время компиляции.

Второй способ. Использование %SQL.CustomResultSet

Подробнее об %SQL.CustomResultSet, пример реализации.

Способ аналогичен предыдущему, но метаданные формируются на основе определения класса-наследника %SQL.CustomResultSet — также, как и в предыдущем случае, во время компиляции.

Примечание: Аналогичным образом можно получать и SQL-данные:

При этом метаданные генерируются во время выполнения, но данные можно получить только из 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(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.DynamicQuery

  Class 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.Queries

  Class 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.Procedures

  Class 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- процедур, о которой я возможно расскажу в следующей статье.