Setting Up Database Objects

+Setting Up Database Objects

Prev	Chapter�9.�Development Tutorial	Next

Setting Up Database Objects

by Joel Aufrecht

OpenACS docs are written by the named authors, and may be edited by OpenACS documentation staff. -

Code the data model

We create all database objects with scripts in the +

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. + myfirstpackage/sql/postgresql directory. Packages can support Oracle, PostgreSQL, or both. In this - tutorial, we'll work only 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.sql, @@ -18,25 +18,26 @@ 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 general-comments and notification. The cost - that our table creation code must include several functions and - stored procedures and is fairly complicated even for a simple - table.

There are many kinds of OpenACS objects in the system. (You - can see them with select object_type from + 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 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. - 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 + 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. (More information about ACS Objects. More information about the Content Repository.) -

Figure�9.2.�Tutorial Data Model

The top of each sql file has some +

Figure�9.2.�Tutorial Data Model

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.

[service0 ~]$ cd /var/lib/aolserver/service0/packages/myfirstpackage/sql/postgresql
-[service0 postgresql]$ emacs myfirstpackage-create.sql

Paste this into the file and save and close.

Figure�9.3.�Database Creation Script - master create file -- creation script +[service0 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:$ @@ -54,14 +55,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, +

The creation script calls a function in PL/pgSQL (PL/pgSQL is a procedural language extention 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 token, derived from "My - First Package," 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.

-[service0 postgresql]$ emacs myfirstpackage-drop.sql

Figure�9.4.�Database deletion script -- drop script + 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. +[service0 postgresql]$ emacs myfirstpackage-drop.sql Figure�9.4.�Database Deletion Script -- drop script -- -- @author joel@aufrecht.org -- @cvs-id &Id:$ @@ -73,28 +73,20 @@ 't', 't' ); - Run the create script manually to add your tables and functions. [service0 postgresql]$ psql -f myfirstpackage-create.sql +

+ (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. [service0 postgresql]$ psql -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) - content_folder__register_content_type ---------------------------------------- - 0 -(1 row) - [service0 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: [service0 postgresql]$ psql -f myfirstpackage-drop.sql - content_folder__unregister_content_type ------------------------------------------ - 0 -(1 row) content_type__drop_type ------------------------- 0 (1 row) -[service0 postgresql]$ Once both scripts are working without errors, run the create script one last time and proceed. [service0 postgresql]$ psql -f myfirstpackage-create.sql

Prev	Home	Next
Creating a Package	Up	Creating Web Pages

docs@openacs.org

View comments on this page at openacs.org +[service0 postgresql]$Once both scripts are working without errors, run the create script one last time and proceed. [service0 postgresql]$ psql -f myfirstpackage-create.sql

Manual testing

Make a list of basic tests to make sure it works

Test Num Action Expected Result

001

Browse to the index page while not logged in and +

Manual testing

Make a list of basic tests to make sure it works

Test Num	Action	Expected Result
001	Browse to the index page while not logged in and while one or more notes exist.	No edit or delete or add links should appear.
002	Browse to the index page while logged in. An Edit link should appear. Click on it. Fill out the form and click Submit.	The text added in the form should be visible on the index page.
API-001	Invoke mfp::note::create with a specific word as the title.	Proc should return an object id.
API-002	Given an object id from API-001, invoke mfp::note::get.	Proc should return the specific word in the title.
API-003	Given the object id from API-001, invoke mfp::note::delete.	Proc should return 0 for success.

Other things to test: try to delete someone else's note. Try to delete your own note. Edit your own note. - Search for a note.

Write automated tests

by Simon Carstensen and Joel Aufrecht

+ Search for a note.

Write automated tests

by Simon Carstensen and Joel Aufrecht

OpenACS docs are written by the named authors, and may be edited by OpenACS documentation staff. -

It seems to me that a lot of people have been asking for some guidelines on how to write automated tests. I've done several tests by now and have found the process to be extremely easy and useful. It's a joy to work with automated testing once you get the hang of it.

Create the directory that will contain the test script and edit the script file. The directory location and file name are standards which are recognized by the automated testing package:

[service0 www]$ mkdir /var/lib/aolserver/service0/packages/myfirstpackage/tcl/test
 [service0 www]$ cd /var/lib/aolserver/service0/packages/myfirstpackage/tcl/test
@@ -67,7 +67,7 @@
 goes inside -test_code {}.  We want to implement test case API-001, "Given an object id from API-001, invoke mfp::note::get.  Proc should return the specific word in the title."
       set name [ad_generate_random_string]
       set new_id [mfp::note::add -title $name]
-      aa_true "Note add succeeded" [exists_and_not_null new_id]
To test our simple case, we must load the test file into the system (just as with the /tcl file in the basic tutorial, since the file didn't exist when the system started, the system doesn't know about it.)  To make this file take effect, go to the APM and choose "Reload changed" for "MyFirstPackage".  Since we'll be changing it frequently, select "watch this file" on the next page.  This will cause the system to check this file every time any page is requested, which is bad for production systems but convenient for developing.  We can also add some aa_register_case flags to make it easier to run the test.  The -procs flag, which indicates which procs are tested by this test case, makes it easier to find procs in your package that aren't tested at all.  The -cats flag, setting categories, makes it easier to control which tests to run.  The smoke test setting means that this is a basic test case that can and should be run any time you are doing any test. (a definition of "smoke test")
Once the file is loaded, go to ACS Automated Testing and click on myfirstpackage.  You should see your test case.  Run it and examine the results.
TCLWebtest tests
API testing can only test part of our package - it doesn't test the code in our adp/tcl pairs.  For this, we can use TCLwebtest.  TCLwebtest must be installed for this test to work.  This provides a library of functions that make it easy to call a page through HTTP, examine the results, and drive forms.  TCLwebtest's functions overlap slightly with acs-automated-testing; see the example provided for one approach on integrating them.
Example
Now we can add the rest of the API tests, including a test with deliberately bad data.  The complete test looks like:
ad_library {
+      aa_true "Note add succeeded" [exists_and_not_null new_id]
To test our simple case, we must load the test file into the system (just as with the /tcl file in the basic tutorial, since the file didn't exist when the system started, the system doesn't know about it.)  To make this file take effect, go to the APM and choose "Reload changed" for "MyFirstPackage".  Since we'll be changing it frequently, select "watch this file" on the next page.  This will cause the system to check this file every time any page is requested, which is bad for production systems but convenient for developing.  We can also add some aa_register_case flags to make it easier to run the test.  The -procs flag, which indicates which procs are tested by this test case, makes it easier to find procs in your package that aren't tested at all.  The -cats flag, setting categories, makes it easier to control which tests to run.  The smoke test setting means that this is a basic test case that can and should be run any time you are doing any test. (a definition of "smoke test")
Once the file is loaded, go to ACS Automated Testing and click on myfirstpackage.  You should see your test case.  Run it and examine the results.
TCLWebtest tests
API testing can only test part of our package - it doesn't test the code in our adp/tcl pairs.  For this, we can use TCLwebtest.  TCLwebtest must be installed for this test to work.  This provides a library of functions that make it easy to call a page through HTTP, examine the results, and drive forms.  TCLwebtest's functions overlap slightly with acs-automated-testing; see the example provided for one approach on integrating them.
Example
Now we can add the rest of the API tests, including a test with deliberately bad data.  The complete test looks like:
ad_library {
     Test cases for my first package.
 }
 
Index: openacs-4/packages/acs-core-docs/www/tutorial-distribute.html
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-core-docs/www/tutorial-distribute.html,v
diff -u -r1.9 -r1.10
--- openacs-4/packages/acs-core-docs/www/tutorial-distribute.html	21 Apr 2004 09:49:43 -0000	1.9
+++ openacs-4/packages/acs-core-docs/www/tutorial-distribute.html	27 Apr 2004 08:01:35 -0000	1.10
@@ -6,5 +6,5 @@
         (37.1KB)
         after the label Distribution
         File: and save the file to
-        /tmp.

+        /tmp.

 
Prev Home  Next
Profile your code Up  Notifications
docs@openacs.org
View comments on this page at openacs.org
Index: openacs-4/packages/acs-core-docs/www/tutorial-newpackage.html
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-core-docs/www/tutorial-newpackage.html,v
diff -u -r1.27 -r1.28
--- openacs-4/packages/acs-core-docs/www/tutorial-newpackage.html	21 Apr 2004 09:49:43 -0000	1.27
+++ openacs-4/packages/acs-core-docs/www/tutorial-newpackage.html	27 Apr 2004 08:01:35 -0000	1.28
@@ -1,31 +1,27 @@
-Creating a PackagePrev Chapter�9.�Development Tutorial  Next
Creating a Package
by Joel Aufrecht
+Creating an ApplicationPrev Chapter�9.�Development Tutorial  Next
Creating an Application
by Joel Aufrecht
           OpenACS docs are written by the named authors, and may be edited
           by OpenACS documentation staff.
-        
The intended page map
Overview
To start developing new code in OpenACS, we build a new
-    package.  A package is a a discrete collection of web
-    pages, tcl code, and database tables and procedures.  A package
-      with user interface is called an application; a package which provides
-      functions to other packages and has no direct interface, a
-      service.  A package
-    can be installed, upgraded, and removed.  It communicates with
-    other packages through an API.  This chapter walks you through the
-    minimum steps to create a useful package, including writing
-      documentation, setting up database tables and procedures,
-      writing web pages, debugging, and automatic regression testing.
+        
The intended page map
Overview
To start developing new code in OpenACS, we build a new package. A package 
+      is a a discrete collection of web pages, tcl code, and database tables and procedures.
+      A package with user interface is called an application; 
+      a package which provides functions to other packages and has no direct interface, a
+      service.  A package can be installed, upgraded, and 
+      removed.  It communicates with other packages through an API.  This chapter walks you through 
+      the minimum steps to create a useful package, including writing documentation, setting up 
+      database tables and procedures, writing web pages, debugging, and automatic regression testing.
       

         This tutorial uses the content repository package.  This
         radically simplifies the database work, but forces us to work
         around the content repository's limitations, including an
         incomplete TCL API.  So the tutorial is messier than we'd like
         right now.  Code that is temporary hackage is clearly marked.
-
       
In this tutorial, we will make an application package for
     displaying a list of text notes.
-
Before you begin
You will need:
A computer with a working installation of
+
Before you begin
You will need:
A computer with a working installation of
 	  OpenACS.  If you don't have this, see Chapter�2, Installation Overview.
 	  
Example files, which are included in the
 standard OpenACS 5.2.0d1 distribution.
-	  
Figure�9.1.�Assumptions in this section
Fully qualified domain name of your server yourserver.test
URL of your server http://yourserver.test:8000
Name of development account service0
New Package key myfirstpackage
Use the APM to initialize a new package
We use the ACS Package Manager (APM) to add, remove, and
+	  
Figure�9.1.�Assumptions in this section
Fully qualified domain name of your server yourserver.test
URL of your server http://yourserver.test:8000
Name of development account service0
New Package key myfirstpackage
Use the APM to initialize a new package
We use the ACS Package Manager (APM) to add, remove, and
     upgrade packages.  It handles package meta-data, such as lists of
     files that belong in the package.  Each package is uniquely
     identified by a package key.  To start developing a new
@@ -35,7 +31,7 @@
     entries for a new package.  (More info on APM)
 
Browse to
         http://yourserver:8000/acs-admin/apm.
-
Click Create a New Package.
Fill in the fields listed below.  Tab through the rest.
+
Click Create a New Package.
Fill in the fields listed below.   Ignore the rest (and leave the check boxes alone).
         (Some will change automatically.  Don't mess with those.)
 

               Package Key:
@@ -44,7 +40,10 @@
               My First Package
             

               Package Plural:
-              My First Package
	  
+              My First Package

+              Package Type:
+              Application
+            
	  
               Initial Version:
               0.1d
             
Summary:
@@ -54,19 +53,18 @@
         
This creates a package rooted at
           /var/lib/aolserver/service0/packages/myfirstpackage.
           This is the "home directory" of our new package, and all
-          files in the package will be within this directory.
Mount the package in the site map
In order to see your work in progress, you must create a
-      map between the URL space of incoming requests and the package.
-      You do this by mounting the package in the Site Map.  This
-      creates a link between the incoming URL and an
-      instance of the package.  (More on Site Maps and nodes)
You can have
-      multiple instances of a package on one site, each with a
+          files in the package will be within this directory.
Add an Application Instance to the Server
In order to see your work in progress, you must create a
+      map between the URL space of incoming requests and the package application instance.
+      You do this by adding the application in the main site administration).  This
+      creates a link between the incoming URL requests and an
+      instance of the application.  (More on applications and nodes)
You can have instances of a package on one site, each with a
       different URL and different permissions, all sharing the same
       code and tables.  This requires that a package be developed
       package-aware.  You'll see how to do that
       in this tutorial.
Browse to
-http://yourserver.test:8000/admin/applications/application-add/.
Choose "My First Package" from the list and click OK (the other fields are optional).
By mounting the package, we've caused all requests to
+http://yourserver.test:8000/admin/applications/application-add.
Choose "My First Package" from the list and click OK (the other fields are optional).
By mounting the package, we've caused all requests to
       http://yourserver.test:8000/my-first-package
-      to be satisfied from the files at /var/lib/aolserver/service0/packages/myfirstpackage/www.
Quick start
The remainder of the tutorial walks you through each file one at a time as you create the package.  You can skip all this, and get a working package, by doing the following:
cd /var/lib/aolserver/service0/packages/acs-core-docs/www/files/tutorial
+      to be satisfied from the files at /var/lib/aolserver/service0/packages/myfirstpackage/www.
Quick start
The remainder of the tutorial walks you through each file one at a time as you create the package.  You can skip all this, and get a working package, by doing the following:
cd /var/lib/aolserver/service0/packages/acs-core-docs/www/files/tutorial
 psql service0 -f myfirstpackage-create.sql
 cp note-edit.* note-delete.tcl index.* ../../../../myfirstpackage/www/
 mkdir ../../../../myfirstpackage/lib
Index: openacs-4/packages/acs-core-docs/www/tutorial-pages.html
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-core-docs/www/tutorial-pages.html,v
diff -u -r1.26 -r1.27
--- openacs-4/packages/acs-core-docs/www/tutorial-pages.html	21 Apr 2004 09:49:43 -0000	1.26
+++ openacs-4/packages/acs-core-docs/www/tutorial-pages.html	27 Apr 2004 08:01:35 -0000	1.27
@@ -1,8 +1,8 @@
 Creating Web PagesPrev Chapter�9.�Development Tutorial  Next
Creating Web Pages
by Joel Aufrecht
           OpenACS docs are written by the named authors, and may be edited
           by OpenACS documentation staff.
-        
Install some API
As a workaround for missing content-repository functionality, copy a provided file into the directory for tcl files:
-    cp /var/lib/aolserver/service0/packages/acs-core-docs/www/files/note-procs.tcl /var/lib/aolserver/service0/packages/myfirstpackage/tcl/
To make this file take effect, go to the APM and choose "Reload changed" for "MyFirstPackage".
Page Map
Our package will have two visible pages.  The first shows a list of all objects; the second shows a single object in view or edit mode, and can also be used to add an object.  The index page will display the list, but since we might reuse the list later, we'll put it in a seperate file and include it on the index page.
Figure�9.5.�Page Map
Build the "Index" page
Each user-visible page in your package has, typically,
+        
Install some API
As a workaround for missing content-repository functionality, copy a provided file into the directory for tcl files:
+    cp /var/lib/aolserver/service0/packages/acs-core-docs/www/files/note-procs.tcl /var/lib/aolserver/service0/packages/myfirstpackage/tcl/
To make this file take effect, go to the APM and choose "Reload changed" for "MyFirstPackage".
Page Map
Our package will have two visible pages.  The first shows a list of all objects; the second shows a single object in view or edit mode, and can also be used to add an object.  The index page will display the list, but since we might reuse the list later, we'll put it in a seperate file and include it on the index page.
Figure�9.5.�Page Map
Build the "Index" page
Each user-visible page in your package has, typically,
       three parts.  The  tcl file
       holds the procedural logic for the page, including TCL and
       database-independent SQL code, and does things like
Index: openacs-4/packages/acs-core-docs/www/tutorial.html
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-core-docs/www/tutorial.html,v
diff -u -r1.11 -r1.12
--- openacs-4/packages/acs-core-docs/www/tutorial.html	18 Feb 2004 14:43:03 -0000	1.11
+++ openacs-4/packages/acs-core-docs/www/tutorial.html	27 Apr 2004 08:01:35 -0000	1.12
@@ -1 +1 @@
-Chapter�9.�Development Tutorial
Prev Part�III.�For OpenACS Package Developers  Next
Chapter�9.�Development Tutorial
Table of Contents
Creating a Package
Setting Up Database Objects
Creating Web Pages
Debugging and Automated Testing
Prev Home  Next
Part�III.�For OpenACS Package Developers Up  Creating a Package
docs@openacs.org
View comments on this page at openacs.org
+Chapter�9.�Development TutorialPrev Part�III.�For OpenACS Package Developers  Next
Chapter�9.�Development Tutorial
Table of Contents
Creating an Application
Setting Up Database Objects
Creating Web Pages
Debugging and Automated Testing
Prev Home  Next
Part�III.�For OpenACS Package Developers Up  Creating an Application
docs@openacs.org
View comments on this page at openacs.org