Zhorn Software
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Go down
avatar
mk
Guest

how is the stickies tags information stored? Empty how is the stickies tags information stored?

Wed Dec 15, 2021 4:32 pm
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.
avatar
mk
Guest

how is the stickies tags information stored? Empty Re: how is the stickies tags information stored?

Fri Dec 17, 2021 10:41 am
answering myself Razz

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
Sooyke
Sooyke
Posts : 421
Join date : 2018-04-28

how is the stickies tags information stored? Empty Re: how is the stickies tags information stored?

Fri Dec 17, 2021 2:34 pm
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
avatar
mk
Guest

how is the stickies tags information stored? Empty Re: how is the stickies tags information stored?

Sat Dec 18, 2021 10:13 am
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.
Sooyke
Sooyke
Posts : 421
Join date : 2018-04-28

how is the stickies tags information stored? Empty Re: how is the stickies tags information stored?

Sat Dec 18, 2021 2:52 pm
Thanks ! Very creative workaround  !
Best regards
Admin
Admin
Admin
Posts : 359
Join date : 2018-03-30
Location : London
http://www.zhornsoftware.co.uk

how is the stickies tags information stored? Empty Re: how is the stickies tags information stored?

Sun Jan 02, 2022 1:32 pm
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
Sponsored content

how is the stickies tags information stored? Empty Re: how is the stickies tags information stored?

Back to top
Permissions in this forum:
You can reply to topics in this forum