Intralink SQL: Changing Revision/Version with Oracle SQL
A PTC/User post of mine from April 2003
"Mongilio, Michael" wrote:
Does anyone know if it is possible to "successfully" change an objects revision to an earlier revision through Oracle? I have an object with two revisions "F" and "G" in the database. They should be "B" and "C". It would be a real hassle to delete both revisions and put them back in (especially since they are instances on a family table).
The following is a procedure to do what you asked for. The changes may work successfully, or they may not. Use them at your own risk. Definitely try them on a test server before messing with your production server.
To see all revisions (and all branches) of 'abc.prt':
set linesize 120column PINAME format a35column BRPATH format a20column PIVREV format a6select piv.PIVID, piv.PIVCLASS, pi.PINAME, br.BRPATH, piv.PIVREV, piv.PIVVERfrom pdm.PDM_PRODUCTITEMVERSION piv, pdm.PDM_BRANCH br, pdm.PDM_PRODUCTITEM piwhere pi.PIID=br.PIID and piv.BRID=br.BRID and pi.PINAME='abc.prt';
Note: The value of PIVCLASS is 0, 1, or 2
0: Non family table objects
To change the 'main' branch Revision/Version of 'abc.prt' from
'B.0' to 'A.3':
update pdm.PDM_PRODUCTITEMVERSIONset PIVREV='A', PIVVER=3, MODIFIEDON=sysdatewhere PIVID IN ( select piv.PIVID from pdm.PDM_PRODUCTITEMVERSION piv, pdm.PDM_BRANCH br, pdm.PDM_PRODUCTITEM pi where piv.BRID=br.BRID and pi.PIID=br.PIID and pi.PINAME='abc.prt' and piv.PIVREV='B' and piv.PIVVER=0 and br.BRPATH='main' );
In this example, the MODIFIEDON column is updated as well as the Revision/Version. If this does not occur, Intralink clients that have the revision cached (displayed in a browser window) get very confused and generate error messages, since they don't know to update from the Commonspace. The column is a 'freshness' timestamp that the client uses to determine whether its cached data is current or out of date.
Also, be sure that you are updating the correct branch. You may have multiple files at 0.0, but only one will be in the 'main' branch.
I'm sure what I am asking is not recommended, but...
Highly not recommended, but possible. If you are not careful, it is easy to make two revisions both have the same Revision/Version (i.e. both could have 2.1), to change the order (i.e. 4.0 becomes 3.6 and 4.1 becomes 3.5), and to set a Revision to something that is not in the list of valid revisions. Trying to do any of these is a really bad idea.
Changing these values also causes some issues with workspaces that have the files checked-out. The revision info in the workspace will no longer correspond to the revision info for the same version in the commonspace, unless the file is checked-out again.
Some unusual conflicts may be produced upon check-in, as later versions may now be present in the commonspace even though the workspace version really is the latest. These conflicts can be overridden with apparently no problems.