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 120
column PINAME format a35
column BRPATH format a20
column PIVREV format a6

select
  piv.PIVID,
  piv.PIVCLASS,
  pi.PINAME,
  br.BRPATH,
  piv.PIVREV,
  piv.PIVVER
from
  pdm.PDM_PRODUCTITEMVERSION piv,
  pdm.PDM_BRANCH br,
  pdm.PDM_PRODUCTITEM pi
where
  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
• 1: Instances
• 2: Generics

To change the 'main' branch Revision/Version of 'abc.prt' from
'B.0' to 'A.3':

update pdm.PDM_PRODUCTITEMVERSION
set
  PIVREV='A',
  PIVVER=3,
  MODIFIEDON=sysdate
where
  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.


As long as you're careful, the procedure should not create any major problems.