omniscale/imposm3 — pull request #20 by skrach (2014-04-21 10:54:05+00:00)
Title: Support import into Spatialite database

# Introduction

This update adds the ability to import OSM data into a SpatiaLite database.

All current features of the PostGIS import (e.g. generalized tables, creation of indexes) are supported by the Spatialite import as well. The only thing missing is support for schema operations like Deployment and rotation between different schemas. This is because SpatiaLite has no concept of schemas.

As the changes I made are rather big, I'll explain them in detail as well as the reasoning behind them.

# Changes

## Refactoring of database/postgis package

The current package structure inside `database` has been changed. A new parent package `sql` has been introduced.

- `database/postgis -> database/sql/postgis` (this package has been moved below the new `sql` package)
- `database/sql/spatialite` (this new package contains spatialite logic)

The new `sql` package contains a lot of the previous postgis logic. Only code specific to postgis has been moved to the new sub package `postgis`. Analogously the new `spatialite` package contains code specific for the handling of a Spatialite database.

The reson for this refactoring is that a lot of database code is independent of the underlying SQL database. Other SQL based RDBMS would be placed as childs of this sql package as well.

For NoSQL databases a new package directly below the `database` package would need to be created.

The `sql` package implements the `database` interfaces defined in `database.go`.

## database/sql/sql.go (was postgis.go)

Inside the file `sql.go` (which previously was `postgis.go`) is the place of shared SQL logic. Most of the code will seem familiar. However to make this logic database agnostic the following changes have been made:

- The struct `PostGIS` has been renamed to SQLDB
- The constructor for creation of this struct has been moved to `postgis.go` and `spatialite.go`
- The code contains no SQL statements, and instead gets those statements by using a QueryBuilder (see below for details)

## database/sql/*/qb.go

The QueryBuilder interface provides a single place to define databse specific SQL statements. imposm3 already had this concept partially by using Specifications. The QueryBuilder interfaces are additions to this paradigma and put all SQL statements behind an interface.

Currently the following QueryBuilder interfaces exist (all defined in `sql.go`):

### QueryBuilder interfaces

#### QueryBuilder

The QueryBuilder interface provides methods not bound to a specific (generalized) table. This means that upon calling this methods, schema and table must be provided as arguments.

Examples include `TableExistsSQL(schema, table)`, `DropTableSQL(schema, table)` or `CreateGeometryIndexSQL(schema, table, column)`. The full list of methods can be found in `sql.go`

#### NormalTableQueryBuilder

This interface defines methods bound to normal (=non generalized) table instances. All methods have no arguments, as the required data is provided using the SQLDB struct.

- `InsertSQL()`
- `DeleteSQL()`
- `CreateTableSQL()`
- `AddGeometryColumnSQL()`
- `CopySQL()`

#### GenTableQueryBuilder

This interface defines methods bound to generalized table instances:

- `InsertSQL()`
- `InsertSQL()`

### Concurrency

Spatialite doesn't allow concurrent writing to a single database. Therefore writes can't be parallelized as during a PostGIS import. Therefore, the amount of workers is now a new property named `Worker` of the SQLDB struct which is set during initialization inside the `postgis.go` and `spatialite.go` files. For the SpatiaLite database import this value is set to `1`.

### Bulk Import

SpatiaLite doesn't allow bulk imports using it's API. A new property controlling this behaviour has been added as well (`SQLDB.BulkSupported`)

### Deployment Support

As SpatiaLite doesn't support schemas, deployment is not possible. Another property (`SQLDB.DeploymentSupported`) allows to enable or disable this feature. I know that the idea behind the current design of imposm3 checks if the Deployer interface is implemented to represent this behaviour, but since sql.go implements the database interface on it's own, this mechanism can't be used.

### Optimize()

Database optimization is specific to a database, therefore the logic for it has bene moved to `postgis.go` and `spatialite.go` as well. A function pointer (`SQLDB.Optimizer`) allows a database impolementation to run database specific code for this scenario. A QueryBuilder is not sufficient in this case as the statements will often vary. For example SpatiaLite has no concept of clustered geometric indexes like PostGIS.

# Final words

I know that this update contains a lot of changes. It was my intention to make them in a useful way so that no duplicate code is created and so that the current design of imposm3 is respected. Nonetheless it's entirely possible that you don't agree to some of the concepts I've introduced or have ideas how things could be built in a better way. I'm open if you've ideas or suggestions!

If you like I'm also happy to discuss this pull request via email, phone or Skype if you like.

I would be really thankful if you could take the time to see through these changes!