1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
|
-- Mopidy-Local-SQLite schema upgrade v6 -> v7
BEGIN EXCLUSIVE TRANSACTION;
CREATE INDEX track_disc_no_index ON track (disc_no);
CREATE INDEX album_musicbrainz_id_index ON album (musicbrainz_id);
CREATE INDEX artist_musicbrainz_id_index ON artist (musicbrainz_id);
CREATE INDEX track_musicbrainz_id_index ON track (musicbrainz_id);
DROP VIEW search;
CREATE VIEW search AS
SELECT docid AS docid,
uri AS uri,
name AS track_name,
album_name AS album,
artist_name AS artist,
composer_name AS composer,
performer_name AS performer,
albumartist_name AS albumartist,
genre AS genre,
track_no AS track_no,
disc_no AS disc_no,
coalesce(date, album_date) AS date,
comment AS comment,
musicbrainz_id AS musicbrainz_trackid,
album_musicbrainz_id AS musicbrainz_albumid,
artist_musicbrainz_id AS musicbrainz_artistid
FROM tracks;
DROP TABLE fts;
CREATE VIRTUAL TABLE fts USING fts3 (
uri,
track_name,
album,
artist,
composer,
performer,
albumartist,
genre,
track_no,
disc_no,
date,
comment,
musicbrainz_trackid,
musicbrainz_albumid,
musicbrainz_artistid
);
DROP TRIGGER track_after_insert;
DROP TRIGGER track_after_update;
CREATE TRIGGER track_after_insert AFTER INSERT ON track
BEGIN
INSERT INTO fts (
docid,
uri,
track_name,
album,
artist,
composer,
performer,
albumartist,
genre,
track_no,
disc_no,
date,
comment,
musicbrainz_trackid,
musicbrainz_albumid,
musicbrainz_artistid
) SELECT * FROM search WHERE docid = new.rowid;
END;
CREATE TRIGGER track_after_update AFTER UPDATE ON track
BEGIN
INSERT INTO fts (
docid,
uri,
track_name,
album,
artist,
composer,
performer,
albumartist,
genre,
track_no,
disc_no,
date,
comment,
musicbrainz_trackid,
musicbrainz_albumid,
musicbrainz_artistid
) SELECT * FROM search WHERE docid = new.rowid;
END;
-- update date
INSERT INTO fts (
docid,
uri,
track_name,
album,
artist,
composer,
performer,
albumartist,
genre,
track_no,
disc_no,
date,
comment,
musicbrainz_trackid,
musicbrainz_albumid,
musicbrainz_artistid
) SELECT * FROM search;
PRAGMA user_version = 7; -- update schema version
END TRANSACTION;
|
