176 lines
7.1 KiB
SQL
176 lines
7.1 KiB
SQL
BEGIN EXCLUSIVE;
|
|
|
|
-- *******************************************************************************
|
|
-- *******************************************************************************
|
|
-- @table iap2_last_revision
|
|
--
|
|
-- The iap2_metadata contains metadata specific to ipod tracks.
|
|
--
|
|
-- @field id The ID to replace with the last revision.
|
|
-- @field rev The last successful revision.
|
|
-- *******************************************************************************
|
|
-- *******************************************************************************
|
|
CREATE TABLE iap2_last_revision (
|
|
id INTEGER PRIMARY KEY DEFAULT 1,
|
|
rev INTEGER NOT NULL
|
|
);
|
|
|
|
-- *******************************************************************************
|
|
-- *******************************************************************************
|
|
-- @table iap2_metadata
|
|
--
|
|
-- The iap2_metadata contains metadata specific to ipod tracks.
|
|
--
|
|
-- @field fid The file ID (fid) for the media track.
|
|
-- @field uid iPod track unique ID (cast to a uint64_t after reading
|
|
-- this value from the database).
|
|
-- @field type iPod track type
|
|
-- @field rating iPod rating
|
|
-- @field ondevice If track is resident on device
|
|
-- @field chp_count Chapter count
|
|
-- @field composer Index to the iap2_composers table
|
|
-- @field albumartist Index to the iap2_albumartists table
|
|
-- @field album_uid Index to the iap2_album_uids table
|
|
-- @field artist_uid Index to the iap2_artist_uids table
|
|
-- @field genre_uid Index to the iap2_genre_uids table
|
|
-- @field composer_uid Index to the iap2_composer_uids table
|
|
-- @field albumartist_uid Index to the iap2_albumartist_uids table
|
|
-- *******************************************************************************
|
|
-- *******************************************************************************
|
|
CREATE TABLE iap2_metadata (
|
|
fid INTEGER PRIMARY KEY NOT NULL REFERENCES files,
|
|
uid TEXT DEFAULT NULL,
|
|
type TEXT DEFAULT NULL,
|
|
rating TEXT DEFAULT NULL,
|
|
ondevice TEXT DEFAULT NULL,
|
|
chp_count INTEGER DEFAULT 0 NOT NULL,
|
|
composer INTEGER DEFAULT 1 NOT NULL REFERENCES iap2_composers,
|
|
albumartist INTEGER DEFAULT 1 NOT NULL REFERENCES iap2_albumartists,
|
|
album_uid INTEGER DEFAULT 1 NOT NULL REFERENCES iap2_album_uids,
|
|
artist_uid INTEGER DEFAULT 1 NOT NULL REFERENCES iap2_artist_uids,
|
|
genre_uid INTEGER DEFAULT 1 NOT NULL REFERENCES iap2_genre_uids,
|
|
composer_uid INTEGER DEFAULT 1 NOT NULL REFERENCES iap2_composer_uids,
|
|
albumartist_uid INTEGER DEFAULT 1 NOT NULL REFERENCES iap2_albumartist_uids
|
|
);
|
|
|
|
|
|
-- *******************************************************************************
|
|
-- *******************************************************************************
|
|
--
|
|
-- iPod metadata support table
|
|
--
|
|
-- *******************************************************************************
|
|
-- *******************************************************************************
|
|
CREATE TABLE iap2_composers (
|
|
composer_id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
composer TEXT
|
|
);
|
|
CREATE INDEX iap2_composers_index_composer ON iap2_composers(composer);
|
|
-- Unknown is always 1
|
|
INSERT INTO iap2_composers(composer_id, composer)
|
|
VALUES(1, NULL);
|
|
|
|
CREATE TABLE iap2_albumartists (
|
|
albumartist_id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
albumartist TEXT
|
|
);
|
|
CREATE INDEX iap2_albumartists_index_albumartist ON iap2_albumartists(albumartist);
|
|
-- Unknown is always 1
|
|
INSERT INTO iap2_albumartists(albumartist_id, albumartist)
|
|
VALUES(1, NULL);
|
|
|
|
CREATE TABLE iap2_album_uids (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
album_uid TEXT
|
|
);
|
|
CREATE INDEX iap2_album_uids_index_album_uid ON iap2_album_uids(album_uid);
|
|
-- Unknown is always 1
|
|
INSERT INTO iap2_album_uids(id, album_uid)
|
|
VALUES(1, NULL);
|
|
|
|
CREATE TABLE iap2_artist_uids (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
artist_uid TEXT
|
|
);
|
|
CREATE INDEX iap2_artist_uids_index_artist_uid ON iap2_artist_uids(artist_uid);
|
|
-- Unknown is always 1
|
|
INSERT INTO iap2_artist_uids(id, artist_uid)
|
|
VALUES(1, NULL);
|
|
|
|
CREATE TABLE iap2_genre_uids (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
genre_uid TEXT
|
|
);
|
|
CREATE INDEX iap2_genre_uids_index_genre_uid ON iap2_genre_uids(genre_uid);
|
|
-- Unknown is always 1
|
|
INSERT INTO iap2_genre_uids(id, genre_uid)
|
|
VALUES(1, NULL);
|
|
|
|
CREATE TABLE iap2_composer_uids (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
composer_uid TEXT
|
|
);
|
|
CREATE INDEX iap2_composer_uids_index_composer_uid ON iap2_composer_uids(composer_uid);
|
|
-- Unknown is always 1
|
|
INSERT INTO iap2_composer_uids(id, composer_uid)
|
|
VALUES(1, NULL);
|
|
|
|
CREATE TABLE iap2_albumartist_uids (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
albumartist_uid TEXT
|
|
);
|
|
CREATE INDEX iap2_albumartist_uids_index_albumartist_uid ON iap2_albumartist_uids(albumartist_uid);
|
|
-- Unknown is always 1
|
|
INSERT INTO iap2_albumartist_uids(id, albumartist_uid)
|
|
VALUES(1, NULL);
|
|
|
|
-- *****************
|
|
-- Required TRIGGERS
|
|
-- *****************
|
|
--
|
|
---- Metadata custom triggers --
|
|
CREATE TRIGGER iap2_metadata_delete AFTER DELETE on files
|
|
BEGIN
|
|
DELETE FROM iap2_metadata WHERE fid=OLD.fid;
|
|
END;
|
|
|
|
-- **********************
|
|
-- Optional Tables (IAP2)
|
|
-- **********************
|
|
--
|
|
-- *******************************************************************************
|
|
-- *******************************************************************************
|
|
-- @table iap2_revision_history
|
|
--
|
|
-- This table tracks the complete revision history of the iPod. It is used more
|
|
-- for debugging although technically it can be used to continue a sync from any
|
|
-- point on the iPod although this may leave the database in an incosistent state.
|
|
--
|
|
-- @field rev The revision ID from the iPod.
|
|
-- @field uid iPod UID of the last known entry before the revision.
|
|
-- @field type The type associated with the stored UID.
|
|
-- *******************************************************************************
|
|
-- *******************************************************************************
|
|
--CREATE TABLE iap2_revision_history (
|
|
-- rev INTEGER NOT NULL,
|
|
-- uid TEXT NOT NULL,
|
|
-- type INTEGER,
|
|
-- primary key(rev, uid, type)
|
|
--);
|
|
|
|
-- ***********************
|
|
-- Optional Indexes (IAP2)
|
|
-- ***********************
|
|
--
|
|
-- Indexing the files.filename field will speed up deletions and the playlist entry to filename mapping
|
|
-- done during the playlist sync at the expense of DB size (IAP2).
|
|
-- CREATE INDEX files_index_filename ON files(filename);
|
|
--
|
|
-- Indexing the playlist_entries.unresolved_entry_text field will drastically speed up playlist entry to
|
|
-- filename mapping done during the playlist sync at the expense of DB size (IAP2).
|
|
-- CREATE INDEX playlist_entries_index_unresolved_entry_text ON playlist_entries(unresolved_entry_text);
|
|
|
|
COMMIT;
|
|
|
|
|