Введение

Запросы классов InterSystems Cache — это полезный инструмент, используемый для абстракции от непосредственно 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
• Cache Studio предоставляет мастер создания запросов классов

Использовать этот запрос из 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")
   }

Или сразу получать resultset с помощью сгенерированного метода queryNameFunc:

   Set resultset = ##class(Sample.Person).ByNameFunc("A") 
   While resultset.%Next() {
         Write !, resultset.%Get("Name")
   }

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

Call Sample.SP_Sample_By_Name('A')

Select * from Sample.SP_Sample_By_Name('A')

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

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

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

• Сложная логика определения того, какие записи должны попасть в результат. Поскольку в кастомном запросе метод, выдающий следующий результат запроса вы пишете сами на 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 ]

Где:

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

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

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

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

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

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

• Обход глобала
• Статический SQL
• Динамический SQL

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вызвать его из 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 за помощь в написании статьи.