Pull to refresh
0
InterSystems
InterSystems IRIS: СУБД, ESB, BI, Healthcare

Запросы классов в InterSystems Caché

Reading time 13 min
Views 4.9K
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 1SELECTMODE "RUNTIME"
     
COMPILEMODE "IMMEDIATE") [ SqlName SP_Sample_By_NameSqlProc ]
{
SELECT IDNameDOBSSN
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(statusDo $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, которая идет в поставке 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 1ROWSPEC "Id:%String,Prop1:%String,Prop2:%Integer") [ SqlName AllRecordsSqlProc ]
{
}


Метод queryNameExecute


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

ClassMethod queryNameExecute(ByRef qHandle As %BinaryargsAs %Status

Где:

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

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

ClassMethod AllRecordsExecute(ByRef qHandle As %BinaryAs %Status
{
    
Set qHandle ""
    
Quit $$$OK
}

Метод queryNameFetch


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

ClassMethod queryNameFetch(ByRef qHandle As %Binary
                           
ByRef Row As %List,
                           
ByRef AtEnd As %Integer 0As %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 %BinaryByRef Row As %ListByRef AtEnd As %Integer 0As %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 %BinaryAs %Status PlaceAfter = queryNameFetch ]

Где:

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

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

ClassMethod AllRecordsClose(ByRef qHandle As %BinaryAs %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 1ROWSPEC "Id:%String,Prop1:%String,Prop2:%Integer") [ SqlName AllStaticSqlProc ]
{
}
ClassMethod AllStaticExecute(ByRef qHandle As %BinaryAs %Status
{
    
&sql(DECLARE CURSOR FOR
        
SELECT IdProp1Prop2
        
FROM Utils.CustomQuery
     
)
     &sql(
OPEN C)
    
Quit $$$OK
}
ClassMethod AllStaticFetch(ByRef qHandle As %BinaryByRef Row As %ListByRef AtEnd As %Integer 0As %Status PlaceAfter = AllStaticExecute ]
{
    
#; INTO должен быть с FETCH
    
&sql(FETCH INTO :Id:Prop1:Prop2)
    
#; Проверяем дошли ли до конца данных   
    
If (SQLCODE'=0) {
        
Set AtEnd = 1
        
Set Row ""
        
Quit $$$OK
    
}
    
Set Row $Lb(IdProp1Prop2)
    
Quit $$$OK
}
ClassMethod AllStaticClose(ByRef qHandle As %BinaryAs %Status PlaceAfter = AllStaticFetch ]
{
    
&sql(CLOSE C)
    
Quit $$$OK
}

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


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

Пример с использованием динамического SQL для Utils.CustomQuery
Query AllDynamic() As %Query(CONTAINID 1ROWSPEC "Id:%String,Prop1:%String,Prop2:%Integer") [ SqlName AllDynamicSqlProc ]
{
}
ClassMethod AllDynamicExecute(ByRef qHandle As %BinaryAs %Status
{
    
Set qHandle ##class(%SQL.Statement).%ExecDirect(,"SELECT * FROM Utils.CustomQuery")
    
Quit $$$OK
}
ClassMethod AllDynamicFetch(ByRef qHandle As %BinaryByRef Row As %ListByRef AtEnd As %Integer 0As %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 %BinaryAs %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.StatusAs %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 за помощь в написании статьи.
Tags:
Hubs:
+14
Comments 0
Comments Leave a comment

Articles

Information

Website
www.intersystems.com
Registered
Founded
1978
Employees
1,001–5,000 employees
Location
США