User Tools

Site Tools


podoc:album_sql_table

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision
Previous revision
podoc:album_sql_table [2007/04/18 13:41] Luudpodoc:album_sql_table [2007/10/19 20:45] (current) Luud
Line 15: Line 15:
 === Schema === === Schema ===
  
-<code>+<code sql>
 create table album ( create table album (
   identifier        integer not null primary key,   identifier        integer not null primary key,
Line 22: Line 22:
   date_of_creation  timestamp,   date_of_creation  timestamp,
   access_rights     integer not null references access_type(identifier),   access_rights     integer not null references access_type(identifier),
-  type              char(1) check (type in ('c', 'u')), 
-  orderby           integer, 
   description       text,   description       text,
-  parent_album      integer references album(identifier)+  parent_album      integer references album(identifier), 
 +  password          text, 
 +  date_changed      timestamp without time zone, 
 +  event             integer references calendar(identifier)
   );   );
 </code> </code>
 +
 +=== References tables ===
 +
 +  * access_rights references [[access_type sql table|access_type]](identifier)
 +  * event references [[calendar sql table|calendar]](identifier)
 +  * parent_album references [[album sql table|album]](identifier) // **self reference** //
 +  * users references [[users sql table|users]](identifier)
 +
 +=== Is referenced by tables ===
 +
 +  * [[album sql table|album]](parent_album) references identifier // ** self reference ** //
 +  * [[album_content sql table|album_content]](album) references identifier
  
 === Related sequences === === Related sequences ===
Line 35: Line 48:
 === Related indexes === === Related indexes ===
  
-  * [[album_id_idx sql index|album_id_idx]]+  * [[album_event_idx sql index|album_event_idx]] 
 +  * <del>[[album_id_idx sql index|album_id_idx]]</del> removed in 2.34
   * [[album_users_idx sql index|album_users_idx]]   * [[album_users_idx sql index|album_users_idx]]
  
Line 46: Line 60:
  
 === Related views === === Related views ===
 +
 +None.
  
 === History === === History ===
Line 61: Line 77:
 = Version 2.5 = = Version 2.5 =
  
-  create table album ( +<code sql> 
-    identifier            integer not null primary key, +create table album ( 
-    users                 integer not null references users(identifier),       -- the users who uploaded the picture, not necessarily the owner +  identifier            integer not null primary key, 
-    caption               text, +  users                 integer not null references users(identifier),       -- the users who uploaded the picture, not necessarily the owner 
-    date_of_creation      timestamp, +  caption               text, 
-    access_rights         integer not null references access_type(identifier) +  date_of_creation      timestamp, 
-  );+  access_rights         integer not null references access_type(identifier) 
 +); 
 +</code>
  
 = Version 2.18 = = Version 2.18 =
  
-<code>+<code sql>
 -- 'c' for customers, 'u' for user albums -- 'c' for customers, 'u' for user albums
 alter table album add column type char(1) check (type in ('c', 'u'));  alter table album add column type char(1) check (type in ('c', 'u')); 
Line 78: Line 96:
 = Version 2.20 =  = Version 2.20 = 
  
-<code>+<code sql>
 alter table album add orderby integer; alter table album add orderby integer;
 </code> </code>
Line 84: Line 102:
 = Version 2.26 = = Version 2.26 =
  
-<code>+<code sql>
 alter table album add column description text; alter table album add column description text;
 </code> </code>
Line 90: Line 108:
 = Version 2.29 = = Version 2.29 =
  
-<code>+<code sql>
 alter table album add column  parent_album integer references album(identifier); alter table album add column  parent_album integer references album(identifier);
 +</code>
 +
 += Version 2.34 =
 +
 +<code sql>
 +-- Add password support to albums
 +alter table album add password text;
 +</code>
 +
 +<code sql>
 +alter table album add date_changed timestamp without time zone;
 +</code>
 +
 +<code sql>
 +-- Drop 'orderby' fields; they're useless now.
 +alter table album drop orderby;
 +</code>
 +
 +<code sql>
 +-- Associate events with albums
 +alter table album add column event integer references calendar(identifier);
 +</code>
 +
 +<code sql>
 +-- this is obselete                    
 +alter table album drop column type;
 </code> </code>
  
Line 98: Line 142:
 = Version 2.18 = = Version 2.18 =
  
-<code>+<code sql>
 update album set type = 'u'; update album set type = 'u';
 </code> </code>
Line 104: Line 148:
 = Version 2.20 = = Version 2.20 =
  
-<code>+<code sql>
 update album set orderby = 1; update album set orderby = 1;
 </code> </code>
Line 110: Line 154:
 = Version 2.29 =  = Version 2.29 = 
  
-<code>+<code sql>
 update album set parent_album=null; update album set parent_album=null;
 +</code>
 +
 += Version 2.34 =
 +
 +<code sql>
 +-- 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;
 </code> </code>
  
podoc/album_sql_table.txt · Last modified: 2007/10/19 20:45 by Luud