Photo Organizer

album

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

We need to keep track of this stuff.

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)
  );

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

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

• album_id_sequence

• album_event_idx
• album_id_idx removed in 2.34
• album_users_idx

• count_albums_by_user
• count_photos_by_album
• count_photos_by_user
• count_subalbums_by_album

None.

Version 2.5

Still in use

= 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;

= 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;