Go to the PosgreSQL 8.3 one-click download page. Click on the Windows button; in the pop-up window, click Run; the installation file will download. It can take several minutes for the download to complete.

• In the pop-up window that (finally) appears, click Run; after a bit, the Setup window will appear. Click Next.

• Accept the default installation directory by clicking Next.

• Accept the default data directory by clicking Next.

  PostgreSQL runs as a service; think of it as a specialized server that handles all postgreSQL work, one that just happens to be hosted on your machine (rather than the usual sitation of a server being on a remote computer). In Windows, a service is run from a specified account. The next window sets up this serice acount, and asks yuo for a password for it. If you have had postgres on this machine before, give it the password of the existing account....if you do not remember it, you will have to delete the exisiting service account: Start->Control Panels->Administrative Tools->Computer Managment; expand Local Users and Groups; click on Users; click on postgres; click the red X to delete ithe postgres user; confirm the delete; exit.

• In the next window, supply the postgres service password and retype it to verify it; click Next.

• In the next window, leave the port number as is; this is quite imporant. click Next.

• In the next window, leave the locale as is; click Next.

• Click Next to begin installation.

The installation will now occur; it may take a few minutes. When it's done, a screen appears saying installation is complete.

If that this point you wish to continue with installing JDBC, leave Launch Stack Builder at exit checked; if not, uncheck that box.

Click finish to leave pgInstaller.

Fabflixs will be using Java programs (among others) to communicate with the FabFlixs PostgreSQL database. JDBC is "community supported" software that enables communication between Java and PostgreSQL databases; the pgIntaller download includes a tool called Stack Builder that makes it easy to obtains JDBC drivers (and other tools) from the Web.

If you left pgInstaller with Launch Stack Builder at exit checked, you will now see a welcome screen for Stack Builder. If you exited the installer without launching Stack Builder, go to Start -> All Programs -> postgreSQL 8.3 -> Application Stack Builder to get to the weclome screen.

• From the pull-down menu, choose the postgres server; click Next.

• From the applications list that appears, click the "+" next to Database Drivers, then check the box next to the psqlJDBC selection. Click Next.

• Next appears a list of mirror sites from which you can download the drivers; click on the "+" next to USA and then click on a server. Click Next.

A confirmation screen will appear. Click Next. (There's no need to change the download directory.) A progress box will pop up anddownload will commence (it may happen very quickly, or take a few seconds, depnding on the speed of the download server). When it's done, a confirmation box will appear; click Next to launch a wizard that will guide you through the JDBC installation..

• When the wizard's opening screen appears, click Next. On the next screen, check the box accepting the license agreement, and click Next

• The desintation folder selection window will appear; leave the directory as given, and click Next

• A new screen appears; click Install.

• A confirmation screen appears; click Finish.

• Click the Finish button on the Stack Builder screen to leave Stack Builder.

The JDBC drivers are now installed. There are several; the one compatible with Java 6 and Tomcat is postgresql-8.3-603.jdbc3.jar. By the way, a "jar" file is just a compressed archive of Java class files, quite similar to a ZIP file. Java uncompresses jars "on the fly" as a program runs.

You can now add the appropriate JDBC to your CLASSPATH environment variable if you want it available every time you compile a Java program-- the string is

Note the ";", which separates this CLASSPATH entry from the previous one.

Instead, you can place the driver into the CLASSPATH switch on the javac command line when you need it (which is whenever you run a java class that uses JDBC commands); for example, if the program to run is PostgresJDBC then the command line is

Note the need for including .\, the current directory, in the classpath. It is not included automatically, as the classpath switch completely overrides any default or classpath environment variable settings.

If you want to add something to your existing classpath for just this compile, start the -cp parameter with %CLASSPATH%, and since the existing classpath may have spaces in its file names, enclose the parameter in quotes; e.g.,

If you are using a development IDE, you may need to "tell it" about the JDBC jar file (of course, how that is done depends on the IDE you are using).

In Phase 3, you will add C functions to the FabFlixs database to give it functionality that SQL and built-in functions do not provide. PostgreSQL expects these added functions to be in a dynamic link library ("DLL") file. To make a DLL from C source code, one needs a C compiler and a linker that can make DLLs.

For the reasons given above, management has chosen Microsoft Visual C++ 2008 Express Edition for C development. However, if you already have C/C++ from .NET installed on your machine, you do not need to install C++ Express; you should be able to make DLLs with .NET (following the same procedure we discuss for Express in Phase 4). And you can use a different C environment, provided it produces DLLs that are PostgreSQL compatible.

To install Microsoft Visual C++ 2008 Express Edition (with service pack 1):

• Go to www.microsoft.com/express/download/.

• In the Visual C++ box, click on Download.

• The download window will appear; click Run.

• After a couple of minutes, you will likely see a security warning window; click on Run. (If such a window does not appear, just skip this step.)

• After a few moments of activity, the Welcome to Setup window appears; click Next.

• On the License Terms window, click the circle next to I have read and accept the license terms; click Next.

• On the Install Options window, you can leave the two boxes checked without harm, but we will not need these products, so you can save installation time and disk space by "unchecking" the two boxes. Click Next.

• The Destination Folder window appears; leave the settings as they are and click Install.

• Installation will now begin. It can take several minutes for it to complete. When it finishes, the Setup complete window will appear; click Exit.

The PostgreSQL-provided C include files are when writing C functions to be added to postgreSQL databases. They were written for a Unix environment. To work correctly with Windows, we need to provide the file libint.h. The contents of libint.h are never used(!) but another include file expects it to be present, so it must be!). We also need to comment out some lines of pg_config_os.h and elog.h that redefine variables that are already defined in Windows-based C libraries (failing to do so results in a doubly-defined variables that cause compilation errors).

To fix these problems:

• Place libintl.h into your computer's \include\server\port\win32 folder; if your postgreSQL installation is in the default location, that would be the folder \Program Files\PostgreSQL\8.3\include\server\port\win32.

• Replace the file \include\server\pg_config_os.h with the version of pg_config_os.h stored here.

• Replace the file \include\server\utils\elog.h with the version of elog.h stored here.

Since the PostgreSQL server resides on the same computer we are using, that make this computer a "local host" of that server. By definition, the local host server has IP address 127.0.0.1, with domain name localhost. By convention, port 5432 is used for the localhost-PostgreSQL communications link. PostgreSQL comes preconfigured to allow communication to localhost on port 5432. The installer sets up two servers: one on 5432 that is accessbile by a superuser (to which the password is not provided!) and another that is set to the user "postrgres," has superuser privileges (which we need) and has the password that you provided during installation. Since Tomcat and JDBC expect communication to happen on port 5432, we need to change the port on the server with user "postgres." (There is a way to get JDBC to talk to a database on ports other than 5432, and there is supposed to be a way to do so for Tomcat. We were able to get the former to work but not the latter, so we're sticking with 5432.) Since we won't be using default database, we won't have a communications conflict between the two databases. The easiest way to change the maintenance database to port 5432 is to

• Launch pgAdmin: Start -> All Programs -> PostgreSQL 8.3 -> pgAdmin III; dismiss the tip.

• Right-click on PostgreSQL 8.3 (localhost:5433) and select Properties. Enter 5432 for the Port and click on OK.

• Read the note; click on OK..

• Check to ensure that the this database is now set to port 5432 (Look in the object browser box).

Postgres' default behavior is to "listen" for all addresses, but to then restrict who may access postgres; the default is to set the listen_addresses parameter in the postgresql.conf configuration file to "*" and to allow access only from programs running on the local machine, using password authentication; that is, setting the host line for IPv4 (the current Internet communication address scheme) in the pg_hba.conf file to

This is the standard configuration for your projects, as it restricts testing to your machine, and enforces password checks, which is part of the security Fabflixs (eventually) implements.

• Do look over these two files' contents so you get an idea of what they do; they are both well-commented.

Enabling remote computers to communicate with the postgreSQL server

If you want PostgreSQL to accept connections from other computers (for example, if you are doing a demo for us on one machine, but your database exists on another, say a computer at home), you need to change pg_hba.conf to specify which IP addresses are allowed to communicate with PostgreSQL.

To enable "outside communication,"

• Open postgresql.conf in an editor; you'll find it in \Program Files\PostgreSQL\8.3\data\..

  To restrict which other IP addresses can access the database, you modify appropriately the IPv4 host command line (it's near the end of the file); some common configurations are listed below:

• To allow a particular IP address to have remote access to the database on this machine. If the remote address is, say, 128.195.30.118, then this line will give that IP address complete access without any password or other checking:

  host all all 128.195.30.118/32 trust

  If you want to allow access, but only when the right password is given, make the line

  host all all 128.195.30.118/32 md5

• To allow access to all clients in a particular subnet: Suppose the subnet is 128.195.30.0 (that is, machines with IP addresses of the form 128.195.30.*) and you want to give them access using password authentication:

  host all all 128.195.30.0/24 md5

Of course, save the file and exit when you're done.

For these changes to take effect, you have to shut down the postgreSQL server (if it is running) and start it up again. If the server was running:

• Go to Start -> All Programs -> PostgreSQL 8.3 -> Stop server. A command line box will appear with a message that the server is shutting down; after a few seconds, the server shuts down; press Return to exit.

• Start up the server with Start -> All Programs -> PostgreSQL 8.3 -> Start Server. A command line box will appear with a message that the server is starting; it will quickly tell you it was started. Press Return to exit.

Note the use of a fixed IP address: A remote machine connecting via an ISP that assigns a different IP address each time a connection is made will likely not get the IP address for which PostgreSQL allows access.

If your machine has a firewall, the firewall may need to be configured to let through the various addresses that you have allowed to communicate with PostgreSQL.

If you have set up your accesses so that a password is required, when you run psql on a chosen database with a particular user id (i.e., use the -U switch), you will be asked for the that user's password.

If you want to do something fancier, you will need some understanding of Internet networking and its jargon (or need to ask someone who has such knowledge) to configure things properly. You can also get more information from the comments in the file, from the PostgreSQL documentation and from pgAdmin's Help function.

Creating an end user

To test that the postgreSQL server is properly installed and configured, and that Java and JDBC are properly installed, we create an end user, a tiny test database and a small Java program that uses JBDC to "talk" to the database. We show how to communicate with the database both via psql and pgAdmin, to introduce you to each approach. (Read the documentation for these tools to learn more.) We start with creating the end user. (Since a superuser can do "anything," if we used it to connect to the database, we would not spot problems a more "normal" user would have. So, we create a more "normal" user for this test.)

To use postgreSQL or any of the tools the communicate with it (e.g., psql and pgAdmin), the postgreSQL server must be running; so, if it is not, start it up: Start -> All Programs -> PostgreSQL 8.3 -> Start Server. A command line box will appear with a message that the server is starting; it will quickly tell you it was started. Press Return to exit.

Using psql:

Run psql: Start -> All Programs -> PostgreSQL 8.3 -> SQL Shell (psql). You will be asked to specify the server to use; accept the default (localhost). (You accept defaults by just hitting Return).Then you'll be asked to specify the database to emply; accept the default, postgres. Then you're asked for the port; again, accept the default, port 5432. Next, your asked for the user; accept postgres, the name of the default superuser. Finally, you'll be asked for the password for the postgres user; supply the one you specified during installation of postgreSQL. If all has gone well, you'll see the psql welcome message.

At the prompt, enter the text in italics given below. Text not in italics is text PostgreSQL prints out; text in square brackets is a description of PostgreSQL-printed text (not the actual text itself).

• # create user testuser with password 'testpass' createdb;
  CREATE ROLE
• # \q

Press any key to exit the window.

Using pgAdmin:

• Launch pgAdmin: Start -> All Programs -> PostgreSQL 8.3 -> pgAdmin III; dismiss the hint.

• Right-click on PostgreSQL 8.3 (localhost:5433) and select Connect.

  You'll be asked for the password for the postgres user; enter the password you chose when isntalling postgres and click on OK

• Click on the "+" next to the server; in the list of items that appears, right-click on Login Roles and select New Login Role...

• For Role name, enter testuser; for Password and Password (again), enter testpass; select Can create database objects; click on OK

• Click on the refresh tool (the red and green arrows icon, second from the left at the top of the screen) to ensure testuser appears in upper right window.

Note that, for entries you make in dialog boxes, pgAdmin displays the SQL it generates from those entries in the pane with the SQL tab. You can look at the SQL at any time; it's a handy way to double-check your dialog box entries.

Building the test database

Now you create a movie database with one table, stars, that has just one entry (our Governator)...just enough to test a JDBC connection. When creating fields (columns) of the stars table below, create them in the order given; a different order will cause errors in the JBDC program: JDBC uses positional indicators to reference columns, so if you change the entry order of the columns, the program will (try to) access incorrect fields.

Using psql:

To enter PostgreSQL as user 'testuser', launch SQL Shell (psql) as before, but now, for user, enter testuser, and for the password, testpass.

Now create a database with a small table with one entry. Be sure to connect to the moviedb (\c moviedb below) or the tables you create will not be associated with the movie database, causing problems down the road.

Using pgAdmin:

• Since pgAdmin is an administator's tool, you have to connect to the database as a superuser, so double-click on PostgreSQL 8.3 (localhost:5433) and enter your postgres user password.

• Click on the "+" next to PostgreSQL 8.3 (localhost:5433), to make Databases visible. Right-click on Databases and select New Database... .

• For Name, enter moviedb; click OK.

• Go to Servers: PostgreSQL 8.3 (localhost:5433):Databases:moviedb:Schemas:public:Tables (again expanding the tree by clicking on "+" as necessary to make Tables visible). Right click and select New Table...

• In the Name box, enter stars; for Owner, select testuser

• Click on the Columns tab, then click Add, to call up the New Column... dialog box. For name, enter id; for Data type, select integer, and click in the Not Null box to turn on that property. Click OK to save this column.

• For last name, click Add again, and enter last_name for the Name and character varying for the Data type; enter 50 for Length, and check Not Null; click OK to save it.

• To make a first name column, execute the steps just above, but with a Name of first_name.

• For date of birth, add a column called dob, with a Data type of date.

• For the photo URL, add a column called photo_url of varchar (varying character) of length 200.

• id needs to be the primary key, so click on the Contraints tab; make sure Primary Key is selected (bottom left of the window). Click Add, to call up the New Primary Key... dialog box. For name, enter id. Click on the Columns tab; select id from the drop down box at the lower left of the window. Click Add to save the key.

• The table is now set up; click OK to leave.

There are a few ways to add Arnold's information into the table; here's a straight-forward one:

• Click the "+" next to Tables, if necessary, to make the stars table visible. Right click on the stars table; choose Scripts, then INSERT script.

• In the VALUES clause that appears in the top left window, select each question mark (?) in turn and enter these five values, one for each question mark: 755011, 'Schwarzeneggar', 'Arnold', '07/30/1947', 'http://www.imdb.com/gallery/granitz/2028/Events/2028/ArnoldSchw_Grani_1252920_400.jpg?path=pgallery&path_key=Schwarzenegger,%20Arnold' .

• Select Execute from the Query menu (or hit the F5 key) to place the record into the table.

• Close the window (you can save the query or not as you like). Exit pgAdmin.

Implementing the test Java program

Now enter and compile the following java program; it uses JDBC to communicate with your test database and retrieve some basic information about it. Study it to get a feel for how JBDC works. (This is just a test program; a production program would clean some things up, such as not throwing an Exception out of main()!)

import java.sql.*;

public class PostgresJDBC
{
 	public static void main(String[] arg) throws Exception
	{
		Class.forName("org.postgresql.Driver").newInstance();
 		Connection connection = DriverManager.getConnection(
 		"jdbc:postgresql://localhost/moviedb", "testuser", "testpass");
 
 		Statement select = connection.createStatement();
		ResultSet result = select.executeQuery("Select * from stars");
  		System.out.println("The results of the query:");
 		ResultSetMetaData metadata = result.getMetaData();
 		System.out.println("There are " + metadata.getColumnCount() + " columns");
 
 		for (int i = 1; i <= metadata.getColumnCount(); i++)
 			System.out.println("Type of column " + i +" is " + metadata.getColumnTypeName(i));

 		while (result.next())
 		{
 			System.out.print("id = " + result.getInt(1));
 			System.out.println(" Name = " + result.getString(2));
 		}
 		connection.close();
	}
}

Replace localhost with the IP address of the computer you are using, if it is not the same machine on which the database resides.

• Now run the program (from the directory in which the class file is stored), making sure postgresql-8.3-603.jdbc4.jar is in the classpath (using any of the methods discussed above); you should see output describing the structure (metadata) and contents of this test database.

To support dynamic content on web pages and Java servlets (extensions of a server's basic services), we use Tomcat, a freeware package form the Apache Software Foundation. (You will be using its capabilities starting in Phase 2.) To install Tomcat

1. Go to http://tomcat.apache.org; read a bit!

2. Under Download, click on Tomcat 6.x (look on the left-hand side of the page).

3. Under 6.0.18, select Windows Server Installer under Binary Distributions, Core; save the EXE file.

4. Launch the EXE file you just downloaded.

5. Read the instructions; click on Next; read the license and agree.

6. On this screen, make sure Tomcat and Start Menu Items are checked. Leave Documentation checked if you want the documentation on your drive (it is also available at the Tomcat site); check Examples if you want some examples of servlets. Click Next.

7. Leave the destination folder as is; click Next.

8. Leave the port at 8080—this is crucial. Choose an administrator login name and password: do not forget them! Click Next.

9. Check that the directory listed is indeed where you have the java virtual machine (java.exe and its associated files) stored; if you used the default directories for java when installing it above, it is C:\Program Files\Java\jre1.6.0_07. (Yes, iat the top of Window it says to select a 5.0 path; don't worry, the message is just a bit out of date!) Click Install.

10. Wait a few seconds while installation occurs.

11. Click Finish. A window should appear showing Tomcat starting up; the release notes file should appear in a window. Because Windows uses a different end-of-line marker than Unix, the notes may be quite difficult to read; don't wory, just close the window. If you need to read the release notes at some point, you can find them here.

To be sure Tomcat is running, right click on the feather-like icon in the lower right of the Windows toolbar (if you hover over the correct icon, Apache Tomcat will appear); Start service should be grayed. To stop Tomcat at any time, right-click on the icon and select Stop service. You can also start and stop Tomcat on the General panel of the Apache Tomcat properties window (double click on the Tomcat icon in the toolbar).

To test that Tomcat is working and communicating with PostgreSQL properly, we've prepared a servlet that performs basically the same tasks as the JDBC program; it queries the test database and returns some structural and content information. To run this program:

1. Make sure the PostgreSQL database server is running. Be sure communications to the database server have been set up as described in Enabling pgAdmin and other applications to communicate with PostgreSQL, above, before proceeding.

2. Download TomcatTest.war

   A WAR file is a "Web Application Archive." It contains a compressed directory of the files needed for a ready-to-run java servlet that Tomcat will launch when you go to the Web page designated in the WAR. (A WAR file must a have certain directory structure and contain appropriate files (with the appropriate contents) for Tomcat to launch the servlet. Details about WAR files will be covered in Phase 2.)

3. Use a Web browser to go to http://localhost:8080; this is the web page that lets you access locally (stored on your computer) hosted Web services. After installing Tomcat (which is a Web service), it was set to the Tomcat welcome page; that page should appear.

4. Click on Tomcat Manager; enter your admin user login and password (given when you installed Tomcat); click OK. If your login and password are correct, the Tomcat Web Application Manger page will appear.

5. Under War file to deploy, enter in or Browse to find the location of TomcatTest.war; then click the Deploy button. This action decompresses the WAR and places its folders into their required locations. TomcatTest should now appear in the Applications list.

6. Go to http://localhost:8080/TomcatTest/servlet/TomcatTest. Tomcat will run the servlet TomcatTest and print its output onto the web page. If the output is database information, all is well. If instead you see a message that an SQLException occurred, check that the PostgreSQL server is running, that it is configured to allow TCP/IP access and that the test database with the correct user name, user password and table structure has been created (see above).

You are now ready to proceed with FabFlixs development!

If you plan to do your demontrations by connecting an XP computer at a meeting place to a remote XP computer that houses the software to be demonstrated, follow the instructions on the Get started using Remote Desktop with Windows XP Professional web page. Some additional things to keep in mind:

• Of course, be sure to leave the remote computer on and connected to the Internet (by whatever means that conputer uses) during the time of the demonstration or, obviously, you will not be able to access it during the demonstration.

• You will need to know the computer's name or its IP address, and the user login and password, if any, for this machine, to be able to connect to it, as described on the above Web page. The name might just be one word, but you may have to provide its entire Internet name if the remote and local computers are not in the same local network. So, if your computer's name is igor, you might have to say igor.ics.uci.edu (or whatever) for the local computer to find it.

• When you are done with the demonstration, instead of logging out, you can click the X in the bar at the top of the screen; you'll see a message box telling you that you are about to disconnect (but can reconnect later); click on OK. You are now disconnected from the remote machine, but will still be logged on to it.

As previously discussed, it will be up to you to make remote computing work when one or both of the machines are running something other than Windows XP; we can only support XP-to-XP connections.