Database Lab 7 & 9 -- 2 Client side programs

Due as specified in course webpage

Client side program

Please submit your programs (file or folder) using submit cs307

Write a program in the language of  your choice, to "print*" one order. PHP would be a suitable language, using a command line interface as would C or C++ with the database API (libpq), or python "Now for something completely different", or perl "there is more than one way to do anything".

Embedded SQL with C is also an option (we'd have to ask for it to be installed). [See first.pgc for a "simple" example.] It solves a problem with the dash in cs-linux and checks for some null values resulting from a left join. Sample output below.

Java is no longer a choice, because the postgresql interface does not do "UNIX socket connections" I am sorry to learn this.

By now you have a concept of an "order" consisting of several items. It could be identified by (key) customer and date, or have a unique order number, for example.

*.(Print is to be understood in the C and Java sense of writing to standard output, that't normally the console, but can be redirected.)

Security!

Not a problem on linux, since Ident authentication is in use. You connect as yourself, I as superuser. If you want to let other students use your application, grant appropriate privileges to individual friends, or group stu. 
GRANT SELECT ON tablename TO stu;

Rules will always work, because queries invoked by the rule use the permissions of the rule owner (you). For example, if a student has permission to insert an order_line, and your rule updates the inventory, the student does not need any permission for inventory.
Do not put user= in any of your programs unless you want to prevent me or anyone else from using it.

What to submit:

Your client side program (source code)

As usual, submit the source code to me You can submit a file or a folder (directory)
And tell me in the form below how to run it, and if input or command line arguments are needed.
submit cs307 MyFile.php

and some More lab 9

For the last lab of the term, I want you to create a practical application for your database, in which you modify it in some way, for instance, taking a new order. Since the emphasis is on the database connectivity, neither a GUI nor a web application is required.
In fact, a GUI would require x2go, which I don't have at home and you probably don't either.
A web interface would need to be on osiris, I could figure out some way to try it out, I guess. There are also numerous security concerns.
The applications should be easy for someone like a store employee or on-line customer to use. The most common operations, such as adding a customer or renting a tool, should be implemented. These Part 1 instructions now include how to use postgresql in C as well as Java and python.
The important issue of transactions and commit status is addressed here.

Some previous notes about web applications, and the PHP language

[Previously I wrote] Now write one more program, that updates your database, or add this functioality to the program you have so far. For example, it could ask for your customer name (or code), display a list of products, and let you pick one to order some amount of. It may be a GUI or a normal stdin - stdout program. It should run as a single transaction, to be committed upon completion. See note about various languages.

Tell me what you did

Lab 7
Lab 9
Your Name:
Your Username:
Program file name: that you submitted
Tell me what you did:

And now the details: (compiling and running)

ecpg - SQL embedded in a program

For the curious, let me just say that the SQL standard requires the ability to embed SQL statements in one programming language out of a list of 5. The postgres developers, being C programmers, chose C, and implemented it as a preprocessor that would turn the embeded SQL into function calls, giveing a C program that could be compiled normally. After a few years of nobody wanting to use it, I have removed most of the instructions.

[ecpg is set up in the lab. You proably don't care. All you need to do, to arrive at an executable named first,  is:

ecpg  first.pgc
gcc -o first first.c -lecpg   -I/usr/include/postgresql-9.0
#OR 2 steps, compile, link-----
gcc -c first.c -I/usr/include/postgresql-9.0
gcc -o first -lecpg first.o
gcc

You need path setting for the include file.  I have compiled it. Run it from the course directory by:

<jensen@linux:/home/COURSES/cs307 > ./first J102

Char and varchar attributes are best cast into type TEXT in your SELECT statements, this results in proper C-strings. Note that the size of the char array must be one greater than the maximum string size, to accomodate the zero byte that ends the string. Output of first: (or run it, and see what's changed!)
[jensen@J118-10 Database]$ ./first J102
==== LIST of COURSES for room: J102 =====
CSC116B01 Low Level Programming Lan J102 Jensen    email: ljensen
CSC301B01 Simulation Techniques     J102 Bentabet    email: (null)
CSC314B01 Computer Networks         J102 Bruda    email: bruda@cs
CSC302B01 Computer Graphics         J102 Allili    email: (null)
CSC204B01 Data Structures           J102 Khouzam    email: nkhouzam
CSC407F01 Honours Dissertation      J102 Staff    email: (null)
CSC272B01 Electric Circuits&Electr. J102 Fournier    email: sfournie
PHY212B01 Electric Circuits & Elect J102 Fournier    email: sfournie
8 rows selected.
]

C (or C++) - using functions in libpq

Alternatively, you can access postgreSQL functions from a C library, as in other languages. In this package, all the functions start with PQ, all the types start with PG. (I supose PQ stands for Postgres Query?) This is a different, more sophisticated libray than libecpg.

You need to include a header file:

#include <libpq-fe.h>

When linking, you will probably need -lpq

Functions include:

PQconnect			//returns a connection 
PQexec		PQprepare
PQexecParams	PQexecPrepared	//use for safe user input

The library is used exactly the same in C or C++  See examples which can be fould on linux.ubishops.ca in the course directory, /home/COURSES/cs307 and you can execute the compiled versions: 

	sources		   executable
  libpq-example.c	./libpq-example-c	
  libpq-example.cc, 	./libpq-example-cc

  fruitstand.c		./fruitstand-c
  fruitstand.cc		./fruitstand-cc

Java

Java instructions were here

Python  -- "and now for something completely different"

is a very nice scripting language. As in all interpreted languages, variables do not have types (only data has). Programs are generally much shorter. Python statements can be executed interactively, simply use the command python --  you get a prompt that looks like >>>

Sample program, proflist.py Run it by typing ./proflist.py

Python code is short and sweet. See python Tutorial. Also refer to the database API. No brackets, indentation determines program structure. This example gets the profs table, as a "list of lists", and loops through it, printing each list (row). [Ask any python programmer how to format elegantly.] If you are interested in python's gui capabilities, see a PyGTK Tutorial.

My guis,

Handling errors:

Use try -- except   The specific errors for database are of type pgdb.DatabaseError  an example, that connects to timetable as the user, is testerr.py , put any query in the command line, enclosed in double quotes. You (group stu) don't have many permissions, except for the enroll table. See python tutorial section 8
Another useful exception is pgdb.Warning, to catch warnings you may raise in your triggers. 
try:
   curs.execute(... some query ...)
except psycopg2.DatabaseError, message:  #one error class, 'message' is the error string
   print "Exception raised..."
   print message		#informative error message
else:
   # what to do if no error, such as fetching results...
# continue with processing

Perl  -- "There's more than one way of doing anything"

Is another, well-known scripting language. It tends to look cryptic, and use lots of special characters. (Scalar) variable names start with $. For information, guides and reference, see:
 http://perldoc.perl.org/ or our somewhat older  local copy
......
A perl  DBI example: myperl.pl asks for part of a (family) name, and prints search results from students.
My gui example, using gtk, is addressgui.pl
       There are lots of perl references and tutorials. One is Picking Up Perl
       For DBI, you can start with the "Short Guide to DBI"
       The official site for reference is www.perl.com
If you are interesting in a GUI, you can use Gtk2-perl, the "Gimp Toolkit," it's same as in python. (In fact, it is coded in C).

PHP

An arronym for PHP Hypertext Preprocessor, formerly known as Personal Home Page, This language, a simplified form of PERL, is primarily used for web programming. We will use this later

Notes about transactions:

The java default for connection objects is autoCommit = true.
This means, like using psql (and unlike embedded C), that each SQL statement is its own transaction. If you wish to define transactions, use setAutoCommit(false) and explicit commit() or rollback(). See the java documentation for sql connection interface. Some slightly outdated documentation is at /home/COURSES/CSC207/java/docs/api/  The up-to-date, searchable documentation, and tutorials, are of course at java.sun.com
Now taken over by Oracle. The actual version 6 api is http://download.oracle.com/javase/6/docs/api/

Transaction status of other languages (AutoCommit?)


Language Autocommit initially =
 comments
 errors
SQL (psql)
 True
(each statement is a separate atomic transaction)
 use begin;  commit; for multi-statement transactions
 messages appear
ecpg
 False
 EXEC SQL COMMIT;
EXEC SQL WHENEVER SQLERROR SQLPRINT;
EXEC SQL WHENEVER SQLWARNING SQLPRINT;
libpq
(C, C++)
 True
 
/* Start a transaction block */
    res = PQexec(conn, "BEGIN");
    //etc. must END 
PQerrorMessage(conn)
Notices (info, notice, warn) are not errors, they are written by default to stderr
Perl
 False, to turn it on:
 my $dbh = DBI->connect('DBI:Oracle:payroll',
                               {AutoCommit => 1}, ...    );
 
$sth->errstr;
 # on trouble with database or statement
Python
 False
 implicitly requires that a transaction is automatically opened
So -- use .commit() whenever a transaction need not be continued
 except  Warning, Error
PHP
 True,
  for trans's, use:
 pg_query ($conn, "begin"); / pg_query ($conn, "commit");
 pg_last_error(), pg_last_notice()
Java
 True
 connection.setAutoCommit(boolean autoCommit)
 catch SQLError, .getWarnings()
  Back to cs307