Media List Editor Examples

  Thread Rating:
  • 1 Vote(s) - 5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Post Reply
DanCooper Offline
Moderator
Posts: 3,412
Joined: Apr 2012
Reputation: 168
Location: Switzerland
Post: #16
(2016-04-24 03:26)Daydream Wrote:  EMM does not store the resolution of art files (poster, thumbs, fanart, etc), correct?
Nope, but that's a good idea for a new feature ;-)
find quote
Fail$tyle420 Offline
Posting Freak
Posts: 1,213
Joined: Jul 2014
Reputation: 44
Post: #17
Would this work for hevc on tvshows? :
Code:
SELECT DISTINCT
  tvshowlist.*
FROM
  tvshowlist
  INNER JOIN TVShowVStreams ON (tvshowlist.idTVShow = TVShowVStreams.TVShowID)
WHERE
  TVShowVStreams.Video_Codec = 'hevc'

*EDIT* - Just got home and tried this out, no go. I replaced tvshow with episode for everything except first 2 tvshowlists, still nadda. What am I missing??
(This post was last modified: 2016-04-28 02:23 by Fail$tyle420.)
find quote
Daydream Offline
Junior Member
Posts: 26
Joined: Jul 2009
Reputation: 0
Location: New York
Exclamation  RE: Media List Editor Examples
Post: #18
Well, I'm not an expert but for starters there is no TVShowVStreams table in the database (if you just replaced words in the syntax of a previous query, it doesn't work like that; get SQLiteSpy and look at the database, the MyVideos37.emm file or whatever it is). Further there's some description here http://embermediamanager.org/databasemodel/index.html for what's what.

I'd like to understand better too what can we do with episodes. If a movie is a standalone element and everything can be referred to it, an episode is part of a season, part of a series, and displaying a selection works in a more complex way. A proper query should show only the series, with only the seasons, with only the episodes that respect a certain condition. I can't get it to work like that right now.

As it stands there is no direct connection (that I can see) between TVVStream (the table I believe you were actually going for), and the tvshowlist view. We could double join with the episode table but I get weird results. Somebody that is stronger with the Force (*cough SQL*) should show us Smile.
find quote
DanCooper Offline
Moderator
Posts: 3,412
Joined: Apr 2012
Reputation: 168
Location: Switzerland
Post: #19
That's not possible with episodes. There is no "episodes only" list that we can display to list all episodes by a query.
I think we have to change the tv show view/list to make it possible to use custom season/episode filters.
find quote
Fail$tyle420 Offline
Posting Freak
Posts: 1,213
Joined: Jul 2014
Reputation: 44
Post: #20
(2016-04-28 03:50)Daydream Wrote:  Well, I'm not an expert but for starters there is no TVShowVStreams table in the database (if you just replaced words in the syntax of a previous query, it doesn't work like that; get SQLiteSpy and look at the database, the MyVideos37.emm file or whatever it is). Further there's some description here http://embermediamanager.org/databasemodel/index.html for what's what.

I'd like to understand better too what can we do with episodes. If a movie is a standalone element and everything can be referred to it, an episode is part of a season, part of a series, and displaying a selection works in a more complex way. A proper query should show only the series, with only the seasons, with only the episodes that respect a certain condition. I can't get it to work like that right now.

As it stands there is no direct connection (that I can see) between TVVStream (the table I believe you were actually going for), and the tvshowlist view. We could double join with the episode table but I get weird results. Somebody that is stronger with the Force (*cough SQL*) should show us Smile.
Yeah, I have no idea what I'm doing (if it wasn't obvious already). Thank you for the link, now I actually have something to work with lol! Thank you for trying!

(2016-04-28 09:42)DanCooper Wrote:  That's not possible with episodes. There is no "episodes only" list that we can display to list all episodes by a query.
I think we have to change the tv show view/list to make it possible to use custom season/episode filters.

Ok, so I didn't know what the hell I was doing AND it's not possible Tongue Thank you for the info!
find quote
DanCooper Offline
Moderator
Posts: 3,412
Joined: Apr 2012
Reputation: 168
Location: Switzerland
Post: #21
(2016-04-28 19:42)Fail$tyle420 Wrote:  Ok, so I didn't know what the hell I was doing AND it's not possible Tongue Thank you for the info!

It's not possible AND your SQL statement is wrong TongueBig Grin
find quote
brunosso Offline
Member
Posts: 62
Joined: May 2014
Reputation: 1
Location: Italy
Post: #22
Dan is possible to create a list of all tvshows is not "Locked" Confused or with the status "Continuing" and not "Ended"

I'm not in danger Skyler, I'm the danger! A guy opens his door and get shot, and you think that's me? No, I'M THE ONE WHO KNOCKS!!

Ember Media Manager lover...
(This post was last modified: 2016-05-10 13:08 by brunosso.)
find quote
DanCooper Offline
Moderator
Posts: 3,412
Joined: Apr 2012
Reputation: 168
Location: Switzerland
Post: #23
(2016-05-10 13:08)brunosso Wrote:  Dan is possible to create a list of all tvshows is not "Locked" Confused or with the status "Continuing" and not "Ended"

Code:
SELECT
  *
FROM
  tvshowlist
WHERE
  tvshowlist.Lock = 0



Code:
SELECT
  *
FROM
  tvshowlist
WHERE
  tvshowlist.Status NOT LIKE 'ended'


or


Code:
SELECT
  *
FROM
  tvshowlist
WHERE
  tvshowlist.Status LIKE 'continuing'
(This post was last modified: 2016-05-10 13:20 by DanCooper.)
find quote
hst12 Offline
Junior Member
Posts: 1
Joined: Nov 2015
Reputation: 0
Post: #24
Surround sound movies with 6 channels or more

Code:
SELECT DISTINCT movielist.* FROM MoviesAStreams INNER JOIN movielist ON (MoviesAStreams.MovieID=movielist.idMovie) WHERE MoviesAStreams.Audio_Channel>=6
find quote
PH-SYM Offline
Fan
Posts: 410
Joined: Feb 2012
Reputation: 18
Location: The Netherlands
Post: #25
I was experimenting with this feature and added a table. After the required restart of Ember I now get the message:
Code:
SQL logic error or missing database
no such table: main.Sources

Is there any way I can undo my error or do I have to delete the entire database and start from scratch?
I already searched in all files for the name of the added table of a unique part of the query to no avail

I think all righthtinking people in this country are sick and tired of being told that ordinary, decent people are fed up in this country with being sick and tired.
(This post was last modified: 2017-08-29 19:39 by PH-SYM.)
find quote
DanCooper Offline
Moderator
Posts: 3,412
Joined: Apr 2012
Reputation: 168
Location: Switzerland
Post: #26
(2017-08-29 19:35)PH-SYM Wrote:  I was experimenting with this feature and added a table. After the required restart of Ember I now get the message:
Code:
SQL logic error or missing database
no such table: main.Sources

Is there any way I can undo my error or do I have to delete the entire database and start from scratch?
I already searched in all files for the name of the added table of a unique part of the query to no avail

Crash on start has been fixed in latest (unofficial) build: Link
In this build you can start Ember and go to the settings without a crash. The catching of wrong SQL queries is not fully done atm.

The "views" are saved in the database. You can open the MyVideosXY.emm with an editor like SQLite Browser. Go to "Views", search your custom entry and delete ONLY THIS ONE.

[Image: X8uIsvg.png]


BTW: main.Sources is wrong. How do you create your query?
(This post was last modified: 2017-08-29 22:09 by DanCooper.)
find quote
PH-SYM Offline
Fan
Posts: 410
Joined: Feb 2012
Reputation: 18
Location: The Netherlands
Post: #27
I took the example in post #10 and modified it like so:
Code:
SELECT movielist.*
FROM movielist INNER JOIN Sources ON (movielist.Source = Sources.Name)
WHERE Sources.Name LIKE "\SD"

I have a single source with subfolders HD and SD and wanted to view only those movies in the SD subfolder.
After the required reboot nothing had changed and the query was gone so I thought perhaps I needed to add a table with that query. That's when I triggered the database error.

While writing this down I noticed at least one mistake I made, "Soures.Name" is never going to give me te result I want since "\SD" isn't part of the source name but of the path instead.
For the record, I was using the BETA 1.4.90-alpha.Git.9457867 version and can confirm that with the latest (1.4.90-alpha.Git.b29978b) it is not crashing.

Lots of thanks for the rescue and the clear instructions to solve the mess I made. The database is OK again. Super!
I should've made a backup first off course when I go messing with things I am still learning about.

Can you give me a hint how I should have made that query to show only movies where the path contains a specific string?
Should I have used Sources.Path instead?
I must have made another critical error because if it was only the wrong object (.Name instead of .Path) I just would've gotten an empty.

Again thanks for saving me some hours rebuilding the database.

I think all righthtinking people in this country are sick and tired of being told that ordinary, decent people are fed up in this country with being sick and tired.
find quote
DanCooper Offline
Moderator
Posts: 3,412
Joined: Apr 2012
Reputation: 168
Location: Switzerland
Post: #28
(2017-08-29 23:14)PH-SYM Wrote:  Can you give me a hint how I should have made that query to show only movies where the path contains a specific string?
Should I have used Sources.Path instead?

The point is that the table for movie sources is no longer named "Sources", it's now "moviesource".
Actually, there is an overview of the current database. Actually means "in theory"... Just checked and seen that I had not updated the overview for months. Big Grin
Now it's up-to-date with the latest official realease (not with Github nightlies, but for your case it's not relevant). You can find the link in the settings:

[Image: i6TfR3j.png]


If you want filter the movies by sub-path of your source you can't filter it by the path of the source. But you can filter it by the path of the movie itself:
Code:
SELECT DISTINCT *
FROM
  movielist
WHERE
  movielist.MoviePath LIKE "FULL-PATH-OF-YOUR-SOURCE\SD%"

FULL-PATH-OF-YOUR-SOURCE is e.g. "D:\Movies"
\SD if your movies are saved in "D:\Movies\SD"
% Wildcard, is the same like * if you search in Explorer, e.g. "D:\Movies\SD*"
(This post was last modified: 2017-08-30 00:54 by DanCooper.)
find quote
PH-SYM Offline
Fan
Posts: 410
Joined: Feb 2012
Reputation: 18
Location: The Netherlands
Post: #29
Works great.
BTW, is there a specific reason for using "SELECT DISTINCT" instead of just "SELECT"? Just to check whether I understand it correctly, it will filter out duplicate movies won't it?

I think all righthtinking people in this country are sick and tired of being told that ordinary, decent people are fed up in this country with being sick and tired.
find quote
Post Reply