vtk_select

vtk_select_create

Usage:         
vtk_select_create [-select select_value] [-from from_value] [-where selectrule] [-order order_value] [-limit limit_value] [-cache 0|1] [-lifetime lifetime] [-qlifetime qlifetime] resultArray

Description:   
Creates a data selection query of the vovserver using concepts similar to SQL queries. The results are organized as rows of columnar data. When any query created basic information about the query and number of rows, columns, etc. are returned via 'resultArray'. Generally, the results of a data results of a query are not returned via the 'resultArray' but can be later retreived using vtk_select_get or iterated over with vtk_select_loop. 

Options: 
The query is specified by the following command line arguments: 
* -select       -- comma separated list of fields to return 
* -from         -- data source to which to apply the query to 
* -where        -- selection criteria 
* -order        -- comma separated list of fields by which to order the query results 
* -limit        -- maximum number of records to return 
* -lifetime     -- how long the query results will remain valid (default = 10 secs) 
* -qlifetime    -- how long the query object will remain valid (default = 10 mins) 
* -cache        -- flag to control cacheing of query. 

To see a list of valid data sources, see the output of the following command: 
vovselect objectname from objects 

The -select values must be valid fields for the object type that corresponds to the -from sources. Additionally, any fields in the -where selection rule must only contain uses of fields that are also valid fields for the object type. 

Example:
set select   "USER,JOBNAME,CPUTIME"
set from     "jobs"
set where    "USER==bob"
set order    "CPUTIME DESC"
set limit    "100"
set cache    "1"
set lifetime "10s"

set queryId [vtk_select_create -select $select -from $from -where $where -order $order -limit $limit -cache $cache -lifetime $lifetime queryResult]

puts "query id: $queryId"
query id: 000001037

parray queryResult
queryResult(bytes)     = 1428
queryResult(cache)     = 1
queryResult(colnames)  = USER JOBNAME CPUTIME
queryResult(errormsg)  =
queryResult(id)        = 000001037
queryResult(matched)   = 2
queryResult(numcols)   = 3
queryResult(numrows)   = 2
queryResult(ts,create) = 1369054199
queryResult(ts,expire) = 1369054209
queryResult(valid)     = 1


A full list of valid data sources can also be obtained by creating a query with -select value of objectname and a -from value of objects:
set queryId [vtk_select_create -select objectname -from objects queryResult]

Similarly, a list of fields for any specific object can be obtained by using a query:
set queryId [vtk_select_create -select fieldname -from objectname queryResult]

where objectname is replaced by the name of an actual supported object listed above.

Queries can either be cached or non-cached.

For cached queries, they are a persistent object within the vovserver with an explicit
lifetime for both the query object itself as well as an explicit lifetime of the selected
data result.  The vovserver will delete the results when data lifetime has expired.
Additionally, the vovserver will delete the query object from the server when
it has expired.  It is enfored that the query lifetime will always equal or exceed the
query data lifetime.  When an unexpired query object with expired data is accessed
the query will be reevaluated and new results computed.  For queries that can
generate large amounts of data, they need to be cached by the server to allow for
transfer between the server and client without buffer overflow errors.  Thus, they need
to be persistent on the server for a certain amount of time to permit the transfer to
safely occur.  This is one of the motivating reasons for the seperation between query
creation and obtaining the data.  A cached query can be specified by using the
command line value of '-cache '". When a query is specified to be cached
then '-lifetime' and '-qlifetime' arguments are used to specify the time period that
the query results and query object are valid.

If it is known that the query will not generate very much data (e.g. a few
megabytes of data at the most) the query can be specified to not be cached and the results
returned during the query creation.  This is generally a more efficient process since
a persistent object is not created on the server.  To specify a non-cached query, use
the command-line argument '-cache 0'.

There is a command line utility that provides the same functionality as vtk_select based queries.
The utility is called vovselect.


Returns:       
The id of the query object. Also, the array queryResult, is populated containing basic information regarding the query.

vtk_select_delete

Usage:         
vtk_select_delete queryId

Description:   
This procedure is used to delete a query object from the server. 

Example: 
vtk_select_delete 00358286

Returns:       
Success or an error message

vtk_select_get

Usage:         
vtk_select_get queryId startRow endRow resultArray

Description:   
Populates a TCL array with the values for rows {[startRow:endRow]} from the existing query given by the queryId. Note that the row indexes start at 1. A query that matched 100 rows would have it's first row numbered 1 and it's last row numbered 100. This is important for both specifying the range of rows to be returned as well as access values in the returned TCL array. 

Example:
vtk_select_get 000001037 1 2 pqueryResult

parray pqueryResult
pqueryResult(1,CPUTIME)     = 100
pqueryResult(1,JOBNAME)     = VCS
pqueryResult(1,USER)        = bob
pqueryResult(2,CPUTIME)     = 60
pqueryResult(2,JOBNAME)     = spice
pqueryResult(2,USER)        = fred
pqueryResult(id)            = 000001037
pqueryResult(numviewrows)   = 2
pqueryResult(viewendrow)    = 2
pqueryResult(viewstartrow)  = 1


Returns:       
Success or an error message.

vtk_select_loop

Usage:         
vtk_select_loop [-id query_id] [-select select_value] [-from from_value] [-where selectrule] [-order order_value] [-limit limit_value] [-cache 0|1] [-lifetime lifetime] [-qlifetime qlifetime] [-first firstRow] [-last lastRow] varlist script

Description:   
vtk_select_loop provides a facility to loop over query results and execute a script block. A map of variables is provided that relates each column of data in a row to a variable in the script block. The variables are set to the appropriate column values before evaluating the script block. By default all rows are looped over. If it is desired to loop over a range of rows, then use the '-first' and '-last' options. The rows indexes start at 1. A query that matched 100 rows would have it's first row numbered 1 and it's last row numbered 100. 

The query to be iterated over can be an existing query or a new query created by vtk_select_loop. To use an existing query, specify it via the '-id' option. To create a new query, use the same options for vtk_select_loop that you would use with vtk_select_create to create the query. 

See vtk_select_create for more information on the options to create a query. 

Examples:
set select   "USER,JOBNAME,CPUTIME"</b>
set from     "jobs"</b>
set where    "USER==bob"</b>
set order    "CPUTIME DESC"</b>
set limit    "100"</b>
set cache    "1"</b>
set lifetime "10s"</b>

set queryId [vtk_select_create -select $select -from $from -where $where -order $order -cache $cache -lifetime $lifetime queryResult]</b>

parray queryResult</b>
queryResult(bytes)     = 1428
queryResult(cache)     = 1
queryResult(colnames)  = USER JOBNAME CPUTIME
queryResult(errormsg)  =
queryResult(id)        = 000001037
queryResult(matched)   = 2
queryResult(numcols)   = 3
queryResult(numrows)   = 2
queryResult(ts,create) = 1369054199
queryResult(ts,expire) = 1369054209
queryResult(valid)     = 1

vtk_select_loop -id $queryId "userName jobName, runTime" {
  puts "User $userName had a $jobName job that ran for $runTime seconds."
}

User bob had a VCS job that ran for 100 seconds.
User fred had a spice job that ran for 60 seconds.


Returns:
Success or an error message.