Menu
▾
▴
SF.net SVN: mailmanager: [3505] MailManager/branches/RELENG_2_1/sql
|
From: <ke...@us...> - 2006-08-21 15:47:24
|
Revision: 3505 Author: kevca Date: 2006-08-21 08:47:13 -0700 (Mon, 21 Aug 2006) ViewCVS: http://svn.sourceforge.net/mailmanager/?rev=3505&view=rev Log Message: ----------- Refactoring SQL methods We can now use dtml-include to reference other files. This makes updating common pieces of SQL much simpler. Note that this may make debug-mode loading of SQL files from disk inconsistent. Modified Paths: -------------- MailManager/branches/RELENG_2_1/sql/__init__.py MailManager/branches/RELENG_2_1/sql/v2_1/generateReportCacheReceived.zsql MailManager/branches/RELENG_2_1/sql/v2_1/getReportCacheReceived.zsql Added Paths: ----------- MailManager/branches/RELENG_2_1/sql/v2_1/reportCacheReceived.inc Modified: MailManager/branches/RELENG_2_1/sql/__init__.py =================================================================== --- MailManager/branches/RELENG_2_1/sql/__init__.py 2006-08-21 14:36:08 UTC (rev 3504) +++ MailManager/branches/RELENG_2_1/sql/__init__.py 2006-08-21 15:47:13 UTC (rev 3505) @@ -29,11 +29,90 @@ from Products.FileSystemSite.Permissions import View, ViewManagementScreens from Products.ZSQLMethods.SQL import SQL +import re +import os.path +from Products.FileSystemSite.DirectoryView import expandpath + + class FSZSQLWrapper(FSZSQLMethod): """ A wrapper to an sql method which converts unicode to utf-8 """ + def modifyData(self, data): + """ Replace macros in the SQL method data + + This is a rather crude way to implement macros in dtml, but it + avoids having to change any of the core code in the dtml parser. + """ + res = '' + # Look for blocks of <dtml-include file=''/> + include_regex = re.compile('<dtml-include\s+file\s*=\s*[\'"](?P<filename>[^\'"]+)[\'"]\s*\/>') + lines = data.split('\n') + for line in lines: + matchobj = include_regex.search(line) + if matchobj: + filename = matchobj.groupdict()['filename'] + fileroot = os.path.join(*os.path.split(self._filepath)[:-1]) + incfile = open(os.path.join(fileroot,filename),'r') + incdata = incfile.read() + line = include_regex.sub(incdata, line) + res += line + res += '\n' + return res + + + def _readFile(self, reparse): + """ Replacement for FileSystemSite's loading of ZSQL methods, in + order to allow use of macros. This replacement method adds in + a hook function to modify data. + """ + + fp = expandpath(self._filepath) + file = open(fp, 'r') # not 'rb', as this is a text file! + try: + data = file.read() + finally: file.close() + + # parse parameters + parameters={} + start = data.find('<dtml-comment>') + end = data.find('</dtml-comment>') + if start==-1 or end==-1 or start>end: + raise ValueError,'Could not find parameter block' + block = data[start+14:end] + + for line in block.split('\n'): + pair = line.split(':',1) + if len(pair)!=2: + continue + parameters[pair[0].strip().lower()]=pair[1].strip() + + # check for required an optional parameters + try: + title = parameters.get('title','') + connection_id = parameters.get('connection id',parameters['connection_id']) + arguments = parameters.get('arguments','') + max_rows = parameters.get('max_rows',1000) + max_cache = parameters.get('max_cache',100) + cache_time = parameters.get('cache_time',0) + except KeyError,e: + raise ValueError,"The '%s' parameter is required but was not supplied" % e + + data = self.modifyData(data) + + self.manage_edit(title, + connection_id, + arguments, + template=data) + + self.manage_advanced(max_rows, + max_cache, + cache_time, + '', # don't really see any point in allowing + '') # brain specification... + + def __call__(self, *args, **kw): # Modified: MailManager/branches/RELENG_2_1/sql/v2_1/generateReportCacheReceived.zsql =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_1/generateReportCacheReceived.zsql 2006-08-21 14:36:08 UTC (rev 3504) +++ MailManager/branches/RELENG_2_1/sql/v2_1/generateReportCacheReceived.zsql 2006-08-21 15:47:13 UTC (rev 3505) @@ -35,51 +35,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="'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_opened >= (SELECT CAST('<dtml-var sqv_date> 00:00' AS <dtml-var sql_datetimestamp>)) - <dtml-and> - date_opened <= (SELECT CAST('<dtml-var sqv_date> 23:59' 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-var sql_delimiter> @@ -102,7 +75,7 @@ COALESCE(table_count.ticket_count, account_count.ticket_count) FROM ( - + <dtml-comment> Obtain the stats from the mm_ticket table, which may omit Modified: MailManager/branches/RELENG_2_1/sql/v2_1/getReportCacheReceived.zsql =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_1/getReportCacheReceived.zsql 2006-08-21 14:36:08 UTC (rev 3504) +++ MailManager/branches/RELENG_2_1/sql/v2_1/getReportCacheReceived.zsql 2006-08-21 15:47:13 UTC (rev 3505) @@ -2,9 +2,12 @@ title:Get the reporting cache for received tickets connection_id: mailmanager_db max_rows:0 -arguments:sqv_from_date sqv_today_date sqv_to_date sqv_subsection sqv_include_live_stats +arguments:sqv_from_date sqv_today_date sqv_to_date sqv_subsection sqv_include_live_stats sqv_hourly -This method also takes into account the current +This method also takes into account the stats from the current date in the +database, if sqv_include_live_stats is true. Setting sqv_hourly will cause +the method to calculate from the database directly, rather than use the +cache, as the resolution of the cache is too low to do hourly stats. </dtml-comment> @@ -55,214 +58,12 @@ </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' or sqv_subsection == 'queue'"> - - 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_opened >= (SELECT CAST(<dtml-sqlvar sqv_today_date type="nb"> AS <dtml-var sql_datetimestamp>)) - <dtml-and> - date_opened <= (SELECT CAST(<dtml-sqlvar sqv_to_date type="nb"> AS <dtml-var sql_datetimestamp>)) - <dtml-and> - state != 'Spam' - </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_opened >= (SELECT CAST(<dtml-sqlvar sqv_today_date type="nb"> AS <dtml-var sql_datetimestamp>)) - <dtml-and> - date_opened <= (SELECT CAST(<dtml-sqlvar sqv_to_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), - 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_opened >= (SELECT CAST(<dtml-sqlvar sqv_today_date type="nb"> AS <dtml-var sql_datetimestamp>)) - <dtml-and> - date_opened <= (SELECT CAST(<dtml-sqlvar sqv_to_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), - 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_opened >= (SELECT CAST(<dtml-sqlvar sqv_today_date type="nb"> AS <dtml-var sql_datetimestamp>)) - <dtml-and> - date_opened <= (SELECT CAST(<dtml-sqlvar sqv_to_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), - 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_opened >= (SELECT CAST(<dtml-sqlvar sqv_today_date type="nb"> AS <dtml-var sql_datetimestamp>)) - <dtml-and> - date_opened <= (SELECT CAST(<dtml-sqlvar sqv_to_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> - </dtml-if> ) AS subquery @@ -273,4 +74,5 @@ subquery.category0, subquery.category1, subquery.category2 + Added: MailManager/branches/RELENG_2_1/sql/v2_1/reportCacheReceived.inc =================================================================== --- MailManager/branches/RELENG_2_1/sql/v2_1/reportCacheReceived.inc (rev 0) +++ MailManager/branches/RELENG_2_1/sql/v2_1/reportCacheReceived.inc 2006-08-21 15:47:13 UTC (rev 3505) @@ -0,0 +1,254 @@ +<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-if expr="sqv_subsection == 'user' or sqv_subsection == 'queue'"> + + SELECT + + COALESCE(table_count.ticket_count, user_count.ticket_count) AS ticket_count, + NULL AS account, + COALESCE(table_count.assigned, user_count.assigned) 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 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_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_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, + NULL AS account, + NULL AS assigned, + COALESCE(table_count.category0, category_count.category0) AS 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_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, + NULL AS account, + NULL AS assigned, + NULL AS category0, + COALESCE(table_count.category1, category_count.category1) AS category1, + NULL 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, + NULL AS account, + NULL AS assigned, + NULL AS category0, + NULL 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. |