[Qgis-developer] QGIS database management

[Vincent Picavet]

Hi,
One of the effort having to be discussed at QGIS Hackfest is the way QGIS 
handles databases, especially database connexion and layers.
Here is a little sum-up of current QGIS tools related to databases.

There is currently native support in QGIS for PostgreSQL/PostGIS and 
spatialite, and support through OGR for ODBC, ESRI Geodatabase, OGDI, 
PostgreSQL and Mysql.

As for functionnality, some are in core, and others are in plugins.

== Core functionalities ==
* Add vector layer
Enables to load a new layer from a database through OGR library. Support for 
ODBC, ESRI GeoDB, OGDI, PG, MySQL is present. The interface presents a 
connexion manager enabling to create, edit and delete connexions.

* Add PostGIS Layer
This is QGIS native PostGIS support. The interface presents :
- a connexion manager
- a list of schemas and tables
- a query builder helper
- a table searching interface
- saving and loading connexions

* Add spatialite layer
Spatialite native support. Same interface as postgis layer, without the query 
builder and connexion save/load functionality.

* New spatialite layer
Interface to create a new layer in an existing or new spatialite db. DDL 
creation helpers.

== Plugin functionalities ==
There are a few plugins related to databases in QGIS.

* PgQuery
Enables to create postgis table from a given query
- open/save query file
- run/save/clear query
- add dataset to qgis
- output query to table
- list tables

* PostGIS SQL editor
Uses a shapefile and pgsql2shp to save the query result and load it to qgis.
- load / save queries

* DB Manager
This is a feature-loaded database management with a pgadmin look & feel
- query execution
- import of data
- visualisation of DB objects
- admin tasks (vacuum, empty/delete/edit ddl)
- geometry processing (to new field of db)
- table versionning
- preview of data

* spatialite manager
This is a tool very similar to DB manager, for spatialite data

* RT SQL Layer
This is another tool for DB management, based on DB manager
- DB explorer
- query builder (open/load previous, query helpers : from, returns, where, 
column retriever)
- adds result of query as in-memory layer

* SPIT
Shapefile to PostGIS importer
- connexion manager
- import options
- editable list of shapefiles to import

* RT postgres extractor
Exports geometry based on layer (no details, not working with latest trunk at 
the moment).
=========

Please correct me if I forgot plugins or functionalities related to database 
and QGIS.
I think now it's time to refactor all those plugins into one or a few DB 
management tool, with a specific menu entry and no duplicate functionalities.

I think we can distinguish a set of main functionalities :
* DB connexion manager 
- with all DB connexions, whatever their type
* DB explorer / manager 
Used as advanced database management
It would refactor spatialite manager, DB manager and RT SQL Layer manager
* adding layer from DB connexion
This would refactor Add spatialite layer, add postgis layer
and adding layer from query which can be found in the various plugins.

I've got some draft mockups for that, and ready to discuss more deeply :)

Vincent




-- 
Vincent Picavet - vincent.picavet at oslandia.com
www.oslandia.com - Engineering your GIS