Code > Database Schema
/*
disks - Information about the floppy disks in the collection.
title - Disk title found on the label. May be NULL if
unknown or generically titled (e.g. "MAG Disk").
uri_name - A unique short name suitable for construction of URIs.
Typically YYYYMM. In cases of years and months that have
more than one disk, then YYYYMM-NN where NN starts at 01.
issue_date - The month and year the disk was issued.
date_exact - Indicates if issue_date is verified exact. Otherwise
the date is a guess based on the contents of the disk.
volume - The AmigaDOS volume name of the disk. Currently unused.
type - AmigaDOS disk type (OFS or FFS). Currently unused.
good_dump - Is the disk image a good dump with no corrupt sectors?
image - Scanned image of the disk label.
adf - The full disk dump.
*/
CREATE TABLE disks (
id SERIAL PRIMARY KEY,
title VARCHAR(128),
uri_name VARCHAR(128) NOT NULL UNIQUE,
issue_date DATE,
date_exact BOOLEAN DEFAULT FALSE,
volume VARCHAR(32),
type VARCHAR(4),
good_dump BOOLEAN,
image BYTEA,
adf BYTEA
);
/*
filesystem - Information about the contents of the floppy disks. In
this section, "object" refers to a file or directory.
parent_id - The ID of the directory the object is in. NULL when the
object is in the root directory of the disk.
name - The object name (i.e. filename or directory name.)
dir - True if the object is a directory.
type - A one-word type of the object (e.g. archive, audio,
binary, directory, executable, icon, image, or text.)
While the mimetype and longtype files were populated
directly with the output of the Linux "file" command,
this field was populated by parsing those two fields.
The "binary" type is the default when a more specific
type couldn't be deteremined. The mimetype and longtype
fields are null when type is "directory".
mimetype - The mimetype of the file, used for downloads. Example:
"image/gif"
longtype - The file's type as reported by "file". Example: "GIF
image data, version 87a"
time_stamp - The AmigaDOS timestamp of the object.
interesting - Indicates a file that is "interesting". Used to populate
the random "interesting" texts and images on the front
page of the site. A cooresponding row should exists in
the texts or images tables.
contents - The contents of the file itself.
*/
CREATE TABLE filesystem (
id SERIAL PRIMARY KEY,
disk_id INTEGER NOT NULL REFERENCES disks (id),
parent_id INTEGER REFERENCES filesystem (id),
name VARCHAR(128) NOT NULL,
dir BOOLEAN NOT NULL DEFAULT FALSE,
type VARCHAR(16),
mimetype VARCHAR(64),
longtype VARCHAR(128),
time_stamp TIMESTAMP WITHOUT TIME ZONE,
interesting BOOLEAN NOT NULL DEFAULT FALSE,
contents BYTEA
);
CREATE INDEX filesystem_disk_id_index ON filesystem (disk_id);
CREATE INDEX filesystem_parent_id_index ON filesystem (parent_id);
CREATE INDEX filesystem_name_index ON filesystem (name);
CREATE INDEX filesystem_interesting_index ON filesystem (interesting);
CREATE TABLE images (
id INTEGER NOT NULL REFERENCES filesystem (id),
width INTEGER,
height INTEGER,
th_width INTEGER NOT NULL,
th_height INTEGER NOT NULL,
conversion BOOLEAN NOT NULL DEFAULT TRUE,
contents BYTEA,
thumbnail BYTEA NOT NULL,
rand DOUBLE PRECISION NOT NULL DEFAULT RANDOM()
);
CREATE INDEX images_id_index ON images (id);
CREATE TABLE texts (
id INTEGER NOT NULL REFERENCES filesystem (id),
description VARCHAR(512) NOT NULL,
rand DOUBLE PRECISION NOT NULL DEFAULT RANDOM()
);
CREATE INDEX texts_id_index ON texts (id);
CREATE TABLE stats (
hits INTEGER NOT NULL
);
INSERT INTO stats (hits) VALUES (0);
/*
// This bit of wonder allows us to build the full path to an arbitrary file
// using only its ID.
//
// => SELECT name, filesystem_path_by_id(id) FROM filesystem WHERE id = 3493;
// name | filesystem_path_by_id
// -------------------+------------------------------------------------
// UtilityDock.brush | 199105-02/AmiDock/dock/Non-Interlace/MainDock/
*/
CREATE FUNCTION filesystem_path_by_id(file_id integer) RETURNS TEXT AS
$$
DECLARE
disk_name text;
full_path text := '';
tmp_record record;
BEGIN
SELECT INTO tmp_record fs.id AS fid, fs.parent_id,
disks.url_name FROM filesystem AS fs, disks
WHERE fs.disk_id = disks.id AND fs.id = file_id;
IF (tmp_record.fid IS NULL) THEN
RAISE EXCEPTION 'No such file ID %', file_id;
END IF;
disk_name := tmp_record.url_name;
WHILE (tmp_record.parent_id IS NOT NULL) LOOP
SELECT INTO tmp_record parent_id, name FROM filesystem
WHERE id = tmp_record.parent_id;
full_path := tmp_record.name || '/' || full_path;
END LOOP;
RETURN disk_name || '/' || full_path;
END
$$
LANGUAGE plpgsql;
