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

Источник: habrahabr
ARechitsky

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

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

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

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

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

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

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

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

Использование %SQL.CustomResultSet
ClassMethod SomeSqlProc( p1 As %Integer = 0) [ ReturnResultsets, SqlProc ]
{
  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) [ ReturnResultsets, SqlProc ]
{
  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.Query
    Class User.Query Extends %Query
    {

    ClassMethod GetInfo(ByRef colinfo As %List, ByRef parminfo As %List, ByRef idinfo As %List, ByRefqHandle 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

    Наш 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:

    image

    Заключение

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

Страница сайта http://test.interface.ru
Оригинал находится по адресу http://test.interface.ru/home.asp?artId=31290