Highlighted
psciascia Honored Contributor.
Honored Contributor.
2706 views

Cannot find CR in CPC, but present in SQL database

Jump to solution

I am playing in the nuts and bolts. I have not tried the SDK just yet - just snooping in the Database and looking at its structure. I made a simple request  for all CR's in a specific view with a specific state.  I get 85 distinctive CR numbers. If I filter with the exact same condition in the CPC I get only 11 CR's.

I have been comparing fields between some that show up in both and some that only show up in the DB, and I can't find a fields that the CPC would be using to filter out these items.

Any ideas?

 

Thanks,

0 Likes
1 Solution

Accepted Solutions
JamesWalsh Absent Member.
Absent Member.

RE: Cannot find CR in CPC, but present in SQL database

Jump to solution

I've updated the query to take into account deleted folders. At the moment shared CRs still display as duplicate entries but you can use DISTINCT depending on what you want to do.

5658.Select-CRs.sql

16 Replies
JamesWalsh Absent Member.
Absent Member.

RE: Cannot find CR in CPC, but present in SQL database

Jump to solution
Hi,

Can i confirm that you are looking at the syn_change view in your database?
The two most likely reasons for seeing more results in the database is that each revision of a CR is stored as a different entry in the database. The database also stores all CRs even ones that have been deleted from the view in the CPC.

Could this explain what you are seeing?

James
0 Likes
psciascia Honored Contributor.
Honored Contributor.

RE: Cannot find CR in CPC, but present in SQL database

Jump to solution
Thanks James,
Yes I am looking at syn_change (but I also tried straight by the table , in case there was more there than in the view). My guess from the beginning was that, as you suggest, they were deleted from the CPC, nut I thought I would have seen a DeletedTime or DeleteUser, none of which are set. Do you know of a field other than the ones I mentioned that would lead me to that conclusion?
By the way, I noticed that each revision had its record so I inner joined on itself using a max(ModifiedTime) gouped by ChangeNumber.
0 Likes
psciascia Honored Contributor.
Honored Contributor.

RE: Cannot find CR in CPC, but present in SQL database

Jump to solution
Just adding more now. I wrote a little program with the SDK to output all CR's in all projects and views, and still many of the ones residing in the database's syn_change view are not being detected. Seems the SDK sees the same as the CPC, which makes sense since the CPC is built on the same blocks.
0 Likes
Micro Focus Expert
Micro Focus Expert

RE: Cannot find CR in CPC, but present in SQL database

Jump to solution
>>Seems the SDK sees the same as the CPC
A more accurate representation would be - the CPC sees the same as the SDK, since it is just a UI built on top of the SDK.

Additionally, to see deleted items, create a RecycleBin off the view, output the cr's from it instead.
prime the bin to report active & deleted items (so you get both) or deleted items only

i imagine that will help reconcile the differences.
0 Likes
Micro Focus Expert
Micro Focus Expert

RE: Cannot find CR in CPC, but present in SQL database

Jump to solution
Alternatively, you could run queries via the command line (stcmd, or the sdk CommandProcessor class)

you may target queries at active items (the default) or include deleted items by injecting the deleted keyword

see the full documentation of the select statement here
community.microfocus.com/.../stql-the-select-command-a-generalized-starteam-query-generation-language
0 Likes
psciascia Honored Contributor.
Honored Contributor.

RE: Cannot find CR in CPC, but present in SQL database

Jump to solution
Thanks again Anil for your help,

Not 100% what the actual command line would look like, I went though the SDK. In included Active and Deleted items as well as deleted folders, though I don' think that was necessary.
Again I targeted only the view where my missing items are tied to in the database. I further filtered by the Status because I know my missing item has Status=0.
The loop returned 11 active items (which matches what I see in the CPC) and 37 deleted items for a total of 48 ChangeRequests in that view and status. When I run the query in SQL I get 66 items (no doubles) - so 18 more than from my SDK function.
Here is a part of my function. Some code is unnecessary, as I basically altered a previous function and did not do a proper clean up - this was only a test:

protected void ViewDeletedChangeRequest(Server s) {
ChangeRequest.Type crType = s.getTypes().CHANGE_REQUEST;

PropertyCollection pc = new PropertyCollection();

pc.add(crType.getProperties().find(ChangeRequest.Type.ResponsibilityProperty.NAME));
pc.add(crType.getProperties().find(ChangeRequest.Type.StatusProperty.NAME));
pc.add(crType.getProperties().find(ChangeRequest.Type.ChangeNumberProperty.NAME));

//traverse the projects and views and find my view 'v'
...

RecycleBin bin = v.getRecycleBin();
Folder root = bin.getRootFolder();

bin.setIncludeActiveItems(true);
bin.setIncludeDeletedItems(true);
bin.setIncludeDeletedFolders(true);

root.refreshItems(crType, pc, -1); //not sure if this is necessary, but grabbed form sample

ChangeRequest[] cr_array = (ChangeRequest[]) root.getItems(crType).toArray(new ChangeRequest[0]);
for (ChangeRequest cr : cr_array) {
if (cr.getStatus().getDisplayName().equals("New")) {
if (cr.isDeleted()) {
System.out.println(cr.getNumber() + "***DELETED***");
iDeletedCount++;
}else{
System.out.println(cr.getNumber());
iActiveCount++;
}

}
}
System.out.println("Active: " + iActiveCount);
System.out.println("Deleted: " + iDeletedCount);



The output (eliminating all the lines with the CR numbers) is:
Active: 11
Deleted: 37
Perhaps you will see something in the code that may explain the missing 18 items .

Thanks again,

Patrick
0 Likes
psciascia Honored Contributor.
Honored Contributor.

RE: Cannot find CR in CPC, but present in SQL database

Jump to solution
More info...
I noticed that in the CPC, I actually had 17 items and not 11.
By snooping around I found that the missing 6 were not in the root folder.
I saw in another post that one solution to get to the subfolders is to use getViewMembers
So I changed the array line (from code in previous post) to:
ChangeRequest[] cr_array = (ChangeRequest[]) v.getViewMembers(crType).toArray(new ChangeRequest[0]);
However now, I no longer get my deleted items - probably because the bin is not a view member.

I am going to try traversing all subfolders of my bin root folder (which is the same as the view root folder) and see if I can find the missing (now) 12 CR's. My guess is that they are in deleted subfolders.

If someone thinks of a better solution, let me know. I'll keep you posted on the results.
0 Likes
JamesWalsh Absent Member.
Absent Member.

RE: Cannot find CR in CPC, but present in SQL database

Jump to solution

 Select-CRs.sql

Hi,

Unfortunately the  Deleted User/Deleted Time does not update correctly after a deletion. You need to do a join on the syn_viewmember view to correctly find whether a CR has not been deleted. Eeven this isn't perfect because if a folder containing CRs is deleted you would further need to find whether that folder was deleted and filter the query further. 

As Anil has suggested using the commandline you're able to pull this information very quickly. You can find documentation on this in the ST_CmdTools.pdf found in your CPC installation directory. 

0 Likes
psciascia Honored Contributor.
Honored Contributor.

RE: Cannot find CR in CPC, but present in SQL database

Jump to solution
Again, thanks James!
The reason I was pushing for SQL was to be able to get a SSRS report plugged directly to the query.
I found out from playing with the SDK that a lot of missing CR's are in deleted folders so, as you mentioned, more filtering would be required.
Not knowing how folders are referenced in SQL, I am going to have to get a data source pulled out from my Java program and plus the report onto that. Not ideal but I guess it will have to do.
0 Likes
psciascia Honored Contributor.
Honored Contributor.

RE: Cannot find CR in CPC, but present in SQL database

Jump to solution
Not giving up yet.
James, you say that it is possible to filter out CRs in deleted folders from SQL. Can you tell me how columns from either syn_Change or syn_ViewMember link to syn_folder and what field I can use to find out if they are deleted?
I'd really like to get dynamic reporting out of this.
Thanks
0 Likes
JamesWalsh Absent Member.
Absent Member.

RE: Cannot find CR in CPC, but present in SQL database

Jump to solution

I've updated the query to take into account deleted folders. At the moment shared CRs still display as duplicate entries but you can use DISTINCT depending on what you want to do.

5658.Select-CRs.sql

psciascia Honored Contributor.
Honored Contributor.

RE: Cannot find CR in CPC, but present in SQL database

Jump to solution
Wow we're getting so close. I was off yesterday but I knew that upon my return I would use the query st the URL below to start my quest for deleted folders:
community.microfocus.com/.../sql-server-query-to-return-information-on-deleted-files
You beat me to the punch! 🙂
Now when I filter my results to status = 0 in a specific view using DISTINCT I get 23 results whereas the CPC returns 17. The 6 items that should not show up are all deleted. I've mucked around further than your query to isloate one of them and found that it is shared - so it shows up in three different views one of which is the one of interest even though it is deleted in that view.
Now I'm trying to weed those out (probably with a funky JOIN or WHERE clause) and beyond that I'm there. I'd love to mark your query as the answer but I'll wait until I can get the results I want.
Thanks so much!
0 Likes
psciascia Honored Contributor.
Honored Contributor.

RE: Cannot find CR in CPC, but present in SQL database

Jump to solution
Upon further investigation, that one CR I'm looking that shows up in three different views is deleted in all three views. It is not in a deleted folder, it is just deleted. So now I'm wondering how all three occurences are returned. I've added the WHERE conditions you hade provided last Friday (Deleted times and User IDs from c, v, and vm ) but they are all 0 and -1.

Another stumper.
0 Likes
psciascia Honored Contributor.
Honored Contributor.

RE: Cannot find CR in CPC, but present in SQL database

Jump to solution
I guess the thing to do would be to access via SQL whatever the 'isDeleted' attribute from the SDK accesses o determine that it is deleted.
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.