NAME

Gtk2::Ex::DbLinker::SqlADataManager - a module that get data from a database using SQL::Abstract::More

VERSION

See Version in Gtk2::Ex::DbLinker::DbTools

SYNOPSIS

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

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

To fetch the data from the database

  my $rdbm = Gtk2::Ex::DbLinker::SqlADataManager->new(
	 	dbh => $dbh,
	 	 primary_keys => ["pk_id"],
		select_param =>{-from=>'table', -where=>{id =>{'<'=> 4}}},
 );

To link the data with a Gtk windows, have the Gtk entries ID, or combo ID in the xml glade file set to the name of the database fields: pk_id, field1, field2...

	  $self->{linker} = Gtk2::Ex::DbLinker::Form->new({ 
		    data_manager => $rdbm,
		    builder =>  $builder,
		    rec_spinner => $self->{dnav}->get_object('RecordSpinner'),
  	    	    status_label=>  $self->{dnav}->get_object('lbl_RecordStatus'),
		    rec_count_label => $self->{dnav}->get_object("lbl_recordCount"),
	    });

To add a combo box in the form:

  my $dman = Gtk2::Ex::DbLinker::SqlADataManager->new(
		dbh => $dbh,
		select_param => {
			-columns => "id, name",
			-from => "table",
			-order_by => [+name]
			-where => {-bool => "1=1"}
			},
	);

The first field given in the -columns value will be used as the return value of the combo. noed is the Gtk2combo id in the glade file and the field's name in the table displayed in the form.

$self->{linker}->add_combo(
	data_manager => $dman,
	id => 'noed',
  );

And when all combos or datasheets are added:

$self->{linker}->update;

To change a set of rows in a subform, listen to the on_changed event of the primary key in the main form:

$self->{subform_a}->on_pk_changed($new_primary_key_value);

In the subform_a module:

sub on_pk_changed {
	 my ($self,$value) = @_;
	$self->{jrn_coll}->get_data_manager->query(-where =>{pk_value_of_the_bound_table => $value },
						   );
	...
	}

DESCRIPTION

This module fetches data from a dabase using SQL::Abstract::More to build sql statements and parameters. A new instance of SqlADataManager is created with passing a hash ref of a database handle and -select parameters. This instance is used by a Gtk2::Ex::DbLinker::Form object or to Gtk2::Ex::DbLinker::Datasheet objet constructors.

METHODS

constructor

The parameters to new are passed in as a list of parameters name => value or as a hash reference with the parameters name as keys

Parameters are

dbh,
new_param

Hash ref of parameters for the "new" in SQL::Abstract::More constructor.
select_param,

The value for select_param can be
- a hash reference with the keys used by select in SQL::Abstract::More using the named parameters : -columns or -from, -where, -order_by, -group_by, -having, -union, -for, -wants_details, -limit, -offset or -page_size, -page-index. Use a -where value if you want fetch an initial set of rows. To get a complete table use -where = {-bool => "1=1"}> or -where = {primarykey =>{'>'=>0}}>.
  
  See SQL::Abstract::More for the values.
- a scalar reference where the scalar holds an sql string that will be executed in the DB. Rows are read only, and can't be deleted or added. The query method can't be used to fetch a new set of rows.
primary_keys,
ai_primary_key.

The value for primary_keys and ai_primary_key are arrayrefs holding the field names of the primary key and auto incremented primary keys.

If the table use a autogenerated key, use ai_primary_key instead of primary_keys to set these. If your DB is mysql ai_primary_key should be detected.
defaults : a hash ref of fieldname => default value;
before_query : a code ref to be run at the start of the query method.

dbh, select_param are mandatory but you may omit a -where clause to retrieve an empty set of records.

Gtk2::Ex::DbLinker::SqlADataManager->new({ dbh => $dbh,
				    select_param => {
						-columns => [qw (abo.ref|ref abo.type|type abo.note|note abo.debut|debut abo.fin|fin abo.nofrn)],
						-from   =>[qw/ abo|t1 noabt=nobat jrnabt|t2/]
						-where  => {nofm => $self->{nofm} }
						-order_by => [qw/ +abo.type +abo.ref/],

						},
					});

`query( -where =` { field => $value} );

To display an other set of rows, call the query method on the datamanager instance. The parameter is a list of param => value or a hash ref of the same. The only key is -where and the value follow the same rules of -where parameter in "select" in SQL::Abstract::More. Return the number of rows. To use the Mysql full text index, use {-where = { -bool =>"match(ti, ex, ad) against('+$bla' in boolean mode)" }}>

my $dman = $self->{form_a}->get_data_manager();

$dman->query({ -where=> {nofm=> $f->{nofm} }});
$self->{form_a}->update;

query will not place the recordset position, but in the above example update on the Form (or a Datasheet) instance will. Be sure to call set_row_pos(0) on the datamanager object after query( ... ) in others situations.

`save();`

Insert a new record or update an existing record. Fetch the value from auto_incremented primary key.

`save($field_name =` $value );>

Pass a list or a hash reference to save when a value has to be saved in the database without using $dman-set_field($ field, $value ) >. Use this when you want to change a field that is part of a multiple fields primary key.

`new_row();`

Insert a new row and set the default values.

`delete();`

Delete the row $pos once set_row_pos( $pos ) has been called.

`set_row_pos( $new_pos);`

Change the current row for the row at position $new_pos.

`get_row_pos( );`

Return the position of the current row, first one is 0.

`set_field ( $field_id, $value);`

Sets $value in $field_id. undef as a value will set the field to null.

`get_field ( $field_id );`

return the value of the field $field_id or undef if null.

`get_field_type ( $field_id);`

Return one of varchar, char, integer, date, serial, boolean.

`row_count();`

Return the number of rows.

`get_field_names();`

Return an array of the field names.

`get_primarykeys()`;

Return an array of primary key(s) (auto incremented or not). Can be supplied to the constructor, or is searched by the code.

`get_autoinc_primarykeys();`

Return an array of auto incremented primary key(s). If the names are not supplied to the constructor, the array of primary keys is returned.

SUPPORT

Any Gk2::Ex::DbLinker::SqlADataManaeger questions or problems can be posted to me (rappazf) on my gmail account.

The current state of the source can be extract using Mercurial from http://sourceforge.net/projects/gtk2-ex-dblinker-dbtools/.

AUTHOR

François Rappaz <rappazf@gmail.com>

COPYRIGHT AND LICENSE

CREDIT

Laurent Dami for its robust SQL::Abstract::More module !

To install Gtk2::Ex::DbLinker::DbTools, copy and paste the appropriate command in to your terminal.

cpanm

cpanm Gtk2::Ex::DbLinker::DbTools

CPAN shell

perl -MCPAN -e shell
install Gtk2::Ex::DbLinker::DbTools

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

VERSION

SYNOPSIS

DESCRIPTION

METHODS

constructor

query( -where = { field => $value} );

save();

save($field_name = $value );>

new_row();

delete();

set_row_pos( $new_pos);

get_row_pos( );

set_field ( $field_id, $value);

get_field ( $field_id );

get_field_type ( $field_id);

row_count();

get_field_names();

get_primarykeys();

get_autoinc_primarykeys();