Thursday, February 12, 2004

Using XML for export/import of DB install values In many systems, you find a bunch of sql code that gets executed at install time. This code is usually brittle, hard to read, and generally nasty. My last employer had a lot of code like this, and it was just that, VERY nasty. I've just come up with, I hope, a better way. I'm using XML to store the data, and then reading it and using my existing database (see: Class::DBI) classes to handle the insertions/updates. This gives me the following benefits:

  1. I'm using the same code to do the initial insertion that I use in the regular running of the system, so I only have to fix a bug once
  2. When I migrate to different new database, I don't have to port the installation code
  3. My code handles changes, as well as additions. So, if you change the value of a column, the loader will handle that properly, instead of doing simple "inserts" - see find_or_create() on Class::DBI
  4. The XML file is generated from my test database (see: DBI::Generator::XML) so I don't have to edit it by hand
  5. I can generate test data programatically and load that at any time
  6. I can dump test data from the system without hardcoded primary key id values!
  7. If I have to edit the file by hand, I can
All sorts of benefits. :) The ability to keep on executing the script without it deleting and re-inserting all of the rows is of special benefit. It lets me progressively add more information to the rows (such as comments) which aren't needed right away. Now, without the export, there is no real benefit. Hand editing XML isn't really pleasant. Hand modifying the XML is handy, you don't have to do too much typing. :)

No comments: