Vivien

It’s been a long time since I blogged about Libgda (and for the matter since I blogged at all!). Here is a quick outline on what has been going on regarding Libgda for the past few months:

• Libgda’s latest version is now 4.2.4
• many bugs have been corrected and it’s now very stable
• the documentation is now faily exhaustive and includes a lot of examples
• a GTK3 branch is maintained, it contains all the modifications to make Libgda work in the GTK3 environment
• the GdaBrowser and GdaSql tools have had a lot of work and are now both mature and stable
• using the NSIS tool, I’ve made available a new Windows installer for the GdaBrowser and associated tools, available at http://www.gnome.org/~vivien/GdaBrowserSetup.exe. It’s only available in English and French, please test it and report any error.

In the next months, I’ll work on polishing even more the GdaBrowser tool which I use on a daily basis (and of course correct bugs).

With the beginning of the year comes new releases of Libgda:

• version 4.0.6 which contains corrections for the stable branch
• version 4.1.4, a beta version for the upcoming 4.2 version

The 4.1.4’s API is now considered stable and except for minor corrections should not be modified anymore.

This new version also includes a new database adaptator (provider) to connect to databases through a web server (which of course needs to be configured for that purpose) as illustrated by the followin diagram:

The database being accessed by the web server can be any type supported by the PEAR::MDB2 module.

The GdaBrowser application now supports defining presentation preferences for each table’s column, which are used when data from a table’s column need to be displayed:

The UI extension now supports improved custom layout, described through a simple XML syntax, as shown in the following screenshot of the gdaui-demo-4.0 program:

For more information, please visit the http://www.gnome-db.org web site.

I’ve spent a lot of time lately to improve the user experience with the GdaBrowser tool which will be included in Libgda starting with version 4.2.

The idea of this tool is to give database administrators a small and powerfull tool to “do stuff” on their databases, limited for now to browse the database schema and execute some SQL statements. Even though it still has some bugs, I use it regularly for my day to day needs.

The schema browser now displays database tables in a cloud style view which makes it easier to spot tables which have a lot of relations with other tables and to search, as shown in the following screenshot where the search highlights (in blue) tables beginning with “c”, shows the one having a “c” in a dark gray color, and almost hides the ones which have nothing in relation with the searched text:

The query editor can now propose a completion list when CTRL+Space is pressed, as shown in the next screenshot. Also note that each favorite query now displays the name of the favorite, the query type (SELECT here) and the variables used by the statement.

All of these improvements are now only available from the sources in git (git.gnome.org) but will be part of the next unstable release.

Long time no blog…

I’ve been busy lately working on adding a UI extension to Libgda: merging the good parts of Libgnomedb and Mergeant (which have not been kept up to date) into Libgda. This new UI “extension” will remain optional (built only if GTK+ is found) and includes:

• some data bound widgets (grid anf form views)
• some “administrative” widgets such as a login widget to enter credentials when opening a connection
• a reworked control center where one can manage named data sources (DSN) and check the list of installed database providers (drivers)
• a re-write of the browser tool usefull to analyse the structure of a database and run some statements; this a a kind of merge with the one which existed in Libgnomedb and Mergeant but with a much improved user interface.

This is now all in the master branch in git.gnome.org, works (except for some DnD) on Windows and MacOSX. Here are some screenshots of the new browser, taken using a PostgreSQL database. One can open several connections at the same time in the browser, and for each connection, several windows can be opened. Each window displays a “perspective” (similar to Eclipse’s perspectives), and currently the schema browser perspective is implemented, which is shown below, more will come later (statements execution, reports, etc).

The shot above shows the index page where all the database objects are displayed (tables only for the moment), and the favorites bar on the left where one can drag n drop tables (or later other objects types) for quick access (the trashcan at the bottom is to remove favorites by dragging them on it). Clicking on a table opens a new tab as shown:

The shot above shows the details of table “products” with the traditional fields list and the constraints below (here clicking on the “warehouses” link opens yet another tab for the warehouses table). You can note in the bottom that when showing a table, it is also possible to display the table’s relations, ie. which table are referenced by the current table and which tables reference the current table, as show:

The shot above cleary shows that the “customers” table references the “locations” and “salesrep” tables and is itself referenced by the “orders” table. The canvas is drawn using GooCanvas and can be exported to PNG and SVG or printed.

That’s all for now, more later…

Following my post on the new embedded web server feature of Libgda’s SQL console, I have spent some time on improving the user experience (the web server is optionnaly enabled and serves pages containing information about the current opened connections).

Here are the improvements so far:

• the UI is more polished thanks to the usage of AJAX (through the very nice JQuery javascript library) and to some CSS work
• a simplified authentication mechanism has been implemented where one can specifiy (when starting the embedded web server) a “token string” which is requested when a browser first connects to the server
• a “terminal emulator” page has been added, which behaves almost like a normal terminal emulator and where commands can be entered. Through it, one can execute SQL commands and internal commands (commands specific to Libgda’s console for various tasks such as opening and closing connections).

The following screenshot shows a sample session in the terminal emulator; the “.c SalesTest” command requires that the “SalesTest” connection be used, the “.d” command lists all the tables and views of the connection.

This second screenshot is similar to the one above except that the result of the “.d” command has been folded (by double clicking on it) so it just shows the number of rows:

I’ve been using SQL consoles for years. Each database has its own SQL console: psql for PostgreSQL, mysql for MySQL, sqlite3 for SQLite3, sqlplus for Oracle,… When one needs information about database objects such as for example the tables, their columns and relations with other tables, one has to remember the syntax to use depending on th SQL console (for example to list the tables: \dt for PostgreSQL, show tables; for MySQL, select name from sqlite_master where type=’table’; for SQLite3).

This can be quite frustrating when using several databases and consoles, and is the reason I decided to write yet another SQL console, part of Libgda which overcomes all these little difficulties. The syntax (and displayed information) is the same for every database Libgda can connect to.

I’ve recently improved it even more by turning the SQL console into a web server to display meta data in a user friendly way. Here is the use case: the user uses the SQL console normally and then decides to start the web server (through a \http command) and then points his web browser to http://localhost:12345 (the port number can also be specified). Here is below the kind of information displayed:

There is at the moment no security around the web server (anyone can connect) but this will be added.

Been a long time without any post… here are some fresh news about Libgda.

Now that the API has been declared stable for the next V4, I’ve started to update the information regarding Libgda at http://www.gnome-db/org. There is now a new description of what Libgda is:

• a wrapper like ODBC but with more features to access several database engines
• a meta data extractor (to know all about database objects in a common way)
• comes with an SQL console application (like mysql, psql or sqlite3 consoles)
• relies on GLib, coded in C, its API is easy to use
• at the moment supports SQLite, MySQL, PostgreSQL, MSAccess and Bdb, work is in progress for other database types (such as Firebird and Oracle) and to wrap ODBC and JDBC for more wider usage
• LGPL license

I’ve been working lately on a JDBC (JAVA’s own mostly standardized way to access a database) wrapper for Libgda; that code has just been committed to subversion. Even though it’s still not finished, it is already quite useable: you can write a C or C++ application which accesses data through JDBC. I’ve been testing it with the H2 and HSQLDB database engines (which are written entirely in JAVA, HSQLDB being the ‘default’ database engine used by OOo’s Base component).

Here is how it works: the JDBC wrapper (which is a shared library like for other Libgda’s databases’ wrappers) loads a Java Virtual Machine (JVM, which is also a shared library) which itself loads the JDBC drivers, opens connections, run queries,… The communication between the C and JAVA parts is done through the Java Native Interface (JNI). I’m testing this on Linux (both 32 and 64 bits) and Windows XP with Sun’s JRE or JDK.

For example on my test machine, the sql console tool reports the following database providers (drivers):

Provider                               | Description
---------------------------------------+----------------------------------------------------------------------------------------
PostgreSQL                             | Provider for PostgreSQL databases
MSAccess                               | Provider for Microsoft Access files
MySQL                                  | Provider for MySQL databases
Berkeley-DB                            | Provider for Berkeley databases
org.hsqldb.jdbcDriver                  | Provider to access databases using JDBC's org.hsqldb.jdbcDriver driver
com.mysql.jdbc.Driver                  | Provider to access MySQL databases using JDBC
org.h2.Driver                          | Provider to access databases using JDBC's org.h2.Driver driver
org.postgresql.Driver                  | Provider to access PostgreSQL databases using JDBC
org.apache.derby.jdbc.AutoloadedDriver | Provider to access databases using JDBC's org.apache.derby.jdbc.AutoloadedDriver driver
org.apache.derby.jdbc.ClientDriver     | Provider to access databases using JDBC's org.apache.derby.jdbc.ClientDriver driver
SQLite                                 | Provider for SQLite databases


There is still some work to be done on the meta data front as each JDBC driver tends to introduce some particularities about this which makes it almost impossible to retreive meta data in a database agnostic way…

Last but not least I’ve proposed a patch to the Gnumeric people to update the Libgda plugin which allows one to insert into a spreadsheet the results of a SELECT query run through Libgda.

Libgda is reaching the point where its API will be declared stable, I expect the next version to be tagged “API stable” in a week or two (depending on the API feedback I get).

I encourage people using Libgda to migrate to this version as it has many bug corrections (and many improved features compared to the V3).

Also for the first time, I managed to create some Win32 ZIP archives (MSI files can be done on request but take more work to do because they can’t be done from Linux). These archives are with all the other Win32 Gnome’s archives at http://ftp.gnome.org/pub/GNOME/binaries/win32/.

The first V4 beta of Libgda is out. It is still buggy and an unfinished work, however one can start to experiment with the new API and features.

The V4 version is a major rework of the library with the following goals of:

• making it easier to use and  understand
• have better thought features
• correct some long standing bugs (such as support for schema in the dictionary or support for updatable data models resulting from SELECT queries)
• better SQL parsing and generation to support database’s specific SQL dialects (and SQL rewrite)

It is available at http://ftp.gnome.org/pub/GNOME/sources/libgda/3.99/

Having spent my last few weeks’ spare time working on the next version of Libgda (V4), I’ve finally merged all the changes into SVN trunk. This next version will break the API with the previous version, but it will offer an easier to understand and to use API, and several other changes (wich will enable closing of some long standing bugs).

The SQL console tool has been improved to show the potential of the new version, here is a sample session which shows the new \graph command which (if the dot GraphViz command is installed) can produce a PNG or PDF file. Note that the view named ‘aview’ has a dependency listed on the table ‘film’ because it uses that table (as seen from its definition).