User Tools

Site Tools


podoc:folder_sql_table

Database Table Description of "folder"

Name

folder

Description

Purpose

Schema

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

References tables

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

Is referenced by tables

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

None.

History

Used first

Version 2.5

Used last

Still in use

Schema change history

= 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);
Data change history

= 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;
podoc/folder_sql_table.txt · Last modified: 2007/10/19 20:39 by Luud