SQLite3.exe, etc. problems

[Spliff]

Oh, I didn't know that, that's brilliant! (I had done it "manually" each time.)

What is the UR files' code page / language format?, please, or are there several codepages even? (Window has got two, not one, from my current research. Is there any setting within UR, perhaps in the registry?

They say the codepage for a sqlite file should be UTF-8 (which would be 65001, there is also 437 for U.S., 850, the default Western European one, and then 1252, West Europen Latin, and I read precisely that one multiple times in UR files' headers, but I find there other indications, too, so I am not sure: which one applies, please?

I haven't found, by web search, any indication of the used character set, neither in the UR help file, "language", "utf", "utf-8", "utf-16", "ansi", "code page", etc. all don't give any result. (So my äöüéàè problem for sqlite.exe clipboard output (only) persists, but I'm in discussion about that problem in their forum currently, will "update" if there is a viable solution, or in case create file output to a virtual drive a: within the work memory (1 GB or just 500 MB would be sufficient), then write to and read from these "files" in the virtual drive. Since:

I WAS MISTAKEN about sqlite3.exe's
.output d:/ur/filename.db
and
.once d:/ur/filename.db
(always with /, not \ !!!):
the file manager I use most of the time (instead of several paid ones), FreeCommander XP, does NOT update (at all, or then just after several minutes!) the "0" content indicator in its list view, since it obviously doesn't "get" that there is already present the expected content, having been written into the pre-existing file (since previously created by sqlite3.exe (i.e. before the "select"), then filled up later on (after the "select"), even in the .once case. Thus I erroneously thought those files remaining empty, since FC told me so: my bad!)

Sorry for the additional confusion created!


EDIT:
Sorry, I had not thought to also search the forum, so I have done this search now, I find the threads
https://www.kinook.com/Forum/showthread.php?t=4405
https://www.kinook.com/Forum/showthread.php?t=1705
https://www.kinook.com/Forum/showthread.php?t=2472
and it seems that 1252 here is right?

BUT then, would UR choose the code page it applies to the files it creates, according to, i.e. in function of the user's system codepage? (Which was 437 in my case and up to now, and which is probably not "ideal" for my multi-language needs anyway...)

I understand code pages must be any developer's nightmare...;-( (All the more so since they say there is a second (!) codepage, somewhat "hidden", in Windows... and that there are even two more but which are considered less relevant... thus, FOUR codepages on any given Windows system...)

[kinook]

Ultra Recall is a Unicode application, and Windows uses UTF-16 encoding for its APIs.

https://docs.microsoft.com/en-us/win...2/intl/unicode

https://docs.microsoft.com/en-us/win...ntl/code-pages

Text is converted to UTF-8 encoding when stored in the URD file.

[Spliff]

Thank you very much, Kyle, for this valuable information, so the "1252" that I read in the urd file "headers" (i.e. quite near to the "top" of the files' binary text) are not to be considered.

So it's clarified then, and sqlite3.exe's clipboard problem seems to be unresolvable, but that's not a real problem after all, and the way to go is:

- by sqlite3.exe, write the select output to file
- read (by AutoHotkey or similar) that file into clipboard or into variable

- do the necessary transformations (e.g. regexreplace for the itemtitle "column") in there (by AHK or else within the var)
- ditto in there (or in a second var, for better checking), transform the result lines into the necessary SQL code ("update tablename...") for sqlite3.exe to execute that

- write that SQL code into a file
(- check that file, create the necessary UR COPY at least now...)
- have sqlite3.exe read and execute that file content (.read filename) (onto the UR COPY...!)


Thank you so much again, Kyle, you've been of tremendous help!


(And obviously, the "there is no such table" error messages by sqlite3.exe I had reported here some weeks ago, must have been caused by the above-mentioned problem of the sqlite.exe ".open filename" command just creating a new, empty file (and without that table then indeed), when you make any typo or don't also enter the suffix, i.e. there is no "there is no such file, create it?" message from slite3.exe then, so you try to work upon an empty file then...)

[Spliff]

Just a detail.

Kyle, you said opening an saving the DBs in UR leaves the db signature, when changed to SQLite format 3, alone, and that's right, and this is fantastic!

"But Tools - Compact and Repair" then changes the signature back to Ultra Recall DB.

(And this explains why I had unexpectedly encountered "chaos" within my db signatures, after having them all changed to the generic SQLite signature in bulk.)

I hope that can be addressed, too? ;-)

[kinook]

There is not a way to prevent that. You would have to change the signature back after compacting. That shouldn't be needed very often.

[Spliff]

Sorry to hear that, all the more so since I made the observation that after lots of moving items around,

"Compact and Repair" seems absolutely necessary!

Here is my observation:

I had not "compacted and repaired" anymore, in order to avoid the above, it's my main db with less than 15,000 items, and sized about 0.6 gb.

Then, in order to get some "intermediate order" at least into my "Inbox" in that - it had grown way faster than I had been able to "file" the items in it, thus had grown to near 2,000 items - I did a "preliminary filing" for its contents, by filing it all, in the meanwhile, not into dozens of real filing targets, but into 5, 6 intermediary ones (parent items = filing targets in the inbox, too, instead of outside of it, as the real targets are, so from a very long item list in the inbox, I went to a short list, with just some items "holding" (and thus "hiding from view") long lists of child items).

This was some days ago, and no problem after. Today then, from one day to the other, new item creation took, instead of less of a second, about 15 seconds for sibling, and 8 seconds for child items, invariably; of course this broke external macros of mine relying on fast item creation.

After compact-and-repair, which, as expected, did not "compact" the db as for its 0.6 gb size since what had been in the db had remained in it, item creation time went back to normal, so it seems that "item moves in numbers" create lots of overhead which then can be discarded again by "compact-and-repair".

Thus, avoiding it, as I had done for the above reason, obviously is counterproductive.

[Spliff]

My UR-SQLite problems have gone in its nth iteration it seems, none of my UR DBs opened in my SQLite frontend anymore, even after re-install of the latter (new Navicat vesion anyway, from 6.0something to 6.1). (Ditto for previous Compact-and-Repair, and then the necessary signature change, of course.)

So I re-installed UR, too (for the nth time), opened the DBs in UR, closed them again after some changes, i.e. after real "save": no chance.

BUT with the re-installed UR, AND after compact-and-repair (in that), then signature change of course, the same UR files opened in Navicat without any problem.

HENCE:

Something on my system obviously deletes / changes some dll or whatever file, so that UR will NOT save my UR files with a correct header, I suppose (my UR files' headers are much longer of course than just the signature); UR will then continue to open those UF files correctly, but the generic form of the header is gone, just my assumption.

Now for a possible culprit: I regularly use (paid and up-to-date) "WiseCare 365", in order to get rid of (each time) thousands of crap files and crap registry entries, and since there are so numerous, I really need that "cleaner" tool.

This is just an assumption of course, something other also could do the change / deletion which then makes that UR obviously does not store my UR files in the generic SQLite 3 format again, so probably this description could help you with identifying the UR problem? (Everything else always works as expected.)

Now for the header, the problem is, those headers are very different from one UR file to the next (except for the signature of course), so in theory I could copy some headers for non-working (= as SQLite DBs) UR files, and of the others that work; this would imply some effort but if that can be helpful I could try, even with the "same" files "before and after" the treatment with the fresh UR install; by my theory, the headers should be different, but since I don't know what those header hyroglyphes "mean", I could not say if the changes then just come from compact-and-repair, or from the alleged fact that fresh UR writes other, correct SQLite headers, than the allegedly incorrect ones non-fresh-UR may write.

But it seems obvious to me that very probably, UR relies upon some "external" dll or such, for writing the headers upon "save", and that some third-party thing in my system changes or deletes that.

For example, the similar tool "RightNote" is said - I have never used it, does not appear "good enough" to me - to write generic SQLite from start on, without the need to do the obvious changes in UR to that effect.

Of course, next time I run WiseCare 365, I'll try before and after, in order to exclude that, in case, but the fact that UR does not write generic SQLite but its own, somewhat different format, seems to be at the heart of the problem, since it then enables the secondary problems to appear.

To make this perfectly clear from above: UR-re-install does not help, Compact-and-Repair does not help, but UR-re-install and immediate Compact-and-Repair after that, with the fresh UR re-install, does help, i.e. does produce the UR file in a format which then, with the necessary signature change in-between, will open in SQLite frontends.

(Ditto of course for the SQLite command line tool, with the additional problem in there that, as explained above, it will not give an error message but will just open some "memory" db in which then the user's sql commands will generate multiple error messages and/or non-"finds".)

All this is of course absolutely awful, I really never know "before" if my UR db "accepts" SQLite commands or if it's "not an SQLite file" again, and again, and again... ;-)


EDIT:

I just tried, Wise Care 365 (WC) is not the culprit:

I made changes in 2 files, in UR, then ran WC while UR was running. I then opened the files in Navicat without problem.

I then made further changes in UR, then closed UR (with the files), then ran again WC, then again opened / run UR again, and opened the files in UR (which now might have been affected by the two WC runs), made changes, saved, closed UR; again I was able to open the files in Navicat without problems.

Hence: Something else running on my system (I don't use another "cleaning" tool though) "fiddles" with something UR reaches out to, and then UR writes its files not in the correct generic SQLite format anymore, and then fresh UR install AND compact-and-repair with that fresh UR install will be necessary, in order to get generic SQLite format again (after then-necessary signature change).

I ALSO want to clarify that the problem is not due to the Navicat installation since I FIRST re-installed the (also updated) Navicat version, without any "progress" in the above-mentioned problems, then only re-installed UR, and even then, the problems persisted (now with new and updated Navicat) for every UR file which I had not yet "compacted and repaired" with the NEW UR install. Thus, some third-party software seems to affect some external file UR seems to need, and without the UR routine making the call becoming "aware" of that change, causing the misfunction of what UR "writes" then, be it in the header or elsewhere... and without that affecting the internal UR processing of the UR files, they are just not generic SQLite anymore then.

TO CLARIFY what above is implied:

If you can give a list of files of which a probably change by third-party software probably might be responsible for the above behavior, I could then copy those files, after a fresh UR installation, into a "safe" folder, in order to then compare them daily with the possibly changed files in their original position.

It would then be of interest, after UR's expected "SQLite fail", to just copy / write those "saved" files back over the "changed originals", then check if UR's "SQLite fail" persists, without doing a complete UR re-installation; thus, we could identify the file(s) which are responsible for the above (and perhaps I could even identify the culprit third-party software which changes that file(s)).

[kinook]

UR does not rely on external DLLs for reading/writing of URD DB files. The SQLite library is statically linked into UltraRecall.exe. And the only difference between a UR-only file and a SQLite file that other tools can open is the first 15 bytes being

Ultra Recall DB

vs.

SQLite format 3

You can open a URD file in a hex editor and change those first 15 bytes if they get changed by UR.

[kinook]

In the latest download (v6.3.0.1), compacting a database will write the 'SQLite format 3' signature rather than 'Ultra Recall DB'.