Database Migration: FoxPro to MySQL

Every database knows that migration between DBMS is a complex procedure. However, migrations from FoxPro to MySQL are easy compared to other DBMS. The reason is lack of database entries enclosing sophisticated logic such as views, triggers, and stored procedures are the reasons for the sheer simplicity. Technically, FoxPro format has been developed to store small and medium volumes of relatively simple data. At the same time most of database logic is encapsulated on the application layer. Therefore,process of database migration from FoxPro to MySQL must handle only data.

However, even that simple process includes a few bottlenecks specified below:

  • Data types that do not match

There are two possible values for type Logical contained in FoxPro – True, designated by ‘T’ and False, designated by ‘F’. Also, there are two possible values for MySQL which are type BOOLEAN or BOOL – 1 represents True and 0 represents False. TINYINT(1) is the most suitable MySQL data type for this kind of conversion. Initial data ‘as is’ may need to be preserved in special case. In such a situation, ENUM(‘T,’F) are best suited for type mapping.

  • Set of characters that are different

There is also a possibility that ‘encoding in the header’ details are empty or incorrect in their corresponding DBF files. This may cause incorrect conversion of text data from DBF files. The only workaround for this issue is accurate validation of the migrated data by comparing random rows from source and destination tables.

Now let us explore a few methods of database migration from FoxPro to MySQL ranged from half-automated approach requiring a lot of manual post processing to full automated migration tools. FoxPro tables stored in separate DBF files are exported into comma separated values format and then imported to the target MySQL database. One of the most straight forward tools for conversion of FoxPro data is dbf2csv. Next step of the migration is implemented through ‘LOAD DATA INFILE’ MySQL statement that is capable of loading CSV file contents into previously created table. Every comma separate values file must be in the dedicated data folder to succeed loading into MySQL for security reasons.

Some methods of FoxPro to MySQL migration requires manual post-processing because two challenges specified above cannot be fixed by those approaches.For example, script dbf2sql.php can be used to convert FoxPro data files into SQL scripts. This method does not require CSV files as intermediate step. Following this way,tables can be built and filled with data in the process. But the drawbacks are:

  • No chance to customize and control FoxPro data type mapping
  • Lack of specific user-defined encoding
  • Missing diagnostic of possible migration issues

In order to automate every step of the migration process,Intelligent Converters produced FoxPro-to-MySQL conversion tool. From encoding process to processing of logical values, this tool has many functionalities. FoxPro-to-MySQL converter helps to avoid manual efforts and intermediate steps by automating the entire database migration with just a few clicks.

In addition, it can migrate FoxPro data into a local MySQL script file as option. That script accepts data in form of SQL statements that generate selected tables and filled with the data. This option can be a lifesaver when remote connections are not accepted by target MySQL.