diff options
Diffstat (limited to 'subsonic-main/src/main/java/net/sourceforge/subsonic/dao')
35 files changed, 4117 insertions, 0 deletions
diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/AbstractDao.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/AbstractDao.java new file mode 100644 index 00000000..de17f4d4 --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/AbstractDao.java @@ -0,0 +1,127 @@ +/* + 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; + +import java.util.Date; +import java.util.List; + +import org.springframework.jdbc.core.*; + +import net.sourceforge.subsonic.Logger; + +/** + * Abstract superclass for all DAO's. + * + * @author Sindre Mehus + */ +public class AbstractDao { + private static final Logger LOG = Logger.getLogger(AbstractDao.class); + + private DaoHelper daoHelper; + + /** + * Returns a JDBC template for performing database operations. + * @return A JDBC template. + */ + public JdbcTemplate getJdbcTemplate() { + return daoHelper.getJdbcTemplate(); + } + + protected String questionMarks(String columns) { + int count = columns.split(", ").length; + StringBuilder builder = new StringBuilder(); + for (int i = 0; i < count; i++) { + builder.append('?'); + if (i < count - 1) { + builder.append(", "); + } + } + return builder.toString(); + } + + protected String prefix(String columns, String prefix) { + StringBuilder builder = new StringBuilder(); + for (String s : columns.split(", ")) { + builder.append(prefix).append(".").append(s).append(","); + } + if (builder.length() > 0) { + builder.setLength(builder.length() - 1); + } + return builder.toString(); + } + + protected int update(String sql, Object... args) { + long t = System.nanoTime(); + int result = getJdbcTemplate().update(sql, args); + log(sql, t); + return result; + } + + private void log(String sql, long startTimeNano) { +// long micros = (System.nanoTime() - startTimeNano) / 1000L; +// LOG.debug(micros + " " + sql); + } + + protected <T> List<T> query(String sql, RowMapper rowMapper, Object... args) { + long t = System.nanoTime(); + List<T> result = getJdbcTemplate().query(sql, args, rowMapper); + log(sql, t); + return result; + } + + protected List<String> queryForStrings(String sql, Object... args) { + long t = System.nanoTime(); + List<String> result = getJdbcTemplate().queryForList(sql, args, String.class); + log(sql, t); + return result; + } + + protected Integer queryForInt(String sql, Integer defaultValue, Object... args) { + long t = System.nanoTime(); + List<Integer> list = getJdbcTemplate().queryForList(sql, args, Integer.class); + Integer result = list.isEmpty() ? defaultValue : list.get(0) == null ? defaultValue : list.get(0); + log(sql, t); + return result; + } + + protected Date queryForDate(String sql, Date defaultValue, Object... args) { + long t = System.nanoTime(); + List<Date> list = getJdbcTemplate().queryForList(sql, args, Date.class); + Date result = list.isEmpty() ? defaultValue : list.get(0) == null ? defaultValue : list.get(0); + log(sql, t); + return result; + } + + protected Long queryForLong(String sql, Long defaultValue, Object... args) { + long t = System.nanoTime(); + List<Long> list = getJdbcTemplate().queryForList(sql, args, Long.class); + Long result = list.isEmpty() ? defaultValue : list.get(0) == null ? defaultValue : list.get(0); + log(sql, t); + return result; + } + + protected <T> T queryOne(String sql, RowMapper rowMapper, Object... args) { + List<T> list = query(sql, rowMapper, args); + return list.isEmpty() ? null : list.get(0); + } + + public void setDaoHelper(DaoHelper daoHelper) { + this.daoHelper = daoHelper; + } +} diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/AlbumDao.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/AlbumDao.java new file mode 100644 index 00000000..603f6dad --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/AlbumDao.java @@ -0,0 +1,243 @@ +/* + 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; + +import net.sourceforge.subsonic.Logger; +import net.sourceforge.subsonic.domain.Album; +import net.sourceforge.subsonic.domain.MediaFile; +import org.apache.commons.lang.ObjectUtils; +import org.springframework.jdbc.core.RowMapper; +import org.springframework.jdbc.core.simple.ParameterizedRowMapper; + +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.Date; +import java.util.List; + +/** + * Provides database services for albums. + * + * @author Sindre Mehus + */ +public class AlbumDao extends AbstractDao { + + private static final Logger LOG = Logger.getLogger(AlbumDao.class); + private static final String COLUMNS = "id, path, name, artist, song_count, duration_seconds, cover_art_path, " + + "play_count, last_played, comment, created, last_scanned, present"; + + private final RowMapper rowMapper = new AlbumMapper(); + + /** + * Returns the album with the given artist and album name. + * + * @param artistName The artist name. + * @param albumName The album name. + * @return The album or null. + */ + public Album getAlbum(String artistName, String albumName) { + return queryOne("select " + COLUMNS + " from album where artist=? and name=?", rowMapper, artistName, albumName); + } + + /** + * Returns the album that the given file (most likely) is part of. + * + * @param file The media file. + * @return The album or null. + */ + public Album getAlbumForFile(MediaFile file) { + + // First, get all albums with the correct album name (irrespective of artist). + List<Album> candidates = query("select " + COLUMNS + " from album where name=?", rowMapper, file.getAlbumName()); + if (candidates.isEmpty()) { + return null; + } + + // Look for album with the correct artist. + for (Album candidate : candidates) { + if (ObjectUtils.equals(candidate.getArtist(), file.getArtist())) { + return candidate; + } + } + + // Look for album with the same path as the file. + for (Album candidate : candidates) { + if (ObjectUtils.equals(candidate.getPath(), file.getParentPath())) { + return candidate; + } + } + + // No appropriate album found. + return null; + } + + public Album getAlbum(int id) { + return queryOne("select " + COLUMNS + " from album where id=?", rowMapper, id); + } + + public List<Album> getAlbumsForArtist(String artist) { + return query("select " + COLUMNS + " from album where artist=? and present order by name", rowMapper, artist); + } + + /** + * Creates or updates an album. + * + * @param album The album to create/update. + */ + public synchronized void createOrUpdateAlbum(Album album) { + String sql = "update album set " + + "song_count=?," + + "duration_seconds=?," + + "cover_art_path=?," + + "play_count=?," + + "last_played=?," + + "comment=?," + + "created=?," + + "last_scanned=?," + + "present=? " + + "where artist=? and name=?"; + + int n = update(sql, album.getSongCount(), album.getDurationSeconds(), album.getCoverArtPath(), album.getPlayCount(), album.getLastPlayed(), + album.getComment(), album.getCreated(), album.getLastScanned(), album.isPresent(), album.getArtist(), album.getName()); + + if (n == 0) { + + update("insert into album (" + COLUMNS + ") values (" + questionMarks(COLUMNS) + ")", null, album.getPath(), album.getName(), album.getArtist(), + album.getSongCount(), album.getDurationSeconds(), album.getCoverArtPath(), album.getPlayCount(), album.getLastPlayed(), + album.getComment(), album.getCreated(), album.getLastScanned(), album.isPresent()); + } + + int id = queryForInt("select id from album where artist=? and name=?", null, album.getArtist(), album.getName()); + album.setId(id); + } + + /** + * Returns albums in alphabetical order. + * + * @param offset Number of albums to skip. + * @param count Maximum number of albums to return. + * @param byArtist Whether to sort by artist name + * @return Albums in alphabetical order. + */ + public List<Album> getAlphabetialAlbums(int offset, int count, boolean byArtist) { + String orderBy = byArtist ? "artist, name" : "name"; + return query("select " + COLUMNS + " from album where present order by " + orderBy + " limit ? offset ?", rowMapper, count, offset); + } + + /** + * Returns the most frequently played albums. + * + * @param offset Number of albums to skip. + * @param count Maximum number of albums to return. + * @return The most frequently played albums. + */ + public List<Album> getMostFrequentlyPlayedAlbums(int offset, int count) { + return query("select " + COLUMNS + " from album where play_count > 0 and present " + + "order by play_count desc limit ? offset ?", rowMapper, count, offset); + } + + /** + * Returns the most recently played albums. + * + * @param offset Number of albums to skip. + * @param count Maximum number of albums to return. + * @return The most recently played albums. + */ + public List<Album> getMostRecentlyPlayedAlbums(int offset, int count) { + return query("select " + COLUMNS + " from album where last_played is not null and present " + + "order by last_played desc limit ? offset ?", rowMapper, count, offset); + } + + /** + * Returns the most recently added albums. + * + * @param offset Number of albums to skip. + * @param count Maximum number of albums to return. + * @return The most recently added albums. + */ + public List<Album> getNewestAlbums(int offset, int count) { + return query("select " + COLUMNS + " from album where present order by created desc limit ? offset ?", + rowMapper, count, offset); + } + + /** + * Returns the most recently starred albums. + * + * @param offset Number of albums to skip. + * @param count Maximum number of albums to return. + * @param username Returns albums starred by this user. + * @return The most recently starred albums for this user. + */ + public List<Album> getStarredAlbums(int offset, int count, String username) { + return query("select " + prefix(COLUMNS, "album") + " from album, starred_album where album.id = starred_album.album_id and " + + "album.present and starred_album.username=? order by starred_album.created desc limit ? offset ?", + rowMapper, username, count, offset); + } + + public void markNonPresent(Date lastScanned) { + int minId = queryForInt("select id from album where true limit 1", 0); + int maxId = queryForInt("select max(id) from album", 0); + + final int batchSize = 1000; + for (int id = minId; id <= maxId; id += batchSize) { + update("update album set present=false where id between ? and ? and last_scanned != ? and present", id, id + batchSize, lastScanned); + } + } + + public void expunge() { + int minId = queryForInt("select id from album where true limit 1", 0); + int maxId = queryForInt("select max(id) from album", 0); + + final int batchSize = 1000; + for (int id = minId; id <= maxId; id += batchSize) { + update("delete from album where id between ? and ? and not present", id, id + batchSize); + } + } + + public void starAlbum(int albumId, String username) { + unstarAlbum(albumId, username); + update("insert into starred_album(album_id, username, created) values (?,?,?)", albumId, username, new Date()); + } + + public void unstarAlbum(int albumId, String username) { + update("delete from starred_album where album_id=? and username=?", albumId, username); + } + + public Date getAlbumStarredDate(int albumId, String username) { + return queryForDate("select created from starred_album where album_id=? and username=?", null, albumId, username); + } + + private static class AlbumMapper implements ParameterizedRowMapper<Album> { + public Album mapRow(ResultSet rs, int rowNum) throws SQLException { + return new Album( + rs.getInt(1), + rs.getString(2), + rs.getString(3), + rs.getString(4), + rs.getInt(5), + rs.getInt(6), + rs.getString(7), + rs.getInt(8), + rs.getTimestamp(9), + rs.getString(10), + rs.getTimestamp(11), + rs.getTimestamp(12), + rs.getBoolean(13)); + } + } +} diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/ArtistDao.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/ArtistDao.java new file mode 100644 index 00000000..41d57c33 --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/ArtistDao.java @@ -0,0 +1,161 @@ +/* + 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; + +import net.sourceforge.subsonic.Logger; +import net.sourceforge.subsonic.domain.Artist; +import org.springframework.jdbc.core.RowMapper; +import org.springframework.jdbc.core.simple.ParameterizedRowMapper; + +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.Date; +import java.util.List; + +/** + * Provides database services for artists. + * + * @author Sindre Mehus + */ +public class ArtistDao extends AbstractDao { + + private static final Logger LOG = Logger.getLogger(ArtistDao.class); + private static final String COLUMNS = "id, name, cover_art_path, album_count, last_scanned, present"; + + private final RowMapper rowMapper = new ArtistMapper(); + + /** + * Returns the artist with the given name. + * + * @param artistName The artist name. + * @return The artist or null. + */ + public Artist getArtist(String artistName) { + return queryOne("select " + COLUMNS + " from artist where name=?", rowMapper, artistName); + } + + /** + * Returns the artist with the given ID. + * + * @param id The artist ID. + * @return The artist or null. + */ + public Artist getArtist(int id) { + return queryOne("select " + COLUMNS + " from artist where id=?", rowMapper, id); + } + + /** + * Creates or updates an artist. + * + * @param artist The artist to create/update. + */ + public synchronized void createOrUpdateArtist(Artist artist) { + String sql = "update artist set " + + "cover_art_path=?," + + "album_count=?," + + "last_scanned=?," + + "present=? " + + "where name=?"; + + int n = update(sql, artist.getCoverArtPath(), artist.getAlbumCount(), artist.getLastScanned(), artist.isPresent(), artist.getName()); + + if (n == 0) { + + update("insert into artist (" + COLUMNS + ") values (" + questionMarks(COLUMNS) + ")", null, + artist.getName(), artist.getCoverArtPath(), artist.getAlbumCount(), artist.getLastScanned(), artist.isPresent()); + } + + int id = queryForInt("select id from artist where name=?", null, artist.getName()); + artist.setId(id); + } + + /** + * Returns artists in alphabetical order. + * + * @param offset Number of artists to skip. + * @param count Maximum number of artists to return. + * @return Artists in alphabetical order. + */ + public List<Artist> getAlphabetialArtists(int offset, int count) { + return query("select " + COLUMNS + " from artist where present order by name limit ? offset ?", rowMapper, count, offset); + } + + /** + * Returns the most recently starred artists. + * + * @param offset Number of artists to skip. + * @param count Maximum number of artists to return. + * @param username Returns artists starred by this user. + * @return The most recently starred artists for this user. + */ + public List<Artist> getStarredArtists(int offset, int count, String username) { + return query("select " + prefix(COLUMNS, "artist") + " from artist, starred_artist where artist.id = starred_artist.artist_id and " + + "artist.present and starred_artist.username=? order by starred_artist.created desc limit ? offset ?", + rowMapper, username, count, offset); + } + + public void markPresent(String artistName, Date lastScanned) { + update("update artist set present=?, last_scanned=? where name=?", true, lastScanned, artistName); + } + + public void markNonPresent(Date lastScanned) { + int minId = queryForInt("select id from artist where true limit 1", 0); + int maxId = queryForInt("select max(id) from artist", 0); + + final int batchSize = 1000; + for (int id = minId; id <= maxId; id += batchSize) { + update("update artist set present=false where id between ? and ? and last_scanned != ? and present", id, id + batchSize, lastScanned); + } + } + + public void expunge() { + int minId = queryForInt("select id from artist where true limit 1", 0); + int maxId = queryForInt("select max(id) from artist", 0); + + final int batchSize = 1000; + for (int id = minId; id <= maxId; id += batchSize) { + update("delete from artist where id between ? and ? and not present", id, id + batchSize); + } + } + + public void starArtist(int artistId, String username) { + unstarArtist(artistId, username); + update("insert into starred_artist(artist_id, username, created) values (?,?,?)", artistId, username, new Date()); + } + + public void unstarArtist(int artistId, String username) { + update("delete from starred_artist where artist_id=? and username=?", artistId, username); + } + + public Date getArtistStarredDate(int artistId, String username) { + return queryForDate("select created from starred_artist where artist_id=? and username=?", null, artistId, username); + } + + private static class ArtistMapper implements ParameterizedRowMapper<Artist> { + public Artist mapRow(ResultSet rs, int rowNum) throws SQLException { + return new Artist( + rs.getInt(1), + rs.getString(2), + rs.getString(3), + rs.getInt(4), + rs.getTimestamp(5), + rs.getBoolean(6)); + } + } +} diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/AvatarDao.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/AvatarDao.java new file mode 100644 index 00000000..abdc118d --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/AvatarDao.java @@ -0,0 +1,94 @@ +/* + 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; + +import net.sourceforge.subsonic.domain.Avatar; +import org.springframework.jdbc.core.simple.ParameterizedRowMapper; + +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.List; + +/** + * Provides database services for avatars. + * + * @author Sindre Mehus + */ +public class AvatarDao extends AbstractDao { + + private static final String COLUMNS = "id, name, created_date, mime_type, width, height, data"; + private final AvatarRowMapper rowMapper = new AvatarRowMapper(); + + /** + * Returns all system avatars. + * + * @return All system avatars. + */ + public List<Avatar> getAllSystemAvatars() { + String sql = "select " + COLUMNS + " from system_avatar"; + return query(sql, rowMapper); + } + + /** + * Returns the system avatar with the given ID. + * + * @param id The system avatar ID. + * @return The avatar or <code>null</code> if not found. + */ + public Avatar getSystemAvatar(int id) { + String sql = "select " + COLUMNS + " from system_avatar where id=" + id; + return queryOne(sql, rowMapper); + } + + /** + * Returns the custom avatar for the given user. + * + * @param username The username. + * @return The avatar or <code>null</code> if not found. + */ + public Avatar getCustomAvatar(String username) { + String sql = "select " + COLUMNS + " from custom_avatar where username=?"; + return queryOne(sql, rowMapper, username); + } + + /** + * Sets the custom avatar for the given user. + * + * @param avatar The avatar, or <code>null</code> to remove the avatar. + * @param username The username. + */ + public void setCustomAvatar(Avatar avatar, String username) { + String sql = "delete from custom_avatar where username=?"; + update(sql, username); + + if (avatar != null) { + update("insert into custom_avatar(" + COLUMNS + ", username) values(" + questionMarks(COLUMNS) + ", ?)", + null, avatar.getName(), avatar.getCreatedDate(), avatar.getMimeType(), + avatar.getWidth(), avatar.getHeight(), avatar.getData(), username); + } + } + + private static class AvatarRowMapper implements ParameterizedRowMapper<Avatar> { + public Avatar mapRow(ResultSet rs, int rowNum) throws SQLException { + return new Avatar(rs.getInt(1), rs.getString(2), rs.getTimestamp(3), rs.getString(4), + rs.getInt(5), rs.getInt(6), rs.getBytes(7)); + } + } + +}
\ No newline at end of file diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/DaoHelper.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/DaoHelper.java new file mode 100644 index 00000000..802a5b3d --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/DaoHelper.java @@ -0,0 +1,117 @@ +/* + 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; + +import net.sourceforge.subsonic.Logger; +import net.sourceforge.subsonic.dao.schema.Schema; +import net.sourceforge.subsonic.dao.schema.Schema25; +import net.sourceforge.subsonic.dao.schema.Schema26; +import net.sourceforge.subsonic.dao.schema.Schema27; +import net.sourceforge.subsonic.dao.schema.Schema28; +import net.sourceforge.subsonic.dao.schema.Schema29; +import net.sourceforge.subsonic.dao.schema.Schema30; +import net.sourceforge.subsonic.dao.schema.Schema31; +import net.sourceforge.subsonic.dao.schema.Schema32; +import net.sourceforge.subsonic.dao.schema.Schema33; +import net.sourceforge.subsonic.dao.schema.Schema34; +import net.sourceforge.subsonic.dao.schema.Schema35; +import net.sourceforge.subsonic.dao.schema.Schema36; +import net.sourceforge.subsonic.dao.schema.Schema37; +import net.sourceforge.subsonic.dao.schema.Schema38; +import net.sourceforge.subsonic.dao.schema.Schema40; +import net.sourceforge.subsonic.dao.schema.Schema43; +import net.sourceforge.subsonic.dao.schema.Schema45; +import net.sourceforge.subsonic.dao.schema.Schema46; +import net.sourceforge.subsonic.dao.schema.Schema47; +import net.sourceforge.subsonic.service.SettingsService; +import org.springframework.jdbc.core.JdbcTemplate; +import org.springframework.jdbc.datasource.DriverManagerDataSource; + +import javax.sql.DataSource; +import java.io.File; + +/** + * DAO helper class which creates the data source, and updates the database schema. + * + * @author Sindre Mehus + */ +public class DaoHelper { + + private static final Logger LOG = Logger.getLogger(DaoHelper.class); + + private Schema[] schemas = {new Schema25(), new Schema26(), new Schema27(), new Schema28(), new Schema29(), + new Schema30(), new Schema31(), new Schema32(), new Schema33(), new Schema34(), + new Schema35(), new Schema36(), new Schema37(), new Schema38(), new Schema40(), + new Schema43(), new Schema45(), new Schema46(), new Schema47()}; + private DataSource dataSource; + private static boolean shutdownHookAdded; + + public DaoHelper() { + dataSource = createDataSource(); + checkDatabase(); + addShutdownHook(); + } + + private void addShutdownHook() { + if (shutdownHookAdded) { + return; + } + shutdownHookAdded = true; + Runtime.getRuntime().addShutdownHook(new Thread() { + @Override + public void run() { + System.err.println("Shutting down database."); + getJdbcTemplate().execute("shutdown"); + System.err.println("Done."); + } + }); + } + + /** + * Returns a JDBC template for performing database operations. + * + * @return A JDBC template. + */ + public JdbcTemplate getJdbcTemplate() { + return new JdbcTemplate(dataSource); + } + + private DataSource createDataSource() { + File subsonicHome = SettingsService.getSubsonicHome(); + DriverManagerDataSource ds = new DriverManagerDataSource(); + ds.setDriverClassName("org.hsqldb.jdbcDriver"); + ds.setUrl("jdbc:hsqldb:file:" + subsonicHome.getPath() + "/db/subsonic"); + ds.setUsername("sa"); + ds.setPassword(""); + + return ds; + } + + private void checkDatabase() { + LOG.info("Checking database schema."); + try { + for (Schema schema : schemas) { + schema.execute(getJdbcTemplate()); + } + LOG.info("Done checking database schema."); + } catch (Exception x) { + LOG.error("Failed to initialize database.", x); + } + } +} diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/InternetRadioDao.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/InternetRadioDao.java new file mode 100644 index 00000000..c3c20a74 --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/InternetRadioDao.java @@ -0,0 +1,89 @@ +/* + 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; + +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.List; + +import org.springframework.jdbc.core.simple.ParameterizedRowMapper; + +import net.sourceforge.subsonic.Logger; +import net.sourceforge.subsonic.domain.InternetRadio; + +/** + * Provides database services for internet radio. + * + * @author Sindre Mehus + */ +public class InternetRadioDao extends AbstractDao { + + private static final Logger LOG = Logger.getLogger(InternetRadioDao.class); + private static final String COLUMNS = "id, name, stream_url, homepage_url, enabled, changed"; + private final InternetRadioRowMapper rowMapper = new InternetRadioRowMapper(); + + /** + * Returns all internet radio stations. + * + * @return Possibly empty list of all internet radio stations. + */ + public List<InternetRadio> getAllInternetRadios() { + String sql = "select " + COLUMNS + " from internet_radio"; + return query(sql, rowMapper); + } + + /** + * Creates a new internet radio station. + * + * @param radio The internet radio station to create. + */ + public void createInternetRadio(InternetRadio radio) { + String sql = "insert into internet_radio (" + COLUMNS + ") values (null, ?, ?, ?, ?, ?)"; + update(sql, radio.getName(), radio.getStreamUrl(), radio.getHomepageUrl(), radio.isEnabled(), radio.getChanged()); + LOG.info("Created internet radio station " + radio.getName()); + } + + /** + * Deletes the internet radio station with the given ID. + * + * @param id The internet radio station ID. + */ + public void deleteInternetRadio(Integer id) { + String sql = "delete from internet_radio where id=?"; + update(sql, id); + LOG.info("Deleted internet radio station with ID " + id); + } + + /** + * Updates the given internet radio station. + * + * @param radio The internet radio station to update. + */ + public void updateInternetRadio(InternetRadio radio) { + String sql = "update internet_radio set name=?, stream_url=?, homepage_url=?, enabled=?, changed=? where id=?"; + update(sql, radio.getName(), radio.getStreamUrl(), radio.getHomepageUrl(), radio.isEnabled(), radio.getChanged(), radio.getId()); + } + + private static class InternetRadioRowMapper implements ParameterizedRowMapper<InternetRadio> { + public InternetRadio mapRow(ResultSet rs, int rowNum) throws SQLException { + return new InternetRadio(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getBoolean(5), rs.getTimestamp(6)); + } + } + +} diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/MediaFileDao.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/MediaFileDao.java new file mode 100644 index 00000000..e75bc7a6 --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/MediaFileDao.java @@ -0,0 +1,374 @@ +/* + 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; + +import net.sourceforge.subsonic.Logger; +import net.sourceforge.subsonic.domain.MediaFile; +import net.sourceforge.subsonic.domain.MediaLibraryStatistics; +import org.springframework.jdbc.core.RowMapper; +import org.springframework.jdbc.core.simple.ParameterizedRowMapper; + +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.Date; +import java.util.List; + +import static net.sourceforge.subsonic.domain.MediaFile.MediaType; +import static net.sourceforge.subsonic.domain.MediaFile.MediaType.*; + +/** + * Provides database services for media files. + * + * @author Sindre Mehus + */ +public class MediaFileDao extends AbstractDao { + + private static final Logger LOG = Logger.getLogger(MediaFileDao.class); + private static final String COLUMNS = "id, path, folder, type, format, title, album, artist, album_artist, disc_number, " + + "track_number, year, genre, bit_rate, variable_bit_rate, duration_seconds, file_size, width, height, cover_art_path, " + + "parent_path, play_count, last_played, comment, created, changed, last_scanned, children_last_updated, present, version"; + + private static final int VERSION = 1; + + private final RowMapper rowMapper = new MediaFileMapper(); + private final RowMapper musicFileInfoRowMapper = new MusicFileInfoMapper(); + + /** + * Returns the media file for the given path. + * + * @param path The path. + * @return The media file or null. + */ + public MediaFile getMediaFile(String path) { + return queryOne("select " + COLUMNS + " from media_file where path=?", rowMapper, path); + } + + /** + * Returns the media file for the given ID. + * + * @param id The ID. + * @return The media file or null. + */ + public MediaFile getMediaFile(int id) { + return queryOne("select " + COLUMNS + " from media_file where id=?", rowMapper, id); + } + + /** + * Returns the media file that are direct children of the given path. + * + * @param path The path. + * @return The list of children. + */ + public List<MediaFile> getChildrenOf(String path) { + return query("select " + COLUMNS + " from media_file where parent_path=? and present", rowMapper, path); + } + + public List<MediaFile> getFilesInPlaylist(int playlistId) { + return query("select " + prefix(COLUMNS, "media_file") + " from media_file, playlist_file where " + + "media_file.id = playlist_file.media_file_id and " + + "playlist_file.playlist_id = ? and " + + "media_file.present order by playlist_file.id", rowMapper, playlistId); + } + + public List<MediaFile> getSongsForAlbum(String artist, String album) { + return query("select " + COLUMNS + " from media_file where album_artist=? and album=? and present and type in (?,?,?) order by track_number", rowMapper, + artist, album, MUSIC.name(), AUDIOBOOK.name(), PODCAST.name()); + } + + public List<MediaFile> getVideos(int size, int offset) { + return query("select " + COLUMNS + " from media_file where type=? and present order by title limit ? offset ?", rowMapper, + VIDEO.name(), size, offset); + } + + /** + * Creates or updates a media file. + * + * @param file The media file to create/update. + */ + public synchronized void createOrUpdateMediaFile(MediaFile file) { + String sql = "update media_file set " + + "folder=?," + + "type=?," + + "format=?," + + "title=?," + + "album=?," + + "artist=?," + + "album_artist=?," + + "disc_number=?," + + "track_number=?," + + "year=?," + + "genre=?," + + "bit_rate=?," + + "variable_bit_rate=?," + + "duration_seconds=?," + + "file_size=?," + + "width=?," + + "height=?," + + "cover_art_path=?," + + "parent_path=?," + + "play_count=?," + + "last_played=?," + + "comment=?," + + "changed=?," + + "last_scanned=?," + + "children_last_updated=?," + + "present=?, " + + "version=? " + + "where path=?"; + + int n = update(sql, + file.getFolder(), file.getMediaType().name(), file.getFormat(), file.getTitle(), file.getAlbumName(), file.getArtist(), + file.getAlbumArtist(), file.getDiscNumber(), file.getTrackNumber(), file.getYear(), file.getGenre(), file.getBitRate(), + file.isVariableBitRate(), file.getDurationSeconds(), file.getFileSize(), file.getWidth(), file.getHeight(), + file.getCoverArtPath(), file.getParentPath(), file.getPlayCount(), file.getLastPlayed(), file.getComment(), + file.getChanged(), file.getLastScanned(), file.getChildrenLastUpdated(), file.isPresent(), VERSION, file.getPath()); + + if (n == 0) { + + // Copy values from obsolete table music_file_info. + MediaFile musicFileInfo = getMusicFileInfo(file.getPath()); + if (musicFileInfo != null) { + file.setComment(musicFileInfo.getComment()); + file.setLastPlayed(musicFileInfo.getLastPlayed()); + file.setPlayCount(musicFileInfo.getPlayCount()); + } + + update("insert into media_file (" + COLUMNS + ") values (" + questionMarks(COLUMNS) + ")", null, + file.getPath(), file.getFolder(), file.getMediaType().name(), file.getFormat(), file.getTitle(), file.getAlbumName(), file.getArtist(), + file.getAlbumArtist(), file.getDiscNumber(), file.getTrackNumber(), file.getYear(), file.getGenre(), file.getBitRate(), + file.isVariableBitRate(), file.getDurationSeconds(), file.getFileSize(), file.getWidth(), file.getHeight(), + file.getCoverArtPath(), file.getParentPath(), file.getPlayCount(), file.getLastPlayed(), file.getComment(), + file.getCreated(), file.getChanged(), file.getLastScanned(), + file.getChildrenLastUpdated(), file.isPresent(), VERSION); + } + + int id = queryForInt("select id from media_file where path=?", null, file.getPath()); + file.setId(id); + } + + private MediaFile getMusicFileInfo(String path) { + return queryOne("select play_count, last_played, comment from music_file_info where path=?", musicFileInfoRowMapper, path); + } + + @Deprecated + public List<String> getArtists() { + return queryForStrings("select distinct artist from media_file where artist is not null and present order by artist"); + } + + public void deleteMediaFile(String path) { + update("update media_file set present=false, children_last_updated=? where path=?", new Date(0L), path); + } + + public List<String> getGenres() { + return queryForStrings("select distinct genre from media_file where genre is not null and present order by genre"); + } + + /** + * Returns the most frequently played albums. + * + * @param offset Number of albums to skip. + * @param count Maximum number of albums to return. + * @return The most frequently played albums. + */ + public List<MediaFile> getMostFrequentlyPlayedAlbums(int offset, int count) { + return query("select " + COLUMNS + " from media_file where type=? and play_count > 0 and present " + + "order by play_count desc limit ? offset ?", rowMapper, ALBUM.name(), count, offset); + } + + /** + * Returns the most recently played albums. + * + * @param offset Number of albums to skip. + * @param count Maximum number of albums to return. + * @return The most recently played albums. + */ + public List<MediaFile> getMostRecentlyPlayedAlbums(int offset, int count) { + return query("select " + COLUMNS + " from media_file where type=? and last_played is not null and present " + + "order by last_played desc limit ? offset ?", rowMapper, ALBUM.name(), count, offset); + } + + /** + * Returns the most recently added albums. + * + * @param offset Number of albums to skip. + * @param count Maximum number of albums to return. + * @return The most recently added albums. + */ + public List<MediaFile> getNewestAlbums(int offset, int count) { + return query("select " + COLUMNS + " from media_file where type=? and present order by created desc limit ? offset ?", + rowMapper, ALBUM.name(), count, offset); + } + + /** + * Returns albums in alphabetical order. + * + * @param offset Number of albums to skip. + * @param count Maximum number of albums to return. + * @param byArtist Whether to sort by artist name + * @return Albums in alphabetical order. + */ + public List<MediaFile> getAlphabetialAlbums(int offset, int count, boolean byArtist) { + String orderBy = byArtist ? "artist, album" : "album"; + return query("select " + COLUMNS + " from media_file where type=? and artist != '' and present order by " + orderBy + " limit ? offset ?", + rowMapper, ALBUM.name(), count, offset); + } + + /** + * Returns the most recently starred albums. + * + * @param offset Number of albums to skip. + * @param count Maximum number of albums to return. + * @param username Returns albums starred by this user. + * @return The most recently starred albums for this user. + */ + public List<MediaFile> getStarredAlbums(int offset, int count, String username) { + return query("select " + prefix(COLUMNS, "media_file") + " from media_file, starred_media_file where media_file.id = starred_media_file.media_file_id and " + + "media_file.present and media_file.type=? and starred_media_file.username=? order by starred_media_file.created desc limit ? offset ?", + rowMapper, ALBUM.name(), username, count, offset); + } + + /** + * Returns the most recently starred directories. + * + * @param offset Number of directories to skip. + * @param count Maximum number of directories to return. + * @param username Returns directories starred by this user. + * @return The most recently starred directories for this user. + */ + public List<MediaFile> getStarredDirectories(int offset, int count, String username) { + return query("select " + prefix(COLUMNS, "media_file") + " from media_file, starred_media_file where media_file.id = starred_media_file.media_file_id and " + + "media_file.present and media_file.type=? and starred_media_file.username=? order by starred_media_file.created desc limit ? offset ?", + rowMapper, DIRECTORY.name(), username, count, offset); + } + + /** + * Returns the most recently starred files. + * + * @param offset Number of files to skip. + * @param count Maximum number of files to return. + * @param username Returns files starred by this user. + * @return The most recently starred files for this user. + */ + public List<MediaFile> getStarredFiles(int offset, int count, String username) { + return query("select " + prefix(COLUMNS, "media_file") + " from media_file, starred_media_file where media_file.id = starred_media_file.media_file_id and " + + "media_file.present and media_file.type in (?,?,?,?) and starred_media_file.username=? order by starred_media_file.created desc limit ? offset ?", + rowMapper, MUSIC.name(), PODCAST.name(), AUDIOBOOK.name(), VIDEO.name(), username, count, offset); + } + + public void starMediaFile(int id, String username) { + unstarMediaFile(id, username); + update("insert into starred_media_file(media_file_id, username, created) values (?,?,?)", id, username, new Date()); + } + + public void unstarMediaFile(int id, String username) { + update("delete from starred_media_file where media_file_id=? and username=?", id, username); + } + + public Date getMediaFileStarredDate(int id, String username) { + return queryForDate("select created from starred_media_file where media_file_id=? and username=?", null, id, username); + } + + /** + * Returns media library statistics, including the number of artists, albums and songs. + * + * @return Media library statistics. + */ + public MediaLibraryStatistics getStatistics() { + int artistCount = queryForInt("select count(1) from artist where present", 0); + int albumCount = queryForInt("select count(1) from album where present", 0); + int songCount = queryForInt("select count(1) from media_file where type in (?, ?, ?, ?) and present", 0, VIDEO.name(), MUSIC.name(), AUDIOBOOK.name(), PODCAST.name()); + long totalLengthInBytes = queryForLong("select sum(file_size) from media_file where present", 0L); + long totalDurationInSeconds = queryForLong("select sum(duration_seconds) from media_file where present", 0L); + + return new MediaLibraryStatistics(artistCount, albumCount, songCount, totalLengthInBytes, totalDurationInSeconds); + } + + public void markPresent(String path, Date lastScanned) { + update("update media_file set present=?, last_scanned=? where path=?", true, lastScanned, path); + } + + public void markNonPresent(Date lastScanned) { + int minId = queryForInt("select id from media_file where true limit 1", 0); + int maxId = queryForInt("select max(id) from media_file", 0); + + final int batchSize = 1000; + Date childrenLastUpdated = new Date(0L); // Used to force a children rescan if file is later resurrected. + for (int id = minId; id <= maxId; id += batchSize) { + update("update media_file set present=false, children_last_updated=? where id between ? and ? and last_scanned != ? and present", + childrenLastUpdated, id, id + batchSize, lastScanned); + } + } + + public void expunge() { + int minId = queryForInt("select id from media_file where true limit 1", 0); + int maxId = queryForInt("select max(id) from media_file", 0); + + final int batchSize = 1000; + for (int id = minId; id <= maxId; id += batchSize) { + update("delete from media_file where id between ? and ? and not present", id, id + batchSize); + } + update("checkpoint"); + } + + private static class MediaFileMapper implements ParameterizedRowMapper<MediaFile> { + public MediaFile mapRow(ResultSet rs, int rowNum) throws SQLException { + return new MediaFile( + rs.getInt(1), + rs.getString(2), + rs.getString(3), + MediaType.valueOf(rs.getString(4)), + rs.getString(5), + rs.getString(6), + rs.getString(7), + rs.getString(8), + rs.getString(9), + rs.getInt(10) == 0 ? null : rs.getInt(10), + rs.getInt(11) == 0 ? null : rs.getInt(11), + rs.getInt(12) == 0 ? null : rs.getInt(12), + rs.getString(13), + rs.getInt(14) == 0 ? null : rs.getInt(14), + rs.getBoolean(15), + rs.getInt(16) == 0 ? null : rs.getInt(16), + rs.getLong(17) == 0 ? null : rs.getLong(17), + rs.getInt(18) == 0 ? null : rs.getInt(18), + rs.getInt(19) == 0 ? null : rs.getInt(19), + rs.getString(20), + rs.getString(21), + rs.getInt(22), + rs.getTimestamp(23), + rs.getString(24), + rs.getTimestamp(25), + rs.getTimestamp(26), + rs.getTimestamp(27), + rs.getTimestamp(28), + rs.getBoolean(29)); + } + } + + private static class MusicFileInfoMapper implements ParameterizedRowMapper<MediaFile> { + public MediaFile mapRow(ResultSet rs, int rowNum) throws SQLException { + MediaFile file = new MediaFile(); + file.setPlayCount(rs.getInt(1)); + file.setLastPlayed(rs.getTimestamp(2)); + file.setComment(rs.getString(3)); + return file; + } + } + +} diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/MusicFolderDao.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/MusicFolderDao.java new file mode 100644 index 00000000..a5205d71 --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/MusicFolderDao.java @@ -0,0 +1,91 @@ +/* + 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; + +import java.io.File; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.List; + +import org.springframework.jdbc.core.simple.ParameterizedRowMapper; + +import net.sourceforge.subsonic.Logger; +import net.sourceforge.subsonic.domain.MusicFolder; + +/** + * Provides database services for music folders. + * + * @author Sindre Mehus + */ +public class MusicFolderDao extends AbstractDao { + + private static final Logger LOG = Logger.getLogger(MusicFolderDao.class); + private static final String COLUMNS = "id, path, name, enabled, changed"; + private final MusicFolderRowMapper rowMapper = new MusicFolderRowMapper(); + + /** + * Returns all music folders. + * + * @return Possibly empty list of all music folders. + */ + public List<MusicFolder> getAllMusicFolders() { + String sql = "select " + COLUMNS + " from music_folder"; + return query(sql, rowMapper); + } + + /** + * Creates a new music folder. + * + * @param musicFolder The music folder to create. + */ + public void createMusicFolder(MusicFolder musicFolder) { + String sql = "insert into music_folder (" + COLUMNS + ") values (null, ?, ?, ?, ?)"; + update(sql, musicFolder.getPath(), musicFolder.getName(), musicFolder.isEnabled(), musicFolder.getChanged()); + LOG.info("Created music folder " + musicFolder.getPath()); + } + + /** + * Deletes the music folder with the given ID. + * + * @param id The music folder ID. + */ + public void deleteMusicFolder(Integer id) { + String sql = "delete from music_folder where id=?"; + update(sql, id); + LOG.info("Deleted music folder with ID " + id); + } + + /** + * Updates the given music folder. + * + * @param musicFolder The music folder to update. + */ + public void updateMusicFolder(MusicFolder musicFolder) { + String sql = "update music_folder set path=?, name=?, enabled=?, changed=? where id=?"; + update(sql, musicFolder.getPath().getPath(), musicFolder.getName(), + musicFolder.isEnabled(), musicFolder.getChanged(), musicFolder.getId()); + } + + private static class MusicFolderRowMapper implements ParameterizedRowMapper<MusicFolder> { + public MusicFolder mapRow(ResultSet rs, int rowNum) throws SQLException { + return new MusicFolder(rs.getInt(1), new File(rs.getString(2)), rs.getString(3), rs.getBoolean(4), rs.getTimestamp(5)); + } + } + +} diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/PlayerDao.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/PlayerDao.java new file mode 100644 index 00000000..f129fa37 --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/PlayerDao.java @@ -0,0 +1,194 @@ +/* + 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; + +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.Calendar; +import java.util.Collections; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + +import org.springframework.jdbc.core.simple.ParameterizedRowMapper; + +import net.sourceforge.subsonic.Logger; +import net.sourceforge.subsonic.domain.CoverArtScheme; +import net.sourceforge.subsonic.domain.Player; +import net.sourceforge.subsonic.domain.PlayerTechnology; +import net.sourceforge.subsonic.domain.PlayQueue; +import net.sourceforge.subsonic.domain.TranscodeScheme; + +/** + * Provides player-related database services. + * + * @author Sindre Mehus + */ +public class PlayerDao extends AbstractDao { + + private static final Logger LOG = Logger.getLogger(PlayerDao.class); + private static final String COLUMNS = "id, name, type, username, ip_address, auto_control_enabled, " + + "last_seen, cover_art_scheme, transcode_scheme, dynamic_ip, technology, client_id"; + + private PlayerRowMapper rowMapper = new PlayerRowMapper(); + private Map<String, PlayQueue> playlists = Collections.synchronizedMap(new HashMap<String, PlayQueue>()); + + /** + * Returns all players. + * + * @return Possibly empty list of all users. + */ + public List<Player> getAllPlayers() { + String sql = "select " + COLUMNS + " from player"; + return query(sql, rowMapper); + } + + /** + * Returns all players owned by the given username and client ID. + * + * @param username The name of the user. + * @param clientId The third-party client ID (used if this player is managed over the + * Subsonic REST API). May be <code>null</code>. + * @return All relevant players. + */ + public List<Player> getPlayersForUserAndClientId(String username, String clientId) { + if (clientId != null) { + String sql = "select " + COLUMNS + " from player where username=? and client_id=?"; + return query(sql, rowMapper, username, clientId); + } else { + String sql = "select " + COLUMNS + " from player where username=? and client_id is null"; + return query(sql, rowMapper, username); + } + } + + /** + * Returns the player with the given ID. + * + * @param id The unique player ID. + * @return The player with the given ID, or <code>null</code> if no such player exists. + */ + public Player getPlayerById(String id) { + String sql = "select " + COLUMNS + " from player where id=?"; + return queryOne(sql, rowMapper, id); + } + + /** + * Creates a new player. + * + * @param player The player to create. + */ + public synchronized void createPlayer(Player player) { + int id = getJdbcTemplate().queryForInt("select max(id) from player") + 1; + player.setId(String.valueOf(id)); + String sql = "insert into player (" + COLUMNS + ") values (" + questionMarks(COLUMNS) + ")"; + update(sql, player.getId(), player.getName(), player.getType(), player.getUsername(), + player.getIpAddress(), player.isAutoControlEnabled(), + player.getLastSeen(), player.getCoverArtScheme().name(), + player.getTranscodeScheme().name(), player.isDynamicIp(), + player.getTechnology().name(), player.getClientId()); + addPlaylist(player); + + LOG.info("Created player " + id + '.'); + } + + /** + * Deletes the player with the given ID. + * + * @param id The player ID. + */ + public void deletePlayer(String id) { + String sql = "delete from player where id=?"; + update(sql, id); + playlists.remove(id); + } + + + /** + * Delete players that haven't been used for the given number of days, and which is not given a name + * or is used by a REST client. + * + * @param days Number of days. + */ + public void deleteOldPlayers(int days) { + Calendar cal = Calendar.getInstance(); + cal.add(Calendar.DATE, -days); + String sql = "delete from player where name is null and client_id is null and (last_seen is null or last_seen < ?)"; + int n = update(sql, cal.getTime()); + if (n > 0) { + LOG.info("Deleted " + n + " player(s) that haven't been used after " + cal.getTime()); + } + } + + /** + * Updates the given player. + * + * @param player The player to update. + */ + public void updatePlayer(Player player) { + String sql = "update player set " + + "name = ?," + + "type = ?," + + "username = ?," + + "ip_address = ?," + + "auto_control_enabled = ?," + + "last_seen = ?," + + "cover_art_scheme = ?," + + "transcode_scheme = ?, " + + "dynamic_ip = ?, " + + "technology = ?, " + + "client_id = ? " + + "where id = ?"; + update(sql, player.getName(), player.getType(), player.getUsername(), + player.getIpAddress(), player.isAutoControlEnabled(), + player.getLastSeen(), player.getCoverArtScheme().name(), + player.getTranscodeScheme().name(), player.isDynamicIp(), + player.getTechnology(), player.getClientId(), player.getId()); + } + + private void addPlaylist(Player player) { + PlayQueue playQueue = playlists.get(player.getId()); + if (playQueue == null) { + playQueue = new PlayQueue(); + playlists.put(player.getId(), playQueue); + } + player.setPlayQueue(playQueue); + } + + private class PlayerRowMapper implements ParameterizedRowMapper<Player> { + public Player mapRow(ResultSet rs, int rowNum) throws SQLException { + Player player = new Player(); + int col = 1; + player.setId(rs.getString(col++)); + player.setName(rs.getString(col++)); + player.setType(rs.getString(col++)); + player.setUsername(rs.getString(col++)); + player.setIpAddress(rs.getString(col++)); + player.setAutoControlEnabled(rs.getBoolean(col++)); + player.setLastSeen(rs.getTimestamp(col++)); + player.setCoverArtScheme(CoverArtScheme.valueOf(rs.getString(col++))); + player.setTranscodeScheme(TranscodeScheme.valueOf(rs.getString(col++))); + player.setDynamicIp(rs.getBoolean(col++)); + player.setTechnology(PlayerTechnology.valueOf(rs.getString(col++))); + player.setClientId(rs.getString(col++)); + + addPlaylist(player); + return player; + } + } +} diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/PlaylistDao.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/PlaylistDao.java new file mode 100644 index 00000000..54cbaded --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/PlaylistDao.java @@ -0,0 +1,142 @@ +/* + 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; + +import net.sourceforge.subsonic.Logger; +import net.sourceforge.subsonic.domain.MediaFile; +import net.sourceforge.subsonic.domain.Playlist; +import org.springframework.jdbc.core.RowMapper; +import org.springframework.jdbc.core.simple.ParameterizedRowMapper; + +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.ArrayList; +import java.util.Date; +import java.util.List; +import java.util.SortedMap; +import java.util.TreeMap; + +/** + * Provides database services for playlists. + * + * @author Sindre Mehus + */ +public class PlaylistDao extends AbstractDao { + + private static final Logger LOG = Logger.getLogger(PlaylistDao.class); + private static final String COLUMNS = "id, username, is_public, name, comment, file_count, duration_seconds, " + + "created, changed, imported_from"; + private final RowMapper rowMapper = new PlaylistMapper(); + + public List<Playlist> getReadablePlaylistsForUser(String username) { + + List<Playlist> result1 = getWritablePlaylistsForUser(username); + List<Playlist> result2 = query("select " + COLUMNS + " from playlist where is_public", rowMapper); + List<Playlist> result3 = query("select " + prefix(COLUMNS, "playlist") + " from playlist, playlist_user where " + + "playlist.id = playlist_user.playlist_id and " + + "playlist.username != ? and " + + "playlist_user.username = ?", rowMapper, username, username); + + // Put in sorted map to avoid duplicates. + SortedMap<Integer, Playlist> map = new TreeMap<Integer, Playlist>(); + for (Playlist playlist : result1) { + map.put(playlist.getId(), playlist); + } + for (Playlist playlist : result2) { + map.put(playlist.getId(), playlist); + } + for (Playlist playlist : result3) { + map.put(playlist.getId(), playlist); + } + return new ArrayList<Playlist>(map.values()); + } + + public List<Playlist> getWritablePlaylistsForUser(String username) { + return query("select " + COLUMNS + " from playlist where username=?", rowMapper, username); + } + + public Playlist getPlaylist(int id) { + return queryOne("select " + COLUMNS + " from playlist where id=?", rowMapper, id); + } + + public List<Playlist> getAllPlaylists() { + return query("select " + COLUMNS + " from playlist", rowMapper); + } + + public synchronized void createPlaylist(Playlist playlist) { + update("insert into playlist(" + COLUMNS + ") values(" + questionMarks(COLUMNS) + ")", + null, playlist.getUsername(), playlist.isPublic(), playlist.getName(), playlist.getComment(), + 0, 0, playlist.getCreated(), playlist.getChanged(), playlist.getImportedFrom()); + + int id = queryForInt("select max(id) from playlist", 0); + playlist.setId(id); + } + + public void setFilesInPlaylist(int id, List<MediaFile> files) { + update("delete from playlist_file where playlist_id=?", id); + int duration = 0; + for (MediaFile file : files) { + update("insert into playlist_file (playlist_id, media_file_id) values (?, ?)", id, file.getId()); + if (file.getDurationSeconds() != null) { + duration += file.getDurationSeconds(); + } + } + update("update playlist set file_count=?, duration_seconds=?, changed=? where id=?", files.size(), duration, new Date(), id); + } + + public List<String> getPlaylistUsers(int playlistId) { + return queryForStrings("select username from playlist_user where playlist_id=?", playlistId); + } + + public void addPlaylistUser(int playlistId, String username) { + if (!getPlaylistUsers(playlistId).contains(username)) { + update("insert into playlist_user(playlist_id,username) values (?,?)", playlistId, username); + } + } + + public void deletePlaylistUser(int playlistId, String username) { + update("delete from playlist_user where playlist_id=? and username=?", playlistId, username); + } + + public synchronized void deletePlaylist(int id) { + update("delete from playlist where id=?", id); + } + + public void updatePlaylist(Playlist playlist) { + update("update playlist set username=?, is_public=?, name=?, comment=?, changed=?, imported_from=? where id=?", + playlist.getUsername(), playlist.isPublic(), playlist.getName(), playlist.getComment(), + new Date(), playlist.getImportedFrom(), playlist.getId()); + } + + private static class PlaylistMapper implements ParameterizedRowMapper<Playlist> { + public Playlist mapRow(ResultSet rs, int rowNum) throws SQLException { + return new Playlist( + rs.getInt(1), + rs.getString(2), + rs.getBoolean(3), + rs.getString(4), + rs.getString(5), + rs.getInt(6), + rs.getInt(7), + rs.getTimestamp(8), + rs.getTimestamp(9), + rs.getString(10)); + } + } +} diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/PodcastDao.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/PodcastDao.java new file mode 100644 index 00000000..3f274ec6 --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/PodcastDao.java @@ -0,0 +1,165 @@ +/* + 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; + +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.List; + +import org.springframework.jdbc.core.RowMapper; +import org.springframework.jdbc.core.simple.ParameterizedRowMapper; + +import net.sourceforge.subsonic.domain.PodcastChannel; +import net.sourceforge.subsonic.domain.PodcastEpisode; +import net.sourceforge.subsonic.domain.PodcastStatus; + +/** + * Provides database services for Podcast channels and episodes. + * + * @author Sindre Mehus + */ +public class PodcastDao extends AbstractDao { + + private static final String CHANNEL_COLUMNS = "id, url, title, description, status, error_message"; + private static final String EPISODE_COLUMNS = "id, channel_id, url, path, title, description, publish_date, " + + "duration, bytes_total, bytes_downloaded, status, error_message"; + + private PodcastChannelRowMapper channelRowMapper = new PodcastChannelRowMapper(); + private PodcastEpisodeRowMapper episodeRowMapper = new PodcastEpisodeRowMapper(); + + /** + * Creates a new Podcast channel. + * + * @param channel The Podcast channel to create. + * @return The ID of the newly created channel. + */ + public synchronized int createChannel(PodcastChannel channel) { + String sql = "insert into podcast_channel (" + CHANNEL_COLUMNS + ") values (" + questionMarks(CHANNEL_COLUMNS) + ")"; + update(sql, null, channel.getUrl(), channel.getTitle(), channel.getDescription(), + channel.getStatus().name(), channel.getErrorMessage()); + + return getJdbcTemplate().queryForInt("select max(id) from podcast_channel"); + } + + /** + * Returns all Podcast channels. + * + * @return Possibly empty list of all Podcast channels. + */ + public List<PodcastChannel> getAllChannels() { + String sql = "select " + CHANNEL_COLUMNS + " from podcast_channel"; + return query(sql, channelRowMapper); + } + + /** + * Updates the given Podcast channel. + * + * @param channel The Podcast channel to update. + */ + public void updateChannel(PodcastChannel channel) { + String sql = "update podcast_channel set url=?, title=?, description=?, status=?, error_message=? where id=?"; + update(sql, channel.getUrl(), channel.getTitle(), channel.getDescription(), + channel.getStatus().name(), channel.getErrorMessage(), channel.getId()); + } + + /** + * Deletes the Podcast channel with the given ID. + * + * @param id The Podcast channel ID. + */ + public void deleteChannel(int id) { + String sql = "delete from podcast_channel where id=?"; + update(sql, id); + } + + /** + * Creates a new Podcast episode. + * + * @param episode The Podcast episode to create. + */ + public void createEpisode(PodcastEpisode episode) { + String sql = "insert into podcast_episode (" + EPISODE_COLUMNS + ") values (" + questionMarks(EPISODE_COLUMNS) + ")"; + update(sql, null, episode.getChannelId(), episode.getUrl(), episode.getPath(), + episode.getTitle(), episode.getDescription(), episode.getPublishDate(), + episode.getDuration(), episode.getBytesTotal(), episode.getBytesDownloaded(), + episode.getStatus().name(), episode.getErrorMessage()); + } + + /** + * Returns all Podcast episodes for a given channel. + * + * @return Possibly empty list of all Podcast episodes for the given channel, sorted in + * reverse chronological order (newest episode first). + */ + public List<PodcastEpisode> getEpisodes(int channelId) { + String sql = "select " + EPISODE_COLUMNS + " from podcast_episode where channel_id=? order by publish_date desc"; + return query(sql, episodeRowMapper, channelId); + } + + /** + * Returns the Podcast episode with the given ID. + * + * @param episodeId The Podcast episode ID. + * @return The episode or <code>null</code> if not found. + */ + public PodcastEpisode getEpisode(int episodeId) { + String sql = "select " + EPISODE_COLUMNS + " from podcast_episode where id=?"; + return queryOne(sql, episodeRowMapper, episodeId); + } + + /** + * Updates the given Podcast episode. + * + * @param episode The Podcast episode to update. + * @return The number of episodes updated (zero or one). + */ + public int updateEpisode(PodcastEpisode episode) { + String sql = "update podcast_episode set url=?, path=?, title=?, description=?, publish_date=?, duration=?, " + + "bytes_total=?, bytes_downloaded=?, status=?, error_message=? where id=?"; + return update(sql, episode.getUrl(), episode.getPath(), episode.getTitle(), + episode.getDescription(), episode.getPublishDate(), episode.getDuration(), + episode.getBytesTotal(), episode.getBytesDownloaded(), episode.getStatus().name(), + episode.getErrorMessage(), episode.getId()); + } + + /** + * Deletes the Podcast episode with the given ID. + * + * @param id The Podcast episode ID. + */ + public void deleteEpisode(int id) { + String sql = "delete from podcast_episode where id=?"; + update(sql, id); + } + + private static class PodcastChannelRowMapper implements RowMapper { + public Object mapRow(ResultSet rs, int rowNum) throws SQLException { + return new PodcastChannel(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), + PodcastStatus.valueOf(rs.getString(5)), rs.getString(6)); + } + } + + private static class PodcastEpisodeRowMapper implements ParameterizedRowMapper<PodcastEpisode> { + public PodcastEpisode mapRow(ResultSet rs, int rowNum) throws SQLException { + return new PodcastEpisode(rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getString(4), rs.getString(5), + rs.getString(6), rs.getTimestamp(7), rs.getString(8), (Long) rs.getObject(9), + (Long) rs.getObject(10), PodcastStatus.valueOf(rs.getString(11)), rs.getString(12)); + } + } +} diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/RatingDao.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/RatingDao.java new file mode 100644 index 00000000..221fe889 --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/RatingDao.java @@ -0,0 +1,99 @@ +/* + 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; + +import net.sourceforge.subsonic.domain.MediaFile; +import org.springframework.dao.EmptyResultDataAccessException; + +import java.util.ArrayList; +import java.util.List; + +/** + * Provides database services for ratings. + * + * @author Sindre Mehus + */ +public class RatingDao extends AbstractDao { + + /** + * Returns paths for the highest rated music files. + * + * @param offset Number of files to skip. + * @param count Maximum number of files to return. + * @return Paths for the highest rated music files. + */ + public List<String> getHighestRated(int offset, int count) { + if (count < 1) { + return new ArrayList<String>(); + } + + String sql = "select user_rating.path from user_rating, media_file " + + "where user_rating.path=media_file.path and media_file.present " + + "group by path " + + "order by avg(rating) desc limit " + count + " offset " + offset; + return queryForStrings(sql); + } + + /** + * Sets the rating for a media file and a given user. + * + * @param username The user name. + * @param mediaFile The media file. + * @param rating The rating between 1 and 5, or <code>null</code> to remove the rating. + */ + public void setRatingForUser(String username, MediaFile mediaFile, Integer rating) { + if (rating != null && (rating < 1 || rating > 5)) { + return; + } + + update("delete from user_rating where username=? and path=?", username, mediaFile.getPath()); + if (rating != null) { + update("insert into user_rating values(?, ?, ?)", username, mediaFile.getPath(), rating); + } + } + + /** + * Returns the average rating for the given media file. + * + * @param mediaFile The media file. + * @return The average rating, or <code>null</code> if no ratings are set. + */ + public Double getAverageRating(MediaFile mediaFile) { + try { + return (Double) getJdbcTemplate().queryForObject("select avg(rating) from user_rating where path=?", new Object[]{mediaFile.getPath()}, Double.class); + } catch (EmptyResultDataAccessException x) { + return null; + } + } + + /** + * Returns the rating for the given user and media file. + * + * @param username The user name. + * @param mediaFile The media file. + * @return The rating, or <code>null</code> if no rating is set. + */ + public Integer getRatingForUser(String username, MediaFile mediaFile) { + try { + return getJdbcTemplate().queryForInt("select rating from user_rating where username=? and path=?", new Object[]{username, mediaFile.getPath()}); + } catch (EmptyResultDataAccessException x) { + return null; + } + } +} diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/ShareDao.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/ShareDao.java new file mode 100644 index 00000000..17d4cd73 --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/ShareDao.java @@ -0,0 +1,131 @@ +/* + 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; + +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.List; + +import org.springframework.jdbc.core.simple.ParameterizedRowMapper; + +import net.sourceforge.subsonic.domain.Share; + +/** + * Provides database services for shared media. + * + * @author Sindre Mehus + */ +public class ShareDao extends AbstractDao { + + private static final String COLUMNS = "id, name, description, username, created, expires, last_visited, visit_count"; + + private ShareRowMapper shareRowMapper = new ShareRowMapper(); + private ShareFileRowMapper shareFileRowMapper = new ShareFileRowMapper(); + + /** + * Creates a new share. + * + * @param share The share to create. The ID of the share will be set by this method. + */ + public synchronized void createShare(Share share) { + String sql = "insert into share (" + COLUMNS + ") values (" + questionMarks(COLUMNS) + ")"; + update(sql, null, share.getName(), share.getDescription(), share.getUsername(), share.getCreated(), + share.getExpires(), share.getLastVisited(), share.getVisitCount()); + + int id = getJdbcTemplate().queryForInt("select max(id) from share"); + share.setId(id); + } + + /** + * Returns all shares. + * + * @return Possibly empty list of all shares. + */ + public List<Share> getAllShares() { + String sql = "select " + COLUMNS + " from share"; + return query(sql, shareRowMapper); + } + + public Share getShareByName(String shareName) { + String sql = "select " + COLUMNS + " from share where name=?"; + return queryOne(sql, shareRowMapper, shareName); + } + + public Share getShareById(int id) { + String sql = "select " + COLUMNS + " from share where id=?"; + return queryOne(sql, shareRowMapper, id); + } + + /** + * Updates the given share. + * + * @param share The share to update. + */ + public void updateShare(Share share) { + String sql = "update share set name=?, description=?, username=?, created=?, expires=?, last_visited=?, visit_count=? where id=?"; + update(sql, share.getName(), share.getDescription(), share.getUsername(), share.getCreated(), share.getExpires(), + share.getLastVisited(), share.getVisitCount(), share.getId()); + } + + /** + * Creates shared files. + * + * @param shareId The share ID. + * @param paths Paths of the files to share. + */ + public void createSharedFiles(int shareId, String... paths) { + String sql = "insert into share_file (share_id, path) values (?, ?)"; + for (String path : paths) { + update(sql, shareId, path); + } + } + + /** + * Returns files for a share. + * + * @param shareId The ID of the share. + * @return The paths of the shared files. + */ + public List<String> getSharedFiles(int shareId) { + return query("select path from share_file where share_id=?", shareFileRowMapper, shareId); + } + + /** + * Deletes the share with the given ID. + * + * @param id The ID of the share to delete. + */ + public void deleteShare(Integer id) { + update("delete from share where id=?", id); + } + + private static class ShareRowMapper implements ParameterizedRowMapper<Share> { + public Share mapRow(ResultSet rs, int rowNum) throws SQLException { + return new Share(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getTimestamp(5), + rs.getTimestamp(6), rs.getTimestamp(7), rs.getInt(8)); + } + } + + private static class ShareFileRowMapper implements ParameterizedRowMapper<String> { + public String mapRow(ResultSet rs, int rowNum) throws SQLException { + return rs.getString(1); + } + + } +} diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/TranscodingDao.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/TranscodingDao.java new file mode 100644 index 00000000..22b8ae20 --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/TranscodingDao.java @@ -0,0 +1,123 @@ +/* + 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; + +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.List; + +import org.springframework.jdbc.core.simple.ParameterizedRowMapper; + +import net.sourceforge.subsonic.Logger; +import net.sourceforge.subsonic.domain.Transcoding; + +/** + * Provides database services for transcoding configurations. + * + * @author Sindre Mehus + */ +public class TranscodingDao extends AbstractDao { + + private static final Logger LOG = Logger.getLogger(TranscodingDao.class); + private static final String COLUMNS = "id, name, source_formats, target_format, step1, step2, step3, default_active"; + private TranscodingRowMapper rowMapper = new TranscodingRowMapper(); + + /** + * Returns all transcodings. + * + * @return Possibly empty list of all transcodings. + */ + public List<Transcoding> getAllTranscodings() { + String sql = "select " + COLUMNS + " from transcoding2"; + return query(sql, rowMapper); + } + + /** + * Returns all active transcodings for the given player. + * + * @param playerId The player ID. + * @return All active transcodings for the player. + */ + public List<Transcoding> getTranscodingsForPlayer(String playerId) { + String sql = "select " + COLUMNS + " from transcoding2, player_transcoding2 " + + "where player_transcoding2.player_id = ? " + + "and player_transcoding2.transcoding_id = transcoding2.id"; + return query(sql, rowMapper, playerId); + } + + /** + * Sets the list of active transcodings for the given player. + * + * @param playerId The player ID. + * @param transcodingIds ID's of the active transcodings. + */ + public void setTranscodingsForPlayer(String playerId, int[] transcodingIds) { + update("delete from player_transcoding2 where player_id = ?", playerId); + String sql = "insert into player_transcoding2(player_id, transcoding_id) values (?, ?)"; + for (int transcodingId : transcodingIds) { + update(sql, playerId, transcodingId); + } + } + + /** + * Creates a new transcoding. + * + * @param transcoding The transcoding to create. + */ + public synchronized void createTranscoding(Transcoding transcoding) { + int id = getJdbcTemplate().queryForInt("select max(id) + 1 from transcoding2"); + transcoding.setId(id); + String sql = "insert into transcoding2 (" + COLUMNS + ") values (" + questionMarks(COLUMNS) + ")"; + update(sql, transcoding.getId(), transcoding.getName(), transcoding.getSourceFormats(), + transcoding.getTargetFormat(), transcoding.getStep1(), + transcoding.getStep2(), transcoding.getStep3(), transcoding.isDefaultActive()); + LOG.info("Created transcoding " + transcoding.getName()); + } + + /** + * Deletes the transcoding with the given ID. + * + * @param id The transcoding ID. + */ + public void deleteTranscoding(Integer id) { + String sql = "delete from transcoding2 where id=?"; + update(sql, id); + LOG.info("Deleted transcoding with ID " + id); + } + + /** + * Updates the given transcoding. + * + * @param transcoding The transcoding to update. + */ + public void updateTranscoding(Transcoding transcoding) { + String sql = "update transcoding2 set name=?, source_formats=?, target_format=?, " + + "step1=?, step2=?, step3=?, default_active=? where id=?"; + update(sql, transcoding.getName(), transcoding.getSourceFormats(), + transcoding.getTargetFormat(), transcoding.getStep1(), transcoding.getStep2(), + transcoding.getStep3(), transcoding.isDefaultActive(), transcoding.getId()); + } + + private static class TranscodingRowMapper implements ParameterizedRowMapper<Transcoding> { + public Transcoding mapRow(ResultSet rs, int rowNum) throws SQLException { + return new Transcoding(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), + rs.getString(6), rs.getString(7), rs.getBoolean(8)); + } + } +} diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/UserDao.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/UserDao.java new file mode 100644 index 00000000..e7807765 --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/UserDao.java @@ -0,0 +1,352 @@ +/* + 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; + +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.List; + +import org.springframework.jdbc.core.simple.ParameterizedRowMapper; + +import net.sourceforge.subsonic.Logger; +import net.sourceforge.subsonic.domain.AvatarScheme; +import net.sourceforge.subsonic.domain.TranscodeScheme; +import net.sourceforge.subsonic.domain.User; +import net.sourceforge.subsonic.domain.UserSettings; +import net.sourceforge.subsonic.util.StringUtil; + +/** + * Provides user-related database services. + * + * @author Sindre Mehus + */ +public class UserDao extends AbstractDao { + + private static final Logger LOG = Logger.getLogger(UserDao.class); + private static final String USER_COLUMNS = "username, password, email, ldap_authenticated, bytes_streamed, bytes_downloaded, bytes_uploaded"; + private static final String USER_SETTINGS_COLUMNS = "username, locale, theme_id, final_version_notification, beta_version_notification, " + + "main_caption_cutoff, main_track_number, main_artist, main_album, main_genre, " + + "main_year, main_bit_rate, main_duration, main_format, main_file_size, " + + "playlist_caption_cutoff, playlist_track_number, playlist_artist, playlist_album, playlist_genre, " + + "playlist_year, playlist_bit_rate, playlist_duration, playlist_format, playlist_file_size, " + + "last_fm_enabled, last_fm_username, last_fm_password, transcode_scheme, show_now_playing, selected_music_folder_id, " + + "party_mode_enabled, now_playing_allowed, avatar_scheme, system_avatar_id, changed, show_chat"; + + private static final Integer ROLE_ID_ADMIN = 1; + private static final Integer ROLE_ID_DOWNLOAD = 2; + private static final Integer ROLE_ID_UPLOAD = 3; + private static final Integer ROLE_ID_PLAYLIST = 4; + private static final Integer ROLE_ID_COVER_ART = 5; + private static final Integer ROLE_ID_COMMENT = 6; + private static final Integer ROLE_ID_PODCAST = 7; + private static final Integer ROLE_ID_STREAM = 8; + private static final Integer ROLE_ID_SETTINGS = 9; + private static final Integer ROLE_ID_JUKEBOX = 10; + private static final Integer ROLE_ID_SHARE = 11; + + private UserRowMapper userRowMapper = new UserRowMapper(); + private UserSettingsRowMapper userSettingsRowMapper = new UserSettingsRowMapper(); + + /** + * Returns the user with the given username. + * + * @param username The username used when logging in. + * @return The user, or <code>null</code> if not found. + */ + public User getUserByName(String username) { + String sql = "select " + USER_COLUMNS + " from user where username=?"; + return queryOne(sql, userRowMapper, username); + } + + /** + * Returns the user with the given email address. + * + * @param email The email address. + * @return The user, or <code>null</code> if not found. + */ + public User getUserByEmail(String email) { + String sql = "select " + USER_COLUMNS + " from user where email=?"; + return queryOne(sql, userRowMapper, email); + } + + /** + * Returns all users. + * + * @return Possibly empty array of all users. + */ + public List<User> getAllUsers() { + String sql = "select " + USER_COLUMNS + " from user"; + return query(sql, userRowMapper); + } + + /** + * Creates a new user. + * + * @param user The user to create. + */ + public void createUser(User user) { + String sql = "insert into user (" + USER_COLUMNS + ") values (" + questionMarks(USER_COLUMNS) + ')'; + update(sql, user.getUsername(), encrypt(user.getPassword()), user.getEmail(), user.isLdapAuthenticated(), + user.getBytesStreamed(), user.getBytesDownloaded(), user.getBytesUploaded()); + writeRoles(user); + } + + /** + * Deletes the user with the given username. + * + * @param username The username. + */ + public void deleteUser(String username) { + if (User.USERNAME_ADMIN.equals(username)) { + throw new IllegalArgumentException("Can't delete admin user."); + } + + String sql = "delete from user_role where username=?"; + update(sql, username); + + sql = "delete from user where username=?"; + update(sql, username); + } + + /** + * Updates the given user. + * + * @param user The user to update. + */ + public void updateUser(User user) { + String sql = "update user set password=?, email=?, ldap_authenticated=?, bytes_streamed=?, bytes_downloaded=?, bytes_uploaded=? " + + "where username=?"; + getJdbcTemplate().update(sql, new Object[]{encrypt(user.getPassword()), user.getEmail(), user.isLdapAuthenticated(), + user.getBytesStreamed(), user.getBytesDownloaded(), user.getBytesUploaded(), + user.getUsername()}); + writeRoles(user); + } + + /** + * Returns the name of the roles for the given user. + * + * @param username The user name. + * @return Roles the user is granted. + */ + public String[] getRolesForUser(String username) { + String sql = "select r.name from role r, user_role ur " + + "where ur.username=? and ur.role_id=r.id"; + List<?> roles = getJdbcTemplate().queryForList(sql, new Object[]{username}, String.class); + String[] result = new String[roles.size()]; + for (int i = 0; i < result.length; i++) { + result[i] = (String) roles.get(i); + } + return result; + } + + /** + * Returns settings for the given user. + * + * @param username The username. + * @return User-specific settings, or <code>null</code> if no such settings exist. + */ + public UserSettings getUserSettings(String username) { + String sql = "select " + USER_SETTINGS_COLUMNS + " from user_settings where username=?"; + return queryOne(sql, userSettingsRowMapper, username); + } + + /** + * Updates settings for the given username, creating it if necessary. + * + * @param settings The user-specific settings. + */ + public void updateUserSettings(UserSettings settings) { + getJdbcTemplate().update("delete from user_settings where username=?", new Object[]{settings.getUsername()}); + + String sql = "insert into user_settings (" + USER_SETTINGS_COLUMNS + ") values (" + questionMarks(USER_SETTINGS_COLUMNS) + ')'; + String locale = settings.getLocale() == null ? null : settings.getLocale().toString(); + UserSettings.Visibility main = settings.getMainVisibility(); + UserSettings.Visibility playlist = settings.getPlaylistVisibility(); + getJdbcTemplate().update(sql, new Object[]{settings.getUsername(), locale, settings.getThemeId(), + settings.isFinalVersionNotificationEnabled(), settings.isBetaVersionNotificationEnabled(), + main.getCaptionCutoff(), main.isTrackNumberVisible(), main.isArtistVisible(), main.isAlbumVisible(), + main.isGenreVisible(), main.isYearVisible(), main.isBitRateVisible(), main.isDurationVisible(), + main.isFormatVisible(), main.isFileSizeVisible(), + playlist.getCaptionCutoff(), playlist.isTrackNumberVisible(), playlist.isArtistVisible(), playlist.isAlbumVisible(), + playlist.isGenreVisible(), playlist.isYearVisible(), playlist.isBitRateVisible(), playlist.isDurationVisible(), + playlist.isFormatVisible(), playlist.isFileSizeVisible(), + settings.isLastFmEnabled(), settings.getLastFmUsername(), encrypt(settings.getLastFmPassword()), + settings.getTranscodeScheme().name(), settings.isShowNowPlayingEnabled(), + settings.getSelectedMusicFolderId(), settings.isPartyModeEnabled(), settings.isNowPlayingAllowed(), + settings.getAvatarScheme().name(), settings.getSystemAvatarId(), settings.getChanged(), settings.isShowChatEnabled()}); + } + + private static String encrypt(String s) { + if (s == null) { + return null; + } + try { + return "enc:" + StringUtil.utf8HexEncode(s); + } catch (Exception e) { + return s; + } + } + + private static String decrypt(String s) { + if (s == null) { + return null; + } + if (!s.startsWith("enc:")) { + return s; + } + try { + return StringUtil.utf8HexDecode(s.substring(4)); + } catch (Exception e) { + return s; + } + } + + private void readRoles(User user) { + synchronized (user.getUsername().intern()) { + String sql = "select role_id from user_role where username=?"; + List<?> roles = getJdbcTemplate().queryForList(sql, new Object[]{user.getUsername()}, Integer.class); + for (Object role : roles) { + if (ROLE_ID_ADMIN.equals(role)) { + user.setAdminRole(true); + } else if (ROLE_ID_DOWNLOAD.equals(role)) { + user.setDownloadRole(true); + } else if (ROLE_ID_UPLOAD.equals(role)) { + user.setUploadRole(true); + } else if (ROLE_ID_PLAYLIST.equals(role)) { + user.setPlaylistRole(true); + } else if (ROLE_ID_COVER_ART.equals(role)) { + user.setCoverArtRole(true); + } else if (ROLE_ID_COMMENT.equals(role)) { + user.setCommentRole(true); + } else if (ROLE_ID_PODCAST.equals(role)) { + user.setPodcastRole(true); + } else if (ROLE_ID_STREAM.equals(role)) { + user.setStreamRole(true); + } else if (ROLE_ID_SETTINGS.equals(role)) { + user.setSettingsRole(true); + } else if (ROLE_ID_JUKEBOX.equals(role)) { + user.setJukeboxRole(true); + } else if (ROLE_ID_SHARE.equals(role)) { + user.setShareRole(true); + } else { + LOG.warn("Unknown role: '" + role + '\''); + } + } + } + } + + private void writeRoles(User user) { + synchronized (user.getUsername().intern()) { + String sql = "delete from user_role where username=?"; + getJdbcTemplate().update(sql, new Object[]{user.getUsername()}); + sql = "insert into user_role (username, role_id) values(?, ?)"; + if (user.isAdminRole()) { + getJdbcTemplate().update(sql, new Object[]{user.getUsername(), ROLE_ID_ADMIN}); + } + if (user.isDownloadRole()) { + getJdbcTemplate().update(sql, new Object[]{user.getUsername(), ROLE_ID_DOWNLOAD}); + } + if (user.isUploadRole()) { + getJdbcTemplate().update(sql, new Object[]{user.getUsername(), ROLE_ID_UPLOAD}); + } + if (user.isPlaylistRole()) { + getJdbcTemplate().update(sql, new Object[]{user.getUsername(), ROLE_ID_PLAYLIST}); + } + if (user.isCoverArtRole()) { + getJdbcTemplate().update(sql, new Object[]{user.getUsername(), ROLE_ID_COVER_ART}); + } + if (user.isCommentRole()) { + getJdbcTemplate().update(sql, new Object[]{user.getUsername(), ROLE_ID_COMMENT}); + } + if (user.isPodcastRole()) { + getJdbcTemplate().update(sql, new Object[]{user.getUsername(), ROLE_ID_PODCAST}); + } + if (user.isStreamRole()) { + getJdbcTemplate().update(sql, new Object[]{user.getUsername(), ROLE_ID_STREAM}); + } + if (user.isJukeboxRole()) { + getJdbcTemplate().update(sql, new Object[]{user.getUsername(), ROLE_ID_JUKEBOX}); + } + if (user.isSettingsRole()) { + getJdbcTemplate().update(sql, new Object[]{user.getUsername(), ROLE_ID_SETTINGS}); + } + if (user.isShareRole()) { + getJdbcTemplate().update(sql, new Object[]{user.getUsername(), ROLE_ID_SHARE}); + } + } + } + + private class UserRowMapper implements ParameterizedRowMapper<User> { + public User mapRow(ResultSet rs, int rowNum) throws SQLException { + User user = new User(rs.getString(1), decrypt(rs.getString(2)), rs.getString(3), rs.getBoolean(4), + rs.getLong(5), rs.getLong(6), rs.getLong(7)); + readRoles(user); + return user; + } + } + + private static class UserSettingsRowMapper implements ParameterizedRowMapper<UserSettings> { + public UserSettings mapRow(ResultSet rs, int rowNum) throws SQLException { + int col = 1; + UserSettings settings = new UserSettings(rs.getString(col++)); + settings.setLocale(StringUtil.parseLocale(rs.getString(col++))); + settings.setThemeId(rs.getString(col++)); + settings.setFinalVersionNotificationEnabled(rs.getBoolean(col++)); + settings.setBetaVersionNotificationEnabled(rs.getBoolean(col++)); + + settings.getMainVisibility().setCaptionCutoff(rs.getInt(col++)); + settings.getMainVisibility().setTrackNumberVisible(rs.getBoolean(col++)); + settings.getMainVisibility().setArtistVisible(rs.getBoolean(col++)); + settings.getMainVisibility().setAlbumVisible(rs.getBoolean(col++)); + settings.getMainVisibility().setGenreVisible(rs.getBoolean(col++)); + settings.getMainVisibility().setYearVisible(rs.getBoolean(col++)); + settings.getMainVisibility().setBitRateVisible(rs.getBoolean(col++)); + settings.getMainVisibility().setDurationVisible(rs.getBoolean(col++)); + settings.getMainVisibility().setFormatVisible(rs.getBoolean(col++)); + settings.getMainVisibility().setFileSizeVisible(rs.getBoolean(col++)); + + settings.getPlaylistVisibility().setCaptionCutoff(rs.getInt(col++)); + settings.getPlaylistVisibility().setTrackNumberVisible(rs.getBoolean(col++)); + settings.getPlaylistVisibility().setArtistVisible(rs.getBoolean(col++)); + settings.getPlaylistVisibility().setAlbumVisible(rs.getBoolean(col++)); + settings.getPlaylistVisibility().setGenreVisible(rs.getBoolean(col++)); + settings.getPlaylistVisibility().setYearVisible(rs.getBoolean(col++)); + settings.getPlaylistVisibility().setBitRateVisible(rs.getBoolean(col++)); + settings.getPlaylistVisibility().setDurationVisible(rs.getBoolean(col++)); + settings.getPlaylistVisibility().setFormatVisible(rs.getBoolean(col++)); + settings.getPlaylistVisibility().setFileSizeVisible(rs.getBoolean(col++)); + + settings.setLastFmEnabled(rs.getBoolean(col++)); + settings.setLastFmUsername(rs.getString(col++)); + settings.setLastFmPassword(decrypt(rs.getString(col++))); + + settings.setTranscodeScheme(TranscodeScheme.valueOf(rs.getString(col++))); + settings.setShowNowPlayingEnabled(rs.getBoolean(col++)); + settings.setSelectedMusicFolderId(rs.getInt(col++)); + settings.setPartyModeEnabled(rs.getBoolean(col++)); + settings.setNowPlayingAllowed(rs.getBoolean(col++)); + settings.setAvatarScheme(AvatarScheme.valueOf(rs.getString(col++))); + settings.setSystemAvatarId((Integer) rs.getObject(col++)); + settings.setChanged(rs.getTimestamp(col++)); + settings.setShowChatEnabled(rs.getBoolean(col++)); + + return settings; + } + } +} diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema.java new file mode 100644 index 00000000..674f85ca --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema.java @@ -0,0 +1,66 @@ +/* + 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 org.springframework.jdbc.core.*; + +/** + * Used for creating and evolving the database schema. + * + * @author Sindre Mehus + */ +public abstract class Schema { + + /** + * Executes this schema. + * @param template The JDBC template to use. + */ + public abstract void execute(JdbcTemplate template); + + /** + * Returns whether the given table exists. + * @param template The JDBC template to use. + * @param table The table in question. + * @return Whether the table exists. + */ + protected boolean tableExists(JdbcTemplate template, String table) { + try { + template.execute("select 1 from " + table); + } catch (Exception x) { + return false; + } + return true; + } + + /** + * Returns whether the given column in the given table exists. + * @param template The JDBC template to use. + * @param column The column in question. + * @param table The table in question. + * @return Whether the column exists. + */ + protected boolean columnExists(JdbcTemplate template, String column, String table) { + try { + template.execute("select " + column + " from " + table + " where 1 = 0"); + } catch (Exception x) { + return false; + } + return true; + } +} diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema25.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema25.java new file mode 100644 index 00000000..33cc2525 --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema25.java @@ -0,0 +1,81 @@ +/* + 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 org.springframework.jdbc.core.*; +import net.sourceforge.subsonic.*; + +/** + * Used for creating and evolving the database schema. + * This class implementes the database schema for Subsonic version 2.5. + * + * @author Sindre Mehus + */ +public class Schema25 extends Schema{ + private static final Logger LOG = Logger.getLogger(Schema25.class); + + public void execute(JdbcTemplate template) { + if (!tableExists(template, "version")) { + LOG.info("Database table 'version' not found. Creating it."); + template.execute("create table version (version int not null)"); + template.execute("insert into version values (1)"); + LOG.info("Database table 'version' was created successfully."); + } + + if (!tableExists(template, "role")) { + LOG.info("Database table 'role' not found. Creating it."); + template.execute("create table role (" + + "id int not null," + + "name varchar not null," + + "primary key (id))"); + template.execute("insert into role values (1, 'admin')"); + template.execute("insert into role values (2, 'download')"); + template.execute("insert into role values (3, 'upload')"); + template.execute("insert into role values (4, 'playlist')"); + template.execute("insert into role values (5, 'coverart')"); + LOG.info("Database table 'role' was created successfully."); + } + + if (!tableExists(template, "user")) { + LOG.info("Database table 'user' not found. Creating it."); + template.execute("create table user (" + + "username varchar not null," + + "password varchar not null," + + "primary key (username))"); + template.execute("insert into user values ('admin', 'admin')"); + LOG.info("Database table 'user' was created successfully."); + } + + if (!tableExists(template, "user_role")) { + LOG.info("Database table 'user_role' not found. Creating it."); + template.execute("create table user_role (" + + "username varchar not null," + + "role_id int not null," + + "primary key (username, role_id)," + + "foreign key (username) references user(username)," + + "foreign key (role_id) references role(id))"); + template.execute("insert into user_role values ('admin', 1)"); + template.execute("insert into user_role values ('admin', 2)"); + template.execute("insert into user_role values ('admin', 3)"); + template.execute("insert into user_role values ('admin', 4)"); + template.execute("insert into user_role values ('admin', 5)"); + LOG.info("Database table 'user_role' was created successfully."); + } + } +} diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema26.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema26.java new file mode 100644 index 00000000..6d60b29b --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema26.java @@ -0,0 +1,110 @@ +/* + 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.*; +import net.sourceforge.subsonic.util.Util; +import org.springframework.jdbc.core.*; + +/** + * Used for creating and evolving the database schema. + * This class implementes the database schema for Subsonic version 2.6. + * + * @author Sindre Mehus + */ +public class Schema26 extends Schema{ + private static final Logger LOG = Logger.getLogger(Schema26.class); + + public void execute(JdbcTemplate template) { + + if (template.queryForInt("select count(*) from version where version = 2") == 0) { + LOG.info("Updating database schema to version 2."); + template.execute("insert into version values (2)"); + } + + if (!tableExists(template, "music_folder")) { + LOG.info("Database table 'music_folder' not found. Creating it."); + template.execute("create table music_folder (" + + "id identity," + + "path varchar not null," + + "name varchar not null," + + "enabled boolean not null)"); + template.execute("insert into music_folder values (null, '" + Util.getDefaultMusicFolder() + "', 'Music', true)"); + LOG.info("Database table 'music_folder' was created successfully."); + } + + if (!tableExists(template, "music_file_info")) { + LOG.info("Database table 'music_file_info' not found. Creating it."); + template.execute("create cached table music_file_info (" + + "id identity," + + "path varchar not null," + + "rating int," + + "comment varchar," + + "play_count int," + + "last_played datetime)"); + template.execute("create index idx_music_file_info_path on music_file_info(path)"); + LOG.info("Database table 'music_file_info' was created successfully."); + } + + if (!tableExists(template, "internet_radio")) { + LOG.info("Database table 'internet_radio' not found. Creating it."); + template.execute("create table internet_radio (" + + "id identity," + + "name varchar not null," + + "stream_url varchar not null," + + "homepage_url varchar," + + "enabled boolean not null)"); + LOG.info("Database table 'internet_radio' was created successfully."); + } + + if (!tableExists(template, "player")) { + LOG.info("Database table 'player' not found. Creating it."); + template.execute("create table player (" + + "id int not null," + + "name varchar," + + "type varchar," + + "username varchar," + + "ip_address varchar," + + "auto_control_enabled boolean not null," + + "last_seen datetime," + + "cover_art_scheme varchar not null," + + "transcode_scheme varchar not null," + + "primary key (id))"); + LOG.info("Database table 'player' was created successfully."); + } + + // 'dynamic_ip' was added in 2.6.beta2 + if (!columnExists(template, "dynamic_ip", "player")) { + LOG.info("Database column 'player.dynamic_ip' not found. Creating it."); + template.execute("alter table player " + + "add dynamic_ip boolean default true not null"); + LOG.info("Database column 'player.dynamic_ip' was added successfully."); + } + + if (template.queryForInt("select count(*) from role where id = 6") == 0) { + LOG.info("Role 'comment' not found in database. Creating it."); + template.execute("insert into role values (6, 'comment')"); + template.execute("insert into user_role " + + "select distinct u.username, 6 from user u, user_role ur " + + "where u.username = ur.username and ur.role_id in (1, 5)"); + LOG.info("Role 'comment' was created successfully."); + } + } + +} diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema27.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema27.java new file mode 100644 index 00000000..4057622e --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema27.java @@ -0,0 +1,54 @@ +/* + 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.*; +import org.springframework.jdbc.core.*; + +/** + * Used for creating and evolving the database schema. + * This class implementes the database schema for Subsonic version 2.7. + * + * @author Sindre Mehus + */ +public class Schema27 extends Schema{ + private static final Logger LOG = Logger.getLogger(Schema27.class); + + public void execute(JdbcTemplate template) { + + if (template.queryForInt("select count(*) from version where version = 3") == 0) { + LOG.info("Updating database schema to version 3."); + template.execute("insert into version values (3)"); + + LOG.info("Converting database column 'music_file_info.path' to varchar_ignorecase."); + template.execute("drop index idx_music_file_info_path"); + template.execute("alter table music_file_info alter column path varchar_ignorecase not null"); + template.execute("create index idx_music_file_info_path on music_file_info(path)"); + LOG.info("Database column 'music_file_info.path' was converted successfully."); + } + + if (!columnExists(template, "bytes_streamed", "user")) { + LOG.info("Database columns 'user.bytes_streamed/downloaded/uploaded' not found. Creating them."); + template.execute("alter table user add bytes_streamed bigint default 0 not null"); + template.execute("alter table user add bytes_downloaded bigint default 0 not null"); + template.execute("alter table user add bytes_uploaded bigint default 0 not null"); + LOG.info("Database columns 'user.bytes_streamed/downloaded/uploaded' were added successfully."); + } + } +} diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema28.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema28.java new file mode 100644 index 00000000..dbee6730 --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema28.java @@ -0,0 +1,110 @@ +/* + 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.*; +import org.springframework.jdbc.core.*; + +/** + * Used for creating and evolving the database schema. + * This class implementes the database schema for Subsonic version 2.8. + * + * @author Sindre Mehus + */ +public class Schema28 extends Schema { + private static final Logger LOG = Logger.getLogger(Schema28.class); + + public void execute(JdbcTemplate template) { + + if (template.queryForInt("select count(*) from version where version = 4") == 0) { + LOG.info("Updating database schema to version 4."); + template.execute("insert into version values (4)"); + } + + if (!tableExists(template, "user_settings")) { + LOG.info("Database table 'user_settings' not found. Creating it."); + template.execute("create table user_settings (" + + "username varchar not null," + + "locale varchar," + + "theme_id varchar," + + "final_version_notification boolean default true not null," + + "beta_version_notification boolean default false not null," + + "main_caption_cutoff int default 35 not null," + + "main_track_number boolean default true not null," + + "main_artist boolean default true not null," + + "main_album boolean default false not null," + + "main_genre boolean default false not null," + + "main_year boolean default false not null," + + "main_bit_rate boolean default false not null," + + "main_duration boolean default true not null," + + "main_format boolean default false not null," + + "main_file_size boolean default false not null," + + "playlist_caption_cutoff int default 35 not null," + + "playlist_track_number boolean default false not null," + + "playlist_artist boolean default true not null," + + "playlist_album boolean default true not null," + + "playlist_genre boolean default false not null," + + "playlist_year boolean default true not null," + + "playlist_bit_rate boolean default false not null," + + "playlist_duration boolean default true not null," + + "playlist_format boolean default true not null," + + "playlist_file_size boolean default true not null," + + "primary key (username)," + + "foreign key (username) references user(username) on delete cascade)"); + LOG.info("Database table 'user_settings' was created successfully."); + } + + if (!tableExists(template, "transcoding")) { + LOG.info("Database table 'transcoding' not found. Creating it."); + template.execute("create table transcoding (" + + "id identity," + + "name varchar not null," + + "source_format varchar not null," + + "target_format varchar not null," + + "step1 varchar not null," + + "step2 varchar," + + "step3 varchar," + + "enabled boolean not null)"); + + template.execute("insert into transcoding values(null,'wav > mp3', 'wav', 'mp3','ffmpeg -i %s -v 0 -f wav -','lame -b %b --tt %t --ta %a --tl %l -S --resample 44.1 - -',null,true)"); + template.execute("insert into transcoding values(null,'flac > mp3','flac','mp3','ffmpeg -i %s -v 0 -f wav -','lame -b %b --tt %t --ta %a --tl %l -S --resample 44.1 - -',null,true)"); + template.execute("insert into transcoding values(null,'ogg > mp3' ,'ogg' ,'mp3','ffmpeg -i %s -v 0 -f wav -','lame -b %b --tt %t --ta %a --tl %l -S --resample 44.1 - -',null,true)"); + template.execute("insert into transcoding values(null,'wma > mp3' ,'wma' ,'mp3','ffmpeg -i %s -v 0 -f wav -','lame -b %b --tt %t --ta %a --tl %l -S --resample 44.1 - -',null,true)"); + template.execute("insert into transcoding values(null,'m4a > mp3' ,'m4a' ,'mp3','ffmpeg -i %s -v 0 -f wav -','lame -b %b --tt %t --ta %a --tl %l -S --resample 44.1 - -',null,false)"); + template.execute("insert into transcoding values(null,'aac > mp3' ,'aac' ,'mp3','ffmpeg -i %s -v 0 -f wav -','lame -b %b --tt %t --ta %a --tl %l -S --resample 44.1 - -',null,false)"); + template.execute("insert into transcoding values(null,'ape > mp3' ,'ape' ,'mp3','ffmpeg -i %s -v 0 -f wav -','lame -b %b --tt %t --ta %a --tl %l -S --resample 44.1 - -',null,true)"); + template.execute("insert into transcoding values(null,'mpc > mp3' ,'mpc' ,'mp3','ffmpeg -i %s -v 0 -f wav -','lame -b %b --tt %t --ta %a --tl %l -S --resample 44.1 - -',null,true)"); + template.execute("insert into transcoding values(null,'mv > mp3' ,'mv' ,'mp3','ffmpeg -i %s -v 0 -f wav -','lame -b %b --tt %t --ta %a --tl %l -S --resample 44.1 - -',null,true)"); + template.execute("insert into transcoding values(null,'shn > mp3' ,'shn' ,'mp3','ffmpeg -i %s -v 0 -f wav -','lame -b %b --tt %t --ta %a --tl %l -S --resample 44.1 - -',null,true)"); + + LOG.info("Database table 'transcoding' was created successfully."); + } + + if (!tableExists(template, "player_transcoding")) { + LOG.info("Database table 'player_transcoding' not found. Creating it."); + template.execute("create table player_transcoding (" + + "player_id int not null," + + "transcoding_id int not null," + + "primary key (player_id, transcoding_id)," + + "foreign key (player_id) references player(id) on delete cascade," + + "foreign key (transcoding_id) references transcoding(id) on delete cascade)"); + LOG.info("Database table 'player_transcoding' was created successfully."); + } + } +} diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema29.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema29.java new file mode 100644 index 00000000..dd4748d1 --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema29.java @@ -0,0 +1,55 @@ +/* + 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.*; +import org.springframework.jdbc.core.*; + +/** + * Used for creating and evolving the database schema. + * This class implementes the database schema for Subsonic version 2.9. + * + * @author Sindre Mehus + */ +public class Schema29 extends Schema { + private static final Logger LOG = Logger.getLogger(Schema29.class); + + public void execute(JdbcTemplate template) { + + if (template.queryForInt("select count(*) from version where version = 5") == 0) { + LOG.info("Updating database schema to version 5."); + template.execute("insert into version values (5)"); + } + + if (!tableExists(template, "user_rating")) { + LOG.info("Database table 'user_rating' not found. Creating it."); + template.execute("create table user_rating (" + + "username varchar not null," + + "path varchar not null," + + "rating double not null," + + "primary key (username, path)," + + "foreign key (username) references user(username) on delete cascade)"); + LOG.info("Database table 'user_rating' was created successfully."); + + template.execute("insert into user_rating select 'admin', path, rating from music_file_info " + + "where rating is not null and rating > 0"); + LOG.info("Migrated data from 'music_file_info' to 'user_rating'."); + } + } +} diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema30.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema30.java new file mode 100644 index 00000000..cdea199b --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema30.java @@ -0,0 +1,56 @@ +/* + 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.*; +import net.sourceforge.subsonic.domain.TranscodeScheme; +import org.springframework.jdbc.core.*; + +/** + * Used for creating and evolving the database schema. + * This class implementes the database schema for Subsonic version 3.0. + * + * @author Sindre Mehus + */ +public class Schema30 extends Schema { + private static final Logger LOG = Logger.getLogger(Schema30.class); + + public void execute(JdbcTemplate template) { + + if (template.queryForInt("select count(*) from version where version = 6") == 0) { + LOG.info("Updating database schema to version 6."); + template.execute("insert into version values (6)"); + } + + if (!columnExists(template, "last_fm_enabled", "user_settings")) { + LOG.info("Database columns 'user_settings.last_fm_*' not found. Creating them."); + template.execute("alter table user_settings add last_fm_enabled boolean default false not null"); + template.execute("alter table user_settings add last_fm_username varchar null"); + template.execute("alter table user_settings add last_fm_password varchar null"); + LOG.info("Database columns 'user_settings.last_fm_*' were added successfully."); + } + + if (!columnExists(template, "transcode_scheme", "user_settings")) { + LOG.info("Database column 'user_settings.transcode_scheme' not found. Creating it."); + template.execute("alter table user_settings add transcode_scheme varchar default '" + + TranscodeScheme.OFF.name() + "' not null"); + LOG.info("Database column 'user_settings.transcode_scheme' was added successfully."); + } + } +}
\ No newline at end of file diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema31.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema31.java new file mode 100644 index 00000000..00fb0c87 --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema31.java @@ -0,0 +1,52 @@ +/* + 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 implementes the database schema for Subsonic version 3.1. + * + * @author Sindre Mehus + */ +public class Schema31 extends Schema { + private static final Logger LOG = Logger.getLogger(Schema31.class); + + public void execute(JdbcTemplate template) { + + if (template.queryForInt("select count(*) from version where version = 7") == 0) { + LOG.info("Updating database schema to version 7."); + template.execute("insert into version values (7)"); + } + + if (!columnExists(template, "enabled", "music_file_info")) { + LOG.info("Database column 'music_file_info.enabled' not found. Creating it."); + template.execute("alter table music_file_info add enabled boolean default true not null"); + LOG.info("Database column 'music_file_info.enabled' was added successfully."); + } + + if (!columnExists(template, "default_active", "transcoding")) { + LOG.info("Database column 'transcoding.default_active' not found. Creating it."); + template.execute("alter table transcoding add default_active boolean default true not null"); + LOG.info("Database column 'transcoding.default_active' was added successfully."); + } + } +} diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema32.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema32.java new file mode 100644 index 00000000..a1439bb0 --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema32.java @@ -0,0 +1,93 @@ +/* + 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 implementes the database schema for Subsonic version 3.2. + * + * @author Sindre Mehus + */ +public class Schema32 extends Schema { + private static final Logger LOG = Logger.getLogger(Schema32.class); + + public void execute(JdbcTemplate template) { + + if (template.queryForInt("select count(*) from version where version = 8") == 0) { + LOG.info("Updating database schema to version 8."); + template.execute("insert into version values (8)"); + } + + if (!columnExists(template, "show_now_playing", "user_settings")) { + LOG.info("Database column 'user_settings.show_now_playing' not found. Creating it."); + template.execute("alter table user_settings add show_now_playing boolean default true not null"); + LOG.info("Database column 'user_settings.show_now_playing' was added successfully."); + } + + if (!columnExists(template, "selected_music_folder_id", "user_settings")) { + LOG.info("Database column 'user_settings.selected_music_folder_id' not found. Creating it."); + template.execute("alter table user_settings add selected_music_folder_id int default -1 not null"); + LOG.info("Database column 'user_settings.selected_music_folder_id' was added successfully."); + } + + if (!tableExists(template, "podcast_channel")) { + LOG.info("Database table 'podcast_channel' not found. Creating it."); + template.execute("create table podcast_channel (" + + "id identity," + + "url varchar not null," + + "title varchar," + + "description varchar," + + "status varchar not null," + + "error_message varchar)"); + LOG.info("Database table 'podcast_channel' was created successfully."); + } + + if (!tableExists(template, "podcast_episode")) { + LOG.info("Database table 'podcast_episode' not found. Creating it."); + template.execute("create table podcast_episode (" + + "id identity," + + "channel_id int not null," + + "url varchar not null," + + "path varchar," + + "title varchar," + + "description varchar," + + "publish_date datetime," + + "duration varchar," + + "bytes_total bigint," + + "bytes_downloaded bigint," + + "status varchar not null," + + "error_message varchar," + + "foreign key (channel_id) references podcast_channel(id) on delete cascade)"); + LOG.info("Database table 'podcast_episode' was created successfully."); + } + + if (template.queryForInt("select count(*) from role where id = 7") == 0) { + LOG.info("Role 'podcast' not found in database. Creating it."); + template.execute("insert into role values (7, 'podcast')"); + template.execute("insert into user_role " + + "select distinct u.username, 7 from user u, user_role ur " + + "where u.username = ur.username and ur.role_id = 1"); + LOG.info("Role 'podcast' was created successfully."); + } + + } +} diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema33.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema33.java new file mode 100644 index 00000000..6f754306 --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema33.java @@ -0,0 +1,47 @@ +/* + 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 implementes the database schema for Subsonic version 3.3. + * + * @author Sindre Mehus + */ +public class Schema33 extends Schema { + + private static final Logger LOG = Logger.getLogger(Schema33.class); + + public void execute(JdbcTemplate template) { + + if (template.queryForInt("select count(*) from version where version = 9") == 0) { + LOG.info("Updating database schema to version 9."); + template.execute("insert into version values (9)"); + } + + if (!columnExists(template, "client_side_playlist", "player")) { + LOG.info("Database column 'player.client_side_playlist' not found. Creating it."); + template.execute("alter table player add client_side_playlist boolean default false not null"); + LOG.info("Database column 'player.client_side_playlist' was added successfully."); + } + } +} diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema34.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema34.java new file mode 100644 index 00000000..daaf98ca --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema34.java @@ -0,0 +1,53 @@ +/* + 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 implementes the database schema for Subsonic version 3.4. + * + * @author Sindre Mehus + */ +public class Schema34 extends Schema { + + private static final Logger LOG = Logger.getLogger(Schema34.class); + + public void execute(JdbcTemplate template) { + + if (template.queryForInt("select count(*) from version where version = 10") == 0) { + LOG.info("Updating database schema to version 10."); + template.execute("insert into version values (10)"); + } + + if (!columnExists(template, "ldap_authenticated", "user")) { + LOG.info("Database column 'user.ldap_authenticated' not found. Creating it."); + template.execute("alter table user add ldap_authenticated boolean default false not null"); + LOG.info("Database column 'user.ldap_authenticated' was added successfully."); + } + + if (!columnExists(template, "party_mode_enabled", "user_settings")) { + LOG.info("Database column 'user_settings.party_mode_enabled' not found. Creating it."); + template.execute("alter table user_settings add party_mode_enabled boolean default false not null"); + LOG.info("Database column 'user_settings.party_mode_enabled' was added successfully."); + } + } +}
\ No newline at end of file diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema35.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema35.java new file mode 100644 index 00000000..56b5073d --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema35.java @@ -0,0 +1,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); + } + } + } +}
\ No newline at end of file diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema36.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema36.java new file mode 100644 index 00000000..caed6cdb --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema36.java @@ -0,0 +1,48 @@ +/* + 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 implementes the database schema for Subsonic version 3.6. + * + * @author Sindre Mehus + */ +public class Schema36 extends Schema { + + private static final Logger LOG = Logger.getLogger(Schema36.class); + + @Override + public void execute(JdbcTemplate template) { + + if (template.queryForInt("select count(*) from version where version = 12") == 0) { + LOG.info("Updating database schema to version 12."); + template.execute("insert into version values (12)"); + } + + if (!columnExists(template, "technology", "player")) { + LOG.info("Database column 'player.technology' not found. Creating it."); + template.execute("alter table player add technology varchar default 'WEB' not null"); + LOG.info("Database column 'player.technology' was added successfully."); + } + } +}
\ No newline at end of file diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema37.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema37.java new file mode 100644 index 00000000..afb8fb6e --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema37.java @@ -0,0 +1,77 @@ +/* + 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 3.7. + * + * @author Sindre Mehus + */ +public class Schema37 extends Schema { + + private static final Logger LOG = Logger.getLogger(Schema37.class); + + @Override + public void execute(JdbcTemplate template) { + + if (template.queryForInt("select count(*) from version where version = 13") == 0) { + LOG.info("Updating database schema to version 13."); + template.execute("insert into version values (13)"); + } + + if (template.queryForInt("select count(*) from role where id = 9") == 0) { + LOG.info("Role 'settings' not found in database. Creating it."); + template.execute("insert into role values (9, 'settings')"); + template.execute("insert into user_role select distinct u.username, 9 from user u"); + LOG.info("Role 'settings' was created successfully."); + } + + if (template.queryForInt("select count(*) from role where id = 10") == 0) { + LOG.info("Role 'jukebox' not found in database. Creating it."); + template.execute("insert into role values (10, 'jukebox')"); + template.execute("insert into user_role " + + "select distinct u.username, 10 from user u, user_role ur " + + "where u.username = ur.username and ur.role_id = 1"); + LOG.info("Role 'jukebox' was created successfully."); + } + + if (!columnExists(template, "changed", "music_folder")) { + LOG.info("Database column 'music_folder.changed' not found. Creating it."); + template.execute("alter table music_folder add changed datetime default 0 not null"); + LOG.info("Database column 'music_folder.changed' was added successfully."); + } + + if (!columnExists(template, "changed", "internet_radio")) { + LOG.info("Database column 'internet_radio.changed' not found. Creating it."); + template.execute("alter table internet_radio add changed datetime default 0 not null"); + LOG.info("Database column 'internet_radio.changed' was added successfully."); + } + + if (!columnExists(template, "changed", "user_settings")) { + LOG.info("Database column 'user_settings.changed' not found. Creating it."); + template.execute("alter table user_settings add changed datetime default 0 not null"); + LOG.info("Database column 'user_settings.changed' was added successfully."); + } + + } +}
\ No newline at end of file diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema38.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema38.java new file mode 100644 index 00000000..fac49511 --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema38.java @@ -0,0 +1,54 @@ +/* + 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 3.8. + * + * @author Sindre Mehus + */ +public class Schema38 extends Schema { + + private static final Logger LOG = Logger.getLogger(Schema38.class); + + @Override + public void execute(JdbcTemplate template) { + + if (template.queryForInt("select count(*) from version where version = 14") == 0) { + LOG.info("Updating database schema to version 14."); + template.execute("insert into version values (14)"); + } + + if (!columnExists(template, "client_id", "player")) { + LOG.info("Database column 'player.client_id' not found. Creating it."); + template.execute("alter table player add client_id varchar"); + LOG.info("Database column 'player.client_id' was added successfully."); + } + + if (!columnExists(template, "show_chat", "user_settings")) { + LOG.info("Database column 'user_settings.show_chat' not found. Creating it."); + template.execute("alter table user_settings add show_chat boolean default true not null"); + LOG.info("Database column 'user_settings.show_chat' was added successfully."); + } + } +}
\ No newline at end of file diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema40.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema40.java new file mode 100644 index 00000000..e01d1ef0 --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema40.java @@ -0,0 +1,46 @@ +/* + 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.0. + * + * @author Sindre Mehus + */ +public class Schema40 extends Schema { + + private static final Logger LOG = Logger.getLogger(Schema40.class); + + @Override + public void execute(JdbcTemplate template) { + + if (template.queryForInt("select count(*) from version where version = 15") == 0) { + LOG.info("Updating database schema to version 15."); + template.execute("insert into version values (15)"); + + // Reset stream byte count since they have been wrong in earlier releases. + template.execute("update user set bytes_streamed = 0"); + LOG.info("Reset stream byte count statistics."); + } + } +}
\ No newline at end of file diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema43.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema43.java new file mode 100644 index 00000000..cba1572c --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema43.java @@ -0,0 +1,65 @@ +/* + 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 org.springframework.jdbc.core.JdbcTemplate; + +import net.sourceforge.subsonic.Logger; + +import java.util.Arrays; + +/** + * Used for creating and evolving the database schema. + * This class implements the database schema for Subsonic version 4.3. + * + * @author Sindre Mehus + */ +public class Schema43 extends Schema { + + private static final Logger LOG = Logger.getLogger(Schema43.class); + + @Override + public void execute(JdbcTemplate template) { + + // version 16 was used for 4.3.beta1 + if (template.queryForInt("select count(*) from version where version = 16") == 0) { + LOG.info("Updating database schema to version 16."); + template.execute("insert into version values (16)"); + } + + if (template.queryForInt("select count(*) from version where version = 17") == 0) { + LOG.info("Updating database schema to version 17."); + template.execute("insert into version values (17)"); + + for (String format : Arrays.asList("avi", "mpg", "mpeg", "mp4", "m4v", "mkv", "mov", "wmv", "ogv")) { + template.update("delete from transcoding where source_format=? and target_format=?", new Object[] {format, "flv"}); + template.execute("insert into transcoding values(null,'" + format + " > flv' ,'" + format + "' ,'flv','ffmpeg -ss %o -i %s -async 1 -b %bk -s %wx%h -ar 44100 -ac 2 -v 0 -f flv -',null,null,true,true)"); + template.execute("insert into player_transcoding select p.id as player_id, t.id as transaction_id from player p, transcoding t where t.name = '" + format + " > flv'"); + } + LOG.info("Created video transcoding configuration."); + } + + if (!columnExists(template, "email", "user")) { + LOG.info("Database column 'user.email' not found. Creating it."); + template.execute("alter table user add email varchar"); + LOG.info("Database column 'user.email' was added successfully."); + } + + } +}
\ No newline at end of file diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema45.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema45.java new file mode 100644 index 00000000..d82f2a92 --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema45.java @@ -0,0 +1,76 @@ +/* + 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.5. + * + * @author Sindre Mehus + */ +public class Schema45 extends Schema { + + private static final Logger LOG = Logger.getLogger(Schema45.class); + + @Override + public void execute(JdbcTemplate template) { + + if (template.queryForInt("select count(*) from version where version = 18") == 0) { + LOG.info("Updating database schema to version 18."); + template.execute("insert into version values (18)"); + } + + if (template.queryForInt("select count(*) from role where id = 11") == 0) { + LOG.info("Role 'share' not found in database. Creating it."); + template.execute("insert into role values (11, 'share')"); + template.execute("insert into user_role " + + "select distinct u.username, 11 from user u, user_role ur " + + "where u.username = ur.username and ur.role_id = 1"); + LOG.info("Role 'share' was created successfully."); + } + + if (!tableExists(template, "share")) { + LOG.info("Table 'share' not found in database. Creating it."); + template.execute("create cached table share (" + + "id identity," + + "name varchar not null," + + "description varchar," + + "username varchar not null," + + "created datetime not null," + + "expires datetime," + + "last_visited datetime," + + "visit_count int default 0 not null," + + "unique (name)," + + "foreign key (username) references user(username) on delete cascade)"); + template.execute("create index idx_share_name on share(name)"); + + LOG.info("Table 'share' was created successfully."); + LOG.info("Table 'share_file' not found in database. Creating it."); + template.execute("create cached table share_file (" + + "id identity," + + "share_id int not null," + + "path varchar not null," + + "foreign key (share_id) references share(id) on delete cascade)"); + LOG.info("Table 'share_file' was created successfully."); + } + } +}
\ No newline at end of file diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema46.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema46.java new file mode 100644 index 00000000..c1fcf357 --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema46.java @@ -0,0 +1,87 @@ +/* + 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 org.springframework.jdbc.core.JdbcTemplate; + +import net.sourceforge.subsonic.Logger; + +/** + * Used for creating and evolving the database schema. + * This class implements the database schema for Subsonic version 4.6. + * + * @author Sindre Mehus + */ +public class Schema46 extends Schema { + + private static final Logger LOG = Logger.getLogger(Schema46.class); + + @Override + public void execute(JdbcTemplate template) { + + if (template.queryForInt("select count(*) from version where version = 19") == 0) { + LOG.info("Updating database schema to version 19."); + template.execute("insert into version values (19)"); + } + + if (!tableExists(template, "transcoding2")) { + LOG.info("Database table 'transcoding2' not found. Creating it."); + template.execute("create table transcoding2 (" + + "id identity," + + "name varchar not null," + + "source_formats varchar not null," + + "target_format varchar not null," + + "step1 varchar not null," + + "step2 varchar," + + "step3 varchar)"); + + template.execute("insert into transcoding2 values(null,'mp3 audio'," + + "'ogg oga aac m4a flac wav wma aif aiff ape mpc shn', 'mp3', " + + "'ffmpeg -i %s -ab %bk -v 0 -f mp3 -', null, null)"); + + template.execute("insert into transcoding2 values(null,'flv/h264 video', " + + "'avi mpg mpeg mp4 m4v mkv mov wmv ogv divx m2ts', 'flv', " + + "'ffmpeg -ss %o -i %s -async 1 -b %bk -s %wx%h -ar 44100 -ac 2 -v 0 -f flv -vcodec libx264 -preset superfast -threads 0 -', null, null)"); + + LOG.info("Database table 'transcoding2' was created successfully."); + } + + if (!tableExists(template, "player_transcoding2")) { + LOG.info("Database table 'player_transcoding2' not found. Creating it."); + template.execute("create table player_transcoding2 (" + + "player_id int not null," + + "transcoding_id int not null," + + "primary key (player_id, transcoding_id)," + + "foreign key (player_id) references player(id) on delete cascade," + + "foreign key (transcoding_id) references transcoding2(id) on delete cascade)"); + + template.execute("insert into player_transcoding2(player_id, transcoding_id) " + + "select distinct p.id, t.id from player p, transcoding2 t"); + + LOG.info("Database table 'player_transcoding2' was created successfully."); + } + + if (!columnExists(template, "default_active", "transcoding2")) { + LOG.info("Database column 'transcoding2.default_active' not found. Creating it."); + template.execute("alter table transcoding2 add default_active boolean default true not null"); + LOG.info("Database column 'transcoding2.default_active' was added successfully."); + } + } + +}
\ No newline at end of file diff --git a/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema47.java b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema47.java new file mode 100644 index 00000000..8b290b47 --- /dev/null +++ b/subsonic-main/src/main/java/net/sourceforge/subsonic/dao/schema/Schema47.java @@ -0,0 +1,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."); + } + } +}
\ No newline at end of file |