Question

I'm looking for a way to export a database file to separate TWiki topics.

Here's the background:

• Someone gave me a file listing approximately 300 group decisions that they wanted to have on-line for easy referencing. One of the main desires was to be able to quickly find all decisions related to a particular policy area. They identified approximately 45 different policy areas.
• With some effort, I reformatted it in an Excel spread sheet with 4 fields per record: date, meeting type, policy areas, summary of decision.
• My first thought was simply to post it as a table in TWiki. The problem is that the "policy areas" may have several entries per recond (think checkbox in a db) which are listed as multiple entries in one field separated by commas. Sorting the table by this column only works for the first policy area listed. I can't figure a way around this limitation given current TWiki table implementation.
• This led me to explore more sophisticated solutions of which I saw two main options:
  1. Create a mysql db and access it within TWiki using DatabasePlugin.
     • Pros
       • Doesn't clutter the TWiki web with a bunch of very small topics.
       • It would be fast.
       • I've kind of wanted an excuse to learn mysql and DatabasePlugin because I have thought I might have other uses for this combo.
     • Cons
       • Seems like overkill for this small application.
       • I don't know mysql and not sure I want to learn it just for this.
       • DatabasePlugin is a bit dated. I'm wondering if DatabasePlugin is still working with Cairo release.
  2. Create separate topic (with special WebForm) for each decision and then query with FormQueryPlugin.
     • Pros
       • Creates an all-TWiki solution.
       • Enables easy referencing of individual decisions within TWiki.
       • Accommodates further commenting and discussion on individual decisions within TWiki.
       • Gives me reason to explore potential of FormQueryPlugin which I think looks like a tool with tons of potential.
     • Cons
       • Creates lots of very small topics which would not be used that often. Side note: I'm considering putting them into separate web of their own.
       • Here's the big one and the reason for this help request: how the heck do I export the individual db records into separate TWiki topics, short of manually doing each one (groan!).

I'm leaning towards the second approach but am stopped by the challenge of getting the db records into separate TWiki topics. I searched the web quite a bit for possible Mac, Window or perl solutions and haven't found one. Of course, there's a bezillion solutions for exporting from one db file format to another. However, I've not found any that will take a single db file and export each record into separate formatted text files. If anyone knows of such a program, I'd appreciate your pointing me to it.

It occurs to me that this could be a tool that would be very useful for anyone seeking to migrate data into TWiki. It also seems to me that it would be fairly trivial to implement in perl (perhaps I'm naive of this count). Consequently, I'll add a few more ideas about how such a script might work, should someone be so inspire to take it on. I can imagine a perl script that accepts the following parameters 1) the db file(s), 2) a special twiki template topic in which the placement of the db fields are defined, and 3) the directory (a TWiki web) into which the output files will go.

Thanks in advance for any suggestions or help!

Environment

-- LynnwoodBrown - 12 Oct 2004

Answer

On exporting individual DB records into separate TWiki topics: This can be done in two steps: (1) Load a DB table into memory, (2) create topic for each table row.

On (1), use some demo code for Perl's DBI module, or try to use the DatabasePlugin.

On (2), for each record, build the topic text in memory, then save it using TWikiFuncModule's TWiki::Func::saveTopicText() function. TWiki does not yet have an API to build the form data, but you can simply add the raw meta format, e.g.

$text = '---+ Decision Record' . "\n";
$text .= $description . "\n";
$text .= '%META:FORM{name="WebForm"}%' . "\n\n";
$text .= '%META:FIELD{name="Decision" title="Decision" value="' . $decision . '"}%' . "\n";

Not sure, one of the existing Wiki-to-TWiki converters might do something similar already, check DolphinToTWikiAddOn, GenericWikiToTWikiAddOn, JSPwikiToTWikiAddOn.

-- PeterThoeny - 13 Oct 2004