Пример #1
0
  /**
   * 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);
    }
  }
Пример #2
0
  /**
   * 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);
    }
  }
Пример #3
0
  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);
  }
Пример #4
0
  /**
   * 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;
  }
Пример #5
0
  /**
   * 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);
    }
  }
Пример #6
0
  /**
   * 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);
    }
  }
Пример #7
0
  /**
   * 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);
    }
  }
Пример #8
0
  /**
   * 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;
  }