cintanotes best practice

alf329
Posts: 15
Joined: Mon Sep 30, 2013 5:07 pm

cintanotes best practice

Postby alf329 » Sat Dec 13, 2014 2:31 pm

There's probably a ton of best practice scattered over this forum and in the way that people use cintanotes. I'm not thinking of goodies here, and I'm not saying everyone needs to do this.

Here's an example I picked up by accident looking for something else. I like this because it is proactive and is a lot better than my old way of doing absolutely nothing, or sleep walking over the edge of the cliff.

I now have a note in my diary to check data integrity of my database once a week, so I exit cn, and run these commands in a dos window in my cn directory (note that I've downloaded sqlite3.exe into my cn directory):

sqlite3 mycintanotesdatabase.db
pragma integrity_check;

ok (is the normal response)

.exit
exit


(The last two commands are to get out of sqlite3 and then the dos window.)

Now it would be good to know which version of sqlite is embedded in each version of cn as it comes out, so that things can be kept in step. Is there an easy way to find out?

Here's another one that I'll do as part of my weekly routine. I'll export the whole database (all notes, all fields) to xml and then create an empty database and import it back in. This is a useful data integrity check and the import reports any duplicates found, which are discarded e.g skipped duplicates 99. This is also one of the options to get out of jail free if something nasty happens.

Just to finish with an example of a recommendation I've found on the forum but I'd like to find out a bit more about it, so it's work in progress:

viewtopic.php?f=6&t=1630

and this extract...

People who need maximum reliability might also consider
sqlite.pragma.synchronous = FULL
(but it will make CN IO operations slower)


After some more research, I've followed this advice.

I'd like my database settings to focus on data integrity (reducing risk of problems, early spotting that they're there) as I don't need high performance - after all it's just me saving the odd note, doing an occasional bit of tagging, and the odd move between sections plus good response when I search.

Cheers
Thomas Lohrum
Posts: 1324
Joined: Tue Mar 08, 2011 11:15 am

Re: cintanotes best practice

Postby Thomas Lohrum » Sun Dec 14, 2014 1:28 pm

alf329 wrote:Now it would be good to know which version of sqlite is embedded in each version of cn as it comes out, so that things can be kept in step. Is there an easy way to find out?

Simply execute 'sqlite3 cintanotes.db "select sqlite_version();"'. For me using 2.8.4 Beta 1 it returns version: 3.7.15.2.

alf329 wrote:Here's another one that I'll do as part of my weekly routine. I'll export the whole database (all notes, all fields) to xml and then create an empty database and import it back in.

Please be aware, that you will loose all content in recycle bin, as it is not included in the XML export.

Thomas
alf329
Posts: 15
Joined: Mon Sep 30, 2013 5:07 pm

Re: cintanotes best practice

Postby alf329 » Sun Dec 14, 2014 4:47 pm

Thanks for the info about export not including the contents of the database recycling bin - I haven't seen that anywhere before.

I'm not sure about your advice on the sqlite database version though. I tried your command on my database and got 3.8.7.3 which is higher than your version. Then the penny dropped, so I tried sqlite3 on its own without any parameters, and it reports 3.8.7.3.

My theory is that if you try your copy of sqlite3 without any parameters you will see what I mean. Of course if by chance your sqlite3 is exactly the same version as the database version...

So, I think your command reports the code version of the libraries in use, not the database. In my case of sqlite3 it is the version I downloaded a couple of days ago.

Doing a bit of research, I can't see that there's a command to inspect the database, but the version may be part of the database header, so if you're nifty with a file dump program...

Not something I have done for a few years, but here goes...

Format of database header is here:

https://www.sqlite.org/fileformat2.html#database_header

and that shows sqlite version number at hex offset 96 for 4 bytes. Mine contains 00 2d e6 01, so convert that to decimal according to:

https://www.sqlite.org/c3ref/c_source_id.html

and I get 3008001 which according to the instructions, gives my database version as 3.8.1

If there is a way to inspect cintanotes.exe to see what version of sqlite it has embedded in it, this might also be useful information.
Last edited by alf329 on Sun Dec 14, 2014 9:47 pm, edited 1 time in total.
Thomas Lohrum
Posts: 1324
Joined: Tue Mar 08, 2011 11:15 am

Re: cintanotes best practice

Postby Thomas Lohrum » Sun Dec 14, 2014 8:06 pm

Hi alf329,

you are correct on your assumption about the sqlite version. Thanks for pointing this out. Since i used sqlite3.exe to execute the query it returned the version, which is embedded in that executable of course. I don't think that the engine version is saved as part of the database header. It's probably best to have CintaNotes report the version of its embedded sqlite version. This could be part of the About-dialog and also a property of the settings-file, so that external tools have access to it's value.

Thomas
User avatar
CintaNotes Developer
Site Admin
Posts: 5001
Joined: Fri Dec 12, 2008 4:45 pm
Contact:

Re: cintanotes best practice

Postby CintaNotes Developer » Mon Dec 15, 2014 8:02 am

Hi Alf,

Thanks a lot for your comments!
They will surely help other people to find information easier, and probably I'll include some of it into the FAQ (which no one reads anyway so.. ;) )

Some of these advice are outdated though. For example, now there's no real need to check the integrity manually as CintaNotes can do it automatically on loading the file if you set debug.dbintegritycheck = 1 in cintanotes.settings.

Also I'd like to keep some of these implementation issues under the hood so that we have the freedom to change things in the future. I don't think that knowing SQLite version will help you much. According to http://www.sqlite.org/fileformat.html, the SQLite data format hasn't really changed since 2006.
Alex

Return to “CintaNotes Personal Notes Manager”