Using mSQL in a Web-Based Production Environment
The mSQL server process needs to be invoked during your machine's startup procedure. Place a line similar to the following in your /etc/rc.d/rc.local file:
For testing purposes, and to save you a reboot, execute the above command from the shell prompt. This gets the server process up and running, ready to handle your database requests.
To make sure your mSQL server has been installed properly, several test scripts are supplied with the mSQL distribution archive. Finally, make sure you take the time to look over the mSQL documentation.
As part of this article, a fully functional web-based database application is presented in its entirety. The purpose is to provide a framework for your own web endeavors, as well as to show off the ease with which you can construct these types of applications using standard Unix tools.
Our example focuses on the creation and interaction of a database to contain concert listings that contains several items of information for each concert. First, we concentrate on populating the database with a series of concert listings, then build some web-based queries.
Before we create the database schema itself, we need to determine the functional elements of the application. What information needs to be stored in the database? What types of queries will be offered to our web guests? Do we need the ability to allow additional concert entries to be added through the Web?
Our database stores the concert date, opening act, headlining act, location and ticket price. For now, we concentrate on simple queries such as looking up concerts by band name and location. We also assume the “add” page is either protected by HTTPD authentication or made available through an Intranet.
The following is the mSQL schema we used to create the concerts database:
create table notices ( show_date char(10), headliner char(30), opening_act char(30), location char(30), ticket_price char(10) )
To create the sample database and load it with initial data, execute the mkconcerts script located in the examples archive. For those of you who don't have ftp access, the data are shown in Listing 1.
To verify that the data have been loaded properly, execute the mkreport script (see Listing 2. mkreport Script), which is also in the examples archive. This script simply dumps the contents of the database into a formatted table, called concert.listings, shown in Listing 3.concert.listings File
Now that the database is created and populated with test data, it's time to begin constructing HTML pages that interact with the database. (Keep in mind that this article is intended to supplement but not replace the documentation that ships with mSQL and w3-msql.)
w3-msql acts as an HTML “preprocesser” of sorts. It takes a standard HTML document and performs database actions based on embedded mSQL primitives as shown in Figure 1.
As a first example, consider the HTML document named ex1.html, shown in Listing 4. HTML Document with Embedded mSQL Commands, which demonstrates a simple link to a document containing embedded mSQL commands.
Note the calling procedure. The document name is placed immediately after the invocation to w3-msql (PATH_INFO). w3-msql takes this document, looks for any embedded mSQL commands and sends the appropriate output to the client. In this case, we are requesting the query1.html document, which contains the HTML shown in Listing 5. HTML Document. When selected, the output of the w3-msql link is shown in Figure 2.
While the previous example is rudimentary, it demonstrates the ease of presenting information from an mSQL database. Let's continue with this example by expanding our queries. One nice feature to implement is the ability to produce a listing sorted by a specific field. Consider the following rewrite of our ex1.html file, which adds several hypertext links, each sorted on a different field. The file, called ex2.html, is shown in Listing 6.
Note the addition of the ?sortby=?????? parameters. We create new variables, make an initial assignment, and pass that, along with the document name (now called query2.html), to w3-msql. We use the sortby variable to contain a field name on which we wish to sort the listing.
Now that we have coded the skeleton for the front end, what changes are needed to the actual query template? Consider the rewrite of query.html, now appropriately called query2.html, shown in Listing 7.
The only major change is in the mSQL select statement. We added the standard ANSI SQL order by clause, passing along the content of our new sortby variable. In addition, note the use of the mSQL print command to display the sort field name in the header above the table. A sortby location displays the HTML table shown in Figure 3.
To finish our simple query example let's revisit the concept of searching by location. Let's allow the user to input a city name manually, and pass it along to w3-msql for querying. Consider the simple HTML document, called ex3.html, shown in Listing 8. It provides an input field in a form, linked to w3-msql as the form processor.
The query3.html document, which handles the actual query-by-city is shown in Listing 9. Note the change in the mSQL select statement. We use the contents of the form field in the ex3.html document as the value of a where-like SQL clause.
To test this form, enter New York as the city name—Figure 4 shows the output.
That about sums it up for performing simple queries to a mSQL database. Try experimenting with different tables and interface designs. Try using frames. Place a search form in one frame and the query results in another. The possibilities are endless with the Linux/mSQL approach.
B. Scott Burkett formerly a Unix system programmer and technical instructor, is the Internet Services Manager for Decision Consultants, Inc, one of the largest software services consulting firms in the country (http://www.dcicorp.com). He enjoys major league baseball (Go Braves), good jazz bars, tinkering with Linux, and derailing military conspiracy plots in third world countries. He can be reached via e-mail at firstname.lastname@example.org.
Practical Task Scheduling Deployment
July 20, 2016 12:00 pm CDT
One of the best things about the UNIX environment (aside from being stable and efficient) is the vast array of software tools available to help you do your job. Traditionally, a UNIX tool does only one thing, but does that one thing very well. For example, grep is very easy to use and can search vast amounts of data quickly. The find tool can find a particular file or files based on all kinds of criteria. It's pretty easy to string these tools together to build even more powerful tools, such as a tool that finds all of the .log files in the /home directory and searches each one for a particular entry. This erector-set mentality allows UNIX system administrators to seem to always have the right tool for the job.
Cron traditionally has been considered another such a tool for job scheduling, but is it enough? This webinar considers that very question. The first part builds on a previous Geek Guide, Beyond Cron, and briefly describes how to know when it might be time to consider upgrading your job scheduling infrastructure. The second part presents an actual planning and implementation framework.
Join Linux Journal's Mike Diehl and Pat Cameron of Help Systems.
Free to Linux Journal readers.Register Now!
- SourceClear Open
- SUSE LLC's SUSE Manager
- Tech Tip: Really Simple HTTP Server with Python
- Linux in Government: Open Source Innovation within the DoD
- Managing Linux Using Puppet
- LAMP Development at Public Sector Web Sites
- Returning Values from Bash Functions
- July 2016 Issue of Linux Journal
- My +1 Sword of Productivity
- Linux Access in State and Local Government, Part VII
With all the industry talk about the benefits of Linux on Power and all the performance advantages offered by its open architecture, you may be considering a move in that direction. If you are thinking about analytics, big data and cloud computing, you would be right to evaluate Power. The idea of using commodity x86 hardware and replacing it every three years is an outdated cost model. It doesn’t consider the total cost of ownership, and it doesn’t consider the advantage of real processing power, high-availability and multithreading like a demon.
This ebook takes a look at some of the practical applications of the Linux on Power platform and ways you might bring all the performance power of this open architecture to bear for your organization. There are no smoke and mirrors here—just hard, cold, empirical evidence provided by independent sources. I also consider some innovative ways Linux on Power will be used in the future.Get the Guide