Filemaker database remediation project

CSCF Master ST item

ST#91265

Project Charter

Introduction

"Filemaker" is a commercial desktop database application development platform that is used in a few distinct contexts within the School:

1. as an data-management application in the SCS Grad Office, to manage data and information about graduate students
2. as an information retrieval tool in the SCS Grad office, to access data managed by the Faculty Grad office (also in a Filemaker application)
3. as a curriculum component in CS courses, as managed by the SCS ISG.

This project is concerned with the first two contexts.

Motivation

Filemaker was developed as a "desktop" data-management tool. It stores data, but does so in a proprietary manner and is not generally aligned with standard SQL DBMS management practices. The data stored by our Grad Office is mission-critical. As such, it behooves CSCF to employ the same standard of care for that data as it does for other mission-critical data.

And therein lies the problem. CSCF has no expertise with Filemaker (the last person with direct knowledge retired in 2011). ISG has expertise with the front-end (application) development aspect of Filemaker, but not with the data-storage server component. Our Filemaker server is running on six-year-old hardware and using an unsupported (or soon-to-be unsupported) operating system. And finally, the Filemaker software version currently in use (for both the desktop and server components) is three major versions out-of-date and no longer available for purchase (for any new users of the application) or for support.

This stiuation represents significant IT risk and thus, this project aims to reduce that risk.

Project goals

1. eliminate risk dues to obsolence
2. prepare for further application development based on standard web application paradigms and not create additional dependency on the proprietary Filemaker product

Project objectives

The project goals translate into three distinct objectives:

1. replace the obsolete proprietary Filermaker server back-end component with a standard DBMS such as MySQL or Postgres.
2. upgrade the end-user community to the current version of the Filemaker desktop software
3. provide documentation pertaining to the data model used in the Grad Office application to enable future web-application development using the data currently available only via Filemaker

There may be technological reasons to sequence these objectives. This will become apparent during the investigation phase of the project.

Project sponsor

Bill Ince, Associate Director CSCF

Project leader

Trevor Grove, CSCF

Project advisory group

Barb Daly, ISG; Margaret Towell, CS Grad Office (or delegate); representative from MFCF and/or Math Grad Office; CSCF application-development staff

Risks, assumptions and mitigating factors

The primary goals of the project are to mitigate risk (as noted above). The principal risk is to not undertake the project. There are some project implementation details that represent risks associated with the technical implementation of the project:

1. There is risk that working on the "live production" Filemaker server may impact service.
2. There is a perceived risk that working with a Filemaker 12 client on the server-based Filemaker 9 application automatically converts the data format "forward" making it unusable by Filemaker 9 clients.

Milestones

Specific child descendants of the master ST item will contain a transactional summary of action items.

The background study has lead to two distinct phases of the project and tasks within those phases. I recommend that we complete phase one even if phase two is delayed.

1. conversion to FM12 maintaining the current FM12 server architecture
   1. install FM12 on grad-office workstations by the end of March 2014 ST#91256
   2. set up FM12 server in full test-production mode ST#92005 already completed, needs further refinement
   3. move to fm12 production by the end of May 2014 (avoiding end of W2014 and start of S2014 which are busy times in the Grad Office)

The goal of system documentation has been achieved inasmuch as it is possible -- there are many business rules embedded in the code of the application independent of the data schema. See ST#91258.

In March 2014 it was decided to abandon the following milestone:

1. separation of the FM12 application data from the application GUI
   1. refine process for converting the FM12 schema and extracting the application data ST#91545
   2. acquire the FM12 server component that handles external data
   3. re-implement the FM12 application to use external data
   4. test-production and deployment

The result is that we will accept the risks associated with using the proprietary Filemaker platform, albeit no longer the obsolete version. Going forward, investigation will focus on:

• using the Filemaker server as an ODBC platform
• setting up a postgres-to-ODBC wrapper

Over time, it may be possible to move just the data that others wish to use (eg room assignment and supervisor data) to an external database and then redevelop just those isolated. New milestone details will emerge as planning proceeds.

Progress/status reports

2013-10-18

Initial investigations of the Filemaker server are not encouraging. The technical brief for the "ESS" product module (which is apparently the needed add-on to the basic product) says:

ESS Design Goals: What It Isn't

The emphasis with ESS should be on integration. The ESS feature set is not intended to allow FileMaker Pro to act as a "front end" to SQL data sources.

(from: https://cs.uwaterloo.ca/cscf/internal/edocs/rtattach/91257/techbrief_intro_ess.pdf). The basic framework for external data sources appears to be:

• Filemaker acts as a "federated database" agent and integrates data contained in a collection of heterogeneous DBMSes. The only supported DBMS of interest to us is MySQL 5.1 community edition.
• The Filemaker application defines the data source as usual for Filemaker, but marks is as external and connects it to a system DSN (data source name).
• The development paradigm within Filemaker is unchanged -- there is no direct access to the underlying DBMS that is attached to an external DSN.
• In essence, Filemaker creates a Filemaker-standard "shadow" representation of the external data. Applications are then written in terms of the Filemaker entity.

Using this framework, we could convert all of the data to "external" data and change/reimplement the Filemaker code to be built upon entirely external tables. The vendor claims that use of external data is mostly the same as native data -- but the degree to which this is true will dictate the effort in converting the existing application from native to external data. In a perfect world, one would start the application as completely external, thus preventing dependencies on the native data. There's really no way to predict the probability of success without getting the application and trying to do the conversion.

An implication here is that we will have to continue to support a Filemaker server to hold the "shadow" entities. database abstraction. In principle we could move the applications back to the desktop, but that seems retrograde. See the technical details of the discussion in ST#91292 (at "Tue, Oct 22 2013 23:10").

To-do items

1. establish contact with Barb Daly to start the broader planning initiative -- in progress (ST#91354) -- done, see below
2. create a new email alias for vendor contacts -- done 2013-10-21 (ST#91321)
3. find a version 9 Windows version of Filemaker for my desktop-- in progress (ST#91371) -- done

2013-11-1

Activities this week included:

• getting FM9 installed and running on my development VM. This entailed some running around finding licence keys that worked. Once the software was working, I grabbed a copy of the FM9 application (GradDB.fp7) from danae.cs.uwaterloo.ca so that I could start playing with it.
• I met with Barb daly to get her perspective on Filemaker and its use within the School (see meeting summary below).

To do

1. re-teach myself how Filemaker works and re-familiarize myself with the development and application paradigm
2. play around with the GradDB.fp7 application
3. start organizing an FM12 server -- it appears that whatever we do, we'll need one

2013-11-27

Activities since the last update:

• acquired and installed (in my development VM) the demo Filermaker conversion software -- unable to get it to do anything. It appears that it wants ODBC connectivity to the FM application, which we don't provide. FM Pro 9 claims to be able to offer ODBC services, but we don't have the FM ODBC driver package. Initial investigation shows that we may need FM Pro Advanced or even the Server Advanced product.
• acquired and installed MySQL 5.6 communicy edition. Installed in my development VM.
• initiated contact with M. Towell in the Grad Office to learn more about the GradDB application

To do items

1. more work on the converter. Might advance the FM12 server installation and use that as the conversion source (needs investigation)
2. meet with Margaret Towell and her team to learn about the GradDB application -- ST#91866

2014-2-28

Activities since the last update:

• spent much time working with the Tech Support for the conversion software. Made some progress, as documented in ST#91545:
  • it doesn't work with a server-based FM file, must use local file
  • needs filemaker metadata via XML export to clipboard; fm12 pro advanced required
  • once metadata is acquired ODBC is used to read the contents from fm
  • output is directly to an online MySQL server -- stores passwords in plaintext
  • repeating fields, value list are all problematic & don't seem to convert * discussion with Margaret Towell ST#91866
  • rough plan evolved:
    • end of March 2014: install FM12 on workstations ST#91256
    • set up fm12 server for testing
    • convert grad office to fm12 end of May 2014
• extracted some documentation on the FM12 application
  • see documents attached to ST#91258

To Do items

1. work on deploying FM12 to grad office workstations ST#91256

2014-3-17

Since last update:

• work on FM12 deployment continues ST#91256

To do items

• new detailed planning for:
  • hardening the FM12 server, including backups
  • confirming the ODBC server component of the FM12 server
  • setting up the postgres-to-odbc wrapper ST#93292

Meetings

Barb Daly, 2013-10-26

I met with Barb to get an understanding of her involvement with Filemaker applications that are in use within the School and to glean whatever technical background I could. I explained the three objectives that I had.

She revealed, confirmed and/or reiterated several points:

• she does not actively manage the Grad office application but is aware of its existence. She generated a FM schema diagram of the application: https://cs.uwaterloo.ca/cscf/internal/edocs/rtattach/91354/Define%20Database%20for%20%e2%80%9cAdmissions%e2%80%9d.pdf
• upgrading to FM12 has a couple of components that must occur simultaneously
  • all desktop clients must be upgraded
  • the server must be upgraded
• Barb is not familiar with the "ESS" add-on that we are contemplating. The "external data" interface that was developed by JCB years ago was done to implement an interface to a web-generating product (ie a third-party application that knew how to read FM data and generate websites). It is no longer in use and does not seem particularly relevant to the mission at hand.
• Barb advised on a technique for working with the database, particularly when I get to the conversion to FM12: get a local copy of the database by copying it from the server.
• Barb develops and manages a filemaker application (was FM9, now FM12) for ISG. It is outside the scope of my project.
• A bunch of FM12 licences were purchased recently. One of these is reserved as the FM12 server for the Grad office application.
• Barb was not aware of Jeannie B's use of FM, nor was she aware of the applications in the Faculty Grad office,.

We discussed the concept of the ESS data. She strongly agreed that we should continue to use a FM server and have the references to external data housed there.

Barb has had experience installing the FM12 server (on a Mac). She reports that it was unpleasant, unpredictable and error prone. She got it working eventually but isn't sure exactly what the problem was or how it was resolved.

We discussed the issue of whether the new FM12 server should be run on a Windows or Mac server. Technically they appear to be equivalent and the Windows FM9 server has worked well. She noted that the ISG FM12 server is a Mac Mini. I questioned whether that would be suitable for a "production" environment and we agreed that it might not. So if we are looking at a "production" environment, a dedicated Mac server is likely not cost-effective. A Windows server (or VM) is likely the way to go.

Margaret Towell, 2014-2-28

See ST#91866 for details.

Subject: Evolution of the "GradDB" filemaker application.
Date: Fri, 28 Feb 2014 15:38:46 -0500
From: Trevor Grove <trevor.grove@uwaterloo.ca>
To: Margaret Towell <mtowell@connect.uwaterloo.ca>
CC: Barbara Zister Daly <bmzister@uwaterloo.ca>, rt-em-gw+91866@cs.uwaterloo.ca

Margaret:

This is to follow up on our conversation earlier today.

I've talked to you and your staff in order to get a better understanding of how you use the Filemaker 9 GradDB application.

The short-term goal is to convert your application to Filemaker 12 and to have your staff switch to it. Longer term, we will be looking at converting the "behind the scenes" data storage to a standard SQL database technology. That is optional and "if possible". For now, the focus is on getting you to the current version of Filemaker so that we can get vendor support (and purchase software if needed).

To this end, we discussed the following plan:

1. end of March or thereabouts: CSCF to install Filemaker 12 on all the workstations in the Grad office. This will not impact your use of the current Filemaker 9 version of the application.

2. CSCF will continue to work on the Filemaker 12 server and making a version of the GradDB application available for testing with Filemaker 12. The contents of this test version will be stale (from Fall 2013) and changes you make during testing will be discarded. Your staff must continue to use the Filemaker 9 version for "live" changes.

3. If testing proceeds OK, we will look at switching from Filemaker 9 to Filemaker 12 some time towards the end of May (after your beginning of term busy period). This switchover will require some downtime (probably half a day) -- we'll work out the details as the time gets closer.

As far as the next steps (after Filemaker 12) go, I understand your concern with privacy and the potential concerns of the GSO. The central database server we would propose to use would be at least as secure as your current application, and possible more-so. If and when we get to this point, we'll bring a GSO representative into the picture to ensure that they are satisfied with the proposed system.

I will co-ordinate all changes and planning with Barb ZD (cc'ed), who is the resident expert on Filemaker.

Please do not hesitate to contact me with questions or concerns.

-trg