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
|
/*
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.apache.commons.io.IOUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
/**
* Used for creating and evolving the database schema.
* This class implementes the database schema for Subsonic version 3.5.
*
* @author Sindre Mehus
*/
public class Schema35 extends Schema {
private static final Logger LOG = Logger.getLogger(Schema35.class);
private static final String[] AVATARS = {
"Formal", "Engineer", "Footballer", "Green-Boy",
"Linux-Zealot", "Mac-Zealot", "Windows-Zealot", "Army-Officer", "Beatnik",
"All-Caps", "Clown", "Commie-Pinko", "Forum-Flirt", "Gamer", "Hopelessly-Addicted",
"Jekyll-And-Hyde", "Joker", "Lurker", "Moderator", "Newbie", "No-Dissent",
"Performer", "Push-My-Button", "Ray-Of-Sunshine", "Red-Hot-Chili-Peppers-1",
"Red-Hot-Chili-Peppers-2", "Red-Hot-Chili-Peppers-3", "Red-Hot-Chili-Peppers-4",
"Ringmaster", "Rumor-Junkie", "Sozzled-Surfer", "Statistician", "Tech-Support",
"The-Guru", "The-Referee", "Troll", "Uptight",
"Fire-Guitar", "Drum", "Headphones", "Mic", "Turntable", "Vinyl",
"Cool", "Laugh", "Study"
};
@Override
public void execute(JdbcTemplate template) {
if (template.queryForInt("select count(*) from version where version = 11") == 0) {
LOG.info("Updating database schema to version 11.");
template.execute("insert into version values (11)");
}
if (!columnExists(template, "now_playing_allowed", "user_settings")) {
LOG.info("Database column 'user_settings.now_playing_allowed' not found. Creating it.");
template.execute("alter table user_settings add now_playing_allowed boolean default true not null");
LOG.info("Database column 'user_settings.now_playing_allowed' was added successfully.");
}
if (!columnExists(template, "web_player_default", "user_settings")) {
LOG.info("Database column 'user_settings.web_player_default' not found. Creating it.");
template.execute("alter table user_settings add web_player_default boolean default false not null");
LOG.info("Database column 'user_settings.web_player_default' was added successfully.");
}
if (template.queryForInt("select count(*) from role where id = 8") == 0) {
LOG.info("Role 'stream' not found in database. Creating it.");
template.execute("insert into role values (8, 'stream')");
template.execute("insert into user_role select distinct u.username, 8 from user u");
LOG.info("Role 'stream' was created successfully.");
}
if (!tableExists(template, "system_avatar")) {
LOG.info("Database table 'system_avatar' not found. Creating it.");
template.execute("create table system_avatar (" +
"id identity," +
"name varchar," +
"created_date datetime not null," +
"mime_type varchar not null," +
"width int not null," +
"height int not null," +
"data binary not null)");
LOG.info("Database table 'system_avatar' was created successfully.");
}
for (String avatar : AVATARS) {
createAvatar(template, avatar);
}
if (!tableExists(template, "custom_avatar")) {
LOG.info("Database table 'custom_avatar' not found. Creating it.");
template.execute("create table custom_avatar (" +
"id identity," +
"name varchar," +
"created_date datetime not null," +
"mime_type varchar not null," +
"width int not null," +
"height int not null," +
"data binary not null," +
"username varchar not null," +
"foreign key (username) references user(username) on delete cascade)");
LOG.info("Database table 'custom_avatar' was created successfully.");
}
if (!columnExists(template, "avatar_scheme", "user_settings")) {
LOG.info("Database column 'user_settings.avatar_scheme' not found. Creating it.");
template.execute("alter table user_settings add avatar_scheme varchar default 'NONE' not null");
LOG.info("Database column 'user_settings.avatar_scheme' was added successfully.");
}
if (!columnExists(template, "system_avatar_id", "user_settings")) {
LOG.info("Database column 'user_settings.system_avatar_id' not found. Creating it.");
template.execute("alter table user_settings add system_avatar_id int");
template.execute("alter table user_settings add foreign key (system_avatar_id) references system_avatar(id)");
LOG.info("Database column 'user_settings.system_avatar_id' was added successfully.");
}
if (!columnExists(template, "jukebox", "player")) {
LOG.info("Database column 'player.jukebox' not found. Creating it.");
template.execute("alter table player add jukebox boolean default false not null");
LOG.info("Database column 'player.jukebox' was added successfully.");
}
}
private void createAvatar(JdbcTemplate template, String avatar) {
if (template.queryForInt("select count(*) from system_avatar where name = ?", new Object[]{avatar}) == 0) {
InputStream in = null;
try {
in = getClass().getResourceAsStream(avatar + ".png");
byte[] imageData = IOUtils.toByteArray(in);
template.update("insert into system_avatar values (null, ?, ?, ?, ?, ?, ?)",
new Object[]{avatar, new Date(), "image/png", 48, 48, imageData});
LOG.info("Created avatar '" + avatar + "'.");
} catch (IOException x) {
LOG.error("Failed to create avatar '" + avatar + "'.", x);
} finally {
IOUtils.closeQuietly(in);
}
}
}
}
|
