Around 1996 I got tired of using VI, BBEdit Lite, and Claris HomePage to create every page on my burgeoning web site. Every time I made a change to the style of the site, I had to go through each page and change the HTML surrounding the content (back then, we didn’t even have CSS).

At the time, FileMaker Pro was a great choice for databases on the Mac, and there wasn’t much of anything else. So I worked up a FileMaker database of my web site that allowed me to more easily relate folders to their contents, and to centralize common elements. AppleScripts made it easy to upload a page, a branch of the site, or all changed pages from within FileMaker to the web site via Anarchy.

That solution served me fairly well, but as time went on the important features to me in FileMaker stagnated while other more automatable and more standards-based options grew. Around the turn of the century I pretty much stopped updating the FileMaker section of the web site because it was comparatively more difficult to work with than the MySQL/PHP section of the web site that I’m writing this in.

Because of that, I haven’t upgraded my FileMaker past version 6. It is starting to show some cracks. The time has come to start migrating all of my FileMaker data before my FileMaker stops working, whether on some future OS or on an Intel Mac.

Most of that data, I’m moving into Django. It provides an easy-to-use interface to MySQL and SQLite; SQLite is a great choice for databases when I want the data in a single easily-managed file.

The goal is to be able to easily reposition my data; I want to be able to use it in PHP, to correlate it with other on-line data, to use it with any other scripting language I might need or any other web templating API. I want to be able to have scripts automatically update my data whether I'm logged in or not. FileMaker remains the best layout creator for printing, but I don’t print as often as I used to. Once the web and other Internet technologies come into play, FileMaker can be harder to use than ostensibly more difficult databases such as MySQL or SQLite.

A sample import script

Transferring from FileMaker (or some other GUI app) into SQLite or MySQL and Django, can be made much easier with appscript.

This is a simplified version of the import script that I used for importing web pages from FileMaker. This doesn’t import all of the fields in the FileMaker database, but from an example standpoint a lot of it was redundant. This is a working script, however.

This script loops through each record and imports the record into the Page model. It looks for the appropriate records from the Site and Template model and attaches them to the page. Each record (except one main home page) also has a parent record. It stores the FileMaker ID of the parent record for later, because the parent record might not have been created yet. After all records have been created, the script loops through each page and attaches its parent.

Some fields contained special codes that were replaced with common data. The text <<HELP DESK>>, for example would on publishing be replaced with contact info for help. In Django I have a library object that contains these common elements. So, fields that might contain those special codes are run through a function, fixEncodings, that replaces one with the other.

Some Django thoughts

1. Use Django’s development server in a separate (terminal) window. It will reload source files such as models.py and views.py as soon as you save them. This lets you easily make changes to your models and views during the conversion process.
   1. manage.py runserver localhost:8000
   2. http://localhost:8000/admin/
2. If you’re using the development server, you can “print” in models.py and views.py, and the printed text will appear in the server’s output.
3. Break the conversion into steps, and organize the steps into pre-conversion and post-conversion steps. Things that involve you testing the new database structure and the conversion process are pre-conversion steps. Steps that involve you modifying the data in the new database, and which you would have to manually re-enter every time you re-import, are post-conversion tasks. Keep your pre-conversion list in your import script; keep your post-conversion list in views.py.
4. While in the pre-conversion stage, continue editing all data in the old source, and re-import regularly.
5. If you have a field for keeping track of when a record was added and when it was last updated (which I strongly recommend), disable the auto_now_add and auto_now options while you’re in the pre-conversion stage. Otherwise, the Django API will overwrite what you put there from the old database with the current time.
6. As you edit your import script, you may notice that some things belong in separate tables. Now is a great time to make those changes.
7. Use Django’s admin filters to track down strange categorization data: add the fields you are currently worried about to list_filter, and then track down what categories you’re using and how many records use them.
8. You can construct Django admin links in models.py. Create a method for your model that returns a string, and put that method in list_display. You can use this, for example, to create your own navigational structure in the Django admin by linking to related records. For example, this will create a link that displays only the children of the current record:

   [toggle code]

   • def childlink(self):
     • childlist = len(self.children(keepUnDisplayed=True, keepHolder=False))
     • if childlist > 0:
       • if childlist == 1:
         • text = "1 child"
       • else:
         • text = str(childlist) + " children"
       • link = '?o=2&parent__id__exact=' + str(self.id)
       • text = '<a href="' + link + '">' + text + '</a>'
       • return text
     • else:
       • return "None"
   • childlink.short_description="Children"
   • childlink.allow_tags = True

Some FileMaker/AppleScript thoughts

1. Select your data in AppleScript, and then use appscript AppleScript translator to convert your AppleScript to Python.
2. In FileMaker, make a simple vertical layout with just the data you need.
3. Use calculation fields or the equivalent to modify data in the old source (such as FileMaker) if that’s easier than modifying it in Python.
4. The web plug-in in FileMaker can convert special characters to their HTML-encoded equivalents.