Forum OpenACS Q&A: Help on cronjob ...
I need some help on the cronjob and a bboard api.
Problem description:
Registered site members decide what forum they want to start on Wed. Mon and Tue they can suggest a new forum topic or vote for the suggested ones by the other members. Tue night, system automatically chooses 5 top voted forums and starts them.
My solution:
I thought I could implement it as follows -
Combine the notes and survey package somehow to get the forum topics and votes. Use cronjob to select top 5 topics and create the new bboards using bboard api.
I tried to use the cronjob to create a test forum using bboard api, but looks like I am doing something wrong.
Cronjob:
sql section: empty
tcl section:
db_transaction {
bboard_forum_new -forum_id 5000 -short_name "test" \
-charter "test" -moderated_p "f" \
-bboard_id 2828 -creation_user 2604 \
-creation_ip 127.0.0.1 -context_id 2828
}
bboard_id is the package id for forums. user_id is the ID for Admin user. acs_objects table does not have object_id = 5000. These params are hardcoded just for the test purpose.
I created it and ran it using 'Run This CronJob Now'. I thought it would add a "test" forum. But it does not seem to be working.
Could someone please tell me what I am doing wrong?
Also is there a better way to implement it?
One more question: How the sql section is supposed to be used?
Sorry for the long post. I would appreciate your reply.
Thank you,
Kiran
-------------------------------------------------------
debug output from openacs-4-6-error.log -
[19/Jan/2003:11:41:53][23907.2051][-sched-] Notice: Running scheduled proc search_indexer...
[19/Jan/2003:11:41:53][23907.2051][-sched-] Debug: PgBindCmd: sql =
select object_id, event_date, event
from search_observer_queue
order by event_date asc
[19/Jan/2003:11:41:53][23907.2051][-sched-] Notice: Querying '
select object_id, event_date, event
from search_observer_queue
order by event_date asc;'
[19/Jan/2003:11:41:53][23907.2051][-sched-] Notice: dbinit: sql(localhost::openacs-4-6): '
select object_id, event_date, event
from search_observer_queue
order by event_date asc
'
[19/Jan/2003:11:41:53][23907.2051][-sched-] Notice: Done running scheduled proc search_indexer.
[19/Jan/2003:11:41:53][23907.5126][-conn2-] Debug: PgBindCmd: sql =
select node_id
from host_node_map
where host = :host
[19/Jan/2003:11:41:53][23907.5126][-conn2-] Debug: PgBindCmd: bind var: host = localhost
[19/Jan/2003:11:41:53][23907.5126][-conn2-] Debug: PgBindCmd: query with bind variables substituted =
select node_id
from host_node_map
where host = 'localhost'
[19/Jan/2003:11:41:53][23907.5126][-conn2-] Notice: Querying '
select node_id
from host_node_map
where host = 'localhost';'
[19/Jan/2003:11:41:53][23907.5126][-conn2-] Notice: dbinit: sql(localhost::openacs-4-6): '
select node_id
from host_node_map where host = 'localhost'
'
[19/Jan/2003:11:41:53][23907.5126][-conn2-] Debug: RP (39.055 ms): rp_filter: setting up request: GET /cronjob/admin/cronjob-run-now cronjob_id=4229
[19/Jan/2003:11:41:53][23907.5126][-conn2-] Debug: Security: Getting token_id 181, value 8FBCEDF3A200F596C8CAE51F449075ACC73CC9D6
[19/Jan/2003:11:41:53][23907.5126][-conn2-] Debug: Security: Expire_Time is 1042995696 (compare to 1042994513), hash is B7F8A124E6D9C4B559AFC3F2299CB5344B918A98
[19/Jan/2003:11:41:53][23907.5126][-conn2-] Debug: Security: Done calling get_cookie 160101,2604 {181 1042995696 B7F8A124E6D9C4B559AFC3F2299CB5344B918A98} for ad_session_id; received 1042995696 expiration, getting 160101,2604 and 181 1042995696 B7F8A124E6D9C4B559AFC3F2299CB5344B918A98.
[19/Jan/2003:11:41:53][23907.5126][-conn2-] Debug: PgBindCmd: sql =
select 1
where 't' = acs_permission__permission_p(:object_id, :party_id, :privilege)
[19/Jan/2003:11:41:53][23907.5126][-conn2-] Debug: PgBindCmd: bind var: object_id = 4224
[19/Jan/2003:11:41:53][23907.5126][-conn2-] Debug: PgBindCmd: bind var: party_id = 2604
[19/Jan/2003:11:41:53][23907.5126][-conn2-] Debug: PgBindCmd: bind var: privilege = admin
[19/Jan/2003:11:41:53][23907.5126][-conn2-] Debug: PgBindCmd: query with bind variables substituted =
select 1
where 't' = acs_permission__permission_p('4224', '2604', 'admin')
[19/Jan/2003:11:41:53][23907.5126][-conn2-] Notice: Querying '
select 1
where 't' = acs_permission__permission_p('4224', '2604', 'admin');'
[19/Jan/2003:11:41:53][23907.5126][-conn2-] Notice: dbinit: sql(localhost::openacs-4-6): '
select 1
where 't' = acs_permission__permission_p('4224', '2604', 'admin')
'
[19/Jan/2003:11:41:53][23907.5126][-conn2-] Notice: ns_getform using encoding iso8859-1 for charset iso-8859-1[19/Jan/2003:11:41:53][23907.5126][-conn2-] Notice: Scheduling proc cronjob_run
[19/Jan/2003:11:41:53][23907.7176][-conn4-] Debug: PgBindCmd: sql =
select node_id
from host_node_map
where host = :host
[19/Jan/2003:11:41:53][23907.7176][-conn4-] Debug: PgBindCmd: bind var: host = localhost[19/Jan/2003:11:41:53][23907.7176][-conn4-] Debug: PgBindCmd: query with bind variables substituted =
select node_id
from host_node_map
where host = 'localhost'
[19/Jan/2003:11:41:53][23907.7176][-conn4-] Notice: Querying '
select node_id
from host_node_map
where host = 'localhost';'
[19/Jan/2003:11:41:53][23907.7176][-conn4-] Notice: dbinit: sql(localhost::openacs-4-6): '
select node_id
from host_node_map
where host = 'localhost'
'
[19/Jan/2003:11:41:53][23907.7176][-conn4-] Debug: RP (77.309 ms): rp_filter: setting up request: GET /cronjob/admin/cronjobs
[19/Jan/2003:11:41:53][23907.7176][-conn4-] Debug: Security: Getting token_id 181, value 8FBCEDF3A200F596C8CAE51F449075ACC73CC9D6
[19/Jan/2003:11:41:53][23907.7176][-conn4-] Debug: Security: Expire_Time is 1042995696 (compare to 1042994513), hash is B7F8A124E6D9C4B559AFC3F2299CB5344B918A98
[19/Jan/2003:11:41:53][23907.7176][-conn4-] Debug: Security: Done calling get_cookie 160101,2604 {181 1042995696 B7F8A124E6D9C4B559AFC3F2299CB5344B918A98} for ad_session_id; received 1042995696 expiration, getting 160101,2604 and 181 1042995696 B7F8A124E6D9C4B559AFC3F2299CB5344B918A98.
[19/Jan/2003:11:41:53][23907.7176][-conn4-] Debug: PgBindCmd: sql =
select 1
where 't' = acs_permission__permission_p(:object_id, :party_id, :privilege)
[19/Jan/2003:11:41:53][23907.7176][-conn4-] Debug: PgBindCmd: bind var: object_id = 4224
[19/Jan/2003:11:41:53][23907.7176][-conn4-] Debug: PgBindCmd: bind var: party_id = 2604
[19/Jan/2003:11:41:53][23907.7176][-conn4-] Debug: PgBindCmd: bind var: privilege = admin
[19/Jan/2003:11:41:53][23907.7176][-conn4-] Debug: PgBindCmd: query with bind variables substituted =
select 1
where 't' = acs_permissio[19/Jan/2003:11:41:53][23907.7176][-conn4-] Debug: PgBindCmd: query with bind variables substituted =
select node_id
from host_node_map
where host = 'localhost'
[19/Jan/2003:11:41:53][23907.7176][-conn4-] Notice: Querying '
select node_id
from host_node_map
where host = 'localhost';'
[19/Jan/2003:11:41:53][23907.7176][-conn4-] Notice: dbinit: sql(localhost::openacs-4-6): '
select node_id
from host_node_map
where host = 'localhost'
'
[19/Jan/2003:11:41:53][23907.7176][-conn4-] Debug: RP (77.309 ms): rp_filter: setting up request: GET /cronjob/admin/cronjobs
[19/Jan/2003:11:41:53][23907.7176][-conn4-] Debug: Security: Getting token_id 181, value 8FBCEDF3A200F596C8CAE51F449075ACC73CC9D6
[19/Jan/2003:11:41:53][23907.7176][-conn4-] Debug: Security: Expire_Time is 1042995696 (compare to 1042994513), hash is B7F8A124E6D9C4B559AFC3F2299CB5344B918A98
[19/Jan/2003:11:41:53][23907.7176][-conn4-] Debug: Security: Done calling get_cookie 160101,2604 {181 1042995696 B7F8A124E6D9C4B559AFC3F2299CB5344B918A98} for ad_session_id; received 1042995696 expiration, getting 160101,2604 and 181 1042995696 B7F8A124E6D9C4B559AFC3F2299CB5344B918A98.
[19/Jan/2003:11:41:53][23907.7176][-conn4-] Debug: PgBindCmd: sql =
select 1
where 't' = acs_permission__permission_p(:object_id, :party_id, :privilege)
[19/Jan/2003:11:41:53][23907.7176][-conn4-] Debug: PgBindCmd: bind var: object_id = 4224
[19/Jan/2003:11:41:53][23907.7176][-conn4-] Debug: PgBindCmd: bind var: party_id = 2604
[19/Jan/2003:11:41:53][23907.7176][-conn4-] Debug: PgBindCmd: bind var: privilege = admin
[19/Jan/2003:11:41:53][23907.7176][-conn4-] Debug: PgBindCmd: query with bind variables substituted =
select 1
where 't' = acs_permission__permission_p('4224', '2604', 'admin')
[19/Jan/2003:11:41:53][23907.7176][-conn4-] Notice: Querying '
n__permission_p('4224', '2604', 'admin')
[19/Jan/2003:11:41:53][23907.7176][-conn4-] Notice: Querying '
order by level asc
[19/Jan/2003:11:41:53][23907.7176][-conn4-] Notice: Querying '
select site_node__url(n2.node_id) as url, n2.object_id,
acs_object__name(n2.object_id) as object_name,
tree_level(n2.tree_sortkey) as level
from (select tree_ancestor_keys(site_node_get_tree_sortkey('4222')) as tree_sortkey) parents,
site_nodes n2
where n2.tree_sortkey = parents.tree_sortkey
order by level asc;'
[19/Jan/2003:11:41:53][23907.7176][-conn4-] Notice: dbinit: sql(localhost::openacs-4-6): '
select site_node__url(n2.node_id) as url, n2.object_id,
acs_object__name(n2.object_id) as object_name,
tree_level(n2.tree_sortkey) as level
from (select tree_ancestor_keys(site_node_get_tree_sortkey('4222')) as tree_sortkey) parents,
site_nodes n2
where n2.tree_sortkey = parents.tree_sortkey
order by level asc
'
[19/Jan/2003:11:41:54][23907.295946][-sched:25-] Notice: Running scheduled proc cronjob_run...
[19/Jan/2003:11:41:54][23907.295946][-sched:25-] Notice: Cronjob_id is 4229
[19/Jan/2003:11:41:54][23907.295946][-sched:25-] Error: can't read "sql": no such variable
can't read "sql": no such variable
while executing
"db_1row crontab_query $sql"
(procedure "cronjob_run" line 4)
invoked from within
"cronjob_run 4229"
("eval" body line 1)
invoked from within
"eval [concat [list $proc] $args]"
(procedure "ad_run_scheduled_proc" line 43)
invoked from within
"ad_run_scheduled_proc {t t 1 cronjob_run 4229 1042994513 0 t}"
Well some folks updated my cronjob package, but made a small error. The sql was removed from the tcl/cronjob-procs.tcl file and put into an xql file, but the $sql variable was left in place, explaining the error.
I fixed it and committed the changes to cvs. I also fixed another bug that didn't allow the day-of-week to display correctly on the cronjob page.
I'm not exactly sure how you can checkout just the changes, but if you used cvs to get the original version, just do a cvs update in the cronjob package directory should work.
Otherwise I can try to package it and mail it to you.
Your other question was: what does the sql part do. The sql you put in that box will be executed. The result will be formatted as a table with the column headings set to the select column names. If you include an email address, the result html table will be emailed to that address. This is great for running reports at a given time and emailing it to your local manager type. Then the tcl script will be run. Leaving out the sql part has no effect on the tcl script part. If you wanted an email from what the tcl script did, you would have to include a call to ns_sendmail, or something similar, in the tcl script.
-Kiran
P.S. Few observations:
1. Seems like drop script does not drop everything necessary to clean up the package. I had to do followings -
a) delete from acs_objects where object_type ='cronjob';
b) delete from acs_object_types where object_type = 'cronjob';
2. It would nice to filter out the ^M characters from the TCL section text. My script failed because of the ^M characters.
