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
Related sequences
Related indexes
album_id_idxremoved in 2.34
Related functions
Related views
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 20:45 by Luud