forked from pool/perl-SQL-Abstract
OBS-URL: https://build.opensuse.org/package/show/devel:languages:perl/perl-SQL-Abstract?expand=0&rev=6
This commit is contained in:
Stephan Kulow
committed by
Git OBS Bridge
Git OBS Bridge
parent
806b21a647
commit
437f26069b
@@ -1,7 +1,7 @@
|
||||
#
|
||||
# spec file for package perl-SQL-Abstract (Version 1.72)
|
||||
# spec file for package perl-SQL-Abstract
|
||||
#
|
||||
# Copyright (c) 2010 SUSE LINUX Products GmbH, Nuernberg, Germany.
|
||||
# Copyright (c) 2012 SUSE LINUX Products GmbH, Nuernberg, Germany.
|
||||
#
|
||||
# All modifications and additions to the file contributed by third parties
|
||||
# remain the property of their copyright owners, unless otherwise agreed
|
||||
@@ -15,16 +15,16 @@
|
||||
# Please submit bugfixes or comments via http://bugs.opensuse.org/
|
||||
#
|
||||
|
||||
|
||||
Name: perl-SQL-Abstract
|
||||
Version: 1.72
|
||||
Release: 1
|
||||
License: GPL+ or Artistic
|
||||
Release: 0
|
||||
%define cpan_name SQL-Abstract
|
||||
Summary: Generate SQL from Perl data structures
|
||||
Url: http://search.cpan.org/dist/SQL-Abstract/
|
||||
License: GPL-1.0+ or Artistic-1.0
|
||||
Group: Development/Libraries/Perl
|
||||
#Source: http://www.cpan.org/authors/id/F/FR/FREW/SQL-Abstract-%{version}.tar.gz
|
||||
Source: %{cpan_name}-%{version}.tar.gz
|
||||
Url: http://search.cpan.org/dist/SQL-Abstract/
|
||||
Source: http://www.cpan.org/authors/id/F/FR/FREW/SQL-Abstract-%{version}.tar.gz
|
||||
BuildArch: noarch
|
||||
BuildRoot: %{_tmppath}/%{name}-%{version}-build
|
||||
BuildRequires: perl
|
||||
@@ -59,112 +59,6 @@ this module to do what you mean, based on the data structures you provide
|
||||
it. The big advantage is that you don't have to modify your code every time
|
||||
your data changes, as this module figures it out.
|
||||
|
||||
To begin with, an SQL INSERT is as easy as just specifying a hash of
|
||||
'key=value' pairs:
|
||||
|
||||
my %data = (
|
||||
name => 'Jimbo Bobson',
|
||||
phone => '123-456-7890',
|
||||
address => '42 Sister Lane',
|
||||
city => 'St. Louis',
|
||||
state => 'Louisiana',
|
||||
);
|
||||
|
||||
The SQL can then be generated with this:
|
||||
|
||||
my($stmt, @bind) = $sql->insert('people', \%data);
|
||||
|
||||
Which would give you something like this:
|
||||
|
||||
$stmt = "INSERT INTO people
|
||||
(address, city, name, phone, state)
|
||||
VALUES (?, ?, ?, ?, ?)";
|
||||
@bind = ('42 Sister Lane', 'St. Louis', 'Jimbo Bobson',
|
||||
'123-456-7890', 'Louisiana');
|
||||
|
||||
These are then used directly in your DBI code:
|
||||
|
||||
my $sth = $dbh->prepare($stmt);
|
||||
$sth->execute(@bind);
|
||||
|
||||
Inserting and Updating Arrays
|
||||
If your database has array types (like for example Postgres), activate
|
||||
the special option 'array_datatypes => 1' when creating the
|
||||
'SQL::Abstract' object. Then you may use an arrayref to insert and
|
||||
update database array types:
|
||||
|
||||
my $sql = SQL::Abstract->new(array_datatypes => 1);
|
||||
my %data = (
|
||||
planets => [qw/Mercury Venus Earth Mars/]
|
||||
);
|
||||
|
||||
my($stmt, @bind) = $sql->insert('solar_system', \%data);
|
||||
|
||||
This results in:
|
||||
|
||||
$stmt = "INSERT INTO solar_system (planets) VALUES (?)"
|
||||
|
||||
@bind = (['Mercury', 'Venus', 'Earth', 'Mars']);
|
||||
|
||||
Inserting and Updating SQL
|
||||
In order to apply SQL functions to elements of your '%data' you may
|
||||
specify a reference to an arrayref for the given hash value. For
|
||||
example, if you need to execute the Oracle 'to_date' function on a
|
||||
value, you can say something like this:
|
||||
|
||||
my %data = (
|
||||
name => 'Bill',
|
||||
date_entered => \["to_date(?,'MM/DD/YYYY')", "03/02/2003"],
|
||||
);
|
||||
|
||||
The first value in the array is the actual SQL. Any other values are
|
||||
optional and would be included in the bind values array. This gives
|
||||
you:
|
||||
|
||||
my($stmt, @bind) = $sql->insert('people', \%data);
|
||||
|
||||
$stmt = "INSERT INTO people (name, date_entered)
|
||||
VALUES (?, to_date(?,'MM/DD/YYYY'))";
|
||||
@bind = ('Bill', '03/02/2003');
|
||||
|
||||
An UPDATE is just as easy, all you change is the name of the function:
|
||||
|
||||
my($stmt, @bind) = $sql->update('people', \%data);
|
||||
|
||||
Notice that your '%data' isn't touched; the module will generate the
|
||||
appropriately quirky SQL for you automatically. Usually you'll want to
|
||||
specify a WHERE clause for your UPDATE, though, which is where handling
|
||||
'%where' hashes comes in handy...
|
||||
|
||||
Complex where statements
|
||||
This module can generate pretty complicated WHERE statements easily.
|
||||
For example, simple 'key=value' pairs are taken to mean equality, and
|
||||
if you want to see if a field is within a set of values, you can use an
|
||||
arrayref. Let's say we wanted to SELECT some data based on this
|
||||
criteria:
|
||||
|
||||
my %where = (
|
||||
requestor => 'inna',
|
||||
worker => ['nwiger', 'rcwe', 'sfz'],
|
||||
status => { '!=', 'completed' }
|
||||
);
|
||||
|
||||
my($stmt, @bind) = $sql->select('tickets', '*', \%where);
|
||||
|
||||
The above would give you something like this:
|
||||
|
||||
$stmt = "SELECT * FROM tickets WHERE
|
||||
( requestor = ? ) AND ( status != ? )
|
||||
AND ( worker = ? OR worker = ? OR worker = ? )";
|
||||
@bind = ('inna', 'completed', 'nwiger', 'rcwe', 'sfz');
|
||||
|
||||
Which you could then use in DBI code like so:
|
||||
|
||||
my $sth = $dbh->prepare($stmt);
|
||||
$sth->execute(@bind);
|
||||
|
||||
Easy, eh?
|
||||
|
||||
%prep
|
||||
%setup -q -n %{cpan_name}-%{version}
|
||||
|
||||
@@ -180,9 +74,6 @@ Complex where statements
|
||||
%perl_process_packlist
|
||||
%perl_gen_filelist
|
||||
|
||||
%clean
|
||||
%{__rm} -rf %{buildroot}
|
||||
|
||||
%files -f %{name}.files
|
||||
%defattr(644,root,root,755)
|
||||
%doc Changes
|
||||
|
||||
Reference in New Issue
Block a user