How to badly mix up your Catalog work in a simple way.

[m.Th.]

Be warned: Do NOT do the following:

Do not rename or change paths outside of XnViewMP to any cataloged folder!

Your work (ratings / colors etc.) will be 'hidden'!

Perhaps most of you know this, but (perhaps) what you do not realize is that even if you change the capitalization (change the case) of a directory, XnView will be 'confused'.

How to reproduce:

Having the following directories (or similar):

Code: Select all

\
  Folder 1
    Subfolder 1
    Subfolder 2
       SubSubFolder 21
       SubSubFolder 22

1. Catalog (rate, set colors, categories etc.) some of the images from eg. SubSubFolder 21. Check that all the stars, colors and categories are in place.
2. Close XnViewMP
3. Rename SubSubFolder 21 to subsubFolder 21 (or change the case in any way). Also, perhaps more spectacular, is to change the case of Subfolder 2 to subfolder 2. It will hide both subfolders. And if you want to be really geek, change the case of the Folder1 to eg. fOldEr1. It will blow away the entire tree underneath.
4. Reopen to XnViewMP. Perhaps if will show (correctly) the last opened folder. To force a refresh, go to another folder and return back.

Expected: Ratings, colors etc. to remain in place.

Actual: All the ratings, colors and such disappeared!

How to fix:

Yes, I cheated. I knew when I looked at the DB structure. The folder paths are stored inside case-sensitively, which smells to me that the engine threats in this way the paths. I checked using the procedure above and indeed the XnViewMP threats c:\foo\bar\baz and c:\Foo\Bar\Baz differently!

The fix for changing case is simple: Store and process the paths in/from DB in case INsensitive way (call lower for example).

Also this fix leads to a whole area of searching opportunities:

• Instant pattern search for directories and files in database. (Btw, 'Use database' checkbox doesn't work)
• Ultra fast finding of duplicates by using the DB info. Way faster than a regular Duplicate Finder like eg. in Tools menu.
• Flexible scope limiting for different DB searches and processing engines (cleanup etc.).
• Relocation. Oh, the big problem of relocation. But this a little latter on...

Yes, I studied a little the area.

Btw, I think that's better to use c++'s Lower and store them in this normalized way rather than do the conversion at each fetch from DB. This will help us a little when we'll optimize the...

Code: Select all

SELECT foo FROM Images WHERE FolderID=? AND FILENAME=?;

...to something much better, I think.

A problem here how the program shows the paths in the Folders Tree. Istm that it looks on the disk, hence no problem having in the catalog in small letters.

PS: Do not forget to do a simple upgrade procedure for the database. It is a simple query. If you want, I'll write it for you.

[JohnFredC]

Paths should be stored as they are named: with mixed case.

Instead, if case is not important for a designated function/comparison/whatever, then use an UpperCase(path) function call whenever referencing the field's stored value.

In Access that would be UCase$(tablename!fieldname). A similar call is available in most other programming languages or one can write a simple function.

[m.Th.]

Paths should be stored as they are named: with mixed case.

Instead, if case is not important for a designated function/comparison/whatever, then use an UpperCase(path) function call whenever referencing the field's stored value.

In Access that would be UCase$(tablename!fieldname). A similar call is available in most other programming languages or one can write a simple function.

Well, let's see...

Let's say that we'll store the paths (AND the filenames - but here is another discussion...) as is: in mixed case.

What it will happen?

Because the program uses (mostly) the fullpath+filename as PK when it comes from the disk, in order to avoid the problem which I described in my first post, it needs to do a case insensitive comparison (WHERE clause, JOINs etc.), correct?

But this is a performance killer, because using the upper() (the corresponding SQL function in SQLite's syntax) it means that any index will be thrown away, forcing a full natural table scan on the said table.

In fact, this is a well-known technique to avoid index usage and/or force a full table scan (yes, sometimes there are exceptions): use a function or a neutral operation (like select foo from bar where myIntegerField+0 = 888 ) in order to ditch the index out.

SQLite doesn't support expression indexes, but even if it would support, personally I would try to avoid them since they are much slower on Selects, Updates and Inserts because the bytecode of the index's expression is evaluated each time.

That's why, it is much better to store the data in normalized way (ie. lower case) for searching and, if it is necessary (eg. for display purposes) put another field with the original name.

But let's see what the others do:

Lightroom (Lr) 4 - its database schema is an entire forest (69 tables, many of them quite wide) - however it is fast enough - their slowness come mostly form the big previews first - they have BIG previews - near to 100% JPGs - and secondary from the whole bunch of data stored in these tables. However (like ASP 1.1) it is prone to the above problem because it stores the path in original case. It is one of the biggest complains about Lr (see their forums). Also they don't offer DB search on folders. Also, for them since the Catalog isn't done automatically, you are forced to work only inside of the application. There is no browse to the 'outside world'.

However, the file names (which for Lr is the most important asset) are stored in 6 (six!) fields:

• originalFilename - which is the original file name with the extension ( - obvious! )
• baseName - only the file name of the above (no extension)
• extension - only the extension
• idx_filename - the UpperCase version of the originalFilename
• lc_idx_filename - the LowerCase version of the originalFilename
• lc_idx_filenameExtension - the LowerCase version of the 'extension' field

AfterShotPro 1.1 - much simpler DB backend, prone to this problem. However it "mitigates" the problem by not allowing any searches outside the catalog (like Lr) and automatically saving any rating/color (etc.) to XMP sidecars. Like Lr, you must manually add the files to catalog but it is a much faster process. Also they don't offer search on folders. Files in catalog are scanned sequentially, case insensitive by LIKE %foo% (very very very slow on many files).


Zoner Photo Studio 15 Pro - the most direct competitor of XnViewMP (from which they took enough things IMHO ) has more fields:

• In Paths table just one field: CIP_NormalizedPath - the UpperCase version of the path
  In CatImages table:
• CIM_OriginalPath - path + filename as is
• CIM_Path - the UpperCase of the path + filename
• CIM_DisplayNameWithExt - filename only as is
• CIM_Extension - the extension

IDImager 5 - relatively big schema (42 tables) - resembles somewhat on path management the above programs - also do not offer DB searches outside the catalog but they do (did) a lot of effort to safeguard when the a media (eg. external USB drive) is mounted on another letter, folders are relocated etc. They have fields like MediumType, MediumName, MediumSerial, RootName, PathState etc.

-----------------------------------------------------------------
In conclusion, ( ) I think that (perhaps we need to discuss the following things in the 1st stage:

• We need to have the present Pathname field to be normalized in lowercase
• I think that we need to have another field with the original path (if we want to display somewhere - eg. in the Catalog pane)
• We badly need (this is in the 2nd stage ) a Catalog pane. In fact, we have it already in the Settings | Database: The 'Cache Maintenance' window there. But we must add there some things about relocation, get it somewhere more to the 'surface' etc.
• What about filenames? We DO change them often? We really need two fields? We need a normalized one? An original case one?

Discuss. Opinions? Comments?

[JohnFredC]

We need to have the present Pathname field to be normalized in lowercase

My 2 cents:

If search speed is slow due to pathname mixed case, then a simple solution is to store two pathnames, one correctly "cased" (for display in the UI), one mono-case for searching. This trades speed for storage space and moves the small overhead of "correcting" the case away from search and to a different part of the user's interaction with the system.

Not all environments are case-sensitive (which ones?), but in Windows I use case differences everywhere to visually distinguish the "words" of my paths and filenames. (I grew up with SmallTalk.)

In my own database work I've not noticed much (read:"practically significant") slowdown for the upper/lower case functions, even with a million records. Internally, case mapping is simple integer arithmetic, which means it is passed to dedicated silicon.

But, another thing to consider in these discussions of database design is the "BIG PICTURE" of performance over time. Speed of processing during search is only one component of whatever it is the user has in mind as there are many parts of a user's task with a computer, several of them "human", not computer.

For instance, if my system did not display the proper case of my pathnames, then my ability to recognize the correct one from a list would be compromised each instance that I peruse that list. In the "BIG PICTURE" over time, that single but repetitive slowdown in MY behavior would likely "wipe-out" any improvements in database search performance that mono-case pathnames were intended to address.

Just sayin': "good design" balances all components, both computer and human.

[m.Th.]

If search speed is slow due to pathname mixed case, then a simple solution is to store two pathnames, one correctly "cased" (for display in the UI), one mono-case for searching. This trades speed for storage space and moves the small overhead of "correcting" the case away from search and to a different part of the user's interaction with the system.

This are exactly my own thoughts also.

Not all environments are case-sensitive (which ones?), but in Windows I use case differences everywhere to visually distinguish the "words" of my paths and filenames. (I grew up with SmallTalk.)

Today are three desktop environments which matter (in our discussion): Windows, Linux and MacOSX. All are case sensitive in storage and display, case insensitive in search.

In my own database work I've not noticed much (read:"practically significant") slowdown for the upper/lower case functions, even with a million records. Internally, case mapping is simple integer arithmetic, which means it is passed to dedicated silicon.

Yeah, I know, it could happen. It depends a lot about the query, optimizer and, of course, hardware. Also, we're in an UTF-8 database which complicates the things.

But, another thing to consider in these discussions of database design is the "BIG PICTURE" of performance over time. Speed of processing during search is only one component of whatever it is the user has in mind as there are many parts of a user's task with a computer, several of them "human", not computer.

Sure. Absolutely.

For instance, if my system did not display the proper case of my pathnames, then my ability to recognize the correct one from a list would be compromised each instance that I peruse that list. In the "BIG PICTURE" over time, that single but repetitive slowdown in MY behavior would likely "wipe-out" any improvements in database search performance that mono-case pathnames were intended to address.

That's why I said that "most likely we need another field for display". That's why I posted also the Lr's example which has no less than 6 (six!) fields for the file names.

Just sayin': "good design" balances all components, both computer and human.

Sure. One of my main complains about XnViewMP is that its GUI many times doesn't advertise some features as it should - doesn't guide the users. Some features aren't discoverable. In short, his human part needs improvement.

To move forward:

What do you think about file names? I think that there we need the same thing also...

[JohnFredC]

If I was a better typist or had my current naming schema sufficiently "to hand" with no lacunae or ambiguities, then renaming would rarely be necessary. As it is, however, files get renamed around here very frequently.

As far as the internals of the database go, it doesn't matter to me how it is implemented as long as its behavior "at the surface" is speedy and characteristic of what I (or a generic user) would expect.

That said, simplicity of metadata portability between XnView installations on disconnected machines would be important to me if available. Even though the Cloud is useful to automate transmission of images between the machines, my images are so large and in such great number that at current bandwidths here it is not feasible to surrender them to a single location in the cloud.

This single issue of portability of metadata has prevented me from properly categorizing and otherwise organizing my images beyond my traditional folder hierarchy and rather complicated naming patterns. All the "custom" metadata I need is stored in the filename (FS.pano.IMG_18841-18893.01.Z.02a.jpg, for instance) while for the camera data I make sure to use a tool (Zoner) that passes it transparently to either jpgs, tifs, or pngs, whichever is appropriate for my project.

This probably doesn't completely answer your question, though.

[m.Th.]

1. Do you know that XnView MP automatically exports/imports the metadata stored in XMP files?
(or at least it should - I didn't check extensively. AFAIS I see that it imports them automatically...)

Why this solution doesn't fit to you?

Set on both workstations the XnView to automatically import/export XMP files.

Work on one PC -> the XMP gets updated in the background.

Copy/move/transfer the entire folder with both png and xmp files.

On the other, immediately when you open the XnView it (should?) see that the XMPs are there and updates the catalog transparently.

Where's the problem?

2. How Zoner does it?

[m.Th.]

We need to have the present Pathname field to be normalized in lowercase
I think that we need to have another field with the original path (if we want to display somewhere - eg. in the Catalog pane)

FOUND IT!!!

No need for more fields. No need for data change.

First, index with NOCASE collations the desired fields:

Code: Select all

create index Folders_PathName_Nocase_Idx on folders(pathname COLLATE NOCASE);
create index Images_Filename_Nocase_Idx on Images(Filename COLLATE NOCASE);

Then, when one want to do a caseinsensitive comparison will use the COLLATE NOCASE postfix:

Code: Select all

Select FolderID from Folders where pathname='C:\foo' collate nocase;

...will return the correct FolderID either the folder is called 'foo', 'Foo' and 'fOo'.

Also, to return all the FolderIDs of the subfolders we can use LIKE which is case insensitive and if we'll use it as 'Starting With' is covered by the NOCASE index:

Code: Select all

Select FolderID from Folders where pathname like 'C:\pictures\%';

Neat, isn't it?

The same stands for the files, of course.

Thanks God, I can put this at the end:

[oops66]

...FOUND IT!!! ... No need for more fields. No need for data change.
First, index with NOCASE collations the desired fields:

Code: Select all

create index Folders_PathName_Nocase_Idx on folders(pathname COLLATE NOCASE);
create index Images_Filename_Nocase_Idx on Images(Filename COLLATE NOCASE);

Then, when one want to do a caseinsensitive comparison will use the COLLATE NOCASE postfix:

Code: Select all

Select FolderID from Folders where pathname='C:\foo' collate nocase;

...will return the correct FolderID either the folder is called 'foo', 'Foo' and 'fOo'. ...

... Clever

... Also, to return all the FolderIDs of the subfolders we can use LIKE which is case insensitive and if we'll use it as 'Starting With' is covered by the NOCASE index:

Code: Select all

Select FolderID from Folders where pathname like 'C:\pictures\%';

Neat, isn't it?

... Clever too , even if "LIKE" is a little bit less efficient/speedy
... and thank you for your "study of case"

[JohnFredC]

Do you know that XnView MP automatically exports/imports the metadata stored in XMP files?

This used to be a manual process... I will test it.

[m.Th.]

...FOUND IT!!! ... No need for more fields. No need for data change.
First, index with NOCASE collations the desired fields:

Code: Select all

create index Folders_PathName_Nocase_Idx on folders(pathname COLLATE NOCASE);
create index Images_Filename_Nocase_Idx on Images(Filename COLLATE NOCASE);

Then, when one want to do a caseinsensitive comparison will use the COLLATE NOCASE postfix:

Code: Select all

Select FolderID from Folders where pathname='C:\foo' collate nocase;

...will return the correct FolderID either the folder is called 'foo', 'Foo' and 'fOo'. ...

... Clever

... Also, to return all the FolderIDs of the subfolders we can use LIKE which is case insensitive and if we'll use it as 'Starting With' is covered by the NOCASE index:

Code: Select all

Select FolderID from Folders where pathname like 'C:\pictures\%';

Neat, isn't it?

... Clever too , even if "LIKE" is a little bit less efficient/speedy
... and thank you for your "study of case"

...LIKE in this particular case with the wildcard at the end (ie myField LIKE 'my string%') - usually known on other systems as 'Starting With' (there are also special operators for it) can use index search if the index has the same case sensitivity with the 'Starting With'. Hence the same speed with two indexed comparisons. IOW, lighting fast and very very very efficient. But one must know how to have his indexes.

[m.Th.]

Do you know that XnView MP automatically exports/imports the metadata stored in XMP files?

This used to be a manual process... I will test it.

Be sure to have the correct settings in Tools | Settings | Metatada and report back your findings.