Index: openacs-4/packages/acs-kernel/acs-kernel.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/acs-kernel.info,v
diff -u -r1.28 -r1.29
--- openacs-4/packages/acs-kernel/acs-kernel.info 15 Aug 2003 14:35:49 -0000 1.28
+++ openacs-4/packages/acs-kernel/acs-kernel.info 22 Aug 2003 11:38:08 -0000 1.29
@@ -7,7 +7,7 @@
t
t
-
+
oracle
postgresql
@@ -17,7 +17,7 @@
2003-02-18
OpenACS
-
+
Index: openacs-4/packages/acs-kernel/sql/oracle/acs-kernel-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/acs-kernel-create.sql,v
diff -u -r1.7 -r1.8
--- openacs-4/packages/acs-kernel/sql/oracle/acs-kernel-create.sql 21 Jun 2002 21:05:23 -0000 1.7
+++ openacs-4/packages/acs-kernel/sql/oracle/acs-kernel-create.sql 22 Aug 2003 11:38:08 -0000 1.8
@@ -17,6 +17,7 @@
@@ acs-object-util
@@ acs-relationships-create
@@ utilities-create
+@@ authentication-create
@@ community-core-create
@@ groups-create
@@ rel-segments-create
Index: openacs-4/packages/acs-kernel/sql/oracle/acs-kernel-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/acs-kernel-drop.sql,v
diff -u -r1.4 -r1.5
--- openacs-4/packages/acs-kernel/sql/oracle/acs-kernel-drop.sql 21 Jun 2002 21:05:23 -0000 1.4
+++ openacs-4/packages/acs-kernel/sql/oracle/acs-kernel-drop.sql 22 Aug 2003 11:38:08 -0000 1.5
@@ -19,6 +19,7 @@
@@ site-node-object-map-drop
@@ site-nodes-drop
@@ community-core-drop
+@@ authentication-drop
@@ acs-relationships-drop
@@ acs-object-util-remove
@@ acs-objects-drop
Index: openacs-4/packages/acs-kernel/sql/oracle/authentication-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/authentication-create.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/acs-kernel/sql/oracle/authentication-create.sql 22 Aug 2003 11:38:08 -0000 1.1
@@ -0,0 +1,95 @@
+--
+-- acs-kernel/sql/oracle/authentication-create.sql
+--
+-- The OpenACS core authentication system.
+--
+-- @author Lars Pind (lars@collaboraid.biz)
+--
+-- @creation-date 20003-05-13
+--
+-- @cvs-id $Id: authentication-create.sql,v 1.1 2003/08/22 11:38:08 peterm Exp $
+--
+
+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 varchar2(255)
+ constraint auth_authority_short_name_un
+ unique,
+ pretty_name varchar2(4000),
+ help_contact_text varchar2(4000),
+ enabled_p char(1) default 't'
+ constraint auth_authority_enabled_p_nn
+ not null
+ constraint auth_authority_enabled_p_ck
+ check (enabled_p in ('t','f')),
+ sort_order integer not null,
+ -- Id of the authentication service contract implementation
+ auth_impl_id integer
+ constraint auth_authority_auth_impl_fk
+ references acs_sc_impls(impl_id),
+ -- Id of the password management service contact implementation
+ pwd_impl_id integer
+ constraint auth_authority_pwd_impl_fk
+ references acs_sc_impls(impl_id),
+ forgotten_pwd_url varchar2(4000),
+ change_pwd_url varchar2(4000),
+ -- Id of the registration service contract implementation
+ register_impl_id integer
+ constraint auth_authority_reg_impl_fk
+ references acs_sc_impls(impl_id),
+ register_url varchar2(4000)
+);
+
+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
+begin
+ acs_object_type.create_type (
+ object_type => 'authority',
+ pretty_name => 'Authority',
+ pretty_plural => 'Authorities',
+ supertype => 'acs_object',
+ table_name => 'auth_authorities',
+ id_column => 'authority_id',
+ package_name => null,
+ abstract_p => 'f',
+ type_extension_table => null,
+ name_method => null
+ );
+end;
+/
+show errors
+
+-- Create PLSQL package
+@@ authentication-package-create
+
+-- 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
Index: openacs-4/packages/acs-kernel/sql/oracle/authentication-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/authentication-drop.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/acs-kernel/sql/oracle/authentication-drop.sql 22 Aug 2003 11:38:08 -0000 1.1
@@ -0,0 +1,28 @@
+--
+-- acs-kernel/sql/oracle/authentication-drop.sql
+--
+-- The OpenACS core authentication system drop script.
+--
+-- @author Lars Pind (lars@collaboraid.biz)
+--
+-- @creation-date 20003-05-14
+--
+-- @cvs-id $Id: authentication-drop.sql,v 1.1 2003/08/22 11:38:08 peterm Exp $
+--
+
+declare
+ foo integer;
+begin
+ for row in (select authority_id from auth_authorities)
+ loop
+ foo := authority.del(row.authority_id);
+ end loop;
+
+ acs_object_type.drop_type('authority', 't');
+end;
+/
+show errors
+
+drop table auth_authorities cascade constraints;
+
+@@ authentication-package-drop
Index: openacs-4/packages/acs-kernel/sql/oracle/authentication-package-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/authentication-package-create.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/acs-kernel/sql/oracle/authentication-package-create.sql 22 Aug 2003 11:38:08 -0000 1.1
@@ -0,0 +1,93 @@
+-- PLSQL packages for the authentication datamodel
+--
+-- @author Peter Marklund
+-- @creation-date 2003-08-21
+
+create or replace package authority
+as
+ function new(
+ authority_id in auth_authorities.authority_id%TYPE default null,
+ object_type acs_object_types.object_type%TYPE default 'authority',
+ short_name in auth_authorities.short_name%TYPE,
+ pretty_name in auth_authorities.pretty_name%TYPE,
+ enabled_p in auth_authorities.enabled_p%TYPE default 't',
+ sort_order in auth_authorities.sort_order%TYPE,
+ auth_impl_id in auth_authorities.auth_impl_id%TYPE default null,
+ pwd_impl_id in auth_authorities.pwd_impl_id%TYPE default null,
+ forgotten_pwd_url in auth_authorities.forgotten_pwd_url%TYPE default null,
+ change_pwd_url in auth_authorities.change_pwd_url%TYPE default null,
+ register_impl_id in auth_authorities.register_impl_id%TYPE default null,
+ register_url in auth_authorities.register_url%TYPE default null,
+ help_contact_text in auth_authorities.help_contact_text%TYPE default null,
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_ip in acs_objects.creation_ip%TYPE default null,
+ context_id in acs_objects.context_id%TYPE default null
+ ) return integer;
+
+ function del(
+ delete_authority_id in auth_authorities.authority_id%TYPE
+ ) return integer;
+
+end authority;
+/
+show errors
+
+create or replace package body authority
+as
+ function new(
+ authority_id in auth_authorities.authority_id%TYPE default null,
+ object_type acs_object_types.object_type%TYPE default 'authority',
+ short_name in auth_authorities.short_name%TYPE,
+ pretty_name in auth_authorities.pretty_name%TYPE,
+ enabled_p in auth_authorities.enabled_p%TYPE default 't',
+ sort_order in auth_authorities.sort_order%TYPE,
+ auth_impl_id in auth_authorities.auth_impl_id%TYPE default null,
+ pwd_impl_id in auth_authorities.pwd_impl_id%TYPE default null,
+ forgotten_pwd_url in auth_authorities.forgotten_pwd_url%TYPE default null,
+ change_pwd_url in auth_authorities.change_pwd_url%TYPE default null,
+ register_impl_id in auth_authorities.register_impl_id%TYPE default null,
+ register_url in auth_authorities.register_url%TYPE default null,
+ help_contact_text in auth_authorities.help_contact_text%TYPE default null,
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_ip in acs_objects.creation_ip%TYPE default null,
+ context_id in acs_objects.context_id%TYPE default null
+ )
+ return integer
+ is
+ v_authority_id integer;
+ begin
+ v_authority_id := acs_object.new(
+ object_id => new.authority_id,
+ object_type => new.object_type,
+ creation_date => sysdate(),
+ creation_user => new.creation_user,
+ creation_ip => new.creation_ip,
+ context_id => new.context_id
+ );
+
+ 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, new.short_name, new.pretty_name, new.enabled_p,
+ new.sort_order, new.auth_impl_id, new.pwd_impl_id,
+ new.forgotten_pwd_url, new.change_pwd_url, new.register_impl_id,
+ new.help_contact_text);
+
+ return v_authority_id;
+ end new;
+
+ function del(
+ delete_authority_id in auth_authorities.authority_id%TYPE
+ )
+ return integer
+ is
+ begin
+ acs_object.delete(delete_authority_id);
+
+ return 0;
+ end del;
+
+end authority;
+/
+show errors
Index: openacs-4/packages/acs-kernel/sql/oracle/authentication-package-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/authentication-package-drop.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/acs-kernel/sql/oracle/authentication-package-drop.sql 22 Aug 2003 11:38:08 -0000 1.1
@@ -0,0 +1,6 @@
+-- Drop PLSQL packages for the authentication datamodel
+--
+-- @author Peter Marklund
+-- @creation-date 2003-08-21
+
+drop package authority;
Index: openacs-4/packages/acs-kernel/sql/oracle/community-core-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/community-core-create.sql,v
diff -u -r1.5 -r1.6
--- openacs-4/packages/acs-kernel/sql/oracle/community-core-create.sql 15 Aug 2003 14:35:50 -0000 1.5
+++ openacs-4/packages/acs-kernel/sql/oracle/community-core-create.sql 22 Aug 2003 11:38:08 -0000 1.6
@@ -512,9 +512,13 @@
constraint users_user_id_fk
references persons (person_id)
constraint users_pk primary key,
- password char(40),
- salt char(40),
- screen_name varchar2(100)
+ authority_id integer
+ constraint users_auth_authorities_fk
+ references auth_authorities(authority_id),
+ username varchar2(100)
+ constraint users_username_nn
+ not null,
+ screen_name varchar2(100)
constraint users_screen_name_un
unique,
priv_name integer default 0 not null,
@@ -529,8 +533,14 @@
last_visit date,
second_to_last_visit date,
n_sessions integer default 1 not null,
+ -- local authentication information
+ password char(40),
+ salt char(40),
password_question varchar2(1000),
- password_answer varchar2(1000)
+ password_answer varchar2(1000),
+ -- table constraints
+ constraint users_authority_username_un
+ unique (authority_id, username)
);
create index users_email_verified_idx on users (email_verified_p);
Index: openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-5.0d2-5.0d3.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-5.0d2-5.0d3.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-5.0d2-5.0d3.sql 22 Aug 2003 11:38:08 -0000 1.1
@@ -0,0 +1,215 @@
+--
+-- 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.1 2003/08/22 11:38:08 peterm 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 varchar2(255)
+ constraint auth_authority_short_name_un
+ unique,
+ pretty_name varchar2(4000),
+ help_contact_text varchar2(4000),
+ enabled_p char(1) default 't'
+ constraint auth_authority_enabled_p_nn
+ not null
+ constraint auth_authority_enabled_p_ck
+ check (enabled_p in ('t','f')),
+ sort_order integer not null,
+ -- Id of the authentication service contract implementation
+ auth_impl_id integer
+ constraint auth_authority_auth_impl_fk
+ references acs_sc_impls(impl_id),
+ -- Id of the password management service contact implementation
+ pwd_impl_id integer
+ constraint auth_authority_pwd_impl_fk
+ references acs_sc_impls(impl_id),
+ forgotten_pwd_url varchar2(4000),
+ change_pwd_url varchar2(4000),
+ -- Id of the registration service contract implementation
+ register_impl_id integer
+ constraint auth_authority_reg_impl_fk
+ references acs_sc_impls(impl_id),
+ register_url varchar2(4000)
+);
+
+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
+begin
+ acs_object_type.create_type (
+ object_type => 'authority',
+ pretty_name => 'Authority',
+ pretty_plural => 'Authorities',
+ supertype => 'acs_object',
+ table_name => 'auth_authorities',
+ id_column => 'authority_id',
+ package_name => null,
+ abstract_p => 'f',
+ type_extension_table => null,
+ name_method => null
+ );
+end;
+/
+show errors
+
+-- Create PLSQL package
+create or replace package authority
+as
+ function new(
+ authority_id in auth_authorities.authority_id%TYPE default null,
+ object_type acs_object_types.object_type%TYPE default 'authority',
+ short_name in auth_authorities.short_name%TYPE,
+ pretty_name in auth_authorities.pretty_name%TYPE,
+ enabled_p in auth_authorities.enabled_p%TYPE default 't',
+ sort_order in auth_authorities.sort_order%TYPE,
+ auth_impl_id in auth_authorities.auth_impl_id%TYPE default null,
+ pwd_impl_id in auth_authorities.pwd_impl_id%TYPE default null,
+ forgotten_pwd_url in auth_authorities.forgotten_pwd_url%TYPE default null,
+ change_pwd_url in auth_authorities.change_pwd_url%TYPE default null,
+ register_impl_id in auth_authorities.register_impl_id%TYPE default null,
+ register_url in auth_authorities.register_url%TYPE default null,
+ help_contact_text in auth_authorities.help_contact_text%TYPE default null,
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_ip in acs_objects.creation_ip%TYPE default null,
+ context_id in acs_objects.context_id%TYPE default null
+ ) return integer;
+
+ function del(
+ delete_authority_id in auth_authorities.authority_id%TYPE
+ ) return integer;
+
+end authority;
+/
+show errors
+
+create or replace package body authority
+as
+ function new(
+ authority_id in auth_authorities.authority_id%TYPE default null,
+ object_type acs_object_types.object_type%TYPE default 'authority',
+ short_name in auth_authorities.short_name%TYPE,
+ pretty_name in auth_authorities.pretty_name%TYPE,
+ enabled_p in auth_authorities.enabled_p%TYPE default 't',
+ sort_order in auth_authorities.sort_order%TYPE,
+ auth_impl_id in auth_authorities.auth_impl_id%TYPE default null,
+ pwd_impl_id in auth_authorities.pwd_impl_id%TYPE default null,
+ forgotten_pwd_url in auth_authorities.forgotten_pwd_url%TYPE default null,
+ change_pwd_url in auth_authorities.change_pwd_url%TYPE default null,
+ register_impl_id in auth_authorities.register_impl_id%TYPE default null,
+ register_url in auth_authorities.register_url%TYPE default null,
+ help_contact_text in auth_authorities.help_contact_text%TYPE default null,
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_ip in acs_objects.creation_ip%TYPE default null,
+ context_id in acs_objects.context_id%TYPE default null
+ )
+ return integer
+ is
+ v_authority_id integer;
+ begin
+ v_authority_id := acs_object.new(
+ object_id => new.authority_id,
+ object_type => new.object_type,
+ creation_date => sysdate(),
+ creation_user => new.creation_user,
+ creation_ip => new.creation_ip,
+ context_id => new.context_id
+ );
+
+ 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, new.short_name, new.pretty_name, new.enabled_p,
+ new.sort_order, new.auth_impl_id, new.pwd_impl_id,
+ new.forgotten_pwd_url, new.change_pwd_url, new.register_impl_id,
+ new.help_contact_text);
+
+ return v_authority_id;
+ end new;
+
+ function del(
+ delete_authority_id in auth_authorities.authority_id%TYPE
+ )
+ return integer
+ is
+ begin
+ acs_object.delete(delete_authority_id);
+
+ return 0;
+ end del;
+
+end authority;
+/
+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
+
+
+-- ****** 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 varchar2(100) default '-'
+ constraint users_username_nn
+ not null;
+
+-- set all current users' username to equal their email
+-- and their authority to be the local authority
+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');
+
+-- add a unique constraint
+alter table users add constraint users_authority_username_un unique (authority_id, username);
+
+-- Need to recreate the cc_users view
+create or replace 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
+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 = acs.magic_object_id('registered_users')
+and m.rel_id = mr.rel_id
+and m.container_id = m.group_id;
+
Index: openacs-4/packages/acs-kernel/sql/postgresql/acs-kernel-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/acs-kernel-create.sql,v
diff -u -r1.7 -r1.8
--- openacs-4/packages/acs-kernel/sql/postgresql/acs-kernel-create.sql 21 Jun 2002 21:05:23 -0000 1.7
+++ openacs-4/packages/acs-kernel/sql/postgresql/acs-kernel-create.sql 22 Aug 2003 11:38:08 -0000 1.8
@@ -18,6 +18,7 @@
\i acs-object-util.sql
\i acs-relationships-create.sql
\i utilities-create.sql
+\i authentication-create.sql
\i community-core-create.sql
\i groups-create.sql
\i rel-segments-create.sql
Index: openacs-4/packages/acs-kernel/sql/postgresql/acs-kernel-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/acs-kernel-drop.sql,v
diff -u -r1.3 -r1.4
--- openacs-4/packages/acs-kernel/sql/postgresql/acs-kernel-drop.sql 21 Jun 2002 21:05:23 -0000 1.3
+++ openacs-4/packages/acs-kernel/sql/postgresql/acs-kernel-drop.sql 22 Aug 2003 11:38:08 -0000 1.4
@@ -19,6 +19,7 @@
\i site-node-object-map-drop.sql
\i site-nodes-drop.sql
\i community-core-drop.sql
+\i authentication-drop.sql
\i acs-relationships-drop.sql
\i acs-object-util-remove.sql
\i acs-objects-drop.sql
Index: openacs-4/packages/acs-kernel/sql/postgresql/authentication-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/authentication-create.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/acs-kernel/sql/postgresql/authentication-create.sql 22 Aug 2003 11:38:08 -0000 1.1
@@ -0,0 +1,97 @@
+--
+-- acs-kernel/sql/postgresql/authentication-create.sql
+--
+-- The OpenACS core authentication system.
+--
+-- @author Peter Marklund (peter@collaboraid.biz)
+--
+-- @creation-date 20003-08-21
+--
+-- @cvs-id $Id: authentication-create.sql,v 1.1 2003/08/22 11:38:08 peterm Exp $
+--
+
+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),
+ enabled_p boolean default 't'
+ constraint auth_authority_enabled_p_nn
+ not null,
+ sort_order integer not null,
+ -- Id of the authentication service contract implementation
+ auth_impl_id integer
+ constraint auth_authority_auth_impl_fk
+ references acs_sc_impls(impl_id),
+ -- Id of the password management service contact implementation
+ pwd_impl_id integer
+ constraint auth_authority_pwd_impl_fk
+ references acs_sc_impls(impl_id),
+ forgotten_pwd_url varchar(4000),
+ change_pwd_url varchar(4000),
+ -- Id of the registration service contract implementation
+ register_impl_id integer
+ constraint auth_authority_reg_impl_fk
+ references acs_sc_impls(impl_id),
+ register_url varchar(4000)
+);
+
+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 PLSQL package
+\i authentication-package-create.sql
+
+-- 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
+);
Index: openacs-4/packages/acs-kernel/sql/postgresql/authentication-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/authentication-drop.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/acs-kernel/sql/postgresql/authentication-drop.sql 22 Aug 2003 11:38:08 -0000 1.1
@@ -0,0 +1,32 @@
+--
+-- acs-kernel/sql/postgresql/authentication-drop.sql
+--
+-- The OpenACS core authentication system drop script.
+--
+-- @author Peter Marklund (peter@collaboraid.biz)
+--
+-- @creation-date 20003-08-21
+--
+-- @cvs-id $Id: authentication-drop.sql,v 1.1 2003/08/22 11:38:08 peterm Exp $
+--
+
+create function inline_0 ()
+returns integer as '
+declare
+ row record;
+begin
+ for row in select authority_id from auth_authorities
+ loop
+ perform authority__del(row.authority_id);
+ end loop;
+
+ perform acs_object_type__drop_type(''authority'', ''t'');
+
+ return 1;
+end;' language 'plpgsql';
+select inline_0 ();
+drop function inline_0();
+
+drop table auth_authorities cascade;
+
+\i authentication-package-drop.sql
Index: openacs-4/packages/acs-kernel/sql/postgresql/authentication-package-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/authentication-package-create.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/acs-kernel/sql/postgresql/authentication-package-create.sql 22 Aug 2003 11:38:08 -0000 1.1
@@ -0,0 +1,85 @@
+-- P/pgLSQL packages for the authentication datamodel
+--
+-- @author Peter Marklund
+-- @creation-date 2003-08-21
+
+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;
+
+begin
+ if p_object_type is null then
+ v_object_type := ''authority'';
+ else
+ v_object_type := p_object_type;
+ 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,
+ p_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';
Index: openacs-4/packages/acs-kernel/sql/postgresql/authentication-package-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/authentication-package-drop.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/acs-kernel/sql/postgresql/authentication-package-drop.sql 22 Aug 2003 11:38:08 -0000 1.1
@@ -0,0 +1,25 @@
+-- Drop PLSQL packages for the authentication datamodel
+--
+-- @author Peter Marklund
+-- @creation-date 2003-08-21
+
+drop function authority__del (integer);
+
+drop 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
+);
Index: openacs-4/packages/acs-kernel/sql/postgresql/community-core-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/community-core-create.sql,v
diff -u -r1.12 -r1.13
--- openacs-4/packages/acs-kernel/sql/postgresql/community-core-create.sql 15 Aug 2003 14:35:50 -0000 1.12
+++ openacs-4/packages/acs-kernel/sql/postgresql/community-core-create.sql 22 Aug 2003 11:38:08 -0000 1.13
@@ -490,25 +490,35 @@
-- show errors
create table users (
- user_id integer not null
- constraint users_user_id_fk
- references persons (person_id)
- constraint users_pk primary key,
- password char(40),
- salt char(40),
- screen_name varchar(100)
- constraint users_screen_name_un
- unique,
- priv_name integer default 0 not null,
- priv_email integer default 5 not null,
- email_verified_p boolean default 't',
- email_bouncing_p boolean default 'f' not null,
- no_alerts_until timestamptz,
- last_visit timestamptz,
- second_to_last_visit timestamptz,
- n_sessions integer default 1 not null,
- password_question varchar(1000),
- password_answer varchar(1000)
+ user_id integer not null
+ constraint users_user_id_fk
+ references persons (person_id)
+ constraint users_pk primary key,
+ authority_id integer
+ constraint users_auth_authorities_fk
+ references auth_authorities(authority_id),
+ username varchar(100)
+ constraint users_username_nn
+ not null,
+ screen_name varchar(100)
+ constraint users_screen_name_un
+ unique,
+ priv_name integer default 0 not null,
+ priv_email integer default 5 not null,
+ email_verified_p boolean default 't',
+ email_bouncing_p boolean default 'f' not null,
+ no_alerts_until timestamptz,
+ last_visit timestamptz,
+ second_to_last_visit timestamptz,
+ n_sessions integer default 1 not null,
+ -- local authentication information
+ password char(40),
+ salt char(40),
+ password_question varchar(1000),
+ password_answer varchar(1000),
+ -- table constraints
+ constraint users_authority_username_un
+ unique (authority_id, username)
);
create table user_preferences (
Index: openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.0d2-5.0d3.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.0d2-5.0d3.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.0d2-5.0d3.sql 22 Aug 2003 11:38:09 -0000 1.1
@@ -0,0 +1,204 @@
+--
+-- 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.1 2003/08/22 11:38:09 peterm 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),
+ enabled_p boolean default 't'
+ constraint auth_authority_enabled_p_nn
+ not null,
+ sort_order integer not null,
+ -- Id of the authentication service contract implementation
+ auth_impl_id integer
+ constraint auth_authority_auth_impl_fk
+ references acs_sc_impls(impl_id),
+ -- Id of the password management service contact implementation
+ pwd_impl_id integer
+ constraint auth_authority_pwd_impl_fk
+ references acs_sc_impls(impl_id),
+ forgotten_pwd_url varchar(4000),
+ change_pwd_url varchar(4000),
+ -- Id of the registration service contract implementation
+ register_impl_id integer
+ constraint auth_authority_reg_impl_fk
+ references acs_sc_impls(impl_id),
+ register_url varchar(4000)
+);
+
+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 PLSQL package
+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;
+
+begin
+ if p_object_type is null then
+ v_object_type := ''authority'';
+ else
+ v_object_type := p_object_type;
+ 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,
+ p_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);
+update users set username = (select email from parties where party_id = users.user_id);
+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;
+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;