Working with the MME Database and SQL

Time values in the MME database

The MME's time is a 64-bit, internally-derived value that is guaranteed to be monotonically increasing, even across system restarts. This value is guaranteed on systems with or without a Real-Time Clock, and on systems on which the real-time is changed forward or backward.

This behavior permits time-based comparisons of entries in the database with other database entries, such as, for example, the lastseen and last_sync fields in the mediastores table to determine if a mediastore requires resynchronization.

The table below lists MME database table columns (fields) that use the MME's internally derived time. These fields can be compared to determine the relative sequence of events, as in the example above.

Table Columns (Fields)
folders
library
mediastores lastseen and last_sync

Note: For systems that do not have a stable real-time clock, you should enable the <TimebaseSet> configuration element. This option causes the MME to run a routine at startup to adjust its internal timebase so that all time values used in the database increase monotonically. See Database time base in the MME Configuration Guide chapter Configuring Database Behavior, and mme_timebase_set() in the MME API Library Reference..

Solutions for database deadlock issues

Database deadlock issues have been observed on some MME projects. The causes for these issues have been identified, and the solutions are described below:

Different database file attached orders

Different database file attached orders for QDB and an external SQLite client result in different locking orders, which cause database deadlocks.

Solution

To prevent database deadlocks caused by different database file attached orders, ensure that your projects lock databases in the same order as they are attached:

  1. mme (master)
  2. mme_temp
  3. mme_custom
  4. mme_library

If you don't have an mme_custom table, use this order:

  1. mme (master)
  2. mme_temp
  3. mme_library

Caution: Locking your database files in any other order causes database deadlocks.

Using a QDB client to verify attached order

Before attaching database files in an external external client, you can have the client ask QDB the attached order for the files. Below is an example of how to ask QDB the attached order of database files, and the result:

qdbc -d mme 'pragma database_list;'
Rows: 5  Cols: 3
Names: +seq+name+file+
00000: |0|main|/fs/tmpfs/mme.db|
00001: |1|temp||
00002: |2|mme_temp|/fs/tmpfs/mme_temp|
00003: |3|mme_custom|/fs/tmpfs/mme_custom.db|
00004: |4|mme_library|/fs/tmpfs/mme_library.db|

Note: If a file doesn't have a filename (row 1), then don't attach it.

Separating deadlock issues from performance issues

During the development phase of your project you should configure your systems to ensure that you are able to correctly separate performance problems from deadlock problems, and understand and solve each problem accordingly:

You can change your system configuration when you prepare your system for the production environment.

Handling of corrupt database

If an operation that uses SQLite, such as those performed by qdb_statement() or qdb_vacuum(), fails because of a corrupt database, the function now returns EBADF and logs an error. Client applications can now check for EBADF, and take appropriate steps to correct the problem with their databases.


Note: For information about checking for and correcting inconsistencies, see the information provided with the mme_sync_db_check() function in the MME API Library Reference.

Optimizing your SQL

This section provides a few tips on how to optimize your SQL when working with the MME.

SQL is very flexible and can perform the same job in many different ways. Not all SQL statements are equal, however, and it is important to optimize your client application's requests to the MME database. SQLite is fast, but it can take time to complete an operation if the query statement is not optimized.

For an overview of how to optimize SQL statements for SQLite, see SQLite Optimizer Overview on the SQLite web site www.sqlite.org.

A note about SQL statements

The QDB (qdb) resource manager is a resource manager interface on top of the SQLite database engine. Through the QDB, the MME uses SQLite to query and write to its databases. This section offers recommendations for composing queries and other SQL statements for the MME.


Note: SQL statements are not case sensitive. For example, the three queries below are equivalent:
select fid,msid,filename from library

SELECT fid,msid,filename from library

SELECT fid,msid,filename FROM library

By convention, however, we use capitals for the SQL keywords to improve the legibility of query statements: SELECT fid,msid,filename FROM library.


Design for size and limit queries

An SQL database can become very large very quickly, with hundreds of thousands of entries. The MME database is designed to scale well, but it's best to limit your queries and to design these queries to avoid duplicating information in the database tables.

Use Indexes

Indexes improve database performance. When a query is made against a table, if a column doesn't have an index, it requires a table scan; and if an unindexed column is of type TEXT, SQL will perform a full table scan string comparing all rows with the requested value.

Use JOINs carefully

Joins are convenient, but they don't scale well and are often much slower than sub-selections for large tables, because the complexity of JOINs is exponential, while the complexity of sub-selections is linear. As you add more rows to the tables, the query sub-selection will increasingly perform better than the query with the JOIN.

The following examples produce the same results, but the statement with the sub-select is much faster, especially with larger tables.

Not recommended

SELECT fid FROM library
    INNER JOIN mediastores on library.msid = mediastores.msid
    WHERE mediastores.available = 1;

Recommended

SELECT fid FROM library
    WHERE msid IN (SELECT msid FROM mediastores WHERE available=1);

If you join two small tables that will never be large, then using a JOIN is acceptable, as it won't impact performance. However, the query with the JOIN won't scale well and performance will cause performance to degrade if either one of the tables increases in size.

Filtering out unavailable tracks

Media files on external devices, such as a PFS device, remain in the MME library after the device has been removed from the system. The exception to this rule is if the device and its files are pruned from the library to keep the MME database within its configured size limits. For information about database pruning, see Database pruning in the MME Configuration Guide. In addition, files that were synchronized but are later found to be unplayable remain in the library, though they are marked as unplayable. For more information, see Marking unplayable tracks in the chapter Playback Errors.

To avoid building track sessions with tracks that aren't available, which could cause gaps in playback, your client application should filter out tracks on unavailable mediastores when it builds its track sessions. It should include either WHERE available=1 or WHERE active=1 in its select statement. The example query statement below selects all tracks in the playlist “Favorites” that are on available mediastores:

SELECT fid FROM playlistdata WHERE
    plid = (SELECT plid from playlists WHERE name = 'Favorites')
    AND msid IN (SELECT msid FROM mediastores WHERE available=1);