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
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
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
o Go to Database Toolbox Page
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-*
JeF
