Possible DBIx::Recordset changes

[Kee Hinckley <na...@somewhere.com>]

I wanted to run these by people before I tried anything.

1. A '$/!' argument that can be passed to any command and is appended 
after the SQL command.  For instance:
	$$set->Insert({ '$option' => 'DELAYED' ... });

2. A new command called Replace.  It does a Delete followed by an 
Insert in those databases that don't support Replace.

3. I'd like an enhancement to make IN work with placeholders, but 
that should probably happen at the DBI level.  Ideally you should be 
able to say
	'$where'	=> 'foo IN ?', # or IN (?) ?
	'$values'	=> [\@list],
and have it do the right thing (which would mean turning @list into 
either (n,n,n) or ('a','a','a')).  Recordset does this in the foo => 
[@bar] case, but there's no good way to do it when you have to use a 
$where clause.

4. I know at one time someone did a patch to let DBIx::Recordset work 
with bigints.  Is there an official solution there?  At some time or 
other I added the following to my code, but I have no idea whether it 
does anything:
     delete $set->{'*NumericTypes'}->{-5};       # treat bigints as strings

5. I often have forms which need to know all the possible values of 
an enum or a set.  Since I'm using mysql I wrote the following, but 
I'd be interested in a builtin, non-db-specific, solution.  This 
function returns a has with the following entries:
	a mapping from the enum/set number position to it's name
		1 => ITEM1, 2 => ITEM2
	a mapping from the name to the number (both upper and lower case)
		ITEM1 => 1, item => 1, ITEM2 => 2, item2 => 2
	-default => the default value for the enum/set
	-names	 => an array of the names
That let's me do something like this in my code:
         [- %enums = EnumValues(DBInit(), 'domain', 'status') -]
         <th align=left>Status:</th>
         <td>
         <select name="status" class="formbutton">
         <option value="[+ $enums{-names}->[$row] +]">[+ 
$enums{-names}->[$row] +]</option>
         </select>
         </td>


sub EnumValues {
     my ($db, $table, $name) = (@_);
     my (%enum, $sth, $hash, $type, @types);

     if ($db->can('DBHdl')) {    # DBIx::Recordset, DBIx::Database
         $db = $db->DBHdl();
     } elsif (!$db->can('prepare')) {
         die("DBIx::Enum: $db is not a supported database handle.");
     }
     $sth = $db->prepare("show columns from $table like '$name'");
     $sth->execute();
     $hash = $sth->fetchrow_hashref();
     $type = $hash->{Type};
     # enum('NONE','USER','SUPPORT','STAFF','ADMIN')
     die("DBIx::Enum: Type of $name in $table is not enum: $type") if 
($type !~ /^(enum|set)/);
     $type =~ s#.*\('(.*)'\).*#$1#;
     @types = split(/','/, $type);
     for (my $i = 1; $i <= @types; ++$i) {
         $enum{$types[$i-1]} = $i;
         $enum{uc($types[$i-1])} = $i;
         $enum{lc($types[$i-1])} = $i;
     }
     $enum{-default} = $hash->{Default};
     $enum{-names} = \@types;

     return %enum if (wantarray);
     return \%enum;
}

-- 
Kee Hinckley
http://www.messagefire.com/         Next Generation Spam Defense
http://commons.somewhere.com/buzz/  Writings on Technology and Society

I'm not sure which upsets me more: that people are so unwilling to accept
responsibility for their own actions, or that they are so eager to regulate
everyone else's.

---------------------------------------------------------------------
To unsubscribe, e-mail: embperl-unsubscribe@perl.apache.org
For additional commands, e-mail: embperl-help@perl.apache.org

Re: Possible DBIx::Recordset changes

[Angus Lees <gu...@inodes.org>]

At Fri, 14 Nov 2003 13:29:45 +0100, Kaare Rasmussen wrote:
> > 3. I'd like an enhancement to make IN work with placeholders, but
> 
> Same.
> Now we're talking placeholders, I'd like them to work anywhere. It's
> necessary with subselects with where-clauses.

If your database can actually take advantage of placeholders in order
to pre-parse the request (and generate a search plan, etc), then where
it allows placeholders is up to it.

-- 
 - Gus


---------------------------------------------------------------------
To unsubscribe, e-mail: embperl-unsubscribe@perl.apache.org
For additional commands, e-mail: embperl-help@perl.apache.org

Re: Possible DBIx::Recordset changes

[Kaare Rasmussen <ka...@kakidata.dk>]

> 2. A new command called Replace.  It does a Delete followed by an
> Insert in those databases that don't support Replace.

Would be nice.

> 3. I'd like an enhancement to make IN work with placeholders, but

Same.
Now we're talking placeholders, I'd like them to work anywhere. It's necessary 
with subselects with where-clauses.

Another request is to enable the use of table aliases.

-- 
Kaare Rasmussen            --Linux, spil,--        Tlf:        3816 2582
Kaki Data                tshirts, merchandize      Fax:        3816 2501
Howitzvej 75               Åben 12.00-18.00        Email: kar@kakidata.dk
2000 Frederiksberg        Lørdag 12.00-16.00       Web:      www.suse.dk


---------------------------------------------------------------------
To unsubscribe, e-mail: embperl-unsubscribe@perl.apache.org
For additional commands, e-mail: embperl-help@perl.apache.org