CODEWITHSUNDEEP
pythondjangosqlite
Christopher Pearson
Christopher Pearson

Reputation: 1193

Why does Django not use Pragma statement to find Primary Key in sqlite introspection?

I am writing a simple database wrapper for sqlite as a learning experience. In doing so, I have been reading code for other, more complicated programs like Django. In the django.db.backends.sqlite3.introspection module, there is a method called get_primary_key_column in the DatabaseIntrospection class which is using re.search to find the primary key:

def get_primary_key_column(self, cursor, table_name):
        """
        Get the column name of the primary key for the given table.
        """
        # Don't use PRAGMA because that causes issues with some transactions
        cursor.execute("SELECT sql FROM sqlite_master WHERE tbl_name = %s AND type = %s", [table_name, "table"])
        row = cursor.fetchone()
        if row is None:
            raise ValueError("Table %s does not exist" % table_name)
        results = row[0].strip()
        results = results[results.index('(') + 1:results.rindex(')')]
        for field_desc in results.split(','):
            field_desc = field_desc.strip()
            m = re.search('"(.*)".*PRIMARY KEY( AUTOINCREMENT)?$', field_desc)
            if m:
                return m.groups()[0]
        return None

When I first saw this, I thought, "Now why don't they just use pragma table_info(foo) and pick out the pk from the last items of the returned tuple set?" Then I saw the comment # Don't use PRAGMA because that causes issues with some transactions which threw me for a loop. So my question is why not use the pragma statement? what issues could that possibly cause?

Upvotes: 0

Views: 269

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1124788

The sqlite3 library implicitly commits an active transaction when issuing a PRAGMA statement and that caused issues with the Django tests.

See this entry on the original issue that introduced the comment:

Just to ensure nobody wastes any undue time on this, I've tracked down the problem with the SQLite implementation of this.

What's happening is check_constraints() is calling get_indexes() on the SQLite Introspection class. The implementation of get_indexes() executes a PRAGMA statement (a few actually).

From what I've been able to determine by testing a few things out, PRAGMA is the problem. It appears that calling PRAGMA is somehow committing the transaction prematurely. The result is that the fixtures being loaded here are being carried over test to test.

I can't find anything online about why PRAGMA statements would do this (or even whether they do). However, I did run these tests a bunch of times and the problem only arose once the PRAGMA statement was executed.

In the a.m. I can re-think this patch a bit and perhaps just focus on MySQL for the time being.

Emphasis mine; the Django devs found transaction issues during testing; the PRAGMA appeared to trigger a transaction commit.

It could be that the sqlite3 documentation was updated after those events; currently this behaviour is documented:

So if you are within a transaction and issue a command like CREATE TABLE ..., VACUUM, PRAGMA, the sqlite3 module will commit implicitly before executing that command. There are two reasons for doing that. The first is that some of these commands don’t work within transactions. The other reason is that sqlite3 needs to keep track of the transaction state (if a transaction is active or not).

Emphasis mine, again.

Upvotes: 2

Related Questions