List:Eventum General Discussion« Previous MessageNext Message »
From:Joao Prado Maia Date:April 16 2004 5:48pm
Subject:RE: How do you configure Issue Reminders?
View as plain text  
Todd,

Sorry for taking so long to reply to your email. I marked it as read and
didn't notice that I never replied to you.

</snip>

> 'DB Error: syntax error'
>

This bug was fixed and pushed to the bitkeeper repo.


> so I don't know if this is the problem or merely a coincidence (as I think
> I was deleting the existing reminder and starting over).
>
> Any thoughts?
>
> Does this functionality work in the 1.1 Dev build? or am I doing something
> wrong again?

While the database error was a definite problem, the reminder system
wouldn't work unless you run a cron job to check for triggered issues.
Here's what we use in our production environment:

*/10 * * * * cd path-to-eventum/misc; /usr/bin/php check_reminders.php

That will execute the check_reminders.php script every 10 minutes, and it
will take care of sending any reminders, if appropriate.

Be warned that we changed the database schema for this feature since your
last email, so you will need to update the schema with the attached SQL
snipplet.

Please let me know if you still have any problems.

--Joao


DROP TABLE IF EXISTS eventum_reminder_level;
CREATE TABLE eventum_reminder_level (
  rem_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  rem_created_date DATETIME NOT NULL,
  rem_rank TINYINT(1) NOT NULL,
  rem_last_updated_date DATETIME NULL,
  rem_title VARCHAR(64) NOT NULL,
  rem_prj_id INT(11) UNSIGNED NOT NULL,
  PRIMARY KEY(rem_id)
);

DROP TABLE IF EXISTS eventum_reminder_priority;
CREATE TABLE eventum_reminder_priority (
  rep_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  rep_rem_id INT(11) UNSIGNED NOT NULL,
  rep_pri_id INT(11) UNSIGNED NOT NULL,
  PRIMARY KEY(rep_id)
);

DROP TABLE IF EXISTS eventum_reminder_requirement;
CREATE TABLE eventum_reminder_requirement (
  rer_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  rer_rem_id INT(11) UNSIGNED NOT NULL,
  rer_iss_id INT(11) UNSIGNED NULL,
  rer_trigger_all_issues TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY(rer_id)
);

DROP TABLE IF EXISTS eventum_reminder_history;
CREATE TABLE eventum_reminder_history (
  rmh_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  rmh_iss_id INT(11) NOT NULL,
  rmh_rma_id INT(11) NOT NULL,
  rmh_created_date DATETIME NOT NULL,
  PRIMARY KEY(rmh_id)
);

DROP TABLE IF EXISTS eventum_reminder_action;
CREATE TABLE eventum_reminder_action (
  rma_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  rma_rem_id INT(11) UNSIGNED NOT NULL,
  rma_rmt_id TINYINT(3) UNSIGNED NOT NULL,
  rma_created_date DATETIME NOT NULL,
  rma_last_updated_date DATETIME NULL,
  rma_title VARCHAR(64) NOT NULL,
  rma_rank TINYINT(2) UNSIGNED NOT NULL,
  PRIMARY KEY(rma_id)
);

DROP TABLE IF EXISTS eventum_reminder_action_list;
CREATE TABLE eventum_reminder_action_list (
  ral_rma_id INT(11) UNSIGNED NOT NULL,
  ral_email VARCHAR(255) NOT NULL,
  ral_usr_id INT(11) UNSIGNED NOT NULL
);

DROP TABLE IF EXISTS eventum_reminder_action_type;
CREATE TABLE eventum_reminder_action_type (
  rmt_id TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  rmt_type VARCHAR(32) NOT NULL,
  rmt_title VARCHAR(64) NOT NULL,
  PRIMARY KEY(rmt_id),
  UNIQUE INDEX rmt_type (rmt_type),
  UNIQUE INDEX rmt_title (rmt_title)
);
INSERT INTO eventum_reminder_action_type (rmt_type, rmt_title) VALUES ('email_assignee',
'Send Email Alert to Assignee');
INSERT INTO eventum_reminder_action_type (rmt_type, rmt_title) VALUES ('sms_assignee',
'Send SMS Alert to Assignee');
INSERT INTO eventum_reminder_action_type (rmt_type, rmt_title) VALUES ('email_list',
'Send Email Alert To...');
INSERT INTO eventum_reminder_action_type (rmt_type, rmt_title) VALUES ('sms_list', 'Send
SMS Alert To...');

DROP TABLE IF EXISTS eventum_reminder_level_condition;
CREATE TABLE eventum_reminder_level_condition (
  rlc_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  rlc_rma_id INT(11) UNSIGNED NOT NULL,
  rlc_rmf_id TINYINT(3) UNSIGNED NOT NULL,
  rlc_rmo_id TINYINT(1) UNSIGNED NOT NULL,
  rlc_created_date DATETIME NOT NULL,
  rlc_last_updated_date DATETIME NULL,
  rlc_value VARCHAR(64) NOT NULL,
  PRIMARY KEY(rlc_id)
);

DROP TABLE IF EXISTS eventum_reminder_field;
CREATE TABLE eventum_reminder_field (
  rmf_id TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  rmf_title VARCHAR(128) NOT NULL,
  rmf_sql_field VARCHAR(32) NOT NULL,
  rmf_sql_representation VARCHAR(255) NOT NULL,
  PRIMARY KEY(rmf_id),
  UNIQUE INDEX rmf_title(rmf_title)
);
INSERT INTO eventum_reminder_field (rmf_title, rmf_sql_field, rmf_sql_representation)
VALUES ('Status', 'iss_sta_id', 'iss_sta_id');
INSERT INTO eventum_reminder_field (rmf_title, rmf_sql_field, rmf_sql_representation)
VALUES ('Last Response Date', 'iss_last_response_date', '(UNIX_TIMESTAMP() -
IFNULL(UNIX_TIMESTAMP(iss_last_response_date), 0))');
INSERT INTO eventum_reminder_field (rmf_title, rmf_sql_field, rmf_sql_representation)
VALUES ('Last Update Date', 'iss_updated_date', '(UNIX_TIMESTAMP() -
IFNULL(UNIX_TIMESTAMP(iss_updated_date), 0))');
INSERT INTO eventum_reminder_field (rmf_title, rmf_sql_field, rmf_sql_representation)
VALUES ('Created Date', 'iss_created_date', '(UNIX_TIMESTAMP() -
IFNULL(UNIX_TIMESTAMP(iss_created_date), 0))');
INSERT INTO eventum_reminder_field (rmf_title, rmf_sql_field, rmf_sql_representation)
VALUES ('First Response Date', 'iss_first_response_date', '(UNIX_TIMESTAMP() -
IFNULL(UNIX_TIMESTAMP(iss_first_response_date), 0))');
INSERT INTO eventum_reminder_field (rmf_title, rmf_sql_field, rmf_sql_representation)
VALUES ('Closed Date', 'iss_closed_date', '(UNIX_TIMESTAMP() -
IFNULL(UNIX_TIMESTAMP(iss_closed_date), 0))');
INSERT INTO eventum_reminder_field (rmf_title, rmf_sql_field, rmf_sql_representation)
VALUES ('Category', 'iss_prc_id', 'iss_prc_id');

DROP TABLE IF EXISTS eventum_reminder_operator;
CREATE TABLE eventum_reminder_operator (
  rmo_id TINYINT(1) UNSIGNED NOT NULL AUTO_INCREMENT,
  rmo_title VARCHAR(32) NULL,
  rmo_sql_representation VARCHAR(32) NULL,
  PRIMARY KEY(rmo_id),
  UNIQUE INDEX rmo_title(rmo_title)
);
INSERT INTO eventum_reminder_operator (rmo_title, rmo_sql_representation) VALUES ('equal
to', '=');
INSERT INTO eventum_reminder_operator (rmo_title, rmo_sql_representation) VALUES ('not
equal to', '<>');
INSERT INTO eventum_reminder_operator (rmo_title, rmo_sql_representation) VALUES ('is',
'IS');
INSERT INTO eventum_reminder_operator (rmo_title, rmo_sql_representation) VALUES ('is
not', 'IS NOT');
INSERT INTO eventum_reminder_operator (rmo_title, rmo_sql_representation) VALUES
('greater than', '>');
INSERT INTO eventum_reminder_operator (rmo_title, rmo_sql_representation) VALUES ('less
than', '<');
INSERT INTO eventum_reminder_operator (rmo_title, rmo_sql_representation) VALUES
('greater or equal than', '>=');
INSERT INTO eventum_reminder_operator (rmo_title, rmo_sql_representation) VALUES ('less
or equal than', '<=');

Thread
How do you configure Issue Reminders?Todd.Morgan23 Mar
  • RE: How do you configure Issue Reminders?Joao Prado Maia16 Apr
RE: How do you configure Issue Reminders?Todd.Morgan26 Apr