Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p><em>Update:</em> Here's the requirements that I followed, spelled out:</p> <ul> <li>No matter how many songs there are in the table or the current location in a playlist, the information the client is required to store (e.g. in a cookie) must have a constant size.</li> <li>Using 'next' 100 times followed by 'prev' 100 times should result in some sequence of 100 songs (possibly including duplicates) followed by that exact sequence reversed, regardless of any insertions that may have been made inbetween any 'next' or 'prev' action. ("Previous song always means the previous song.")</li> <li>A song that would have been in a playlist "when it was generated/initialized" and has since been deleted will be skipped. <ul> <li>Would not be hard to change my answer below to return a not-found indicator instead.</li> </ul></li> </ul> <hr> <p>I think you need one more piece of information: a secondary song key of an integer position, in addition to your GUID (or you could replace it with this). Then, combined with a <a href="http://en.wikipedia.org/wiki/PRNG" rel="nofollow noreferrer">PRNG</a>, you can do the most simple and quick lookup possible. Pseudocode:</p> <pre><code>def next_song(initial_seed, current_prng_index, high_song_index): """Returns the next song and the parameters to be later passed to next/prev_song.""" while True: current_prng_index += 1 current_seed = PRNG_advance(initial_seed, current_prng_index) song_index = song_index_from_seed(current_seed, high_song_index) song_id = (SELECT SongID FROM Songs WHERE SongIndex=song_index) if song_id: # test, somehow, for non-deleted songs return song_id, (initial_seed, current_prng_index, high_song_index) # include values that must be passed on the next call # prev is the same, except uses -= 1 def song_index_from_seed(seed, highest): return seed % (highest + 1) # simple, but better possibilities might exist for your data def new_playlist(): """Returns the parameters to be passed to next/prev_song.""" high_song_index = (SELECT MAX(SongIndex) FROM Songs) return get_a_random_number(), 0, high_song_index </code></pre> <p>This will get progressively slower on each next song and doesn't allow wrapping by going backwards (without some creativity). If you find a suitable reversible PRNG (though uncommon among good PRNGs, AFAIK):</p> <pre><code>def next_song(current_seed, high_song_index): while True: current_seed = PRNG_next(current_seed) song_index = song_index_from_seed(current_seed, high_song_index) song_id = (SELECT SongID FROM Songs WHERE SongIndex=song_index) if song_id: # test, somehow, for non-deleted songs return song_id, (current_seed, high_song_index) # include values that must be passed on the next call # prev is the same, except uses PRNG_prev </code></pre> <p>This handles deletions by skipping those songs (or if you never delete that's not even an issue), but otherwise doesn't change the order no matter how many are deleted. Insertions are handled by the song_index_from_seed function, by constraining the index so new songs are never 'seen'. This is also an infinite loop if all the available songs have been deleted, but I think this code handles all other corner cases.</p> <p>(Refactoring the next/prev versions to be simple wrappers around a common function isn't hard, but left out here to increase clarity.)</p> <p>I've effectively replaced your dateAdded with my position index, but this is an improvement, since you don't need to keep deleted rows as dummies (but still cannot reuse their index) as you would if the position index was computed by ordering on dateAdded.</p> <p>Using a PRNG like this seems naive, but works; and you'll have to be careful that the chosen PRNG and song_index_from_seed combination behaves as you expect: it's possible that some initial seeds appear to generate "non-random" playlists. (Not because they're not random, but because listeners expect a mix of songs instead of just <a href="http://xkcd.com/221/" rel="nofollow noreferrer">4, 4</a>, <a href="http://web.archive.org/web/20011027002011/http://dilbert.com/comics/dilbert/archive/images/dilbert2001182781025.gif" rel="nofollow noreferrer">9, 9</a>, ...)</p>
    singulars
    1. This table or related slice is empty.
    1. POCreate a randomly ordered playlist from a table of songs and return the name of the current, next, and previous song?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POCreate a randomly ordered playlist from a table of songs and return the name of the current, next, and previous song?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload