Цитаты из википедии (1NF):
В данной статье будут рассмотрены стандартные способы ускорения SQL-запросов по следующим типам полей: строка, дата, простой список (в формате $LB), коллекции-cписки и коллекции-массивы.
Оглавление:
Рассмотрим вначале классический вариант на примере списка телефонов.
Создадим тестовые данные:
Теперь выведем список телефонов, разделённых запятой, для каждого имени:
или так:
Результат:
Построив индекс по phone, мы можем делать очень быстрый поиск по конкретному телефону. Единственный минус такого решения — в дублировании имён: чем больше элементов в списке, тем больше будет пухнуть наша БД.
Поэтому иногда бывает полезно хранить в одном поле сразу множество значений — это может быть список телефонов или его частей, паролей и т.д, — в виде строки с разделителем, и иметь при этом возможность делать быстрый поиск по отдельным значениям. Конечно, можно создать обычный индекс на такое поле и делать поиск подстроки в этой большой строке, но, во-первых, так как элементов может быть довольно много, то и длина индекса будет существенна, а во-вторых, такой индекс всё равно никак нам не поможет ускорить наш поиск.
Так как же быть?
Специально для таких случаев был введён особый вид индекса для полей, содержащих коллекции.
Коллекции могуть быть либо «настоящими» (встроенные list of <...> и array of <...>) либо «виртуальными».
В случае встроенных коллекций за формирование по ним такого индекса отвечает система, и программист не может этот процесс изменить, в случае же виртуальных — ответственность за формирование индекса ложится на программиста.
Простая строка с разделителем, дата, простой список — это и есть примеры таких «виртуальных» коллекций.
Итак, синтаксис индексов на коллекции следующий:
За процесс формирования такого индекса отвечает метод с именем propertynameBuildValueArray, который разработчик должен реализовать самостоятельно.
Общая сигнатура у метода следующая:
Где:
Как уже было сказано выше, для встроенных коллекций данный метод генерируется системой автоматически и имеет атрибут [Final], что не даёт возможности разработчику его переопределить.
Давайте построим такие индексы и посмотрим как их задействовать в наших SQL-запросах.
Создадим следующий класс:
Запустим в терминале метод Fill():
Как видим, в индекс попала строка не целиком, а отдельные её части. Таким образом, вы сами решаете как разбить на подстроки одну большую строку. Помимо строк с разделителем это могут быть и xml, и json или что-либо другое.
Содержимое нашей таблицы будет следующим:
Теперь попытаемся найти все строки, содержащие значение «a». Для этого обычно используют предикаты like '%xxx%' или [ 'xxx', например:
С учётом сказанного наш запрос примет следующий вид:
Конечно же, допустимы и более сложные условия, например:
(%Value %STARTSWITH 'а')
(%Value [ 'a' and %Value [ 'b')
(%Value in ('c','d'))
(%Value is null)
А теперь немного магии…
В методе BuildValueArray мы обычно заполняем массив array на основе значения в value.
Но что будет, если мы не будем следовать этому правилу?
Давайте попробуем такой пример:
После заполнения, содержимое таблицы окажется таким:
А теперь — внимание! — попробуем выполнить следующий запрос:
Нам вернётся:
В итоге мы видим, что часть данных видна в таблице, а часть — запрятана в индекс, но по которым мы, тем не менее, можем делать поиск.
Где же это нам может пригодиться?
Например, в индексе можно скрыть не один, как общепринято, а целый набор доступных заданному пользователю паролей или любую другую чувствительную информацию, которую нежелательно открывать посредством SQL. Конечно, для этого существуют и другие возможности, например GRANT column-privilege. Но в этом случае придётся использовать хранимые процедуры для доступа к защищённым полям.
Если посмотреть глобалы, в которых хранятся данные и индексы для нашей таблицы, то мы там не увидим значений наших ключей: «5.4», «fg» и т.д.:
Тогда зачем же мы их задавали?
Чтобы ответить на этот вопрос, немного модифицируем наш индекс и перезаполним таблицу.
Глобалы примут уже другой вид (приведу только глобал с индексами):
Отлично, теперь у нас хранятся как значения ключей, так и сами значения элементов. Как нам это может в дальнейшем пригодиться?
Например, в ранее предложенном варианте с паролями, кроме непосредственно самих паролей можно хранить дату, до которой будет действовать этот пароль, или что-то другое. И тогда в нашем запросе этот факт можно использовать следующим образом:
Где и что хранить — решать вам, единственное нужно помнить, что ключи уникальны, а значения — нет.
Кроме того, в «коллекционном» индексе так же как и в стандартном индексе, можно хранить дополнительные данные:
При таком варианте, указанный выше запрос, уже не будет обращаться к данным, а возьмёт всё из индекса, что тоже сэкономит время.
Казалось бы, а какое отношение даты имеют к коллекциям? Самое непосредственное, ведь зачастую приходится делать поиск только по дню, месяцу или году. Обычный индекс здесь нам не поможет, а вот «коллекционный» — очень даже будет к месту.
Давайте рассмотрим следующий пример:
Содержимое нашей таблицы после заполнения примет следующий вид:
Теперь очень просто, и главное очень быстро, можно делать поиск по конкретным частям даты, например вывести всех февральских именинников:
Результат:
В СУБД Caché существует специальный тип данных для простого списка (%List), который можно использовать вместо строки, если с выбором разделителя есть затруднения.
Использование такого поля не намного отличается от работы со строкой.
Рассмотрим небольшой пример:
Содержимое таблицы в режиме отображения ODBC будет выглядеть так:
В данном случае разделитель элементов не участвует, поэтому мы можем использовать любые символы в элементах.
При выводе поля типа %List в режиме ODBC в качестве разделителя используется параметр ODBCDELIMITER, который по умолчанию равен ",".
Например, при таком поле наша таблица будет выглядеть так:
Поиск элементов ничем не отличается от строки с разделителем:
Особо стоит отметить, что вариант с %INLIST пока не использует «коллекционные» индексы, а потому будет медленне вышепредложенного:
Перепишем пример выше, но вместо простого списка воспользуемся коллекцией-списком:
В этом примере почти всё то же самое, да не совсем. Особо следует обратить внимание на следующие вещи:
Как выше было отмечено, коллекция-список не даёт нам возможности использовать ключи. Коллекция-массив исправляет этот недостаток.
Создадим следующий класс:
Здесь нужно сделать несколько пояснений:
Итак, содержимое наших таблиц после метода Fill() будет следующим:
Таблица demo.test
Таблица demo.test_ArrayStr
Кажется, что имея вместо одной таблицы две, мы теперь вынуждены делать JOIN между ними, но это не так.
Учитывая предоставляемые СУБД Caché объектные расширения для SQL, наш тестовый запрос, выводящий поле str из demo.test для строк с ключом "-" и значением элемента «111», примет следующий вид:
Результат:
Как видим, ничего сложного и никаких JOIN, поскольку все наши данные фактически хранятся в одном глобале и Caché знает о «родственности» этих таблиц. Поэтому и на поля можно ссылаться из обеих таблиц. Поля test_ArrayStr в таблице demo.test на самом деле не существует, хотя мы и можем через него получить доступ к родственной таблице.
Описанный здесь механизм индексации широко используется и в некоторых системных классах, таких, например, как %Stream.GlobalCharacterSearchable, который обеспечивает индексацию текстового потока и поиск по нему через SQL. В статье специально не освещены примеры индексации коллекций на классы, в виду их большого разнообразия: встраиваемые, хранимые, потоки, пользовательские, с коллекциями коллекций и другие. Кроме того, со многими из них не всегда удобно и эффективно работать через SQL, потому автор не видит особой надобности в таких коллекциях кроме каких-то уж очень редких случаев. Также здесь не освещён полнотекстовый поиск, так как это другая область со своими индексами и подходом в работе через SQL. Кроме того были опущены примеры использования у свойств таких параметров как SqlListType и SqlListDelimiter, но пытливый читатель, надеюсь, cможет самостоятельно попробовать их в действии.
Полезные ссылки:
Каждое пересечение строки и столбца содержит ровно одно значение из соответствующего домена (и больше ничего).
Одно и то же значение может быть атомарным или неатомарным в зависимости от смысла этого значения. Например, значение «4286» является
- атомарным, если его смысл — «пин-код кредитной карты» (при разбиении на части или переупорядочивании смысл теряется)
- неатомарным, если его смысл — «набор цифр» (при разбиении на части или переупорядочивании смысл не теряется)
В данной статье будут рассмотрены стандартные способы ускорения SQL-запросов по следующим типам полей: строка, дата, простой список (в формате $LB), коллекции-cписки и коллекции-массивы.
Оглавление:
- Строка с разделителем
- Скрытие чувствительных данных
- Скрытие чувствительных данных (продолжение)
- Дата (время и т.д)
- Простой список
- Коллекция-список
- Коллекция-массив
- В заключение
Введение
Рассмотрим вначале классический вариант на примере списка телефонов.
Создадим тестовые данные:
create table cl_phones(tname varchar2(100), phone varchar2(30));
insert into cl_phones(tname,phone) values ('Андрей','867-843-25');
insert into cl_phones(tname,phone) values ('Андрей','830-044-35');
insert into cl_phones(tname,phone) values ('Андрей','530-055-35');
insert into cl_phones(tname,phone) values ('Максим','530-055-35');
insert into cl_phones(tname,phone) values ('Максим','555-011-35');
insert into cl_phones(tname,phone) values ('Ваня','530-055-31');
insert into cl_phones(tname,phone) values ('Ваня','531-051-32');
insert into cl_phones(tname,phone) values ('Ваня','532-052-33');
insert into cl_phones(tname,phone) values ('Ваня','533-053-35');
Теперь выведем список телефонов, разделённых запятой, для каждого имени:
SELECT
%exact(tname) tname,
LIST(phone) phonestr
FROM cl_phones
GROUP BY tname
или так:
SELECT
distinct %exact(tname) tname,
LIST(phone %foreach(tname)) phonestr
FROM cl_phones
Результат:
| tname | phonestr |
|---|---|
| Андрей | 867-843-25,830-044-35,530-055-35 |
| Ваня | 530-055-31,531-051-32,532-052-33,533-053-35 |
| Максим | 530-055-35,555-011-35 |
Поэтому иногда бывает полезно хранить в одном поле сразу множество значений — это может быть список телефонов или его частей, паролей и т.д, — в виде строки с разделителем, и иметь при этом возможность делать быстрый поиск по отдельным значениям. Конечно, можно создать обычный индекс на такое поле и делать поиск подстроки в этой большой строке, но, во-первых, так как элементов может быть довольно много, то и длина индекса будет существенна, а во-вторых, такой индекс всё равно никак нам не поможет ускорить наш поиск.
Так как же быть?
Специально для таких случаев был введён особый вид индекса для полей, содержащих коллекции.
Коллекции могуть быть либо «настоящими» (встроенные list of <...> и array of <...>) либо «виртуальными».
В случае встроенных коллекций за формирование по ним такого индекса отвечает система, и программист не может этот процесс изменить, в случае же виртуальных — ответственность за формирование индекса ложится на программиста.
Простая строка с разделителем, дата, простой список — это и есть примеры таких «виртуальных» коллекций.
Итак, синтаксис индексов на коллекции следующий:
INDEX idx1 ON (MyField(ELEMENTS));или
INDEX idx1 ON (MyField(KEYS));
За процесс формирования такого индекса отвечает метод с именем propertynameBuildValueArray, который разработчик должен реализовать самостоятельно.
Общая сигнатура у метода следующая:
ClassMethod propertynameBuildValueArray(value, ByRef valueArray) As %Status
Где:
- value — значение поля для разбивки на элементы;
- valueArray — результирующий массив, содержащий отдельные элементы.
Массив представляет собой набор ключ/значение вида:
array(key1)=value1
array(key2)=value2
и т.д.
Как уже было сказано выше, для встроенных коллекций данный метод генерируется системой автоматически и имеет атрибут [Final], что не даёт возможности разработчику его переопределить.
Давайте построим такие индексы и посмотрим как их задействовать в наших SQL-запросах.
Примечание: чтобы не оставалось артефактов от предыдущего примера, рекомендуется перед каждым новым полностью очищать глобалы и схему хранения у класса.
Строка с разделителем
Создадим следующий класс:
Class demo.test Extends %Persistent
{
Index iPhones On Phones(ELEMENTS);
Property Phones As %String;
ClassMethod PhonesBuildValueArray(
value,
ByRef array) As %Status
{
If value="" {
Set array(0)=value
}else{
Set list=$ListFromString(value,","),ptr=0
While $ListNext(list,ptr,item){
Set array(ptr)=item
}
}
Quit $$$OK
}
ClassMethod Fill()
{
&sql(truncate table demo.test)
&sql(insert into demo.test(phones)
select null union all
select 'a' union all
select 'b,a' union all
select 'b,b' union all
select 'a,c,b' union all
select ',,'
)
ZWrite ^demo.testD
ZWrite ^demo.testI
}
}
Запустим в терминале метод Fill():
USER>do ##class(demo.test).Fill()^demo.testD=6^demo.testD(1)=$lb("","")^demo.testD(2)=$lb("","a")^demo.testD(3)=$lb("","b,a")^demo.testD(4)=$lb("","b,b")^demo.testD(5)=$lb("","a,c,b")^demo.testD(6)=$lb("",",,")^demo.testI("iPhones"," ",1)=""^demo.testI("iPhones"," ",6)=""^demo.testI("iPhones"," A",2)=""^demo.testI("iPhones"," A",3)=""^demo.testI("iPhones"," A",5)=""^demo.testI("iPhones"," B",3)=""^demo.testI("iPhones"," B",4)=""^demo.testI("iPhones"," B",5)=""^demo.testI("iPhones"," C",5)=""Как видим, в индекс попала строка не целиком, а отдельные её части. Таким образом, вы сами решаете как разбить на подстроки одну большую строку. Помимо строк с разделителем это могут быть и xml, и json или что-либо другое.
Содержимое нашей таблицы будет следующим:
| ID | Phones |
|---|---|
| 1 | (null) |
| 2 | a |
| 3 | b,a |
| 4 | b,b |
| 5 | a,c,b |
| 6 | ,, |
select * from demo.test where Phones [ 'a'Но в таком случае не будет использоваться наш индекс iPhones. Чтобы его задействовать, необходимо использовать специальный предикат
select * from demo.test where Phones like '%a%'
FOR SOME %ELEMENT(нашеполе) (%VALUE = значениеэлемента)С учётом сказанного наш запрос примет следующий вид:
select * from demo.test where for some %element(Phones) (%value = 'a')В итоге, в силу использования специализированного индекса, скорость этого запроса по сравнению с предыдущими вариантами будет значительно выше.
Конечно же, допустимы и более сложные условия, например:
(%Value %STARTSWITH 'а')
(%Value [ 'a' and %Value [ 'b')
(%Value in ('c','d'))
(%Value is null)
А теперь немного магии…
Скрытие чувствительных данных
В методе BuildValueArray мы обычно заполняем массив array на основе значения в value.
Но что будет, если мы не будем следовать этому правилу?
Давайте попробуем такой пример:
Class demo.test Extends %Persistent
{
Index iLogin On Login(ELEMENTS);
Property Login As %String;
ClassMethod LoginBuildValueArray(
value,
ByRef array) As %Status
{
If value="Вася" {
Set array(0)="test1"
Set array(1)="test2"
Set array(2)="test3"
}ElseIf value="Петя" {
Set array("-")="111"
Set array("5.4")="222"
Set array("fg")="333"
}else{
Set array("key")="value"
}
Quit $$$OK
}
ClassMethod Fill()
{
&sql(truncate table demo.test)
&sql(insert into demo.test(login)
select 'Вася' union all
select 'Вася' union all
select 'Петя' union all
select 'Петя' union all
select 'Иван' union all
select 'Иван'
)
ZWrite ^demo.testD
ZWrite ^demo.testI
}
}
После заполнения, содержимое таблицы окажется таким:
| ID | Login |
|---|---|
| 1 | Вася |
| 2 | Вася |
| 3 | Петя |
| 4 | Петя |
| 5 | Иван |
| 6 | Иван |
select * from demo.test where for some %element(Login) (%value = '111')
Нам вернётся:
| ID | Login |
|---|---|
| 3 | Петя |
| 4 | Петя |
Где же это нам может пригодиться?
Например, в индексе можно скрыть не один, как общепринято, а целый набор доступных заданному пользователю паролей или любую другую чувствительную информацию, которую нежелательно открывать посредством SQL. Конечно, для этого существуют и другие возможности, например GRANT column-privilege. Но в этом случае придётся использовать хранимые процедуры для доступа к защищённым полям.
Скрытие чувствительных данных (продолжение)
Если посмотреть глобалы, в которых хранятся данные и индексы для нашей таблицы, то мы там не увидим значений наших ключей: «5.4», «fg» и т.д.:
^demo.testD=6^demo.testD(1)=$lb("","Вася")^demo.testD(2)=$lb("","Вася")^demo.testD(3)=$lb("","Петя")^demo.testD(4)=$lb("","Петя")^demo.testD(5)=$lb("","Иван")^demo.testD(6)=$lb("","Иван")^demo.testI("iLogin"," 111",3)=""^demo.testI("iLogin"," 111",4)=""^demo.testI("iLogin"," 222",3)=""^demo.testI("iLogin"," 222",4)=""^demo.testI("iLogin"," 333",3)=""^demo.testI("iLogin"," 333",4)=""^demo.testI("iLogin"," TEST1",1)=""^demo.testI("iLogin"," TEST1",2)=""^demo.testI("iLogin"," TEST2",1)=""^demo.testI("iLogin"," TEST2",2)=""^demo.testI("iLogin"," TEST3",1)=""^demo.testI("iLogin"," TEST3",2)=""^demo.testI("iLogin"," VALUE",5)=""^demo.testI("iLogin"," VALUE",6)=""Тогда зачем же мы их задавали?
Чтобы ответить на этот вопрос, немного модифицируем наш индекс и перезаполним таблицу.
Index iLogin On (Login(KEYS), Login(ELEMENTS));
Глобалы примут уже другой вид (приведу только глобал с индексами):
^demo.testI("iLogin"," -"," 111",3)=""^demo.testI("iLogin"," -"," 111",4)=""^demo.testI("iLogin"," 0"," TEST1",1)=""^demo.testI("iLogin"," 0"," TEST1",2)=""^demo.testI("iLogin"," 1"," TEST2",1)=""^demo.testI("iLogin"," 1"," TEST2",2)=""^demo.testI("iLogin"," 2"," TEST3",1)=""^demo.testI("iLogin"," 2"," TEST3",2)=""^demo.testI("iLogin"," 5.4"," 222",3)=""^demo.testI("iLogin"," 5.4"," 222",4)=""^demo.testI("iLogin"," FG"," 333",3)=""^demo.testI("iLogin"," FG"," 333",4)=""^demo.testI("iLogin"," KEY"," VALUE",5)=""^demo.testI("iLogin"," KEY"," VALUE",6)=""Отлично, теперь у нас хранятся как значения ключей, так и сами значения элементов. Как нам это может в дальнейшем пригодиться?
Например, в ранее предложенном варианте с паролями, кроме непосредственно самих паролей можно хранить дату, до которой будет действовать этот пароль, или что-то другое. И тогда в нашем запросе этот факт можно использовать следующим образом:
select * from demo.test where for some %element(Login) (%key='-' and %value = '111')
Где и что хранить — решать вам, единственное нужно помнить, что ключи уникальны, а значения — нет.
Кроме того, в «коллекционном» индексе так же как и в стандартном индексе, можно хранить дополнительные данные:
Index iLogin On (Login(KEYS), Login(ELEMENTS)) [ Data = (Login, Login(ELEMENTS)) ];
При таком варианте, указанный выше запрос, уже не будет обращаться к данным, а возьмёт всё из индекса, что тоже сэкономит время.
Дата (время и т.д)
Казалось бы, а какое отношение даты имеют к коллекциям? Самое непосредственное, ведь зачастую приходится делать поиск только по дню, месяцу или году. Обычный индекс здесь нам не поможет, а вот «коллекционный» — очень даже будет к месту.
Давайте рассмотрим следующий пример:
Class demo.test Extends %Persistent
{
Index iBirthDay On (BirthDay(KEYS), BirthDay(ELEMENTS));
Property BirthDay As %Date;
ClassMethod BirthDayBuildValueArray(
value,
ByRef array) As %Status
{
If value="" {
Set array(0)=value
}else{
Set d=$zd(value,3)
Set array("yy")=+$p(d,"-",1)
Set array("mm")=+$p(d,"-",2)
Set array("dd")=+$p(d,"-",3)
}
Quit $$$OK
}
ClassMethod Fill()
{
&sql(truncate table demo.test)
&sql(insert into demo.test(birthday)
select {d '2000-01-01'} union all
select {d '2000-01-02'} union all
select {d '2000-02-01'} union all
select {d '2001-01-01'} union all
select {d '2001-01-02'} union all
select {d '2001-02-01'}
)
ZWrite ^demo.testD
ZWrite ^demo.testI
}
}
Содержимое нашей таблицы после заполнения примет следующий вид:
| ID | BirthDay |
|---|---|
| 1 | 01.01.2000 |
| 2 | 02.01.2000 |
| 3 | 01.02.2000 |
| 4 | 01.01.2001 |
| 5 | 02.01.2001 |
| 6 | 01.02.2001 |
select * from demo.test where for some %element(BirthDay) (%key='mm' and %value = 2)
Результат:
| ID | BirthDay |
|---|---|
| 3 | 01.02.2000 |
| 6 | 01.02.2001 |
Простой список
В СУБД Caché существует специальный тип данных для простого списка (%List), который можно использовать вместо строки, если с выбором разделителя есть затруднения.
Использование такого поля не намного отличается от работы со строкой.
Рассмотрим небольшой пример:
Class demo.test Extends %Persistent
{
Index iList On List(ELEMENTS);
Property List As %List;
ClassMethod ListBuildValueArray(
value,
ByRef array) As %Status
{
If value="" {
Set array(0)=value
}else{
Set ptr=0
While $ListNext(value,ptr,item){
Set array(ptr)=item
}
}
Quit $$$OK
}
ClassMethod Fill()
{
&sql(truncate table demo.test)
&sql(insert into demo.test(list)
select null union all
select $LISTBUILD('a') union all
select $LISTBUILD('b','a') union all
select $LISTBUILD('b','b') union all
select $LISTBUILD('a','c','b') union all
select $LISTBUILD('a,,',null,null)
)
ZWrite ^demo.testD
ZWrite ^demo.testI
}
}
Содержимое таблицы в режиме отображения ODBC будет выглядеть так:
Примечание: в Caché существует три режима представления данных: логический, ODBC и режим отображения (Data Display Options)
| ID | List |
|---|---|
| 1 | (null) |
| 2 | a |
| 3 | b,a |
| 4 | b,b |
| 5 | a,c,b |
| 6 | «a,,»,, |
При выводе поля типа %List в режиме ODBC в качестве разделителя используется параметр ODBCDELIMITER, который по умолчанию равен ",".
Например, при таком поле наша таблица будет выглядеть так:
Property List As %List(ODBCDELIMITER = "^");
| ID | List |
|---|---|
| 1 | (null) |
| 2 | a |
| 3 | b^a |
| 4 | b^b |
| 5 | a^c^b |
| 6 | a,,^^ |
select * from demo.test where for some %element(List) (%value = 'a,,')
| ID | List |
|---|---|
| 6 | «a,,»,, |
select * from demo.test where 'a,,' %inlist List
Коллекция-список
Перепишем пример выше, но вместо простого списка воспользуемся коллекцией-списком:
Class demo.test Extends %Persistent
{
Index iListStr On ListStr(ELEMENTS);
Property ListStr As list Of %String;
ClassMethod Fill()
{
&sql(truncate table demo.test)
&sql(insert into demo.test(liststr)
select null union all
select $LISTBUILD('a') union all
select $LISTBUILD('b','a') union all
select $LISTBUILD('b','b') union all
select $LISTBUILD('a','c','b') union all
select $LISTBUILD('a,,',null,null)
)
ZWrite ^demo.testD
ZWrite ^demo.testI
}
}
В этом примере почти всё то же самое, да не совсем. Особо следует обратить внимание на следующие вещи:
- на значения COLLATION наших полей, ключей и элементов индекса в array, которые перед сохранением в глобал претерпевают соответствующие трансформации.
Сравните значения в глобале-индексе в обоих примерах, особенно представление значения NULL; - метод BuildValueArray отсутствует, поэтому мы не можем использовать ключи, а только значения элементов;
- типом нашего поля является специальный класс-коллекция (%ListOfDataTypes).
Коллекция-массив
Как выше было отмечено, коллекция-список не даёт нам возможности использовать ключи. Коллекция-массив исправляет этот недостаток.
Создадим следующий класс:
Class demo.test Extends %Persistent
{
Index iArrayStr On (ArrayStr(KEYS), ArrayStr(ELEMENTS));
Property str As %String;
Property ArrayStr As array Of %String;
ClassMethod Fill()
{
&sql(truncate table demo.test)
&sql(insert into demo.test(str)
select null union all
select 'aaa' union all
select 'bbb' union all
select 'bbb' union all
select 'ccc' union all
select null
)
&sql(insert into demo.test_ArrayStr(test,element_key,arraystr)
select 1,'0','test1' union all
select 1,'1','test2' union all
select 1,'2','test3' union all
select 2,'0','test1' union all
select 2,'1','test2' union all
select 2,'2','test3' union all
select 3,'-','111' union all
select 3,'5.4','222' union all
select 3,'fg','333' union all
select 4,'-','111' union all
select 4,'5.4','222' union all
select 4,'fg','333' union all
select 5,'key','value' union all
select 6,'key','value'
)
ZWrite ^demo.testD
ZWrite ^demo.testI
}
}
Здесь нужно сделать несколько пояснений:
- наши данные по прежнему хранятся в двух глобалах: ^имя классаD (собственно данные) и ^имя классаI (индексы);
- имея один класс, тем не менее мы имеем уже две таблицы: как обычно demo.test и одну вспомогательную demo.test_ArrayStr;
- таблица demo.test_ArrayStr предоставляет удобный SQL-доступ к данным массива и имеет следующие поля, некоторые имена которых заранее предопределены:
- element_key — значение ключа (имя поля предопределено);
- ArrayStr — значение элемента;
- test — ссылка на родительскую таблицу demo.test;
- ID — служебный первичный ключ, который имеет формат test||element_key (имя поля предопределено);
- типом нашего поля является специальный класс-коллекция (%ArrayOfDataTypes).
Итак, содержимое наших таблиц после метода Fill() будет следующим:
Таблица demo.test
| ID | str |
|---|---|
| 1 | (null) |
| 2 | aaa |
| 3 | bbb |
| 4 | bbb |
| 5 | ccc |
| 6 | (null) |
| ID | test | element_key | ArrayStr |
|---|---|---|---|
| 1||0 | 1 | 0 | test1 |
| 1||1 | 1 | 1 | test2 |
| 1||2 | 1 | 2 | test3 |
| 2||0 | 2 | 0 | test1 |
| 2||1 | 2 | 1 | test2 |
| 2||2 | 2 | 2 | test3 |
| 3||5.4 | 3 | 5.4 | 222 |
| 3||- | 3 | - | 111 |
| 3||fg | 3 | fg | 333 |
| 4||5.4 | 4 | 5.4 | 222 |
| 4||- | 4 | - | 111 |
| 4||fg | 4 | fg | 333 |
| 5||key | 5 | key | value |
| 6||key | 6 | key | value |
Учитывая предоставляемые СУБД Caché объектные расширения для SQL, наш тестовый запрос, выводящий поле str из demo.test для строк с ключом "-" и значением элемента «111», примет следующий вид:
select test ID,test->str from demo.test_ArrayStr where element_key='-' and arraystr='111'или такой
select %ID, str from demo.test where test_ArrayStr->element_key='-' and test_ArrayStr->arraystr='111'
Результат:
| ID | str |
|---|---|
| 3 | bbb |
| 4 | bbb |
В заключение
Описанный здесь механизм индексации широко используется и в некоторых системных классах, таких, например, как %Stream.GlobalCharacterSearchable, который обеспечивает индексацию текстового потока и поиск по нему через SQL. В статье специально не освещены примеры индексации коллекций на классы, в виду их большого разнообразия: встраиваемые, хранимые, потоки, пользовательские, с коллекциями коллекций и другие. Кроме того, со многими из них не всегда удобно и эффективно работать через SQL, потому автор не видит особой надобности в таких коллекциях кроме каких-то уж очень редких случаев. Также здесь не освещён полнотекстовый поиск, так как это другая область со своими индексами и подходом в работе через SQL. Кроме того были опущены примеры использования у свойств таких параметров как SqlListType и SqlListDelimiter, но пытливый читатель, надеюсь, cможет самостоятельно попробовать их в действии.
Полезные ссылки: