Database layout

[Prime95]

Here are the tables I created a year ago when I was evaluating PostgreSQL:


create table factors (p int4 not null, factor numeric (99, 0) not null)
create unique index factors_index on factors (p)

create table nofactors (p int4 not null, bits int2 not null, B1 numeric(12,0), B2 numeric(12,0))
create unique index nofactors_index on nofactors (p)

create table LL_tests (p int4 not null, type int2 not null, uid varchar(14) not null, compid varchar(12), progid varchar(6) not null, residue varchar(16) not null, shiftcount int4, errors varchar(8))
create index ll_index on LL_tests (p)
create index ll_index_userid on LL_tests (uid)


create table timings (fftsize int4 not null, start_p int4 not null, end_p int4 not null, timing float4 not null)


create table users (userid varchar(14) not null, password varchar(8) not null, name varchar(80), emailaddr varchar(80), newsletters boolean not null, creationdate timestamp not null)
create index usr_index on users (userid)

create table computers (userid varchar(14) not null, computer varchar(12) not null, cputype int2, speed int4, client_version int2)
create index comp_index on computers (userid, computer)



create table reservations (p int4 not null, bits int4 not null, pminus1 boolean not null, resvdate timestamp, expcompdate timestamp, expiredate timestamp, userid varchar(14), computer varchar(12), iteration int4)
create unique index resv_index on reservations (p)
create index resvuid_index on reservations (userid)

create table next_reservation (work_type int4 not null, p int4 not null)
create unique index nextresv_index on next_reservation (work_type)

[Prime95]

Here is some pseudo code extracted from my test code to get a reservation:

/* Start a transaction */

BEGIN

/* Get the last exponent assigned for this work type */
/* It is quicker to scan for the next available reservation searching */
/* forward from the last reservation rather than rescanning the entire table */
/* Once a day we can reset the next available reservation value to zero */

sprintf (stmt, "DECLARE mycursor CURSOR FOR select p from next_reservation where work_type = %d FOR UPDATE OF next_reservation", work_type);
FETCH ALL in mycursor"
p = atoi (PQgetvalue (res, 0, 0));
CLOSE mycursor

/* Now find next available reservation */

sprintf (stmt, "DECLARE mycursor CURSOR FOR select p, bits, pminus1 from reservations where p > %u and expiredate < CURRENT_TIMESTAMP for update of reservations limit 1", p);
"FETCH ALL in mycursor"
p = atoi (PQgetvalue (res, 0, 0));
bits = atoi (PQgetvalue (res, 0, 1));
pminus1 = atoi (PQgetvalue (res, 0, 2));
CLOSE mycursor

/* Update reservation and next available reservation */

sprintf (stmt, "update next_reservation set p=%u where work_type = %d", p, work_type);
sprintf (stmt, "update reservations set resvdate=CURRENT_TIMESTAMP, expcompdate=CURRENT_TIMESTAMP+'30 days'::interval, expiredate=CURRENT_TIMESTAMP+'1 hours'::interval, userid='%s', computer='%s', iteration=null where p = %d", p, userid, computer);

/* Finish the transaction */

COMMIT