Getting more from Trac Notification
Mar 10th, 2007 by paolo
Trac has a great module to support notification about ticket changes via email. Starting from the 0.10 version of Trac the TracNotification module has some new configuration options that give to the administrator or project manager a good control on who should be notified and when.
By the way Trac lacks, in my opinion, of a simple way to associate an email address (useful to handle notifications) to a user. In fact the only way to this is to ask to each users to define its own email address in the Settings panel. There is currently no way for an administrator to populate email address of other users. The lack is even worse if you have to manage more projects where you should have to ask your users to define their email address for each project.
I think this is annoying so I've made some code to solve this problem in an environment with more than one Trac projects.
The idea is to store users settings in a unique csv file and to create a script that add these information into the Trac project dbs macthing on the username (sid - session identifier).
First we have to create the csv file with the settings for all our users. We'll make a file with a line for each user and for each user we'll specify the username, the full-name and the email address. Something like the following:
-
paolino;Paolino Paperino;paolino@paperino.org
-
paolo;Paolo Casarini;paolo@mydomain.org
-
...
Then the code. I've made it in PHP5, quick enough for my purposes.
TracUser.php
We need a class (it is not really necessary but I think it could be useful to make the code more readable) to map the user settings. This class has a private constructor and to create new instances I've made a factory method that parse a csv line of our input file.
-
class TracUser {
-
private $_username;
-
private $_fullName;
-
private $_email;
-
-
/**
-
* Private constructor.
-
*
-
* @param string $username The username used from the user to log in in Trac
-
* @param string $fullName The full name associated to this user (for ex. Paolo Casarini)
-
* @param string $email The email address associated to this user (for ex. paolo@mydomain.com)
-
*/
-
private function __construct($username, $fullName, $email) {
-
}
-
-
/**
-
* Factory method given a CSV string that contains the values to build a TracUser instance.
-
*
-
* @param string $csvString a CSV string that contains the values to build a TracUser instance.
-
* @return TracUser the corresponding TracUser
-
* @exception Exception if the CSV string specified is malformed
-
*/
-
throw new Exception("Parsing Error: 3 fields expected (for ex. username:full name:email)");
-
}
-
-
return new TracUser($fields[0], $fields[1], $fields[2]);
-
}
-
-
public function getUsername() {
-
return $this->_username;
-
}
-
public function getFullName() {
-
return $this->_fullName;
-
}
-
public function getEmail() {
-
return $this->_email;
-
}
-
}
build-trac-users-metadata.php
Now we have to create the script to read user settings from the csv file and put them in a specified Trac db. The first part of the script is very simple. Some checks on parameters and the definition of the $csvFilename and $dbFilename variables:
-
#!/usr/bin/php -q
-
<?php
-
require_once("TracUser.php");
-
-
// Configure this
-
?>
-
This is a command line PHP script with two options
-
-
Usage:
-
-
<?php
-
die;
-
}
-
-
$csvFilename = $argv[1];
-
$dbFilename = $argv[2];
Then we have to read users settings from the csv file and build a TracUser object for each line. Once the object is built we put it in hash map where the key is the username and the value the corresponding TracUser instance:
-
// Load users metadata from CSV file
-
if ($handle) {
-
if ($buffer) {
-
try {
-
$user = TracUser::createFromCsvRow($buffer);
-
$users[$user->getUsername()] = $user;
-
} catch(Exception $e) {
-
}
-
}
-
}
-
}
As last task we have to put our data in the Trac db. In PHP5 the right way to deal with sqlite3 is throught the PDO lib that is a good choice also if your Trac db in on a Postgresql DBMS.
-
// create a SQLite3 database file with PDO and return a database handle
-
try{
-
$dbHandle = new PDO('sqlite:' . $dbFilename);
-
} catch( PDOException $exception ){
-
}
-
-
// insert session user metadata into the db
-
try {
-
$sql = 'SELECT sid FROM session WHERE authenticated = 1';
-
$stm = $dbHandle->prepare($sql);
-
$stm->execute();
-
while ($row = $stm->fetch()) {
-
if ($user = $users[$row['sid']]) {
-
-
// name
-
$stm2 = $dbHandle->prepare("SELECT 1 FROM session_attribute WHERE sid = :username AND authenticated = 1 AND name = 'name'");
-
$stm2->bindValue(':username', $user->getUsername(), PDO::PARAM_STR);
-
$stm2->execute();
-
if ($stm2->fetch()) {
-
echo "Updating full name metadata for " . $user->getUsername() . " with " . $user->getFullName() . "\n";
-
$stm2 = $dbHandle->prepare("UPDATE session_attribute SET value = :value WHERE sid = :username AND authenticated = 1 AND name = 'name'");
-
$stm2->bindValue(':username', $user->getUsername(), PDO::PARAM_STR);
-
$stm2->bindValue(':value', $user->getFullName(), PDO::PARAM_STR);
-
$stm2->execute();
-
} else {
-
echo "Inserting full name metadata for " . $user->getUsername() . " with " . $user->getFullName() . "\n";
-
$stm2 = $dbHandle->prepare("INSERT INTO session_attribute (sid, authenticated, name, value) VALUES(:username, 1, 'name', :value)");
-
$stm2->bindValue(':username', $user->getUsername(), PDO::PARAM_STR);
-
$stm2->bindValue(':value', $user->getFullName(), PDO::PARAM_STR);
-
$stm2->execute();
-
}
-
-
// email
-
$stm2 = $dbHandle->prepare("SELECT 1 FROM session_attribute WHERE sid = :username AND authenticated = 1 AND name = 'email'");
-
$stm2->bindValue(':username', $user->getUsername(), PDO::PARAM_STR);
-
$stm2->execute();
-
if ($stm2->fetch()) {
-
$stm2 = $dbHandle->prepare("UPDATE session_attribute SET value = :value WHERE sid = :username AND authenticated = 1 AND name = 'email'");
-
$stm2->bindValue(':username', $user->getUsername(), PDO::PARAM_STR);
-
$stm2->bindValue(':value', $user->getEmail(), PDO::PARAM_STR);
-
$stm2->execute();
-
} else {
-
$stm2 = $dbHandle->prepare("INSERT INTO session_attribute (sid, authenticated, name, value) VALUES(:username, 1, 'email', :value)");
-
$stm2->bindValue(':username', $user->getUsername(), PDO::PARAM_STR);
-
$stm2->bindValue(':value', $user->getEmail(), PDO::PARAM_STR);
-
$stm2->execute();
-
}
-
}
-
}
-
} catch( PDOException $e){
-
}
