User Tools

Site Tools


podoc:album_sql_table

This is an old revision of the document!


Database Table Description of "album"

Name

album

Description

This is the list of all albums in the system, including album specifics like their descriptions.

Purpose

We need to keep track of this stuff.

Schema

CREATE TABLE album (
  identifier        INTEGER NOT NULL PRIMARY KEY,
  users             INTEGER NOT NULL REFERENCES users(identifier), -- the users who uploaded the picture, not necessarily the owner
  caption           text,
  date_of_creation  TIMESTAMP,
  access_rights     INTEGER NOT NULL REFERENCES access_type(identifier),
  TYPE              CHAR(1) CHECK (TYPE IN ('c', 'u')),
  orderby           INTEGER,
  description       text,
  parent_album      INTEGER REFERENCES album(identifier)
  );

References tables

  • access_rights references access_type(identifier)
  • parent_album references album(identifier) self reference
  • users references users(identifier)

Is referenced by tables

  • album(parent_album) references identifier self reference
  • album_content(album) references identifier

None.

History

Used first

Version 2.5

Used last

Still in use

Schema change history

= Version 2.5 =

CREATE TABLE album (
  identifier            INTEGER NOT NULL PRIMARY KEY,
  users                 INTEGER NOT NULL REFERENCES users(identifier),       -- the users who uploaded the picture, not necessarily the owner
  caption               text,
  date_of_creation      TIMESTAMP,
  access_rights         INTEGER NOT NULL REFERENCES access_type(identifier)
);

= Version 2.18 =

-- 'c' for customers, 'u' for user albums
ALTER TABLE album ADD COLUMN TYPE CHAR(1) CHECK (TYPE IN ('c', 'u')); 

= Version 2.20 =

ALTER TABLE album ADD orderby INTEGER;

= Version 2.26 =

ALTER TABLE album ADD COLUMN description text;

= Version 2.29 =

ALTER TABLE album ADD COLUMN  parent_album INTEGER REFERENCES album(identifier);
Data change history

= Version 2.18 =

UPDATE album SET TYPE = 'u';

= Version 2.20 =

UPDATE album SET orderby = 1;

= Version 2.29 =

UPDATE album SET parent_album=NULL;
podoc/album_sql_table.1177074440.txt.gz · Last modified: 2007/04/20 13:07 by Luud