Page 1 of 3

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

Posted: Sat Aug 27, 2011 8:55 am
by JeF
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

Re: Extra Queries (v1.0)

Posted: Sun Sep 04, 2011 9:24 am
by JeF
v1.1 is out : few bugs fix

JeF

Re: Extra Queries (v1.0)

Posted: Sun Sep 04, 2011 8:26 pm
by TerribleFloater
Does it know LOWER and UPPER functions?

Re: Extra Queries (v1.0)

Posted: Sun Sep 04, 2011 9:55 pm
by JeF
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

Re: Extra Queries (v1.1)

Posted: Mon Sep 05, 2011 4:24 am
by TerribleFloater
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 :(

Re: Extra Queries (v1.1)

Posted: Mon Sep 05, 2011 7:38 am
by JeF
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

Re: Extra Queries (v1.1)

Posted: Mon Sep 05, 2011 8:59 am
by TerribleFloater
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:

Re: Extra Queries (v2.0)

Posted: Fri Sep 23, 2011 6:39 am
by JeF
Hi all,

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

;)
JeF

Re: Extra Queries (v2.0)

Posted: Fri Sep 23, 2011 5:59 pm
by TerribleFloater
I think V.2.0 (maybe over V.1.1?) locks DB so no movie can be deleted :-(

Re: Extra Queries (v2.0)

Posted: Fri Sep 23, 2011 8:58 pm
by JeF
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