folder

CREATE TABLE folder (
  identifier            integer NOT NULL PRIMARY KEY,
  parent_folder         integer REFERENCES folder(identifier),
  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,
  password              text,,
  date_changed          timestamp without time zone,
  event                 integer REFERENCES calendar(identifier)
);

• access_rights references access_type(identifier)
• event references calendar(identifier)
• parent_folder references folder(identifier) self reference
• users references users(identifier)

• folder(parent_folder) references identifier self reference
• photo(folder) references identifier

• folder_id_sequence

• folder_event_idx
• folder_id_idx removed in 2.34
• folder_parent_idx
• folder_users_idx

• count_folders_by_user
• count_photos_by_user
• count_subfolders_by_folder

None.

Version 2.5

Still in use

= Version 2.5 =

CREATE TABLE folder (
  identifier            integer NOT NULL PRIMARY KEY,
  parent_folder         integer REFERENCES folder(identifier),
  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.20 =

ALTER TABLE folder ADD orderby integer;

= Version 2.26 =

ALTER TABLE folder ADD COLUMN description text;

= Version 2.34 =

-- Add password support to folders
ALTER TABLE folder ADD password text;

ALTER TABLE folder ADD date_changed timestamp without time zone;

-- Drop 'orderby' fields; they're useless now.
alter table folder drop orderby;

-- Associate events with folders
ALTER TABLE folder ADD COLUMN event integer REFERENCES calendar(identifier);

= Version 2.5 =

INSERT INTO folder VALUES (1, NULL, 1, 'Trash', now(), 3);

= Version 2.20 =

UPDATE folder SET orderby = 1;

= Version 2.29 =

-- create a special Orphanage for deleted photos that were involved in some kind of transaction
INSERT INTO folder (identifier, parent_folder, users, caption, date_of_creation, access_rights, orderby, description)
  SELECT NEXTVAL('folder_id_sequence') AS identifier,
         NULL AS parent_folder,
         identifier AS users,
         'Orphanage' AS caption,
         now() AS date_of_creation,
         3 AS access_rights,
         7 AS orderby,
         NULL AS description
    FROM users;