SQLite3.exe, etc. problems

[kinook]

Excellent, glad to hear that it is working.

One more note:

7) If you want all new files created by UR to have the SQLite Format 3 signature, take one that you have already modified and copy it to

%APPDATA%\Kinook Software\Ultra Recall\template.urd

(literally type %APPDATA%\Kinook Software\Ultra Recall into Windows Explorer to go to that path)

See "User-defined new Info Database" at

https://kinook.com/UltraRecall/Manual/miscellaneous.htm

[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.