Martin's MYSQL Blog

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: MYSQL, Table Structures, VFP

This entry was posted on Friday, February 12th, 2010 at 3:33 pm and is filed under Transferring Table Structures, VFP to MYSQL Migrations. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.