OpenACS

maltes
committed
MAIN:maltes:20061108144207
on 08 Nov 06
A couple of minor fixes
openacs-4/.../postgresql/postcard-create.sql (+126 -4)
1 1 -- electronic postcard
  2
  3
  4 /* first we create the permissions model for postcards
  5
  6   defining these basic actions
  7   
  8   - upload images
  9   - view postcard
  10   - moderate images
  11   - create card to send
  12
  13   and then define the base security privileges that we
  14   want to have. applications that want to designate
  15   to the end user fine grained permission control, should
  16   designate appropiate basic privileges as the atomic level
  17   of an applications/services security.
  18
  19 */
  20 begin;
  21
  22  select acs_privilege__create_privilege('postcard_create_image',null,null);
  23  select acs_privilege__create_privilege('postcard_create_card',null,null);
  24  select acs_privilege__create_privilege('postcard_read',null,null);
  25  select acs_privilege__create_privilege('postcard_admin',null,null);
  26
  27
  28  -- bind privileges to privilege heirarchy
  29
  30  -- temporarily drop this trigger to avoid a data-change violation
  31  -- on acs_privilege_hierarchy_index while updating the child privileges.
  32
  33  drop trigger acs_priv_hier_ins_del_tr on acs_privilege_hierarchy;
  34
  35  select acs_privilege__add_child('create', 'postcard_create_image');
  36  select acs_privilege__add_child('create', 'postcard_create_card');
  37  select acs_privilege__add_child('read', 'postcard_read');
  38
  39  -- re-enable the trigger before the last insert to force the
  40  -- acs_privilege_hierarchy_index table to be updated.
  41
  42  create trigger acs_priv_hier_ins_del_tr after insert or delete
  43  on acs_privilege_hierarchy for each row
  44  execute procedure acs_priv_hier_ins_del_tr ();
  45
  46  select acs_privilege__add_child('admin','postcard_admin');
  47 end;
  48
  49
  50 /*
  51    Creating permissions basically involves binding the
  52    privilege to the object system.
  53
  54    permissions involve binding a particular privilege
  55    to a user in a given context.
  56
  57    We grant the permissions to the public
  58
  59    inline function - inline funcs are used to allow
  60    calling pl/pgsql.
  61 */
  62  
  63 create function inline_0 ()
  64 returns integer as '
  65 declare
  66     default_context integer;
  67     registered_users integer;
  68     the_public integer;
  69 begin
  70
  71     default_context := acs__magic_object_id(''default_context'');
  72     registered_users := acs__magic_object_id(''registered_users'');
  73     the_public := acs__magic_object_id(''the_public'');
  74
  75     -- give registered users the power to post by default
  76
  77     perform acs_permission__grant_permission (
  78         default_context,
  79         registered_users,
  80         ''postcard_create_card''
  81     );
  82
  83     perform acs_permission__grant_permission (
  84         default_context,
  85         registered_users,
  86         ''postcard_create_image''
  87     );
  88
  89     -- give the public the power to read by default
  90
  91     perform acs_permission__grant_permission (
  92         default_context,
  93         the_public,
  94         ''postcard_read''
  95     );
  96
  97     return 0;
  98 end;
  99 ' language 'plpgsql';
  100
  101 select inline_0 ();
  102 drop function inline_0 ();
  103
  104 /* basic data model
  105
  106 this needs to get migrated to the content-repository
  107
  108 for now the original data model is utilized.
  109
  110 */
  111
2 112 create sequence postcard_seq;
  113 create view postcard_sequence as
  114         select nextval('postcard_seq');
  115         
3 116 create sequence postcard_image_seq;
  117 create view postcard_image_sequence as
  118         select nextval('postcard_image_seq');
4 119
5 120 create table postcard_images (
6 121   card_image_id integer primary key,
7     image         oid not null,
  122   lob         integer references lobs,
8 123   mime_type     varchar(100),
9 124   title         varchar(1000),
10 125   description   varchar(4000)
11 126 );
12 127
  128 -- to enable pg lob support see kernel/sql/postgresql/lobs.sql
  129 create trigger postcard_images_lob_trigger before delete or update or insert
  130 on postcard_images for each row execute procedure on_lob_ref();
  131
13 132 create table postcards (
14 133   card_id      integer primary key,
15     card_picture references postcard_images,
  134   card_picture integer references postcard_images,
16 135   recipient    varchar(1000),
17 136   sender       varchar(1000),
18 137   message      varchar(4000),
19 138   pickup_code  varchar(1000),
20 139   picked_up    date
21 140 );
22 141
23 142
  143
  144
  145