Photo Organizer

(See also 8)

• Allow non-owners to make changes
• Allow individual “clients” access to different sets of photos
• Allow finer-grained control over original images

This permission model will be group-based; individual users will have a personal “group”, but can exist in any number of groups. “Guests” will have their own special group, and permissions can be assigned to them like any other group.

By default, no groups will have permission to do anything, other than the administrator, who has full access to everything. Permissions are explicitly granted on an image-by-image basis, so if no specific permission is set on the image for a particular group, access will be denied.

This allows “group ownership” of images, folders, etc, and allows for individual user control while allowing them to share a common set of images.

-- as of 4/26/2007 @12:15pm

create sequence groups_sequence start 0;

-- 'date_added' is set via trigger.

create table groups (
      identifier integer not null primary key,
      description text,
      password text,                         -- group password, if any...
      date_added timestamp without time zone
);

create table group_memberships (
      group_id integer not null references groups(identifier),
      user integer not null references users(identifier),
      owner boolean not null default 'f', -- aka can change permissions
      date_added timestamp without time zone
);

-- setting 'owner' to 't' implicitly sets 'view' to 't' (via trigger)
-- setting 'edit' to 't' implicitly sets 'caption' to 't' (via trigger)

create table photo_permissions (
      photo integer not null references photo(identifier),
      group_id integer not null references groups(identifier),
      -- basic permissions per-image
      owner boolean not null, -- aka can change permissions
      view boolean not null, -- aka thumbnail/preview & basic info
      details boolean not null, -- view all detailed info on image
      original boolean not null, -- access original
      edit boolean not null, -- modify 
      caption boolean not null, -- modify keywords/captions/etc
      delete boolean not null -- remove
);

create table folder_permissions (
      folder integer not null references folder(identifier),
      group_id integer not null references groups(identifier),
      -- default image permissions for this folder
      owner boolean not null, -- aka can change permissions
      edit boolean not null, -- modify 
      delete boolean nut null, -- remove
      list boolean not null, -- display folder & view photo list
      modify boolean not null -- add/remove entries, (photos and/or subfolders)
);

create table album_permissions (
      album integer not null references album(identifier),
      group_id integer not null references groups(identifier),
      -- default image permissions for this album
      owner boolean not null, -- aka can change permissions
      edit boolean not null, -- modify 
      delete boolean nut null, -- remove
      list boolean not null, -- display album & view photo list
      modify boolean not null -- add/remove entries, (photos and/or subalbums)
);

-- Lookup functions go here!


-- post-migration we need to:

drop table client;
drop table client_status;

alter table photo drop users;
alter table folder drop users;
alter table album drop users;

alter table photo drop hide_original;
alter table photo drop access_rights;
alter table folder drop access_rights;
alter table album drop access_rights;

------- not sure about these:

-- alter table users drop type; 
-- drop table user_type;
-- drop sequence user_type_sequence;

-- Don't forget about camera/film/filter/flash/support/scanner/location/clients
-- as they also have user/owners..
-- We also need Indices!

This code is roughly what we need to do to see if an image/folder/album has the appropriate permissions. Before it goes into production it'll need to be implemented on the database using pl/pgsql.

// type == photo, group, album
// identifier == photo#, group#, album#
// user == userid
// permission == view/original/edit/caption/delete/details list/modify
// returns true if allowed, or false if no match found or denied.
function detail_permission(user, type, identifier, permission) {
      "SELECT g.group_id, p.$permission
        FROM $type_permissions p,
       WHERE p.$type = $identifier
         AND p.group_id in (SELECT 1 UNION SELECT g.group_id from group_memberships g
                            WHERE g.user = $user);"

      foreach ($results as $result) {
              if ($result == true) {
                      return true;
              }
      }
      return false;
}

function user_in_admin_grp($user) {
    $res = "SELECT userid from group_memberships where user = $user and group_id = 0";

    return (num_rows($res) > 0));
}

// view == "folder", "album", "photo", "search", etc...
function permission(user, photo, permission, view) {
      if ($user == NULL) user = "null";
      // members of admin group get all permissions implicitly.
      if (user_in_admin_grp($user)) return TRUE;

      // explicit permission on the photo trumps everything else.
      $stored = detail_permission($user, 'photo',
                                  $photo, $permission);
      return $stored;
}

A few comments:

• Admin group id is 0. Guest group id is 1. These are fixed.
• Guest users have a NULL userid, which has to be handled cleanly.
• All users (and guests) are implicitly members of the 'guests' group.

The goal of this migration is to transform all existing permissions to the new model.

1. Create new tables. (see above)
2. Create group 0, “Admin Users”, and populate it with all admin users.
3. Create group 1, “Guests”
4. Create group 2, “Registered Users”.

1. If user is admin, add user to “Admin Users” group as an owner.
2. Add user to “Registered Users” group.
3. Create a new “$user's private” group, add user to it as the owner.
4. Create a new “$user's clients” group, add user to it as the owner. Then add all of user's clients to it.
5. Create a group for each individual client and add user to it as the owner, then add the client to it.

1. Add row granting owner all rights to the folder.
2. if access_rights is private, stop here.
3. if access_rights is protected (ie clients-only) add a row granting read access to the client group.
4. if access_rights is public (ie everyone) add a row granting read access to guest group.

1. Add row granting owner all rights to the folder.
2. if access_rights is private or album_type is 'client', stop here.
3. if access_rights is protected (ie clients-only) add a row granting read access to the client group.
4. if access_rights is public (ie everyone) add a row granting read access to guest group.

1. Add row granting owner all rights.
2. if access_rights is protected (ie clients-only) add a row granting read access to the client group.
3. if access_rights is public (ie everyone) add a row granting read access to guest group.
4. If access_rights is private, stop here.

Note – the 'hide_original' setting will be respected for these entries

1. Drop all obseleted columns and tables from the database (see above)

1. Schema finalization
2. Migration code for installer
3. Write PL/pgsql permission lookup code and any necessary triggers.
4. Define default permission sets for new users and new folders/albums/photos. In other words, each new photo etc gets permissions G1→X, G2→Y, G3→Z)
5. Port account creation/registration (auto-create necessary groups, etc)
6. Port admin pages (account status, etc)
7. Port photo & version import pages
8. Port photo view/description/edit pages
9. Port folder/album/search/print/export/spool/etc (ie photo listing) pages
10. Port folder/album add/edit/delete pages (don't forget password-protection!)
11. Port bulk update
12. Create Group management UI elements
13. Create Permission management UI elements (for photo add and bulk update too!)
14. Port over equipment/profile pages. (yuck)