/*
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);
}
}
}
}