Re: [sqlite] WAL: no longer able to use read-only databases?

2010-07-15 Thread Matthew L. Creech
On Thu, Jul 15, 2010 at 12:23 PM, Pavel Ivanov  wrote:
>> I don't think so.  Just like the older SQLite journal system, it's important 
>> that the WAL files survive through a crash.
>
> I believe WAL file is not a problem here (despite some confusing macro
> name that Matthew proposed). The problem is SHM file which don't have
> to survive - SQLite rebuilds it in case if it's missing.
>

Right, sorry for the confusing terminology.  The "-shm" file is what I
was referring to, since that's the part that needs to be writable even
for a read-only app, if I understand correctly.  I believe that it's
okay to put that in a ramdisk, issues with chroot() aside.

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


Re: [sqlite] WAL: no longer able to use read-only databases?

2010-07-15 Thread Pavel Ivanov
> I don't think so.  Just like the older SQLite journal system, it's important 
> that the WAL files survive through a crash.

I believe WAL file is not a problem here (despite some confusing macro
name that Matthew proposed). The problem is SHM file which don't have
to survive - SQLite rebuilds it in case if it's missing.


Pavel

On Thu, Jul 15, 2010 at 12:01 PM, Simon Slavin  wrote:
>
> On 15 Jul 2010, at 4:52pm, Matthew L. Creech wrote:
>
>> This exists in many Linux systems as "/dev/shm", or even "/tmp" would
>> work fine for a lot of users.
>
> I don't think so.  Just like the older SQLite journal system, it's important 
> that the WAL files survive through a crash.  SQLite finds the WAL file the 
> next time the database is opened, and uses the contents to restore the 
> database to a sane and useful state.  Most forms of Unix wipe the /tmp 
> directory during boot, so the WAL file would not survive.  And /dev/shm is 
> sometimes real RAM storage so naturally that will be empty after a boot too.
>
> These options work fine for read-only databases but read-only databases don't 
> actually need a WAL file at all.  Rather than spend time writing code to move 
> the WAL file to a different place, it makes more sense to spend that time 
> writing code so that a WAL file is not made at all for a read-only database.
>
> Simon.
> ___
> 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: no longer able to use read-only databases?

2010-07-15 Thread Simon Slavin

On 15 Jul 2010, at 4:52pm, Matthew L. Creech wrote:

> This exists in many Linux systems as "/dev/shm", or even "/tmp" would
> work fine for a lot of users.

I don't think so.  Just like the older SQLite journal system, it's important 
that the WAL files survive through a crash.  SQLite finds the WAL file the next 
time the database is opened, and uses the contents to restore the database to a 
sane and useful state.  Most forms of Unix wipe the /tmp directory during boot, 
so the WAL file would not survive.  And /dev/shm is sometimes real RAM storage 
so naturally that will be empty after a boot too.

These options work fine for read-only databases but read-only databases don't 
actually need a WAL file at all.  Rather than spend time writing code to move 
the WAL file to a different place, it makes more sense to spend that time 
writing code so that a WAL file is not made at all for a read-only database.

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


Re: [sqlite] WAL: no longer able to use read-only databases?

2010-07-15 Thread Matthew L. Creech
So if I'm reading the documentation correctly:


The wal-index is in shared memory, and so technically it does not have
to have a name in the host computer filesystem. Custom VFS
implementations are free to implement shared memory in any way they
see fit, but the default unix and windows drivers that come built-in
with SQLite implement shared memory using mmapped files named using
the suffix "-shm" and located in the same directory as the database
file.

...

Early (pre-release) implementations of WAL mode stored the wal-index
in volatile shared-memory, such as files created in /dev/shm on Linux
or /tmp on other unix systems. The problem with that approach is that
processes with a different root directory (changed via chroot) will
see different files and hence use different shared memory areas,
leading to database corruption.


It seems like the only thing preventing WAL from working with
read-only databases is this lack of a global namespace for shared
memory.

This exists in many Linux systems as "/dev/shm", or even "/tmp" would
work fine for a lot of users.  I totally understand that you can't
make this the default, because it could potentially lead to strange
behavior with chroot()s and the like.  But for those of us with
controlled environments who know that all applications using SQLite
share the same view of the filesystem, it would be great if we could
#define an option which turns this on.  For my application, chroot()ed
apps are a complete non-issue, whereas lack of read-only DB access is
a dealbreaker for WAL (which would really be a shame, the performance
benefit is substantial!)

I know I could always write my own VFS to do this, but that seems like
overkill.  :)  More importantly, it requires maintenance - I'd
probably create my VFS by copying os_unix.c, but then I wouldn't
automatically be getting any fixes/updates that you guys make to that
file going forward.

Best would be if SQLite had a #define like SQLITE_CUSTOM_WAL_LOCATION,
which defaults to undefined (and hence you use the same directory as
the DB file), but which users could define to "/dev/shm", "/tmp", or
some other location to place all the shm files there, globally.  The
changes in

http://www.sqlite.org/src/fdiff?v1=ae173c9f6afaa58b2833a1c95c6cd32021755c42=a76d1952ac7984574701c48b665220b871c5c9a5

are pretty straightforward, so I could probably take a stab at this if you want.

What do you guys think?

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


Re: [sqlite] WAL: no longer able to use read-only databases?

2010-07-13 Thread Max Vlasov
>
> > In testing the latest SQLite snapshot with WAL enabled, it seems that
> > there's no way to use a database in a read-only location.
> >
>
> Documentation on the developers' current view of this issue can be found
> here:
>
>
>
In my opinion it's ok, WAL already has special conditions on which it would
operate and the current documentation describes them all thoroughly.  I wish
only the "advantages" sections of WAL also grew a little, maybe giving more
details about speed improvement encouraging using this mode more frequently

Thanks

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


Re: [sqlite] WAL: no longer able to use read-only databases?

2010-07-13 Thread Richard Hipp
On Fri, Jul 9, 2010 at 3:21 PM, Matthew L. Creech wrote:

> In testing the latest SQLite snapshot with WAL enabled, it seems that
> there's no way to use a database in a read-only location.
>

Documentation on the developers' current view of this issue can be found
here:

http://www.sqlite.org/draft/wal.html#readonly

If you have any comments and complaints, please send them to this mailing
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


[sqlite] WAL: no longer able to use read-only databases?

2010-07-09 Thread Matthew L. Creech
In testing the latest SQLite snapshot with WAL enabled, it seems that
there's no way to use a database in a read-only location.  For
example, let's say I've created a database as root, then closed it
(cleanly):

$ ls -l /flash/alarms.db*
-rw-r--r--1 root root 36864 Jan  1 00:14 /flash/alarms.db

If I try as another user to use that database, I get an error:

$ sqlite3 /flash/alarms.db
SQLite version 3.7.0
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> PRAGMA journal_mode;
Error: unable to open database file
sqlite>

However, if I create a symlink to that database in a location that I
have write access to, then everything works fine:

$ ln -s /flash/alarms.db ./alarms.db
$ ls -l alarms.db*
lrwxrwxrwx1 adminadmin   16 Jan  1 00:15 alarms.db ->
/flash/alarms.db
$ sqlite3 alarms.db
SQLite version 3.7.0
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> PRAGMA journal_mode;
wal
sqlite> .quit
$ ls -l alarms.db*
lrwxrwxrwx1 adminadmin   16 Jan  1 00:15 alarms.db ->
/flash/alarms.db
-rw-r--r--1 adminadmin32768 Jan  1 00:15 alarms.db-shm
-rw-r--r--1 adminadmin0 Jan  1 00:15 alarms.db-wal

So clearly this is a side-effect of WAL creation, which happens in the
same directory as the database file.

This doesn't seem like it should fundamentally be any different than
normal journaling mode, in that opening a database in read-only mode
makes the creation of a journal / WAL unnecessary.  But I'm not
familiar with the WAL internals, so maybe there's more to it.  FYI,
this works fine with normal journaling mode (we bumped in existing
code after changing the journal_mode).

Any additional flags or ways of doing this that I'm missing?  Or is it a bug?

Thanks!

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