The OpenACS Structure

Directory Structure

The conventional directory structure set by OpenACS for developers to follow when they are creating packages to run under OpenACS is a well-designed and modular one. It enables the application to operate under different database systems and makes it easy for future developers to edit the application or add functionality to it.

The structure is based upon a system of separating the files depending on their function. The different functions are:

1. Information file, indicating what files there are and where they are located

2. Data model files, which create the data structures that will be used to store the information

a. For PostgreSQL

b. For Oracle

3. Query files, which allow the application to be displayed online and query the database

Files from each one of these functions are each placed in a different directory to separate them from each other and make it easy to identify their purpose.

The directory is structured as follows:

./packages/

à Services packages

à Application packages

à /wp-slim/

à wp-slim.info

à /sql/

à /oracle/

à data model files

à /postgresql/

à data model files

à /www/

à /pics/

à picture files associated with the package

à Query files

à TCL flies

à ADP files

There are several aspects of this file structure that should be highlighted. Firstly, it is through the use of a different directory under the SQL directory that allows OpenACS to operate under several different database systems without any inputs from the user. This is possible through the use of the Query Dispatcher. The query dispatcher acts as a middle agent, deciding which queries to use depending on the database platform. At the moment it can recognise both Oracle and PostgreSQL. The structure of the application is such, however, that it can be extended to include other database managers easily.

OpenACS Architecture

OpenACS architecture involves a number of layers that enables a fairly sophisticated package to operate:

· Independent of the type of database used

· Allows for the use of permissions and grouping of members

· Separation of packages so that the user may choose what functions they may want

The basic elements of the OpenACS application involves the following building blocks:

· AOL Server

· Oracle or PostgreSQL RDBMS

· OpenACS installation

The AOLServer is the means by which OpenACS can maintain a presence online. It acts as a web server and hosts the OpenACS files. It is a very reliable and proven system, used by many large organizations such as Sloan School of Business and Ericsson amongst others.

OpenACS itself can also be downloaded online for free, from http://www.openacs.org, as can all the other systems required (except the Oracle RDBMS which must be purchased).

As a backend OpenACS uses a Relational Database system to keep track of its data store. It can use both a PostgreSQL or an Oracle RDBMS to perform this role. However, the whole purpose of the OpenACS system is to be open source software. Forcing users of the system to purchase a commercial Oracle RDBMS defeats this purpose. This has led to the porting of the OpenACS system so that it is able to work on an open source RDBMS, PostgreSQL. However, at the same time support for the Oracle RDBMS had to continue and this was possible by the implementation of the Database Independent structure designed by OpenACS and discussed later. This is the main purpose of this thesis.

Each of these elements forms the building blocks upon which the software package can run. The actual programming languages used to build up on the OpenACS application involves the use of:

· SQL

· XQL

· TCL

· ADP

· HTML

OpenACS v 4.2 is based on a PostgreSQL or Oracle 8i RDBMS, connects to the Internet via an AOLServer TCL API (which is capable of handling huge number of requests easily – important for scalable systems).

In effect for every installation of the OpenACS application the following systems have to be considered:

1. Unix: This is a remarkably reliable and complex operating that also has security features. It offers multiprocessing and memory protection. And the multi tasking facility allows the use of Relational Database Management Systems.

2. It is also Open source and follows the vein in which OpenACS is following. Unix is also a very good operating system for running high volume web sites.

3. RDBMS: To store the information in an organised manner and allows for easy retrieval of that information. Both are ACID compliant meaning that they allow for atomicity (all events scheduled to happen actually occur or none of the events occur), consistency (that is illegal events that cause the database to enter an invalid state are not allowed to occur and cause a roll back to occur), isolation (results of transactions are invisible to all other transactions until that transaction is finished) and durability (results of transactions are permanent and will survive system failures).

a. Oracle: Commercial RDBMS.

b. PostgreSQL: Open source RDBMS

4. AOLServer Web Server: To prepare the web pages. It also has a high degree of connectivity with various RDBMS. Open source as well.

5. OpenACS Installation

The OpenACS architecture is designed such that it provides for a way of delivering Web services cheaply, effectively and reliably. This is made possible by the use of modular pieces throughout the designing phase.

PERMISSIONS

OpenACS uses a standardised way of checking permissions to objects. This is done by the acs services package called acs-permissions that establishes the permissions that can be used and how they are used.

Data Model

Background Information

The data model is the most difficult part of a package. If the design and execution is not correct then the application may not do what the users want it to do and it might be unreliable. Another matter that must be considered is the effective use of the RDBMS and whether or not only relevant information is stored to make best use of the resources available to it. You cannot use the queries, insertions and retrievals from the web pages without first having defined some tables.

What happens is that tables are created to store info, set variables within these tables to organise the information.

Information is stored in tables when using relational databases. These tables are created to suit particular purposes and each application will have a different table structure to store the information. Each table is designed to store different information. These ‘variables’ are made of different attributes, that includes:

· Varchar: which are string of characters

· Integer: Natural numbers

· Float: Real Numbers

· Boolean: True or False tokens

Wimpy Point Data Model Structure

The wimpy point package uses a data structure that closely follows the structure of an actual presentation. In this regard there are a number of elements that form a presentation:

· Presentation: each presentation has a presentation row associated to it

o The information stored includes:

§ Presentation ID

§ Presentation Title

§ Page Signature

§ Copyright Notice

§ Style

§ Audience

§ Background

· Slide: each presentation has a number of slides associated to it

o The information stored includes:

§ Slide ID

§ Slide Title

§ Text Information

§ Bullet Point Information

§ Images

· Attachments: each slide is allowed to have a number of images associated to it

o The information stored includes:

§ The file name of the image

§ Where in the slide the picture will be displayed

There are also a number of functions that are created when creating the data structure of the package. These functions are what allow the application to insert, modify and remove pieces of information into the database. Without these functions the database would be useless, as there would be no way to store the data. These functions are performed from the SQL queries that form part of the HTML, TCL and ADP files. They represent the actions that are taken on the database.

These functions perform a number of functions such as:

§ Presentations:

· Create new presentation

· Modify existing presentation

· Delete existing presentation

§ Slides:

· Create new slide

· Modify existing slide

· Delete existing slide

§ Attachments:

· Create new attachment

· Modify existing attachment

· Delete existing attachment

The way the presentation is setup is shown below in Figure 5.2. With the box numbered 1, we have the Wimpy Point package and its associated presentations. Each of these presentations has associated with it a number of slides (box 2). Each slide then has associated to it a title, preamble, bullet items, post-amble and an attachment (box 3). Each attachment in turn has information on the name of the file and where on the slide to display the image. Based on this it is evident how one table may have links to objects in other tables, and so on.

Oracle to PostgreSQL Data Model

The creation of a data model compatible with the PostgreSQL RDBMS formed a major part of the thesis project. The previous version of Wimpy Point, version 4.2, utilised the Oracle RDBMS, however, the data model for version 4.2 was not compatible with PostgreSQL. As such the first step in creating the new Wimpy Point application was the creation of a data model compatible with PostgreSQL and Oracle. To facilitate this the directory structure of the application was changed to allow two database systems to run. This was achieved by creating the following directory structure:

à /sql/

à /oracle/

· for the files creating the Oracle database

è /postgresql/

· for the files creating the PostgreSQL database

This structure will allow the Query Dispatcher to pass queries along to the RDBMS dependent on the type of database used. This feature of OpenACS, allowing scalable applications, could eventually see it operating on any number of different database systems. This was made possible by the use of an information file, called wp-slim.info.

Given the new directory structure and the capabilities of the information file the next step in creating an application capable of running against two very different database systems involves the design of a data model creation and removal file: wp-slim-create.sql and wp-slim-drop.sql, for both the Oracle and PostgreSQL versions. Each one is placed into the appropriate sql directory, either within oracle/ or postgresql/, to allow the information file to know where to look for the data model files.

The Oracle data model files already existed, from version 4.2, and these have been left much the same. However, the PostgreSQL data model design would have to be made. For this purpose the oracle data structure was followed. What was changed was the method by which the data structure was actually created.

Some of the differences between the way Oracle and PostgreSQL creates the data model are:

· Use of functions and procedures in Oracle whereas PostgreSQL uses on functions

· Syntactical differences

· Need to have all commands within inline statement in PostgreSQL, whereas in Oracle the commands can be called from outside of functions

· Different variable types: such as text à varchar, blob à record

· Different inputs required to create elements of the table.

· PostgreSQL Tables and other Attributes

Within the Wimpy Point PostgreSQL the following tables were created to store information:

· wp_styles: stores the different styles available for backgrounds, font colours and sizes

· cr_wp_presentations: stores the information related to a presentation such as Presentation name, background and audience

· cr_wp_slides: stores the information of a slide

· cr_wp_presentations_aud: stores the audience group for the presentation

· cr_wp_presentations_back: stores the background of a presentation

· cr_wp_slides_preamble: stores the preamble information of a slide

· cr_wp_slides_postamble: stores the post-amble information for the slide

· cr_wp_slides_bullet_items: stores the bullet item information for the slide

· cr_wp_attachments: stores the file names that are being loaded up

The following section of code (Figure 5.4) creates the tables, which is representative of the different tables create. It is found in the Wimpy Point data model create file and is used to create the presentation table ready to store information.

--jackp: create the presentation table

create table cr_wp_presentations (

presentation_id integer

constraint cr_wp_presentations_id_fk

references cr_revisions

constraint cr_wp_presentations_pk

primary key,

-- The title of the presentations, as displayed to the user.

pres_title varchar(400)

constraint cr_wp_presentations_title_nn

not null,

-- A signature on the bottom.

page_signature varchar(200),

-- The copyright notice displayed on all pages.

copyright_notice varchar(400),

-- Style information.

style integer

constraint cr_wp_style_fk

references wp_styles on delete set null,

public_p boolean

constraint cr_wp_public_p_ck

check(public_p in ('t','f')),

-- Show last-modified date for slide?

show_modified_p boolean

constraint cr_wp_show_p_ck

check(show_modified_p in ('t','f'))

);

Figure 5.4: This code is used to create the cr_wp_presentations table. It is one of many tables that are created to store information for the Wimpy Point package.

This is similar to the way tables are created in Oracle.

These tables have a number of types associated to it. These ‘types’ represent single instances of the table. That is they form rows of the table. The following types are created for Wimpy Point:

· cr_wp_presentation_aud

· cr_wp_presentation_back

· cr_wp_presentation

· cr_wp_slide_preamble

· cr_wp_slide_postamble

· cr_wp_slide_bullet_items

· cr_wp_slide

The code in Figure 5.5 shows the difference with how types are created in Oracle and PostgreSQL for the presentations table:

Oracle

PostgreSQL

content_type.create_type (

content_type => 'cr_wp_presentation',

pretty_name => 'WimpyPoint Presentation',

pretty_plural => 'WimpyPoint Presentations',

table_name => 'cr_wp_presentations',

id_column => 'presentation_id'

);

PERFORM content_type__create_type (

''cr_wp_presentation'',

''content_revision'',

''Wimpy Point Presentation'',

''WimpyPoint Presentations'',

''cr_wp_presentations'',

''presentation_id'',

null

);

Figure 5.5: This table shows the difference in how Oracle and PostgreSQL create content types.

Further to this each type has a number of attributes, or the information that it actually stores, associated to it and these include:

· cr_wp_presentation

o pres_title

o page_signature

o copyright_notice

o style

o public_p

o show_modified_p

· cr_wp_slide

o sort_key

o slide_title

o include_in_outline_p

o context_break_after_p

o style,

o cr_wp_attachment

· cr_wp_presentation_aud

o presentation_id

· cr_wp_presentation_back

o presentation_id

· cr_wp_slide_preamble

o slide_id

· cr_wp_slide_postamble

o slide_id

· cr_wp_slide_bullet_items

o slide_id

· cr_wp_attachment

o content_revision

o display

These attributes are created as shown by the subsequent example:

Oracle

PostgreSQL

attr_id := content_type.create_attribute (

content_type => 'cr_wp_presentation',

attribute_name => 'pres_title',

datatype => 'text',

pretty_name => 'Presentation Title',

pretty_plural => 'Presentation Titles',

column_spec => 'varchar2(400)'

);

attr_id := content_type__create_attribute (

''cr_wp_presentation'',

''pres_title'',

''text'',

''Presentation Title'',

''Presentation Titles'',

null,

''text''

);

Figure 5.6: This table depicts the differences in how the two RDBMS create attributes.

Each of these different tables, types and attributes serve a purpose of storing relevant information that makes up a Wimpy Point presentation.

Database Functions

Every database has a limited usefulness unless there exists a means of inserting, modifying and retrieving information from it. Both Oracle and PostgreSQL Relational Database Management Systems use functions and procedures, in Oracle’s case, to perform these actions to the database. Despite this there is a huge amount of differences in the way both systems perform the actions to the database. These differences include such items as:

· Syntactical differences, an example of which in the devclaration of variables

· Calling of functions

A good example of these differences is illustrated in the way both systems insert information related to a new presentation. The following section of code shows how the Oracle version of Wimpy Point declares variables:

create or replace package body wp_presentation

function new (

creation_date in acs_objects.creation_date%TYPE

default sysdate,

creation_user in acs_objects.creation_user%TYPE

default null,

creation_ip in acs_objects.creation_ip%TYPE default null,

pres_title in cr_wp_presentations.pres_title%TYPE,

page_signature in cr_wp_presentations.page_signature%TYPE,

copyright_notice in cr_wp_presentations.copyright_notice%TYPE,

style in cr_wp_presentations.style%TYPE default -1,

public_p in cr_wp_presentations.public_p%TYPE,

show_modified_p in cr_wp_presentations.show_modified_p%TYPE default 'f',

audience in varchar2,

background in varchar2

) return cr_items.item_id%TYPE

v_item_id cr_items.item_id%TYPE;

v_audience_item_id cr_items.item_id%TYPE;

v_background_item_id cr_items.item_id%TYPE;

v_revision_id cr_revisions.revision_id%TYPE;

v_audience_revision_id cr_revisions.revision_id%TYPE;

v_background_revision_id cr_revisions.revision_id%TYPE;

v_max_id integer;

v_name cr_wp_presentations.pres_title%TYPE;

begin

…

Figure 5.7: This table shows the standard heading for functions in Oracle. Refer to Figure 6.7 for the PostgreSQL version.

As you can see there are two different types of variables those that are inputted to the function and those that are not inputted to the function but used by the function. In Oracle the way to differentiate between the two is to have the inputted variables within the set of brackets after the header and the non-inputted variables between the ‘’is’’ command and the ‘’begin’’ command. This is in comparison with the PostgreSQL method which follows:

create function wp_presentation__new (

timestamp, integer, varchar(400), varchar(400), varchar(400), varchar, integer, boolean, boolean,

varchar, varchar )

returns integer as'

declare
creation_date alias for $1;

creation_user alias for $2;

creation_ip alias for $3;

p_pres_title alias for $4;

p_page_signature alias for $5;

p_copyright_notice alias for $6;

p_style alias for $7;

p_public_p alias for $8;

p_show_modified_p alias for $9;

audience alias for $10;

background alias for $11;

v_item_id cr_items.item_id%TYPE;

v_audience_item_id cr_items.item_id%TYPE;

v_background_item_id cr_items.item_id%TYPE;

v_revision_id cr_revisions.revision_id%TYPE;

v_audience_revision_id cr_revisions.revision_id%TYPE;

v_background_revision_id cr_revisions.revision_id%TYPE;

v_max_id integer;

v_name cr_wp_presentations.pres_title%TYPE;

begin …

Figure 5.8: This is the PostgreSQL syntax for the heading of functions.

Here it is seen that the inputted variables are identified by the use of the word ‘’alias’’ that makes it the variable that stores the information passed to that particular variable. The remaining variables, called virtual variables are declared in the same position, but without the ‘’alias’’ identifier. Further to this inputted variables from within the function need to be declared with a ‘’p_*’’ as their variable name. There are a number of reasons for this the most relevant of which is that when inserting the variables into the tables if the variable name is the same as the column name for the table the PostgreSQL compiler will not accept this.

The following section of code shows how the insert operation is performed:

insert into cr_wp_presentations

(

presentation_id,

pres_title,

page_signature,

copyright_notice,

style,

public_p,

show_modified_p

) values (

v_revision_id,

p_pres_title,

p_page_signature,

p_copyright_notice,

p_style,

p_public_p,

p_show_modified_p

);

Figure 5.9: This is the way information is actually inserted into the tables created. The code is similar for both RDBMS types.

So the first half defines the column names of the appropriate table, which is defined in the header of the insert to command, that the information will be inserted into. Then the last half gives the variables that store the information.

Other functions that are commonly performed onto the database include modification of a past entry, a functionality that allows the presentations properties to be modified. This useful ability is capable of being performed against both the presentation and each slide. This function is performed by the insertion of new values into tables.

Database Permissions and Privileges

In addition to all the table creations and database functions there exists a group of functions that operate under the core services packages. These functions are created by the backbone packages that enable the OpenACS application to actually work together as a single unit. Some of these functions include:

· acs_permissions

· acs-kernel

· acs-content-repository

These functions enable the online community to operate in a way that member’s privacy is not interfered with and they may choose who may, in Wimpy Point’s case:

· View the presentations

· Modify the presentations

· Delete presentations

· Create presentations

As such a presentation can be created wherein the creator can set who is able to view it and who is not. This is an extremely useful capability in the sense of large communities where members may not want to share all their presentations. The following section of code shows just how the permissions are set in the database:

PERFORM acs_permission__grant_permission(

default_context,

registered_users,

''wp_create_presentation''

);

Figure 5.10: This section of code shows how permissions are granted in OpenACS.

Query Files

Query Dispatcher

An advantage of using OpenACS as an online community manager is its ability to work flawlessly with either an Oracle or a PostgreSQL backend. This has an enormous benefit in that it allows people a choice of which database to use, depending on their circumstances and needs. This capability is achieved through the use of the OpenACS Query Dispatcher, which selects which form of query should be sent to the RDBMS, depending on the type of database used.

The Query Dispatcher is a tool created for use with OpenACS; it basically works with the OpenACS architecture to allow the online community application to operate independently of the database used. This has an enormous impact on the scalability of the application. What happens s that the user now does not have to worry about setting up a specific RDBMS that they may not have experience with. They may select one from the list of database systems that OpenACS is able to work with.

Certainly at this point in time the software is capable of working with only two different types of systems, but it is conceivable that in the near future further work will be done to extend the number of databases that OpenACS is compatible with.

The Query Dispatcher does pose a threat to the scalability of the application. If it was capable of running against only a single database then the risk of overloading the system would be much lower. However, with the added functionality of being able to run against a multiple number of database systems this increases the resources needed by the application to operate. This is because each database system is different and needs different data model creation commands and queries.

This could have posed a tremendous risk to the OpenACS project. In truth, however, this problem has been solved by the use of caching of the queries when the package is installed onto the system. This allows the system to load only the information relevant to the type of database used when the Wimpy Point package is installed. This maintains a low level of resources that s required by the package, no matter how many different database systems it is made to operate with.

Query Files

Query files fit in the layer operating between the data model and the user. It allows information to pass between the user and the database. They act as a means of sending messages to the data model to perform the following actions:

· Insert information that the user has entered from the web page

· Retrieve information that the user has requested from a web page

· Modify information that the user has entered from a web page

All these operations make the entire package operational from the Internet. It provides a layer that adds the online functionality to the entire OpenACS application. It also provides the means for the user to interact with the database, setting the package a dynamic environment.

What are the Elements of the Query Files

Query files are made up of three different formats of files, each of which serves a different purpose. These three are:

· ADP: provides the templates of the web page

· TCL: provides the logic that runs behind the presentation layer

· XQL: provides the SQL queries that are passed to the RDBMS

Each of these will be discussed in the following sections and finally an overview of how they work together. It is important to note, however, that when a single web page is loaded up one of each of these three files are also cached and each one has a contribution to make to what is presented on the screen and, or, sent to the database.

ADP

The ADP file contains the HTML scripts that will be used to create the layout of the Web page as well as any forms that may be needed. It is also the means by which images may be loaded. It serves as a template, allowing the designer to set the page however they may wish it to look like, without affecting the functionality with the RDBMS.

Figure 6.10 is an example of how the ADP file can be used to set the Web page. Refer to the Figure to see how it sets a form to allow the user to enter data relating to what they wish to display in the slide.

TCL

The TCL file deals with the declaration of variables and such items that are needed to be passed to and from the database. Without this layer there would be no way for the information stored in the HTML pages, that is the forms, to be passed on to the queries. It serves as the logic layer between the Internet and the database.

Refer to Figure 6.11 to view an example of the TCL file required to set the logic behind the creation of a slide. It declares the variables that will be used as well as how those variables will be used in the query.

XQL

These files contain the actual queries that must be performed against the RDBMS. This is the layer that queries the database. In the previous version of OpenACS there was no need to have XQL files as the queries could simply be put in the TCL file. However, with the implementation of database independence the XQL files are required as they serve as the means of separating the query layer from the rest of the Logic and Layout layers. This was required since the different RDBMS have different syntax for passing queries to them.

As such there are now XQL files that serve to separate the queries between RDBMS. If a query can be passed to both an Oracle RDBMS and a PostgreSQL RDBMS then it is placed in the general XQL file (*.xql). However, should the query be different between the two RDBMS then the Oracle specific queries need to be inserted in the *-oracle.xql file and the PostgreSQL specific queries need to be placed into the *-postgresql.xql file.

Refer to Figure 6.12 to see the PostgreSQL specific query that inserts data into the RDBMS to store data for a slide.

Differences Between the ORACLE and PostgreSQL Query Files

There exists many differences in the structure of the query files from version 4.2 to version 4.x of Wimpy Point. This variation occurs because of the need for a Query Dispatcher to allow for multiple database compatibility.

On a directory level the differences are not noticeable as all the query files are still located in the /www/ directory. However once you move to the file level the differences are more noticeable. One notable difference is the lack of XQL files (files that end with the suffix .xql). This is because when OpenACS was running under version 4.2, the Oracle only version, there was no need to separate the queries passed to the database management system because they were all aimed to comply with Oracle standards. However, with the introduction of version 4.x, and the functionality of multiple database systems compatibility, a need was created to somehow separate the queries needed for different database systems – to allow the Query Dispatcher to perform its job well.

This led to the introduction of the XQL files which can are made up of:

· Filename.xql: Queries that share the same form between both Oracle and PostgreSQL database systems are kept here

· Filename-oracle.xql: Queries that are Oracle specific are kept here

· Filename-postgresql.xql: Queries that are PostgreSQL specific are kept here

This structure allows the Query Dispatcher to know where to look for a particular function. When a function is called the Query Dispatcher first check the *.xql file, to see if the function is stored there. If it is then the Dispatcher will use that query to pass the information to the RDBMS. If, on the other hand, the Query Dispatcher does not find the function there it will then look up the xql file that corresponds to the database system that is being used.

This format makes it simple for future additions to the number of database that OpenACS can work with.

There are some differences in the way queries are written between Oracle and PostgreSQL. Perhaps the most common is the use of different syntax to perform a query. Oracle uses the “select” command whilst PostgreSQL uses the “perform” command to perform database queries.

The following sections of code (Figures 6.10 –6.12) deal with the query files to create a slide and insert information into it. There are a number of files here and each ones role will be described appropriately.

The first file is called ‘’create-slide.adp’’ (Figure 5.11) and this slide deals with creating the HTML format of the function. It creates the forms, loads the images and sets the inputs for the user to interact with. This file has nothing to do with the database and does not carry any queries with it. It is strictly for use with the creation of the web page itself and the forms that will be used. It is used to create what the user will see on their monitor when they run the option to create a slide for their presentation. The code for this file follows and as is evident it clearly deals with only the HTML:

<property name="title">Create A Slide</property>

<h2>@pres_title@</h2>

@nav_bar@

<hr>

function swapWithNext(index)

{

var val = document.f['bullet.' + index].value;

document.f['bullet.' + index].value = document.f['bullet.' + (index+1)].value;

document.f['bullet.' + (index+1)].value = val;

}

</script>

<table>

<tr>

<th align=right nowrap>Slide Title: </th>

</tr>

<th align=right nowrap><br>Preamble:</th>

<td>

<i>(optional random text that goes above the bullet list)</i>

</td>

</tr>

<th align=right nowrap>Bullet Items:</th>

<td>

<ul>

<br><i>You can add additional bullets later.</i>

</ul>

</td>

</tr>

<th align=right nowrap><br>Postamble:</th>

<td>

<i>(optional random text that goes after the bullet list)</i>

</td>

</tr>

</table>

</center>

</form>

Figure 5.11: This section of code depicts the ADP file that is used to create the HTML of the query files.

From here the next file in line is the create-slide.tcl (Figure 5.12). This file deals with the declaration of the variables that will be used to store the user inputs. This is so that the query files can actually get access to those variables as entered by the user. The following section of code displays the TCL file.

# /packages/wp/www/create-slide-2.tcl

ad_page_contract {

@author Paul Konigsberg (paul@arsdigita.com)

@creation-date Tue Nov 21 10:41:42 2000

@cvs-id $Id: create-slide-2.tcl,v 1.1.1.1 2001/04/20 20:51:24 donb Exp $

} {

pres_item_id:naturalnum,notnull

slide_title:notnull

sort_key:naturalnum

preamble

array_max:integer

bullet:array

postamble

}

set package_id [ad_conn package_id]

set user_id [ad_verify_and_get_user_id]

set creation_ip [ad_conn peeraddr]

set bullet_list [list]

for {set i 1} {$i <= $array_max} {incr i} {

if {![empty_string_p $bullet($i)]} {

lappend bullet_list $bullet($i)

}

#insert the slide

db_exec_plsql wp_slide_insert {

begin

:1 := wp_slide.new(

pres_item_id => :pres_item_id,

creation_user => :user_id,

creation_ip => :creation_ip,

creation_date => sysdate,

slide_title => :slide_title,

original_slide_id => -100,

sort_key => :sort_key,

preamble => :preamble,

postamble => :postamble,

bullet_items => :bullet_list

);

end;

}

ad_returnredirect "presentation-top?[export_url_vars pres_item_id]"

Figure 5.12: The TCL file for the creation of a slide. It sets the variables so that they may be passed onto the database.

From here there are the XQL files that interface with the data models. These files take the user input variables and use these to enter information, modify information and get information from the database. There are three types of XQL files depending on the types of query being performed. If the query is compatible with both Oracle and PostgreSQL then there is only a single *.xql file, as in this case. However, if there are differences, as decided by the Query Extractor, then there will be three XQL files created. The general *.xql file, that any common queries can be placed into, a postgresql.xql file and an oracle.xql file. Each of these files will take database specific queries. It is this mechanism that allows the OpenACS system to be database independent. For the example being discussed the following is the section of code dealing with the queries for creating a slide (Figure 5.13).

<?xml version="1.0"?>

<rdbms><type>postgresql</type><version>7.1</version></rdbms>

select wp_slide__new(

:pres_item_id,

now(),

:user_id,

:creation_ip,

:slide_title,

'-1',

'-100',

:sort_key,

:preamble,

:bullet_list,

:postamble,

't',

'f',

NULL

);

</querytext>

</fullquery>

</queryset>

Figure 5.13: This fragment of code shows how the Query is actually passed to the RDBMS.

Creation of Wimpy Point version 4.x

Steps Involved in Thesis

This thesis required a majority of practical with a small amount of research needed to understand the basics of all the different programming languages involved and how they all worked together in OpenACS. The research also covered the standards that were required to be followed by the team at OpenACS. The following are the steps, in chronological order, that were taken to complete the thesis project:

· Research of material

o Programming Languages

§ TCL

§ SQL

§ PostgreSQL Data Model Creation

§ XQL

o OpenACS standards and background

o Wimpy Point

o Online Communities (Greenspuns Book) etc. (quote references here)

· Setup Wimpy Point packages directory, where OpenACS is installed

· Wimpy Point data model creation and drop files for PostgreSQL

· Run the Query Extractor

· Make Query files PostgreSQL compatible

· Test the Wimpy Point package

· Commit the Wimpy Point package to the OpenACS CVS

Each step is further expanded upon in the following sections.

Research

The research conducted involved intensive online study of a number of web sites for the knowledge and background information required to complete the project. The websites referenced are:

· http://www.openacs.org

· http://www.arsdigita.com

Both sites proved a wealth of knowledge and were used to gather information regarding the different languages used such as:

· TCL

· SQL

· PostgreSQL data model creation

In addition it was also important to research how all these different elements of OpenACS fit together. In this respect the www.openacs.org site was of extreme usefulness as they actually contain a number of basic guidelines that allow a beginner to gain a grasp of the work involved and how to proceed. This site also had a discussion forum for package developers that proved to be a very useful resource for problems that were ingrained in the OpenACS system.

The use of both the OpenACS and ArsDigita web sites was good to gain an understanding of how to write code in the different languages involved and also on the standards that must be followed for the OpenACS system. These standards were fairly general in nature and mostly dealt with syntactical changes that would need to be implemented and did not involve any discussions on how the design of the data models would change from OpenACS v.3.2 to OpenACS v.4.2.

An example of some of these standards that proved to be very useful for the porting of the Wimpy Point package are:

1. File Naming

a. General: object_type-verb.extension. In the case of specific packages, such as Wimpy Point there is no need for the object type as all the files will share the same object type, wimpy-point. As such this can be omitted.

b. For Web page flows: These are the files that step through a number of processes for loading information use the following format:

i. verb.extension

ii. verb-2.extesion

iii. …

iv. verb-N.extension

2. Normally these page flows follow the following steps:

3. Present a form to the user.

4. Present confirmation to the user.

5. Perform the database query to return the information to the user.

6. Further to this there are a number of different categories for the file types and each type will go into a different directory structure.

7. Data Model files need to go into the /package_name/sql/postgresql/ directoy.

8. Tcl, ADP and XQL files need to go into the /package_name/www/ directory tree.

9. File Headers

a. Need to include headers in the files. These headers will describe:

i. Path from the server.

ii. Brief description of what the file does.

iii. Authors email address and creation date.

10. Data Modelling

a. At the moment this is up to the creators design.

b. In the future, it is panned to have standardised column names and naming conventions.

11. Documentation

a. Currently at authors discretion.

b. Currently under discussion.

12. When naming the files use name-name.extension

13. When using variables use name_name_name.extension

14. List the standards

The discussion on how OpenACS works and how the different packages work together was also very useful, though the information is very general and should have had more specific data on the dependencies and how packages used the different tables and content types and items that are used by PostgreSQL.

Setup OpenACS Installation

This procedure involved implementing the new directory and file structure that is used by OpenACS v 4.2. As such it involved the creation of a new sub-directory within /sql/ called /postgresql/. This simply separates the data model creation and deletion code based on the type of RDBMS used. It permits the OpenACS package to be database independent and provides for future improvements in the scalability of the application (see Chapter ) as more sub-directories could be added with the appropriate data model creation and deletion code for any type of RDBMS.

This directory structure will allow for the implementation of Database Independence by allowing for the separation and logical organisation of code for different types of database systems.

The new directory structure is graphically displayed below with the change from the previous version of OpenACS to the current version. As is evident there has been the addition of sub-directories within the /sql directory. This allows separation of code for the two RDBMS types whilst also maintaining a logical directory structure:

Figure 6.1: This is a graphical representation of the change required in the Directory Structure to allow for Database Independence.

Data Models

This was the most intense and time-consuming aspect of the project. It involved the most work in the sense that the PostgreSQL data model code had very few similarities to the Oracle data models. There were also a lot of changes in the syntax and headings used by PostgreSQL, which meant that the Oracle code had to be drastically re-written. There are some basic syntactical changes that had to be implemented and these include:

· The use of inline functions to head operations. This was not required under the Oracle system.

Oracle

PostgreSQL

commit;

-- Define some privileges on the wp_presentation object.

Begin