Extra Queries (v2.2) for Movienizer until v5.5

Additional plug-ins for the program.
User avatar
JeF
Black Belt Squirrel
Posts: 1173
Joined: Fri Feb 20, 2009 3:34 pm
Location: France

Extra Queries (v2.2) for Movienizer until v5.5

Post by JeF » Sat Aug 27, 2011 8:55 am

Hi all,

Initialy released within JeF's Toolbox, i realized this is far more convenient as a standalone plugin.

How to install :
Just unzip in plugins subfolder of Movienizer install dir.

How to import new queries from JeF's Extra Queries thread :
- Choose the query to import from the post
- Click 'SELECT ALL' and then copy selected content (CTRL-C)
- Switch to Movienizer
- Start ExtraQueries plugin
- Click 'Import' button
- Paste the lines (CTRL-V)
- Click OK button
You'll then see the new imported query, ready to be run

;)
JeF

Latest changes :
Version 2.2 (2011/10/03)
[*] Sql syntax check now using prepare command
[-] Few Bug fix (progress bar, categories)

Version 2.1 (2011/09/27)
[+] Quick Filter
[*] Results formatting speeded up
[-] Bug fixing (out of memory error)

Version 2.0 (2011/09/23)
[+] Ticker : Ticking tool for selected movies or persons
Choose a select query involving movies or persons and hit the ticked checkbox button to access the Ticker
Then you can choose :
- how to reset (or not reset at all) previously ticked items
- to tick or untick all items returned by the query
The reset part is optionnal (leave previously ticked/unticked untouched) so it's possible to run several queries one after another and easily build a complex selection
[+] SQLite Return Codes and Messages Support (useful for fixing sql syntax errors)
[+] User Defined Categories (3 dots button)
[+] Returned Items Count in result page title (<RowCount> tag for xml export)
[*] Enhanced guessed number of records to process for altering data queries (number displayed in warning message before running an altering data query)
[-] Bug fixing
Attachments
ExtraQueries_v2_2.zip
Extra Queries plugin v2.2
(296.79 KiB) Downloaded 1059 times

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

Re: Extra Queries (v1.0)

Post by JeF » Sun Sep 04, 2011 9:24 am

v1.1 is out : few bugs fix

JeF

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

Re: Extra Queries (v1.0)

Post by TerribleFloater » Sun Sep 04, 2011 8:26 pm

Does it know LOWER and UPPER functions?
Хочу: чёрные списки, пол людей, пользовательские поля изданий.
Кинопоиск: Image ПлюсКинопоиск: Image

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

Re: Extra Queries (v1.0)

Post by JeF » Sun Sep 04, 2011 9:55 pm

TerribleFloater wrote:Does it know LOWER and UPPER functions?
As it's similar to a wrapper it should accept any valid sqlite 3 syntax.
I've successfully tested these functions using a basic select on movies table :

Code: Select all

select upper(title), original_title, lower(original_title) from movies
;)
JeF

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

Re: Extra Queries (v1.1)

Post by TerribleFloater » Mon Sep 05, 2011 4:24 am

Hmm. The original query:

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
runs about 5 seconds.

The modified one:

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 (LOWER(t1.name) = LOWER(t2.name) AND t1.code<t2.code) order by t1.name, t1.code asc
P0121D=Person duplicates
runs forever :(
Хочу: чёрные списки, пол людей, пользовательские поля изданий.
Кинопоиск: Image ПлюсКинопоиск: Image

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

Re: Extra Queries (v1.1)

Post by JeF » Mon Sep 05, 2011 7:38 am

Hi,
TerribleFloater wrote:The modified one:

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 (LOWER(t1.name) = LOWER(t2.name) AND t1.code<t2.code) order by t1.name, t1.code asc
P0121D=Person duplicates
runs forever :(
I'm not so surprised : building a join condition on calculated fields...
When using sqlite 3 browser, the issue is the same : runnig for a lonnnng time.

Tested on a Db having 4786 persons and 1 duplicate person:
with a sqlite 3 dedicated browser : approx. 30 sec.
with plugin : approx. 50 sec
So :
- no, it doesn't run forever
- yes, the plugin is slower than a sqlite dedicated browser due maybe to plugin formatting process (i'll check later the process time of the query alone inside the plugin)

I don't know the number of persons your db has but i'm sure process time will be very long whatever the way you try (even with a dedicated browser).
Maybe this is an idea for a new plugin : a duplicates finder... (building a memory table of lowercased names and then check...)

JeF

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

Re: Extra Queries (v1.1)

Post by TerribleFloater » Mon Sep 05, 2011 8:59 am

JeF, thanks for answer.
JeF wrote:I don't know the number of persons your db has but i'm sure process time will be very long whatever the way you try (even with a dedicated browser).
My DB has about 120000 persons (2500 duplicates) so I can name this query 'Overnight Delivery' (I do like this movie) :D

PS: Wow, 12:25! :lol:
Хочу: чёрные списки, пол людей, пользовательские поля изданий.
Кинопоиск: Image ПлюсКинопоиск: Image

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

Re: Extra Queries (v2.0)

Post by JeF » Fri Sep 23, 2011 6:39 am

Hi all,

V2.0 is out, coming with new features (see first post)

;)
JeF

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

Re: Extra Queries (v2.0)

Post by TerribleFloater » Fri Sep 23, 2011 5:59 pm

I think V.2.0 (maybe over V.1.1?) locks DB so no movie can be deleted :-(
Хочу: чёрные списки, пол людей, пользовательские поля изданий.
Кинопоиск: Image ПлюсКинопоиск: Image

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

Re: Extra Queries (v2.0)

Post by JeF » Fri Sep 23, 2011 8:58 pm

TerribleFloater wrote:I think V.2.0 (maybe over V.1.1?) locks DB so no movie can be deleted :-(
I've no pb with deletion.
Could you please give an example (the query and the number of persons to delete) ?
Step by step actions description from movienizer start should also help.
JeF

Post Reply