Подход к поиску в базах данных. Search-функции в PostgreSQL

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

В общем случае поисковая форма представляет собой совокупность полей для поиска по всем колонкам таблицы. Каждое поле - возможный критерий поиска. При этом в реализации поиска появляется ряд проблем:

1. Учитываются только те критерии, которые выбрал пользователь. Если он указал что-то в поле "Имя", но оставил пустым поле "Фамилия", то при поиске учитывается только "Имя". Соответственно клиентская часть должна формировать SQL-запрос на лету, учитывая какое именно поле для поиска выбрал пользователь.
2. Условности полнотекстового поиска. Клиентская часть приложения, которая строит SQL-запрос, для текстовых полей использует оператор LIKE (ILIKE). Если нужен полнотекстовый (fulltext) поиск, синтаксис может меняться (в зависимости от базы данных и разных реализаций полнотекстового поиска).
3. Условности типов данных и способов поиска по ним. При построении запроса клиентская часть должна учитывать тип колонки, по которой производится поиск. Оператор LIKE не используется для поиска Boolean-значений. Пример условности в способе поиска: поле, представляющее номер кредитной карты. Если пользователь указал несколько цифр для поиска карты по номеру, то следует искать только с начала номера, а не из его середины.

Мы предлагаем решение - избавить клиентскую часть приложения от каких-либо размышлений по поводу поиска. Интерфейс, которым должна пользоваться клиентская часть должен выглядеть вот так:

• function search_some_entity(id integer, name string, is_published boolean)
• function search_some_another_entity(id integer, number bigint, some_entity_id integer)

Клиентская часть обращается к базе данных с запросом о поиске, просто передавая критерии, которые указал пользователь. База данных сама решает, как поступать с поиском, где использовать тот или иной оператор. Она возвращает результаты по своему усмотрению.

Таким образом мы получаем инкапсуляцию механизма поиска данных. Поиском данных занимается тот, кто и должен этим заниматься - база данных. Клиентская часть получает удобный интерфейс для работы и не принимает решений.

Если вы использовали один способ поиска данных и решили сменить его - интерфейсы для клиентской части не изменятся. Если вы решили учитывать при поиске дополнительные параметры - интерфейс просто и понятно расширяется. Нет необходимости переписывать кучу SQL-запросов, которые разбросаны по коду всей клиентской части.

Такой подход мы используем при работе с PostgreSQL на основе search-функций. В другой высокоуровневой СУБД данный метод может быть использован аналогично, возможно с применением другого синтаксиса или возможностей выбранной базы данных.

Описание нашего решения

Наше решение реализации интерфейса поиска в базе данных основано на создании отдельной поисковой функции для каждой логической сущности в базе данных. Под логической сущностью подразумевается таблица или совокупность таблиц, которые хранят единичный объект в логике базы данных. Например: клиент, банк, товар, продажа и т.д. Поскольку мы используем PostgreSQL, то функции были написаны на языке PL/pgSQL. Обязательное требование - однородность функций, чтобы сделать возможной автоматическую генерацию их кода по таблице или view. Если нужна специфическая логика поиска - автоматически генерированный код правится.

Рассмотрим таблицу пример:

 1
 2
 3
 4
 5
 6
 7
 8

CREATE TABLE client
(
  cl_id integer NOT NULL DEFAULT nextval('client_id_sequence'::regclass),
  cl_name character varying NOT NULL
  CONSTRAINT client_pkey PRIMARY KEY (cl_id)
) 
WITHOUT OIDS;
ALTER TABLE client OWNER TO postgres;

Вот автоматически генерированный код поисковой функции для данной таблицы:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

CREATE OR REPLACE FUNCTION search_client(cl_id integer, cl_name character varying)
  RETURNS SETOF client AS
$BODY$DECLARE
    item client%ROWTYPE;
    result_sql varchar;
    start_sql varchar;
BEGIN
    start_sql := 'SELECT * FROM client';
    result_sql := 'SELECT * FROM client';

   -- integer field  
    IF cl_id IS NOT NULL THEN 
        result_sql := search_add_int_parameter('cl_id', cl_id, start_sql, result_sql); 
    END IF;
   -- varchar field  
    IF cl_name IS NOT NULL THEN 
        result_sql := search_add_like_parameter('cl_name', cl_name, start_sql, result_sql); 
    END IF;
  
    -- RAISE NOTICE 'SQL is %', result_sql;
    FOR item IN EXECUTE result_sql LOOP
        RETURN NEXT item;
    END LOOP;
    RETURN;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION search_client(cl_id integer, cl_name character varying) OWNER TO postgres;

Рассмотрим её код поподробнее. Функция умеет искать клиентов по идентификатору и имени. Пользователь может указать:

• один параметр: функция ищет, учитывая только этот один параметр.
• оба параметра: функция ищет, учитывая оба параметра и используя логическое AND.
• ни одного: функция возвращает все значения таблицы client, пользователь ничем не ограничил выборку.

Такая логика работы не навязывается. Вы можете использовать оператор OR или же не возвращать ни одной записи, если пользователь ничего не указал в параметрах поиска. Это зависит только от вашего приложения. Здесь сразу видна удобность подхода с функциями - мы можем поменять логику поиска не меняя его интерфейса.

Для каждого типа данных мы создали вспомогательную функцию добавления параметра в генерируемый SQL-запрос. Вот функция для integer-типов, где необходимо совпадение значений:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

CREATE OR REPLACE FUNCTION search_add_int_parameter(param_name character varying, param_value integer, start_sql character varying, result_sql character varying)
  RETURNS character varying AS
$BODY$
DECLARE
    --	
BEGIN
    IF result_sql = start_sql THEN
	RETURN result_sql || ' WHERE ' || quote_ident(param_name) || ' = ' || cast(param_value as varchar);
    ELSE
	RETURN result_sql || ' AND ' || quote_ident(param_name) || ' = ' || cast(param_value as varchar);
    END IF;
END;
$BODY$
  LANGUAGE 'plpgsql' STABLE;
ALTER FUNCTION search_add_int_parameter(param_name character varying, param_value integer, start_sql character varying, result_sql character varying) OWNER TO postgres;

Вот функция для текстовых типов (varchar, text и т.д.), где применяется ILIKE:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

CREATE OR REPLACE FUNCTION search_add_like_parameter(param_name character varying, param_value anyelement, start_sql character varying, result_sql character varying)
  RETURNS character varying AS
$BODY$
DECLARE
    -- declare	
BEGIN
    IF result_sql = start_sql THEN
	RETURN result_sql || ' WHERE ' || quote_ident(param_name) || ' ILIKE ''%' || cast(param_value as varchar) || '%''';
    ELSE
	RETURN result_sql || ' AND ' || quote_ident(param_name) || ' ILIKE ''%' || cast(param_value as varchar) || '%''';
    END IF;
END;
$BODY$
  LANGUAGE 'plpgsql' STABLE;
ALTER FUNCTION search_add_like_parameter(param_name character varying, param_value anyelement, start_sql character varying, result_sql character varying) OWNER TO postgres;

Такие же функции можно написать для любого используемого вами типа данных. Таким образом логика поиска по текстовым данным лежит в одном месте. Она может быть без проблем заменена (например, после внедрения полнотекстового поиска). Аналогичные функции могут быть написаны для диапазонов значений (например, для дат). Здесь нет никакой специфической логики. Код генерируется автоматически и легко заменяется.

Функции могут искать по нескольким таблицам. К примеру, в таблицу клиент добавилось поле Банк (bank_id), как внешний ключ на таблицу банков. Пользователю необходимо производить поиск дополнительно по имени банка (bank_name). Вы создаёте client_view, который включает поля bank_id и bank_name и добавляете в поисковую функцию два параметра. Выборка превратится в "SELECT * FROM client_view", а возвращаемое значение в "SETOF client_view".

Таким образом, мы получаем инкапсулированную функциональность поиска по базе данных в виде чёткого и понятного интерфейса.

Автор: http://www.pleso.net/