almostm4/nbtevo-system-dump
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
nbtevo-system-dump/sda0/opt/mm/etc/sql/mme.sql
almostm4 3a0b731bad initial push
2025-06-09 15:07:26 +02:00

1395 lines
72 KiB
SQL
Raw Permalink Blame History

-- vim: ts=3
BEGIN TRANSACTION;
--
-- Set SQLite's journal mode.
--
-- Set SQLite's journal mode.
--
-- journal_mode=memory - set to voletile RAM to avoid using RAMdisc for temp files
PRAGMA journal_mode=memory;
--
-- Set SQLite's vacuum mode.
--
-- auto_vacuum=none - to avoid using RAMdisc for temp files
PRAGMA auto_vacuum=full;
-- The QDB information for this database
CREATE TABLE _qdb_info_ (
version INTEGER NOT NULL
);
INSERT INTO _qdb_info_(version) VALUES(1010);
-- *******************************************************************************
-- *******************************************************************************
-- @table CONTROLCONTEXTS
--
-- The <fname>controlcontexts</fname> table defines MME control contexts. Control
-- contexts define where clients can connect to the MME and control it.
-- Each control context can play one media track at a time, manage its own list of
-- items to play, and output playback to one zone.
--
-- Control contexts are statically configured and enumerated at startup time.
--
-- @field ccid The control context ID.
-- @field trksessionid The ID of the tracksession that is being used on the control
-- context.
-- @field zoneid The ID of the zone to which the control context is outputting
-- playback.
-- @field rendid The ID of the renderer that this control context should use.
-- @field name The name of the control context. This name will appear
-- as /dev/mme/<var>name</var>
-- *******************************************************************************
-- *******************************************************************************
CREATE TABLE controlcontexts (
ccid INTEGER PRIMARY KEY AUTOINCREMENT,
trksessionid INTEGER DEFAULT 0 NOT NULL REFERENCES trksessions,
zoneid INTEGER DEFAULT 0 NOT NULL REFERENCES zones,
rendid INTEGER NOT NULL REFERENCES renderers,
name TEXT NOT NULL UNIQUE
);
-- *******************************************************************************
-- *******************************************************************************
-- @table RENDERERS
--
-- The <fname>renderers</fname> table defines the <fname>io-media</fname> instances
-- that exist in the system, and the capabilities of these <fname>io-media</fname>
-- instances.
-- A control context uses the specified <fname>io-media</fname> to decoding and
-- encode work.
--
-- @field rendid The ID of the renderer instance.
-- @field available The renderer availability. Set to 1 if this renderer can be
-- used, 0 if it can't be used.
-- @field permanent Permanent renderers may not be removed.
-- @field path The path to the renderer. For example:
-- <fname>/net/</fname><var>node</var><fname>/dev/io-media</fname>.
-- *******************************************************************************
-- *******************************************************************************
CREATE TABLE renderers (
rendid INTEGER PRIMARY KEY AUTOINCREMENT,
available INTEGER DEFAULT 1 NOT NULL,
permanent INTEGER DEFAULT 1 NOT NULL,
path TEXT NOT NULL
);
-- *******************************************************************************
-- *******************************************************************************
-- @table ZONES
--
-- The <fname>zones</fname> table defines the MME zones. The output devices
-- associated with a zone are listed in the <fname>zoneoutputs</fname> table.
--
-- @field zoneid The ID of the zone
-- @field name The name of the zone
-- *******************************************************************************
-- *******************************************************************************
CREATE TABLE zones (
zoneid INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE
);
-- *******************************************************************************
-- *******************************************************************************
-- @table ZONEOUTPUTS
--
-- The <fname>zoneoutputs</fname> table lists the output devices that are associated
-- with each zone. Each <var>zoneid</var> can have multiple rows to support multiple
-- output devices.
--
-- @field zoneid The ID of the zone.
-- @field outputdeviceid The IDs of the output devices in the zone.
-- *******************************************************************************
-- *******************************************************************************
CREATE TABLE zoneoutputs (
zoneid INTEGER NOT NULL REFERENCES zones,
outputdeviceid INTEGER NOT NULL REFERENCES outputdevices
);
-- *******************************************************************************
-- *******************************************************************************
-- @table OUTPUTDEVICES
--
-- The <fname>outputdevices</fname> table lists known output devices. Output
-- devices define where media can be sent. An output device could be a GF layer, an
-- <fname>io-audio</fname> PCM name, a Bluetooth headset, etc.
--
-- @field outputdeviceid The ID of the output device.
-- @field type The type of device, as defined by the enumerated type
-- <dtype>mme_outputtype_t</dtype> values: <const>OUTPUTTYPE_*</const>.
-- @field available The availability of the output device. Set to 1 for available.
-- @field permanent The device permanency. Set to 1 to make the device permanent
-- and forbid its removal.
-- @field name The name of the device. This name can be shared with end users.
-- @field devicepath The location of the output device, used to connect to the
-- output device. This path is not shared with end users.
-- *******************************************************************************
-- *******************************************************************************
CREATE TABLE outputdevices (
outputdeviceid INTEGER PRIMARY KEY AUTOINCREMENT,
type INTEGER DEFAULT 0 NOT NULL,
available INTEGER DEFAULT 1 NOT NULL,
permanent INTEGER NOT NULL,
name TEXT NOT NULL,
devicepath TEXT NOT NULL
);
-- *******************************************************************************
-- *******************************************************************************
-- @table SLOTS
-- The <fname>slots</fname> table lists the slots known to the MME. Slots define the
-- physical locations where the MME looks for new mediastores. The default setup
-- assumes two USB mass storage devices, one CD/DVD drive, and the hard drive.
-- You may wish to customize where the location of the hard drive. In addition, if
-- you add control contexts and they have their own slots, you must add them to
-- this table. Note that the local control context's hard drive must be the first
-- entry in the table, with <var>msid</var> = 1.
--
-- @field slotid The ID for the slot.
-- @field active Indicates whether the slot is active (available), or
-- unavailable:
-- * 1 = active
-- @field msid The ID of the mediastore associated with this slot.
-- @field slottype The type of slot. These correspond to the
-- <const>MME_SLOTTYPE_*</const> types defined in <fname sh>mme/interface.h</fname>:
-- * 0 = standard
-- * 1 = USB
-- * 2 = CD/DVD
-- * 3 = harddrive
-- * 4 = media file system (<cmd>io-fs</cmd>)
-- @field zoneid The ID of the zone associated with this slot.
-- @field max_lib_entries The maximum number of library table entries an
-- active media store in this slot is permitted to use.
-- A value of 0 means there is no limit enforced.
-- @delete_at_start If non-zero, mediastores that were listed as active
-- at shutdown in this slot are deleted instead of
-- being set to unvavailable.
-- @field path The filesystem path to this slot.
-- @field name The slot name. This name is used as the default for mediastores
-- without names.
-- *******************************************************************************
-- *******************************************************************************
CREATE TABLE slots (
slotid INTEGER PRIMARY KEY AUTOINCREMENT,
active INTEGER DEFAULT 0 NOT NULL,
msid INTEGER DEFAULT 0 NOT NULL REFERENCES mediastores,
multimsid INTEGER DEFAULT 0 NOT NULL,
slottype INTEGER DEFAULT 0 NOT NULL,
zoneid INTEGER NOT NULL REFERENCES zones,
max_lib_entries INTEGER DEFAULT 0 NOT NULL,
delete_at_start INTEGER DEFAULT 0 NOT NULL,
path TEXT NOT NULL,
name TEXT DEFAULT NULL
);
CREATE INDEX slots_msid_index ON slots(msid); -- important for trkcache query
-- *******************************************************************************
-- *******************************************************************************
-- @table LANGUAGES
--
-- The <fname>languages</fname> table defines strings that your application can
-- use for multi-language support.
--
-- @field language_id The language ID.
-- @field active Indicates whether this is the active (current)
-- language.
-- * 1 = active
-- @field lang_code The 2-character ISO639-1 language code.
-- @field language The language name.
-- @field unknown String for "unknown".
-- @field unknown_artist String for "unknown artist"
-- @field unknown_album String for "unknown album"
-- @field unknown_genre String for "unknown genre"
-- @field unknown_category String for "unknown category"
-- @field synchronizing String for "synchronizing"
-- @field unknown_track String for building unknown title of CDDA and DVD-Audio tracks
-- @field unknown_chapter String for building unknown title of DVD-Video tracks
-- @field unknown_title String for building unknown title of DVD-Video tracks
-- @field unknown_group String for building unknown title of DVD-Audio tracks
-- *******************************************************************************
-- *******************************************************************************
CREATE TABLE languages (
language_id INTEGER PRIMARY KEY AUTOINCREMENT,
active INTEGER DEFAULT 0 NOT NULL,
lang_code TEXT NOT NULL,
language TEXT NOT NULL,
unknown TEXT,
unknown_artist TEXT,
unknown_album TEXT,
unknown_genre TEXT,
unknown_category TEXT,
synchronizing TEXT,
unknown_language TEXT,
unknown_track TEXT DEFAULT NULL,
unknown_chapter TEXT DEFAULT NULL,
unknown_title TEXT DEFAULT NULL,
unknown_group TEXT DEFAULT NULL,
-- NBT Specific: composer, conductor, soloist, ensemble, opus
unknown_composer TEXT DEFAULT NULL
--unknown_conductor TEXT DEFAULT NULL,
--unknown_soloist TEXT DEFAULT NULL,
--unknown_ensemble TEXT DEFAULT NULL,
--unknown_opus TEXT DEFAULT NULL
);
-- *******************************************************************************
-- *******************************************************************************
-- @table MediaStores
-- The <fname>mediastores</fname> table lists the mediastores known to the MME.
-- A mediastore is a collection of media tracks and/or files that the MME can
-- access and play. Where a slot is the physical location of some media
-- (for example, a CDROM drive), a mediastore represents the media itself
-- (for example, a CD).
-- <p>
-- Mediastores are managed by the MME, so you don't need to customize this table.
--
-- @field msid The mediastore ID.
-- @field slotid The ID of the physical slot associated with this mediastore.
-- @field available Indicates whether the mediastore is available:
-- * 0 = not available,
-- * 1 = available
-- @field storage_type The storage type, which corresponds to the
-- <const>MME_STORAGETYPE_*</const> types defined in
-- <fname sh>mme/interface.h</fname>.
-- @field lastseen The last time the mediastore was seen by the MME.
-- If there is no RTC in the system, this value will increment
-- each time the mediastore is seen, but it will not show the
-- true time.
-- @field capabilities The capabilities of this mediastore (for example, can it be
-- explored or synchronized?). These capabilities correspond to the
-- <const>MME_MSCAP_*</const> type defined in <fname sh>mme/interface.h</fname>.
-- @field name The name of the mediastore (for example,
-- <quote>memory stick</quote>. This field may be null if the
-- mediastore name cannot be determined.
-- @field identifier A unique identifier, such as the FAT serial number. Set to
-- NULL to flag the mediastore as invalid and ready to be
-- deleted in the background.
-- @field driver_identifier A unique identifier, as provided by the device driver.
-- @field mountpath The mounted path of the mediastore.
-- @field trksessionid The last tracksession that was saved on this mediastore.
-- @field active Indicates if a mediastore is active, or if a slot change
-- is required. A mediastore can not be active if it is not
-- available:
-- * 0 = not active,
-- * 1 = active (i.e. currently active slot in a changer)
-- @field location The location of the device where the mediastore is currently
-- inserted:
-- * empty = not currently inserted in a slot, or is in
-- a device for which location has no meaning
-- * non-empty = location string that has meaning only to
-- the device (devices that only support one location
-- will always be set to empty)
-- @field last_sync The time (in nanoseconds from the reference) of the last
-- synchronization attempt of any time on the mediastore.
-- @field syncflags Indicates which synchronizations have been completed on the
-- mediastore:
-- * 0 = none (not synchronized)
-- * 1 = pass 1 (files)
-- * 2 = pass 2 (metadata)
-- * 4 = pass 3 (playlists)
-- * others to be determined
-- @field supported Indicates if the device is supported:
-- * 0 = not supported
-- * 1 = supported
-- 2=<quote>two readers</quote>, etc.
-- @field metadatapluginid The metadataplugin that was used to sync the
-- mediastore. 0 means not specified.
-- @field mssname Internal use only. The MSS plugin that handles this mediastore.
-- *******************************************************************************
-- *******************************************************************************
CREATE TABLE mediastores (
msid INTEGER PRIMARY KEY AUTOINCREMENT,
slotid INTEGER DEFAULT 0 NOT NULL REFERENCES slots,
available INTEGER DEFAULT 0 NOT NULL,
storage_type INTEGER DEFAULT 0 NOT NULL,
trksessionid INTEGER DEFAULT 0 NOT NULL REFERENCES trksessions,
lastseen INTEGER DEFAULT 0 NOT NULL,
capabilities INTEGER DEFAULT 0 NOT NULL,
active INTEGER DEFAULT 0 NOT NULL,
location TEXT,
syncflags INTEGER DEFAULT 0 NOT NULL,
supported INTEGER DEFAULT 1 NOT NULL,
last_sync INTEGER DEFAULT 0 NOT NULL,
metadatapluginid INTEGER DEFAULT 0 NOT NULL REFERENCES metadataplugins,
mssname TEXT NOT NULL,
name TEXT DEFAULT NULL,
identifier TEXT,
driver_identifier TEXT,
mountpath TEXT NOT NULL,
-- custom extension required by CoMMCo MediaManager
medium_content INTEGER DEFAULT 0,
content_type INTEGER DEFAULT 0,
-- custom extension required by PreCtrl
pcslotid INTEGER DEFAULT 0 NOT NULL,
pcsernum TEXT DEFAULT '' NOT NULL
);
CREATE INDEX mediastores_identifier_index ON mediastores(identifier);
CREATE INDEX mediastores_driver_identifier_index ON mediastores(driver_identifier);
CREATE INDEX mediastores_active_index ON mediastores(active);
-- *******************************************************************************
-- *******************************************************************************
-- @table metadataplugins
-- The <fname>metadataplugins</fname> table lists the metadata syncronizers
-- known to the MME.
--
-- @field metadatapluginid The metadata plugin ID.
-- @field name The name of the metadata plugin.
-- *******************************************************************************
-- *******************************************************************************
CREATE TABLE metadataplugins (
metadatapluginid INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL
);
-- *******************************************************************************
-- *******************************************************************************
-- @table Playlists
-- The <fname>playlists</fname> table holds playlists that your application can
-- convert into track sessions and play. A playlist is a collection of media tracks.
-- Each playlist is defined by an SQL statement that queries the library for tracks
-- that meet some criteria. Alternately, the SQL statement may query the
-- <fname>playlistdata</fname> table, which can contain an arbitrary selection of
-- tracks, grouped by a matching playlist ID.
--
-- @field plid The playlist ID.
-- @field ownership Indicates who owns this playlist:
-- * 0 = owned by the MME
-- * 1 = owned by the device
-- * 2 = owned by the user
-- @field folderid The ID of the folder that the playlist is in.
-- @field msid A link to a mediastore. If this playlist belongs to more
-- than one mediastore, then this msid is 0.
-- @field mode The playlist mode:
-- * 0 = library mode
-- * 1 = generated mode
-- @field seen Indicates that the file was seen during the latest
-- synchronization. This field is set to 0 at the beginning
-- of a synchronization, then set to 1 when the file is found.
-- @field date_modified The date this playlist was last modified.
-- @field size The size of the playlist file on the device.
-- @field filename If the playlist points to a device, the filename of the
-- playlist on the device. This name is a path relative to
-- the basepath of the folder.
-- @field accurate If this field is set to 1, the playlist is accurate.
-- @field last_sync The time (in nanoseconds from the reference) of the
-- last playlist (pass 3) synchronization attempt for the
-- playlist.
-- @field name The playlist name.
-- @field signature md5 hash of the playlist.
-- @field statement An SQL statement that returns a list of file IDs
-- (<var>fid</var>s), either from the <fname>library</fname>
-- table, or from the <fname>playlistdata</fname> table.
-- @field seed_data Used by playlist generators (i.e. mode = 1)
-- *******************************************************************************
-- *******************************************************************************
CREATE TABLE playlists (
plid INTEGER PRIMARY KEY AUTOINCREMENT,
ownership INTEGER DEFAULT 0 NOT NULL,
folderid INTEGER DEFAULT 0 NOT NULL REFERENCES folders,
msid INTEGER DEFAULT 0 NOT NULL REFERENCES mediastores,
mode INTEGER DEFAULT 0 NOT NULL,
seen INTEGER DEFAULT 1 NOT NULL,
date_modified INTEGER DEFAULT 0 NOT NULL,
accurate INTEGER DEFAULT 0 NOT NULL,
last_sync INTEGER DEFAULT 0 NOT NULL,
size INTEGER DEFAULT 0 NOT NULL,
signature TEXT DEFAULT '0' NOT NULL,
filename TEXT DEFAULT '' NOT NULL,
name TEXT NOT NULL,
statement TEXT,
seed_data TEXT
);
-- *******************************************************************************
-- *******************************************************************************
-- @table trksessions
-- The <fname>trksessions</fname> table stores track sessions, which are lists of
-- file IDs(<var>fid</var>s) that the MME can access and play. A track session can
-- be generated by using a playlist, or by any query to the <fname>library</fname>
-- table that results in a list of file IDs (selecting all tracks by an artist,
-- for example).
-- <caution>Your application shouldn't write to this table directly. It can create
-- track sessions by calling the <xref xid="api/mme_newtrksession.html">
-- <func>mme_newtrksession</func></xref> function.
--
-- <p>The fields in the <fname>trksessions</fname> table should only be accessed
-- through MME function calls. The MME may cache some of the values in this table,
-- so if the client application reads this table directly it may have incorrect data.
-- </caution>
-- @field zoneid The ID of the control context with which this track session
-- is associated.
-- @field track_offset Internal use only.
-- @field saved_offset The saved fid used to resume the trksession (0 = not saved).
-- @field savedposition The saved position in a <var>fid</var>/<var>bid</var> that
-- can be used for resuming playback.
-- @field mode The track session mode:
-- * 0 = library mode
-- @field tvcomplete Indicates if the track view was finished loading:
-- * 0 = no
-- * 1 = yes
-- @field statement The SQL statement that results in a list of file IDs that
-- the track session plays.
-- *******************************************************************************
-- *******************************************************************************
CREATE TABLE trksessions (
trksessionid INTEGER PRIMARY KEY AUTOINCREMENT,
track_offset INTEGER DEFAULT 0 NOT NULL,
saved_offset INTEGER DEFAULT 0 NOT NULL,
savedposition BLOB DEFAULT 0 NOT NULL,
mode INTEGER DEFAULT 0 NOT NULL,
random INTEGER NOT NULL DEFAULT 0,
repeat INTEGER NOT NULL DEFAULT 0,
tvcomplete INTEGER NOT NULL DEFAULT 0,
statement TEXT NOT NULL
);
-- *******************************************************************************
-- *******************************************************************************
-- @table encodeformats
--
-- The <fname>encodeformats</fname> table defines encode formats that can be used
-- by the MME.
-- Note that codecs that support multiple mime types or multiple bitrates will have
-- separate entries in this table.
-- @field encodeformatid The endcode format ID.
-- @field name The name for the encode format.
-- @field mime The mime type to use.
-- @field bitrate The bitrate to encode at, in kilobytes.
-- @field extension The output file extension.
-- *******************************************************************************
-- *******************************************************************************
CREATE TABLE encodeformats (
encodeformatid INTEGER PRIMARY KEY AUTOINCREMENT,
bitrate INTEGER DEFAULT 0,
name TEXT UNIQUE NOT NULL,
mime TEXT NOT NULL,
extension TEXT
);
-- *******************************************************************************
-- *******************************************************************************
-- @table copyqueue
--
-- The <fname>copyqueue</fname> table is a queue of files to copy from one mediastore
-- to another. While the files are being copied, they may also be encoded
-- (<quote>ripped</quote>). If the files are encoded, the encode format is defined
-- by the <fname>encodeformats</fname> table.
-- @field cqid Copy queue ID.
-- @field ccid The ID of the control context for an item.
-- @field srcfid The ID of the source file to copy. Will be 0 if using a URL.
-- @field srcurl The URL of the source file to copy. Will be NULL if using a fid.
-- @field srcmsid The ID of the source mediastore.
-- @field destmsid The ID of the destination mediastore.
-- @field destfolder The destination folder basepath name, in the format
-- <fname>/xxxxxx/</fname>.
-- @field destfilename The destination filename. Don't add the extension. If this
-- field is <const>NULL</const>, the MME will create a name.
-- @field copyflags Copy flags supplied by user
-- @field copyattempts The number of failed copy attempts to make before removing
-- the item from the copy queue.
-- @field encodeformatid The encode format to use for the copy.
-- @field unknown_album Metadata used to replace unknown album (if nonaccurate)
-- @field unknown_artist Metadata used to replace unknown artist (if nonaccurate)
-- *******************************************************************************
-- *******************************************************************************
CREATE TABLE copyqueue (
cqid INTEGER PRIMARY KEY AUTOINCREMENT,
ccid INTEGER NOT NULL REFERENCES controlcontexts,
srcfid INTEGER NOT NULL,
srcurl TEXT DEFAULT NULL,
srcmsid INTEGER NOT NULL,
destmsid INTEGER NOT NULL,
copyflags INTEGER NOT NULL DEFAULT 0,
copyattempts INTEGER NOT NULL DEFAULT 0,
encodeformatid INTEGER NOT NULL DEFAULT 1 REFERENCES encodeformats,
destfolder TEXT,
destfilename TEXT,
unknown_album TEXT,
unknown_artist TEXT
);
-- *******************************************************************************
-- *******************************************************************************
-- @table bookmarks
--
-- The <fname>bookmarks</fname> table contains information about all bookmarks for
-- file IDs.
--
-- @field bookmarkid The bookmark ID.
-- @field fid The file ID of the bookmarked track.
-- @field msid The mediastore ID for the mediastore with the bookmarked
-- file.
-- @field btype The bookmark type (mme_bookmark_type_t).
-- @field data Data used for resuming playback at the proper location.
-- Internal use only.
-- @field name A name for the bookmark, specified with
-- <xref xid="api/mme_bookmark_create.html">
-- <func>mme_bookmark_create</func></xref>.
-- *******************************************************************************
-- *******************************************************************************
CREATE TABLE bookmarks (
bookmarkid INTEGER PRIMARY KEY AUTOINCREMENT,
fid INTEGER REFERENCES library,
msid INTEGER REFERENCES mediastores,
btype INTEGER NOT NULL DEFAULT 1,
name TEXT,
data BLOB NOT NULL
);
CREATE INDEX bookmarks_index_fid ON bookmarks(fid);
CREATE INDEX bookmarks_index_msid ON bookmarks(msid);
CREATE INDEX bookmarks_index_btype ON bookmarks(btype);
-- *******************************************************************************
-- *******************************************************************************
-- @table trksessionview
--
-- The <fname>trksessionview</fname> table contains a snapshot of the current track
-- session. All its fields are updated by the functions
-- <xref xid="api/mme_settrksession.html"><func>mme_settrksession</func></xref>
-- and <xref xid="api/mme_trksessionview_update.html"><func>mme_trksessionview_update</func></xref>.
--
-- @field sequentialid The track file IDs (<var>fid</var>) in sequential order,
-- based on the results of the ORDER BY clauses in the SQL
-- statement used to create the track session.
-- @field fid The file ID of the track.
-- @field trksessionid The track session ID.
-- @field randomid The track file IDs (<var>fid</var>), in pseudo-random order.
-- If random mode is turned on for the control context, the
-- MME will play tracks in the order they appear in this field.
-- *******************************************************************************
-- *******************************************************************************
CREATE TABLE trksessionview(
sequentialid INTEGER PRIMARY KEY AUTOINCREMENT,
fid INTEGER NOT NULL,
trksessionid INTEGER NOT NULL,
randomid INTEGER
);
CREATE INDEX trksessionview_index_random ON trksessionview(trksessionid, randomid);
CREATE INDEX trksessionview_index_seq ON trksessionview(trksessionid, sequentialid);
-- Internal use only
CREATE TABLE copy_incomplete(
fid INTEGER PRIMARY KEY
);
-- *******************************************************************************
-- *******************************************************************************
-- @table mdi_image_cache
--
-- The <fname>imagecache</fname> table contains a list of all image files
-- stored in the persistent cache.
-- @field insertion_sequence An id that keeps track of insertion order.
-- @field msid The MSID the source image file was from.
-- @field fid The file ID of the track.
-- @field image_index The image index of a given track
-- @field profile_index The profile index for a converted image. -1 not converted.
-- @field size The size in bytes of the given index.
-- @field hit_count The number of cache hits for this entry.
-- @field hit_sequence A sequence number that indicates the last hit file.
-- @field file The relative path to the file within the cache.
-- @field cache_index An index value representing the cache for multiple caches.
-- @field ext_image The cached image is external to the file.
-- *******************************************************************************
-- *******************************************************************************
CREATE TABLE mdi_image_cache(
insertion_sequence INTEGER PRIMARY KEY AUTOINCREMENT,
msid INTEGER NOT NULL,
fid INTEGER NOT NULL,
image_index INTEGER NOT NULL,
profile_index INTEGER NOT NULL,
size INTEGER NOT NULL,
hit_count INTEGER NOT NULL,
hit_sequence INTEGER NOT NULL,
file TEXT NOT NULL,
cache_index INTEGER NOT NULL DEFAULT 0,
ext_image INTEGER NOT NULL DEFAULT 0
);
-- *******************************************************************************
-- *******************************************************************************
-- @table ext_db_sync_state
--
-- The <fname>ext_db_sync_state</fname> table contains persistent state information for
-- all extern DBs that must stay in sync with the MME database.
-- @field uniquedbid Unique identifier to each plugin so it can differentiate
-- its state from that of another plugin.
-- @field msid The MSID that corresponds to the state.
-- @field data The state data of the external DB
-- *******************************************************************************
-- *******************************************************************************
CREATE TABLE ext_db_sync_state(
unique_db_id TEXT NOT NULL,
msid INTEGER NOT NULL REFERENCES mediastores,
data BLOB NOT NULL
);
-- *******************************************************************************
-- *******************************************************************************
-- @table Folders
-- The <fname>folders</fname> table stores the path of files found on mediastores and can be used
-- to hierarchically find folders.
--
-- @field folderid The folder ID for the folder.
-- @field msid The mediastore to which the folder belongs.
-- @field parentid The parent folder for this folder. Set to 0 if there is no
-- parent folder.
-- @field foldername The name of the folder (for example, <fname>Rolling Stones</fname>).
-- @field basepath The full path of the folder (for example,
-- <fname>Music/Rolling Stones</fname>).
-- @field synced If this field is set to 1, the folder has been synchronized
-- during the first synchronization pass.
-- @field last_sync Reserved for the time (in nanoseconds from the reference) of
-- the last synchronization attempt on the mediastore.
-- @field folderflags Flags providing more information about this folder.
-- @field inode Optional. The inode for the associated file.
-- @field seen A flag to indicate if the folder was seen or not seen
-- during synchronization.
-- @filecount The number of files in the folder.
-- @playlistcount The number of playlists in the folder.
-- @foldercount The number of subfolders in the folder.
-- @hash For internal use only.
-- @collisions For internal use only.
-- @collision_names For internal use only.
-- *******************************************************************************
-- *******************************************************************************
CREATE TABLE folders (
folderid INTEGER PRIMARY KEY AUTOINCREMENT,
msid INTEGER REFERENCES mediastores,
parentid INTEGER DEFAULT 0 NOT NULL,
synced INTEGER DEFAULT 0 NOT NULL,
seen INTEGER DEFAULT 1 NOT NULL,
filecount INTEGER DEFAULT 0 NOT NULL,
playlistcount INTEGER DEFAULT 0 NOT NULL,
foldercount INTEGER DEFAULT 0 NOT NULL,
last_sync INTEGER DEFAULT 0 NOT NULL,
folderflags INTEGER DEFAULT 0 NOT NULL,
-- inode INTEGER DEFAULT 0 NOT NULL,
foldername TEXT NOT NULL,
basepath TEXT NOT NULL,
hash BLOB,
collisions BLOB,
collision_names BLOB
);
-- *******************************************************************************
-- *******************************************************************************
-- @table Library
-- The <fname>library</fname> table defines the media library used by the MME.
-- Each entry in this table is a media track, which you can use to build track
-- sessions and playlists.
-- <p>
-- The <fname>library</fname> is managed by the MME, so you don't need to customize
-- it.
--
-- @field fid The file ID (<var>fid</var>) for the media track.
-- @field msid The mediastore that this track is stored on.
-- @field folderid The path in the mediastore where the track is located.
-- @field filename The file name of the media track.
-- @field ftype The type of the media track, which corresponds to the
-- <const>FTYPE_*</const> types defined in <fname sh>mme/meinterface.h</fname>:
-- * 0 = unknown
-- * 1 = audio
-- * 2 = video
-- * 3 = audio and video
-- * 4 = photo
-- @field accurate Indicates if the metadata for the track is known to be
-- accurate.
-- @field last_sync The time (in nanoseconds from the reference) of the
-- last metadata synchronization attempt for the track.
-- @field seen Indicates that the track has been identified on the mediastore.
-- @field artist_id The ID of the track artist.
-- @field title The track title.
-- @field album_id The ID of the track album.
-- @field genre_id The ID of the track genre.
-- @field year The year of the track.
-- @field size The size of the track, in bytes.
-- @field category_id The ID of the track category.
-- @field discnum The disc number of the content. This field is useful for box sets.
-- @field titlenum The title/group number of the CDDA/DVDV/DVDA.
-- @field tracknum The track/chapter number of the CDDA/DVDV/DVDA.
-- @field rating The rating (0 = unkown, 1 = worst, 255 = best). Format specific rating is
-- scaled to 1 - 255 range, for example, 1 stars = 60, 2 starts = 125, ..., 5 starts = 255)
-- @field description An arbitrary text description of the track.
-- @field date_added The date the track entry was added to the <fname>library</fname>
-- table.
-- @field date_modified The date the track entry was modified in the <fname>library</fname>
-- table.
-- @field bitrate The track bitrate.
-- @field audio_index The audio index of the track on the DVD.
-- @field format The format of the track, as defined by the
-- <xref xid="api/mme_format.html"><const>MME_FORMAT_*</const></xref> values.
-- @field num_channels The number of audio channels on the track.
-- @field language_id The ID of the track language.
-- @field samplerate The sampling rate, in hertz, of the audio stream.
-- @field protected Indicates if there is DRM on the track
-- @field inode Optional. The inode for the associated file.
-- @field last_played The date this track was last played by the MME.
-- @field fullplay_count The number of times this track has been played by the MME.
-- @field duration The track length, in milliseconds.
-- @field copied_fid The file ID of the copied file. This field is 0 if the
-- file has not been copied.
-- @field playable Indicates if the track is playable.
-- @field permanent If this field is set to 1, the file cannot be pruned.
-- @field contenttype Derived content type bitmask from ftype, filename and duration, supporting additional content types AUDIOBOOK and PODCAST.
-- @field decade Derived decade (from year)
-- *******************************************************************************
-- *******************************************************************************
CREATE TABLE library (
fid INTEGER PRIMARY KEY AUTOINCREMENT,
msid INTEGER DEFAULT 0 NOT NULL REFERENCES mediastores,
folderid INTEGER DEFAULT 0 NOT NULL REFERENCES folders,
ftype INTEGER DEFAULT 0 NOT NULL,
accurate INTEGER DEFAULT 0 NOT NULL,
last_sync INTEGER DEFAULT 0 NOT NULL,
seen INTEGER DEFAULT 1 NOT NULL,
artist_id INTEGER DEFAULT 1 NOT NULL REFERENCES library_artists,
album_id INTEGER DEFAULT 1 NOT NULL REFERENCES library_albums,
genre_id INTEGER DEFAULT 1 NOT NULL REFERENCES library_genres,
year INTEGER DEFAULT 0 NOT NULL,
size INTEGER DEFAULT 0 NOT NULL,
category_id INTEGER DEFAULT 1 NOT NULL REFERENCES library_categories,
discnum INTEGER DEFAULT 0 NOT NULL,
titlenum INTEGER DEFAULT 0 NOT NULL,
tracknum INTEGER DEFAULT 0 NOT NULL,
rating INTEGER DEFAULT 0 NOT NULL,
date_added INTEGER DEFAULT 0 NOT NULL,
date_modified INTEGER DEFAULT 0 NOT NULL,
bitrate INTEGER DEFAULT 0 NOT NULL,
audio_index INTEGER DEFAULT 0 NOT NULL,
format INTEGER DEFAULT 0 NOT NULL,
num_channels INTEGER DEFAULT 0 NOT NULL,
language_id INTEGER DEFAULT 1 NOT NULL REFERENCES library_languages,
samplerate INTEGER DEFAULT 0 NOT NULL,
protected INTEGER DEFAULT 0 NOT NULL,
last_played INTEGER DEFAULT 0 NOT NULL,
fullplay_count INTEGER DEFAULT 0 NOT NULL,
duration INTEGER DEFAULT 0 NOT NULL,
copied_fid INTEGER DEFAULT 0 NOT NULL,
playable INTEGER DEFAULT 1 NOT NULL,
permanent INTEGER DEFAULT 0 NOT NULL,
-- The following fields are an optional block. To enable them, uncomment
-- here and look for ExtendedMetadata in the mme.conf file.
album_artist_id INTEGER DEFAULT 1 NOT NULL REFERENCES library_albumartists,
album_genre_id INTEGER DEFAULT 1 NOT NULL REFERENCES library_albumgenres,
album_title_id INTEGER DEFAULT 1 NOT NULL REFERENCES library_albumtitles,
keywords TEXT DEFAULT '' NOT NULL,
mood_id INTEGER DEFAULT 1 NOT NULL REFERENCES library_moods,
bpm INTEGER DEFAULT 0 NOT NULL,
compilation INTEGER DEFAULT 0 NOT NULL,
media_type INTEGER DEFAULT 0 NOT NULL,
-- NBT Specific: composer, conductor, soloist, ensemble, opus
composer_id INTEGER DEFAULT 1 NOT NULL REFERENCES library_composers,
--conductor_id INTEGER DEFAULT 1 NOT NULL REFERENCES library_conductors,
--soloist_id INTEGER DEFAULT 1 NOT NULL REFERENCES library_soloists,
--ensemble_id INTEGER DEFAULT 1 NOT NULL REFERENCES library_ensembles,
--opus_id INTEGER DEFAULT 1 NOT NULL REFERENCES library_opus,
-- End optional block.
-- inode INTEGER DEFAULT 0 NOT NULL,
album_secret_id INTEGER DEFAULT 1 NOT NULL REFERENCES library_albums_secret,
artist_secret_id INTEGER DEFAULT 1 NOT NULL REFERENCES library_artists_secret,
description TEXT DEFAULT '' NOT NULL,
title TEXT DEFAULT NULL,
title_secret TEXT DEFAULT NULL,
filename TEXT DEFAULT '' NOT NULL,
contenttype INTEGER DEFAULT 0 NOT NULL,
decade INTEGER DEFAULT 0 NOT NULL,
title_asia TEXT DEFAULT NULL,
genitemid INTEGER DEFAULT NULL UNIQUE,
title_normal TEXT DEFAULT NULL
);
CREATE TABLE library_genres (
genre_id INTEGER PRIMARY KEY AUTOINCREMENT,
genre TEXT,
genre_asia TEXT,
genre_normal TEXT
);
CREATE INDEX library_genres_index_genre on library_genres(genre);
CREATE TABLE library_artists (
artist_id INTEGER PRIMARY KEY AUTOINCREMENT,
artist TEXT,
artist_asia TEXT,
artist_normal TEXT
);
CREATE TABLE library_artists_secret (
artist_secret_id INTEGER PRIMARY KEY AUTOINCREMENT,
artist_secret TEXT
);
CREATE INDEX library_artists_index_artist on library_artists(artist);
CREATE TABLE library_albums (
album_id INTEGER PRIMARY KEY AUTOINCREMENT,
album TEXT,
album_asia TEXT,
album_normal TEXT
);
CREATE TABLE library_albums_secret (
album_secret_id INTEGER PRIMARY KEY AUTOINCREMENT,
album_secret TEXT
);
CREATE INDEX library_albums_index_album on library_albums(album);
CREATE TABLE library_categories (
category_id INTEGER PRIMARY KEY AUTOINCREMENT,
category TEXT,
category_asia TEXT
);
CREATE INDEX library_categories_index_category on library_categories(category);
CREATE TABLE library_languages (
language_id INTEGER PRIMARY KEY AUTOINCREMENT,
language TEXT UNIQUE
);
-- See the extended metadata note in the library table
CREATE TABLE library_moods (
mood_id INTEGER PRIMARY KEY AUTOINCREMENT,
mood TEXT
);
CREATE INDEX library_moods_index_mood on library_moods(mood);
CREATE TABLE library_albumartists (
album_artist_id INTEGER PRIMARY KEY AUTOINCREMENT,
album_artist TEXT
);
CREATE INDEX library_albumartists_index_album_artist on library_albumartists(album_artist);
CREATE TABLE library_albumgenres (
album_genre_id INTEGER PRIMARY KEY AUTOINCREMENT,
album_genre TEXT
);
CREATE INDEX library_albumgenres_index_album_genre on library_albumgenres(album_genre);
CREATE TABLE library_albumtitles (
album_title_id INTEGER PRIMARY KEY AUTOINCREMENT,
album_title TEXT
);
CREATE INDEX library_albumtitles_index_album_title on library_albumtitles(album_title);
-- NBT Specific: composer
CREATE TABLE library_composers (
composer_id INTEGER PRIMARY KEY AUTOINCREMENT,
composer TEXT,
composer_asia TEXT,
composer_normal TEXT
);
CREATE INDEX library_composers_index_composer on library_composers(composer);
-- NBT Specific: conductors
--CREATE TABLE library_conductors (
-- conductor_id INTEGER PRIMARY KEY AUTOINCREMENT,
-- conductor TEXT
-- );
-- CREATE INDEX library_moods_index_mood on library_moods(mood);
--
--CREATE TABLE library_composers (
-- composer_id INTEGER PRIMARY KEY AUTOINCREMENT,
-- composer TEXT
-- );
-- CREATE INDEX library_composers_index_composer on library_composers(composer);
-- *******************************************************************************
-- *******************************************************************************
-- @table exif
-- The <fname>exif</fname> table contains EXIF metadata found by the MME.
-- <p>
-- This table depends on the <fname>library</fname> table in that rows in this
-- table cannot exist unless there is a correspoding row in the
-- <fname>library</fname> table. However, this table is sparse. That is, it
-- contains rows only for those library table items for which EXIF metadata
-- has been found.
-- Column names match those provided by the library used to extract the exif
-- metadata.
-- Rows in this table are created or updated during metadata synchronization
-- passes on the media store on which the referenced files are found.
-- The <fname>exif</fname> is managed by the MME, so you don't need to customize
-- it.
--
-- @field fid The file ID (<var>fid</var>) for the item; refers
-- a row in the library table.
-- @field GPSVersionID The GPS version ID.
-- @field GPSMapDatum The GPS The geodetic survey data used.
-- @field GPSAreaInformation The name of the GPS area.
-- @field GPSLatitude The latitude, not including the reference.
-- @field GPSLatitude The latitude, not including the reference.
-- @field GPSLatitudeRef The latitude reference.
-- @field GPSLongitude The longitude, not including the reference.
-- @field GPSLongitudeRef The longitude reference.
-- @field ImageWidth The number of columns of image data. If unknown, empty.
-- @field ImageLength The number of rows of image data. If unknown, empty.
-- @field Orientation The image orientation viewed in terms of rows and
-- columns. If unknown, empty.
-- *******************************************************************************
-- *******************************************************************************
CREATE TABLE exif (
fid INTEGER PRIMARY KEY REFERENCES library,
GPSVersionID TEXT DEFAULT '' NOT NULL,
GPSMapDatum TEXT DEFAULT '' NOT NULL,
GPSAreaInformation TEXT DEFAULT '' NOT NULL,
GPSLatitude TEXT DEFAULT '' NOT NULL,
GPSLatitudeRef TEXT DEFAULT '' NOT NULL,
GPSLongitude TEXT DEFAULT '' NOT NULL,
GPSLongitudeRef TEXT DEFAULT '' NOT NULL,
ImageWidth TEXT DEFAULT '' NOT NULL,
ImageLength TEXT DEFAULT '' NOT NULL,
Orientation TEXT DEFAULT '' NOT NULL
);
-- *******************************************************************************
-- *******************************************************************************
-- @table db_sync
-- The <fname>db_sync</fname> table is used by the generic handler for external
-- database synchronization plugins. It should be considered private to the MME.
--
-- @field msid The ID media store that the library table entry is on.
-- @field fid The ID of a library table entry that synchronizers
-- have been told about. (<var>fid</var>).
-- @field last_sync The internal timestamp value when the external database
-- synchronizers were last told about this file.
-- *******************************************************************************
-- *******************************************************************************
CREATE TABLE db_sync (
msid INTEGER NOT NULL REFERENCES mediastores,
fid INTEGER NOT NULL REFERENCES library,
last_sync INTEGER DEFAULT 0 NOT NULL
);
-- *******************************************************************************
-- *******************************************************************************
-- @table playlistdata
-- The <fname>playlistdata</fname> table is available for storing any linear created
-- playlists. They can be selected using the <quote>statement</quote> from the
-- <fname>playlists</fname> table.
--
-- @field plid The ID of the playlist to which this track belongs.
-- @field fid The track file ID (<var>fid</var>).
-- @field oid An order identifier. This can be used to assign an arbitrary
-- order to the playlist using the SQL <const>ORDER BY</const> clause.
-- @field unresolved_entry_text The entry from the playlist that could not be
-- resolved, but that matches the configured regex.
-- *******************************************************************************
-- *******************************************************************************
CREATE TABLE playlistdata (
oid INTEGER PRIMARY KEY AUTOINCREMENT,
plid INTEGER NOT NULL REFERENCES playlists,
fid INTEGER NOT NULL REFERENCES library,
msid INTEGER NOT NULL REFERENCES mediastores,
unresolved_entry_text TEXT DEFAULT NULL
);
-- *******************************************************************************
-- *******************************************************************************
-- INDEXES
--
-- Below are examples of indexes. You should remove indexes that you do not use,
-- and add indexes that you do use. Performance can be greatly increased when
-- reading from tables if the columns that are being used for <const>WHERE</const>
-- and <const>ORDER BY</const> have indexes. <const>PRIMARY KEY</const> columns
-- do not need an index created on them.
-- However, be warned:
-- 1/ Adding indices slows down table insertion, and may significantly increase
-- synchronization times.
-- 2/ The presence of excessive indices has even been observed to increase
-- lookup times.
-- Therefore, the recommendation is to use indices sparingly and carefully.
-- *******************************************************************************
-- *******************************************************************************
--For first pass resync when there are changes (bfsrecurse; signiticant savings)
CREATE INDEX library_index_folderid_msid_filename ON library(folderid,msid,filename);
-- This helps first pass resync (bfsrecurse)
CREATE INDEX folders_index_parentid ON folders(parentid);
-- *******************************************************************************
-- *******************************************************************************
-- TRIGGERS
--
-- The MME relies on the following triggers to maintain a consistent database.
-- *******************************************************************************
-- *******************************************************************************
-- Clear copied_fid when deleting a fid
CREATE INDEX library_index_copiedfid ON library(copied_fid);
CREATE TRIGGER library_trigger_delete DELETE ON library
BEGIN
UPDATE library SET copied_fid=0 WHERE copied_fid=OLD.fid;
END;
-- *******************************************************************************
-- *******************************************************************************
-- @table Now Playing
-- The <fname>nowplaying</fname> table holds information about the currently playing
-- or last played track for a control context. This information is maintained by
-- the MME: your client application can query it this table, but shouldn't write to
-- it. You can query this table when your client receives a
-- <const>MME_EVENT_TRACKCHANGE</const> event indicating that a new track is
-- playing. The information may be limited by the metadata available, so some fields
-- may not contain data for every track.
-- <note>The MME doesn't clear this table after a track stops playing, so if there's
-- no playing track, it contains information about the last played track.</note>
--
-- @field ccid The ID for the control context where the track is currently playing.
-- @field playing Reserved for future use.
-- @field fid The file ID (<var>fid</var>) for the track (0 if unknown).
-- @field msid The ID of the mediastore with the track.
-- @field ftype The track's file type. See the <fname>ftype</fname> field in the
-- <xref xid="#library"><fname>library</fname> table</xref>.
-- @field year The track's year.
-- @field bitrate The track's bitrate, in bytes per second.
-- @field samplerate The track's samplerate, in hertz.
-- @field num_channels The track's number of channels: 1=mono, 2=stereo.
-- @field size The track's size, in bytes.
-- @field discnum The track's disc number.
-- @field tracknum If the track is part of a collection (i.e. an album), the track's
-- @field rating The rating (0 = unkown, 1 = worst, 255 = best).
-- number in the collection.
-- @copied_fid The file ID for the copied file, placed in the
-- <fname>library</fname> table by media copy and ripping
-- operations.
-- @field filename The filename of the track (empty string if unknown).
-- @field artist The track's artist.
-- @field title The track's title.
-- @album album The track's album.
-- @field genre The track's genre.
-- @field category The track's category.
-- @field description The track's description.
-- *******************************************************************************
-- *******************************************************************************
CREATE TABLE nowplaying (
ccid INTEGER PRIMARY KEY REFERENCES controlcontexts,
playing INTEGER DEFAULT 0 NOT NULL,
fid INTEGER DEFAULT 0 NOT NULL REFERENCES library,
msid INTEGER DEFAULT 0 NOT NULL REFERENCES mediastores,
ftype INTEGER DEFAULT 0 NOT NULL,
year INTEGER DEFAULT 0 NOT NULL,
bitrate INTEGER DEFAULT 0 NOT NULL,
samplerate INTEGER DEFAULT 0 NOT NULL,
num_channels INTEGER DEFAULT 0 NOT NULL,
size INTEGER DEFAULT 0 NOT NULL,
discnum INTEGER DEFAULT 0 NOT NULL,
tracknum INTEGER DEFAULT 0 NOT NULL,
rating INTEGER DEFAULT 0 NOT NULL,
copied_fid INTEGER DEFAULT 0 NOT NULL,
filename TEXT DEFAULT '' NOT NULL,
artist TEXT DEFAULT '',
title TEXT DEFAULT '',
album TEXT DEFAULT '',
genre TEXT DEFAULT '',
-- The following fields are an optional block. To enable them, uncomment
-- here and look for ExtendedMetadata in the mme.conf file.
album_artist TEXT DEFAULT '',
album_genre TEXT DEFAULT '',
album_title TEXT DEFAULT '',
keywords TEXT DEFAULT '',
mood TEXT DEFAULT '',
bpm INTEGER DEFAULT 0 NOT NULL,
compilation INTEGER DEFAULT 0 NOT NULL,
media_type INTEGER DEFAULT 0 NOT NULL,
-- NBT Specific: composer, conductor, soloist, ensemble, opus
composer TEXT DEFAULT '',
--conductor TEXT DEFAULT '',
--soloist TEXT DEFAULT '',
--ensemble TEXT DEFAULT '',
--opus TEXT DEFAULT '',
-- End optional block.
category TEXT DEFAULT '',
description TEXT DEFAULT ''
);
-- ===============================================================================
-- LANGUAGE CONFIGURATION
-- ===============================================================================
INSERT INTO languages (language,lang_code) VALUES ('Abkhaz','ab');
INSERT INTO languages (language,lang_code) VALUES ('Afar','aa');
INSERT INTO languages (language,lang_code) VALUES ('Afrikaans','af');
INSERT INTO languages (language,lang_code) VALUES ('Akan','ak');
INSERT INTO languages (language,lang_code) VALUES ('Albanian','sq');
INSERT INTO languages (language,lang_code) VALUES ('Amharic','am');
INSERT INTO languages (language,lang_code) VALUES ('Arabic','ar');
INSERT INTO languages (language,lang_code) VALUES ('Aragonese','an');
INSERT INTO languages (language,lang_code) VALUES ('Armenian','hy');
INSERT INTO languages (language,lang_code) VALUES ('Assamese','as');
INSERT INTO languages (language,lang_code) VALUES ('Avaric','av');
INSERT INTO languages (language,lang_code) VALUES ('Avestan','ae');
INSERT INTO languages (language,lang_code) VALUES ('Aymara','ay');
INSERT INTO languages (language,lang_code) VALUES ('Azerbaijani','az');
INSERT INTO languages (language,lang_code) VALUES ('Bambara','bm');
INSERT INTO languages (language,lang_code) VALUES ('Bashkir','ba');
INSERT INTO languages (language,lang_code) VALUES ('Basque','eu');
INSERT INTO languages (language,lang_code) VALUES ('Belarusian','be');
INSERT INTO languages (language,lang_code) VALUES ('Bengali','bn');
INSERT INTO languages (language,lang_code) VALUES ('Bihari','bh');
INSERT INTO languages (language,lang_code) VALUES ('Bislama','bi');
INSERT INTO languages (language,lang_code) VALUES ('Bosnian','bs');
INSERT INTO languages (language,lang_code) VALUES ('Breton','br');
INSERT INTO languages (language,lang_code) VALUES ('Bulgarian','bg');
INSERT INTO languages (language,lang_code) VALUES ('Burmese','my');
INSERT INTO languages (language,lang_code) VALUES ('Catalan','ca');
INSERT INTO languages (language,lang_code) VALUES ('Chamorro','ch');
INSERT INTO languages (language,lang_code) VALUES ('Chechen','ce');
INSERT INTO languages (language,lang_code) VALUES ('Chichewa','ny');
INSERT INTO languages (language,lang_code) VALUES ('Chinese','zh');
INSERT INTO languages (language,lang_code) VALUES ('Chuvash','cv');
INSERT INTO languages (language,lang_code) VALUES ('Cornish','kw');
INSERT INTO languages (language,lang_code) VALUES ('Corsican','co');
INSERT INTO languages (language,lang_code) VALUES ('Cree','cr');
INSERT INTO languages (language,lang_code) VALUES ('Croatian','hr');
INSERT INTO languages (language,lang_code) VALUES ('Czech','cs');
INSERT INTO languages (language,lang_code) VALUES ('Danish','da');
INSERT INTO languages (language,lang_code) VALUES ('Divehi','dv');
INSERT INTO languages (language,lang_code) VALUES ('Dutch','nl');
INSERT INTO languages (language,lang_code) VALUES ('Dzongkha','dz');
INSERT INTO languages (language,lang_code) VALUES ('English','en');
INSERT INTO languages (language,lang_code) VALUES ('Esperanto','eo');
INSERT INTO languages (language,lang_code) VALUES ('Estonian','et');
INSERT INTO languages (language,lang_code) VALUES ('Ewe','ee');
INSERT INTO languages (language,lang_code) VALUES ('Faroese','fo');
INSERT INTO languages (language,lang_code) VALUES ('Fijian','fj');
INSERT INTO languages (language,lang_code) VALUES ('Finnish','fi');
INSERT INTO languages (language,lang_code) VALUES ('French','fr');
INSERT INTO languages (language,lang_code) VALUES ('Fula','ff');
INSERT INTO languages (language,lang_code) VALUES ('Galician','gl');
INSERT INTO languages (language,lang_code) VALUES ('Ganda','lg');
INSERT INTO languages (language,lang_code) VALUES ('Georgian','ka');
INSERT INTO languages (language,lang_code) VALUES ('German','de');
INSERT INTO languages (language,lang_code) VALUES ('Greek, Modern','el');
INSERT INTO languages (language,lang_code) VALUES ('Guaran<EFBFBD>','gn');
INSERT INTO languages (language,lang_code) VALUES ('Gujarati','gu');
INSERT INTO languages (language,lang_code) VALUES ('Haitian','ht');
INSERT INTO languages (language,lang_code) VALUES ('Hausa','ha');
INSERT INTO languages (language,lang_code) VALUES ('Hebrew','he');
INSERT INTO languages (language,lang_code) VALUES ('Herero','hz');
INSERT INTO languages (language,lang_code) VALUES ('Hindi','hi');
INSERT INTO languages (language,lang_code) VALUES ('Hiri Motu','ho');
INSERT INTO languages (language,lang_code) VALUES ('Hungarian','hu');
INSERT INTO languages (language,lang_code) VALUES ('Icelandic','is');
INSERT INTO languages (language,lang_code) VALUES ('Ido','io');
INSERT INTO languages (language,lang_code) VALUES ('Igbo','ig');
INSERT INTO languages (language,lang_code) VALUES ('Indonesian','id');
INSERT INTO languages (language,lang_code) VALUES ('Interlingua','ia');
INSERT INTO languages (language,lang_code) VALUES ('Interlingue','ie');
INSERT INTO languages (language,lang_code) VALUES ('Inuktitut','iu');
INSERT INTO languages (language,lang_code) VALUES ('Inupiaq','ik');
INSERT INTO languages (language,lang_code) VALUES ('Irish','ga');
INSERT INTO languages (language,lang_code) VALUES ('Italian','it');
INSERT INTO languages (language,lang_code) VALUES ('Japanese','ja');
INSERT INTO languages (language,lang_code) VALUES ('Javanese','jv');
INSERT INTO languages (language,lang_code) VALUES ('Kalaallisut','kl');
INSERT INTO languages (language,lang_code) VALUES ('Kannada','kn');
INSERT INTO languages (language,lang_code) VALUES ('Kanuri','kr');
INSERT INTO languages (language,lang_code) VALUES ('Kashmiri','ks');
INSERT INTO languages (language,lang_code) VALUES ('Kazakh','kk');
INSERT INTO languages (language,lang_code) VALUES ('Khmer','km');
INSERT INTO languages (language,lang_code) VALUES ('Kikuyu','ki');
INSERT INTO languages (language,lang_code) VALUES ('Kinyarwanda','rw');
INSERT INTO languages (language,lang_code) VALUES ('Kirundi','rn');
INSERT INTO languages (language,lang_code) VALUES ('Komi','kv');
INSERT INTO languages (language,lang_code) VALUES ('Kongo','kg');
INSERT INTO languages (language,lang_code) VALUES ('Korean','ko');
INSERT INTO languages (language,lang_code) VALUES ('Kurdish','ku');
INSERT INTO languages (language,lang_code) VALUES ('Kwanyama, Kuanyama','kj');
INSERT INTO languages (language,lang_code) VALUES ('Kyrgyz','ky');
INSERT INTO languages (language,lang_code) VALUES ('Lao','lo');
INSERT INTO languages (language,lang_code) VALUES ('Latin','la');
INSERT INTO languages (language,lang_code) VALUES ('Latvian','lv');
INSERT INTO languages (language,lang_code) VALUES ('Limburgish','li');
INSERT INTO languages (language,lang_code) VALUES ('Lingala','ln');
INSERT INTO languages (language,lang_code) VALUES ('Lithuanian','lt');
INSERT INTO languages (language,lang_code) VALUES ('Luba-Katanga','lu');
INSERT INTO languages (language,lang_code) VALUES ('Luxembourgish','lb');
INSERT INTO languages (language,lang_code) VALUES ('Macedonian','mk');
INSERT INTO languages (language,lang_code) VALUES ('Malagasy','mg');
INSERT INTO languages (language,lang_code) VALUES ('Malay','ms');
INSERT INTO languages (language,lang_code) VALUES ('Malayalam','ml');
INSERT INTO languages (language,lang_code) VALUES ('Maltese','mt');
INSERT INTO languages (language,lang_code) VALUES ('Manx','gv');
INSERT INTO languages (language,lang_code) VALUES ('Marathi','mr');
INSERT INTO languages (language,lang_code) VALUES ('Marshallese','mh');
INSERT INTO languages (language,lang_code) VALUES ('Mongolian','mn');
INSERT INTO languages (language,lang_code) VALUES ('Maori','mi');
INSERT INTO languages (language,lang_code) VALUES ('Montenegro','me');
INSERT INTO languages (language,lang_code) VALUES ('Nauru','na');
INSERT INTO languages (language,lang_code) VALUES ('Navajo','nv');
INSERT INTO languages (language,lang_code) VALUES ('Ndonga','ng');
INSERT INTO languages (language,lang_code) VALUES ('Nepali','ne');
INSERT INTO languages (language,lang_code) VALUES ('North Ndebele','nd');
INSERT INTO languages (language,lang_code) VALUES ('Northern Sami','se');
INSERT INTO languages (language,lang_code) VALUES ('Norwegian','no');
INSERT INTO languages (language,lang_code) VALUES ('Norwegian Bokm<6B>l','nb');
INSERT INTO languages (language,lang_code) VALUES ('Norwegian Nynorsk','nn');
INSERT INTO languages (language,lang_code) VALUES ('Nuosu','ii');
INSERT INTO languages (language,lang_code) VALUES ('Occitan','oc');
INSERT INTO languages (language,lang_code) VALUES ('Ojibwe, Ojibwa','oj');
INSERT INTO languages (language,lang_code) VALUES ('Old Church Slavonic','cu');
INSERT INTO languages (language,lang_code) VALUES ('Oriya','or');
INSERT INTO languages (language,lang_code) VALUES ('Oromo','om');
INSERT INTO languages (language,lang_code) VALUES ('Ossetian','os');
INSERT INTO languages (language,lang_code) VALUES ('Panjabi','pa');
INSERT INTO languages (language,lang_code) VALUES ('Pashto','ps');
INSERT INTO languages (language,lang_code) VALUES ('Persian','fa');
INSERT INTO languages (language,lang_code) VALUES ('Polish','pl');
INSERT INTO languages (language,lang_code) VALUES ('Portuguese','pt');
INSERT INTO languages (language,lang_code) VALUES ('Pali','pi');
INSERT INTO languages (language,lang_code) VALUES ('Quechua','qu');
INSERT INTO languages (language,lang_code) VALUES ('Romanian','ro');
INSERT INTO languages (language,lang_code) VALUES ('Romansh','rm');
INSERT INTO languages (language,lang_code) VALUES ('Russian','ru');
INSERT INTO languages (language,lang_code) VALUES ('Samoan','sm');
INSERT INTO languages (language,lang_code) VALUES ('Sango','sg');
INSERT INTO languages (language,lang_code) VALUES ('Sanskrit','sa');
INSERT INTO languages (language,lang_code) VALUES ('Sardinian','sc');
INSERT INTO languages (language,lang_code) VALUES ('Scottish','gd');
INSERT INTO languages (language,lang_code) VALUES ('Serbian','sr');
INSERT INTO languages (language,lang_code) VALUES ('Shona','sn');
INSERT INTO languages (language,lang_code) VALUES ('Sindhi','sd');
INSERT INTO languages (language,lang_code) VALUES ('Sinhala','si');
INSERT INTO languages (language,lang_code) VALUES ('Slovak','sk');
INSERT INTO languages (language,lang_code) VALUES ('Slovene','sl');
INSERT INTO languages (language,lang_code) VALUES ('Somali','so');
INSERT INTO languages (language,lang_code) VALUES ('South Ndebele','nr');
INSERT INTO languages (language,lang_code) VALUES ('Southern Sotho','st');
INSERT INTO languages (language,lang_code) VALUES ('Spanish','es');
INSERT INTO languages (language,lang_code) VALUES ('Sundanese','su');
INSERT INTO languages (language,lang_code) VALUES ('Swahili','sw');
INSERT INTO languages (language,lang_code) VALUES ('Swati','ss');
INSERT INTO languages (language,lang_code) VALUES ('Swedish','sv');
INSERT INTO languages (language,lang_code) VALUES ('Tagalog','tl');
INSERT INTO languages (language,lang_code) VALUES ('Tahitian','ty');
INSERT INTO languages (language,lang_code) VALUES ('Tajik','tg');
INSERT INTO languages (language,lang_code) VALUES ('Tamil','ta');
INSERT INTO languages (language,lang_code) VALUES ('Tatar','tt');
INSERT INTO languages (language,lang_code) VALUES ('Telugu','te');
INSERT INTO languages (language,lang_code) VALUES ('Thai','th');
INSERT INTO languages (language,lang_code) VALUES ('Tibetan Standard','bo');
INSERT INTO languages (language,lang_code) VALUES ('Tigrinya','ti');
INSERT INTO languages (language,lang_code) VALUES ('Tonga','to');
INSERT INTO languages (language,lang_code) VALUES ('Tsonga','ts');
INSERT INTO languages (language,lang_code) VALUES ('Tswana','tn');
INSERT INTO languages (language,lang_code) VALUES ('Turkish','tr');
INSERT INTO languages (language,lang_code) VALUES ('Turkmen','tk');
INSERT INTO languages (language,lang_code) VALUES ('Twi','tw');
INSERT INTO languages (language,lang_code) VALUES ('Ukrainian','uk');
INSERT INTO languages (language,lang_code) VALUES ('Urdu','ur');
INSERT INTO languages (language,lang_code) VALUES ('Uyghur','ug');
INSERT INTO languages (language,lang_code) VALUES ('Uzbek','uz');
INSERT INTO languages (language,lang_code) VALUES ('Venda','ve');
INSERT INTO languages (language,lang_code) VALUES ('Vietnamese','vi');
INSERT INTO languages (language,lang_code) VALUES ('Volap<EFBFBD>k','vo');
INSERT INTO languages (language,lang_code) VALUES ('Walloon','wa');
INSERT INTO languages (language,lang_code) VALUES ('Welsh','cy');
INSERT INTO languages (language,lang_code) VALUES ('Western Frisian','fy');
INSERT INTO languages (language,lang_code) VALUES ('Wolof','wo');
INSERT INTO languages (language,lang_code) VALUES ('Xhosa','xh');
INSERT INTO languages (language,lang_code) VALUES ('Yiddish','yi');
INSERT INTO languages (language,lang_code) VALUES ('Yoruba','yo');
INSERT INTO languages (language,lang_code) VALUES ('Zhuang','za');
INSERT INTO languages (language,lang_code) VALUES ('Zulu','zu');
update languages SET unknown='Unknown', unknown_artist='', unknown_album='', unknown_genre='', unknown_category='', synchronizing='Synchronizing',unknown_language='Unknown',unknown_track='', unknown_chapter='', unknown_title='', unknown_group='', unknown_composer='';
-- Select the default language
UPDATE languages SET active=1 WHERE language_id=1;
-- NBT Specific BEgin
INSERT INTO encodeformats(name, mime) VALUES('copy', 'copy');
INSERT INTO encodeformats(name, mime, bitrate, extension) VALUES('wave', 'wav', 0, '.wav');
--INSERT INTO encodeformats(name, mime, bitrate, extension) VALUES('Cinemo WMA', 'wma', 0, '.wma');
INSERT INTO encodeformats(name, mime, bitrate, extension) VALUES('Cinemo AAC', 'AAC-LC', 192000, '.aac');
INSERT INTO encodeformats(name, mime, bitrate, extension) VALUES('SPEEX', 'SPEEX', 0, '.speex');
--NBT Specific End
-- *******************************************************************************
-- *******************************************************************************
-- REQUIRED DEFAULT DATA
-- ** Do not customize this section **
-- *******************************************************************************
-- *******************************************************************************
-- Unknown is always 1
INSERT INTO library_genres(genre_id, genre)
VALUES(1, (SELECT unknown_genre FROM languages WHERE active=1));
INSERT INTO library_artists(artist_id, artist)
VALUES(1, (SELECT unknown_artist FROM languages WHERE active=1));
INSERT INTO library_artists_secret(artist_secret_id, artist_secret)
VALUES(1, (SELECT unknown_artist FROM languages WHERE active=1));
INSERT INTO library_albums(album_id, album)
VALUES(1, (SELECT unknown_album FROM languages WHERE active=1));
INSERT INTO library_albums_secret(album_secret_id, album_secret)
VALUES(1, (SELECT unknown_album FROM languages WHERE active=1));
INSERT INTO library_categories(category_id, category)
VALUES(1, (SELECT unknown_category FROM languages WHERE active=1));
INSERT INTO library_languages(language_id, language)
VALUES(1, (SELECT unknown_language FROM languages WHERE active=1));
INSERT INTO library_composers(composer_id, composer)
VALUES(1, (SELECT unknown_composer FROM languages WHERE active=1));
INSERT INTO library_albumartists(album_artist_id, album_artist)
VALUES(1, (SELECT unknown_artist FROM languages WHERE active=1));
--INSERT INTO library_conductors(conductor_id, conductor)
-- VALUES(1, (SELECT unknown_conductor FROM languages WHERE active=1));
--INSERT INTO library_soloists(soloist_id, soloist)
-- VALUES(1, (SELECT unknown_soloist FROM languages WHERE active=1));
--INSERT INTO library_ensembles(ensemble_id, ensemble)
-- VALUES(1, (SELECT unknown_ensemble FROM languages WHERE active=1));
--INSERT INTO library_opus(opus_id, opus)
-- VALUES(1, (SELECT unknown_opus FROM languages WHERE active=1));
INSERT INTO bookmarks(name, data) VALUES('row is only for dvd lum', '');
-- *****************
-- Optional TRIGGERS
-- *****************
---- Library Triggers
-- Do not reintegrate tiggers, its project specific
CREATE TRIGGER library_insert AFTER INSERT ON library
BEGIN
UPDATE library SET contenttype=deriveContentType(NEW.ftype, NEW.duration, (SELECT genre FROM library_genres WHERE genre_id=NEW.genre_id), NEW.filename) WHERE fid=NEW.fid;
END;
CREATE TRIGGER library_update AFTER UPDATE ON library
BEGIN
UPDATE library SET contenttype=deriveContentType(NEW.ftype, NEW.duration, (SELECT genre FROM library_genres WHERE genre_id=NEW.genre_id), NEW.filename) WHERE fid=NEW.fid;
END;
COMMIT;
Reference in New Issue View Git Blame Copy Permalink