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:


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:


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:

Examples:

Advantages:


Fixed vs. Variable length records (continued)

Disadvantages:

Fixed-length records

Advantages:

Disadvantages:


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:


Indexing issues

Issues to consider when using indexes:

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:

Other problems:


Database APIs

We will look at several databases


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

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(?):

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:

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);
}