June 4 2009 
FMPro Webinar Published, Valentina Conversion Part 3
View the first in our RevSelect Webinar series, and continue converting your Filemaker database to Valentina in part 3 of this series.
Our first RevSelect Webinar last Tuesday was a great success. David Simpson talked us through the conversion process from Filemaker to Revolution using FMPro Migrator, and demonstrated how you can save literally hundreds of hours of time by using this software. If you missed it, you can download the recording of the webinar here. One of the most notable achievements of this really extraordinary software, is the ability to convert your whole Filemaker layout and bring it intact into Revolution with just a few clicks and a couple of seconds of compilation time - imagine how long it would take you to recreate all your layouts... It will even pick up all of your scripts, copy them into a neat text file for you, and automate the conversion on any readily converted scripts. If you have a need to convert your Filemaker database into any one of a long list of other formats (not just Revolution but also MySQL, Oracle, Access, SQL Server, Sybase, DB2, OpenBase, PostgreSQL and Valentina), you really can't afford to be without this tool. In a single use it will more than pay for itself. To help you get started, we're offering a special 20% off package during June, on Studio and FMPro Migrator purchased together. To get this discount, use the coupon code FMSTUDJUNE.
We will be continuing this series of RevSelect Webinars, the next date for your diary is July 7th, and we'll be looking at chartsEngine from Derbrill. Sign up for this Webinar here. You can also keep up to date, sign up and review completed Webinars by checking our website here.
If you have been following our series on converting from Filemaker to Valentina using FMPro Migrator, here is part three in the series, Migrating Relationships. Part One can be read here, and Part Two, here.
Migrating Relationships - Valentina
FmPro Migrator reads FileMaker Pro relationship info from DDR XML files exported by FileMaker Pro Developer/Advanced 7+. This relationship info is then used to create SQL code to re-create the relationships within the Valentina database.
Migrating Relationships - Import DDR XML File
Within FmPro Migrator:
(1) click on the Relationships tab, then
(2) click on the Import Relationships button.
Note: Relationships will only be imported correctly if all of the base tables have already been created within the Tables tab.
Migrating Relationships - Import DDR XML File - Select File
(1) Select the exported DDR XML file, then
(2) click the Open button.
Don't select the Summary.xml file.
Migrating Relationships - Generate Relationship SQL Code
Select one or more relationships from the list, click on the Generate Relationship SQL button.
If the relationship cannot be generated from the FileMaker Pro relationship, error text will be displayed within the Relationship SQL Code field. Otherwise, the generated SQL code will be displayed in the Relationship SQL Code field.
The most common reasons relationship code won't be generated are:
1) Having a missing primary key in the table.
2) The TO name specified in the relationship does not represent a base table. Relationship SQL code can only be generated for base tables, since these are the only tables which will get created in the Valentina database.
Any base tables which don't contain a primary key, will be listed in a error dialog when the Generate Relationship SQL button is clicked. This is just an informative dialog to let you know that relationships won't be generated for the tables which don't have a primary key. Relationship SQL code will still be generated for all of the remaining tables.
For Valentina databases, FileMaker Pro relationships are converted into Valentina ObjectPtr links. The SQL code to create each ObjectPtr link is generated automatically by clicking on the Generate Relationship SQL button. Valentina ObjectPtr links can be significantly faster than using standard foreign key relationships, require less disk space for storage, and often result in simpler SQL Join syntax.
FK-Links vs ObjectPtr Links
Valentina includes both Foreign Key Links and high performance ObjectPtr Links. FmPro Migrator includes a pop-up menu below the Create SQL for Relationships button enabling the Valentina developer to choose between these two methods. By default, all relationships are created as FK Links.
After reading about the ObjectPtr Links feature within the Valentina documentation you can then decide whether to make changes to your code and schema in order to utilize the ObjectPtr Links feature.
Migrating Relationships - Generated SQL Code - FK Links
Here is an example of relationship SQL code generated by FmPro Migrator to implement relationships using FK Links:
ALTER TABLE Invoice DROP CONSTRAINT fk_Invoice_Client_ID
ALTER TABLE Invoice MODIFY COLUMN Client_ID ULONG
ALTER TABLE Invoice ADD CONSTRAINT fk_Invoice_Client_ID FOREIGN KEY (Client_ID) REFERENCES Client(Client_ID)
1) The first ALTER TABLE statement drops a previously created foreign key constraint, in case the code gets run more than once.
2) A foreign key constraint is added to the child table in the relationship (or in FileMaker Pro terms, the Right Table of the relationship).
Migrating Relationships - Generated SQL Code - ObjectPtr Links
Here is an example of relationship SQL code generated by FmPro Migrator to implement relationships using Valentina ObjectPtrs:
ALTER TABLE Invoice DROP CONSTRAINT fk_Invoice_Client_ID
ALTER TABLE Invoice MODIFY COLUMN Client_ID ULONG
ALTER TABLE Invoice ADD CONSTRAINT fk_Invoice_Client_ID FOREIGN KEY (Client_ID) REFERENCES Client(Client_ID) ON DELETE CASCADE
ALTER TABLE Invoice ADD COLUMN LNK_InvoiceToClient_ptr ObjectPtr CONSTRAINT LNK_InvoiceToClient REFERENCES Client ON DELETE CASCADE INDEXED
COPY LINKS FROM fk_Invoice_Client_ID TO LNK_InvoiceToClient
ALTER TABLE Invoice DROP CONSTRAINT fk_Invoice_Client_ID
--ALTER TABLE Invoice DROP COLUMN Client_ID
--ALTER TABLE Client DROP COLUMN Client_ID
1) The first ALTER TABLE statement drops a previously created foreign key constraint, in case the code gets run more than once.
2) A foreign key constraint is added to the child table in the relationship (or in FileMaker Pro terms, the Right Table of the relationship).
This foreign key is only added for the purpose of later converting the foreign key constraint into a Valentina LINK with the COPY LINKS command.
3) An ObjectPtr column named LNK_InvoiceToClient is added to the Invoice table.
4) The COPY LINKS command converts all of the records in the existing foreign key relationship to use the new ObjectPtr link.
5) The FK contraint is dropped since it is no longer needed.
The remaining commented code drops the previously created Primary Key and Foreign Key columns from the tables. These additional drop column statements are left commented in order to give the Valentina developer the opportunity to manually drop these columns later.
It is important to note that some SQL changes will need to be made in your record insertion/updating code in order to update the ObjectPtr link values instead of the foreign key column. You would read the RecID column and put its value into the ObjectPtr field, instead of using the previously created ULONG Primary Key field for the parent table. RecID values are used to improve performance and conserve disk space compared to creating ULONG columns for Primary Keys.
Migrating Relationships - Create Relationships in Valentina Database
Click the Transfer Relationships button to execute the relationship creation SQL code and create the selected relationships in the Valentina database.
Troubleshooting the Relationship Creation Process
1) One reason for the failure of a relationship to be created in the Valentina database, is having mismatched column types when creating the foreign key relationships. FmPro Migrator attempts to prevent these types of problems with Valentina relationships by changing the Foreign Key column into a ULONG column type in order to match the column types assigned to Primary Key columns.
With the Valentina relationship creation code, the creation of a foreign key relationship has to succeed first, before the ObjectPtr relationship can be converted with the COPY LINK command.
2) It is recommended that the Valentina database file not be opened by FmPro Migrator and Valentina Studio at the same time. Valentina employs a file locling feature to insure that two applications don't write to the same file at the same time. Sometimes, this locking mechanism can prevent Valentina Studio from opening the file until the other applicatioin has been closed, even if the other application is not currently writing to the database file.
Generally, it is best to close FmPro Migrator before attempting to open the file with Valentina Studio. This way you can insure that Valentina's file locking feature recognizes that the file has been completely closed and released by FmPro Migrator prior to Valentina Studio's attempt to open the file. If this occurs, Valentina Studio will display the error message "Database could not be opened!" when attempting to open the file.
If the Valentina database file has been opened in Valentina Studio prior to working with the file within FmPro Migrator, database tasks will simply not complete since the file is locked. This can be seen while attempting to drop and create a table. No error message is returned by the Revolution revDB statements to indicate that the file is locked, but the requested task will not be completed and the "In Progress" status will be displayed below the button.
3) When clicking the Transfer SQL Relationhip button for a single relationship, an incorrect error message will be displayed stating that the relationship already exists within the Valentina database. This error can be ignored because the relationship actually has been created within the Valentina database, and can be verified with Valentina Studio. Pasting the relationship creation SQL code into Valentina Studio does not cause this error to be displayed.
In the next edition we will conclude this series with a look at some remaining manual tasks.
 |
|
