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 queryOther 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 TreeViewEach item in the 'fields' key is a hash, with the following possible 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 ( diabled and reverts to text )
- combo - static combo box with a pre-defined list of options
- dynamic_combo - combo box that depends on values in the current row
- toggle - good for boolean values
- date - good for dates - MUST be in YYYY-MM-DD format ( ie most databases should be OK )
- time - supurb for times - MUST be in 24-hour format
- hidden - use this for hidden columns
model - a TreeModel to use with a combo renderer
model_setup - object describing the setup of a dynamic_combo ( see below )
validation - a sub to run after data entry and before the value is accepted to validate dataAs of version 0.8, the database schema is queried and a suitable renderer is automatically selected if one is not specified. You will of course still have to set up combos yourself.
In 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 :)Note that as my MOFO::CellRendererSpinButton package is broken ( doesn't accept changes to value ), any fields with a number renderer will currently default back to a text renderer. I will re-enable the number renderer as soon as I figure out what the problem is. In the meantime, you can always write a small function that checks for numeric input and refer to it in your field's 'validation' key.
For dynamic_combo renderers, the 'model_setup' object should take the following form:
{
id => "ID"
display => "Description",
table => "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 clauseThe '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
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. See also undo method, below
undo
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.
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 ).
As of version 0.8, default values from the database schema are automatically inserted into all columns that aren't explicitely set as above.
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.
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 ).
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 :)
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 ).
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 an umbrella project, 'Axis Not Evil', 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_not_evil