White dot for spacing only
The Dice Project


DICE Web Team - Open Source Database

This page is out of date. The service has been implemented as the ISDD. Some of the follow still holds, but real docs on how to manage the system are missing. There are some tips at the bottom.

Initial spec

Current Design Decisions

The database is PostgreSQL, I used the their docs pages a lot! To get to the DB, run psql -h localhost osdb as wwwrun.
Backend Database
Store data about authors, packages and accounting info in 3 tables. The actual source tarball will be held in the file system, not in the DB, it will only contain a pointer to the source. The 3 tables will be:
Author Table
Details about the author: uun, name, school/college, email address, enabled:
CREATE TABLE authors (
    uun character varying(20) PRIMARY KEY,
    surname character varying(100),
    firstname character varying(100),
    school character varying(100),
    email character varying(100),
    enabled boolean DEFAULT true
);

ALTER TABLE ONLY authors
    ADD CONSTRAINT authors_pkey PRIMARY KEY (uun);

Table "authors"
Attribute Type Modifier
uun varchar(20)  
surname varchar(100)  
firstname varchar(100)  
school varchar(100)  
email varchar(100)  
enabled boolean  
Package Table
Details about the package: authorID, name and version, location of src, size of package, supporting/doc URL, keywords, which declarations apply (field for each one), which extras are offered (field for each one), which license(s) apply (field for each one), date author agreed/uploaded the package, is the package downloadable, is it deleted
CREATE TABLE packages (
 id SERIAL PRIMARY KEY,
 uun character varying(20) references authors,
 src text,
 size int,
 name text NOT NULL,
 version text,
 keywords text NOT NULL,
 deleted boolean DEFAULT false NOT NULL,
 visible boolean DEFAULT true NOT NULL,
 url text,
 views int,
 downloads int,
 prevdownloads int,
 licinq int,
 consinq int,
 dec1 boolean,
 dec2 boolean,
 dec3 boolean,
 dec4 boolean,
 dec5 boolean,
 dec6 boolean,
 dec7 boolean,
 dec8 boolean,
 dec9 boolean,
 dec10 boolean,
 lic1 boolean,
 lic2 boolean,
 lic3 boolean,
 lic4 boolean,
 lic5 boolean,
 lic6 boolean,
 date timestamp
);


ALTER TABLE ONLY packages
    ADD CONSTRAINT packages_pkey PRIMARY KEY (id);

ALTER TABLE ONLY packages
    ADD CONSTRAINT "$1" FOREIGN KEY (uun) REFERENCES authors(uun)
    ON UPDATE NO ACTION ON DELETE NO ACTION;

ALTER TABLE ONLY packages
    ADD CONSTRAINT unique_package UNIQUE (uun, name, "version");


Table "packages"
Attribute Type Modifier
id integer not null default nextval('public.packages_id_seq'::text)
uun varchar(20)  
src text  
size integer  
name text not null
version text  
kwords text not null
deleted boolean  
visible boolean  
url text  
views integer  
downloads integer  
prevdownloads integer  
licinq integer  
consinq integer  
dec1 boolean  
dec2 boolean  
dec3 boolean  
dec4 boolean  
dec5 boolean  
dec6 boolean  
dec7 boolean  
dec8 boolean  
dec9 boolean  
dec10 boolean  
lic1 boolean  
lic2 boolean  
lic3 boolean  
lic4 boolean  
lic5 boolean  
lic6 boolean  
date timestamp  
Index: packages_pkey
Access Table
Records the access details of downloads
CREATE TABLE "access" (
    date timestamp without time zone,
    package integer,
    country text,
    remoteaddr inet
);

Table "public.access"
Column Type Modifiers
date timestamp without time zone  
package integer  
country text  
remoteaddr inet  

To initialise the Database from scratch, and allow the 'wwwrun' user to access it (see beezer.inf for the necessary LCFG to configure things initially) do, this is on drooit.

createdb -U postgres osdb
psql -U postgres osdb
>> Now in SQL mode
CREATE USER wwwrun;
You can now nsu to wwwrun and do psql -h localhost osdb and paste in the SQL above to create the tables and indexes.

We won't keep any date or yearly access records, just the lifetime total. The thing sucking out the info from the Stats table can do that.

Authorisation
Should anyone with a Informatics account automatically be able to log? Some sort of vetting procedure? Using kx509 should give us a lot of this for free.
Authors page
Having successfully created an account and logged in, the author can choose to do one of three things:
  1. Update their details
  2. Upload a new package
  3. Remove a package
Search page
As well as being able to search for software, people need to be able to browse and see things like: the most popular, recently updated, packages.

Tips

Changing the ownership of a package

People leave, or requish their ownership of a package. As typically on the owner of the package and modify it, or check its stats, you may need to reassign ownership. To do this you need to resort to SQL. You'll probably need to know the package id of the one you want to change.


 : Doc 

Mini Informatics Logo - Link to Main Informatics Page
Please contact us with any comments or corrections.
Unless explicitly stated otherwise, all material is copyright The University of Edinburgh
Spacing Line