/**
* Finds and returns playlists that match some specified sql where clause
*
* @param db
* @param limit
* @param offset
* @param whereSql
* @return
* @throws SQLException
*/
protected static List<Playlist> findPlaylistsForSql(
final Database db, final int limit, final int offset, final String whereSql)
throws SQLException {
PreparedStatement st = null;
ResultSet rs = null;
try {
final List<Playlist> lists = new ArrayList<Playlist>();
String sql = getSelectFromSql() + whereSql + " order by p.id desc ";
if (limit != -1) {
sql += " limit " + limit + " offset " + offset;
}
st = db.prepare(sql);
rs = st.executeQuery();
while (rs.next()) {
lists.add(createFromResultSet(rs));
}
return lists;
} finally {
Utils.close(rs);
Utils.close(st);
}
}
/**
* this action allows you to give the path of a track on disk, and it will be resolved to the
* tracks internal ID. this can then be used normally for playing music. the path coming in is
* assumed to have forward slashes to delimit path components, but this needs to be converted to
* whatever the actual path separator is for the current system BEFORE we try and query the
* database, otherwise, well, it just won't work.
*
* <p>NB! ATM, this feature is only here for the the folder browsing stuff, so if that's not
* turned on this this won't work.
*/
protected void resolvePath() throws BadRequestException, SQLException, IOException {
// check folder browsing is enabled
Utils.checkFeatureEnabled(getProperties(), "browse.folders.enabled");
ResultSet rs = null;
PreparedStatement st = null;
try {
final Database db = getDatabase();
final Locale locale = getLocale();
final Request req = getRequest();
final String path = convertPath(req.getArgument("path"));
final String sql = Track.getSelectFromSql() + " where t.path = ? ";
st = db.prepare(sql);
st.setString(1, path);
rs = st.executeQuery();
if (!rs.next())
throw new BadRequestException(locale.getString("www.error.trackNotFound"), 404);
final Track track = Track.createFromResultSet(rs);
final TResolvePath tpl = new TResolvePath();
tpl.setTrack(track);
getResponse().showJson(tpl.makeRenderer());
} finally {
Utils.close(rs);
Utils.close(st);
}
}
public void testGetLatestAlbums() throws SQLException {
final Properties p = createMock(Properties.class);
expect(p.get(Constants.WWW_BROWSE_LATEST_ALBUMS_COUNT, 10)).andReturn(10l);
replay(p);
final ResultSet rs = createNiceMock(ResultSet.class);
expect(rs.next()).andReturn(true);
expect(rs.next()).andReturn(true);
expect(rs.next()).andReturn(false);
replay(rs);
final PreparedStatement st = createMock(PreparedStatement.class);
st.setInt(1, 10);
expect(st.executeQuery()).andReturn(rs).times(1);
replay(st);
final Database db = createMock(Database.class);
expect(db.prepare((String) anyObject())).andReturn(st).times(1);
replay(db);
final Latester b = new Latester();
b.setProperties(p);
b.setDatabase(db);
final Vector<Album> albums = b.getLatestAlbums();
assertNotNull(albums);
assertEquals(2, albums.size());
verify(db);
verify(st);
verify(rs);
verify(p);
}
/**
* this method extracts the full path in the request where the relative path after the json action
* name is prefixed by the collection path specified in the query string.
*
* <p>e.g. /json/action/File/System/Path?collectionId=2
*
* <p>Will return /home/rod/File/System/Path because the collection with id = 2 is rooted at
* /home/rod
*
* @return String
*/
private String getPathFromRequest() throws SQLException, BadRequestException {
final Request req = getRequest();
final Locale locale = getLocale();
final int collectionId = Integer.parseInt(req.getArgument("collectionId"));
String path = "";
ResultSet rs = null;
PreparedStatement st = null;
for (int i = 2; i < req.getParamCount(); i++) {
final String pathElement = req.getUrlParam(i);
// don't allow going up directories
if (!pathElement.equals("..")) path += "/" + req.getUrlParam(i);
}
try {
final Database db = getDatabase();
final String sql = " select c.path " + " from collection c " + " where c.id = ? ";
st = db.prepare(sql);
st.setInt(1, collectionId);
rs = st.executeQuery();
// check the collection exists and we got it's root path
if (rs.next()) {
// we need to trim the trailing slash off the collection path
final String collPath = rs.getString("path");
path = collPath.substring(0, collPath.length() - 1) + path;
} else throw new BadRequestException(locale.getString("www.error.invalidCollectionId"), 404);
path = path.replaceAll("\\/\\/", "\\/");
} finally {
Utils.close(rs);
Utils.close(st);
}
log.debug("pathFromRequest: " + path);
return path;
}
/**
* fetches an album by id, if it's not found then a BadRequestException is thrown
*
* @param id
* @return
* @throws java.sql.SQLException
* @throws com.pugh.sockso.web.BadRequestException
*/
protected Album getAlbum(final int id) throws SQLException, BadRequestException {
ResultSet rs = null;
PreparedStatement st = null;
try {
final Database db = getDatabase();
final String sql =
" select ar.id as artistId, ar.name as artistName, "
+ " al.id as albumId, al.name as albumName, "
+ " al.date_added, ( "
+ " select count(*) "
+ " from play_log l "
+ " inner join tracks t "
+ " on t.id = l.track_id "
+ " where t.album_id = al.id "
+ " ) as playCount "
+ " from albums al "
+ " inner join artists ar "
+ " on ar.id = al.artist_id "
+ " where al.id = ? "
+ " limit 1 ";
st = db.prepare(sql);
st.setInt(1, id);
rs = st.executeQuery();
if (!rs.next()) throw new BadRequestException("album not found", 404);
return new Album(
new Artist(rs.getInt("artistId"), rs.getString("artistName")),
rs.getInt("albumId"),
rs.getString("albumName"),
rs.getDate("date_added"),
-1,
rs.getInt("playCount"));
} finally {
Utils.close(rs);
Utils.close(st);
}
}
/**
* fetches the tracks from an album
*
* @param albumId
* @return
* @throws java.sql.SQLException
*/
protected Vector<Track> getAlbumTracks(final int albumId) throws SQLException {
ResultSet rs = null;
PreparedStatement st = null;
try {
final Database db = getDatabase();
final String sql =
Track.getSelectFromSql() + " where t.album_id = ? " + " order by t.track_no asc ";
st = db.prepare(sql);
st.setInt(1, albumId);
rs = st.executeQuery();
return Track.createVectorFromResultSet(rs);
} finally {
Utils.close(rs);
Utils.close(st);
}
}
/**
* tries to delete a users playlist. needs to check things like did they create it, etc... if all
* goes ok then sends back the ID so that the javascript handler can do whatever...
*
* @throws BadRequestException
* @throws SQLException
* @throws IOException
*/
protected void deletePlaylist() throws BadRequestException, SQLException, IOException {
final Request req = getRequest();
final User user = getUser();
final Locale locale = getLocale();
if (user == null)
throw new BadRequestException(locale.getString("www.json.error.notLoggedIn"), 403);
final Database db = getDatabase();
final int id = Integer.parseInt(req.getUrlParam(2));
final String sql =
" select 1 " + " from playlists p " + " where p.id = ? " + " and p.user_id = ? ";
ResultSet rs = null;
PreparedStatement st = null;
try {
// check user owns playlist before deleting it
st = db.prepare(sql);
st.setInt(1, id);
st.setInt(2, user.getId());
rs = st.executeQuery();
if (!rs.next()) throw new BadRequestException("You don't own that playlist", 403);
cm.removePlaylist(id);
// done, send success response
final TString tpl = new TString();
tpl.setResult(Integer.toString(id));
getResponse().showJson(tpl.makeRenderer());
} finally {
Utils.close(rs);
Utils.close(st);
}
}
/**
* Finds a playlist by id, or returns null if it doesn't exist
*
* @param db
* @param id
* @return
*/
public static Playlist find(final Database db, final int id) throws SQLException {
PreparedStatement st = null;
ResultSet rs = null;
try {
final String sql = getSelectFromSql() + " where p.id = ? ";
st = db.prepare(sql);
st.setInt(1, id);
rs = st.executeQuery();
if (rs.next()) {
return createFromResultSet(rs);
}
} finally {
Utils.close(st);
Utils.close(rs);
}
return null;
}