===== Database Table Description of "folder" =====

=== Name ===

folder

=== Description ===

=== Purpose ===

=== Schema ===

<code sql>
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)
);
</code>

=== References tables ===

  * access_rights references [[access_type sql table|access_type]](identifier)
  * event references [[calendar sql table|calendar]](identifier)
  * parent_folder references [[folder sql table|folder]](identifier) // **self reference** //
  * users references [[users sql table|users]](identifier)

=== Is referenced by tables ===

  * [[folder sql table|folder]](parent_folder) references identifier // **self reference** //
  * [[photo sql table|photo]](folder) references identifier

=== Related sequences ===

  * [[folder_id_sequence sql sequence|folder_id_sequence]]

=== Related indexes ===

  * [[folder_event_idx sql index|folder_event_idx]]
  * <del>[[folder_id_idx sql index|folder_id_idx]]</del> removed in 2.34
  * [[folder_parent_idx sql index|folder_parent_idx]]
  * [[folder_users_idx sql index|folder_users_idx]]

=== Related functions ===

  * [[count_folders_by_user sql function|count_folders_by_user]]
  * [[count_photos_by_user sql function|count_photos_by_user]]
  * [[count_subfolders_by_folder sql function|count_subfolders_by_folder]]

=== Related views ===

None.

=== History ===

== Used first ==

Version 2.5

== Used last ==

Still in use

== Schema change history ==

= Version 2.5 =

<code sql>
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)
);
</code>

= Version 2.20 =

<code sql>
alter table folder add orderby integer;
</code>

= Version 2.26 =

<code sql>
alter table folder add column description text;
</code>

= Version 2.34 =

<code sql>
-- Add password support to folders
alter table folder add password text;
</code>

<code sql>
alter table folder add date_changed timestamp without time zone;
</code>

<code>
-- Drop 'orderby' fields; they're useless now.
alter table folder drop orderby;
</code>

<code sql>
-- Associate events with folders
alter table folder add column event integer references calendar(identifier);
</code>

== Data change history ==

= Version 2.5 =

<code sql>
insert into folder values (1, null, 1, 'Trash', now(), 3);
</code>

= Version 2.20 =

<code sql>
update folder set orderby = 1;
</code>

= Version 2.29 =

<code sql>
-- 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;
</code>