How can we map our schema into SQLite?

[simonwo]

Hi all,

So we've been looking at SQLite as a simple way of making our entire tariff database available under an open license. The motivations behind this are broadly that it is a container format that is extremely accessible (requires no ETL work to get started with the data), that we can embed custom view logic in, and that is easily enhancable (using e.g. Datasette).

I've been doing some spike work to understand how feasible this would be from an engineering perspective. The way I've been looking at this so far is to get Django to manage the schema just as it manages our PostgreSQL schema, such that Django will create all of the tables and will keep the SQLite schema up to date when we change our main schema. This makes it easy for us to maintain whilst also allowing downstream users to access our new schema straight away. It would also let us e.g. use querysets to put stuff into SQLite.

So far the issues I'm encountering are with SQLite's limited support for types. Django handles most of this except for where we've used the PostgreSQL-specific DateRange field – SQLite has no support for that. Ideally in SQLite land we would just have two separate columns, one for the lower and upper parts of the date range, and then we could call the date functions and also use BETWEEN syntax on them.

From what I've looked at though, turning one field into two columns is not really a thing Django will handle. I've also tried to see if Django would let you declare different fields depending on the database in use – again, not a thing as far as I can see. So I'm a bit stuck as to how we could work around the DateRange field – does anyone have any ideas?

[simonwo]

evil laugh

if settings.SQLITE:
    valid_between_start = DateField(db_index=True, null=True, blank=True)
    valid_between_end = DateField(db_index=True, null=True, blank=True)
else:
    valid_between = TaricDateRangeField(db_index=True)

[stuaxo]

Is that enough to make the importer happy enough to do an initial import from TARIC XML ?

Since we use TaricDateRangeField I wonder if that could have some magic for sqlite ?

[simonwo]

Yeah, I looked into if we could localise the logic into TaricDateRangeField and have it generate two fields if we are in SQLite mode. What I found was that a) you can't vary the fields based on the database connection, they are assumed to be constant across all databases, and b) Django really is wedded to the idea that 1 field == 1 database column. The switch above works well, but you have to run SQLite operations in a separate process.

I haven't actually done an import, and instead just copied data between Postgres and SQLite directly. It's "fast", only taking a couple of minutes, but certainly way faster than faffing with the importer.

[stuaxo]

That sounds good, I hadn't even looked into how to do that (though it wouldn't surprise me if somebody had written a SQLite backend for Postgres).

One of the more evil solutions I dismissed was some sort of code modification, and using that to generate a tamato-sqlite with the other kind of field(s).