JeF's Extra Queries (for interface and/or plugin)

How to make Movienizer support new sites. Customize Movienizer.
User avatar
JeF
Black Belt Squirrel
Posts: 1175
Joined: Fri Feb 20, 2009 3:34 pm
Location: France

Re: JeF's Extra Queries (for interface and/or plugin)

Post by JeF » Sun Jul 06, 2014 3:57 pm

iosefra wrote:Hi
I need queue to merge two duplicate movies/books if they have the same name. If there no data on the item which code is bigger then second one, than copy data from first to second.
something like that:

FROM movies t1, movies t2
WHERE (t1.title = t2.title AND t1.code<t2.code)
for every field where t2.* <> NULL or t2.*<>0 set t2.*=t1.*

Can anyone help?
Hmm... I really doubt just a simple sql command could do that (movies merging). Because movie data isn't only stored in "movies" table but has dependencies with other tables (for example movies_codes and movies_manuals tables are two of them) and merging two movies is, imo, rather a complex job that should be implemented as a plugin (or main program feature) where an interface could even allow user to choose which piece of data to keep and which to drop.
So, quite a big dev... which may not sound worthwhile if you're not frequently dealing with duplicates.

If you just want to fill empty fields then you can use an sqlite database browser (like SQLiteStudio for instance) to have a list of tables and columns.
Then you can build all sql queries (or even a dedicated interface script) you need to reach your goal but, again, i'm not sure it's worthwhile (until you're really freqently dealing with duplicates).

iosefra
Supporting actor
Posts: 88
Joined: Wed Jan 28, 2009 9:07 am

Re: JeF's Extra Queries (for interface and/or plugin)

Post by iosefra » Mon Jul 07, 2014 7:56 am

I need to do it once, but for about 1000 books.
This is because I did some import of DB from Access, and downloading persons info created books duplication.
So Actually I need to take at least some user defined parameters from my books to books downloaded with persons.
The items I sure I need is book #, location, disk_label, in_collection, seen, series, series_nom. I think it important one.
If you could help me to write sql to merge at least one of that fields, I think I could manage to do the same with all others.

User avatar
JeF
Black Belt Squirrel
Posts: 1175
Joined: Fri Feb 20, 2009 3:34 pm
Location: France

Re: JeF's Extra Queries (for interface and/or plugin)

Post by JeF » Mon Jul 07, 2014 11:53 am

iosefra wrote:I need to do it once, but for about 1000 books.
This is because I did some import of DB from Access, and downloading persons info created books duplication.
So Actually I need to take at least some user defined parameters from my books to books downloaded with persons.
The items I sure I need is book #, location, disk_label, in_collection, seen, series, series_nom. I think it important one.
If you could help me to write sql to merge at least one of that fields, I think I could manage to do the same with all others.
So we're talking about books and not movies... (sadly, imdb unique id could therefore not be used...)
I think the best way is a two main steps way :
1) set a unique identifier (book # i suppose) on all downloaded cards. That way, further queries could rely on that unique id to merge data between "imported" and "downloaded" cards. This could be done with a query matching titles but this also could lead to wrong matches.
This is why i think it's important to first alter id only and check results (before merging all remaining data)
2) build a query to populate an empty piece of data of a movie with the populated piece of data of the other movie having the same id.
Then build as many similar queries as needed (one for each particuliar piece of data).

Well, some further thinking is needed, here ... I'll post here another message when i've a more precise idea on how to do things.

raycomp
Extra
Posts: 15
Joined: Thu Dec 04, 2014 8:24 pm

Re: JeF's Extra Queries (for interface and/or plugin)

Post by raycomp » Sun Dec 21, 2014 12:40 pm

Hi Jef, I am more into series than movies and would like something to tell me if there are episodes in a series without a Filename eg E:\S02E07 The Beantown Bailout Job.avi that will give me an indication that the episodes was not downloaded.
Also maybe to show the original air date.

User avatar
JeF
Black Belt Squirrel
Posts: 1175
Joined: Fri Feb 20, 2009 3:34 pm
Location: France

Re: JeF's Extra Queries (for interface and/or plugin)

Post by JeF » Sun Dec 21, 2014 3:11 pm

raycomp wrote:Hi Jef, I am more into series than movies and would like something to tell me if there are episodes in a series without a Filename eg E:\S02E07 The Beantown Bailout Job.avi that will give me an indication that the episodes was not downloaded.
Also maybe to show the original air date.
Seems this should concern two cases :

1) you want to list all episodes having an edition without a video filename
Here is the query (for plugin use):

Code: Select all

Qry = select m.title, e.season, e.episode, e.original_air_date, editions.code, editions.filename from episodes e, movies m left outer join editions on editions.movie = e.movie and editions.season = e.season and editions.episode = e.episode where m.code = e.movie and editions.filename = '' order by m.code, e.season, e.episode
Desc = Series episodes having an edition without a video file
2) you want to list all episodes EITHER having no edition OR having an edition but without a video filename
Here is the query (for plugin use):

Code: Select all

Qry = select m.title, e.season, e.episode, e.original_air_date, editions.code, editions.filename from episodes e left outer join editions on editions.movie = e.movie and editions.season = e.season and editions.episode = e.episode join movies m on m.code = e.movie where editions.code is null or editions.filename = '' order by e.movie, e.season, e.episode
Desc = Series episodes without an edition or without a video file
;)
JeF

meliam
Extra
Posts: 15
Joined: Fri Nov 07, 2014 8:30 pm

Re: JeF's Extra Queries (for interface and/or plugin)

Post by meliam » Sat May 09, 2015 9:55 am

Hello everyone
I write a new custom query using "Extra queries" plugin for search persons that works together (such as actors or directors or writers or any crew that saved in your personal Data-Base).
I hope this query is useful


Title: Persons Worked Together
Qry: select title, year from movies where code in (select movie from data where person in (select code from persons where name='Tim Burton')) and code in (select movie from data where person in (select code from persons where name='Johnny Depp')) and code in (select movie from data where person in (select code from persons where name='Helena Bonham Carter')) order by year desc


notice: if you want search about more than persons, you must repeat this line more and more :
and code in (select movie from data where person in (select code from persons where name='FirstName LastName'))

User avatar
xbox80
Superstar
Posts: 505
Joined: Tue Sep 15, 2009 8:50 am
Location: Italy-Russian

Re: JeF's Extra Queries (for interface and/or plugin)

Post by xbox80 » Thu Dec 29, 2016 8:15 pm

queries for people in the database, but not "data" and "filmography" downloaded :idea: :?: :|

Post Reply