gifts2017

Связи Метаданных. Построитель SQL запросов

Опубликовал олег шишкин (shishkin1966) в раздел Программирование - Теория программирования

Для написания SQL запросов часто требуется знать взаимосвязи объектов Метаданных. Данная обработка помогает решить проблему написания SQL запросов с учетом взаимосвязей объектов Метаданных.

Данная обработка строит таблицу взаимосвязей в терминах SQL следующих объектов Метаданных:

- Справочники (Реквизиты, ТабличныеЧасти, Владельцы)

- Документы (Реквизиты, ТабличныеЧасти)

- РегистрыНакопления (Измерения, Реквизиты, Ресурсы)

- РегистрыСведений (Измерения, Реквизиты, Ресурсы)

- РегистрыБухгалтерии (Измерения, Реквизиты, Ресурсы)

- РегистрыРасчета (Измерения, Реквизиты, Ресурсы)

- ЖурналыДокументов (РегистрируемыеДокументы,Графы)

- Задачи (Реквизиты, ТабличныеЧасти)

- БизнесПроцессы (Реквизиты, ТабличныеЧасти, ВедущаяЗадача)

Обработка позволяет:

- визуально строить SQL запросы. Построитель может перетаскиванием вставлять в запрос имена таблиц и входящие в нее столбцы, просто столбцы, взаимосвязи таблиц. По возможности вставляются описания имен таблиц и колонок в терминах 1С.

- выполнять запросы и копировать в Excel результаты. Копируется текстовое представление значения ячеек.

- копировать в Excel таблицу взаимосвязей SQL таблиц

Соединение с БД происходит с использованием OLEDB провайдера. Если у вас есть проблемы с соединением с БД, то используйте собственную строку соединения. При удачном соединении параметры соединения сохраняются в файле ConnectСвязиМетаданныхПостроительЗапросаSQL.xml, который используется при последующем запуске обработки. Строка соединения по умолчанию тестировалась на MS SQL Server 2005.

Для определения формы вывода колонок табличного поля результата служит поле выбора подставлять заголовки. Режим "Определять тип колонок" соотвествует выводу заголовка колонки в виде "ИмяТаблицы_ИмяКолонки", по которому будет определен тип колонки, в режиме "Брать из метаданных" заголовки колонок соответствуют именам реквизитов таблиц, в режиме "Брать из метаданных все " - заголовки колонок соответствуют именам реквизитов таблиц, а синонимы таблиц будут соответстовать именам таблиц 1С, в режиме "Оставлять как есть" в качестве заголовков колонок отображаются имена полей. Построитель позволяет выводить результаты сразу нескольких Select'ов.

Для совместной работы с текстовыми редакторами (например, с EditPlus) добавлено поле "Автообновление текста запроса".

Для серверов MS SQL добавлена возможность просмотра плана запроса - флаг "План".

При наличие справочника "СправочникСвязиМетаданных" со следующей структурой:

ПервичнаяТаблицаПолноеИмя                           СТРОКА(255)
ПервичнаяТаблицаИмяТаблицыХранения         СТРОКА(128)
ПервичнаяТаблица                                             СТРОКА(255)
КолонкаПервичнойТаблицы                               СТРОКА(128)
КолонкаПервичнойТаблицыИмяПоляХранения  СТРОКА(128)
СвязаннаяТаблицаПолноеИмя                            СТРОКА(255)
СвязаннаяТаблицаИмяТаблицыХранения          СТРОКА(128)
СвязаннаяТаблица                                              СТРОКА(255)
КолонкаСвязаннойТаблицы                                СТРОКА(128)
КолонкаСвязаннойТаблицыИмяПоляХранения   СТРОКА(128)
ТипСвязи                                                              СТРОКА(64)

Все поля индексированы и переменной длины. Программа заполняет данный справочник и обращается уже к нему (сокращая тем самым время загрузки - время загрузки ВО для УПП редакции 1.2 составляет 2 мин). Для обновления его - нажмите пункт меню  "Обновить информацию о Метаданных".

Пример строки соединения с БД PostgreSQL:

Provider=PostgreSQL OLE DB Provider;Data Source=HOME;location=DB_1C_82;User ID=postgres;password=*****;

или лучше через ODBC DSN:

DSN=PostgreSQL35W;DRIVER=PostgreSQL;DATABASE=DB_1C_81;Servername=HOME;UID=postgres;PWD=*****;

Для отображения ссылок вы можете использовать следующую функцию преобразования Binary колонок в Hex строки (Transact SQL):

create function dbo.sp_hexadecimal ( @binvalue varbinary(255) )
returns nvarchar(255)
as
begin
  declare
    @charvalue nvarchar(255)
    ,@i int
    ,@length int
    ,@hexstring nchar(16)
    ,@tempint int
    ,@firstint int
    ,@secondint int

  select @charvalue = '0x'
  select @i = 1
  select @length = datalength(@binvalue)
--  select @hexstring = '0123456789abcdef'
  select @hexstring = '0123456789ABCDEF'

  while (@i <= @length) begin
    select @tempint = convert(int, substring(@binvalue,@i,1))
    select @firstint = floor(@tempint/16)
    select @secondint = @tempint - (@firstint*16)
    select @charvalue = @charvalue + substring(@hexstring, @firstint+1, 1) + substring(@hexstring, @secondint+1, 1)
    select @i = @i + 1
  end

  return ( @charvalue )
end
go

grant execute on dbo.sp_hexadecimal to public
go

Для получения ссылки по строковому представлению Ссылки можете использовать функцию

ПолучитьСсылкуПоID(ID)

 

Скачать файлы

Наименование Файл Версия Размер Кол. Скачив.
Связи Метаданных. Построитель SQL запросов
.epf 31,04Kb
14.12.10
457
.epf 31,04Kb 457 Скачать
Transact-SQL Reference (MS SQL). CHM формат
.zip 2,07Mb
25.11.10
51
.zip 2,07Mb 51 Скачать
Postgre SQL Reference (Postgre SQL). CHM формат
.chm 2,94Mb
25.11.10
34
.chm 2,94Mb 34 Скачать

См. также

Подписаться Добавить вознаграждение

Комментарии

1. Герман (German) 22.11.10 09:58
2. олег шишкин (shishkin1966) 22.11.10 13:08
(1) а зачем он нужен ? Можно тащить результат из RecordSet во что угодно. Можно тащить в обычное табличное поле (я не стал этого делать, чтобы убрать преобразование типов RecordSet -> 1C). Кому нужны суррогатные ключи 1C ? Важны связи между таблицами, а не представление суррогатных ключей. Цель этой обработки - создание Query Analyzer для 1С. Когда работаешь с БД, важно не наличие каких-то сущностей, а их связи друг с другом.
3. Герман (German) 22.11.10 13:10
а как же параметры запросов :D
4. олег шишкин (shishkin1966) 22.11.10 13:20
(3)
в Query Analyzer все параметры указываются ручками. Даже списки. А писать условие по типу Where ID = 2247623874356 или Where ID in (2463296,2452845) это дурной тон в разработке !!! (потому что прийдя через месяц никак не узнать почему ID = 348539 или 85674976) - таких примеров можно увидеть у Диасофта - там такого добра хватает. У нормальных разработчиков пишется (разве сложно написать ?)
T1 join T2 on t1.id = t2.id where t2.Фамилия in ("Шишкин","Пупкин","Фыфкин") или
T1 where T1.ID = (select max(ID) from T2 where Фамилия = "Шишкин")
5. олег шишкин (shishkin1966) 24.11.10 12:54
доработан вызов ADO, для обработки таких запросов:

sp_executesql
N'with Номенклатура(Level,_Description, _ParentIDRRef) as
(Select
1 as Level
,_Reference4._Description
,_Reference4._IDRRef
From _Reference4
Where 1=1
and convert(int,_Reference4._ParentIDRRef) = 0
union all
Select
Level + 1
,_Reference4._Description
,_Reference4._IDRRef
From _Reference4
join Номенклатура on _Reference4._ParentIDRRef = Номенклатура._ParentIDRRef
)
select Level,_Description as ''Наименование'' from Номенклатура
'
6. Трактор Трактор (Трактор) 24.11.10 13:30
7. олег шишкин (shishkin1966) 24.11.10 13:52
я могу еще под Oracle, только не знаю на какую версию он встанет. У меня есть 9.2/10.
Под Linux (PostGreSQL) ничего не писал. Даже Unux не ставил :cry:, а надо бы
8. олег шишкин (shishkin1966) 24.11.10 13:53
9. олег шишкин (shishkin1966) 24.11.10 13:58
а так он писан через вызов ADO, а там какой провайдер укажешь - к тому и зацепишься.
10. олег шишкин (shishkin1966) 24.11.10 17:04
Дополнения:
- Расширил вывод информации о связи в тексте запроса
11. олег шишкин (shishkin1966) 30.11.10 10:48
Дополнения:
- обработка задач
12. олег шишкин (shishkin1966) 30.11.10 17:08
Дополнения:
- для серверов MS SQL добавлена возможность просмотра плана запроса
13. олег шишкин (shishkin1966) 01.12.10 16:00
Дополнения:
- добавлена вставка по возможности синонимов 1С в текст запроса. Режим поля "Подставлять заголовки" = "Брать из Метаданных все"
14. Наталия Мастербатова (zzz_natali) 02.12.10 21:38
При запуске обработки заполняется только левая часть формы "Текст запроса и результат". правая часть остается пустая. Где и чем помыть руки? Спасибки...
15. олег шишкин (shishkin1966) 02.12.10 23:37
(14)
Данный построитель аналог программы Query Analyzer фирмы Microsoft для MS SQL 2000. Это главный инструмент разработчика запросов к БД от MS. Ваша задача построить запрос в текстовом виде. Для этого просто перетягиваете таблицы (колонки) из дерева таблиц на основное поле, или это же самое проделываете с деревом связей таблиц 1С. Построив запрос вы затем просто его выполняете - нажимая кнопку "Выполнить запрос" и анализируете полученный результат. Все просто и гениально. Если надо задать параметры - строите условие WHERE. Только среди разработчиков SQL считается дурным тоном строить вот такие условия
Select * from Table1 Where ID = 23424532523, что равносильно запросу 1с: Выбрать * ИЗ Номенклатура ГДЕ Номенклатура = &Номенклатура_Ссылка;

Для рисования запросов можете использовать любимый программерский редактор - у меня это EditPlus. Он удобный, шустрый и подсвечивает красиво.

Или вам нужен визуальный построитель?
16. Наталия Мастербатова (zzz_natali) 03.12.10 08:35
(15) Гениально было бы об этом написать в предварительном описании Вашей обработки! :)
Или Вы блондинофоб? ;)
(блондинофоб - здесь: человек, ненавидящий особей женского пола со светлым типом волос)
Спасибо.
17. олег шишкин (shishkin1966) 03.12.10 10:01
А вы в текстовом виде когда - нибудь писали запросы или всегда пользуетесь построителем запроса ?
Просто ни один из разработчиков БД, которых я знаю, не пользуются никакими визуальными построителями запросов. Все набирают текст в обычных текстовых редакторах и посылают их на выполнение. У них никогда не возникали вопросы, а почему правое поле пустое, будь это Oracle Developer или Query Analyzer. Для них только нужен список таблиц,VIEW(и их колонок) и описание связей между таблицами и все.
18. олег шишкин (shishkin1966) 09.12.10 23:33
Добавлено:
- поддержка вывода результатов нескольких Select'ов в одном запросе
19. олег шишкин (shishkin1966) 10.12.10 16:05
Добавлено:
- запуск на выполнение выделенной части текста
Поручик; +1 Ответить
20. Артем Пиженков (pizhenkov) 02.07.14 13:11
{Форма.Форма.Форма(2242)}: Поле объекта не обнаружено (СвязиМетаданныхПостроительЗапросаSQL)
Форма = Обработки.СвязиМетаданныхПостроительЗапросаSQL.ПолучитьФорму("Connect");

При открытии в УТ 10.3 (1С:Предприятие 8.2 (8.2.18.109))
21. олег шишкин (shishkin1966) 03.07.14 19:43
(20) pizhenkov,
я больше не поддерживаю 1С - раньше все работало - теперь все вопросы к ним. Если они меняют доступ к объектам даже в пределах одной подверсии - слов нет. Все работало вроде до версии 8.2.13 - дальше все было вырезано. Лазил по форумам - ничего не получилось. Спросите у знающих 1с - как открыть модальную форму внешней обработки.
22. Алексей Главизнин (realchel) 24.12.15 20:21
А что в этом файле?
Transact-SQL Reference (MS SQL).

Postgre SQL Reference (Postgre SQL).
23. олег шишкин (shishkin1966) 25.12.15 10:21
(22) realchel,
Описание языков программирования БД
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа