Figure 8.2. Database Creation Script

--
-- packages/samplenote/sql/postgresql/samplenote-create.sql
--
-- @author rhs@mit.edu
-- @creation-date 2000-10-22
-- @cvs-id $Id: tutorial-database.html,v 1.1.2.1 2003/03/30 20:33:12 joela Exp $
--
--

/* We create a temporary function 'inline_0' which we use to
create an ACS Object Type.  Each record in our package is a
'samplenote', and each samplenote will be an ACS Object.  By making
our records ACS objects, we gain access to the permissions model and
other meta-functions.  Each 'samplenote' record will have a matching
record in acs_objects.  In general, you should make a table an ACS 
Object Type if you expect to apply permissions directly at that level. */

create function inline_0 ()
returns integer as '
begin
    PERFORM acs_object_type__create_type (
	''samplenote'',			        -- object_type
	''Sample Note'', 			-- pretty_name
	''Sample Notes'', 			-- pretty_plural
	''acs_object'',			        -- supertype
	''samplenote'', 			-- table_name
	''note_id'',        			-- id_column
	null,				        -- package_name
	''f'',				        -- abstract_p
	null,				        -- type_extension_table
	''samplenote__name'' 			-- name_method
	);
    return 0;
end;' language 'plpgsql';
select inline_0 ();
drop function inline_0 ();

/* We create the table.  Each constraint is named to make it easier to
identify during debugging.  The note_id is identical to the object
id.*/

create table samplenote (
    note_id    integer        constraint samplenote_fk
			        references acs_objects(object_id) 
			      constraint samplenote_pk
			        primary key,
    title      varchar(255) 
			      constraint samplenote_title_nn
 			        not null,
    body       varchar(1024)
);

/* Since each record is also an Object, we make a creation function
that will create an object and then use the object id to create a
record in our table.  The function also takes several optional input
variables.  Creation date will default to now; creation user should be
the user id that will own the object; creation ip is optional; context
id should usually be the package id, which will be explained later.*/

create function samplenote__new (varchar,varchar,timestamp,integer,varchar,integer)
returns integer as '
declare
  p_title					alias for $1;
  p_body					alias for $2;
  p_creation_date				alias for $3;
  p_creation_user				alias for $4;
  p_creation_ip					alias for $5;
  p_context_id					alias for $6;
  v_samplenote_id				int;
begin
	v_samplenote_id := acs_object__new (
		null,
		''samplenote'',
		p_creation_date,
		p_creation_user,
		p_creation_ip,
		p_context_id
	);
	insert into samplenote
	  (note_id, title, body)
	values
	  (v_samplenote_id, p_title, p_body);
	PERFORM acs_permission__grant_permission(
          v_samplenote_id,
          p_owner_id,
          ''admin''
    );
	return v_samplenote_id;
end;' language 'plpgsql';

/* The __delete function deletes a record and all related overhead. */
  
create function samplenote__delete (integer)
returns integer as '
declare
  p_samplenote_id				alias for $1;
begin
    delete from acs_permissions
		   where object_id = p_samplenote_id;
	delete from samplenote
		   where note_id = p_samplenote_id;
	raise NOTICE ''Deleting samplenote...'';
	PERFORM acs_object__delete(p_samplenote_id);
	return 0;
end;' language 'plpgsql';

/* When we created the acs object type above, we specified a
'name_method'.  This is the name of a function that will return the
name of the object.  This is a convention ensuring that all objects
can be identified.  Now we have to build that function.  In this case,
we'll return a field called title as the name. */

create function samplenote__name (integer)
returns varchar as '
declare
    p_samplenote_id      alias for $1;
    v_samplenote_name    samplenote.title%TYPE;
begin
	select title into v_samplenote_name
		from samplenote
		where samplenote_id = p_samplenote_id;
    return v_samplenote_name;
end;
' language 'plpgsql';

Figure 8.3. Database deletion script

-- packages/samplenote/sql/samplenote-drop.sql
-- drop script
--

/* This script removes from the database everything associated with
our table. */

--drop functions
drop function samplenote__new (varchar,varchar,timestamp,integer,varchar,integer);
drop function samplenote__delete (integer);
drop function samplenote__name (integer);

--drop permissions
delete from acs_permissions where object_id in (select note_id from samplenote);

--drop objects
create function inline_0 ()
returns integer as '
declare
	object_rec		record;
begin
	for object_rec in select object_id from acs_objects where object_type=''samplenote''
	loop
		perform acs_object__delete( object_rec.object_id );
	end loop;
	return 0;
end;' language 'plpgsql';

select inline_0();
drop function inline_0();

--drop table
drop table samplenote;

--drop type
select acs_object_type__drop_type(
	   'samplenote',
	   't'
    );