--
-- Upgrade script from 5.0d2 to 5.0d3
--
-- @author Peter Marklund (peter@collaboraid.biz)
--
-- @cvs-id $Id: upgrade-5.0d2-5.0d3.sql,v 1.14 2004/06/29 10:17:38 jeffd Exp $
--
-- ****** New authentication datamodel
create table auth_authorities (
authority_id integer
constraint auth_authorities_pk
primary key
constraint auth_authorities_aid_fk
references acs_objects(object_id)
on delete cascade,
short_name varchar(255)
constraint auth_authority_short_name_un
unique,
pretty_name varchar(4000),
help_contact_text varchar(4000),
help_contact_text_format varchar(200),
enabled_p boolean default 't'
constraint auth_authority_enabled_p_nn
not null,
sort_order integer not null,
-- auth_authentication implementation
-- (Cannot reference acs_sc_impls table as it doesn't exist yet)
auth_impl_id integer
constraint auth_authority_auth_impl_fk
references acs_objects(object_id),
-- auth_password implementation
pwd_impl_id integer
constraint auth_authority_pwd_impl_fk
references acs_objects(object_id),
forgotten_pwd_url varchar(4000),
change_pwd_url varchar(4000),
-- auth_registration implementation
register_impl_id integer
constraint auth_authority_reg_impl_fk
references acs_objects(object_id),
register_url varchar(4000),
-- auth_user_info implementation
user_info_impl_id integer
constraint auth_authority_userinf_impl_fk
references acs_objects(object_id),
-- batch sync
-- auth_sync_retrieve implementation
get_doc_impl_id integer references acs_objects(object_id),
-- auth_sync_process implementation
process_doc_impl_id integer references acs_objects(object_id),
batch_sync_enabled_p boolean default 'f'
constraint auth_authority_bs_enabled_p_nn
not null
);
comment on column auth_authorities.help_contact_text is '
Contact information (phone, email, etc.) to be displayed
as a last resort when people are having problems with an authority.
';
comment on column auth_authorities.forgotten_pwd_url is '
Any username in this url must be on the syntax foo={username}
and {username} will be replaced with the real username
';
comment on column auth_authorities.change_pwd_url is '
Any username in this url must be on the syntax foo={username}
and {username} will be replaced with the real username
';
-- Define the acs object type
select acs_object_type__create_type (
'authority',
'Authority',
'Authorities',
'acs_object',
'auth_authorities',
'authority_id',
null,
'f',
null,
null
);
create table auth_driver_params(
authority_id integer
constraint auth_driver_params_aid_fk
references auth_authorities(authority_id)
constraint auth_driver_params_aid_nn
not null,
impl_id integer
constraint auth_driver_params_iid_fk
-- Cannot reference acs_sc_impls table as it doesn't exist yet
references acs_objects(object_id)
constraint auth_driver_params_iid_nn
not null,
key varchar(200),
value text,
unique (authority_id, impl_id)
);
-- Create PLSQL functions
create or replace function authority__new (
integer, -- authority_id
varchar, -- object_type
varchar, -- short_name
varchar, -- pretty_name
boolean, -- enabled_p
integer, -- sort_order
integer, -- auth_impl_id
integer, -- pwd_impl_id
varchar, -- forgotten_pwd_url
varchar, -- change_pwd_url
integer, -- register_impl_id
varchar, -- register_url
varchar, -- help_contact_text
integer, -- creation_user
varchar, -- creation_ip
integer -- context_id
)
returns integer as '
declare
p_authority_id alias for $1; -- default null,
p_object_type alias for $2; -- default ''authority''
p_short_name alias for $3;
p_pretty_name alias for $4;
p_enabled_p alias for $5; -- default ''t''
p_sort_order alias for $6;
p_auth_impl_id alias for $7; -- default null
p_pwd_impl_id alias for $8; -- default null
p_forgotten_pwd_url alias for $9; -- default null
p_change_pwd_url alias for $10; -- default null
p_register_impl_id alias for $11; -- default null
p_register_url alias for $12; -- default null
p_help_contact_text alias for $13; -- default null,
p_creation_user alias for $14; -- default null
p_creation_ip alias for $15; -- default null
p_context_id alias for $16; -- default null
v_authority_id integer;
v_object_type varchar;
v_sort_order integer;
begin
if p_object_type is null then
v_object_type := ''authority'';
else
v_object_type := p_object_type;
end if;
if p_sort_order is null then
select into v_sort_order max(sort_order) + 1
from auth_authorities;
else
v_sort_order := p_sort_order;
end if;
-- Instantiate the ACS Object super type with auditing info
v_authority_id := acs_object__new(
p_authority_id,
v_object_type,
now(),
p_creation_user,
p_creation_ip,
p_context_id,
''t''
);
insert into auth_authorities (authority_id, short_name, pretty_name, enabled_p,
sort_order, auth_impl_id, pwd_impl_id,
forgotten_pwd_url, change_pwd_url, register_impl_id,
help_contact_text)
values (v_authority_id, p_short_name, p_pretty_name, p_enabled_p,
v_sort_order, p_auth_impl_id, p_pwd_impl_id,
p_forgotten_pwd_url, p_change_pwd_url, p_register_impl_id,
p_help_contact_text);
return v_authority_id;
end;
' language 'plpgsql';
create or replace function authority__del (integer)
returns integer as '
declare
p_authority_id alias for $1;
begin
perform acs_object__delete(p_authority_id);
return 0;
end;' language 'plpgsql';
-- Create the local authority
select authority__new(
null, -- authority_id
null, -- object_type
'local', -- short_name
'OpenACS Local', -- pretty_name
't', -- enabled_p
1, -- sort_order
null, -- auth_impl_id
null, -- pwd_impl_id
null, -- forgotten_pwd_url
null, -- change_pwd_url
null, -- register_impl_id
null, -- register_url
null, -- help_contact_text
null, -- creation_user
null, -- creation_ip
null -- context_id
);
-- ****** Changes to the users table
alter table users add authority_id integer
constraint users_auth_authorities_fk
references auth_authorities(authority_id);
alter table users add username varchar(100);
-- set all current users' username to equal their email
-- and their authority to be the local authority
-- Exclude the unregistered visitor as he/she has a null email
update users
set username = (select email from parties where party_id = user_id),
authority_id = (select authority_id from auth_authorities where short_name = 'local')
where user_id <> 0 and username is null;
update users set username = '-' where username is null;
-- Does not work with PG 7.2, 5.0 only supports 7.3+ so set not null.
alter table users alter column username set not null;
alter table users add constraint users_authority_username_un
unique (authority_id, username);
drop view cc_users CASCADE;
create view cc_users
as
select o.*, pa.*, pe.*, u.*, 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 amo
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 amo.name = 'registered_users'
and m.group_id = amo.object_id
and m.rel_id = mr.rel_id
and m.container_id = m.group_id;
drop function acs_user__new (integer,varchar,timestamptz,integer,varchar,varchar,varchar,varchar,varchar,char,char,varchar,varchar,varchar,boolean,integer);
create function acs_user__new (
integer, -- user_id
varchar, -- object_type
timestamptz, -- creation_date
integer, -- creation_user
varchar, -- creation_ip
integer, -- authority_id; default 'local'
varchar, -- username
varchar, -- email
varchar, -- url
varchar, -- first_names
varchar, -- last_name
char, -- password
char, -- salt
varchar, -- screen_name
boolean, -- email_verified_p
integer -- context_id
)
returns integer as '
declare
p_user_id alias for $1; -- default null
p_object_type alias for $2; -- default ''user''
p_creation_date alias for $3; -- default now()
p_creation_user alias for $4; -- default null
p_creation_ip alias for $5; -- default null
p_authority_id alias for $6; -- defaults to local authority
p_username alias for $7; --
p_email alias for $8;
p_url alias for $9; -- default null
p_first_names alias for $10;
p_last_name alias for $11;
p_password alias for $12;
p_salt alias for $13;
p_screen_name alias for $14; -- default null
p_email_verified_p alias for $15; -- default ''t''
p_context_id alias for $16; -- default null
v_user_id users.user_id%TYPE;
v_authority_id auth_authorities.authority_id%TYPE;
v_person_exists varchar;
begin
v_user_id := p_user_id;
select case when count(*) = 0 then ''f'' else ''t'' end into v_person_exists
from persons where person_id = v_user_id;
if v_person_exists = ''f'' then
v_user_id := person__new(
v_user_id,
p_object_type,
p_creation_date,
p_creation_user,
p_creation_ip,
p_email,
p_url,
p_first_names,
p_last_name,
p_context_id
);
else
update acs_objects set object_type = ''user'' where object_id = v_user_id;
end if;
-- default to local authority
if p_authority_id is null then
select authority_id
into v_authority_id
from auth_authorities
where short_name = ''local'';
else
v_authority_id := p_authority_id;
end if;
insert into users
(user_id, authority_id, username, password, salt, screen_name, email_verified_p)
values
(v_user_id, v_authority_id, p_username, p_password, p_salt, p_screen_name, p_email_verified_p);
insert into user_preferences
(user_id)
values
(v_user_id);
return v_user_id;
end;' language 'plpgsql';
drop function acs__add_user (integer,varchar,timestamptz,integer,varchar,varchar,varchar,varchar,varchar,char,char,varchar,varchar,varchar,boolean,varchar);
create function acs__add_user (
integer, -- user_id
varchar, -- object_type
timestamptz, -- creation_date
integer, -- creation_user
varchar, -- cretion_ip
integer, -- authority_id; default 'local'
varchar, -- username
varchar, -- email
varchar, -- url
varchar, -- first_names
varchar, -- last_name
char, -- password
char, -- salt
varchar, -- screen_name
boolean, -- email_verified_p
varchar -- member_state
)
returns integer as '
declare
p_user_id alias for $1; -- default null
p_object_type alias for $2; -- default ''user''
p_creation_date alias for $3; -- default now()
p_creation_user alias for $4; -- default null
p_creation_ip alias for $5; -- default null
p_authority_id alias for $6; -- defaults to local authority
p_username alias for $7; --
p_email alias for $8;
p_url alias for $9; -- default null
p_first_names alias for $10;
p_last_name alias for $11;
p_password alias for $12;
p_salt alias for $13;
p_screen_name alias for $14; -- default null
p_email_verified_p alias for $15; -- default ''t''
p_member_state alias for $16; -- default ''approved''
v_user_id users.user_id%TYPE;
v_rel_id membership_rels.rel_id%TYPE;
begin
v_user_id := acs_user__new (
p_user_id,
p_object_type,
p_creation_date,
p_creation_user,
p_creation_ip,
p_authority_id,
p_username,
p_email,
p_url,
p_first_names,
p_last_name,
p_password,
p_salt,
p_screen_name,
p_email_verified_p,
null -- context_id
);
v_rel_id := membership_rel__new (
null,
''membership_rel'',
acs__magic_object_id(''registered_users''),
v_user_id,
p_member_state,
null,
null);
PERFORM acs_permission__grant_permission (
v_user_id,
v_user_id,
''read''
);
PERFORM acs_permission__grant_permission (
v_user_id,
v_user_id,
''write''
);
return v_user_id;
end;' language 'plpgsql';
create view cc_users_of_package_id
as
SELECT u.*, au.package_id
FROM application_users au, cc_users u
WHERE (au.user_id = u.user_id);
OpenACS – The Toolkit for Online Communities : ACS API Browser : ACS Kernel 6.0.0d1 : upgrade-5.0d2-5.0d3.sql
Begin main content
