--- name: "DBIx::Simple::Examples" section: "3pm" platform: "linux" package: "libdbix-simple-perl" version: "1.37-3" release: "Debian 13 (Trixie)" platforms: ["linux"] url: "https://new.carta.tech/DBIx::Simple::Examples" --- # DBIx::Simple::Examples(3pm) Examples of how to use DBIx::Simple ## NAME DBIx::Simple::Examples - Examples of how to use DBIx::Simple ## DESCRIPTION DBIx::Simple provides a simplified interface to DBI, Perl's powerful database module. ## EXAMPLES ### 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; ``` ### 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 ``` ### 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)); ``` ### Fetching all rows in one go *One big flattened list (primarily for single column queries)* ``` my @names = $db->query('SELECT name FROM people WHERE id > 5')->flat; ``` *Rows as array references* ``` for my $row ($db->query('SELECT name, email FROM people')->arrays) { print "Name: $row->[0], Email: $row->[1]\n"; } ``` *Rows as hash references* ``` for my $row ($db->query('SELECT name, email FROM people')->hashes) { print "Name: $row->{name}, Email: $row->{email}\n"; } ``` ### Fetching one row at a time *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"; } } ``` *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"; } } ``` *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"; } } ``` *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"; } } ``` ### Building maps (also fetching all rows in one go) *map* A hash of hashes ``` my $customers = $db -> query('SELECT id, name, location FROM people') -> map_hashes('id'); # $customers = { $id => { name => $name, location => $location }, ... } ``` A hash of arrays ``` my $customers = $db -> query('SELECT id, name, location FROM people') -> map_arrays(0); # $customers = { $id => [ $name, $location ], ... } ``` A hash of values (two-column queries) ``` my $names = $db -> query('SELECT id, name FROM people') -> map; # $names = { $id => $name, ... } ``` *group* A hash of arrays of hashes ``` my $customers = $db -> query('SELECT id, name, location FROM people') -> group_hashes('location'); # $customers = { $location => [ { id => $id, name => $name }, ... ], ... } ``` A hash of arrays of arrays ``` my $customers = $db -> query('SELECT id, name, location FROM people') -> group_arrays(2); # $customers = { $location => [ [ $id, $name ], ... ], ... } ``` A hash of arrays of values (two-column queries) ``` my $names = $db -> query('SELECT location, name FROM people') -> group; # $names = { $location => [ $name, $name, ... ], ... } ``` ## EXAMPLES WITH SQL::Interp If you have SQL::Interp installed, you can use the semi-abstracting method `"iquery"`. This works just like `"query"`, but with parts of the query interleaved with the bind arguments, passed as references. You should read 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 `"sql"` and `"sql_type"`, you can import them with `"use SQL::Interp;"` ## EXAMPLES WITH SQL::Abstract If you have SQL::Abstract installed, you can use the abstracting methods `"select"`, `"insert"`, `"update"`, `"delete"`. These work like `"query"`, but instead of a query and bind arguments, use abstracted arguments. You should read SQL::Abstract. These examples are not enough to fully understand all the possibilities. The SQL::Abstract object is available (writable) through the `"abstract"` property. The following examples are based on the documentation of SQL::Abstract. ### 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) { ... } ``` ### Complete examples *select* ``` my @tickets = $db->select( 'tickets', '*', { requestor => 'inna', worker => ['nwiger', 'rcwe', 'sfz'], status => { '!=', 'completed' } } )->hashes; ``` *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', ... } ); ``` *update, delete* ``` $db->update( 'tickets', { worker => 'juerd', status => 'completed' }, { id => $id } ) $db->delete('tickets', { id => $id }); ``` *where* The `"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 `"abstract"` property, you can still easily access it: ``` my $where = $db->abstract->where({ foo => $foo }); ``` ## EXAMPLES WITH DBIx::XHTML\_Table If you have DBIx::XHTML\_Table installed, you can use the result methods `"xto"` and `"html"`. You should read 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. ### 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 `"select"` would be: ``` print $db->select('foo', '*')->html; ``` A DBIx::XHTML\_Table object can be generated with the `"xto"` (**X**HTML\_**T**able **O**bject) method: ``` my $table = $db->query($query)->xto; ``` ### Passing attributes DBIx::Simple sends the attributes you pass to `"html"` both to the constructor and the output method. This allows you to specify both HTML attributes (like `"bgcolor"`) and options for XHTML\_Table (like `"no_ucfirst"` and `"no_indent"`) all at once: ``` print $result->html( tr => { bgcolor => [ qw/silver white/ ] }, no_ucfirst => 1 ); ``` ### 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 {}! ``` ## EXAMPLES WITH Text::Table - "$result-\>text("neat")" Neither neat nor pretty, but useful for debugging. Uses DBI's `"neat_list"` method. Doesn't display column names. ``` '1', 'Camel', 'mammal' '2', 'Llama', 'mammal' '3', 'Owl', 'bird' '4', 'Juerd', undef ``` - "$result-\>text("table")" Displays a simple table using ASCII lines. ``` id | animal | type ---+--------+------- 1 | Camel | mammal 2 | Llama | mammal 3 | Owl | bird 4 | Juerd | ``` - "$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 | | +----+--------+--------+ ``` For `"table"` and `"box"`, you need Anno Siegel's Text::Table module installed. ## AUTHOR Juerd Waalboer \ \ ## SEE ALSO DBIx::Simple, SQL::Abstract