The process of migrating FoxPro databases to MySQL server is rather straight forward, compared to migration between another DBMS. The reason of this relative simplicity is that FoxPro does not have such complex database objects as stored procedures, triggers and views. In other words, FoxPro databases are just used as storages while all data handling logic is enclosed in the corresponding application(s). Due to this particularity of the source database, FoxPro to MySQL database migration involves only schemas, data and indexes.
However, even migration of FoxPro data to MySQL server may be a difficult task. The main issues one may encounter include:
- Unmatched data types. FoxPro has type Logical having two possible values: True (stored as symbol ‘T’) or False (stored as symbol ‘F’). MySQL has corresponding type BOOLEAN or BOOL that is synonyms for TINYINT(1) that also accept two possible values: 1 for True and 0 for False. According to semantic equivalent, ‘T’ must be mapped into 1 and ‘F’ must be mapped into 0. However, particular cases may require to preserve the original data “as is”. The most appropriate type mapping for such situations is ENUM(‘T’,’F’).
- Different character sets. Although DBF files store information about encoding in the header, sometimes it may be empty or incorrect. So, in order to get correct conversion it is necessary to review results after converting data with codepage mentioned in the DBF file. Is resulting text includes invalid symbols, the person responsible for database migration should select another codepage for the tool and rerun conversion.
Now it is reasonable to explore how different approaches to FoxPro to MySQL migration process the bottlenecks listed above? The most straight forward option is exporting DBF files to Comma Separate Values format and then importing resulting files into MySQL. DBF files can be converted into CSV format using free tool dbf2csv available at SourceForge.net. MySQL “LOAD DATA INFILE” statement allows to import CSV files into MySQL tables that must be previously created:
- Copy the CSV file(s) into data folder of the destination MySQL database, because MySQL will only allows you load data from a CSV file that is in the data folder (for security reasons).
- 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;
Obviously, none of two described challenges is solved by this approach, it is necessary to take certain post-processing steps in order to resolve them manually.
Script dbf2sql.php available at https://github.com/xtranophilist/dbf2sql allows to convert DBF files into SQL script that creates table and fill it with data avoiding intermediate steps like CSV file. However, it does not allow to configure mapping of FoxPro logic type and to specify user-defined encoding, and so it does not resolve possible issues of FoxPro to MySQL migration in intelligent manner.
Commercial tools like FoxPro to MySQL by Intelligent Converters allow to customize every possible parameter of the conversion process: how to process logical values, what encoding should be used and others. This feature eliminates intermediate steps during the migration process and allows to avoid manual efforts. Moreover, the program can either migrate FoxPro database to MySQL server directly or export the data into local MySQL script file containing SQL statements to create tables and fill them with data. The second option should be used if the target MySQL server does not accept remote connections.
