INFO-340 Lab #3 Introduction to PostgreSQL Objectives • To review ...

disturbedoctopusΔιαχείριση Δεδομένων

27 Νοε 2012 (πριν από 4 χρόνια και 11 μήνες)

228 εμφανίσεις

INFO
-
340 Lab #3

Introduction to PostgreSQL


Objectives



To review the software infrastructure
used in this class (tomcat, jsp, etc.)
so
that you can sketch a diagram of its major components and describe them



To learn how
to
submit SQL queries to the Postgr
eSQL server from the
command line and from NetBeans



To learn how to run PostgreSQL scripts to load and query databases



To learn some basic SQL commands


These
activities will teach the basic skills needed for Assignment #2.


1)

psql command shell
: The psql
command shell allows you to submit SQL
queries for querying and updating databases. To use the shell, follow these
steps:

a.

Use SSH to log into the iSchool Linux server,

b.

At the command line type
psql DB_NAME
, where DB_NAME is the
name of the database that
you will connect to. For the iSchool server,
your database name is your iSchool username.

c.

psql will prompt you for a password. The default database password is
(
the first 5 digits of your
student ID
+ ‘temp’) (e.g. ‘12345temp’)
.

d.

You should now be in the p
sql command shell. If you didn’t change
your PostgreSQL password in Lab #1, you should do so now. To change
your passwprd, enter the following at the psql prompt:


alter user USER_NAME with password 'NEW_PASSWORD';

(don’t forget the semi
-
colon)


2)

Practic
e with the psql shell
. Using the psql shell, try out the following:

a.

Create a table for storing the
names
and
ages
for
Sailors


b.

Make up some sailor names and ages, and insert three records into the
table

c.

Write and execute an SQL query to select all sailors
that are older that
30 years old


The information you need for these tasks is available here:

INFO
-
340 Course Page

Programming Examples

PostgreSQL


Quick Guide


3)

Load data into tables with a psql script
.

a.

Examine the script

INFO
-
340 HP

Programming
Examples

PosgreSQL



Importing Data


b.

Now, adapt this script to load the dreamhome data tables into your
database. The data for dreamhome is found here:

http://courses.washington.edu
/info340/data/dreamhome/


4)

Connect to the database with NetBeans
.

a.

Install the PostgreSQL driver into NetBeans. The driver is located here:

http://jdbc.postgresql.org/download.html

Download this ver
sion:
pg74.216.jdbc3.jar


To install it,
navigate to: Runtime tab, Databases, Drivers and then right click on
Drivers. Then add the driver.


b.

Navigate to the PostgreSQL driver by clicking on the Runtime tab, then
Databases, then Drivers


c.

Now, right click o
n the PostgreSQL driver you just installed and select
Connect using…
Configure the database connecting as follows:


jdbc:postgresql://<HOST>:<PORT>/<DB>

HOST ==
linux.ischool.washington.edu

PORT ==
5432

DB == your login name


The string should look some
thing like:


jdbc:postgresql://

linux.ischool.washington.edu
:
5432/username


Note:
NetBeans cannot directly connect to the database from off
-
campus.
If you wish to connect to the database from outside the network, you must
first log into the VPN.


d.

Entry
you
r user name (your login name) a
nd password

e.

Select the “public” schema and press OK

f.

Expand the new connection and the folder called Tables. You should
see the Sailors table that you created earlier.

g.

Right click the folder Tables and select
Execute Command
.
Type in
some SQL commands such as
select * from Sailor
. Click the
Run button to execute your queries.

h.

You can use this GUI to execute SQL commands to databases.

i.

You can navigate the folder to see tables and their attributes


Spend a few minutes viewing a
nd editing your tables.


5) Exporting your database

Sometimes we might want to transfer our database from one server to another.
We can do this by exporting our database file from
PostgreSQL
. The
pg_dump

utility allows us to convert an existing database in
to a sequence of SQL
commands that can be used to recreate the database.



a. Return to the linux command prompt (not the psql prompt)


b. Enter the following command:

pg_dump DATABASE_NAME > lab3db.sql

the
>
character copies the output of pg_dump into a
file named
lab3db.sql.
The file now contains the SQL needed to recreate the
database.

c. You can now use the exported file to recreate your databases, as you
did in Step 2. For instructions on how to export different types of data,
type
man pg_dump
at
the linux prompt.


What to turn in

Create a
lab03
directory in your current NetBeans project. Create a new
index.jsp
file in that directory, and paste the contents of
lab3db.sql
into that
directory (don’t worry about formatting). Alternately, you can link
to the
lab3db.sql from the page. In the top
-
level
index.jsp
, create a link to your new lab
file. Deploy the WAR file to the server.