Index: openacs-4/packages/acs-core-docs/www/tutorial-database.html =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-core-docs/www/tutorial-database.html,v diff -u -r1.47 -r1.47.2.1 --- openacs-4/packages/acs-core-docs/www/tutorial-database.html 25 Apr 2018 08:38:28 -0000 1.47 +++ openacs-4/packages/acs-core-docs/www/tutorial-database.html 2 Mar 2019 19:30:06 -0000 1.47.2.1 @@ -1,28 +1,19 @@ -
We create all database objects with scripts in the +
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 + tutorial, we will be working with PostgreSQL
The first file will be
myfirstpackage-create.sql
. The
package manager requires a file with the name
-
,
+ packagekey
-create.sqlpackagekey-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 + 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
@@ -31,9 +22,7 @@
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 + stored procedures, and is complicated (even for simple tables).
There are many kinds of OpenACS objects in the system. (You
can see them with the psql code: select object_type from
acs_object_types;
.) One such object is the
content_item, which is part of the content repository system.
@@ -43,23 +32,13 @@
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 +
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/
-[$OPENACS_SERVICE_NAME postgresql]$$OPENACS_SERVICE_NAME
/packages/myfirstpackage/sql/postgresqlemacs myfirstpackage-create.sql
Paste the text below into the file, save, and close.
-Figure 9.3. The Database Creation Script
-- creation script + expanded when the file is checked in to cvs.[$OPENACS_SERVICE_NAME ~]$cd /var/lib/aolserver/$OPENACS_SERVICE_NAME/packages/myfirstpackage/sql/postgresql
+[$OPENACS_SERVICE_NAME postgresql]$emacs myfirstpackage-create.sql
Paste the text below into the file, save, and close.
Figure 9.3. The Database Creation Script
-- creation script -- -- @author joel@aufrecht.org -- @cvs-id &Id:$ @@ -77,20 +56,13 @@ -- necessary to work around limitation of content repository: select content_folder__register_content_type(-100,'mfp_note','t'); --
-The creation script calls a function in PL/pgSQL (PL/pgSQL is a procedural language extension to sql), +
The creation script calls a function in PL/pgSQL (PL/pgSQL is a procedural language extension to sql),
content_type__create_type
, 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.
Create a database file to drop everything if the package is uninstalled.
-
-[$OPENACS_SERVICE_NAME postgresql]$ emacs myfirstpackage-drop.sql
- Figure 9.4. Database Deletion Script
-- drop script + with objects from other packages.Create a database file to drop everything if the package is uninstalled.
+[$OPENACS_SERVICE_NAME postgresql]$emacs myfirstpackage-drop.sql
Figure 9.4. Database Deletion Script
-- drop script -- -- @author joel@aufrecht.org -- @cvs-id &Id:$ @@ -102,30 +74,19 @@ 't', 't' ); --
-(like the creation script the drop script calls a PL/pgSQL function:
-content_type__drop_type
Run the create script manually to add your tables and functions.
-[$OPENACS_SERVICE_NAME postgresql]$psql service0 -f myfirstpackage-create.sql
+
(like the creation script the drop script calls a PL/pgSQL function: content_type__drop_type
Run the create script manually to add your tables and functions.
[$OPENACS_SERVICE_NAME postgresql]$ psql service0 -f myfirstpackage-create.sql
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]$
- 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
+[$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
content_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
- 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