Running Perl DBI

Here, we've seen many concepts involved in DBI programming, so let's continue to do some sample databases to handle things. At first, the 1th chapter outlines our goals. This chapter lists the issues that we will deal with by writing DBI scripts.
For the credit retention scheme, we would like to be able to retrieve any given test or test score.
For historical alliances, we want to do the following things:
Generates a member directory in a different format. We would like to use a list of only names in the annual banquet program in a format that can be used to generate the display table of contents.
Look for league members who will soon be updating their membership, and then send an e-mail notification to them.
Edit the Member items (after all, we will update their expiration dates when we update the memberships).
Find members who share common interests.
Bring this directory online.
For some of these tasks, we'll write a script that runs from the command line. Other tasks, we will be in 7. Section 4, "Using DBI in a Web application," creates a script that can be used in conjunction with a Web server. At the end of this chapter, we will still have many goals to be accomplished. The remaining goals will be completed in the 8th chapter, "PHP API".

Build History League Directory

One of our goals is to generate information about the historical League catalogue in different formats. The simplest format we will generate is the list of member names for an annual banquet program. That could be a simple list of unformatted text. It will be a larger document to create this program, so all we need is some content that can be pasted into the document.
For a table of contents that can be displayed, a better representation than unformatted text is required, because we want to format some of the content more finely. A reasonable choice here is RT F, Rich Text format, a format developed by Microsoft that can be recognized by many word handlers. Of course, Word is one of these programs, but many other software, such as WordPerfect and a P L e Work, are also recognizable. Different word handlers also have different levels of support for RTF, but we will use a basic subset of all RTF provisions that are supported by any word processing program that is believed to be the lowest level of RT F.
The process of generating banquet lists and rich-text catalog formats is essentially the same: publishing queries to retrieve these items and then running loops that extract and format each item. Gives the basic similarities  the turbulent Xiong Xiong Torr acenaphthene jue Noisy reef bared zhi  mu malaria k yu  the ザ of the of the E n _ d i r, it can generate output from this directory in different formats. We can organize this script like this:
1 Complete any initialization that this output format may require before writing out the project content. The Banquet program member list does not require any special initialization, but we need to write some initial control language for this rich text version.
2 extract and display each item and format the type we want to output appropriately.
3 after processing all of the items, but also to complete any necessary clearance and termination. In addition to some of the shutdown control languages needed for this RTF version, the banquet list does not require special processing.
In the future, we might want to use this script to write output in other formats, so we set the "Convert box"--Each output format has a hash of the element, making it extensible. Each element specifies a function that generates the appropriate output for a given format: the initialization function, writing the project function, and the purge function are as follows:

Each element of the conversion box is identified by a format name (in this case, "b a n q UE t" and "R T F"). We'll write this script so that you can specify the format you want on the command line when you run it:
% Gen_dir Banquet
% Gen_dir RTF
By setting the converter box in this way, we can easily add performance to the new format:
1 write three formatting functions.
2 adds a new element to the conversion box that points to those functions.
3 to generate output in a new format, call G e n _ D i r and specify this format name on the command line.
The code for the appropriate conversion box item selected according to the first parameter in the command line is shown below. It is because the name of the output format is the keyword in the%switchbox hash. If no such keyword exists in the converter box, the format is invalid. You do not need the hard wired format in this code, and if you add a new format to the converter box, it is automatically detected. If no format name is specified on the command line, or if an invalid name is specified, the script generates an error message and displays a list of allowed names:

If a valid format name is specified on the command line, the preceding code sets $ f UN c _ h a s H r E F. Its value will be a hash reference to the write function that has the format output selected. Then we can run the query for this select item. After that, we invoke the initialization function, extract and display the items, and activate the Purge function:

For some reason, the recycle of the Extraction Project uses Fetchrow_hashref (). If this loop extracts an array, the Format function must know the order of the columns. It may be obtained by accessing the $sth->{name} property (which contains the column name of the return order), but why bother? By using a hash reference, the formatting function can only name the column values that you want to use $entry_ref->{col_name}. That's very inefficient, but it's easy to do and can be used in any format you want to generate, because we know that any domain we need is in the hash.
The rest of the work is to write these functions for each output format (that is, to name the functions by converting the box items).
1. Generate Banquet Program member list
For this output format, we only want the names of the members. You do not need to initialize or purge calls. Only one project formatting function is required:


The Format_banquet_entry () parameter is a hash reference to the column value of the row. This function connects the first name with the last name, plus any suffixes that may appear. The trick here is "J R." or "S R." The suffix should be preceded by a comma or a space, but a space such as "I" or "I I" suffix can be preceded by only one:

Because the letters ' I ', ' V ' and ' X ' cover all the generated numbers, from 1th to 3rd 9, we can use the following test to determine whether to add a comma:

The Format_banquet_entry () code with the name is also some of the content that the RTF version of this directory will need. However, instead of copying the code in Format_rtf_entry (), let's populate it with functions:

Placing a named string in the Format_name () function reduces the format_banquet_entry () function to almost no:

2. Create a table of contents for display format
It is more tricky to generate a rich text version of this directory than to generate a banquet program member list. First, we need to show more information from each project. Second, we need to use each project to generate some rich text control language to do what we want. The minimum framework for RTF documents is this:


This document starts and ends with curly braces ' {' and '} '. The RTF keyword starts with a backslash symbol, and the first keyword of the document must be the version number of the RTF specified by the \ r T f n,n for this document. 0 is more appropriate for our purposes.
Inside this document, we specify the font table to illustrate the fonts used by these items. The font table information is listed in the group and consists of curly braces that contain leading \fonttbl keywords and some font information. The word body described in the frame defines the font number 0 as Ti m e s (we only need one font, but if we want to show it better, we can use a variety of fonts).
Some of the following instructions set the default formatting style: \plain Select unformatted format, \f0 Select font 0 (we've defined it as times in the font table), \fs24 set the font size to 12 dots (the number after \FS indicates the size of the half lattice). Setting margin margins is not required; Most word handlers will provide a reasonable default value.
To get a very simple method, you can display each item as a series of rows with a label on each line. If the information that corresponds to a particular output line is missing, the row is ignored (for example, a member without an e-mail address does not display the "E m a I:" line). Some lines (such as "A D D r e S: rows) are made up of information in multiple columns (street, city, state, postal code), so the script must be able to handle the various combinations of missing values. Here is a sample of the output format we will use:

For the formatted item that is displayed, the presentation of RTF is as follows:

To make the "Name:" Behavior bold, precede it with a \b (followed by a space) to open the bold and close the bold with \ b 0来. Each line has a segment marker (p a R) at the end to tell the word handler to move to the next line--nothing too complicated.
The initialization function produces a leading RTF control language (note that the two backslash symbols get a backslash in the output):

Similarly, the purge function produces a stop-control language (not too much!). ）：
Sub Rtf_cleanup
{
print '}\n ';
}
The real work is about formatting this project, even if the task is relatively simple. The main complex point is to format the address string and determine which output line should be displayed:

Of course, it's not limited to this particular formatting style. You can change the way you display any field, so you can change the displayed directory almost arbitrarily by simply changing Format_rtf_entry (). It's not easy to do something with its original format directory (a word processing document)!
The Gen_dir script is now complete. By running the following commands, we can generate this directory in any of the output formats:
% Gen_dir Banquet > Name.txt
% Gen_dir rtf > Directory.rtf
In Windows, I can run G e n _ D i r, and these files are ready to be used from within the Windows word processing program. In Unix, I can run the above commands, and then send these output files to myself as attachments, so I can get them from my Macintosh and load them into word handlers. I occasionally use the Mutt mailing program, which allows you to specify an attachment from the command line using the-a option. You can send yourself a message with these two additional files as follows:
% mutt-a name.txt-a directory.rtf paul0snake.net
Other mail programs may also allow the creation of attachments. Alternatively, these files can be transmitted in other meanings, such as f T P. In any case, it's easier to read the list of names after the files have been placed where they want to be, and paste them into the annual program document, or read RTF in any word processing program that recognizes RTF. DBI makes it easy to extract the information we want from MySQL, and the ability of Perl's text processing makes it easy to put this information in a specified format. MySQL does not provide any special means of information output, but it does not matter, because it is effortless to integrate MySQL's database processing capabilities into Perl's language, which has excellent text processing capabilities.

Send Membership Update Notification

When maintaining a History league directory as a word-processing document, it is time-consuming and error-prone to determine which member should be notified of its membership should be updated. Now that we have information in the database, let's see how to automate the process of updating notifications. We want to identify the members that need to be updated by email so we don't have to go through the power
Words or mails to contact them.
What we need to do is to determine which member is going to be up to date in some days. This query involves a relatively simple date calculation:
SELECT ... From member
WHERE Expiration < Date_add (Current_date,interval cutoff day)
C ut o FF indicates the number of days we agree to the allowable error. This query selects member items that are quickly updated within a few days. As a special case, the endpoint value is 0, looking for a member whose expiration date has passed (that is, those members that are actually terminated).
After we have identified these records for limiting notifications, what should we do about them? One option is to send the message directly from the same script, but it may be useful to review the list without sending any messages first. For this reason, we will use a two-phase approach:
Phase 1: Run the script Need_renewal to identify the member that needs to be updated. You can check the list or use it as an input to send an update notification to phase 2nd.
Phase 2: Run the script r e N e w A l _ N o t i f y, which sends "please update" notifications to members via email. This script should notify you that you do not have a member with an e-mail address so that you can contact them in other ways.
In the first part of this task, the Need_renewal script must identify which member needs to be updated. Its operation is as follows:

The output of the Need_renewal script is shown below (because it is the result of the current date, and the time you read the book and the time I write it will be different, so I will get different output)

As you can see, those who are in a negative number of days need to be updated. Negative numbers mean that we are out of date (this can happen when records are manually maintained; some people slipped out of the gap.) Now that we have this information in the database, we are looking for a few people missing in front of us!
The second part of the update Notification task involves a script that sends notifications via email r e N E w A l _ N o t i f y. To make renewal_notify easier to use, we can enable it to support three types of command-line arguments: The membership ID number, the e-mail address, and the file name. The parameter of the numeric value represents the membership ID value, and the parameter with the character ' @ ' indicates the address of the e-mail message. Everything else is interpreted as the name of the file that should be read in order to find their ID number or e-mail address, either directly on the command line or by listing them in a file (in particular, you can use Need_renewal output as a renewal_notify Input).
For each member to send a notification, this script looks for the corresponding membership table item, extracts the e-mail address, and sends a message to that address. If there is no e-mail address in this entry, Renewal_notify generates a message informing you that you need to contact these members in some other way.
To send an e-mail message, renewal_notify open the pipe to the SendMail program and push the message into the pipeline (this cannot be done under Windows), and there is no S e n d m in Windows. You may need to look for a module to send the message instead of using it. Near the beginning of this script, set the path name to SendMail as a parameter. You may need to change the path because the location of the SendMail changes as the system changes:
# change path to match your system
My ($sendmail) = "/usr/lib/sendmail-t-oi";
The operation of the main parameter processing loop is shown below. If no arguments are specified at the command line, we read the standard output as input. Otherwise, by passing the parameter to i n ter P r e T _ a rgument (), classify it as an ID number, an e-mail address, or a filename to handle each parameter:

The function read_file () reads the contents of the file (assuming it is already open) and looks at the first field in each row (if we use the output of Need_renewal as renewal_notify input, there are several fields per row, but we only want to see the first field).

i n ter p r e T _ a rgument () function classifies each parameter to determine whether it is an ID number, an e-mail address, or a file name. For an ID number and an e-mail address, it looks for the appropriate member item and passes it to n o t i f y _ (). We must pay attention to the members specified by the email. It is possible for two members to have the same address (for example, husbands and wives), and we do not want to send a message to someone who cannot use the message. To avoid this, we looked up the ID number of the member corresponding to the e-mail address to ensure that the content was correct. If this address matches more than one ID number, it is indeterminate and we ignore it after displaying a warning message.
If the parameter does not look like an ID number or an e-mail address, read it as a file name for further input. Here, we must also be careful--to avoid the possibility of infinite loops, if we have read a file, we do not want to read the file again:

In fact, the code for the Notify_member () function that sends update notifications is shown below. If you conclude that this member does not have an e-mail address, do nothing, but notify_member () displays a warning message to know that you need to contact that member in some other way. You can call S H o-member with the membership ID number shown in this message to view all items-for example, to find the phone number and mailing address of this member.

It might get better content-for example, by adding a column to the member table to record when the prompt for the latest update was sent. Doing so will help avoid sending notifications too frequently. In fact, we simply assume that there are no programs running more than once a month.
Now run the two scripts so that they can be used:
% Need_renewal > Junk
% (take a look at the junk and check if it's reasonable)
% renewal_notify Junk
To notify individual members, you can specify them by ID number or e-mail address:
% NEED_RENEWL g.steve@pluto.com

Historical League Member Project editor

After we start sending update notifications, it is safe to assume that some of the people we notify will update their membership. When this happens, we will need a way to update the new end date item that it has. In the next chapter, we'll develop a way to edit member records on a Web browser, but here we'll build a life
The line script e D i t _ member allows you to update the project with the method of the new values for each part of the prompt. It operates as follows:
If no arguments are invoked on the command line, Edit_member assumes that you want to enter a new number, prompt for the initial information in the member project, and create a new project.
If you call on the command line with a member ID number, Edit_member finds the contents of the item and then prompts you to update each column. If you enter a column's value, it replaces the current value. If you press ENTER, the column does not change (if you do not know the ID number of the member, you can run Show_member last_name to find its contents).
If you only want to update the end date for a member, this way of allowing all items to be edited may be an unnecessary overreaction. A script like this, on the other hand, also provides a simple general-purpose way to update any part of a project without having to understand any knowledge of SQL (a special case where Edit_member does not allow you to change the member_id domain, because when you create a project, you automatically assign the domain, and cannot be changed in the future).
The first thing edit_member need to know is the names of these columns in the member table:

Then we can enter the body loop:

The code for creating a new member project is shown below. It requests each member table column, and then publishes an INSERT statement to add a new record:


The prompt routines used by New_member () are as follows:

The reason for Col_prompt () with the $show_current parameter is that when the script is used to update the project, we also use this function for the column values of the existing member project request. When you create a new project, the $show _current will be 0, because there are currently no values to display. When you edit an existing item, it is Non-zero. The prompt in the latter case will display the current value, and the user can simply press ENTER to accept it.
Code that edits an existing member is similar to the code that creates a new member. However, we have an operable item, so the prompt routine displays the value of the current item, and the Edit_member () function publishes an UPDATE statement instead of an INSERT statement:

The problem with Edit_member is that it does not validate any input values. For most domains in the member table, there is no checksum-they are just string fields. But for expiration columns, you should actually check the input values to make sure they look like dates. In a general purpose data entry application, you may want to extract information about the table to determine the type of all its columns. Then, you might be able to verify by the constraints on those types. That's more than what I'm looking for here, so I'll just add a quick method to the Col_prompt () function to check the format of the input if the column is named "E x P i r a t i o n". A minimum date value check can be done this way:


This template tests the number of three sequences separated by non-numeric characters. This is only part of the check because it does not detect the value of "1999-14-2 2" as invalid. To make the script better, you should give it a tighter date check and other checks, such as a field that requires a first and last name, and a non-null value.
Some other improvements may be that if the column is not changed, skip the update and notify the user if someone else has changed the record when they are editing it. You can do this by saving the original data from the Member Project column, and then writing the UPDATE statement to update only those columns that have changed. If not, you do not even need to publish this statement. Similarly, for each original column value, you can write a WHERE clause to include a N D col_name = col_val. If someone else has changed this record, this could cause the update to fail, at which point the feedback is that two people have to change the project at the same time.

Historical League members looking for common interest

One of the responsibilities of the Secretary of History is to deal with members ' requests, which may require a list of other people who are interested in special times or special characters in the American history field, such as in the Great Depression or Abraham Lincoln's life. When maintaining this directory in a word-processing document, you can easily find such a member by using the "F i N" feature of the word processor. However, it is difficult to produce a list of items that contain only qualified members, as it involves a large number of copies and pastes. With MySQL, it's much easier to work, because we can run only the following queries:
SELECT * from member WHERE interests like "%lincoln%"
ORDER BY Last_name,first_name
Unfortunately, if you run this query in the MySQL client program, the results don't look very good. Let's put together a small number of DBI scripts and interests that produce more beautiful output. First, check the script to make sure that there is at least one named argument on the command line, because without a named parameter there is no content to search for. Then, for each parameter, the script runs a query on the interests column of the member table:

To search for a keyword string, we put the wildcard '% ' on each side so that it can be found anywhere in the interests column. Then, we show the matching items:


The Format_entry () function is not present here. It is essentially the same as the function format_rtf_entry () of the Gen_dir script, but the Format_entry () function removes the RTF control word.

Online History League Catalog

In the 7. In section 4, we'll start writing a script to connect to the MySQL server and extract the information, as well as to write the information that appears in the client's Web browser as a Web page. Those scripts dynamically generate the H T M L in accordance with the client request. Before we get to that point, let's write the static in the Web server document tree by writing a build that can be loaded
The DBI code for the HTML document begins to consider the relevant H T M L. The Historical League directory created in HTML is the best choice, because one of our goals is to bring the catalog online anyway.
In general, HTML documents are a bit like the following structure:

In order to generate a directory in this format, it is not necessary for you to write a complete script. Recall that when we wrote the Gen_dir script, we used an extensible framework, so we inserted the code in order to produce a directory in another format. This means that if the code generates HTML output, we need to write a function that initializes and clears the document, as well as formatting individual items. Then we need to create the transformation box elements to point to these functions.
The online documents that are displayed are easily decomposed into the preamble and end parts that can be processed by the initialization function and the purge function, and the middle part generated by the project formatting function. The HTML initialization function generates each part of the level 1 header, and the Purge function generates the part that closes the </BODY> and </HTML> tags:

In general, the real job is to format the project. But even this is not too difficult. We can copy the Format_rtf_entry () function, make sure any special characters in the project are encoded, and replace the RTF control Word with the HTML-flagged flag:

Now we'll add another element to the conversion box, point to the function of writing HTML, and complete the correction to G e N _ D i r:

To produce an HTML-formatted directory, run the following command and install the resulting output file in the Web server's document tree:
% Gen_dir HTML > directory.html
When you update the catalog, you can run the command again to update the online version. Another scenario is to establish a cron job that executes periodically. That is to say, the online catalog will be automatically updated. For example, I might use a crontab item like this to run at 4 o'clock in the morning every day G e N _ D i r:
04****/u/paul/samp_db/gen_dir>/usr/local/apache/htdocs/directory.html
Users running this cron job must allow them to execute scripts in the samp_db directory and write the files to the Web server's document tree.