This function is used to open a database so it can be used for querying.
The path may contain any of the escape sequences described in Escape Sequences. At runtime, the path to a database must be absolute (/) or relative to the home folder (~/) and must have a filename extension of .csv, .tsv or .tab.
If the specified path does not exist or if any errors occur while opening the file, action processing or batch processing will be terminated.
Every database opened for querying typically has one column which contains a unique value for each item (row). This is called the key column. There are a number of choices as to how the key column is specified:
- Yate Track ID
- The first column containing a Yate Track ID becomes the key column.
- Yate Album ID
- The first column containing a Yate Album ID becomes the key column.
- In file
- This mode extracts the key column information from the database sidecar file. This means that you must have manually assigned the key column in the Database Viewer. If a key column has not been assigned, the database will be opened keyless.
- Keyless
- This is a special mode where there is no key column. This limits the way some of the associated query database statements operate.
- Column Number
- You supply the number of the column to be used as the key column. (0, 1, 2, ...)
- Column Name
- You supply the name of the column (case sensitive) to be used as the key column. The column specification may contain any of the escape sequences described in Escape Sequences.
Leading and trailing spaces are always ignored in keys. Further, you can specify that keys are to be treated as case insensitive and/or diacritic insensitive. If you have an insensitivity option set and the database has two items which are the same based on the insensitivity options, the uniqueness requirement will be broken.
If you wish to make a determination at runtime as to which column should be key, you can always read the database sidecar file before opening the database. You can do this via the Read Database Sidecar File statement.
Typically when a query database is opened it reads the database from the file system. You can elect to grab a database if it is already open in the UI. This allows for interaction between the action and the open database. The Grab open database option is a request. If the database is open, it will be grabbed. If it is not open, the open will proceed as if the grab was not requested. When a query database grabs an open database all changes are made directly to the open database. Named Variables are created which allow for interaction with the open database including a list of selected rows. Note that when a database is grabbed, you are working with the unfiltered list of rows regardless of the current filter.
As mentioned, when grabbing a database the query database will be opened regardless as to whether the database was open in the UI or not. You can elect to set the Open in UI option which will open the database in the UI if not already open, prior to attempting the grab. Unless the database cannot be opened (in which case the action test state will be false), the grab will succeed. Note that when a database is opened in this manner, the loading of thumbnails will be halted while the database is grabbed.
If the Open in UI option is set, the database can be opened as readonly. If the database was already open, it will still be made readonly. When a database is readonly, no structural changes or editing is possible. Nor can the database be closed while an action is running.
When a query database is opened, named variables are automatically created. You specify a prefix for the names. This prefix is the preferred method of referencing open query databases in other statements. If the prefix is empty, Query Database is assumed. A prefix, empty or not, must be unique. The prefix field may contain any of the escape sequences described in Escape Sequences. The following named variables are created:
- prefix-Names
- A list containing the column names (headers).
- prefix-Encoding
- A list containing information to determine the field type associated with a column. See the Read Database Sidecar File statement for a description of the format.
- prefix-Qualifiers
- A list containing information to qualify multi item field types associated with a column. See the Read Database Sidecar File statement for a description of the format.
- prefix-DB Type
- The type of the database. The named variable will contain one of: track, album or standalone.
- prefix-Key Column
- The column number of the key field. The value will be -1 if the Keyless option was chosen.
- prefix-Last Row
- A properly formatted value which can be used as a parameter to statements requiring a key parameter. The last item in the database will be referenced.
- prefix-Path
- The unescaped and possibly expanded path used to open the file. Do not change modify this value.
- prefix-Row Count
- The number of rows (items) in the database.
- prefix-Grabbing
- If a grab was successful this item will be created with a value of 1. If you test the numeric value of this named variable you can determine if a query database was opened by grabbing a database open in the UI.
- prefix-Selected Rows
- If a grab was successful this item will contain a list of indexes representing the rows selected in the grabbed UI database. If the open database was filtered, the row indexes will be adjusted to represent the indexes in the unfiltered database. Each index is preceded by a # so that the list represents absolute row references. The list is delimited by the default list delimiter (\~).
- UI-Clicked Column
- When an action is run from a database viewer, this named variable will contain the name of the column which was clicked to display the context menu.
- UI-Clicked Row
- When an action is run from a database viewer, this named variable will contain the number of the row which was clicked to display the context menu. The row number is preceded by a # so that it is valid to use as a key. Note that this row will always be included in the UI-Selected Rows list.
An attempt to open a query database which is already open will be ignored. In this case the action test state will be set to false. If the database is successfully opened, the action test state will be set to true. If the database is not opened because it is already open, a named variable with the supplied prefix (or Query Database if the prefix was empty), will be set to the prefix of the pre-opened database. eg. if the failed open had a prefix of DB2 and the previously opened database had a prefix of DB1, named variable DB2 will have a value of DB1.
Query databases are automatically closed when either the Batch Processor terminates or if not batch processing, when action processing terminates. Modifications are automatically saved. If you wish to discard modifications, you can use the Close Query Database statement. Note that databases opened in the UI are not automatically closed.
If you run an action from a Database Viewer's Run Action menu, this statement is automatically executed at the start of the action. The opened query database has an identifying prefix of UI, is keyless and is opened as grabbing. Additional information can be found in Synching a Query Database with an Open Database Viewer.
Click here for information on identifying an open query database.