Why Write Your Own MySQL Programs?
A MySQL distribution includes a set of client programs. For example, mysqldump exports the structural definitions and contents of tables, mysqlimport loads data files into tables, mysqladmin performs administrative operations, and mysql lets you interact with the server to execute arbitrary SQL statements. Each of the standard MySQL clients is intended as a small, focused program with a specific, limited function. This is true even for mysql, which is more flexible than the other clients in the sense that you can use it to execute any number of different queries: It's designed with the single purpose of allowing you to issue SQL queries directly to the server and view the results.
The standard client programs handle many of the most common tasks that MySQL users need to perform, but applications sometimes have requirements that are not addressed by the capabilities of those clients. In this part of the book, we'll discuss what you need to know to write your own MySQL-based programs for accessing your databases. To make this possible, MySQL includes a client-programming library that gives you the flexibility to satisfy whatever specialized requirements your applications may have. By providing you with access to the MySQL server, the client library opens up possibilities limited only by your own imagination.
To understand what you gain by writing your own programs, consider what you can accomplish that way in comparison to using the capabilities of the mysql client and its no-frills interface to the MySQL server:
You can customize input handling.
With mysql, you enter raw SQL statements. With your own programs, you can provide input methods for the user that are more intuitive and easier to use. Your program can eliminate the need for its users to know SQL, or even to be aware of the role of the database in the task being performed. Input collection can be something as rudimentary as a command-line interface that prompts the user and reads a value, or something as sophisticated as a screen-based entry form implemented using a screen management package such as curses or S-Lang, an X window using Tcl/Tk, or a form in a Web page. For most people, it's a lot easier to specify search parameters by filling in a form than by issuing a SELECT statement. For example, a real estate agent looking for houses in a certain price range, style, or location just wants to enter search parameters into a form and get back the qualifying offerings with a minimum of fuss. For entering new records or updating existing records, similar considerations apply; a keyboard operator in a data entry department should need to know only the values to be entered into records, not the SQL syntax for INSERT, REPLACE, or UPDATE. An additional reason to interpose an input-collection layer between the end user and the MySQL server is that you can validate input provided by the user. For example, you can check dates to make sure they conform to the format that MySQL expects, or you can require certain fields to be filled in. This enhances the safety and security of your applications. Some applications might not even involve a human user, such as when input for MySQL is generated by another program. You might configure your Web server to write log entries to MySQL rather than to a file. A system-monitoring program can be set up to run periodically and record status information to a database.
You can customize your output.
mysql output is essentially unformatted; you have a choice of tab-delimited or tabular style. If you want nicer-looking output, you must format it yourself. This might range from something as simple as printing "Missing" rather than NULL to more complex report-generation requirements. Consider the following report:
State City Sales
------------------------------
AZ Mesa $94,384.24
Phoenix $17,328.28
-----------------------
Subtotal $117,712.52
------------------------------
CA Los Angeles $118,198.18
Oakland $38,838.36
-----------------------
Subtotal $157,036.54
==============================
TOTAL $274,749.06
This report includes several specialized elements: Customized headers. Suppression of repeating values in the State column so that the values are printed only when they change. Subtotal and total calculations. Formatting of numbers, such as 94384.24, to print as dollar amounts, such as $94,384.24.
Another common type of task involving complex formatting is invoice production, where you need to associate each invoice header with information about the customer and about each item ordered. This kind of report can easily exceed mysql's formatting capabilities. For some types of tasks, you may not want to produce any output at all. Perhaps you're simply retrieving information to calculate a result that you insert back into another database table. Or you may want the output to go somewhere other than to the user running the query. For example, if you're extracting names and email addresses to feed automatically into a process that generates form letters for bulk email, your program does produce output, but it consists of messages that go to the mail recipients, not to the person running the program.
You can work around constraints imposed by the nature of SQL itself.
SQL is not a procedural language with a set of flow control structures such as conditionals, loops, and subroutines. SQL scripts consist of a set of statements executed one at a time from beginning to end, with minimal error checking. If you execute a file of SQL queries using mysql in batch mode, mysql either quits after the first error, or, if you specify the --force option, executes all the queries indiscriminately, no matter how many errors occur. By writing your own program, it's possible to selectively adapt to the success or failure of queries by providing flow control around statement-execution operations. You can make execution of one query contingent on the success or failure of another, or make decisions about what to do next based on the result of a previous query. MySQL 5.0 introduces support for stored functions and procedures. These can use flow control and error-handling constructs, which provides additional flexibility at the SQL level. However, these constructs still are not as flexible as those provided by general-purpose programming languages. SQL has very limited persistence across statements, and this carries into mysql. It's difficult to use the results from one query and apply them to another or to tie together the results of multiple queries. LAST_INSERT_ID() can be used to get the AUTO_INCREMENT value that was most recently generated by a prior statement, and user variables can be assigned values and referred to later. But that's about all. This limitation makes certain common operations difficult to perform using SQL alone, such as retrieving a set of records and using each one as the basis for a complex series of subsequent operations. If you retrieve a list of customers and then look up a detailed credit history for each one, the process may involve several queries per customer. mysql is unsuitable for this kind of task, because it may be necessary to issue several statements that depend on the results of previous queries. In general, a tool other than mysql is needed for tasks that involve master-detail relationships and have complex output-formatting requirements. A program provides the "glue" that links queries together and allows you to use the output from one query as the input to another.
You can integrate MySQL into any application.
Many programs stand to benefit by exploiting the capability of a database to provide information. The client-programming library gives you the means to do this. An application that needs to verify a customer number or check whether an item is present in inventory can do so by issuing a quick query. A Web application that enables a client to ask for all books by a certain author can look them up in a database and then send the results to the client's browser. It's possible to achieve a kind of rudimentary "integration" of MySQL into an application by using a shell script that invokes mysql with an input file containing SQL statements, and then postprocessing the output using other utilities. But that can become ugly, especially as your task becomes more involved. It may also produce a sense of "this works, but it feels wrong" as the application grows by accretion into a messy patchwork. In addition, the process-creation overhead of a shell script that runs other commands may be more than you want to incur. It can be more effective to use the client library to interact with the MySQL server directly, extracting exactly the information you want as you need it at each phase of your application's execution.
Chapter 1, "Getting Started with MySQL and SQL," enumerated several goals with respect to our sampdb sample database that require us to write programs to interact with the MySQL server. Some of these goals are shown in the following list:
Format the Historical League member directory for printing Allow for online presentation and searching of the member directory Send membership renewal notices by email Easily enter student scores into the gradebook using a Web browser
One issue that we'll consider in some detail is the question of how to integrate MySQL's capabilities into a Web environment. MySQL provides no direct support for Web applications, but by combining MySQL with appropriate tools, you can issue queries from your Web server on behalf of a client user and report the results to the user's browser. This allows your databases to be accessed easily over the Web.
There are two complementary perspectives on the marriage of MySQL and the Web:
Use a Web server to provide enhanced access to MySQL.
In this case, your main interest is your database, and you want to use the Web as a tool to gain easier access to your data. This is the point of view that a MySQL administrator probably would take. The place of a database in such a scenario is explicit and obvious because it's the focus of your interest. For example, you can write Web pages that allow you to see what tables your database contains, what each one's structure is, and what its contents are.
Use MySQL to enhance the capabilities of your Web server.
In this case, your primary interest is your Web site, and you may want to use MySQL as a tool for making your site's content more valuable to the people who visit it. This is the point of view a Web developer probably would take. For example, if you run a message board or discussion list for visitors to the site, you can use a database to keep track of the messages. The role of MySQL in this case is more subtle, and visitors to the site might not even be aware that a database plays a part in the services the site offers.
These perspectives need not be mutually exclusive. For example, in the Historical League scenario, we'll use the Web as a means for members to gain easy access to the contents of the membership directory by making entries available online. That is a use of the Web to provide access to the database. At the same time, adding directory content to the League's Web site increases the site's value to members. That is a use of the database to enhance the services provided at the site.
No matter how you view the integration of MySQL with the Web, the implementation is similar. You connect your Web site front end to your MySQL back end, using the Web server as an intermediary. The Web server collects information from a client user, sends it to the MySQL server in the form of a query, and then retrieves the result and returns it to the client's browser for viewing.
You don't have to put your data online, of course, but often there are benefits to doing so, particularly in comparison with accessing your data via the standard MySQL client programs:
People accessing your data through the Web can use whichever browser they prefer, on whatever type of platform they prefer. They're not limited to systems that the standard MySQL client programs run on. No matter how widespread the MySQL clients are, Web browsers are more so. The interface for a Web application can be made simpler to use than that of a standalone command-line MySQL client. A Web interface can be customized to the requirements of a particular application. The MySQL clients are general-purpose tools with a fixed interface. Dynamic Web pages extend MySQL's capabilities to do things that are difficult or impossible to do using only the standard MySQL clients. For example, you can't really use them to put together an application that incorporates a shopping cart.
Any programming language can be used to write Web-based applications, but some are more suitable than others. We'll see this later in this chapter in the section "Choosing an API."
|
