NAME
Gtk2::Ex::Datasheet::DBI
SYNOPSIS
use DBI; use Gtk2 -init; use Gtk2::Ex::Datasheet::DBI;
my $dbh = DBI->connect ( "dbi:mysql:dbname=sales;host=screamer;port=3306", "some_username", "salespass", { PrintError => 0, RaiseError => 0, AutoCommit => 1, } );
my $datasheet_def = { dbh => $dbh, table => "BirdsOfAFeather", primary_key => "ID", sql_select => "select FirstName, LastName, GroupNo, Active", sql_order_by => "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 => $group_model }, { 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");
DESCRIPTION
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.
METHODS
new
Object constructor. Expects a hash of key / value pairs. Bare minimum are:
dbh - a DBI database handle
table - the name of the table you are querying
primary_key - the primary key of the table you are querying ( required for updating / deleting )
sql_select - the 'select' clause of the query
Other keys accepted are:
sql_where - the 'where' clause of the query
sql_order_by - the 'order by' clause of the query
multi_selcet - a boolean to turn on the TreeView's 'multiple' selection mode
fields - an array of hashes to describe the fields ( columns ) in the TreeView
Each item in the 'fields' key is a hash, with the following keys:
name - the name to display in the column's heading
x_percent - a percentage of the available width to use for this column
x_absolute - an absolute value to use for the width of this column
renderer - string name of renderer - possible values are currently:
- text - default if no renderer defined )
- number - invokes a customer CellRendererSpin button - see below
- combo - requires a model to be defined as well )
- toggle - good for boolean values )
- none - use this for hidden columns
model - a TreeModel to use with a combo renderer
validation - a sub to run after data entry and before the value is accepted to validate dataIn the case of a 'number' renderer, the following keys are also used:
min - the minimum value of the spinbutton
max - the maximum value of the spinbutton
digits - the number of decimal places in the spinbutton
step - the value that the spinbutton's buttons spin the value by :)query ( [ new_where_clause ], [ dont_apply ] )
Requeries the DB 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 new where clause is passed, it will replace the existing one. If dont_apply is set, *no* dialog will appear if there are outstanding changes to the data.
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.
apply
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 you can use the column_from_name method for fetching column numbers from field names ( see below ).
delete
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_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.
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.
General Ranting
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 :)
CellRendererCombo
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.
Authors
Daniel Kasak - dan@entropy.homelinux.org
Bugs
I think you must be mistaken
Other cool things you should know about
This module is part of a 3-some:
Gtk2::Ex::DBI - forms
Gtk2::Ex::Datasheet::DBI - datasheets
PDF::ReportWriter - reports
Together ( and with a little help from other modules such as Gtk2::GladeXML ), these modules give you everything you need for rapid application development of database front-ends on Linux, Windows, or ( with a little frigging around ) Mac OS-X.
All the above modules are available via cpan, or from: http://entropy.homelinux.org