Script to import PVCS Tracker project into Trac

[terje@…]

Script to import PVCS/Merant/Serena Tracker projects into Trac

I and some of my customers have been using PVCS Tracker for some years, but the product has now fossilated due to hostile mergers of the owning companies. It's a pity for Tracker but I'm now a Trac fan instead so I'm OK.

The need for at conversion script occurs when trying to preserve the knowledge contained in existing Tracker projects containing thousands of tickets and GB of data. A somehow automated process would help a lot, and avoid waisting lots of accumulated experience with the related products. I do think too that various programming communities has made great use of the Tracker product through the years, so I'm probably not alone wishing for a solution here.

All necessary resources should actuallty be present for at solution. Tracker has both at web interface and a "close-to-the-database" API as alternatives to dabbling around in the proprietary Tracker db. ASFAIK Trac has every necessary ability to receive the information contained in Tracker projects.

My priority list is:

1. Transfer of Tracker SCRs to Trac tickets, including comments an attachments
   

2. Transfer of Tracker Queries to Trac queries
   

3. Transfer of Tracker users to Trac users
   

Priority 1 is of course the major part, while 2 and 3 are nice-to-haves but can with reasonable effort be done also by hand for each project.

Please email me if you like to discuss ideas, etc.: terje at solida dot se

Terje Jönsson

[anonymous]

Looks like you didn't a reply for some time. Is this still a requirement from you or did you find a solution?

I've also been wanting to do this. Exprting crashes so I've taken to extracting the database data directly although I'm having trouble with date formats for changes to a tracker job. The format seems a little weird as its a number e.g. 1145875908

24/4/2006 11:51:48, 1175176525 = 29/03/2007 14:55:25, etc. I tried splitting the number into 2 halves of 5 digits each. Left one looks like date in days from 1975 and right looks like a variation time of seconds but a later time does not always cause a larger number? Although accuracy of this alters through the each year! Anyone recognise the format? Otherwise coversion seems doable.

[terje@…]

We didn't get any hints from others so we made a joint effort together with a customer who wantes to do the same thing. I will post a short description here today.

[terje@…]

Porting an existing PVCS/Merant/Serena Tracker project to Trac

We successfully ported an existing Tracker projekt to Trac, and here's a brief description of the key issues.

Scope

Existing project

• Server: W2K
• DB: SQL Server 6
• Web server: IIS 6
• Tracker 8.0.2

Target environment

• Server: Gentoo Linux
• DB: SQLite
• Web server: Apache 2
• Trac 0.9.6

Principle

We installed the target environment in the standard fashion and had it up and running. Then we made a database transfer from the Tracker DB to the Trac DB.

Database transfer

We made a database export from SQL Server and manually transferred the data to the Trac DB with SQL statements in SQLite. Unfortunately we didn't keep a strict log of our operations here, but most of it was straight-forward. You need to keep your attention to a couple of things, though:

• attached files (further described below)
• some text fields (e.g. user names) seemed to get extra space characters, and this had to be corrected manually

Moving attached files from Tracker to Trac

In Tracker (SQL Server), attached files are stored as binary objects (BLOB). In Trac, the files are stored as regular files in a directory structure where each ticket stores their attached files in a folder named by the ticket number.

Basic stucture of the Tracker database, table: trkfile (only the interesting columns shown here)

fileRecId  int       ticket nbr
fileName   varchar   filename incl. path
fileDate   int       integer date/time
fileCont   image     BLOB (the file itself)

fileRecId is converted to a string and is the name of the folder where the file should be copied
fileName is stripped off the path so that only the filename remains
fileDate is used to determine the latest version of a file in case there are more than one file with the same name (Tracker does support this, Tracker does not)
fileCont is read binary and written to a file with filename and folder as described above (from fileName)

Filename problems:

1. the path has to be removed (see fileName above).
   
2. the target environment (in our case) does not handle filenames in the same way as Windows/SQL Server. All special characters (e.g. space, #, éèáàåäö, etc.) are stored in hexadecimal equivalents, e.g. %20 for space. All special characters have to be transformed before the file is written to its target folder.
   
3. Tracker supports several files with the same name in each ticket, whereas in Trac the filenames must be unique as they are stored in the same folder. We used two different approaches to this challange. In one project we stored only the latest file and threw away the others. In another project we appended the fileDate integer to the filename to create truly unique file names.

How to transfer attached files from a Tracker DB to a Trac directory structure

1. set up an ODBC connection to the SQL Server DB where Tracker is located
2. allocate memory for the necessary variables needed for the ODBC connection, SQL queries, etc.
3. run a SQL query that retrieves file data as described above
4. bind columns in the data (except the BLOB) to variables in the application
5. strip off the path and replace all special characters
6. assemble the new path to the target file
7. get the file size, create the file, and transfer the data (4000 bytes/read) to the file

When all rows from the query have been processed, then the new directory structure is ready and all files are stored in their new location. The attachment folders (with names like ticket numbers) are then transferred to the /trac/attachments/ticket folder. Done!

[terje@…]

Replying to anonymous:

Looks like you didn't a reply for some time. Is this still a requirement from you or did you find a solution?

In fact we "solved" it by hand, see my description in this ticket.

I've also been wanting to do this. Exprting crashes so I've taken to extracting the database data directly although I'm having trouble with date formats for changes to a tracker job. The format seems a little weird ...

We didn't experience any date format issues, but we had to deal with a few other environment-related things, like character encoding in file names

[anonymous]

Thanks a lot for the information. Especially the file attachment issues.

My Tracker database is in Oracle 8 so I'll keep trying with the date formats.

Thanks for the help.

Alan

[anonymous]

CREATE OR REPLACE PACKAGE BODY PVCS_UTILS AS

FUNCTION CONVERT_PVCS_DATE(pNumber IN NUMBER) RETURN DATE IS BEGIN

IF pNumber = 0 THEN

RETURN NULL;

ELSE

RETURN TO_DATE('1/1/1970', 'DD/MM/YYYY')+(TO_NUMBER(pNumber)/60/60/24);

END IF;

END;

END;