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 2012 (C) Madevil */ 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 Madsonic version 4.7. * * @author Madevil */ public class SchemaMadsonic extends Schema { private static final Logger LOG = Logger.getLogger(SchemaMadsonic.class); @Override public void execute(JdbcTemplate template) { //////////////////// if (template.queryForInt("select count(*) from version where version = 101") == 0) { LOG.info("Updating database schema to version 101."); template.execute("insert into version values (101)"); // Reset Usersetting: show_now_playing & show_chat if (columnExists(template, "show_chat", "user_settings")) { template.execute("update user_settings set show_chat = false, show_now_playing = false"); LOG.info("Database Update 'user_settings.show_chat' was added successfully."); LOG.info("Database Update 'user_settings.show_now_playing' was added successfully."); } } //////////////////// // Add Statistic Table if (!tableExists(template, "statistic_user")) { LOG.info("Database table 'statistic_user' not found. Creating it."); template.execute("create table statistic_user (" + "id identity," + "username varchar not null," + "media_file_id int not null," + "played datetime not null," + "foreign key (media_file_id) references media_file(id) on delete cascade," + "foreign key (username) references user(username) on delete cascade)"); template.execute("create index idx_statistic_user_media_file_id on statistic_user(media_file_id)"); template.execute("create index idx_statistic_user_username on statistic_user(username)"); LOG.info("Database table 'statistic_user' was created successfully."); } //////////////////// // Add Hot Recommmed Table if (!tableExists(template, "hot_rating")) { LOG.info("Database table 'hot_rating' not found. Creating it."); template.execute("create table hot_rating (" + "username varchar not null," + "path varchar not null," + "id int not null," + "primary key (username, path)," + "foreign key (username) references user(username) on delete cascade)"); LOG.info("Database table 'hot_rating' was created successfully."); } if (template.queryForInt("select count(*) from version where version = 102") == 0) { LOG.info("Updating database schema to version 102."); template.execute("insert into version values (102)"); if (!columnExists(template, "index", "music_folder")) { LOG.info("Database column 'music_folder.index' not found. Creating it."); template.execute("alter table music_folder add index int default 1 not null"); LOG.info("Database column 'music_folder.index' was added successfully."); } } //////////////////// if (template.queryForInt("select count(*) from version where version = 103") == 0) { LOG.info("Updating database schema to version 103."); template.execute("insert into version values (103)"); template.execute( "create index idx_starred_media_file_media_file_id_username on starred_media_file(media_file_id, username)"); template.execute("create index idx_starred_media_file_created on starred_media_file(created)"); LOG.info("Database index 'idx_starred_media_file_media_file_id_username' was added successfully."); LOG.info("Database index 'idx_starred_media_file_created' was added successfully."); } //////////////////// if (template.queryForInt("select count(*) from version where version = 104") == 0) { LOG.info("Updating database schema to version 104."); template.execute("insert into version values (104)"); if (template.queryForInt("select count(*) from role where id = 12") == 0) { LOG.info("Role 'search' not found in database. Creating it."); template.execute("insert into role values (12, 'search')"); // default for admin/stream role template.execute( "insert into user_role " + "select distinct u.username, 12 from user u, user_role ur " + "where u.username = ur.username and ur.role_id = 8"); LOG.info("Role 'search' was created successfully."); } } //////////////////// if (template.queryForInt("select count(*) from version where version = 105") == 0) { LOG.info("Updating database schema to version 105."); template.execute("insert into version values (105)"); // Added new Usersettings if (!columnExists(template, "customscrollbar", "user_settings")) { LOG.info("Database column 'user_settings.customscrollbar' not found. Creating it."); template.execute("alter table user_settings add customscrollbar boolean default true not null"); LOG.info("Database column 'user_settings.customscrollbar' was added successfully."); } } //////////////////// // Add new User Role 'search' and add as default if (template.queryForInt("select count(*) from role where id = 12") == 0) { LOG.info("Role 'search' not found in database. Creating it."); template.execute("insert into role values (12, 'search')"); // default for admin/stream role template.execute("insert into user_role " + "select distinct u.username, 12 from user u, user_role ur " + "where u.username = ur.username and ur.role_id = 8"); LOG.info("Role 'search' was created successfully."); } //////////////////// // new transcoding settings if (template.queryForInt("select count(*) from version where version = 106") == 0) { LOG.info("Updating database schema to version 106."); template.execute("insert into version values (106)"); // wtv transcoding if (template.queryForInt("select count(*) from transcoding2 where name = 'wtv video'") == 0) { template.execute( "insert into transcoding2(name, source_formats, target_format, step1) values('wtv video', 'wtv', 'flv', " + "'ffmpeg -ss %o -i %s -async 30 -b %bk -r 23-.976 -s %wx%h -ar 44100 -ac 2 -v 0 -f flv -vcodec libx264 -preset fast -threads 0 -')"); } // FLAC transcoding if (template.queryForInt("select count(*) from transcoding2 where name = 'FLAC audio'") == 0) { if (template.queryForInt( "SELECT count(*) from transcoding2 where source_formats like '%flac%' and name = 'mp3 audio'") == 1) { template.execute( "update transcoding2 set source_formats = 'ogg oga aac m4a wav wma aif aiff ape mpc shn' " + "where source_formats like '%flac%' and name = 'mp3 audio'"); } template.execute( "insert into transcoding2(name, source_formats, target_format, step1, step2) values('FLAC audio', 'flac', 'mp3', " + "'ffmpeg -i %s -v 0 -f wav -', 'lame -V 0 --tt %t --ta %a --tl %l -S --resample 44.1 - -')"); } // SubWiji transcoding if (template.queryForInt("select count(*) from transcoding2 where name = 'SubWiji'") == 0) { template.execute( "insert into transcoding2(name, source_formats, target_format, step1, default_active) values('SubWiji', 'mp3', 'mp3', " + "'ffmpeg -f mp3 -i %s -ab %bk -v 0 -f mp3 -', false)"); } } //////////////////// // new transcoding settings if (template.queryForInt("select count(*) from version where version = 107") == 0) { LOG.info("Updating database schema to version 107."); template.execute("insert into version values (107)"); // FLAC transcoding if (template.queryForInt("select count(*) from transcoding2 where name = 'FLAC audio'") == 1) { template.execute("delete from transcoding2 where name = 'FLAC audio'"); if (template.queryForInt( "SELECT count(*) from transcoding2 where source_formats like '%m4a%' and name = 'mp3 audio'") == 1) { template.execute( "update transcoding2 set source_formats = 'ogg oga aac wav wma aif aiff ape mpc shn' " + "where source_formats like '%m4a%' and name = 'mp3 audio'"); } template.execute( "insert into transcoding2(name, source_formats, target_format, step1, step2) values('m4a/FLAC audio', 'flac m4a', 'mp3', " + "'ffmpeg -i %s -v 0 -f wav -', 'lame -V 0 --tt %t --ta %a --tl %l -S --resample 44.1 - -')"); } LOG.info("new transcoding in table 'transcoding2' was inserted successfully."); } //////////////////// // new transcoding settings if (template.queryForInt("select count(*) from version where version = 108") == 0) { LOG.info("Updating database schema to version 108."); template.execute("insert into version values (108)"); // FLAC transcoding if (template.queryForInt("select count(*) from transcoding2 where name = 'm4a/FLAC audio'") == 1) { template.execute("delete from transcoding2 where name = 'm4a/FLAC audio'"); template.execute( "insert into transcoding2(name, source_formats, target_format, step1 ) values('m4a/FLAC audio', 'm4a flac', 'mp3', " + "'Audioffmpeg -i %s -ab 256k -ar 44100 -ac 2 -v 0 -f mp3 -')"); template.execute( "update transcoding2 set step1 = 'Audioffmpeg -i %s -ab %bk -v 0 -f mp3 -' where name = 'mp3 audio'"); template.execute( "update transcoding2 set step1 = 'Audioffmpeg -f mp3 -i %s -ab %bk -v 0 -f mp3 -' where name = 'SubWiji'"); } LOG.info("new transcoding in table 'transcoding2' was inserted successfully."); } // Cleanup Transcoding if (template.queryForInt("select count(*) from version where version = 109") == 0) { LOG.info("Updating database schema to version 109."); // template.execute("insert into version values (109)"); } //////////////////// // new Access Control if (template.queryForInt("select count(*) from version where version = 110") == 0) { LOG.info("Updating database schema to version 110."); template.execute("insert into version values (110)"); // Add Group Table if (!tableExists(template, "user_group")) { LOG.info("Database table 'user_group' not found. Creating it."); template.execute("create table user_group (" + "id identity, " + "name varchar not null, " + "primary key (id))"); LOG.info("Database table 'user_group' was created successfully."); } // Add Group Access Table if (!tableExists(template, "user_group_access")) { LOG.info("Database table 'user_group_access' not found. Creating it."); template.execute("create table user_group_access (" + "user_group_id integer not null, " + "music_folder_id integer not null, " + "enabled boolean default true not null, " + "primary key (user_group_id, music_folder_id)," + "foreign key (user_group_id) references user_group(id) on delete cascade," + "foreign key (music_folder_id) references music_folder(id) on delete cascade)"); LOG.info("Database table 'user_group_access' was created successfully."); template.execute( "create index idx_user_group_access_user_group_id_music_folder_id_enabled on user_group_access(user_group_id, music_folder_id, enabled)"); LOG.info( "Database index 'idx_user_group_access_user_group_id_music_folder_id_enabled' was added successfully."); } } //////////////////// // new transcoding settings if (template.queryForInt("select count(*) from version where version = 111") == 0) { LOG.info("Updating database schema to version 111."); template.execute("insert into version values (111)"); //ALTER TABLE USER drop constraint FK_2 //ALTER TABLE USER drop group_id template.execute("alter table user add column group_id integer default 0 not null;"); template.execute("insert into user_group (id, name) values (0, 'ALL')"); template.execute("insert into user_group (id, name) values (1, 'GUEST')"); template.execute("insert into user_group (id, name) values (2, 'FAMILY')"); template.execute("insert into user_group (id, name) values (3, 'FRIENDS')"); template.execute("insert into user_group (id, name) values (4, 'LIMITED')"); // Insert Default Access to admin // template.execute("insert into public.user_group_access (user_group_id, music_folder_id) values (0, 0)"); // Insert Default Access to all template.execute("insert into user_group_access (user_group_id, music_folder_id, enabled) " + "(select distinct g.id as user_group_id, f.id as music_folder_id, 'true' as enabled from user_group g, music_folder f)"); template.execute( "alter table user add constraint fk_group_id foreign key (group_id) references user_group (id)"); LOG.info("Database table 'user' was updated successfully."); } // Reset Access to default if (template.queryForInt("select count(*) from version where version = 112") == 0) { LOG.info("Updating database schema to version 112."); template.execute("insert into version values (112)"); template.execute("delete from user_group_access"); template.execute("insert into user_group_access (user_group_id, music_folder_id, enabled) " + "(select distinct g.id as user_group_id, f.id as music_folder_id, 'true' as enabled from user_group g, music_folder f)"); } } }