Extension talk:RT

Hey-

Looks like this only works with RT using Postgres, not with MySQL? It doesn't explicitly say so anywhere, but it appears the pg_connect is only for postgres, and hence this extension is limited to Postgres based RT installations.

quick / dirty mysql hack to RT.php
i just did this to quickly get things going for our site and will improve later. --Erikvw 12:14, 1 June 2009 (UTC)
 * all my comments are marked with erikvw
 * original code remains in the file, just commented out with ####
 * requires a few extra parameters instead of just the $wgRequestTracker_DBconn
 * $wgRequestTracker_DBhost=
 * $wgRequestTracker_DBuser=
 * $wgRequestTracker_DBpasswd=
 * $wgRequestTracker_DBdbname=
 * mostly change pg_ to mysql_, mysql does not have mysql_fetch_all, mysql can be case sensitive on table names
 * use date_format and concat in main sql string instead.

";	}

// Standard info we gather $TZ = "AT TIME ZONE 'GMT'"; // removed erikvw - begin /* #### $ticketinfo = 't.id, t.subject, t.priority, concat(ucase(left(t.status,1)),substring(t.status,2)) AS status, q.name AS queue,' . ' COALESCE(u.realname, u.name) AS owner,' . ' u.name AS username,' . ' COALESCE(u2.realname, u2.name) AS creator,' . " TO_CHAR(t.lastupdated $TZ, '$wgRequestTracker_TIMEFORMAT_LASTUPDATED'::text) AS lastupdated," . " TO_CHAR(t.lastupdated $TZ, '$wgRequestTracker_TIMEFORMAT_LASTUPDATED2'::text) AS lastupdated2," . " TO_CHAR(now $TZ, '$wgRequestTracker_TIMEFORMAT_NOW'::text) AS nowtime," . " TO_CHAR(t.created $TZ, '$wgRequestTracker_TIMEFORMAT_CREATED'::text) AS created," . " TO_CHAR(t.created $TZ, '$wgRequestTracker_TIMEFORMAT_CREATED2'::text) AS created2," . " TO_CHAR(t.resolved $TZ, '$wgRequestTracker_TIMEFORMAT_RESOLVED'::text) AS resolved," . " TO_CHAR(t.resolved $TZ, '$wgRequestTracker_TIMEFORMAT_RESOLVED2'::text) AS resolved2," . "	CASE WHEN (now $TZ - t.created) <= '1 second'::interval THEN '1 second' ELSE" . " CASE WHEN (now $TZ - t.created) <= '2 minute'::interval THEN EXTRACT(seconds FROM now $TZ - t.created) || ' seconds' ELSE" . " CASE WHEN (now $TZ - t.created) <= '2 hour'::interval THEN EXTRACT(minutes FROM now $TZ - t.created) || ' minutes' ELSE" . " CASE WHEN (now $TZ - t.created) <= '2 day'::interval THEN EXTRACT(hours FROM now $TZ - t.created) || ' hours' ELSE" . " EXTRACT(days FROM now $TZ - t.created) || ' days' END END END END AS age"; */	// removed erikvw - end // added erikvw - begin // warning: i have REALLY simplified this, e.g. the age calc and dropped timezone issues... $ticketinfo = 't.id, t.subject, t.priority, concat(ucase(left(t.status,1)),substring(t.status,2)) AS status, q.name AS queue,' . ' COALESCE(u.realname, u.name) AS owner,' . ' u.name AS username,' . ' COALESCE(u2.realname, u2.name) AS creator,' . " date_format(t.lastupdated, '$wgRequestTracker_TIMEFORMAT_LASTUPDATED') AS lastupdated," . " date_format(t.lastupdated, '$wgRequestTracker_TIMEFORMAT_LASTUPDATED2') AS lastupdated2," . " date_format(now, '$wgRequestTracker_TIMEFORMAT_NOW') AS nowtime," . " date_format(t.created, '$wgRequestTracker_TIMEFORMAT_CREATED') AS created," . " date_format(t.created, '$wgRequestTracker_TIMEFORMAT_CREATED2') AS created2," . " date_format(t.resolved, '$wgRequestTracker_TIMEFORMAT_RESOLVED') AS resolved," . " date_format(t.resolved, '$wgRequestTracker_TIMEFORMAT_RESOLVED2') AS resolved2," . " datediff(now,t.created) AS age"; // added erikvw - end

// removed erikvw - begin // The standard query //$ticketquery = "SELECT $ticketinfo FROM tickets t" //	. ' JOIN users u ON t.owner = u.id' //	. ' JOIN users u2 ON t.creator = u2.id' //	. ' JOIN queues q ON t.queue = q.id'; // removed erikvw - end

// added erikvw - begin // mysql is case sensistive to the table names (rt3.6) $ticketquery = "SELECT $ticketinfo FROM Tickets t"               . ' JOIN Users u ON t.owner = u.id' . ' JOIN Users u2 ON t.creator = u2.id' . ' JOIN Queues q ON t.queue = q.id'; // added erikvw - end

// If just a single number, treat it as # if ( 1 === count( $args ) ) { if ( preg_match( '/^\d+$/', key($args) ) ) { $ticketnum = key($args); }	}

// Look up a single ticket number if ( $ticketnum ) { $SQL = "$ticketquery AND t.id = $ticketnum"; #$res = pg_query( $dbh, $SQL ); $res = mysql_query( $SQL ); if ( !$res ) { die ( wfMsg( 'rt-badquery' ) ); }		$info = mysql_fetch_array( $res ); if ( !$info ) { return " RT #$ticketnum "; }		return rtFancyLink( $info, $args, $parser, 0 ); }

// Add in a LIMIT clause if l=xx was used $limit = ''; if ( array_key_exists( 'l', $args ) ) { $limit = trim( $args['l'] ); if ( !preg_match( '/^ *\d+ *$/', $limit ) ) { die ( wfMsg ( 'rt-badlimit', $limit ) ); }		$limit = " LIMIT $limit"; }

// Change the default ORDER BY clause if ob=xx was used $orderby = 'ORDER BY t.lastupdated DESC, t.id'; $valid_orderby = array (		 'id'         => 't.id',		 'subject'     => 't.subject',		 'priority'    => 't.priority',		 'status'      => 't.status',		 'queue'       => 'q.name',		 'owner'       => 'COALESCE(u.realname, u.name)',		 'creator'     => 'COALESCE(u2.realname, u2.name)',		 'lastupdated' => 't.lastupdated',		 'created'     => 't.created',		 'resolved'    => 't.resolved',		 ); if ( array_key_exists( 'ob', $args ) ) { $orderby = 'ORDER BY'; $orderbyargs = trim( strtolower( $args['ob'] ) ); foreach ( preg_split( '/\s*,\s*/', $orderbyargs ) as $word ) { $oldlen = strlen( $word ); $word = ltrim( $word, '!' ); $mod = $oldlen !== strlen( $word ) ? ' DESC' : ''; if ( !preg_match( '/^\w+$/', $word ) ) { die ( wfMsg ( 'rt-badorderby', $word ) ); }			if ( array_key_exists( $word, $valid_orderby ) ) { $word = $valid_orderby[$word]; }			else if ( !preg_match ('/^\d+$/', $word ) ) { die ( wfMsg ( 'rt-badorderby', $word ) ); }			$orderby .= " $word$mod,"; }		$orderby = rtrim( $orderby, ',' ); }

// Determine what status to use. Default is new and open: $searchstatus = "AND t.status IN ('new','open')"; $valid_status = array( 'new', 'open', 'resolved', 'deleted', 'stalled', 'rejected' ); if ( array_key_exists( 's', $args ) ) { $statusargs = trim( strtolower( $args['s'] ) ); if ( $statusargs === 'all' ) { $searchstatus = ''; }		else { $searchstatus = 'AND t.status IN (';			foreach ( preg_split( '/\s*,\s*/', $statusargs ) as $word ) {				if ( !in_array( $word, $valid_status ) ) {					die ( wfMsg ( 'rt-badstatus', $word ) );				}				$searchstatus .= "'$word',";			}			$searchstatus = preg_replace( '/.$/', ')', $searchstatus ); }	}

// See if we are limiting to one or more queues $searchq = ''; if ( array_key_exists('q', $args ) ) { $qargs = trim( strtolower( $args['q'] ) ); $searchq = 'AND LOWER(q.name) IN (';		foreach ( preg_split( '/\s*,\s*/', $qargs ) as $word ) {			$word = trim( $word );			if ( !preg_match( '/^[\w \.-]+$/', $word ) ) {				die ( wfMsg ( 'rt-badqueue', $word ) );			}			$searchq .= "'$word',";		}		$searchq = preg_replace( '/.$/', ')', $searchq ); }

// See if we are limiting to one or more owners $searchowner = ''; if ( array_key_exists('o', $args ) ) { $oargs = trim( strtolower( $args['o'] ) ); $searchowner = 'AND LOWER(u.name) IN (';		foreach ( preg_split( '/\s*,\s*/', $oargs ) as $word ) {			$word = trim( $word );			if ( !preg_match( '/^[\w\@\.\-\:\/]+$/', $word ) ) {				die ( wfMsg ( 'rt-badowner', $word ) );			}			$searchowner .= "'$word',";		}		$searchowner = preg_replace( '/.$/', ')', $searchowner ); }

// Build and run the final query $SQL = "$ticketquery $searchq $searchowner $searchstatus $orderby $limit"; ####$res = pg_query( $dbh, $SQL ); // erikvw add mysql_query - begin $res = mysql_query( $SQL ); // erikvw add mysql_query - begin

if ( !$res ) { die ( wfMsg( 'rt-badquery' ) ); }	####$info = pg_fetch_all( $res ); // erikvw add mysql fetch - begin ####$info = mysql_fetch_all( $res ); while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) { $info[] = $row; $i++; }	// erikvw add mysql fetch - end

if ( !$info ) { $msg = wfMsg( 'rt-nomatches' ); return " "; }

// Figure out what columns to show // Anything specifically requested is shown // Everything else is either on or off by default, but can be overidden $output = '';

// The queue: show by default unless searching a single queue $showqueue = 1; if ( array_key_exists('noqueue', $args )		|| ($searchq && false === strpos( $searchq, ',' ) && !array_key_exists( 'queue', $args ) ) ) { $showqueue = 0; }

// The owner: show by default unless searching a single owner $showowner = 1; if ( array_key_exists( 'noowner', $args )		|| ( $searchowner && false === strpos( $searchowner, ',' ) && !array_key_exists( 'owner', $args ) ) ) { $showowner = 0; }

// The status: show by default unless searching a single status $showstatus = 1; if ( array_key_exists( 'nostatus', $args )		|| ( false === strpos($searchstatus, ',' ) && !array_key_exists( 'status', $args ) ) ) { $showstatus = 0; }

// Things we always show unless told not to: $showsubject = ! array_key_exists( 'nosubject', $args ); $showupdated = ! array_key_exists( 'noupdated', $args ); $showticket = ! array_key_exists( 'noticket', $args );

// Things we don't show unless asked to: $showpriority = array_key_exists( 'priority',  $args ); $showupdated2 = array_key_exists( 'updated2',  $args ); $showcreated  = array_key_exists( 'created',   $args ); $showcreated2 = array_key_exists( 'created2',  $args ); $showresolved = array_key_exists( 'resolved',  $args ); $showresolved2 = array_key_exists( 'resolved2', $args ); $showage      = array_key_exists( 'age',       $args );

// Unless 'tablerows' has been set, output the table and header tags if ( !array_key_exists( 'tablerows',$args ) ) {

//changed erikvw. like wikitable and sortable #### $output = " ';	}

return $output; }

function rtFancyLink( $row, $args, $parser, $istable ) {

global $wgRequestTracker_URL, $wgRequestTracker_Formats, $wgRequestTracker_Useballoons;

$ticketnum = $row['id']; //changed erikvw. i like it a bit simpler #### $ret = "[$wgRequestTracker_URL=$ticketnum RT #$ticketnum]"; $ret = "[$wgRequestTracker_URL=$ticketnum $ticketnum]";

## Check for any custom format args in the rt tag. ## If any are found, use that and ignore any other args $foundformat = 0; foreach ( array_keys( $args ) as $val ) { if ( array_key_exists( $val, $wgRequestTracker_Formats ) ) { $format = $wgRequestTracker_Formats[$val]; foreach ( array_keys( $row ) as $rev ) { $format = str_replace( "?$rev?", "$row[$rev]", $format ); }			$ret .= " $format"; $foundformat = 1; break; }	}

## Process any column-based args to the rt tag if ( !$foundformat and !$istable ) { foreach ( array_keys( $args ) as $val ) { if ( array_key_exists( $val, $row ) ) { $format = $args[$val]; if ( false === strpos( $format, '?' ) ) { $showname = $val === 'lastupdated' ? 'Last updated' : ucfirst( $val ); $ret .= " $showname: $row[$val]"; }				else { $ret .= " ". str_replace( '?', $row[$val], $format ); }			}		}	}

$ret = $parser->recursiveTagParse( $ret );

// Not using balloons? Just return the current text if ( !$wgRequestTracker_Useballoons || array_key_exists( 'noballoon', $args ) ) { return " $ret "; }

$safesub = preg_replace( '/\"/', '\"', $row['subject'] ); $safesub = preg_replace( '/\'/', "\'", $safesub ); $safesub = htmlspecialchars( $safesub );

$safeowner = $row['owner']; if ($row['owner'] !== $row['username']) { $safeowner .= " ($row[username])"; }	$safeowner = preg_replace( '/\"/', '\"', $safeowner ); $safeowner = preg_replace( '/\'/', "\'", $safeowner ); $safeowner = htmlspecialchars( $safeowner );

$safeq = preg_replace( '/\"/', '\"', $row['queue'] ); $safeq = preg_replace( '/\'/', "\'", $safeq ); $safeq = htmlspecialchars( $safeq );

$text = "RT #$ticketnum"; $text .= " Status: $row[status]"; $text .= " Subject: $safesub"; $text .= " Owner: $safeowner"; $text .= " Queue: $safeq"; $text .= " Created: $row[created]"; if ( $row['status'] === 'Resolved' ) { $text .= " Resolved: $row[resolved]"; }	else { $text .= " Last updated: $row[lastupdated]"; }

## Prepare some balloon-tek $link  = isset( $args['link'] )   ? $args['link']  : ''; $target = isset( $args['target'] ) ? $args['target'] : ''; $sticky = isset( $args['sticky'] ) ? $args['sticky'] : '0'; $width = isset( $args['width'] )  ? $args['width'] : '0';

$event = isset( $args['click'] ) && $args['click'] && !$link ? 'onclick' : 'onmouseover'; $event2 = ''; $event = "$event=\"balloon.showTooltip(event,'${text}',${sticky},${width})\"";

if ( preg_match( '/onclick/',$event ) && $args['hover'] ) { $event2 = " onmouseover=\"balloon.showTooltip(event,'" . $args['hover'] . "',0,${width})\""; }

$has_style = isset( $args['style'] ) && $args['style']; $style = "style=\"" . ($has_style ? $args['style'] . ";cursor:pointer\"" : "cursor:pointer\"");	$target = $target ? "target=${target}" : '';	$output = "$ret ";

return $output; }

$rtDate = gmdate( 'YmdHis', @filemtime( __FILE__ ) ); $wgCacheEpoch = max( $wgCacheEpoch, $rtDate );

Hack for mysql works great thanks! --198.187.27.5 18:18, 17 June 2011 (UTC)