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
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
|
/*
This file is part of Subsonic.
Subsonic is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
Subsonic is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with Subsonic. If not, see <http://www.gnu.org/licenses/>.
Copyright 2009 (C) Sindre Mehus
*/
package net.sourceforge.subsonic.dao.schema;
import net.sourceforge.subsonic.Logger;
import org.springframework.jdbc.core.JdbcTemplate;
/**
* Used for creating and evolving the database schema.
* This class implements the database schema for Subsonic version 4.7.
*
* @author Sindre Mehus
*/
public class Schema47 extends Schema {
private static final Logger LOG = Logger.getLogger(Schema47.class);
@Override
public void execute(JdbcTemplate template) {
if (template.queryForInt("select count(*) from version where version = 20") == 0) {
LOG.info("Updating database schema to version 20.");
template.execute("insert into version values (20)");
}
if (!tableExists(template, "media_file")) {
LOG.info("Database table 'media_file' not found. Creating it.");
template.execute("create cached table media_file (" +
"id identity," +
"path varchar not null," +
"folder varchar," +
"type varchar not null," +
"format varchar," +
"title varchar," +
"album varchar," +
"artist varchar," +
"album_artist varchar," +
"disc_number int," +
"track_number int," +
"year int," +
"genre varchar," +
"bit_rate int," +
"variable_bit_rate boolean not null," +
"duration_seconds int," +
"file_size bigint," +
"width int," +
"height int," +
"cover_art_path varchar," +
"parent_path varchar," +
"play_count int not null," +
"last_played datetime," +
"comment varchar," +
"created datetime not null," +
"changed datetime not null," +
"last_scanned datetime not null," +
"children_last_updated datetime not null," +
"present boolean not null," +
"version int not null," +
"unique (path))");
template.execute("create index idx_media_file_path on media_file(path)");
template.execute("create index idx_media_file_parent_path on media_file(parent_path)");
template.execute("create index idx_media_file_type on media_file(type)");
template.execute("create index idx_media_file_album on media_file(album)");
template.execute("create index idx_media_file_artist on media_file(artist)");
template.execute("create index idx_media_file_album_artist on media_file(album_artist)");
template.execute("create index idx_media_file_present on media_file(present)");
template.execute("create index idx_media_file_genre on media_file(genre)");
template.execute("create index idx_media_file_play_count on media_file(play_count)");
template.execute("create index idx_media_file_created on media_file(created)");
template.execute("create index idx_media_file_last_played on media_file(last_played)");
LOG.info("Database table 'media_file' was created successfully.");
}
if (!tableExists(template, "artist")) {
LOG.info("Database table 'artist' not found. Creating it.");
template.execute("create cached table artist (" +
"id identity," +
"name varchar not null," +
"cover_art_path varchar," +
"album_count int default 0 not null," +
"last_scanned datetime not null," +
"present boolean not null," +
"unique (name))");
template.execute("create index idx_artist_name on artist(name)");
template.execute("create index idx_artist_present on artist(present)");
LOG.info("Database table 'artist' was created successfully.");
}
if (!tableExists(template, "album")) {
LOG.info("Database table 'album' not found. Creating it.");
template.execute("create cached table album (" +
"id identity," +
"path varchar not null," +
"name varchar not null," +
"artist varchar not null," +
"song_count int default 0 not null," +
"duration_seconds int default 0 not null," +
"cover_art_path varchar," +
"play_count int default 0 not null," +
"last_played datetime," +
"comment varchar," +
"created datetime not null," +
"last_scanned datetime not null," +
"present boolean not null," +
"unique (artist, name))");
template.execute("create index idx_album_artist_name on album(artist, name)");
template.execute("create index idx_album_play_count on album(play_count)");
template.execute("create index idx_album_last_played on album(last_played)");
template.execute("create index idx_album_present on album(present)");
LOG.info("Database table 'album' was created successfully.");
}
if (!tableExists(template, "starred_media_file")) {
LOG.info("Database table 'starred_media_file' not found. Creating it.");
template.execute("create table starred_media_file (" +
"id identity," +
"media_file_id int not null," +
"username varchar not null," +
"created datetime not null," +
"foreign key (media_file_id) references media_file(id) on delete cascade,"+
"foreign key (username) references user(username) on delete cascade," +
"unique (media_file_id, username))");
template.execute("create index idx_starred_media_file_media_file_id on starred_media_file(media_file_id)");
template.execute("create index idx_starred_media_file_username on starred_media_file(username)");
LOG.info("Database table 'starred_media_file' was created successfully.");
}
if (!tableExists(template, "starred_album")) {
LOG.info("Database table 'starred_album' not found. Creating it.");
template.execute("create table starred_album (" +
"id identity," +
"album_id int not null," +
"username varchar not null," +
"created datetime not null," +
"foreign key (album_id) references album(id) on delete cascade," +
"foreign key (username) references user(username) on delete cascade," +
"unique (album_id, username))");
template.execute("create index idx_starred_album_album_id on starred_album(album_id)");
template.execute("create index idx_starred_album_username on starred_album(username)");
LOG.info("Database table 'starred_album' was created successfully.");
}
if (!tableExists(template, "starred_artist")) {
LOG.info("Database table 'starred_artist' not found. Creating it.");
template.execute("create table starred_artist (" +
"id identity," +
"artist_id int not null," +
"username varchar not null," +
"created datetime not null," +
"foreign key (artist_id) references artist(id) on delete cascade,"+
"foreign key (username) references user(username) on delete cascade," +
"unique (artist_id, username))");
template.execute("create index idx_starred_artist_artist_id on starred_artist(artist_id)");
template.execute("create index idx_starred_artist_username on starred_artist(username)");
LOG.info("Database table 'starred_artist' was created successfully.");
}
if (!tableExists(template, "playlist")) {
LOG.info("Database table 'playlist' not found. Creating it.");
template.execute("create table playlist (" +
"id identity," +
"username varchar not null," +
"is_public boolean not null," +
"name varchar not null," +
"comment varchar," +
"file_count int default 0 not null," +
"duration_seconds int default 0 not null," +
"created datetime not null," +
"changed datetime not null," +
"foreign key (username) references user(username) on delete cascade)");
LOG.info("Database table 'playlist' was created successfully.");
}
if (!columnExists(template, "imported_from", "playlist")) {
LOG.info("Database column 'playlist.imported_from' not found. Creating it.");
template.execute("alter table playlist add imported_from varchar");
LOG.info("Database column 'playlist.imported_from' was added successfully.");
}
if (!tableExists(template, "playlist_file")) {
LOG.info("Database table 'playlist_file' not found. Creating it.");
template.execute("create cached table playlist_file (" +
"id identity," +
"playlist_id int not null," +
"media_file_id int not null," +
"foreign key (playlist_id) references playlist(id) on delete cascade," +
"foreign key (media_file_id) references media_file(id) on delete cascade)");
LOG.info("Database table 'playlist_file' was created successfully.");
}
if (!tableExists(template, "playlist_user")) {
LOG.info("Database table 'playlist_user' not found. Creating it.");
template.execute("create table playlist_user (" +
"id identity," +
"playlist_id int not null," +
"username varchar not null," +
"unique(playlist_id, username)," +
"foreign key (playlist_id) references playlist(id) on delete cascade," +
"foreign key (username) references user(username) on delete cascade)");
LOG.info("Database table 'playlist_user' was created successfully.");
}
}
}
|
