Gtk2::Ex::Datasheet::DBI
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");
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. 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 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 data
As 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 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
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
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 ).
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.
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.
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.
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.
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 ).
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 :)
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
I think you must be mistaken
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
To install Gtk2::Ex::Datasheet::DBI, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Gtk2::Ex::Datasheet::DBI
CPAN shell
perl -MCPAN -e shell install Gtk2::Ex::Datasheet::DBI
For more information on module installation, please visit the detailed CPAN module installation guide.