Setting Up Database Objects

by Joel Aufrecht
OpenACS docs are written by the named authors, and may be edited by OpenACS documentation staff.

Code the data model

We create all database objects with scripts in the samplenote/sql/ directory. All database scripts are database-specific and are thus in either the samplenote/sql/oracle or samplenote/sql/postgresql. Packages can support Oracle, PostgreSQL, or both.

The first file will be samplenote-create.sql. The package manager requires a file with the name packagekey-create.sql, which it will run automatically when the package in installed. This file should create all tables and views.

Our package is going to store all of its information in one table. It takes more than just a CREATE TABLE command, however, because we want to integrate our table with the OpenACS system. By making each record in our table an OpenACS object, we gain access to the permissions system as well as some metadata. The downside is that our table creation code must include several functions and stored procedures and is fairly complicated even for a simple table. A listing is provided below for you to cut and paste. Comments are located within the source code, with each comment preceeding the relevant code. (More info about ACS Objects)

First, create the necessary subdirectories and add them cvs as you go.

[service0@yourserver samplenote]$ cd /web/service0/packages/samplenote
[service0@yourserver samplenote]$ mkdir sql
[service0@yourserver samplenote]$ cvs add sql
Directory /cvsroot/service0/packages/samplenote/sql added to the repository
[service0@yourserver samplenote]$ cd sql/
[service0@yourserver sql]$ mkdir postgresql
[service0@yourserver sql]$ cvs add postgresql
Directory /cvsroot/service0/packages/samplenote/sql/postgresql added to the repository
[service0@yourserver sql]$ cd postgresql/

We break out the sql commands into several files that can be called independently, and then call all of the create files from the master create file. The top of each sql file has some standard comments, including doc tags such as @author which will be picked up by the API browser. The string $Id: tutorial-database.html,v 1.1.2.5 2003/04/29 05:58:34 joela Exp $ will automatically be expanded when the file is checked in to cvs.

[service0@yourserver postgresql]$ emacs samplenote-create.sql

Paste this into the file and save and close.

Figure 8.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.1.2.5 2003/04/29 05:58:34 joela Exp $
--
--

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

Create the file to create the database table.

[service0@yourserver postgresql]$ emacs samplenote-table-create.sql

Paste this into the file and save and close.

Figure 8.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.1.2.5 2003/04/29 05:58:34 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)
);


/* Create a temporary function 'inline_0' which we use to create an
ACS Object Type.  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. */

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

Create the file to create the functions used to manipulate records.

[service0@yourserver postgresql]$ emacs samplenote-functions-create.sql

Paste this into the file and save and close.

Figure 8.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.1.2.5 2003/04/29 05:58:34 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, 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,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';

Create a database file to drop everything if the package is uninstalled.

[service0@yourserver postgresql]$ emacs samplenote-drop.sql

Figure 8.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.1.2.5 2003/04/29 05:58:34 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
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'
    );

Add the database files to cvs.

[service0@yourserver postgresql]$ cvs add *.sql
cvs add: scheduling file `samplenote-create.sql' for addition
cvs add: scheduling file `samplenote-drop.sql' for addition
cvs add: use 'cvs commit' to add these files permanently
[service0@yourserver samplenote]$ cvs commit -m "new database files"
cvs commit: Examining .
cvs commit: Examining sql
cvs commit: Examining sql/postgresql
cvs commit: Examining www
cvs commit: Examining www/doc
cvs commit: Examining www/doc/xml
RCS file: /cvsroot/service0/packages/samplenote/sql/postgresql/samplenote-create.sql,v
done
(many lines omitted)
done
[service0@yourserver samplenote]$

Run the create script manually to add your tables and functions.

[service0@yourserver samplenote]$ cd sql/postgresql/
[service0@yourserver postgresql]$ psql -f samplenote-create.sql
psql:samplenote-table-create.sql:22: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'samplenote_pk' for table 'samplenote'
psql:samplenote-table-create.sql:22: NOTICE:  CREATE TABLE will create implicit
trigger(s) for FOREIGN KEY check(s)
CREATE
CREATE
 inline_0
----------
        0
(1 row)

DROP
 define_function_args
----------------------
                    1
(1 row)

CREATE
 define_function_args
----------------------
                    1
(1 row)

CREATE
 define_function_args
----------------------
                    1
(1 row)

CREATE
[service0@yourserver postgresql]$

If there are errors, use them to debug the sql file and try again. If there are errors in the database table creation, you may need to run the drop script to drop the table so that you can recreate it. The drop script will probably have errors since some of the things it's trying to drop may be missing. They can be ignored.

If there are errors creating the functions, you can re-run the function creation file directly after fixing it, because all of the functions are created with create or replace commands. This will also make it easier to fix mistakes within the functions that aren't apparent until the functions are used. And it will make upgrades easier.

Once you get the same output as shown above, test the drop script:

[service0@yourserver postgresql]$ psql -f samplenote-drop.sql
psql:samplenote-drop.sql:13: NOTICE:  DROP PACKAGE: samplenote
psql:samplenote-drop.sql:13: NOTICE:  DROPPING FUNCTION: samplenote__delete
psql:samplenote-drop.sql:13: NOTICE:  DROPPING FUNCTION: samplenote__name
psql:samplenote-drop.sql:13: NOTICE:  DROPPING FUNCTION: samplenote__new
psql:samplenote-drop.sql:13: NOTICE:  PACKAGE: samplenote: DROPPED
 drop_package
--------------

(1 row)

DELETE 0
CREATE
 inline_0
----------
        0
(1 row)

DROP
psql:samplenote-drop.sql:35: NOTICE:  DROP TABLE implicitly drops referential integrity trigger from table "acs_objects"
psql:samplenote-drop.sql:35: NOTICE:  DROP TABLE implicitly drops referential integrity trigger from table "acs_objects"
DROP
 acs_object_type__drop_type
----------------------------
                          0
(1 row)

[service0@yourserver postgresql]$

Once both scripts are working without errors, run the create script one last time and proceed.

View comments on this page at openacs.org