Select: select
The select command may be executed directly through the SDK's CommandProcessor class, or indirectly through either stcmd or stcmdEx.
Syntax
The syntax for this command is:
select * | all | access-rights | changes | linked-items | changed-files | enhanced-links | lifecycle | links | workrecords | agile-breakdown | scope-change | differences | historical-revisions | attached-labels | share-tree | unlabeled-revisions | merge-counts | attachments |connections-log | duplicate-shares | missing-artifacts | non-tip-artifacts | behavior | {propertyName, propertyName,...} | filter = 'filtername' from 'typeName' | starteam {history} {deleted} {backlog} {workspace} {rolldown} {between-labels labelname1 and labelname2} {attached-to-label labelname } {at [label = "label" | promotionstate = "promotion state" | datetime = "date" {-pattern "pattern"}] into fileName { separator 'field separator' } {newline newlineSeparator} {headers 'on' | 'off' | sqlnames} {toExcel} {-pattern "datetimepattern"} {-locale "localecode"} where {{ attached-label = 'labelName' } | { query = 'myquery' } | propertyName relation value and/or propertyName relation value and/or...} {for} {folder = 'myfolder' {recurse} or folder = 'myfolderhierarchy' {recurse} or folder = . {recurse}} or ...} order by {propertyName, propertyName,...} | orderFilter = 'myOrderFilter' [–epwdfile “passwordfilepath”] [-p "userName:password@hostName:endpoint/projectName/ [viewName/][folderHierarchy/]"] | [-s "userName:password@hostName:endpoint]
Use the select command to invoke StarTeam meta-queries. The combination of options determines the type of query, which could be over a file, folder, change request or etc., and the saved filters for the type. Values that contain spaces should be enclosed in double quotes. This command has been modeled on the standard SQL SELECT syntax. Cross type joins are not supported. If folder identification clauses are not specified, the tool assumes the folderHierarchy is set through the setProject command or the root folder of the view, with recurse ON (that is, all descendants or depth == -1). The WHERE clause is constrained to a query and a possible set of folders. Folders may be combined with an OR, but cannot be joined with an AND. Folders act as a further constraint to a query. Folders potentially reduce the subset of results obtained from the query to the items that reside within the specified folders. When a folder hierarchy is specified in the WHERE clause of a select, update or delete statement, the path must start with the root folder and traverse the folder tree all the way down to the leaf folder of interest. It must be explicitly terminated by a \. However, the root folder path must not start with a \. / and \ are interchangeable.
Simple dynamic queries support either chained OR clauses or chained AND clauses. However, they do not support a mix of OR and AND conditions. Complex queries are supported but only as saved queries, for example: where query = 'mySavedQueryname'.
If a property name in the where clause identifies a text property, the relation is '=' and the value starts with a "*", then the * is treated as a wildcard targeted for expansion. This query returns all items whose property values end with the text of the query value. For example, the following returns all files in the view whose names end in .doc.
Only * is supported as a wildcard, and can exist contextually at the start of, at the end of or surrounding a phrase. In other words, "*.doc", "doc*", or "*.*" will all expand the * out to mean - any set of characters.
No other wildcard characters are supported. "." itself is treated as a literal character, no different than (say) a, b or c.
Parameters
For information about the command parameters, refer to Operation Parameters.
In addition:
Parameter | Description | ||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
* (| all) |
Specifies all defined properties for the type. (This is a superset of propertyName1, propertyName2, ...)
propertyName specifies the subset of properties for the type.
relation may be any one of the following:
= < <= > >= <> != in (,). Alternative equivalents are
eq, epic, lt, lte, gt, gte, neq.
in represents a multi-value set relationship. For example,
|
||||||||||||||||||||||||||||
access-rights | Overrides properties. If specified, it generates an access rights report. The columns are the set of available permissions. The rows are the Securable’s (or Container’s) for which access rights exist. | ||||||||||||||||||||||||||||
agile-breakdown |
Overrides properties. If specified, it generates an agile breakdown report.
|
||||||||||||||||||||||||||||
historical-revisions |
historical-revisions is a report that identifies and lists all the revisions at which the items are actually attached to the specified label. If historical-revisions is specified, then the where clause is ignored. |
||||||||||||||||||||||||||||
attached-labels | Produces a report combining all historical revisions of the selected item(s) with the labels attached to each of those revisions. The rows of this report match the label tab details in the StarTeam Cross-Platform Client. | ||||||||||||||||||||||||||||
share-tree | Produces a report describing the share tree of the selected items, equivalent to the Reference tab on the StarTeam. | ||||||||||||||||||||||||||||
unlabelled-revisions | Produces a report which is the converse of attached-labels. It lists only those historical revisions of the selected item(s) which are unlabeled as of the last build. | ||||||||||||||||||||||||||||
changes | Overrides properties. If specified, it generates a change package changes report. The columns identify revision details of each attached item to the change package. The rows identify the attached items. The report can span multiple change packages. | ||||||||||||||||||||||||||||
changed-files | Overrides properties. If specified, it generates a software lines-of-code count report. The rows identify the attached files. The columns identify revision details of all attached files to the process item. The report can span multiple process items. Each process item may be linked to multiple revisions of the same artifact (file). Each row provides a count of the number of lines added, number of lines changed, or number of lines deleted in the context of the checked in revision. | ||||||||||||||||||||||||||||
scope-change | Overrides properties. Generates a scope change report that is targeted at measuring the total scope (cost) of the sprint. It includes the history of the sprint including all active/deleted stories associated with the sprint and the history of active stories, with their estimated points. | ||||||||||||||||||||||||||||
differences | Overrides properties. If specified, it generates a
differences report for files (spanning all revisions) between two labels.
Note: differences can only be used in conjunction with the file type. If the differences keyword is specified, then between-labels'labelname1'and'labelname2' must be specified, (and overrides the where clause) |
||||||||||||||||||||||||||||
linked-items | Overrides properties. If specified, it generates a process item report. The columns identify revision details of all attached items to the process item. The rows identify the attached items. The report can span multiple process items. Each process item may be linked to multiple revisions of the same artifact (file, folder). | ||||||||||||||||||||||||||||
enhanced-links | Overrides properties. If specified, it generates an enhanced links report, which provides trace visibility across all views in the project for which trace the queried items shares exist. The columns identify revision details of all attached traces to the queried item. The rows identify the attached queried items. The report can span multiple items. | ||||||||||||||||||||||||||||
links | Overrides properties. If specified, it generates a links report. The query follows all the traces which lead to or away from the specified item and describes the details of the items at the other end point. | ||||||||||||||||||||||||||||
lifecycle | Overrides properties. If specified, it generates a report that tracks the item through its lifecycle (from creation, through moves and shares, to possible deletion). The rows identify all history and revisions per item, covering item changes, moves and deletes, ordered by modification time - from most recent to oldest. The report can span multiple items. The first column identifies the item by item id. | ||||||||||||||||||||||||||||
workrecords | Workrecords can only be specified in conjunction with the
Task type. When
workrecords is specified, a
workrecords report will be produced.
|
||||||||||||||||||||||||||||
merge-counts | Overrides properties and can only be specified in conjunction with the File type. When specified, the resultant report records a count of the number of times a file has been merged from any other view. The counts are generated per view. | ||||||||||||||||||||||||||||
attachments | Produces a report identifying all the attachments by name, ID, size and md5 for each item in the select list. | ||||||||||||||||||||||||||||
connections-log | Overrides properties. Generates a connections report. It queries the server log, parses each connection out, and identifies the users who have logged in, logged out, their license types (fixed v/s floating) and the total number of consumed licenses. The connections-log report is a metadata query that requires the type to be 'starteam'. | ||||||||||||||||||||||||||||
duplicate-shares | Overrides properties. If specified, it generates an exception report of all items which are shares of each other in the selected view. | ||||||||||||||||||||||||||||
missing-artifacts | Overrides properties. If specified, it generates an exception report of all items which are not attached to he label (specified by attached-to-label) in the selected view. | ||||||||||||||||||||||||||||
non-tip-artifacts | Overrides properties. If specified, it generates an exception report of all items which are attached to the label (specified by attached-to-label) in the selected view, but not at the tip revision. | ||||||||||||||||||||||||||||
behavior | Produces a behavior report. This report identifies the branch state and configuration characteristics of the selected items (from the where clause) and matches the StarTeam Cross-Platform Client Advanced Behavior dialog box. | ||||||||||||||||||||||||||||
Filter | Overrides properties. It identifies a saved filter (by name) for the specified type, and expands into a subset of properties for the type. | ||||||||||||||||||||||||||||
typeName | Specifies the item type. Types are mutually exclusive, and include any one of the stock or custom component (if any) types. Type name 'starteam' is a special case which currently only supports the 'connections-log' query. If 'starteam' is the specified type, then -s is sufficient to identify the server; i.e. -p is not required. | ||||||||||||||||||||||||||||
at |
describes a rolled back view configuration. It may be one of label, promotion state or datetime. If specified, the query is run at the rolled back configuration. datetime may be further qualified by a pattern. The pattern must match any valid pattern supported by the java JDK in
|
||||||||||||||||||||||||||||
attached-label | Specifies a label to which the items of the specified type have been attached. The items to be selected are the ones attached to the label. | ||||||||||||||||||||||||||||
deleted | Specifies the result set. If specified, only deleted items are returned. | ||||||||||||||||||||||||||||
history | Specifies a qualifier on the result set. For example, if the result set contains ten items of a given type, history returns all revisions of each item. | ||||||||||||||||||||||||||||
backlog | Can only be specified in the context of the Story type. For example, if the result set contains ten items of a given type, history returns all revisions of each item. | ||||||||||||||||||||||||||||
workspace | Acts as a constraint on file queries. If specified, it locates not-in-view folders and files from the file system (the view path mapped to working folders on disk) and includes them in the report. | ||||||||||||||||||||||||||||
rolldown | Can only be specified for tree item types. When specified, the children of the selected tree items (specifically, the entire tree of descendants) are added to the result set. | ||||||||||||||||||||||||||||
Folder | Specifies the
StarTeam folder name in the current view.
If there are multiple folders with the same name, the command returns all folders with that name. Or specify the folder hierarchy in the "/" format. The folder path musts start and end with "/". Start from the root folder and end in a branch folder. For example: /StarDraw/SourceCode/On-line Help/. |
||||||||||||||||||||||||||||
recurse | Designates all descendants from the folder specified. | ||||||||||||||||||||||||||||
. | Implies the current working folder, requiring the tool to find StarTeam folders with paths mapping to the current working folder. The Command processor must be running inside the StarTeam folder hierarchy. | ||||||||||||||||||||||||||||
into | "fileNameWithPath" specifies a file to which the output of the query will be written. The generated output is tabular in format, each row separated by a carriage return/line feed, specific to the platform the query is run on. | ||||||||||||||||||||||||||||
newline |
If newline separator is specified, then embedded new lines inside text fields are replaced by the provided separator. If not specified, then embedded new lines are replaced by their character string equivalents, specifically, "\r" and/or "\n". |
||||||||||||||||||||||||||||
headers | By default,
headers = on . If
headers = off , output files are written without headers. A special case of headers turned on is to substitute the property internal names for the display names by using the syntax
sqlnames.
|
||||||||||||||||||||||||||||
toExcel | Produces an output file which is in CSV format. The columns are separated by a comma and embedded new lines in text are replaced by line feeds. This is a special format which allows Microsoft Excel to successfully import these types of files directly into a spreadsheet while retaining the tabular structure of the data. | ||||||||||||||||||||||||||||
myQuery |
Specifies a saved StarTeam query name for the type. The suported property value types are:
|
||||||||||||||||||||||||||||
order by | Specifies a default sort order for the output result set. | ||||||||||||||||||||||||||||
OrderFilter | Specifies the saved
StarTeam filter name for the type.
It expands into a subset of properties for the type, which is used for sorting. myFilter and myOrderFilter can be different. The properties specified in MyOrderFilter should not be set for grouping. If a set of property names is specified instead of the order filter, the sort criteria default to ascending order, sort by text is set for text properties, and sort by date is set for Date/DateTime Properties. If you need more specific sorting, specify an existing saved filter. |
In general, the select grammar is positional. It is best to follow the position of the syntax as outlined in the command description above.
Note: For the Change Request type, the AddressedInBuild property can be used in the where clause of a query using the special value "Next Build" or "-2".
Note: For use with stcmdEx: Enclose the greater than ">" and less than "<" characters within double quotes. By design, stcmdEx interprets the > as a redirect to a file so, without "" you get an error "The system cannot find the file specified".
Examples
select * from File where query = "Status = Current" order by orderfilter = "All Files By Status" -p "Administrator:Administrator@localhost:49201/StarDraw/Release 1.0 Maintenance"
The following example shows how to use the differences keyword. It selects all properties of all change requests and writes them into a file called QueryOutput.txt.
select differences from file between-labels 14.0.3.21 and 14.0.3.27 into "c:/temp/differencesReport.txt" separator | -p "username:password@hostname:port/project/view"
select * from changerequest into "c:/temp/QueryOutput.txt"
select linked-items from ChangeRequest where AddressedIn = "Next Build" or select linked-items from ChangeRequest where AddressedIn = "-2"
The example below selects three properties, Name, Status, and File Time Stamp at check in, for all files, which satisfies the built in query Files to Checkin.
select Name, Status, Modified from file where query = "Files to CheckIn"
The example below selects all tasks from the Sales Materials folder or the Marketing Materials folder and its descendants. It returns a result set containing only the task properties described by the "By Status and Responsibility" filter. select * from task where filter = "By Status & Responsibility"
select filter = "By Status and Responsibility" from task where folder = "Sales Materials" or folder = "Marketing Materials" recurse
The following examples show how to use select with change requests and change packages.
select linked-items from ChangeRequest into fullyQualifiedPathToOutputFile where ChangeNumber = 1234 -p "username:password@host:port/project/view"
select changes from ChangePackage where name = "Workspace Changes on 2013-10-15@22-43-00Z"
This example shows how to use the select command with the lifecycle parameter.
select lifecycle from File into fullyQualifiedPathToOutputFile where FileName = Server.java -p "username:password@host:port/project/view"
This example shows how to generate a workrecords report:
select workrecords from task where StTaskNumber = 88
The following example shows how to user the select command with the rolldown parameter:
stcmd (ex) select {propertyname,(s)…} from task rolldown where {…} –p “…”
This examples shows how to generate an agile-breakdown report:
select agile-breakdown from sprint into c:/temp/agileReport.txt separator "|" where SprintID = 1234 -p "user:password@host:port/project/view"
The following example shows you how to select rows in a range:
select "*" from changerequest into "c:/temp/stout.txt" where changenumber ">" 50000 and changenumber "<" 50100 -p "user:password@host:port/project/view"
The next example uses a date range on the ModifiedTime property:
select "*" from ChangeRequest into "c:/temp/stout.txt" pattern = "M/d/y" where modifiedtime ">" 1/1/2014 and modifiedtime "<" 8/30/2014 -p "user:pwd@host:port/project/view"
The following example produces history results similar to the deprecated hist command:
select revisionnumber, viewid, modifieduserid, modifiedtime, comment from file history -p "Username:Password@host:port/project/view/[folderhioerarchy]/" where name = "filename"
The next example shows the use of historical-revisions
select historical-revisions from file attached-to-label "label name" -p "user:password@host:port/project/view"
The next two examples show the use of wildcards and fully qualified folder paths
select folder, name from file where name = "*.doc" for folder = "StarDraw\Source Code\External Resources\"
select viewmemberid, name, description from file where name = "*.doc"
The next few examples show different usage of property relation values:
select * from ChangeRequest pattern = "M/d/y" where ModifiedTime > 12/14/1999 and modifiedtime < "2/16/1999"
select * from ChangeRequest where ChangeNumber in (10298, 10310, 10316, 10320)");
select * from ChangeRequest where ChangeNumber = 10298 select * from ChangeRequest where ChangeNumber ">=" 10298 and ChangeNumber "<=" 10320 select * from File where name = "GNUmakefile.build*" select * from File where name = "*.buildinfo" select * from File where name = "*ranching release views.doc*" select * from ChangeRequest where Responsibility = "Alan Kucheck"
Note that ">=" can be replaced by gte without the double quotes around it,
= can be replaced by eq, etc.
This example shows the use of date patterns driving the format of output date time property values:
select name, modifiedtime from file -pattern "yyyyMMddHHmmss" where name = "*.java" order by name -p "user:pwd@host:port/project/view"
This example queries the path of all files which are exclusively locked by a user:
select Path from file where exclusivelocker <> ""
select connections-log from starteam into "c:/temp/connections.log" -s "user:password@host:port"
This example reports on custom properties, and uses them to drive the query.
select changenumber, usr_projecttype, usr_dateclosed from changerequest into custom.cr.output.txt headers off pattern = "M/d/y" where USR_DATECLOSED gte 1/1/2015 and USR_DATECLOSED lt 1/1/2016
This same query with greater precision:
select changenumber, usr_projecttype, usr_dateclosed from changerequest into custom.cr.output.txt headers off pattern = "M/d/y H:m:s" where USR_DATECLOSED gte "1/1/2015 0:0:0" and USR_DATECLOSED lt "12/31/2016 23:59:59"