On 22 Apr 2012, at 9:31am, Steinar Midtskogen <stei...@latinitas.org> wrote:
> This might be slightly off topic, but perhaps a common problem for > many sqlite users. Common problem, and it would be nice to have a solution. > I have a database (wal mode) that apache (the web server) needs to > access, readonly. Since it needs to be able to lock it for reading, > apache needs write access. So the database has these permissions: > > -rw-rw-r-- 1 user apache 1837704192 2012-04-22 09:58 database.db > > The directory is also group writeable. > > The trouble is that when apache is accessing the database, the > database file owner can't access it, not even for reading. The solution I came up with is that the database file owner also uses Apache to look at it: I use web-facing database administration software rather than opening the database in another application. (I wrote a simple one myself in PHP and JavaScript.) However this is unacceptable for some users. > The result > is "unable to open database file". I believe that the cause is that > apache creates these files: > > -rw-r--r-- 1 apache apache 32768 2012-04-22 10:15 database.db-shm > -rw-r--r-- 1 apache apache 0 2012-04-22 09:58 database.db-wal Take a look at "PRAGMA journal_mode": <http://www.sqlite.org/pragma.html#pragma_journal_mode> You're using WAL mode. DELETE mode is the default behaviour: when the last connection to the database is closed, the journal is deleted. But you can change this to TRUNCATE or some other value that suits you. That way, the files will not have to be remade. So then you would … 1) Use any app/interface to open the SQLite database. 2) Set the journal mode to, for example, TRUNCATE. 3) Have the app/interface close the database. 4) Using your operating system, set the protection on the journal files so that they can be accessed by whatever apps and users you want. 5) From then on, every app using the database must always remember to set that journal mode every time it opens the database. Otherwise it'll revert to DELETE. However, you're using WAL mode, and a useful change to SQLite might be a WAL PERSISTENT mode, perhaps by creating another option for the existing PRAGMA or by creating one PRAGMA for journal format and another for journal clearup. An alternative -- probably better -- solution would be for SQLite to check the permissions on the database file, and when it creates a journal file, set the same permissions for the new file. I am not a SQLite programmer and I don't know how difficult either of these solutions would be to implement. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
