MySQL Program Design

MySQL has a set of utilities. For example, mysqldump exports the context and structure definitions of a table. mysqlimport loads data files into the table, and mysqladmin manages w operations. mysql allows users to interact with the server to execute arbitrary queries. Every standard MySQL utility tends to be small, with a focus on the specific and limited functionality that a program can accomplish. Even in mysql, mysql is more flexible than other utilities, so it can be used to execute any number of queries, that is, it is designed to allow direct SQL queries to the server and view the query results.

The limited feature of the MySQL client is not a drawback, but is specially designed. Programs are utility programs with a general purpose; they do not try to anticipate all the possible needs you want to do. MySQL developers do not approve of writing large, bloated programs to try to do everything they may want to do, and the results will include a large number of programs that you don't care about at all. event code ). However, sometimes some applications do need to be unable to process the capabilities of conventional clients. To handle these situations, MySQL provides a client programming library. This allows you to write your own program to meet any specific requirements that your application may have. By allowing you to access the MySQL server, the openness of the client is limited only by your imagination.

How can I write my own program to obtain special capabilities? Let's compare the access to the mysql server by the MySQL client and its interface without additional code:

Customizable Input Processing

You can use mysql to enter the original SQL statement. With your own program, you can provide users with more intuitive and easy-to-use input methods. The user does not need to know SQL-or even the role of the database in the completed task.

The collection of input information may be like a command line-style prompt and value reading, or may be using screen management packages such as curses or S-L a n g), using the Tcl/Tk X Window or Web browser format based on the screen input as complex.

For most people, it is easier to specify the search parameters by entering a certain format than to publish a SELECT statement. For example, if a real estate agent is looking for a house with a certain price range, style, or location, you only need to input the search parameters into the table to get the Matching content at the minimum price. This application is also considered when you enter a new record or update an existing record. The keyboard operator in the data input department should not need to know the SQL syntax such as INSERT, REPLACE or UPDATE.

Another reason for proposing the input collection layer between the end user and the MySQL server is that the input provided by the user can be verified. For example, you can check the data to confirm that they are in a MySQL-compliant format, or you can ask for a specific region.

Customizable output

Mysql outputs are basically unformatted. You can select a tab as the separator or output in the form of a table. If you want to make the output result look better, You must format it yourself. These requirements may be as simple as printing "Missing" instead of "NULL", or more complex. Consider the following report:

This report includes several specific elements:

1. Customize the title.
2. duplicate values in the State column can be printed only when they are changed.
3. Calculate the subtotal and total.
4. digit format, for example, 9 4 3 8 4. 2 4. The number of USD printed is $9 4, 3 8 4. 2 4. Some tasks may not even require any output. You may be inserting computation results to another database table for simple retrieval. In addition to running this query, you may even want to output this result to other places. For example, if you are extracting the name and email address to automatically enter the format of the batch email to generate the mail, the program generates the output. However, the output consists of the information of the email recipient without the information of the running program personnel. It can work in an environment where SQL itself imposes constraints. SQL is not a process language with a process control structure with conditional selection, loops, and subroutines. SQL scripts include a set of executable statements from the beginning to the end, with a minimum error check.

If you use mysql to execute an SQL query file in batch mode, mysql exits after the first error occurs, or, if you specify the -- force option, no matter how many errors occur, all queries are executed without selection. The program can provide process control around the statement so that it can selectively adapt to the query success or failure. You can execute a query based on the success or failure of another query, or decide what to do next based on the results of the previous query. SQL has very limited continuity between statements, which is also brought to mysql. Using the results of one query and applying them to another query, or associating multiple query results is difficult. L a s t _ INSERT _ ID () can be used to obtain the AUTO_INCREMENT value generated by the previous statement, just about it. More generally, it is difficult to retrieve a group of records and then use each record as the basis for a series of complex further operations. For example, retrieving a consumer list and querying the detailed credit history of each consumer may involve several queries for each customer. In some cases, you may want to open an invoice, write the customer information you need to contact in the invoice header, and then list each item in order. Mysql is not suitable for these types of tasks, because it may depend on several queries of the first few query results, and these tasks are beyond the mysql layout design capability. In general, in addition to mysql, tools are also required to execute tasks that include the master-Detail relationship and complex output format requirements. The program provides the "glue" that connects queries, and can use the output of one query as the input of another query.

5. MySQL can be integrated into any application. Many programs use the capabilities of the database to provide information. By publishing a quick query, the application can check the consumer number or whether an entry is in the product list. Assume that a customer is looking for all the books of some authors, the Web application can search for them in the database and then display the results on the client's browser.

By calling the shell script of mysql with an input file containing SQL statements), you can implement a preliminary "integration". Then, use other UNIX utilities to process these outputs. However, this may become ugly, especially when tasks become more complex. As applications grow into messy patching jobs, it may also produce a feeling of "at work, but wrong. In addition, the overhead of the shell script for running other commands may exceed your expectation. However, it may interact directly with the MySQL server more efficiently. When it is required at each stage of application execution, it can precisely extract the desired information. For the sample database samp_db we installed in Chapter 1st "MySQL and SQL Introduction", we have already listed the targets of several programs that require self-writing interaction with the MySQL server. Some of these targets are displayed in the following list:

■ Format the Historical leags directory for printing.
■ Consider looking for the appearance and connection directory.
■ Send supplementary notifications to members by email.
■ Using a Web browser, you can easily enter scores into your credits.
In some details, we will consider one aspect of integrating MySQL capabilities with the Web environment. MySQL does not directly provide support for Web applications. However, by combining MySQL with appropriate tools, We B can easily access the database. You can use the Web server to specify a query and report the results to the client's browser. There may be two ways to combine MySQL and Web:

■ The main interest lies in the database. I just want to use the Web as a tool to obtain easier access to data. In this way, the database location is clear and obvious because it is the focus of interest. For example, you can write a Web page to view the table, table structure, and table content contained in the database. You plan to use a Web server to improve MySQL access capabilities. This may also be the opinion of the MySQL manager.
■ The main interest may be a Web site. To make the site content more valuable to visitors, you may want to use MySQL as a tool. For example, if you run an information board or discuss a list for a visitor to the site, you can use MySQL to retain the information track. In this case, the role of the database is more subtle, and the visitor may not even care about the part that you must provide to him for execution on the server. You plan to use MySQL to improve the Web server capabilities. This may also be a point of view of Web site developers.

These ideas are not contradictory. For example, in Historical League, We want to use We B by allowing online input as a member to get access to the content of the member directory. Database Access is a Web usage. At the same time, leleague's Web site is somewhat incomplete in some aspects, so it adds directory content to the site to improve the value of the site for members. Enhancing the services provided by the site is a database usage.

No matter what you think about the combination of MySQL and Web, the implementation method is similar, that is, connecting the front-end Web site to the back-end MySQL, using the Web server as the media. The Web server sends the query results from the user to the MySQL server, retrieves the query results, and then delivers them to the customer, which is displayed in the browser.

Of course, you do not have to process data online, but it is often advantageous to do so, especially when comparing it with accessing data through a standard MySQL client program:
■ Access data through the Web, and people can use their favorite browsers to run on their favorite platforms. They do not restrict the systems that MySQL client programs run. This is especially true for Web browsers, regardless of the wide distribution of MySQL clients.
■ The Web interface is easier to use than the MySQL client program of the independent command line.
■ The Web interface can be customized according to the requirements of special applications. The MySQL client uses a fixed interface to complete basic functions.
■ Dynamic Web pages expand the capabilities of MySQL, which can be hard or impossible to do with MySQL clients. For example, it is impossible to use only the MySQL client program to combine the integrated vehicle purchase applications into a whole.