# Example – migrating a document database
For our internal "Improvement suggestion scheme", we have to migrate a database with about 1400 entries to ELO. The metadata and the documents are located in this database. In ELO, we want to create a folder every time metadata is entered, which then contains the actual document as a child entry. ELOas has been selected as primary tool for the migration.
Since ELOas currently cannot create documents, a dummy entry had to be initially created for each folder. Fortunately, the entries in the database have been numbered consecutively from 1 to 1440. For this reason, the dummy folders were relatively simple to create using a VBS script. All folders were created within another folder with the object ID of 274312.
Set ELO = CreateObject("ELO.professional")
Elo.CheckUpdate 0
for i=1 to 1440
call Elo.PrepareObjectEx( 0, 4, 337 )
Elo.ObjShort="TrackId " & i
Elo.ObjIndex="#274312"
call Elo.SetObjAttrib(2, i)
call Elo.SetObjAttrib(0, "GilleM")
call Elo.SetObjAttrib(3, "Produktverbesserung")
Elo.UpdateObject
next
Elo.CheckUpdate 1
After this, ELOas is called. The data is collected from an SQL database:
"select Editor, Email, TheSubject, LTrim(BunField1) BunField1,
ClassName, Task
from [InetHelpDesk].[dbo].tblTasks a,
[InetHelpDesk].[dbo].tblBatch b,
[InetHelpDesk].[dbo].tblClass c,
[InetHelpDesk].[dbo].tblUser d
where a.BunID = b.BunId
and a.KlaID = c.KlaID
and a.UsrID = d.UsrID
and AufID = " + ETS_COUNT
Fig.: SQL database
This is a somewhat extensive SELECT statement, which otherwise offers no special features. There is only one point worth mentioning: in the Select list, there is a column titled LTrim(BunField1) BunField1. In the database field BunField1, the data is partially saved with leading spaces, which we don't want. These are removed with LTrim. However, this means that the column would no longer have a name, which is why the column name is subsequently specified as BunField1. This technique should always be used when calculated values are to be used in the Select list.
The complete ruleset will look like this:
<ruleset>
<base>
<name>ImportTracker</name>
<search>
<name>"ETS_COUNT"</name>
<value>"*"</value>
<mask>337</mask>
<max>200</max>
</search>
<interval>1H</interval>
</base>
<rule>
<name>Rule1</name>
<condition></condition>
<script>
/* The data for the current folder is
*/ loaded from the database here
var item = db.getLine(1, "select Editor, Email, TheSubject,
LTrim(BunField1) BunField1, ClassName, Task
from [InetHelpDesk][dbo].tblTasks a,
[InetHelpDesk].[dbo].tblBatch b,
[InetHelpDesk].[dbo].tblClass c,
[InetHelpDesk].[dbo].tblUser d
where a.BunID = b.BunId and a.KlaID = c.KlaID and a.UsrID = d.UsrID and AufID = " + ETS_COUNT);
/* ETS_COUNT contains the record number, which is cleared after successful processing. */
ETS_COUNT = "";
/* The short name field is completed from the database, please note maximum field length! */
NAME = item.DerBetreff;
if (NAME == "") { NAME = "unknown"; }
if (NAME.length() > 127) { NAME = NAME.substring(0, 126); }
// The initiator is populated from the database.
ETS_MAIL = item.Email;
/* The subject field was assigned different keywords in the database than in the repository
/* translation table is used here for this reason.
A column index is used in ELO. */
var thema = item.BunFeld1;
if (thema == "Administration, Installation, Reporting") { thema = "Administration¶Installation¶Reporting"; }
if (thema == "Display, Sort, Edit, Send, Manage, Search") { thema = "Document Editing¶Viewer¶Structure Editing¶Search"; }
if (thema == "Display, Edit, Sort, Send, Manage, Search") { thema = "Document Editing¶Viewer¶Structure Editing¶Search"; }
if (thema == "User Interface, Design, Menus, Navigation") { thema = "Usability¶Interface"; }
if (thema == "Sticky notes, Stamps") { thema = "Annotations"; }
if (thema == "Office / Explorer Integration") { thema = "Office Integration¶OS Integration"; }
if (thema == "Offline availability") { thema = "Offline"; }
if (thema == "Links, References, Attachments") { thema = "Links¶References"; }
if (thema == "Scanning, Intray, Conversion, Printing") { thema = "Scanning¶Intray¶Conversion¶Printing"; }
if (thema == "Security, Login, Encryption, User rights") { thema = "User rights"; }
if (thema == "Keyword lists, Metadata, Metadata forms, Versioning") { thema = "Metadata¶Document filing"; }
if (thema == "Workflow, Tasks") { thema = "Workflow¶Tasks"; }
if (thema == "Interfaces, Scripts") { thema = "Scripting¶Interfaces"; }
ETS_THEMA = thema;
ETS_USER = "Product management";
ETS_STATUS_INT = item.ClassName;
EM_WRITE_CHANGED = true;
//* The database information has now been entered. Only the document still remains unprocessed.
This will be created as an HTML file with an XML control file
for the ELO XML Importer.. First, the HTML file is written:*/
var id = Sord.getId();
var dataFile = new File("d:\\temp\\trk\\" + id + ".htm");
Utils.stringToFile(item.Order, dataFile, "ISO-8859-15");
/* Next, the XML data stream is created.
As the metadata is entered in the folder, only rudimentary metadata can be found here. */
var xmlDesc = NAME.replace("\"", "'").
replace("&", "&amp;").
replace("<", "&lt;").
replace(">", "&gt;");
var xmlFile = new File("d:\\temp\\trk\\" + id + ".xml");
var xmlText = "<?xml version=\"1.0\" ?><eloobjlist
ver=\"1.0\"><obj><desc value=\"";
xmlText = xmlText + xmlDesc;
xmlText = xmlText +
"\"/><type value=\"0\"/><destlist><destination
type=\"1\" value=\"#";
xmlText = xmlText + id;
xmlText = xmlText + "\"/></destlist><docfile name=\"";
xmlText = xmlText + id;
xmlText = xmlText + ".htm\"/></obj></eloobjlist>";
// The XML file is written last.
Utils.stringToFile(xmlText, xmlFile, "UTF-8");
</script>
</rule>
<rule>
<name>Global Error Rule</name>
<condition>OnError</condition>
<script></script>
</rule>
</ruleset>
After ELOas has entered the metadata the database and created the HTML and XML document files, the ELO XML Importer imports the HTML files into the corresponding folders. This concludes the migration process. Time required for the complete project: about 4 hours.