The Desktop copies of the Recurring stickies overlap - I need to fix that.

I see no way to change the x,y locations of the Recurring stickies from the UI.  If I am away from my PC for the day, then many will overlap when I come back.  

I want to be able to rearrange the Desktop copies how I want them, and then copy their (x,y) locations back to the Recurring stickies from which they were created.

I can see that the database is an SQLLite DB and I have an SQLLite DB reader & editor, and it seems to me that the right SQL Query could do that, but my SQL knowledge is a little rusty.  So can anyone give me the query to do that?

In the stickies table, the Desktop stickies noteclass is 1, and the Recurring stickies noteclass is 3.
In the stickiesproperties table the (x,y) coordinates are types 18 and 19

Hi,

this is feasible, but quite (very ?) complex (I'll try to do that in a second time, but I'm not sure to success...).

While there is a very easy solution to do the same without SQL, only using Stickies application itself.

When you have rearranged the desktop copies of your stickies, you "only" have to set these desktop copies with the recurring parameters you want (the same than those of original ones). These desktop copies become then new recurring stickies, with x,y position as you defined it (and disappear from desktop).
Then go to "Manage Notes" applet (right click on stickies icon in tray), select "Recurring" tab, delete "old" recurring notes, and keep only the "new" ones.
(To be sure to identify "new" vs "old", sort the list by modification date, or right click on it to get properties and check its position)
And (if necessary) for those of them you want to have them immediately on desktop (without waiting for next recurrence), right click on it and select "Wake a copy"

It was a little bit hard, but I eventually succeeded.
Hereafter the SQL code you asked for.

This code sets the line/column position of each recurring stickynote having at least one existing copy on desktop to the line/column position of its desktop copy.
In the particular case where a recurring note has several copies existing on the desktop, the new position given to recurring note is the position of one of these copies (no control on which one), or maybe in some cases, the X position of one of these copies, and the Y position of another of these copies.

Code:

update stickiesproperties as SP
   set t_i = newvalue
from stickiesproperties
JOIN
(
   select origkey, type, t_i as newvalue
   from stickiesproperties
   JOIN
   (
      select key, t_i as origkey
      from stickiesproperties
      where type = 38
   ) as X
   ON X.key = stickiesproperties.key
   where type in (18, 19)
      and stickiesproperties.key in (select distinct key from stickies where noteclass = 1)
) as listnewvalues
ON
   SP.key = listnewvalues.origkey
   and SP.type = listnewvalues.type


Thanks.  But I tried your code in:

1. SQLite3 command line program
   
2. DB Browser for SQLite
   
3. SQLiteMan
   

All of these had a problem with the SP alias, so I eliminated that and tried this:

Code:

update stickiesproperties
   set t_i = newvalue
from stickiesproperties
JOIN
(
   select origkey, type, t_i as newvalue
   from stickiesproperties
   JOIN
   (
      select key, t_i as origkey
      from stickiesproperties
      where type = 38
   ) as X
   ON X.key = stickiesproperties.key
   where type in (18, 19)
      and stickiesproperties.key in (select distinct key from stickies where noteclass = 1)
) as listnewvalues
ON
   stickiesproperties.key = listnewvalues.origkey
   and stickiesproperties.type = listnewvalues.type;


SQLite3 said:
Parse error: target object/alias may not appear in FROM clause: stickiesproperties

DB Browser for SQLite said:
Execution finished with errors.
Result: near "from": syntax error
At line 1:
update stickiesproperties
set t_i = newvalue
from

SQLiteman said:
Query Error: near "from": syntax error Unable to execute statement

I do not know SQL well enough to know what to try next.

Hi,

very surprised of what you say (at least with DB Browser for SQLite, since it's the tool I'm running (https://sqlitebrowser.org/ version 3.12.2 under Windows 10))
I just run once more the SQL in DB Brower For SQlite version 3.12.2 on Windows 10, and it worked fine again.

I cannot understand what is the problem you get. Giving an alias to a table ("as SP") cannot be a problem. At the maximum, it is useless, but here it is needed, because when missing, it may cause the clause "ON stickiesproperties.key = ..." ambiguous, because stickiesproperties being present several times, SQL do not know which one is concerned.
It is the error message I got (with DB Browser for SQlite) with the version modified as you did

L'exécution s'est terminée avec des erreurs.

Résultat : ambiguous column name: stickiesproperties.key

I tried to remove the ambiguity without this alias (the only solution being to give an alias to all other instances of stickiesproperties) and, of course, it works also, but not differently than with my first version. If you want to test, the modified code is there

Code:

update stickiesproperties
   set t_i = newvalue
from stickiesproperties as SP1
JOIN
(
   select origkey, type, t_i as newvalue
   from stickiesproperties as SP2
   JOIN
   (
      select SP3.key, SP3.t_i as origkey
      from stickiesproperties as SP3
      where SP3.type = 38
   ) as X
   ON X.key = SP2.key
   where SP2.type in (18, 19)
      and SP2.key in (select distinct key from stickies where noteclass = 1)
) as listnewvalues
ON
   stickiesproperties.key = listnewvalues.origkey
   and stickiesproperties.type = listnewvalues.type;

I'll do some tests (later...) with SQLite3 and SQLiteMan, but since I do not have these tools yet, I have before to install them.

In any case, I wrote this SQL "for fun", because I'm convinced that the solution I proposed you without SQL and using only application's features themselves is better (from far !)

I just run the test with SQLiteMAN (what a horrific gui !!! I cannot understand how anyone can use such a mess), and it works fine too.

But I'll don't do the test with SQLite3, since I saw it needs Python, and I don't want to install all this stuff.

I just run a new test, with SQLiteStudio (rev 3.4.4) this time... and I got an error.
With help of ChatGPT, I eventually found a light modification, that works fine this time in BOTH tools, "DB Brower For SQlite" AND SQLiteStudio

Just one remark, nevertheless : With SQLiteStudio, if we use the stickies.db without any precaution, we get at each time the message "too many levels of trigger recursion". So I deleted the trigger "stickiesproperties_update" (whose purpose is to put a timestamp on every updated lines, in sysmod column). And without this trigger, everything works fine. This is likely due to an invalid implementation of triggers in SQLiteStudio.

Anyway, "DB Brower For SQlite" (that do not have this problem with triggers) is from far better than SQLiteStudio and remain my prefered tool in this domain !

So pls find just below the V2 version of SQL request that is apparently more "universal" than previous one.

Code:

Update stickiesproperties as SP
set t_i = listnewvalues.newvalue
from
stickiesproperties as SP1
JOIN
(
  select origkey, type, t_i as newvalue
  from stickiesproperties as SP2
  JOIN
  (
      select key, t_i as origkey
      from stickiesproperties as SP3
      where type = 38
  ) as X
  ON X.key = SP2.key
  where SP2.type in (18, 19)
      and SP2.key in (select distinct key from stickies where noteclass = 1)
) as listnewvalues
ON
  SP1.key = listnewvalues.origkey
  and SP1.type = listnewvalues.type
where SP.key = listnewvalues.origkey
   and SP.type = listnewvalues.type
;


PS : The best "proof" that issue with triggers does not come from the (quite complex) SQL request is that we have the same error message when running a SQL request as simple as "update stickiesproperties set t_i = 4 where key = 869 and type = 18;" to update one specific line in the table....

I just ran a last test (yes, it's the last one, afterwards, I stop !) with Firefox's extension "SQLite Manager" as SQLite dabase manager.

And it's now clear that my V2 is better than V1, since V1 fails with "SQLite Manager", while V2 works fine.

I reviewed once more my code.... and I see something uselessly complex. I don't even understand why I inserted this complexity
And after simplification, my code becomes (V3)

Code:

Update stickiesproperties as SP
set t_i = listnewvalues.newvalue
from
(
  select origkey, type, t_i as newvalue
  from stickiesproperties as SP2
  JOIN
  (
      select key, t_i as origkey
      from stickiesproperties as SP3
      where type = 38
  ) as X
  ON X.key = SP2.key
  where SP2.type in (18, 19)
      and SP2.key in (select distinct key from stickies where noteclass = 1)
) as listnewvalues
where SP.key = listnewvalues.origkey
   and SP.type = listnewvalues.type
;


Of course, I have tested this latest version, with the three tools, DB Browser For SQLite, SQLiteStudio and Firefox's extension "SQLite Manager", and it works fine with the three tools.

Final version, then, and topic closed (from my perspective).

The last two versions you did both work.  I am keeping your V3 to use.
Part of the problem was that I was using very old versions of DB Browser for SQLite and SQLiteman, and SQLite only added support for a FROM clause in an UPDATE statement in version 3.33.0, released on August 14, 2020.

My sqlite3.exe works fine.  Note that this is a stand-alone program, not the sqlite3 built into Python.  It is in sqlite-tools-win-x64-3450000.zip www-dot-sqlite-dot-org/download.html.  You do not need Python.

After I use the Manager to:

• Close all Desktop stickies
  
• "Wake a copy" of everything in Recurring
  

Then I can arrange all of my somewhat numerous reminders so they do not overlap and appear where I want them.  Then:

• Close stickies.exe because I found it caches the (x,y) locations and does not reread them from the DB when it makes Desktop stickies.
  
• Run a batch script RepositionStickies.bat which contains this:
  sqlite3 C:\Users\%USER%\AppData\Roaming\stickies\stickies.db < C:\rtb\tools\RepositionStickies.sql
  
• Where RepositionStickies.sql contains your V3 SQL
  
• Restart stickies.exe.
  

This all works much easier and faster than manually resetting the Recurring stickies.

       Thanks very much.  You did a great job.

You're welcome.

But let me give you two advices : 

1 - NEVER use a "very old version" of any tool : At the minimum they may be not working correctly (as it was the case this time), but, worst, they can cause great damages, in particular on security standpoint. So, a good policy, is to ALWAYS keep tools as much "up-to-date" as they can.

2 - NEVER update internal database of system files of any aplication without, before, stopping the application. You wrote "Close stickies.exe because I found...". Even if you didn't have found any reason to close Stickies, it would be nevertheless MANDATORY to close it before updating in any way its database.

Have fun !

PS: I'll give (a day...) a try to standalone Sqlite3

Hi,

You don't need me, but my 2 cents are... (well in the UK it's 2 pence, but I expect more people will understand centS)

You could also achieve this, albeit manually, by using the Properties of a desktop note to see the x and y co-ordinates, and then you can set them in a recurring note by opening the Properties of that note, then holding shift as you double-click where it says "Properties" to open the advanced editor.  You can then set the values in there.

The other thing is about how Stickies re-reads the database file.  Mostly it doesn't - when it starts it reads in what it needs, and then what's in memory is real, and the database is assumed to be the same.  As rtbailey found, that's effectively caching the values.  Stored notes however are treated differently.  Seeing as there could be very many of these, they are never cached in memory, so it's safe to change them in the database.

I've thought about replacing the "save anything which has changed" routine to be "sync memory and the database writing changes in either to the other place", but it would be a chunk of work, and hardly anyone would ever use it so I've not done that.

Tom

OK, I see that works.  I see that it was documented, but that feature was hard to find.  That would be good to use for occasionally adjusting a few locations.

I started out by putting in quite a few Recurring stickies without repositioning them initially, and so they ended up on top of one another, so I could only see one of them.  That is what I was trying to get away from, as I had been using Windows Task Scheduler and msg.exe to create my reminders, but msg.exe would not show multiple reminders at the same time.  I was hoping that checking off "Rolled up notes have different positions on screen" in Desktop settings, but that did not seem to deconflict the reminders.  So that is why I asked for an SQL expert for the SQL to automate the repositioning.

Now that I have the LaurentG SQL and SQLite3.exe installed, I can rearrange all my reminders very quickly and easily.

Thanks.

Re:  Tom's reply, dated Mon Jan 29, 2024 10:55 pm, to "The Desktop copies of the Recurring stickies overlap" 

"opening the Properties of that note, then holding shift as you double-click where it says "Properties" to open the advanced editor"

I have repeatedly tried to open Properties advanced editor but I can not do so.  I have searched the help files without finding any reference to this "advanced editor."

Any help or direction would me most appreciated.


Update:

After much trial and error I finally discovered that "Shift + Double click" will not work on the actual Note.  It must be accessed through the "Manage Stickies" function.  It also only works for "Attached", "Sleeping" or "Recurring notes."
Here are the steps I used:

1. Right click the Stickies icon in the Notification Center on the taskbar.
2. Select Manage Notes (Ctrl + M)
3. Select the "Sleeping" icon.
4. Right click on the note for which you want to edit the properties.
5. Holding down the shift key, double click the "properties item at the bottom of the dropdown list that appears.

I have added the above to my personal help files as I can still not find it in the official help file.