Visual Foxpro was a platform of choice for small and medium applications for decades. I started writing things in Foxpro when it did not belong to Microsoft yet. I believe when it was in version 2.0. In a time when there was no Windows and machines had less than 1 Mb of Ram and a 80 MB harddisk was big. Back then the concern was to keep program and data small. After all those 80 Mb on your Novell Server had to last for a few years. I had a customer back then using SBT and entering about 300 invoices/day with about 15 lines per invoice on average and back then we used about 15 MB a year to store that information…..

Fast Forward to 2010. Said customer still does those 300 invoices on average a day, SBT is long gone, but the software has grown, been updated, improved, Moved to VFP9.0. Many features added, much more information stored. Now those 300 invoices/day run up close to 2 Gb of hard disk space a year. Not a big deal with the size and price of todays Hard disks. But it introduced quite a few new problems.

1. VFP tables are limited to 2 Gb space. So if one wants to store multiple years of data one has to sooner or later make some hard decisions. Like do we archive some of the data, Do we split the table horizontally or vertically etc.
2. Performance: VFP is based on Flat files. So if the table(s) and index files are big it takes considerable amount of time to send them to the workstation over a network. The code resides usually on a server with the data. So the workstations access the code files from the server and then they access the flat data/index files. All resulting in a considerable amount of network traffic. The bigger the tables get the slower the system gets noticeably.
3. Difficulties with access over the Net. Yes one can use VFPOLE and a web server or .Net. Or pcAnywhere or Logmein. But why duplicate a screen as a web page or tie up one or multiple computers with a remote access software. Because using VFP over a VPN over the net is out. I tried it just for “fun” once and printing an invoice over a 256kb DSL connection took 4 Minutes
4. Microsoft is discontinuing VFP. Granted there is a chance of them changing their minds and there are some people around that are working on projects that will compile VFP code into .NET or other platforms. BUT…. are they going to complete it? Are they going to stay around? Are they going to address the 2GB table size restrictions? And then we are going to do a rewrite in .NET….

So especially in view of points above I made the decision to move on and find a new platform. So my first idea was to go to MS SQL. There are upsizing wizards for that included with VFP and after all I wrote quite a few things for MS SQL in the late 90’s namely the whole backend for a day trading system including real time stock analysis etc.

So then how did I get here to MYSQL?

MS SQL fell apart mostly because of what I call financial realities and how it worked out also – ease of use – at least in my case. Granted Microsoft gives the express edition away for free. But its pretty useless in the above scenario as a 4Gb Database limit is worse than a 2GB table size limit. And people like to store a lot of data…. Plus Microsoft has the tendency of forcing you to upgrade. I bought Office 2000, it worked fine for me. Now I got a Vista machine and a Windows 7 Laptop and office 2000 will not even install… So now I am using Open Office…

So I laid out a plan for the migration.

1. Move the VFP data onto a MYSQL Server
2. Split the VFP code into Client/Server portion and write the MYSQL procedures and Triggers to pretty much equal the ones in the VFP database
3. Keep VFP as a front end for now and alter the form code to access the remote MYSQL database instead of the Foxpro database. This should allow to take care of the aforementioned problems and overcome the size and performance limitations that currently exist with the use of VFP plus allow good performance in accessing the data over the Internet/VPN
4. In the future if necessary leave VFP and turn the whole application into one that runs on PHP/APACHE/MYSQL. This would allow great flexibility as to available platforms to run on and allow any device capable of “doing websites” to access the data.

Next Chapter: “First Contact” Setting up VFP and MYSQL to interact