Force readonly when connecting to sqlite file

Question

On many front ends, I wish to enforce my scripts using a readonly connection to a sqlite3 file mirrored from a master server.

Is there's a way to say DBI to do this ?

For the moment, I'm doing this:

       $dbHand = DBI->connect("dbi:SQLite:dbname=$dbName", $dbUser, $dbPass, {
            PrintError => 0,
            RaiseError => 1,
            AutoCommit => 1,
    }) or die $DBI::errstr;

And get full access.

• I'm not perl fluent, I have to maintain/evolve existing
• (I don't want to deal with file permissions)

Correlative

You can check metacpan.org/pod/DBD::SQLite#dbh-sqlite_set_authorizer-code_ref – Mitchiner

Answer 1

Use the sqlite_open_flags attribute as explained in the DBD::SQLite documentation:

use DBD::SQLite;
my $dbh = DBI->connect("dbi:SQLite:$dbfile", undef, undef, {
  sqlite_open_flags => DBD::SQLite::OPEN_READONLY,
});

This will cause an error if you try to open a database that doesn't exist (normally, a new database would be created) or if you try to write to an existing database.

Note that you have to explicitly use DBD::SQLite; in order to use the constant DBD::SQLite::OPEN_READONLY.

---

Be aware that DBI provides a ReadOnly handle attribute, but DBD::SQLite doesn't honor it prior to v1.49_05:

use strict;
use warnings;

use Data::Dump;
use DBI;

my $db = 'foo.db';
unlink $db if -f $db;

my $dbh = DBI->connect("dbi:SQLite:dbname=$db",'','', {
    RaiseError => 1,
    ReadOnly   => 1
});

$dbh->do( q{CREATE TABLE foo(id INTEGER, name TEXT)} );
$dbh->do( q{INSERT INTO foo VALUES(1, 'foo')} );
my $values = $dbh->selectall_arrayref( q{SELECT * FROM foo} );

dd $values;

Outputs:

[[1, "foo"]]

Switching to sqlite_open_flags => DBD::SQLite::OPEN_READONLY causes errors.

If the database doesn't exist:

DBI connect('dbname=foo.db','',...) failed: unable to open database file

If the database exists:

DBD::SQLite::db do failed: attempt to write a readonly database

---

As of DBD::SQLite v1.49_05, you can also use the ReadOnly attribute, but only as an option to connect. Setting the attribute after connecting doesn't work and throws a warning:

my $dbh = DBI->connect("dbi:SQLite:dbname=$db",'','', {
    RaiseError => 1
});
$dbh->{ReadOnly} = 1;

Outputs:

DBD::SQLite::db STORE warning: ReadOnly is set but it's only advisory

Phooey

I noticed that the DBD::SQLite docs made no mention of the ReadOnly option and instead showed a completely different way to do it; that made me curious. I opened a ticket because DBD::SQLite is not keeping its end of the bargain here...it doesn't even warn that ReadOnly is ignored! – Phooey

Answer 2

Just set

$dbHand ->{ReadOnly} = 1

as described here

Hypo

Unfortunately, DBD::SQLite doesn't appear to honor this. See my answer. – Phooey

@Overnuts: Please take note of the comment and solution from ThisSuitIsBlackNot. It looks like this standard solution is wrong for SQLite – Hypo

This has been fixed in DBD::SQLite 1.49_05 (developer release). Unfortunately, you have to set the attribute as an option to connect; setting it on the handle after connecting doesn't work and throws a warning. – Phooey