Separation with ESS and schema migration

[William Keyte]

Right this is a biggy! Need some serious help here... or at least some pointers on where to go next.

We're a company based in the UK that specialise in developing neat slick Filemaker apps that are upgradeable. As many of you will know, that brings with it a whole bag of issues in the world of Filemaker! We're currently employing techniques that involve registering all data set tables in a table of its own and recording info like primary table layout, key field, current/next serial number etc and using that info to export/import all data from an old copy and import into a new upgraded version. It is actually very slick but the development overheads are, as you imagine, quite high! :-) ...and if you get it wrong it could be nasty!

There's no way we can continue with this in the long term so I'm looking now in more detail at the possibility of data/interface separation using MySQL back-end and continuing to develop our interfaces in Filemaker.

Have done some initial experiments and it all works pretty nicely. I've been using the 'Actual' ODBC drivers and have installed MySQL on my macBook for the moment. Will be doing the same on Windows shortly. Not had any real problems so far.

The really interesting stuff is to do with how a front-end interface written in Filemaker can be swapped out and any schema changes in the client's already existing data file can be altered (fields/tables added etc.) automatically by the Filemaker front-end interface file.

Well, last night I successfully designed a nice Filemaker layout/global fields and scripts system to create my schema change SQL commands and execute them using Filemaker's own Execute SQL function. Works a treat. I created functions for creating a new table, new field, renaming tables and fields, deleting tables and fields etc. all by filling in the parameters in the interface. It allows me to add new fields and tables right from within Filemaker as a user - unbelievably liberating!! I've further developed it so that it creates a series of SQL commands - one for each change, and saves them all as a set of records. A single 'migration' script then runs the whole batch in a loop thus 'updating' the SQL database.

Obviously there is still a lot more testing to do but I'm trying to get a feel for whether or not I'm treading new ground here or have you clever people out there done this before. Possible problems might include account management (I'd like to do this from Filemaker) using the 'grant all' command and run that through the ODBC driver but I'm getting errors on that. Basically I want to be able to send out a new interface file, run the schema migration script, and get it to work really smoothly and slickly.

My questions to you guys out there are:

1. Am I mad? - careful how you answer that one! :-)

2. Does this have mileage?

3. Can any of you shed any light on any of these issues?

Thanks for reading my ramble! Best wishes to all FM developers!

Will

[DRoberts]

Hi Will,

I'm about to embark on a similar voyage - using MS SQL Server as the back end.

Any pointers would be welcome - should I give up now?

David

[William Keyte]

Hi Will,

I'm about to embark on a similar voyage - using MS SQL Server as the back end.

Any pointers would be welcome - should I give up now?

David

Hi David

Welcome aboard on all this ESS stuff! Nice to see someone else is mucking about with this a bit. Unfortunately, for the moment at least, I've backed away from this area because it's just not quite developed enough from Filemaker's point of view. ESS is quite powerful but what I'm wanting to do is very specific. I'm wanting to use ESS (SQL/Filemaker) as a set of tools that allow me to release data separated projects and deploy them in a way that can allow elegant upgrading so that schema back-end and interface front-end can remain utterly separate. It's quite a big ask actually because it's not really specifically what Filemaker designed it for (yet??).

Most people require the ability to manipulate data itself through the ESS tools by employing proper querying etc. Actually I'm not really interested in this at the moment. What I'm interested in is more to do with the DDL commands (Data Description Language). These are a subset of SQL commands that specifically manipulate the schema (Create Field, Table etc), because when I send out a new Filemaker interface file to 'place' over the top of their existing schema, any small changes I need to make in their SQL backend like create a new field needs to be performed by the new interface file.

So my interest is more in using ESS as a series of deployment and separation tools.

The main problem I've come across is accounts. I want to be able to manage the accounts right within the data. An account is a data record. That account data, therefore needs to be picked up by the interface file and acted upon. So when a new account is created, Filemaker needs to be able to add that record AND manipulate the SQL database. This normally has to be done using the 'grant' command and you cannot send that through from Filemaker. Bang! The whole thing immediately becomes a problem.

The problem with all of this is that you're constantly working at the ceiling of Filemaker. Hitting your head on its roof all the time. Filemaker (at the moment) is not really enough of a 'developer's' tool. Its layers of programability just aren't there yet. But I think things are changing.

Let us know how you get on, David, and good luck.