SQLite Viewer - MetaDb_Module

The module MetaDb_Module implements the virtual tables MediaLibrary and Playlist. The virtual table will be created with CREATE VIRTUAL TABLE <TableName> USING MetaDB_Module([parameter,...]);

The module accepts the following optional parameters:

add_defaults Specify, that all missing tags will be added to the table definition before the creation of the virtual table
no_multivalue_split Virtual tables, which are created with this parameter ignore the attribute split multivalue tag completely
medialibrary
Specify, that the virtual table implements access to the media library. This parameter is the default and can be omitted.
playlist
Specify, that the virtual table implements access to the playlists. It can only be omitted for accessing the playlists, if the table name is the same as the parameter name

Automatic index

For all fields of the types Tag, TechInfo and TFExpression and for the predefined Playlist fields playlist_name, playlist_index and playlist_item an automatic index will be used in a query, if these fields are not part of an expression and if they are used with the operators =, <, <=, > or >=, e.g. for artist='Genesis' such an automatic index will be used. This will decrease the query time related to the relevant field in many cases up to 50%.

In some cases the query time might increase. Here the use of the automatic index can be disabled by adding an empty string to a the relevant field, e.g. artist||''='Genesis'.

DML support

!!! Caution: Use the functionality described below with care. If used wrongly you can destroy your tags and/or your playlists. !!!

For the virtual tables of the types medialibrary and playlist UPDATE operations are supported. It requires, that the relevant virtual table is created with the parameter no_multivalue_split, like this:

CREATE VIRTUAL TABLE MediaLibrary_Updatable USING MetaDB_Module(no_multivalue_split);
CREATE VIRTUAL TABLE Playlist_Updatable USING MetaDB_Module(no_multivalue_split,playlist);

These virtual tables should be created in addition to the standard virtual tables. The UPDATE statement on such a table will only prepare the update of the tags. The actual update will be done with committing the changes. This means reverting changes with a rollback is not possible.

For the virtual table of the type playlist also DELETE and INSERT operations are supported. As for the UPDATE operations, the affected virtual tables needs be created with the parameter no_multivalue_split.

Deleting entries from the Playlist table will result in removing items from the relevant playlists. For example, the statement DELETE FROM Playlist_Updatable WHERE artist='Michael Jackson' will delete the items where the artist is "Michael Jackson" from all playlists, which have such items.

Inserting entries into the Playlist table will result in adding items to the relevant playlists. Only the columns metadb_handle, playlist_index and playlist_name will be considered for the INSERT statement. All other columns will be ignored. The column metadb_handle is mandatory, the column playlist_name will be ignored, if the column playlist_index is present. If neither playlist_index nor playlist_name is used the target playlist will be "SQLite Viewer playlist". If the specified playlist doesn't exist, it will be created. Some examples:

Adding all songs from "Metallica" to each playlist:

INSERT INTO Playlist_Updatable(metadb_handle)
SELECT metadb_handle
FROM MediaLibrary
WHERE artist='Metallica'

Adding all songs from "Metallica" to the playlists "Metallica" and "Heavy Metal"

INSERT INTO Playlist_Updatable(metadb_handle,playlist_name)
SELECT a.metadb_handle,b.playlist_name
FROM MediaLibrary a, (SELECT 'Metallica' "playlist_name" UNION ALL SELECT 'Heavy Metal') b
WHERE artist='Metallica'

As for updating tags, changing the playlists in that way will take effect with committing the changes. Reverting these changes can be done playlist-wise with the standard Undo from the Edit menu.