FoxPro Ramblings

There are a few products out there that are used for interfacing Visual Foxpro and Web pages together to give your views dynamic content. Just a couple of these products are Web Connect, and FoxWeb. It's my opinion that both products are outdated and no longer necessary.

While products such as these do make some aspects of dynamic web content easier, they are in no way efficient, and they more often than not are a waste of your Web Server's resources. Today's blog is going to explain how to use Visual Foxpro on a Web Server, and minimize the amount of resources that are wasted.

For this blog, I will be referencing my production server Thor. Thor is a Dual Zeon 2.4ghz server with 8 gig of ram, and 8 300 gig hard drives which are mirrored in pairs giving me 1.2 gig of overall storage. Thor is housed in a data center where I have burstable bandwidth up to a full 100mbps.

First off lets talk about the four basic resources that you are working with on your web server, and how important each one is to the overall product.

Bandwidth (burstable to 100mbps):
Your bandwidth is the amount of data you can move across your network at any given time. Its often measures in Megabits per second or mbps. In cases of smaller networks, it could also be measured in Kilobits er Second or kbps.

Most commercial Internet connections will be synchronous (same speed both directions.) There are way too many options to discuss here. I the case of Thor I chose to Co-Locate at a local Data Center, and I have an allotted bandwidth, and if I should go over that amount I am billed for that addition bandwidth. What this means is that my bandwidth is calculated for an entire month, and the averaged out over that time frame, and if it exceeds the allotted amount I am billed extra.

My servers also have access to "Burstable Bandwidth" of 100mbps. What this means is that if my server what hit by an unusually large number of hits, it would not max out until reaching 100mbps.

On the average server, this is typically your bottleneck, and in most cases if you have excess bandwidth, and you are suffering poor performance, you are having problems somewhere else in the system.

Memory (8 gigs)
You cannot go wrong with more memory, the more memory your system has the better overall it will operate, just remember to make sure you are running an operating system, and software that can use all the memory you give it. For instance Windows 2003 Server Standard Edition can only access the first 4 Gig of memory. Anything over 4 gig is a waste. I personally wouldn't recommend Standard Edition for any Web Server, and ESPECIALLY not one that's going to be running Visual Foxpro for dynamic content.

Processor (Dual Zeon 2.4ghz)
This is where the real magic is performed on your server. You would think that the faster the processors the faster the server is, but this isn't the case. The only time that your processor utilization should climb on a server is during backups, or in the event that you are running a complex mathematical process.

For a typical website that serves data driven dynamic content, I would rarely expect your processor to reach more than 15-20% utilization, and even then only for short bursts during the peak hours.

In most cases a single processor with middle of the road speed would do fine. I personally recommend dual processors for servers that are going to run anything other that IIS and VBScript. So adding Visual Foxpro to the mix, you simply add a processor too.

Hard Drive I/O.
Visual Foxpro has enjoyed the reputation for having the fastest data access of any desktop Database system for years. The reason for this is because it uses a combination of memory and Hard drive IO to get that reputation. Visual Foxpro loses its speed and reputation when it comes to accessing it's data across a network, and becomes impossible across a wide area network.

If your data is going to be housed on your server's local drives expect to see your hard drives getting a workout. Make sure you are running the highest quality hard drives you can get. If your data is not going to reside on the local drives of the Web Server I would highly recommend against using Foxpro's Local Tables.

Don't Skimp on your hard drives, go with the FASTEST, most reliables drives you can get, and replace them frequently to keep them reliable.

On Thor, I replace two hard drives ever three months giving me a total of one year's use on the oldest drive in the system. With hot swappable mirrored sets, it's easy to do this, you just remove on drive in the pair, plug in the new one and tell the system to rebuild the mirror and your done.

I then move the drives I pulled out of the system to my backup server, and from that server to the off site server and eventually if they survive long enough they end up on a shelf in my office as temporary spares.

Trust me the expense of doing this pays off in spades with system reliability. You would be surprised to hear that I have had only one drive failure in the first year of operation, one in 12-18 months and three failures in the 18-24 month range. And these are high quality server drives from a reputable manufacturer.

How a web page is accessed.
If you are new to accessing data with a web server, you are in for a surprise. Web Servers open and close data tables every time a page is served to the user. They do not remember what the user typed, or what was even sent to the user.

This means that you will be taking a whole different approach to laying out your applications. Traditionaly Foxpro programmers have opened the tables at the beggining of the application and then closed them when the user quit the application. Or a variant on this of opening the tables when the user needed access to them, then closing them at the end of the need for the tables.

Rarely if ever did we open a table, get the data from the table, and then immediately close the table before we allowed the user to do their work. Forcing the user to endure the process time for opening a table, getting the data, closing it, only to open the same data, save the user changes, and then close it again... You wouldn't consider that at all on a desktop application.

Well guess what, that's exactly what you will be doing with web content. To make matters worse, once you send the web page to the user, you will forget you ever sent that page/data to the user. Then when they return the replacement data your program will start off all fresh.

This is because a Web Server operates in a mode called "Stateless." What this means is that every transaction to the server is a complete transaction, and does not rely on anything else. Sounds fun right? Well actually it is, not too much really changes between the way you currently work and the way you will need to be working with data.

There are only two real areas that you will need to change about how you access your data. The first is that you will no longer have the record in the table current when the user saves, so you will need to work out some form of pointer to the record. For Foxpro Tables you can usually get away with using recno(). The other area is record locking. This is the area that becomes a real pain when working with data via the web.

Record Pointers can be as simple as the record number, or they can be a series of fields that make a record unique, or they can be a single field.

I don't like using record numbers because they have a tendency to change over time, especially if you do any data maintenance such as packing tables to remove deleted records. They work because they only have to be accurate during the scope for which the user is editing the records.

Using a combination of fields or any field that a user keys in or changes is dangerous. If a user happens to change one of these fields then the link back to the original data is lost.

I prefer to use a "Primary Key" field, what this is is a numeric field that is auto incremented, and kept unique. I never renumber these fields or reset counter for zapped tables so I end up with a number that is 100% unique for the life of the data. Works perfect for linking to records. And coincidentally it also works beautiful for foreign keys in other tables too...

Record Locking in a Stateless Environment:
Most books I have read have basically said that there is no such thing as record locking in a stateless environment, and they are partially correct. You could not ever use Foxpro's Record locking, the reason is that by the time the user has posted their changes back to the server the previous session would have already closed the tables which releases the locks.

Record locking is a complex thing when it comes to stateless because you have no way of even knowing if the user is going to post changes back or not, so basically you have a few choices, none of which are elegant.

1) Last Save Wins
What this means is that the last person to save a record gets to keep their changes, so in the case of two people editing the same record the last one wins.

2) First Save Wins
This is similar to Last Save, but instead it stores a timestamp with the save, and if the timestamp from your source record is earlier than the one in the table your saves are not saved. This one is better in my opinion because it gives you an option to pragmatically move on to a conflict resolution option where you display the data saved while you were editing, and the data you changed and give the user the chance to make the decision about what is saved.

3) Create your own record locking flags
This is the option I actually use in most cases. The way that I implemented this is to create a table "Record Locks" and in this table, I have TableName, RecordID, SessionID, DateTimeStamp. When a user clicks to edit a record I insert a record(s) into this table for the data they chose to edit. Then I delete that record when they save.

With this option, you need to offer a way for locks to automatically be released, or your in for a lot of calls asking you to release such and such record because its in use by someone. So first you need to determine a period of time that a lock can remain active. Then simply remove the locks after that period of time.

I make several choices to make this work, first off when a user logs off the system correctly I automatically remove all their locks. Second when their session expires (we'll talk about this later) I delete the locks, and last off, I allow a lock to remain in affect for 1 hour, after one hour it's automatically removed.

Stick around, tomorrow we will get into exactly what to use Foxpro for and what not to use it for when developing web pages.

Merry Christmas to everyone, and hope you stop back tomorrow.