This article describes MySQL, an effective tool for developing electronic trade and other complex and dynamic websites using third-party databases. MySQL is a fast, multi-threaded, and fully functional SQL Server. In addition to describing the basic architecture of the MySQL system, this article also provides simple examples written in Tcl and C ++ to help you develop Web applications that support databases. A must-be-stored or Linux tip
This article describes MySQL, an effective tool for developing electronic trade and other complex and dynamic websites using third-party databases. MySQL is a fast, multi-threaded, and fully functional SQL Server. In addition to describing the basic architecture of the MySQL system, this article also provides simple examples written in Tcl and C ++ to help you develop Web applications that support databases.
An application that must store or access a large amount of information can benefit from using third-party database products. This is especially true when access to information must be performed on multiple instances of the program. Web-based applications (including electronic trade) are good examples.
Why is an independent database used?
The Web server must have a processing script to store the status information for future access. Although it is possible to use more primitive methods-such as dumping to text files or developing self-made mini databases-only mature database applications can provide all the services required by more complex Web applications. Because some free software packages can be used for this purpose, writing custom application-specific database engines is not very advantageous. In addition, the use of third-party databases also frees Web developers from investing in database development and maintenance tasks.
MySQL database
By using the script language and compiled system language (such as C), it is quite easy to integrate the database into a Linux application. MySQL databases available for free (released under the GNU Public License) provide a series of complex SQL functions and are easy to integrate into applications. MySQL is fast and multithreading and supports ANSI and odbc SQL standards. With third-party software, MySQL supports transaction security tables for transaction processing applications.
Note: What is transaction processing?
A transaction is a series of changes made to the database that need to be performed in an atomic manner. Either they must be executed in full or none of them. For example, when selling products on the Web, all necessary database changes constitute a transaction.
The database needs to deduct the customer account balance and product inventory at the same time, otherwise the operation fails and is not executed.
No matter what causes the server to crash, the transaction should not be partially executed. For example, billing calculation, product delivery, or inventory inaccuracy may be the result of partially completed transactions.
A database that supports transaction processing can encapsulate a set of database code in a transaction. any failure during transaction execution will cause the database to roll back to the state before the transaction starts.
This is achieved by maintaining logs of all database operations and copies of their original status tables. rollback is allowed when the server is restarted next time after a failure. This time and space overhead is a necessary compromise for the transaction security database system.
A single MySQL Server controls a series of databases, which can be accessed through the server in a similar way. Each database is actually a group of any number of tables, similar to other SQL database users. Each table is composed of data columns with data types. The data can be an integer, real value, string, or other type, including the original binary stream. Each row in the table is a record stored in the database.
MySQL is designed and constructed as a client/server. The server mysqld can run on any machine that can access the Internet (preferably on the same or nearest machine as the Web server to ensure a reasonable response time ). The MySQL client uses a request to contact the MySQL server to modify or query the database owned by the server. In Web applications that support databases, database clients are Web servers or CGI scripts generated by Web servers. These clients can be written in advanced scripting or low-level system languages, as long as there is a database API in this language. In Linux, most scripting languages are implemented in C. because MySQL C APIs exist, it is easy to add MySQL support to any existing scripting language or tool. Most scripting languages have completed this step.
MySQL API
MySQL APIs can be used in a variety of languages, including almost all languages used to compile the website backend. Using these APIs, we can build MySQL clients controlled by Web servers.
APIs (for database access) work in connection-based mode. The first thing the client must do is to open the connection with the MySQL server. This includes using the username and password recognized by the server to authenticate the connection. After a connection is established, the server selects the specific database to be used. After initialization is confirmed, the client application (for example, the CGI script on the server side) can freely interact with the database in one of the two ways: you can run regular SQL commands, including adding and deleting tables and adding records to them. you can also query the databases that return results. The query generates a set of records that match the query. then, the client can access the records one by one until all the records are viewed, or the client cancels the temporary record retrieval. Once the script completes database operations, the connection to the server is closed.
To build a website accessed by an integrated database, you need to write a CGI script to generate dynamic results based on the database status. The Web server starts the CGI script and then outputs properly formatted HTML to their standard output stream. The Web server captures HTML and sends it back to the client, as if the request was for a static HTML page. During the HTML generation process, the script can modify the database or query and merge the results into their output.
As an example of a simple explanation of the above process, the following code (written in C and Tcl) queries a database that contains a list of products sold by a company. This does not use all the features of the MySQL API in the two languages, but provides examples of fast and simple scaling that can execute any SQL commands on the database content. In this example, the script shows all products that are lower than the specified price. In practice, users may enter the price in a Web browser and send it to the server. We do not need to read from environment variables to determine the details of HTML form values, because it is no different from the execution in CGI scripts that do not support databases. For clarity, we assume that specific parameters (such as the price to be queried) are set in advance ).
The following code uses the free Tcl Generic Database Interface to implement it in Tcl. The advantage of such an interface is that Tcl is interpreted and can quickly develop and modify code.
Tcl example
# This code prints out all products in the database
# That are below a specified price (assumed to have been determined
# Beforehand, and stored in the variable targetPrice)
# The output is in HTML table format, appropriate for CGI output
# Load the SQL shared object library. the Tcl interpreter coshould also
# Have been compiled with the library, making this line unnecessary
Load/home/aroetter/tcl-SQL/SQL. so
# These are well defined beforehand, or they cocould
# Be passed into the script
Set DBNAME "clientWebSite ";
Set TBLNAME "products ";
Set DBHOST "backend.company.com"
Set DBUSER "mysqluser"
Set DBPASSWD "abigsecret"
Set targetPrice 200;
# Connect to the database
Set handle [SQL connect $ DBHOST $ DBUSER $ DBPASSWD]
SQL selectdb $ handle $ DBNAME; # get test database
# Run a query using the specified SQL code
SQL query $ handle "select * from $ TBLNAME where price <= $ targetPrice"
# Print out html table header
Puts"
"Puts"
| Product Id |
Description |
Price (\ $ )"
# Output table rows-each fetchrow retrieves one result
# From the SQL query
While {[set row [SQL fetchrow $ handle]! = ""}{
Set prodid [lindex $ row 0]
Set descrip [lindex $ row 1]
Set price [lindex $ row 2]
Puts" |
| $ Prodid |
$ Descrip |
$ Price"
}
Puts" |
"
# Empty the query result buffer-shoshould already be empty in this case
SQL endquery $ handle
# Close the db connection-in practice this same connection
# Is used for multiple queries
SQL disconnect $ handle
The following code is equivalent scripts written in C ++ using the official MySQL C ++ API MySQL ++. The advantage of this version is that it is compiled and therefore faster than the interpreted language. Database code that is often used on a specific site should be written in C or C ++ and then accessed by scripts or directly by Web servers to improve the overall running time.
C ++ example
# Include
# Include
# Include
Const char * DBNAME = "clientWebSite ";
Const char * DBTABLE = "products ";
Const char * DBHOST = "backend.company.com ";
Const char * DBUSER = "mysqluser ";
Const char * DBPASSWD = "abigsecret ":
Int main (){
Try {
// Open the database connection and query
Connection con (DBNAME, DBHOST, DBUSER, DBPASSWD );
Query query = con. query ();
// Write valid SQL code to the query object
Query <"select * from" <DBTABLE;
// Run the query and store the results
Result res = query. store ();
// Write out the html table header
Cout <"
\ N ";Cout <"
| Product Id |
Description"
<" |
Price ($) "<endl;
Result: iterator curResult;
Row row;
// Iterate over each result and put it into an html table
For (curResult = res. begin (); curResult! = Res. end (); curResult ++ ){
Row = * curResult;
Cout <" |
"<Row [0]
<" |
"<Row [1] |
