<sect1 id="tutorial-database">
    <title>Setting Up Database Objects</title>
    
    <authorblurb>
      <para>by <ulink url="mailto:joel@aufrecht.org">Joel Aufrecht</ulink></para>
    </authorblurb>
    
    <sect2>
      <title>Code the data model</title>
      <para>We create all database objects with scripts in the
      <computeroutput>myfirstpackage/sql/</computeroutput> directory.  All
      database scripts are database-specific and are thus in either
      the <computeroutput>myfirstpackage/sql/oracle</computeroutput> or
      <computeroutput>myfirstpackage/sql/postgresql</computeroutput> directory.
      Packages can support Oracle, PostgreSQL, or both.  In this
      tutorial, we will be working with PostgreSQL</para>
      <para>The first file will be
      <computeroutput>myfirstpackage-create.sql</computeroutput>.  The
      package manager requires a file with the name
      <computeroutput><replaceable>packagekey</replaceable>-create.sql</computeroutput>,
      which it will run automatically when the package in installed.
      This file should create all tables and views.</para>

      <para>Our package is going to store all of its information in
      one table.  It takes more than just a <computeroutput>CREATE
      TABLE</computeroutput> 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 <computeroutput>general-comments</computeroutput> and 
      <computeroutput>notification</computeroutput>. The cost is
      that our table creation code must include several functions,
      stored procedures, and is complicated (even for simple tables).</para>

    <para>There are many kinds of OpenACS objects in the system.  (You
      can see them with the psql code: <computeroutput> select object_type from
      acs_object_types;</computeroutput>.)  One such object is the
      content_item, which is part of the content repository system.
      To use it, we will make our data objects children of the content_revision object, 
      which is a child of content_item.  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.  (<ulink url="objects.html">More
      information about ACS Objects</ulink>.  <ulink
      url="/doc/acs-content-repository">More information about the
      Content Repository</ulink>.)
</para>
    <figure>
      <title>Tutorial Data Model</title>
      <mediaobject>
        <imageobject>
          <imagedata fileref="images/tutorial-data-model.png" format="PNG" align="center"/>
        </imageobject>
      </mediaobject>
</figure>
      <para>The top of each sql file has some
      standard comments, including doc tags such as
      <computeroutput>@author</computeroutput> which will be picked up
      by the API browser.  The string
      <computeroutput>&#36;Id:$</computeroutput> will automatically be
      expanded when the file is checked in to cvs.</para>
<screen>[$OPENACS_SERVICE_NAME ~]$ <userinput>cd /var/lib/aolserver/<replaceable>$OPENACS_SERVICE_NAME</replaceable>/packages/myfirstpackage/sql/postgresql</userinput>
[$OPENACS_SERVICE_NAME postgresql]$ <userinput>emacs myfirstpackage-create.sql</userinput></screen>
      <para>Paste the text below into the file, save, and close.</para>
      <figure>
        <title>The Database Creation Script</title>
    <programlisting><xi:include href="../../files/tutorial/myfirstpackage-create.sql" xi:parse="text" xmlns:xi="http://www.w3.org/2001/XInclude"><xi:fallback>example missing</xi:fallback></xi:include></programlisting>
      </figure>
    <para>The creation script calls a function in PL/pgSQL (PL/pgSQL is a procedural language extention to sql),
    <computeroutput><ulink url="/api-doc/plsql-subprogram-one?type=FUNCTION&amp;name=content%5ftype%5f%5fcreate%5ftype">content_type__create_type</ulink></computeroutput>, which
    in turn creates the necessary database changes to support our data
    object.  Notice the use of "mfp."  This is derived from "My
    First Package" and ensures that our object is unlikely to conflict
    with objects from other packages.</para>
      <para>Create a database file to drop everything if the package is uninstalled.</para>
      <screen>
[$OPENACS_SERVICE_NAME postgresql]$ <userinput>emacs myfirstpackage-drop.sql</userinput></screen>
      <figure>
        <title>Database Deletion Script</title>
    <programlisting><xi:include href="../../files/tutorial/myfirstpackage-drop.sql" xi:parse="text" xmlns:xi="http://www.w3.org/2001/XInclude"><xi:fallback>example missing</xi:fallback></xi:include></programlisting>
      </figure>
    <para>(like the creation script the drop script calls a PL/pgSQL function: <computeroutput><ulink url="/api-doc/plsql-subprogram-one?type=FUNCTION&amp;name=content%5ftype%5f%5fdrop%5ftype">content_type__drop_type</ulink></computeroutput></para>
      <para>Run the create script manually to add your tables and functions.</para>
      <screen>[$OPENACS_SERVICE_NAME postgresql]$ <userinput>psql service0 -f myfirstpackage-create.sql</userinput>
psql:myfirstpackage-create.sql:15: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'mfp_notes_pkey' for table 'mfp_notes'
psql:myfirstpackage-create.sql:15: NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
 content_type__create_type
---------------------------
                         0
(1 row)

[$OPENACS_SERVICE_NAME postgresql]$</screen>
      <para>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.</para>
      <para>Once you get the same output as shown above, test the drop script:</para>
      <screen>[$OPENACS_SERVICE_NAME postgresql]$ <userinput>psql service0 -f myfirstpackage-drop.sql</userinput>

 content_type__drop_type
-------------------------
                       0
(1 row)

[$OPENACS_SERVICE_NAME postgresql]$</screen>
    <para>Once both scripts are working without errors, <emphasis>run the create script one last time</emphasis> and proceed.</para>
      <screen>[$OPENACS_SERVICE_NAME postgresql]$ <userinput>psql service0 -f myfirstpackage-create.sql</userinput></screen>
    </sect2>
  </sect1>
