How can I write to a SQLite database file in a SourceForge project's web space?

I have a small Perl-based CGI application, which I am running in the project web space provided for a SourceForge project. This application stores d开发者_运维知识库ata in a SQLite (v. 3) database file.

When I run test scripts from a shell, I can read from and write to this SQLite file. However, when the CGI code is executed by Apache, it has read-only access. Write operations result in a log file error:

error.log.web-2:[Wed Oct 27 14:40:22 2010] [error] [client 127.0.0.1] DBD::SQLite::db do failed: unable to open database file

For testing purposes, I have cranked the permissions for that SQLite file all the way up to 777. No difference.

However, there are some funny caveats to SourceForge's project web space, and I wonder if I'm being tripped up by that. Generally, the main web server filesystem is read-only to Apache. If you have files that need to be writable at runtime, you're supposed to store them in a special "persistent" directory elsewhere... and create symlinks from your web space to the actual files under that directory.

I have done this, and the permissions are set to 777 for both the symlink and the actual SQLite file under the "persistence" location. I know this mechanism works in general, because I'm doing the same thing with cache and log files and it works there.

I'm wondering if there's anything funky about SQLite itself, along the lines of it not wanting to open a symlink (rather than a raw file) for writing.

---

I believe the answer to this question is that it can't be done. Further research into SQLite tells me that the driver must get a lock on the database file before it can do any write operations. This type of lock cannot be obtained when the actual file is on a different machine with its filesystem cross-mounted.

I believe this is the case with SourceForge project web space hosting. It looks like the (writable) "persistent" directory is actually on a totally separate machine from the read-only web server filesystem.

In short, if you stumble across this question because you're having the same issue... either look for different web space hosting, or else it may be time to re-work your app and step up to MySQL or some other DB (SourceForge gives you free MySQL hosting anyway).

---

Another issue is if you have permissions for the specific db file but you don't have permission to make the temporary files in the directory. (Mixed permissions, or too restrictive permissions)

https://www.sqlite.org/tempfiles.html

If you can't write the temporary files then you can't do any writes on a sqlite database file. If you switch it to a :memory: database you could get by or maybe use the pragma mentioned by @bob.faist PRAGMA temp_store = MEMORY, but really you should diagnosis and fix the permissions problem if possible.

Use these commands to see if you have permission to write in those file locations.

ls -l app.db
getfacl app.db
ls -l -d .  # check the directory to see if you can write the temp files there
getfacl .

Use chmod or setfacl -m to fix the files or folders to let you write to them.

Also check your diskspace.

df -k

If it shows that your partition where the database file is located or is trying to write its files to are full, you could also get these kinds of issues.

Hope that helps.