diff options
author | Norbert Preining <preining@logic.at> | 2010-05-12 16:54:37 +0000 |
---|---|---|
committer | Norbert Preining <preining@logic.at> | 2010-05-12 16:54:37 +0000 |
commit | 661c41a09e39a182865e0b51e34cc995a0dc96e8 (patch) | |
tree | 2f79bb1406e22fdcb2587be8ffda6c0c609d7932 /Master/tlpkg/tlperl/lib/DBIx/Simple | |
parent | b645030efc22e13c2498a1522083634ab91b2de1 (diff) |
move tlperl.straw to tlperl
git-svn-id: svn://tug.org/texlive/trunk@18210 c570f23f-e606-0410-a88d-b1316a301751
Diffstat (limited to 'Master/tlpkg/tlperl/lib/DBIx/Simple')
-rwxr-xr-x | Master/tlpkg/tlperl/lib/DBIx/Simple/Comparison.pod | 131 | ||||
-rwxr-xr-x | Master/tlpkg/tlperl/lib/DBIx/Simple/Examples.pod | 390 |
2 files changed, 521 insertions, 0 deletions
diff --git a/Master/tlpkg/tlperl/lib/DBIx/Simple/Comparison.pod b/Master/tlpkg/tlperl/lib/DBIx/Simple/Comparison.pod new file mode 100755 index 00000000000..062bfe99c37 --- /dev/null +++ b/Master/tlpkg/tlperl/lib/DBIx/Simple/Comparison.pod @@ -0,0 +1,131 @@ +=head1 NAME + +DBIx::Simple::Comparison - DBIx::Simple in DBI jargon + +=head1 DESCRIPTION + +This is just a simple and B<inaccurate> overview of what DBI things the +DBIx::Simple things represent, or the other way around. + +This document can be useful to find the foo equivalent of bar. + +C<?> means that DBI doesn't have an equivalent or that I couldn't find one. + +C<=> means that DBIx::Simple provides a direct wrapper to the DBI function. + +C<~> means that DBIx::Simple's method does more or less the same, but usually +in a more high level way: context sensitive, combining things, automatically +taking care of something. + +Note that DBIx::Simple is a wrapper around DBI. It is not "better" than DBI. In +fact, DBIx::Simple cannot work without DBI. + +Using DBI directly is always faster than using DBIx::Simple's equivalents. (For +the computer, that is. For you, DBIx::Simple is supposed to be faster.) + +=head2 Classes, common names + + use DBI ~ use DBIx::Simple + + $DBI::errstr = DBIx::Simple->error + + DBI::db ~ DBIx::Simple + $dbh ~ $db + $dbh->errstr = $db->error + + connect ~ connect + connect ~ new + + DBI::st ~ DBIx::Simple::Result + <undef> ~ DBIx::Simple::Dummy + $sth ~ $result + +=head2 Queries + +DBI + + my $sth = $dbh->prepare_cached($query); + $sth->execute(@values); + +~ DBIx::Simple + + my $result = $db->query($query, $values); + +=head2 Results + + DBI DBIx::Simple + + bind_columns ~ bind + + fetchrow_arrayref/fetch = fetch + fetchrow_array ~ list + *1 ~ flat + [@{fetchrow_arrayref}] = array + fetchall_arrayref ~ arrays + fetchrow_hashref() *2*3 = hash + fetchall_arrayref({}) *4 ~ hashes + + fetchall_hashref *2 = map_hashes + ? ? map_arrays + fetchall_hashref(1) *2 = map + + $sth->{NAME_lc/NAME} = $result->columns + +*1 There's no fetch variant, but you can do C<< { @{ +$dbh->selectcol_arrayref('SELECT ...', { Slice => [] }) } } >>. + +*2 To receive the keys (column names) lowercased, use C<< +$db->{FetchHashKeyName} = 'NAME_lc' >>. DBIx::Simple lower cases them by +default. + +*3 Or supply an argument, C<'NAME_lc'>. + +*4 No, arrayref isn't a typo. When supplied an empty hash reference, DBI's +fetchall_arrayref actually returns hashrefs. This DBI method does not support +lower casing of keys, DBIx::Simple does. + +=head2 Direct access + + DBI DBIx::Simple + + $dbh = $db->dbh + $sth->{$foo} = $result->attr($foo) + + func = func + + begin_work = begin_work + commit = commit + rollback = rollback + last_insert_id = last_insert_id + rows = rows + + disconnect ~ disconnect + finish ~ finish + +=head2 DBIx::Simple specific (?) + + keep_statements + lc_columns + iquery (via SQL::Interp) + select, insert, update, delete (via SQL::Abstract) + abstract (via SQL::Abstract) + flat + hashes + map_arrays + map + +=head1 LICENSE + +There is no license. This software was released into the public domain. Do with +it what you want, but on your own risk. The author disclaims any +responsibility. + +=head1 AUTHOR + +Juerd Waalboer <juerd@cpan.org> <http://juerd.nl/> + +=head1 SEE ALSO + +L<DBI>, L<DBIx::Simple> + +=cut diff --git a/Master/tlpkg/tlperl/lib/DBIx/Simple/Examples.pod b/Master/tlpkg/tlperl/lib/DBIx/Simple/Examples.pod new file mode 100755 index 00000000000..dafe78e8d27 --- /dev/null +++ b/Master/tlpkg/tlperl/lib/DBIx/Simple/Examples.pod @@ -0,0 +1,390 @@ +=head1 NAME + +DBIx::Simple::Examples - Examples of how to use DBIx::Simple + +=head1 DESCRIPTION + +DBIx::Simple provides a simplified interface to DBI, Perl's powerful database +module. + +=head1 EXAMPLES + +=head2 General + + #!/usr/bin/perl -w + use strict; + use DBIx::Simple; + + # Instant database with DBD::SQLite + my $db = DBIx::Simple->connect('dbi:SQLite:dbname=file.dat') + or die DBIx::Simple->error; + + # Connecting to a MySQL database + my $db = DBIx::Simple->connect( + 'DBI:mysql:database=test', # DBI source specification + 'test', 'test', # Username and password + { RaiseError => 1 } # Additional options + ); + + # Using an existing database handle + my $db = DBIx::Simple->connect($dbh); + + # Abstracted example: $db->query($query, @variables)->what_you_want; + + $db->commit or die $db->error; + +=head2 Simple Queries + + $db->query('DELETE FROM foo WHERE id = ?', $id) or die $db->error; + + for (1..100) { + $db->query( + 'INSERT INTO randomvalues VALUES (?, ?)', + int rand(10), + int rand(10) + ) or die $db->error; + } + + $db->query( + 'INSERT INTO sometable VALUES (??)', + $first, $second, $third, $fourth, $fifth, $sixth + ); + # (??) is expanded to (?, ?, ?, ?, ?, ?) automatically + +=head2 Single row queries + + my ($two) = $db->query('SELECT 1 + 1')->list; + my ($three, $four) = $db->query('SELECT 3, 2 + 2')->list; + + my ($name, $email) = $db->query( + 'SELECT name, email FROM people WHERE email = ? LIMIT 1', + $mail + )->list; + +Or, more efficiently: + + $db->query('SELECT 1 + 1')->into(my $two); + $db->query('SELECT 3, 2 + 2')->into(my ($three, $four)); + + $db->query( + 'SELECT name, email FROM people WHERE email = ? LIMIT 1', + $mail + )->into(my ($name, $email)); + +=head2 Fetching all rows in one go + +=head3 One big flattened list (primarily for single column queries) + + my @names = $db->query('SELECT name FROM people WHERE id > 5')->flat; + +=head3 Rows as array references + + for my $row ($db->query('SELECT name, email FROM people')->arrays) { + print "Name: $row->[0], Email: $row->[1]\n"; + } + +=head3 Rows as hash references + + for my $row ($db->query('SELECT name, email FROM people')->hashes) { + print "Name: $row->{name}, Email: $row->{email}\n"; + } + +=head2 Fetching one row at a time + +=head3 Rows into separate variables + + { + my $result = $db->query('SELECT name, email FROM people'); + $result->bind(my ($name, $email)); + while ($result->fetch) { + print "Name: $name, Email: $email\n"; + } + } + +or: + + { + my $result = $db->query('SELECT name, email FROM people'); + while ($result->into(my ($name, $email))) { + print "Name: $name, Email: $email\n"; + } + } + +=head3 Rows as lists + + { + my $result = $db->query('SELECT name, email FROM people'); + while (my @row = $result->list) { + print "Name: $row[0], Email: $row[1]\n"; + } + } + +=head3 Rows as array references + + { + my $result = $db->query('SELECT name, email FROM people'); + while (my $row = $result->array) { + print "Name: $row->[0], Email: $row->[1]\n"; + } + } + +=head3 Rows as hash references + + { + my $result = $db->query('SELECT name, email FROM people'); + while (my $row = $result->hash) { + print "Name: $row->{name}, Email: $row->{email}\n"; + } + } + +=head2 Building maps (also fetching all rows in one go) + +=head3 A hash of hashes + + my $customers = + $db + -> query('SELECT id, name, location FROM people') + -> map_hashes('id'); + + # $customers = { $id => { name => $name, location => $location } } + +=head3 A hash of arrays + + my $customers = + $db + -> query('SELECT id, name, location FROM people') + -> map_arrays(0); + + # $customers = { $id => [ $name, $location ] } + +=head3 A hash of values (two-column queries) + + my $names = + $db + -> query('SELECT id, name FROM people') + -> map; + + # $names = { $id => $name } + +=head1 EXAMPLES WITH SQL::Interp + +If you have SQL::Interp installed, you can use the semi-abstracting method +C<iquery>. This works just like C<query>, but with parts of the query +interleaved with the bind arguments, passed as references. + +You should read L<SQL::Interp>. These examples are not enough to fully +understand all the possibilities. + +The following examples are based on the documentation of SQL::Interp. + + my $result = $db->iquery('INSERT INTO table', \%item); + my $result = $db->iquery('UPDATE table SET', \%item, 'WHERE y <> ', \2); + my $result = $db->iquery('DELETE FROM table WHERE y = ', \2); + + # These two select syntax produce the same result + my $result = $db->iquery('SELECT * FROM table WHERE x = ', \$s, 'AND y IN', \@v); + my $result = $db->iquery('SELECT * FROM table WHERE', {x => $s, y => \@v}); + + for ($result->hashes) { ... } + +Use a syntax highlighting editor for good visual distinction. + +If you need the helper functions C<sql> and C<sql_type>, you can import them +with C<use SQL::Interp;> + +=head1 EXAMPLES WITH SQL::Abstract + +If you have SQL::Abstract installed, you can use the abstracting methods +C<select>, C<insert>, C<update>, C<delete>. These work like C<query>, but +instead of a query and bind arguments, use abstracted arguments. + +You should read L<SQL::Abstract>. These examples are not enough to fully +understand all the possibilities. + +The SQL::Abstract object is available (writable) through the C<abstract> +property. + +The following examples are based on the documentation of SQL::Abstract. + +=head2 Overview + +If you don't like the defaults, just assign a new object: + + $db->abstract = SQL::Abstract->new( + case => 'lower', + cmp => 'like', + logic => 'and', + convert => 'upper' + ); + +If you don't assign any object, one will be created automatically using the +default options. The SQL::Abstract module is loaded on demand. + + my $result = $db->select($table, \@fields, \%where, \@order); + my $result = $db->insert($table, \%fieldvals || \@values); + my $result = $db->update($table, \%fieldvals, \%where); + my $result = $db->delete($table, \%where); + + for ($result->hashes) { ... } + +=head2 Complete examples + +=head3 select + + my @tickets = $db->select( + 'tickets', '*', { + requestor => 'inna', + worker => ['nwiger', 'rcwe', 'sfz'], + status => { '!=', 'completed' } + } + )->hashes; + +=head3 insert + +If you already have your data as a hash, inserting becomes much easier: + + $db->insert('people', \%data); + +Instead of: + + $db->query( + q[ + INSERT + INTO people (name, phone, address, ...) + VALUES (??) + ], + @data{'name', 'phone', 'address', ... } + ); + +=head3 update, delete + + $db->update( + 'tickets', { + worker => 'juerd', + status => 'completed' + }, + { id => $id } + ) + + $db->delete('tickets', { id => $id }); + +=head3 where + +The C<where> method is not wrapped directly, because it doesn't generate a +query and thus doesn't really have anything to do with the database module. + +But using the C<abstract> property, you can still easily access it: + + my $where = $db->abstract->where({ foo => $foo }); + +=head1 EXAMPLES WITH DBIx::XHTML_Table + +If you have DBIx::XHTML_Table installed, you can use the result methods +C<xto> and C<html>. + +You should read L<DBIx::XHTML_Table>. These examples are not enough to fully +understand what is going on. When reading that documentation, note that you +don't have to pass hash references to DBIx::Simple's methods. It is supported, +though. + +DBIx::XHTML_Table is loaded on demand. + +=head2 Overview + +To print a simple table, all you have to do is: + + print $db->query('SELECT * FROM foo')->html; + +Of course, anything that produces a result object can be used. The same thing +using the abstraction method C<select> would be: + + print $db->select('foo', '*')->html; + +A DBIx::XHTML_Table object can be generated with the C<xto> (B<X>HTML_B<T>able +B<O>bject) method: + + my $table = $db->query($query)->xto; + +=head2 Passing attributes + +DBIx::Simple sends the attributes you pass to C<html> both to the constructor +and the output method. This allows you to specify both HTML attributes (like +C<bgcolor>) and options for XHTML_Table (like C<no_ucfirst> and C<no_indent>) +all at once: + + print $result->html( + tr => { bgcolor => [ qw/silver white/ ] }, + no_ucfirst => 1 + ); + +=head2 Using an XHTML_Table object + +Not everything can be controlled by passing attributes. For full flexibility, +the XHTML_Table object can be used directly: + + my $table = $db->query($query)->xto( + tr => { bgcolor => [ qw/silver white/ ] } + ); + + $table->set_group('client', 1); + $table->calc_totals('credit', '%.2f'); + + print $table->output({ no_ucfirst => 1 }); # note the {}! + +=head1 EXAMPLES WITH Text::Table + +=over 8 + +=item C<< $result->text("neat") >> + +Neither neat nor pretty, but useful for debugging. Uses DBI's C<neat_list> +method. Doesn't display column names. + + '1', 'Camel', 'mammal' + '2', 'Llama', 'mammal' + '3', 'Owl', 'bird' + '4', 'Juerd', undef + +=item C<< $result->text("table") >> + +Displays a simple table using ASCII lines. + + id | animal | type + ---+--------+------- + 1 | Camel | mammal + 2 | Llama | mammal + 3 | Owl | bird + 4 | Juerd | + +=item C<< $result->text("box") >> + +Displays a simple table using ASCII lines, with an outside border. + + +----+--------+--------+ + | id | animal | type | + +----+--------+--------+ + | 1 | Camel | mammal | + | 2 | Llama | mammal | + | 3 | Owl | bird | + | 4 | Juerd | | + +----+--------+--------+ + +=back + +For C<table> and C<box>, you need Anno Siegel's Text::Table module installed. + +=head1 LICENSE + +There is no license. This software was released into the public domain. Do with +it what you want, but on your own risk. The author disclaims any +responsibility. + +=head1 AUTHOR + +Juerd Waalboer <juerd@cpan.org> <http://juerd.nl/> + +=head1 SEE ALSO + +L<DBIx::Simple>, L<SQL::Abstract> + +=cut |