[DanCooper]

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 ;-)

[Fail$tyle420]

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??

[Daydream]

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 .

[DanCooper]

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.

[Fail$tyle420]

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 .

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!

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 Thank you for the info!

[DanCooper]

Ok, so I didn't know what the hell I was doing AND it's not possible Thank you for the info!

It's not possible AND your SQL statement is wrong

[brunosso]

Dan is possible to create a list of all tvshows is not "Locked" or with the status "Continuing" and not "Ended"

[DanCooper]

Dan is possible to create a list of all tvshows is not "Locked" 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'

[hst12]

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

[PH-SYM]

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

[DanCooper]

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.




BTW: main.Sources is wrong. How do you create your query?

[PH-SYM]

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.

[DanCooper]

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.
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:




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*"

[PH-SYM]

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?

[calexicoca]

Just wondering if someone can help. It's beyond my knowledge to create a custom list. I would like to have a list of TV Shows when it was premiered and a separate one when was it added to database? Is that complicated to make?