Charles:
OK, the immediate issue now is that:
(1) You committed the old *.xql files for PG, ones that contain 'unique' when they should contain 'distinct'. The affected five files are:
- calendar-permissions.xql
- calendar-preferences-postgresql.xql
- cal-item-permissions-postgresql.xql
- cal-item-permissions.xql
- one-postgresql.xql
Fixing these is trivial, when in the packages/calendar/www/admin dir you can do
perl -p -i -e 's/unique/distinct/' `grep -l unique *.xql |grep -v oracle`
to fix them all in one go. If you are more comfortable with editing them one at a time, that's fine too of course! After fixing those up, I then ran into the real issue you seem to be facing:
(2) Queries use the cal_items table but this table no longer exists
It looks like your datamodel porting puts the calendar items into the CR (NB this was a good design idea) instead of a separate cal_items table which was there in ACS4 Classic. However, from what I can see, you didn't follow through that design change and modify all the queries that use the cal_items table. I don't know if your plan was to replace cal_items with a view on the CR, or to modify the queries... but you'll need to do something with that.
FYI I found this by turning on debug and verbose options in my nsd.tcl and generating the error (ie after creating a subfolder of "Main Site" and mounting calendar there, I browsed to /calendar. Then I looked for errors in the AOLserver log file.
Here is an excerpt:
[05/Sep/2001:12:40:44][15961.4101][-conn0-] Error: Ns_PgExec: result status: 7 message: ERROR: Unable to identify an ordering opera
tor '<' for type 'unknown'
Use an explicit ordering operator or modify the query
[05/Sep/2001:12:40:44][15961.4101][-conn0-] Error: dbinit: error(localhost::openacs4,ERROR: Unable to identify an ordering operator
'<' for type 'unknown'
Use an explicit ordering operator or modify the query
): '
select distinct(calendar_id) as calendar_id,
calendar_name,
' ' as checked_p
from calendars
where acs_permission__permission_p(calendar_id, '0', 'calendar_read') = 't'
and acs_permission__permission_p(calendar_id, '0', 'calendar_show') = 't'
and private_p = 'f'
union
select distinct(on_which_calendar) as calendar_id,
calendar__name(on_which_calendar) as calendar_name,
' ' as checked_p
from cal_items
where acs_permission__permission_p(cal_item_id, '0', 'cal_item_read') = 't'
and calendar__private_p(on_which_calendar) = 'f'
'
notice: RP (739.8 ms): error in rp_handler: serving GET /calendar/
ad_url "/calendar/" maps to file "/var/lib/aolserver/servers/openacs4/packages/calendar/www/index.adp"
errmsg is Database operation "select" failed (exception NSDB, "Query was not a statement returning rows.")
Warning: APM: RestrictErrorsToAdminsP does not exist
Warning: APM: AutomaticErrorReportingP does not exist
Warning: APM: EnabledP does not exist
[05/Sep/2001:12:40:44][15961.4101][-conn0-] Error: called "ns_log" with too many arguments
called "ns_log" with too many arguments
while executing
"ns_log "Error" [ns_conn method] $error_url [ns_conn query] $message"
(procedure "rp_report_error" line 41)
invoked from within
"rp_report_error"
(procedure "rp_handler" line 126)
invoked from within
"rp_handler"
Note that the error you seein the Browser is the generic "statemetn not returning rows", but the log is much more useful. I wish the error show by the browser was the same detailed one returned by the 'dbinit' line in the log, but somehow it is getting converted or masked.
Finally, there seems to me to be another issue here, but one that is not yours. The "called ns_log with too many arguments" error seems to be in the RP (request processor) and should be looked into separately.
I hope this has given you some ideas on how to proceed 😊
