How to effectively manipulate BCP-packages

Today, I’d like to show you a new way to manipulate Vault BCP-packages. BCP is a brilliant little tool that allows you to export and import data from and to Vault. We frequently use it for migrating from different systems to Vault or for merging Vaults together. As the import of a BCP-package might take some hours, we developed an application, called bcpChecker, that shows the content of such a package in a Vault-similar user interface. This way, you can test the structure and content of the package before running the import. You can even review with your customer the result of the import before running it!

Vault-bcpChecker

The bcpChecker has some more cool features such as checking if all linked files are accessible, or report all used behaviors, or transform a BCP-package into a dummy package, so that it can be imported without files.

A secret of the bcpChecker is, that when you open a BCP-package, the complete content of such package is imported into a little, local SQL database file. In fact, in the folder of your BCP-package, you will find a file called bcpChecker.sdf. This is done in order to deal with even very large packages, and it provides superlative possibilities for manipulating BCP-packages via SQL statements.

In the following example, I will manipulate a sample assembly (PadLock), which has been imported via Autoloader. The files in Vault are all in category base and have no lifecycle or state. Via some SQL statements I will add 2 new folders (Parts and Drawings), “move” the files from the folders External and Internal into the 2 new folders, set the files to the category Engineering with according lifecycle and state, and update the user defined property Company to the value “coolOrange”. This is how it looks like:

before-after

And here are the SQL statements:

--create 2 additional folders, called Parts and Drawings
--folder objects requires the Created child component, so also create according Created entries
--SELECT * FROM Folder
INSERT INTO Folder (EntityID, ParentID, ParentTable, Name, IsLibrary, Category, cO_Value) VALUES (9,3,'Folder','Parts',null,'Folder','')
Go
INSERT INTO Folder (EntityID, ParentID, ParentTable, Name, IsLibrary, Category, cO_Value) VALUES (10,3,'Folder','Drawings',null,'Folder','')
Go
INSERT INTO Created (EntityID, ParentID, ParentTable, User, Date, cO_Value) VALUES (54,9,'Folder','Administrator', '2014-05-20T00:00:00.000Z', '')
Go
INSERT INTO Created (EntityID, ParentID, ParentTable, User, Date, cO_Value) VALUES (55,10,'Folder','Administrator', '2014-05-20T00:00:00.000Z', '')
Go
--SELECT * FROM File WHERE ParentID in (5,6) AND ParentTable = 'Folder'
--SELECT * FROM File WHERE ParentID in (9,10) AND ParentTable = 'Folder'
--set the categorty to engineering just for the files in the folders calles External and Internal with the ID 5 and 6
UPDATE File SET Category='Engineering' WHERE ParentID in (5,6) AND ParentTable = 'Folder'
Go
--SELECT * FROM State
--as in our package all the files have no state, the column Definition is missing. So create the column and populate it just for the files we will later move
ALTER TABLE State ADD COLUMN Definition VARCHAR(1000)
Go
INSERT INTO State (ParentID, ParentTable, Name, Definition)
SELECT EntityID, 'Iteration', 'Work in Progress', 'Flexible Release Process' FROM Iteration WHERE ParentTable='Revision' AND ParentID IN (
  SELECT EntityID FROM REVISION WHERE ParentTable='File' AND ParentID IN (
    SELECT EntityID FROM FILE WHERE ParentID in (9,10) AND ParentTable = 'Folder'
    )
  )
Go
--update the EntityID with progressive number. OID = row number
UPDATE State SET EntityID=OID WHERE EntityID IS NULL
GO
--SELECT * FROM Revision
--set the revision scheme and revision for the according files
ALTER TABLE Revision ADD COLUMN Definition VARCHAR(1000)
Go
ALTER TABLE Revision ADD COLUMN Label VARCHAR(1000)
Go
UPDATE Revision SET Definition='Standard Alphabetic Format', Label='A' WHERE ParentID in (SELECT EntityID FROM FILE WHERE ParentID in (9,10) AND ParentTable = 'Folder')
Go
--'move' the files from the folders 5 and 6, External and Internal, to the folder 9 and 10
UPDATE File SET ParentID='9' WHERE ParentID in (5,6) AND ParentTable = 'Folder' AND Name like '%.ipt'
Go
UPDATE File SET ParentID='10' WHERE ParentID in (5,6) AND ParentTable = 'Folder' AND Name like '%.idw'
Go
--SELECT * FROM UDP WHERE Name='Company'
--update the Company user defined property to the value 'coolOrange'
UPDATE UDP SET cO_Value = 'coolOrange' WHERE Name='Company' AND ParentTable='Iteration' AND ParentID IN (
  SELECT EntityID FROM ITERATION WHERE ParentTable='Revision' AND ParentID IN (
    SELECT EntityID FROM REVISION WHERE ParentTable='File' AND ParentID IN (
      SELECT EntityID FROM FILE WHERE ParentID in (9,10) AND ParentTable = 'Folder'
      )
    )
  )
Go

In order to better understand how this works, let me explain you the structure of the SQL database. The bcpChecker basically imports the XML files from the BCP-package. Now, in the XML files you will find elements, such as Root, Folder, File, Revision, Iteration, UDP, etc. Each element corresponds to a SQL table with the same name. Each XML element might have attributes, such as Name, IsLibrary, Category, etc. Each attribute is a column of the according table. Pretty simple!

BCP2SQL

So, if you have some experience with the BCP XML format, you will find according tables and columns in the database. If you don’t have BCP experience, well, then you will quickly learn some relevant tables such as File, Revision, Iteration and Folder. In both cases, it’s not complicated.

The beauty of having the XML files in a database is that with a simple UPDATE <TableName> SET <Column>=<Value> you can change thousands of records in less than a second!!! Additionally, you can create your own SQL script file and repeat and optimize the manipulation as many times you like!

So, the bcpChecker reads the BCP-package into a local SQL database file. Via File>Save As… you can export such content again to a BCP-package!

In order to manipulate the database, you can either open it via the SQL Server Management Studio or like in our case, I’d like to introduce you to a very handy and cool product called LINQPad (www.linqpad.net). The tool allows writing code in several languages and it’s quite known to whom uses LINQ expressions. However, in our case we use it for opening and querying our database. So, download LINQPad and add a new connection.

LINQPad-Connection

You have to add an additional driver in order to open this type of SQL database, so click on “View more drivers…” and add the driver. Now you are able to select SQLite ad provider, pick the .sdf file and open the database.
LINQPad
As you can see on the left hand side, you can expand the database structure and navigate through all the tables. If you now open the padlock.linq file or copy&paste the statements above, you can read, write and update the data for this database.

Now you are set and ready to do whatever you like with your BCP-package. Have fun!!!

If you like to test these steps yourself, download this zip file containing the BCP package before and after the manipulation and the according .linq file with the SQL statements.

This entry was posted in Vault API, Vault BCP. Bookmark the permalink.

One Response to How to effectively manipulate BCP-packages

  1. Pingback: New version of bcpChecker available | coolorange

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s