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é реализуются с использованием хранимых процедур по следующей схеме:

    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- процедур, о которой я возможно расскажу в следующей статье.
    InterSystems
    InterSystems IRIS: СУБД, ESB, BI, Healthcare

    Comments 5

      +1
      По сигнатуре SQL-процедуры неизвестно, что она может вернуть, это становится известно только во время выполнения процедуры.
      Входные и выходные параметры хранимой процедуры можно посмотреть не вызывая её: скриншот.
        0
        Ну ладно, известно что может вернуть, но неизвестно, что она вернет на самом деле. Когда процедура вызывается по xDBC, сначала она передает метаданные, и вот в них уже точная информация о выходных данных. Здесь речь о том что список выходных параметров с точки зрения xDBC не обязан быть статичным.
          0
          Взглянув на сигнатуру хранимой процедуры, не вызывая её, я могу (должен) определить набор входных/выходных параметров.

          Конкретно для Вашей ХП я вижу один входной параметр и ни одного выходного.
          Отсюда я могу сделать вывод, что набор выходных полей будет либо динамическим либо пустым.
          Так же, как по наличию синтаксиса args... в методе я могу догадаться о наличии неопределённого числа аргументов.

          Но если окажется, что ХП вначале говорит, что вернёт три поля определённых типов, а в итоге возвращает два или двадцать полей неважно уже каких типов, то это будет вводить в заблуждение.
            0
            Но если окажется, что ХП вначале говорит, что вернёт три поля определённых типов, а в итоге возвращает два или двадцать полей неважно уже каких типов, то это будет вводить в заблуждение.
            Согласен, некрасиво получится. Но протоколом xDBC такой сценарий предусмотрен. Вообще, суть немного в другом. XDBC позволяет делать динамические ХП, а Caché из коробки при NoSQL-доступе к данным — нет. И статья как раз о том, как обойти это ограничение.
        0
        Вообще, суть немного в другом. XDBC позволяет делать динамические ХП, а Caché из коробки при NoSQL-доступе к данным — нет. И статья как раз о том, как обойти это ограничение.
        Мной изначально предполагалось, что Вы как-то учтёте в конкретном предложении статьи сделанное уточнение.

        Only users with full accounts can post comments. Log in, please.