mersenneforum.org  

Go Back   mersenneforum.org > Great Internet Mersenne Prime Search > PrimeNet

Reply
 
Thread Tools
Old 2003-01-18, 00:28   #1
Prime95
P90 years forever!
 
Prime95's Avatar
 
Aug 2002
Yeehaw, FL

7,351 Posts
Default Database layout

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 is offline   Reply With Quote
Old 2003-01-18, 00:49   #2
Prime95
P90 years forever!
 
Prime95's Avatar
 
Aug 2002
Yeehaw, FL

7,351 Posts
Default

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
Prime95 is offline   Reply With Quote
Reply

Thread Tools


Similar Threads
Thread Thread Starter Forum Replies Last Post
Database for k-b-b's: 3.14159 Miscellaneous Math 325 2016-04-09 17:45
CPU Performance Database JustinGC Hardware 3 2012-06-22 03:11
Database question Xyzzy Forum Feedback 0 2012-04-01 15:37
NPLB Database IronBits No Prime Left Behind 177 2009-10-10 09:00
database.zip HiddenWarrior Data 1 2004-03-29 03:53

All times are UTC. The time now is 07:35.

Sat Feb 27 07:35:16 UTC 2021 up 86 days, 3:46, 0 users, load averages: 2.35, 1.74, 1.63

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.

This forum has received and complied with 0 (zero) government requests for information.

Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.2 or any later version published by the Free Software Foundation.
A copy of the license is included in the FAQ.