StQL: The "select" command. A generalized StarTeam query generation language

over 3 years ago

The select command may be executed directly through the SDK's CommandProcessor class, or indirectly through either stcmd or stcmdEx. The syntax of the 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 | unlabeled-revisions | merge-counts | connections-log | duplicate-shares | missing-artifacts | share-tree | workspace-sync | {propertyName, propertyName,...} | filter = "filtername"

from typeName  | starteam {history} {deleted} {backlog} {workspace} {rolldown} {exclude-views | exclude-folders}

{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"}


{{ 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. 

Note: All elements in the grammar are required as is, including commas, double-quotes etc. where specified. The only caveat to this is the | and { } notations, which are used to indicate optional components of the syntax. 

* (| all) specifies all defined properties for the type. (This is a superset of propertyName1, propertyName2, ...)

propertyName{n} specifies a subset of properties for the type, identified by case insensitive internal names.

Note that property names of a specific type are the internal (catalog) names of the type. To identify the list of property name, run the type specific command line command

              (stcmd{ex} describe <typeName> -s "user:password@host:port";

where typeName can be one of the stock types File, Folder, ChangeRequest, Task, Topic, Sprint, Story, Requirement, Trace, ChangePackage, WhiteBoard, Concept etc... or custom component types

Property names are case insensitive, Property names with embedded white space (if any) should be enclosed in double quotes. User defined properties always start with usr_ 

relation may be one of {=, <, <=, >, >=, <>, !=, in (,)}. Alternative equivalents are {eq, eqic, lt, lte, gt, gte, neq}. in represents a mutli-valued set relationship, e.g. changenumber in (1,2,3,4). all the other relationships are single valued. e.g. changenumber = 1, changenumber > 2 and changenumber < 5

eq and eqic both represent explicit equality (=). however, eqic also implies case insensitive queries for text values properties.

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. An access rights report may be run for a specific project/view and viewmember type or it may be run for an individual user or group across the server. The syntax determines the context. To query results for a specific view/type, the query takes the form ---- select access-rights from type where ... -p "..." ----. However, to query results for a user or group, the query takes an alternate form ---- select access-rights from [ user | group ] where principal = principalName -s "..." 

With exclude-views turned ON, views & folders access rights are excluded. With exclude-folders turned ON, views are processed, folder access rights are excluded.

agile-breakdown  overrides properties. If specified, it generates a breakdown of Sprints or Plans.

Sprints For sprints, it is a denormalized join or view of all stories, tasks, and workrecords attached to the selected sprints. Each row represents the break down to the workrecord detail. 
Plans For plans, it is a denormalized join or view of all requirements attached to the selected plans.

historical-revisions produces a report that identifies and lists all the revisions at which the items are actually attached to the label specified by attached-to-label. If historical-revisions is specified, then attached-to-label must be specified and 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 CPC.

unlabeled-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.

scope-change overrides properties. If specified, it generates a scope change report, 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. scope-change can only be used in conjunction with the sprint type,

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 the where clause is ignored.

linked-items Overrides properties. If specified, it generates a process item report. The rows identify the attached items. The columns identify revision details of all attached items 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, folder).

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 and/or number of lines deleted in the context of that checked in revision.

duplicate-shares Overrides properties. If specified, it generates an exception report of all items which are shares of each other in the selected view.

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.

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.

workrecords overrides properties and can only be specified in conjunction with the Task type. When specified, a workrecords report will be produced.

attachments overrides properties and be specified in conjunction with any Item type. When specified, an attachments report will be produced, identifying the names, id's, sizes and md5s of all associated attachments.

user-resources overrides properties and be specified only in conjunction with the Task or Requirement type. When specified, a responsible users report will be produced, identifying the names, and id's, of assigned users.

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.

share-tree produces a report describing the share tree of the selected items, equivalent to the Reference TAB on the Cross Platform Client.

workspace-sync produces a report describing the content of the syncdb on the local machine’s workspace, mapped to the target view & files specified in the query.

This report returns sync details of the file as it is checked out to disk, not necessarily as it is at the tip of the repository.

So if, for example, an earlier version were to have been checked out, the report would return the revision information of the historical revision.

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'

filter  overrides properties. It identifies a saved StarTeam filter (by name) for the specified type, and expands into a subset of properties for the type. 

typeName Specifies the StarTeam 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 java.text.SimpleDateFormat.applyLocalizedPattern(String) and ensure that embedded date/
time strings strictly adhere to that pattern. If a pattern is specified, a 2 character country code locale may also be optionally specified.

attached-to-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 constraints the result set. If specified, only deleted items are returned.

history is 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 acts as a constraint on story queries. It identifies those stories (from a selected range) that are not linked to any sprint.

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 may 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. To explicitly qualify a given folder, specify the folder hierarchy in the "/" format. Start from the root folder and end in a branch folder. For example: /StarDraw/SourceCode/On-line Help/. Folder names or paths with embedded white space must always be enclosed in double quotes.

Note that some item types (e.g. File, ChangeRequest) have a folder property. In queries against these types, the following statements are grammatically correct and produce equivalent result-sets.

select all from file where for folder = "foo" -p "..." // this uses the folder keyword in the grammar

select all from file where folder = "foo" -p "..." // this uses the folder property of the (File) type

The "where" keyword is required to use the "for" keyword, even if there are no other where conditions, static (saved) or dynamic queries, etc.

recurse Designates all descendants from the folder specified.

. is a special case that implies the current working folder, requiring the query engine to find StarTeam folders with paths mapping to the current working folder. which further implies that the engine must be running inside the StarTeam folder hierarchy mapped to disk.

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 are turned on. If headers = off is specified, 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 further qualifying the parameter sqlnames. The headers keyword only works in conjunction with an output file to which the results are written vide the into clause

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.

query = myQuery specifies a saved StarTeam query name for the type.

It acts as the equivalent of a compound where clause of a SQL statement, such as a complex combinations of relations and operators. If no query name is specified, the command performs the action on all objects of the

 An alternative to using saved queries is to build up dynamic simple queries using and/or combinations of propertyName <relation> propertyValue.

propertyName relation value (relation in {=, <, <=, >, >=, <>, !=}).

The supported property value types are:
        Text Literal string.
        Integer A string in the form of an integer like "1234".
        Double A string in the form of a double like "1234.5678".
        Long A string in the form of a long like "1234567890".
        Boolean The string "true" or "false" - case insensitive.
        Date String format yyyy-mm-dd, 4 digit year, 1 <= mm <= 12, 1 <= dd <= 31.
        DateTime If -pattern "pattern" is specified, then it is parsed using
                            java.text.SimpleDateFormat, localized pattern set to "pattern".

                             If -pattern is not specified, attempt to match patterns using java.text.DateFormat
                             {SHORT, MEDIUM, LONG, FULL} in that order.
                              If all else fails, the engine will attempt ISO8601 parsing e.g.: yyyy-mm-ddThh:mm:ssZ 

                   DateTime patterns work two ways. When specified, they are used to parse input date/time strings in the query itself. Additionally, if datetime properties are output, they are formatted to fit the specified pattern.
        TimeSpan String format [ws][-][d. |d]hh:mm:ss[.ff][ws], items in brackets optional.
                             see com.starteam.util.TimeSpan. ws whitespace, d days, ff fractional second,

                              hh hours, mm minutes 0 <= mm <= 59, ss seconds 0 <= ss <= 59.
         Enumerated String. Enumerated value specified may be internal name, display name, or string
                                               representation of integer enumeration code.

                                                If the Enumerated property is multiselectable, two or more enums may be specified.                                                 In this case, they must be separated by a period. For example: 101.102.103. 

                   User Value specified may be user name or string representation of integer user id.

                   Group Value specified may be group name or string representation of integer group id.  

                   Label Value specified may be label name or string representation of integer label id.


order by Specifies a default sort order for the output result set.

OrderFilter Specifies a 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 filters. 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.

-p Indicates the view or folder to be used and also provides the user name and password needed to access the StarTeam Server. specifically, -p "user{:password}@host:port/projectName/viewName}

-epwdfile if a password is not specified in -p, then -epwdfile must be specified.


Important: When used with select, update, insert, or delete, the command execution context is stateless. The position of -p in the statement is important. It must be located at the very end as the very last parameter of the
command. Placement anywhere else in the query will produce indeterminate results, possibly leading up to an SDKRuntimeException.

The position of -epwdfile is equally important, and must immediately precede -p.

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":

Important 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".

Several examples of the select syntax follow,,,

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 and 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”
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 Name = -p "username:password@host:port/project/view"

This example shows how to generate a workrecords report:
select workrecords from task where StTaskNumber = 88

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
where name = "filename" -p "Username:Password@host:port/project/view/[folderhioerarchy]/" 

and the following example produces the history of a deleted file

select all from file history deleted into "c:\temp\history.txt" where name = "" -p "user:password @server:port/project/view/"

this 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 value(s)

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 = "*"
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 

select Path from file where exclusivelocker <> "" which are exclusively locked by some user

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 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 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"

this query returns the entire set of folders in a view

select all from folder into "c:\temp\folder.txt" -p "user:password@host:port/project/view"

select all from folder where for folder = "/StarGate/" returns the set of folders under the 'root' folder StarGate

select all from folder where for folder = "/StarGate/dev/" returns the set of folders under the folder StarGate/dev

select all from folder where for folder = "/StarGate/dev/" recurse returns all folders under the folder StarGate/dev, recusring down through the folder tree(s) below /StarGate/dev/


select viewmemberid, dotnotation, revision, md5 from file where attached-label = "" into "output.file.txt" -p "user:pwd@host:port/projectname/viewname" returns all files that are attached to the specified label.


select duplicate-shares from changerequest -p "user:pwd@host:port/projectname/viewname" is an exception report identifying artifacts with duplicate shares in a view

select missing-artifacts from file attached-to-label "" -p "user:pwd@host:port/projectname/viewname" is an exception report identifying files not attached to label in the view.

some access rights query examples

select access-rights from file -p "user:pwd@host:port/projectname/viewname" returns all file rights granted/denied in the view

select access-rights from groups where principal = "Administrators" returns all rights granted/denied to administrators across the server

select access-rights from users returns all rights explicitly granted/denied to any/all users across the server


The select command continues to grow as new requirements are identified.


How To-Best Practice
Comment List
Related Discussions