March 26 2009 
FileMaker to Valentina using FMPro
The first in a tutorial series showing you how to use the newly released FMPro Migrator 4.74 to move your database to Valentina.
FmPro Migrator Developer Edition 4.74 (released 3/23/2009) incorporates two new features which may be of interest to Revolution developers. A FileMaker Layout to HTML conversion feature, and a new feature which converts FileMaker Pro relationships into Valentina Foreign Key Links or ObjectPtr Links. This article series covers the conversion of FileMaker Pro databases to Valentina, including the new relationship conversion feature.
Introduction - FileMaker to Valentina 4
Why convert FileMaker Pro to Valentina?
There is a lot to like about FileMaker Pro as a product. It is relatively inexpensive on a single-user basis, it is easy to develop with, and it completely takes care of all aspects of managing data from multi-table relationships. You can drop a field for any related table onto a layout and the correct related information will always be displayed automatically. You don't even have to think about issuing SQL commands to join data together within multiple tables, it all happens for you automatically.
But there are a number of reasons why you might want to migrate a database from FileMaker Pro to a SQL database like Valentina. For one thing, you might want to prototype your SQL database schema and even perform some user interface testing by using FileMaker Pro as a rapid application development environment. Once you have finalized the database schema, you could then convert the database and relationships into the destination SQL database.
Performance
Valentina has been well known for years as a high performance database engine. There are specialized techniques used within the design of the Valentina database including the organization of the data on disk, query optimization and even replacement of foreign key relationships with Valentina ObjectPtr/Binary Links which are designed to improve performance. For instance the ObjectPtr feature can provide as much as as 4x improvement in performance by itself. I will show later in this article how FmPro Migrator Developer Edition converts FileMaker Pro relationships into these high performance ObjectPtr links.
Embedded & Server Deployment
Revolution includes built-in support for using SQLite as an embedded application database, and my products utilize SQLite in this very manner. But I have seen some situations in which a SQLite database file has grown to over 400Mb in size and started to slow down considerably. I am continuing to monitor this situation and if I see this situation occur more frequently, I will probably replace SQLite with Valentina in the future.
But if you think that your application's database may grow large from the beginning, you should probably consider using Valentina from the start. Furthermore, Valentina can not only be used as a single-user embedded database, it can also be used in a client-server configuration with desktop, web and even iPhone applications.
Deployment Cost
Due to internal performance enhancements, FileMaker Pro Server 10 now supports 999 simultaneous database connections, which is up from the previous limit of 250. If you were going to purchase FileMaker Pro client software at a cost of $300 for each of these users, the cost would add up quickly.
Single user, non-networked applications can be compiled into royalty free FileMaker Pro standalone applications by using FileMaker Pro Advanced. But if you really need a networked application, this would not be a viable deployment option. In fact, you can't even use a FileMaker Pro standalone application as an embedded database at all with development tools like Revolution, due to the fact that all network sharing is disabled. You could query a standalone solution file using AppleScript, but AppleScript would not be a high performance interface for data transfer and would be limited to being used only on the Mac OS X platform.
Valentina is available for royalty free distribution for embedded applications on multiple platforms, and for low cost networked deployment in client-server applications.
FileMaker ODBC Driver Limitations
The FileMaker Pro ODBC driver is officially limited to retrieving 170 columns of data and inserting/updating 100 columns of data in a single SQL statement. These limitations can cause issues with large tables, which are common in many of the FileMaker Pro databases. This driver also cannot write into FileMaker Pro container fields across the network.
When working with Valentina from within Revolution, you don't even have to make an ODBC connection to the database file. If you know the physical path to the Valentina .vdb file, you can just open it directly. I use this technique within FmPro Migrator when I perform database migrations, in order to streamline the process for the user. I store an empty Valentina .vdb file within a custom property, and when the user clicks the Create Table button I check for the existence of the Valentina .vdb file in the output directory and create it if it doesn't already exist. Then I connect to the Valentina database file and instantly create the table. Then when the user clicks the Transfer Data button I transfer data directly from the FileMaker Pro database into the Valentina database, once again without having to prompt the user to select the file or specify an ODBC DSN.
Migration Features Provided by FmPro Migrator Developer Edition
FmPro Migrator Developer Edition provides a number of features for migrating FileMaker Pro databases, including:
• Automated generation of Valentina table creation SQL code from the original FileMaker Pro database tables. This table creation process uses information gathered from the FileMaker Pro database regarding the maximum amount of data stored within each field, in order to create columns of the proper size.
• Transfer of all data from FileMaker Pro tables into Valentina tables, including Text, Date, Numeric and Container field data. Images can also be exported to disk for situations in which the data is no longer going to be stored within a database table.
• Migration of FileMaker Pro repeating fields data into a relational database table structure.
• Conversion of FileMaker Pro relationships into high performance Valentina ObjectPtr LINKs, and conversion of the the actual foreign key relationship data into LINKs.
• Conversion of FileMaker Pro layouts into Revolution cards, which can be used as the starting point for creating a graphical interface for the new Valentina database.
• Output of reports for each converted FileMaker Pro table, providing detailed info regarding calculation formulas used for stored and unstored calculation fields.
In the next edition, we will look at how to get schema info about the FileMaker Pro database.
 |
||
|
