| A MySQL distribution includes a set of utility programs. For example, mysqldump exports the structural definitions and contents of tables, mysqlimport loads data files into tables, mysqladmin performs administrative operations, and mysql enables you to interact with the server to execute arbitrary queries. Each of the standard MySQL utilities is designed to be a small, focused program with a specific, limited function. This is true even for mysql, which is more flexible than the other utilities 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. This limited nature of the MySQL clients is not a flaw it's by design. The programs are general-purpose utilities that are not intended to anticipate all possible requirements you might have. The MySQL developers do not subscribe to the philosophy of writing huge, bloated programs that try to do everything you might possibly want to do (and thus end up including lots of code for many things you don't care about at all). 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 provides you with the flexibility to satisfy whatever specialized requirements your applications may have. By giving you access to the MySQL server, the client library opens up possibilities limited only by your own imagination. To understand specifically 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. The program can eliminate the need for the user 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 style 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 rather 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. Some applications might not even involve a 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 produces output. But the output consists of the 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. 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 SQL variables can be assigned values and referred to later. But that's about it. 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 enables 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. 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 post-processing the output using other UNIX utilities. However, that can become ugly, especially as your task becomes more involved. It may also produce a sense of "it-works-but-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 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 scores into the grade book using a Web browser One area that we'll consider in some detail is integrating 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: Using 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 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 enable you to see what tables your database contains, what each one's structure is, and what its contents are. Using 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 site 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. Here, the role of MySQL is more subtle, and visitors to the site may not even be aware that a database plays a part in the services the site offers. These perspectives are not necessarily 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, retrieves the result, and then 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 on which the MySQL client programs run. 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 the MySQL clients. For example, you can't really put together an application that incorporates a shopping cart using just MySQL clients. Any programming language can be used to write Web-based applications, but some are more suitable than others. We'll see this in the "Choosing an API" section later in this chapter. |
