Week 8, CSc 103

Using and Setting up a Database

Week of October 29 - November 2

Comprising:

1. What kind of data would you like to store?
2. Crash course in using SQL
3. Accessing your database from Python
4. Storing data using a CGI script - and querying it. See my references for each cgi language for syntax and semantics
   

Think about your final form

Crash course in SQL

Databases store data in tables, consisting of rows and columns. Columns have names, and store data of the same type. We have PostgreSQL database management system (DBMS) installed on osiris (and also on cs-linux).

When it comes to accessing the data, there is a big advantage over just appending to a file: the data is structured, and there are lots of possibilities for querying the data selectively. You can ask for certain columns, and  give criteria for which rows you want. You can sort the results as well.

How to connect

1. In the Linux lab, use the psql interactive terminal, and give your password.  psql -h osiris timetable
   
2. by ssh (or Putty) to osiris, and use the psql interactive terminal. No password required.  psql timetable
You can start typing SQL Select queries immediately.
   
3. In the Linux lab, use Pgadmin3 (blue elephant icon) (need to create a connection to osiris, with username and password), Then start clicking.
   
4. From anywhere with a web browser: http://osiris.ubishops.ca/phpPgAdmin
   

[jensen@goatherder 315lab]$ psql fiddle
Welcome to psql 8.0.6, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

fiddle=> create table faddle(
fiddle(> name varchar(20),
fiddle(> age integer,
fiddle(> poem text);
CREATE TABLE
fiddle=> insert into faddle values('Lin',39,'Roses are red\nViolets are yellow');
INSERT 18827 1
fiddle=> select * from faddle;
 name | age |     poem
------+-----+---------------
 Lin  |  39 | Roses are red
Violets are yellow
(1 row)

Insert

INSERT INTO faddle VALUES ('Lin',52,'The rain in Spain can''t fall')

INSERT INTO faddle (name, poem) VALUES ('Lin','The rain in Spain')

Select

SELECT columns
FROM   table
WHERE  condition;

Examples:

SELECT *
FROM students;

SELECT given, family, email
FROM students
WHERE family = 'O''Hara'; 

SELECT name 
FROM faddle
WHERE poem like '%rose%';   -- % matches any number of any characters

Join

SELECT given, family, activity
FROM  students, likes
WHERE students.linux = likes.linux
   AND given = 'Alice'
ORDER BY activity;

Update and Delete

UPDATE likes SET activity='drinking tea' 
WHERE linux='jdoe' AND activity='drinking beer';
DELETE from likes WHERE linux='jdoe' AND activity='extreme bartending';

 Connecting from a PHP program

You connect to a database (on osiris), do any number of 'queries', look at the results of select queries, and close the connection (or else it is closed at the end of the script.) Here is a commented example:

$conn=pg_connect ('dbname=timetable') or die ('Connect failed ');

$query = "SELECT * from courses where prof='$profname'";    //from form
$result = pg_query($query) or die("query failed".pg_last_error());
print pg_num_rows($result) . " rows found for $profname<br>\n";

while ( $line = pg_fetch_assoc($result) ) 
   foreach ($line as $key => $value)
	print "$key = ".htmlentities($value)."<br>\n";

   /* Free resultset */
    pg_free_result($result);

$query2 = "INSERT into students (given, family, linux) values ($1,$2,$3)";
$result = pg_query_params($query2, array($giv, $fam, $linux)) or die("query failed".pg_last_error());
			// (could fail on duplicate primary key )
print pg_affected_rows ($result) . " rows inserted for $linux<br>\n";
		// note: use this, not num_rows, for insert, update, delete queries

pg_close($conn);

Putting this together

GRANT SELECT,INSERT,UPDATE ON tablename TO apache;

Goal for this week: