From: Kevin C. <ke...@us...> - 2006-03-28 15:22:11
|
Update of /cvsroot/mailmanager/MailManager/sql/v2_1 In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv25562/sql/v2_1 Modified Files: Tag: RELENG_2_1 addRulesetState.zsql editTicket.zsql finalTables.zsql listTickets.zsql Added Files: Tag: RELENG_2_1 migrateRulesetStates.zsql Log Message: - Issues with New state (#1456222) Removed the New state from the ruleset, converted existing tickets. Hook for setting tickets as read now calls ruleset function correctly, although the Viewed status is still calculated from the mm_ticket table, which should be treated as authoritative at present. Index: listTickets.zsql =================================================================== RCS file: /cvsroot/mailmanager/MailManager/sql/v2_1/listTickets.zsql,v retrieving revision 1.2.2.3 retrieving revision 1.2.2.4 diff -u -d -r1.2.2.3 -r1.2.2.4 --- listTickets.zsql 27 Feb 2006 12:36:03 -0000 1.2.2.3 +++ listTickets.zsql 28 Mar 2006 15:22:05 -0000 1.2.2.4 @@ -2,7 +2,7 @@ title:List tickets connection_id: mailmanager_db max_rows: 0 -arguments:sqv_count sqv_sort_on sqv_sort_order sqv_limit sqv_offset sqv_unread sqv_state sqv_account_id sqv_assigned sqv_from_date sqv_to_date sqv_priority sqv_category0 sqv_category1 sqv_category2 sqv_from_name sqv_from_email sqv_support_of sqv_ticket_id sqv_searchText sqv_is_overdue sqv_subject +arguments:sqv_count sqv_sort_on sqv_sort_order sqv_limit sqv_offset sqv_unread sqv_state sqv_account_id sqv_assigned sqv_from_date sqv_to_date sqv_priority sqv_category0 sqv_category1 sqv_category2 sqv_from_name sqv_from_email sqv_support_of sqv_ticket_id sqv_searchText sqv_is_overdue sqv_subject sqv_rsname This method does quite a lot of varying operations, and should probably at some point be split into separate methods for clarity. Note that the dtml @@ -127,6 +127,8 @@ <dtml-and> <dtml-sqltest sqv_subject column="subject" type="nb" optional> <dtml-and> + <dtml-sqltest sqv_rsname column="rsname" type="nb" optional> + <dtml-and> <dtml-sqltest sqv_assigned column="assigned" type="nb" optional> <dtml-and> <dtml-if sqv_from_date> --- NEW FILE: migrateRulesetStates.zsql --- <dtml-comment> title:Migrate ruleset states (v2_1_1 -> v2_1_2) connection_id:mailmanager_db arguments: This is a migration script from 2_1_1 to 2_1_2 which alters ruleset_states to have a composite key. Previously this key has been only on the ruleset state itself, not combined with the ruleset name, so multiple ruleset could not share state names. </dtml-comment> <dtml-if expr="sql_database == 'mysql'"> ALTER TABLE <dtml-var schema>mm_ruleset_states DROP PRIMARY KEY <dtml-var sql_delimiter> <dtml-else> ALTER TABLE <dtml-var schema>mm_ruleset_states DROP CONSTRAINT mm_ruleset_states_pkey <dtml-var sql_delimiter> </dtml-if> UPDATE <dtml-var schema>mm_ruleset_states SET rsname = 'queuesystem' <dtml-var sql_delimiter> ALTER TABLE <dtml-var schema>mm_ruleset_states ADD PRIMARY KEY (rsname, name) <dtml-var sql_delimiter> Index: editTicket.zsql =================================================================== RCS file: /cvsroot/mailmanager/MailManager/sql/v2_1/editTicket.zsql,v retrieving revision 1.2.2.3 retrieving revision 1.2.2.4 diff -u -d -r1.2.2.3 -r1.2.2.4 --- editTicket.zsql 27 Feb 2006 12:36:03 -0000 1.2.2.3 +++ editTicket.zsql 28 Mar 2006 15:22:05 -0000 1.2.2.4 @@ -2,7 +2,7 @@ title:Edit a Ticket connection_id: mailmanager_db max_rows:0 -arguments:sqv_id sqv_subject sqv_assigned sqv_state sqv_priority sqv_category0 sqv_category1 sqv_category2 sqv_support_of sqv_set_date_closed sqv_clear_date_closed sqv_debug_date_opened sqv_debug_respond_by sqv_debug_date_responded sqv_debug_date_closed sqv_set_support_of sqv_clear_support_of +arguments:sqv_id sqv_subject sqv_assigned sqv_state sqv_priority sqv_category0 sqv_category1 sqv_category2 sqv_support_of sqv_set_date_closed sqv_clear_date_closed sqv_debug_date_opened sqv_debug_respond_by sqv_debug_date_responded sqv_debug_date_closed sqv_set_support_of sqv_clear_support_of sqv_rsname The initial assignment of id = id purely exists so that the commas separating each sql variable can be added in a clean fashion. It is purely a dummy @@ -42,6 +42,9 @@ <dtml-if sqv_state> , state=<dtml-sqlvar sqv_state type="nb"> </dtml-if> + <dtml-if sqv_rsname> + , rsname=<dtml-sqlvar sqv_rsname type="nb"> + </dtml-if> <dtml-if sqv_priority> , priority=<dtml-sqlvar sqv_priority type="int"> </dtml-if> Index: addRulesetState.zsql =================================================================== RCS file: /cvsroot/mailmanager/MailManager/sql/v2_1/addRulesetState.zsql,v retrieving revision 1.1.4.2 retrieving revision 1.1.4.3 diff -u -d -r1.1.4.2 -r1.1.4.3 --- addRulesetState.zsql 27 Feb 2006 12:38:02 -0000 1.1.4.2 +++ addRulesetState.zsql 28 Mar 2006 15:22:05 -0000 1.1.4.3 @@ -1,12 +1,14 @@ <dtml-comment> title:Add a state to a ruleset connection_id:mailmanager_db -arguments:sqv_name sqv_description +arguments:sqv_name sqv_rsname sqv_description </dtml-comment> INSERT INTO <dtml-var schema>mm_ruleset_states ( - name + name, + rsname <dtml-if sqv_description>, description </dtml-if> ) VALUES ( - <dtml-sqlvar sqv_name type="nb"> + <dtml-sqlvar sqv_name type="nb">, + <dtml-sqlvar sqv_rsname type="nb"> <dtml-if sqv_description> , <dtml-sqlvar sqv_description type="nb"> </dtml-if> ) Index: finalTables.zsql =================================================================== RCS file: /cvsroot/mailmanager/MailManager/sql/v2_1/Attic/finalTables.zsql,v retrieving revision 1.1.2.1 retrieving revision 1.1.2.2 diff -u -d -r1.1.2.1 -r1.1.2.2 --- finalTables.zsql 28 Mar 2006 13:39:45 -0000 1.1.2.1 +++ finalTables.zsql 28 Mar 2006 15:22:05 -0000 1.1.2.2 @@ -43,8 +43,9 @@ CREATE TABLE <dtml-var schema>mm_ruleset_states ( rsname VARCHAR(20), FOREIGN KEY (rsname) REFERENCES <dtml-var schema>mm_ruleset(name), - name VARCHAR(64) PRIMARY KEY, - description TEXT + name VARCHAR(64), + description TEXT, + PRIMARY KEY (rsname, name) ) <dtml-var sql_tabletype> <dtml-var sql_charset> <dtml-var sql_delimiter> |