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