SQL queries for modifications between labels?

[ismangil]

Hi,

Is it possible to construct SQL queries to do the following:

Given two labels, and a repository folder, list all files that have been modified, created, deleted from the first label up to second label.

The reason I need SQl queries is because I want to incorporate this into our reporting tool, which already does SQL to our issues log and help desk system.

This kind of report would be very useful for release documents.

Thanks.

[jclausius]

Where is the label applied with respect to the folder (is it applied to a parent or a sub-child)?

It would be tough to do, but you could look at comparing results. You'll need tblfsobjectlabels which is the "header" information regarding the label as well as tblfsobjectlabelviews which contain the label's layout.

[ismangil]

Where is the label applied with respect to the folder (is it applied to a parent or a sub-child)?

I would be comparing the folder where the label is actually applied, but I also want to list out recursively all modifications for all sub-child of that folder (that is all sub-child items that inherits the label).

It would be tough to do, but you could look at comparing results. You'll need tblfsobjectlabels which is the "header" information regarding the label as well as tblfsobjectlabelviews which contain the label's layout.

I'll have a look round those tables. Any more hints?

Thanks for the (usual) lightning quick response!

[ismangil]

If I look at tblfsobjectlabels through EntManager, the labeldate field for a label will say 26-05-2005 16:17:13, but through Vault GUI Client History Exploer it would say that the label date is 26-05-2005 17:17:13.

Is this something to do with timezone? Currently UK is UTC+1 due to daylight saving time, and I was wondering whether the db date is UTC, display date is UTC+1 or something like that....

[jeremy_sg]

A quicker way to do this, if you're on 3.0.6 or later is to use the Command Line Client's history command. It can accept labels as the beginning and end dates for the query, and prints an xml output. You can then process the output in code to get the specific information you want. It would save you a lot of time learning the Vault DB layout.

[ismangil]

A quicker way to do this, if you're on 3.0.6 or later is to use the Command Line Client's history command. It can accept labels as the beginning and end dates for the query, and prints an xml output. You can then process the output in code to get the specific information you want. It would save you a lot of time learning the Vault DB layout.

Agreed. We have ditched the SQL idea for now, and I will actually use vaulthistory nant task, probably tweaked to add the same capability as the CLC, such as end date or end label (which I think translate it into a datetime anyway?).

[jeremy_sg]

Yes, it does translate to a datetime. Let us know if you need any help tweaking it.

[ismangil]

Looking for help on the VaultLib.VaultHistoryQueryRequest class...

I can see that this where you actually define the history query, so how do I set a filename mask. I was planning to use ".*" to exclude folders. Or is there a more reliable way to exclude folders in the history results?

[jeremy_sg]

You can post-process the results of the history query on the VaultHistoryItem.Type == VaultFSObjectDefine.TypeFile when printing out the results. I don't think that there is a way to filter out folder actions in the history query.

[ismangil]

OK, I've filtered it to item type of file only but it still doesn't quite work the way I intended.

The "Added" items are actually duplicating "Create" items. And Added is considered a file type. So maybe I'll just exclude Added action.