Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

selectall_hashref structure

by 0xbeef (Hermit)
on Dec 24, 2009 at 10:58 UTC ( #814210=perlquestion: print w/ replies, xml ) Need Help??
0xbeef has asked for the wisdom of the Perl Monks concerning the following question:

Esteemed monks,

I have written a function that I for example invoke like so:

myfunction($id, { max => 90, cval => 89 });

The Dumper output of the resulting hash ends up something like the following:

$VAR1 = { 'D96C3A4B' => { 'cval' => '90', 'max' => '92' },

However, when I then store and retrieve these values from a little SQL database using selectall_hashref($sql, [ 'id','key']) they become:

'1C8962EB' => { 'cval' => { 'value' => '90', 'id' => '1C8962EB', 'key' => 'cval' }, 'max' => { 'value' => '92', 'id' => '1C8962EB', 'key' => 'max' } }

The table in question is simple, being created something like 'CREATE TABLE evkeypairs (id INT,key TEXT,value TEXT);'

Is it possible to craft or map the select in such a way so as to not insert the literal SQL column names similar to the format I use when calling myfunction()? Else, the many calls I will need to make to myfunction() will become tedious.

I would like (if at all possible) to avoid making any copies of the hash so as to avoid memory overhead.

Niel

Comment on selectall_hashref structure
Select or Download Code
Re: selectall_hashref structure
by Anonymous Monk on Dec 24, 2009 at 11:43 UTC
    #!/usr/bin/perl -- use strict; use warnings; use DBI; use DBD::SQLite; use Data::Dumper; { my $dbh = DBI->connect( 'dbi:SQLite:dbname=test.sqlite', undef, undef, { RaiseError => 1, PrintError => 1, }, ); eval { $dbh->do('CREATE TABLE evkeypairs (id INTEGER PRIMARY KEY NOT +NULL,key TEXT,value TEXT);'); $dbh->do(q!INSERT INTO evkeypairs (key, value) VALUES ('foo',' +bar');!); $dbh->do(q!INSERT INTO evkeypairs (key, value) VALUES ('kung', +'foo');!); } or warn "$@"; print Dumper( $dbh->selectall_hashref('select * from evkeypairs ', + [ 'id','key']) ),"\n"; print Dumper( $dbh->selectall_hashref('select * from evkeypairs ', + 'id') ),"\n"; $dbh->disconnect; use autodie qw' unlink '; unlink 'test.sqlite'; } __END__ $VAR1 = { '1' => { 'foo' => { 'value' => 'bar', 'id' => '1', 'key' => 'foo' } }, '2' => { 'kung' => { 'value' => 'foo', 'id' => '2', 'key' => 'kung' } } }; $VAR1 = { '1' => { 'value' => 'bar', 'id' => '1', 'key' => 'foo' }, '2' => { 'value' => 'foo', 'id' => '2', 'key' => 'kung' } };
      I am actually trying to avoid having the above hash structure in the related function I mentioned, since this function deals with many different attributes that I find convenient to provide via the hash.

      If I were to use a similar hash structure to your example in this function, it would now need to be called in the following (tedious) way:

      myfunction($id,{ kung => { value => foo }, foo => { value => bar}});

      Here is the ideal hash structure with some example attributes that I am trying to attain through the select:

      Attributes now: $VAR1 = { '49CBAF4B' => { 'occurrences' => 999, 'object' => 'device123', 'timestamp' => 1261660376, 'value' => 90, 'max' => 96 } };

      If this cannot be done, I might be forced to map the results to a hash with the desired layout, or look at a different approach altogether.

      Niel

Re: selectall_hashref structure
by vitoco (Friar) on Dec 24, 2009 at 15:41 UTC

    I'm not sure about what you want to do, but I hope this helps:

    #!perl use strict; use warnings; use Data::Dumper; my $id = '1C8962EB'; myfunction($id, { max => 90, cval => 89 }); sub myfunction { my %p = %{$_[1]}; print Dumper(\%p); my %newp = map { $_ => { value => $p{$_}, key => $_, id => $_[0] } + } keys %p; print Dumper(\%newp); } __END__ $VAR1 = { 'max' => 90, 'cval' => 89 }; $VAR1 = { 'cval' => { 'value' => 89, 'id' => '1C8962EB', 'key' => 'cval' }, 'max' => { 'value' => 90, 'id' => '1C8962EB', 'key' => 'max' } };
      Thanks it does help, I was just wondering if there was any way to avoid having to use map or another transformation at all.

      To clarify the original objective, it was to have a common hash structure between the function I mentioned and results from selectall_hashref.

      I was trying to find a way to manipulate the selectall_hashref to provide a resulting hash as described that can be used as $$hash{$id}{$key} = $value.

      Your code provides a valid solution albeit through a different approach - "modify the structure passed to the function to look like the result from the selectall hash", and should work just fine unless someone can come up with an even better solution.

Re: selectall_hashref structure
by bobf (Monsignor) on Dec 24, 2009 at 16:42 UTC

    If I understand the question (and your subsequent clarification) correctly, I think this should do the trick:

    use strict; use warnings; use DBI; use DBD::SQLite; use Data::Dumper; my $dbh = DBI->connect( 'dbi:SQLite:dbname=test.sqlite', undef, undef, { RaiseError => 1, PrintError => 1, } ); eval { $dbh->do('CREATE TABLE evkeypairs (id INTEGER PRIMARY KEY NOT NULL +,key TEXT,value TEXT);'); $dbh->do(q!INSERT INTO evkeypairs (key, value) VALUES ('foo','bar' +);!); $dbh->do(q!INSERT INTO evkeypairs (key, value) VALUES ('kung','foo +');!); } or warn "$@"; my $sth = $dbh->prepare( 'SELECT * FROM evkeypairs' ); $sth->execute; my $href = $sth->fetchall_hashref( 'id' ); print Dumper $href; $dbh->disconnect; unlink 'test.sqlite'; __END__ $VAR1 = { '1' => { 'value' => 'bar', 'id' => '1', 'key' => 'foo' }, '2' => { 'value' => 'foo', 'id' => '2', 'key' => 'kung' } };

    Thanks to AM for providing the code to create a test db.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://814210]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (4)
As of 2014-10-02 03:20 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    What is your favourite meta-syntactic variable name?














    Results (45 votes), past polls