Запити класів у InterSystems Caché

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 НА 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 метод повинен розташовуватися після Виконання методу, це важливо тільки при використанні статичного 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.CustomQueryQuery AllStatic() As %Query(CONTAINID = 1, ROWSPEC = "Id:%String,Prop1:%String,Prop2:%Integer".) [ SqlName = AllStatic, SqlProc ]
{
}
ClassMethod AllStaticExecute(ByRef qHandle As %Binary) As %Status
{
    &sqlDECLARE C CURSOR FOR
        SELECT Id, Prop1, Prop2
        FROM Utils.CustomQuery
     )
     &sqlOPEN C)
    Quit $$$OK
}
ClassMethod AllStaticFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = AllStaticExecute ]
{
    #; В повинен   FETCH
    &sqlFETCH 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 ]
{
    &sqlCLOSE C)
    Quit $$$OK
}

Динамічний SQL
Підхід полягає у використанні інших запитів класів і динамічного SQL. Актуально для випадків, коли крім власне запиту, який представимо у вигляді SQL, потрібно робити будь-які додаткові дії, наприклад, необхідно виконати SQL запит, але в кількох областях по черзі. Або перед виконанням запиту потрібна ескалація прав.

Приклад з використанням динамічного SQL Utils.CustomQueryQuery 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 * ВІД 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.CustomQueryClass 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 за допомогу в написанні статті.

Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.