2016-09-26

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 | {propertyName, propertyName,...} | filter = "filtername" from typeName  | starteam {history} {deleted} {backlog} {workspace} {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.  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, 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 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 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. 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  overrides properties and can only be specified in conjunction with the Task type. When 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. 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-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. 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 . 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 type.  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".                             See http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html.                              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.              Object                    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 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 Name = 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 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 = "deletedFile.java" -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 = "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  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 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" 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 = "15.0.1.45" into "output.file.txt" -p "user:pwd@host:port/projectname/viewname" returns all files that are attached to the specified label. The select command continues to grow as new requirements are identified.

Show more