From: <ke...@us...> - 2006-05-25 16:05:09
|
Revision: 3064 Author: kevca Date: 2006-05-25 09:04:50 -0700 (Thu, 25 May 2006) ViewCVS: http://svn.sourceforge.net/mailmanager/?rev=3064&view=rev Log Message: ----------- * SECURITY - Added in fix for potential security issue Postgres encoding security hole (#1494281) The code should not be vulnerable to this issue, but additional bugs in the release may expose the vulnerability. Adding in a patch which will prevent the exploit in all cases. Added Paths: ----------- MailManager/branches/RELENG_2_1/sql/v2_0/account.zsql MailManager/branches/RELENG_2_1/sql/v2_0/attachment.zsql MailManager/branches/RELENG_2_1/sql/v2_0/ticket.zsql MailManager/branches/RELENG_2_1/sql/v2_1/account.zsql MailManager/branches/RELENG_2_1/sql/v2_1/attachment.zsql MailManager/branches/RELENG_2_1/sql/v2_1/ticket.zsql Added: MailManager/branches/RELENG_2_1/sql/v2_0/account.zsql =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_0/account.zsql (rev 0) +++ MailManager/branches/RELENG_2_1/sql/v2_0/account.zsql 2006-05-25 16:04:50 UTC (rev 3064) @@ -0,0 +1,9 @@ +<dtml-comment> +title:Account (w/pluggable brain) +connection_id: mailmanager_db +max_rows:0 +arguments:email +</dtml-comment> +SELECT * +FROM <dtml-var schema>mm_account +WHERE <dtml-sqltest email type=nb> Added: MailManager/branches/RELENG_2_1/sql/v2_0/attachment.zsql =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_0/attachment.zsql (rev 0) +++ MailManager/branches/RELENG_2_1/sql/v2_0/attachment.zsql 2006-05-25 16:04:50 UTC (rev 3064) @@ -0,0 +1,9 @@ +<dtml-comment> +title:Attachment (w/pluggable brain) +connection_id: mailmanager_db +max_rows:0 +arguments:id +</dtml-comment> +SELECT * +FROM <dtml-var schema>mm_attachment +WHERE <dtml-sqltest id type=int> Added: MailManager/branches/RELENG_2_1/sql/v2_0/ticket.zsql =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_0/ticket.zsql (rev 0) +++ MailManager/branches/RELENG_2_1/sql/v2_0/ticket.zsql 2006-05-25 16:04:50 UTC (rev 3064) @@ -0,0 +1,12 @@ +<dtml-comment> +title:Ticket (w/pluggable brain) +connection_id: mailmanager_db +max_rows:0 +arguments:id +</dtml-comment> +SELECT *, CASE WHEN ( + respond_by < CURRENT_TIMESTAMP + AND status='open' + AND date_responded IS NULL) THEN 'overdue' ELSE status END AS full_status +FROM <dtml-var schema>mm_ticket +WHERE <dtml-sqltest id type=int> Added: MailManager/branches/RELENG_2_1/sql/v2_1/account.zsql =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_1/account.zsql (rev 0) +++ MailManager/branches/RELENG_2_1/sql/v2_1/account.zsql 2006-05-25 16:04:50 UTC (rev 3064) @@ -0,0 +1,9 @@ +<dtml-comment> +title:Account (w/pluggable brain) +connection_id: mailmanager_db +max_rows:0 +arguments:email +</dtml-comment> +SELECT * +FROM <dtml-var schema>mm_account +WHERE <dtml-sqltest email type=nb> Added: MailManager/branches/RELENG_2_1/sql/v2_1/attachment.zsql =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_1/attachment.zsql (rev 0) +++ MailManager/branches/RELENG_2_1/sql/v2_1/attachment.zsql 2006-05-25 16:04:50 UTC (rev 3064) @@ -0,0 +1,9 @@ +<dtml-comment> +title:Attachment (w/pluggable brain) +connection_id: mailmanager_db +max_rows:0 +arguments:id +</dtml-comment> +SELECT * +FROM <dtml-var schema>mm_attachment +WHERE <dtml-sqltest id type=int> Added: MailManager/branches/RELENG_2_1/sql/v2_1/ticket.zsql =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_1/ticket.zsql (rev 0) +++ MailManager/branches/RELENG_2_1/sql/v2_1/ticket.zsql 2006-05-25 16:04:50 UTC (rev 3064) @@ -0,0 +1,9 @@ +<dtml-comment> +title:Ticket (w/pluggable brain) +connection_id: mailmanager_db +max_rows:0 +arguments:id +</dtml-comment> +SELECT * +FROM <dtml-var schema>mm_ticket +WHERE <dtml-sqltest id type=int> This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <ke...@us...> - 2006-06-06 02:57:20
|
Revision: 3130 Author: kevca Date: 2006-06-05 09:01:06 -0700 (Mon, 05 Jun 2006) ViewCVS: http://svn.sourceforge.net/mailmanager/?rev=3130&view=rev Log Message: ----------- - Ruleset permissions are too strict (#1499441) Modified Paths: -------------- MailManager/branches/RELENG_2_1/migrations/v2_1/__init__.py Added Paths: ----------- MailManager/branches/RELENG_2_1/ruleset/data/queuesystem_2_1_4.py MailManager/branches/RELENG_2_1/sql/v2_1/modifyTicketRulesets.zsql Modified: MailManager/branches/RELENG_2_1/migrations/v2_1/__init__.py =================================================================== --- MailManager/branches/RELENG_2_1/migrations/v2_1/__init__.py 2006-06-05 15:59:44 UTC (rev 3129) +++ MailManager/branches/RELENG_2_1/migrations/v2_1/__init__.py 2006-06-05 16:01:06 UTC (rev 3130) @@ -158,6 +158,7 @@ the database. Fixes Replied tickets are being marked as overdue (#1497428) + Fixes Ruleset permissions are too strict (#1499441) """ # Add the Responded and Viewed attributes for each ticket @@ -176,6 +177,12 @@ sqv_setting = self.mmobj.sql_true ) + # Update the ruleset to remove the ACL restrictions + self.engine.setup(rsname = 'queuesystem_2_1_4') + self.sql.modifyTicketRulesets( + sqv_new_ruleset = 'queuesystem_2_1_4' + ) + # Update the mversion to show we are complete self.mversion = 'v2_1_5' Added: MailManager/branches/RELENG_2_1/ruleset/data/queuesystem_2_1_4.py =================================================================== --- MailManager/branches/RELENG_2_1/ruleset/data/queuesystem_2_1_4.py (rev 0) +++ MailManager/branches/RELENG_2_1/ruleset/data/queuesystem_2_1_4.py 2006-06-05 16:01:06 UTC (rev 3130) @@ -0,0 +1,422 @@ +# +# Ruleset description for mailmanager2.1 with the queueing support added. +# This ruleset is basically a clone of the ruleset adding a new state +# (Queued), and dealing with some new events +# +# This is a fix from queuesystem 2.1.2, which does the following: +# +# Removes Send (actual method is duplicated as SendReply, which is what +# is used in the MailManager code) +# Adds Overdue state to AddNote (fixes bug #1494173 - Add Note missing +# from overdue tickets) +# + +try: + import ruleset +except ImportError: + from Products.MailManager import ruleset + +events = [] +states = [ + 'Received', + 'Open', + 'Closed', + 'Hold', + 'Spam', + 'Overdue', + 'Queued' +] + +############################################################################# +# +# User Interface Methods +# + +# +# Mass Updates +# These are special case events. At present, we cannot configure or restrict +# them in any way. +# + +event = ruleset.events.MassChangeUIEvent('ChangeSelected') +events.append(event) + +event = ruleset.events.FilterEvent('ApplyFilter') +events.append(event) + +# +# Viewing a ticket +# + +event = ruleset.events.UserInterfaceEvent('ViewTicket') + +for state in states: + event.addTransition( + state = state, + attrs = ['!Viewed'], + actions = [ruleset.actions.SetAttribute('Viewed')] + ) +events.append(event) + +# +# Adding a note +# + +event = ruleset.events.UserInterfaceEvent('AddNote') +event.addTransition( + state = 'Open', +) +event.addTransition( + state = 'Overdue', +) +event.addTransition( + state = 'Closed', +) +events.append(event) + +# +# Sending a reply +# + +event = ruleset.events.UserInterfaceEvent('SendReply') +event.addTransition( + state = 'Open', + attrs = [], + newstate = 'Hold', + actions = [ruleset.actions.SetAttribute('Responded')] +) +event.addTransition( + state = 'Closed', + attrs = [], + newstate = 'Closed', + actions = [ruleset.actions.SetAttribute('Responded')] +) +event.addTransition( + state = 'Overdue', + attrs = [], + newstate = 'Open', + actions = [ruleset.actions.SetAttribute('Responded')] +) +event.addTransition( + state = 'Hold', + attrs = [], + newstate = 'Hold', + actions = [ruleset.actions.SetAttribute('Responded')] +) +events.append(event) + +# +# Send and close +# + +event = ruleset.events.UserInterfaceEvent('SendAndClose') +event.addTransition( + state = 'Open', + attrs = ['!OpenChildren'], + newstate = 'Closed', + actions = [ruleset.actions.SetAttribute('Responded')] +) +event.addTransition( + state = 'Closed', + attrs = ['!OpenChildren'], + newstate = 'Closed', + actions = [ruleset.actions.SetAttribute('Responded')] +) +event.addTransition( + state = 'Overdue', + attrs = ['!OpenChildren'], + newstate = 'Closed', + actions = [ruleset.actions.SetAttribute('Responded')] +) +events.append(event) + +# +# Send and hold +# + +event = ruleset.events.UserInterfaceEvent('SendAndHold') +event.addTransition( + state = 'Open', + attrs = [], + newstate = 'Hold', + actions = [ruleset.actions.SetAttribute('Responded')] +) +event.addTransition( + state = 'Closed', + attrs = [], + newstate = 'Hold', + actions = [ruleset.actions.SetAttribute('Responded')] +) +event.addTransition( + state = 'Overdue', + attrs = [], + newstate = 'Hold', + actions = [ruleset.actions.SetAttribute('Responded')] +) +events.append(event) + +# +# Close +# + +event = ruleset.events.UserInterfaceEvent('Close') +event.addTransition( + state = 'Open', + attrs = ['!OpenChildren'], + newstate = 'Closed', +) +event.addTransition( + state = 'Closed', + attrs = ['!OpenChildren'], + newstate = 'Closed', +) +event.addTransition( + state = 'Hold', + attrs = ['!OpenChildren'], + newstate = 'Closed', +) +events.append(event) + + +# +# Get next ticket from queue +# + +event = ruleset.events.UserInterfaceEvent('GetNextTicketFromQueue') +event.addTransition( + state = 'Queued', + attrs = [], + newstate = 'Open', + actions = [ruleset.actions.AssignUser('_$Current')] +) +events.append(event) + + +# +# Return ticket to queue +# + +event = ruleset.events.UserInterfaceEvent('ReturnToQueue') +event.addTransition( + state = 'Open', + attrs = ['_AssignQueue'], + newstate = 'Queued', + actions = [ruleset.actions.AssignUser('$QueueUser')] +) +event.addTransition( + state = 'Hold', + attrs = ['_AssignQueue'], + newstate = 'Queued', + actions = [ruleset.actions.AssignUser('$QueueUser')] +) +event.addTransition( + state = 'Closed', + attrs = ['_AssignQueue'], + newstate = 'Queued', + actions = [ruleset.actions.AssignUser('$QueueUser')] +) +event.addTransition( + state = 'Overdue', + attrs = ['_AssignQueue'], + newstate = 'Queued', + actions = [ruleset.actions.AssignUser('$QueueUser')] +) +events.append(event) + + +# # +# # Update Ticket Details +# # +# +# event = ruleset.events.UserInterfaceModify('UpdateTicketDetails') +# event.addTransition( +# state = 'Any', +# newstate = 'Any', +# actions = 'Any' +# ) +# +# event = ruleset.events.UserInterfaceModify('UpdateTicketBatch') +# event.addTransition( +# state = 'Any', +# newstate = 'Any', +# actions = 'Any' +# ) +# +# # +# # Delete User And Reassign +# # +# +# event = ruleset.events.UserInterfaceEvent('DeleteUserAndReassign') +# event.addTransition( +# state = 'Any', +# actions = [ReassignTicket()] +# ) +# +# # +# # Restore Tickets +# # + + +############################################################################# +# +# getMail methods +# + +event = ruleset.events.GetMailEvent('AppendToTicket') +event.addTransition( + state = 'Closed', + attrs = ['Responded'], + newstate = 'Open', + actions = [ruleset.actions.AlertOwner('Opened'), ruleset.actions.AlertGroup('Opened')] +) +event.addTransition( + state = 'Closed', + newstate = 'Open', + attrs = ['!Responded'], + actions = [ruleset.actions.AlertOwner('Opened'), ruleset.actions.AlertGroup('Opened')] +) +event.addTransition( + state = 'Open', + newstate = 'Open', +) +event.addTransition( + state = 'Spam', + newstate = 'Spam', +) +event.addTransition( + state = 'Overdue', + newstate = 'Overdue', +) +events.append(event) + + + +############################################################################# +# +# Time based methods +# + +event = ruleset.events.TimeEvent('Overdue') +event.addTransition( + state = 'Open', + attrs = ['!Responded'], + newstate = 'Overdue', + actions = [ruleset.actions.AlertOwner('Overdue'), ruleset.actions.AlertGroup('Overdue')] +) +events.append(event) + + +event = ruleset.events.TimeEvent('AutoLogout') +event.addTransition( + state = 'Open', + attrs = ['_AssignQueue'], + newstate = 'Queued', + actions = [ruleset.actions.AssignUser('$QueueUser')] +) +event.addTransition( + state = 'Hold', + attrs = ['_AssignQueue'], + newstate = 'Queued', + actions = [ruleset.actions.AssignUser('$QueueUser')] +) +event.addTransition( + state = 'Closed', + attrs = ['_AssignQueue'], + newstate = 'Queued', + actions = [ruleset.actions.AssignUser('$QueueUser')] +) +event.addTransition( + state = 'Overdue', + attrs = ['_AssignQueue'], + newstate = 'Queued', + actions = [ruleset.actions.AssignUser('$QueueUser')] +) +events.append(event) + + +############################################################################# +# +# Epsilon Transitions +# + +event = ruleset.events.EpsilonEvent('Epsilon') + +# Spam goes direct to the spam state +event.addTransition( + state = 'Received', + attrs = ['Spam'], + newstate = 'Spam', + actions = [] +) + +# Queue any tickets which are assigned to a queue +event.addTransition( + state = 'Received', + attrs = ['_AssignQueue'], + newstate = 'Queued', + actions = [ruleset.actions.AssignUser('$QueueUser')] +) + +# Tickets assigned to a user or a group should go straight to the Open state +# Don't reassign the ticket to any user, as the AccountPluggableBrain will +# currently do that from the assignment engine (this will be replaced at +# a later stage, merging the assignment engine with the queueing rules) +event.addTransition( + state = 'Received', + attrs = ['!_AssignQueue'], + newstate = 'Open', +) +events.append(event) + +# +# +############################################################################# + + +ruleset = { + 'eventnames' : [ + 'Epsilon', # Internal + 'Overdue', # Overdue Event + 'ViewTicket', # User Interface + 'Send', # User Interface + 'Close', # User Interface + 'AddNote', # User Interface + 'SendReply', # User Interface + 'SendAndClose', # User Interface + 'SendAndHold', # User Interface + 'UpdateTicketDetails', # User Interface + 'UpdateTicketBatch', # User Interface + 'DeleteUserAndReassign', # User Interface + 'RestoreTickets', # User Interface + 'ReturnToQueue', # User Interface + 'GetNextTicketFromQueue', # User Interface + 'AppendToTicket', # Get Mail + 'AutoLogout', # Timed Event + ], + + 'actions' : [ + 'AlertOwner', + 'AlertGroup', + 'AlertSender', + 'SetAttribute', + 'UnsetAttribute', + 'ReassignTicket', + ], + + 'states' : states, + + 'attributes' : [ + 'OpenChildren', + 'Responded', + 'Viewed', + 'Spam' + ], + + 'categories' : [ + 'sales', + 'support' + ], + + 'events' : events +} + Copied: MailManager/branches/RELENG_2_1/sql/v2_1/modifyTicketRulesets.zsql (from rev 3128, MailManager/branches/RELENG_2_1/sql/v2_1/modifyTicketRulesets.py) =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_1/modifyTicketRulesets.zsql (rev 0) +++ MailManager/branches/RELENG_2_1/sql/v2_1/modifyTicketRulesets.zsql 2006-06-05 16:01:06 UTC (rev 3130) @@ -0,0 +1,13 @@ +<dtml-comment> +title:Batch update the ruleset on tickets +connection_id: mailmanager_db +max_rows:0 +arguments:sqv_old_ruleset sqv_new_ruleset +</dtml-comment> + +UPDATE <dtml-var schema>mm_ticket SET + rsname = <dtml-sqlvar sqv_new_ruleset type="nb"> + <dtml-sqlgroup where> + <dtml-sqltest sqv_old_ruleset column="rsname" type="nb" optional> + </dtml-sqlgroup> + This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <av...@us...> - 2006-06-30 18:41:24
|
Revision: 3278 Author: aveitch Date: 2006-06-30 11:41:16 -0700 (Fri, 30 Jun 2006) ViewCVS: http://svn.sourceforge.net/mailmanager/?rev=3278&view=rev Log Message: ----------- State Overdue now handled correctly in various reports. (#1515271) Modified Paths: -------------- MailManager/branches/RELENG_2_1/sql/v2_1/accountStatus.zsql MailManager/branches/RELENG_2_1/sql/v2_1/homePageStats.zsql MailManager/branches/RELENG_2_1/sql/v2_1/userStatus.zsql Modified: MailManager/branches/RELENG_2_1/sql/v2_1/accountStatus.zsql =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_1/accountStatus.zsql 2006-06-30 15:08:26 UTC (rev 3277) +++ MailManager/branches/RELENG_2_1/sql/v2_1/accountStatus.zsql 2006-06-30 18:41:16 UTC (rev 3278) @@ -43,13 +43,9 @@ (SELECT COUNT(1) FROM <dtml-var schema>mm_ticket <dtml-sqlgroup where> - state='Open' + state='Overdue' <dtml-and> account_id=email - <dtml-and> - respond_by < <dtml-var sql_now> - <dtml-and> - date_responded IS NULL </dtml-sqlgroup>) AS overdue FROM <dtml-var schema>mm_account, <dtml-var schema>mm_ticket Modified: MailManager/branches/RELENG_2_1/sql/v2_1/homePageStats.zsql =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_1/homePageStats.zsql 2006-06-30 15:08:26 UTC (rev 3277) +++ MailManager/branches/RELENG_2_1/sql/v2_1/homePageStats.zsql 2006-06-30 18:41:16 UTC (rev 3278) @@ -32,16 +32,12 @@ (SELECT COUNT(1) FROM <dtml-var schema>mm_ticket <dtml-sqlgroup where> - state='Open' + state='Overdue' <dtml-and> account_id=email <dtml-and> <dtml-sqltest sqv_assigned column="assigned" type="nb" optional> <dtml-and> - respond_by < <dtml-var sql_now> - <dtml-and> - date_responded IS NULL - <dtml-and> <dtml-sqltest sqv_from_email_addrs multiple=True type="nb" column="from_email" optional> </dtml-sqlgroup>) AS overdue Modified: MailManager/branches/RELENG_2_1/sql/v2_1/userStatus.zsql =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_1/userStatus.zsql 2006-06-30 15:08:26 UTC (rev 3277) +++ MailManager/branches/RELENG_2_1/sql/v2_1/userStatus.zsql 2006-06-30 18:41:16 UTC (rev 3278) @@ -19,8 +19,7 @@ (SELECT COUNT(1) FROM <dtml-var schema>mm_ticket WHERE unread=<dtml-var sql_true> AND mm_ticket.assigned=username) AS new, (SELECT COUNT(1) FROM <dtml-var schema>mm_ticket - WHERE respond_by < <dtml-var sql_now> AND state='Open' - AND date_responded IS NULL AND mm_ticket.assigned=username) AS overdue + WHERE state='Overdue' AND mm_ticket.assigned=username) AS overdue FROM <dtml-var schema>mm_user, <dtml-var schema>mm_ticket WHERE mm_ticket.assigned=mm_user.username GROUP BY mm_user.username This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <ke...@us...> - 2006-07-21 13:05:59
|
Revision: 3304 Author: kevca Date: 2006-07-21 06:05:37 -0700 (Fri, 21 Jul 2006) ViewCVS: http://svn.sourceforge.net/mailmanager/?rev=3304&view=rev Log Message: ----------- Continued fix for Cannot set a filter to a group/user of certain length (#1526419) Added Paths: ----------- MailManager/branches/RELENG_2_1/sql/v2_1/migrateFilterGroupNameSizes.zsql MailManager/branches/RELENG_2_1/testdata/filtertests/tickets/000005.py Added: MailManager/branches/RELENG_2_1/sql/v2_1/migrateFilterGroupNameSizes.zsql =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_1/migrateFilterGroupNameSizes.zsql (rev 0) +++ MailManager/branches/RELENG_2_1/sql/v2_1/migrateFilterGroupNameSizes.zsql 2006-07-21 13:05:37 UTC (rev 3304) @@ -0,0 +1,62 @@ +<dtml-comment> +title:Migrate Filter Assign Name sizes (2_1_4 - 2_1_5) +connection_id:mailmanager_db +arguments: + +This migration sets the user name limit to 64 characters, and fixes +an issue with the queues table not being appropriately sized for +referring to a group + +Only postgresql and mysql are supported at present + +Postgres 7.4 doesn't support alter column :( + +</dtml-comment> + + +<dtml-if expr="sql_database == 'postgres'"> + + -- Filter table + ALTER TABLE <dtml-var schema>mm_filter + RENAME COLUMN assign_user TO assign_user_old <dtml-var sql_delimiter> + ALTER TABLE <dtml-var schema>mm_filter + ADD COLUMN assign_user <dtml-var sql_varchar>(64) <dtml-var sql_delimiter> + UPDATE <dtml-var schema>mm_filter + SET assign_user = assign_user_old <dtml-var sql_delimiter> + ALTER TABLE <dtml-var schema>mm_filter + DROP COLUMN assign_user_old <dtml-var sql_delimiter> + + ALTER TABLE <dtml-var schema>mm_filter + ADD CONSTRAINT mm_filter_assign_user_fkey FOREIGN KEY (assign_user) REFERENCES <dtml-var schema>mm_user(username); + <dtml-var sql_delimiter> + + ALTER TABLE <dtml-var schema>mm_filter + RENAME COLUMN assign_group TO assign_group_old <dtml-var sql_delimiter> + ALTER TABLE <dtml-var schema>mm_filter + ADD COLUMN assign_group <dtml-var sql_varchar>(128) <dtml-var sql_delimiter> + UPDATE <dtml-var schema>mm_filter + SET assign_group = assign_group_old <dtml-var sql_delimiter> + ALTER TABLE <dtml-var schema>mm_filter + DROP COLUMN assign_group_old <dtml-var sql_delimiter> + + ALTER TABLE <dtml-var schema>mm_filter + ADD CONSTRAINT mm_filter_assign_group_fkey FOREIGN KEY (assign_group) REFERENCES <dtml-var schema>mm_group(group_name); + <dtml-var sql_delimiter> + +<dtml-elif expr="sql_database == 'mysql'"> + + ALTER TABLE <dtml-var schema>mm_filter + MODIFY COLUMN assign_user <dtml-var sql_varchar>(64) <dtml-var sql_delimiter> + ALTER TABLE <dtml-var schema>mm_filter + MODIFY COLUMN assign_group <dtml-var sql_varchar>(128) <dtml-var sql_delimiter> + +<dtml-else> + <dtml-raise NotImplementedError>migrateFilterAssignNameSizes.zsql is only implemented for PostgreSQL and MySQL at present</dtml-raise> +</dtml-if> + +<dtml-comment> + + Modifications for the queue table referencing groups + +</dtml-comment> + Added: MailManager/branches/RELENG_2_1/testdata/filtertests/tickets/000005.py =================================================================== --- MailManager/branches/RELENG_2_1/testdata/filtertests/tickets/000005.py (rev 0) +++ MailManager/branches/RELENG_2_1/testdata/filtertests/tickets/000005.py 2006-07-21 13:05:37 UTC (rev 3304) @@ -0,0 +1,19 @@ +{ 'dateoffset' : -1.00, + 'rawmessage' : """ +From kev@chana.logicalprogression.example Mon, 13 Jun 2005 15:45:45 +0100 +Return-Path: <kev@chana.logicalprogression.example> +X-Original-To: sales@acmewidgets.example +Delivered-To: acme-sales@chana.logicalprogression.example +Received: by chana.logicalprogression.net (Postfix, from userid 503) + id 5DD8050075; Mon, 13 Jun 2005 15:45:45 +0100 (BST) +To: sales@acmewidgets.example +Subject: Filter Message 5 +Message-Id: <00005@example> +Date: Mon, 13 Jun 2005 15:45:45 +0100 (BST) +X-Spam-Flag: yes +From: kev@chana.logicalprogression.example + +test message 5 + +""" +} This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <av...@us...> - 2006-07-25 08:58:24
|
Revision: 3312 Author: aveitch Date: 2006-07-25 01:58:13 -0700 (Tue, 25 Jul 2006) ViewCVS: http://svn.sourceforge.net/mailmanager/?rev=3312&view=rev Log Message: ----------- Added in migrations for categories and date_opened lack indexes (#1527686) and tickets incorrectly set as overdue (#1516996) Modified Paths: -------------- MailManager/branches/RELENG_2_1/migrations/v2_1/__init__.py Added Paths: ----------- MailManager/branches/RELENG_2_1/sql/v2_1/migrateAddDateOpenedIndex.zsql MailManager/branches/RELENG_2_1/sql/v2_1/migrateAddTicketCategoryIndexes.zsql Modified: MailManager/branches/RELENG_2_1/migrations/v2_1/__init__.py =================================================================== --- MailManager/branches/RELENG_2_1/migrations/v2_1/__init__.py 2006-07-24 19:38:22 UTC (rev 3311) +++ MailManager/branches/RELENG_2_1/migrations/v2_1/__init__.py 2006-07-25 08:58:13 UTC (rev 3312) @@ -11,6 +11,7 @@ import logging from Products.MailManager.support.logger import log +from DateTime.DateTime import DateTime def migrate_v2_1_0_v2_1_1(self, migrationParams): @@ -311,7 +312,31 @@ # Update the mversion to show we are complete self.mversion = 'v2_1_9' +def migrate_v2_1_9_v2_1_10(self, migrationParams): + """ Fixes incorrect overdue tickets and add missing indexes + Tickets incorrectly set as Overdue (#1516996) + Categories and date_opened lack indexes (#1527686) + """ + + # Sort out the overdue tickets + for tick in self.sql.listTickets(sqv_state='Overdue'): + ticket = self.ticket(id=tick.id) + respond_by = ticked.calculateOverdueTime() + if respond_by < mx.DateTime.utc(): + # Ticket should not be Overdue + # xxx change ticket from Overdue to Open + self.engine.queueTimeEvent(eventname='Overdue', + ticket=ticket, + time=DateTime(respond_by.ticks(), 'UTC')) + + # Add the missing indexes + self.sql.migrateAddDateOpenedIndex() + self.sql.migrateAddTicketCategoryIndexes() + + # Update the mversion to show we are complete + self.mversion = 'v2_1_10' + def migrate_convert_postgres_unicode(self, migrationParams): """ Convert postgres database to using unicode """ @@ -467,6 +492,12 @@ 'overview' : 'Fixes Cannot control getMail frequency effectively (#1521234)', 'method' : migrate_v2_1_8_v2_1_9, }, + 'v2_1_9' : { + 'targetstate' : 'v2_1_10', + 'overview': 'Fixed Tickets incorrectly set as Overdue (#1516996)\n' + \ + 'Categories and date_opened lack indexes (#1527686)', + 'method' : migrate_v2_1_9_v2_1_10, + }, } # Add on mangling for unicode conversion for postgres users Added: MailManager/branches/RELENG_2_1/sql/v2_1/migrateAddDateOpenedIndex.zsql =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_1/migrateAddDateOpenedIndex.zsql (rev 0) +++ MailManager/branches/RELENG_2_1/sql/v2_1/migrateAddDateOpenedIndex.zsql 2006-07-25 08:58:13 UTC (rev 3312) @@ -0,0 +1,12 @@ +<dtml-comment> +title:Migrate add date_opened index (v2_1_9 - v2_1_10) +connection_id:mailmanager_db +arguments: + +Create index on ticket date opened + +</dtml-comment> + +CREATE INDEX mm_ticket_date_opened_idx ON +<dtml-var schema>mm_ticket(date_opened); + Added: MailManager/branches/RELENG_2_1/sql/v2_1/migrateAddTicketCategoryIndexes.zsql =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_1/migrateAddTicketCategoryIndexes.zsql (rev 0) +++ MailManager/branches/RELENG_2_1/sql/v2_1/migrateAddTicketCategoryIndexes.zsql 2006-07-25 08:58:13 UTC (rev 3312) @@ -0,0 +1,18 @@ +<dtml-comment> +title:Migrate add category indexes (v2_1_9 - v2_1_10) +connection_id:mailmanager_db +arguments: + +Create indexes on ticket categories + +</dtml-comment> + +CREATE INDEX mm_ticket_category0_idx ON +<dtml-var schema>mm_ticket(category0); + +CREATE INDEX mm_ticket_category1_idx ON +<dtml-var schema>mm_ticket(category1); + +CREATE INDEX mm_ticket_category2_idx ON +<dtml-var schema>mm_ticket(category2); + This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <ke...@us...> - 2006-07-28 12:30:47
|
Revision: 3335 Author: kevca Date: 2006-07-28 05:30:23 -0700 (Fri, 28 Jul 2006) ViewCVS: http://svn.sourceforge.net/mailmanager/?rev=3335&view=rev Log Message: ----------- Now stores a revision history Modified Paths: -------------- MailManager/branches/RELENG_2_1/migrations/__init__.py MailManager/branches/RELENG_2_1/migrations/v2_1/__init__.py Added Paths: ----------- MailManager/branches/RELENG_2_1/sql/v2_1/addVersionHistory.zsql MailManager/branches/RELENG_2_1/sql/v2_1/getVersionHistory.zsql MailManager/branches/RELENG_2_1/sql/v2_1/migrateCreateVersionHistory.zsql Modified: MailManager/branches/RELENG_2_1/migrations/__init__.py =================================================================== --- MailManager/branches/RELENG_2_1/migrations/__init__.py 2006-07-28 12:17:13 UTC (rev 3334) +++ MailManager/branches/RELENG_2_1/migrations/__init__.py 2006-07-28 12:30:23 UTC (rev 3335) @@ -59,4 +59,12 @@ migrationParams['targetstate'] = migrations[self.mversion]['targetstate'] migrations[self.mversion]['method'](self, migrationParams) + if migrations[self.mversion].get('logupgrade', False): + # Log the upgrade + self.sql.addVersionHistory( + sqv_mversion = self.mversion, + sqv_version = self.version + ) + + Modified: MailManager/branches/RELENG_2_1/migrations/v2_1/__init__.py =================================================================== --- MailManager/branches/RELENG_2_1/migrations/v2_1/__init__.py 2006-07-28 12:17:13 UTC (rev 3334) +++ MailManager/branches/RELENG_2_1/migrations/v2_1/__init__.py 2006-07-28 12:30:23 UTC (rev 3335) @@ -358,10 +358,17 @@ logging.INFO, 'migration') self.rebuildReportCache() + # Update the mversion to show we are complete + self.mversion = 'v2_1_10' +def migrate_v2_1_10_v2_1_11(self, migrationParams): + """ Add in a revision history table + """ + self.sql.migrateAddVersionHistory() + # Update the mversion to show we are complete - self.mversion = 'v2_1_10' + self.mversion = 'v2_1_11' def migrate_convert_postgres_unicode(self, migrationParams): """ Convert postgres database to using unicode """ @@ -526,6 +533,12 @@ 'Fixes Close times missing from performance report (#1529307)', 'method' : migrate_v2_1_9_v2_1_10, }, + 'v2_1_10' : { + 'targetstate' : 'v2_1_11', + 'overview': 'Adds in a revision history table\n', + 'method' : migrate_v2_1_10_v2_1_11, + 'logupgrade' : True, + }, } # Add on mangling for unicode conversion for postgres users Added: MailManager/branches/RELENG_2_1/sql/v2_1/addVersionHistory.zsql =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_1/addVersionHistory.zsql (rev 0) +++ MailManager/branches/RELENG_2_1/sql/v2_1/addVersionHistory.zsql 2006-07-28 12:30:23 UTC (rev 3335) @@ -0,0 +1,18 @@ +<dtml-comment> +title:Add revision history +connection_id:mailmanager_db +arguments:sqv_mversion sqv_version_string + +</dtml-comment> + +INSERT INTO <dtml-var schema>mm_revision_history ( + mversion, + version_string, + change_date +) VALUES ( + sqv_mversion, + sqv_version_string, + <dtml-var sql_now> +) + + Added: MailManager/branches/RELENG_2_1/sql/v2_1/getVersionHistory.zsql =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_1/getVersionHistory.zsql (rev 0) +++ MailManager/branches/RELENG_2_1/sql/v2_1/getVersionHistory.zsql 2006-07-28 12:30:23 UTC (rev 3335) @@ -0,0 +1,9 @@ +<dtml-comment> +title:Get revision history +connection_id:mailmanager_db +arguments: + + +</dtml-comment> + +SELECT * FROM <dtml-var schema>mm_revision_history Added: MailManager/branches/RELENG_2_1/sql/v2_1/migrateCreateVersionHistory.zsql =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_1/migrateCreateVersionHistory.zsql (rev 0) +++ MailManager/branches/RELENG_2_1/sql/v2_1/migrateCreateVersionHistory.zsql 2006-07-28 12:30:23 UTC (rev 3335) @@ -0,0 +1,16 @@ +<dtml-comment> +title:Create revision history (v2_1_10 - v2_1_11) +connection_id:mailmanager_db +arguments: + +Creates a table to log all migration history. This will be highly useful +in retrospectively fixing datasets. + +</dtml-comment> + +CREATE TABLE <dtml-var schema>mm_revision_history ( + mversion <dtml-var sql_smalltext>, + version_string <dtml-var sql_smalltext>, + change_date <dtml-var sql_datetimestamp> +) <dtml-var sql_tabletype> <dtml-var sql_charset> + This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <ke...@us...> - 2006-08-04 13:03:39
|
Revision: 3391 Author: kevca Date: 2006-08-04 06:03:32 -0700 (Fri, 04 Aug 2006) ViewCVS: http://svn.sourceforge.net/mailmanager/?rev=3391&view=rev Log Message: ----------- Adding in FOR UPDATE options to improve Concurrency issues (#1534512) Modified Paths: -------------- MailManager/branches/RELENG_2_1/sql/v2_1/listGroupMembers.zsql MailManager/branches/RELENG_2_1/sql/v2_1/listGroups.zsql Modified: MailManager/branches/RELENG_2_1/sql/v2_1/listGroupMembers.zsql =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_1/listGroupMembers.zsql 2006-08-04 13:00:46 UTC (rev 3390) +++ MailManager/branches/RELENG_2_1/sql/v2_1/listGroupMembers.zsql 2006-08-04 13:03:32 UTC (rev 3391) @@ -2,7 +2,7 @@ title:List group members connection_id: mailmanager_db max_rows:0 -arguments:sqv_group_name sqv_username +arguments:sqv_group_name sqv_username sqv_update </dtml-comment> SELECT * FROM <dtml-var schema>mm_group_members <dtml-sqlgroup where> @@ -11,3 +11,6 @@ <dtml-sqltest sqv_username column="username" type="nb" optional> </dtml-sqlgroup> ORDER BY username +<dtml-if sqv_update> + FOR UPDATE +</dtml-if> Modified: MailManager/branches/RELENG_2_1/sql/v2_1/listGroups.zsql =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_1/listGroups.zsql 2006-08-04 13:00:46 UTC (rev 3390) +++ MailManager/branches/RELENG_2_1/sql/v2_1/listGroups.zsql 2006-08-04 13:03:32 UTC (rev 3391) @@ -2,10 +2,13 @@ title:List groups connection_id: mailmanager_db max_rows:0 -arguments:sqv_group_name +arguments:sqv_group_name sqv_update </dtml-comment> SELECT * FROM <dtml-var schema>mm_group <dtml-sqlgroup where> <dtml-sqltest sqv_group_name column="group_name" type="nb" optional> </dtml-sqlgroup> ORDER BY group_name +<dtml-if sqv_update> + FOR UPDATE +</dtml-if> This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <ke...@us...> - 2006-08-08 09:43:19
|
Revision: 3413 Author: kevca Date: 2006-08-08 02:43:07 -0700 (Tue, 08 Aug 2006) ViewCVS: http://svn.sourceforge.net/mailmanager/?rev=3413&view=rev Log Message: ----------- Fixes for Queued tickets don't go overdue (#1535519) Modified Paths: -------------- MailManager/branches/RELENG_2_1/Extensions/TicketPluggableBrain.py MailManager/branches/RELENG_2_1/sql/v2_1/deleteRulesetTimeEvents.zsql Modified: MailManager/branches/RELENG_2_1/Extensions/TicketPluggableBrain.py =================================================================== --- MailManager/branches/RELENG_2_1/Extensions/TicketPluggableBrain.py 2006-08-08 09:41:19 UTC (rev 3412) +++ MailManager/branches/RELENG_2_1/Extensions/TicketPluggableBrain.py 2006-08-08 09:43:07 UTC (rev 3413) @@ -1982,8 +1982,22 @@ return eventTime else: return None + + security.declarePrivate('calculateOverdueTime') + def isOverdue(self): + """ Submethod to check if current ticket should be overdue. - + This method checks both the state to see if the necessary + requirements are met, and checks the time of the response + target against the current database time. + + return: boolean + """ + if self.sql.getCurrentTime()[0].now >= self.calculateOverdue(): + return True + else: + return False + security.declareProtected('MailManager Manage Tickets', 'calculateOverdue') def calculateOverdue(self, eventname='Overdue'): """ Determine when this ticket will be due for the given event Modified: MailManager/branches/RELENG_2_1/sql/v2_1/deleteRulesetTimeEvents.zsql =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_1/deleteRulesetTimeEvents.zsql 2006-08-08 09:41:19 UTC (rev 3412) +++ MailManager/branches/RELENG_2_1/sql/v2_1/deleteRulesetTimeEvents.zsql 2006-08-08 09:43:07 UTC (rev 3413) @@ -6,9 +6,7 @@ DELETE FROM <dtml-var schema>mm_ruleset_event_queue <dtml-sqlgroup where> - <dtml-if sqv_tid> - ticket_id = <dtml-sqlvar sqv_tid type="int"> - </dtml-if> + <dtml-sqltest sqv_tid column="ticket_id" type="int" multiple optional> <dtml-and> <dtml-if sqv_eventname> event = <dtml-sqlvar sqv_eventname type="string"> This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <ke...@us...> - 2006-08-21 21:18:27
|
Revision: 3515 Author: kevca Date: 2006-08-21 14:17:58 -0700 (Mon, 21 Aug 2006) ViewCVS: http://svn.sourceforge.net/mailmanager/?rev=3515&view=rev Log Message: ----------- Factoring out report cache closed into an include file Modified Paths: -------------- MailManager/branches/RELENG_2_1/sql/v2_1/generateReportCacheClosed.zsql MailManager/branches/RELENG_2_1/sql/v2_1/getReportCacheClosed.zsql Added Paths: ----------- MailManager/branches/RELENG_2_1/sql/v2_1/reportCacheClosed.inc Modified: MailManager/branches/RELENG_2_1/sql/v2_1/generateReportCacheClosed.zsql =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_1/generateReportCacheClosed.zsql 2006-08-21 20:54:44 UTC (rev 3514) +++ MailManager/branches/RELENG_2_1/sql/v2_1/generateReportCacheClosed.zsql 2006-08-21 21:17:58 UTC (rev 3515) @@ -29,57 +29,30 @@ </dtml-comment> -INSERT INTO <dtml-var schema>mm_report_cache_received ( +INSERT INTO <dtml-var schema>mm_report_cache_closed ( cache_date, assigned, ticket_count ) - SELECT + <dtml-let sqv_start_date="sqv_date+' 00:00'"> + <dtml-let sqv_end_date="sqv_date+' 23:59'"> + <dtml-let sqv_subsection="'user'"> - <dtml-sqlvar sqv_date type="nb"> AS cache_date, - COALESCE(table_count.assigned, user_count.assigned), - COALESCE(table_count.ticket_count, user_count.ticket_count) + SELECT - FROM ( - <dtml-comment> + <dtml-sqlvar sqv_date type="nb"> AS cache_date, + subres.assigned, + subres.ticket_count - Obtain the stats from the mm_ticket table, which may omit - an entry for users where there are no tickets. + FROM ( + <dtml-include file='reportCacheReceived.inc'/> + ) AS subres - </dtml-comment> + </dtml-let> + </dtml-let> + </dtml-let> - SELECT - assigned, COUNT(1) AS ticket_count - FROM <dtml-var schema>mm_ticket - - <dtml-sqlgroup where> - date_closed >= (SELECT CAST('<dtml-var sqv_date> 00:00' AS <dtml-var sql_datetimestamp>)) - <dtml-and> - date_closed <= (SELECT CAST('<dtml-var sqv_date> 23:59' AS <dtml-var sql_datetimestamp>)) - <dtml-and> - state = 'Closed' - </dtml-sqlgroup> - - GROUP BY assigned - ORDER BY assigned - ) AS table_count - - <dtml-comment> - - Join the mm_user table to ensure that all users are included in - the result. This select gives a username and a 0 for all users. - - </dtml-comment> - - RIGHT OUTER JOIN ( - SELECT - username AS assigned, 0 AS ticket_count - FROM <dtml-var schema>mm_user - ) AS user_count - - ON table_count.assigned = user_count.assigned - <dtml-var sql_delimiter> @@ -95,53 +68,24 @@ ticket_count ) - SELECT + <dtml-let sqv_start_date="sqv_date+' 00:00'"> + <dtml-let sqv_end_date="sqv_date+' 23:59'"> + <dtml-let sqv_subsection="'account'"> - <dtml-sqlvar sqv_date type="nb"> AS cache_date, - COALESCE(table_count.account_id, account_count.account_id), - COALESCE(table_count.ticket_count, account_count.ticket_count) + SELECT - FROM ( + <dtml-sqlvar sqv_date type="nb"> AS cache_date, + subres.account, + subres.ticket_count - <dtml-comment> + FROM ( + <dtml-include file='reportCacheReceived.inc'/> + ) AS subres - Obtain the stats from the mm_ticket table, which may omit - an entry for accounts where there are no tickets. + </dtml-let> + </dtml-let> + </dtml-let> - </dtml-comment> - - SELECT - account_id, COUNT(1) AS ticket_count - FROM <dtml-var schema>mm_ticket - - <dtml-sqlgroup where> - date_closed >= (SELECT CAST('<dtml-var sqv_date> 00:00' AS <dtml-var sql_datetimestamp>)) - <dtml-and> - date_closed <= (SELECT CAST('<dtml-var sqv_date> 23:59' AS <dtml-var sql_datetimestamp>)) - <dtml-and> - state = 'Closed' - </dtml-sqlgroup> - - GROUP BY account_id - ORDER BY account_id - ) AS table_count - - <dtml-comment> - - Join the mm_account table to ensure that all accounts are - included in the result. This select gives an account_id and - a 0 for all accounts. - - </dtml-comment> - - RIGHT OUTER JOIN ( - SELECT - email AS account_id, 0 AS ticket_count - FROM <dtml-var schema>mm_account - ) AS account_count - - ON table_count.account_id = account_count.account_id - <dtml-var sql_delimiter> @@ -156,55 +100,25 @@ category0, ticket_count ) + <dtml-let sqv_start_date="sqv_date+' 00:00'"> + <dtml-let sqv_end_date="sqv_date+' 23:59'"> + <dtml-let sqv_subsection="'category0'"> - SELECT + SELECT - <dtml-sqlvar sqv_date type="nb"> AS cache_date, - COALESCE(table_count.category0, category_count.category0), - COALESCE(table_count.ticket_count, category_count.ticket_count) + <dtml-sqlvar sqv_date type="nb"> AS cache_date, + subres.category0, + subres.ticket_count - FROM ( - - <dtml-comment> + FROM ( + <dtml-include file='reportCacheReceived.inc'/> + ) AS subres - Obtain the stats from the mm_ticket table, which may omit - an entry for categories where there are no tickets. + </dtml-let> + </dtml-let> + </dtml-let> - </dtml-comment> - SELECT - category0, COUNT(1) AS ticket_count - FROM <dtml-var schema>mm_ticket - - <dtml-sqlgroup where> - date_closed >= (SELECT CAST('<dtml-var sqv_date> 00:00' AS <dtml-var sql_datetimestamp>)) - <dtml-and> - date_closed <= (SELECT CAST('<dtml-var sqv_date> 23:59' AS <dtml-var sql_datetimestamp>)) - <dtml-and> - state = 'Closed' - </dtml-sqlgroup> - - GROUP BY category0 - ORDER BY category0 - ) AS table_count - - <dtml-comment> - - Join the mm_categoryX table to ensure that all category choices - are included in the result. This select gives a category choice - and a 0 for all category choices. - - </dtml-comment> - - RIGHT OUTER JOIN ( - SELECT - choice AS category0, 0 AS ticket_count - FROM <dtml-var schema>mm_category_choices - WHERE category_id = 0 AND choice != '' - ) AS category_count - - ON table_count.category0 = category_count.category0 - <dtml-var sql_delimiter> @@ -220,53 +134,24 @@ ticket_count ) - SELECT + <dtml-let sqv_start_date="sqv_date+' 00:00'"> + <dtml-let sqv_end_date="sqv_date+' 23:59'"> + <dtml-let sqv_subsection="'category1'"> - <dtml-sqlvar sqv_date type="nb"> AS cache_date, - COALESCE(table_count.category1, category_count.category1), - COALESCE(table_count.ticket_count, category_count.ticket_count) + SELECT - FROM ( - <dtml-comment> + <dtml-sqlvar sqv_date type="nb"> AS cache_date, + subres.category1, + subres.ticket_count - Obtain the stats from the mm_ticket table, which may omit - an entry for categories where there are no tickets. + FROM ( + <dtml-include file='reportCacheReceived.inc'/> + ) AS subres - </dtml-comment> + </dtml-let> + </dtml-let> + </dtml-let> - SELECT - category1, COUNT(1) AS ticket_count - FROM <dtml-var schema>mm_ticket - - <dtml-sqlgroup where> - date_closed >= (SELECT CAST('<dtml-var sqv_date> 00:00' AS <dtml-var sql_datetimestamp>)) - <dtml-and> - date_closed <= (SELECT CAST('<dtml-var sqv_date> 23:59' AS <dtml-var sql_datetimestamp>)) - <dtml-and> - state = 'Closed' - </dtml-sqlgroup> - - GROUP BY category1 - ORDER BY category1 - ) AS table_count - - <dtml-comment> - - Join the mm_categoryX table to ensure that all category choices - are included in the result. This select gives a category choice - and a 0 for all category choices. - - </dtml-comment> - - RIGHT OUTER JOIN ( - SELECT - choice AS category1, 0 AS ticket_count - FROM <dtml-var schema>mm_category_choices - WHERE category_id = 1 AND choice != '' - ) AS category_count - - ON table_count.category1 = category_count.category1 - <dtml-var sql_delimiter> @@ -283,52 +168,24 @@ ticket_count ) - SELECT + <dtml-let sqv_start_date="sqv_date+' 00:00'"> + <dtml-let sqv_end_date="sqv_date+' 23:59'"> + <dtml-let sqv_subsection="'category2'"> - <dtml-sqlvar sqv_date type="nb"> AS cache_date, - COALESCE(table_count.category2, category_count.category2), - COALESCE(table_count.ticket_count, category_count.ticket_count) + SELECT - FROM ( - <dtml-comment> + <dtml-sqlvar sqv_date type="nb"> AS cache_date, + subres.category2, + subres.ticket_count - Obtain the stats from the mm_ticket table, which may omit - an entry for categories where there are no tickets. + FROM ( + <dtml-include file='reportCacheReceived.inc'/> + ) AS subres - </dtml-comment> + </dtml-let> + </dtml-let> + </dtml-let> - SELECT - category2, COUNT(1) AS ticket_count - FROM <dtml-var schema>mm_ticket - <dtml-sqlgroup where> - date_closed >= (SELECT CAST('<dtml-var sqv_date> 00:00' AS <dtml-var sql_datetimestamp>)) - <dtml-and> - date_closed <= (SELECT CAST('<dtml-var sqv_date> 23:59' AS <dtml-var sql_datetimestamp>)) - <dtml-and> - state = 'Closed' - </dtml-sqlgroup> - - GROUP BY category2 - ORDER BY category2 - ) AS table_count - - <dtml-comment> - - Join the mm_categoryX table to ensure that all category choices - are included in the result. This select gives a category choice - and a 0 for all category choices. - - </dtml-comment> - - RIGHT OUTER JOIN ( - SELECT - choice AS category2, 0 AS ticket_count - FROM <dtml-var schema>mm_category_choices - WHERE category_id = 2 AND choice != '' - ) AS category_count - - ON table_count.category2 = category_count.category2 - <dtml-var sql_delimiter> Modified: MailManager/branches/RELENG_2_1/sql/v2_1/getReportCacheClosed.zsql =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_1/getReportCacheClosed.zsql 2006-08-21 20:54:44 UTC (rev 3514) +++ MailManager/branches/RELENG_2_1/sql/v2_1/getReportCacheClosed.zsql 2006-08-21 21:17:58 UTC (rev 3515) @@ -13,265 +13,72 @@ FROM - ( SELECT ticket_count, account, assigned, category0, category1, category2 FROM + ( - <dtml-var schema>mm_report_cache_closed - <dtml-sqlgroup where> - cache_date >= <dtml-sqlvar sqv_from_date type="nb"> - <dtml-and> - cache_date <= <dtml-sqlvar sqv_to_date type="nb"> - <dtml-and> - <dtml-if sqv_subsection> - <dtml-if expr="sqv_subsection == 'account'"> - account IS NOT NULL - </dtml-if> - <dtml-if expr="sqv_subsection == 'user'"> - assigned IS NOT NULL - </dtml-if> - <dtml-if expr="sqv_subsection == 'category0'"> - category0 IS NOT NULL - </dtml-if> - <dtml-if expr="sqv_subsection == 'category1'"> - category1 IS NOT NULL - </dtml-if> - <dtml-if expr="sqv_subsection == 'category2'"> - category2 IS NOT NULL - </dtml-if> + <dtml-unless sqv_nocache> + + SELECT ticket_count, account, assigned, category0, category1, category2 FROM + + <dtml-var schema>mm_report_cache_closed + <dtml-sqlgroup where> + cache_date >= <dtml-sqlvar sqv_from_date type="nb"> + <dtml-and> + cache_date <= <dtml-sqlvar sqv_to_date type="nb"> + <dtml-and> + <dtml-if sqv_subsection> + <dtml-if expr="sqv_subsection == 'account'"> + account IS NOT NULL + </dtml-if> + <dtml-if expr="sqv_subsection == 'user'"> + assigned IS NOT NULL + </dtml-if> + <dtml-if expr="sqv_subsection == 'category0'"> + category0 IS NOT NULL + </dtml-if> + <dtml-if expr="sqv_subsection == 'category1'"> + category1 IS NOT NULL + </dtml-if> + <dtml-if expr="sqv_subsection == 'category2'"> + category2 IS NOT NULL + </dtml-if> + </dtml-if> + <dtml-and> + </dtml-sqlgroup> + + <dtml-if sqv_include_live_stats> + + UNION ALL + </dtml-if> - <dtml-and> - </dtml-sqlgroup> + </dtml-unless sqv_nocache> + <dtml-if sqv_include_live_stats> - UNION ALL - <dtml-comment> Live Cache - Please see generateReportCacheClosed for a complete explanation + Please see generateReportCacheReceived for a complete explanation of this section. It returns results which are row compatible with - the cache results, and allows the current data from the live - database to be included in reports. + the cache results, and allows the current data from the live + database to be included in reports. </dtml-comment> + <dtml-let sqv_start_date=sqv_today_date> + <dtml-let sqv_end_date=sqv_to_date> + <dtml-include file='reportCacheReceived.inc'/> + </dtml-let> + </dtml-let> - <dtml-if expr="sqv_subsection == 'user'"> - - SELECT - - COALESCE(table_count.ticket_count, user_count.ticket_count), - NULL AS account, - COALESCE(table_count.assigned, user_count.assigned), - NULL AS category0, - NULL AS category1, - NULL AS category2 - - FROM ( - - SELECT - assigned, COUNT(1) AS ticket_count - FROM <dtml-var schema>mm_ticket - - <dtml-sqlgroup where> - date_closed >= (SELECT CAST(<dtml-sqlvar sqv_today_date type="nb"> AS <dtml-var sql_datetimestamp>)) - <dtml-and> - date_closed <= (SELECT CAST(<dtml-sqlvar sqv_to_date type="nb"> AS <dtml-var sql_datetimestamp>)) - <dtml-and> - state = 'Closed' - </dtml-sqlgroup> - - GROUP BY assigned - ORDER BY assigned - - ) AS table_count - - RIGHT OUTER JOIN ( - SELECT - username AS assigned, 0 AS ticket_count - FROM <dtml-var schema>mm_user - ) AS user_count - - ON table_count.assigned = user_count.assigned - - </dtml-if> - - - <dtml-if expr="sqv_subsection == 'account'"> - - SELECT - - COALESCE(table_count.ticket_count, account_count.ticket_count), - COALESCE(table_count.account_id, account_count.account_id), - NULL AS assigned, - NULL AS category0, - NULL AS category1, - NULL AS category2 - - FROM ( - - SELECT - account_id, COUNT(1) AS ticket_count - FROM <dtml-var schema>mm_ticket - - <dtml-sqlgroup where> - date_closed >= (SELECT CAST(<dtml-sqlvar sqv_today_date type="nb"> AS <dtml-var sql_datetimestamp>)) - <dtml-and> - date_closed <= (SELECT CAST(<dtml-sqlvar sqv_to_date type="nb"> AS <dtml-var sql_datetimestamp>)) - <dtml-and> - state = 'Closed' - </dtml-sqlgroup> - - GROUP BY account_id - ORDER BY account_id - - ) AS table_count - - RIGHT OUTER JOIN ( - SELECT - email AS account_id, 0 AS ticket_count - FROM <dtml-var schema>mm_account - ) AS account_count - - ON table_count.account_id = account_count.account_id - - </dtml-if> - - - <dtml-if expr="sqv_subsection == 'category0'"> - - SELECT - - COALESCE(table_count.ticket_count, category_count.ticket_count), - NULL AS account, - NULL AS assigned, - COALESCE(table_count.category0, category_count.category0), - NULL AS category1, - NULL AS category2 - - FROM ( - - SELECT - category0, COUNT(1) AS ticket_count - FROM <dtml-var schema>mm_ticket - - <dtml-sqlgroup where> - date_closed >= (SELECT CAST(<dtml-sqlvar sqv_today_date type="nb"> AS <dtml-var sql_datetimestamp>)) - <dtml-and> - date_closed <= (SELECT CAST(<dtml-sqlvar sqv_to_date type="nb"> AS <dtml-var sql_datetimestamp>)) - <dtml-and> - state = 'Closed' - </dtml-sqlgroup> - - GROUP BY category0 - ORDER BY category0 - - ) AS table_count - - RIGHT OUTER JOIN ( - SELECT - choice AS category0, 0 AS ticket_count - FROM <dtml-var schema>mm_category_choices - WHERE category_id = 0 AND choice != '' - ) AS category_count - - ON table_count.category0 = category_count.category0 - - </dtml-if> - - - <dtml-if expr="sqv_subsection == 'category1'"> - - SELECT - - COALESCE(table_count.ticket_count, category_count.ticket_count), - NULL AS account, - NULL AS assigned, - NULL AS category0, - COALESCE(table_count.category1, category_count.category1), - NULL AS category2 - - FROM ( - - SELECT - category1, COUNT(1) AS ticket_count - FROM <dtml-var schema>mm_ticket - - <dtml-sqlgroup where> - date_closed >= (SELECT CAST(<dtml-sqlvar sqv_today_date type="nb"> AS <dtml-var sql_datetimestamp>)) - <dtml-and> - date_closed <= (SELECT CAST(<dtml-sqlvar sqv_to_date type="nb"> AS <dtml-var sql_datetimestamp>)) - <dtml-and> - state = 'Closed' - </dtml-sqlgroup> - - GROUP BY category1 - ORDER BY category1 - - ) AS table_count - - RIGHT OUTER JOIN ( - SELECT - choice AS category1, 0 AS ticket_count - FROM <dtml-var schema>mm_category_choices - WHERE category_id = 1 AND choice != '' - ) AS category_count - - ON table_count.category1 = category_count.category1 - - </dtml-if> - - - <dtml-if expr="sqv_subsection == 'category2'"> - - SELECT - - COALESCE(table_count.ticket_count, category_count.ticket_count), - NULL AS account, - NULL AS assigned, - NULL AS category0, - NULL AS category1, - COALESCE(table_count.category2, category_count.category2) - - FROM ( - - SELECT - category2, COUNT(1) AS ticket_count - FROM <dtml-var schema>mm_ticket - - <dtml-sqlgroup where> - date_closed >= (SELECT CAST(<dtml-sqlvar sqv_today_date type="nb"> AS <dtml-var sql_datetimestamp>)) - <dtml-and> - date_closed <= (SELECT CAST(<dtml-sqlvar sqv_to_date type="nb"> AS <dtml-var sql_datetimestamp>)) - <dtml-and> - state = 'Closed' - </dtml-sqlgroup> - - GROUP BY category2 - ORDER BY category2 - - ) AS table_count - - RIGHT OUTER JOIN ( - SELECT - choice AS category2, 0 AS ticket_count - FROM <dtml-var schema>mm_category_choices - WHERE category_id = 2 AND choice != '' - ) AS category_count - - ON table_count.category2 = category_count.category2 - - </dtml-if> - </dtml-if> ) AS subquery - GROUP BY - subquery.account, - subquery.assigned, - subquery.category0, - subquery.category1, + GROUP BY + subquery.account, + subquery.assigned, + subquery.category0, + subquery.category1, subquery.category2 - Added: MailManager/branches/RELENG_2_1/sql/v2_1/reportCacheClosed.inc =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_1/reportCacheClosed.inc (rev 0) +++ MailManager/branches/RELENG_2_1/sql/v2_1/reportCacheClosed.inc 2006-08-21 21:17:58 UTC (rev 3515) @@ -0,0 +1,346 @@ +<dtml-comment> + + Get a report cache entry for the given date range. It reads from + the live information in the database. + + This sub method is used in the methods genReportCacheReceived and + getReportCacheReceived. In the former, the results are used to + build cache tables. In the latter, it is joined with cache tables + in order to get stats for periods where no cache exists. + + sqv_start_date + sqv_end_date + sqv_subsection + + It will return a relation of the form + + ticket_count, account, assigned, category0, category1, category2 + + Where only one of the key fields + (account, assigned, category0, category1, category2) will be non null. + + Received report cache table format is as follows + + cache_date <dtml-var sql_date>, + account <dtml-var sql_varchar>(255), + assigned <dtml-var sql_varchar>(64), + category0 <dtml-var sql_smalltext>, + category1 <dtml-var sql_smalltext>, + category2 <dtml-var sql_smalltext>, + ticket_count <dtml-var sql_integer> + +</dtml-comment> + + +<dtml-comment> + + Report by User ---------------------------------------------------------- + +</dtml-comment> + + <dtml-if expr="sqv_subsection == 'user'"> + + SELECT + + COALESCE(table_count.ticket_count, user_count.ticket_count) AS ticket_count, + NULL AS account, + COALESCE(table_count.assigned, user_count.assigned), + NULL AS category0, + NULL AS category1, + NULL AS category2 + + FROM ( + <dtml-comment> + + Obtain the stats from the mm_ticket table, which may omit + an entry for users where there are no tickets. + + </dtml-comment> + + SELECT + assigned, COUNT(1) AS ticket_count + FROM <dtml-var schema>mm_ticket + + <dtml-sqlgroup where> + date_closed >= (SELECT CAST(<dtml-sqlvar sqv_start_date type="nb"> AS <dtml-var sql_datetimestamp>)) + <dtml-and> + date_closed <= (SELECT CAST(<dtml-sqlvar sqv_end_date type="nb"> AS <dtml-var sql_datetimestamp>)) + <dtml-and> + state = 'Closed' + </dtml-sqlgroup> + + GROUP BY assigned + ORDER BY assigned + ) AS table_count + + <dtml-comment> + + Join the mm_user table to ensure that all users are included in + the result. This select gives a username and a 0 for all users. + + </dtml-comment> + + RIGHT OUTER JOIN ( + SELECT + username AS assigned, 0 AS ticket_count + FROM <dtml-var schema>mm_user + ) AS user_count + + ON table_count.assigned = user_count.assigned + + </dtml-if> + + +<dtml-comment> + + Report by Account ------------------------------------------------------- + +</dtml-comment> + + <dtml-if expr="sqv_subsection == 'account'"> + + SELECT + + COALESCE(table_count.ticket_count, user_count.ticket_count) AS ticket_count, + COALESCE(table_count.account_id, account_count.account_id) AS account, + NULL AS assigned, + NULL AS category0, + NULL AS category1, + NULL AS category2 + + FROM ( + + <dtml-comment> + + Obtain the stats from the mm_ticket table, which may omit + an entry for accounts where there are no tickets. + + </dtml-comment> + + SELECT + account_id, COUNT(1) AS ticket_count + FROM <dtml-var schema>mm_ticket + + <dtml-sqlgroup where> + date_closed >= (SELECT CAST(<dtml-sqlvar sqv_start_date type="nb"> AS <dtml-var sql_datetimestamp>)) + <dtml-and> + date_closed <= (SELECT CAST(<dtml-sqlvar sqv_end_date type="nb"> AS <dtml-var sql_datetimestamp>)) + <dtml-and> + state = 'Closed' + </dtml-sqlgroup> + + GROUP BY account_id + ORDER BY account_id + ) AS table_count + + <dtml-comment> + + Join the mm_account table to ensure that all accounts are + included in the result. This select gives an account_id and + a 0 for all accounts. + + </dtml-comment> + + RIGHT OUTER JOIN ( + SELECT + email AS account_id, 0 AS ticket_count + FROM <dtml-var schema>mm_account + ) AS account_count + + ON table_count.account_id = account_count.account_id + + </dtml-if> + +<dtml-var sql_delimiter> + + +<dtml-comment> + + Report by Category0 ----------------------------------------------------- + +</dtml-comment> + + <dtml-if expr="sqv_subsection == 'category0'"> + + SELECT + + COALESCE(table_count.ticket_count, user_count.ticket_count) AS ticket_count, + NULL AS account, + NULL AS assigned, + COALESCE(table_count.category0, category_count.category0), + NULL AS category1, + NULL AS category2 + + FROM ( + + <dtml-comment> + + Obtain the stats from the mm_ticket table, which may omit + an entry for categories where there are no tickets. + + </dtml-comment> + + SELECT + category0, COUNT(1) AS ticket_count + FROM <dtml-var schema>mm_ticket + + <dtml-sqlgroup where> + date_closed >= (SELECT CAST(<dtml-sqlvar sqv_start_date type="nb"> AS <dtml-var sql_datetimestamp>)) + <dtml-and> + date_closed <= (SELECT CAST(<dtml-sqlvar sqv_end_date type="nb"> AS <dtml-var sql_datetimestamp>)) + <dtml-and> + state = 'Closed' + </dtml-sqlgroup> + + GROUP BY category0 + ORDER BY category0 + ) AS table_count + + <dtml-comment> + + Join the mm_categoryX table to ensure that all category choices + are included in the result. This select gives a category choice + and a 0 for all category choices. + + </dtml-comment> + + RIGHT OUTER JOIN ( + SELECT + choice AS category0, 0 AS ticket_count + FROM <dtml-var schema>mm_category_choices + WHERE category_id = 0 AND choice != '' + ) AS category_count + + ON table_count.category0 = category_count.category0 + + </dtml-if> + +<dtml-var sql_delimiter> + + +<dtml-comment> + + Report by Category1 ----------------------------------------------------- + +</dtml-comment> + + <dtml-if expr="sqv_subsection == 'category1'"> + + SELECT + + COALESCE(table_count.ticket_count, user_count.ticket_count) AS ticket_count, + NULL AS account, + NULL AS assigned, + NULL AS category0, + COALESCE(table_count.category1, category_count.category1), + NULL AS category2 + + FROM ( + <dtml-comment> + + Obtain the stats from the mm_ticket table, which may omit + an entry for categories where there are no tickets. + + </dtml-comment> + + SELECT + category1, COUNT(1) AS ticket_count + FROM <dtml-var schema>mm_ticket + + <dtml-sqlgroup where> + date_closed >= (SELECT CAST(<dtml-sqlvar sqv_start_date type="nb"> AS <dtml-var sql_datetimestamp>)) + <dtml-and> + date_closed <= (SELECT CAST(<dtml-sqlvar sqv_end_date type="nb"> AS <dtml-var sql_datetimestamp>)) + <dtml-and> + state = 'Closed' + </dtml-sqlgroup> + + GROUP BY category1 + ORDER BY category1 + ) AS table_count + + <dtml-comment> + + Join the mm_categoryX table to ensure that all category choices + are included in the result. This select gives a category choice + and a 0 for all category choices. + + </dtml-comment> + + RIGHT OUTER JOIN ( + SELECT + choice AS category1, 0 AS ticket_count + FROM <dtml-var schema>mm_category_choices + WHERE category_id = 1 AND choice != '' + ) AS category_count + + ON table_count.category1 = category_count.category1 + + </dtml-if> + +<dtml-var sql_delimiter> + + + +<dtml-comment> + + Report by Category2 ----------------------------------------------------- + +</dtml-comment> + + <dtml-if expr="sqv_subsection == 'category2'"> + + SELECT + + COALESCE(table_count.ticket_count, user_count.ticket_count) AS ticket_count, + NULL AS account, + NULL AS assigned, + NULL AS category0, + NULL AS category1, + COALESCE(table_count.category2, category_count.category2) + + FROM ( + <dtml-comment> + + Obtain the stats from the mm_ticket table, which may omit + an entry for categories where there are no tickets. + + </dtml-comment> + + SELECT + category2, COUNT(1) AS ticket_count + FROM <dtml-var schema>mm_ticket + + <dtml-sqlgroup where> + date_closed >= (SELECT CAST(<dtml-sqlvar sqv_start_date type="nb"> AS <dtml-var sql_datetimestamp>)) + <dtml-and> + date_closed <= (SELECT CAST(<dtml-sqlvar sqv_end_date type="nb"> AS <dtml-var sql_datetimestamp>)) + <dtml-and> + state = 'Closed' + </dtml-sqlgroup> + + GROUP BY category2 + ORDER BY category2 + ) AS table_count + + <dtml-comment> + + Join the mm_categoryX table to ensure that all category choices + are included in the result. This select gives a category choice + and a 0 for all category choices. + + </dtml-comment> + + RIGHT OUTER JOIN ( + SELECT + choice AS category2, 0 AS ticket_count + FROM <dtml-var schema>mm_category_choices + WHERE category_id = 2 AND choice != '' + ) AS category_count + + ON table_count.category2 = category_count.category2 + + </dtml-if> + +<dtml-var sql_delimiter> + This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <ke...@us...> - 2006-08-22 09:32:21
|
Revision: 3522 Author: kevca Date: 2006-08-22 02:32:14 -0700 (Tue, 22 Aug 2006) ViewCVS: http://svn.sourceforge.net/mailmanager/?rev=3522&view=rev Log Message: ----------- Deal with strong typing in postgres Modified Paths: -------------- MailManager/branches/RELENG_2_1/sql/v2_1/reportCacheClosed.inc MailManager/branches/RELENG_2_1/sql/v2_1/reportCacheReceived.inc Modified: MailManager/branches/RELENG_2_1/sql/v2_1/reportCacheClosed.inc =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_1/reportCacheClosed.inc 2006-08-22 09:30:24 UTC (rev 3521) +++ MailManager/branches/RELENG_2_1/sql/v2_1/reportCacheClosed.inc 2006-08-22 09:32:14 UTC (rev 3522) @@ -43,11 +43,11 @@ SELECT COALESCE(table_count.ticket_count, user_count.ticket_count) AS ticket_count, - NULL AS account, + <dtml-var sql_nullastext> AS account, COALESCE(table_count.assigned, user_count.assigned) AS assigned, - NULL AS category0, - NULL AS category1, - NULL AS category2 + <dtml-var sql_nullastext> AS category0, + <dtml-var sql_nullastext> AS category1, + <dtml-var sql_nullastext> AS category2 FROM ( <dtml-comment> @@ -103,10 +103,10 @@ COALESCE(table_count.ticket_count, account_count.ticket_count) AS ticket_count, COALESCE(table_count.account_id, account_count.account_id) AS account, - NULL AS assigned, - NULL AS category0, - NULL AS category1, - NULL AS category2 + <dtml-var sql_nullastext> AS assigned, + <dtml-var sql_nullastext> AS category0, + <dtml-var sql_nullastext> AS category1, + <dtml-var sql_nullastext> AS category2 FROM ( @@ -163,11 +163,11 @@ SELECT COALESCE(table_count.ticket_count, category_count.ticket_count) AS ticket_count, - NULL AS account, - NULL AS assigned, + <dtml-var sql_nullastext> AS account, + <dtml-var sql_nullastext> AS assigned, COALESCE(table_count.category0, category_count.category0) AS category0, - NULL AS category1, - NULL AS category2 + <dtml-var sql_nullastext> AS category1, + <dtml-var sql_nullastext> AS category2 FROM ( @@ -225,11 +225,11 @@ SELECT COALESCE(table_count.ticket_count, category_count.ticket_count) AS ticket_count, - NULL AS account, - NULL AS assigned, - NULL AS category0, + <dtml-var sql_nullastext> AS account, + <dtml-var sql_nullastext> AS assigned, + <dtml-var sql_nullastext> AS category0, COALESCE(table_count.category1, category_count.category1) AS category1, - NULL AS category2 + <dtml-var sql_nullastext> AS category2 FROM ( <dtml-comment> @@ -286,10 +286,10 @@ SELECT COALESCE(table_count.ticket_count, category_count.ticket_count) AS ticket_count, - NULL AS account, - NULL AS assigned, - NULL AS category0, - NULL AS category1, + <dtml-var sql_nullastext> AS account, + <dtml-var sql_nullastext> AS assigned, + <dtml-var sql_nullastext> AS category0, + <dtml-var sql_nullastext> AS category1, COALESCE(table_count.category2, category_count.category2) AS category2 FROM ( Modified: MailManager/branches/RELENG_2_1/sql/v2_1/reportCacheReceived.inc =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_1/reportCacheReceived.inc 2006-08-22 09:30:24 UTC (rev 3521) +++ MailManager/branches/RELENG_2_1/sql/v2_1/reportCacheReceived.inc 2006-08-22 09:32:14 UTC (rev 3522) @@ -37,11 +37,11 @@ SELECT COALESCE(table_count.ticket_count, user_count.ticket_count) AS ticket_count, - NULL AS account, + <dtml-var sql_nullastext> AS account, COALESCE(table_count.assigned, user_count.assigned) AS assigned, - NULL AS category0, - NULL AS category1, - NULL AS category2 + <dtml-var sql_nullastext> AS category0, + <dtml-var sql_nullastext> AS category1, + <dtml-var sql_nullastext> AS category2 FROM ( @@ -92,10 +92,10 @@ COALESCE(table_count.ticket_count, account_count.ticket_count) AS ticket_count, COALESCE(table_count.account_id, account_count.account_id) AS account_id, - NULL AS assigned, - NULL AS category0, - NULL AS category1, - NULL AS category2 + <dtml-var sql_nullastext> AS assigned, + <dtml-var sql_nullastext> AS category0, + <dtml-var sql_nullastext> AS category1, + <dtml-var sql_nullastext> AS category2 FROM ( @@ -132,11 +132,11 @@ SELECT COALESCE(table_count.ticket_count, category_count.ticket_count) AS ticket_count, - NULL AS account, - NULL AS assigned, + <dtml-var sql_nullastext> AS account, + <dtml-var sql_nullastext> AS assigned, COALESCE(table_count.category0, category_count.category0) AS category0, - NULL AS category1, - NULL AS category2 + <dtml-var sql_nullastext> AS category1, + <dtml-var sql_nullastext> AS category2 FROM ( @@ -174,11 +174,11 @@ SELECT COALESCE(table_count.ticket_count, category_count.ticket_count) AS ticket_count, - NULL AS account, - NULL AS assigned, - NULL AS category0, + <dtml-var sql_nullastext> AS account, + <dtml-var sql_nullastext> AS assigned, + <dtml-var sql_nullastext> AS category0, COALESCE(table_count.category1, category_count.category1) AS category1, - NULL AS category2 + <dtml-var sql_nullastext> AS category2 FROM ( @@ -216,10 +216,10 @@ SELECT COALESCE(table_count.ticket_count, category_count.ticket_count) AS ticket_count, - NULL AS account, - NULL AS assigned, - NULL AS category0, - NULL AS category1, + <dtml-var sql_nullastext> AS account, + <dtml-var sql_nullastext> AS assigned, + <dtml-var sql_nullastext> AS category0, + <dtml-var sql_nullastext> AS category1, COALESCE(table_count.category2, category_count.category2) AS category2 FROM ( This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <ke...@us...> - 2006-09-25 16:05:00
|
Revision: 3617 http://svn.sourceforge.net/mailmanager/?rev=3617&view=rev Author: kevca Date: 2006-09-25 09:04:43 -0700 (Mon, 25 Sep 2006) Log Message: ----------- Renaming .inc files so they can be used standalone Modified Paths: -------------- MailManager/branches/RELENG_2_1/sql/v2_1/generateReportCacheReceived.zsql MailManager/branches/RELENG_2_1/sql/v2_1/getReportCacheReceived.zsql MailManager/branches/RELENG_2_1/sql/v2_1/reportCacheReceived.inc Modified: MailManager/branches/RELENG_2_1/sql/v2_1/generateReportCacheReceived.zsql =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_1/generateReportCacheReceived.zsql 2006-09-25 15:47:04 UTC (rev 3616) +++ MailManager/branches/RELENG_2_1/sql/v2_1/generateReportCacheReceived.zsql 2006-09-25 16:04:43 UTC (rev 3617) @@ -46,7 +46,7 @@ subres.ticket_count FROM ( - <dtml-include file='reportCacheReceived.inc'/> + <dtml-include file='reportCacheReceived.zsql'/> ) AS subres </dtml-let> Modified: MailManager/branches/RELENG_2_1/sql/v2_1/getReportCacheReceived.zsql =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_1/getReportCacheReceived.zsql 2006-09-25 15:47:04 UTC (rev 3616) +++ MailManager/branches/RELENG_2_1/sql/v2_1/getReportCacheReceived.zsql 2006-09-25 16:04:43 UTC (rev 3617) @@ -27,7 +27,7 @@ <dtml-let sqv_start_date=sqv_from_date> <dtml-let sqv_end_date=sqv_to_date> - <dtml-include file='reportCacheReceived.inc'/> + <dtml-include file='reportCacheReceived.zsql'/> </dtml-let> </dtml-let> @@ -73,7 +73,7 @@ <dtml-let sqv_start_date=sqv_today_date> <dtml-let sqv_end_date=sqv_to_date> - <dtml-include file='reportCacheReceived.inc'/> + <dtml-include file='reportCacheReceived.zsql'/> </dtml-let> </dtml-let> Modified: MailManager/branches/RELENG_2_1/sql/v2_1/reportCacheReceived.inc =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_1/reportCacheReceived.inc 2006-09-25 15:47:04 UTC (rev 3616) +++ MailManager/branches/RELENG_2_1/sql/v2_1/reportCacheReceived.inc 2006-09-25 16:04:43 UTC (rev 3617) @@ -1,4 +1,8 @@ <dtml-comment> +title:Generate a report cache entry for the given date for the received reports +connection_id: mailmanager_db +max_rows:0 +arguments:sqv_start_date sqv_end_date sqv_subsection Get a report cache entry for the given date range. It reads from the live information in the database. This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <ke...@us...> - 2006-09-25 16:05:29
|
Revision: 3618 http://svn.sourceforge.net/mailmanager/?rev=3618&view=rev Author: kevca Date: 2006-09-25 09:05:10 -0700 (Mon, 25 Sep 2006) Log Message: ----------- Renaming .inc files so they can be used standalone Added Paths: ----------- MailManager/branches/RELENG_2_1/sql/v2_1/reportCacheReceived.zsql Removed Paths: ------------- MailManager/branches/RELENG_2_1/sql/v2_1/reportCacheReceived.inc Deleted: MailManager/branches/RELENG_2_1/sql/v2_1/reportCacheReceived.inc =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_1/reportCacheReceived.inc 2006-09-25 16:04:43 UTC (rev 3617) +++ MailManager/branches/RELENG_2_1/sql/v2_1/reportCacheReceived.inc 2006-09-25 16:05:10 UTC (rev 3618) @@ -1,258 +0,0 @@ -<dtml-comment> -title:Generate a report cache entry for the given date for the received reports -connection_id: mailmanager_db -max_rows:0 -arguments:sqv_start_date sqv_end_date sqv_subsection - - Get a report cache entry for the given date range. It reads from - the live information in the database. - - This sub method is used in the methods genReportCacheReceived and - getReportCacheReceived. In the former, the results are used to - build cache tables. In the latter, it is joined with cache tables - in order to get stats for periods where no cache exists. - - sqv_start_date - sqv_end_date - sqv_subsection - - It will return a relation of the form - - ticket_count, account, assigned, category0, category1, category2 - - Where only one of the key fields - (account, assigned, category0, category1, category2) will be non null. - - Received report cache table format is as follows - - cache_date <dtml-var sql_date>, - account <dtml-var sql_varchar>(255), - assigned <dtml-var sql_varchar>(64), - category0 <dtml-var sql_smalltext>, - category1 <dtml-var sql_smalltext>, - category2 <dtml-var sql_smalltext>, - ticket_count <dtml-var sql_integer> - -</dtml-comment> - - - <dtml-if expr="sqv_subsection == 'user' or sqv_subsection == 'queue'"> - - SELECT - - COALESCE(table_count.ticket_count, user_count.ticket_count) AS ticket_count, - <dtml-var sql_nullastext> AS account, - COALESCE(table_count.assigned, user_count.assigned) AS assigned, - <dtml-var sql_nullastext> AS category0, - <dtml-var sql_nullastext> AS category1, - <dtml-var sql_nullastext> AS category2 - - FROM ( - - <dtml-comment> - - Obtain the stats from the mm_ticket table, which may omit - an entry for users where there are no tickets. - - </dtml-comment> - - SELECT - assigned, COUNT(1) AS ticket_count - FROM <dtml-var schema>mm_ticket - - <dtml-sqlgroup where> - date_opened >= (SELECT CAST(<dtml-sqlvar sqv_start_date type="nb"> AS <dtml-var sql_datetimestamp>)) - <dtml-and> - date_opened <= (SELECT CAST(<dtml-sqlvar sqv_end_date type="nb"> AS <dtml-var sql_datetimestamp>)) - <dtml-and> - state != 'Spam' - </dtml-sqlgroup> - - GROUP BY assigned - ORDER BY assigned - - ) AS table_count - - <dtml-comment> - - Join the mm_user table to ensure that all users are included in - the result. This select gives a username and a 0 for all users. - - </dtml-comment> - - RIGHT OUTER JOIN ( - SELECT - username AS assigned, 0 AS ticket_count - FROM <dtml-var schema>mm_user - ) AS user_count - - ON table_count.assigned = user_count.assigned - - </dtml-if> - - <dtml-if expr="sqv_subsection == 'account'"> - - SELECT - - COALESCE(table_count.ticket_count, account_count.ticket_count) AS ticket_count, - COALESCE(table_count.account_id, account_count.account_id) AS account, - <dtml-var sql_nullastext> AS assigned, - <dtml-var sql_nullastext> AS category0, - <dtml-var sql_nullastext> AS category1, - <dtml-var sql_nullastext> AS category2 - - FROM ( - - SELECT - account_id, COUNT(1) AS ticket_count - FROM <dtml-var schema>mm_ticket - - <dtml-sqlgroup where> - date_opened >= (SELECT CAST(<dtml-sqlvar sqv_start_date type="nb"> AS <dtml-var sql_datetimestamp>)) - <dtml-and> - date_opened <= (SELECT CAST(<dtml-sqlvar sqv_end_date type="nb"> AS <dtml-var sql_datetimestamp>)) - <dtml-and> - state != 'Spam' - </dtml-sqlgroup> - - GROUP BY account_id - ORDER BY account_id - - ) AS table_count - - RIGHT OUTER JOIN ( - SELECT - email AS account_id, 0 AS ticket_count - FROM <dtml-var schema>mm_account - ) AS account_count - - ON table_count.account_id = account_count.account_id - - </dtml-if> - - - <dtml-if expr="sqv_subsection == 'category0'"> - - SELECT - - COALESCE(table_count.ticket_count, category_count.ticket_count) AS ticket_count, - <dtml-var sql_nullastext> AS account, - <dtml-var sql_nullastext> AS assigned, - COALESCE(table_count.category0, category_count.category0) AS category0, - <dtml-var sql_nullastext> AS category1, - <dtml-var sql_nullastext> AS category2 - - FROM ( - - SELECT - category0, COUNT(1) AS ticket_count - FROM <dtml-var schema>mm_ticket - - <dtml-sqlgroup where> - date_opened >= (SELECT CAST(<dtml-sqlvar sqv_start_date type="nb"> AS <dtml-var sql_datetimestamp>)) - <dtml-and> - date_opened <= (SELECT CAST(<dtml-sqlvar sqv_end_date type="nb"> AS <dtml-var sql_datetimestamp>)) - <dtml-and> - state != 'Spam' - </dtml-sqlgroup> - - GROUP BY category0 - ORDER BY category0 - - ) AS table_count - - RIGHT OUTER JOIN ( - SELECT - choice AS category0, 0 AS ticket_count - FROM <dtml-var schema>mm_category_choices - WHERE category_id = 0 AND choice != '' - ) AS category_count - - ON table_count.category0 = category_count.category0 - - </dtml-if> - - - <dtml-if expr="sqv_subsection == 'category1'"> - - SELECT - - COALESCE(table_count.ticket_count, category_count.ticket_count) AS ticket_count, - <dtml-var sql_nullastext> AS account, - <dtml-var sql_nullastext> AS assigned, - <dtml-var sql_nullastext> AS category0, - COALESCE(table_count.category1, category_count.category1) AS category1, - <dtml-var sql_nullastext> AS category2 - - FROM ( - - SELECT - category1, COUNT(1) AS ticket_count - FROM <dtml-var schema>mm_ticket - - <dtml-sqlgroup where> - date_opened >= (SELECT CAST(<dtml-sqlvar sqv_start_date type="nb"> AS <dtml-var sql_datetimestamp>)) - <dtml-and> - date_opened <= (SELECT CAST(<dtml-sqlvar sqv_end_date type="nb"> AS <dtml-var sql_datetimestamp>)) - <dtml-and> - state != 'Spam' - </dtml-sqlgroup> - - GROUP BY category1 - ORDER BY category1 - - ) AS table_count - - RIGHT OUTER JOIN ( - SELECT - choice AS category1, 0 AS ticket_count - FROM <dtml-var schema>mm_category_choices - WHERE category_id = 1 AND choice != '' - ) AS category_count - - ON table_count.category1 = category_count.category1 - - </dtml-if> - - - <dtml-if expr="sqv_subsection == 'category2'"> - - SELECT - - COALESCE(table_count.ticket_count, category_count.ticket_count) AS ticket_count, - <dtml-var sql_nullastext> AS account, - <dtml-var sql_nullastext> AS assigned, - <dtml-var sql_nullastext> AS category0, - <dtml-var sql_nullastext> AS category1, - COALESCE(table_count.category2, category_count.category2) AS category2 - - FROM ( - - SELECT - category2, COUNT(1) AS ticket_count - FROM <dtml-var schema>mm_ticket - - <dtml-sqlgroup where> - date_opened >= (SELECT CAST(<dtml-sqlvar sqv_start_date type="nb"> AS <dtml-var sql_datetimestamp>)) - <dtml-and> - date_opened <= (SELECT CAST(<dtml-sqlvar sqv_end_date type="nb"> AS <dtml-var sql_datetimestamp>)) - <dtml-and> - state != 'Spam' - </dtml-sqlgroup> - - GROUP BY category2 - ORDER BY category2 - - ) AS table_count - - RIGHT OUTER JOIN ( - SELECT - choice AS category2, 0 AS ticket_count - FROM <dtml-var schema>mm_category_choices - WHERE category_id = 2 AND choice != '' - ) AS category_count - - ON table_count.category2 = category_count.category2 - - </dtml-if> - Copied: MailManager/branches/RELENG_2_1/sql/v2_1/reportCacheReceived.zsql (from rev 3617, MailManager/branches/RELENG_2_1/sql/v2_1/reportCacheReceived.inc) =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_1/reportCacheReceived.zsql (rev 0) +++ MailManager/branches/RELENG_2_1/sql/v2_1/reportCacheReceived.zsql 2006-09-25 16:05:10 UTC (rev 3618) @@ -0,0 +1,258 @@ +<dtml-comment> +title:Generate a report cache entry for the given date for the received reports +connection_id: mailmanager_db +max_rows:0 +arguments:sqv_start_date sqv_end_date sqv_subsection + + Get a report cache entry for the given date range. It reads from + the live information in the database. + + This sub method is used in the methods genReportCacheReceived and + getReportCacheReceived. In the former, the results are used to + build cache tables. In the latter, it is joined with cache tables + in order to get stats for periods where no cache exists. + + sqv_start_date + sqv_end_date + sqv_subsection + + It will return a relation of the form + + ticket_count, account, assigned, category0, category1, category2 + + Where only one of the key fields + (account, assigned, category0, category1, category2) will be non null. + + Received report cache table format is as follows + + cache_date <dtml-var sql_date>, + account <dtml-var sql_varchar>(255), + assigned <dtml-var sql_varchar>(64), + category0 <dtml-var sql_smalltext>, + category1 <dtml-var sql_smalltext>, + category2 <dtml-var sql_smalltext>, + ticket_count <dtml-var sql_integer> + +</dtml-comment> + + + <dtml-if expr="sqv_subsection == 'user' or sqv_subsection == 'queue'"> + + SELECT + + COALESCE(table_count.ticket_count, user_count.ticket_count) AS ticket_count, + <dtml-var sql_nullastext> AS account, + COALESCE(table_count.assigned, user_count.assigned) AS assigned, + <dtml-var sql_nullastext> AS category0, + <dtml-var sql_nullastext> AS category1, + <dtml-var sql_nullastext> AS category2 + + FROM ( + + <dtml-comment> + + Obtain the stats from the mm_ticket table, which may omit + an entry for users where there are no tickets. + + </dtml-comment> + + SELECT + assigned, COUNT(1) AS ticket_count + FROM <dtml-var schema>mm_ticket + + <dtml-sqlgroup where> + date_opened >= (SELECT CAST(<dtml-sqlvar sqv_start_date type="nb"> AS <dtml-var sql_datetimestamp>)) + <dtml-and> + date_opened <= (SELECT CAST(<dtml-sqlvar sqv_end_date type="nb"> AS <dtml-var sql_datetimestamp>)) + <dtml-and> + state != 'Spam' + </dtml-sqlgroup> + + GROUP BY assigned + ORDER BY assigned + + ) AS table_count + + <dtml-comment> + + Join the mm_user table to ensure that all users are included in + the result. This select gives a username and a 0 for all users. + + </dtml-comment> + + RIGHT OUTER JOIN ( + SELECT + username AS assigned, 0 AS ticket_count + FROM <dtml-var schema>mm_user + ) AS user_count + + ON table_count.assigned = user_count.assigned + + </dtml-if> + + <dtml-if expr="sqv_subsection == 'account'"> + + SELECT + + COALESCE(table_count.ticket_count, account_count.ticket_count) AS ticket_count, + COALESCE(table_count.account_id, account_count.account_id) AS account, + <dtml-var sql_nullastext> AS assigned, + <dtml-var sql_nullastext> AS category0, + <dtml-var sql_nullastext> AS category1, + <dtml-var sql_nullastext> AS category2 + + FROM ( + + SELECT + account_id, COUNT(1) AS ticket_count + FROM <dtml-var schema>mm_ticket + + <dtml-sqlgroup where> + date_opened >= (SELECT CAST(<dtml-sqlvar sqv_start_date type="nb"> AS <dtml-var sql_datetimestamp>)) + <dtml-and> + date_opened <= (SELECT CAST(<dtml-sqlvar sqv_end_date type="nb"> AS <dtml-var sql_datetimestamp>)) + <dtml-and> + state != 'Spam' + </dtml-sqlgroup> + + GROUP BY account_id + ORDER BY account_id + + ) AS table_count + + RIGHT OUTER JOIN ( + SELECT + email AS account_id, 0 AS ticket_count + FROM <dtml-var schema>mm_account + ) AS account_count + + ON table_count.account_id = account_count.account_id + + </dtml-if> + + + <dtml-if expr="sqv_subsection == 'category0'"> + + SELECT + + COALESCE(table_count.ticket_count, category_count.ticket_count) AS ticket_count, + <dtml-var sql_nullastext> AS account, + <dtml-var sql_nullastext> AS assigned, + COALESCE(table_count.category0, category_count.category0) AS category0, + <dtml-var sql_nullastext> AS category1, + <dtml-var sql_nullastext> AS category2 + + FROM ( + + SELECT + category0, COUNT(1) AS ticket_count + FROM <dtml-var schema>mm_ticket + + <dtml-sqlgroup where> + date_opened >= (SELECT CAST(<dtml-sqlvar sqv_start_date type="nb"> AS <dtml-var sql_datetimestamp>)) + <dtml-and> + date_opened <= (SELECT CAST(<dtml-sqlvar sqv_end_date type="nb"> AS <dtml-var sql_datetimestamp>)) + <dtml-and> + state != 'Spam' + </dtml-sqlgroup> + + GROUP BY category0 + ORDER BY category0 + + ) AS table_count + + RIGHT OUTER JOIN ( + SELECT + choice AS category0, 0 AS ticket_count + FROM <dtml-var schema>mm_category_choices + WHERE category_id = 0 AND choice != '' + ) AS category_count + + ON table_count.category0 = category_count.category0 + + </dtml-if> + + + <dtml-if expr="sqv_subsection == 'category1'"> + + SELECT + + COALESCE(table_count.ticket_count, category_count.ticket_count) AS ticket_count, + <dtml-var sql_nullastext> AS account, + <dtml-var sql_nullastext> AS assigned, + <dtml-var sql_nullastext> AS category0, + COALESCE(table_count.category1, category_count.category1) AS category1, + <dtml-var sql_nullastext> AS category2 + + FROM ( + + SELECT + category1, COUNT(1) AS ticket_count + FROM <dtml-var schema>mm_ticket + + <dtml-sqlgroup where> + date_opened >= (SELECT CAST(<dtml-sqlvar sqv_start_date type="nb"> AS <dtml-var sql_datetimestamp>)) + <dtml-and> + date_opened <= (SELECT CAST(<dtml-sqlvar sqv_end_date type="nb"> AS <dtml-var sql_datetimestamp>)) + <dtml-and> + state != 'Spam' + </dtml-sqlgroup> + + GROUP BY category1 + ORDER BY category1 + + ) AS table_count + + RIGHT OUTER JOIN ( + SELECT + choice AS category1, 0 AS ticket_count + FROM <dtml-var schema>mm_category_choices + WHERE category_id = 1 AND choice != '' + ) AS category_count + + ON table_count.category1 = category_count.category1 + + </dtml-if> + + + <dtml-if expr="sqv_subsection == 'category2'"> + + SELECT + + COALESCE(table_count.ticket_count, category_count.ticket_count) AS ticket_count, + <dtml-var sql_nullastext> AS account, + <dtml-var sql_nullastext> AS assigned, + <dtml-var sql_nullastext> AS category0, + <dtml-var sql_nullastext> AS category1, + COALESCE(table_count.category2, category_count.category2) AS category2 + + FROM ( + + SELECT + category2, COUNT(1) AS ticket_count + FROM <dtml-var schema>mm_ticket + + <dtml-sqlgroup where> + date_opened >= (SELECT CAST(<dtml-sqlvar sqv_start_date type="nb"> AS <dtml-var sql_datetimestamp>)) + <dtml-and> + date_opened <= (SELECT CAST(<dtml-sqlvar sqv_end_date type="nb"> AS <dtml-var sql_datetimestamp>)) + <dtml-and> + state != 'Spam' + </dtml-sqlgroup> + + GROUP BY category2 + ORDER BY category2 + + ) AS table_count + + RIGHT OUTER JOIN ( + SELECT + choice AS category2, 0 AS ticket_count + FROM <dtml-var schema>mm_category_choices + WHERE category_id = 2 AND choice != '' + ) AS category_count + + ON table_count.category2 = category_count.category2 + + </dtml-if> + This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |