QDepo using CUBRID - Tutorial

1. Prerequisites

Perl and the required Perl modules. For Windows, the recommended Perl distributions are: Citrus Perl and Strawberry Perl. Both come with Wx and Tk toolkit modules preinstalled.

A working CUBRID server with the "demodb" database created and started. The example configuration is for a local installation, but any other host name can be used instead of "localhost". The IP address is an other valid option.

Note: the application can be installed and run on any OS where Perl and the required modules are available, but the author has tested it only on GNU/Linux and Microsoft Windows.

Note: QDepo is the new name of the "TPDA - Query Repository Tool" project.

2. Installation and Configuration

This is a Perl application, so the principle is pretty simple: download the package from sourceforge, unpack, install the prerequisites listed in Makefile.PL, then install it like any other standard Perl module:

% tar xvzf QDepo-0.XX.tar.gz
% cd QDepo-0.XX

The easiest way to install the prerequisites is with:

% cpan .

or

% cpanm --installdeps .     #  with App::cpanminus

The graphical user interface can use the PerlTk or wxPerl widget libraries. Install one of them or both. To switch between the two toolkits, set "widgetset" to "Tk" or "Wx", in the ".qdepo/etc/main.yml" configuration file.

This are some of the Perl module prerequisites, for the graphical user interface:

• wxPerl:

Wx
Wx::Perl::ListCtrl

• PerlTk:

Tk
Tk::MListbox
Tk::MsgBox
Tk::StatusBar
Tk::ToolBar

And of course, for the database we use the DBD::cubrid module. Add the prefix for CUBRID in DBI.pm (I have DBI v1.620), like this:

# --- Driver Specific Prefix Registry ---
my $dbd_prefix_registry = {
...
  cubrid_      => { class => 'DBD::cubrid',         },
...
};

Also, the test 40lobs_file.t must be ignored.

There are several modules that can be used for exporting:

• Spreadsheet::WriteExcel
• Text::CSV_XS
• OpenOffice::OODoc
• ODF::lpOD

It is recommended to use the Spreadsheet::WriteExcel module for generating Microsoft Excel compatible files and the Text::CSV_XS module for generating CSV files. The former is a decent alternative because it can be imported in spreadsheet applications and works on any platform. This two are also the fastest modules among the alternatives.

After installing the required modules, continue with the installation:

make
make test
make install

At first start, the configuration directory is initialized automatically. The following command will do that and also will list all the currently defined configurations in the terminal window.

qdepo -l

Next, we run the following command to create a new configuration. Multiple database connection configurations can coexists using any of the database interface modules supported by the application.

qdepo -i cubrid-test

This command will create a new configuration directory underneath the ".qdepo/db/" path. This directory contains two sub directories, "etc" and "qdf". The "etc" directory contains a file named "connection.yml". If your CUBRID server is not installed on localhost then change the connection parameters in this file as appropriate for your system.

The other directory named "qdf" is the place where your query definition files are stored. The ".qdf" files can be created and updated with the graphical user interface.

3. Adding a new Query

The application has, hopefully, a simple and intuitive interface. Start the graphical user interface with the following command:

qdepo cubrid-test

The first example shows how to add a very basic query, and the last example adds a parameter. Up to five parameters can be used currently.

The technology behind the implementation for the parameters, is simple and may seem naive (and maybe it is, but works pretty well with simple queries). Whenever the "valueN" string (where "N" can take values from 1 to 5) is found in the query text, it is replaced with a "?" character, a placeholder. Then the associated value from the Parameters tab is bound with each placeholder. "N" must occur in ascending numerical order in the query text. That is "value1" first, than "value2", and so, until "valueN".

3.1 The Simplest Query

Add a new query using the button from the toolbar. The application will automatically enter in edit mode. Change the title to "Athletes list", the output file name to "athletes_list" and the description to "List of all the athletes from the table".

Here is a screenshot of the wxPerl version of the user interface:

Also, edit the SQL statement text:

SELECT *
FROM athlete

Click on the button to save and exit the edit mode.

The edit mode can be toggled with the toolbar button, but the application doesn't check if there is unsaved data in the controls (fields), the user have to explicitly click on the button to save the changes.

Switch to the "Log info" tab and click on the toolbar button. Watch the messages, and if all is good, then a new file named "athletes_list.xls", filled with the appropriate data, should have been created in the output path.

3.2 Query With A Parameter

Switch back to the "Query List" tab and click on the button again. Fill in the values for the title, output file name and description to "Athletes list, filtered", "athletes_list_filtered" and "List of the athletes, filtered by nation code". This is only an example, of course, you can enter anything that makes sense to you and helps to easily identify the query you wish to execute.

The Parameters tab contains 5 pair of text controls, where the left column contains the description of the parameter - any short text label and the second column contains the value of the parameter - without any quotes.

In the Parameters Tab fill in a description and a value for a parameter, for example for the "athlete" table:

description: Nation code
value      : ROU

Delete all other labels and values on other rows.

Switch to the "SQL Query" tab and edit the statement, to add the WHERE clause:

SELECT *
FROM athlete
WHERE nation_code = value1
Note: the value1 string is without any quotes.

This shows another feature of the program, the parameter is replaced with it's value when leaving the "edit mode".

Again, save and click on the toolbar button. A new file, filled with the appropriate data, should have been created in the output path.

4. Deleting Queries

I suppose that it's not a big surprise that deleting queries from the list is made using the toolbar button. The list entry and the associated ".qdf" file is not immediately deleted, only marked and deleted when the application is closed. There is a safety feature, the ".qdf" file is not deleted, only renamed to ".qdf.bak", so it can be recovered.

5. Conclusion

This concludes the tutorial for QDepo. If you have any questions please post them on our Q&A site or on our forum.

Thank you for your time and have fun using the application!