Getting Started With OpenACS

The tutorial is divided in 4 days (actually 3.5 days! although is known that many people finish it sooner). Each day has two sections, in total there are 7 sections, each section will guide you through a set of activities that will keep you learning OpenACS powerful software development features.

This tutorial was written with an OpenACS system running on an Ubuntu system as reference, so the examples, default directories, screenshots, etc are aimed at an Ubuntu installation, but this do not limit the reach of this tutorial to just Linux at the end of the tutorial you'll find a table with the important directories listed both on Ubuntu and Windows, this can help you if you are using Windows as your base system.

On each section you will find different elements, a typical section will consist of:

· The day number we expect the section to be accomplished.

· What you will learn on this section.

· Little notes about related topics.

· Files we will be working on.

· And the “Try It!” icon.
Sometimes after describing an example your will find a: “Try It!” icon,, this means to go ahead and try what we just did, we will usually give a url to try, this URL will be the one of the reference system, something like: http://localhost:8000/helloworld if no url is present then try again the last page we have been working on.

Day 1

Objectives:

On this first day we want to get you up and running with an OpenACS system and have you to test how to create your very first web pages using OpenACS.

Section 1

On this Section You Will Learn:

How to set up your own OpenACS test site
What is Tcl and where to learn it
How to create basic web pages with OpenACS

Installation

Linux: Installing on a Ubuntu system:

You can get the whole system up and running in just a few minutes with it.

Follow these steps to install in Ubuntu, the installer is designed to work with Ubuntu 8.04 (Hardy Heron) but it can easily work on newer releases with slight adjustments.

Follow these steps for Ubuntu 8.04 and Ubuntu 8.10:

Follow these steps for Ubuntu 9.04:

You can find the most up to date instructions on the installer's Wiki Page:

- http://openacs.org/xowiki/ubuntu

After you do this, please go to: http://localhost:8000 and fill out the form that will be on that page, this starts the installation process of the actual service you will be using, it basically populates the database and sets everything up. After you do this, you will need to restart your openacs service, do it this way:

· sudo /etc/init.d/openacs restart

Your normal Ubuntu user probably will not have write permission on the openacs directories, to correct this execute the next commands:

· sudo usermod -a -G www-data ubuntuuser

· sudo chmod -R 775 /usr/share/openacs/www

Windows Installer:

You can download an all in one installer for Windows (XP and Vista 32 bits), this installer gets you set up with everything you need.

For detailed installation instructions please go to http://www.friendlybits.com/en/inf_tec_en/win32openacs_en/

If you are going to use the windows installer please keep this in mind:

It is going to install you 2 services, please use the one named “OpenACS”
Before you start the service, start the database server.
On Windows Vista you will need to start and stop the database server and the openacs service with administrator privileges.

Basic Tcl

What is Tcl?:

Originally from "Tool Command Language", Tcl is a highly flexible and easy to use programming language, it is the language we will be using to work with OpenACS, so if you do not know it, now it is a great time to try and learn a little bit about it.

Why Tcl?:

The OpenACS toolkit is intended to be used with the AOLServer HTTP server, Tcl is AOLServer's built in scripting language so it is natural to write the applications using Tcl.

Example and Resources:

1. A highly recommended Tcl reference book http://philip.greenspun.com/tcl/, it is free and easy to follow, you will learn Tcl in just a few minutes with it, keep it close while developing on OpenACS.

My First Page

My First ADP:

ADP stands for AOLServer Dynamic Pages, they are pretty similar to a plain HTML page, but they include a few more tags and extensions to able to handle dynamic information coming from the Tcl page.
The process of creating a user viewable dynamic page on OpenACS is actually divided on at least 2 files a Tcl file and one ADP file, so for a page called "foo" you will have foo.tcl and foo.adp

The adp page is just an HTML page with the necessary markup to display dynamic data.
The Tcl page handles permissions, logic, calculations and provides the data to the ADP page.
The naming on these pages is really important, remember that tcl is a case sensitive language.

There is a third kind of page that could be involved, this third kind defines the database queries to be used, we will not be using them on this tutorial.

Examples:

On the most basic level you can set a variable on the Tcl file and retrieve it on the ADP, this variable becomes a datasource for the ADP page. Open up your favorite editor, we recommend Notepad++ (http://notepad-plus.sourceforge.net/uk/site.htm) on Windows and Kate (found under the K Menu on KDE) or GEdit (found on the applications menu on Gnome) on a Linux system, and follow these next examples.

Hello World:

helloworld.tcl:

set hello "Hello World!"

helloworld.adp

Hey, @hello@

These simple two lines will generate your first page on OpenACS, you can create both files under the /usr/share/openacs/www directory on your test site. And when you go to http://localhost:8000/helloworld (notice we are not using any file extension) you will see something like this:

Then save it to: "/usr/share/openacs/packages/todo/sql/postgresql/todo-create.sql"

By saving it with that name you make sure that if you ever install the "todo" package in any OpenACS system, it will create the todo_item table during the installation process; right now we will not use this file again, but will come in handy if you need to install the package again on another system.

If you need help running the script, you can check the Appendix B at the end of this tutorial.

You can also specify a file with instructions to be executed when uninstalling the package, you save this to:"/usr/share/openacs/packages/todo/sql/postgresql/todo-drop.sql" for now a simple drop table statement will work.

drop table todo_item;

Adding some test Entries:

Let’s test our table by adding a few items directly on the data base.
You will need to know your user id, to find this out go to the Users administration page: http://localhost:8000/acs-admin/users/ and look for your user details page, there you will a find your user's id.

Then run this commands on the data base prompt:

insert into todo_item values(acs_object_id_seq.nextval, 'My first item', 'My first item description', 'p', 568);
insert into todo_item values(acs_object_id_seq.nextval, 'My second item', 'My second item description', 'p', 568);
insert into todo_item values(acs_object_id_seq.nextval, 'Another item', ' item description', 'p', 568);

On psql run both the create table statement and the test values. See appendix B on how to connect.

Day 3

Objectives:

On your third with OpenACS we want you to be able to finish up the package we started yesterday, have all the user interface set up and be able to use your "To Do" list application.

Section 4

On this Section You Will Learn:

- How to create web forms with OpenACS

o How to use ad_form for:

- Entering new data

- Editing an existing entry

- Displaying one entry's information

- How to create reports with OpenACS

o How to use the List Builder for:

- Displaying information as a list

- Add sorting to a list

- Add actions to a list

Creating user accessible pages:

Our next step will be to create the user interface of our package; we need to create the web pages that the user will be interacting with. Please remember to save all of this pages you want the user to be able to access and the www directory of your package, this is very important for the package to work properly.

Add and Edit item page:

We will create our page to add an item, we will do it in a way that we can reuse the same page to edit the item.

You can do a simple HTML form and handle the user inputs yourself, but there is no fun in that. We will do it the OpenACS way, by using the built in procedure ad_form that allows you to specify the form fields, the actions to be executed on different states of the form, input validation, etc. You can read all about ad_form here: ad_form on the OpenACS API

Let’s do the ADP first, you will see the page is really simple:

todo-ae.adp

<master>
<property name="doc(title)">@page_title@</property>

<formtemplate id="todo_item_form"></formtemplate>

todo-ae.tcl

Let’s begin with the ad_page_contract for this page and declaring a few variables that we will use later.

ad_page_contract {
This page allows the users to add new items to their to do list or edit existing items.
} {
item_id:optional
}

set page_title "Add/Edit Todo Item"
set user_id [ad_conn user_id]

If you notice we have declared item_id as optional parameter on this page, this allows to easily know if we are on edit mode or adding new information, if item_id is present then we are editing an existing item, if not, we are adding a new one, ad_form is smart enough to know this. We have used ad_conn to get information about the current user.

Form Widgets:

Let’s continue by building our forms widgets, take a look at the next code snippet:

ad_form -name todo_item_form -export { user_id } -form {
item_id:key
{title:text {label "Task Title"} }
{description:text(textarea) {label "Description"}}
{due_date:date(date) {label "Due Date: "} {format {MONTH DD YYYY} } }
{status:text(select) {label "Status"}
               {options { {"Pending" "p"}
                                  {"Complete" "c"}
                                  {"Canceled" "x" }
                                 } }
}
}

On the first line we are starting to use ad_form, the "name" parameter gives a name to our form, this way we can reference it on the ADP or later on the same Tcl file. We also "exported" some variables to the ad_form, this takes the variables from our Tcl file and includes them in the form as hidden fields.

ad_form -name todo_item_form -export {user_id } -form {

After the "-form" switch we can start defining our form's widgets, we start with the "key" widget, this is usually a unique identifier for the item we are adding or editing.

item_id:key

Next we have a simple text field.

{title:text {label "Task Title"} }

Our Next line defines a "text area" widget for our task's description.

description:text(textarea) {label "Description"}}

The next one is a date widget, what this actually does is to insert 2 "selects" and one text field so the user can select the month and day and input a year, this saves you the trouble of handling 3 different widgets for just one piece of information. We specify also the format we wish to use with the date.

{due_date:date(date) {label "Due Date: "} {format {MONTH DD YYYY} } }

Our last widget is a "select" widget, this one contains an "options" list with all the possible values and labels this select will present to the user, each option is a list of two elements {<label> <value> }

{status:text(select) {label "Status"}
               {options { {"Pending" "p"}
                                  {"Complete" "c"}
                                  {"Canceled" "x" }
                                 } }
}

At this point if you go to this page in the browser you will see something like this:

2. Updating the item

Now that we have the data on the form we can edit it, but we also need to able to store the edited information, for this we use another switch, the “edit_data” switch, it works pretty much in the same way as new_data does, the only difference is that it runs only when we are updating an item.

-edit_data {
db_dml update_item "
    update todo_item
          set title = :title,
              description =:description,
              status = :status,
              due_date = to_date(:due_date,'YYYY MM DD'),
    where item_id = :item_id and owner_id = :user_id "
}

Remember to add the where clause to make sure you are updating only the item you want to update, in this case we added a couple of other checks to make sure we are updating only items that belong to us.

After submit:

Sometimes, after processing correctly the form you may wish to do some more calculations or simply redirect the user to another page, we can easily do this in the “after_submit” block. Here we will use it to direct the user to this package's index page.

-after_submit {
ad_returnredirect index
ad_script_abort
}

Form Modes:

At last in this section we will add one more trick to our form, a display mode, this way you can present the item's information in a non-editable way to the user.
To do this you just need to add one more switch to the ad_form call, like this:

ad_form -name todo_item_form -export { user_id } -mode $form_mode -form {

We added the form_mode variable as part of the ad_page_contract, with a default value of "edit", it can also be “display” to display the information in a non-editable way.

todo-ae.tcl

The complete Tcl file for todo-ae.tcl looks like this:

ad_page_contract {
This page allows the users to add new items to their to do list or edit existing items.
} {
item_id:optional
{due_date ""}
{form_mode "edit" }
}

set page_title "Add/Edit Todo Item"
set user_id [ad_conn user_id]

ad_form -name todo_item_form -export {user_id } -mode $form_mode -form {
item_id:key
{title:text {label "Task Title"} }
{description:text(textarea),optional {label "Description"}}
{due_date:date(date) {label "Due Date: "} {format {MONTH DD YYYY} } }
{status:text(select) {label "Status"}
               {options { {"Pending" "p"}
                                  {"Complete" "c"}
                                  {"Canceled" "x" }
                                 } }
}
} -select_query {
     select title,
             description,
             to_char(due_date, 'YYYY MM DD') as due_date,
             status
      from    todo_item
      where item_id = :item_id
        and owner_id = :user_id
} -new_data {
db_dml insert_item "
    insert into todo_item
          (item_id, title, description, status, due_date, owner_id, package_id)
    values
      (:item_id, :title, :description, :status, to_date(:due_date,'YYYY MM DD'), :user_id)”
} -edit_data {
db_dml update_item "
    update todo_item
          set title = :title,
              description =:description,
              status = :status,
              due_date = to_date(:due_date,'YYYY MM DD'),
    where item_id = :item_id and owner_id = :user_id "
} -after_submit {
ad_returnredirect index
ad_script_abort
}

Index Page:

We will create the index page as a list of all the “To do” items we have created.

To create the list we will use the List Builder, a powerful tool from the OpenACS templating system, it allows us to easily create lists with a great deal of functionalities. The main procedure of the list builder is template::list::create we can do many things with this procedure, but right now we will limit ourselves to the basics.

We want this page to show a table with this information:

Title	Due Date	Status	Actions
The title for the item goes here.	April 28 2009	Pending	View Edit Delete Mark Completed
another item	April 1 2010	Pending	View Edit Delete Mark Completed
Finish the tutorial	April 27 2009	Pending	View Edit Delete Mark Completed

The way to use the list builders is similar to the way we used ad_form in the previous section, it is just one call to a procedure and different switches affect the outcome and behavior of the list, also, associated to the list there is a multirow, we will create this by using the db_multirow function from the database API.

The ADP page is very simple, so Let’s get that out of the way:

index.adp

<master>
<property name="doc(title)">@title@</property>

<listtemplate name="todo_list"></listtemplate>

The ad_page_contract:

Right now we do not have anything on our ad_page_contract, but as we build functionality into the list we will add one or two more things.

ad_page_contract {
This page will display a list of to do items belonging to the current user.
} {
}

Defining variables:

We will define a couple of variables we will use later, this are just some context variables for the query and the adp page.

set title "My To Do List"
set user_id [ad_conn user_id]

Defining the list:

We will start by defining the list and the first couple of switches:

template::list::create -name todo_list \
-multirow todo_list_mr \

The first line calls the template::list::create procedure, names our list, the name is necessary to be able to call it on the ADP page, we also define here the name of the multirow that will be the source of the list elements.

Defining the elements:

We define the elements of the list inside the "elements" switch, each element follows the same basic format:

element_name {
element_option option_value
element_option option_value
}

There are many options and you do not always need to use them, you can read all about them here: template::list::element::create on the OpenACS API.

Let’s start adding our elements:

A simple:

title {

}

would work, because the list builder knows you want to output the value of the title field on each row returned on the multirow so you do not need any additional options, but we can do it better than that.

To add a column header use the label option:

label "Task"

To make the content of the element, in this case the task's title, link to another page use the link_url_col option

link_url_col <name of a variable with the url>

To add additional options to the link use the link_html option

link_html {title "You can create a title text for the link here" }

Our complete element for the task's title looks like this:

title {
         label "Task"
         link_url_col item_url
         link_html {title "Click to view this item details" }
      }

The rest of the elements can be defined in a similar way, this would be the complete list builder call for this list:

template::list::create -name todo_list \
-multirow todo_list_mr \
-elements {
      title {
         label "Task"
         link_url_col item_url
         link_html {title "Click to view this item details" }
      }
      due_date_pretty {
      label "Due Date"
      }
     status_text {
   label "Status"
     }
    creation_date_pretty {
      label "Creation Date"
    }
    view {
       display_template "View"
       link_url_col item_url
      }
    delete {
        display_template "Delete"
        link_url_col delete_url
    }
    completed {
        display_template "Mark Completed"
        link_url_col completed_url
    }
    cancel {
        display_template "Cancel"
        link_url_col cancel_url
    }
}

But we are not done yet with the list, we need to define its data source, the multirow, for this we will use the db_multirow procedure.

The list's datasource:

The db_multirow proc creates a multirow object, this multirow object will contain the results of the database query we will pass it.

The usual way to use db_multirow is like this:

db_multirow -extend { <list of extra variables to be part of the multirow> } <multirow_name> <multirow statement name> {
sql query to retrieve the data
} {
block of Tcl code that will be executed for each row of the results, it is useful for setting the variables from the extend switch

Our db_multirow is defined like this:

db_multirow -extend { item_url delete_url cancel_url completed_url status_text } todo_list_mr todo_list_mr \
"select item_id,
        title,
        due_date,
        to_char(due_date, 'Month DD YYYY ') as due_date_pretty,
        creation_date,
        to_char(creation_date, 'Month DD YYYY ') as creation_date_pretty,
        status
from todo_item
where owner_id = :user_id"

We have extended our multirow with the item_url, delete_url, cancel_url, completed_url and status_text variables to make the list more useful, we will need to define this in the optional Tcl code block.

The item url will be pretty simple, remember the little "mode" fix we did to the add and edit page just before starting with the list? well, we did it thinking of this, now all we need to do is to tell the add and edit page to show us the information on "display" mode.

set form_mode display
set item_url "todo-ae?[export_vars -url { item_id form_mode }]"

The export_vars procedure takes care of creating the query string for the URL, it only needs a list of variables it will include on the URL.

The status we store on the database is just one character, so it is not very helpful to display that, we will create a new variable to hold a better description for us. The tcl's switch statement comes handy for that.

switch $status {
   p {set status_text "Pending"}
   c {set status_text "Completed"}
   x {set status_text "Canceled"}
   default {set status_text "Unknown" }
}

The delete, completed and cancel links are done pretty much the same way, they link to another page that does the actual update and then we return to the list. We will create these pages later, but right now we can just create the links to them.

   set return_url [util_get_current_url]
   set delete_url "todo-delete?[export_vars -url {item_id return_url}]"

if { $status != "c" } {
      set new_status completed
      set completed_url "todo-update-item?[export_vars -url {item_id new_status return_url}]"
}
if { $status != "x" } {
      set new_status canceled
      set cancel_url "todo-update-item?[export_vars -url {item_id new_status return_url}]"
   }

If everything has worked so far, you should see a list that looks like this:

At this point you have a basic, but functional, to do list application, built using a couple of the tools OpenACS provides. Next is the final index.tcl file with all of the functionality we built in the last few sections.

index.tcl

This is the complete index.tcl file for our package.

ad_page_contract {
This page will display a list of to do items belonging to the current user.
} {
orderby:optional
}

set page_title "My To Do List"
set user_id [ad_conn user_id]

template::list::create -name todo_list \
-multirow todo_list_mr \
-elements {
      title {
         label "Task"
         link_url_col item_url
         link_html {title "Click to view this item details" }
      }
      due_date_pretty {
      label "Due Date"
      }
     status_text {
   label "Status"
     }
    creation_date_pretty {
      label "Creation Date"
    }
    view {
       display_template "View"
       link_url_col item_url
      }
    delete {
        display_template "Delete"
        link_url_col delete_url
    }
    completed {
        display_template "Mark Completed"
        link_url_col completed_url
    }
    cancel {
        display_template "Cancel"
        link_url_col cancel_url
    }
} -orderby {
title {orderby title}
due_date_pretty {orderby due_date}
status_text {orderby status}
creation_date_pretty {orderby creation_date}
} -actions {
"Add New Task" "todo-ae" "Click here to add a new item to the list"
}

if {[exists_and_not_null orderby]} {
    set orderby_clause "ORDER BY [template::list::orderby_clause -name todo_list]"
} else {
    set orderby_clause "ORDER BY due_date asc"
}

db_multirow -extend { item_url delete_url cancel_url completed_url status_text } todo_list_mr todo_list_mr \
"select item_id,
        title,
        due_date,
        to_char(due_date, 'Month DD YYYY ') as due_date_pretty,
        creation_date,
        to_char(creation_date, 'Month DD YYYY ') as creation_date_pretty,
        status
from todo_item
where owner_id = :user_id
$orderby_clause

" {

set form_mode display
set item_url "todo-ae?[export_vars -url { item_id form_mode }]"

switch $status {
   p {set status_text "Pending"}
   c {set status_text "Completed"}
   x {set status_text "Canceled"}
   default {set status_text "Unknown" }
}

set return_url [util_get_current_url]
set delete_url "todo-delete?[export_vars -url {item_id return_url}]"

if { $status != "c" } {
      set new_status completed
      set completed_url "todo-update-item?[export_vars -url {item_id new_status return_url}]"
}
if { $status != "x" } {
      set new_status canceled
      set cancel_url "todo-update-item?[export_vars -url {item_id new_status return_url}]"
   }
}

http://localhost:8000/todo/

Deleting Items:

We will create a very simple page for deleting the items, it will take an item id, delete it and return the user to the previous page. We only need the Tcl file for this page.

todo-delete.tcl

ad_page_contract {
This page will delete an item from the todo list package and then return the user
to the specified return URL.
} {
item_id
return_url
}

set user_id [ad_conn user_id]
db_dml delete_item "delete from todo_item where item_id = :item_id and owner_id = :user_id"
ad_returnredirect $return_url

Updating the Status:

The page for updating the items' status works in pretty much the same way, just the dml operation is different this time.

ad_page_contract {
This page will update an item's status from the todo list package and then return the user
to the specified return URL.
} {
item_id
new_status
return_url
}

set user_id [ad_conn user_id]
switch $new_status {
   "pending"   {set status_code p}
   "completed" {set status_code c}
   "canceled" {set status_code x }
   default     {set status_code p }
}

db_dml update_status "update todo_item
                      set status = :status_code
                      where item_id = :item_id
                        and owner_id = :user_id"

ad_returnredirect $return_url

Section 5

On This Section You Will Learn:

- How to define your own procedures on OpenACS

- How to create a file that contains the procedures

- How to make OpenACS reload your filin memory your files / procedures

Adding a Tcl API to our package:

OpenACS is not limited to user visible pages, in addition each package can define their own procedures. Remember the /tcl directory on our package? We will use that now.

Create a new file called todo-procs.tcl in /usr/share/openacs/packages/todo/tcl/ and paste the following code there.

todo.tcl

ad_library {
Procs for the To Do list package.
}

namespace eval todo {}

ad_proc -public todo::get_status_label { status } {

This procedure receives a status code and returns the corresponding label.
@param status is the status code received.
@return Label corresponding to the status code or Unknown if the status code is not valid

} {
switch $status {
   p {
        set status_text "Pending"
      }
   c {
        set status_text "Completed"
      }
   x {
        set status_text "Canceled"
      }
   default {
        set status_text "Unknown"
      }
}
return $status_text
}

ad_proc -public todo::get_status_code { status_text } {
This procedure returns the status code of the task by the label it has.
@param status_text is the status’s label.
@return One character status code.

} {
set status_text [string tolower $status_text]
switch $status_text {
    "pending"   {
                       set status_code p
                   }
    "completed" {
                       set status_code c
                     }
    "canceled" {
                       set status_code x
                   }
    default     {
                       set status_code p
                   }
   }
return $status_code
}

The first few lines define this tcl file as a new library and provide documentation for its purpose by using the ad_library procedure. Next we define a new namespace for our procedures, this way we make sure any procedure we define here will not have conflicts with another package's procedure with the same name, but different namespace.

Next we defined two different procedures to encapsulate some code we did earlier, we have set their scope as public so any package can use them.

But we cannot still use the package, we will need to tell package manager that there is a new procedure file and that it needs to load it. Go to the package manager http://localhost:8000/acs-admin/apm and look for our package on the list, and click on "reload changed"

Now you can replace the code on the index.tcl page and todo-update-status.tcl for a call to the corresponding procedures, like this. Find the correct place and replace it.

on index.tcl:

set status_text [todo::get_status_label $status ]

on todo-status-update.tcl:

set status_code [todo::get_status_code $new_status]

Day 4

Objectives:

On your fourth day working with OpenACS we want you to learn about the ACS Objects system and how your packages and items can be a part of it so your applications can take full advantage of the powerful services and modules that OpenACS presents.
We will learn integrating OpenACS Object s into your "To Do" list application.

Section 6

On This Section You Wil Learn:

- What are ACS Objects

- How to create anD use ACS Objects

- What Benefits we have using ACS Objects

- We will transform our "To Do" items into an Object

- We will add object functionality to our package

ACS Objects

OpenACS gives us the option to use its objects system to integrate our application to the system and be able to take advantage of many of the different services that OpenACS provides.

The motivation behind the existence and use of ACS objects is the fact that while developing OpenACS applications and modules you will find that many of them share some common characteristics, and needs, such as:

· User information related to a given database entry

· Roles and permissions over those database entries

· Some might need a versioning system for the entries

· Notifications

· Relate comments, ratings and other possible services

· Multiple instances of the application to be used by different groups within the same website

How to use objects? :

Using the ACS Objects it is simple but it requires you to take some extra steps while defining your data model, this is what you need to do.

1. Create a new object type

· Object types are similar to classes on an object oriented programming language

2. Define a table for your application

· This table is for all the application specific information about your object

· This table's primary key column must reference the object_id in the acs_objects table that is the central table that manages the ACS objects

3. Define application specific procedures

· This can be either tcl procedures or stored procedures on the database

4. Make your application "object aware"

· With just a few changes we can make our "To Do" application to be aware of the existence of our objects

Let’s start now to modify our "todo" application to use ACS Objects and integrate it better with OpenACS.

Using Objects:

Preparing our packagE:

We will work upon the same package we built in the last few sections, some new functionality will be added and the ability to create and manage different package instances will be explored.

One of the first things we’ll do is to drop our current table, execute this command on psql:

drop table todo_item;

Do not worry, we will rebuild it again and better than ever.

Defining the data model:

Remember that we store our data model files on /usr/share/openacs/packages/todo/sql/postgresql/

todo-create.sql

This file is the one that will be loaded by the system when we install our package on another OpenACS installation, in this case since the package is already installed, we will define and then load it manually to the database.

This file serves two purposes:

- To create our applications data model table.

- To register a new object type on the system.

The first section of the files defines our new table, if you notice we have eliminated many of the previous fields we were using, that is because the objects system will keep track of the package id, creation user, creation date, etc. Also notice that our item_id now points to the acs_objects table.

The second part is a little bit more interesting, here we will create and execute a function that will register our new object type and will define it as using our table "todo_item" as the table that holds its information.

create table todo_item (
item_id integer,
title varchar(500),
description text,
status char(1),
due_date date default now(),
constraint todo_item_pk primary key (item_id),
constraint todo_item_fk foreign key (item_id) references acs_objects(object_id)
);

-- Here is the function that will register a new object type for our package, in order the parameters are:
-- Name of the new object_type
-- "Pretty" name of the object type, this is an human readable name for our new type
-- Pretty plural, our pretty name in plural form
-- Supertype, this object parent type, usually it is acs_object for a new kind of object
-- Table name, the name that holds the data model for this object
-- ID column, the column of our data model table primary key
-- Package Name, our package's name
-- The last 3 parameters are not relevant at this time

create function inline_0 ()
returns integer as '
begin
    PERFORM acs_object_type__create_type (
        ''todo_item'',
        ''To Do Item'',
        ''To Do Items'',
        ''acs_object'',
        ''todo_item'',
        ''item_id'',
        ''todo'',
        ''f'',
        null,
        null
        );

    return 0;
end;' language 'plpgsql';

select inline_0 ();

drop function inline_0 ();

\i packages/todo/sql/postgresql/todo-package.sql

The last line simply calls a new sql file where we will define a couple of functions to deal with our new object and the acs_objects table:

todo-package.sql

We will define two Postgresql functions to handle our objects, one to create a new object and one to delete it. Those functions must exist in order to comply with the OpenACS standards and expect ACS Object system behavior.

- todo_obj_item__new: This function takes as parameters all the fields that we need to handle our to do list items, it uses some of them to create a new object and just leaves the application specific ones to be added to the todo_item table we defined earlier. Noticed that we must tell the function that creates the new object what type of object are we creating.

- todo_obj_item__delete: This function takes just one argument, the id of the item we want to delete, it first removes it from our applications data model and then calls a function to remove the object from the system, do not try to simply delete the object from acs_objects since this function exists to ensure that the objects are deleted in a safe way.

CREATE OR REPLACE FUNCTION todo_item__new (integer,varchar,text,char,
integer,date,varchar,integer)
RETURNS integer AS '
DECLARE
        p_item_id ALIAS FOR $1;
        p_title        ALIAS FOR $2;        -- default null
        p_description        ALIAS FOR $3;   -- default null
        p_status               ALIAS FOR $4;    -- default null
        p_creation_user ALIAS FOR $5;    -- default null
        p_due_date        ALIAS FOR $6;
        p_creation_ip ALIAS FOR $7;      -- default null
        p_context_id ALIAS FOR $8;       -- default null

        v_id integer;
        v_type varchar;
BEGIN

        v_type := ''todo_item'';

        v_id := acs_object__new(
            p_item_id,
            v_type,
            now(),
            p_creation_user,
            p_creation_ip,
            p_context_id::Integer,
            true
        );

   insert into todo_item
          (item_id, title, description, status, due_date)
   values
          (p_item_id, p_title, p_description, p_status, p_due_date);

   return v_id;

END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION todo_item_delete (integer)
RETURNS VOID AS '
DECLARE
        p_item_id ALIAS FOR $1;
BEGIN
   delete from todo_item where item_id = p_item_id;
   PERFORM acs_object__delete(p_item_id);
END;
' LANGUAGE 'plpgsql';

See appendix B for instructions on how to access your database.

Changes to the packages Pages:

We have modified our data model to use objects, but we need to make sure our application knows this and uses them, in order to do this we are going to make some small changes in our user visible pages.

Add and Edit with objects:

We will start the changes on the "todo-ae" page, the changes we need to make are to ensure that:

- When adding new items, we are adding them as objects

- When editing an item, we are updating the object

- When retrieving and displaying information, we are displaying the object info

Changes Retrieving Information:

When retrieving information, either for editing or displaying the item's details, we need to change the "select_query" block on the ad_form definition, we are simple going to rewrite the query to be like this:

select todo.title,
             todo.description,
             to_char(todo.due_date, 'YYYY MM DD') as due_date,
             todo.status
      from    todo_item todo,
              acs_objects obj
      where todo.item_id = :item_id
        and obj.object_id = todo.item_id
        and obj.creation_user = :user_id
        and obj.context_id = :package_id

As you can see the main change here was getting some of the information out of the acs_objects table, one of the fields included on the where clause, context_id tells in what package instance was the item created and creation_user field tells us who created this item, we use them to be sure we are displaying information that belongs to the correct instance and the appropriate user.

Remember when we used the site map to mount our first package? What we did there is to create a new instance of the package “todo”, you can go there and mount as many instances you want, for example you could mount an instance named “todo-work” and another “todo-school” to keep track of your work and school activities on a separate instance, OpenACS will handle the logic behind creating the URL, permissions, users, etc.

Working with multiple instances:

To create multiple instances of the same package follow these steps:

1. Head to your site’s “Site Map”, http://localhost:8000/admin/site-map

2. Following the same process we did when mounting our package for the first time, select “To Do List” from the dropdown box menu and give this new instance a new name, we called our new instance “todo-work”

3. Now click “Mount Package”, the page will reload and you will have a new instance of our “To Do List” package mounted on, http://localhost:8000/todo-work

4. You can repeat this process and mount as many instances as you like.

5. If you click on the “Permissions” link for any instance, you can see and manage who has what privilege over this particular instance.

6. Click on “Don’t inherit Permission’s from Main Site” to remove the automatic permission’s this instance has obtained from the main site. In this case it will remove the public’s permission to read anything on this instance.

Changes Adding New Data:

The main changes here will be replacing our insert query for a call to the procedure that creates a new object of type todo_item. All of these changes take place inside the "new_data" block of the ad_form definition.

-new_data {
    set context_id [ad_conn package_id]
    set new_object_id [ db_exec_plsql do_insert {
        select todo_item__new (
              :item_id,
              :title,
              :description,
              :status,
              :user_id,
              to_date(:due_date, 'YYYY MM DD HH24 MI SS'),
              :ip_address,
              :context_id
   );
      } ]
}

As you can see we defined a "context_id" variable, this is just for sakes of making the function call clearer on what parameter we are passing, our insert statement was replaced by a function call using db_exec_plsql (which is special for calling database functions).

Changes editing an item:

The changes needed on our edit section are mainly to just deal with the information related to the object and to keep track of when this object was modified and by whom.
These changes go into the "edit_data" block of the ad_form definition.

-edit_data {
    # update the information on our table
   db_dml todo_item_update "
        update todo_item
            set title= :title,
                description = :description,
            status = :status,
                due_date = to_date(:due_date, 'YYYY MM DD HH24 MI SS')
            where item_id = :item_id"

   # update the last modified information on the object
   db_exec_plsql to_do_list_obj_item_object_update {
        select acs_object__update_last_modified(:item_id,:user_id,:ip_address)
   }
   }

To edit our item's information we just issued an updated statement on the database, for the object we used a call to the acs_object__update_last_modified function that will record when the last change was made, on what IP address and by which user.

The complete todo-ae.tcl file

ad_page_contract {
This page allows the users to add new items to their to do list or edit existing items.
} {
item_id:optional
{form_mode "edit" }
}

set page_title "Add/Edit Todo Item"
set user_id [ad_conn user_id]
set package_id [ad_conn package_id]
set ip_address [ad_conn peeraddr]

ad_form -name todo_item_form -export {user_id package_id} -mode $form_mode -form {
item_id:key
{title:text {label "Task Title"} }
{description:text(textarea),optional {label "Description"}}
{due_date:date(date) {label "Due Date: "} {format {MONTH DD YYYY} } }
{status:text(select) {label "Status"}
               {options { {"Pending" "p"}
                                  {"Complete" "c"}
                                  {"Canceled" "x" }
                                 } }
}
} -select_query {
     select todo.title,
             todo.description,
             to_char(todo.due_date, 'YYYY MM DD') as due_date,
             todo.status
      from    todo_item todo,
              acs_objects obj
      where todo.item_id = :item_id
        and obj.object_id = todo.item_id
        and obj.creation_user = :user_id
        and obj.context_id = :package_id
} -new_data {
    set context_id [ad_conn package_id]

      set new_object_id [ db_exec_plsql do_insert {
        select todo_item__new (
              :item_id,
              :title,
              :description,
              :status,
              :user_id,
              to_date(:due_date, 'YYYY MM DD HH24 MI SS'),
              :ip_address,
              :context_id
   );
      } ]
} -edit_data {

   # update the information on our table
   db_dml todo_item_update "
        update todo_item
            set title= :title,
                description = :description,
            status = :status,
                due_date = to_date(:due_date, 'YYYY MM DD HH24 MI SS')
            where item_id = :item_id"

   # update the last modified information on the object
   db_exec_plsql to_do_list_obj_item_object_update {
        select acs_object__update_last_modified(:item_id,:user_id,:ip_address)
   }


} -after_submit {
ad_returnredirect index
ad_script_abort
}

The ADP file does not need any changes at this point.

Index Page with objects:

The index page that just presents a list of our todo items and allows us to add more, also have to be modified a little to make it work with our new data model.

Changing the multirow:

Our first change will be in the multirow's sql query, we will need to add some information from the objects table, change the multirow's query to look like this:

select todo.item_id,
        todo.title,
        todo.due_date,
        to_char(todo.due_date, 'Month DD YYYY ') as due_date_pretty,
        obj.creation_date,
        to_char(obj.creation_date, 'Month DD YYYY ') as creation_date_pretty,
        todo.status
from todo_item todo, acs_objects obj
where obj.context_id = :package_id
    and obj.creation_user = :user_id
    and obj.object_id = todo.item_id
$orderby_clause

Notice how we pulled the creation date out of the object_id and made sure the context of the objects we are displaying is inside our current package.

Changing our orderby statement:

We will need to do a couple of adjustments to the orderby statements, nothing major, we will just make sure that we are ordering by the correct fields.

-orderby {
title {orderby todo.title}
due_date_pretty {orderby todo.due_date}
status_text {orderby todo.status}
creation_date_pretty {orderby obj.creation_date}
}

And those are all the changes we need on the user visible pages to make them work with objects system, but right now we just made sure we can retain the same functionality.

http://localhost:8000/todo/

What can we gain from using the ACS Objects? We will explore that in the next section.

Section 7

On This Section You Wil Learn:

- How to integrate your objects with other OpenACS services

- How to add The service comments to your "To Do" items and other objects

- How to use the Permissions API

Integrating with other services:

One of the most noticeable benefit from using ACS objects is to be able to integrate your application with some of the services provided by OpenACS we will explore how to do it for a couple of them.

Comments:

What we intend to do is to add the ability to store comments on a task, we could simply create our own data model and store our comments, but the real benefit of using OpenACS is taking advantage of all of the great modules and applications that already have been developed and tested with it. For our comments functionality we are going to use the "General Comments" service, that allows us to tie any ACS Object with comments and we'll do all of this in just a few lines of code.

But first, let’s check if the "General Comments" package is already installed, to do this go to: http://localhost:8000/acs-admin/install/ and under "Installed Packages" look for General Comments.

Finishing the transition:

There still a couple of pages we have not updated to use our new objects, the delete and updated status pages. This is how they look now with ACS Objects:

todo-delete.tcl

ad_page_contract {
This page will delete, an item from the todo list package and then return the user
to the specified return URL.
} {
item_id
return_url
}

permission::require_permission -object_id $item_id -privilege delete
set user_id [ad_conn user_id]
db_transaction {
db_exec_plsql delete_item { select todo_item__delete ( :item_id ) }
}

ad_returnredirect $return_url

Notice how we have replaced the delete statement for a call to the todo_item_delete stored procedure, this way we ensure that both the item and the object are deleted correctly. We are also checking that the user has the correct permissions to delete this item.

todo-update-status.tcl

ad_page_contract {
This page will update an item's status from the todo list package and then return the user
to the specified return url.
} {
item_id
new_status
return_url
}

permission::require_permission -object_id $item_id -privilege write

set user_id [ad_conn user_id]
set status_code [todo::get_status_code $new_status]
db_transaction {
db_dml update_status "update todo_item
set status = :status_code
where item_id = :item_id"
}

ad_returnredirect $return_url

This page has not changed much, we are just making sure the user has the required permissions to carry out the operation. We waited to update these pages so we could take advantage of the user permissions checking.

And that is all for objects for now, they are a rich tool and their integration with the other OpenACS services allows you to add a great deal of functionality in an easy and organized way.

Conclusions

Still we need at OpenACS quick tutorial for important areas such as xql, libraries, advanced examples of ad_form & list builder, usage of the content repository, more advanced ACS Objects examples, etc, so hopefully more people will be interested to develop new quick tutorials for OpenACS and its features.

Appendix A

OpenACS on Windows:

Thanks to the flexibility of OpenACS and the software it is built upon it is possible to run an OpenACS system on different environments, including Microsoft Windows © at the moment of this writing the Windows installer works with Windows XP and Vista, both on the 32 bits version.

Next is a table detailing where to find the different directories we discussed on the tutorial, this paths are valid if you used the windows installer.

Directory	Ubuntu	Windows
Main OpenACS installation	/usr/share/openacs	C:\aolserver\servers\openacs
OpenACS packages directory	/usr/share/openacs/packages	C:\aolserver\servers\openacs\packages
Our first “To Do” package	/usr/share/openacs/packages/todo	C:\aolserver\servers\openacs\packages\todo
OpenACS configuration file	/etc/openacs/config.tcl	C:\aolserver\servers\openacs\etc\config.tcl
OpenACS log file	/usr/share/openacs/log/error.log	C:\aolserver\servers\openacs\log\error.log

APPENDIX B

Connecting to postgresQL on Windows and Ubuntu:

There are many ways to connect to your OpenACS database on both Windows and Linux, we will deal here exclusively with how to connect to the database using the built-in psql command line utility always included on a Postgresql installation.

Connecting on Windows:

Text Box: 1. Open a command prompt window.
- Go to, Start à Run à cmd.exe and press enter.
2. Connect as the postgres user by entering the command:
- psql –U postgres –d openacs
Ø If you are asked for a password the default password for the postgres user when using the win32 installer is “qwe123”
3. Now you can perform any sql command on this console.

Connecting on Ubuntu:

Text Box: 1. Open up a terminal application
1. On Gnome: Applications à Accessories à Terminal
2. On KDE: K Menu à Applications à System à Terminal
2. Enter the command:
1. psql –d openacs –U www-data
3. You should be able to enter any sql command.

[1] On a Windows system: C:\aolserver\servers\openacs\packages

Package Key	todo
Package Name	To Do List
Package Plural	To Do Lists
Package Type	Application
OpenACS Core	Leave it unchecked
Singleton	Leave it unchecked
Auto-Mount URI	Leave it blank
Package URL	Leave the default
Initial Version	0.1d
Version URL	Leave the default
Summary	A little application to keep track of items on a "To do list"
Description	A little application to keep track of items on a "To do list"
Primary Owner	Leave the default
Primary Owner URL	Leave the default
Secondary Owner	not necessary
Secondary Owner URL	not necessary
Vendor	not necessary
Vendor URL	not necessary

Getting Started with OpenACS: your quick guide to a powerful framework.

How this Tutorial is Organized:

Objectives:

On this Section You Will Learn:

Installation

Linux: Installing on a Ubuntu system:

Windows Installer:

Basic Tcl

What is Tcl?:

Why Tcl?:

Example and Resources:

My First Page

My First ADP:

Examples:

Hello World:

helloworld.tcl:

helloworld.adp

Connection Information:

connection.tcl

connection.adp

Better looking connection.adp:

Passing Variables between pages:

nameage.tcl

nameage.adp

display-name-age.tcl

display-name-age.adp

Objectives:

On this Section You Will Learn:

My First Package

The To Do list Application:

Creating a New Package:

todo.info:

sql:

tcl:

www:

Mounting our new package:

On this Section You Will Learn:

To Do Data model:

Adding some test Entries:

Objectives:

On this Section You Will Learn:

Creating user accessible pages:

Add and Edit item page:

todo-ae.adp

todo-ae.tcl

Form Widgets:

http://localhost:8000/todo/todo-ae

Inserting new data:

Editing an existing item:

After submit:

Form Modes:

todo-ae.tcl

Index Page:

index.adp

The ad_page_contract:

Defining variables:

Defining the list:

Defining the elements:

The list's datasource:

Adding Sorting:

Adding an Action Button:

index.tcl

Deleting Items:

todo-delete.tcl

Updating the Status:

On This Section You Will Learn:

Adding a Tcl API to our package:

todo.tcl

on index.tcl:

on todo-status-update.tcl:

Objectives:

Section 6

On This Section You Wil Learn:

ACS Objects

How to use objects? :

Using Objects:

Preparing our packagE:

Defining the data model:

todo-create.sql

todo-package.sql

Getting Started with OpenACS:
your quick guide to a powerful framework.