How to use the API in MySQL

5.2 Select API
This section describes the ability to compare the C, DBI, and PHP APIs based on the methods of selecting a P I for various types of applications, giving them relative advantages and disadvantages, and indicating when to choose.
First of all, it should be pointed out that the author does not consider any language superior to other languages. Although I do have their own preferences, but still use them all. You will also have your own preferences, like my critics. One critic would feel the importance of C for MySQL programming should be emphasized to a greater degree, while another critic would argue that C
Programming is very difficult, should put  ∧φ than Ê Jue embarrassed Zhang annoyed Wu ┮, Meconematidae Wu-Wu , about drought Murian di t 诙 Yun Fontanel ㄈ i mounted patchwork 鯝 Pi, to consider the following questions:
The expected execution environment. Expect to use the context environment of the application.
Performance When written in the API language, how to make the application execute efficiently.
The ease of development. How to easily write applications in the API and its language.
Portability Whether or not the application will be used in other database systems other than MySQL.
The following is a further analysis of each issue. Attention should be paid to the interplay of these factors. For example, you want a well-run application, but it is equally important to use a language that quickly develops the application, even if the application does not run very efficiently.
5.2.1 Execution Environment
When writing an application, you should usually consider which environment to use. For example, the application might be a Report Builder program invoked from a shell, or an Accounts payable profile, run as a cron job at the end of each month. Commands that run from a shell or cron program often depend on themselves and rarely require a running environment. Alternatively, you can write an application that attempts to be invoked by a Web server. Such a program expects to extract very special types of information from its operating environment: What browser is the customer using? What parameters are entered in the mailing list subscription request format? Does the customer provide the correct password to access our personal information? Each API language varies with its suitability for writing applications in these different environments:
C is the language of the universal target, which can theoretically be used for any task. In practice, C tends to be used for more frequent stand-alone programs than for Web programming. This may be due to the fact that text processing and memory management are not as easy to implement in C as in Perl or in PHP, and that these processes and management are used extensively in Web applications.
Perl, like C, is suitable for writing stand-alone programs. However, Perl is also useful for Web site development, for example, by using the CGI.PM module. This makes Perl a convenient language for writing applications that connect to MySQL and the web. Such applications can be CGI.PM modules and web interfaces, and can interact with MySQL using DBI.
PHP is a language designed to write Web applications, so this environment is clearly the most appropriate. Also, database access is one of the biggest advantages of PHP, so it is the natural choice for Web applications that implement MySQL-related tasks. You can also use PHP as a standalone interpreter (for example, to run a script from a shell), but you can't do it very frequently.
Based on these considerations, C and Perl are the best languages for stand-alone applications. For our B applications, Perl and PHP are the most appropriate. If you need to write both types of applications without using any of these languages and want to learn with as little effort as possible, Perl may be your best choice.
5.2.2 Performance
We usually like applications to run as fast as possible. In fact, however, the importance of performance depends on the frequency of the program being used. Performance may not be very important for a program that runs at night for one months. For programs that run several times a second on a Web site, it can be a huge difference to exclude a bit of inefficiency. Latter kind
, performance plays an important role in the effectiveness and request of the site. A slow site is annoying to the user, regardless of the content of the site, if you rely on the site as a source of revenue, the performance of the reduction of direct impact on revenue. If you can't service multiple connections at once, visitors will only get bored and go to other sites.
Performance evaluation is a complex problem. When writing a specific API, the best indicator of how well the application is done is to write and test in this API environment. And the best comparison test is to run the application multiple times under different API environments to compare each version. Of course, it's not a normal job. In general, you just want to get the application written
Program. Once it's working, if it needs to run faster, you can consider optimizing it, using less memory, or some aspect that needs to be improved in other ways. However, there are at least two factors that can affect performance:
A compiled program runs faster than an interpreted program.
For interpreted languages that are used in a Web context environment, performance is better when the interpreter is invoked as part of the we server itself rather than as a separate process module.
1. Compilation language relative to the interpretation language
A compiled application is more efficient, uses less memory, and executes faster than the same version of a program written in a scripting language, which is the basic rule. This is due to an overhead problem with the interpreter for the language that executes the script. Because C is compiled, and Perl and PHP are interpreted, C programs usually run faster than Perl or PHP scripts. For a large number of applications, C is usually the best choice. The MySQL command line client program included in the MySQL distribution is the best example.
There are, of course, a number of factors that can make this apparent difference smaller. For a task, you can write faster programs in C, but it is also possible to write inefficient C programs. Programs written in the compiled language do not automatically guarantee better performance. So you need to keep thinking about what you are doing. In addition, if a scripted application spends most of its time executing code that connects to the MySQL client library routine that is connected to the interpreter engine, the difference between the compiler and the interpreter will be reduced.
2. Independent programs relative to the module version of the language interpreter
For web-based applications, scripting language interpreters are usually used in one of two forms, at least for Apache, and when writing Web applications, Apache is the Web server we will use:
Apache can be arranged to invoke this interpreter as a separate process. When Apache needs to run a Perl or PHP script, it launches the appropriate program and tells it to execute the script. In this case, Apache uses the interpreter as a CGI program, that is, it communicates with them using the Common Gateway Interface (Common Gateway Inter face,cgi) protocol.
An interpreter can be used as a module to directly connect to the Apache binaries and run as part of its process itself. In Apache terms, Perl and PHP interpreters get the form of Mod_perl and MOD_PHP3 modules.
The advocates of Perl and PHP strongly preach that the interpreter has a speed advantage, but all agree that the reason why it likes to interpret the program is because it runs in a way that is much more tempting than the language itself. In both cases, the interpreter runs as a module faster than a stand-alone CGI application.
For stand-alone applications, the interpreter must be started whenever a script is run, which will result in the overhead of a significant creation process. When used internally as a module in the Apache process already running, the interpreter can be accessed from the Web page immediately. Dramatically improves performance by reducing overhead and translates directly to fast processing of fetched request concurrency
Increase in the ability to send them.
Standalone interpreter startup performance is at least one order of magnitude worse than the performance of the module interpreter. The overhead of interpreting a program's startup is particularly important when considering the Web page service, which includes a small amount of processing for fast transactions rather than a lot of processing. If you spend a lot of time just to start instead of actually executing the script, most of the resources are in the waiting state. Most of the day may be spent on preparation, 4 o'clock arrives and then home at 5 o ' hour.
You might want to know why the module version of the interpreter saves time because it has to start Apache at all times? The reason for this is that when Apache starts, it immediately generates some child processes to handle the requests received. When a request including a script execution arrives, there is already an Apache process ready to wait to process it. Similarly, each instance of Apache can serve multiple requests, so the cost of starting the process only causes each group of requests to be requested once rather than once for each request.
When Perl and PHP are installed as modules (like Mod_perl and M o D _ P 3), which one completes better? That is the subject of the controversy, and the following are guidelines for general use:
Perl transforms the script into an internally-compiled form, but PHP does not. Therefore, once the script is parsed, Perl can execute it more quickly, especially for loops with a large number of iterations.
Mod_perl can run script caching to improve the performance of scripts that are repeatedly executed. If the script is in the cache, Perl can start executing the script faster because it does not need to be parsed again. Otherwise, PHP starts executing the script faster.
Mod_perl has a larger memory footprint than PHP, and an Apache process that utilizes mod_perl connections is larger than the use of mod_php3. PHP is assumed to be in synergy within another process, and can be activated or undone multiple times within that process. Perl is designed to run as a stand-alone program from the command line, not as a language that is embedded in the Web server process. This may make it pay a larger memory footprint;
Perl is a module, so it does not run in its own environment. The script's cache and the additional Perl modules used by the script are additional factors that pay a larger memory footprint. In both cases, more code uses memory and keeps the running Apache process in memory.
In terms of the speed at which the script runs, Perl is eliminated by PHP 4, regardless of its advantages over PHP. PHP 4 is similar to PHP 3 in terms of its capabilities and interfaces, but it incorporates zend,zend as the engine for a more high-performance interpreter.
In any case, all of these factors only lead to a different performance ratio between Perl and PHP's module versions. Whichever language you choose, the most important thing is to avoid an independent interpreter as much as possible.
A standalone version of the interpreter does have an advantage over its version of the module, that is, it can be scheduled to run under a different user ID. The module version is always running under the same user ID as the Web server, and for security reasons, the user is a typical account with very little permissions. does not work well for scripts that require special permissions (for example, if you need to be able to read and write protected files). If you want, you can combine a modular approach with a stand-alone approach: using the module version by default, and using a standalone version in the case of a script with a specific user's permissions.
Reduce the Mod_perl memory requirements
Some techniques allow you to use only certain Apache processes for mod_perl. This creates an extra memory overhead for only those processes running the Perl script. The Mod_perl section of the Apache Web site has a variety of options to discuss (for more information, see H t t P://per L. A p a c h e. O rg/g UI D E/). In summary, that is to say, to choose Perl or PHP, you should try to use it from the Apache module rather than by invoking a separate interpreter procedure. Use separate interpreters for situations that cannot be handled by the module, such as scripts that require special permissions. For these instances, you can use the Apache suexec mechanism to start an interpreter under a given user ID to process the script.
5.2.3 Development Time
These factors just described affect the performance of your application, but you cannot only consider operational efficiencies. The simplicity of time and programming is also important, so another factor to consider when choosing an API for MySQL programming is how to quickly develop your own application. If you develop the same program, with a Perl script that takes only half of the time in C, you might prefer to use the Perl DBI API rather than the C API, even if the application you are developing isn't running very fast. It is often reasonable to consider a program running less time than it takes to consider writing a program, especially for applications that do not run frequently. Your hour is much more valuable than the one hour of the machine!
In general, scripting language writing programs are faster, especially for applications that have prototypes faster, for the following two reasons:
First, scripting languages provide a higher level of architecture. This allows you to think at a higher level of abstraction to focus on what to do rather than what to do. For example, an associative array (hash) of Perl saves a lot of time maintaining data with key/value systems such as Student ID/student name pairs. C does not have such a structure. If you want to do this in C, you might need to write code to handle a lot of low-level details, including memory management and string manipulation issues, and it takes time to debug it.
Second, there are fewer steps in the development cycle of scripting languages. When you develop an application in C, you go through the usual edit-compile-test cycle. Each time you modify a program, you must recompile it before testing. In Perl and PHP, the development cycle can be simplified as an edit-test because the script can be run immediately after each modification without compiling. On the other hand, the compiler has more constraints on the strict type-checking form of the program. More constraints imposed by the compiler help avoid errors that are not easily captured in loose languages such as Perl and PHP. In C, if you misspelled the name of a variable, the compiler warns you. PHP is not so, Perl is not so, unless you ask it. These stricter constraints can be particularly useful when applications become larger and more difficult to maintain.
In general, the tradeoff between compiling and interpreting a language is the tradeoff between development time and performance: Is it possible to use a compiled language to develop a program so that it can be faster at run time, but spend more time writing it? Or do you want to write a program in a scripting language to shorten the programming time, but lose some speed?
It is also possible to combine the two methods. Write a script as the "first draft" to quickly develop an application prototype to test its logic and determine the availability of the algorithm. If the program works and is used frequently, performance becomes the focus of concern, and you can write code back to it as a compiled application. This gives you the advantage of two ways to quickly get the initial development prototype of your application and get the best performance of the final product. In some strict sense, the Perl DBI and PHP APIs do not give you the ability to do so in the C client library. This is because both APIs are connected to Perl and PHP interpreters via the MYSQLC library to gain access to MySQL. However, for environments embedded in MySQL, C is a lot different from Perl or PHP. Consider what to do when interacting with a MySQL server and ask how each API language can help you accomplish these things. Here are some examples:
Memory management. In C, you find yourself working with malloc () and free () for any task, including dynamic allocation of data structures. Perl and PHP can handle these tasks for you. For example, the size of an array is automatically increased, and dynamic length strings can be used without regard to memory management.
Text processing. At this point Perl has the greatest development capability, while PHP is in the second. In comparison, C is very elementary. Of course, you can write your own library in C to encapsulate tasks such as memory management and text processing into functions that are easier to work with. However, you must then debug them, and you also want to make your algorithms more efficient. In both respects, it is almost certain that the algorithms in Perl and PHP are generally easy to debug and reasonably efficient because they already have the benefit of checking through many pairs of eyes. You can save time by taking advantage of other people's work (on the other hand, if the interpreter occasionally has an error, you may have to carry it until the problem is corrected.) When written in C, you can control the performance of the program more carefully.
The difference in these languages is also in their "security". The C API provides the lowest level of interface to the server and has the least mandatory principle. In this sense, it provides the lowest level of security network. If you execute API functions in a normal order, you may get a "Out of sync" error or cause the program to crash. Both Perl and PHP provide good protection. If you do not do so in the proper order, the script fails, but the interpreter does not crash. Another very likely source of crash errors in C programs is the use of dynamically allocated memory and pointers associated with them. Perl and PHP handle memory management for you, so your scripts are rarely paralyzed by memory management errors.
Development time is affected by external support for language availability. C available external support is the form of a wrapper library that encapsulates the MYSQLC API function as an easier to use instance. These libraries are available for both C and C + +. Perl undoubtedly has the largest number of additional software, all of which are in the form of Perl modules (similar to the concepts in the Apache module). There is even an infrastructure designed to easily locate and capture these modules (that is, the Integrated Perl Archive Network comprehensive Perl Archive Network, CPA N). Using the Perl module, you can get access to all types of functions without writing code. Would you like to write a script that generates reports from a database, and then send it to someone as an attachment? As soon as you get one of the MIME modules, you have the ability to build attachments immediately.
PHP does not have the same level of external support (this is not surprising because it is a newer language). Perhaps the best add-on software you know is the PHP base library (PHP base library,php LIB). Based on some sort of name and password mechanism, suppose you are writing a Web application that needs to qualify only authorized users to access a Web page. You can write a support program for it in any language, but if you use PHP L B, you don't have to take the time to redo this thing. PHP L I B provides confirmation and allows you to track authorized users through a session (a hit from a continuous page in a given client that is part of a single logical access). You can also assign permissions to users, which allows you to work with administrative users who define more permissions.
5.2.4 Portability
The question of portability has to do with how programs written to access the MySQL engine can be easily modified for programs that use different engines. You may not be worried about this thing. However, unless you can predict the future, say "I will never use this program on any other database except MySQL" may be a bit risky: suppose you find another
Job and want to use your old program, but does your new boss use a different database system? If portability is a priority, consider the difference between APIs:
The DBI API is the best portability because it is independent of the database and is an explicit goal of the DBI design.
PHP is slightly less portable because it does not provide the same type of unified interface for the various database engines provided by DBI. The invocation of each PHP function that supports the database is similar to those in the corresponding base C API. Slightly different, but very little, you need to change the name of the database-related function you called. It is also possible to modify the logic of the application, since the excuses for different databases do not all work the same way.
The C API provides the least portability between databases. Because it was born for MySQL design. When it is necessary to access multiple database systems in the same application, it is particularly important to be independent of database portability. This may include simple tasks like moving data from one RDBMS to another, or more complex tasks, such as generating reports based on information obtained from many database systems.
Both DBI and PHP provide support for accessing multiple database engines, so it is easy to connect to the server at the same time on different databases and even on different hosts. However, DBI and PHP differ in the suitability of the task of retrieving and processing data from multiple heterogeneous database systems. DBI is better because it uses a separate set of access calls, regardless of which database is used. Suppose you want to transfer data between MySQL, mSQL, and Postgres databases. Using DBI, the only difference between using these three databases is the DBI-> Connect () call that is used to connect to each server. When you use PHP, you might want to have more complex scripts that will contain three sets of read calls and three write calls.
An excellent example of a multiple database application is the Crash-me script in the MySQL distribution, which tests the capabilities of many different database servers. The script is written in DBI, and for such an application, this choice is obvious because you can access all the databases in the same way.