Code > Database Schema

Download Plaintext Version
/*
    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;