Databases

Why databases in a client-server course?

95% of all servers probably use some sort of database.

Examples we have looked at so far:

POP                                  Mail database                         
Gopher                               Directories and files                 
HTTP                                 Directories and files                 
Fortune                              fortune database                      

Services which do not use a database include:

• Time of day service
• Computational services

What is a database?

Andrew's definition:

A database holds information and provides for a mechinism to retrieve this information.

Examples of some common databases:

• Unix passwd file
• IRS records system
• Rolodex(tm)
• Computer file system
• Library (the kind with books)
• Library (the kind with object files)

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     x45026      andrew@sdsu.edu              
Over             Ben            D    57     x411        ben.d.over@sciences.sdsu.edu 

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 which each group
• /etc/passwd has variable length fields

Advantages:

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

Fixed vs. Variable length records (continued)

Disadvantages:

• Difficult to insert or delete
• Hard to search through
• Cannot simple map a C struct to a record

Fixed-length records

Advantages:

• Easy allocation/deallocation
• Easy searching

Disadvantages:

• Not flexible
• Hard to change a field length

Indexing

Needed:A quick method to find a specific record.

Solution:Table Index

Two methods:

1. Mark one or more fields of a record as the key
2. Keep a separate key for a record

Keys are stored in a separate data structure:

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

Indexing issues

Issues to consider when using indexes:

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

The decision of what to use depends on the use of the database.

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

Concurrency (again?!?!?)

Goal:

The index and the table must always be in sync.

Some solutions:

• Record locking (complex)
• Table locking (easier...)
• Access serialization (frequently not possible)
• Allow only one "updater" per table

Other problems:

• Caching
• Permission

Database APIs

We will look at several databases

• Oracle
• ndbm
• gdbm
• metal base

Oracle

Oracle uses SQL (Structured Query Language)

RDBMS (Relational Database Management System)

Data is organized in tables with fixed-length records

API uses a pre-processor

• Pro*C
• Pro*Ada
• Pro*FORTRAN
• Pro*COBOL
• Pro*Pascal
• Pro*PL/1

Structure of database query:

1. Send a query
2. Use a ``cursor'' to go through all results

Example Pro*C program

EXEC SQL BEGIN DECLARE SECTION;
    VARCHAR uid[20];
    VARCHAR pwd[20];
    float sal, comm;
    char ename[11];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
main()
{
    strcpy(uid.arr, "SCOTT"); uid.len=strlen(uid.arr);
    strcpy(pwd.arr, "TIGER"); pwd.len=strlen(pwd.arr);
    EXEC SQL WHENEVER SQLERROR STOP;
    EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;
    EXEC SQL DECLARE C1 CURSOR FOR
        SELECT ENAME,SAL,COMM
        FROM EMP WHERE JOB='SALESMAN';
    EXEC SQL OPEN C1;
    EXEC SQL WHENEVER NOT FOUND STOP;
    for (;;)
    {
        EXEC SQL FETCH C1 INTO :ename, :sal, :comm;
        printf("%-10s\t\t%6.2f\t\t%6.2f\n", ename, sal, comm);
    }
    EXEC SQL CLOSE C1;
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    EXEC SQL COMMIT WORK RELEASE;
    exit(0);
}

ndbm

Features(?):

• One table per database
• Variable length records (up to 4096 bytes)
• Separate key
• No duplicate keys allowed

Both key and data are stored in a struct:

typedef struct
{
    char   *dptr;
    int    dsize;
} datum;

The database uses two files: .pag and .dir

ndbm API

DBM *dbm_open(char *file, int flags, int mode);

void dbm_close(DBM *db);

datum dbm_fetch(DBM *db, datum key);

int dbm_store(DBM *db, datum key, datum content, int flags

int dbm_delete(DBM *db, datum key);

datum dbm_firstkey(DBM *db);

datum dbm_nextkey(DBM *db);

int dbm_error(DBM *db);

int dbm_clearerr(DBM *db);

Example ndbm program

#include <ndbm.h>
#include <stdio.h>
#include <fcntl.h>
typedef struct
{
    char   firstname[20];
    char   lastname[30];
    char   mi;
    char   id[15];
    char   email[40];
} Person;
main(int argc, char *argv[])
{
    DBM *people;
    datum data;
    Person *person;
    if (argc != 2) exit(1);
    people = ndbm_open("people", O_RDWR, 0644);
    key.dptr = argv[1];
    key.dsize = strlen(argv[1]);
    data = ndbm_fetch(people, key);
    person = (Person *) data.dptr;
    printf("%s %c %s: %s\n",
        person->firstname,
        person->mi,
        person->lastname,
        person->id);
    dbm_close(people);
    return 0;
}

gdbm

GNU enhanced version of the dbm library

Features:

• One table per database
• Variable length records (No limit)
• Separate key
• No duplicate keys allowed
• Concurrent access control
• Caching

The library has a ndbm compatibility mode

Structure of programs is the same as ndbm programs

gdbm API

GDBM_FILE gdbm_open(char *name, int block_size, int read_write, int mode, void (*fatal_func)());

void gdbm_close(GDBM_FILE dbf);

int gdbm_store(GDBM_FILE dbf, datum key, datum content, int flag);

datum gdbm_fetch(GDBM_FILE dbf, datum key);

int gdbm_reorganize(GDBM_FILE dbf);

void gdbm_sync(GDBM_FILE dbf);

int gdbm_exists(GDBM_FILE dbf, datum key);

char *gdbm_strerror(gdbm_error errno);

int gdbm_setopt(GDBM_FILE dbf, int option, int *value, int size);

Metal Base

Public domain relational database

external schema (record) definition

General data entry/form capability

Fixed length records

Concurrent access control through table locking

Mbase example program

#include <mbase.h>
#include "equipment.h"
main()
{
    relation *rel;
    /* Open relation */
    rel = mb_inc("/usr/joe/equipment", strtokey("key"));
    /* Locate a record */
    equipment_rec.price_code = 114.20;
    strcpy(equipment_rec.part_numb, "117");
    mb_sel(rel, 0, &equipment_rec, EQUAL, NULL);
    /* Change the found record */
    equipment_rec.num_purch = 14;
    strcpy(equipment_rec.customer, "Su");
    strcpy(equipment_rec.part_numb, "112");
    strcpy(equipment_rec.show_addr, "Dallas, TX");
    equipment_rec.price_code = 12;
    mb_upd(rel, &equipment_rec);
    /* Cleanup */
    mb_rmv(rel);
    mb_exit(0);
}