Using phpPgAdmin
Using phpPgAdmin
Using phpPgAdmin
Using phpPgAdmin
A free database administration tool, called phpPgAdmin, is available for PostgreSQL and is installed on our Computing
server. It provides both command-line and visual tools for administering the PostgreSQL databases installed on the
server. The following provides basic instructions on how to use it to perform this week’s tutorial task.
Please read this document carefully and follow the instructions to create a user account on our Computing server if you
don’t have one, and to load the DEMOBLD.SQL database. Then do the exercises in the SQLPracticalExercises.doc
document.
1
Using phpPgAdmin
Before you can work with a database on the Department of Electronics, Computing & Mathematics’ Linux server
(which is variously known as “the Computing server”, computing.derby.ac.uk, commerce3.derby.ac.uk, or just
“Commerce3”1) you’ll need an account. As long as you have a working unimail mail account, you can create an
account for yourself.
If you already have an account on Commerce3 and remember your password – perhaps because you followed the
instructions in the Using_pgAdmin document – please skip to Step 1.
Otherwise, go to http://computing.derby.ac.uk/accgen and follow the instructions to create your account, or to reset
your password if you already have an account and have forgotten it.
Your account details will be emailed to your unimail account and will look something like this:
For these exercises, the important password is the one on the last line, the line that starts with “You have a
PostgreSQL database…”
Keep your passwords secret and safe. If you suspect anyone other than you knows any of your passwords, use the URL
above to reset your passwords and notify Dave Voorhis at [email protected] immediately.
You’ll need your user name and password for the following exercises. Please note that your user name and password
are case-sensitive.
1
The reason for this name has long been forgotten in the dim, distant mists of time. There used to be a commerce1, but
it’s gone now, and no one remembers whether there was ever a commerce2 or not.
2
Using phpPgAdmin
3
Using phpPgAdmin
You’ll need to connect to your PostgreSQL database on the commerce3 server. Under the “Servers” entry on the left
side of the screen, you should see “PostgreSQL” with a red ‘x’ beside it. Click on PostgreSQL.
You should now see a dialog box somewhat like the following:
Fill in your PostgreSQL user name and password, then press the Login button.
Do not ask the university technicians to help you with your database account or create an account for you! If
you’re having problems with your account, please email Dave Voorhis at [email protected]
4
Using phpPgAdmin
Once you’ve successfully connected, you should see something like the following:
It will show a list of all databases on the server, and there are a lot of databases. You can see the names of all of them,
but you can only effectively use your own, so you need to find its name.
Please note that these are mainly student database accounts, so security is deliberately relaxed. Do not use your
database to store sensitive or private information!
Once you find the name of your database, click on it. You should see the following:
5
Using phpPgAdmin
6
Using phpPgAdmin
Click on the “SQL” link below the command-line icon in the toolbar. It’s circled in red in the following image.
You should now see the PostgreSQL command-line SQL entry area shown below. You can enter SQL queries in the
box labelled SQL.
VERY IMPORTANT: Make sure the ‘Paginate results’ checkbox is not checked, unless you are entering a
SELECT query. Otherwise, strange error messages may appear and your query might not work. Unless you need
pagination to display a lengthy query result, it’s safest to always make sure ‘Paginate results’ is not checked.
The result of the query will appear when you press the Execute button, like that shown below:
7
Using phpPgAdmin
To execute another query, click on the ‘Edit SQL’ link, erase your previous query, and enter a new one.
8
Using phpPgAdmin
It will create a number of tables, sequences, and a view that will be used in the SQL practical exercises.
If you have already done this step in the Using_pgAdmin document, you don’t need to do it again. Skip straight to
doing the exercises in the SQLPracticalExercises.doc document.
Open DEMOBLD.SQL using your favourite text editor (e.g., Notepad, Notepad++, TextWrangler, etc.), and copy all
the DEMOBOLD.SQL text into the SQL command-line box described in Step 4. E.g.:
DO NOT use the “or upload an SQL script [Choose file]” mechanism. It’s sometimes buggy; it might work, but it
probably won’t.
Press the Execute button. You should see something like the following:
2
Some of you will notice the distinctly American flavour of the sample data in the script. That’s because it’s based on
some classic Oracle Corporation (which is a USA-based international company) database examples and exercises
which are familiar to almost every database developer. Soon, you’ll be familiar with them too.
9
Using phpPgAdmin
This indicates that script worked. It doesn’t return any results (because it’s not a SELECT query) so it indicates “No
rows found”; it took about 55 milliseconds to run; and “SQL executed” indicates that the SQL code in the script
successfully executed.
Click on “Edit SQL”, then clear the SQL box, to be ready to enter your next query.
10