after months of work and a whole month behind the schedule, we finally shipped earlier today the third version of profilsmart. it is a true client/server application with a mysql ( or ms sql server 2005) as backend database. the whole app is written in vfp utilizing a 2 1/2 tier framework (more on this in a later post).
this newest version has no differences in the ui or any more features than the previous one but i tried to rewrite thousands of queries against local dbf files, as spt calls against a distant database server. that was a dramatic change in the existing application and as i didn't know anything about mysql my first thoughts were to migrate my database only in the sql server. visiting many clients at their site very soon i discovered that an unbelievable high percentage of their pcs do not meet the ms requirements for a sql server 2005 express installation. (i saw many pcs running windows xp without sp2!) as i have written again in the past the majority of our customers do not know about service packs, windows update and etc... so it is really tough for us to convince them that they have to pay a technician for maintaining the pcs in a healthy state. the mysql database was a good alternative choice for a backend as it can be installed very easily in older machines too.
having by side the draft chapters of the mysql client-server applications with visual foxpro, the whole process was a great experience for me as i had to rewrite thousands lines of code, i had to modify many parts of the existing framework and of course i had to solve many new problems that they didn't exist before. my best moment was when i had to populate my listview-like controls with photograhs stored in the database. in the previous versions of profilsmart the photographs were pure files lying on the disk and in the dbf was stored only the paths. because i didn't want to have all these photographs a so easy copysource for everybody, i decided to put them in the database. in a typical installation there about 10,000 elements of alouminium with photographs. at first i created a blob field (medium blob in the mysql) in the same table with other characteristics of these parts. very soon i realised that transfering 10 - 20 records over the lan was a considerable pain ( seconds are hours* ) for the user. so i broke the table and i saved the photos in a second table of the database but even then, the amount of data over the wire was huge. finally i remembered andy kramek saying that you can combine a remote database with dbfs! so my app transfers only once all the photos from the database in local dbfs in blob fields for every terminal and now all the photographs appear instantly! - great tip!
i plan to give you a long serie of posts with some technical details about the making of the profilsmart in the near future. i hope to be helpful for the community to know the hows and whys of the making of a real application. please feel free to send me your thougths or comments about this, in order to help me prepare something more helpful.
* joel's spolsky book - user interface for programmers - highly recommended!