This page contains details about Google Summer of Code 2011 project "DB Manager plugin"
Here's the project page on GSoC 2011 site.
I created a public repository on GitHub named db_manager that will contain the plugin code.For changes/improvement done to the QGis main code, see at my QuantumGIS fork on GitHub.
In particular, see at the following branch:
- dataitems, it contains the changes to implement dataitems support for providers
- import_layer, which contains the changes to implement the import layer feature
#12 Report: 15 Aug - 21 Aug¶
This is my last weekly report.
The last week was very successful, I've implemented the import vector layer feature for the SpatiaLite and OGR providers (calling the QgsVectorFileWriter::saveAsVector static method).
To work with OGR I added another optional param, here's the new method definition:
int importVector( QgsVectorLayer* layer,
<pre>
const QString& providerKey,
const QString& uri,
const QgsCoordinateReferenceSystem *destCRS,
bool onlySelected = false,
QString *errorMessage = 0,
bool skipAttributeCreation = false,
const QMap<QString, QVariant> *options = 0;
);</pre>
The options param allow to pass provider specific params, e.g. the OGR driverName.
I've also improved DB Manager to make that feature available to users. Now users can make a copy of a table (with or without geometry) just by doing drag'n'drop of that table from a database to another one, the import feature will do the rest. In this moment there's no warning message when a previous existent table is being to be overwritten, I will add it soon.
As the import feature uses providers, only valid QGis layers could be imported/exported. i.e. a table without a suitable integer primary key would neither imported into or exported from a PG database.
No issues found this week, everything went smoothly.
Thanks all of you for your support! Any comments are very welcome.
#11 Report: 08 Aug - 14 Aug¶
This week I've implemented the import feature for the postgres provider. It still needs few adjustments but it seems to work pretty fine! See at the import_layer branch on my GitHub repository.
I've also made few tests, trying to import in a postgres database a vector file, a layer based on a PG table (with and without geometry), a SL layer too.
vl = qgis.utils.iface.activeLayer()
uri = QgsDataSourceURI()
uri.setConnection('127.0.0.1', '5432', 'test', 'guest', 'guest' )
uri.setDataSource('public', 'new_comuni', QString(), QString(), 'gid')
ret, msg = QgsProviderRegistry.instance().importVector( vl, 'postgres', uri.uri(), None, False, False )where
- vl is the vector layer loaded on the canvas and selected on the TOC,
- uri is the uri to the database in which import the layer
The method definition is the following:
int importVector( QgsVectorLayer* layer,
<pre>
const QString& providerKey,
const QString& uri,
const QgsCoordinateReferenceSystem *destCRS,
bool onlySelected = false,
QString *errorMessage = 0,
bool skipAttributeCreation = false
);</pre>
The next week I would implement the import feature on the SL provider. This should be quite easy, just a porting of that feature from the PG provider.
One issue found while trying to import a SL layer with srid 3003 to a PG database.
You can reproduce it by adding a SL layer with srid 3003 to canvas, then executing the following snippet on the python console:
qgis.utils.iface.activeLayer().crs().postgisSrid()
returns 0 instead of 3003.
You can also check the layer's CRS from Layer->Property->General, it's a custom CRS very similar to the 3003 QGis SRS but without the towgs84 params. It isn't reproducable using a PG layer instead.
#11 Report: 31 Jul - 07 Aug¶
I was back on 4 Aug. I've continued the work on the postgres provider to develop the import layer feature.
As discussed in developer ML, I added a new static method instead of use a provider's instance method. The method is called importVectorLayer and will allow to import a vector layer into the PostgreSQL database. I'm trying to follow the same steps used in the QgsVectorFileWriter class to save a vector layer on a file.
Next week I would finish and test this new feature, than I would move on the spatialite provider to implement the import feature also for that database provider.
#10 Report: 24 Jul - 30 Jul¶
I've left Italy on thu 28 Jul and I will be abroad until 4 Aug. My mentor was aware with it because it was in the proposed schedule.
The few days before departure I've started to work to import feature on postgres provider.
#9 Report: 18 Jul - 23 Jul¶
This week was fully dedicated to study for an exam. I've successfull passed it :)
Next week I'd start to code the Import layer feature for providers.
First step is to discuss changes with the dev-team about the way to access the new feature from providers. I wrote about possible chooses in the last report. After this, next step would be to start coding to add that feature into the provider.
#8 Report: 11 Jul - 17 Jul¶
This week I fixed few bugs in the dataitems branch, then I started to work on the Import layer feature for providers.
About Import layer feature, in this moment no real changes to the code was done, I saw at the providers code to understand what's the best way to implement this feature. I would avoid to use and export a static method because it may be hard to access it once the provider is exported as library. Even if we could use QLibrary to resolve the symbol, accessing it from API could became difficult. A faster way may be to use an instance of the provider created passing it connection information only (without any layer information). Once established the connection, an import method could allow to copy the layer into the database.
A similar approach is used in WMS provider yet and the same approch I used in dataitems branch. Passing only connection information to the provider, it can be used to return available layers, instead passing also layers information it can be used when creating a layer.
The next week I cannot work as I have to study for an exam. My mentor is aware of this.
#7 Report: 4 Jul - 10 Jul¶
This week I've went through less things than I expected.
I completed the table's data preview paying attention to the save of memory. This would be easy to do when working on a database that supports scrolling cursors, but the sqlite database doens't support them so in this moment it fetches all data before displaying them. I think that should be improved in the future releases because it's an inefficient way to deal with large tables with thousands of rows.
Also the preview of table geometry works fine now, so I'm going to publish the first version of the DB_Manager as experimental plugin.
Even if there're some missing features, users I think it's user-proof and ready for testing.
The next week I'd set to work to allow the import of layers to database. This task was scheduled to be at the end of the second coding period, but according to my mentor I'm starting soon as possible to make sure it will be done and working well.
#6 Report: 27 Jun - 3 Jul¶
This week I've developed a little framework that allow to each database to register/unregister their actions to the main window. Once the user select a database (or its children) in the tree, the database adds their functionalities to the main window's menu.
Few actions are actually implemented: delete schema, delete table/view and empty table.
I've also completed the info viewer, now it shows the same information the user can see using pg_manager, but again, it's the database plugin that displayes only the right info. To make things easy, I've developed a pre-set of Html tags with a predefined style, so all the database plugins/connectors can display their data using the same style.
For the next week, I would complete the tasks in progress, i.e. showing preview and table data, loading layer in canvas from the sql window, creating tables from GUI.
This was the week of issues, but I was lucky because I fixed all them :)
#5 Report: 20 Jun - 26 Jun¶
First thing I've done this week is to refactor the main window. In fact I've removed the main window's .ui file because wasn't useful to have it, so I've put the code that create the main window into the db_manager.py file. I've also created the class for the preview of the layer but I've not implemented it yet, I've just copied the code from the RT_Sql_Layer plugin.
About connectors, even if I've added methods to add, rename and delete either tables, views and schemas, the GUI to access those features is not completed, but I'm working on it. I think I won't need to make deep changes to connectors in the future development phase as them contains almost all the features available in PG/SL_Manager.
For the next week I would complete the GUIs to manage tables, I would also allow to the users to load a query as layer in canvas from within the sql window.
About issues, few segfaults had still occurred, but I healed it. Now all opened connections are closed before exit, so opening and closing more and more times the DB Manager doesn't cause any freeze due to busy connections.
#4 Report: 14 Jun - 19 Jun¶
This week I've completed the information viewer, a class used to display information about the selected item in the connection tree, either database or schema or table. The info viewer get the data it displays calling few methods on the selected item (e.g. generalInfo(), spatialInfo(), privilegedDetails(), ...) and the item retrieves all those informations using the connector.
I've also moved the classes to run queries from PG_Manager and I've fitted them to work with the new architecture. Now you're able to execute a query on the selected database, either postgres or spatialite.
Unlike I wrote on the proposal, I'm working on both the connector at one time. I started to develop the postgres connector only but I realized that working on both makes things easier because this helps me to discover issues in advance, specially those due to make the DB_Manager working with different databases.
The next week I would release a first mockup so people could help in testing it. I'd continue to work on connectors by adding methods to add/edit/delete object from the databases.
This week I've found some problems.
First, segfaults after opening and closing again and again the DB_Manager. I think those was due to memory leak, so I added debug messages in desctructors to understand what could be the issue, so I realized that I've forgotten to delete objects with cross-references. I fixed it, in this moment seems it works well.
Second problem due to work on one connector at one time. As I wrote this approach increases the time I've to spend to make a feature working with other databases.
#3 Report: 06 Jun - 13 Jun¶
No important step taken this week, I've mostly spent it in studying. About the project, I'm nearly to complete the components to display information about databases, schemas and tables.
The next week I would complete the work done this week and then start to work on the postgres connector.
No issues found.
#2 Report: 28 May - 05 Jun¶
This week I've completed the main window. All the connections are listed in a big tree so the user can work with more connections at the same time. In the future development this choose toghether with drag'n'drop could help to make the db_manager user-friendly when coping/moving layers between different database.
I've also implemented the QgsDataItem for the postgres provider. To do this I added to the provider the supportedLayers method which returns tables in the database and I created the QgsPostgresConnection class to make easy to get postgres connections (as done for the wms provider).
For the next week, the first few days I'll study for an exam and the remaining days I would complete the plugin's main classes developing the minor GUI components needed in order to display tables' info and data.
About issues, I think we should develop in QGis a connection manager or maybe just add a method to the provider to makes the connection list available to the other classes/plugins without having duplicated code (in this moment the only way to get the connection list is to parse the configuration file). This could be done adding one new method accessible from the provider library, something like QGISEXTERN QList<QgsConnection> connectionList() where QgsConnection contains the connection name and uri, but I don't know if this is the right way.
#1 Report: 23 May - 27 May¶
After discussing with devs about some features, if I should add them to core instead of developing them as plugin functions, I started to code.
I've been working on the plugin architecture, the GUI and it's parts. The GUI will be the similar to the PG_Manager plugin's one, but differently to it the main class will contains just few functions because more of them will be moved to other classes.
For the next week, I'd like to complete the main window and the connection list. To do that I would develop some classes which lists connections, these will be developed in C++ and added to QGis core.
No issues in this moment.
Pre GSoC Period: collecting ideas¶
IMPORTANT: the following are just scattered ideas. I'm writing every thing could be worth for the next development of the DBManager plugin.
Capabilities¶
As the aim of the DB Manager plugin is to work with different kind of databases, I would distinguish between required and optional capabilities.
Required capabilities are either very basic tasks (e.g. run query) or needed by DB Manager to work (e.g. get informations).
They must be implemented in database connectors, so I won't create any flags for them.
The following table displays a list of methods the connector must implements to work with DB Manager (just an idea, names and args could change in the next weeks).
| Method |
| Description |
| getSchemas() |
| return the list of schemas or None if connected to a non-schema database |
| getTablesAndViews(schemas) |
| return tables and views within the schemas or the list of all tables and views if schemas is None |
| runQuery(sql) |
| run a query on the database |
| getInfo(object) |
| return an html string containing info about the passed object |
| addSchema(name) |
| add the schema to the database and return the Schema object |
| deleteSchema(schema) |
| delete the schema from the database |
| renameSchema(schema, new_name) |
| rename the schema |
| addTable(...) |
| |
| addView(name, sql) |
| add a view to the database and return the View Object |
| deleteTableOrView(object) |
| delete either a table or view from the database |
| renameTableOrView(object, new_name) |
| rename the table/view |
| addTableColumns(...) |
| |
| addIndex(...) |
| |
| deleteIndex(index) |
| delete the index |
| addTrigger(...) |
| deleteTrigger(trigger) |
| delete the trigger |
| addConstraint(...) |
| deleteConstraint(constraint) |
| delete the constraint |
| getPrivileges(object) |
| returns privileges the user has on the passed object |
|
As you can see in the table above, the connector must implement the getSchemas method so it returns None when user is connected to a non-schema database, in the same manner the getPrivileges method always returns true if the database doesn't support permissions.
Optional capabilities are all of them that each database connector can implement or not (e.g. import table from file/canvas).
These flags doesn't take care about permissions on the related objects.
| Optional capabilities |
| Description |
| TruncateTable |
| true if database allows to truncate a table or the connector implements a workaround to do this |
| DeleteTableColumn |
| true if the database allows to delete an existent column |
| RenameTableColumns |
| true if the database allows to rename an existent column |
| VacuumOnDatabase |
| true if it allows to run vacuum to rebuild the entire database |
| VacuumOnTables |
| true if it allows to run vacuum on tables |
| LoadTablesAsLayer |
| true if the provider for this kind of database exists |
| LoadQueryAsLayer |
| true if the provider supports custom query |
| ImportLayer |
| true if the provider supports import layer |
|
For each optional capabilities will be defined a method the connector may implement
GUI to start a connection¶
I list the different approaches I can use to define a GUI to start a connection.
In PG_Manager, but also in SL_Manager and RT_Sql_Layer plugin, connections are listed in the first menu named "Database".
Looking at RT_Sql_Layer plugin which works with both PG and SL connections yet, the way to distinguish the connection type is an icon.
That is a nice solution, but unfortunately in Ubuntu 11.04 menus doens't show icons...
1. My idea would be to insert a "Connect..." button in the Database menu. When the user clicks on that button DB Manager displays a dialog with a list of connection and the user can start a connection.
2. Another approach could be to show all database connections in a list (tree?). Once the user selects the database connection the list will get hidden and database contents tree would be shown instead. (suggested by Martin)
3. A variant to 2., it would be to insert a tab widget with 2 pages, the "Connections" page that would list all the connections and "Database" page would show instead the contents of the selected database.
4. Yet another approach would be what pgadmin3 does: keep all database connections and database contents in one big tree. That would actually allow users to simultaneously work with more databases without having to connect/disconnect. (Martin's suggestion)
Connection Manager¶
After the user selects a connection, a connection manager would take care to instantiate the right connector.
The connection manager would be developed using the Abstract Factory design pattern.
A ConnectionFactory class will import the connector according to the type of the connection to instantiate returning the concrete factory class (e.g. PostGISConnectionFactory) that will be used to get the real connection.
Accessing to the connection would be done using an interface, a predefined set of methods the connector must implement.
Use customized dialog¶
Sometimes the default dialog for a feature is not easily fittable to work with a particular database. In those cases it's better to create a new dialog for that feature. This could be a general behavior, not just for dialog.
DB Manager should allow to a database connector to override a feature implementation displaying the right dialog. This could be done using the Abstract Factory pattern.
Register actions to menu¶
If a database connector implements a feature specific for a such database, it should be able to register that feature so it can be executed by the user from the DBManager main window.
The registerAction method could take as arguments the action associated to feature, the menu name, the callback function and would insert into the menu (2nd arg) the passed action (1st arg) connecting it to the callback function (3rd arg).
Once triggered the action would call the callback function passing to it the selected item on the database tree and the database connection.
Could I use QgsDataItem class?¶
Less than a week ago Martin and Radim merged into trunk the QBrowser.
QBrowser is a standalone application which allow users to browse directories to search vector/raster files, but also WMS connections. It displays informations, attributes and preview of the selected layer.
QBrowser gets all the data to create the tree of connections/directories from the QGis providers, in this moment GDAL/OGR and WMS providers only.
I should consider if use the same way to get connections to database, that is by implementing QgsDataItem and QgsDataCollectionItem for the DB providers. This would also require to create classes for list connections and get the connection params, as was done yet for wms provider.
Summary¶
DBManager plugin should be developed as a container of plugins which implementing the same interface named DBPlugin.
Each concreate DBPlugin class (e.g. PostGisDBPlugin, SpatiaLiteDBPlugin) represents a particular kind of database and its instances have a connector implementing the interface DBConnector and it will take care the communication to the database.
The creation of both DBPlugin and DBConnector instances will be developed in according to the Abstract Factory design pattern, so at runtime the factories classes DBPluginFactory and DBConnectionFactory could create respectively a database plugin and a connector of the right type.
DBPlugin must implement a basic set of features (e.g. getSchemas, getTables, getInfo, ...), all of those that the DBManager needs to work.
Furthermore, it could implement additional features that will be available to the user by registering them to the DBManager main window.
For this reason DBManager would have a method registerAction which takes an action, a menu name and a callback function, then it adds the action to the passed menu and connect the action to the callback function. Once triggered the action would call the callback function passing to it the selected item on the database tree and the database connection.
Accepted Proposal¶
?Name: Giuseppe Sucameli
Country: Italy
School and degree: University of Pisa, Computer Engineering
Email: brush.tyler at gmail dot com
Phone: ...
OSGeo project(s): QuantumGIS
Title: DBManager plugin for QGIS
Describe your idea
1. Introduction
Integrate PG_Manager, SL_Manager, Rt_Sql_Layer and other related python plugins to create DBManager, a multi-db manager easily expandable to manage other spatial DBs.
2. Background
PG_Manager is a very useful python plugin which permits users to get informations, to manage and execute queries on a PostGIS database from within QGIS.
Written by Martin Dobias, PG_Manager has a very intuitive GUI and for this reason it was chosen as base for some other db-related plugins.
SL_Manager is a fork of PG_Manager plugin working on SpatiaLite databases.
The RT_Sql_Layer plugin allow users to load a PostGIS or SpatiaLite layer using whatever select statement. It's based on PG_Manager and works by using a core feature implemented in PostGIS and SpatiaLite provider. Unlike SL_Manager, the RT_Sql_Layer plugin doesnÂt make available all the PG_Manager's functionalities because different was its aim.
These 3 plugins have a lot of duplicated code-base and are very difficult to maintain because porting fixes between them and adding new features could require a lot of time.
3. The Idea
My aim is to merge both 3 plugins together and create the DBManager plugin, in Python of course.
First and the most important requirement, the DBManager plugin must be easily expandable to manage different kind of spatial DBMSs (e.g. MySQL).
Second, it should permit an easy management of databases, running common tasks as create/alter/delete tables, retrieve info about tables, run queries.
Those are implemented in PG/SL_Manager yet, but I have to do some changes to make them more general than they are.
Third, DBManager should allow to load tables as layer on the QGis canvas and, on the contrary, import layers from the canvas into the db.
The first part of the project would be dedicated to a short analysis of the above plugins architecture to understand how to make it working with other DBMS.
The idea is to create a common interface for all the databases, so it's just needed implementing a connector with a small set of functions to make the plugin working with other DBMS backend.
This part would end before the beginning of the GSoC coding period.
The second part would aim at the development of the new architecture but also of two connectors to test if communication works as expected.
The third part would then make mostly of the feature exported by PG_Manager working with the new architecture.
4. Project plan
As required by OSGeo, I will provide at least every week a report about the work done, pitfalls and improvements.
I premise I'm going to be abroad from Jul 29 to Aug 4 (5 working days). If there are no objections I could start coding few days before May 24 to recover that week.
HereÂs my plan.
PRE-GSOC GOAL: Requirements and Analysis
April 8 - May 1: Fix up the requirements with mentor and mentoring organization
May 2 - May 16: Analysis of current architecture
May 9 - May 23: Design of the new architecture
MIDDLE-TERM GOAL, due July 11: Implement the new architecture
May 24 - Jun 14 (3 weeks): main plugin's classes according to the new architecture
Jun 15 - Jun 21: connector for PostGIS
Jun 22 - Jun 28: connector for SpatiaLite
Jun 29 - Jul 12 (2 weeks): code testing, bug-fixing
FINAL-TERM GOAL, due Aug 24: Make mostly of the common tasks working with the new architecture
Jul 13 - Jul 20 (1 week): display info about tables and databases, run queries
Jul 21 - Aug 28 (>1 week): create/edit/delete tables, add/delete columns
Jul 29 - Aug 4 (<1 week): ABROAD
Aug 5 - Aug 11 (1 week): interact with QGIS, load a table in canvas or import a layer from canvas
Aug 12 - Aug 25 (2 weeks): code testing, bug-fixing
5. Future ideas / How can your idea be expanded?
Future tasks will be the development of new features and connector for other DBMSs, but also the rasters management. I'm thinking about the WKT rasters in PostGIS database and the WKTRaster plugin for QGIS.
Please provide details of general computing experience:
I use almost exclusively Linux OS (Ubuntu) but I'm able to run tests on Windows. I've written programs in C++, Python, PHP, Java and other programming languages, I know the QT framework. I usually use the SQLite and PostgreSQL DBMS and their spatial extensions.
Please provide details of any previous involvement with GIS programming and other software programming:
I'm a QuantumGIS developer, the maintainer of GdalTools the author of some other plugins for QGIS.
My undergrad thesis was about a pdf-parser in C, but I wrote smaller programs and libs too. Two years ago I attended the GSoC 2009 for the Asterisk Project.
Please tell us why you are interested in GIS and open source software:
I like OSS because it allows me to learn programming languages reading working code and not just snippets, so I can catch coding ways different from mine, even tricks and solutions to uncommon problems.
Please tell us why you are interested in working for OSGeo and the software project you have selected:
QGIS community is my community and in the last year I've had a lot of relations with members of other OSGeo projects. I feel at home.
Please tell us why you are interested in your specific coding project:
I'm author and maintainer of SL_Manager and RT_Sql_Layer plugin, integration would be a way to avoid future waste of time in porting fixes. Furthermore, I've recently upgrated the RT_Sql_Layer to work with SpatiaLite DBs too, so would be very useful generalizing such behavior.
Would your application contribute to your ongoing studies/degree? If so, how?
No. I think this coding project would be useful for QGIS users at all easily managing different DBs. Probably my master thesis will have GIS as subject, maybe a new core feature, but I've no idea about it now.
Please explain how you intend to continue being an active member of your project and/or OSGeo AFTER the summer is over:
Primarily by maintaining the plugin code.
I'm within the project yet, so shouldn't be difficult being an active member.
Do you understand this is a serious commitment, equivalent to a full-time paid summer internship or summer job?
Yes, I do.
Any comments are very welcome.