Gtk3::Ex::Datasheet::DBI
use DBI; use Gtk3 -init; use Gtk3::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, 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 = Gtk3::Ex::Datasheet::DBI->new( $datasheet_def ) || die ("Error setting up Gtk3::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' Gtk3::TreeView - I use Gtk3::GladeXML, but I assume you can do it the old-fashioned way
* Create a Gtk3::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.
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 ).
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:
$datasheet->{name_to_number_mapping}->{some_column_name}
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.
DEPRECIATED - see COLUMN NAMING section
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.
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.
Sets the column's header markup ( ie the label at the top of the column ) to the new string $markup
Export data to csv format
%options is a hash containing:
The full path to the destination file
An array containing the column NUMBERS ( ie from $datasheet->{column_name_to_number_mapping } ) that you want included in the csv
Convert data into an array of arrays ( ie like you're get from $dbh->selectall_arrayref )
%options is an optional hash containing:
An array containing the column NUMBERS ( ie from $datasheet->{column_name_to_number_mapping } ) that you want included in the array
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 ).
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.
Returns the maximum value in column $column
Returns the average value in column $column
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 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 Gtk3::TreeView to attach to. You must either supply a treeview OR a vbox ( below )
A Gtk3::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 )
a limit clause
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 that activates column sorting. If this is set, the user will be able to click on a column heading to sort by that column. All columns types should now sort properly. Please let me know if they don't.
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
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 Gtk3::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 default font size to use for cells that support the font property. This will be overridden if you supply a font_size for an individual cell
A boolean to activate fixed-row-height mode. This will set all rows to be slightly bigger ( x 1.8 ) than the default_font_size ( ie above ... you MUST set this value ). FIXME: This could possibly be done in a more elegant fashion
POSSIBLY DANGEROUS
an array of primary keys of the table you are querying. These are detected in most cases, so you shouldn't have to specify them. In some cases ( ie multi-table queries, which aren't exactly supported ), you can *possibly* specify an array of primarys 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 boolean to force cells to be editable ( eg so you can copy from them ) even if 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 some code to run before the query() method is called
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 ...
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 Gtk3::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 - percentage - boolean - activate percentage formatting - decimals - number - decimal places to render - decimal_fill - boolean - fill values to decimal_places - null_if_zero - boolean - don't render zero values - highlight_negative - boolean - render negatives values in red OR $Gtk3::Ex::Datasheet::DBI::highlight_colour ( see below ) - separate_thousands - boolean - separate thousands with commas
Activating the 'currency' key will also activate: decimals => 2, decimal_fill => TRUE, separate_thousands => TRUE
If you activate percentage formatting, values should be in decimal format, ie 50% == 0.5
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 to activate text wrapping for the cell
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 Gtk3::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 Gtk3::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 Gtk3::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
Gtk3::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 Gtk3::CellRenderer objects, others trigger the use of custom-built Gtk3::CellRenderer objects, and yet others merely do some formatting of information. So they don't *exactly* map to 'renderers' in the sense of Gtk3::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 ...
the image renderer sets up a CellRendererPixbuf renderer for you. To render your image, you have to specify your own rendering code in 'custom_render_functions' ( see above ). Your code will receive:
( $tree_column, $renderer, $model, $iter )
You can then decide what to render, based on the data in the current cell / row, and render it with:
$renderer->set( pixbuf => $some_pixbuf );
The datasheet model holds a Glib::String column in the place of the image column, so you can store either numeric or string data in the image column
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.
The new way of accessing columns ( ie fetching a column number ) is via the 'column_name_to_number_mapping' hash, ie:
$datasheet->{column_name_to_number_mapping}->{your_column_name}
will give you the column number.
This is meant to replace all the other dodgy BS such as:
column_from_column_name() column_from_name() column_from_sql_name() column_name_to_sql_name() ... 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:
{
renderer, text_path, new_text, model
}
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 Gtk3::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 ...
The model_setup is identical to the form in Gtk3::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 Gtk3::ListStore. Possible values are Glib::Int and Glib::String.
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 Gtk3::Ex::DBI DBI handle
---
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 model => the treemodel that the datasheet it based on iter => the treeiter of the current row }
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 of info about the current record:
You can specify some code to run when a row is selected ( see new() method ). Your code will be passed the Gtk3::TreeSelection object ( and anything else you pass yourself ). Nothing internal to Gtk3::Ex::Datasheet::DBI is currently passed to this code, as it is trivial to grab the data you need via get_column_value().
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 Gtk3 GUI may appear to hang while populating the treeview from a large query. To make things feel more fluid, you can set $Gtk3::Ex::Datasheet::DBI::gtk2_main_in_query = TRUE in your application, which will trigger:
Gtk3::main_iteration while ( Gtk3::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 :)
This is the colour used to highlight negative values when the 'highlight_negative' key is set in the number hash. The default value to use is red.
This is the standard colour to give ( positive ) values when teh 'highlight_negative' key is set in the number hash. The default value to use is black.
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 - d.j.kasak.dk@gmail.com
Muppet
- tirelessly offered help and suggestions in response to my endless list of questions
Torsten Schoenfeld
- wrote custom CellRendererDate ( from the Gtk3-Perl examples ) - wrote custom CellRendererText ( with improved focus policy ) in Odot which I used here
Gtk3-Perl Authors
- obviously without them, I wouldn't have gotten very far ...
Gtk3-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?
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 Gtk3::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.
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:
Gtk3::Ex::DBI - forms Gtk3::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
31 POD Errors
The following errors were encountered while parsing the POD:
You forgot a '=back' before '=head3'
=back without =over
=over should be: '=over' or '=over positive_number'
You forgot a '=back' before '=head2'
To install Gtk3::Ex::DBI, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Gtk3::Ex::DBI
CPAN shell
perl -MCPAN -e shell install Gtk3::Ex::DBI
For more information on module installation, please visit the detailed CPAN module installation guide.