Introduction to the Tuner Database

The Tuner database uses an open-source, freeware database called SQLite (see www.sqlite.org). The source version is 2.8.16. An entire LVST db exists as a single file; all associated audio files, etc. are stored in the database, so back-ups and transporting are as simple as copying the database file to the new location.

SQLite has several caveats you may not be familiar with. The first is that the database is not intended to support simultaneous multi-user access, which means you should regard the database as single-user only. If other users need access to the same database, you must make a copy for each separate user.

The second caveat is that 2.8.16 supports a subset of SQL-92, which imposes certain constraints on queries; see the SQLite home page for details.

Finally, binary data stored in the database (such as audio data, or precompiled grammar objects) must be stored as a string, not as plain binary. Binary data often contains NULL characters, as well as single quotes; NULL characters will cause the data to be truncated incorrectly, while single quotes have special significance within SQL. Both characters have to be handled correctly for binary data to successfully reside in the database. The mechanism we use to handle NULLs and quotes is built-in to the SQLite library, in the form of sqlite_encode_binary, and sqlite_decode_binary . The Tuner dlls all use these functions automatically, but, if you work with the database independently from the LVST, you will have to encode and decode the binary data manually. The tables and columns that contain binary data are detailed in the individual table documentation; currently, only tblLVST_GrammarContent and tblLVST_AudioData store audio data, although it is possible to store binary data in tblLVST_CustomApplicationCallData and tblLVST_CustomApplicationInteractionData.

Naming Conventions

In general, names in the database indicate what the entity is, but here are some particulars:

Primary Key identifiers have an ID suffix:

• tblLVST_Calls.CallID
• tblLVST_CallInteractions.InteractionID

Table fields which link to tblLVST_StringTable do so via tblLVST_StringTable.StringID; these fields are prefixed with an st:

• tblLVST_CallInteractions.stEvent
• tblLVST_InterKeyValue.stKeyID
• tblLVST_InterKeyValue.stValueID

Table fields which are intended to be a database constant are linked to the appropriate constant name via the source table name plus _ plus the source tables field name. The related value is ConstantValue; you can recover the name of the constant using ConstantName. The source field names are prefixed with a c:

• tblLVST_Audio.cAudioFormat -> tblLVST_Audio_cAudioFormat.ConstantValue
• tblLVST_GrammarContent.cGContentFormat -> tblLVST_GrammarContent_cGContentFormat.ConstantValue

Regular Relationships

In general, relationships between tables are based on a single ID as the foreign key.

You can identify a call and interaction by combining the unique call identifier (with LumenVox, this is the CallGUID, Nuance uses SessionID), and the name of the interaction (see tblLVST_CallInteractions.Interaction for a description of how to work with names). This yields the InteractionID; InteractionID is used in nearly every table in the database to join information with a particular call and interaction.

Two exceptions to single ID foreign keys are tblLVST_Transcripts (for storing transcriptions) and tblLVST_ScoringProtocol (for finding scoring information for transcription). Transcripts vary according to the interaction (InteractionID), the kind of transcription (cTranscriptType), and the version (Version). These three keys are the foreign key for tblLVST_ScoringProtocol.

For more information, see the precise descriptions of the database tables.