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: 1173
Joined: Fri Feb 20, 2009 3:34 pm
Location: France

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

Post by JeF » Fri May 28, 2010 6:59 pm

Hi all,

I started this thread after having contacts with some of you requesting customized lists.
So you may find here some extra queries to build extra lists which coudl be useful in some cases.
Feel free to post your wishes or your own extra queries (thanks to TerribleFloater for contributing)

So, how does it work (published from unfinished documentation) :

What is an extra list :
An extra list definition consists in two lines to be added at bottom of sql requests file JeF_program_sql.ini
o A sql query line
o A title/description line which will be displayed as a link in concerned section of database toolbox page
and has an identifier (Id) composed of :
  • - A type prefix : M for Movie, P for Person or Z for Other
    - A unique number between 0101 and 0200
Extra Lists are divided into three groups :
o Movies Lists (Ids like M0101, M0101D)
o Persons Lists (Ids like P0101, P0101D)
o Other Lists (Ids like Z0101, Z0101D)
  • Export list : extract data from database to the result file ‘JeF_Sql_Result.csv’
    Database Altering List : add, update or delete data from database
Important : an extra list number (whatever the type prefix is) must be used only once. That is, for instance, you must not have M0101 and P0101 ids in the file. Correct ids should be M0101 and P0102.

Some of the extra lists are included within JeF’s package.
Others are available here.

You can also create your own extra lists.
A minimum of Sql and Movienizer database structure knowledge is required to do so, but you can ask here for a particular list and, if no one has answered before, I’ll try to give you the requested lines to add at bottom of the sql file (see below for details).

How to build a new extra list
o Choose the Id number to be used for the new list (between 0101 and 0200) and prepare the two lines to be added (you can use sqlitebrowser to build and test the sql query) :
  • 1st line : Prefix (M, P or Z) + Id number + ‘=’ + sql query
    2nd line : Prefix (M, P or Z) + Id number + ‘D’ + ‘=’ + title/description
How to add a new extra list to Database Toolbox Page
o Go to Database Toolbox Page (GearWheel icon)
o Open sql requests file by clicking the link 1. Edit Sql Requests File (advanced users)
o Make sure the chosen Id number is not already in use in sql requests file (choose a new id if it is)
o Insert the two lines at bottom of file
o Save file and quit text editor
o Reload sql requests file by clicking the link 2. Reload Sql Requests File
o A new line is now displayed as a link in concerned section (Movies Lists, Persons Lists or Other Lists)

Here are additional extra lists previously requested on forum or by PM.
You can choose and add the ones you need at bottom of sql file.

Please make sure the sql Ids given here as examples are not already in use in your local file.

Code: Select all

M0104=select code, title, year, rating from movies where in_collection=1 and code in (select distinct movie from editions where filename>"") order by date_add desc, code
M0104D=Playable movies (having a filename)

Code: Select all

M0105=select code, title, year from movies where in_collection=1 and code in (select movie from movies_codes group by movie having count(*)>1) order by code desc
M0105D=Movies in collection where sites > 1

Code: Select all

M0106=select code, title, year from movies where in_collection=1 and code not in (select distinct movie from images) order by date_add desc, code
M0106D=Movies without a Picture

Code: Select all

Z0101=insert or ignore into manuals (code,reference,name) select max(code),11,'NR' from manuals where reference = 11 and name = 'NR' 
Z0101D=create NR mpaa rating

Code: Select all

Z0102=update movies set mpaa=(select code from manuals where reference=11 and name='NR') where in_collection=1 and mpaa NOT in (select code from manuals where reference=11)
Z0102D=Assign NR value to movies with no mpaa rating

Code: Select all

Z0103=update editions set filename=substr(filename,2,length(filename)-2) where filename like '"%';
Z0103D=Remove leading and trailing double quotes from filename

Code: Select all

Z0105=update movies set mpaa=null where code in (select m.code from movies m inner join manuals ma on ma.reference=11 and ma.code=m.mpaa where ma.name NOT in ('NR','G','NC-17','PG','PG-13','R'))
Z0105D=Clear mpaa value when not one of 'NR','G','NC-17','PG','PG-13','R'
Z0106=delete from manuals where reference=11 and name NOT in ('NR','G','NC-17','PG','PG-13','R')
Z0106D=Delete mpaa value definitions which are not one of 'NR','G','NC-17','PG','PG-13','R'

Code: Select all

Z0107=insert or ignore into partners (code,site,url,picture,sort_order,language) select max(code),'csfd','http://www.csfd.cz/','csfd.gif',14,'Czech' from partners where site = 'csfd'
Z0107D=create 'www.csfd.cz' czech site definition with 'csfd.gif' as logo picture
Z0108=insert or ignore into partners (code,site,url,picture,sort_order,language) select max(code),'YouTube','http://www.youtube.com/','youtube.gif',15,'English' from partners where site = 'YouTube'
Z0108D=create 'www.youtube.com' english site definition with 'youtube.gif' as logo picture
Z0109=insert or ignore into partners (code,site,url,picture,sort_order,language) select max(code),'fdb','http://www.fdb.cz/','fdb.gif',16,'Czech' from partners where site = 'fdb'
Z0109D=create 'www.fdb.cz' czech site definition with 'fdb.gif' as logo picture

Code: Select all

Z0110=select distinct e.disc_nom, m.title, ma1.name, ma2.name, m.imdb_code from movies m left outer join editions e, movies_manuals mm on e.movie=m.code and mm.movie=m.code and mm.reference=8 left outer join manuals ma1 on ma1.code=m.mpaa outer join manuals ma2 on ma2.code=mm.ref_code
Z0110D=Export Disc#, title, genres, imdb

Code: Select all

Z0111=select path from images where movie in (select code from movies where title='Gran Torino')
Z0111D=Pictures paths for movie 'Gran Torino'

Code: Select all

Z0112=update editions set disc_nom = '0' || disc_nom where disc_nom > '' and length(disc_nom) < 4
Z0112D=Format Disc # to have 4 digits

Code: Select all

M0113=select code, title from movies where in_collection=1 and not code in (select distinct movie from data)
M0113D=Movies with no cast and crew

Code: Select all

M0114=select code, title from movies where in_collection=1 and not code in (select distinct movie from data where mode=1)
M0114D=Movies with no director

Code: Select all

M0115=select code, title from movies where in_collection=1 and not code in (select distinct movie from data where mode=3)
M0115D=Movies with no actor

Code: Select all

M0116=select code, title, year from movies where code in (select movie from editions where disc_nom like 'S-%');
M0116D=List Movies having disk# matching S-*

Code: Select all

Z0117=update movies set rating = 0
Z0117D=Reset User Rating on all movies

Code: Select all

Z0118=select e.filename, e.code from editions e left outer join movies_manuals mm on mm.movie=e.movie join manuals mn on mn.code = mm.ref_code where mn.name = 'Western' 
Z0118D=Export Editions List of genre 'Western' (to use, for instance, with XBMC plugin 'Load List' button)

Code: Select all

Z0119=insert or ignore into partners (code,site,url,picture,sort_order,language) select max(code),'TheTVDB','http://www.thetvdb.com/','thetvdb.gif',14,'Multiple' from partners where site = 'TheTVDB'
Z0119D=Create 'www.thetvdb.com' site definition with 'thetvdb.gif' as logo picture

Code: Select all

Z0120=update movies set title_sort = upper(substr(title_sort,1,1)) || substr(title_sort,2,length(title_sort)-1)
Z0120D=Capitalize the first letter of movies sort title

Code: Select all

Z0121=update episodes set title = upper(substr(title,1,1)) || lower(substr(title,2,length(title)-1)) where title = upper(title)
Z0121D=Capitalize episode titles which are full uppercased

Code: Select all

P0121=SELECT t1.name AS name, t1.code AS code1, t1.birth_date AS birth_date1, t1.original_name AS original_name1, t2.code AS code2, t2.birth_date AS birth_date2, t2.original_name AS original_name2 FROM persons t1, persons t2 WHERE (t1.name = t2.name AND t1.code<t2.code) order by t1.name, t1.code asc
P0121D=Person duplicates (Thanks to TerribleFloater)

Code: Select all

Z0122=update movies set in_collection = 1 where description > ''
Z0122D=Set 'in collection' movies having a description

Code: Select all

Z0123=update persons set in_collection = 1 where description > ''
Z0123D=Set 'in collection' persons having a description

Code: Select all

Z0124=update movies set checked = 1 where in_collection = 1 
Z0124D=Group Operations : Tick all Movies in collection
Z0125=update movies set checked = 0 where in_collection = 1 
Z0125D=Group Operations : UnTick all Movies in collection

Code: Select all

M0126=select code, title, year from movies where code in (select distinct movie from editions where sort_order > 1) and code not in (select distinct movie from episodes)
M0126D=Movies with 2 or more editions
This one will only work with plugin version of Extra Queries (JeF's ToolBox)

Code: Select all

P0127=SELECT p.code as Code, p.name as Name, COUNT( d.person ) as RoleCount FROM data d, persons p WHERE d.person = 
p.code AND d.mode = 3 GROUP BY p.code HAVING ( ( COUNT( * ) > 10 ) ) ORDER BY COUNT( d.person ) DESC, p.name ASC
P0127D=Actors by count of roles

Code: Select all

M0128=UPDATE movies SET in_collection=1 WHERE code IN (SELECT movie FROM editions WHERE filename<>'')
M0128D=Set the In collection flag for all movies that have links to files

Code: Select all

M0129=UPDATE movies SET wanted=0 WHERE wanted = 1 AND ( in_collection = 1 OR seen = 1)
M0129D=Switch off 'Wanted' flag for movies 'in collection' or 'seen'

Code: Select all

Z0130=update editions set disc_nom='', disc_nom_int=0 where disc_nom>''
Z0130D=Reset ALL assigned disk numbers

Code: Select all

P0131=select code, name from persons where in_collection=1 and code not in (select distinct person from images where person > 0 and mode = 6) order by date_add desc, code desc
P0131D=Persons in collection without a Photo (by creation order, newest first)

Code: Select all

Z0132=update movies set imdb_rating = 0 where imdb_rating > 0
Z0132D=Reset ALL imdb ratings

Code: Select all

Z0133=update movies set mpaa_rating = '' where mpaa_rating > ''
Z0133D=Reset ALL mpaa ratings (text only)

Code: Select all

Z0134=update movies set mpaa = 0, mpaa_rating = '' where mpaa > 0 or mpaa_rating > ''
Z0134D=Reset ALL mpaa ratings (value and text)
For plugin use:

Code: Select all

P0135=SELECT p.code as Code, p.name as Name, COUNT( d.person ) as RoleCount, COUNT( m2.code) as CollectionCount, COUNT( m.code) as SeenCount FROM data d, persons p LEFT OUTER JOIN movies m ON m.code = d.movie AND m.seen = 1 LEFT OUTER JOIN movies m2 ON m2.code = d.movie AND m2.in_collection = 1 WHERE d.person = p.code AND d.mode = 3 GROUP BY p.code ORDER BY COUNT( d.person ) DESC, p.name ASC
P0135D=Movie Counts by Actor : Movies as actor, In collection movies, Seen movies
For plugin use:

Code: Select all

Qry = select mn.name as Location, m.title as MovieTitle, e.disc_nom as EditionDiskNbr from loans l, editions e, movies m, manuals mn where e.code = l.edition and m.code = e.movie and mn.code = l.location and mn.name = 'Shelf' order by m.title
Desc = Movie Editions located in 'Shelf'

Code: Select all

Z0136=update editions set filename = 'H:' || substr(filename,3,length(filename)-2) where filename like 'R:%';
Z0136D=Change Drive Letter in video filenames (From R: to H:)

Code: Select all

Z0137=update editions set disc_nom = trim(disc_nom) where disc_nom > '';
Z0137D=Remove leading and trailing spaces from Disk#

Code: Select all

M0138=select m.code, m.title as Title from movies m, movies_codes mc where m.in_collection = 1 and mc.movie = m.code and mc.script = 'imdb' group by m.code having ( count(*) > 1 )
M0138D=Movies with multiple imdb reference

Code: Select all

M0139=select code, title, year from movies where code in (select distinct movie from editions where code not in (select edition from loans)) and in_collection = 1 order by title_sort
M0139D=Movies having one or more editions without a location

Code: Select all

M0140=select code, title, year from movies where checked = 1 order by title_sort
M0140D=Ticked Movies

Code: Select all

Z0141=update persons set height = height || '0' where height is not null and length(height) > 1 and length(height) < 3
Z0141D=Fix person height being less than 1 meter
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
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
For plugin use:

Code: Select all

Qry =update editions set filename = 'Z' || substr(filename,2) where substr(filename,2,2) = ':\'
Desc = Set Drive Letter to Z in all video filenames beginning with a drive letter ( ?:\ )
;)
JeF

estate
Star
Posts: 465
Joined: Sat Sep 05, 2009 6:06 pm
Location: Meppel, The Netherlands

Re: JeF's interface Extra Queries

Post by estate » Sat May 29, 2010 10:00 pm

Nice to see you starting this topic, gives us the possibility to extend MN a bit more into the ultimate by adding some special developed or requested sql to customize MN to our wishes.

You're the best ;)

estate
Star
Posts: 465
Joined: Sat Sep 05, 2009 6:06 pm
Location: Meppel, The Netherlands

Re: JeF's interface Extra Queries

Post by estate » Sat May 29, 2010 10:18 pm

Hi Jef,

Windows 7 today changed my drive number with the database on it for no reason from R to Q

Now I am in desperate need of a querie that alteres all my data ( and movie trailer locations) from R to Q

Is that possible ? and if so are you willing to post it here ?

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

Re: JeF's interface Extra Queries

Post by JeF » Sat May 29, 2010 10:44 pm

estate wrote:Hi Jef,

Windows 7 today changed my drive number with the database on it for no reason from R to Q

Now I am in desperate need of a querie that alteres all my data ( and movie trailer locations) from R to Q

Is that possible ? and if so are you willing to post it here ?
You should use filename renamer tool 'Begining' mode and use only the drive letters as source and target. See more here
;)
JeF

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

Re: JeF's interface Extra Queries

Post by JeF » Fri Jul 09, 2010 4:08 pm

Hi all,
Added three extra lists (at bottom of firts post)
;)
JeF

rumrak
Passerby
Posts: 2
Joined: Thu Aug 26, 2010 12:24 am

Re: JeF's interface Extra Queries

Post by rumrak » Fri Sep 10, 2010 9:08 am

Great post keeping them all together. Some handy ones in there, I added few as well.

There is another one I would like to add if its possible. "a reset (clear) all option to the user rating."

I imported a large database from allmymovies and all the IMDB ratings from there got turned into user ratings in movienizer. be nice to clear em out for a fresh and organized start.

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

Re: JeF's interface Extra Queries

Post by JeF » Fri Sep 10, 2010 9:38 am

rumrak wrote:There is another one I would like to add if its possible. "a reset (clear) all option to the user rating."
Added Z0117 query at end of first post.
;)
JeF

rumrak
Passerby
Posts: 2
Joined: Thu Aug 26, 2010 12:24 am

Re: JeF's interface Extra Queries

Post by rumrak » Fri Sep 10, 2010 5:25 pm

JeF wrote:
rumrak wrote:There is another one I would like to add if its possible. "a reset (clear) all option to the user rating."
Added Z0117 query at end of first post.
;)
JeF
Thanks Jef, worked great.

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

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

Post by JeF » Sun Nov 28, 2010 4:17 pm

Added person duplicates query. Thanks to TerribleFloater. :)
JeF

TerribleFloater
Superstar
Posts: 1889
Joined: Thu Feb 28, 2008 4:20 pm

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

Post by TerribleFloater » Tue Mar 15, 2011 5:20 am

Useful technical queries:

Code: Select all

P0128=select p.code from persons p where p.code not in (select person from persons_codes) order by p.name
P0128D=Persons without codes

Code: Select all

P0129=select p.code from persons p where p.in_collection=1 and p.code in (select person from persons_codes group by person having (persons_codes.code='')) order by p.name
P0129D=Persons in collection with the blank codes

Code: Select all

P0130=select p.code from persons p where p.in_collection=1 and p.code in (select person from persons_codes group by person having (persons_codes.site='')) order by p.name
P0130D=Persons in collection with the blank URLs

Code: Select all

P0131=select p.code from persons p where p.code not in (select person from data) order by p.name
P0131D=Persons without movies
Check your bases, ladies and gentlemen! :o
Хочу: чёрные списки, пол людей, пользовательские поля изданий.
Кинопоиск: Image ПлюсКинопоиск: Image

Post Reply