Allow creating playlists via SQLite DB queries

[jackandjohn 11]

Allow a playlist to be populated by a database query, restrict queries to admins only (only show it in the admin panel?), then expose the query field to users.

Without code that interprets or sanity-checks the query, there's less for the devs to do, and full power for those users that know how to use it (or are willing to learn)

 

 

Here's an example:

SELECT PremiereDate, Id  FROM MediaItems
  WHERE SeriesName LIKE '%Star%Trek%' AND type IS '8' AND Album LIKE 'Season%' AND PremiereDate IS NOT NULL
  GROUP BY PremiereDate
UNION
SELECT PremiereDate, Id
  FROM MediaItems
  WHERE Name LIKE '%Star%Trek%' AND type IS '5' AND OfficialRating IS NOT NULL
  GROUP BY PremiereDate
ORDER BY PremiereDate ASC

This would create a playlist of all Star Trek movies and Episodes, sorted by date (it's a little rough, but it worked perfect for me)

With a "refresh" button, it would only run the query when the user calls it so would not have to be part of any resource-hogging background process.

 

 

 

It probably seems scary from the perspective of Emby Staff, but we're all grownups here.

Put a disclaimer before "allowing us to enable it" if you like.

[jackandjohn 11]

For reference in case anyone wants to try something similar to what I did today (YMMV, Swim At Your Own Risk, No Lifeguard On Duty)

# Prerequisites:
## sqlite3 gem (`gem install sqlite3`)
## An already-created playlist with a single item
## The Emby server shut down from within the interface
## The database edited to delete the `ListItems` entry for the playlist item (leaving an empty playlist)

require "sqlite3"
db = SQLite3::Database.new "./config/data/library.db"

$NEXTLISTIDNUMBER = 20 # Find this by checking for the last number shown in the `ListItems` table
PLAYLISTIDTOPOPULATE = 40198 # Find this in the `MediaItems` table. `Name` matches the name of the playlist

trekQuery = p %{
SELECT PremiereDate, Id
  FROM MediaItems
  WHERE SeriesName LIKE '%Star%Trek%' AND type IS '8' AND Album LIKE 'Season%' AND PremiereDate IS NOT NULL
  GROUP BY PremiereDate
UNION
SELECT PremiereDate, Id
  FROM MediaItems
  WHERE Name LIKE '%Star%Trek%' AND type IS '5' AND OfficialRating IS NOT NULL
  GROUP BY PremiereDate
ORDER BY PremiereDate ASC
}

trekResultRows = db.execute(trekQuery)

trekResultRows.each_with_index do |row, index|
  db.execute("INSERT INTO ListItems (ListItemEntryId, ListId, ListItemId, ListItemOrder) VALUES (?, ?, ?, ?)", [NEXTLISTIDNUMBER.to_i, PLAYLISTIDTOPOPULATE.to_i, row[1].to_i, index])
  $NEXTLISTIDNUMBER = $NEXTLISTIDNUMBER + 1
end

Edited July 1, 2019 by jackandjohn

[Luke 40114]

Hi, that's very neat. Realistically this is not going to happen in our user interface, but smart playlists are planned for the future. thanks !