По следам статьи «Полноценный upper (или lower) в SQLite при работе с unicode»

Автор указанной выше статьи не известен, к сожалению.
Ссылка на статью: https://habr.com/ru/sandbox/98493/

См., также, текст всех функций (html-формат).

Среда: Delphi 10.2 Tokyo

При разработке ПО в среде Delphi зачастую возникает необходимость хранения данных в какой-либо локальной БД.

СУБД SQLite вполне подходит для этих целей, но в некоторых случаях проблема, обозначенная в указанной статье (см. выше), существенно усложняет ее использование.

Суть проблемы в том, что функции UPPER и LOWER в SQLite не умеет работать с кириллицей (под кириллицей в данном документе подразумевается русский алфавит).

В статье (см. выше) приведен конкретный, работающий вариант решения этой проблемы.

Воспользовавшись идеей и конкретным примером, приведенным в статье (см. выше), решил реализовать это применительно к среде Delphi (а если конкретно, то Delphi 10.2 Tokyo).

В конечном итоге, хотелось получить что-то вроде такого (см. ниже).

Вместо этого (которое с кириллицей не работает):

S := S + ‘upper(note) like ‘+#39+‘%СЛОВО%’+#39;

Использовать вот это:

S := S + SQLite_Upper(‘note’)+ ‘ like ‘+#39+‘%СЛОВО%’+#39;

А еще лучше, что-то вроде этого:

procedure TfMain.n_ListWordsFilterCreateClick(Sender: TObject);
Var
  ListWords:TStrings; //Список слов (поисковых выражений)
  ListRes:TStrings;   //Результат
begin
  Application.ProcessMessages;
  ListWords:=TStringList.Create;
  ListRes:=TStringList.Create;
  TRY
    ListWords.Add('%ЦВЕТ%');
    ListWords.Add('%СКАЗ%');
    ListWords_Filter_Create('note', ListWords, ListRes, false);
    ShowMessage(ListRes.Text);
  FINALLY
    FreeAndNil(ListWords);
    FreeAndNil(ListRes);
  END;
end;

В результате, были разработаны несколько Delphi-функций:

SQLite_Upper_Char;
SQLite_Upper;
WordFilter_Create;
ListWords_Filter_Create;

См., также, таблицу 1:

Пример использования:

Сформировать SQL-запрос для поля note таблицы БД с алиасом t9z.
Слова (поисковые выражения): %ЦВЕТ% и %СКАЗ% для «LIKE» (OR).

procedure TfMain.n_ListWordsFilterCreateClick(Sender: TObject);
Var
  ListWords:TStrings; //Список слов (поисковых выражений)
  ListRes:TStrings;   //Результат
begin
  Application.ProcessMessages;
  ListWords:=TStringList.Create;
  ListRes:=TStringList.Create;
  TRY
    ListWords.Add('%ЦВЕТ%');
    ListWords.Add('%СКАЗ%');
    ListWords_Filter_Create('note', ListWords, ListRes, false);
    ShowMessage(ListRes.Text);
  FINALLY
    FreeAndNil(ListWords);
    FreeAndNil(ListRes);
  END;
end;

Результат:

 ( (
WITH RECURSIVE
under_name(note, char, level) as
(
select t9z.note, '', 0
union
select note, coalesce(lu.u,substr(note,level,1)), under_name.level+1
from under_name
left join 
(
 select 'А' as u, 'а' as l
union select 'Б' as u, 'б' as l
union select 'В' as u, 'в' as l
union select 'Г' as u, 'г' as l
union select 'Д' as u, 'д' as l
union select 'Е' as u, 'е' as l
union select 'Ё' as u, 'ё' as l
union select 'Ж' as u, 'ж' as l
union select 'З' as u, 'з' as l
union select 'И' as u, 'и' as l
union select 'Й' as u, 'й' as l
union select 'К' as u, 'к' as l
union select 'Л' as u, 'л' as l
union select 'М' as u, 'м' as l
union select 'Н' as u, 'н' as l
union select 'О' as u, 'о' as l
union select 'П' as u, 'п' as l
union select 'Р' as u, 'р' as l
union select 'С' as u, 'с' as l
union select 'Т' as u, 'т' as l
union select 'У' as u, 'у' as l
union select 'Ф' as u, 'ф' as l
union select 'Х' as u, 'х' as l
union select 'Ц' as u, 'ц' as l
union select 'Ч' as u, 'ч' as l
union select 'Ш' as u, 'ш' as l
union select 'Щ' as u, 'щ' as l
union select 'Ъ' as u, 'ъ' as l
union select 'Ы' as u, 'ы' as l
union select 'Ь' as u, 'ь' as l
union select 'Э' as u, 'э' as l
union select 'Ю' as u, 'ю' as l
union select 'Я' as u, 'я' as l
)
lu on substr(note,level,1)=lu.l
where level <= length(note)
)
select group_concat(char,'') from under_name
) like '%ЦВЕТ%') 
 or 
 ( (
WITH RECURSIVE
under_name(note, char, level) as
(
select t9z.note, '', 0
union
select note, coalesce(lu.u,substr(note,level,1)), under_name.level+1
from under_name
left join 
(
 select 'А' as u, 'а' as l
union select 'Б' as u, 'б' as l
union select 'В' as u, 'в' as l
union select 'Г' as u, 'г' as l
union select 'Д' as u, 'д' as l
union select 'Е' as u, 'е' as l
union select 'Ё' as u, 'ё' as l
union select 'Ж' as u, 'ж' as l
union select 'З' as u, 'з' as l
union select 'И' as u, 'и' as l
union select 'Й' as u, 'й' as l
union select 'К' as u, 'к' as l
union select 'Л' as u, 'л' as l
union select 'М' as u, 'м' as l
union select 'Н' as u, 'н' as l
union select 'О' as u, 'о' as l
union select 'П' as u, 'п' as l
union select 'Р' as u, 'р' as l
union select 'С' as u, 'с' as l
union select 'Т' as u, 'т' as l
union select 'У' as u, 'у' as l
union select 'Ф' as u, 'ф' as l
union select 'Х' as u, 'х' as l
union select 'Ц' as u, 'ц' as l
union select 'Ч' as u, 'ч' as l
union select 'Ш' as u, 'ш' as l
union select 'Щ' as u, 'щ' as l
union select 'Ъ' as u, 'ъ' as l
union select 'Ы' as u, 'ы' as l
union select 'Ь' as u, 'ь' as l
union select 'Э' as u, 'э' as l
union select 'Ю' as u, 'ю' as l
union select 'Я' as u, 'я' as l
)
lu on substr(note,level,1)=lu.l
where level <= length(notenote)
)
select group_concat(char,'') from under_name
) like '%СКАЗ%')

Детально материал представлен в документе SQLite_Upper.pdf.

Дополнительно к этому документу прилагаются исходные тексты как самих функций (проект в среде Delphi 10.2 Tokyo), так и программа тестирования (с исходниками, ясное дело).
Имя файла ZIP-архива: SQLite_Upper_Test_pas.zip (скачать).

Скомпилированный EXE-модуль для тестирования (и пример БД) находится в папке: \Win32\Debug\

Имя EXE-модуля: SQLite_Upper_Test.exe.
Имя файла БД SQLite: SQLite_Upper_Test.db.

Структура БД SQLite_Upper_Test.db:

CREATE TABLE notes_list
(
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
npp integer,
note VARCHAR(512), -- примечание к заметке
info TEXT –- текст заметки
);
CREATE INDEX npp_noteslist ON notes_list(npp ASC);

В таблице БД notes_list содержатся следующие строки:

ВАЖНО!!!

Автор этого документа не ставил перед собой цель покорить этот мир красотой и оптимальностью примененных алгоритмов, а также − совершенством исходного кода.
Ясное дело, что эти исходники можно (да и нужно, пожалуй) использовать, как некий иллюстрирующий материал для своих собственных разработок.
Огромнейшее спасибо автору статьи (см. выше): https://habr.com/ru/sandbox/98493/

Детально материал представлен в документе SQLite_Upper.pdf.

См., также, текст всех функций (html-формат).

На предыдущую страницу…

Дата: 22.02.2024

Программируй и разрабатывай

Программируй и разрабатывай

По следам статьи «Полноценный upper (или lower) в SQLite при работе с unicode»