d. OpenACS Programming FAQ

Q: I need to brush up on my sql - any good docs?

A:

• SQL for Web Nerds by Philip Greenspun.
• Postgres SQL by Bruce Momjian
• Postgres Interactive documentation
• Oracle documentation

Q: How do I get the latest CVS? How do I commit changes?

A: CVS Instructions

Q: How do I keep track of what's been committed into CVS, bugs, etc?

A: You can track it through RSS or even IMAP. You can truck bugtracker bugs either through email notifications or this RSS feed

Q: How do I help out or get involved?

A: See the OpenACS contribute page.

Also, look at getting started with OpenACS.

Q: How do I contribute a package?

A: See http://openacs.org/contribute and http://openacs.org/forums/message-view?message_id=133054

Q: Where do I get the Query Extractor?

A: You probably don't want to use it. It was used a long time ago when people were porting OpenACS from ACS. But if you insist, Download the Query Extractor. This tool will extract queries from aD packages and do a lot of the porting work for you. Documentation on how to use it is in the /doc directory once you extract the tar file.

Q: How do I use the Content Repository?

A: Currently, the best documentation for the content repository is at Jade Rubick's site.

Q: What goes in which .xql file again?

A: Try this post

Q: I've added a tcl library file to my package, but it's not getting sourced. Why?

A: Probably because the APM doesn't recognize it. The file has to end in "-procs.tcl" or "-init.tcl". See this thread. This thread describes the difference between -procs.tcl and -init.tcl files

Q: Why aren't my changes to my .xql file being recognized?

A: This is a very common problem. You have to go to the package manager (/acs-admin/apm), and watch the files for that package. You should only do this on developmental servers. Restarting Aolserver will also reload the .xql files, but any changes you make after the reload will require watching or another restart. This makes watching files from the APM the preferred way to do it.

While you're developing (and only while you're developing), you can put this in your *-init.tcl file in your /tcl directory:

apm_watch_all_files 

foreach package_key $package_key_list {
    apm_watch_all_files $package_key
}

Q: Are we using any naming/style conventions?

A: plsql standards

In PL/PGSQL functions, we're using 'p_' prefixes for arguments to the function and 'v_' prefixes for other local variables. Also, for function calls, we're trying to keep comments that explain the purpose of the arguments. For example:

workflow.add_place (
    workflow_key => 'ttracker_wf',
    place_key    => 'end',
    place_name   => 'Closed',
    sort_order   => 4
);

becomes

select workflow__add_place (
    'ttracker_wf',	-- workflow_key 
    'end',		-- place_key    
    'Closed',		-- place_name   
    4			-- sort_order   
);

Q: How do I port dynamic queries?

A: Well you should have read this in the WP. Plus here's a bboard post. And another.

Q: How do I get around Postgres 7.2's limit of 16 parameters for a function?

A: Bummer, huh? Fortunately, this limit has been removed in Postgres 7.3. Look throughout the code to see how the function is called. Are some of the parameters always the same? Then go ahead and take those parameters out and set them explicitly in the function's body. If that's still not enough, then you'll have to overload the function with different sets of parameters. See packages/acs-content-repository/sql/postgresql/content-item.sql to see how Dan ported content_item__new.

Q: How should I name my sequences so they're consistent between Oracle and PG?

A: See this post.

Q: How do I call PL/PGSQL functions?

A: Check out this post

Q: Can I see a function's source code while in psql?

A: Yup, this has the advantage of showing you line numbers, which are sometimes spit up by PG error messages:
openacs=# select * from acs_func_defs where fname='acs_object__new'

Q: I've looked at some ported packages and I've noted that ported queries are in the .xql files, but are also still in the .tcl files. Is that right?

A: Yup, at least for now. At some point we may take the queries out and perhaps replace them with some other information, such as which variables get set with that particular query. As long as the proper queries are in the .xql file, the query dispatcher will ignore the query in the .tcl file.

Q: How does the permissions system work?

A: See this forum post on permissions

You can also look in the documents

Q: I just want to see how work is progressing.

A: You can browse the CVS.

Q: Where are all the programmers?

A: Why on IRC of course. We're on #openacs on the OpenProjectsNet. Feel free to drop by, whether or not you're a programmer! More information on OpenACS IRC, including logs.

Q: Can you change the background color of an ad_form?

A: http://openacs.org/forums/message-view?message_id=128589

Q: How do you use the spellchecker with ad_form?

A: Starting in 5.0, spellchecking is an option in ad_form. See this posting for details.

Q: How do I port connect by statements from Oracle to Postgres?

A: Read this document

Q: How do I drop a function in Postgres?

A: You must provide all the parameters of the function in the drop statement.

openacs4=# drop function foo (integer,varchar,text);

Q: How do I port outer joins from Oracle to Postgres?

A: Check out these bboard posts: Post 1 Post 2 Post 3

Q: Does 'order by' work the same in PG and Oracle?

A: Not quite. Ordering rows by an aliased column works in both PG and Oracle, but if you want to apply a function to that aliased column before ordering, then it breaks in PG.

select name as foo from bar order by foo; -- works

select name as foo from bar order by lower(foo); -- doesn't work (ERROR: attribute foo not found)

So, you'll have to run the function on the actual column name:

select name as foo from bar order by lower(name);

Q: Can you cache or memoize a db_multirow?

A: Yes and no.

See these postings:

http://openacs.org/forums/message-view?message_id=81266

http://openacs.org/forums/message-view?message_id=33959

http://openacs.org/forums/message-view?message_id=77562

Q: How do I test my packages?

A: There are two mechanisms, neither of which is well documented (please document it!).

1. http://tclwebtest.sourceforge.net/ - to test web pages
2. acs-automated-testing - to test Tcl procedures

Q: What's the prefered way of quoting strings in Tcl?

A: http://openacs.org/forums/message-view?message_id=130991

Q: How would I do a zip code proximity search?

A: See http://openacs.org/forums/message-view?message_id=131695

Q: What is noquoting and what do I need to do to make my packages work for OpenACS 5.0?

A: http://jagor.srce.hr/~hniksic/no-quote-upgrade.html

Q: Where can I find documentation for notifications?

A: They're not part of the core documentation yet, although they will be soon: http://openacs.org/bugtracker/openacs/com/notifications/bug?bug%5fnumber=573

Here's where they are now: http://openacs.org/forums/message-view?message_id=108283

Q: How can I get rid of a user?

A: There is no UI for it right now, but see this: forum post on deleting users

Q: What's the best way of associating custom information with a user?

A: See extending the users table or a separate table?

Q: Is there an easy way to do XML-RPC in OpenACS?

A: http://openacs.org/forums/message-view?message_id=134323

Q: How do I connect to Paypal from OpenACS?

A: One example: http://openacs.org/forums/message-view?message_id=134307

Q: How do I deal with timezones in my code?

A: See this thread on timezones and this thread too

Q: How do I test credit card transactions?

A: http://openacs.org/forums/message-view?message_id=135948

Q: Can I make secure offline credit card billing?

A: http://openacs.org/forums/message-view?message_id=158674

Q: How do I internationalize my package?

A: Well, this is part of the answer to that question.

Q: Is there a way I can type Tcl commands directly to the server?

A: There are three different ways to do this:

1. In OpenACS 5.0 and above, you can install the developer support package, which is probably a good idea anyway (it makes development much easier). There is a /ds/shell.tcl page that lets you type in Tcl to the server directly.
2. A second method (for pre-5.0 systems) is detailed here: Setting up a Tcl shell page
3. A third method is to use the nscp module in Aolserver. Here's how this works (courtesy of jim on IRC):

   The basic deal is you would enable nscp. You'd do that in the config file, because nscp is in the aolserver source of late. A couple of there: you specify the port, username and password (as a crypt hash). You should be able to say "only let this port be accessed from localhost" and you probably should do that. Once you have that part, you can do telnet localhost <thatport> put in the username and password and you're in.

   At that point, you can type commands and whathaveyou. While this would be a repetition, people they can and probably should use emacs shell mode, and do the telnet from the emacs shell. emacs as a kind of client like that makes a lot of different things very convenient. Among other things, it means you can save your session (but warning, if you exit emacs, your session log silently disappears). You can do cut/copy/paste. Other than that, the telnet session in and of itself is very primitive, cmdline-edit-wise.

Q: What is a .vuh file and why would I want to use one?

A: See an explanation of .vuh files

Q: What is the preferred way of displaying dates (and how do you handle international variations in displaying dates)?

A: forum thread on how to display dates

Q: What's the best date to get dates?

A: getting dates from database or Tcl.

Q: Are PDF versions of the documentation available?

A: Yes! http://openacs.org/forums/message-view?message_id=145227

Q: How do I export to Excel?

A: http://openacs.org/forums/message-view?message_id=146238

Q: How do I refer to a query in another .xql file?

A: You want to use a query in www/foo.xql from www/foo-edit.xql. What is the syntax for fully qualified query names?

dbqd.module_name.www.foo.query_name

Q: Is there an easy way to make site-wide print pages?

A: Yep, easy squeezy. Dave explains how to use CSS to get site-wide print pages.

Q: Is there a way to make sure emails aren't sent out from my developmental server?

A: Yes, there is. If you look in your /etc/config.tcl file, there is a section called acs-rollout-support. There are several options there that define how to override what happens when email is sent out. Very useful!

Q: Can I use PHP with OpenACS?

A: There are ways you can call PHP code. You can also use the Gatekeeper package to control permissions to PHP or other software packages. There are a lot of options with this.

Q: How do I write a query that only returns rows that the user has permission to see?

A:

You need to join on the acs_object_party_privilege_map. An example for the forums package:

select * from forums_messages where forum_id=:forum_id
and exists (select 1
                   from acs_object_party_privilege_map m
                   where m.object_id = forums_messages.message_id
                     and m.party_id = :viewing_user_id
                     and m.privilege = 'read')

Q: Can I connect to an external database?

A: See http://openacs.org/forums/message-view?message_id=160743 and http://openacs.org/forums/message-view?message_id=212780

Q: How do I add those cool navbars?

A: Navbars are the cool, tablike things found in a couple of packages. See this thread for details.

Q: Is there a way I can do this in an .adp: <multiple name=name>@@name.html@@</multiple>?

A: http://openacs.org/forums/message-view?message_id=196310

Q: What are those funny #foobar# messages in my .adp files?

A: They are internationalized keys. They allow you to have multi-lingual chunks of text inserted. See: http://yoursite.com/doc/i18n-introduction.html and http://yoursite.com/acs-lang/admin/

Q: How do I get SVG files working on Aolserver and OpenACS?

A: SVG files on Aolserver.

Q: How do I avoid errors when calling Tcl procs with variables that start with a "-"

A: forum thread

Q: How do I develop for both Oracle and Postgres at the same time?

A: Developing for Oracle and Postgres at the same time

Q: How do I move files stored in the database to the filesystem?

A: http://openacs.org/forums/message-view?message_id=213900

Q: Is there a way to pass multirows to and from procedures?

A: Passing multirows in ad_proc

Q: Why does package require packagename work in tclsh, but not from AOLserver.

A: Chances are you have two copies of Tcl installed. Compare the path of the tcl that AOLserver was compiled against to "which tclsh". If you compiled Tcl from source, it is probably under /usr/local/lib/tcl8.x where x is the minor version number. The default search path for Tcl packages will be under that. If you installed the Tcl package you want to use under /usr/lib/tcl8.x you can try to symlink the package directory under /usr/local/lib/tcl8.x.

Q: AOLserver is having trouble connecting to the database, but psql works fine.

A: Did you install more than one version of PostgreSQL? Make sure that nspostgres.so is compiled against the same PostgreSQL install that you are trying to connect to. If you need to run both versions, rename nspostgres8.0.so or nspostgres7.4.so so you can keep them straight and refer to the correct one in the config.tcl file.

Q: How do I add general comments to my ETP driven pages

A: Taken from http://www.cognovis.de/entwickler/tips/etp/

To add general comments, you need to edit the template where you want to add the general comments to.

In the /packages/edit-this-page/templates/mytemplate.tcl file, set the gc_link like this:

set gc_link [etp::get_gc_link -item_id $pa(item_id) -return_url [ad_conn url]]

<id @gc_link@ no nil>@gc_link;noquote@</if>