From: <pk...@us...> - 2010-06-01 22:06:48
|
Revision: 27454 http://tikiwiki.svn.sourceforge.net/tikiwiki/?rev=27454&view=rev Author: pkdille Date: 2010-06-01 22:06:42 +0000 (Tue, 01 Jun 2010) Log Message: ----------- [QT] Quality Team Backport r26052 | jonnybradley | 2010-03-10 17:29:32 +0100 (Wed, 10 Mar 2010) | 6 lines [bp/r25883] in 4.x [bp/r25749] [NEW] Tracker dump. Allows an entire tracker to be exported as CSV. Overrides existing permissions so has it's own new perm, and saves raw values only (so far). Tested with up to 15,000 items with 20 fields each. Faster with more memory assigned but tested successfully down to around 45M. [bp/r25758] [FIX] Add test for admin on dump tracker [bp/r25771] [FIX] Correct quoting on item info and reduce database call to fields actually used Modified Paths: -------------- branches/3.0/db/tiki.sql branches/3.0/lib/trackers/trackerlib.php branches/3.0/templates/tiki-export_tracker.tpl branches/3.0/tiki-export_tracker.php Added Paths: ----------- branches/3.0/installer/schema/20100226_tracker_dump_perm_tiki.sql Modified: branches/3.0/db/tiki.sql =================================================================== --- branches/3.0/db/tiki.sql 2010-06-01 22:05:19 UTC (rev 27453) +++ branches/3.0/db/tiki.sql 2010-06-01 22:06:42 UTC (rev 27454) @@ -3090,6 +3090,7 @@ INSERT INTO users_permissions (permName, permDesc, level, type) VALUES ('tiki_p_view_backlink', 'View page backlinks', 'basic', 'wiki'); INSERT INTO users_permissions (permName, permDesc, level, type) VALUES ('tiki_p_admin_notifications', 'Can admin mail notifications', 'editors', 'mail notifications'); +INSERT INTO `users_permissions` (`permName`, `permDesc`, `level`, `type`, `admin`, `feature_check`) VALUES('tiki_p_tracker_dump', 'Can save a CSV backup of entire trackers', 'admin', 'trackers', NULL, 'feature_trackers'); UPDATE users_permissions SET feature_check = 'feature_wiki' WHERE permName IN( 'tiki_p_admin_wiki', Added: branches/3.0/installer/schema/20100226_tracker_dump_perm_tiki.sql =================================================================== --- branches/3.0/installer/schema/20100226_tracker_dump_perm_tiki.sql (rev 0) +++ branches/3.0/installer/schema/20100226_tracker_dump_perm_tiki.sql 2010-06-01 22:06:42 UTC (rev 27454) @@ -0,0 +1 @@ +INSERT INTO `users_permissions` (`permName`, `permDesc`, `level`, `type`, `admin`, `feature_check`) VALUES('tiki_p_tracker_dump', 'Can save a CSV backup of entire trackers', 'admin', 'trackers', NULL, 'feature_trackers'); Modified: branches/3.0/lib/trackers/trackerlib.php =================================================================== --- branches/3.0/lib/trackers/trackerlib.php 2010-06-01 22:05:19 UTC (rev 27453) +++ branches/3.0/lib/trackers/trackerlib.php 2010-06-01 22:06:42 UTC (rev 27454) @@ -1726,6 +1726,141 @@ return $total; } + function dump_tracker_csv($trackerId) { + global $tikilib; + $tracker_info = $this->get_tracker_options($trackerId); + $fields = $this->list_tracker_fields($trackerId, 0, -1, 'position_asc', ''); + + $trackerId = (int)$trackerId; + + // write out file header + session_write_close(); + $this->write_export_header(); + + // then "field names -- index" as first line + $str = ''; + $str .= 'itemId,status,created,lastModif,'; // these headings weren't quoted in the previous export function + if (count($fields['data']) > 0) { + foreach ($fields['data'] as $field) { + $str .= '"'.$field['name'].' -- '.$field['fieldId'].'",'; + } + } + echo $str; + + // prepare queries + $mid = ' WHERE tti.`trackerId` = ? '; + $bindvars = array($trackerId); + $join = ''; + + $query_items = 'SELECT tti.itemId, tti.status, tti.created, tti.lastModif' + .' FROM `tiki_tracker_items` tti' + .$mid + .' ORDER BY tti.`itemId` ASC'; + $query_fields = 'SELECT tti.itemId, ttif.`value`, ttf.`type`' + .' FROM (' + .' `tiki_tracker_items` tti' + .' INNER JOIN `tiki_tracker_item_fields` ttif ON tti.`itemId` = ttif.`itemId`' + .' INNER JOIN `tiki_tracker_fields` ttf ON ttf.`fieldId` = ttif.`fieldId`' + .')' + .$mid + .' ORDER BY tti.`itemId` ASC, ttif.`fieldId` ASC'; + $base_tables = '(' + .' `tiki_tracker_items` tti' + .' INNER JOIN `tiki_tracker_item_fields` ttif ON tti.`itemId` = ttif.`itemId`' + .' INNER JOIN `tiki_tracker_fields` ttf ON ttf.`fieldId` = ttif.`fieldId`' + .')'.$join; + + + $query_cant = 'SELECT count(DISTINCT ttif.`itemId`) FROM '.$base_tables.$mid; + $cant = $this->getOne($query_cant, $bindvars); + + + $memory_limit = trim(ini_get('memory_limit')); + $last = strtolower($memory_limit{strlen($memory_limit)-1}); + switch ( $last ) { + // The 'G' modifier is available since PHP 5.1.0 + case 'g': $memory_limit *= 1024; + case 'm': $memory_limit *= 1024; + case 'k': $memory_limit *= 1024; + } + + $avail_mem = $memory_limit - memory_get_usage(true); + $maxrecords_items = intval(($avail_mem - 10 * 1024 * 1025) / 5000); // depends on size of items table (fixed) + $offset_items = 0; + + $items = $this->get_dump_items_array($query_items, $bindvars, $maxrecords_items, $offset_items); + + $avail_mem = $memory_limit - memory_get_usage(true); // update avail after getting first batch of items + $maxrecords = (int)($avail_mem / 40000) * count($fields['data']); // depends on number of fields + $canto = $cant * count($fields['data']); + $offset = 0; + $lastItem = -1; + $count = 0; $icount = 0; + $field_values = array(); + + // write out rows + for ($offset = 0; $offset < $canto; $offset = $offset + $maxrecords) { + $result = $this->query($query_fields, $bindvars, $maxrecords, $offset); + $field_values = array(); // no fetchAll() in 3.x + while( $row = $result->fetchRow() ) { + $field_values[] = $row; + } + $mem = memory_get_usage(true); + + foreach ( $field_values as $res ) { + if ($lastItem != $res['itemId']) { + $lastItem = $res['itemId']; + echo "\n".$items[$lastItem]['itemId'].','.$items[$lastItem]['status'].','.$items[$lastItem]['created'].','.$items[$lastItem]['lastModif'].','; // also these fields weren't traditionally escaped + $count++; + $icount++; + if ($icount > $maxrecords_items) { + $offset_items += $maxrecords_items; + $items = $this->get_dump_items_array($query_items, $bindvars, $maxrecords_items, $offset_items); + $icount = 0; + } + } + echo '"' . $res['value'] . '",'; + } + ob_flush(); + flush(); + //if ($offset == 0) { $maxrecords = 1000 * count($fields['data']); } + } + echo "\n"; + ob_end_flush(); + } + + function get_dump_items_array($query, $bindvars, $maxrecords, $offset) { + $result = $this->query($query, $bindvars, $maxrecords, $offset); + $items_array = array(); // no fetchAll() in 3.x + while( $row = $result->fetchRow() ) { + $items_array[] = $row; + } + + $items = array(); + foreach ($items_array as $item) { + $items[$item['itemId']] = $item; + } + unset($items_array); + return $items; + } + + function write_export_header() { + header("Content-type: text/comma-separated-values; charset:".$_REQUEST['encoding']); + if (!empty($_REQUEST['file'])) { + if (preg_match('/.csv$/', $_REQUEST['file'])) { + $file = $_REQUEST['file']; + } else { + $file = $_REQUEST['file'].'.csv'; + } + } else { + $file = tra('tracker').'_'.$_REQUEST['trackerId'].'.csv'; + } + header("Content-Disposition: attachment; filename=$file"); + header("Expires: 0"); + header("Cache-Control: must-revalidate, post-check=0,pre-check=0"); + header("Pragma: public"); + } + function _describe_category_list($categs) { global $categlib; $res = ''; Modified: branches/3.0/templates/tiki-export_tracker.tpl =================================================================== --- branches/3.0/templates/tiki-export_tracker.tpl 2010-06-01 22:05:19 UTC (rev 27453) +++ branches/3.0/templates/tiki-export_tracker.tpl 2010-06-01 22:06:42 UTC (rev 27454) @@ -78,4 +78,37 @@ </tr> </table> </form> +{if $tiki_p_tracker_dump eq "y" or $tiki_p_admin eq "y"} + <h2>{tr}Dump All Tracker Items{/tr}</h2> + <div> + <form action="{$smarty.server.PHP_SELF}" method="post"> + <table class="normal"> + <tr class="formcolor"> + <td width="20%"><label for="tracker">{tr}Tracker{/tr}</label></td> + <td> + <select name="trackerId" onchange="this.form.submit();" id="dumpTrackerId"> + {foreach from=$trackers item=tracker} + <option value="{$tracker.trackerId}" title="{$tracker.description|escape}"{if $tracker.trackerId eq $trackerId} selected="selected"{/if}> + {$tracker.name|escape} + </option> + {/foreach} + </select> + {$recordsMax} {tr}Items{/tr} + </td> + </tr> + </table> + </form> + <form action="tiki-export_tracker.php?trackerId={$trackerId}" method="post" id="dump_form"> + <table> + <tr class="formcolor"> + <td width="20%"> </td> + <td> + <input type="submit" name="dump_tracker" id="dump_tracker" value="{tr}Dump{/tr}" /> + </td> + </tr> + </table> + </form> + </div> +{/if} + Modified: branches/3.0/tiki-export_tracker.php =================================================================== --- branches/3.0/tiki-export_tracker.php 2010-06-01 22:05:19 UTC (rev 27453) +++ branches/3.0/tiki-export_tracker.php 2010-06-01 22:06:42 UTC (rev 27454) @@ -41,6 +41,13 @@ die; } +if (isset($_REQUEST['dump_tracker'])) { + $access->check_permission('tiki_p_tracker_dump'); + + $trklib->dump_tracker_csv($_REQUEST['trackerId']); + return; +} + $filters = array(); if (!empty($_REQUEST['listfields'])) { if (is_string($_REQUEST['listfields'])) { This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |