Drop support for SQLite

[rod-glover]

Reasons to drop support for SQLite:

1. Production databases are all PostgreSQL.
2. Unit tests are almost all now in PostgreSQL (courtesy of package testing.postgresql). There are a few lingering legacy unit tests that use SQLite, but they appear to be redundant to the PostgreSQL unit tests. The few that aren't can easily be added to that set.
3. Supporting SQLite adds overhead since its feature set is smaller than PostgreSQL. This bites particularly hard in migration code, where very different special cases have to be made for the different database engines.
4. For development (e.g., for dev test modelmeta databases), it is not hard to set up a localhost Postgres server. Alternatively, we could set up a permanent dev/staging Postgres database server on our existing infrastructure and use that.

Reasons to retain support for SQLite:

1. ???

[corviday]

SQLite's isolation is really convenient for testing indexing. I have a localhost Postgres, but it's not as smooth a testing workflow as SQLite. I have to run queries to delete entries for a file and whatever it references, like spatial reference, from the database each time I want to test indexing, because otherwise the indexer notices the file / srid / variable / whatever is already in the database and doesn't update the values I am tinkering with. My workflow would be considerably faster with SQLite - I'd just make a new copy of the empty database each time I wanted a fresh test to avoid contending with the wrong values left over from previous tests.

That said, it sounds like SQLite has become more trouble than it's worth overall.

[rod-glover]

Hmm. Wonder if there's an easy way to clone a base database in Postgres / pgAdmin. I know a somewhat laborious way :(

[rod-glover]

Several options here. Not quite as easy as ctrl+C, ctrl+V, but pretty easy.

[jameshiebert]

Pretty easy as long as no one is working on the database at the time. Please do not run the pg_terminate_backend() against our production database!

[jameshiebert]

I have to say, that I also really like the isolation and easy of use of SQLite for a development work flow. Sure, it's relatively easy to stand up PostgreSQL on your workstation, but it's trivial to stand up SQLite. And I find that we're more productive when we're less attached to a single system. So if you set up PostgreSQL on your workstation, and then you can't move your code to another system because it doesn't have PG on it... you get the idea. We actually run into this case quite a bit when we want to develop on our workstations, and then test the code on compute nodes that actually have the bulk data in them. We can't set up PG on the compute nodes (well, we can, but there's more friction).

I appreciate that it's a pain to maintain migration scripts for two DMBSes, but are there that many things in our code base where SQLAlchmey fails to abstract the database differences?

[rod-glover]

New information to consider:

1. Matthew's excellent work on dockerizing a Postgres server has made spinning up a test Postgres database really easy, either on a local machine on or on a docker-dev server.

2. PostGIS cannot be applied to a SQLite database. See my comments there about how to continue supporting SQLite with the equivalent of what comes with Postgis. TL;DR: We can do it if we want, with some, but not prohibitive, effort.

3. There aren't that many differences between Postgres and SQLite that SQLAlchemy doesn't abstract. In migrations, however, the differences do definitely bite harder; for example, see PR https://github.com/pacificclimate/modelmeta/pull/10 and https://github.com/pacificclimate/modelmeta/issues/68#issuecomment-419266047.

In summary:

1. There is now one less reason to continue supporting SQLite.
2. It is not that hard to continue supporting SQLite if we want to.

[rod-glover]

We will drop SQLite support.