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