A query database is a Yate database that has been opened via an action for querying. Query databases typically have a key column describing a unique value which can be used to differentiate the items (rows) of the database. Every value in the key column must be unique. You can have as many query databases as you wish open at a time.
You can create the databases in three ways:
- In a different application
- You will have to manually define an export set to map the columns to Yate fields. In Settings - Export Sets, select a single Export Set, click on the ⇒ button and a menu will be displayed which has an Export as Sidecar item.
- As an empty database via the UI
- In Settings - Export Sets, select a single Export Set, click on the ⇒ button and a menu will be displayed which has an Export as Empty Database item.
- Via a Create Album or Track Database statement
- You must specify Create only and may have to specify Standalone in order to bypass the Export Set requirements for the particular statement.
While it is possible to open and manipulate databases without using the query functionality, the query functionality is far more efficient and faster.
It is possible to synch a query database with an open database viewer. For more information see Synching a Query Database with an Open Databases Viewer.
A query database is opened by the Open Database for Querying statement. You supply the full path of the database and a prefix for named variables which are automatically produced describing the characteristics of the database. You optionally specify a named variable prefix to be used with the numerous named variables which are automatically produced when a query database is opened. You also specify the conditions as to how the key column is determined.
When a database is first opened, named variables are created which describe the properties of the file. The named variables created are described in Open Database for Querying.
Example:
Open Database for Querying "\v1" (Yate Track ID) named variables -> Query Database
All other query database statements must reference a previously opened database. Information on how to identify an open query database can be found here.
Typically, query databases are automatically closed saving all modifications. If you are running in the Batch Processor, all open query databases will be closed when batch processing terminates. If you are not running in the Batch Processor, all open query database will be closed when acton processing terminates. You can explicitly close a query database which provides the option of not saving modifications. Databases are closed via the Close Query Database statement.
Examples:
Close Query Database (default) saving changes
Close Query Database "\<My Track Database>" discarding changes
The Access Query Database Cell statement is used to get set or apply the contents of a single cell in the database. Access to a cell is provided by specifying a database (optional), and a key value. The key value is used to lookup the key field established when the database was opened. An alternate for a key value is an absolute row number. Absolute row numbers are specified as #row. The rows are numbered such that the first row number is zero. Columns can either be specified symbolically or as a column number (also zero based). A symbolic column name must exactly match the name of one of the columns in the database (case sensitive). All the fields in the statement can contain any of the escape sequences described in Escape Sequences.
When getting the value of a cell, its contents are saved to a named variable. You can extract data from any cell in the database, regardless of the column type.
When setting the value of a cell, you can set it to any data you wish. You cannot modify the contents of a cell in the key column.
When applying the value of a cell, Yate will ignore any attempt to set a value based on a column which does not map to an item stored within an audio file. It will also ignore an attempt to apply the contents of the key column. If the chosen column is appropriate for applying, the contents of the cell will be applied to the current set of files. The definition of the set depends on whether the current execution mode is stepwise or grouped.
Examples:
New rows can be added to a query database by means of the Add Item to Query Database statement. You specify the key to be used to identify the new item. A duplicate key is treated as a hard error. Note that all columns except for the key field will be empty in the new item.
Examples:
Add Item to Query Database "My Track Database" Key="643E1D0C-B75D-431A-8638-356FBB8FC87F"
Add Item to Query Database (default) Key="\v1"
Rows can be removed from a query database by means of the Remove Item from Query Database statement. You specify the key to be used to identify the row to be deleted.
Examples:
Remove Item from Query Database "My Track DB" Key="643E1D0C-B75D-431A-8638-356FBB8FC87F5"
Remove Item from Query Database (default) Key="\v1"
You can issue queries against a database looking for items which match against a particular column. A large number of tests can be performed which can be textual, numeric or date comparisons. The queries are made via the Issue Database Query statement. Queries can be accumulated. The result of any one query is returned in a named variable containing a list of key values identifying rows in the database. The key values are separated by a \~ sequence. When specifying a query you can stipulate that a previous result set is to be used as the source of the query as opposed to the entire database.
Examples:
Column Value List from Query Database is another query statement which can be used to extract the unique values in a database column. The query can be based on the entire database or a previous result list returned by the Issue Database Query statement. The produced list can optionally contain the number of times a result occurs. In this case the produced list is a key-value list which can be processed by the List Key statement. The list delimiters are the default \~ and \,sequences.
Examples:
Additional Information