Creating a Phonebook from the Command Line

Sometimes it is useful to prototype with command-line programs before getting involved with user-interface details. This program offers a simple way of adding records. It loops prompting for data about a person. For each person, it loops

prompting for phone data. Each loop ends when no data is entered for a required field.

Here’s the code.

01: #!/usr/bin/perl -w 02: # 03: # program 14-1, Chapter 14, Listing 1 04: #       05: use DBI; 06: use strict; 07: my $Connection = DBI->connect     (‘DBI:mysql:test’,’bookuser’,’testpass’) 08:     or die "Can’t connect to database\nError: $DBI::errstr\nExiting";

Lines 1–8 start the program in the usual way, loading the DBI module and enabling warnings and the strict pragma. Then they get a connection to the database or exit with an informative error message.

09: my $Query_statement = undef; 10: my $Person_insert_statement = undef; 11: my $Phone_insert_statement = undef;

Lines 9–11 define global variables used to store statement handles. You’ll see why they are global later on.

12: my %Fields = ( 13:     id           => {required => 1, label => ‘ID’         }, 14:     first_name   => {required => 1, label => ‘First Name’   }, 15:     last_name    => {required => 0, label => ‘Last Name’    }, 16:     greeting     => {required => 0, label => ‘Greeting’   }, 17:     phone_type   => {required => 1, label => ‘Number Type’}, 18:     phone_number => {required => 1, label => ‘Number’     }, 19:     note         => {required => 0, label => ‘Note’       }, 20: );

Lines 12–20 define another global that holds useful information about the data the program uses. In addition to keeping a nice label for each column, this version stores whether or not the field is required; we’ll use this for validating the input. The advantage of variables like this is that, if you add more columns to the database and to this variable, the program should just work with the new variables.

21: my @Person_prompts = qw(first_name last_name greeting); 22: my @Phone_prompts = qw(phone_type phone_number note); 23: my @Phone_cols = (‘id’, @Phone_prompts);

Lines 21–23 define more globals that control how the program works: the list of columns to prompt for and the columns to INSERT into the phone table.

24: while ( my %person = input_person() ) { 25:     while ( my %phone = input_phone() ) { 26:         $phone{id} = $person{id}; 27:         add_phone( %phone ); 28:     } 29: }

Lines 24–29 are the heart of the program. While the user provides another set of person data and while he or she types more phone details, these lines make sure the person.id is copied to the phone.id; then they INSERT the phone record. There’s slightly more than that happening, but it’s hidden away in the input_person and input_phone subroutines. Both subroutines are expected to return a hash of the data the user types. Each while condition is true if the hashes contain data. The condition ends when the hash is empty, such as when the user simply hits enter without any data.

30: $Connection->disconnect; 31: exit 0;

Lines 30–31. When the user has finished entering numbers and people, close the connection to the database and end the program.

32: sub input_person { 33:     my %person;       34:     print "Enter person details, (Fields marked with * are required).\nTo exit program, hit CTRL-D.\n";

Line 32 starts the routine to collect the information needed for the person table.

Line 33 declares a hash used to store the data using the column names as keys.

Line 34 prints out some basic instructions to the user.

35:     foreach my $col ( @Person_prompts ) { 36:         my $req = $Fields{$col}{‘required’} ? ‘*’ : ‘’; 37:         print "$Fields{$col}{‘label’}$req:\t"; 38:         my $line = <STDIN>; 39:         return    unless defined $line;    # no input 40:         chomp( $line ); 41:         if ( $Fields{$col}{‘required’} and $line !~ /\S+/ ) { 42:             warn "Required field missing, please re-enter\n"; 43:             return; 44:         } 45:         $person{$col} = $line; 46:     }

Lines 35–46 make up the loop that prompts for, reads, validates, and saves user input for each column of the person table.

Line 36 checks whether the current field requires a value, and sets a variable that we’ll show the user.

Lines 37 and 38 print the prompt and read a line of input.

Line 39 deals with undefined input, which happens when the user presses the EOF character (usually control-D), by returning no value from the subroutine. This ends the while loop in the main program and the program.

Lines 40–44 remove the line delimiter and, if the column is required, check that the input contains one or more nonspace (\S+) characters. If required fields are missing, the program returns rather abruptly.

Line 45 (finally!) stores the data we’ve just collected into the person hash.

Line 46 ends the loop that began on line 35.

47:     unless ( defined($person{id} = get_person_id(%person)) ) {

Line 47 checks whether the person already exists in the database. get_person_id returns the ID if it finds a person matching the values in the person hash.

48:         $person{id} = add_person( %person ); 49:         return undef    unless $person{id}; 50:     }

Line 48 adds the person if get_person_id doesn’t find one.

Line 49 returns undef if, for some unknown reason the add_person had failed to add the person to the database table.

Line 50 closes the unless block that began on line 47.

51:     return %person; 52: }

Line 51 returns the validated input to the main program.

Line 52 ends the input_person subroutine.

53: sub get_person_id { 54:     my %person = @_; 55:     my $person_query_sql = qq(SELECT * FROM person  56:        WHERE first_name = ? AND last_name = ?);

Lines 53 begins the get_person_id subroutine. The get_person_id subroutine looks in the database for a row matching the name passed in. It returns the ID if found or undef if not.

Line 54 makes a local copy of the parameters, storing them into the %person hash.

Lines 55 and 56 create the SQL query string. The query has two placeholders for the values we are looking for.

57:     $Query_statement ||= $Connection->prepare( $person_query_sql ) 58:         or die "Can’t prepare query\nError: $DBI::errstr\nExiting";

Lines 57 and 58 prepare the SQL statement and get a statement handle, but only once. The first time the subroutine is called, the global $Query_statement is undefined, so the or-equals assignment operator executes the prepare method and stores the result. Once $Query_statement has a value, prepare is not repeated. We only need to prepare the statement once because we used placeholders. Each time we execute this SQL statement, we can pass different values and the placeholders dynamically change in the SQL.

We could have prepared the statement in the main program, but the SELECT statement would have been a long way away from where it’s used. It’s convenient to keep the SQL near where it’s needed, if only so that you don’t forget which columns and parameters it uses.

59:     if ($Query_statement->execute($person{first_name},$person{last_name})){ 60:         my $href = $Query_statement->fetchrow_hashref(); 61:         return undef    unless $href; 62:         return $href->{id}; 63:     }

Line 59 executes the query passing in the names we get from the user.

Line 60 fetches a reference to a hash containing column:value pairs of the first matching person. (An enhancement would be to deal with multiple matches.)

Line 61 returns undef if the $href reference is undef, there are no matching records.

Line 62 returns the ID, we must have found a match.

Line 63 closes the if statement that began on line 59.

64:     else { 65:         warn "Can’t execute query\nError: $DBI::errstr"; 66:         return; 67:     } 68: }

Line 64 begins an else block. This block gets entered if, for some reason, the execute on line 59 failed.

Line 65 prints an error message, informing the user of the problem.

Line 66 returns nothing back to the program.

Line 67 ends the if..else block which began on line 59.

Line 68 ends the get_person_id subroutine.

The add_person subroutine adds a row to the person table and returns the person ID or undef if the INSERT fails.

69: sub add_person { 70:     my %person = @_; 71:     my $person_insert_sql = ‘INSERT INTO person(‘ 72:         . join(‘,’, @Person_prompts) . ‘) VALUES( 73:         . join(‘,’, map {‘?’} @Person_prompts) . ‘)’;

Line 69 begins the add_person subroutine. The add_person subroutine adds a row to the person table and returns the person ID or undef if the INSERT fails.

Line 70 makes a private copy of the parameters and stores them into the %person hash.

Lines 71–73 define the INSERT statement we’ll use. Rather than hard-code the column names, it uses the list defined near the start of the program. We use the same list to generate the placeholders with map.

74:     $Person_insert_statement ||= $Connection->prepare($person_insert_sql) 75:         or die "Can’t prepare insert: $DBI::errstr";

Line 74 prepares the SQL statement and store the result in the global variable $Person_insert_statement.

Line 75 is a continuation of line 74, an error here is fatal, so we call the die method and inform the user.

76:     unless ($Person_insert_statement->execute(@person{@Person_prompts})) { 77:         warn "Can’t execute insert: $DBI::errstr"; 78:         return undef; 79:     }

Lines 76 executes the SQL INSERT. We wrapped the execute method inside of an unless statement so that we know if the statement executed or failed. The parameters to execute, which will be substituted for the placeholders in the SQL statement, are accessed as a "hash-slice", with @foo{keylist} syntax. In this case, the key list is a list of values corresponding to the keys from the @Person_prompts array.

Line 77 prints a warning if for some reason the execute statement on line 76 failed.

Line 78 returns undef.

Line 79 ends the unless block from line 76.

Because we’re using the same array to generate the SQL and supply the values, we need to change only the array if the number or order of fields changes.

80:     # and return the id    ***MySQL specific attribute**** 81:     return $Person_insert_statement->{‘mysql_insertid’}; 82: }

Line 80 is a comment.

Line 81 uses a MySQL-specific attribute of the statement handle to get the value that MySQL assigns to the autoincrementing id column and returns it to signify success.

Other DBMS’s do this in different ways. For instance, PostgreSQL uses “sequence generators” with nextval and currval functions. You can create a generator for person-IDs and use SQL like this:

INSERT INTO person(id,first_name,last_name,greeting) VALUES(nextval(‘person_id’),?,?,?)

This stores the next value from the generator in id; then SELECT currval(‘person_id’) to get the current value of the generator.

The input_phone subroutine is similar to input_person but is a little simpler because it doesn’t check whether a matching record already exists.

83: sub input_phone { 84:     my %phone;       85:     print "Enter phone details, (Fields marked with * are required)\n"; 86:     foreach my $col ( @Phone_prompts ) { 87:         my $req = $Fields{$col}{‘required’} ? ‘*’ : ‘’; 88:         print "$Fields{$col}{‘label’}$req:\t"; 89:         my $line = <STDIN>; 90:         return    unless defined $line;    # no input 91:         chomp( $line ); 92:         if ( $Fields{$col}{‘required’} and $line !~ /\S+/ ) { 93:             warn "Required field missing, please re-enter\n"; 94:             return; 95:         } 96:         $phone{$col} = $line; 97:     } 98:     return %phone; 99: }

Lines 83–99 are basically the same as Lines 32–46 except that the data is stored in the %phone hash and we use the @Phone_prompts list of field-names to prompt for.

100: sub add_phone { 101:     my %phone = @_; 102:     my $phone_insert_sql = ‘INSERT INTO phone(‘ 103:         . join(‘,’, @Phone_cols) . ‘) VALUES(‘ 104:         . join(‘,’, map {‘?’} @Phone_cols) . ‘)’;       105:     $Phone_insert_statement ||= $Connection->prepare($phone_insert_sql) 106:         or die "Can’t prepare insert\nError: $DBI::errstr\nExiting";       107:     $Phone_insert_statement->execute(@phone{@Phone_cols}) 108:         or warn "Can’t execute insert\nError: $DBI::errstr"; 109: }

Lines 100–109 are the add_phone subroutine. The add_phone subroutine corresponds to add_person except that it uses the @Phone_cols array for the column list.

One difference from add_person is that duplicate records are possible. The primary key of the phone table is id+phone_type, so if the user enters an existing value for phone_type, the execute will fail.

When the program runs, it looks like this:

Enter person details, (Fields marked with * are required) To exit the program, hit CTRL-D. First Name*:      Harry Last Name:        Hotspur Greeting:       Sir Enter phone details, (Fields marked with * are required) Number Type*:   work Number*:        020s 555 1515 Note: Enter phone details, (Fields marked with * are required) Number Type*: Required field missing, please re-enter Enter person details, (Fields marked with * are required) Forename*: Required field missing, please re-enter

That’s it! The program should be easy to change to allow for more columns; just change the %Fields hash and the arrays holding column names. There are a couple of changes you can make:

Allow reinput of required fields without losing existing input.
Check for duplicate keys in the phone table before adding a phone number, or allow the user to edit the values after an error.