Category=Database Maintenance
Name=05. Migrate- Image Data to SURROGATEIMAGE
Description=Grabs data from current VMR tables and imports it to populate the SurrogateImage table

Heading=Import Page Data Into SurrogateImage

OutputLevel=1

Import2LSQL=SELECT DOCUMENTID from DOCUMENT|SELECT	\
								\
DISTINCT '{0}' DOCUMENTID,		\
lfNr	PAGEID,					\
(SELECT INSTITUTIONID FROM INSTITUTION WHERE NAME='Bibelmuseum') AS INSTITUTIONID,			\
'10' SURROGATEID,				\
bild	SURROGATEURI,			\
max(concat('http://intf.uni-muenster.de/vmr/NTVMR/thumb/','{0}','/',thumb)) as FULLTHUMB,			\
coalesce(rechte, 0) DISTRIBUTIONLICENSEID	\
								\
from cont{0}					\
where bild is not null and CHAR_LENGTH(bild) > 1	\
group by DOCUMENTID, lfNR, INSTITUTIONID, SURROGATEID, bild order by lfNr|		\
INSERT INTO SURROGATEIMAGE (	\
								\
DOCUMENTID,						\
PAGEID,							\
INSTITUTIONID,					\
SURROGATEID,					\
SURROGATEURI,					\
THUMBNAILURI,					\
DISTRIBUTIONLICENSEID			\
) VALUES ({0}, {1}, {2}, '{3}', '{4}', '{5}', {6})

Import2LSQL2=SELECT DOCUMENTID from DOCUMENT|SELECT	\
								\
DISTINCT '{0}' DOCUMENTID,		\
lfNr	PAGEID,					\
(SELECT INSTITUTIONID FROM INSTITUTION WHERE NAME='Bibelmuseum') AS INSTITUTIONID,			\
'20' SURROGATEID,				\
bild2	SURROGATEURI,			\
max(concat('http://intf.uni-muenster.de/vmr/NTVMR/thumb/','{0}','/',thumb)) as FULLTHUMB,			\
'2' DISTRIBUTIONLICENSEID	\
								\
from cont{0}					\
where bild2 is not null and CHAR_LENGTH(bild2) > 1	\
group by DOCUMENTID, lfNR, INSTITUTIONID, SURROGATEID, bild2 order by lfNr|		\
INSERT INTO SURROGATEIMAGE (	\
								\
DOCUMENTID,						\
PAGEID,							\
INSTITUTIONID,					\
SURROGATEID,					\
SURROGATEURI,					\
THUMBNAILURI,					\
DISTRIBUTIONLICENSEID			\
) VALUES ({0}, {1}, {2}, '{3}', '{4}', '{5}', {6})

Import2LSQL3=SELECT DOCUMENTID from DOCUMENT|SELECT	\
								\
DISTINCT '{0}' DOCUMENTID,		\
lfNr	PAGEID,					\
(SELECT INSTITUTIONID FROM INSTITUTION WHERE NAME='Bibelmuseum') AS INSTITUTIONID,			\
'30' SURROGATEID,				\
bild3	SURROGATEURI,			\
max(concat('http://intf.uni-muenster.de/vmr/NTVMR/thumb/','{0}','/',thumb)) as FULLTHUMB,			\
'2' DISTRIBUTIONLICENSEID	\
								\
from cont{0}					\
where bild3 is not null and CHAR_LENGTH(bild3) > 1	\
group by DOCUMENTID, lfNR, INSTITUTIONID, SURROGATEID, bild3 order by lfNr|		\
INSERT INTO SURROGATEIMAGE (	\
								\
DOCUMENTID,						\
PAGEID,							\
INSTITUTIONID,					\
SURROGATEID,					\
SURROGATEURI,					\
THUMBNAILURI,					\
DISTRIBUTIONLICENSEID			\
) VALUES ({0}, {1}, {2}, '{3}', '{4}', '{5}', {6})


Import2LSQL4=SELECT DOCUMENTID from DOCUMENT|SELECT	\
								\
DISTINCT '{0}' DOCUMENTID,		\
lfNr	PAGEID,					\
(SELECT INSTITUTIONID FROM INSTITUTION WHERE NAME='Bibelmuseum') AS INSTITUTIONID,			\
'40' SURROGATEID,				\
bild4	SURROGATEURI,			\
max(concat('http://intf.uni-muenster.de/vmr/NTVMR/thumb/','{0}','/',thumb)) as FULLTHUMB,			\
'2' DISTRIBUTIONLICENSEID	\
								\
from cont{0}					\
where bild4 is not null and CHAR_LENGTH(bild4) > 1	\
group by DOCUMENTID, lfNR, INSTITUTIONID, SURROGATEID, bild4 order by lfNr|		\
INSERT INTO SURROGATEIMAGE (	\
								\
DOCUMENTID,						\
PAGEID,							\
INSTITUTIONID,					\
SURROGATEID,					\
SURROGATEURI,					\
THUMBNAILURI,					\
DISTRIBUTIONLICENSEID) VALUES ({0}, {1}, {2}, '{3}', '{4}', '{5}', {6})

IgnoreException=Error Code: 1146
IgnoreException2=Error Code: 1146
IgnoreException3=Error Code: 1146
IgnoreException4=Error Code: 1146