Bug #2903 for HTML-FormEngine-DBSQL: [PATCH] Better Postgres support and form generation

Hello, Attached is a patch for HTML::FormEngine::DBSQL. It makes the following changes and improvements: - Better sizing for form fields. Many of my text form fields where appearing as 20 characters wide when larger or smaller more appropriate. Now they may appear smaller if that's all the field accomodates. Also, if the size is larger than the default, the field will be turned into a textarea of appropriate size. This keeps the form looking uniform and prevents fields like varchar(500) or varchar(4000) from being super-wide. - The interface to the postgres system tables has been re-written. Creating a "column_info" table is no longer required. Instead, the functionality built-in to DBD::Pg for this purpose is used. This has a few advantages. First, behind the scenes DBD::Pg is supporting the system table formats of several database versions, including 7.1, 7.2 and 7.3, so you have better compatibility. It's also making a small number of queries to get the data (perhaps just 1). rather than one query per table attribute as before. This also means you get back a real size rather than "dtypmod", which should simplify things a bit. There is a remaining "bug" here in that DBD::Pg needs to be patched to return the description field as well. This should be easy. For general table information, this already being fetched into a REMARKS field. I imagine this feature would be welcome addition. This part of the patch would close ticket #2714. I tested this with Postgres 7.1.3 and a CVS version of the soon to be released DBD::Pg 1.30 version.

Only in /usr/local/lib/perl5/site_perl/5.8.0/HTML/FormEngine/: Checks.pm Only in /usr/local/lib/perl5/site_perl/5.8.0/HTML/FormEngine/: Config.pm Only in /usr/local/lib/perl5/site_perl/5.8.0/HTML/FormEngine/DBSQL: Checks.pm Only in /usr/local/lib/perl5/site_perl/5.8.0/HTML/FormEngine/DBSQL: Config.pm diff -ru /usr/local/lib/perl5/site_perl/5.8.0/HTML/FormEngine/DBSQL/PGSQL/DtHandler.pm FormEngine/DBSQL/PGSQL/DtHandler.pm --- /usr/local/lib/perl5/site_perl/5.8.0/HTML/FormEngine/DBSQL/PGSQL/DtHandler.pm Sun Feb 9 11:47:50 2003 +++ FormEngine/DBSQL/PGSQL/DtHandler.pm Fri Jul 4 19:27:58 2003 @@ -28,13 +28,27 @@ sub _dbsql_pgsql_dthandle_string { my ($self,$res,$info) = @_; - $res->{templ} = 'text'; - if($info->{dtypmod} =~ m/^[0-9]+$/ && $info->{dtypmod} > 4) { - $res->{MAXLEN} = $info->{dtypmod} -4; - if(! defined($self->{default}->{text}->{SIZE}) || ($res->{MAXLEN} < $self->{default}->{text}->{SIZE})) { - $res->{SIZE} = $res->{MAXLEN}; - } - } + my $len = $info->{size}; + $res->{MAXLEN} = $info->{size}; + + my $default_field_size = $self->{default}->{text}->{SIZE} || 20; + $res->{SIZE} = $res->{MAXLEN} if($res->{MAXLEN} < $default_field_size); + + if ($len <= $default_field_size) { + # make cells slightly larger than the data in them. + # this is needed to make it look "right" in some browsers. -mls 07/04/03 + $res->{SIZE} = $len+2; + $res->{templ} = 'text'; + } + # If it's larger than the default, turn it into a textarea + # This prevents things like varchar(4000) from looking crazy. -mls 07/04/03 + # The textarea is specially sized to fit the length of the field + else { + my $row_size = int $len/$default_field_size +1; + $res->{templ} = 'textarea'; + $res->{ROWS} = $row_size; + } + } 1; diff -ru /usr/local/lib/perl5/site_perl/5.8.0/HTML/FormEngine/DBSQL/PGSQL.pm FormEngine/DBSQL/PGSQL.pm --- /usr/local/lib/perl5/site_perl/5.8.0/HTML/FormEngine/DBSQL/PGSQL.pm Tue Mar 4 08:50:32 2003 +++ FormEngine/DBSQL/PGSQL.pm Fri Jul 4 20:56:24 2003 @@ -102,26 +102,37 @@ sub get_tbl_struct { my ($self, $tbl, $fields, $donotuse) = @_; - my ($sth, $field, @struct); - $sth = $self->{dbsql}->prepare('SELECT * FROM column_info WHERE relname=\'' . $tbl . '\' AND attname LIKE ?'); - foreach $field (@{$fields}) { - if(defined($field) && ! $donotuse->{$field}) { - $sth->execute($field); - while($_ = $sth->fetchrow_hashref) { - if(! $donotuse->{$_->{attname}}) { - push @struct, { - 'name' => $_->{attname}, - 'notnull' => $_->{attnotnull}, - 'dtyp' => $_->{typname}, - 'default' => $_->{adsrc}, - 'description' => $_->{description}, - 'dtypmod' => $_->{atttypmod} + my @struct; + my $attrs = $self->{dbsql}->func($tbl, 'table_attributes') || []; + + my %fields_in_db; + for my $attr (grep { !$donotuse->{$_->{NAME}} } @$attrs) { + $fields_in_db{$attr->{NAME}} = { + 'name' => $attr->{NAME}, + 'notnull' => $attr->{NULLABLE}, + 'dtyp' => $attr->{TYPE}, + 'default' => $attr->{DEFAULT}, + size => $attr->{SIZE}, + + # not supported for now, but could be added to DBD::Pg + # 'description' => $_->{description}, + + # not used PGSQL.pm anyway -mls 07/04/03 + #'dtypmod' => $_->{atttypmod} }; - } - } - } } - return \@struct; + + # If fields were passed in, we just want that subset + # It's not clear what '%' is used for, but that's how the code worked when I found it.. -mls 07/04/03 + if (ref $fields && $fields->[0] ne '%' ) { + return [map { $fields_in_db{$_} } @$fields]; + } + # Otherwise, return the keys sorted alphabetically, as Postgres would do anyway. + else { + return [ map { $fields_in_db{$_} } sort keys %fields_in_db]; + } + + } 1; Only in /usr/local/lib/perl5/site_perl/5.8.0/HTML/FormEngine/DBSQL: Skin.pm diff -ru /usr/local/lib/perl5/site_perl/5.8.0/HTML/FormEngine/DBSQL.pm FormEngine/DBSQL.pm --- /usr/local/lib/perl5/site_perl/5.8.0/HTML/FormEngine/DBSQL.pm Tue Mar 11 16:27:11 2003 +++ FormEngine/DBSQL.pm Fri Jul 4 18:15:09 2003 @@ -678,21 +678,21 @@ } foreach $tbl (@{$self->{dbsql_tables}}) { - $struct = $self->get_tbl_struct($tbl,$self->{dbsql_fields}->{$tbl},$donotuse{$tbl}); - $self->{dbsql_fields}->{$tbl} = []; - foreach $tmp (@{$struct}) { - $_ = $self->_dbsql_makeconf($tmp,$tbl); - push @{$self->{dbsql_fields}->{$tbl}}, $_->{fname}; - if(defined($_->{prepend})) { - push @fconf, @{retarref($_->{prepend})}; - delete $_->{prepend}; - } - push @fconf, $_; - if(defined($_->{append})) { - push @fconf, @{retarref($_->{append})}; - delete $_->{append}; - } - } + $struct = $self->get_tbl_struct($tbl,$self->{dbsql_fields}->{$tbl},$donotuse{$tbl}); + $self->{dbsql_fields}->{$tbl} = []; + foreach $tmp (@{$struct}) { + $_ = $self->_dbsql_makeconf($tmp,$tbl); + push @{$self->{dbsql_fields}->{$tbl}}, $_->{fname}; + if(defined($_->{prepend})) { + push @fconf, @{retarref($_->{prepend})}; + delete $_->{prepend}; + } + push @fconf, $_; + if(defined($_->{append})) { + push @fconf, @{retarref($_->{append})}; + delete $_->{append}; + } + } } if(defined($self->{dbsql_append})) { Only in /usr/local/lib/perl5/site_perl/5.8.0/HTML/FormEngine/: Handler.pm Only in /usr/local/lib/perl5/site_perl/5.8.0/HTML/FormEngine/: Skin.pm