Хитрая задачка: интеграция таблиц клиентов из разных БД - Базы данных

Здравствуйте, michag, Вы писали:

M>Блин, то ли я туплю, то ли неясно излагаю свои мысли, в результате чего между нами возникает неполное понимание.

M>Нам нужно не только таблицу клиентов импортировать, но и связанные с клиентами данные — например, Orders (заказы, сделанные клиентами).

M>Представим себе: мы проимпортировал таблицу клиентов, из двух источников. В единой таблице в БД назначения у нас есть поле ClientGuid (как вы говорите).

M>Затем, нам нужно импортировать заказы. Вот берем первую запись из таблицы OrdersFromOracle — там стоит ClientId = 13245 (это идентификатор клиента в одной из БД-источников, но в БД назначения у нас этого идентификатора нет). А нам в БД назначения нужно, чтобы Orders ссылались на Clients.ClientGuid. То есть нам нужно из ClientId получить ClientGuid. И сделать это никак, кроме как через таблицу, хранящую соответствие между этими идентификаторами, по-моему, нельзя.

Все вам разжуй и в рот положи. Дарю.

CREATE TABLE dbo.Clients
    (
    ClientId int NOT NULL IDENTITY (1, 1),
    Name nvarchar(50) NOT NULL,
    ClientGuid uniqueidentifier NOT NULL ROWGUIDCOL,
    Ver timestamp NOT NULL -- это синоним типа rowversion, просто дизайнер SSMS его не понимает
    )
GO
ALTER TABLE dbo.Clients ADD CONSTRAINT
    DF_Clients_ClientGuid DEFAULT newid() FOR ClientGuid
GO
ALTER TABLE dbo.Clients ADD CONSTRAINT
    PK_Clients_ClientId PRIMARY KEY CLUSTERED 
    (
    ClientId
    )

GO
ALTER TABLE dbo.Clients ADD CONSTRAINT
    IX_Clients_ClientGuid UNIQUE NONCLUSTERED 
    (
    ClientGuid
    )

GO
CREATE NONCLUSTERED INDEX IX_Clients_Ver ON dbo.Clients
    (
    Ver
    )
GO


CREATE TABLE dbo.Orders
    (
    OrderId int NOT NULL IDENTITY (1, 1),
    ClientId int NOT NULL,
    OrderGuid uniqueidentifier NOT NULL ROWGUIDCOL,
    Ver timestamp NOT NULL
    )
GO
ALTER TABLE dbo.Orders ADD CONSTRAINT
    DF_Orders_OrderGuid DEFAULT newid() FOR OrderGuid
GO
ALTER TABLE dbo.Orders ADD CONSTRAINT
    PK_Orders_OrderId PRIMARY KEY CLUSTERED 
    (
    OrderId
    )

GO
CREATE NONCLUSTERED INDEX IX_Orders_ClientId ON dbo.Orders
    (
    ClientId
    )
GO
ALTER TABLE dbo.Orders ADD CONSTRAINT
    IX_Orders_OrderGuid UNIQUE NONCLUSTERED 
    (
    OrderGuid
    )

GO
CREATE NONCLUSTERED INDEX IX_Orders_Ver ON dbo.Orders
    (
    Ver
    )
GO
ALTER TABLE dbo.Orders ADD CONSTRAINT
    FK_Orders_Clients1 FOREIGN KEY
    (
    OrderId
    ) REFERENCES dbo.Clients
    (
    ClientId
    )
GO

Сервер с консолидироваными данными:

CREATE TABLE dbo.ClientsGlobal
    (
    Name nvarchar(50) NOT NULL,
    ClientGuid uniqueidentifier NOT NULL ROWGUIDCOL
    )
GO
ALTER TABLE dbo.ClientsGlobal ADD CONSTRAINT
    DF_ClientsGlobal_ClientGuid DEFAULT newid() FOR ClientGuid
GO
ALTER TABLE dbo.ClientsGlobal ADD CONSTRAINT
    PK_ClientsGlobal_ClientGuid PRIMARY KEY CLUSTERED 
    (
    ClientGuid
    )
GO


CREATE TABLE dbo.OrdersGlobal
    (
    ClientGuid uniqueidentifier NOT NULL,
    OrderGuid uniqueidentifier NOT NULL ROWGUIDCOL
    )
GO
ALTER TABLE dbo.OrdersGlobal ADD CONSTRAINT
    DF_OrdersGlobal_OrderGuid DEFAULT newid() FOR OrderGuid
GO
ALTER TABLE dbo.OrdersGlobal ADD CONSTRAINT
    PK_OrdersGlobal_OrderGuid PRIMARY KEY CLUSTERED 
    (
    OrderGuid
    )

GO
CREATE NONCLUSTERED INDEX IX_OrdersGlobal_ClientGuid ON dbo.OrdersGlobal
    (
    ClientGuid
    )
GO
ALTER TABLE dbo.OrdersGlobal ADD CONSTRAINT
    FK_OrdersGlobal_ClientsGlobal FOREIGN KEY
    (
    OrderGuid
    ) REFERENCES dbo.Clients
    (
    ClientGuid
    )
GO

Вставка тестовых данных:

insert Clients([Name]) values(N'Пупкин Василий');
insert Clients([Name]) values(N'Клитов Евгений');
insert Clients([Name]) values(N'Затрищенко Агропена');

insert Orders(ClientId) values(1);
insert Orders(ClientId) values(2);
insert Orders(ClientId) values(3);

Запросы используемые, например, для передачи данных через объект класса SqlBulkCopy

select
    [Name],
    ClientGuid,
    Ver
from
    Clients
where
    Ver > 0x00000000000007D2 -- [1]
compute
    max(Ver) -- [2]
;
/*
[1] - здесь должно быть значение [2] из предыдущего такого успешно реплицированного выполненного запроса.
Для репликации всей таблицы достаточно [1] = 0
*/

select
    (select
        ClientGuid
    from
        Clients c
    where
        c.ClientId = o.ClientId
    ) as ClientGuid,
    o.OrderGuid,
    o.Ver
from
    Orders o
where
    Ver > 0x00000000000007D4
compute
    max(Ver)
;

Здравствуйте, _d_m_, Вы писали:

___>Здравствуйте, michag, Вы писали:

Помедитируй еще над этим http://rsdn.ru/Forum/Message.aspx?mid=3179962&only=1

Здравствуйте, _d_m_, Вы писали:

___>Все вам разжуй и в рот положи. Дарю.

Только что прочел ваш ответ — сразу скажу спасибо большое за подарок

Буду изучать и размышлять.

Здравствуйте, michag, Вы писали:

M>Перед нами стоит задача интегрирования нескольких баз данных, различных видов (Oracle, SQL Server) и имеющих различную структуру.

Похоже, без репликаций не обойтись. Что-то конкретное посоветовать сложно, потому что задача имеет много вариантов решения. Какой из них устроит — сложно сказать, поэтому выскажу лишь общие соображения.
Не обязательно создавать 2 таблицы, можно использовать и одну, атрибуты которой будут объединенияем множеств атрибутов физиков и юриков. Разумеется, на это следует идти только при полной уверенности, что пустые значения атрибутов не такая большая проблема. Этот подход сэкономит нервы при написании приложения. С другой стороны, использование 2-х таблиц экономит место на диске, но не экономит нервы, если потребуется дополнять или модифицировать объединенную таблицу клиентов.
Общая идея при репликации — использование специального суррогатного репликационного ключа.
Помимо этой задачи, следует еще решить вопросы сериализации/десериализации при передаче по каналу, а также вопрос функции преобразования при использовании разноструктурных таблиц. Будет время — напишу статью. Пока есть лишь первоначальные наработки для случая двух одноструктурных баз, так что показывать нечего.
Да, и еще: скорее всего, придется написать менеджер репликаций с поддержкой распределенных транзакций.

Иными словами, будет желание — обращайтесь. Чем смогу — помогу.

Здравствуйте, megascalper, Вы писали:

M>Помимо этой задачи, следует еще решить вопросы сериализации/десериализации при передаче по каналу,

Зачем?

M>а также вопрос функции преобразования при использовании разноструктурных таблиц.

Представления с замещающим триггером.

M>Будет время — напишу статью. Пока есть лишь первоначальные наработки для случая двух одноструктурных баз, так что показывать нечего.

А у меня есть чего. См. мои сообщения. Уже два года промышленной эксплуатации.

M>Да, и еще: скорее всего, придется написать менеджер репликаций с поддержкой распределенных транзакций.

Зачем?

Здравствуйте, _d_m_, Вы писали:

M>>Помимо этой задачи, следует еще решить вопросы сериализации/десериализации при передаче по каналу,
___>Зачем?
В общем случае. Кто его знает, где эти базы находятся? Через инет гонять прямые подключения? Нафиг-нафиг!

M>>а также вопрос функции преобразования при использовании разноструктурных таблиц.
___>Представления с замещающим триггером.
Угу. Но такиж преобразующая функция, нихт вар?

M>>Будет время — напишу статью. Пока есть лишь первоначальные наработки для случая двух одноструктурных баз, так что показывать нечего.
___>А у меня есть чего. См. мои сообщения. Уже два года промышленной эксплуатации.
Два года — уважаю. Смотрел. Много букавак, но читал. Проникся. Гуид как ключ для репликации — сильно. MS SQL timestamp как версия строки — сильно (по крайней мере, для MS SQL). Увидел простой случай построения реплики в случае внесения или обновления записи. Специально попробовал — на разных серверах сработает, только если на одном из них разрешать исключительно выборку данных. Вывод: хорошая и надежная система, при условии, что технические ограничения не мешают реальному использованию.

M>>Да, и еще: скорее всего, придется написать менеджер репликаций с поддержкой распределенных транзакций.
___>Зачем?

Затем, что все равно придется решать вопрос "кто прав — кто не прав". В простом случае разрешение этих вопросов переваливается на один из серверов. Если функциональности хватит. А транзакции все равно нужны, потому что либо придется выгонять всех юзверей на время репликации, либо молиться на Богдыхара, чтобы они чего не изменили за время переноса данных. Впрочем, учитывая предыдущий абзац, весь менеджер вырождается в 3 оператора.

Здравствуйте, megascalper, Вы писали:

M>Здравствуйте, _d_m_, Вы писали:

M>>>Помимо этой задачи, следует еще решить вопросы сериализации/десериализации при передаче по каналу,
___>>Зачем?
M>В общем случае. Кто его знает, где эти базы находятся? Через инет гонять прямые подключения? Нафиг-нафиг!

Вобще MS SQL поддерживает шифрование на уровне соединения, но мы пользуемся VPN.

M>>>а также вопрос функции преобразования при использовании разноструктурных таблиц.
___>>Представления с замещающим триггером.
M>Угу. Но такиж преобразующая функция, нихт вар?

Да.

M>>>Будет время — напишу статью. Пока есть лишь первоначальные наработки для случая двух одноструктурных баз, так что показывать нечего.
___>>А у меня есть чего. См. мои сообщения. Уже два года промышленной эксплуатации.
M>Два года — уважаю. Смотрел. Много букавак, но читал. Проникся. Гуид как ключ для репликации — сильно. MS SQL timestamp как версия строки — сильно (по крайней мере, для MS SQL). Увидел простой случай построения реплики в случае внесения или обновления записи. Специально попробовал — на разных серверах сработает, только если на одном из них разрешать исключительно выборку данных. Вывод: хорошая и надежная система, при условии, что технические ограничения не мешают реальному использованию.

На самом деле система несколько сложнее. Поддерживается двухсторонняя репликация. Т.е. для реплицируемой таблицы есть еще вспогат. две таблицы: ключи удаленных строк [1], ключи реплицир. строк [2]. Плюс триггер на удаление — для заполнения таблицы [1]. Плюс триггер на обновление на реплицир. таблицу — для удаления из таблицы [2].
При реализации я использовал все MS-овские заделы для поддержки родной репликации:
— столбцы rowversion (ака timestamp, хотя к времени не имеют никакого отношения);
— использование опций для триггеров и констрэйнтов not for replication.

M>>>Да, и еще: скорее всего, придется написать менеджер репликаций с поддержкой распределенных транзакций.
___>>Зачем?

Я имел ввиду, что поддержка распределенных транзакций не нужна.

M>Затем, что все равно придется решать вопрос "кто прав — кто не прав". В простом случае разрешение этих вопросов переваливается на один из серверов. Если функциональности хватит. А транзакции все равно нужны, потому что либо придется выгонять всех юзверей на время репликации, либо молиться на Богдыхара, чтобы они чего не изменили за время переноса данных. Впрочем, учитывая предыдущий абзац, весь менеджер вырождается в 3 оператора.

Менеджер у нас есть, у него такой функционал:
— удаление данных через bulk copy API;
— заливка данных в транзакции пакетами максимального указанного в конфиге размера (обычно 20000 строк) через bulk copy API;
— если есть конфликт — локализация конфликта до 1 строки и запись данных конфликта репликации [1];
— запись максимальной реплицированной версии [2].

Так если возникнет ошибка в случае [1], то конфликт будет записан в следующем сеансе репликации;
Если возникнет ошибка в случае [2], то просто эти данные будут переданы повторно.
Вобщем нет нужды в распределенных транзакциях.

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

	От:	_d_m_
	Дата:	20.11.08 01:54
	Оценка:

	От:	_d_m_
	Дата:	20.11.08 02:45
	Оценка:

	От:	michag
	Дата:	20.11.08 08:31
	Оценка:

От:	megascalper	http://lazy-coder.livejournal.com
Дата:	25.11.08 16:15
Оценка:

	От:	_d_m_
	Дата:	25.11.08 23:44
	Оценка: