Daniel Kasak




   use DBI;

   use Gtk2 -init;

   use Gtk2::Ex::Datasheet::DBI; 

   my $dbh = DBI->connect (
           PrintError => 0,
           RaiseError => 0,
           AutoCommit => 1

   my $datasheet_def = {
       dbh          => $dbh,
       sql          => {
                           select            => "FirstName, LastName, GroupNo, Active",
                           from              => "BirdsOfAFeather",
                           order_by          => "LastName"
       treeview     => $testwindow->get_widget( "BirdsOfAFeather_TreeView" ),
       fields       => [
                               name          => "First Name",
                               x_percent     => 35,
                               validation    => sub { &validate_first_name(@_); }
                               name          => "Last Name",
                               x_percent     => 35
                               name          => "Group",
                               x_percent     => 30,
                               renderer      => "combo",
                               model_setup   => {
                                     fields     => [
                                                        name            => "ID",
                                                        type            => "Glib::Int"
                                                        name            => "GroupName",
                                                        type            => "Glib::String"
                                     sql        => {
                                                        from            => "Groups",
                                                        where_object    => {
                                                            where       => "Active = 1 and Location = ?",
                                                            bind_values => [ $some_location_id ]
                               name          => "Active",
                               x_absolute    => 50,
                               renderer      => "toggle"
       multi_select => TRUE
   $birds_of_a_feather_datasheet = Gtk2::Ex::Datasheet::DBI->new( $datasheet_def )
      || die ("Error setting up Gtk2::Ex::Datasheet::DBI\n");


This module automates the process of setting up a model and treeview based on field definitions you pass it, querying the database, populating the model, and updating the database with changes made by the user.

Steps for use:

* Open a DBI connection

* Create a 'bare' Gtk2::TreeView - I use Gtk2::GladeXML, but I assume you can do it the old-fashioned way

* Create a Gtk2::Ex::Datasheet::DBI object and pass it your TreeView object

You would then typically create some buttons and connect them to the methods below to handle common actions such as inserting, deleting, etc.



    Object constructor. For more info, see section on CONSTRUCTION below.

query ( [ where_object ], [ dont_apply ] )

    Requeries the Database Server. If there are any outstanding changes that haven't been applied to the database, a dialog will be presented to the user asking if they want to apply updates before requerying.

    If a where object is passed, the relevent parts will be replaced ( the where clause and the bind_values ). Note that you don't have to provide both. For example, if you leave out the where clause and only supply bind_values, the original where clause will continue to be used.

    If dont_apply is set, *no* dialog will appear if there are outstanding changes to the data.

    The where_object is a hash:

            where         => a where clause - can include placeholders
            bind_values   => an array of values to bind to placeholders ( optional )

    The query method doubles as an 'undo' method if you set the dont_apply flag, eg:

    $datasheet->query ( undef, TRUE );

    This will requery and reset all the status indicators. See also undo method, below


    Basically a convenience function that calls $self->query( undef, TRUE ) ... see above. I've come to realise that having an undo method makes understanding your code a lot easier later.


    Applies all changes ( inserts, deletes, alterations ) in the datasheet to the database. As changes are applied, the record status indicator will be changed back to the original 'synchronised' icon.

    If any errors are encountered, a dialog will be presented with details of the error, and the apply method will return FALSE without continuing through the records. The user will be able to tell where the apply failed by looking at the record status indicators ( and considering the error message they were presented ).

insert ( [ @columns_and_values ] )

    Inserts a new row in the *model*. The record status indicator will display an 'insert' icon until the record is applied to the database ( apply method ).

    You can optionally set default values by passing them as an array of column numbers and values, eg: $datasheet->insert( 2 => "Default value for column 2", 5 => "Another default - for column 5" );

    Note that there are a number of ways of fetching a column number. The recommended way is by accessing the 'column_name_to_number_mapping' hash, eg:


    As of version 0.8, default values from the database schema are automatically inserted into all columns that aren't explicitely set as above.


    Marks all selected records for deletion, and sets the record status indicator to a 'delete' icon. The records will remain in the database until the apply method is called.

column_from_sql_name ( $sql_fieldname )


    Returns a field's column number in the model. Note that you *must* use the SQL fieldname, and not the column heading's name in the treeview.

get_column_value ( $sql_fieldname )

    Returns the value of the requested column in the currently selected row. If multi_select is on and more than 1 row is selected, only the 1st value is returned. You *must* use the SQL fieldname, and not the column heading's name in the treeview.

set_column_value ( $sql_fieldname, $value )

    Sets the value in the given field in the current recordset. You *must* use the SQL fieldname, and not the column heading's name in the treeview.

replace_combo_model ( $column_no, $new_model )

    Replaces the model for a combo renderer with a new one. You should only use this to replace models for a normal 'combo' renderer. An example of when you'd want to do this is if the options in your combo depend on a value on your *main* form ( ie not in the datasheet ), and that value changes. If you instead want to base your list of options on a value *inside* the datasheet, use the 'dynamic_combo' renderer instead ( and don't use replace_combo_model on it ).

sum_column ( $column, [ $conditions ] )

    This is a convenience function that returns the sum of all values in the given column ( by number ). Fetch the column number via the column_from_sql_name() or column_from_column_name() function. Optionally, a hash description of conditions can be passed to activate 'conditional sum' functionality. The conditions hash should contain:


    The column number to perform the comparison on. Fetch the column via the column_from_sql_name() or column_from_column_name() function.


    The type of comparison operation, from a list of:

          == ... a numeric equals operator

      eq ... a string equals operator

      < ... less than

      > ... greater than


    The value to compare the column data to.

max_column( $column )

    Returns the maximum value in column $column

average_column( $column )

    Returns the average value in column $column

count ( $column, [ $conditions ] )

    This is a convenience function that counts the number of records. Fetch the column number via the column_from_sql_name() or column_from_column_name() function. Optionally, a column number AND a hash description of conditions can be passed to activate 'conditional count' functionality. The conditions hash should contain:


    The column number to perform the comparison on. Fetch the column via the column_from_sql_name() or column_from_column_name() function.


    The type of comparison operation, from a list of:

          == ... a numeric equals operator

      eq ... a string equals operator

      < ... less than

      > ... greater than


    The value to compare the column data to.


The new() method requires only 3 bits of information: - a dbh - an sql object - a treeview or vbox

Usually, you would also supply a fields array. All possible keys in the constructor hash are:


    a DBI database handle


    A Gtk2::TreeView to attach to. You must either supply a treeview OR a vbox ( below )


    A Gtk2::VBox to place treeviews in. You must either supply a treevie OR a vbox. You'd use a vbox instead of a treeview if you wanted to activate the 'footer' functionality


    a hash describing the SQL to execute. Note that each clause has it's directive ( ie 'select', 'from', where', 'order by' *ommitted* ) The SQL object contains the following keys:


    the select clause


    the from clause


    the where clause ( may contain values or placeholders )


    an array of values to bind to placeholders


    a command which is passsed directly to the Database Server ( that hopefully returns a recordset ). If a pass_through key is specified, all other keys are ignored. You can use this feature to either construct your own SQL directly, which can include executing a stored procedure that returns a recordset. Recordsets based on a pass_through query will be forced to read_only mode, as updates require that column_info is available. I'm only currently using this feature for executing stored procedures, and column_info doesn't work for these. If you want to enable updates for pass_through queries, you'll have to work on getting column_info working ...

    A boolean to activate the footer treeview. This feature requires you to pass a vbox instead of a treeview, and 2 treeviews will be created, with the footer treeview tracking changes in the main treeview. You will also have to set up footer functions in the field definitions ( see the fields section below )

    A Gtk2::TreeView to render the footer in. Pass one in if you want to arrange / format the treeview yourself, instead of having it automatically created for you ( as in the footer support, directly above )



    the primary key of the table you are querying. This is detected in most cases, so specifying it is not required. In some cases ( ie multi-table queries, which aren't exactly supported ), you can possibly specify a primary key here and then try to use the datasheet to update data ... but don't look at me if things go sour :)


    a boolean to turn on the TreeView's 'multiple' selection mode. Note that if you turn this on, the function get_column_value() will only a value return the 1st selected row. The default for this is FALSE.


    a boolean to lock the entire datasheet to read-only ( record status indicator will also disappear ). Note that you can also set individual fields to read_only


    a coderef to a custom function to run *before* a record is applied. For more information, see USER-DEFINED CALL-BACKS below


    a coderef to a custom function to run *after* a recordset is applied. For more information, see USER-DEFINED CALL-BACKS below


    a coderef to a custom function to run when a row is selected For more information, see USER-DEFINED CALL-BACKS below


    a boolean to turn on dumping of SQL string to the STDOUT on a DBI error


    a string to use in dialogs ( eg apply changes to XXX before continuing, etc ). If you don't pass a friendly_table_name, the sql->{from} clause will be used


    Some text ( including pango markup ) to use for a dialog to present to the user when there are changes to the datasheet that they're about to drop, eg if they close the window or requery without hitting apply. This is only needed if you want a CUSTOM message; a relatively decent one is already raised in these situations


    an array of hashes to describe each field ( column ) in the TreeView. If you don't supply any field definitions, they will be constructed for you, but you will loose the ( very useful ) ability to specify column widths. Each field, described by a hash, has the following possible keys:


    the name of the column. This is the name that you pass to the function: column_from_column_name() The column name is also used in the column's header, unless you specify some header_markup ( see below )

    ! * ! * ! * ! * ! * ! * ! * ! BIG FAT WARNING ! * ! * ! * ! * ! * ! * ! * !

    It is *strongly* recommended that you use the SQL field-name as the column name. The reason I'm recommending this is that I'm considering dropping support completely for NOT doing this :) Things are getting overly complex for no real reason, having to deal with SQL names, field names, etc. The recommended way of accessing column numbers is now via the 'column_name_to_number_mapping' hash ( see below ). If this is going to cause you a problem, you should contact me now and tell me about it ...

    ! * ! * ! * ! * ! * ! * ! * ! BIG FAT WARNING ! * ! * ! * ! * ! * ! * ! * !


    some pango markup to use in the column's header - this will be used instead of the column's name


    the text alignment for this field - possible values are:

     - left
     - centre OR center
     - right
     - a decimal value between 0 ( left aligned ) and 1 ( right aligned )


    percentage of the available width to use for this column. If you specify an x_percent, the actual width of the field is recalculated each time the treeview is resized. The total width of the treeview is calculated, then all the fields with absolute sizing ( see below ) are taken away from the total width, and the remaining width is divided up amongst fields with percentage values set.


    an absolute value to use for the width of this column - ie fixed field width


    name of Gtk2::Ex::Datasheet::DBI renderer. For more information, see the section on RENDERERS below.


    A hash describing numeric formatting. Possible keys are:

       - currency               - boolean - activate currency formatting
       - decimals               - number  - decimal places to render
       - decimal_fill           - boolean - fill values to decimal_places
       - null_if_zero           - boolean - don't render zero values
       - red_if_negative        - boolean - render negatives values in red
       - separate_thousands     - boolean - separate thousands with commas

      Activating the 'currency' key will also activate: decimals => 2, decimal_fill => TRUE, separate_thousands => TRUE

    A string indicating which footer function to use in the footer treeview.

    Current options:

       - sum
       - max
       - average

    Adding more functions is trivial


    the colour to use for the foreground text for this field


    the colour to use for the background for this field


    the size of font to use for the cell ( in render mode ... edit mode is different )


    a boolean flag to set bold font rendering for this field


    a TreeModel to use for a combo renderer ( see COMBOS section below )


    hash describing the setup of a combo or dynamic_combo renderer ( see COMBOS section below )


    a boolean flag that locks data in this field from user edits. Note that you can also set the entire Gtk2::Ex::Datasheet::DBI object to read_only as well.


    a coderef to a custom function to validate data after editing and BEFORE the data is accepted. For more info, see the section on DATA VALIDATION, below.


    an ARRAY of CODEREFs of custom functions to perform when rendering the field. These get attached to the CellRenderer via $renderer->set_cell_data_func .... with the added bonus that you can string one after the other easily. These custom render functions get executed in the order that they are specified in, and as a whole they get executed AFTER any builtin_render_functions ( see below )

    Your custom render function wil be passed:

    ( $tree_column, $renderer, $model, $iter, @other_stuff )

     ... ie @other_stuff is where you'll get anything that you pass into the function when you set it up.

    To allow these functions to be chained together, we copy the value from the model into the $tree_column hash, and then ALL FUNCTIONS SHOULD USE THIS VALUE AND UPDATE IT ACCORDINGLY

    ie In your custom render functions, you should pull the value from $tree_column->{render_value}


    an ARRAY of strings specifying built-in ( ie internal to Gtk2::Ex::Datasheet::DBI ) render functions to format or modify field data when the cell is rendered. As with custom_render_functions ( above ), these are attached to the CellRenderer via $renderer->set_cell_data-func. Built-in render functions are executed in the order that they are specified, and get executed BEFORE any custom_render_functions. Current built-in functions to choose from are:


A Microsoft workaround that understands MS Access' ridiculous time format. While I don't expect people to use Gtk2::Ex::Datasheet::DBI to talk to MS Access (!), people might have DATETIME fields in their database servers to stores TIME data for MS Access. This renderer understands, and sympathises with such problems ... ie values will have: '1899-12-30' prepended to them, so Access recognizes them as 'time' values.


    This function strips off trailing garbage from data before rendering, and is excellent for dealing with Microsoft SQL Server's idiotic lack of a DATE type - ie SQL Server insists that all date values have 00:00:00 appended to the end of them. This function should *only* be used in conjunction with date renderers


    This function is the same as the date_only function ( above ), but for text renderers. ie if you manually force the renderer type to 'text', then use this render function instead of the above one


    This function converts dates in yyyy-mm-dd format to dd-mm-yyyy before rendering


    Gtk2::Ex::Datasheet::DBI offers a number of 'renderers', which are defined per-column ( or field ). The purpose of a renderer is to present data in the datasheet, and to allow you to edit the data with the most appropriate type of interface I can muster. Some of these trigger the use of stock Gtk2::CellRenderer objects, others trigger the use of custom-built Gtk2::CellRenderer objects, and yet others merely do some formatting of information. So they don't *exactly* map to 'renderers' in the sense of Gtk2::CellRenderers, but it's close enough anyway.

    Renderers currently available ( feel free to submit patches for more ), are:


    default if no renderer defined, and suitable for all kinds of text :)


    static combo box with a pre-defined list of options. Note that the model used for this renderer *can* be replaced via the function replace_combo_model(). See below section on COMBOS for more info.


    combo box with a list of options that depends on values in the current row. As well as cutting down on the list of options displayed, this actually improves performance significantly - particularly if you have a lot of data. See below section on COMBOS for more info.


    great for boolean values, and good looking too :)


    good for dates. MUST be in YYYY-MM-DD format ( ie most databases should be OK )


    uses a cell renderer with 3 spin buttons for setting the time


    a progress bar. Give it a decimal between 0 and 1. Read-Only ... in fact I don't know what will happen if you try to apply a datasheet with a progress renderer - I've never tested. Looks nice in read-only datasheets ...


    use this for ... hidden columns!


    This renderer is currently broken and being defaulted back to the text renderer. I'm keeping it in place in the hope that someone will fix it. Alternatively, gtk-2.10.x has added a CellRendererSpinButton, which could be used here. Either way, I'm keeping this around with the intention of one day reactivating it. It's perfectly safe to define fields with a number renderer, and have them default back to text.

Accessing columns

    The new way of accessing columns ( ie fetching a column number ) is via the 'column_name_to_number_mapping' hash, ie:


    will give you the column number.

    This is meant to replace all the other dodgy BS such as:

     ... some of which wasn't documented anyway. If you use any of these functions, it's time to stop using
     them, or email me and tell me why I shouldn't remove them in the next release :)


You can specify a custom function to validate data as it's entered in a cell, and before the data is accepted into the cell. Your function will receive a hash containing:




You can also use functions such as get_column_value() to extract the values of other columns in the currently selected row ( as long as you don't have multi-select turned on ).

Your sub should return TRUE to accept the changes, or FALSE to reject them. If you reject changes, you should provide your own error dialog ( eg via Gtk2::Ex::Dialogs ) explaining what's happening.


For combo and dynamic_combo renderers, the 'model_setup' hash should is ( unfortunately ) quite different. I'm planning on updating the dynamic_combo 'model_setup' hash to be more like everything else, but for now, it's different ...

model_setup for combo renderers

    The model_setup is identical to the form in Gtk2::Ex::DBI. There is an example at the very top of this POD. Descriptions of each hash element:


    An array of field definitions. Each field definition is a hash with the following keys:


    The SQL fieldname / expression


    The ( Glib ) type of column to create for this field in the Gtk2::ListStore. Possible values are Glib::Int and Glib::String.

cell_data_func ( optional )

    A reference to some perl code to use as this columns's renderer's custom cell_data_func. You can use this to perform formatting on the column ( or cell, whatever ) based on the current data. Your function will be passed ( $column, $cell, $model, $iter ), as well as anything else you pass in yourself.


    A hash of SQL related stuff. Possible keys are:


    The from clause


    This can either be a where clause, or a hash with the following keys:


    The where key should contain the where clause, with placeholders ( ? ) for each value. Using placeholders is particularly important if you're assembling a query based on values taken from a form, as users can initiate an SQL injection attack if you insert values directly into your where clause.


    bind_values should be an array of values, one for each placeholder in your where clause.


    An 'order by' clause


    A DBI handle to use instead of the current Gtk2::Ex::DBI DBI handle


model_setup for dynamic_combo renderers

    The current format for dynamic_combos is:


      id              => "ID"
      display         => "Description",
      from            => "SomeTable",
      criteria        => [
                                 field          => "first_where_clause_field",
                                 column_name    => "column_name_of_first_value_to_use"
                                 field          => "second_where_clause_field",
                                 column_name    => "column_name_of_second_value_to_use"
      group_by        => "group by ID, Description",
      order_by        => "order by some_field_to_order_by"


    Briefly ...

    The 'id' key defines the primary key in the table you are querying. This is the value that will be stored in the dynamic_combo column.

    The 'display' key defines the text value that will be *displayed* in the the dynamic_combo column, and also in the list of combo options.

    The 'table' key is the source table to query.

    The 'criteria' key is an array of hashes for you to define criteria. Inside each hash, you have:

      - 'field' key, which is the field in the table you are querying ( ie it will go into the where clause )
      - 'column_name' key, which is the *SQL* column name to use as limiting value in the where clause

    The 'group_by' key is a 'group by' clause. You *shouldn't* need one, but I've added support anyway...

    The 'order_by' key is an 'order by' clause



    You can specify a custom function to run *before* changes to a recordset are applied ( see new() method ). The function will be called for *every* record that has been changed. The user-defined code will be passed a reference to a hash:

        status          => a string, with possible values: 'inserted', 'changed', or 'deleted'
        primary_key     => the primary key of the record in question

    Your code *must* return a positive value to allow the record to be applied - if your code returns FALSE, the changes to the current record will NOT be applied.


    You can specify some code to run *after* changes to a recordset is applied ( see new() method ). It will be called for *every* record that has been changed. The user-defined code will be passed a reference to a hash:

        status          => a string, with possible values: 'inserted', 'changed', or 'deleted'
        primary_key     => the primary key of the record in question


    You can specify some code to run when a row is selected ( see new() method ). Your code will be passed the Gtk2::TreeSelection object ( and anything else you pass yourself ). Nothing internal to Gtk2::Ex::Datasheet::DBI is currently passed to this code, as it is trivial to grab the data you need via get_column_value().


Automatic Column Widths

    You can use x_percent and x_absolute values to set up automatic column widths. Absolute values are set once - at the start. In this process, all absolute values ( including the record status column ) are added up and the total stored in $self->{sum_absolute_x}.

    Each time the TreeView is resized ( size_allocate signal ), the size_allocate method is called which resizes all columns that have an x_percent value set. The percentages should of course all add up to 100%, and the width of each column is their share of available width: ( total width of treeview ) - $self->{sum_absolute_x} * x_percent

    IMPORTANT NOTE: The size_allocate method interferes with the ability to resize *down*. I've found a simple way around this. When you create the TreeView, put it in a ScrolledWindow, and set the H_Policy to 'automatic'. I assume this allows you to resize the treeview down to smaller than the total width of columns ( which automatically creates the scrollbar in the scrolled window ). Immediately after the resize, when our size_allocate method recalculates the size of each column, the scrollbar will no longer be needed and will disappear. Not perfect, but it works. It also doesn't produce *too* much flicker on my system, but resize operations are noticably slower. What can I say? Patches appreciated :)


    For slow network connections, your gtk2 GUI may appear to hang while populating the treeview from a large query. To make things feel more fluid, you can set $Gtk2::Ex::Datasheet::DBI::gtk2_main_in_query = TRUE in your application, which will trigger:

    Gtk2->main_iteration while ( Gtk2->events_pending );

    for each record appended to the treeview. While this slows down operation of your application, it *appears* to have the opposite effect, as the GUI remains responsive. This is even the case when using high speed networks.

    I am considering using a 2nd thread to fetch data, which will remove the need for this, but for now, it's a hack that works. Please supply patches for multi-threaded operation :)

Use of Database Schema

    Version 0.8 introduces querying the database schema to inspect column attributes. This considerably streamlines the process of setting up the datasheet and inserting records.

    If you don't define a renderer, an appropriate one is selected for you based on the field type. The only renderers you should now have to explicitely define are 'hidden', 'combo', and 'dynamic_combo' - the latter 2 you will obviously still have to set up by providing a model.

    When inserting a new record, default values from the database field definitions are also used ( unless you specify another value via the insert() method ).


    If you have Gtk-2.6 or greater, you can use the new CellRendererCombo. Set the renderer to 'combo' and attach your model to the field definition. You currently *must* have a model with ( numeric ) ID / String pairs, which is the usual for database applications, so you shouldn't have any problems. See the example application for ... an example.


Daniel Kasak - dan@entropy.homelinux.org



 - tirelessly offered help and suggestions in response to my endless list of questions

Torsten Schoenfeld

 - wrote custom CellRendererDate ( from the Gtk2-Perl examples )
 - wrote custom CellRendererText ( with improved focus policy ) in Odot which I used here

Gtk2-Perl Authors

 - obviously without them, I wouldn't have gotten very far ...

Gtk2-Perl list

 - yet more help, suggestions, and general words of encouragement


I think you must be mistaken


That's right. These are 'issues', not 'bugs' :)


    For some reason, the 1st time you go to edit a cell with a CellRendererTime, it doesn't receive the current value. It works every other time after this. Weird. Anyone know what's up?

SQL Server compatibility

    To use SQL Server, you should use FreeTDS ==> UnixODBC ==> DBD::ODBC. Only this combination supports the use of bind values in SQL statements, which is a requirement of Gtk2::Ex::Datasheet::DBI. Please make sure you have the *very* *latest* versions of each.

    The only problem I've ( recently ) encountered with SQL Server is with the 'money' column type. Avoid using this type, and you should have flawless SQL Server action.

Other cool things you should know about:

This module is part of an umbrella 'Axis' project, which aims to make Rapid Application Development of database apps using open-source tools a reality. The project includes:

  Gtk2::Ex::DBI                 - forms
  Gtk2::Ex::Datasheet::DBI      - datasheets
  PDF::ReportWriter             - reports

All the above modules are available via cpan, or for more information, screenshots, etc, see: http://entropy.homelinux.org/axis

Crank ON!

21 POD Errors

The following errors were encountered while parsing the POD:

Around line 4456:

You forgot a '=back' before '=head3'

Around line 4493:

=back without =over

Around line 4520:

You forgot a '=back' before '=head3'

Around line 4557:

=back without =over

Around line 4601:

You forgot a '=back' before '=head3'

Around line 4647:

=back without =over

Around line 4766:

You forgot a '=back' before '=head3'

Around line 4984:

You forgot a '=back' before '=head3'

You forgot a '=back' before '=head3'

Around line 4992:

=back without =over

Around line 5025:

=back without =over

Around line 5040:

You forgot a '=back' before '=head2'

Around line 5122:

=back without =over

Around line 5180:

You forgot a '=back' before '=head2'

Around line 5186:

You forgot a '=back' before '=head2'

Around line 5214:

=back without =over

Around line 5216:

=back without =over

Around line 5224:

You forgot a '=back' before '=head2'

Around line 5238:

You forgot a '=back' before '=head2'

Around line 5257:

=back without =over

Around line 5267:

=back without =over

Around line 5277:

=back without =over