Daniel Kasak
and 1 contributors

NAME

Gtk2::Ex::DBI - Bind a Gtk2::GladeXML - generated window to a DBI data source

SYNOPSIS

use DBI; use Gtk2 -init; use Gtk2::GladeXML; use Gtk2::Ex::DBI;

my $dbh = DBI->connect ( "dbi:mysql:dbname=sales;host=screamer;port=3306", "some_username", "salespass", { PrintError => 0, RaiseError => 0, AutoCommit => 1, } );

my $prospects_form = Gtk2::GladeXML->new("/path/to/glade/file/my_form.glade", 'Prospects');

my $prospects_form = Gtk2::Ex::DBI->new( { dbh => $dbh , sql => { select => "*" , from => "Prospects" } , form => $prospects } );

DESCRIPTION

This class ties data from a DBI datasource to widgets in a Gtk2+ window ( generated by Glade ). All that is required is that you name your widgets the same as the fields in your data source. You have to set up combo boxes ( ie create your Gtk2::ListStore and attach it to your combo box ) *before* creating your Gtk2::Ex::DBI object.

Steps for use:

* Open a DBI connection

* Create a window from a Gtk2::GladeXML object

* Create a Gtk2::Ex::DBI object and link it to your window

You would then typically create some buttons ( in your Glade object ) and connect them to the methods below to handle common actions such as inserting, moving, deleting, etc.

METHODS

new

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

query ( [ where_object ] )

    Requeries the Database Server, either with the current where clause, or with a new one ( if passed ).

    Version 2.x expects a where_object hash, containing the following keys:

where

    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

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

    Version 1.x expected to be passed an optional string as a new where clause. This behaviour is still supported for backwards compatibility. If a version 1.x call is detected ( ie if where_object isn't a hash ), any existing bind_values will be deleted

insert

    Inserts a new record in the *in-memory* recordset and sets up default values, either from database defaults, or optionally overridden with values from the default_values hash. If you're using sequences, the next sequence value will not be fetched yet; this will happen when the user first starts entering data in the new record.

count

    Returns the number of records in the current recordset.

move ( offset, [ absolute_position ] )

    Moves to a specified position in the recordset - either an offset, or an absolute position. If an absolute position is given, the offset is ignored. If there are changes to the current record, these are applied to the Database Server first. Returns TRUE if successful, FALSE if unsuccessful.

apply

    Apply changes to the current record back to the Database Server. Returns TRUE if successful, FALSE if unsuccessful.

revert

    Reverts the current record back to its original state. Deletes the in-memory recordset if we were inserting a new record.

undo

    Synonym of revert

delete

    Deletes the current record.

set_widget_value( fieldname, value )

    A convenience function to set a widget ( via it's fieldname ) with a given value. This function will automatically set up data formatting for you ( eg numeric, date ), based on the assumption that you are giving it data in the format that the database server likes ( for example, yyyymmdd format dates ).

get_widget( widget_name )

    A convenience function to get a widget. Works with both Glade and GTK Builder. If no object with the given name exists, we also check if an object with the given name, starting with $self->{widget_prefix} exists. widget_prefix can be passed in the constructor.

get_widget_value( widget_name )

    Complimentary to the set_widget_value, this will return the value that data in a current widget REPRESENTS from the database's point of view, ie with formatting stripped. You can call get_widget_value() on non-managed widgets as well as managed ones.

original_value( fieldname )

    A convenience function that returns the original value of the given field ( at the current position in the recordset ), since the recordset was last applied. This is also the ONLY way of fetching the value of a field that is IN the recordset, but NOT represented by a widget.

sum_widgets( @widget_names )

    Convenience function that returns the sum of all given widgets. get_widget_value() is used to retrieve each value, which will stips formatting from managed widgets, but you can include non-managed widgets as well - they just have to be in the same window.

lock

    Locks the current record to prevent editing. This is implemented by setting a value in a field that you specify as a value of data_lock_field in your constructor. The apply() method is automatically called when locking, and if apply() fails, lock() also fails.

unlock

    Unlocks a locked record so the user can edit it again.

setup_combo ( widget_name, [ new_where_object ] )

    Creates a new model for the combo of widget_name. You can use this to refresh the items in a combo's list. You can optionally pass a hash containing a new where_object ( where clause and bind_values ).

    You can call this method on widgets that aren't being managed ( ie aren't in the field list ).

setup_autocompletion ( widget_name, [ new_where_object ] )

    Creates an autocompletion object for a text entry. Autocompletions are similar to combo boxes, but don't have a button thing. They're invisible additions to a text entry that magically pop up a list of values to select from.

calculator ( Gtk2::Widget )

    Opens up a simple calculator dialog that allows the user to enter a list of values to be added. The result will be applied to the given widget ( which assumes a set_text() method ... ie a Gtk2::Entry would be a good choice ).

find_dialog ( [ field ] )

    Opens a find 'dialog' ( a window in GTK speak ) that allows the user to query the active table ( whatever's in the sql->{from} clause ). This will allow them to *alter* the where clause. If you only want them to be able to *append* to the existing where clause, then set the disable_full_table_find key to TRUE ( see 'new' method ).

    If an optional field is passed, this will be inserted into the dialog as the first field in the criteria list.

    Note that the user can currently activate the find_dialog by right-clicking in a text field. To disable this behaviour, set the disable_find key to TRUE ( see 'new' method ).

position

    Returns the current position in the recordset ( starting at zero ).

fieldlist

    Returns a fieldlist as an array, based on the current query. Mainly for internal Gtk2::Ex::DBI use

find_dialog

    Pops up a rudimentary 'find' dialog for searching. FIXME :)

INTERNAL METHODS

assemble_new_record

    This method is called by the insert method. It actually creates the hash that represents the new record. It sets default values using either database defaults or from the default_values hash.

paint

    Paints the form with current data.

fetch_new_slice

    Fetches a new slice of records based on the apeture size.

changed

    Called each time a widget value that Gtk2::Ex::DBI is managing changes.

last_insert_id

    Wrapper around DBI's last_insert_id(), with some smarts for various broken bits in different drivers.

record_status_label_set

    Sets the record status label.

paint_calculated

    Sets the values of all calculated fields. Called from the changed method.

set_record_spinner_range

    Sets the record spinner range. Nothing to see here.

process_entry_keypress

    Called on every keypress. Injects a 'tab' keypress when the user hits 'enter'.

reset_record_status

    Resets the record status

formatter_number_to_widget

    Performs numeric formatting

formatter_date_to_widget

    Performs date formatting

formatter_number_from_widget

    Opposite of formatter_number_to_widget

formatter_date_from_widget

    Opposite of formatter_date_to_widget

parse_sql_server_default

    Parses default definitions from SQL Server ( v7 )

build_right_click_menu

    Builds the menu that pops up when you right-click most widgets that we manage

set_active_iter_for_broken_combo_box

    This method is called when a ComboBoxEntry's value is changed See http://bugzilla.gnome.org/show_bug.cgi?id=156017

    Wow ... a 10-year-old bug. They're not going to fix it :(

    Called when user activates search functionality in the find dialog

find_dialog_add_criteria

    Creates a new row of widgets for entering more criteria in the find dialog

calculator_process_editing

    Calculates totals in our calculator

destroy_signal_handlers

    Destroys all signal handlers Gtk2::Ex::DBI has created.

destroy_self

    Internal use only.

destroy

    Destroys itself

CONSTRUCTION

The new() method expects a hash of key / value pairs.

dbh

    A DBI database handle

form

    The Gtk2::GladeXML object to bind to. You need to set either the 'form' or 'builder' key.

builder

    A Gtk2::Builder object to bind to. You need to set either the'form' or 'builder' key.

sql

    The sql object describes the query to be executed to fetch your records. Note that in contrast to version 1.x, all the keywords ( select, from, where, order by, etc ) are *OMMITTED* ... see above example. This is for consistency and ease of manipulating things. Trust me.

    Minimum requirements for the sql object are the 'select' and 'from' keys, or alternatively a 'pass_through'. All others are optional.

    Details:

select

    The SELECT clause

from

    The FROM clause

where

    The WHERE clause ( try '0=1' for inserting records )

bind_values

    An array of values to bind to placeholders ... you ARE using placeholders, right?

order_by

    The ORDER BY clause

pass_through

    A command which is passsed directly to the Database Server ( that hopefully returns a recordset ). If a pass_through key is specified, then this will be used as the SQL command, and all the other keys will be 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 ...

That's it for essential keys. All the rest are optional.

widgets

    The widgets hash contains information particular to each widget, including formatting information and SQL fieldname to widget name mapping. See the WIDGETS section for more information.

combos

    The combos hash describes how to set up GtkComboBoxEntry widgets. See COMBOS section for more informaton.

primary_key

    The PRIMARY KEY of the table you are querying.

    As of version 2.0, the primary key is automatically selected for you if you use MySQL. Note, however, that this will only work if the FROM clause contains a single table. If you have a multi-table query, you must specify the primary_key, otherwise the last primary_key encountered will be used. I recommend against using multi-table queries anyway.

on_current

    A reference to some Perl code to run when moving to a new record

before_query

    A reference to some Perl code to run *before* executing a query. Your code will be passed the 'where' object. Keep in mind this could either be a scalar or a hash, depending on how you're using it. Return TRUE to allow the query method to continue, or FALSE to prevent the query method from continuing.

before_apply

    A reference to some Perl code to run *before* applying the current record. Return TRUE to allow the apply method to continue, or FALSE to prevent the apply method from continuing.

on_apply

    A reference to some Perl code to run *after* applying the current record. Your code will be passed a reference to a hash of info about the current record:

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

on_undo

    A reference to some Perl code to run *after* undo() is called. This can either be called by your code directly, or could be called if the user makes changes to a recordset, and then wants to close the form / requery without applying changes, which will call undo()

on_changed

    A reference to some Perl code that runs *every* time the changed signal is fired. Be careful - it's fired a LOT, eg every keypress event in entry widgets, etc

on_initial_changed

    A reference to some Perl code that runs *only* when the record status initially changes for each record ( subsequent changes to the same record won't trigger this code )

auto_apply

    A boolean that will cause datasheets to *automatically* apply changes if a new query is run while outstanding changes exist, or if the user tries to close a form with outstanding changes ... ie NO question dialog will appear

calc_fields

    A hash of fieldnames / Perl expressions to provide calculated fields

apeture

    The size of the recordset slice ( in records ) to fetch into memory. ONLY change this BEFORE querying

record_spinner

    The name of a GtkSpinButton to use as the record spinner. The default is to use a widget called RecordSpinner. However there are a number of reasons why you may want to override this. You can simply pass the name of a widget that *doesn't* exist ( ie NONE ) to disable the use of a record spinner. Otherwise you may want to use a widget with a different name, for example if you have a number of Gtk2::Ex::DBI objects connected to the same Glade XML project.

friendly_table_name

    This is a string you can use to override the default table name ( ie $self->{sql}->{from} ) in GUI error messages.

manual_spinner

    Disable automatic move() operations when the RecordSpinner is clicked

read_only

    Whether we allow updates to the recordset ( default = FALSE ; updates allowed )

data_lock_field

    The name of a field that controls record locking. If this field contains a non-zero or not null value at the point of the on_current event, the record will be LOCKED from edits and deletes. See also the lock() and unlock() methods

defaults

    A HOH of default values to use when a new record is inserted

quiet

    A flag to silence warnings such as missing widgets

status_label

    The name of a label to use to indicate the record status. This is especially useful if you have more than 1 Gtk2::Ex::DBI object bound to a single Gtk2::GladeXML object

schema

    The schema to query to get field details ( defaults, column types ) ... not required for MySQL

disable_full_table_find

    Don't allow the user to replace the where clause; only append to the existing one

disable_find

    Disable the 'find' item in the right-click menu of GtkText widgets ( ie disable user-initiated searches )

dont_update_keys

    Don't include primary keys in update statements. Some databases don't like that.

widget_prefix

    A string to prefix to widget names when getting hold of them ( ie via Glade or Builder ). NOTE: we FIRST try without the widget prefix.

auto_incrementing

    A flag ( default ON ) to indicate whether we should try to poll the last inserted ID after an insert.

WIDGETS

The widgets hash contains information particular to each managed widget. Each hash item in the widgets hash should be named after a widget in your Glade XML file. The following are possible keys for each widget:

sequence_sql

    If you set this, it can be any SQL that can be executed and then fetched to return a value that will be used as a sequence. The value will be applied to the current widget. This will happen each time editing begins in a new record.

sequence_dbh

    If you want your sequence_sql to be executed against a different connection to your main dbh, pass a sequence_dbh.

sql_fieldname

    The sql_fieldname is, as expected the SQL fieldname. This is the name used in selects, updates, deletes and inserts. The most common use ( for me ) is to support SQL aliases. For example, if you have a complex window that has a number of Gtk2::Ex::DBI objects attached to it, you may encounter the situation where you have name clashes. In this case, Gtk2::Ex::DBI will use the sql_fieldname when talking to the database, but will bind to the widget which matches this widget hash's name. Another ( perhaps more natural ) way of generating this behaviour is to simply create an alias in your SQL select string. Gtk2::Ex::DBI parses the select string and populates the sql_fieldname key of the widgets hash where appropriate for you.

number

    This is a HASH of options to control numeric formatting. Possible keys are:

decimal_places

    You can specify the number of decimal places values are rounded to when being displayed. Keep in mind that if a user edits data, when they apply, the value displayed in the widget will be the one applied. This default to 2 if you set the 'currency' field.

decimal_fill

    Whether to fill numbers out to the specified number of decimal places. This is automatically selected if you set the 'currency' field.

currency

    Whether to apply currency formatting to data. It adds a dollar sign before values. It also sets the following options if they aren't already specified: - decimal_places - 2 - decimal_fill - TRUE - separate_thousands - TRUE

percentage

    Whether to convert values to percentage when rendering to a widget. Note that if the number hash exists for a widget, then Gtk2::Ex::DBI will *always* check for percentages, and convert when necessary, when fetching a value *from* a widget ( ie whether the percentage key is set or not )

separate_thousands

    Whether to separate each group of 3 digits with a comma before rendering to a widget. If the number hash exists, values will *always* have commas stripped from them when fetching a value *from* a widget ( ie whether the separate_thousands key is set or not )

date

    This is a HASH of options controlling date formatting. Possible options are:

format

    This formatter converts dates from the international standard ( yyyy-mm-dd ) to the Australian ( and maybe others ) fomat ( dd-mm-yyyy ). If you use this formatter, you should also use the complementary output_formatter, also called date_dd-mm-yyyy ... but in the output_formatter array.

strip_time

    This formatter strips off the end of date values. It is useful in cases where the database server returns a DATETIME value and you only want the DATE portion. Keep in mind that when you apply data, you will only be passing a DATE value back to the database.

COMBOS

Gtk2::Ex::DBI uses the GtkComboBoxEntry widget, which is available in gtk 2.4 and above. To populate the list of options, a model ( Gtk2::ListStore ) is attached to the combo. Gtk::Ex::DBI expects this model to have the ID in the 1st column, and the String column 2nd column. You can pack as many other columns in as you like ... at least for now :)

If you choose to set up each combo's model yourself, you *must* do this before constructing your Gtk2::Ex::DBI object.

Alternatively you can pass a hash of combo definitions to the constructor, and they will be set up for you. If you choose this method, you get a couple of other features for free. You will be able to refresh the combo's model with the setup_combo() method ( see above ). Users will also be able to trigger this action by right-clicking in the combo's entry and selecting 'refresh'. You will also get autocompletion set up in the combo's entry widget ( this is triggered after typing the 1st character in the combo's entry ).

To make use of the automated combo setup functionality, create a key in the combos hash, with a name that matches the GtkComboBoxEntry's widget name in your glade xml file. Inside this key, create a hash with the following keys:

sql

    A hash of SQL related stuff. Possible keys are:

select

    The select clause that defines the fields you want in your combo

from

    The from clause

where_object

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

where

    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

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

order_by

    An 'order by' clause

alternate_dbh

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

fields ( optional )

    An array of field definitions. Note that the fields array is now optional. If you ommit it, simply define a 'select' key in the SQL hash, and the fields will be automatically set up ( with Glib::String types for each column ). The only reason you'd really want to define a 'fields' array now is to set a cell_data_func.

    Each field definition is a hash with the following keys:

name

    The SQL fieldname / expression

type

    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.

Class behaviour flags

$Gtk2::Ex::DBI::highlight_ok_colour

    The colour to use in various places for 'ok' type highlighting. Currently this only affects the record status label, when the record is synchronised

$Gtk2::Ex::DBI::highlight_colour

    The colour to use in various places for 'warning' type highlighting. Currently this only affects the record status label, when the record is locked or changed.

ISSUES

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::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.

BUGS

'destroy' method doesn't currently work

I don't know what the problem with this is. I attach a *lot* of signals to widgets. I also go to great lengths to remember them all and disconnect them later. Perhaps I'm missing one of them? Perhaps it's something else. Patches gladly accepted :)

AUTHORS

Daniel Kasak - d.j.kasak.dk@gmail.com

CREDITS

Muppet

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

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

Other cool things you should know about:

This module is part of an umbrella 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