Figure 9.2. Database Creation Script - master create file

--
-- packages/samplenote/sql/postgresql/samplenote-create.sql
--
-- @author rhs@mit.edu
-- @creation-date 2000-10-22
-- @cvs-id $Id: tutorial-database.html,v 1.2 2003/06/24 03:58:11 joela Exp $
--
--

\i samplenote-table-create.sql
\i samplenote-functions-create.sql

Figure 9.3. Database Creation Script - table

--
-- packages/samplenote/sql/postgresql/samplenote-table-create.sql
--
-- @author rhs@mit.edu
-- @creation-date 2000-10-22
-- @cvs-id $Id: tutorial-database.html,v 1.2 2003/06/24 03:58:11 joela Exp $
--

/* 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)
);


/* Each record in our package is an 'samplenote', and each samplenote
is an ACS Object.  That means that each record in the samplenote table
has a corresponding record in acs_objects.  By making our records ACS
objects, we gain access to the permissions model and other
meta-functions.  In general, you should make a table an ACS Object
Type if you expect to apply permissions directly at that level. */

select 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
	);

Figure 9.4. Database Creation Script - functions

--
-- packages/samplenote/sql/postgresql/samplenote-functions-create.sql
--
-- @author rhs@mit.edu
-- @creation-date 2000-10-22
-- @cvs-id $Id: tutorial-database.html,v 1.2 2003/06/24 03:58:11 joela Exp $
--
--

/* 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 input variables.
Title is required
Body is required
Creation date is optional and defaults to now
Creation user, required, is the user id owns the object
Creation ip is optional
Context id, required, is the id of the package instance.  This allows 
segregation of records by package instance, required to make the package 
"package-aware." 
define_function_args prepares the function to be used by a tcl wrapper function. */

select define_function_args('samplenote__new','note_id,title,body,creation_date,creation_user,creation_ip,context_id'); 

create or replace function samplenote__new (integer,varchar,varchar,timestamptz,integer,varchar,integer)
returns integer as '
declare
  p_note_id                                     alias for $1;
  p_title					alias for $2;
  p_body					alias for $3;
  p_creation_date				alias for $4;
  p_creation_user				alias for $5;
  p_creation_ip					alias for $6;
  p_context_id					alias for $7;
  v_samplenote_id				int;
begin
	v_samplenote_id := acs_object__new (
		p_note_id,
		''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_creation_user,
          ''admin''
    );
	return v_samplenote_id;
end;' language 'plpgsql';

/* The __delete function deletes a record and all related overhead. */
  

select define_function_args('samplenote__delete','note_id');
create or replace 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. */

select define_function_args('samplenote__name','note_id');

create or replace 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 note_id = p_samplenote_id;
    return v_samplenote_name;
end;
' language 'plpgsql';

Figure 9.5. Database deletion script

-- packages/samplenote/sql/samplenote-drop.sql
-- drop script
--
-- @author rhs@mit.edu
-- @creation-date 2000-10-22
-- @cvs-id $Id: tutorial-database.html,v 1.2 2003/06/24 03:58:11 joela Exp $
--

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

--drop package, which drops all functions created with define_function_args

select drop_package('samplenote');

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

--drop objects
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';

--drop table
drop table samplenote;

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