Всем привет. Клиенты пользуются компонентой 1sqlite от Орефкова версии 1.0.2.6 для поиска данных в больших таблицах. Версия 1с dbf. Поиск нечеткий, т.е. требуется найти совпадение в любой части искомой строки. Соответственно используется like в запросе. Работает достаточно сносно, но при поиске не задействован индекс. Нашел, что в sqlite есть полнотекстовый поиск FTS4 или FTS5, но так понимаю, что Орефков в сборку такой функционал не добавлял. Может кто прикручивал такую штуку?

заведите ещё одну таблицу "FulltextIndex".

В неё сохраняйте текст с разбивкой по словам. Можно дополнительно отбросить окончания (стемминг) и убрать бессмысленные слова (предлоги и т.п.).

В поисковом выражении используйте
FulltextIndex.word LIKE "слово%"

Важно, чтобы слева не было знака процента, тогда будет задействован индекс по полю.

(1) Это я знаю, что like без % впереди по индексу работает. По словам не получится разбить, т.к. нужно искать именно внутри слов, например, "Замазка", а искать будут маз.

Прикрутить FTS к dbf версии 1С не получится
Один фиг нужны отдельные таблицы

Используй метод N-грамм

И кстати поиск у вас вполне "четкий"

(3)+ https://habr.com/ru/post/114997/

строится табличка-"индекс" из трехсимвольных сочетаний
каждому сочетанию из 3 символов сопоставляются записи где оно есть

затем то что ищется разбивается со смещением на всевозможные 3-символы и они ищутся в табличке-индексу
далее по кол-ву совпадений выводится что надо

(5)+ https://habrastorage.org/r/w1560/storage/habraeffect/57/98/579859076e907968952471e047fd8827.png

Замазка = зам, ама, маз, азк, зка

Логично что поиск "маз" выдаст ссылку на "Замазка", причем индексы СУБД хорошо пашут

(5) Да, не, так не пойдет. Это же я просто для примера написал, а так могут и 3, и 4, и 5 символов ввести для поиска. В общем случае n символов. Мне что же, на каждое количество свои таблички городить? Так поиск по ним будет дольше, чем full scan по первоначальной сделать.

Плюс эти таблички надо актуализировать постоянно, данные то не статичны, а это тоже затраты временные.

Это так работать не будет.

(8) Сделайте поиск асинхронным, чтобы пользователь мог заниматься своими делами, пока фоновое задание ищет "замазку".

Плюс выдавайте промежуточные результаты по мере нахождения с возможностью прервать поиск.

(8) блин подучи теорию
свое слово из "3, и 4, и 5 символов ввести для поиска" разбиваешь аналогично со смещением по 3 символа
и их ищешь
чем больше совпадений тем ближе

(9) а ты думал в сказку попал?

(12) Это все фигня, из области рассуждений про розовых пони. Будем микросервис городить, который будет быстро искать, а 1С только в качестве клиента выступать будет.

Нахрена велосипед с поиском свой изобретать, когда умные дяди уже все сделали за нас, просто хотелось обойтись малой кровью.

(14) это не фигня а реально рабочее решение
на чем его делать на 1С или внешним пофиг

(15) ты извини не очень понимаешь
твои умные дяди ничего не знают от твоих табличках DBF в которых 1С данные хранит

(17) Какая разница в чем хранятся данные, таблицы - они и в Африке таблицы.

1sqlite умеет же с таблицами dbf 1с-скими работать

(12) Это все понятно, только все-равно коряво, поиск в 2 прохода придется делать, сначала искать совпадения, потом считать их количество, а потом еще по какому-то критерию это количество анализировать

в чем проблема при перезаписи справочников или документов для нужных реквизитов делать (5) и писать куда то
а затем при поиске разбивать искомое слово на N-граммы и искать их, затем сортировать по кол-ву совпадений и выводить

Замазка = зам, ама, маз, азк, зка
Смазка = сма, маз, азк, зка
Камаз = кам, ама, маз
Мазок = маз, азо, зок

Допустим ищем "мазо" = маз, азо

Совпадения "маз" - 4 (Замазка, Смазка, Камаз, Мазок), "азо" - 1(Мазок)
Пересечение только "Мазок" - его и выводим

Какие проблемы это сделать?

(21) Да нет проблем, только я не уверен, что это будет быстрее, чем сейчас работать

(22) метод N-грамм применяется и гуглом и яндексом ))

Будет работать и очень быстро

(23) Как быстро в 1С пересечение таблиц определить без их перебора?

(0) есть strmatch (нечеткий поиск от scorp) - куда УЖЕ ВСТРОЕН скулайт как раз для вот такого вот ускорения и работает очень быстро на больших массивах.
взять можно у Djelf (причем он регулярно обновляет strmatch с выходом значимых версий скулайта)

(25) во, вот это дело )) Нифига там уже версии какие, а у моих все по старинке ))

Быстрый поиск делается строго без таблиц.
Или на крайний случай в колоночных СУБД.
https://ru.wikipedia.org/wiki/Столбцовое_хранение

стрматч вообще хорошо работает на чем более длинные строки сравниваются.
юзал стрматч на куче задач - вполне удовлетворяет по релевантности, почти всегда - если нужной позиции нет в первой пятерке похожих - то ее или вообще нет или называется так что хрен поймешь что это одно и то же ;-). В strmatch можно еще задавать веса цифр, входящих в поисковые строки. При этом strmatch штатный вроде как выкидывает нефонетические символы, грубо говоря Счет№1 и Cчет№(/)1 будет одно и то же. Специально не проверял, т.к. на своих задачах почему-то изначально сравниваемые строки принудительно очищал от незначимых символов. а провести тесты сейчас - влом запросто так (поле применения очень малое)
.
при этом нискольо не спорю насчет триграмм и прочих методов. каждый юзает что ему удобно и что считает подходящим для задачи.
.
по нечеткому посику с типовой strmatch смотри на ИС пример мой для загрузки заявок от клиентов с нечетким поиском "Удар по бездуховности"

там же на ИС, кстати вроде есть статья где сравнивается нескольо методов нечеткого поиска - будет интересно - ищи сам...

(24) в sql пересечение это внутреннее соединение INNER JOIN

(30)+ в смысле (21) прекрасно реализуется средствами 1sqlite для dbf'ной 1С 7.7
или через 1С++ для sql 1C

(25) ТС надо искать по базе

(31) Попробую сделать через триграммы, прям даже интересно, что получится.

В общем придется делать, strmatch не совсем то, что нужно.

(33) Потом отпишись в ветке, интересно

(24) в 1с++ в ИТЗ (индексированные таблицы) есть для таблиц значений - и лефт джойн, и прочее
ВнутреннееСоединение / InnerJoin
ЛевоеСоединение / LeftJoin
ПравоеСоединение / RightJoin
ПолноеСоединение / FullJoin
Объединить / Merge
Пересечение / Conjunction
Разность / Difference
Копия / Copy

жуй: https://www.1cpp.ru/docum/html/IndexedTable.html

(34) обоснуй конкретно почему не подходит

(38) не считается (38). Нужен like, а не похожесть....

https://spec-zone.ru/sqlite/fts3#introduction_to_fts3_and_fts4

(33) Хм, триграммный токиназейр в fts5 я и не заметил.
Лови, развлекайся: https://cloud.mail.ru/public/YAQT/3bgLTJXi6

DROP TABLE IF EXISTS tri;
CREATE VIRTUAL TABLE tri USING fts5(ID UNINDEXED, DESCR, tokenize='trigram');
INSERT INTO tri (ID,DESCR)
SELECT 
Номенклатура.ID ID
,Номенклатура.DESCR DESCR 
FROM Справочник_Номенклатура AS Номенклатура;
SELECT * FROM tri('ивНо');

(41) прикольно

Ссылка на описание: https://runebook.dev/ru/docs/sqlite/fts5#the_experimental_trigram_tokenizer

(41) Собрал по примеру:
    _глБД_SQLite = СоздатьОбъект("SQLiteBase");
    _глБД_SQLite.Открыть(":memory:");
    Запрос = _глБД_SQLite.НовыйЗапрос();
    Запрос.ВыполнитьЗапрос("DROP TABLE IF EXISTS tri;
                            |CREATE VIRTUAL TABLE tri USING fts5(ID UNINDEXED, DESCR, tokenize='trigram');
                            |INSERT INTO tri (ID,DESCR)");

    ТекстЗапроса="
        |SELECT
        |    Клиенты.ID ID ,Клиенты.DESCR DESCR
        |FROM [Справочник.Клиенты] Клиенты;
        |
        |SELECT * FROM tri('Сташкова');";


Выдает весь справочник.
Что не так сделал? Не пойму

(44) Раздели запросы вот так:

    База=СоздатьОбъект("SQLiteBase");
    База.Открыть("r:\base.db3");
    Запрос=База.НовыйЗапрос();
    Запрос.ВыполнитьЗапрос("DROP TABLE IF EXISTS tri;");
    Запрос.ВыполнитьЗапрос("
    |CREATE VIRTUAL TABLE tri USING fts5(ID UNINDEXED, DESCR, tokenize='trigram');
    |");
    Запрос.ВыполнитьЗапрос("
    |INSERT INTO tri (ID,DESCR)
    |SELECT
    |Номенклатура.ID ID
    |,Номенклатура.DESCR DESCR
    |FROM Справочник_Номенклатура AS Номенклатура;    
    |");
    тз=Запрос.ВыполнитьЗапрос("
    |SELECT * FROM tri WHERE DESCR LIKE '%ивНо%';
    |");


а интересно, долго строит триграммный индекс/таблицу? ну скажем для 50'000-100'000 товаров?
и надо каждый раз ее грохать и сроить заново или можно обновлять?

(44) ++ Спасибо

(45) ++ Спасибо. Промахнулся

(46) 520940 записей вида guid, заполнение 7498мс, запрос 2мс, база на диске 135мб
Прямой запрос к таблице 1С по like 400мс, но это в мономольном режиме
По ссылке в (43) все есть, как так нелизя обновлять? Конечно можно.

(46) в базе ~114000 выполнялось ~4сек

(41) Круть, спасибо.

(41) Да, зачетно. Надо попробовать.

Пиз..ц скорость )) Справочник - 180000 элементов, индекс около 4-5 секунд, поиск мгновенный просто. Я просто счастлив

Теперь второй вопрос ))) А обычный SQL так же ускорить для like возможно? Периферийки на DBF, центр в SQL

1cpp используется, прямые запросы тоже.

(41) Да это праздник какой-то.

(56) Как то проглядел, было введено 2020-12-01 (3.34.0) https://www.sqlite.org/draft/changes.html
Довольно не плохо выглядит, а 100кб это не размер (ну не 700кб, а 800кб будет), оставлю эту опцию при следующих сборках.

+(57) Сейчас в сборке:
+ математика(зачем синусы в клюшках я не понимаю, но пусть будут);
+ модуль json, это очень просто и очень быстро https://www.sqlite.org/json1.html
если захочется хмл таким же образом распарсить, то есть модуль xml_to_json https://cloud.mail.ru/public/9znr/ZJ6ULE9aR
+ модуль fts5 c 2023/03/16

(58) regexp же встроенный?

(59) Нет, это сторонним модулем. Слишком много вариантов реализаций.

(60) Я почему то думал, что он встроенный из-за этого:
2021-06-18 (3.36.0)
...
The REGEXP extension is now included in CLI builds.
хотя написано же в CLI версии...

(58) Где же ты раньше был, друг?

(56) нашествие живых мертвецов ;-)

(63) зомби бессмертны...

Вот не пойму:
то ли добавить Вас в Книгу знаний,
то ли удалить, потому что в восьмёрке уже всё реализовано штатными средствами платформы...

(65) Восьмерка еще сырая ))

(66) она отсырела, заржавела и вертится со скрипом ;-)

UPD: По поводу обновления.
Потребуется слегка изменить таблицу fts5. Финальный вариант ниже.
Обратите внимание: у fts5 есть колонка ROWID с первичным индексом и у 7ки тоже, соответсвие полей будет по этой колоке.
Проверка неоходимости обновления сначала по VERSTAMP, затем по DESCR, так чуток быстрее.
Кстати, запросы вставки и обновления возвращают количество измененных записей.
[b]
DROP TABLE IF EXISTS tri;
CREATE VIRTUAL TABLE tri USING fts5(ID UNINDEXED, DESCR,VERSTAMP UNINDEXED, tokenize='trigram');

/* заполнение fts5 */
INSERT INTO tri(ROWID,ID,DESCR,VERSTAMP)
SELECT
    Номенклатура.ROWID,
    Номенклатура.ID,
    Номенклатура.DESCR,
    Номенклатура.VERSTAMP
FROM Справочник_Номенклатура AS Номенклатура;

/* обновление fts5 */
INSERT OR REPLACE INTO tri(ROWID,ID,DESCR,VERSTAMP)
SELECT
    Номенклатура.ROWID,
    Номенклатура.ID,
    Номенклатура.DESCR||'xxxxx', /* xxxxx для проверки что сработало */
    Номенклатура.VERSTAMP
FROM Справочник_Номенклатура AS Номенклатура
LEFT JOIN tri
    ON tri.ROWID = Номенклатура.ROWID
    AND tri.VERSTAMP = Номенклатура.VERSTAMP
    AND tri.DESCR = Номенклатура.DESCR
WHERE tri.ROWID IS NULL;

/* запрос к fts5 */
SELECT
    tri.ID [Номенклатура :Справочник.Номенклатура]
    ,highlight(tri, 1, ' {', '} ') DESCR /* жаль что тп не умеет выделять слова цветом, ну хоть так */
    ,VERSTAMP
    ,ROWID
FROM tri('рус OR лат NOT мат');
[/b]

А с триггерами что-то может получиться?

[code]
CREATE TABLE tbl(a INTEGER PRIMARY KEY, b, c);
CREATE VIRTUAL TABLE fts_idx USING fts5(b, c, content='tbl', content_rowid='a');

- Триггеры для поддержания индекса FTS в актуальном состоянии.
CREATE TRIGGER tbl_ai AFTER INSERT ON tbl BEGIN
  INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;
CREATE TRIGGER tbl_ad AFTER DELETE ON tbl BEGIN
  INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
END;
CREATE TRIGGER tbl_au AFTER UPDATE ON tbl BEGIN
  INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
  INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;
[/code]

(70) Это можно использовать если таблица не 1С, а sqlite. На таблицу 1С триггеров не повесить.

Нашел проблему в компоненте: если одновременно наложить больше 3 условий %like% происходит вылет 1С

Ругается именно на 1sqlite.dll на компоненту

Вот такой запрос
ТекстЗапроса = "
SELECT
  ID [ID :Справочник.НоменклатураПоставщиков]
FROM
  trinp
WHERE
  KONTRAGENT IN (SELECT VAL FROM СписокАктивныхПоставщиков)
  AND DESCR LIKE '%кастор%'
  AND DESCR LIKE '%масл%'
  AND DESCR LIKE '%30%'
  AND DESCR LIKE '%мл%'
ORDER BY PRIORITY"

(74) имхо превышение по ram

Не, не в количестве AND дело, вылет, если в LIKE меньше 3 символов и таких LIKE несколько

(75)+ движок sqlite кушает в этом случае много памяти, вот и вылетает ВК

SELECT
  ID [ID :Справочник.НоменклатураПоставщиков]
FROM
  trinp
WHERE
  KONTRAGENT IN (SELECT VAL FROM СписокАктивныхПоставщиков)
  AND DESCR LIKE '%кастор%'
  AND DESCR LIKE '%масл%'
  AND DESCR LIKE '%300%'
  AND DESCR LIKE '%мл%'
ORDER BY PRIORITY"

Вот так работает без вылета

Костыль прикручу конечно, но интересно из-за чего это происходит

ты понимаешь что в случае биграмм метод триграмм уже не может работать как задумано

(80) но почему-то только на втором LIKE падает

по факту твое '%мл%' выливается в '% мл%' or '%мл %' or '%млА%' or '%млБ%' or '%млВ%' or '%млГ%' or ...

(81) FTS с триграммами не работают на двух символьных
полный перебор идет

т.е. на один LIKE памяти хватает, а дальше - все

А если вместо LIKE попробовать MATCH? Только уже % не нужен в строке поиска

(85) Ща, смоделируем

То же самое, вылет на втором условии

(84) проверь без FTS так же вылетает?

Без FTS все работает, но медленно

Буду костылить, запрещу меньше 3 символов отправлять в поиск и все

(82) чтобы вот такой херни не было

(83) Нет, на 2х символах вообще никакого перебора нет, поиск только начиная с 3х символов.
(72) Это достаточно нежная таблица, защиты от дурака в ней почти нет.
А поиск лучше делать по MATCH

FROM trinp
WHERE trinp MATCH 'кастор OR масл OR 300'
или так, для MATCH порядок слов не важен
WHERE trinp MATCH 'кастор AND масл AND 300'


WHERE trinp MATCH 'кастор AND масл AND 30 AND мл'
интересно что будет

(93) Будет 0 строк.

(94) Неа, будет результат, причем 30 фильтранется

т.е. версия Garykom верная

Вот так WHERE trinp MATCH 'кастор AND масл AND 30

AND мл - вылет

т.е. фулскан таблицы таки делается

Уточняю после проверки: с LIKE 30 фильтруется, с MATCH - не фильтруется

Но MATCH все равно роняет 1С если 2 раза пихнуть 2-х символьную строку

Да, и спец символы не ищет. Т.к. режим триграмм убирает всякие точки, запятые, # и пр.. Я попробовал сделать поиск у себя хештег типа #хештег - выдало ошибку поиска " fts5: syntax error near "#""

Бл.. я ничего не понимаю, match перестал ронять 1С

Но логично в принципе, он не фулсканит таблицу, значит не жрет память, в отличии от LIKE

(102) Может, если создать таблицу в режиме tokenize = "ascii tokenchars '-_#'" - тогда будет искать и спецсимволы. То тогда в строке поиска нужно будет использовать маску вроде "масл*"

(99) там не совсем фуллскан
перебор триграмм идет для поиска биграмм в них, а затем уже по найденным триграммам быстро ищет
но кол-во триграмм велико, вот и вылет

для проверки можешь попробовать на скольки триграммах аналогично вылетает
если продолжить строку WHERE trinp MATCH 'ааа AND ааб AND аав AND ааг AND ...'

(106)+ т.е. проблема не в "30" а "мл"
"30" редкое сочетание
а вот "мл" в куче слов думаю есть

(106) нафиг, проблему главное решили, причины понятны. Бум знать на будущее

ну немного хуже в плане удобства по сравнению без FTS, но скорость в моем случае важнее удобства, привыкнут

Проблема с памятью легко решается.
Нужно просто открыть базу sqlite не в пямяти, а на диске.
Чуток медленне, зато можно такую базу открывать несколькими пользователями.
(109) Можно отключить индекс плюсиком

WHERE tri MATCH 'пив' /* сначала поиск >=3х символов по индексу  */
AND +DESCR LIKE '%30%'   /* затем поиск в найденом <=2х символов без индекса */


(110) Да ты чертов гений (в хорошем смысле этого слова), теперь прям совсем хорошо стало. Пользоваться можно как раньше, но скорость как сейчас.

(110) А точно надо вот так WHERE tri MATCH 'пив' искать по индексу, а не WHERE DESCR MATCH 'пив'

(112) В описании https://www.sqlite.org/fts5.html довольно мутно описано.
Предполагаю что по tri идет поиск по всем индексированным колонкам в таблице (у нас она одна), а по DESCR только по колонке DESCR.
Кроме того посмотри пункт 3.6 там в MATCH можно указать конкретные колонки по которым идет поиск.

(113) Получается последующие условия AND в запросе учитывают результаты предыдущих условий AND? Я всегда думал, что они одновременно накладываются.

(114) Ну это же не 7ка, где в ЕСЛИ выполнятся все условия, что является полным идиотизмом.
Первое условие не прошло, второе уже не выполняется.

(113). Да. Поиск WHERE tri - это и есть полнотекстовый поиск. По всем колонкам сразу. Движок создает автоматически колонку с именем таблицы и пихает туда весь текст со всех остальных колонок.

круть.
народ умный, что капец...

В аду для перфекционистов нету ни серы, ни огня, и лишь слегка несимметрично стоят щербатые закопчённые котлы.

+(118) :-)

Немного оффтоп: Если SQL вариант работы 7.7 какой есть альтернативный вариант полнотекстового поиска вместо 1sqlite+FTS5