We create all database objects with scripts in the myfirstpackage/sql/ directory. All database
+Code the data
+model
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
directory. Packages can support Oracle, PostgreSQL, or both. In
this tutorial, we will be working with PostgreSQL
The first file will be myfirstpackage-create.sql. The package
manager requires a file with the name
-, which
-it will run automatically when the package in installed. This file
+packagekey-create.sql, which it
+will run automatically when the package in installed. This file
should create all tables and views.packagekey-create.sql
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
+permission system and to services that integrate with OpenACS
objects, such as general-comments and notification. The cost is that our table
creation code must include several functions, stored procedures,
and is complicated (even for simple tables).
There are many kinds of OpenACS objects in the system. (You can @@ -40,15 +47,15 @@ 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
+
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:$ will automatically be expanded when
-the file is checked in to cvs.
[$OPENACS_SERVICE_NAME ~]$cd /var/lib/aolserver/+the file is checked in to cvs.$OPENACS_SERVICE_NAME/packages/myfirstpackage/sql/postgresql[$OPENACS_SERVICE_NAME ~]$cd /var/lib/aolserver/$OPENACS_SERVICE_NAME/packages/myfirstpackage/sql/postgresql[$OPENACS_SERVICE_NAME postgresql]$emacs myfirstpackage-create.sqlPaste the text below into the file, save, and close.
-Figure 9.3. The Database +
Figure 9.3. The Database Creation Script
-- creation script -- -- \@author joel\@aufrecht.org @@ -77,7 +84,7 @@ uninstalled.[$OPENACS_SERVICE_NAME postgresql]$emacs myfirstpackage-drop.sql-Figure 9.4. Database Deletion +
Figure 9.4. Database Deletion Script
-- drop script -- -- \@author joel\@aufrecht.org @@ -94,32 +101,29 @@(like the creation script the drop script calls a PL/pgSQL function:
content_type__drop_typeRun the create script manually to add your tables and -functions.
-[$OPENACS_SERVICE_NAME postgresql]$psql service0 -f myfirstpackage-create.sql+functions.[$OPENACS_SERVICE_NAME postgresql]$psql service0 -f myfirstpackage-create.sqlpsql: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]$If there are errors, use them to debug the sql file and try +[$OPENACS_SERVICE_NAME 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:
-[$OPENACS_SERVICE_NAME postgresql]$psql service0 -f myfirstpackage-drop.sql+script:[$OPENACS_SERVICE_NAME postgresql]$psql service0 -f myfirstpackage-drop.sqlcontent_type__drop_type ------------------------- 0 (1 row) [$OPENACS_SERVICE_NAME postgresql]$Once both scripts are working without errors, run the create script one last time and -proceed.
-[$OPENACS_SERVICE_NAME postgresql]$psql service0 -f myfirstpackage-create.sql+proceed.[$OPENACS_SERVICE_NAME postgresql]$psql service0 -f myfirstpackage-create.sql