SQLite pragmas

[Isaac Jurado]

Hello,

Yesterday I managed to make some SQLite pragmas work automatically by
following this simple recipie:

http://stackoverflow.com/questions/4534992/place-to-set-sqlite-pragma-option-in-django-project

But it has come to my mind that this could be improved slightly, at
least for the pragma case. Therefore, please consider the following
proposal: the addition of a new 'PRAGMAS' item in the DATABASES setting
dictionary. For example:

DATABASES = {
'default': {
'ENGINE': 'django.db.backends.sqlite3',
'NAME': '/path/to/file.sqlite',
'PRAGMAS': (
('page_size', 4096),
('journal_mode', "WAL"),
('cache_size', 5000)
)
}
}

This is very easy to implement with the following patch on current
master branch:

diff --git a/django/db/backends/sqlite3/base.py b/django/db/backends/sqlite3/base.py
index f4fd1cc..94a4197 100644
--- a/django/db/backends/sqlite3/base.py
+++ b/django/db/backends/sqlite3/base.py
@@ -318,7 +318,10 @@ class DatabaseWrapper(BaseDatabaseWrapper):
return conn

def init_connection_state(self):
- pass
+ pragmas_list = settings_dict.get('PRAGMAS', ())
+ if pragmas_list:
+ pragmas = "\n".join("PRAGMA %s=%s;" % item for item in pragmas_list)
+ self.connection.cursor().executescript(pragmas)

def _sqlite_create_connection(self):
conn_params = self.get_connection_params()

The practical advantage of this approach over using the signaling
machinery is that it can enable automatic pragmas also for SQLite
dbshells: write a temporary file with the pragmas and pass the file to
the SQLite shell with the "-init" option.

Some other notes:

- This is only for the sqlite3 backend.

- Pragmas in SQLite are usually used for tuning purposes, which can be
considered as part of the configuration. That's why I think this
change could make sense.

- My first attempt used a dictionary, as the value for 'PRAGMAS',
instead of a list/tuple. However, even the order of the pragmas seems
to be relevant. In particular, the "page_size" pragma is critically
sensitive to it.

- The liberal use of self.connection is likely wrong, but I didn't want
to invest more time until I had some feedback about the interest on
implementing this.

Thanks for your time. Best regards.

--
Isaac Jurado

"The noblest pleasure is the joy of understanding."
Leonardo da Vinci