Запросы классов в InterSystems Caché14.12.2015 17:19

Andre Derain Landscape ear Chatou

Введение


Запросы классов InterSystems Caché — это полезный инструмент, используемый для абстракции от непосредственно SQL запросов в COS коде. В самом простом случае это выглядит так: допустим вы используете один и тот же SQL запрос в нескольких местах, но с разными аргументами.

Для того чтобы не писать его каждый раз, вы можете обозначить текст запроса как запрос класса и в дальнейшем обращаться к этому запросу по его имени. А ещё есть кастомные запросы, в которых логику получения следующей строки результатов вы пишете сами. Под катом я расскажу о том, как этим всем можно пользоваться.

Базовые запросы классов


Итак, базовые запросы классов — это метод представления SELECT SQL запросов. Они обрабатываются оптимизатором и компилятором SQL, как и обычные SQL запросы, но их проще вызывать из COS контекста. В определении класса это элементы типа Query (аналогично, например, Method или Property). Они определяются следующим образом:

  • Тип — %SQLQuery
  • В списке аргументов нужно перечислить список аргументов SQL запроса
  • Тип запроса — SELECT
  • Обращение к аргументу осуществляется через двоеточие (аналогично статическому SQL)
  • Определите параметр ROWSPEC — он содержит информацию о названиях и типах данных возвращаемых результатов, а также порядок полей
  • (Опционально) Определите параметр CONTAINID он равен порядковому номеру поля, содержащему Id. Если Id не возвращается, указывать CONTAINID не нужно
  • (Опционально) Определите параметр COMPILEMODE. Аналогичен такому же параметру в статическом SQL и определяет, когда компилируется SQL выражение. Если равен IMMEDIATE (по умолчанию), то компиляция происходит во время компиляции класса. Если равен DYNAMIC, то компиляция происходит перед первым выполнением запроса, аналогично динамическому SQL
  • (Опционально) Определите параметр SELECTMODE — декларацию формата результатов запроса
  • Добавьте свойство SqlProc, если хотите вызывать этот запрос как SQL процедуру
  • Установите свойство SqlName, если хотите переименовать запрос. По умолчанию имя запроса в SQL контексте: PackageName.ClassName_QueryName
  • Caché Studio предоставляет мастер создания запросов классов


Пример определения класса Sample.Person c запросом ByName который возвратит всех людей, имена которых начинаются на определённую букву

Class Sample.Person Extends %Persistent
{
Property Name As %String;
Property DOB As %Date;
Property SSN As %String;
Query ByName (name As %String = ») As %SQLQuery
    (ROWSPEC=«ID:%Integer, Name:%String, DOB:%Date, SSN:%String»,  
     CONTAINID = 1,  SELECTMODE = «RUNTIME»,  
     COMPILEMODE = «IMMEDIATE») [ SqlName = SP_Sample_By_Name,  SqlProc ]
{
SELECT ID,  Name,  DOB,  SSN
FROM Sample.Person
WHERE (Name %STARTSWITH : name)
ORDER BY Name
}
}


Использовать этот запрос из COS контекста можно следующим образом:

   Set statement=##class(%SQL.Statement).%New()
   Set status=statement.%PrepareClassQuery(«Sample.Person», «ByName»)
   If $$$ISERR(status) { Do $system.OBJ.DisplayError(status) }
   Set resultset=statement.%Execute(«A»)
   While resultset.%Next() {
         Write !,  resultset.%Get(«Name»)
   }


Кроме того, этот запрос можно вызвать из SQL контекста: 

Call Sample.SP_Sample_By_Name('A')


Этот класс можно найти в области SAMPLES, которая идет в поставке Caché. Вот собственно и всё о простых запросах. Теперь перейдём к кастомным запросам.

Кастомные запросы классов


Базовые запросы классов достаточны для большинства ситуаций. Однако, есть случаи, в которых вашему приложению необходим полный контроль над поведением запроса, в частности:

  • Сложная логика определения того, какие записи должны попасть в результат. Поскольку в кастомном запросе метод, выдающий следующий результат запроса вы пишете сами на COS, то и логика эта может быть сколь угодно сложной
  • Если вы получаете доступ к данным через API, формат которого вас не устраивает
  • Если данные хранятся в глобалах, без классов
  • Если для доступа к данным необходима эскалация прав
  • Если для доступа к данным необходимо запросить внешнее API
  • Если для доступа к данным необходим доступ к файловой системе
  • Необходимы какие-то дополнительные операции перед выполнением самого запроса (установление соединения, проверка прав и т.д.)


Итак, как же пишутся кастомные запросы классов? Для создания запроса queryName Вы определяете 4 метода, которые реализуют всю логику работы запроса, от создания и до уничтожения:

  • queryName — похож на базовый запрос класса, предоставляет информацию о запросе
  • queryNameExecute — осуществляет первоначальное инстанцирование запроса
  • queryNameFetch — осуществляет получение следующего результата
  • queryNameClose — деструктор запроса


Теперь об этих методах поподробнее.

Метод queryName


Метод queryName предоставляет информацию о запросе.

  • Тип — %Query
  • Оставьте определение пустым
  • Определите параметр ROWSPEC — он содержит информацию о названиях и типах данных возвращаемых результатов, а также порядок полей
  • (Опционально) Определите параметр CONTAINID он равен порядковому номеру поля, содержащему Id. Если Id не возвращается, указывать CONTAINID не нужно


В качестве примера будем создавать запрос AllRecords (те. queryName = AllRecords, и метод будет называться просто AllRecords), который будет по очереди выдавать все записи хранимого класса.

Для начала создадим новый хранимый класс Utils.CustomQuery:

Class Utils.CustomQuery Extends (%Persistent,  %Populate)
{
Property Prop1 As %String;
Property Prop2 As %Integer;
}


Теперь напишем описание запроса AllRecords:

Query AllRecords () As %Query(CONTAINID = 1,  ROWSPEC = «Id:%String, Prop1:%String, Prop2:%Integer») [ SqlName = AllRecords,  SqlProc ]
{
}


Метод queryNameExecute


Метод queryNameExecute производит всю необходимую инициализацию запроса. У него должна быть следующая сигнатура:

ClassMethod queryNameExecute (ByRef qHandle As %Binary,  args) As %Status


Где:

  • qHandle используется для сообщения с другими методами имплементации запроса
  • Этот метод должен привести qHandle в состояние, которое получает на вход метод queryNameFetch
  • qHandle может принимать значения OREF, переменной или многомерной переменной
  • args — это дополнительные параметры, передающиеся в запрос. Их может быть сколь угодно много или вообще не быть
  • Возвращается статус инициализации запроса


Вернёмся к нашему примеру. Есть много вариантов обхода экстента (далее будут описаны основные подходы к организации кастомных запросов), я предлагаю использовать обход глобала с помощью функции $Order. qHandle соответственно будет хранить текущий Id, в данном случае — пустую строку. arg не используем, так как какие-либо дополнительные аргументы не нужны. В результате получается:

ClassMethod AllRecordsExecute (ByRef qHandle As %Binary) As %Status
{
    Set qHandle = »
    Quit $$$OK
}


Метод queryNameFetch


Метод queryNameFetch возвращает один результат в формате $List. У него должна быть следующая сигнатура:

ClassMethod queryNameFetch (ByRef qHandle As %Binary,  
                           ByRef Row As %List,
                           ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = queryNameExecute ]


Где:

  • qHandle используется для сообщения с другими методами имплементации запроса
  • При выполнении запроса, qHandle принимает значения установленные queryNameExecute или предыдущим вызовом queryNameFetch
  • Row должен принять либо значение в формате %List, либо он должен быть равен пустой строке, если данных больше нет
  • AtEnd должен быть равен 1 при достижении конца данных
  • Ключевое слово PlaceAfter определяет положение метода в int коде (о компиляции и генерации int кода на хабре есть статья), Fetch метод должен располагаться после Execute метода, это важно только при использовании статического SQL, а точнее курсоров внутри запроса.


Внутри этого метода, в общем случае, выполняются следующие операции:

  1. Определяем, достигнут ли конец данных
  2. Если данные еще есть: Создаём %List и устанавливаем значение переменной Row
  3. Иначе, устанавливаем AtEnd равным 1
  4. Устанавливаем qHandle для последующих вызовов
  5. Возвращаем статус


В нашем примере это будет выглядеть следующим образом:

ClassMethod AllRecordsFetch (ByRef qHandle As %Binary,  ByRef Row As %List,  ByRef AtEnd As %Integer = 0) As %Status
{
    #;  Обходим глобал ^Utils.CustomQueryD
    #;  Записываем следующий id в qHandle,  а значение глобала с новым id в val
    Set qHandle = $Order(^Utils.CustomQueryD (qHandle),1, val)
    #;  Проверяем дошли ли до конца данных   
    If qHandle = » {
        Set AtEnd = 1
        Set Row = »
        Quit $$$OK
    }
    #;  Если нет,  формируем %List
    #;  val = $Lb (»,  Prop1,  Prop2) — см. Storage Definition
    #;  Row = $Lb (Id,  Prop1,  Prop2) — см. ROWSPEC запроса AllRecords
    Set Row = $Lb(qHandle,  $Lg(val,2),  $Lg(val,3))
    Quit $$$OK
}


Метод queryNameClose


Метод queryNameClose завершает работу с запросом после получения всех данных. У него должна быть следующая сигнатура:

ClassMethod queryNameClose (ByRef qHandle As %Binary) As %Status [ PlaceAfter = queryNameFetch ]


Где:

  • Caché выполняет этот метод после последнего вызова метода queryNameFetch
  • Этот метод — деструктор запроса
  • В имплементации этого метода, закройте используемые SQL курсоры, запросы, удалите локальные переменные
  • Метод возвращает статус


В нашем примере нужно удалить локальную переменную qHandle:

ClassMethod AllRecordsClose (ByRef qHandle As %Binary) As %Status
{
    Kill qHandle
    Quit $$$OK
}


Вот и всё. После компиляции класса, запрос AllRecords можно использовать аналогично базовым запросам класса — с помощью %SQL.Statement.

Логика кастомного запроса


Итак, как можно организовать логику кастомного запроса? Есть 3 основных подхода:

Обход глобала


Подход состоит в использовании функции $Order и подобных для обхода глобала. Его стоит использовать в случаях, если:

  • Данные хранятся в глобалах, без классов
  • Нужно уменьшить количество gloref — обращений к глобалам
  • Результаты должны/могут быть отсортированы по ключу глобала


Статический SQL


Подход состоит в использовании курсоров и статического SQL. Это может быть сделано в целях:

  • Упрощения чтения int кода
  • Упрощения работы с курсорами
  • Уменьшения времени компиляции (статический SQL вынесен в запрос класса и компилируется только один раз)


Особенности:

  • Курсоры, сгенерированные из запросов типа %SQLQuery именуются автоматически, например Q14
  • Все курсоры, используемые в рамках класса должны иметь разные имена
  • Сообщения об ошибках относятся ко внутренним именам курсоров, которые имеют дополнительный символ в конце названия. К примеру ошибка в курсоре Q140 скорее всего относится к курсору Q14
  • Используйте PlaceAfter и следите, чтобы декларация и использование курсора происходила в одной int программе
  • INTO должен располагаться вместе с FETCH, а не с DECLARE


Пример с использованием статического SQL для Utils.CustomQuery

Query AllStatic () As %Query(CONTAINID = 1,  ROWSPEC = «Id:%String, Prop1:%String, Prop2:%Integer») [ SqlName = AllStatic,  SqlProc ]
{
}
ClassMethod AllStaticExecute (ByRef qHandle As %Binary) As %Status
{
    &sql (DECLARE C CURSOR FOR
        SELECT Id,  Prop1,  Prop2
        FROM Utils.CustomQuery
     )
     &sql (OPEN C)
    Quit $$$OK
}
ClassMethod AllStaticFetch (ByRef qHandle As %Binary,  ByRef Row As %List,  ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = AllStaticExecute ]
{
    #;  INTO должен быть с FETCH
    &sql (FETCH C INTO : Id,  : Prop1,  : Prop2)
    #;  Проверяем дошли ли до конца данных   
    If (SQLCODE'=0) {
        Set AtEnd = 1
        Set Row = »
        Quit $$$OK
    }
    Set Row = $Lb(Id,  Prop1,  Prop2)
    Quit $$$OK
}
ClassMethod AllStaticClose (ByRef qHandle As %Binary) As %Status [ PlaceAfter = AllStaticFetch ]
{
    &sql (CLOSE C)
    Quit $$$OK
}


Динамический SQL


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

Пример с использованием динамического SQL для Utils.CustomQuery

Query AllDynamic () As %Query(CONTAINID = 1,  ROWSPEC = «Id:%String, Prop1:%String, Prop2:%Integer») [ SqlName = AllDynamic,  SqlProc ]
{
}
ClassMethod AllDynamicExecute (ByRef qHandle As %Binary) As %Status
{
    Set qHandle = ##class(%SQL.Statement).%ExecDirect(, «SELECT * FROM Utils.CustomQuery»)
    Quit $$$OK
}
ClassMethod AllDynamicFetch (ByRef qHandle As %Binary,  ByRef Row As %List,  ByRef AtEnd As %Integer = 0) As %Status
{
    If qHandle.%Next()=0 {
        Set AtEnd = 1
        Set Row = »
        Quit $$$OK
    } 
    Set Row = $Lb(qHandle.%Get(«Id»),  qHandle.%Get(«Prop1»),  qHandle.%Get(«Prop2»))
    Quit $$$OK
}
ClassMethod AllDynamicClose (ByRef qHandle As %Binary) As %Status
{
    Kill qHandle
    Quit $$$OK
}


Альтернативный подход — %SQL.CustomResultSet


Альтернативно, можно определить запрос как наследника класса %SQL.CustomResultSet. На хабре есть статья об использовании %SQL.CustomResultSet. Преимущества такого подхода:

  • Несколько более высокая скорость работы
  • Вся метаинформация берётся из определения класса, ROWSPEC не нужен
  • Соответствие принципам ООП


При создании наследника класса %SQL.CustomResultSet нужно выполнить следующие шаги:

  1. Определите свойства, которые будут соответствовать полям результата
  2. Определите приватные свойства, которые будут содержать контекст запроса, и не являться частью результата
  3. Переопределите метод %OpenCursor — аналог метода queryNameExecute, отвечающий за первоначальное создание контекста. В случае возникновения ошибок установите %SQLCODE и %Message
  4. Переопределите метод %Next — аналог метода queryNameFetch отвечающий за получение следующего результата. Заполните свойства. Метод возвращает 0, если данных больше нет, если есть, то 1
  5. Переопределите метод %CloseCursor — аналог метода queryNameClose, если это необходимо


Пример с использованием %SQL.CustomResultSet для Utils.CustomQuery

Class Utils.CustomQueryRS Extends %SQL.CustomResultSet
{
Property Id As %String;
Property Prop1 As %String;
Property Prop2 As %Integer;
Method %OpenCursor () As %Library.Status
{
    Set …Id = »
    Quit $$$OK
}
Method %Next (ByRef sc As %Library.Status) As %Library.Integer [ PlaceAfter = %Execute ]
{
    Set sc = $$$OK
    Set …Id = $Order(^Utils.CustomQueryD (…Id),1, val)
    Quit:…Id=» 0
    Set …Prop1 = $Lg(val,2)
    Set …Prop2 = $Lg(val,3)
    Quit $$$OK
}
}


Вызвать его из COS кода можно следующим образом:

    Set resultset= ##class(Utils.CustomQueryRS).%New()
    While resultset.%Next() {
        Write resultset.Id,!
    }


А ещё в области SAMPLES есть пример — класс Sample.CustomResultSet реализующий запрос для класса Samples.Person.

Выводы


Кастомые запросы позволяют решать такие задачи как абстракция SQL кода в COS и реализация поведения, сложно реализуемого одним только SQL.

Ссылки


Запросы классов
Обход глобала
Статический SQL
Динамический SQL
%SQL.CustomResultSet
Класс Utils.CustomQuery
Класс Utils.CustomQueryRS

Автор выражает благодарность хабраюзеру adaptun за помощь в написании статьи.

© Habrahabr.ru