First, add an entry to the acs_object_types table with the following PL/SQL call:

begin  
  acs_object_type.create_type ( 
    supertype     => 'acs_object', 
    object_type   => 'note', 
    pretty_name   => 'Note', 
    pretty_plural => 'Notes', 
    table_name    => 'NOTES', 
    id_column     => 'NOTE_ID' 
  ); 
end;
/
show errors;

This PL/SQL call tells the system that we would like to use the table NOTES as the basis for a new object type called note. This type is a subtype of the acs_object type, which means that we want to inherit all of the basic attributes of all ACS objects. As mentioned, it will take some work on our part to make this happen, since Oracle can't do it automatically. In general, most basic applications will define types that are simple subtypes of acs_object.

Add entries to the acs_attributes table to describe the data attributes of the new type. This data can eventually be used to do things like automatically generate user interfaces to manipulate the notes table, though that functionality isn't yet available.

declare 
 attr_id acs_attributes.attribute_id%TYPE; 
begin
  attr_id := acs_attribute.create_attribute ( 
    object_type    => 'note', 
    attribute_name => 'TITLE', 
    pretty_name    => 'Title', 
    pretty_plural  => 'Titles', 
    datatype       => 'string' 
  ); 
 
  attr_id := acs_attribute.create_attribute ( 
    object_type    => 'note', 
    attribute_name => 'BODY', 
    pretty_name    => 'Body', 
    pretty_plural  => 'Bodies', 
    datatype       => 'string' 
  ); 
end; 
/ 
show errors; 

We can stop here and not bother to register the usual OpenACS 3.x attributes of creation_user, creation_date and last_modified, since the object type acs_object already defines these attributes. Again, because the new type note is a subtype of acs_object, it will inherit these attributes, so there is no need for us to define them.

The next thing we do is make a small modification to the data model to reflect the fact that each row in the notes table represents something that is not only an object of type note, but also an acs_object. The new table definition looks like this:

create table notes (
    note_id    integer references acs_objects(object_id) primary key,
    owner_id   integer references users(user_id),
    title      varchar(255) not null,
    body       varchar(1024)
)

The usual creation_date and modified_date columns are absent since they already exist in acs_objects. Also, note the constraint we have added to reference the acs_objects table, which makes clear that since note is a subtype of acs_object, every row in the notes table must have a corresponding row in the acs_objects table. This is the fundamental means by which we model inheritance; it guarantees that any services that use the acs_objects table to find objects will transparently find any objects that are instances of any subtype of acs_objects.

The next step is to define a PL/SQL package for your new type, and write some basic procedures to create and delete objects. Here is a package definition for our new type:

create or replace package note 
as 
  function new ( 
    note_id             in notes.note_id%TYPE default null, 
    owner_id            in notes.owner_id%TYPE default null, 
    title               in notes.title%TYPE, 
    body                in notes.body%TYPE, 
    object_type         in acs_object_types.object_type%TYPE default 'note', 
    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, 
    context_id          in acs_objects.context_id%TYPE default null 
  ) return notes.note_id%TYPE; 
 
  procedure delete ( 
    note_id      in notes.note_id%TYPE 
  ); 
end note; 
/ 
show errors 

You might be wondering what all the extra parameters are to these calls, since we haven't mentioned them before. These parameters are needed to fill out information that will be stored about the object that's not stored directly in the table you defined. The OpenACS Object System defines these attributes on the type acs_object since all objects should have these attributes. Internally, there are tables that store this information for you. Most of the data is pretty self-explanatory and reflects attributes that existed in the earlier OpenACS 3.x data models, with the exception of the context_id attribute.

The context_id attribute stores the ID of an object that represents the default security domain to which the object belongs. It is used by the permissions system in this way: if no permissions are explicitly attached to the object, then the object inherits its permissions from the context. For example, if I had told you how to use the permissions system to specify that an object OBJ was "read only", then any other object that used OBJ as its context would also be "read only" by default. We'll talk about this more later.

The PL/SQL package body contains the implementations of the procedures defined above. The only subtle thing going on here is that we must use acs_object.new to insert a row into acs_objects, before inserting a row into the notes. Similarly, when we delete a row from note, we have to be sure to delete the corresponding acs_object row.

create or replace package body note 
as 
 
  function new ( 
    note_id             in notes.note_id%TYPE default null, 
    owner_id            in notes.owner_id%TYPE default null, 
    title               in notes.title%TYPE, 
    body                in notes.body%TYPE, 
    object_type         in acs_object_types.object_type%TYPE default 'note', 
    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, 
    context_id          in acs_objects.context_id%TYPE default null 
  ) return notes.note_id%TYPE 
  is 
    v_note_id integer; 
  begin 
    v_note_id := acs_object.new ( 
      object_id     => note_id, 
      object_type   => object_type, 
      creation_date => creation_date, 
      creation_user => creation_user, 
      creation_ip   => creation_ip, 
      context_id    => context_id 
    ); 
    
    insert into notes 
     (note_id, owner_id, title, body) 
    values 
     (v_note_id, owner_id, title, body); 
 
     return v_note_id; 
  end new; 
  
  procedure delete ( 
    note_id      in notes.note_id%TYPE 
  ) 
  is 
  begin 
    delete from notes 
    where note_id = note.delete.note_id; 
 
    acs_object.del(note_id); 
  end delete; 
 
end note; 
/ 
show errors; 

That's pretty much it! As long as you use the note.new function to create notes, and the note.delete function to delete them, you'll be assured that the relationship each note has with its corresponding acs_object is preserved.

The last thing to do is to make a file ROOT/packages/notes/sql/notes-drop.sql so it's easy to drop the data model when, say, you're testing:

begin 
  acs_object_type.drop_type ('note'); 
end; 
/ 
show errors 
 
drop package note; 
drop table notes;