Forum OpenACS Q&A: PostgreSQL/OACS Module Development

Collapse
1: PostgreSQL/OACS Module Development
Posted by John Luong on 12/26/02 01:07 PM
Greetings and Happy Holidays to all,

I've been following OpenACS for some time now, but I hadn't had a need to actually work with it until now.  (Longwinded way of saying: "Long time listener, first time caller.")

Basically, I'm trying to crank out a quick and dirty web application for work and I figured it would be a good time to use OpenACS because I get the feeling that I'll be producing more applications in the future and that OpenACS would be an ideal intranet/application server framework for our purposes.

After getting everything installed, I read over the developer documentation and noticed that the example Notes application that they walked through was written in plsql/Oracle.

After studying the PostgreSQL version of the "Notes" application it has become painfully clear that "winging it" just won't work despite my data needs being only marginally more than the Notes application.  I'm wondering if there is any documentation that involves walking through the PL/pgSQL language.  I've already read the PostgreSQL Programmer's Guide section regarding PL/pgSQL, but it failed to teach me substantially more than I could glean from the Notes/PostgreSQL code.

Any suggestions are appreciated.

John

Collapse
2: Re: PostgreSQL/OACS Module Development (response to 1)
Posted by Claudio Pasolini on 12/26/02 01:43 PM
The better source I found is this, taken from the book Practical PostgreSQL.

Claudio

Collapse
3: Re: PostgreSQL/OACS Module Development (response to 1)
Posted by Tilmann Singer on 12/27/02 08:06 AM
Have you seen Roberto's guide about the differences between plsql and plpgsql?

http://www.postgresql.org/idocs/index.php?plpgsql-porting.html

Regarding the notes package as example: I saw that it has one major shortcoming in that it uses the context_id of acs_objects to distinguish which package a note belongs to, which is bad practice. The context_id is only there for security context information, not for application specific data (at least that's the latest state of the discussion as far as I know).

So there should actually be a package_id column in the notes table. Take a look at e.g. packages/page/www/index.tcl for a simple example how to handle this (don't take the lack of templating in that package as an example though 😉)

Collapse
4: Re: PostgreSQL/OACS Module Development (response to 1)
Posted by Samir Joshi on 12/27/02 12:21 PM
John,

I am bit hesitant as I write this , but this is the way I followed in my 'Intro. to OpenACS Programming' sessions, and it works.

Specificaly, I do not start with PL/pgSQL usage at all ! Instead I introduce participants to db api for invoking simple SQL commands and create those tables from psql interface. Note that this is not recommended long term practice, but works best if you want to get your hand dirty early on.

I am in process of creating some examples that will introduce tweaking OpenACS in what ol' Mr. Greenspun called 'gentle slope' way.

Collapse
5: Re: PostgreSQL/OACS Module Development (response to 4)
Posted by Tilmann Singer on 12/28/02 10:32 AM
Does this 'Intro. to OpenACS Programming' exist already? Where can we look at it?
Collapse
6: Re: PostgreSQL/OACS Module Development (response to 4)
Posted by John Luong on 12/28/02 03:42 PM
Thanks to everyone who replied.

I had already found/read PostgreSQL documentation for PL/pgSQL and the database API, so for the most part, I'm up to speed on those things.

What I am really after is something that resembles the OpenACS documentation that walks through the Notes example, but in the context of PostgreSQL.  After reading the documentation, the Note example makes perfect sense in the Oracle context and I have a good understanding of how to implement the same things in PL/pgSQL.  What I don't understand is the file:

/packages/notes/postgresql/notes-sc-create.sql

acs_sc_impl__new
function notes__itrg
trigger note_itrg

So far in my application, I've omitted these because in my case, the application is better done than good (for now), but I feel that in the long term scheme of things, it'd be a good thing to know.  That's mostly what I'm after and a more concrete example.

I'm also wondering if anybody is using OpenACS/PostgreSQL as an application interface while using Perl/DBI/DBDpg to feed data in.

Thanks again for all your replies.

Collapse
7: Re: PostgreSQL/OACS Module Development (response to 6)
Posted by Vinod Kurup on 12/28/02 07:50 PM
Hi John, 
> What I don't understand is the file:
>
>/packages/notes/postgresql/notes-sc-create.sql
>
>acs_sc_impl__new
>function notes__itrg
>trigger note_itrg
That file is meant to set up search services for the notes package. Site wide search is set up through things called service contracts (sc). See the acs-service-contract docs for more info.

Basically the search package publishes a contract that other packages have to fulfill if they want their content to be available to site wide search. the ac_sc_impl* calls notify openacs that notes is fulfilling those contracts. The triggers run everytime you insert, update or delete a note and inform the search package of the change in the data.