Eu tenho duas tabelas armazenadas em dois bancos de dados diferentes e preciso realizar alguma sincronização entre as duas tabelas com base em lastmodifieddate
e lastSyncAt
. Vamos chamá-los como db1
e db2
. Este é o esquema DDL para a tabela db1.reps
- aquele que precisa ser atualizado por meio da sincronização:
CREATE TABLE `db1.reps` (
`veeva_rep_id` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
`territories_id` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`display_name` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
`avatar_url` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'default_avatar.png',
`rep_type` varchar(45) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'VEEVA',
`username` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
`first` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
`last` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
`title` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`bio` longtext COLLATE utf8_unicode_ci,
`phone` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`email` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
`inactive` tinyint(1) NOT NULL DEFAULT '0',
`lastLoginAt` datetime DEFAULT NULL,
`lastSyncAt` datetime NOT NULL,
`repTokenId` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
`createdAt` datetime NOT NULL,
`updatedAt` datetime NOT NULL,
PRIMARY KEY (`veeva_rep_id`),
KEY `IDX_485DE7B033B9A304` (`territories_id`),
CONSTRAINT `FK_485DE7B033B9A304` FOREIGN KEY (`territories_id`) REFERENCES `territories` (`veeva_territory_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
E este é o DDL para db2.user
- tipo de mestre:
CREATE TABLE `user` (
`id` varchar(18) NOT NULL,
`username` varchar(80) DEFAULT NULL,
`lastname` varchar(80) DEFAULT NULL,
`firstname` varchar(40) DEFAULT NULL,
`email` varchar(128) DEFAULT NULL,
`lastmodifieddate` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Eu poderia fazer isso usando programação, mas quero saber e aprender, se possível, a fazer isso por meio do SQL. Eu preciso, para cada linha, db2.user
realizar um UPDATE em db1.reps
mas apenas quando db2.user.lastmodifieddate > db1.reps.lastSyncAt
, como? Devo esclarecer que db1.reps.lastSyncAt
deve ser atualizado para NOW()
se um UPDATE foi executado nele. Isto é o que eu fiz até agora:
INSERT INTO db1.reps (
display_name,
username,
`first`,
`last`,
email,
lastSyncAt
)(
SELECT
CONCAT(
LCAPITAL (firstname),
' ',
LCAPITAL (lastname)
) AS display_name,
username,
LCAPITAL (firstname),
LCAPITAL (lastname),
email,
NOW()
WHERE
db1.reps.veeva_rep_id = db2.`user`.id
)
WHERE
db2.`user`.lastmodifieddate > db1.reps.lastSyncAt ON DUPLICATE KEY UPDATE db1.reps.display_name =
VALUES
(
CONCAT(
LCAPITAL (db2.`user`.firstname),
' ',
LCAPITAL (db2.`user`.lastname)
)
),
db1.reps.username =
VALUES
(db2.`user`.username),
db1.reps.`first` =
VALUES
(LCAPITAL(db2.`user`.firstname)),
db1.reps.`last` =
VALUES
(LCAPITAL(db2.`user`.lastname)),
db1.reps.email =
VALUES
(db2.`user`.email)
db1.reps.lastSyncAt =
VALUES
(NOW())
Alguém pode me dar uma ajuda?
EDITAR
Por db2.user
ter duplicatas (uma tabela sem nenhuma restrição - veja a imagem abaixo) preciso limpar um pouco a trigger antes de executar.
É assim que estou fazendo no lado do SQL:
SELECT
count(*) AS reps,
userid,
lastmodifieddate,
territoryid
FROM
(
SELECT
userid,
territoryid,
count(*) AS territories
FROM
userterritory
GROUP BY
userid
HAVING
territories = 1
) T1
INNER JOIN (
SELECT
id AS userid,
lastmodifieddate
FROM
`user`
WHERE
`user`.`id` IN (
SELECT
userterritory.`userid`
FROM
userterritory
)
) T2 USING (userid)
GROUP BY
territoryid
HAVING
reps = 1
A consulta acima me garante apenas repetições em um território, como deveria ser. Como devo modificar o gatilho para fazer isso?
NOTA: LCAPITAL()
é uma função que tenho para normalizar strings
Você pode usar um
TRIGGER
para atualizardb1.reps.lastSyncAt
quandodb2.user.lastmodifieddate
for atualizado.Acionar:
E se você quiser
INSERT
entrar nareps
tabela quando qualquer linha for adicionada àuser
tabela:A propósito, você precisa alterar os tipos de dados de suas tabelas porque 75-80% são apenas
VARCHARs
. A funçãoLCAPITAL
que fiz pode ser usada com mais de 1 palavra, exemplo:SELECT test.LCAPITAL(CONCAT('oanRE',' ','AnTIGUA'));
Resultado: