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

image

Введение

Запросы классов 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")

   
}
Кроме того, этот запрос можно вызвать из 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 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 за помощь в написании статьи.

Оригинал статьи на Habrahabr.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *