-- -- acs-kernel/sql/community-core-create.sql -- -- Abstractions fundamental to any online community (or information -- system, in general), derived in large part from the ACS 3.x -- community-core data model by Philip Greenspun (philg@mit.edu), from -- the ACS 3.x user-groups data model by Tracy Adams (teadams@mit.edu) -- from Chapter 3 (The Enterprise and Its World) of David Hay's -- book _Data_Model_Patterns_, and from Chapter 2 (Accountability) -- of Martin Fowler's book _Analysis_Patterns_. -- -- @author Michael Yoon (michael@arsdigita.com) -- @author Rafael Schloming (rhs@mit.edu) -- @author Jon Salz (jsalz@mit.edu) -- -- @creation-date 2000-05-18 -- -- @cvs-id $Id: community-core-create.sql,v 1.29.6.7 2024/08/13 07:52:33 gustafn Exp $ -- -- HIGH PRIORITY: -- -- * What can subtypes add to the specification of supertype -- attributes? Extra constraints like "not null"? What about -- "storage"? Can a subtype override how a given attribute is -- stored? -- -- * Can we realistically revoke INSERT and UPDATE permission on the -- tables (users, persons, etc.) and make people use the PL/SQL API? -- One downside is that it would then be difficult or impossible to -- do things like "update ... set ... where ..." directly, without -- creating a PL/SQL procedure to do it. -- -- * Figure out how to migrate from ACS 3.x users, user_groups, -- user_group_types, etc. to ACS 4.0 objects/parties/users/organizations; -- also need to consider general_* and site_wide_* tables -- (Rafi and Luke) -- -- * Take an inventory of acs-kernel tables and other objects (some of which -- may still be in /www/doc/sql/) and create their ACS 4 analogs, including -- mapping over all general_* and site_wide_* data models, and make -- appropriate adjustments to code -- (Rafi and Yon/Luke/?). -- -- * Create magic users: system and anonymous (do we actually need these?) -- -- * Define and implement APIs -- -- * Figure out user classes, e.g., treat "the set of parties that -- have relationship X to object Y" as a party in its own right -- -- * Explain why acs_rel_types, acs_rel_rules, and acs_rels are not -- merely replicating the functionality of a relational database. -- -- * acs_attribute_type should impose some rules on the min_n_values -- and max_n_values columns of acs_attributes, e.g., it doesn't -- really make sense for a boolean attribute to have more than -- one value -- -- * Add support for default values to acs_attributes. -- -- * Add support for instance-specific attributes (e.g., -- user_group_member_fields) -- -- MEDIUM PRIORITY: -- -- * Read-only attributes? -- -- * Do we need to store metadata about enumerations and valid ranges -- or should we query the Oracle data dictionary for info on check -- constraints? -- -- * Create a "user_group_type" (an object_type with "organization" -- as its supertype (do we need this?) -- -- * Add in ancestor permission view, assuming that we'll use a -- magical rel_type: "acs_acl"? -- -- * How do we get all attribute values for objects of a specific -- type? "We probably want some convention or standard way for -- providing a view that joins supertypes and a type. This could -- be automatically generated through metadata, or it could simply -- be a convention." - Rafi -- -- LOW PRIORITY: -- -- * Formalize Rafi's definition of an "object": "A collection of rows -- identified by an object ID for which we maintain metadata" or -- something like that. -- -- * "We definitely need some standard way of extending a supertype into -- a subtype, and 'deleting' a subtype into a supertype. This will be -- needed when we want to transform a 'person' into a registered -- user, and do 'nuke user' but keep around the user's contributed -- content and associate it with the 'person' part of that user. This -- actually works quite nicely with standard oracle inheritance since -- you can just insert or delete a row in the subtype table and -- mutate the object type." - Rafi -- -- ACS 4.1: -- -- * Figure out what to do with pretty names (message catalog) -- -- COMPLETED: -- -- * Create magic parties: all_users (or just use null party_id?) -- and registered_users -- -- * Test out relationship attributes (making "relationship" an -- acs_object_type) -- -- * Create magic object_types (object, party, person, user, -- organization) including attrs and rels -- -- * Create constraints for creation_user and modifying_user -- -- procedure inline_0/0 -- CREATE OR REPLACE FUNCTION inline_0( ) RETURNS integer AS $$ DECLARE attr_id acs_attributes.attribute_id%TYPE; BEGIN -- -- Party: the supertype of person and organization -- PERFORM acs_object_type__create_type ( 'party', '#acs-kernel.Party#', '#acs-kernel.Parties#', 'acs_object', 'parties', 'party_id', 'party', 'f', null, 'party__name' ); attr_id := acs_attribute__create_attribute ( 'party', 'email', 'string', '#acs-kernel.Email_Address#', '#acs-kernel.Email_Addresses#', null, null, null, 0, 1, null, 'type_specific', 'f' ); attr_id := acs_attribute__create_attribute ( 'party', 'url', 'string', '#acs-kernel.URL#', '#acs-kernel.URLs#', null, null, null, 0, 1, null, 'type_specific', 'f' ); -- -- Person: the supertype of user -- attr_id := acs_object_type__create_type ( 'person', '#acs-kernel.Person#', '#acs-kernel.People#', 'party', 'persons', 'person_id', 'person', 'f', null, 'person__name' ); attr_id := acs_attribute__create_attribute ( 'person', 'first_names', 'string', '#acs-kernel.First_Names#', '#acs-kernel.First_Names#', null, null, null, 0, 1, null, 'type_specific', 'f' ); attr_id := acs_attribute__create_attribute ( 'person', 'last_name', 'string', '#acs-kernel.Last_Name#', '#acs-kernel.Last_Names#', null, null, null, 0, 1, null, 'type_specific', 'f' ); -- -- User: people who have registered in the system -- attr_id := acs_object_type__create_type ( 'user', '#acs-kernel.User#', '#acs-kernel.Users#', 'person', 'users', 'user_id', 'acs_user', 'f', null, null ); attr_id := acs_attribute__create_attribute ( 'user', 'username', 'string', '#acs-kernel.Username#', '#acs-kernel.Usernames#', null, null, null, 0, 1, null, 'type_specific', 'f' ); attr_id := acs_attribute__create_attribute ( 'user', 'screen_name', 'string', '#acs-kernel.Screen_Name#', '#acs-kernel.Screen_Names#', null, null, null, 0, 1, null, 'type_specific', 'f' ); attr_id := acs_attribute__create_attribute ( 'person', 'bio', 'string', '#acs-kernel.Bio#', '#acs-kernel.Bios#', null, null, null, 0, 1, null, 'type_specific', 'f' ); return 0; END; $$ LANGUAGE plpgsql; select inline_0 (); drop function inline_0 (); -- show errors -- ****************************************************************** -- * OPERATIONAL LEVEL -- ****************************************************************** create table parties ( party_id integer not null constraint parties_party_id_fk references acs_objects (object_id) constraint parties_party_id_pk primary key, email varchar(100) constraint parties_email_un unique, url varchar(200) ); create index parties_email_lower_idx on parties(lower(email)); comment on table parties is ' Party is the supertype of person and organization. It exists because many other types of object can have relationships to parties. '; comment on column parties.url is ' We store url here so that we can always make party names hyperlinks without joining to any other table. '; ------------------- -- PARTY PACKAGE -- ------------------- -- added select define_function_args('party__new','party_id;null,object_type;party,creation_date;now(),creation_user;null,creation_ip;null,email,url;null,context_id;null'); -- -- procedure party__new/8 -- CREATE OR REPLACE FUNCTION party__new( new__party_id integer, -- default null new__object_type varchar, -- default 'party' new__creation_date timestamptz, -- default now() new__creation_user integer, -- default null new__creation_ip varchar, -- default null new__email varchar, new__url varchar, -- default null new__context_id integer -- default null ) RETURNS integer AS $$ DECLARE v_party_id parties.party_id%TYPE; BEGIN v_party_id := acs_object__new(new__party_id, new__object_type, new__creation_date, new__creation_user, new__creation_ip, new__context_id, 't', new__email, null); insert into parties (party_id, email, url) values (v_party_id, lower(new__email), new__url); return v_party_id; END; $$ LANGUAGE plpgsql; -- added select define_function_args('party__delete','party_id'); -- -- procedure party__delete/1 -- CREATE OR REPLACE FUNCTION party__delete( party_id integer ) RETURNS integer AS $$ DECLARE BEGIN PERFORM acs_object__delete(party_id); return 0; END; $$ LANGUAGE plpgsql; -- added select define_function_args('party__name','party_id'); -- -- procedure party__name/1 -- CREATE OR REPLACE FUNCTION party__name( party_id integer ) RETURNS varchar AS $$ DECLARE BEGIN if party_id = -1 then return 'The Public'; else return null; end if; END; $$ LANGUAGE plpgsql immutable strict; -- added select define_function_args('party__email','party_id'); -- -- procedure party__email/1 -- CREATE OR REPLACE FUNCTION party__email( email__party_id integer ) RETURNS varchar AS $$ DECLARE BEGIN return email from parties where party_id = email__party_id; END; $$ LANGUAGE plpgsql stable strict; -- show errors ------------- -- PERSONS -- ------------- create table persons ( person_id integer not null constraint persons_person_id_fk references parties (party_id) constraint persons_person_id_pk primary key, first_names varchar(100) not null, last_name varchar(100) not null, bio text ); comment on table persons is ' Need to handle titles like Mr., Ms., Mrs., Dr., etc. and suffixes like M.D., Ph.D., Jr., Sr., III, IV, etc. '; -------------------- -- PERSON PACKAGE -- -------------------- -- create or replace package person -- as -- -- function new ( -- person_id in persons.person_id%TYPE default null, -- object_type in acs_objects.object_type%TYPE -- default 'person', -- 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, -- 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, -- context_id in acs_objects.context_id%TYPE default null -- ) return persons.person_id%TYPE; -- -- procedure delete ( -- person_id in persons.person_id%TYPE -- ); -- -- function name ( -- person_id in persons.person_id%TYPE -- ) return varchar2; -- -- end person; -- show errors -- create or replace package body person -- function new -- old define_function_args('person__new','person_id,object_type;person,creation_date;now(),creation_user,creation_ip,email,url,first_names,last_name,context_id') -- new select define_function_args('person__new','person_id;null,object_type;person,creation_date;now(),creation_user;null,creation_ip;null,email,url;null,first_names,last_name,context_id;null'); -- -- procedure person__new/10 -- CREATE OR REPLACE FUNCTION person__new( new__person_id integer, -- default null new__object_type varchar, -- default 'person' new__creation_date timestamptz, -- default now() -- default 'now()' new__creation_user integer, -- default null new__creation_ip varchar, -- default null new__email varchar, new__url varchar, -- default null new__first_names varchar, new__last_name varchar, new__context_id integer -- default null ) RETURNS integer AS $$ DECLARE v_person_id persons.person_id%TYPE; BEGIN v_person_id := party__new(new__person_id, new__object_type, new__creation_date, new__creation_user, new__creation_ip, new__email, new__url, new__context_id); update acs_objects set title = new__first_names || ' ' || new__last_name where object_id = v_person_id; insert into persons (person_id, first_names, last_name) values (v_person_id, new__first_names, new__last_name); return v_person_id; END; $$ LANGUAGE plpgsql; -- procedure delete -- added select define_function_args('person__delete','person_id'); -- -- procedure person__delete/1 -- CREATE OR REPLACE FUNCTION person__delete( delete__person_id integer ) RETURNS integer AS $$ DECLARE BEGIN delete from persons where person_id = delete__person_id; PERFORM party__delete(delete__person_id); return 0; END; $$ LANGUAGE plpgsql; -- function name -- added select define_function_args('person__name','person_id'); -- -- procedure person__name/1 -- CREATE OR REPLACE FUNCTION person__name( name__person_id integer ) RETURNS varchar AS $$ DECLARE BEGIN return first_names || ' ' || last_name from persons where person_id = name__person_id; END; $$ LANGUAGE plpgsql stable strict; -- function first_names -- added select define_function_args('person__first_names','person_id'); -- -- procedure person__first_names/1 -- CREATE OR REPLACE FUNCTION person__first_names( first_names__person_id integer ) RETURNS varchar AS $$ DECLARE BEGIN return first_names from persons where person_id = first_names__person_id; END; $$ LANGUAGE plpgsql stable strict; -- function last_name -- added select define_function_args('person__last_name','person_id'); -- -- procedure person__last_name/1 -- CREATE OR REPLACE FUNCTION person__last_name( last_name__person_id integer ) RETURNS varchar AS $$ DECLARE BEGIN return last_name from persons where person_id = last_name__person_id; END; $$ LANGUAGE plpgsql stable strict; -- show errors create table users ( user_id integer not null constraint users_user_id_fk references persons (person_id) constraint users_user_id_pk primary key, authority_id integer constraint users_authority_id_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, last_visit timestamptz, second_to_last_visit timestamptz, n_sessions integer default 1 not null, -- local authentication information password character varying(128), password_hash_algorithm character varying(100) DEFAULT 'salted-sha1' NOT NULL, salt char(40), password_question varchar(1000), password_answer varchar(1000), password_changed_date timestamptz, -- used for the authentication cookie auth_token varchar(100), -- table constraints constraint users_authority_username_un unique (authority_id, username) ); create index users_username_lower_idx on users(lower(username)); create index users_screenname_lower_idx on users(lower(screen_name)); create table user_preferences ( user_id integer constraint user_preferences_user_id_fk references users (user_id) constraint user_preferences_user_id_pk primary key, prefer_text_only_p boolean default 'f', -- an ISO 639 language code (in lowercase) language_preference char(2) default 'en', dont_spam_me_p boolean default 'f', email_type varchar(64), timezone varchar(100) ); CREATE OR REPLACE FUNCTION inline_1 () RETURNS integer AS $$ BEGIN insert into acs_object_type_tables (object_type, table_name, id_column) values ('user', 'user_preferences', 'user_id'); return 0; END; $$ LANGUAGE plpgsql; select inline_1 (); drop function inline_1 (); -- show errors alter table acs_objects add constraint acs_objects_creation_user_fk foreign key (creation_user) references users(user_id); alter table acs_objects add constraint acs_objects_modifying_user_fk foreign key (modifying_user) references users(user_id); comment on table users is ' The creation_date and creation_ip columns inherited from acs_objects indicate when and from where the user registered. How do we apply a constraint ("email must not be null") to the parent type? '; comment on column users.last_visit is ' Set when user reappears at site '; comment on column users.second_to_last_visit is ' This is what most pages query against (since last_visit will only be a few minutes old for most pages in a session) '; comment on column users.n_sessions is ' How many times this user has visited '; ---------------------- -- ACS_USER PACKAGE -- ---------------------- -- create or replace package acs_user -- as -- -- function new ( -- 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, -- 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, -- password_question in users.password_question%TYPE default null, -- password_answer in users.password_answer%TYPE default null, -- screen_name in users.screen_name%TYPE default null, -- email_verified_p in users.email_verified_p%TYPE default 't', -- context_id in acs_objects.context_id%TYPE default null -- ) -- return users.user_id%TYPE; -- -- procedure approve_email ( -- user_id in users.user_id%TYPE -- ); -- -- procedure unapprove_email ( -- user_id in users.user_id%TYPE -- ); -- -- procedure delete ( -- user_id in users.user_id%TYPE -- ); -- -- end acs_user; -- show errors -- create or replace package body acs_user select define_function_args('acs_user__new','user_id;null,object_type;user,creation_date;now(),creation_user;null,creation_ip;null,authority_id;null,username,email,url;null,first_names,last_name,password,salt,screen_name;null,email_verified_p;t,context_id;null'); -- -- procedure acs_user__new/16 -- CREATE OR REPLACE FUNCTION acs_user__new( p_user_id integer, -- default null p_object_type varchar, -- default 'user' p_creation_date timestamptz, -- default now() p_creation_user integer, -- default null p_creation_ip varchar, -- default null p_authority_id integer, -- defaults to local authority p_username varchar, p_email varchar, p_url varchar, -- default null p_first_names varchar, p_last_name varchar, p_password char, p_salt char, p_screen_name varchar, -- default null p_email_verified_p boolean, -- default 't' p_context_id integer -- default null ) RETURNS integer AS $$ DECLARE v_user_id users.user_id%TYPE; v_authority_id auth_authorities.authority_id%TYPE; v_person_exists integer; BEGIN v_user_id := p_user_id; select 1 from persons into v_person_exists where person_id = v_user_id; if NOT FOUND 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; -- -- procedure acs_user__new/5 -- CREATE OR REPLACE FUNCTION acs_user__new( email varchar, fname varchar, lname varchar, pword char, salt char ) RETURNS integer AS $$ DECLARE BEGIN return acs_user__new(null, 'user', now(), null, null, email, null, fname, lname, pword, salt, null, null, null, 't', null ); END; $$ LANGUAGE plpgsql; -- procedure approve_email -- added select define_function_args('acs_user__approve_email','user_id'); -- -- procedure acs_user__approve_email/1 -- CREATE OR REPLACE FUNCTION acs_user__approve_email( approve_email__user_id integer ) RETURNS integer AS $$ DECLARE BEGIN update users set email_verified_p = 't' where user_id = approve_email__user_id; return 0; END; $$ LANGUAGE plpgsql; -- procedure unapprove_email -- added select define_function_args('acs_user__unapprove_email','user_id'); -- -- procedure acs_user__unapprove_email/1 -- CREATE OR REPLACE FUNCTION acs_user__unapprove_email( unapprove_email__user_id integer ) RETURNS integer AS $$ DECLARE BEGIN update users set email_verified_p = 'f' where user_id = unapprove_email__user_id; return 0; END; $$ LANGUAGE plpgsql; -- procedure delete -- added select define_function_args('acs_user__delete','user_id'); -- -- procedure acs_user__delete/1 -- CREATE OR REPLACE FUNCTION acs_user__delete( delete__user_id integer ) RETURNS integer AS $$ DECLARE BEGIN delete from user_preferences where user_id = delete__user_id; delete from users where user_id = delete__user_id; PERFORM person__delete(delete__user_id); return 0; END; $$ LANGUAGE plpgsql; -- show errors