SQL console

Having used PostgreSQL’s psql console tool a lot in the past, I got used to its very good features (short commands to display info such as \dt, paged output when it contains more lines than current terminal size, (basic) HTML output for quick reports,…)

Those features are not always available in the SQL console of the other DBMS systems I’ve come to use, so I decided some time ago to write a new SQL console using Libgda, and it’s now useable with some nice features:

  • partial implementation of some of the PostgreSQL’s psql SQL console (some short commands, paged output, HTML export mode,…)
  • multiple simultaneous connections (with the possibility to change the current connection used to issue SQL commands)
  • possibility to bind several connections into one and issue cross-connection SQL statements (for example a SELECT using tables from 2 different connections)
  • support for all the DBMS types supported by Libgda

Here is a sample session (where a connection named cnc0 is opened to the SalesTest defined data source):

sqlconsole.png

Continue reading SQL console

New releases

Just a few words about the 3.1.2 release of Libgda and Libgnomedb and mergeant 0.67. The 3.1.2 is the second unstable version which brings a few new features and a lot of improvements and bug fixes compared to 3.1.1; the new features are stabilizing…

Mergant has not evolved a lot lately (my efforts are currently essentialle spend on Libgda), and this new version mainly allows one to test the improvements in Libgda/Libgnomedb.

The source code is available from the GNOME FTP server:

Libgda/Libgnomedb 3.1.1

I finally decided to make all the last weeks’ improvements to Libgda and Libgnomedb in a packaged version. New important features are:

  • Virtual connections
  • WIN32 port
  • Relocatable installation
  • Much better documentation and code examples
  • New report engine
  • New command line tool (interractive SQL commands)
  • New experimental GooCanvas based widget
  • Automatic layout if Graphviz is present

The source code is available from the GNOME FTP server:

Libgda: reports generation

Before releasing a 3.1.1 version of Libgda, I wanted to work a bit on Libgda’s report engine which is currently very broken (does not work at all and seems to have a difficult to use API). So I decided to reimplement one into a separate component, even though this will break the API.

After a few hours, the reports component has a generic engine which takes an XML input, and replaces the <gda_report_…> nodes with some actual content, each of these nodes being commands to perform or display some data. With only 5 of these commands, it’s possible to achieve some nice and complex results.

I have so far tested it with some DocBook and RML files. I can generate HTML from DocBook very easily, but PDF generation is more tricky since it uses FOP (which is not always packaged on distros), and it’s easy to generate PDF from RML files. Even though many features are still missing, it’s a good start…

Sample output:

  • PDF output using this RML template:

pdf.png

  • HTML and then PDF output using this DocBook template:

html.png pdf-docbook.png

The code is in SVN trunk, and the examples which generate these outputs are in the samples/Report directory.

‘Repair’ your F-Spot database with Libgda

Continuing to work around the idea of virtual connections and virtual tables in Libgda (which allow to run SQL commands on data coming from databases but also from data models) I have written a small tool to repair an F-Spot database.

The “repair” term is a bit awkard since the aim of the tool is to correct the path of some pictures in your F-Spot database after you have moved them to another directory. For example if I had some of my pictures in /my/pics/dir and moved those to /another/dir then I can run:
repair-path /another/dir
and the paths will be corrected for the files which have a unique file name in /another/dir.
The tool is far from being featurefull as it for example will look only for unique file names in resolution, but it’s just an example.

Internally a connection is opened to the F-Spot database (an SQLite file), a data model is created which lists the files in /another/dir and a virtual connection is created which imports the tables from the SQLite connection using the spot namespace, and shows the files table as the contents of the data model, as shown in the following diagram:

Connections setupSQL commands are then issued to the virtual connection to determine missing images and the possible corrections.

The code requires SVN trunk’s Libgda version and can be found in the http://svn.gnome.org/viewcvs/libgda/trunk/samples/F-Spot
directory. Make sure to read the README file for more information.

200 lines of C to make a file duplicates finder and remover with Libgda

To illustrate the usage of virtual connections in Libgda, I wrote a small program which is just about 200 lines of code and which can either:

  • show all the file duplicates in a directory (based on actual file contents comparison, not just MD5 hash)
  • show the file duplicates for a file
  • show and delete the duplicates for a file (though it should not be used in production machines as there are probably still some bugs).

All this is made using some SQL code on a list of files made into a data model.

I thought it would be slow but in the end it’s quite quick as it takes only 5s to search for duplicates in GLib’s 1000 (not compiled) source files and 9s for GTK+’s 3500 files (the duplicates seem to be images from the documentation, but also for example gdk/win32/gdkspawn-win32.c and gdk/quartz/gdkspawn-quartz.c).

The code requires SVN trunk’s Libgda version and can be found in the http://svn.gnome.org/viewcvs/libgda/trunk/samples/DirDataModel
directory. Make sure to read the README file for more information.

The next step is to make a small program to help repair an F-Spot database if the user has moved files around (as I should not have done).

Libgda: running SELECT SQL commands on CSV files

I recently spent some time in Libgda’s code to implement a new type of connection which lets the user run SQL commands on tables defined as data model objects (the GdaDataModel is itself an interface with various implementations).

The operating mode is easy: create a new connection, add data models to it which will be represented as tables, and then run SQL as for a normal connection. There are of course limitations due to the fact that all of this is implemented using the virtual tables feature of SQLite, and some performances limitations dues to the early nature of the code.

In the future, I plan to enhance the concept further by being able to run SQL commands on the tables of several connections, to enable direct data migration or comparison from different databases for example.

To illustrate this, SNV trunk now holds an example which computes information from two different CSV files (in samples/Virtual).

Windows port…

I’ve spent a few days to make Libgda and Libgnomedb work under Windows, using the cross compilation toolchain provided by MinGW .

Here are a few of the adaptations I’ve had to make (with the help of other people who have already done a big part of that job):

  • make sure file names are with the correct separator (even though I’m not sure it would not work with standard unix separators)
  • make the libraries relocatable as people tend to install them in various places not depending on compile time settings. This is required since both libraries use a lot of external ressources (XML files, pictures, plugin files). For this purpose, the GLib provides a WIN32 specific API such as g_win32_get_package_installation_directory(), which is not available for Unix, so to complete the job, I used the BinReloc code.
  • define strtok_r() which does not exist in the WIN32 API
  • rework a bit the configure.in and some of the Makefile.am files

The modifications will soon be in SVN and I’ll add a dedicated page on the Gnome-DB wiki for those of you who want to test…

Here is a screenshot of the result:

WindowsPort

New printer

I just bought a new printer for occasional home usage, to replace a 12 years old HP laserjet printer. This time I chose an inkjet because they are very inexpensive and because I can also quickly print photos with it.

The model I bought is an HP PhotoSmart 2570, an “all in one” product (printer + scanner) and I was very glad that both the printer and the scanner work perfectly thanks to HP’s commitment to the HPLIP drivers, which I want to thank for that.



As a side note, it’s too bad the installation and users manuals don’t even mention Linux…

Libgda/Libgnomedb 3.0.0

The last stable version of the libgda and libgnomedb libraries have just been released. This new version brings a whole lot of new features which should make it easy for application developers to interface with several DBMS without any code modifications.

Libgda provides the low-level (non GUI) code to abstract DBMS access, while libgnomedb provides several data-bound widgets to interract with the data.

More information can be found at the reworked Gnome-Db web site http://www.gnome-db.org, and the source code can be downloaded from the usual GNOME FTP servers.