Home
The Toolkit for Online Communities
17216 Community Members, 0 members online, 2057 visitors today
Log In Register
OpenACS Home : Forums : OpenACS Development : Problems with loading the calendar module

Forum OpenACS Development: Problems with loading the calendar module

Icon of envelope Request notifications

Collapse
Posted by Charles Mok on
Hi, this is regarding the calendar module that I'm porting at the moment. I was trying to execute the calendar through my server and found the following error:

Database operation "select" failed (exception NSDB, "Query was not a
statement returning rows.")
        while executing
    "ns_pg_bind select nsdb0 {
          

        select   unique(calendar_id) as calendar_id,
                 calendar_name,
                 ' ' as checked_p
        from ..."
        ("uplevel" body line 1)

I searched through all my xql files with "unique(calendar_id)" and found that it is present in a 2 xql files:

  • cal-options-oracle.xql
  • cal-options-postgresql.xql
These xql files, when generated using the QueryExtractor, contains NO "FIXME" comments. Does anyone know the cause of this error? Thank you for your help.

Collapse
Posted by Gilbert Wong on
I don't think postgresql has a UNIQUE statement.  You have to change that to DISTINCT.
Collapse
Posted by Don Baccus on
This is a case that we didn't think of when writing the QE, though I guess that's only partly true since it did know to put it into separate .xql files (of course, something else may've triggered that, I can't tell without seeing the entire query).

The fix is easy - "select distinct(calendar_id) as calendar_id".  This
is the proper SQL92 form and works fine in PG.

My e-mail never seems to go through to you guys, did you get my message that some of the .xql files referenced in the .info file, for the "admin" directory, never got added to the CVS file?  Hate to have this conversation in public but your e-mail server seems to be very unreliable.

Collapse
Posted by Charles Mok on
Don,

Sorry for all the troubles.
We have just uploaded the files onto CVS. Please have a look and see if they are ok, please let me know.

Thank you.

Collapse
Posted by Don Baccus on
No problem, Charles (and Lilian) ... CVS is very strange at times.  I've only posted in this public thread because your e-mail server bounces mail and my e-mail delivery agent says it's been down forever and forever (my e-mail to Rafael goes through, though).

Otherwise I wouldn't post here ...

But thanks for the updates, I know you guys are doing good work and I'm eager to try out your Calendar port.

Collapse
Posted by Charles Mok on
I have replaced the "unique" with "distinct" in the postresql.xql (cal-options-postgresql.xql)file. But I still got this error
Database operation "select" failed (exception NSDB, "Query was not a statement returning rows.")
        while executing
    "ns_pg_bind select nsdb0 {
          

        select   distinct(calendar_id) as calendar_id,
                 calendar_name,
                 ' ' as checked_p
        fro..."
        ("uplevel" body line 1)
        invoked from within
    "uplevel $ulevel [list ns_pg_bind $type $db $sql"
        invoked from within
    "db_exec select $db $full_statement_name $sql"
        invoked from within
    "set selection [db_exec select $db $full_statement_name $sql]"
        ("uplevel" body line 2)
        invoked from within
    "uplevel 1 $code_block "
        invoked from within
    "db_with_handle db {
            set selection [db_exec select $db $full_statement_name $sql]

            set counter 0
            while { [db_getrow $db $selection] } {
                if { [e..."
        (procedure "db_multirow" line 28)
        invoked from within
    "db_multirow calendars get_readable_calendars {

        select   unique(calendar_id) as calendar_id,
                 calendar_name,
                 ' ' as check..." --this query is from the tcl file. Am I suppose to change unique to distinct?
        ("uplevel" body line 78)
        invoked from within
    "uplevel {
              # /packages/calendar/www/cal-nav.tcl

......
This is the actual query in the xql file
    select   distinct(calendar_id) as calendar_id,
             calendar_name,
             ' ' as checked_p
    from     calendars
    where    acs_permission__permission_p(calendar_id, :user_id, 'calendar_read$    and      acs_permission__permission_p(calendar_id, :user_id, 'calendar_show$    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, :user_id, 'cal_item_read'$    and     calendar__private_p(on_which_calendar) = 'f'

Collapse
Posted by Jonathan Marsden on
Charles,

I just tried installing calendar (fresh copy from CVS).  It fails to install.  This makes testing your issue with the query much more difficult, so you might want to ensure that what you commit can at least be installed, before further chasing down the issue with the unique/distinct query :-)

I see the error :

couldn't open "/var/lib/aolserver/servers/openacs4/packages/calendar/www/admin/cal-item-permissions-postgresql.xql": no such file or directory

when I try to install calendar.  So your .info file is out of sync with the set of files you have committed to CVS.  You may just need to regenerate the .info file and commit that changed file, or you may need to check that you haven't forgotten to add/commit one or more other files.

I see the file packages/calendar/www/admin/cal-item-permissions.tcl is there, so I think perhaps you forgot to cvs add and/or cvs commit the corresponding .xql file(s).  There are *no* .xql files under www/admin at all, so maybe you need them for some of the other .tcl files there as well?

If you can commit the missing file(s), so the package will install, then I'll give calendar a tryout...

Collapse
Posted by Don Baccus on
Yes, this is exactly the problem I've been trying to communicate via their broken e-mail server and posts here - you've forgotten to add a bunch of files in the admin subdirectory.

You need to do a "cvs add *.xql" in that directory followed by a commit.  We can then help you out.

As far as "unique" goes, no, you do not need to change the query in the .tcl file.  The query dispatcher has replaced it with the proper PostgreSQL query, which - as can be seen from the log file you've posted - contains "distinct()".  As to why it is failing now, as Jonathan says it will be a lot easier to help you after you get all your files checked in properly  so we can install the package and take a look.

Collapse
Posted by Jonathan Marsden on
Charles,

Thanks for the commit of the missing files.  Your package now installs, and can be mounted successfully.

Two minor things so far:

(1) calendar requires the acs-datetime package... but the calendar.info file does not specify this.  Could you add this requirement, so people don't have to track down why calendar is calling the non-existent proc dt_sysdate (which is what happens if you install calendar without acs-datetime!)?

(2) All other packages I have seen use a 'Name' that has an initial Capital.  But calendar thinks that its Name is 'calendar', rather than 'Calendar'.  That puts it at the end of the list of apps to mount, which confused me for a while -- I thought it was missing from the list, because it wasn't between Bboard and Clickthrough.  COuld you alter the name of the package to 'Calendar'?

Once I install acs-datetime as well as calendar, I then see your 'unique' select failure myself, and I'll try to post a solution for that in a short while... stay tuned!

Collapse
Posted by Charles Mok on
Thanks Jonathan.
Sorry for all the troubles. It is the first time I use CVS
Collapse
Posted by Charles Mok on
I have updated the calendar.info file to show it require acs-datetime and also change "calendar" to "Calendar"
Collapse
Posted by Jonathan Marsden on
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 :-)

Collapse
Posted by Charles Mok on
Hi,
The unique and distinct matter had been fixed in the newer version.

For the "cal_item table" problem, the line
"/i cal-item-create.sql" was accidently commented out in the
previous version, which had been fixed in the latest version.

From the errors you posted above, we are unsure if they are caused by the application not being able to find the cal_item table and hence raised such problems, or those errors are caused by some other problems from somewhere else. Would you mind letting us know?

Collapse
Posted by Jonathan Marsden on
> From the errors you posted above, we are unsure if they are caused
> by the application not being able to find the cal_item table
> and hence raised such problems, or those errors are caused by some
> other problems from somewhere else. Would you mind letting us know?

Sure, I'll retest. Give me another half an hour or so to get to it. But surely you folks can do a test install also, and try out the same thing I did... does calendar now work fine for you on your systems??

OpenACS4 reinstall in progress... :-)

Collapse
Posted by Jonathan Marsden on
Alright: the error I reported earlier in the log extract remains even when cal_items really does get created. So I dug a bit further, basically just playing with the offending query in psql.

The fix is easy, but I don't know why it is required. Advice from a real PG guru suggested :-)

The issue is with PG not being able to identify the type of the constant string in the ' ' as checked_p part of the select!

If you change that to

  select distinct(calendar_id) as calendar_id, calendar_name,
  ' '::varchar as checked_p
  from ...

then the query concerned works fine.

Could someone please exlain why PG is unable to figure out that ' ' is a string?? Anyway, at least you now have a suggested fix!

This fix moves the error I see to one from www/cal-dayview.tcl, where it looks like you have some "sql in a string" stuff going on that needs a careful looking over. I'm going to call a halt here for today, and let you folks debug that one :-)

Collapse
Posted by Tilmann Singer on
calendar.info is currently missing the dependency entry on acs-events
Collapse
Posted by Don Baccus on
But in does have the dependency on acs-datetime which in turn has a dependency on acs-events, doesn't it????
Collapse
Posted by Tilmann Singer on
Ooops, that's right - the dependence is there indirectly via acs-datetime.

Just before I wanted to post my true and humble apologies for overlooking this fact I stumbled across this in acs-datetime.info: "Note: this version of the package requires acs-event(s) for a helper function. This requirement may go away in the future." Phew! That saves me ;-)

Althoug "in the future" generally translates to "never" when written in software documentation, it might still be a good idea to put the dependency directly into calendar.info because there are tables of acs-events that are directly referenced by calendar tables.

Collapse
Posted by Don Baccus on
Sure, we can do that ... I'll try to remember to do so soon.  BTW thanks for the testing and bug fixes you've e-mailed me, and for your attention to detail.  Being nit-picky's a good thing when it comes to software...