We create all database objects with scripts in the myfirstpackage/sql/ directory. All database scripts are database-specific and are thus in either the myfirstpackage/sql/oracle or myfirstpackage/sql/postgresql. Packages can support Oracle, PostGreSQL, or both. In this tutorial, we'll work only with PostGreSQL

The first file will be myfirstpackage-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 and to services that integrate with OpenACS objects, such as general-comments and notification. The cost that our table creation code must include several functions and stored procedures and is fairly complicated even for a simple table.

There are many kinds of OpenACS objects in the system. (You can see them with select object_type from acs_object_types;.) One such object is the content_item, which is part of the content repository system. We will make our data objects children of the content_item object. Not only will we gain the benefits of both OpenACS Objects and content objects, we can also use some content repository functions to simplify our database creation. (More information about ACS Objects. More information about the Content Repository.)

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.8 2003/10/28 22:18:47 joela Exp $ will automatically be expanded when the file is checked in to cvs.

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

Paste this into the file and save and close.

--
-- @author rhs@mit.edu
-- @cvs-id $Id: tutorial-database.html,v 1.8 2003/10/28 22:18:47 joela Exp $
--

select content_type__create_type(
    'mfp_note',                    -- content_type
    'content_revision',            -- supertype
    'MFP Note',                    -- pretty_name,
    'MFP Notes',                   -- pretty_plural
    'mfp_notes',                   -- table_name
    'note_id',                     -- id_column
    null                           -- name_method
);

The creation script calls a function, content_type__create_type, which in turn creates the necessary database changes to support our data object. Notice the use of "mfp." This token, derived from "My First Package," ensures that our object is unlikely to conflict with objects from other packages.

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

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

-- packages/myfirstpackage/sql/myfirstpackage-drop.sql
-- drop script
--
-- @author rhs@mit.edu
-- @cvs-id $Id: tutorial-database.html,v 1.8 2003/10/28 22:18:47 joela Exp $
--
-- THIS SCRIPT IS BROKEN AT THE MOMENT
create function inline_0 ()
returns integer as '
declare
    rec                 record;      
begin
    for rec in (select folder_id 
                  from cr_folders 
                 where package_id in (select package_id 
                                        from apm_packages 
                                       where package_key = ''myfirstpackage''))
        loop
            perform content_folder__unregister_content_type(rec.folder_id, ''mfp_note'',''t'');
        end loop;
    return 0;
end;' language 'plpgsql';
select inline_0();
drop function inline_0 ();

select content_type__drop_type(
	   'mfp_notes',
	   't',
	   't'
    );

The drop script is a bit hairier than the create script.

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

[service0@yourserver postgresql]$ psql -f myfirstpackage-create.sql
psql:myfirstpackage-create.sql:14: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'mfp_notes_pkey' for table 'mfp_notes'
psql:myfirstpackage-create.sql:14: NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
 content_type__create_type
---------------------------
                         0
(1 row)

[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.

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

[service0@yourserver postgresql]$ psql -f myfirstpackage-drop.sql
[service0@yourserver postgresql]$

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