mailmanager/sql addCategory.zsql,1.1,1.2 addCategoryChoice.zsql,1.1,1.2 addTemplate.zsql,1.1,1.2 cre

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 454-5900

Update of /cvsroot/mailmanager/mailmanager/sql
In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv12876/sql

Modified Files:
	addAttachment.zsql addMessage.zsql addTicket.zsql 
	deleteTickets.zsql editTicket.zsql listCategories.zsql 
	listMessages.zsql listTickets.zsql 
Added Files:
	addCategory.zsql addCategoryChoice.zsql addTemplate.zsql 
	createCategoryAndTemplateTables.zsql createRulesetTables.zsql 
	deleteCategory.zsql deleteCategoryChoice.zsql 
	deleteTemplate.zsql editCategory.zsql getHighestTicketId.zsql 
	getRulesetTransitionHooks.zsql getTemplate.zsql 
	getTicketSubstateDetails.zsql getTicketSubstates.zsql 
	listCategoryChoices.zsql listRulesetAttributes.zsql 
	listTemplates.zsql listTicketAttributes.zsql 
	migrate_2_0_1.zsql populateRuleset.zsql 
	setTicketAttribute.zsql 
Log Message:
Merge down from changes made to 2.0.1 and r8 to head. The 2.0.1 branch was
dropped on top of head, and the changes for the reporting engine reapplied.
A basic split off HTML reporting engine now works, partially, and provides
some space to develop/test the replacement engine.


Index: editTicket.zsql
===================================================================
RCS file: /cvsroot/mailmanager/mailmanager/sql/editTicket.zsql,v
retrieving revision 1.5
retrieving revision 1.6
diff -u -d -r1.5 -r1.6

--- editTicket.zsql	11 Aug 2005 16:28:39 -0000	1.5
+++ editTicket.zsql	14 Sep 2005 13:20:44 -0000	1.6
@@ -2,15 +2,31 @@
 title:Edit a Ticket
 connection_id: mailmanager_db
 max_rows:0
-arguments:id subject assigned status priority category0 category1 category2 support_of set_date_closed clear_date_closed
+arguments:id subject assigned status priority category0 category1 category2 support_of set_date_closed clear_date_closed date_opened respond_by date_responded date_closed
 
 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
-assignment
+assignment. The assignment to date_opened, date_closed or date_responded should 
+only be used for testing purposes. This value should not normally be modified. The 
+same applies to respond_by, which is simply the cached result of date_opened + the 
+corresponding account's respond by interval.
+
 </dtml-comment>
 
 UPDATE <dtml-var schema>mm_ticket
 SET id = id
+    <dtml-if date_responded>
+      , date_responded=<dtml-sqlvar date_responded type="string">
+    </dtml-if>
+    <dtml-if date_closed>
+      , date_closed=<dtml-sqlvar date_closed type="string">
+    </dtml-if>
+    <dtml-if date_opened>
+      , date_opened=<dtml-sqlvar date_opened type="string">
+    </dtml-if>
+    <dtml-if respond_by>
+      , respond_by=<dtml-sqlvar respond_by type="string">
+    </dtml-if>
     <dtml-if set_date_closed>
       , date_closed=CURRENT_TIMESTAMP
     </dtml-if>

--- NEW FILE: createRulesetTables.zsql ---
<dtml-comment>
title:Populate Ruleset
connection_id: mailmanager_db
max_rows:0
arguments:

This method creates all of the tables required for using the new ruleset engine
in MailManager 2.1.

Only currently tested on Postgres

--    ALTER TABLE <dtml-var schema>mm_ticket DROP COLUMN priority;

</dtml-comment>


  CREATE TABLE <dtml-var schema>mm_ruleset_states (
    name TEXT PRIMARY KEY,
    description TEXT
  );


<dtml-comment>
--  
-- Equiv of categories?
--
-- Arbitrary categories
-- 
-- This should be moved out of mm_ruleset in order to replace the current
-- category system in the main MailManager code.
-- Note that this table should list all possible attributes (even dynamic
-- ones). It is used internally, and is not used to present the end user
-- with a list of attributes to apply.
--
</dtml-comment>

  CREATE TABLE <dtml-var schema>mm_ruleset_attributes (
    attribute TEXT PRIMARY KEY
  );

  CREATE TABLE <dtml-var schema>mm_ticket_attributes (
    ticket_id INT REFERENCES <dtml-var schema>mm_ticket,
    attribute TEXT REFERENCES <dtml-var schema>mm_ruleset_attributes
  );

<dtml-comment>
-- 
-- A substate is a grouping of a state with a number of attributes
--
-- Should use primary keys to ensure conditions are unique
--
</dtml-comment>
 
  CREATE TABLE <dtml-var schema>mm_ruleset_substates (
    id SERIAL PRIMARY KEY,
    state TEXT,
      FOREIGN KEY (state) REFERENCES <dtml-var schema>mm_ruleset_states
  );
    
  CREATE TABLE <dtml-var schema>mm_ruleset_substate_conditions (
    substate INT,
      FOREIGN KEY (substate) REFERENCES <dtml-var schema>mm_ruleset_substates,
    attribute TEXT,
      FOREIGN KEY (attribute) REFERENCES <dtml-var schema>mm_ruleset_attributes,
    setting BOOL
  );
  

<dtml-comment>
--
-- Transitions
--
-- Transitions define what substates (states + attributes) lead to other
-- substates (states + attributes). Constraints should ensure that each
-- initial_substate is unique
--
</dtml-comment>

CREATE TABLE <dtml-var schema>mm_ruleset_transitions (
    initial_substate INT UNIQUE,
      FOREIGN KEY (initial_substate) REFERENCES <dtml-var schema>mm_ruleset_substates,
    final_state TEXT,
      FOREIGN KEY (final_state) REFERENCES <dtml-var schema>mm_ruleset_states
);

<dtml-comment>
--
-- Transition hooks
--
-- Should these be by states, or by states and conditions?
--
</dtml-comment>

  CREATE TABLE <dtml-var schema>mm_ruleset_actions (
    action TEXT PRIMARY KEY
  );

  CREATE TABLE <dtml-var schema>mm_ruleset_transition_hooks (
    initial_state TEXT,
      FOREIGN KEY (initial_state) REFERENCES <dtml-var schema>mm_ruleset_states,
    final_state TEXT,
      FOREIGN KEY (final_state) REFERENCES <dtml-var schema>mm_ruleset_states,
    action_type TEXT,
      FOREIGN KEY (action_type) REFERENCES <dtml-var schema>mm_ruleset_actions,
    action_data TEXT 
  );



<dtml-comment>
-- Future events
</dtml-comment>

  CREATE TABLE <dtml-var schema>mm_ruleset_event_queue (
    triggertime TIMESTAMP
  );

<dtml-comment>
--
--
--      CREATE TABLE ruleset_dfa_states (
--        
--      );
--
--      CREATE TABLE ruleset_dfa_states (
--
--      );
--
--      CREATE TABLE ruleset_transition_hooks (
--        initial_state INT REFERENCES ruleset_states,
--        final_state INT REFERENCES ruleset_states,
--        action_type INT REFERENCES ruleset_actions,
--        action_data TEXT 
--      );
</dtml-comment>


--- NEW FILE: addTemplate.zsql ---
<dtml-comment>
title:Add a reply template
connection_id:mailmanager_db
arguments: name body html
</dtml-comment>
INSERT INTO <dtml-var schema>mm_templates (
    name,
    body,
    html
) VALUES (
    <dtml-sqlvar name type="nb">,
    <dtml-sqlvar body type="nb">,
    <dtml-sqlvar html type="nb">
)

--- NEW FILE: listCategoryChoices.zsql ---
<dtml-comment>
title:List Categories
connection_id:mailmanager_db
arguments:category_id
</dtml-comment>

SELECT choice FROM <dtml-var schema>mm_category_choices 
    WHERE category_id = <dtml-sqlvar category_id type="int">

--- NEW FILE: addCategoryChoice.zsql ---
<dtml-comment>
title:Add a choice to a category
connection_id:mailmanager_db
arguments: category_id choice
</dtml-comment>
INSERT INTO <dtml-var schema>mm_category_choices (category_id, choice)
VALUES (<dtml-sqlvar category_id type="int">,
        <dtml-sqlvar choice type="string">)

--- NEW FILE: createCategoryAndTemplateTables.zsql ---
<dtml-comment>
title:Create Tables (Postgres)
connection_id:mailmanager_db
arguments:
</dtml-comment>

<dtml-comment>

  The following 2 tables are there in order to replace the functionality of
  MailManager 2.0's categories. Eventually these will be restructured somewhat
  in order to allow an arbitrary number of categories. This should involve 
  little more than removing the category id, and replacing the primary key 
  with the category label, although the UI pages will need altered throughout.

  mm_category_choices 

    replaces self.category_choices in MailManager 2.0

  mm_category_labels 

    replaces self.category_labels and self.category_on in MailManager 2.0
    
</dtml-comment>

CREATE TABLE <dtml-var schema>mm_categories (
    id INT PRIMARY KEY
       CHECK (id IN (0,1,2)),
    label TEXT,
    enabled BOOL
) <dtml-var sql_tabletype> <dtml-var sql_delimiter>

CREATE TABLE <dtml-var schema>mm_category_choices (
    category_id INT,
        FOREIGN KEY(category_id) REFERENCES <dtml-var schema>mm_categories(id),
    choice TEXT,
    <dtml-if expr="sql_database == 'postgres'">
      PRIMARY KEY(category_id, choice)
    <dtml-elif expr="sql_database == 'mysql'">
      PRIMARY KEY(category_id, choice(255))
    </dtml-if>
) <dtml-var sql_tabletype> <dtml-var sql_delimiter>


<dtml-comment>

  mm_templates 

    This table stores reply templates, with the primary key being the
    name given to the template. Either one of body or html_body should be
    set to NULL in order to select what type of reply to generate.

    replaces self.reply_templates in MailManager 2.0

</dtml-comment>

CREATE TABLE <dtml-var schema>mm_templates (
    name TEXT,
    body TEXT NOT NULL,
    html BOOL DEFAULT <dtml-var sql_false>,
    <dtml-if expr="sql_database == 'postgres'">
      PRIMARY KEY(name)
    <dtml-elif expr="sql_database == 'mysql'">
      PRIMARY KEY(name(255))
    </dtml-if>
) <dtml-var sql_tabletype> 


--- NEW FILE: setTicketAttribute.zsql ---
<dtml-comment>
title:Set an attribute on a ticket
connection_id: mailmanager_db
max_rows: 0
arguments: ticket_id attribute setting
</dtml-comment>
<dtml-if setting>

    DELETE 
      FROM <dtml-var schema>mm_ticket_attributes
        <dtml-sqlgroup where>
          <dtml-sqltest ticket_id type="int">
        <dtml-and>
          <dtml-sqltest attribute type="nb">
        </dtml-sqlgroup> 
      ;

    INSERT INTO <dtml-var schema>mm_ticket_attributes (
        ticket_id, attribute
      ) VALUES (
        <dtml-sqlvar ticket_id type="int">,
        <dtml-sqlvar attribute type="nb">
      );

<dtml-else>

    DELETE 
      FROM <dtml-var schema>mm_ticket_attributes
        <dtml-sqlgroup where>
          <dtml-sqltest ticket_id type="int">
        <dtml-and>
          <dtml-sqltest attribute type="nb">
        </dtml-sqlgroup>

</dtml-if>

Index: addAttachment.zsql
===================================================================
RCS file: /cvsroot/mailmanager/mailmanager/sql/addAttachment.zsql,v
retrieving revision 1.5
retrieving revision 1.6
diff -u -d -r1.5 -r1.6
--- addAttachment.zsql	16 Aug 2005 15:57:21 -0000	1.5
+++ addAttachment.zsql	14 Sep 2005 13:20:44 -0000	1.6
@@ -9,10 +9,10 @@
 VALUES (<dtml-sqlvar message_id type="int">,
         <dtml-sqlvar title type="string">,
         <dtml-sqlvar content_type type="nb">,
-        <dtml-var is_file>,
+        <dtml-sqlvar is_file type="string">,
         <dtml-if expr="sql_database == 'postgres'">
           <dtml-var body>::bytea,
         <dtml-else>
           <dtml-sqlvar body type="string">,
         </dtml-if>
-        <dtml-var dangerous>)
+        <dtml-sqlvar dangerous type="string">)

Index: addTicket.zsql
===================================================================
RCS file: /cvsroot/mailmanager/mailmanager/sql/addTicket.zsql,v
retrieving revision 1.5
retrieving revision 1.6
diff -u -d -r1.5 -r1.6
--- addTicket.zsql	11 Aug 2005 16:28:39 -0000	1.5
+++ addTicket.zsql	14 Sep 2005 13:20:44 -0000	1.6
@@ -28,7 +28,7 @@
           <dtml-sqlvar respond_by type="string">
         <dtml-elif respond_by_interval>
           <dtml-if expr="sql_database == 'postgres'">
-            CURRENT_TIMESTAMP+<dtml-sqlvar respond_by_interval type="int">::INTERVAL
+            (EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)+<dtml-sqlvar respond_by_interval type="int">)::INTEGER::abstime::<dtml-var sql_datetimestamp>
           <dtml-elif expr="sql_database == 'mysql'">
             FROM_UNIXTIME(UNIX_TIMESTAMP(CURRENT_TIMESTAMP) + <dtml-sqlvar respond_by_interval type="int">)
           </dtml-if>

--- NEW FILE: listTicketAttributes.zsql ---
<dtml-comment>
title:List attriubutes which can be used in the ruleset
connection_id: mailmanager_db
max_rows: 0
arguments: ticket_id
</dtml-comment>
SELECT * 
  FROM <dtml-var schema>mm_ticket_attributes
  WHERE ticket_id = <dtml-sqlvar ticket_id type="int">

--- NEW FILE: listRulesetAttributes.zsql ---
<dtml-comment>
title:List attriubutes which can be used in the ruleset
connection_id: mailmanager_db
max_rows: 0
arguments:
</dtml-comment>
SELECT * 
  FROM <dtml-var schema>mm_ruleset_attributes

--- NEW FILE: populateRuleset.zsql ---
<dtml-comment>
title:Populate Ruleset
connection_id: mailmanager_db
max_rows:0
arguments:

This method populates the ruleset with test data

</dtml-comment>

  INSERT INTO <dtml-var schema>mm_ruleset_attributes ( attribute ) VALUES ( 'prio_0'      );
  INSERT INTO <dtml-var schema>mm_ruleset_attributes ( attribute ) VALUES ( 'prio_1'      );
  INSERT INTO <dtml-var schema>mm_ruleset_attributes ( attribute ) VALUES ( 'prio_2'      );
  INSERT INTO <dtml-var schema>mm_ruleset_attributes ( attribute ) VALUES ( 'prio_3'      );
  INSERT INTO <dtml-var schema>mm_ruleset_attributes ( attribute ) VALUES ( 'prio_4'      );
  INSERT INTO <dtml-var schema>mm_ruleset_attributes ( attribute ) VALUES ( 'spam'        );
  INSERT INTO <dtml-var schema>mm_ruleset_attributes ( attribute ) VALUES ( 'isoverdue'   );

  INSERT INTO <dtml-var schema>mm_ruleset_states (name, description) VALUES ('received', 'Initial state for newly genereated tickets');
  INSERT INTO <dtml-var schema>mm_ruleset_states (name, description) VALUES ('new', 'Incoming tickets which have not yet been viewed');
  INSERT INTO <dtml-var schema>mm_ruleset_states (name, description) VALUES ('open', 'Incoming tickets which have been read');
  INSERT INTO <dtml-var schema>mm_ruleset_states (name, description) VALUES ('overdue', 'Tickets which have passed their respond_by time');
  INSERT INTO <dtml-var schema>mm_ruleset_states (name, description) VALUES ('spam', 'Tickets which have been identified as spam');

  INSERT INTO <dtml-var schema>mm_ruleset_actions (action) VALUES ('AlertOwner');
  INSERT INTO <dtml-var schema>mm_ruleset_actions (action) VALUES ('AlertUser');
  INSERT INTO <dtml-var schema>mm_ruleset_actions (action) VALUES ('AlertCustomer');

<dtml-comment>
  -- Substate 1, received + spam
</dtml-comment>
  INSERT INTO <dtml-var schema>mm_ruleset_substates (id, state) VALUES (1, 'received');
  INSERT INTO <dtml-var schema>mm_ruleset_substate_conditions (substate, attribute, setting) VALUES (1, 'spam', True);

<dtml-comment>
  -- Substate 2, received + no spam
</dtml-comment>
  INSERT INTO <dtml-var schema>mm_ruleset_substates (id, state) VALUES (2, 'received');
  INSERT INTO <dtml-var schema>mm_ruleset_substate_conditions (substate, attribute, setting) VALUES (2, 'spam', False);


<dtml-comment>
  -- Transition from state(new) + attribute(spam) to state(spam)
</dtml-comment>
  INSERT INTO <dtml-var schema>mm_ruleset_transitions (
      initial_substate, final_state
  ) VALUES (
      1, 'spam'
  );

<dtml-comment>
  -- Transition from state(new) + attribute(nospam) to state(open)
</dtml-comment>
  INSERT INTO <dtml-var schema>mm_ruleset_transitions (
      initial_substate, final_state
  ) VALUES (
      2, 'new'
  );


<dtml-comment>
  -- Transition from state(new) + attribute(nospam) to state(new)
</dtml-comment>

  INSERT INTO <dtml-var schema>mm_ruleset_transition_hooks (
      initial_state, final_state, action_type, action_data
    ) VALUES (
      'received',   'new',          'AlertOwner',       'NewTicket'
    );

  INSERT INTO <dtml-var schema>mm_ruleset_transition_hooks (
      initial_state, final_state, action_type, action_data
    ) VALUES (
      'open',       'overdue',      'AlertOwner',       'NewTicket'
    );

  INSERT INTO <dtml-var schema>mm_ruleset_transition_hooks (
      initial_state, final_state, action_type, action_data
    ) VALUES (
      'open',       'overdue',      'AlertUser',        'managerOverdue'
    );

<dtml-comment>
  -- Resolving tickets

  INSERT INTO <dtml-var schema>mm_ruleset_transition_hooks (
      initial_state, final_state, action_type, action_data
    ) VALUES (
      'open',       'resolved',     'AlertCustomer',   'Resolved',
    );

  INSERT INTO <dtml-var schema>mm_ruleset_transition_hooks (
      initial_state, final_state, action_type, action_data
    ) VALUES (
      'overdue',    'resolved',     'AlertCustomer',   'Resolved',
    );

  INSERT INTO <dtml-var schema>mm_ruleset_transition_hooks (
      initial_state, final_state, action_type, action_data
    ) VALUES (
      'responded',  'resolved',     'AlertCustomer',   'Resolved',
    );


  INSERT INTO <dtml-var schema>mm_ruleset_transition_hooks (
      initial_state, final_state, action_type, action_data
    ) VALUES (
      'resolved',   'open',         'AlertOwner',      'Reopened',
    );

</dtml-comment>

--- NEW FILE: getTicketSubstateDetails.zsql ---
<dtml-comment>
title:Get all details for a given substate
connection_id: mailmanager_db
max_rows:0
arguments:substate
</dtml-comment>
SELECT * FROM <dtml-var schema>mm_ruleset_substates 
    LEFT JOIN <dtml-var schema>mm_ruleset_substate_conditions
        ON <dtml-var schema>mm_ruleset_substates.id = <dtml-var schema>mm_ruleset_substate_conditions.substate 
    WHERE <dtml-var schema>mm_ruleset_substates.id = <dtml-sqlvar substate type="nb">;

--- NEW FILE: listTemplates.zsql ---
<dtml-comment>
title:List Templates
connection_id:mailmanager_db
arguments:
</dtml-comment>
SELECT * FROM <dtml-var schema>mm_templates
ORDER BY name

--- NEW FILE: migrate_2_0_1.zsql ---
<dtml-comment>
title:Migration (2.0.1)
connection_id:mailmanager_db
arguments:

This is a migration script from 2.0-r8 to 2.0.1-rc1 which adds in indexes
on some additional columns in order to improve performance when dealing
with referential integrity.
</dtml-comment>

CREATE INDEX mm_ticket_support_of_idx
ON <dtml-var schema>mm_ticket(support_of) <dtml-var sql_delimiter>


--- NEW FILE: getRulesetTransitionHooks.zsql ---
<dtml-comment>
title:Gets transition hooks for a state change
connection_id: mailmanager_db
max_rows: 0
arguments: initial_state final_state
</dtml-comment>
SELECT * FROM <dtml-var schema>mm_ruleset_transition_hooks 
<dtml-sqlgroup where>
  <dtml-sqltest initial_state type="nb" optional>
<dtml-and>
  <dtml-sqltest final_state type="nb" optional>
</dtml-sqlgroup>

Index: addMessage.zsql
===================================================================
RCS file: /cvsroot/mailmanager/mailmanager/sql/addMessage.zsql,v
retrieving revision 1.5
retrieving revision 1.6
diff -u -d -r1.5 -r1.6
--- addMessage.zsql	11 Aug 2005 16:28:39 -0000	1.5
+++ addMessage.zsql	14 Sep 2005 13:20:44 -0000	1.6
@@ -4,6 +4,13 @@
 max_rows:0
 arguments:id ticket_id message_id from_name from_email subject msg_date msg_to cc bcc reply_to raw_headers body html_body
 </dtml-comment>
+
+<dtml-if expr="sql_database == 'mysql'">
+  <dtml-if expr="mysql_max_allowed_packet > 0">
+    SET max_allowed_packet = <dtml-var mysql_max_allowed_packet> <dtml-var sql_delimiter>
+  </dtml-if>
+</dtml-if>
+
 INSERT INTO <dtml-var schema>mm_message (id, ticket_id, message_id, from_name,
                                          from_email, subject, msg_date,
                                          msg_to, cc, bcc, reply_to, raw_headers,
@@ -29,6 +36,7 @@
 
 <dtml-if expr="sql_database == 'mysql'">
 
+
     INSERT INTO mm_message_index ( 
         id, ticket_id, body, html_body 
     ) VALUES ( 

--- NEW FILE: getTemplate.zsql ---
<dtml-comment>
title:Get Template
connection_id:mailmanager_db
arguments:name
</dtml-comment>
SELECT * FROM <dtml-var schema>mm_templates
  WHERE name = <dtml-sqlvar name type="string">

--- NEW FILE: getHighestTicketId.zsql ---
<dtml-comment>
title:Get highest ticket id (PostgeSQL, MySQL)
connection_id: mailmanager_db
max_rows:0
arguments:
This method will return NULL if there are no current tickets
</dtml-comment>

  SELECT MAX(id) AS id FROM <dtml-var schema>mm_ticket


Index: listTickets.zsql
===================================================================
RCS file: /cvsroot/mailmanager/mailmanager/sql/listTickets.zsql,v
retrieving revision 1.8
retrieving revision 1.9
diff -u -d -r1.8 -r1.9
--- listTickets.zsql	17 Aug 2005 14:25:40 -0000	1.8
+++ listTickets.zsql	14 Sep 2005 13:20:44 -0000	1.9
@@ -2,7 +2,7 @@
 title:List tickets
 connection_id: mailmanager_db
 max_rows: 0
-arguments:count sort_on sort_order limit offset unread status account_id assigned from_date to_date priority category0 category1 category2 from_name from_email support_of ticket_id searchText is_overdue
+arguments:count sort_on sort_order limit offset unread status account_id assigned from_date to_date priority category0 category1 category2 from_name from_email support_of ticket_id searchText is_overdue subject
 
   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
@@ -123,23 +123,19 @@
   <dtml-and>
     <dtml-sqltest account_id type="nb" optional>
   <dtml-and>
+    <dtml-sqltest subject type="nb" optional>
+  <dtml-and>
     <dtml-sqltest assigned type="nb" optional>
   <dtml-and>
     <dtml-if from_date>
       <dtml-if expr="sql_database == 'postgres'">
-        date_opened >= <dtml-sqlvar from_date type="nb"> 
+        date_opened > <dtml-sqlvar from_date type="nb"> 
       <dtml-elif expr="sql_database == 'mysql'">
-        date_opened >= (SELECT CAST(<dtml-sqlvar from_date type="nb"> AS DATETIME))
+        date_opened > (SELECT CAST(<dtml-sqlvar from_date type="nb"> AS DATETIME))
       </dtml-if>
     </dtml-if>
   <dtml-and>
-    <dtml-if to_date>
-      <dtml-if expr="sql_database == 'postgres'">
-        date_opened < <dtml-sqlvar to_date type="nb"> 
-      <dtml-elif expr="sql_database == 'mysql'">
-        date_opened < (SELECT CAST(<dtml-sqlvar to_date type="nb"> AS DATETIME))
-      </dtml-if>
-    </dtml-if>
+    <dtml-sqltest to_date type="nb" op="le" column="date_opened" optional>
   <dtml-and>
     <dtml-sqltest priority type="int" optional>
   <dtml-and>
@@ -163,7 +159,7 @@
       <dtml-if expr="sql_database == 'postgres'">
         idxFTI @@ (SELECT to_tsquery('default', <dtml-sqlvar searchText type="nb">))
       <dtml-elif expr="sql_database == 'mysql'">
-        MATCH(body, html_body) AGAINST (<dtml-sqlvar searchText type="nb">)
+        MATCH(body, html_body) AGAINST (<dtml-sqlvar searchText type="nb"> IN BOOLEAN MODE)
       </dtml-if>
     </dtml-if>
   <dtml-and>
@@ -173,6 +169,34 @@
 </dtml-sqlgroup>
 
 
+
+<dtml-if suboptimise>
+  <dtml-if status>
+    <dtml-if expr="not (status=='open' or status=='overdue')">
+
+      <dtml-if sort_on>
+        <dtml-if expr="not sort_on=='status'">
+          )
+        </dtml-if>
+      <dtml-else>
+        )
+      </dtml-if>
+      
+    </dtml-if>
+  <dtml-else>
+
+    <dtml-if sort_on>
+      <dtml-if expr="not sort_on=='status'">
+        )
+      </dtml-if>
+    <dtml-else>
+      )
+    </dtml-if>
+
+  </dtml-if>
+</dtml-if>
+
+
 <dtml-unless count>
     
   <dtml-comment>
@@ -225,30 +249,3 @@
 
 </dtml-unless>
 
-
-<dtml-if suboptimise>
-  <dtml-if status>
-    <dtml-if expr="not (status=='open' or status=='overdue')">
-
-      <dtml-if sort_on>
-        <dtml-if expr="not sort_on=='status'">
-          )
-        </dtml-if>
-      <dtml-else>
-        )
-      </dtml-if>
-      
-    </dtml-if>
-  <dtml-else>
-
-    <dtml-if sort_on>
-      <dtml-if expr="not sort_on=='status'">
-        )
-      </dtml-if>
-    <dtml-else>
-      )
-    </dtml-if>
-
-  </dtml-if>
-</dtml-if>
-

--- NEW FILE: deleteCategory.zsql ---
<dtml-comment>
title:Delete a category
connection_id:mailmanager_db
arguments: id 
</dtml-comment>
DELETE FROM <dtml-var schema>mm_categories 
    WHERE id = <dtml-sqlvar id type="int">

--- NEW FILE: deleteTemplate.zsql ---
<dtml-comment>
title:Delete a template
connection_id:mailmanager_db
arguments: name
</dtml-comment>
DELETE FROM <dtml-var schema>mm_templates
    WHERE name = <dtml-sqlvar name type="string">

--- NEW FILE: getTicketSubstates.zsql ---
<dtml-comment>
title:Get all substates id for a given state
connection_id: mailmanager_db
max_rows:0
arguments:state
</dtml-comment>
SELECT DISTINCT(<dtml-var schema>mm_ruleset_substates.id) 
    FROM <dtml-var schema>mm_ruleset_states 
      LEFT JOIN <dtml-var schema>mm_ruleset_substates 
        ON <dtml-var schema>mm_ruleset_states.name = <dtml-var schema>mm_ruleset_substates.state 
    WHERE <dtml-var schema>mm_ruleset_states.name = <dtml-sqlvar state type="nb">;

Index: listCategories.zsql
===================================================================
RCS file: /cvsroot/mailmanager/mailmanager/sql/listCategories.zsql,v
retrieving revision 1.3
retrieving revision 1.4
diff -u -d -r1.3 -r1.4
--- listCategories.zsql	11 Aug 2005 16:28:39 -0000	1.3
+++ listCategories.zsql	14 Sep 2005 13:20:44 -0000	1.4
@@ -4,16 +4,8 @@
 max_rows:0
 arguments:category_id
 </dtml-comment>
-
-<dtml-if category_id>
-  <dtml-if expr="category_id==0">
-    SELECT DISTINCT(category0) AS catname FROM <dtml-var schema>mm_ticket
-  <dtml-elif expr="category_id==1">
-    SELECT DISTINCT(category1) AS catname FROM <dtml-var schema>mm_ticket
-  <dtml-elif expr="category_id==0">
-    SELECT DISTINCT(category2) AS catname FROM <dtml-var schema>mm_ticket
-  </dtml-if>
-</dtml-if>
-
-SELECT DISTINCT(category0) AS catname FROM <dtml-var schema>mm_ticket
-<dtml-var sql_delimiter>
+SELECT id, label, enabled
+FROM <dtml-var schema>mm_categories
+  <dtml-sqlgroup where>
+    <dtml-sqltest category_id type="int" column="id" optional>
+  </dtml-sqlgroup>

--- NEW FILE: addCategory.zsql ---
<dtml-comment>
title:Add a category
connection_id:mailmanager_db
arguments: id label enabled
</dtml-comment>
INSERT INTO <dtml-var schema>mm_categories (id, label, enabled)
VALUES (<dtml-sqlvar id type="int">,
        <dtml-sqlvar label type="string">,
        <dtml-sqlvar enabled type="nb">)

Index: listMessages.zsql
===================================================================
RCS file: /cvsroot/mailmanager/mailmanager/sql/listMessages.zsql,v
retrieving revision 1.3
retrieving revision 1.4
diff -u -d -r1.3 -r1.4
--- listMessages.zsql	11 Aug 2005 16:28:39 -0000	1.3
+++ listMessages.zsql	14 Sep 2005 13:20:44 -0000	1.4
@@ -4,6 +4,13 @@
 max_rows:0
 arguments:ticket_id message_id
 </dtml-comment>
+
+<dtml-if expr="sql_database == 'mysql'">
+  <dtml-if expr="mysql_max_allowed_packet > 0">
+    SET max_allowed_packet = <dtml-var mysql_max_allowed_packet> <dtml-var sql_delimiter>
+  </dtml-if>
+</dtml-if>
+
 SELECT * FROM <dtml-var schema>mm_message
 <dtml-sqlgroup where>
   <dtml-sqltest ticket_id type="int" optional>

--- NEW FILE: editCategory.zsql ---
<dtml-comment>
title:Edit a category
connection_id:mailmanager_db
arguments: id label enabled
</dtml-comment>
UPDATE <dtml-var schema>mm_categories 
    SET label = <dtml-sqlvar label type="string">,
        enabled = <dtml-sqlvar enabled type="nb">   
    WHERE id = <dtml-sqlvar id type="int">

Index: deleteTickets.zsql
===================================================================
RCS file: /cvsroot/mailmanager/mailmanager/sql/deleteTickets.zsql,v
retrieving revision 1.4
retrieving revision 1.5
diff -u -d -r1.4 -r1.5
--- deleteTickets.zsql	11 Aug 2005 16:28:39 -0000	1.4
+++ deleteTickets.zsql	14 Sep 2005 13:20:44 -0000	1.5
@@ -2,7 +2,10 @@
 title:Delete Tickets
 connection_id: mailmanager_db
 max_rows:0
-arguments:date_opened account_id status category0 category1 category2
+arguments:date_opened account_id status category0 category1 category2 ticket_id
+
+This method also updates the text search cache for MySQL
+
 </dtml-comment>
 
 
@@ -14,6 +17,8 @@
                 date_opened <= (SELECT CAST(<dtml-sqlvar date_opened type="nb"> AS <dtml-var sql_datetimestamp>))
               </dtml-if>
             <dtml-and>
+              <dtml-sqltest ticket_id column="id" type="nb" optional>
+            <dtml-and>
               <dtml-sqltest account_id type="nb" optional>
             <dtml-and>
               <dtml-sqltest status type="nb" multiple optional>
@@ -33,6 +38,8 @@
                 date_opened <= (SELECT CAST(<dtml-sqlvar date_opened type="nb"> AS <dtml-var sql_datetimestamp>))
               </dtml-if>
             <dtml-and>
+              <dtml-sqltest ticket_id column="ticket_id" type="nb" optional>
+            <dtml-and>
               <dtml-sqltest account_id type="nb" optional>
             <dtml-and>
               <dtml-sqltest status type="nb" multiple optional>
@@ -44,7 +51,6 @@
               <dtml-sqltest category2 type="nb" optional>
             </dtml-sqlgroup>
         ) <dtml-var sql_delimiter>
-
 </dtml-if>
 
 DELETE FROM <dtml-var schema>mm_ticket
@@ -53,6 +59,8 @@
     date_opened <= (SELECT CAST(<dtml-sqlvar date_opened type="nb"> AS <dtml-var sql_datetimestamp>))
   </dtml-if>
 <dtml-and>
+  <dtml-sqltest ticket_id column="id" type="nb" optional>
+<dtml-and>
   <dtml-sqltest account_id type="nb" optional>
 <dtml-and>
   <dtml-sqltest status type="nb" multiple optional>

--- NEW FILE: deleteCategoryChoice.zsql ---
<dtml-comment>
title:Delete a category choice
connection_id:mailmanager_db
arguments: category_id choice 
</dtml-comment>
DELETE FROM <dtml-var schema>mm_category_choices
    WHERE category_id = <dtml-sqlvar category_id type="int"> 
        AND choice = <dtml-sqlvar choice type="string">
    
    



