Transferring VFP Table Structures to MYSQL

So now that we have established some decent contact between VFP and MYSQL it is time to transfer as much as possible from the VFP Database into the MYSQL Schema. Being a long time believer that making a plan can keep you out of trouble I laid out the following steps for that task

  • Transfer Table Structures
  • Set up Indexes
  • Set up foreign keys

So lets look at each step more closely

Transfer Table Structures

Granted we could sit down and take a printout of all the structures and then use Workbench or something like it and start setting up the tables. But being faced with having to do that with close to 100 tables some of which having 40 or so fields this appeared like a daunting task. So I took a look to see how much could be optimized. Tables In MYSQL are created by using the CREATE TABLE ….. syntax which is pretty straight forward. Granted there are some VFP features that MYSQL does not have and some that are available in MYSQL and lacking in VFP but at least for the overlapping part one could quickly write some VFP code as follows:

FUNCTION CreateTable

PARAMETERS lc_sourcetable, lc_desttable

USE (lc_sourcetable) AGAIN ALIAS a_conv

ln_c = AFIELDS(la_f)

lc_sql = “DROP TABLE IF EXISTS `”+lc_desttable + “`”

IF !MYSQLEXEC(lc_sql, .f., “Cannot drop table”)

    RETURN .f.

ENDIF

lc_sql = “CREATE TABLE `”+lc_desttable + “` (“

FOR ln = 1 TO ln_c

    IF ln > 1

       lc_sql = lc_sql + “, “

    ENDIF

 

    lc_sql = lc_sql + “`” + la_f(ln, 1) + “` ”

 

    DO CASE

    CASE la_f(ln, 2) = “C”

        lc_sql = lc_sql + “VARCHAR(” + ALLTRIM(STR(la_f(ln, 3), 10, 0)) + “)”

    CASE la_f(ln, 2) = “D”

        lc_sql = lc_sql + “DATE”

    CASE la_f(ln, 2) = “T”

        lc_sql = lc_sql + “DATETIME”

     CASE la_f(ln, 2) = “M”

         lc_sql = lc_sql + “TEXT”

     CASE la_f(ln, 2) = “L”

         lc_sql = lc_sql + “BIT”

     CASE la_f(ln, 2) = “N”

         lc_sql = lc_sql + “DECIMAL(” + + ALLTRIM(STR(la_f(ln, 3), 10, 0)) + ;

         ”, “ + ALLTRIM(STR(la_f(ln, 4), 10, 0)) + “)”

     CASE la_f(ln, 2) = “I”

         lc_Sql = lc_sql + “INTEGER”

     CASE la_f(ln, 2) = “G”

         lc_sql = lc_sql + “MEDIUMBLOB”

    ENDCASE

 

    IF !la_f(ln, 5)

        lc_sql = lc_sql + ” NULL”

    ELSE

        lc_sql = lc_sql + ” NOT NULL”

    ENDIF

 

    IF !EMPTY(la_f(ln, 9))

        lc_sql = lc_sql + ” DEFAULT ‘” + la_f(ln, 9) + “‘”

    ENDIF

NEXT

lc_sql = lc_sql + “) ENGINE = INNODB”

IF !MYSQLEXEC(lc_sql, .f., “Cannot create table”)

    RETURN .f.

ENDIF

RETURN .t.

This Function simple creates a CREATE TABLE statement for the VFP table <lc_sourcetable> sent to it an creates a mysql table named <lc_desttable> if it is successful then it returns .t. otherwise .f. The MYSQLEXEC() function is a udf I wrote that handles all the MYSQL traffic including setting up connections and error logging/reporting but could have been accomplished with a combination of SQLCONNECT() and SQLEXEC() functions. We are using the AFIELDS() VFP function to get an array of all the fields in the TABLE and the according properties.

Now all one needs to do is some code like

OPEN DATABASE myvfpdb

ln_tcnt = ADBOBJECTS(la_t, “TABLE”) && get a list of all the tables

FOR x = 1 TO ln_tcnt && loop through all the tables

    WAIT “Creating ” + la_t(x) WINDOW NOWAIT

    IF !createtable(“<pathtodatadir>\” + la_t(x), la_t(x))

        *what ever error handling you want to do

    ENDIF

NEXT

So now that we got the structures across we can get to

Transfer Indexes and Foreign Keys

Unfortunatly in my case I did not come up with a way that was quicker by writing some code than to do this manually. VFP allows too many things in Indexes to drastically reduce the number that could be transferred by a program. Simpe VFP things like using UDF’s or VFP functions in indexes and also FOR expressions (filters) on indexes. A quick look at the documentation showed that less than 20 % of my indexes could be transferred programatically. This also means that quite some code has to be changed so transferring the same Indexes used in VFP is not a good idea. Indexes are there to improve data retrieval so they need to be optimized to the particular engine.

VFP accomplishes referential integrity with triggers. And there again the two platforms are too different to just write a short procedure to accomplish this. And in this case MYSQL with INNODB has some features that VFP does not have. So it made more sense to do this manually and examine each case and set it up in the way most suitable for MYSQL.

Next: transferring the Data

Tags: , ,

3 Responses to “Transferring VFP Table Structures to MYSQL”

  1. Steve says:

    Nice articles, I am working on the same type of project. My VFP program has been a vfp, php & mysql project for years. I need to move most database table into Mysql bad and have my vfp program acces mysql directly – it is such a daunting taks.
    I found the following software very inexpensive and useful for copying tables and structures into mysql.

    DBSync for MSFoxpro and Mysql can be your best friend:
    http://dbconvert.com/convert-foxpro-to-mysql-sync.php

    Keep the articles coming.

  2. Elvir says:

    Good article. I like this . In meantime I did similar app in order to solve this transition. Keep posting.

  3. Rainer R. Greim says:

    Hi Martin,
    great postings thx for that.
    Im also migrating my vfp container to other backends. And I was searching and trying tools for that.
    But there is a easier way.

    Get a Omnis 5 Standard Edition for MySQL ( http://www.Tigerlogic.com)
    Get the ODBC-driver for FoxpPro
    Setup a ODBC-DSN for your Foxpro db
    Start Omnis and setup 2 sessions in the session manager
    Session 1: odbc to the vfp dsn
    Session 2: to the Mysql db via MySQL Dam see also : http://www.tigerlogic.com/tigerlogic/omnis/technotes/tnsq0012.jsp
    Then open both sessions
    Now drag the vfp tables to the MySql session and do some finetuning
    Example memo fields shout be varchar, byte a…
    Index on deleted will not run, delete…

    RRG
    http://www.greimconsulting.de/node/116

Leave a Reply