how is the stickies tags information stored?

Hi All,

I've browsed the v10 sqlite db trying to find a relationship between sticky notes and their associated tags. Has anyone figured that out?

Thanks
M.

answering myself

tag id is stored in stickiesproperties.t_i attribute poining to tags.key 


sql dump:
UPDATE stickiesproperties SET sysmod=1639737140 WHERE rowid=2472;
UPDATE stickiesproperties SET t_i=1, sysmod=1639737140 WHERE rowid=2495;
UPDATE tags SET "key"=2, name='check', colour=12615680, sysmod=1639737513 WHERE rowid=1;
INSERT INTO tags(rowid,"key",name,colour,sysmod) VALUES(2,1,'dupa',65535,1639737513);
INSERT INTO tags(rowid,"key",name,colour,sysmod) VALUES(3,0,'tag1',13479028,1639737513);


thank you for a great piece of software!
all the best to everyone

Thank you for sharing it , i looked with dbviewer but fail to find something ( i am not familiar with sql..... ) . I wonder if there is a way to see this without query for it in a viewer .


Best regards

yeah, for some reason it is hard to spot by only looking at the static database

what helped, was a trick to use sqlite sqldiff utility

see sqldiff on the sqlite official website


in order to find out how stickies store data, I did create a copy of the main storage:

Code:

cd c:\Users\mk\AppData\Roaming\stickies\
copy stickies.db stickies2.db 

and then, after changing something in stickies content:

Code:

c:\bin\sqlite-tools-win32-x86-3370000\sqldiff.exe stickies.db stickies2.db

what you will get, is a nice sql dump of changes between the original copy, and the current state of the db

Code:

DELETE FROM content_png WHERE rowid=37;
UPDATE sqlite_sequence SET seq=336 WHERE rowid=1;
UPDATE stacks SET sysmod=1639378126 WHERE "key"=5;
UPDATE stickies SET sysmod=1639719609 WHERE "key"=4;

....

have fun!

br
M.

Thanks ! Very creative workaround  !
Best regards

I hope you've found the Excel file embedded in the help which explains the database structure?  Open Stickies help, and go to the stickies.db page - there's a file you can extract which lists how the database is structured.  You'll note that the [t_i] value indicates a tag when [type] field is 54.

Tom