Sorting, etc. by Notes column? And user keywords in general, and in the Notes column

[Spliff]

1)

Nobody's happy with the current user keywords management: Having to open a dialog for entering a(nother) user keyword is one thing (better would be an additional pane, with the list, its entries to be checked / unchecked then), but having to open (and then to close again) a dialog for KNOWING which user keywords have (already) been assigned to the item in question, is simply not realistic.

Hence fellow users - the "user keyword" threads in this forum abound, and I have read them all (!), during the last days - trying to use "better", i.e. more practical, "user keywords", "abusing" UR's full-text search for that, and putting their "user keywords" into the content field.

Unfortunately, you, dear Kyle, have excluded most "special non-char chars" from full-text search, and it has to be said - this is a very important remark to retain, in order to prevent users' big deceptions - that the respective help item, "Auto-Generated Keywords", does NOT apply to possible special-chars in full-text search;

as the help item correctly states, "Auto-generated keywords are displayed only if full-text search enhancements are disabled.", and then, logically, even these remarks there,

"· Alphabet characters and $ _ @ characters are included.
· # - + % & characters are included if not the beginning of a word.
· . and : characters are included if not at beginning or end of a word."

unfortunately do NOT apply, for their alleged "positive (!) exceptional" side, so virtually ANY special, non-char character is excluded as user-side "user keyword" marker.

I finally found ¹ though, e.g. ¹a, ¹af, ¹afk (so that a search for ¹a would also find the other ones if you want it that way, i.e. "hierarchical tagging"), ¹whatever, so I have found a way, at last, to do "user keywords" which would be visible, and you would probably put them into your content pane.

(You can access ¹ by Alt-185 or Alt-0185 in the content pane, but only by 0185 in the search pane, so you would be well advised to assign the ¹, by Alt-0185, to one key or one key combination.)

Then, you also could - especially if you use these "user keywords" often or even systematically - put them into the Notes pane, always displaying that pane, e.g. quite flat, showing just one line, above the content pane.

Anyway, the ¹ "user keyword" marker (there might be others, just none of the "regular" ones that would come to mind) seems to be a big step forward; search would be without problem, general search including the Notes column, specific "Notes" search being available by "Advanced", but the ¹ sufficiently "standing out" in order for no "Advanced Search" being even necessary, wherever you put your "user keywords" marked that way.

=====OH!=====

I just had to discover, to my dismay, that, whilst even a single ¹ will be found (so that I had hoped it could be used within a search for "has ANY user keyword", searching for it also finds all occ of "1", and thus, yes, if as a user keyword, you just use ¹something without a space between the ¹ and something, and if you always use a space between numbers and text, it is possible to do your user keywords this way indeed (but without having the possibility to search for "any user keyword")...

but it's just another big deception re UR user keywording; Kyle, you really should allow for some start char for USER "user keyword" marking, be it the "dead" sign or something like that, we could perhaps discuss this again, in order to prevent unwanted results indeed, as with @/. and so on, but then, a LEADING dot, i.e.
.keyword
with space or newline or tab before could be a brilliant solution, since after all, only Windows' .NET would then be an unwanted hit in case.

I am absolutely sure that EVERY fellow user will want such a user keyword solution, since opening and closing a dialog, in order to just see your item's keywords, is surreal.

At least, ¹something, as a user keyword, is visually much more pleasing than 1something...

Remember, if your "marker plus user keyword" is not put into the full-text index, it's not worth anything, and that's our problem here which only Kyle could resolve! ;-)


2)

For newspaper (sites') clippings, I have got a special problem: Within every one of my respective "politics" categories (i.e. sub-trees), I want to order the items by publication date; those publication dates are, most of the time, within the first 300 or 400 chars of the clippings, and almost every time, they are the very first calendar date within them.

Unfortunately, they come, from multiple countries and multiple newspaper (sites), in multiple formats, so that any scripting for their normalization is error-prone and will not find (i.e. then treat) all occurrences, and I would have to check, afterwards, in order to rectify manually whenever my script didn't do it right (e.g. for dates in the form 04/05/2021, my script can also check which newspaper, so - just an example -, it knows it's May, when the clipping is from the Figaro, but April, from Daily Mail).

(UR items have a "creation date", but that being just quite "near" the publication date, I want the date I create to be precise, all the more so since I "follow" multiple, tinier sites just once every some weeks, then all the clippings getting the same creation date.)

UR comes with the table "Item", with ItemID, ItemTitle, and other columns; it comes with the table "ftsItem_content", with the columns docid, c0title, c1content and c2note; I have checked about 20 items for correspondence between ItemID (in Item) and docid (in ftsItem_content) (comparing their titles), and I HOPE (?) they will ALWAYS be identical in UR?

This is not assured per se, since docid is SQLite rowid, and those rowids can change, in generic SQLite, whilst ItemIDs are invariable once-and-for-all, so I hope the two IDs will never go apart within UR? (Of course, the user should refrain from external, rowid-changing SQL commands...)

Thus, in theory, I would be able to run an external script, onto a partial sql-to-csv dump, analyzing the c1content, and working on the c2note, then visually check the csv, manually adjust the erroneous note dates, and read back the corrected csv values into the UR SQLite file, externally. There are two problems then, one just technical, for me, whilst the other one possibly can't be overcome.

3)

There is the table ItemLink, with the column ChildPosition; I will have to look again into the tables, since the same item, if transcluded, will have different positions as child of different parent items, but once I will have understood where the "current" item position is listed - that should be some cross-table - I can then script the respective ITEM sorted orders in the UR tree (which remains to be done indeed after 2), and which is the reason for 2));

as for the DATA sort, that's easy to be done in any scripting language, once the dates are normalized, into the form 2021-12-31, and from there, you can sort the ChildPosition for the ItemID (=rowid) beneath its respective parent item.

4)

If I'm not mistaken though, external changes to the fts-table fields for content and note will NOT be preserved in UR? Since it seems that their unique use in UR is for full-text search('s index maintenance), and that their COMBINED, FORMATTED content is stored within the item's (unique?!) blob, and upon displaying any item, in UR, what we see on screen, is retrieved NOT from those columns, but from the item's blob column, and then, by a UR routine, distributed into those two panes?

This would mean that my external changes to these fields are lost upon loading the item in question, since their - externally changed - content is overwritten upon loading the item, or at least when UR leaves the item (and updates the full-text index).

5)

This would also mean that any SQLite3.exe changes into these fts-table columns (i.e. content and note), made in-between (i.e. when the file is run in UR, see my current thread upon SQLite3 use and problems), would also be discarded, probably not upon the "Refresh all" (shift-f5), but - since there would now an asynchronity between their (plain text) content and what would be their plain-text "expected" content, according to the (formatted, common) blob content - at the very next "loading" of the item in question, be it at load time, be it at save time for that item.

All this because the (common?!) blob, with its formatted content, always takes precedence over the (non-formatted and otherwise deemed identical) content of those text-only columns.

(It goes without saying that in all this, I assume that we can't, externally, fiddle around with the blobs; this would be technically possible, for an expert, but obviously is not realistic in our context.)

6)

I suppose that users with NEW DBs could quite easily overcome most of above-described problems by NOT using - as I have always done - the generic UR template, but some template with additional user attributes, and thus such a "user text attribute" always being visible on-screen; perhaps even sorting by the data in such a user attribute, from within UR, would be possible, even automatically, for some DB (or sub-hierarchy in a DB)?

7)

Could you give some hint how I would REPLACE all my items in such a way?

a) First creating a new DEFAULT template, with such an additional field (default for the db in question of course, not for all DBs); then saving the db; then externally (i.e. by SQLite3.exe / frontend) linking all my current items to this new default template, instead of their current one; then re-opening the db in UR: This should not crash I hope, but just show my current items, with that additional user attribute field empty? This would leave alone all the existing data, especially the existing blobs. or:

b) Create a new db, with, as default template, that "enhanced" template; then import my current db. Here again, my current data should be displayed then as it is now, just with those new, user attribute fields left empty. I'd prefer solution a) though, since that would prevent shuffling around gigabytes of data, with its inherent risks (and problems which might not become obvious but later on in case).