<<

. 63
( 69 .)



>>

While SQL, the standard language used for relational
When you start a psql session and connect to a database,
databases, is not exactly free-flowing English, it is still easy
you are acting as a client of the database. You can select
to use and understand. The command select phone
data from tables by searching for certain values and by
from contacts where areacode=415; is about as
restricting what you want to see from these tables by
close to flowing speech as computer syntax comes.
columns as well.
SQL also allows you insert data into tables with insert,
PostgreSQL, like most modern databases, is a relational
remove data from tables with delete, modify data in
database. That term implies that all data are stored in a
tables with update and sort data with order by.
uniform manner “ in tables. Unlike a hierarchical database,
in which data elements have parent/child relationships,
relational databases have no such relationships. Instead,
data of all kinds is stored in tables and all records are
accessed in the same manner.

RUN A DATABASE ON MAC OS X (CONTINUED)




‡ Type adduser and press ° Type sudo mkdir ‚ Type su - postgres and
Note: To complete step 7, you must
have already created an adduser
Return. press Return, then type the
/usr/local/pgsql/data
script. If not, see Chapter 11. and press Return. postgres password at the
prompt.
· Type sudo chown postgres
/usr/local/pgsql/data.



310
18
DEVELOP UNIX APPLICATIONS




You can get help during your psql session by typing \?.
When you enter this command, a list of slash commands,
such as \l for creating a list of your databases, appears.
You can use the \h command to get help on a particular
command. For example, typing \h select describes the
syntax and use of the select command.
To exit your psql session, type \q.
SQL syntax provided by the help function will display
optional portions of a command inside square brackets. If
you type \h modify, for example, you will notice that the
word ONLY appears within square brackets “ [ ONLY ].
This part of the command is, therefore, optional. Similarly,
[ WHERE condition ] means that you can optionally
specify a condition, such as where areacode=415.




” Type /usr/local/pgsql/bin/ ± Type nohup /usr/local/ ¡ Type createdb testdb and
press Return.
initdb -D /usr/local/pgsql/data pgsql/bin/postmaster -D
and press Return. /usr/local/pgsql/data </dev/null
– Your database is created.
>>server.log 2>&1 </dev/null &
„ Type psql testdb and press
and press Return.
Return.
– Your database service
– You connect to
starts.
the database.
311
UNIX FOR MAC



WRITE SQL COMMANDS
You define each column in the table as having a particular

Y
ou can issue SQL commands to your PostgreSQL
type. Most of these types are character fields of a specified
database. To use all of the features of your database
length. After you define a table, you can add records to it.
software, you need to learn a number of SQL
You can do this one record at a time, or you can bulk load
commands. In particular, you need to learn how to create
a table from a flat text file. To add a single record, you can
tables, insert and remove records from these tables, and run
identify the table and the value you want to assign to each
queries to extract information from these tables.
column in the new record. For example, you may say
The command for creating a table is create table insert into books values('Paul Whitehead
tablename. When you define a table, you need to specify and Eric Kramer','Your visual blueprint
the number of columns you want to add. For example, the for building Perl scripts','Wiley
Publishing',2000,'0-7645-3478-5'); paying
SQL command for adding a book table to the database may
particular attention to the semicolon at the end.
look like this:
You can list the contents of the books table in its entirety
create table books (
with the command select * from books; or you can
select some of the records by running a select command
author varchar(32),
with a where clause. For example, to list the titles of the
title varchar(64),
books in your table that were published in the year 2000,
publisher varchar(16),
you can type select title from books where pubyear = 2000;.
pubyear int,
ISBN varchar(13)
);

WRITE SQL COMMANDS




„ Type psql testdb and press ‹ Type name varchar(12), ˇ Type age int, and press ‡ Type ); and press Return.
Return. and press Return. Return.
– Your table is added to the
¤ Type create table pets ( › Type type varchar(6), and Á Type fixed varchar(1) and database.
and press Return. press Return. press Return.




312
18
DEVELOP UNIX APPLICATIONS


You can insert records into a PostgreSQL table by typing an
insert command for each record, or you can create a text file
containing the commands and load that file. To create a table and
load it from a text file, you can type the table create
command along with each of the insert commands into a file,
such as pets.sql.

Example:
create table pets name varchar(12), type varchar(6), age int, fixed varchar(1));
insert into pets values('Amaranthe','cat',1,'y');
insert into pets values('Raven','dog',6,'y');
insert into pets values('Maize','cat',.5,'n');

You can enter the following to load the data into the database:

psql “d testdb “f /Users/user/pets.sql

To count the records in a table, you can use an SQL count command.

RESULT:
TYPE THIS:
count
select count(*) from pets;
--------
3




– The database selects and
° Type insert into pets ‚ Type insert into pets ± Type select * from pets
where type='cat'; and press prints the data you request.
values('Amaranthe','cat',1,'y'); values('Maize','cat',.5,'n');
and press Return. and press Return. Return.
· Type insert into pets ” Type select * from pets; ¡ Type select name from pets
and press Return. where fixed='n'; and press
values('Raven','dog',6,'y');
and press Return. Return.
– The database server
displays the contents of
your table. 313
UNIX FOR MAC


ACCESS DATABASES FROM PHP
<?

Y
ou can access data in your databases from PHP and
$host = "localhost";
include this information in your Web pages. While
$user = "postgres";
the setup required to provide information stored in a
$pass = "dbacct";
postgres database on a Web page is not intuitive, you can
$db = "testdb";
provide this functionality when you have the proper tools.
You can then open a connection to the database with a
You need to install PostgreSQL on your server. You also need
command such as $connect = pg_connect ("host=
to use a Web server that supports PHP ” for example, an
$host dbname=$db user=$user password=$pass
installation of Apache that supports PHP dynamically or
password=$pass");. You can then determine if your
statically. In addition, your PHP build must support
connection is successful by testing the $connect value.
PostgreSQL; that is, you must build it with the -with-
pgsql configuration parameter. Lastly, for a Web site to use Next, you can create the query that you want to run ” for
PostgreSQL commands, the database must be running. example, $query = "select * from pets"; ” and
execute that query with a command such as $result =
When you are sure that you have these prerequisites, you
pg_query($connect, $query) or die("Query
can create a Web page that incorporates information from
failed: $query");. If your query is successful, the
your database. The first step is to identify the database that
command stores the data that you just fetched in $result.
you are using, as follows:
You can now decide how to process and display the data
that the command returns from the database.

ACCESS DATABASES FROM PHP




„ Start the Pico editor ‹ Type <head><title>test ˇ Type <? and press Return. ‡ Type $pass = "dbacct";
to create a file named and press Return, then type
PHP and PostgreSQL
Á Type $host = "localhost";
/sw/apache/htdocs/testpg.php </title></head> and $db = "testdb"; and press
and press Return, then type
and press Return. press Return. Return.
$user = "postgres"; and press
– The Pico screen appears. › Type <body> and press Return.
Return once more.
¤ Type <html> and press
Return.

314
18
DEVELOP UNIX APPLICATIONS

This example comprises the complete PHP
file for testing your database accessibility.

Example:
<html>
<head><title>test PHP and PostgreSQL</title></head>
<body>
<?
// database access parameters
$host = "localhost";
$user = "postgres";
$pass = "dbacct";
$db = "testdb";
// open a connection
$connect = pg_connect ("host=$host dbname=$db user=$user
password=$pass");
if (!$connect) {
die("could not open a connection to db server"); }
// generate and execute query
$query = "select * from pets";
$result = pg_query($connect, $query) or die("error in query:
$query" . pg_last_error($connection));
// get number of rows
$rows = pg_num_rows($result);
echo "There are $rows records in the pets db";
// close db connection
pg_close($connect);
?>
</body>
</html>

<<

. 63
( 69 .)



>>