User Tools

Site Tools


podoc:album_sql_table

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),
  description       text,
  parent_album      INTEGER REFERENCES album(identifier),
  password          text,
  date_changed      TIMESTAMP WITHOUT TIME zone,
  event             INTEGER REFERENCES calendar(identifier)
  );

References tables

  • access_rights references access_type(identifier)
  • event references calendar(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);

= Version 2.34 =

-- Add password support to albums
ALTER TABLE album ADD password text;
ALTER TABLE album ADD date_changed TIMESTAMP WITHOUT TIME zone;
-- Drop 'orderby' fields; they're useless now.
ALTER TABLE album DROP orderby;
-- Associate events with albums
ALTER TABLE album ADD COLUMN event INTEGER REFERENCES calendar(identifier);
-- this is obselete                    
ALTER TABLE album DROP COLUMN TYPE;
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;

= Version 2.34 =

-- Add the 'Spool' Album for everyone.
 
INSERT INTO album (identifier, parent_album, users, caption, TYPE,
                    date_of_creation, access_rights, orderby, description)
  SELECT NEXTVAL('album_id_sequence') AS identifier,
         NULL AS parent_album,
         identifier AS users,
         'Spool' AS caption,
         'u' AS TYPE,
         now() AS date_of_creation,
         3 AS access_rights,
         7 AS orderby,
         NULL AS description
    FROM users;
podoc/album_sql_table.txt · Last modified: 2007/10/19 16:45 by Luud