Tech

Database migration: DBF to MySQL

FoxPro database to MySQL server migration process is one of the simplest and easiest. After all, both DBMSs offer smooth and reliable passage to database administrators. The simplicity of FoxPro is to applaud for this. Devoid of complex database objects such as views, triggers, and stored procedures; it is easy to maneuver. The data handling logic is enclosed in the corresponding applications while FoxPro databases are used as storages. Because of this, using MySQL database instead of FoxPro is required.

As simple as the migration process may be, there are some complexities attached to the task which includes:

  • Data types which are unmatched –

There are two possible values that can be found in FoxPro. They include True or False. Both values are stored as symbols ‘T’ or ‘F’. Also, there is a corresponding type BOOLEAN or BOOL synonyms for TINYINT(1) in MySQL. This synonym can accept possible values 1 and 0 for True and False respectively. Following semantic equivalent, ‘T’ is mapped to 1 while ‘F’ is mapped to 0. Since some cases require that the original data is preserved, it is mostly appropriate to use type mapping for situations. This type mapping is ENUM(‘T’,’F’)

  • There are different character sets –

The header of DRF files is mostly a storage site of encoding information from FoxPro format. However, these storage may be incorrect or empty. To avoid this incorrect conversion or prevent the ultimate occurrence, it is necessary that before converting data with code pages in the DBF file, the results are reviewed extensively. The database administrator must check for the incorrectness of text and run a conversion in a different code page.

  • Resolution of problems

Comma Separate Values (CSV) format is the easiest and clearest method to export DBF files that are storage sites for FoxPro tables. Afterward, they are imported into MySQL. A free tool called dbf2scv can be sued to convert database or FoxPro files into the csv format. The following step is done via MySQL “LOAD DATA INFILE” statement by:

  1. MySQL is the target database in which the csv files are copied. The reason is because MySQL only allows csv file data to be loaded if they are contained in the data folder. This is due to security reasons.
  2. Run the following statement

LOAD DATA INFILE ‘student.csv’ INTO TABLE mydatabase.student

FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘

LINES TERMINATED BY ‘\r\n’ IGNORE 1 LINES;

It is unfortunate that the aforementioned challenges are not solved in these steps. To solve them manually, it is important to employ post-processing steps.

CSV file is an intermediate step that can be mitigated. This can be done by downloading Script dbf2sql.php which permits the conversion of DBM files into SQL statements before the creation of tables which are subsequently filled with data. While the script can be downloaded, customized mapping of FoxPro logic type cannot be allowed. Also, it cannot specify user-defined encoding. This translates to the impossibility in resolving the migration problems of DBF to MySQL.

Intelligent Converters, a software company, established in 2001 has created a commercial database migration tool called FoxPro to MySQL converter. This converter has many functions such as:

  • Customization of parameters of the process of conversion
  • Processing of logical values
  • Finding usage for encoding

With these functions, the intermediate steps and extra efforts needed in the conversion process will be eliminated.

Also, migration of database from DBF to MySQL server is a core function. Asides that, it allows the conversion of the migration product into local MySQL script file which contains preexisting SQL statements. The last stage is to fill them with data. phpMyAdmin or command line client are standard tools that allow the import of MySQL script file into target database.