Re: [sqlite] Access to sqlite3_api_routines outside of a loadable extension context

[Keith Medcalf]

You write the code as if it were a loadable extension.  

If you compile "inline" (ie, appended to the amalgamation) the headers will 
detect the SQLITE_CORE symbol being defined and generate direct calls rather 
than indirect calls.  There is really no need to change the code from the code 
you would use from a loadable extension, unless you want to "hide" the loadable 
extension init() symbol when compiled as part of the amalgamation (or inline 
using the SQLITE_CORE define) and use the EXTRA_INIT hook to do initialization.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Max Vlasov
>Sent: Thursday, 5 April, 2018 09:24
>To: SQLite mailing list
>Subject: [sqlite] Access to sqlite3_api_routines outside of a
>loadable extension context
>
>Hi,
>I'm considering creating a virtual table or user function that might
>possible work either as a loadable extension or as a general,
>statically
>created one. In order to avoid repeating during developing, I thought
>that
>I might use sqlite3_api_routines structure as a universal access to
>sqlite
>code routines in both cases. But it seems it is impossible to access
>the
>structure outside of a loadable extension context. Or am I missing
>something?
>
>Probably some direct access to sqlite3Apis variable is possible, but
>I
>suspect such practice isn't  recommended.
>
>Thanks
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Access to sqlite3_api_routines outside of a loadable extension context

[Max Vlasov]

Hi,
I'm considering creating a virtual table or user function that might
possible work either as a loadable extension or as a general, statically
created one. In order to avoid repeating during developing, I thought that
I might use sqlite3_api_routines structure as a universal access to sqlite
code routines in both cases. But it seems it is impossible to access the
structure outside of a loadable extension context. Or am I missing
something?

Probably some direct access to sqlite3Apis variable is possible, but I
suspect such practice isn't  recommended.

Thanks
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In memory only WAL file

[Pavel Cernohorsky]

Hello Dan, thank you very much for clearing this up, because that was my 
important misunderstanding.


Risking corruption when the OS crashes is not really an option for me. I 
will probably go for what David Raymond suggested in one of other posts, 
or I may also solve my whole problem on the application level and have 2 
databases. One in memory only, which holds "all the changes since the 
last checkpoint", the second one on disk with all the data up to the 
last checkpoint. The character of the data I need to store allows this - 
I can first query the in-memory database for the most recent results, if 
I do not find them, I can query the on-disk database. My manual 
checkpoint will then be simply "writing everything from my in-memory 
database to my on-disk database in a single transaction". But my first 
choice will be the VFS David suggested.


Thanks,

Pavel


On 04/05/2018 09:28 AM, Dan Kennedy wrote:

On 04/05/2018 02:08 PM, Pavel Cernohorsky wrote:
Hello Dan, so you are saying that if WAL is somehow in memory only 
(not properly persisted) and app or OS crashes in the middle of the 
checkpoint operation, my main database file will get corrupted? And 
by corrupted you mean as in "you will loose changes which were in the 
WAL file", or "you will end up with unusable main database file, or 
file where rows which were affected by the checkpoint will have wrong 
contents (halfway through written, ...)". In other words, I may end 
up with the main database file in some other state than just "like 
before checkpointing" or "like after checkpointing"? I understood 
checkpointing as a kind of atomic operation which "merges data in the 
main database file and in the WAL". Is that understanding wrong?


That's correct. If you crash mid-checkpoint and lose the wal file, 
some future queries may return inconsistent results or SQLITE_CORRUPT 
errors.


The suggestion made in another post to put the wal file on a tmpfs or 
similar file-system is a good one. Then you will only risk corruption 
if the OS crashes. There is no easy way to do that at the moment 
though, you will have to do some hacking to get it to work.


Dan.







Thanks, Pavel


On 04/04/2018 06:33 PM, Dan Kennedy wrote:

On 04/04/2018 09:01 PM, Pavel Cernohorsky wrote:
Hello, does anybody know if there is some possibility to not have 
WAL file as a normal file on the disk, but only in memory? I 
understand that all the modifications to the database would get 
lost in case of the application / OS crash, but for my application, 
I only need the level of durability based on checkpointing. I just 
need to guarantee that all the data are properly written to the 
main database and synchronized to disk when manual (or even 
automatic) WAL checkpoint is called, but I do not care if I loose 
data in between the checkpoints. Of course database should never 
get corrupted.


If your app or the OS crashes halfway through a checkpoint and this 
means that the WAL file is lost, the database is likely to be 
corrupted. Is that a problem?


Dan.




My goal is to limit the number of IOps being performed to the disk. 
Currently I use "PRAGMA synchronous = 1" and there is only one 
process manipulating the database (multiple reader threads, only 
one writer thread at one moment in time). Or if it is not possible 
to have WAL in memory only, is there something like “PRAGMA 
wal_synchronous = none_and_delete_wal_if_corrupted”?


Thanks for suggestions, kind regards,
Pavel


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In memory only WAL file

[Pavel Cernohorsky]

Hello David, thanks for describing things in details, it helped to clear 
up some of my misunderstandings.


I was also thinking about VFS, but your description pointed out some 
very important things I have missed (such as persisting my in-memory WAL 
to disk before checkpointing).


If nobody picks up your challenge to "rip apart your suggestion", as you 
are saying, I will probably go for that.


Thanks a lot,

Pavel


On 04/04/2018 09:00 PM, David Raymond wrote:

I don't think you can have all of the above. The "should never get corrupted" 
part of SQLite comes from having the data in 2 non-volatile storage files during a 
commit/checkpoint. Problems while writing data to the main file are covered by having the 
rollback journal or WAL on disk to recover from in the event of death.

The memory journal mode says "I care about speed to the exclusion of recovery" 
and means a problem at the wrong time will leave you with a corrupted database. Without 
that second non-volatile store of the data then there will always be a chance of 
corruption while writing to the one and only permanent file that you're using.

WAL gets you around the writer blocking readers problem, but still uses the 
stored-in-2-places technique to prevent disaster.

(Sketchy on implementation details from here out)

With all your access being from 1 connection on the local computer, I would 
think that the best-case for you would be to write your own VFS that keeps a 
WAL in-memory so that you can have the concurrent access and speed, but then 
for corruption avoidance come checkpoint time writes the pages to be updated to 
a non-volatile rollback journal before updating the main db file. That way you 
could still have multiple in-memory non-recoverable transactions and commits 
between recoverable checkpoints. You'd still have to write to both the journal 
and to the main file, but would only have to do so once per page updated 
between checkpoints rather than potentially multiple times.

At least I think that's an option anyway. Those more familiar with what's 
actually possible and actually useful will now rip apart my suggestion.



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Pavel Cernohorsky
Sent: Wednesday, April 04, 2018 10:01 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] In memory only WAL file

Hello, does anybody know if there is some possibility to not have WAL
file as a normal file on the disk, but only in memory? I understand that
all the modifications to the database would get lost in case of the
application / OS crash, but for my application, I only need the level of
durability based on checkpointing. I just need to guarantee that all the
data are properly written to the main database and synchronized to disk
when manual (or even automatic) WAL checkpoint is called, but I do not
care if I loose data in between the checkpoints. Of course database
should never get corrupted.

My goal is to limit the number of IOps being performed to the disk.
Currently I use "PRAGMA synchronous = 1" and there is only one process
manipulating the database (multiple reader threads, only one writer
thread at one moment in time). Or if it is not possible to have WAL in
memory only, is there something like “PRAGMA wal_synchronous =
none_and_delete_wal_if_corrupted”?

Thanks for suggestions, kind regards,
Pavel


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In memory only WAL file

[Dan Kennedy]

On 04/05/2018 02:08 PM, Pavel Cernohorsky wrote:
Hello Dan, so you are saying that if WAL is somehow in memory only 
(not properly persisted) and app or OS crashes in the middle of the 
checkpoint operation, my main database file will get corrupted? And by 
corrupted you mean as in "you will loose changes which were in the WAL 
file", or "you will end up with unusable main database file, or file 
where rows which were affected by the checkpoint will have wrong 
contents (halfway through written, ...)". In other words, I may end up 
with the main database file in some other state than just "like before 
checkpointing" or "like after checkpointing"? I understood 
checkpointing as a kind of atomic operation which "merges data in the 
main database file and in the WAL". Is that understanding wrong?


That's correct. If you crash mid-checkpoint and lose the wal file, some 
future queries may return inconsistent results or SQLITE_CORRUPT errors.


The suggestion made in another post to put the wal file on a tmpfs or 
similar file-system is a good one. Then you will only risk corruption if 
the OS crashes. There is no easy way to do that at the moment though, 
you will have to do some hacking to get it to work.


Dan.







Thanks, Pavel


On 04/04/2018 06:33 PM, Dan Kennedy wrote:

On 04/04/2018 09:01 PM, Pavel Cernohorsky wrote:
Hello, does anybody know if there is some possibility to not have 
WAL file as a normal file on the disk, but only in memory? I 
understand that all the modifications to the database would get lost 
in case of the application / OS crash, but for my application, I 
only need the level of durability based on checkpointing. I just 
need to guarantee that all the data are properly written to the main 
database and synchronized to disk when manual (or even automatic) 
WAL checkpoint is called, but I do not care if I loose data in 
between the checkpoints. Of course database should never get corrupted.


If your app or the OS crashes halfway through a checkpoint and this 
means that the WAL file is lost, the database is likely to be 
corrupted. Is that a problem?


Dan.




My goal is to limit the number of IOps being performed to the disk. 
Currently I use "PRAGMA synchronous = 1" and there is only one 
process manipulating the database (multiple reader threads, only one 
writer thread at one moment in time). Or if it is not possible to 
have WAL in memory only, is there something like “PRAGMA 
wal_synchronous = none_and_delete_wal_if_corrupted”?


Thanks for suggestions, kind regards,
Pavel


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In memory only WAL file

[Pavel Cernohorsky]

Well, so you are saying that I should use single transaction for the 
"checkpoints"? Unfortunately, that wouldn't play really well with those 
multiple database readers I mentioned - those readers would not see 
changes done by the writer until the transaction finishes (which can be 
several seconds in between the "checkpoints") and that is not an option. 
Also, documentation states that:


> "If the application using SQLite crashes in the middle of a 
transaction when the MEMORY journaling mode is set, then the database 
file will very likely go corrupt"


Which is, as I mentioned not acceptable. Or, it depends, what is meant 
by corruption here. As I mentioned, I can loose D from ACID in between 
the checkpoints, I need the other ones.


Pavel


On 04/04/2018 07:15 PM, Simon Slavin wrote:

On 4 Apr 2018, at 3:01pm, Pavel Cernohorsky  
wrote:


Hello, does anybody know if there is some possibility to not have WAL file as a 
normal file on the disk, but only in memory? I understand that all the 
modifications to the database would get lost in case of the application / OS 
crash, but for my application, I only need the level of durability based on 
checkpointing. I just need to guarantee that all the data are properly written 
to the main database and synchronized to disk when manual (or even automatic) 
WAL checkpoint is called, but I do not care if I loose data in between the 
checkpoints. Of course database should never get corrupted.

You may be mistaking what checkpoints are for.  They should not be used as an 
ACID barrier.

In your situation, I recommend you use

 PRAGMA journal_mode = MEMORY

and where you write 'checkpoint' above, that's where you do a COMMIT.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In memory only WAL file

[Pavel Cernohorsky]

And how do you change location of the WAL file? Plus, will the main 
database file really get corrupted (as in Consistency corrupted, not 
only Durability of the data which were in the WAL file) if the OS crashes?


Pavel

On 04/04/2018 07:07 PM, Peter Da Silva wrote:

You could put the WAL in a tmpfs/ramfs so the DB would only get corrupted if 
the OS crashed, it'd still be there for recovering from application crashes.
  


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In memory only WAL file

[Pavel Cernohorsky]

Hello Dan, so you are saying that if WAL is somehow in memory only (not 
properly persisted) and app or OS crashes in the middle of the 
checkpoint operation, my main database file will get corrupted? And by 
corrupted you mean as in "you will loose changes which were in the WAL 
file", or "you will end up with unusable main database file, or file 
where rows which were affected by the checkpoint will have wrong 
contents (halfway through written, ...)". In other words, I may end up 
with the main database file in some other state than just "like before 
checkpointing" or "like after checkpointing"? I understood checkpointing 
as a kind of atomic operation which "merges data in the main database 
file and in the WAL". Is that understanding wrong?


Thanks, Pavel


On 04/04/2018 06:33 PM, Dan Kennedy wrote:

On 04/04/2018 09:01 PM, Pavel Cernohorsky wrote:
Hello, does anybody know if there is some possibility to not have WAL 
file as a normal file on the disk, but only in memory? I understand 
that all the modifications to the database would get lost in case of 
the application / OS crash, but for my application, I only need the 
level of durability based on checkpointing. I just need to guarantee 
that all the data are properly written to the main database and 
synchronized to disk when manual (or even automatic) WAL checkpoint 
is called, but I do not care if I loose data in between the 
checkpoints. Of course database should never get corrupted.


If your app or the OS crashes halfway through a checkpoint and this 
means that the WAL file is lost, the database is likely to be 
corrupted. Is that a problem?


Dan.




My goal is to limit the number of IOps being performed to the disk. 
Currently I use "PRAGMA synchronous = 1" and there is only one 
process manipulating the database (multiple reader threads, only one 
writer thread at one moment in time). Or if it is not possible to 
have WAL in memory only, is there something like “PRAGMA 
wal_synchronous = none_and_delete_wal_if_corrupted”?


Thanks for suggestions, kind regards,
Pavel


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In memory only WAL file

[Pavel Cernohorsky]

Hello Jens and Warren,

Performance is really one of the reasons, second is concurrent writer 
and readers (which can be in fact viewed as part of the performance).


Pavel


On 04/04/2018 06:57 PM, Jens Alfke wrote:



On Apr 4, 2018, at 9:01 AM, Warren Young  wrote:

Why turn on WAL mode at all, then?

Performance, probably. An in-memory WAL would scream.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users