Re: [sqlite] WAL questions

[Richard Hipp]

On Thu, Jul 8, 2010 at 12:45 PM, Richard Hipp  wrote:

>
>
> PRAGMA name.journal_mode=MODE; -- set the mode of database "name" to MODE.
> PRAGMA name.journal_mode;  -- report the mode of database "name".
> PRAGMA journal_mode; -- an alias for "PRAGMA main.journal_mode"
> PRAGMA journal_mode=MODE;  -- set the mode of all currently ATTACHed
> databases.
>

The latest version 3.7.0 pre-release snapshot does journal_mode as described
above.
http://www.sqlite.org/draft/download.html to get a copy for testing.  The
documentation at
http://www.sqlite.org/draft/pragma.html#pragma_journal_modehas been
updated as well.  Please report any problems to this list.  Thanks!



-- 
-
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL questions

[Richard Hipp]

On Thu, Jul 8, 2010 at 12:23 PM, Jay A. Kreibich  wrote:

> > First Proposed Change:
> >
> > (1) and (2) are the same.
> >
> > (3) PRAGMA journal_mode=MODE;  -- set the mode to MODE for all attached
> > databases.  Databases created by subsequent ATTACH use MODE.  Existing
> > databases added by ATTACH use MODE if and only if doing so does not
> require
> > them to change in or out of WAL mode.
>
> > (4) PRAGMA journal_mode;  -- report the most recent setting by (3).  Or
> if
> > (3) has never been used, work the same as "PRAGMA main.journal_mode;"
>
> >
>  Contexts are generally bad.  This is even more true if there might
>  ever be a way to change the compile time default journal mode to
>  anything other than "delete."  Then there is no way to get the default.
>

Agreed.

Since sending the prior email, internal discussion has moved us in the
direction of eliminating the "default journal mode" all together.  This
makes both the code and documentation smaller, which implies that it will
also make things easier to understand.

So probably we'll end up with something like this:

PRAGMA name.journal_mode=MODE; -- set the mode of database "name" to MODE.
PRAGMA name.journal_mode;  -- report the mode of database "name".
PRAGMA journal_mode; -- an alias for "PRAGMA main.journal_mode"
PRAGMA journal_mode=MODE;  -- set the mode of all currently ATTACHed
databases.

When new databases are ATTACH-ed, they come up in either DELETE or WAL mode
(depending on how they were last closed) and must be changed to whatever the
application desires using a new PRAGMA journal_mode call.  No more trying to
guess what the application wants and apply that as a default.

This is ever-so-slightly incompatible with prior SQLite versions, but on the
other hand, we never have promised to maintain PRAGMA compatibility, and if
we are going to change something, it seems like 3.6->3.7 is a good time to
change it.


-- 
-
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL questions

[Jay A. Kreibich]

On Thu, Jul 08, 2010 at 08:06:23PM +0400, Alexey Pechnikov scratched on the 
wall:
> 2010/7/8 Jay A. Kreibich 
> 
> > > It's not helpful for backward compability. How about version downgrade of
> > > the Android or some other mobile OS and as result impossibility to open
> > > any SQLite database?..
> >
> >   That's not backwards compatibility (newer versions working with items
> >  from older environments), that's forwards compatibility (older versions
> >  working new items from a newer environment).
> >
> 
> I did speak about "Backwards Compatibility" chapter from
> http://www.sqlite.org/draft/wal.html

  Which pretty much says "if you turn this on, the file will not work
  with older versions until you turn it off."  What's the issue?

> > It is no different than a new(er) application that uses newer APIs
> > not working on an older version of the OS.
> 
> And how many such changes in POSIX file API?.. I think SQLite API as
> applications file format is similar to POSIX file API.

  Which is why you're using bleeding-edge features in an
  unreleased development version?

  If it is just a file API, use the system libs in their default
  configuration and be done with it.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL questions

[Jay A. Kreibich]

On Thu, Jul 08, 2010 at 08:23:55AM -0400, Richard Hipp scratched on the wall:
> On Thu, Jul 8, 2010 at 7:10 AM, Richard Hipp  wrote:
> 
> > Though, I will admit, this is confusing, and I was thinking last night
> > about ways we could possibly change it
> 
> Current behavior:
> 
> (1) PRAGMA name.journal_mode=MODE; -- set the mode to MODE for database
> "name".
> 
> (2) PRAGMA name.journal_mode; -- return current journal mode for database
> "name".
> 
> (3) PRAGMA journal_mode=MODE;  -- set the mode to MODE for all attached
> databases and also apply MODE to all databases ATTACH-ed in the future.
> 
> (4) PRAGMA journal_mode; -- report the most recent setting by (3).  Report
> "delete" if there have been no prior occurences of (3).
> 
> First Proposed Change:
> 
> (1) and (2) are the same.
> 
> (3) PRAGMA journal_mode=MODE;  -- set the mode to MODE for all attached
> databases.  Databases created by subsequent ATTACH use MODE.  Existing
> databases added by ATTACH use MODE if and only if doing so does not require
> them to change in or out of WAL mode.

> (4) PRAGMA journal_mode;  -- report the most recent setting by (3).  Or if
> (3) has never been used, work the same as "PRAGMA main.journal_mode;"

  Contexts are generally bad.  This is even more true if there might
  ever be a way to change the compile time default journal mode to
  anything other than "delete."  Then there is no way to get the default.


  There are a few pragmas that use this general approach... where
  getting/setting an unqualified setting changes the default, not
  "main".  It might be better if all unqualified statements refer
  to main, and pragmas like this have a default.journal_mode (get/set)
  or all.journal_mode (set) syntax.


> Second Proposed Change:
> 
> Remove the WAL mode from "PRAGMA journal_mode".  The journal_mode pragma
> only specifies the various rollback journal modes.  Enable the WAL using a
> separate pragma such as  "PRAGMA wal=ON; PRAGMA wal=OFF;"
>
> Thoughts?  Comments? 

  I like this.  I suspect in the future we will have a "PRAGMA
  wal_mode" as well, so this makes a bit more sense.

  But as an extension, does it make even more sense to have "PRAGMA
  wal=[on|off]" or "PRAGMA transaction=[wal|journal]" ? 




> Other suggestions?

  This has nothing to do with WAL, but it might be nice to expose
  the logic that does SQL-type => SQLite-affinity mappings
  (i.e. sqlite3AffinityType()):

int sqlite3_get_affinity( const char *type );

  Takes a string that contains an SQL type.  Returns one of:

  SQLITE_AFF_TEXT, SQLITE_AFF_NONE, SQLITE_AFF_NUMERIC,
  SQLITE_AFF_INTEGER, SQLITE_AFF_REAL.


   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL questions

[Alexey Pechnikov]

2010/7/8 Jay A. Kreibich 

> > It's not helpful for backward compability. How about version downgrade of
> > the Android or some other mobile OS and as result impossibility to open
> any
> > SQLite database?..
>
>   That's not backwards compatibility (newer versions working with items
>  from older environments), that's forwards compatibility (older versions
>  working new items from a newer environment).
>

I did speak about "Backwards Compatibility" chapter from
http://www.sqlite.org/draft/wal.html

 It is no different than a new(er) application that uses newer APIs
>  not working on an older version of the OS.


And how many such changes in POSIX file API?.. I think SQLite API as
applications file format is similar to POSIX file API.

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL questions

[Jay A. Kreibich]

On Thu, Jul 08, 2010 at 06:50:52PM +0400, Alexey Pechnikov scratched on the 
wall:
> 2010/7/8 Richard Hipp 
> 
> > In the current implementation, if you call "PRAGMA wal_checkpoint" just
> > prior to closing the database, the WAL file will be deleted automatically.
> > But it keeps the database in WAL mode, so the WAL is recreated the next
> > time you open and write to the database.
> 
> It's not helpful for backward compability. How about version downgrade of
> the Android or some other mobile OS and as result impossibility to open any
> SQLite database?..

  That's not backwards compatibility (newer versions working with items
  from older environments), that's forwards compatibility (older versions
  working new items from a newer environment).
  
  It is no different than a new(er) application that uses newer APIs
  not working on an older version of the OS.  If you want the ability
  to do this, don't use new features (or turn it off every time you
  close the database).

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL questions

[Richard Hipp]

On Thu, Jul 8, 2010 at 10:50 AM, Alexey Pechnikov wrote:

> 2010/7/8 Richard Hipp 
>
> > In the current implementation, if you call "PRAGMA wal_checkpoint" just
> > prior to closing the database, the WAL file will be deleted
> automatically.
> > But it keeps the database in WAL mode, so the WAL is recreated the next
> > time
> > you open and write to the database.
>
>
> It's not helpful for backward compability. How about version downgrade of
> the Android or some other mobile OS and as result impossibility to open any
> SQLite database?..
>

Presumably the version downgrade comes with a script.  (Otherwise, other
changes such as schema changes would go uncorrected.)  That script merely
has to run

 PRAGMA journal_mode=DELETE;

on every database.  Problem solved.


>
> --
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
-
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL questions

[Alexey Pechnikov]

2010/7/8 Richard Hipp 

> In the current implementation, if you call "PRAGMA wal_checkpoint" just
> prior to closing the database, the WAL file will be deleted automatically.
> But it keeps the database in WAL mode, so the WAL is recreated the next
> time
> you open and write to the database.


It's not helpful for backward compability. How about version downgrade of
the Android or some other mobile OS and as result impossibility to open any
SQLite database?..

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL questions

[Richard Hipp]

On Thu, Jul 8, 2010 at 10:15 AM, Alexey Pechnikov wrote:

> >
> > Remove the WAL mode from "PRAGMA journal_mode".  The journal_mode pragma
> > only specifies the various rollback journal modes.  Enable the WAL using
> a
> > separate pragma such as  "PRAGMA wal=ON; PRAGMA wal=OFF;"
> >
>
> It's more clean I think. With wal=on and journal_mode=delete SQLite may
> delete WAL journal before the last connection is closed and so provide
> backward compability (of cource, only new SQLite versions can restore the
> crashed databases). With wal=on and journal_mode=persist SQLite may use
> persistent WAL journal without backward compability reasons.
>

In the current implementation, if you call "PRAGMA wal_checkpoint" just
prior to closing the database, the WAL file will be deleted automatically.
But it keeps the database in WAL mode, so the WAL is recreated the next time
you open and write to the database.

-- 
-
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL questions

[Alexey Pechnikov]

>
> Remove the WAL mode from "PRAGMA journal_mode".  The journal_mode pragma
> only specifies the various rollback journal modes.  Enable the WAL using a
> separate pragma such as  "PRAGMA wal=ON; PRAGMA wal=OFF;"
>

It's more clean I think. With wal=on and journal_mode=delete SQLite may
delete WAL journal before the last connection is closed and so provide
backward compability (of cource, only new SQLite versions can restore the
crashed databases). With wal=on and journal_mode=persist SQLite may use
persistent WAL journal without backward compability reasons.
-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL questions

[Max Vlasov]

Second Proposed Change:

>
> Remove the WAL mode from "PRAGMA journal_mode".  The journal_mode pragma
> only specifies the various rollback journal modes.  Enable the WAL using a
> separate pragma such as  "PRAGMA wal=ON; PRAGMA wal=OFF;"
>
> Thoughts?  Comments?  Other suggestions?
>
>
Maybe it's not the right time, but can it be implemented with separated
pragma as your second proposed change but with more options like OFF,
PERSISTENT, TEMPORAL. Still thinking about the fact that WAL being a new
software feature and actually becomes a new file format change, the latter
option can solve this (if it's technically possible and not hard to append
of course). The logic can be like this, if WAL=TEMPORAL, the first
reader/writer that accesses the base makes the changes to 18,19 bytes (=2)
allowing it and following reader/writers work in WAL mode and the last
reader/writer that closes the base, reverts it back (=1) allowing the file
format stays the same. Sure there are possible cases when the format still
changed (for example unexpected program end), but the next successful
open/close will fix this.

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

Re: [sqlite] WAL questions

[Richard Hipp]

On Thu, Jul 8, 2010 at 7:10 AM, Richard Hipp  wrote:

> Though, I will admit, this is confusing, and I was thinking last night
> about ways we could possibly change it
>


Current behavior:

(1) PRAGMA name.journal_mode=MODE; -- set the mode to MODE for database
"name".

(2) PRAGMA name.journal_mode; -- return current journal mode for database
"name".

(3) PRAGMA journal_mode=MODE;  -- set the mode to MODE for all attached
databases and also apply MODE to all databases ATTACH-ed in the future.

(4) PRAGMA journal_mode; -- report the most recent setting by (3).  Report
"delete" if there have been no prior occurences of (3).

First Proposed Change:

(1) and (2) are the same.

(3) PRAGMA journal_mode=MODE;  -- set the mode to MODE for all attached
databases.  Databases created by subsequent ATTACH use MODE.  Existing
databases added by ATTACH use MODE if and only if doing so does not require
them to change in or out of WAL mode.

(4) PRAGMA journal_mode;  -- report the most recent setting by (3).  Or if
(3) has never been used, work the same as "PRAGMA main.journal_mode;"

Second Proposed Change:

Remove the WAL mode from "PRAGMA journal_mode".  The journal_mode pragma
only specifies the various rollback journal modes.  Enable the WAL using a
separate pragma such as  "PRAGMA wal=ON; PRAGMA wal=OFF;"

Thoughts?  Comments?  Other suggestions?

-- 
-
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL questions

[Richard Hipp]

On Thu, Jul 8, 2010 at 7:51 AM, Max Vlasov  wrote:

> So all his current code base works once it started using this version of
> sqlite, but consequently small (or maybe large part) of his bases becomes
> WAL-enabled (number 2 in the file format). The latter may appear because of
> his own WAL-On without WAL-Off or WAL-On with unexpected program
> interruption. Everything is ok, until these bases have to be
> distributed/used in other enviroments, that probably use lower than 3.7
> versions of sqlite3, so it stops working claming about unknown file format.
>


Correct.  Older versions of SQLite cannot read or write a database file that
has a non-empty WAL, since older versions of SQLite do not know what to do
with the WAL.  There is nothing really we can do about this, other than not
provide WAL as an option.



-- 
-
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL questions

[Max Vlasov]

> You want "PRAGMA main.journal_mode"
>
> A "PRAGMA journal_mode;" (without the "main.") shows you the default
> journal
> mode used by newly created databases, which is always "DELETE" unless you
> have changed it with a prior "PRAGMA journal_mode=MODE" command.
>
> Though, I will admit, this is confusing, and I was thinking last night
> about
> ways we could possibly change it
>

Thanks for pointing out. I have two observations
- According to docs this was present for ages so it's a shame on me not to
knowing it in the first place. So probably it should stay as it is now.

- But this is the first time when this important pragma really affects the
sqlite file format and what it more important, not only for current session.
You probably had reasons for implementing WAL the way it is now, but
imagine, this setting once changed don't remind of itself for the developer.
So all his current code base works once it started using this version of
sqlite, but consequently small (or maybe large part) of his bases becomes
WAL-enabled (number 2 in the file format). The latter may appear because of
his own WAL-On without WAL-Off or WAL-On with unexpected program
interruption. Everything is ok, until these bases have to be
distributed/used in other enviroments, that probably use lower than 3.7
versions of sqlite3, so it stops working claming about unknown file format.
I think it's a potential way of new wave of mass bug/missing reporting or
simply confusion

I may exaggerate, I suggest other participants of the list share their
thoughts

Thanks,

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

Re: [sqlite] WAL questions

[Richard Hipp]

On Thu, Jul 8, 2010 at 12:45 AM, Max Vlasov  wrote:

> Alexey,
>
> I read this sentence, but it didn't help.
> So I suppose there's a bug in PRAGMA journal_mode logic
> Steps to reproduce.
>
> 1. Create an empty base with some table.
> Look at the 18,19 offsets, they both = 1, it's ok, the base is compatible
> with full range of sqlite3 versions.
>
> 2. Do PRAGMA journal_mode=WAL; , it returns wal, so WAL is set.
> Look at the 18,19 offsets, they both = 2, the base no longer compatible
> with
> older versions, checking... yes, they say "encrypted" or something.
> Query PRAGMA journal_mode; alone (just to check not set) , it still says
> "wal", ok
>
> 3. Close the database. offsets 18,19 still = 2, so the WAL setting are kept
> between sessions.
>
> 4. Open the db again, offsets 18,19 still = 2,
> query PRAGMA journal_mode; it says  "delete", but definetely should return
> "wal".
>

You want "PRAGMA main.journal_mode"

A "PRAGMA journal_mode;" (without the "main.") shows you the default journal
mode used by newly created databases, which is always "DELETE" unless you
have changed it with a prior "PRAGMA journal_mode=MODE" command.

Though, I will admit, this is confusing, and I was thinking last night about
ways we could possibly change it



>
> Max
>
>
> On Thu, Jul 8, 2010 at 12:09 AM, Alexey Pechnikov  >wrote:
>
> > See http://sqlite.org/draft/wal.html :
> >
> > "An SQLite database _connection_
> > defaults
> > to journal_mode=DELETE. To convert to WAL mode, use the following
> > pragma: PRAGMA journal_mode=WAL;"
> >
> > --
> > Best regards, Alexey Pechnikov.
> > http://pechnikov.tel/
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
-
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL questions

[Alexey Pechnikov]

I see this too:

$ sqlite3 grow.db 'pragma journal_mode'
delete

$ hexdump -s 17 -n 2 grow.db   | head -n1
011 0200

$ sqlite3 grow.db 'pragma journal_mode=delete'
delete

$ hexdump -s 17 -n 2 grow.db  | head -n1
011 0100

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL questions

[Max Vlasov]

Alexey,

I read this sentence, but it didn't help.
So I suppose there's a bug in PRAGMA journal_mode logic
Steps to reproduce.

1. Create an empty base with some table.
Look at the 18,19 offsets, they both = 1, it's ok, the base is compatible
with full range of sqlite3 versions.

2. Do PRAGMA journal_mode=WAL; , it returns wal, so WAL is set.
Look at the 18,19 offsets, they both = 2, the base no longer compatible with
older versions, checking... yes, they say "encrypted" or something.
Query PRAGMA journal_mode; alone (just to check not set) , it still says
"wal", ok

3. Close the database. offsets 18,19 still = 2, so the WAL setting are kept
between sessions.

4. Open the db again, offsets 18,19 still = 2,
query PRAGMA journal_mode; it says  "delete", but definetely should return
"wal".

Max


On Thu, Jul 8, 2010 at 12:09 AM, Alexey Pechnikov wrote:

> See http://sqlite.org/draft/wal.html :
>
> "An SQLite database _connection_
> defaults
> to journal_mode=DELETE. To convert to WAL mode, use the following
> pragma: PRAGMA journal_mode=WAL;"
>
> --
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL questions

[Alexey Pechnikov]

See http://sqlite.org/draft/wal.html :

"An SQLite database _connection_ defaults
to journal_mode=DELETE. To convert to WAL mode, use the following
pragma: PRAGMA journal_mode=WAL;"

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users