Corruption in the XnView's Database + temporary solution
Posted: Mon Dec 27, 2010 12:23 pm
Problem:
After MOVE operation Categories database becomes corrupt
Steps to reproduce the bug:
1) Assign a category to an image
2) click on the category tab to see all categories (check boxes) and then click on the check box of the category assigned to the image in step 1.
RESULT: image is visible and everything in OK
3) Move the corresponding image file from within the application (EDIT... MOVE...).
COMMENT: It is important to move the file from within the XnView application since moving the file from another application (e.g. Windows Explorer) would cause database corruption without any doubt.
4) Repeat Step 2.
RESULT: image is not visible!
The database is corrupted since it expects to see the image on the same place where it was when it was assigned the category
Problem cause:
It is obvious that the Category database is corrupted since it was not updated by the MOVE operation in step 3.
Temporary solution:
I see two possible solutions:
1) If one moves the image again and returns it to the same location it was in the step 1. the image is visible when the category check box is selected. This is a single file so the easiest solution is to return the file to its original folder.
2) However if one spends hours to categorize thousands of files we cannot use the first solution. A month ago I asked the question on General Support forum asking for the XnView's database specification (see http://newsgroup.xnview.com/viewtopic.php?f=35&t=21338) since I didn't find the specification in the application's manuals (by the way back then I asked for database specification not because of this bug but for the reason of feeling more comfortably knowing that in case the database was in an open, specified format I would not depend on XnView too much). Since I didn't receive the answer about database specification I investigated the files (using a hex editor) and I found out that XnView uses SQLite (free, open source SQL database engine C library). Then I found free tool called "SQLite Database Browser" (http://sqlitebrowser.sourceforge.net/) which is able to operate (read and modify) databases created with SQLite engine, thus also able to modify XnView's databases.
If the category database is corrupted for a large number of image files follow this procedure:
a) Use SQLliteBrowser and load the XnViews's database (FILE -> OPEN DATABASE and point to XnView's file Category.db)
b) Export the database into SQL commands text file (FILE -> EXPORT -> DATABASE TO SQL FILE)
c) Open the SQL text file in any text editor which support Reg-ex (Regular expressions). I used free text editor Notepad++ (http://notepad-plus-plus.org/)
d) modify the SQL text file by text editor using this Reg-ex find/replace command:
find: INSERT INTO t2 VALUES\((\d+),('.*')\);
replace with: UPDATE t2 SET f = \2 where i = \1;
use another find-replace combination to do the same with the directory names which come after "INSERT INTO t2 VALUES..." in the SQL text file.
Note that \\ is needed in Find in order to denote \ in folder name e.g. C:\\Documents and Settings\\... See http://www.scintilla.org/SciTERegEx.html for details on RegEx
e) Copy paste the modified text in the SQL text file into "SQL STRING" part of the SQLliteBrowser's "EXECUTE SQL" tab
f) Press EXECUTE QUERY in the SQLliteBrowser's EXECUTE SQL tab
g) Press SAVE DATABASE in SQLliteBrowser
The XnView's Category.db database will be updated/corrected by SQLliteBrowser
Related issues:
In relation to the question I posted a month ago regarding the application's databases (see question 2 at http://newsgroup.xnview.com/viewtopic.php?f=35&t=21338) I think we need a mechanism to export application's database data in a way to make the data potentially accessible to other applications. There are two possibilities:
1) To specify XnView's database formats on the lowest level (file level)
or more preferably
2) extend the application to export its internal databases in an universally understandable database language (e.g. SQL). Since XnView uses a database engine which is already compliant with SQL specification this would be relatively easy task to do. All we need is:
a) a module within XnView which is able to do the same kind of job as SQLliteBrowser does when exporting an SQL database (FILE -> EXPORT -> DATABASE TO SQL FILE)
b) update XnView specifications and explain the database structure on SQL level (e.g. Category.db has 5 tables so we need to know what is each table and each field within table for). Something of this is already understandable (like folder names of the image files what I used in my temporary solution), but some fields and tables are not very clear.
NOTE: You could do only 2.b. without 2.a., what is cheaper, but in this case you have to specify that XnView uses an open database format namely "SQLite", which in turn specifies its database structure (http://www.sqlite.org/doclist.html) since it is a free, open source database format, so then one could use any tool like SQLliteBrowser or any other to operate the XnView's databases.
However doing both, 2.a. and 2.b, is still better since it leaves XnView's database format hidden from the specification point of view thus leaving possibility to change its internal structure in the future (e.g. by changing the database engine), without affecting end users who would not exploit the knowledge of XnView's database internal structure on the file level since it would not be documented.
kind regards from sunny Croatia
After MOVE operation Categories database becomes corrupt
Steps to reproduce the bug:
1) Assign a category to an image
2) click on the category tab to see all categories (check boxes) and then click on the check box of the category assigned to the image in step 1.
RESULT: image is visible and everything in OK
3) Move the corresponding image file from within the application (EDIT... MOVE...).
COMMENT: It is important to move the file from within the XnView application since moving the file from another application (e.g. Windows Explorer) would cause database corruption without any doubt.
4) Repeat Step 2.
RESULT: image is not visible!
The database is corrupted since it expects to see the image on the same place where it was when it was assigned the category
Problem cause:
It is obvious that the Category database is corrupted since it was not updated by the MOVE operation in step 3.
Temporary solution:
I see two possible solutions:
1) If one moves the image again and returns it to the same location it was in the step 1. the image is visible when the category check box is selected. This is a single file so the easiest solution is to return the file to its original folder.
2) However if one spends hours to categorize thousands of files we cannot use the first solution. A month ago I asked the question on General Support forum asking for the XnView's database specification (see http://newsgroup.xnview.com/viewtopic.php?f=35&t=21338) since I didn't find the specification in the application's manuals (by the way back then I asked for database specification not because of this bug but for the reason of feeling more comfortably knowing that in case the database was in an open, specified format I would not depend on XnView too much). Since I didn't receive the answer about database specification I investigated the files (using a hex editor) and I found out that XnView uses SQLite (free, open source SQL database engine C library). Then I found free tool called "SQLite Database Browser" (http://sqlitebrowser.sourceforge.net/) which is able to operate (read and modify) databases created with SQLite engine, thus also able to modify XnView's databases.
If the category database is corrupted for a large number of image files follow this procedure:
a) Use SQLliteBrowser and load the XnViews's database (FILE -> OPEN DATABASE and point to XnView's file Category.db)
b) Export the database into SQL commands text file (FILE -> EXPORT -> DATABASE TO SQL FILE)
c) Open the SQL text file in any text editor which support Reg-ex (Regular expressions). I used free text editor Notepad++ (http://notepad-plus-plus.org/)
d) modify the SQL text file by text editor using this Reg-ex find/replace command:
find: INSERT INTO t2 VALUES\((\d+),('.*')\);
replace with: UPDATE t2 SET f = \2 where i = \1;
use another find-replace combination to do the same with the directory names which come after "INSERT INTO t2 VALUES..." in the SQL text file.
Note that \\ is needed in Find in order to denote \ in folder name e.g. C:\\Documents and Settings\\... See http://www.scintilla.org/SciTERegEx.html for details on RegEx
e) Copy paste the modified text in the SQL text file into "SQL STRING" part of the SQLliteBrowser's "EXECUTE SQL" tab
f) Press EXECUTE QUERY in the SQLliteBrowser's EXECUTE SQL tab
g) Press SAVE DATABASE in SQLliteBrowser
The XnView's Category.db database will be updated/corrected by SQLliteBrowser
Related issues:
In relation to the question I posted a month ago regarding the application's databases (see question 2 at http://newsgroup.xnview.com/viewtopic.php?f=35&t=21338) I think we need a mechanism to export application's database data in a way to make the data potentially accessible to other applications. There are two possibilities:
1) To specify XnView's database formats on the lowest level (file level)
or more preferably
2) extend the application to export its internal databases in an universally understandable database language (e.g. SQL). Since XnView uses a database engine which is already compliant with SQL specification this would be relatively easy task to do. All we need is:
a) a module within XnView which is able to do the same kind of job as SQLliteBrowser does when exporting an SQL database (FILE -> EXPORT -> DATABASE TO SQL FILE)
b) update XnView specifications and explain the database structure on SQL level (e.g. Category.db has 5 tables so we need to know what is each table and each field within table for). Something of this is already understandable (like folder names of the image files what I used in my temporary solution), but some fields and tables are not very clear.
NOTE: You could do only 2.b. without 2.a., what is cheaper, but in this case you have to specify that XnView uses an open database format namely "SQLite", which in turn specifies its database structure (http://www.sqlite.org/doclist.html) since it is a free, open source database format, so then one could use any tool like SQLliteBrowser or any other to operate the XnView's databases.
However doing both, 2.a. and 2.b, is still better since it leaves XnView's database format hidden from the specification point of view thus leaving possibility to change its internal structure in the future (e.g. by changing the database engine), without affecting end users who would not exploit the knowledge of XnView's database internal structure on the file level since it would not be documented.
kind regards from sunny Croatia