Community
Participate
Working Groups
For my sins I am forced to use PVCS Tracker (version 7?) for defect and request management. A connector would be a most welcome addition to my tool set.
James: please vote for this bug, and others should do the same if interested. Also, it could make sense to contact Serena about your interest in the support.
I have tried to contact Serena support to get some guidance on this. Unfortunately the support license for the version of PVCS Tracker I had installed (7.0.10) when the support team called back expired last year. We also have version 7.0.15 with an unused 4-user license, so I've installed that and responded to the ticket to this effect in the hope that the license for that version will still entitle us to support. I got a mail saying that they'll get back to me on Monday. In the meantime, I've noted that our installation uses a MS SQL Server (2000 I think) database to store the Tracker data. We have in the past managed to reverse-engineer some of the tables to perform certain unsupported actions (e.g. moving comments from one ticket to another when incorrectly entered) so we may be able to do the same in order to create a plug-in. We are also investigating the possibility of migrating to Trac for ticket tracking, so doing so would serve a dual purpose for us as well as potentially helping out the community.
James: you might also be interested in bug 184532.
That's a great call, I've added myself to the CC list for that bug. The feedback I've had from Serena is as follows: -----8<----- short answer is probably [a connector would be possible -- JT], but they would need to write their own connector. I cannot see any evidence anywhere that there is a connector for Tracker (GUI). There is a generic Mylyn connector for web-based "repositories", that might be usable for Tracker I-Net. (They can easily make the interface not acceissble form standard I-Net clients.) However, if they have to have the GUI TTK (toolkit) access, this would mean writing the thing themselves, bearing in mind that the Tracker TTK is written in C and not Java ! Best bet would be TeamTrack. -----8<----- Since we have no intention of upgrading to TeamTrack (confirmed today by my manager) I suspect the generic SQL connector is going to be the way for us to go.
(In reply to comment #4) > That's a great call, I've added myself to the CC list for that bug. Maybe try and add some description of your reverse engineered SQL Server database as well. Need that to see how it stores for example tasks, comments on tasks and attachments.
I've spent quite a significant amount of time reverse-engineering our Tracker (7.0.15) DB Schema. It's a little complicated and what I've got is almost certainly specific in many ways to our implementation, but it might just help. trkmaster schema ================ Seems to define lots of server-level stuff that is applicable to all repositories. Of particular interest to us is the trkprj table: prjId: int(4) NULL prjDbName: char(254) NULL prjDescr: char(32) NULL prjParm1: int(4) NULL prjParm2: int(4) NULL prjParm3: int(4) NULL prjParm4: int(4) NULL prjParm5: int(4) NULL prjParm6: int(4) NULL prjParm7: int(4) NULL prjParm8: int(4) NULL prjDescr is the name of the project as shown in the PVCS Tracker "Tracker Projects" view. prjDbName is the name of the database that contains the tickets. The schema of that database is larger than I would have expected -- 31 tables -- but most of the interesting stuff is relatively transparent. I found it interesting to note that there were no foreign/primary key constraints on my database and all fields were nullable, but that may have been as a result of the way it was copied. Repository Schema ================= Table trkusr ------------ usrId: int(4) usrClass: int(4) usrFlags: int(4) usrLoginId: nvarchar(32) usrFullNm: nvarchar(64) usrTel: nvarchar(64) usrEmail: nvarchar(128) usrPwd: nvarchar(72) usrComment: nvarchar(254) usrLstLid: int(4) usrNfyTid: int(4) usrMailTid: int(4) usrViewTid: int(4) usrMailOpt: int(4) usrSrvMsg: int(4) usrProjMsg: int(4) usrParm1: int(4) usrParm2: int(4) usrParm3: int(4) usrParm4: int(4) usrParm5: int(4) usrParm6: int(4) usrParm7: int(4) usrParm8: int(4) It's all pretty self-explanatory. Username is in usrLoginId; note that this is space-padded to the full field width. Password is encoded in usrPwd; I haven't performed any analysis on the encoding method. Table trkscrst (SCR State?) --------------------------- id: int(4) -- SCR ID, shown in ID field of Tracker z1: nvarchar(254) -- Title z2: ntext(16) -- Description z3: nvarchar(254) -- The rest are String fields for this ticket z4: nvarchar(254) z5: nvarchar(254) z6: nvarchar(254) z7: nvarchar(254) z8: nvarchar(254) z9: nvarchar(254) z10: nvarchar(254) z11: nvarchar(254) z12: nvarchar(254) Table trkscrsl (SCR Slots?) --------------------------- id: int(4) -- References trkscrst.id x1: int(4) y1: int(4) ... ... x28: int(4) y28: int(4) Table trkfld (Fields) --------------------- fldId: int(4) fldClass: int(4) -- Field type (1=Choice; 2=String; 3=Number; -- 4=Date; 5=Submitter; 6=Owner) fldFlags: int(4) -- No idea fldVid: int(4) -- Seems like some sort of ID fldTypeId: int(4) -- Field type -- references trktype table fldLen: int(4) -- Length fldStorLen: int(4) -- No idea, seems to be zero for us fldSlot: int(4) -- Defines where to find the data for this field fldIndex: int(4) -- Boolean (1/0): Index this field? fldActive: int(4) -- Boolean (1/0): Is this field active? fldLabel: nvarchar(32) -- Field label fldDep1: int(4) fldDep2: int(4) fldDep3: int(4) fldDep4: int(4) fldParm1: int(4) -- Default value fldParm2: int(4) -- Min value fldParm3: int(4) -- Max value fldParm4: int(4) fldParm5: int(4) fldParm6: int(4) fldParm7: int(4) fldParm8: int(4) This warrants some explanation. String values for tickets are stored in the trkscrst table. Numeric values (anything that isn't a string) are stored in the trkscrsl table. Choice labels come from the trkch table. For field type 4 it is seconds since 1970-01-01 00:00:00. For field types 5 and 6 trkusr.usrId is referenced. The column in trkscrst or trkscrsl in which the data is stored determines the label by way of trkfield.fldslot, e.g.: trkscrst.id=1 trkscrst.z1=Some Bug trkscrsl.x14=123 trkfld.fldSlot=1 trkfld.fldLabel=Title trkfld.fldSlot=14 trkfld.fldLabel=Estimate Hence for ticket #1, the Title is "Some Bug" and the Estimate is 123. I've run out of time for this instalment, but I'll add more about ticket change history, notes and attachments this evening.
Before I go on to change history, notes and attachments, I just want to add a bit more detail about the "Choice" fields. They are really just an enumeration. The trkch table looks like this: Table trkch ----------- chId: int(4) chClass: int(4) chFlags: int(4) chLabel: nvarchar(32) chFldId: int(4) -- References trkfld.fldId chOrder: int(4) -- Order of this value in the drop-down list chValue: int(4) -- The value stored in trkscrsl chParm1: int(4) chParm2: int(4) chParm3: int(4) chParm4: int(4) chParm5: int(4) chParm6: int(4) chParm7: int(4) chParm8: int(4) It should be pretty clear from there how to get from a record in trkscrst to its corresponding row in trkscrsl, to pull out the field label from trkfld and the appropriate display label from trkch. I also want to expand upon the Type fields, which reference the trktype table: Table trktype ------------- typeId: int(4) typeClass: int(4) typeFlags: int(4) typeVid: int(4) typeTbId: int(4) typeParId: int(4) typeLastId: int(4) typePrefix: nvarchar(8) typeShrtNm: nvarchar(32) typeLongNm: nvarchar(64) typeParm1: int(4) typeParm2: int(4) typeParm3: int(4) typeParm4: int(4) typeParm5: int(4) typeParm6: int(4) typeParm7: int(4) typeParm8: int(4) Our repository has two entries here, one for SCRs and one for time entries, although I have no idea where we enter time details. So moving on to change history. This is stored in trkchg: Table trkchg ------------ chgId: int(4) chgClass: int(4) -- 1=Field change; 2=Note change; 3=File change chgFlags: int(4) chgTransId: int(4) -- Transaction ID? chgTableId: int(4) -- References trktype.typeTbId? chgRecId: int(4) -- References trkscrst.id chgUsrId: int(4) -- References trkusr.usrId chgDate: int(4) -- Date in seconds since 1970-01-01 00:00:00 chgPrevX: int(4) -- Previous numeric data chgPrevY: int(4) chgNewX: int(4) -- New numeric data chgNewY: int(4) chgPrevStr: nvarchar(254) -- Previous textual data chgNewStr: nvarchar(254) -- New textual data chgParm1: int(4) chgParm2: int(4) chgParm3: int(4) chgParm4: int(4) chgParm5: int(4) chgParm6: int(4) chgParm7: int(4) chgParm8: int(4) Where the change was a field change, the previous value is stored in either chgPrevX or chgPrevStr and the new value is stored in either chgNewX or chgNewStr. For new notes chgPrevStr is NULL and chgNewStr is the note title. For note changes the fields appear to represent previous and new note titles, although I'm not sure what happens if the title remains the same and the note contents are changed -- perhaps this is where chgFlags and chgParm1 come into play. Notes are stored in trknote: Table trknote ------------- noteId: int(4) noteClass: int(4) noteFlags: int(4) noteAuthId: int(4 -- Note author, references trkusr.usrId noteCrDate: int(4) -- Note creation date in seconds since 1970-01-01 00:00:00 noteMdDate: int(4) -- Note modification date, ditto noteTbId: int(4) -- References trktype.typeTbId noteRecId: int(4) -- References trkscrst.id noteOrder: int(4) noteParm1: int(4) noteParm2: int(4) noteParm3: int(4) noteParm4: int(4) noteParm5: int(4) noteParm6: int(4) noteParm7: int(4) noteParm8: int(4) noteTitle: nvarchar(254) noteText: ntext Self-explanatory really, so we'll move swiftly on to attachments, or files as Tracker calls them: Table trkfile ------------- fileId: int(4) fileClass: int(4) fileFlags: int(4) fileTbId: int(4) -- References trktype.typeTbId fileRecId: int(4) -- References trkscrst.id fileOrder: int(4) fileName: int(254) -- Full pathname of the uploaded file fileUsrId: int(4) -- Who attached it? References trkusr.isrId fileDate: int(4) -- In seconds since 1970-01-01 00:00:00 fileParm1: int(4) fileParm2: int(4) fileParm3: int(4) fileParm4: int(4) fileParm5: int(4) fileParm6: int(4) fileParm7: int(4) fileParm8: int(4) fileCont: image -- The file contents Also very self-explanatory. There are some blanks, like what the various Class and Flags fields mean, what the various ParmX fields are used for. There are also quite a few other tables I haven't mentioned here because I don't think they're relevant. I'm able to try out things on my local dataset to prove things, but I can't share the actual data and I can't share any source code I produce in my day job. Hope this all helps.
A quick note about the trkchg table, because I appear to have been focusing on records here being strictly changes to notes. Where chgClass == 1, trkchg.chgParm1 indicates which ticket field changed, and references trkfld.fldId
OK, I've figured out the encoding used in trkusr.usrPwd -- am I allowed to post the details here?
(In reply to comment #9) > OK, I've figured out the encoding used in trkusr.usrPwd -- am I allowed to post > the details here? I'm not sure what the security implications of posting it are.
My concern exactly. While I'd like to contribute the information to allow a more complete connector to be developed, I'd hate to think that by doing so I'd be compromising other people's sensitive data. That said, if the attacker has access to the database to get the encoded password, they could just as easily get that sensitive data anyway. I guess it only becomes a security issue if they're able to somehow get the Tracker client to show another user's encoded password?
I made a mistake in the trkch table. trkch.chValue refers to the "Weight" of the option; the value entered into trkscrsl comes from trkch.id. Also, in trkfld there is an additional value for fldClass -- 7 indicates a field type of "User".
The Generic SQL connector is sort of ready for consumption: - https://bugs.eclipse.org/bugs/show_bug.cgi?id=184532 - http://wiki.eclipse.org/Mylyn/Incubator/Generic_SQL_Connector Maybe you can try and connect to the SQL using either ibatis or mod of the JPA example provided.
Well I've been off this for a while, but I recently had a request for these details and having considered my previous statement, my feeling is that if an attacker has access to the database anyway, all bets are off. So the details are as follows: The user password is divided into chunks of 9 characters. To encrypt the password, for each chunk, add the following offsets to the ASCII code of each character in the chunk: Index: 0 1 2 3 4 5 6 7 8 Offset: 0x4b, 0x3b, 0x23, 0x55, 0x53, 0x7b, 0x24, 0x41, 0x3d The password is stored as the string representation of the hex character codes. Decryption is obviously the reverse of this procedure. That's it. I haven't looked at passwords with non-ASCII characters in them, but I might take a look later, probably when I get to trying the Generic SQL Connector.
Mylyn has been restructured, and our issue tracking has moved to GitHub [1]. We are closing ~14K Bugzilla issues to give the new team a fresh start. If you feel that this issue is still relevant, please create a new one on GitHub. [1] https://github.com/orgs/eclipse-mylyn
