FTS expression tree

[vkfoto]

What is an FTS expression tree and why is it too large

FTS expression tree.JPG (15.66 KiB) Viewed 15957 times

I was removing a bunch of images from the database when this popped up.

I work with databases all day and whenever I set the status of a record to Delete, it becomes hidden and no longer contributes to any process, extract or count. Then the next time the database is purged the the record is actually removed. I would imagine that compacting the database is equivalent to purging it so why does it take so long to remove a bunch of images from just the database. No image is removed from the disk so there would not be any read/write overhead.

[fbungarz]

I too noticed that it is basically impossible to delete images from PSu. Trying to remove folders, however, is even worse, especially if they still contain images. Even if the folders or images actually no longer exist it takes ages to remove them in PSu. Rather weird...

[andrew.heard]

FTS = Free Text Search

[jstartin]

A quick "google" reveals that the error message is generated by SQLite and "it's something SQLite says if you've got a query with way too many arguments within a single set of parenthesis". Could these difficulties be related to filenames containing characters that act as operators or delimiters in SQLite searches, such as hyphens ("NOT") and spaces ("OR). I avoid these like the plague, and I have no difficulty at all removing files and directories from PSu, or deleting them from the file system by using PSu - it is the only way I do so.

[vkfoto]

... filenames containing characters that act as operators or delimiters in SQLite searches, such as hyphens ("NOT") and spaces ("OR)... .

Are you seriously suggesting I rename most of my image file names? Hyphens and spaces are legal Windows name characters.

[vlad]

You could leave the image files as they are, but you might want to use quotes when searching for file names.

[fbungarz]

Vlad,
this is not about searching. It is about deleting images (and folders). I have not experienced any problems deleting files and ALL my file names have underscores "_" as part of the file names, e.g., "PX_1254.jpg". For my folders I also use underscores "_" instead of hyphens "-", e.g., "2010_11_12" .

Even though I am not using spaces " " or hyphens "-" and even though I never ran into problems deleting a file, in myexperience deleting folders is a REAL pain! It doesn't work at all, if the folder still contains images. It is excruciatingly slow, if the images have been deleted. Ironically it does not even matter if these folders still exist on the hard drive. Even if I delete folders and files on the hard drive using Windows File Explorer, deleting them from PSu is still extremely slow. So, this is clearly a PSu database problem.
I have no idea why deleting a folder or files would not be just as snappy inside PSu as inside Windows File Explorer - both are, after all, only databases of the file system.

Besides, I doubt that using hyphens or spaces should have an impact. Even if these are used regularly in SQLite search statements good programming should take care of that. It would imply that you cannot do a search in PSu using a space? Almost all of my keywords have spaces, some include hyphens. Searching for photos with those keywords works perfectly - both via CTRL+F and using the global search box.
As vkfoto points out these characters are allowed by the Windows file system and there is no reason, why a good image database program like PSu should accommodate that.

Frank

[jstartin]

... filenames containing characters that act as operators or delimiters in SQLite searches, such as hyphens ("NOT") and spaces ("OR)... .

Are you seriously suggesting I rename most of my image file names? Hyphens and spaces are legal Windows name characters.

No, I'm not suggesting that at all, but if my speculative hypothesis (that the error message is related to legal file name characters being inappropriately interpreted as search operators) is anywhere near correct then it points to a bug in PSu. In that case an (optional) workaround could be to eliminate spaces and hyphens in file names - or I may be way off the mark .

....why does it take so long to remove a bunch of images from just the database. No image is removed from the disk so there would not be any read/write overhead.

I guess PSu is having to seek out references to the images in many different tables and write delete flags for them all. Deleting from the file system only has to alter the "index", I believe.

[jstartin]

Frank,
In separate posts above you wrote:

I too noticed that it is basically impossible to delete images from PSu....

and even though I never ran into problems deleting a file

Those statements seem contradictory...............

[fbungarz]

Ooops - good catch.
I should have explained that better: In my experience deleting images from the hard drive does not remove them from the database (actually makes perfect sense!), but if you then try to delete those empty image references from PSu, the program occasionally behaves quite erratically. Deleting images from within PSu in my experience usually woks well. It is deleting folders that I really struggle with. Perhaps I am too impatient, but in my case it literally takes several minutes to delete a folder...

[jstartin]

Besides, I doubt that using hyphens or spaces should have an impact. Even if these are used regularly in SQLite search statements good programming should take care of that. It would imply that you cannot do a search in PSu using a space? Almost all of my keywords have spaces, some include hyphens. Searching for photos with those keywords works perfectly - both via CTRL+F and using the global search box.
As vkfoto points out these characters are allowed by the Windows file system and there is no reason, why a good image database program like PSu should [not] accommodate that.

An illustration:
Define a label "odd-bod" (note the hyphen).
Assign it to an image.
Type odd-bod into the search box (top right of the screen).
Notice that odd-bod is listed as a suggestion in the drop-down panel.
Hit enter.
Note that the image is NOT retrieved in the collection viewer: by including the "hyphen" you have actually put in a "minus sign" to exclude records with bod as a token.
Search for odd bod without the hyphen: now you get the image labelled odd-bod (you have asked for records with odd OR bod) but, in my collection, many others too.
Quote-wrapping is the solution, and either "odd bod" or "odd-bod" will work. Or, change the label to odd_bod and search for it "as-is".

[Mke]

I too noticed that it is basically impossible to delete images from PSu. Trying to remove folders, however, is even worse, especially if they still contain images. Even if the folders or images actually no longer exist it takes ages to remove them in PSu. Rather weird...

I don't recall ever having that problem.
Just ran a test and imported a new folder with 80 JPEGs - 8 seconds to import (and 55 overall with synchronizing and building thumbnails); 12 seconds to delete the folder with the associated images. That seems reasonable.

ALL my file names have underscores "_"

Mine too...

[vkfoto]

....why does it take so long to remove a bunch of images from just the database. No image is removed from the disk so there would not be any read/write overhead.

I guess PSu is having to seek out references to the images in many different tables and write delete flags for them all. Deleting from the file system only has to alter the "index", I believe.

As jstartin speculates, perhaps it is doing a lot of table updates.

As I label many previous year's images, I'm finding duplicate folders and other anomalies. I first make corrections in the windows folders and then tell PSu to update itself. That's when things slow to a crawl. Even empty folders take a lot of time to remove. Is this the best that can be done?

[jstartin]

As I label many previous year's images, I'm finding duplicate folders and other anomalies. I first make corrections in the windows folders and then tell PSu to update itself. That's when things slow to a crawl. Even empty folders take a lot of time to remove. Is this the best that can be done?

Could you be a bit more specific about how you tell PSu to update itself, in PSu terminology?
Are you using "Verify folder", and if so what folder do you start it from?
How many files do the duplicated folders contain?
When you say 'empty folders take a lot of time to remove' do you mean that there is a long delay if you right click a folder in the folder explorer and select "Remove this folder", or that "Verify folder" takes a long time overall? (Verify does a great deal of work looking for unknown and missing folders and files, calculating file hashes to detect changes, etc.)

In my experience the best way to do housekeeping on catalogued folders/files is to do it using PSu. The catalog and file system are then always in-step and various pitfalls avoided.

[vkfoto]

I'm not fluent in PSu yet.

I had 72 images in the wrong folder. In Windows, I created a new folder and moved them. Then in PSu I selected the original folder and clicked verify folder. It quickly discovered the missing images and started the synchronizing and building thumbnails process. 8 minutes and 40 second later it was finished. Only about 7.2 seconds per image.

This was only a simple move of a few images. Often, I also have to rename and redate some older images, much easier in another program, as well as move them to proper folders. When it involves many hundreds, it is time to make some tea.