Поиск во всей базе по строковым реквизитам (с регулярными выражениям)

Мы разберем обработку поиска строк в базе средствами SQL. При открытии обработки нас встречает закладка подключения:

Подключение к SQL и CLRПодключение к SQL и CLR Здесь указываем пути и пароли к нашей базе, обратите внимание как оригинально в коде получаютя текущие значения ИмяСервера и ИмяБазы.

ИмяСервера  = НСтр(СтрокаСоединенияИнформационнойБазы(), "Srvr" );
ИмяБазы     = НСтр(СтрокаСоединенияИнформационнойБазы(), "Ref"  );


Если вы захотите помимо шаблонов оператора LIKE использовать регулярные выражения для поиска в базе, то необходимо подключить к MS SQL 2005 внешнюю компоненту SqlRegEx.dll по технологии CLR (common language runtime). Размещаете внешнюю компоненту на SQL сервере, указываете путь к ней. И нажимаете кнопку "Включить CLR". При этом будут выполнена некая магия:

// включение CLR
Connection.Execute("exec sp_configure 'clr enabled', 1");
// магия
Connection.Execute("RECONFIGURE");
// еще магия - создаем ASSEMBLY
Connection.Execute("CREATE ASSEMBLY [SqlRegEx] FROM '"+ПутьКSqlRegEx+"' WITH PERMISSION_SET = SAFE");
// создаем пользовательскую функцию-обёртку ufn_RegExIsMatch,
// возвращающую результат проверки шаблона регулярного выражения к строковому образцу
Connection.Execute("CREATE FUNCTION [dbo].[ufn_RegExIsMatch]
                    |(@Input NVARCHAR(MAX), @Pattern NVARCHAR(MAX), @IgnoreCase BIT)
                    |RETURNS BIT
                    |AS EXTERNAL NAME SqlRegEx.[SqlClrTools.SqlRegEx].RegExIsMatch
                    |"
);


Переходим к закладке "Область поиска"

Область поиска строки-шаблонаОбласть поиска строки-шаблона
Здесь можно отметить область для поиска. Если не отметили ни одной галочки, то поиск будет вестись по всем объектам. Всё понятно.

Теперь основная закладка поиска.

Поиск с регулярными выражениями в 1СПоиск с регулярными выражениями в 1С

Вводим наш шаблон поиска в формате оператора LIKE или в формате RegEx. Отмечаем сколько подходящих записей хотим получить, если указать 0, то все. Отмечаем, при необходимости, флагом SqlRegEx использование RegEx и уточняем чувствительнорсть к регистру. Жмем кнопку "Искать" и запасаемся терпением. В окне сообщений будет выводиться длительность выполнения запроса к базе данных. И вот результат в виде таблицы, в которой можно кликнуть по строке и открыть найденный объект.


Разберем схематично основные блоки процедуры поиска в SQL:

//получаем структуру БД
СтруктураБД = ПолучитьСтруктуруХраненияБазыДанных(,Истина);
//обходим ее и отфильтровываем область поиска
Для Каждого ТекСтрока из СтруктураБД Цикл
 
//наполняем таблицу   поисковых запросов
 
Для Каждого ТекПоле Из ТекСтрока.Поля Цикл
   
//т.е. мы проверяем по метаданным каждый реквизит на строковой тип
    //и вносим в таблицу
   
С = мТЗПолейПоиска.Добавить();
   
С.ТаблицаSQL = ТекСтрока.ИмяТаблицыХранения// имя таблицы в SQL
   
С.ИмяПоляПоиска = ТекПоле.ИмяПоляХранения; // имя колонки в SQL
.....
//сворачиваем таблицу
мТЗПолейПоиска.Свернуть("ИмяПоляПоиска,ТаблицаSQL");
//сортируем
мТЗПолейПоиска.Сортировать("ТаблицаSQL");
//добавим терминальную пустую строку
мТЗПолейПоиска.Добавить();
//начинаем обход и строим запросы так, чтоб к каждой таблице обращаться 1 раз
//т.е. будем накапливать условия в ТекУсловиеПоиска и потом выполнять запрос
ТекИмяТаблицы = Неопределено;
ТекУсловиеПоиска = " (1=0) ";
Для Каждого
СтрокаТаблицы из мТЗПолейПоиска Цикл
    Если
ТекИмяТаблицы = Неопределено ИЛИ
        
СтрокаТаблицы.ТаблицаSQL = ТекИмяТаблицы
     Тогда
       
ТекИмяТаблицы = СтрокаТаблицы.ТаблицаSQL;
       
//имя таблицы не изменилось продолжаем накапливать условие поиска
       
Если ИспользоватьSqlRegEx Тогда
           
//вариант RegEx
           
ТекУсловиеПоиска = ТекУсловиеПоиска +
           
" OR ( dbo.ufn_RegExIsMatch(["+СтрокаТаблицы.ИмяПоляПоиска+"], '"+
                                        
ШаблонПоиска+"', "+
                                         ?(
СУчетомРегистра,"0","1")+") = 1 ) ";
        Иначе
           
//вариант LIKE
           
ТекУсловиеПоиска = ТекУсловиеПоиска +
           
" OR ( ["+СтрокаТаблицы.ИмяПоляПоиска+"] LIKE '"+ШаблонПоиска+"' ) ";
        КонецЕсли;

     Иначе
       
//имя таблицы изменилось, надо сделать запрос и сохранить результат
       
RSet = C.Execute("SELECT * FROM ["+Connection.DefaultDatabase+
                        
"].[dbo].["+ТекИмяТаблицы+"] WHERE "+ТекУсловиеПоиска);
        Пока Не
RSet.Eof() Цикл
           
//если что-то нашлось, то надо получить ссылку на объект в 1С
            //ссылка хранится как binary(16)
           
ComSafeArray = RSet.Fields("_IDRRef").Value;

           
//получим 16-ричное строковое представление
           
СсылкаSQL = "0x"
           
+Из_Число_В_XСчисл(ComSafeArray.GetValue(15))
            +
Из_Число_В_XСчисл(ComSafeArray.GetValue(14))
            +
Из_Число_В_XСчисл(ComSafeArray.GetValue(13))
            +
Из_Число_В_XСчисл(ComSafeArray.GetValue(12))
            +
Из_Число_В_XСчисл(ComSafeArray.GetValue(11))
            +
Из_Число_В_XСчисл(ComSafeArray.GetValue(10))
            +
Из_Число_В_XСчисл(ComSafeArray.GetValue(9))
            +
Из_Число_В_XСчисл(ComSafeArray.GetValue(8))
            +
Из_Число_В_XСчисл(ComSafeArray.GetValue())
            +
Из_Число_В_XСчисл(ComSafeArray.GetValue(1))
            +
Из_Число_В_XСчисл(ComSafeArray.GetValue(2))
            +
Из_Число_В_XСчисл(ComSafeArray.GetValue(3))
            +
Из_Число_В_XСчисл(ComSafeArray.GetValue(4))
            +
Из_Число_В_XСчисл(ComSafeArray.GetValue(5))
            +
Из_Число_В_XСчисл(ComSafeArray.GetValue(6))
            +
Из_Число_В_XСчисл(ComSafeArray.GetValue(7));

           
//пребразуем в GUID
           
RSet2 = C.Execute("select convert(uniqueidentifier,"+СсылкаSQL+",1)");
            Пока
RSet2.Eof() = Ложь Цикл
               
_GUID = Сред(RSet2.Fields().Value,2,СтрДлина(RSet2.Fields().Value)-2);
               
RSet2.MoveNext();
            КонецЦикла;

           
//по GUID получим ссылку
           
СсылкаПоGUID = XMLЗначение(Тип(ВидМетаданных+"Ссылка."+ИмяМетаданных),_GUID);

           
//добавим в результат
           
С = ТаблицаРезультата.Добавить();
           
С.Метаданные = ТекМетаданные;
           
С.Данные = СсылкаПоGUID;


Вы можете свободно скачать обработку с открытым кодом и DLL компоненту.

Использовались материалы:
тут лежит SqlRegEx.dll
Поиск ссылок на объект средствами SQL


Прикрепленный файлРазмер
обработка с открытым кодом SQLПоискПоСтрокам.epf16.52 кб
SqlRegEx.dll20 кб

Похожие материалы

Справочники

Документы

Отчеты

Обработки