| Prev | Chapter�8.�Development Tutorial | Next |
|---|
+
We create all database objects with scripts in the - samplenote/sql/ directory. All +
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. + the samplenote/sql/oracle or + samplenote/sql/postgresql. Packages can support Oracle, PostgreSQL, or both.
The first file will be - samplenote-create.sql. The + samplenote-create.sql. The package manager requires a file with the name - packagekey-create.sql, + 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 + 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 @@ -25,22 +25,22 @@ 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 + 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 +[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 +[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 + @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.
[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
-- + $Id$ will automatically be + expanded when the file is checked in to cvs.[service0@yourserver postgresql]$ emacs samplenote-create.sqlPaste 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 @@ -50,7 +50,7 @@ -- \i samplenote-table-create.sql -\i samplenote-functions-create.sqlCreate the file to create the database table.
[service0@yourserver postgresql]$ emacs samplenote-table-create.sqlPaste this into the file and save and close.
Create the file to create the database table.
[service0@yourserver postgresql]$ emacs samplenote-table-create.sqlPaste 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 @@ -101,7 +101,7 @@ 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.sqlPaste this into the file and save and close.
Create the file to create the functions used to manipulate records.
[service0@yourserver postgresql]$ emacs samplenote-functions-create.sqlPaste 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 @@ -196,7 +196,7 @@ end; ' language 'plpgsql';Create a database file to drop everything if the package - is uninstalled.
[service0@yourserver postgresql]$ emacs samplenote-drop.sqlFigure�8.5.�Database deletion script
-- packages/samplenote/sql/samplenote-drop.sql + is uninstalled.[service0@yourserver postgresql]$ emacs samplenote-drop.sqlFigure�8.5.�Database deletion script
-- packages/samplenote/sql/samplenote-drop.sql -- drop script -- -- @author rhs@mit.edu @@ -237,11 +237,11 @@ select acs_object_type__drop_type( 'samplenote', 't' - );Add the database files to cvs.
[service0@yourserver postgresql]$ cvs add *.sql + );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" +[service0@yourserver samplenote]$ cvs commit -m "new database files" cvs commit: Examining . cvs commit: Examining sql cvs commit: Examining sql/postgresql @@ -252,8 +252,8 @@ 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 +[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) @@ -283,7 +283,7 @@ (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 +[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