-- -- packages/acs-kernel/sql/acs-create.sql -- -- @author rhs@mit.edu -- @creation-date 2000-08-22 -- @cvs-id acs-create.sql,v 1.1.2.9 2000/08/24 07:09:18 rhs Exp -- create table acs_magic_objects ( name varchar2(100) constraint acs_magic_objects_pk primary key, object_id not null constraint acs_magic_objects_object_id_fk references acs_objects(object_id) ); create index acs_mo_object_id_idx on acs_magic_objects (object_id); comment on table acs_magic_objects is ' This table allows us to provide semantic names for certain special objects like the site-wide organization, and the all users party. '; create or replace package acs as function add_user ( user_id in users.user_id%TYPE default null, object_type in acs_objects.object_type%TYPE default 'user', creation_date in acs_objects.creation_date%TYPE default sysdate, creation_user in acs_objects.creation_user%TYPE default null, creation_ip in acs_objects.creation_ip%TYPE default null, authority_id in auth_authorities.authority_id%TYPE default null, username in users.username%TYPE, email in parties.email%TYPE, url in parties.url%TYPE default null, first_names in persons.first_names%TYPE, last_name in persons.last_name%TYPE, password in users.password%TYPE, salt in users.salt%TYPE, screen_name in users.screen_name%TYPE default null, email_verified_p in users.email_verified_p%TYPE default 't', member_state in membership_rels.member_state%TYPE default 'approved' ) return users.user_id%TYPE; procedure remove_user ( user_id in users.user_id%TYPE ); function magic_object_id ( name in acs_magic_objects.name%TYPE ) return acs_objects.object_id%TYPE; end acs; / show errors create or replace package body acs as function add_user ( user_id in users.user_id%TYPE default null, object_type in acs_objects.object_type%TYPE default 'user', creation_date in acs_objects.creation_date%TYPE default sysdate, creation_user in acs_objects.creation_user%TYPE default null, creation_ip in acs_objects.creation_ip%TYPE default null, authority_id in auth_authorities.authority_id%TYPE default null, username in users.username%TYPE, email in parties.email%TYPE, url in parties.url%TYPE default null, first_names in persons.first_names%TYPE, last_name in persons.last_name%TYPE, password in users.password%TYPE, salt in users.salt%TYPE, screen_name in users.screen_name%TYPE default null, email_verified_p in users.email_verified_p%TYPE default 't', member_state in membership_rels.member_state%TYPE default 'approved' ) return users.user_id%TYPE is v_user_id users.user_id%TYPE; v_rel_id membership_rels.rel_id%TYPE; begin v_user_id := acs_user.new( user_id => user_id, object_type => object_type, creation_date => creation_date, creation_user => creation_user, creation_ip => creation_ip, authority_id => authority_id, username => username, email => email, url => url, first_names => first_names, last_name => last_name, password => password, salt => salt, screen_name => screen_name, email_verified_p => email_verified_p ); v_rel_id := membership_rel.new ( object_id_two => v_user_id, object_id_one => acs.magic_object_id('registered_users'), member_state => member_state ); acs_permission.grant_permission ( object_id => v_user_id, grantee_id => v_user_id, privilege => 'read' ); acs_permission.grant_permission ( object_id => v_user_id, grantee_id => v_user_id, privilege => 'write' ); return v_user_id; end; procedure remove_user ( user_id in users.user_id%TYPE ) is begin delete from acs_permissions where grantee_id = user_id; for row in (select rel_id from acs_rels where object_id_two = user_id) loop acs_rel.del(rel_id => row.rel_id); end loop; acs_user.del(user_id => user_id); end; function magic_object_id ( name in acs_magic_objects.name%TYPE ) return acs_objects.object_id%TYPE is object_id acs_objects.object_id%TYPE; begin select object_id into magic_object_id.object_id from acs_magic_objects where name = magic_object_id.name; return object_id; end magic_object_id; end acs; / show errors -- ****************************************************************** -- * Community Core API -- ****************************************************************** create or replace view registered_users as select p.email, p.url, pe.first_names, pe.last_name, u.user_id,u.authority_id,u.username,u.password,u.salt,u.screen_name,u.priv_name,u.priv_email,u.email_verified_p,u.email_bouncing_p,u.no_alerts_until,u.last_visit,u.second_to_last_visit,u.n_sessions,u.password_question,u.password_answer,u.password_changed_date, mr.member_state from parties p, persons pe, users u, group_member_map m, membership_rels mr where party_id = person_id and person_id = user_id and u.user_id = m.member_id and m.rel_id = mr.rel_id and m.group_id = (select acs.magic_object_id('registered_users') from dual) and m.container_id = m.group_id and m.rel_type = 'membership_rel' and mr.member_state = 'approved' and u.email_verified_p = 't'; -- faster simpler view -- does not check for registered user/banned etc create or replace view acs_users_all as select pa.*, pe.*, u.* from parties pa, persons pe, users u where pa.party_id = pe.person_id and pe.person_id = u.user_id; create or replace view cc_users as select o.object_id,o.object_type,o.context_id,o.security_inherit_p,o.creation_user,o.creation_date,o.creation_ip,o.last_modified,o.modifying_user,o.modifying_ip, pa.party_id, pa.email, pa.url, pe.person_id, pe.first_names, pe.last_name, u.user_id,u.authority_id,username,u.password,u.salt,u.screen_name,u.priv_name,u.priv_email,u.email_verified_p,u.email_bouncing_p,u.no_alerts_until,u.last_visit,u.second_to_last_visit,u.n_sessions,u.password_question,u.password_answer,password_changed_date, mr.member_state, mr.rel_id from acs_objects o, parties pa, persons pe, users u, group_member_map m, membership_rels mr, acs_magic_objects mo where o.object_id = pa.party_id and pa.party_id = pe.person_id and pe.person_id = u.user_id and u.user_id = m.member_id and m.group_id = mo.object_id and mo.name = 'registered_users' and m.rel_id = mr.rel_id and m.container_id = m.group_id and m.rel_type = 'membership_rel'; ----------------------------------- -- Community Core Initialization -- ----------------------------------- -- The very first thing we must do is create the security_context_root -- object. declare root_id integer; begin root_id := acs_object.new ( object_id => -4 ); insert into acs_magic_objects (name, object_id) values ('security_context_root', -4); end; / show errors begin -------------------------------------------------------------- -- Some privilege that will be fundamental to all objects. -- -------------------------------------------------------------- acs_privilege.create_privilege('read'); acs_privilege.create_privilege('write'); acs_privilege.create_privilege('create'); acs_privilege.create_privilege('delete'); acs_privilege.create_privilege('admin'); --------------------------------------------------------- -- Administrators can read, write, create, and delete. -- --------------------------------------------------------- acs_privilege.add_child('admin', 'read'); acs_privilege.add_child('admin', 'write'); acs_privilege.add_child('admin', 'create'); acs_privilege.add_child('admin', 'delete'); commit; end; / show errors declare foo acs_objects.object_id%TYPE; begin -- Make the "Unregistered Visitor" be object 0, which corresponds -- with the user_id assigned throughout the toolkit Tcl code -- LARS: Make object_id 0 be a user, not a person insert into acs_objects (object_id, object_type) values (0, 'user'); insert into parties (party_id) values (0); insert into persons (person_id, first_names, last_name) values (0, 'Unregistered', 'Visitor'); insert into users (user_id, username) values (0, 'guest'); insert into acs_magic_objects (name, object_id) values ('unregistered_visitor', 0); -- Create the public group foo := acs_group.new ( group_id => -1, group_name => 'The Public', join_policy => 'closed' ); insert into acs_magic_objects (name, object_id) values ('the_public', -1); -- Add our only user, the Unregistered Visitor, to the public group foo := membership_rel.new ( rel_type => 'membership_rel', object_id_one => acs.magic_object_id('the_public'), object_id_two => acs.magic_object_id('unregistered_visitor'), member_state => 'approved' ); -- Make the registered users group foo := acs_group.new ( group_id => -2, group_name => 'Registered Users', join_policy => 'closed' ); insert into acs_magic_objects (name, object_id) values ('registered_users', -2); -- Now declare "The Public" to be composed of itself and the "Registered -- Users" group foo := composition_rel.new ( rel_type => 'composition_rel', object_id_one => acs.magic_object_id('the_public'), object_id_two => acs.magic_object_id('registered_users') ); commit; end; / show errors; -- Create the default context. declare object_id integer; begin object_id := acs_object.new ( object_id => -3 ); insert into acs_magic_objects (name, object_id) values ('default_context', object_id); commit; end; / show errors; -- Create the local authority declare v_authority_id integer; begin v_authority_id := authority.new( short_name => 'local', pretty_name => 'OpenACS Local', sort_order => '1' ); end; / show errors