Forum OpenACS Development: OpenACS Data Model

Posted by David Kuczek on
I wanted to better understand the OpenACS data model, so I was browsing through documentation and the sql create files. Here is a first version of the OpenACS data model (5.1 + categories)... Please comment if anything is mistaken or if you would like to add any important elements to it.

2: Re: OpenACS Data Model (response to 1)
Posted by Malte Sussdorff on
This is interesting. Especially it shows how few unique primary keys we have (that do not reference to something else). It would be nice to have the references displayed as arrows, but it would break the view ...
3: Re: OpenACS Data Model (response to 1)
Posted by Nima Mazloumi on
David, thank you very much.

There is actually a programm for postgres that does that automatically. It creates an ER diagram from the database schema:

Autodoc can export to html, docbook, dia and vizgraph (supports ps, gif, jpeg, vrml, png.......).

You can see a complete database of my OpenACS/dotLRN 2.0.3 installation here:


We could extend somehow the documentation package to autocreate the database model for all installed packages.

I have no idea if there is anything similar for Oracle. But I bet there is.


4: Re: OpenACS Data Model (response to 1)
Posted by Malte Sussdorff on
Hi Nima, this sounds awesome, could you publish the documentation to a publicly accessible page, I'm curious
5: Re: OpenACS Data Model (response to 4)
Posted by Joel Aufrecht on
I played with autodoc last week but couldn't get it to process OpenACS databases without an error - glad to see it was something specific to my system.  Nima, can you build one of those for a plain Core install and put the png in acs-core-docs/www/images so I can link to it (go ahead and put in a high-res version and I'll make thumbnails etc) in the docs?
6: Re: OpenACS Data Model (response to 1)
Posted by Nima Mazloumi on

Joel/Malte here the part for the documentation if you like to add that:

How to create a documentation for the OpenACS Data Model

  • Download PostgreSQL Autodoc
  • Unpack autodoc
    gzip -d postgresql_autodoc-1.22.tar.gz 
    tar xsf postgresql_autodoc-1.22.tar 
  • Install missing perl modules with cpan. Configure tells you what modules are missing. Installing a module is very easy as you can see from the following two examples. Run configure first to see if and what module is missing.
    perl -MCPAN -e 'install DBD::Pg'
    perl -MCPAN -e 'install HTML::Template'
  • Install autodoc ./configure make make install
  • Open postgresql_autodoc in emacs to configure your database setting. You have to either set some postgres environment variables or do the following changes. I use service0 database, user service0, no database password and localhost as host.

    Changes from line 65 ff

    # Setup the default connection variables based on the environment
    # my $dbuser = $ENV{'PGUSER'};
    # $dbuser ||= $ENV{'USER'};
    my $dbuser = 'service0';
    # my $database = $ENV{'PGDATABASE'};
    # $database ||= $dbuser;
    my $database = 'service0';
    # my $dbhost = $ENV{'PGHOST'};
    # $dbhost ||= "";
    my $dbhost = 'localhost';

    Changes from line 191 ff

    # Database Connection
    my $dsn = "dbi:Pg:dbname=$database";
    # $dsn .= ";host=$dbhost" if ( "$dbhost" ne "" );
    # $dsn .= ";port=$dbport" if ( "$dbport" ne "" );
    # my $dbh = DBI->connect( $dsn, $dbuser, $dbpass );
    my $dbh = DBI->connect( $dsn, $dbuser);
  • Export the data model
    This results in the following files:
    service0.xml		for docbook
    service0.html		for browser		for vizgraph --> Exports to png, jpg, gif, ps, vrml,.....
    service0.dia		for dia
    unima1.zigzag.dia	for dia
7: Re: OpenACS Data Model (response to 1)
Posted by Nima Mazloumi on
Joel, I can create a png for OpenACS. Just let me know for which version. I am not very good on cvs so either I can create one for the latest tarball or please send me the cvs commands I need to get wanted version of OpenACS you want the graphic for.
8: Re: OpenACS Data Model (response to 1)
Posted by David Kuczek on
Hey Nima,

well... too bad for me 😉

It would be great to have a newly generated model of oacs 5.1.0 at a publicly accessible location (I have to log into your university system to see the models that you posted)...