populateV3Publisher+Version+Action.sql 1.67 KB
tee populateV3Publisher+Version+Action.log

SET foreign_key_checks = 0;

insert into Publisher (publisherId, validatedVersion_publisherVersionId)
	select ID_EDITEUR, ID_EDITEUR from editeur;

insert into PublisherVersion (publisherVersionId, publisher_publisherId,
	publisherName, publisherStreetAddress, publisherPostalCode,
	publisherPostOfficeBoxNumber, publisherAddressRegion,
	publisherAddressLocality, publisherAddressCountry_countryId,
	publisherTelephone, publisherEmail, publisherURL,
	publisherHistory, publisherVersionAuthor_userId, publisherVersionDatetime)
select ID_EDITEUR, ID_EDITEUR, NOM_EDITEUR, ADR_EDITEUR, CP_EDITEUR, NULL,
	NULL, VILLE, countryId, TEL_EDITEUR, EMAIL_EDITEUR, URL_EDITEUR,
	HISTORIQUE, ID_UTILISATEUR, DATE_ACTION
from editeur e
left join Country on countryName = e.PAYS
join v2.journal j on j.ID_JOURNALISABLE = e.ID_EDITEUR
where j.DATE_ACTION = (select max(DATE_ACTION) from v2.journal j2
	where j2.ID_JOURNALISABLE = e.ID_EDITEUR)
union
select ID_EDITEUR, ID_EDITEUR, NOM_EDITEUR, ADR_EDITEUR, CP_EDITEUR, NULL,
	NULL, VILLE, countryId, TEL_EDITEUR, EMAIL_EDITEUR, URL_EDITEUR,
	HISTORIQUE, null, null
from editeur e
left join Country on countryName = e.PAYS
where ID_EDITEUR not in (select distinct ID_JOURNALISABLE from v2.journal)
;

insert into PublisherAction (ActionType, publisherActionAuthor_userId,
	publisherVersion_publisherVersionId, publisherActionDatetime,
	publisher_publisherId)
select 0, ID_UTILISATEUR, ID_EDITEUR, DATE_ACTION, ID_EDITEUR
from editeur e
join v2.journal j on j.ID_JOURNALISABLE = e.ID_EDITEUR
where j.DATE_ACTION = (select max(DATE_ACTION) from v2.journal j2
	where j2.ID_JOURNALISABLE = e.ID_EDITEUR)
;

SET foreign_key_checks = 1;