CS 596 Client-Server Programming
Databases

[To Lecture Notes Index]
San Diego State University -- This page last updated February 22, 1996

Contents of Databases Lecture

Databases

Why databases in a client-server course?

95% or more of all servers will use some sort of database.

Examples we have looked at so far:

POP -- Mail files
HTTP -- Directories and files
FTP -- Directories and files

Services which do not use a database include:

Time of day service
Computational services

What is a database?

Andrew's definitions:

A database holds information and provides for a mechanism to access this information.

Examples of some common (electronic) databases:

Unix passwd file
IRS records system
Rolodex(TM)
Computer file system
Library (object files)
Student grades
Telephone directory

Database terms

Table:

A collection of records

Record:

A collection of fields which belong together

Field:

A piece of data (datum)

Example record:

Lastname   Firstname  MI  ID  Phone Email

Example table of these records:

Scherpbier Andrew    W   42  45026 none
Over       Ben       D   57  411   a@b

Fixed vs. Variable length records

Records can be fixed-length or variable length.

Variable length records:

Number of fields is variable, or
Fields themselves are variable length.

Examples:

/etc/group has a variable number of usernames with each group
/etc/passwd has variable length fields

Variable length records

Advantages:

Use only as much storage as is needed
Can accomodate unusual data not originally planned
Most natural way for humans.

Disadvantages:

Difficult to insert or delete
Hard to search through
Cannot simply map a structure to a record.
Fragmentation

Fixed length records

Advantages:

Easy allocation/deallocation
Easy searching
Easy management

Disadvantages:

Not flexible
Hard to change field lengths (change structure)
Waste space in fields if the data in the fields is shorter than the field length

Example of wasted space:

PC with 1.2GB disk:

512 bytes per sector
64 sectors per cluster
minimum of one cluster per file.

Result: A 10 byte file takes up 32K on disk.

Indexing

Once we store data, we need a way to get at the data.

Indexing provides a method to find records.

An index maps keys to records

Keys can be:

One or more fields of a record
Independent from a record

Keys are normally stored in a separate data structure.
Some common datastructures used:

Sorted for binary search
Tree (btree or some other efficient tree structure)
Hash table

Multiple indexes can be created on a single table

Indexing issues

Issues to consider when using indexes:

Speed/cost of update of the table and index(es)
Duplicate keys

The decision of what to use depends on what needs to be accomplished and what resources are available.

(Some data structures do not (easily) allow for duplicate keys.)

Roll your own vs. using existing

There are many database management systems available.

Most of them commercial but others are free or come with the OS.

In many cases it is quite possible to design and implement your own database.

For a concurrent server, you have to worry about concurrency.

Concurrency

Goal:

The table and its indexes must always be in sync.

Some solutions:

Record locking (complex)
Table locking (easier...)
Access serialization (frequently not possible)
Allow only one "updater" per table (One writer, Many readers)

Existing database systems

We will look at several databases. Each has its own advantages and disadvantages.

Oracle
ndbm
mSQL (Mini SQL)

Oracle:

Professional database system
Expensive
Uses SQL (Structured Query Language)
No direct java interface yet

ndbm:

Standard Unix library
Only hashed indexing
Simple to use from C, C++, Perl, etc.
No direct java interface

mSQL:

Free (for education) database system
Uses SQL
Has java interface (MsqlJava)

Interacting with a database

Many databases systems have an interactive tool to manage tables, records, and fields.

Oracle uses sqlplus

mSQL uses msql

ndbm does NOT have an interactive tool. It is just a library of C functions.

We will be using mSQL through the MsqlJava interface library.

Since mSQL uses a subset of ANSI SQL, we will cover the syntax recognized by mSQL.

SQL: CREATE

The create clause is used to create a table.

CREATE TABLE table_name (
    col_name    col_type [ NOT NULL | PRIMARY KEY]
    [, col_name col_type [ NOT NULL | PRIMARY KEY]]*
)

Example:

CREATE TABLE students
(
     firstname  CHAR(20) NOT NULL,
     lastname   CHAR(20),
     phone      CHAR(10),
     code       INTEGER
)
CREATE TABLE codes
(
     code       INTEGER,
     name       CHAR(20)
)

SQL: DROP

Drop is used to remove a table definition from the database.

DROP TABLE table_name

Example:

DROP TABLE students

SQL: INSERT

Putting data into a table is done with the INSERT clause:

INSERT INTO table_name [(column [, column ]*)]
VALUES (value [, value]*)

example:

INSERT INTO students VALUES (`John', `Smith', `555-9876', 1516)

INSERT INTO students VALUES (`Ben', `Over', `555-1212', 9500)

INSERT INTO codes VALUES (1516, `Good guy')

INSERT INTO codes VALUES (9500, `Bad guy')

SQL: DELETE

Records can be deleted from a table using the DELETE clause. Note that we need to specify which record or records need to be deleted:

DELETE FROM table_name
WHERE column OPERATOR value
    [ AND | OR column OPERATOR value ]*

OPERATOR can be <, >, =, <=, >=, <>, or LIKE

Example:

DELETE FROM students WHERE firstname = `Ben'

SQL: SELECT

Accessing data with SQL is done using a query.

A query always starts with SELECT:

SELECT [table.]column [, [table.]column]*
FROM table [=alias][, table [= alias]]*
[ WHERE [table.]column OPERATOR VALUE
    [ AND | OR [table.]column OPERATOR VALUE]*]
[ ORDER BY [table.]column [DESC][, [table.]column [DESC]]

OPERATOR can be <, >, =, <=, >=, <>, or LIKE
VALUE can be a literal value or a column name

The expression syntax supported by the LIKE clause can have the following special characters:

`_' matches any single character
`%' matches 0 or more characters of any value
`\' escapes special characters (e.g. `\%' matches % and `\\' matches \ )
all other characters match themselves

SQL: SELECT examples

Simple example:

SELECT firstname, lastname FROM students

will produce the following in msql:

+----------------------+----------------------+
| firstname            | lastname             |
+----------------------+----------------------+
| John                 | Smith                |
| Ben                  | Over                 |
+----------------------+----------------------+

Queries can be more complex by adding keywords:

SELECT * FROM students WHERE code < 5000

+----------------------+----------------------+------------+----------+
| firstname            | lastname             | phone      | code     |
+----------------------+----------------------+------------+----------+
| John                 | Smith                | 555-9876   | 1516     |
+----------------------+----------------------+------------+----------+

SQL: SELECT examples (cont.)

Relations can be made between tables:

SELECT students.firstname, codes.name FROM students, codes
WHERE students.code = codes.code

+----------------------+----------------------+
| firstname            | name                 |
+----------------------+----------------------+
| John                 | Good guy             |
| Ben                  | Bad guy              |
+----------------------+----------------------+

SQL: UPDATE

Modifying a record is done with the UPDATE clause:

UPDATE table_name SET column=value
    [,column=value]*
WHERE column OPERATOR value
    [ AND | OR column OPERATOR value]*

OPERATOR can be <, >, =, <=, >=, <>, or LIKE

UPDATE students SET firstname=`Johnny' WHERE code=1516

The msql monitor program

The interactive query tool that comes with mSQL is called msql and it currently resides in

~turtle/msql/msql

To start it type the following:

~turtle/msql/msql -h teamball dbXXXX

where XXXX is the number of your username.

For example (using masc0253 on rohan...):
rohan% ~turtle/msql/msql -h teamball db0253
Welcome to the miniSQL monitor. Type \h for help.
mSQL > \h
MiniSQL Help!
The following commands are available :-
\q Quit
\g Go (Send query to database)
\e Edit (Edit previous query)
\p Print (Print the query buffer)
mSQL > \q

The relshow table viewer

To show what tables are in your database or to show the structure of a particular table, use the relshow program.

For example (again as masc0253 on rohan):

% ~turtle/msql/relshow -h teamball db0253
Database = db0253
  +---------------------+
  |       Table         |
  +---------------------+
  | students            |
  +---------------------+
%

To show the fields in the students table:

% ~turtle/msql/relshow -h teamball db0253 students
Database = db0253
Table    = students
 +-----------------+----------+--------+----------+-----+
 |     Field       |   Type   | Length | Not Null | Key |
 +-----------------+----------+--------+----------+-----+
 | firstname       | char     | 20     | N        | N   |
 | lastname        | char     | 20     | N        | N   |
 | phone           | char     | 10     | N        | N   |
 | code            | int      | 4      | N        | N   |
 +-----------------+----------+--------+----------+-----+

MsqlJava

To use mSQL from Java, there is a set of classes.
The classes are included in the sdsu package file (sdsu.zip) which should be in your CLASSPATH.

The package is called msql

To start using a mSQL database, a Msql object needs to be created:

Msql  database = new msql.Msql();

After this, a connection to the actual database needs to be built. There are two different ways of doing this, only one is of interest to us:

try
{
    database.Connect("teamball");
}
catch (msql.MsqlException e)
{
}

MsqlJava: Closing the connection

After you're done with using MsqlJava, you can close the connection to the database server:

try
{
    database.Close();
}
catch (msql.MsqlException e)
{
}

MsqlJava: Selecting a database

Once a connection has been made, a database needs to be selected. Your database will be named dbXXXX where XXXX is the number in your username.

For example, using masc0253 on rohan:

try
{
    database.SelectDB("db0253");
}
catch (msql.MsqlException e)
{
}

MsqlJava: Performing a SELECT query

Queries to the database server are sent as a single string using the Query() method of the msql.Msql class.

For example:

try
{
   msql.MsqlResult result = database.Query(
        "select * from students");
}
catch (msql.MsqlException e)
{
}

The results of the query will be stored in a MsqlResult object and can be retrieved using the FetchRow() method:

String row[]
while ((row = result.FetchRow()) != null)
{
   System.out.println(row[0] + ", " + row[1]);
}

MsqlJava: Dealing with the results

The msql.MsqlResult class also provides access to field information:

msql.MsqlFieldDesc field[] = result.ListFields();

The msql.MsqlFieldDesc class has some useful members:

TableName()
FieldName()
FieldType()
FieldLength()
IsKey()
NonNull()

MsqlJava: Performing other Queries

The Query() member of the msql.Msql class is used for all general SQL statements.

This means that in order to insert data into a table, the SQL INSERT statement needs to be constructed in a string before passing it to the Query() member.

In SQL, character strings are delimited with single quotes. Be careful to escape (with a backslash) any single quotes that may occur in values.

For example:

database.Query("insert into student (lastname) " +
    "values (`O\'grady')");

CS 596 Client-Server Programming Databases

Contents of Databases Lecture

CS 596 Client-Server Programming
Databases