Using mSQL in a Web-Based Production Environment
Over the past few years, many companies have realized the benefits of using Linux to serve web content to the masses. The power of a freely available, feature-laden 32-bit operating system, coupled with a vast number of utilities and development tools, provides a cost-effective solution for implementing enterprise and publicly-available information servers.
While many organizations have championed Linux as a web server, few have taken advantage of perhaps one of the most interesting aspects of the Web: dynamic content generation and delivery. Think about it. Of all the web sites you visit on a regular basis, how many of them have static content? Not many. Many us go to Yahoo! each day to see “What's New on the Internet”. Many cruise over to catch the news on CNN throughout the day. These sites have dynamic content. If the listings on Yahoo! didn't change every day, how many of us would go back after the first visit?
To provide dynamic content to your cyberguests, you can use a variety of tools and methods. One of the more popular approaches is to integrate data repositories with the Web. Creating web-based applications that integrate with existing database pools seems to be the rage this year. This paradigm has led to some amazing third-party products such as Bluestone Software's Sapphire/Web (http://www.bluestone.com/) and Haht Software's HahtSite (http://www.haht.com/). These products provide full development environments for designing, creating and deploying web-based applications. Unfortunately, the majority of these products are not yet available for Linux (iBSC options ignored for the moment). However, there is an alternative.
You can retrofit a Linux-based web server to provide access to enterprise data in a very cost-effective manner. Third-party packages typically have an integrated development environment (IDE) to provide for seamless, somewhat painless development. This can be easily replaced by your favorite text/HTML editor. Third-party packages typically interface nicely with expensive, proprietary database platforms such as Oracle, Sybase and Informix. These database systems cost thousands of dollars, and generally require a seasoned database administrator (DBA) to operate efficiently. In our Linux model, we will employ David Hughes' mSQL engine, which costs a whopping $170 USD, and is a breeze to use. To fully implement such an approach, expect to spend no less than $10,000 on the software alone. The Linux/mSQL approach (including the cost of a Linux CD-ROM distribution, the mSQL engine and coffee) should cost around $250. Senior management has always had a love affair with saving money—show them the numbers. It sells itself, folks.
In this article, the following assumptions are made:
You have a working, fully installed Linux server.
You have a functional HTTP server running (NCSA, CERN, Apache, etc.).
You have installed either BASH, pdksh or ksh93.
You have the standard Unix tools in place (awk, sed, Perl, etc.).
The first item you need is the mSQL (mini Structured Query Language) engine itself. The mSQL package implements a relatively fast, lightweight database engine that uses a subset of the ANSI SQL standard to perform its operations. As of this writing, the current stable release is version 1.0.16, although the long awaited v2.0 release has been promised soon. It can be obtained via ftp at ftp://bond.edu.au/pub/Minerva/msql/. The official home of mSQL is at http://Hughes.com.au/.
Next, you need the w3-msql package, also written and distributed by David Hughes. This package provides the CGI (Common Gateway Interface) interface to the databases managed by mSQL. As of this writing, the current version of w3-msql is version 1.0, although 2.0 is in the works. It is available via ftp at ftp://bond.edu.au/pub/Minerva/w3-msql/.
Finally, the example scripts presented in this article are available via ftp at ftp://www.dcicorp.com/pub/unix/msqlweb/. Unless you are a typing enthusiast and are already familiar with mSQL, I recommend you snag the examples.
Once you have obtained the distribution archive, move it to either a scratch directory or the base of your normal source tree. You can extract the package as follows:
gzip -d msql-1.0.16.tar.gz tar xf msql-1.0.16.tar
To prepare for compilation, switch to the ./msql-1.0.16 directory and execute the following commands:
make target cd targets/Linux* ./setupYou will be asked the following questions pertaining to the actual build of the package. Here are a few notes to guide you:
Top of install tree? While mSQL can be installed virtually anywhere on your system, you should use the default path, /usr/local/Minerva. It makes installing third-party add-ons easier.
Will this installation be running as root? This question is concerned primarily with the TCP port mSQL uses for network communication. If your distribution is running as root, the default TCP port is 1112; otherwise port 4333 is used. You can tailor these defaults in the ./common/site.h header file. Also, take a look at the mSQL FAQ, available at the mSQL web site, which describes a number of other scenarios this setting affects.
Directory for PID file? Where do you keep your PID files? The default is /var/adm, which is fine for most folks.
At this point, the script will finish its tailoring process. Before you actually compile the package, you can perform several customizations by editing a few of the source files. The first, ./common/site.h, contains such gems as selecting the German language over English for error reporting. Give it a quick glance and make sure you are comfortable with the settings. Another possible modification lies in the ./msql/msql_priv.h file. I like to bolster my database limits a bit. At the top of this file are several values you can alter to suit your needs, including the maximum number of fields returned in a query, maximum number of network connections allowed, and the maximum length for field and table names. Feel free to modify these as you see fit. For the non-adventurous, the defaults should suffice.
To compile the package, simply execute the following command from the base source directory (./targets/Linux*):
Compilation on a Pentium-class machine generally takes a little over a minute. If there are no compiler errors, you can install the package by executing the following command:
make installThe system is installed in /usr/local/Minerva (or whatever you set the install directory to when you ran setup).
Compiling and installing the w3-msql utility is much simpler. After you obtain the distribution archive, extract it into your source or scratch directory as follows:
gzip -d w3-msql-1.0.tar.gz tar xf w3-msql-1.0.tar
Change into the w3-msql-1.0 directory, and remove the -lsocket -lnls assignment to the make variable LIBS. Linux does not require these libraries to be linked into the application. Run make, and you are in business. If the build was successful, simply copy the w3-msql binary image over to your web server's cgi-bin directory.
|Designing Electronics with Linux||May 22, 2013|
|Dynamic DNS—an Object Lesson in Problem Solving||May 21, 2013|
|Using Salt Stack and Vagrant for Drupal Development||May 20, 2013|
|Making Linux and Android Get Along (It's Not as Hard as It Sounds)||May 16, 2013|
|Drupal Is a Framework: Why Everyone Needs to Understand This||May 15, 2013|
|Home, My Backup Data Center||May 13, 2013|
- New Products
- Linux Systems Administrator
- Senior Perl Developer
- Technical Support Rep
- UX Designer
- Designing Electronics with Linux
- Dynamic DNS—an Object Lesson in Problem Solving
- Using Salt Stack and Vagrant for Drupal Development
- Making Linux and Android Get Along (It's Not as Hard as It Sounds)
- Nice article, thanks for the
2 hours 3 min ago
- I once had a better way I
7 hours 49 min ago
- Not only you I too assumed
8 hours 7 min ago
- another very interesting
10 hours 15 sec ago
- Reply to comment | Linux Journal
11 hours 53 min ago
- Reply to comment | Linux Journal
18 hours 47 min ago
- Reply to comment | Linux Journal
19 hours 3 min ago
- Favorite (and easily brute-forced) pw's
20 hours 55 min ago
- Have you tried Boxen? It's a
1 day 2 hours ago
- seo services in india
1 day 7 hours ago
Enter to Win an Adafruit Pi Cobbler Breakout Kit for Raspberry Pi
It's Raspberry Pi month at Linux Journal. Each week in May, Adafruit will be giving away a Pi-related prize to a lucky, randomly drawn LJ reader. Winners will be announced weekly.
Fill out the fields below to enter to win this week's prize-- a Pi Cobbler Breakout Kit for Raspberry Pi.
Congratulations to our winners so far:
- 5-8-13, Pi Starter Pack: Jack Davis
- 5-15-13, Pi Model B 512MB RAM: Patrick Dunn
- 5-21-13, Prototyping Pi Plate Kit: Philip Kirby
- Next winner announced on 5-27-13!
Free Webinar: Hadoop
How to Build an Optimal Hadoop Cluster to Store and Maintain Unlimited Amounts of Data Using Microservers
Realizing the promise of Apache® Hadoop® requires the effective deployment of compute, memory, storage and networking to achieve optimal results. With its flexibility and multitude of options, it is easy to over or under provision the server infrastructure, resulting in poor performance and high TCO. Join us for an in depth, technical discussion with industry experts from leading Hadoop and server companies who will provide insights into the key considerations for designing and deploying an optimal Hadoop cluster.
Some of key questions to be discussed are:
- What is the “typical” Hadoop cluster and what should be installed on the different machine types?
- Why should you consider the typical workload patterns when making your hardware decisions?
- Are all microservers created equal for Hadoop deployments?
- How do I plan for expansion if I require more compute, memory, storage or networking?