Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

DBIx::Simple

by Juerd (Abbot)
on Mar 28, 2002 at 10:26 UTC ( [id://154918]=perlmeditation: print w/replies, xml ) Need Help??

Hi,

As you probably already know, modules should be discussed in public before releasing them to CPAN. That's what I'm doing now ;)

The proposed module is called DBIx::Simple

UPDATE 1 - Uploaded to CPAN.
UPDATE 2 - v0.02 (uploaded to CPAN too), implementing tradez's and mattr's ideas.

Why a wrapper?

  • Many don't need the very advanced features that DBI provides
  • DBI's interface is a bit inconsistent, and "fetchrow_" is a lot of typing
  • dbh and sth tend to be confusing to some
Why not DBIx::Easy?
  • It aims at SQL abstraction (automatically generating queries from code and datastructures), which is another goal
  • I didn't think it was easy at all :)
  • Too many features
Why DBIx::Simple?
  • Many people have written their own wrappers to make life easier, and none made it to CPAN
  • It's time for a consistent simple interface, instead of 101+ homebrew wrappers
  • DBI doesn't have a Simple interface in CPAN, but a lot of modules do
I'd like to know if you think this module is worthy of the name "::Simple".

Here it is:
#-----------------------# package DBIx::Simple; #-----------------------# use DBI; use strict; our $VERSION = '0.02'; our %results; sub connect { my ($class, @arguments) = @_; my $self = { dbi => DBI->connect(@arguments) }; return undef unless $self->{dbi}; return bless $self, $class; } sub disconnect { my ($self) = @_; $self->{dbi}->disconnect() if $self->{dbi}; } sub query { my ($self, $query, @binds) = @_; $self->{success} = 0; my $sth = $self->{dbi}->prepare($query) or do { $self->{reason} = 'Prepare failed'; return DBIx::Simple::Dummy->new(); }; $sth->execute(@binds) or do { $self->{reason} = 'Execute failed'; return DBIx::Simple::Dummy->new(); }; $self->{success} = 1; my $result; # $self is quoted on purpose, to pass along the stringified versio +n, # and avoid increasing reference count. return $results{$self}{$result} = $result = DBIx::Simple::Result-> +new("$self", $sth); } sub commit { my ($self) = @_; $self->{dbi}->commit(); } sub rollback { my ($self) = @_; $self->{dbi}->rollback(); } sub DESTROY { my ($self) = @_; $results{$self}{$_}->DESTROY() for keys %{ $results{$self} }; $self->disconnect(); } #------------------------------# package DBIx::Simple::Dummy; #------------------------------# use strict; sub new { bless \my $dummy, shift } sub AUTOLOAD { undef } package DBIx::Simple::Result; use Carp; use strict; sub new { my ($class, $db, $sth) = @_; my $self = { db => $db, sth => $sth }; return bless $self, $class; } sub list { my ($self) = @_; return $self->{sth}->fetchrow_array; } sub array { my ($self) = @_; return $self->{sth}->fetchrow_arrayref; } sub hash { my ($self) = @_; return $self->{sth}->fetchrow_hashref; } sub flat { my ($self) = @_; return map @$_, $self->arrays; } sub arrays { my ($self) = @_; return @{ $self->{sth}->fetchall_arrayref }; } sub hashes { my ($self) = @_; my @return; my $dummy; push @return, $dummy while $dummy = $self->{sth}->fetchrow_hashref +; return @return; } sub map_hashes { my ($self, $keyname) = @_; croak 'Key column name not optional' if not defined $keyname; my @rows = $self->hashes; my @keys; for (@rows) { push @keys, $_->{$keyname}; delete $_->{$keyname}; } my %return; @return{@keys} = @rows; return \%return; } sub map_arrays { my ($self, $keyindex) = @_; $keyindex += 0; my @rows = $self->arrays; my @keys; for (@rows) { push @keys, splice @$_, $keyindex, 1; } my %return; @return{@keys} = @rows; return \%return; } sub map { my ($self) = @_; return { map { $_->[0] => $_->[1] } $self->arrays }; } sub rows { my ($self) = @_; return $self->{sth}->rows; } sub DESTROY { my ($self) = @_; delete $DBIx::Simple::results{ $self->{db} }{$self} if $self and $ +self->{db}; $self->{sth}->finish() if $self->{sth}; $self->{sth} = undef; } 'Spirit moves through all things'; __END__ =head1 NAME DBIx::Simple - An easy-to-use, object oriented interface to DBI =head1 SYNOPSIS #!/usr/bin/perl -w use strict; use DBIx::Simple; my $db = DBIx::Simple->connect( 'DBI:mysql:database=test', # DBI source specification 'test', 'test', # Username and password { PrintError => 1 } # Additional options ); #### SIMPLE QUERIES $db->query('DELETE FROM foo'); die "$db->{reason} ($DBI::errstr)" if not $db->{success}; for (1..100) { $db->query( 'INSERT INTO foo VALUES (?, ?)', int rand(10), int rand(10) ); } #### SINGLE ROW QUERIES my ($two) = $db->query('SELECT 1 + 1')->list; my ($three, $four) = $db->query('SELECT 3, 2 + 2')->list; #### FETCHING ALL IN ONE GO my @names = $db->query('SELECT name FROM people WHERE foo > 5')->f +lat; for my $row ($db->query('SELECT field1, field2 FROM foo')->arrays) + { print "--> $row->[0], $row->[1]\n"; } for my $row ($db->query('SELECT field1, field2 FROM foo')->hashes) + { print "--> $row->{field1}, $row->{field2}\n"; } #### FETCHING ONE ROW AT A TIME { my $result = $db->query('SELECT field1, field2 FROM foo'); while (my $row = $result->array) { print "--> $row->[0], $row->[1]\n"; } } { my $result = $db->query('SELECT field1, field2 FROM foo'); while (my $row = $result->hash) { print "--> $row->{field1}, $row->{field2}\n"; } } #### BUILDING MAPS (also fetching all in one go) # Hash of hashes my $customers = $db -> query('SELECT id, name, location FROM people') -> map_hashes('id'); # Hash of arrays my $customers = $db -> query('SELECT id, name, location FROM people') -> map_arrays(0); # Hash of values my $names = $db -> query('SELECT id, name FROM people') -> map; =head1 DESCRIPTION This module is aimed at ease of use, not at SQL abstraction or efficiency. The only thing this module does is provide a bone easy interface to the already existing DBI module. With DBIx::Simple, the terms dbh and sth are not used in the documentation (except for this description), although they're omnipresent in the module's source. You don't have to think about them. A query returns a result object, that can be used directly to pick the sort of output you want. There's no need to check if the query succeeded in between calls, you can stack them safely, and check for success later. This is because failed queries have dummy results, objects of which all methods return undef. =head2 DBIx::Simple object methods =over 10 =item C<DBIx::Simple-E<gt>connect( ... )> This argument takes the exact arguments a normal DBI->connect() would take. It's the constructor method, and it returns a new DBIx::Simple object. =item C<query($query, @values)> This calls DBI's prepare() and execute() methods, passing the values along to replace placeholders. query() returns a new DBIx::Simple::Result object (or DBIx::Simple::Dummy), that can be used immediately to get data out of it. =item C<commit>, C<rollback> These just call the DBI methods and Do What You Mean. =item C<disconnect> Does What You Mean. Also note that the connection is automatically terminated when the object is destroyed (C<undef $db> to do so explicitly), and that all statements are also finished when the object is destoryed. disconnect() Does not destory the object. =back =head2 DBIx::Simple::Result object methods =over 10 =item C<new> The constructor should only be called internally, by DBIx::Simple itself. Some simple minded garbage collection is done in DBIx::Simple, and you shouldn't be directly creating your own result objects. The curious are encouraged to read the module's source code to find out what the arguments to new() are. =item C<list> list() Returns a list of elements in a single row. This is like a dereferenced C<$result->array()>. =item C<array> and C<hash> These methods return a single row, in an array reference, or a hash reference, respectively. Internally, fetchrow_arrayref or fetchrow_hashref is used. =item C<flat> flat() Returns a list of all returned fields, flattened. This can be very useful if you select a single column. Consider flat to be list()'s plural. =item C<arrays> and C<hashes> These methods return a list of rows of array or hash references. Internally, fetchall_arrayref is dereferenced, or a lot of fetchrow_hashref returns are accumulated. =item C<map_arrays(column number)> and C<map_hashes(column name)> These methods build a hash, with the chosen column as keys, and the remaining columns in array or hash references as values. For C<map_arrays>, the column number is optional and defaults to 0 (the first column). The methods return a reference to the built hash. =item C<map> Returns a reference to a hash that was built using the first two columns as key/value pairs. Use this only if your query returns two values per row (other values will be discarded). =item C<rows> Returns the number of rows. =item finish? There is no finish method. To finish the statement, just let the object go out of scope (you should always use "C<my>", and "C<use strict>") or destroy it explicitly using C<undef $result>. =back =head1 FEEDBACK This module has a very low version number for a reason. I'd like to hear from you what you think about DBIx::Simple, and if it has made your life easier :). If you find serious bugs, let me know. If you think an important feature is missing, let me know (but I'm not going to implement functions that aren't used a lot, or that are only for effeciency, because this module has only one goal: simplicity). =head1 BUGS Nothing is perfect, but let's try to create perfect things. Of course, this module shares all DBI bugs. If you want to report a bug, please try to find out if it's DBIx::Simple's fault or DBI's fault first, and don't report DBI bugs to me. =item C<new> The constructor should only be called internally, by DBIx::Simple itself. Some simple minded garbage collection is done in DBIx::Simple, and you shouldn't be directly creating your own result objects. The curious are encouraged to read the module's source code to find out what the arguments to new() are. =item C<list> list() Returns a list of elements in a single row. This is like a dereferenced C<$result->array()>. =item C<array> and C<hash> These methods return a single row, in an array reference, or a hash reference, respectively. Internally, fetchrow_arrayref or fetchrow_hashref is used. =item C<flat> flat() Returns a list of all returned fields, flattened. This can be very useful if you select a single column. Consider flat to be list()'s plural. =item C<arrays> and C<hashes> These methods return a list of rows of array or hash references. Internally, fetchall_arrayref is dereferenced, or a lot of fetchrow_hashref returns are accumulated. =item C<map_arrays(column number)> and C<map_hashes(column name)> These methods build a hash, with the chosen column as keys, and the remaining columns in array or hash references as values. For C<map_arrays>, the column number is optional and defaults to 0 (the first column). The methods return a reference to the built hash. =item C<map> Returns a reference to a hash that was built using the first two columns as key/value pairs. Use this only if your query returns two values per row (other values will be discarded). =item C<rows> Returns the number of rows. =item finish? There is no finish method. To finish the statement, just let the object go out of scope (you should always use "C<my>", and "C<use strict>") or destroy it explicitly using C<undef $result>. =back =head1 FEEDBACK This module has a very low version number for a reason. I'd like to hear from you what you think about DBIx::Simple, and if it has made your life easier :). If you find serious bugs, let me know. If you think an important feature is missing, let me know (but I'm not going to implement functions that aren't used a lot, or that are only for effeciency, because this module has only one goal: simplicity). =head1 BUGS Nothing is perfect, but let's try to create perfect things. Of course, this module shares all DBI bugs. If you want to report a bug, please try to find out if it's DBIx::Simple's fault or DBI's fault first, and don't report DBI bugs to me. Note: the map functions do not check if the key values are unique. If they are not, keys are overwritten. =head1 USE THIS MODULE AT YOUR OWN RISK No warranty, no guarantees. I hereby disclaim all responsibility for what might go wrong. =head1 AUTHOR Juerd <juerd@juerd.nl> =head1 SEE ALSO L<DBI> =cut

U28geW91IGNhbiBhbGwgcm90MTMgY
W5kIHBhY2soKS4gQnV0IGRvIHlvdS
ByZWNvZ25pc2UgQmFzZTY0IHdoZW4
geW91IHNlZSBpdD8gIC0tIEp1ZXJk

Replies are listed 'Best First'.
Re: DBIx::Simple
by gmax (Abbot) on Mar 28, 2002 at 12:49 UTC
    <personal opinion>
    It's a nice design, and I appreciate the time you have spent doing this. However, I usually think that modules like this one can be more dangerous than helpful, since they don't add anything but they hide functionality that a beginner could eventually get acquainted with, had it been available.
    </personal opinion>

    In any real world DBI application, I am using more than one $sth, so that I can have multiple queries with the same connection. Even without talking about DBI's advanced features, I would say that this module is just too simple for normally demanding database applications.

    As an example, let's have a look at a classic case of customer / order situation. We have to query for the customer ID, and for each ID we want to perform something on the related orders.

    Note: There are things in this situation that can be done directly with one SQL query, and others that require separate ones. For example, if we want to display all the data for each customer, and then ask for their orders, it would be disastrously inefficient to join customers and orders.
    # untested but realistic my $dbh = DBI->connect("...",{RaiseError => 1} ) or die "can't\n"; my $cust_sth = $dbh->prepare( qq{SELECT cust_ID, cust_name from customer}); $cust_sth->execute(); while (my $cust = $cust_sth->fetchrow_hashref()) { my $orders_sth = $dbh->prepare( qq{select * from orders where cust_ID = ? }); # do something smart with customer name $order_sth->execute($cust->{cust_ID}); while (my $order = $orders_sth->fetchrow_hashref()) { # do something even smarter with the orders data } }
    With DBIx::Simple, I should either copy the result of the first query to an array, and suffer the copy delay, or create two objects (and bear in mind that in a real application I need to use more than that), thus having two connections and their relative overhead.

    <personal opinion>
    Anyway, everyone is free to see things from a different angle. I wouldn't either use nor recommend such a module for a real project. As for teaching, I'd rather go through the more rewarding details of the DBI. It's something the students needs to handle if they want to be more than second-rate database programmers.
    It's like learning regular expressions. You can get a wrapper that builds a regex from some procedural instructions, but you won't ever learn them if you don't manage to overcome the initial shock and be able to read and understand the "line noise".
    </personal opinion>

    update
    Interesting database applications are too complex to quote some meaningful example and discussing about their implementation. However, my abstract consideration on the issue is that DBI is already a wrapper over the DBD driver, which is another wrapper over the database API. Knowing this, I try to avoid any further layer.
    Moreover, DBI has still room for more features rather than a need for reducing them.
    _ _ _ _ (_|| | |(_|>< _|

      # untested but realistic my $dbh = DBI->connect("...",{RaiseError => 1} ) or die "can't\n"; my $cust_sth = $dbh->prepare( qq{SELECT cust_ID, cust_name from customer}); $cust_sth->execute(); while (my $cust = $cust_sth->fetchrow_hashref()) { my $orders_sth = $dbh->prepare( qq{select * from orders where cust_ID = ? }); # do something smart with customer name $order_sth->execute($cust->{cust_ID}); while (my $order = $orders_sth->fetchrow_hashref()) { # do something even smarter with the orders data } }
      With DBIx::Simple, I should either copy the result of the first query to an array, and suffer the copy delay, or create two objects (and bear in mind that in a real application I need to use more than that), thus having two connections and their relative overhead.

      I didn't want to talk about statement handles, but I get the impression that you think DBIx::Simple can use only one. However, there are result objects, that internally are just objects holding a sth., with an alternative, consistent interface.

      I don't think existing scripts should be re-written, but here's yours for demonstration purposes:

      use DBIx::Simple; my $db = DBIx::Simple->connect("...",{RaiseError => 1} ) or die "can't +\n"; my $cust_result = $db->query('SELECT cust_ID, cust_name FROM customer' +); while (my $cust = $cust_result->hash) { my $orders_result = $db->query( 'SELECT * FROM orders WHERE cust_ID = ?', $cust->{cust_ID} ); while (my $order = $orders_result->hash) { # code } }
      Or, when slurping into memory is not a problem, you can write beautiful code with DBIx::Simple:
      use DBIx::Simple; my $db = DBIx::Simple->connect("...",{RaiseError => 1} ) or die "can't +\n"; for my $cust ($db->query('SELECT cust_ID, cust_name FROM customer')->h +ashes) { for my $order ($db->query('SELECT * FROM orders WHERE cust_ID = ?' +, $cust->{cust_ID}->hashes) { # code } }

      U28geW91IGNhbiBhbGwgcm90MTMgY
      W5kIHBhY2soKS4gQnV0IGRvIHlvdS
      ByZWNvZ25pc2UgQmFzZTY0IHdoZW4
      geW91IHNlZSBpdD8gIC0tIEp1ZXJk
      

Re: DBIx::Simple
by lachoy (Parson) on Mar 28, 2002 at 13:29 UTC

      Not to be rude, but in addition to EZDBI there are also DBIx::Abstract, DBIx::DWIW, DBIx::Easy.

      EZDBI: See my reply to the other post mentioning it.

      DBIx::Abstract: aims at SQL abstraction, not just making things easier. I often find myself wanting to write queries myself.

      DBIx::DWIW: New to me (again, a lowzy name, just like EZDBI), but is MySQL specific and seems not to be capable of returning rows one at a time.

      DBIx::Easy: Another SQL abstractor.

      So there isn't really competition, just different approaches to using DBI.

      I think writing a DBI wrapper is becoming one of those tasks every Perl programmer undertakes, like writing a templating system :-)

      That's why you see a lot of CGI scripts with sub query { ... } and sub dbopen { ... } in them. This just proves that DBI itself isn't easy enough.

      I've written multiple DBI wrappers myself, and until recently, I created a new one for every project. I thought about how I would really like it, and how error handling in between prepare, execute and fetch* could be done, so that you can safely stack method calls and discover it went wrong later. The result is presented to you in this thread.

      U28geW91IGNhbiBhbGwgcm90MTMgY
      W5kIHBhY2soKS4gQnV0IGRvIHlvdS
      ByZWNvZ25pc2UgQmFzZTY0IHdoZW4
      geW91IHNlZSBpdD8gIC0tIEp1ZXJk
      

        Don't get me wrong -- I think you should submit this to CPAN. The module is well-written, documented and serves a specific purpose. IMO competition is good, and one of the unfortunate things about CPAN modules is that many of them are heavily upgraded for ~6 months and then abandoned. If you maintain your module for the long-haul, keeping up with upgrades to the DBI, then you'll be a step ahead in my book.

        That said, I think the comparisons you draw with the other modules are distinctions without differences. They do much the same tasks but just in different ways. Some ways might fit the way a developer approaches a problem, some might not. None of these modules fit your approach enough to contribute to it rather than develop your own. No problem.

        Fortunately, there's not an all-powerful gatekeeper to CPAN who gives the thumbs-up or thumbs-down to a module. IMO this is one of the features of Perl that makes it organically vital. The fact that there are so many DBI wrappers (and templating modules) means this is a problem everyone tackles, and problems like this are often too generic to have one solution. This might annoy or turn away developers from other languages, particularly ones that have been handed down from the mount (Java), but so what?

        Keep up the great work!

        Chris
        M-x auto-bs-mode

      ++ Chris, but...
      maybe there is someone who will 'invent' the same old circle in his own - maybe for some of us *better* way...
      As for me DBI is ok - i used to it ;-) but i still use the templating module written by my friend (also ! @CPAN). None of CPAN modules :-( gave me such ease of use and functionality. Just K.I.S.S. ;-)

      Greetz, Tom.
Re: DBIx::Simple
by Kanji (Parson) on Mar 28, 2002 at 12:17 UTC
    Why DBIx::Simple?
    • Many people have written their own wrappers to make life easier, and none made it to CPAN

    *cough* *cough* EZDBI *cough*

        --k.


      *cough* *cough* EZDBI *cough*

      Now that name's obvious. I spent an hour on a very slow search.cpan.org, searching for "Easy", "Simple" and "DBI", and of course found this one, but didn't think it would be even related to DBI.

      I've looked at its documentation, and think it's a weird module. It allows for only one db connection (which should still be sufficient for most people), and has SQL semi-abstraction (the first words are made functions...).

      I have to admit, EZDBI is very easy to use. But I think it's EZDBI and DBIx::Simple are not competitors, as EZDBI is not object oriented.

      U28geW91IGNhbiBhbGwgcm90MTMgY
      W5kIHBhY2soKS4gQnV0IGRvIHlvdS
      ByZWNvZ25pc2UgQmFzZTY0IHdoZW4
      geW91IHNlZSBpdD8gIC0tIEp1ZXJk
      

      I'm glad you mentioned it, so I didn't have to. I actually use EZDBI preferentially over DBI for my pet projects. dominus should be merging some of my contributed code RealSoonNow. The code brings in the functionality of princepawn's DBIx::Connect, which really scratches an itch for me personally. (Initial verions used DBIx::Connect, but it brings in too many dependencies for the process to be called EZ) .oO(It is conceivable that I could add in the ability to handle multiple handles when using this modified interface...)

      --
      perl -pe "s/\b;([st])/'\1/mg"

(jeffa) Re: DBIx::Simple
by jeffa (Bishop) on Mar 28, 2002 at 15:12 UTC
    As i began to write this i had pre-conceived notions of "why are you doing that?" ... but after some reflection i am starting to like DBIx::Easy. :D

    Looks like you really really really want to submit this module and we are not going to be able to talk you out of it. Heh, you have pretty much proven why the other modules out there don't do what this one does ... so i say go for it!

    I wrote DBIx::XHTML_Table because even though i could do the same with CGI and DBI, i wanted one module. HTML::Table works, but i really don't like that module. I really don't like that module. After i wrote and submitted my module, somebody submitted the much nicer named DBIx::HTMLTable - i was a bit irate. But the point is that my module may not 'click' with a particular user the way DBIx::HTMLTable does, so i don't mind the competition. TIMTOWTDI!!

    I think what folks are trying to tell you is that the CPAN namespace is getting a bit, untidy. But what i say is variety is good. A user of CPAN has the responsibility to research which module fits their needs, and if DBIx::Simple happens to help only a handful of people then i think it worthy of being a CPAN module.

    P.S. next time you find yourself waiting for searches at search.cpan.org - try Perl Monk's Official Runner Up: kobe!

    jeffa

    L-LL-L--L-LL-L--L-LL-L--
    -R--R-RR-R--R-RR-R--R-RR
    B--B--B--B--B--B--B--B--
    H---H---H---H---H---H---
    (the triplet paradiddle with high-hat)
    

    UPDATE: hehe, i like to get to the meat of the matter ;)

    Yes, i think DBIx::Simple is an a-ok name.

      Looks like you really really really want to submit this module and we are not going to be able to talk you out of it. Heh, you have pretty much proven why the other modules out there don't do what this one does ... so i say go for it!

      I really want to submit this to CPAN, yes (I'd like all of my non-specific public domain modules to be on CPAN, one day - that way I can easily distribute them and perhaps others can use them). That's why I didn't ask if it's CPAN worthy or not, because I think it is, and that question has already been answered by some people who got the module before Perl Monks got it.

      The main question, as stated in the root node is: Is "DBIx::Simple" a good name? Is it simple enough for that?

      U28geW91IGNhbiBhbGwgcm90MTMgY
      W5kIHBhY2soKS4gQnV0IGRvIHlvdS
      ByZWNvZ25pc2UgQmFzZTY0IHdoZW4
      geW91IHNlZSBpdD8gIC0tIEp1ZXJk
      

Re: DBIx::Simple
by rob_au (Abbot) on Mar 28, 2002 at 14:15 UTC
    While I certainly do not want to disparague your industrial spirit, personally I don't think there is much here to warrant yet-another DBI-wrapper module. For the most part, all this module offers is the ability to call existing methods of DBI by different names - This, given the number of existing DBIx modules which offer, in some cases, vastly different interface constructs for the DBI module, to my mind, negates any real worth which this module could offer to the DBIx namespace.

    I must say that I agree with the observation of lachoy that a DBI wrapper is one of the "must-do" projects for Perl programmers, but not one that necessarily needs to be incorporated into the CPAN namespace.

     

      For the most part, all this module offers is the ability to call existing methods of DBI by different names -

      It's not an alternative approach to database programming, as other modules are. Thus far, all others aim at:

      • Removing OO, leaving pure simplicity (EZDBI)
      • Abstracting SQL, leaving pure perl code that's transformed into SQL (DBIx::Abstract, DBIx::Easy)
      • Remove the statement-idea completely (DBIx::DWIW)
      The basics of DBI are very good, imho. I want an alternative that has OO, difference between connection and query statement, and does not abstract the SQL. Further, I want it to provide only simple features - no caching, no thousand-and-one ways of binding variables :)

      That didn't exist, so I created one myself, just like everyone has done before. I have then documented it, and I have the dummy result object for easing error handling.

      If this module would do more than call existing methods by different names, I had probably used another name, DBIx::Extra or something like that. This is not to _add_ functionality (it does remove functionality).

      U28geW91IGNhbiBhbGwgcm90MTMgY
      W5kIHBhY2soKS4gQnV0IGRvIHlvdS
      ByZWNvZ25pc2UgQmFzZTY0IHdoZW4
      geW91IHNlZSBpdD8gIC0tIEp1ZXJk
      

Re: DBIx::Simple
by mpeppler (Vicar) on Mar 28, 2002 at 18:17 UTC
    Nicely done.

    However, I think one drawback is that there is no way to re-execute a previously prepared query. Depending on the situation this can be a serious issue.

    Update

    Kanji asked me to elaborate...
    The query method above always calls prepare. There doesn't seem to be any way to execute the same query with different parameters without prepare getting called. In DBI I can do:

    $sth = $dbh->prepare(q(update foo set bar = ? where quux = ?)); $sth->execute('one', 'two'); $sth->execute('three', 'four'); ...

    Depending on the complexity of the query the prepare step can be very expensive...

    Michael

Re: DBIx::Simple
by trs80 (Priest) on Mar 28, 2002 at 19:06 UTC
    Short response: I don't like it.

    Long response: I have been using DBI since 1997 and I have on many occasions wanted a simpler manner in which to interact with it. I have made various wrapping routines and what I don't like about the methods you present for the most part are the names and manner which the connects are handled. For me if DBI is to be Simple it needs to have the following: 1) Configurataion file based connect parameters. These are be predefined before a connect is called or they can be loaded if absent at the time a connection is requested. 2) The user should rarely have to explictly interact with the connect statement, individual methods should make the connection when they are called. 3) The names of the methods should state exactly what is being returned, this may increase the length of the name, but it will go a long way in making the script more mantainable.

    Here is some code I have been using for some resent development work, it is not production grade, but hopefully illustrates my point. (disproves?)

    our %dbh; # by using a hash of connections, each database # can have its own current connection, which allows # for presistent connections and works fine with Apache::DBI # code similar to this has been running in production code # for the last two years with no ill side effects. # dbhandle that is sub dbhandle { my $self = shift; $self->error_to_log("db_access requested"); $self->error_to_log("Request for connect to: " . $self->conf___database . "Host Name: " . $self->conf___dbd_hostname ); if (defined $dbh{$self->conf___database()}) { $self->error_to_log("Used *cached* handle."); return ($dbh{$self->conf___database()}) } else { my $dsn = "dbi:" . $self->conf___dbd_driver . ":" . $self->c +onf___database . ";host=" . $self->conf___dbd_hostname . ";port=" . $self->conf___dbd_port; $dbh{$self->conf___database()} = DBI->connect("$dsn",$self->co +nf___dbd_user,$self->conf___dbd_password, { PrintError => 1, RaiseError => 1, } ) || die $DBI::errstr; # DBI->trace(2,"/tmp/dbitrace.log"); return($dbh{$self->conf___database()}) ; } } sub db_tables { my $self = shift; my @tables = $self->dbhandle->func( '_ListTables' ); return(@tables); } sub db_add_row_to_table { my ($self,$table,$args) = @_; my @place = (); my @field = (); my @value = (); foreach (keys %{$args}) { push @place, '?'; push @field, $_; push @value, $args->{$_}; } my $string = qq[ insert into $table ( ] . join(' ,', @field) . qq[ ) values ( ] . join(' ,', @place) . qq[ ) ]; my $id = $self->db_do($string , \@value ); return ($id); } sub db_do { my ($self,$string,$placeholders) = @_; my $id; $self->error_to_log("$string"); my $cursor = $self->dbhandle->prepare($string); $cursor->execute(@{ $placeholders }); if ($string =~ /^\s?insert/i) { $id = $self->dbhandle->{'mysql_insertid'}; # ($id) = $self->db_row("SELECT MAX(page_id) FROM page"); } return ( $id ); } sub db_disconnect { my $self = shift; my $change = 1; if ($self->dbhandle && $change == 1) { $self->dbhandle->disconnect(); # $self->error_to_log("Disconnected from: $db_to_disco +nnect"); } } sub db_list_of_databases_as_array { my $self = shift; my $drh = DBI->install_driver( $self->conf___dbd_driver() ); my @databases = $drh->func('127.0.0.1', '3306', '_ListDBs'); return(@databases); } sub db_single_column_as_array { my ($self,$sql) = @_; my (@list); my $cursor=$self->dbhandle->prepare("$sql"); $cursor->execute; while ( my ($tmp) = $cursor->fetchrow ) { push @list,$tmp; } $cursor->finish; return(@list); } sub db_single_row_as_array { my ($self,$string,$placeholders) = @_; $self->error_to_log("STRING: $string"); my $cursor = $self->dbhandle->prepare("$string"); $cursor->execute(@{$placeholders}); my @result = $cursor->fetchrow; $cursor->finish; return(@result); } sub db_all_rows_as_arrayref { my ($self,$string) = @_; my $cursor = $self->dbhandle->prepare("$string"); $cursor->execute; my $results = $cursor->fetchall_arrayref; $cursor->finish; return($results); } sub db_one_row_as_hashref { my ($self,$string,$placeholders) = @_; my $cursor = $self->dbhandle->prepare($string); $cursor->execute(@{$placeholders}); my $result = $cursor->fetchrow_hashref; $cursor->finish; return ($result); }

      FWIW: There's a relatively new module on by princepawn to deal with the connection information: DBIx::Connect. It uses AppConfig to store/parse the information but provides a common interface.

      Chris
      M-x auto-bs-mode

        Thanks for the reminder that this module is out there. For applications that will only access a single database this is a simple/good way to keep your connect information external of the code.

        AppConfig had some limitation I couldn't live with. I use XML::Simple since I need some complex datastructures.

      1) Configurataion file based connect parameters. These are be predefined before a connect is called or they can be loaded if absent at the time a connection is requested. 2) The user should rarely have to explictly interact with the connect statement, individual methods should make the connection when they are called.

      I disagree completely. Configuration files should not be handled by a module. If the database has to be configurable, the user must provide for a function like that. Another reason I didn't create a dsn myself is that DBI's dsn is already useable, and I'm planning on writing a DSN-generator that uses the PEAR DSN scheme (mysql://user:pass@host/database and like that), so that you can use connect(pear('foo://bar...')).

      OO Modules should NOT require external configuration files, but should get their configuration in the constructor method.

      3) The names of the methods should state exactly what is being returned, this may increase the length of the name, but it will go a long way in making the script more mantainable.

      You're right on that, but "hash" and "array" are acceptable, as a sub CANNOT return a real hash or real array. A sub can return a hash _reference_, or array _reference_. Besides, documentation removes all ambiguity, as the pod is very clear on what is returned.

      That's another thing DBI does wrong: fetchrow_array returns a list, so it should be called fetchrow_list.

      U28geW91IGNhbiBhbGwgcm90MTMgY
      W5kIHBhY2soKS4gQnV0IGRvIHlvdS
      ByZWNvZ25pc2UgQmFzZTY0IHdoZW4
      geW91IHNlZSBpdD8gIC0tIEp1ZXJk
      

        I know you disagree, that is why you did yours differently.
        I am curious, why an OO module should not have an external configuration file. By having a separate configuration file you can access the configuration file from utilities outside of the modules when you are doing something small and other languages can read them if they are in a digestable format. I use the same connect handling mechanism and dynamicly determine which conf to use based on directory location, this avoids be from having to hard code anything inside of the modules themsevles, which I thought was the real point in OO, not to disallow any external information, but to promote external information for configuration and settings. The code I have allows for BOTH from a configuration file and setting the parameters inside of the application. Here is a trimed down constructor I am using in a current project.

        sub new { my ($class) = @_; my $self = {}; bless $self , $class; my $config = { dbd_user => 'user', dbd_driver => 'mysql', title_text => 'TITLE HERE', database => 'my_database', dbd_port => '3306', style_sheet => 'default.css', dbd_password => 'my_pass', smtp_server => 'mytrusted.mailserver.com', dbd_hostname => '127.0.0.1' }; $self->_debug(1); $self->assign_to_object('conf',$config); # $self->error_to_log(Dumper($self)); return $self; } sub assign_to_object { my ($self,$prefix,$hash) = @_; foreach my $column (keys %{$hash}) { my $method = "$prefix" . "___" . "$column"; $self->$method($hash->{$column}); } }
        This is all part of a much larger framework that I am still refining. I some of this makes no sense unless you know more about the rest of the framework, I will have to save that for another post.
Missing a few methods
by tradez (Pilgrim) on Mar 29, 2002 at 20:43 UTC
    Very good, very consitent design. But if people are going to be using this for web apps something like this might be needed
    sub oracle_escape { my $self = shift; my ($ret_value) = @{{@_}}{qw/dirty/}; $ret_value =~ s/\'/\'\'/g; return $ret_value; }
    and it looks like your presumes autocommit turned on. This is not normally the case, so in that case why not add these
    sub commit { my $self = shift; $self->{'dbh'}->commit(); } sub rollback { my $self = shift; $self->{'dbh'}->rollback(); }
    Just some food for thought. Please tell me if you put these in, would be cool to know I helped with something on CPAN ;).

    Tradez
    "Never underestimate the predicability of stupidity"
    - Bullet Tooth Tony, Snatch (2001)
Re: DBIx::Simple
by mattr (Curate) on Mar 30, 2002 at 07:31 UTC
    Nice job. I see it is in CPAN, great!

    I've used DBI for years and I liked learning it, and learning it. Maybe the right phrase would be "in an every-learning state". I used to roll my own schema system and still do try to hide SQL in subs. But I have a few queries (sorry).

    - at first I felt a little dyslexic reading the hashes/arrays method names. I'd much prefer hashlist/arraylist, or even LoH/LoL. A plural word just got me somehow, maybe you'd like to add a synonym.

    - Another niggle, seems that a Group By would be more efficient than doing a query for each order in your example of nested queries on two databases. No problem for your audience I'd guess, but you might like to mention the idea of database overhead in your docs.

    - I also was intrigued by DBIx::Easy, the makemap and serial methods for example seemed to answer questions I once had when learning databases, and then picked up or rolled my own. I'm sure you've looked at it, but it does have an interesting take on the meaning of the term "easy".

    I was going to suggest doing something more radical to make things easy, something like a smart gun - point it in the general direction of the opposing forces and you get a bullseye. Maybe EasyDB tried to do that. Can you make it even easier than that module? I'm thinking of not forcing the user to do any of those loops or anything, just hand coderefs for an error sub and a sub that says what to do with each returned value. Anyway your take on it seems fine. Was hoping for something easy enough to lure PHP users back to Perl. :)

    - I am a little worried about people not being able to step up to DBI, perhaps you'd like to mention differences or things left out with regard to big old DBI. In fact tons of really easy to understand documentation about databases would be wonderful for beginners.

    - If there is anything else, I'd ask for more methods that do things common to web site work so that people aren't always rolling their own. One example is authentication and cookies. There are lots of ways to do it, but even if the answer is "leave it to Apache" it would be invaluable for you to provide such a facility with ample installation, usage, and philosophy docs. "Simple" doesn't mean the programmer is simple-minded or uneducated, it could just mean "I've rolled my own umpteen times but would like to get something stable that works done ASAP and go home at a reasonable hour without delving into my code catacombs". So, making hard things Simple would be a good code word. Then making really hard things Simple, etc. (thanks Zeno).

    Hope this helps.

    Matt

      - at first I felt a little dyslexic reading the hashes/arrays method names. I'd much prefer hashlist/arraylist, or even LoH/LoL. A plural word just got me somehow, maybe you'd like to add a synonym.

      You will get used to it. The names are logical: hash() returns a single hash(ref), hashes returns multiple of the same.

      - Another niggle, seems that a Group By would be more efficient than doing a query for each order in your example of nested queries on two databases. No problem for your audience I'd guess, but you might like to mention the idea of database overhead in your docs.

      Which example of two nested queries? /me has no idea what you're talking about...

      - I also was intrigued by DBIx::Easy, the makemap and serial methods for example seemed to answer questions I once had when learning databases, and then picked up or rolled my own. I'm sure you've looked at it, but it does have an interesting take on the meaning of the term "easy".

      Hmm, typically, you use all data returned by a query, and not just a little bit of it. It makes sense to loop over the returned results. However, maybe there are cases when such a map comes in handy, and I implemented the idea in 0.02 (to be released soon).

      Extract from the new synopsis:

      # BUILDING MAPS (also fetching all in one go) my $customers = $db -> query('SELECT id, name, location FROM people') -> map_hashes('id'); # $customers is { $id => { name => $name, location => $location }, + ... } my $customers = $db -> query('SELECT id, name, location FROM people') -> map_arrays(0); # $customers is { $id => [ $name, $location ], ... } my $names = $db -> query('SELECT id, name FROM people') -> map; # $names is { $id => $name, ... }
      So the example mentioned in DBIx::Easy's POD would be:
      $easy->makemap('components', 'id', 'price', 'price > 10') $simple->query('SELECT id, price FROM components WHERE price > 10')->m +ap
      Yes, it indeed is more typing, but that's because I don't want any SQL abstraction in DBIx::Simple.

      I'm thinking of not forcing the user to do any of those loops or anything, just hand coderefs for an error sub and a sub that says what to do with each returned value. Anyway your take on it seems fine. Was hoping for something easy enough to lure PHP users back to Perl. :)

      That would only make things unclear and therefore not-simple. I think loops should be very clear and visible, not abstracted.

      If there is anything else, I'd ask for more methods that do things common to web site work so that people aren't always rolling their own. One example is authentication and cookies.

      Those things are not tasks for a database interface. DBIx::Simple is in absolutely no way aimed at pulling PHP users, although it would be a welcome side-effect. Perl has much more uses than web development. Maybe one day, there'll be a complete set of PHP modules implementing PHP's built in functions.

      U28geW91IGNhbiBhbGwgcm90MTMgY
      W5kIHBhY2soKS4gQnV0IGRvIHlvdS
      ByZWNvZ25pc2UgQmFzZTY0IHdoZW4
      geW91IHNlZSBpdD8gIC0tIEp1ZXJk
      

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlmeditation [id://154918]
Approved by root
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others scrutinizing the Monastery: (3)
As of 2024-03-19 04:51 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found