QUERY
Description
Executes SQL queries. Requires SQL storage.
Usage
cm query <sql_command> [--outputfile=<output_file>] [--solveuser=<column_name>[,...]] [--solvepath=<column_name>[,...]] [--columnwidth=<value>] [--nocolumnname] [--columnseparator=<sep>] [--repository=<name>]
Options
| Option / Argument | Description |
|---|---|
| --outputfile | Writes the result in an output file. |
| --solveuser | Sets the specified columns as username columns. The query interpreter will assume that data of these columns will be users, and will try to solve them. |
| --solvepath | Sets the specified columns as itemid column. The query interpreter will try to solve item id to filesystem paths. |
| --columnwidth | Specifies the width of each column to format the output. |
| --nocolumnname | Does not print column name. |
| --columnseparator | Uses char as column separator instead of a tab (\t). |
| --repository | Repository to query. |
| sql_command | The sql query to be executed. |
Help
Remarks
This command allows users to execute SQL queries in the server database.
In order to write SQL queries, use these two pre-defined functions to manage users and paths:
- 'SolveUser(<username>)' that resolves a username into Unity VCS format.
- 'SolvePath(<path>)' that resolves a disk path into an item id.
Also, you can use options to show query results in a human readable form.
You can use the options '--solveuser=<column_name>' and '--solvepath=<column_name>' to specify columns that query interpreter must convert to a legible text. You can specify more than one column name, comma separated.
Examples
cm query "SELECT * FROM revision" --columnwidth=25 --repository=reptest
(Retrieves data from 'revision' table from repository 'reptest'.)
cm query "SELECT b.sname as br_name, o.dtimestamp as date from branch b, object o, seid s where b.iobjid=o.iobjid and o.fidowner=s.iseidid and scode='SolveUser(john)'" --outputfile=query.txt (Outputs into a file the branches with owner 'john'.)
cm query "select r.iobjid, r.fiditem as path, s.scode as username FROM revision r, object o, seid s WHERE r.iobjid=o.iobjid and fidowner=s.iseidid and o.dtimestamp>04/25/2014" \ --solveuser=username --solvepath=path --repository=reptest@server2:9095 (Retrieves selected data from selected repository.)
cm query "SELECT * FROM revision WHERE fiditem=SolvePath(c:\mywkpath\info)"
(Retrieves all revision data of path 'info'.)