E-commerce and Linux

paRcat asks: "My company is using a proprietary system for letting our customers order online. It takes the order, and as soon as they click submit, sends it on to our main system via a serial connection. Both systems are running on NT, and they die constantly. I've been pushing to get Linux in here, and I think replacing the online ordering server is the best way to start. Our catalog has around 25,000 items in it. It's held in an Access database right now... around 14 Megs. I suppose it could be converted, but every time a pricing update comes out, it's distributed in mdb format. What tools exist for Linux that can do what I need? It just needs to allow access to the database, take the order, and send it down the serial line. I was contemplating setting up mod_perl and just writing a bunch of code, but I'm still a bit new to PERL, and I'm not even sure if that's the best choice. " Apache, Perl/mod_perl and MySQL/Msql/PostgreSQL all sound perfect for this application, but the issue of getting the Access information (both the existing data and the future updates) might be a problem. Any ideas?

Comments

Converting Access to MySQL

[Gene77]

I would suggest looking to use MySQL, and as you receive updates in your MDBs, use the ExportSQL script by Cynergi. If you're comfortable in VBA, you'll want to tweak it a little if you are to use it a lot. I've used it at work, so I know that it works fairly well.

Access allows you to get away with somewhat sloppy data modeling, so you'll need to revisit the generated SQL and make sure you have all the "NOT NULL" and different data types in place as you need them (this can all be tweaked in the VBA without too much effort, but you should be knowledgable in VBA and SQL). You no doubt know that the MySQL data types are more specific than the data types used by Jet (the actual db engine Access uses). This means that you'll want to make sure that the column that was exported as "REAL" shouldn't really be "MEDIUMINT" instead.

The script generates SQL files which can then be used against MySQL in batch mode. So with it, you can set up the VBA function to create the appropriate SQL for refreshing your tables whenever you receive a new MDB.

My suggestion is to dump Access. It's nice for rapid devolopment, but if you're running an e-commerce thing with it, then I pity you. Not only would NT be your problem, but you'll have Access' sloppy page-level record locking and it's tendency to not release connections and record locks with the consistency e-commerce apps should have the right to expect.

Suggestions

[msuzio]

Well, it sounds like a lot more research is needed before you're going to be able to present a convincing case. The NT side has a few things going for it:

- It's there. He who gets there first has the home-field advantage
- It handles the Access data import needs without any problems (or at least you didn't mention any)
- The usual PHB tendency to swallow MS FUD(tm) will probably work against you.

That having been said, here's a start on countering it and working up a case:

- Definately look into ODBC or some sort of easy export from Access. I'm not familiar enough with the MS world to know a sure solution, but I imagine the worst case solution is some sort of pretty simple VBA scripting in Access to open the file and dump out selected records (or all records) in a nice format MySQL can import.

- I agree that MySQL is probably a good client database. Don't rule out other options, however, you don't want to find MySQL doesn't fit your needs and then have to propose *another* change to management. PostGres has worked well for us in some applications, and is a little more full-featured than MySQL (although not as fast, and feature-wise it's actually a pretty close race). Oracle on Linux is even a possible choice, but you haven't mention budget or database size. Since it's coming from Access, I'll assume it's a small database -- in that case, MySQL looks pretty good.

- Web server: Apache or Stronghold if you need SSL. We just started using Stronghold, and so far it seems dead easy (much better than Netscape Enterprise Server which was the only SSL solution we had tried before this). It's not expensive as far as SSL solutions go, and it seems to track Apache releases pretty well.

- Application coding:
Perl is great, but you say you're not experienced yet. That's not a show stopper at all, but consider carefully how to proceed. If you're willing to pick up the Camel book, the Ram Book, and a printout of the CGI.pm and mod_perl documentation, you may be ready to be a Perl Web Programmer :-). But plan on it taking some time to get it right. Be sure to steal as much code as possible from the people who have gone before you -- I suggest a through reading of the WebTechniques archives to see some excellent solutions to common problems.
As far as non-Perl solutions, Python is great, and Zope seems to be getting more and more attention. Check out Zope and scour freshmeat for other Web Application architectures, you may find those solve some of your problems well. Java servlets are an excellent choice also, but expect those to require much more programming savvy.

Anyway, the first step really is to analyze the current system and figure out all the components. For each one, pick a few possible substitutes. Play around and convince yourself that the pieces you select play nice together. Then show *that* to the PHB and get the go-ahead, they tend to be pretty easy to convince if you know your stuff...

Been there, done that

[SheldonYoung]

Okay, can say I've done this exact thing. We started out prototyping on IIS and Access because we didn't have a decent Linux box yet and Access makes it easy to prototype databases. Our plan was always to go to Linux, but we were forced into it by IIS doing some freaky things.

The perl was a piece of cake to move over - we even switched to mod_perl along the way. The database, however, was a bit of a pain. We moved it first from Access to SQL 7, then used SQL 7s data export function to stuff it into Informix on the Linux box. It was a nightmare, there are so many things that just don't move across. Views, identifiers longer than 18 characters, etc.

My suggestions, from hard-won experience are:

1. Run a multiplatform web server, not IIS. Unless you're using ASP or such it should be simple. This is by far the best move we made - It was almost bearable to develop on NT once IIS was out of the way.
   
   
2. Clean up your database architecture and make your SQL portable. Don't assume TRUE == 1 or you can escape table names with [].
   
   
3. Move in stages. Get mySQL, Informix, Oracle, whatever running under NT and move the data to it first. Then move it to the Linux box when things have settled down. Same with switching web servers.
   

This is a good lesson in why to create portable applications. Just move in pieces and you'll gradually see your system get more and more stable, without getting above your head in new things to learn.

Good luck.

A suggested migration path...

[wdr1]

You'll find that radical shifts are often resisted in the buisness world. A phased implementation might be the best way to go & would easy to do.

You're on the right path... Perl can certainly do what you. In fact, most of the difficult code that you would need is already written: DBI & DBD::ODBC. What you'd want to do is establish an ODBC connection to your Access database & do all your data manipulation that way.

Using this, you can move the dynamic component of your site to Perl based CGIs running on your existing NT servers. (Take care to avoid anything platform specific, and avoid ASP/Perl-script. That will only furhter anchor you to NT.) Not a radical shift, and one that should both help your current situtation and increase everyone's confidence in what you're doing.

Next, on the side, implement you linux box running mysql. Write a Perl script using DBI, DBD::ODBC, and DBD::Mysql to periodically refresh the mysql database from the Access table. This will be the trickest part. I recommend keeping the mysql data model the same as the Access data model to keep things simple. Unfortunetly, there isn't an easy/inexpensive way to read Access files on a linux box, but that's not a big obstacle. You can use DBD::Proxy. Alternatively, have two Perl scripts: one on the NT box to export data to a tab-delimited flat file, and another script on the Linux side to import the file. With some smart scripting, the use of Net::FTP & NET::Telnet, you could integrate this into one file. (Simpler, but less cool, just do some intelligent automated scheduling on both sides via NT's scheduler & Linux's cron.)

Now, through mod_perl into the mix. Present each solution side by side to your superiours. Explain the performance difference, the scalability, the cost difference, the seemless integration, and the added functionality of the Linux/Apache/Perl/mod_perl/Mysql solution, and *presto* you have your solution (and you look like a superstar. :)

-Bill