#
# spec file for package perl-SQL-Abstract (Version 1.72)
#
# Copyright (c) 2010 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
# upon. The license for this file, and modifications and additions to the
# file, is the same license as for the pristine package itself (unless the
# license for the pristine package is not an Open Source License, in which
# case the license is the MIT License). An "Open Source License" is a
# license that conforms to the Open Source Definition (Version 1.9)
# published by the Open Source Initiative.

# Please submit bugfixes or comments via http://bugs.opensuse.org/
#

Name:           perl-SQL-Abstract
Version:        1.72
Release:        1
License:        GPL+ or Artistic
%define cpan_name SQL-Abstract
Summary:        Generate SQL from Perl data structures
Url:            http://search.cpan.org/dist/SQL-Abstract/
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
BuildArch:      noarch
BuildRoot:      %{_tmppath}/%{name}-%{version}-build
BuildRequires:  perl
BuildRequires:  perl-macros
BuildRequires:  perl(Class::Accessor::Grouped) >= 0.10002
BuildRequires:  perl(Getopt::Long::Descriptive) >= 0.086
BuildRequires:  perl(Hash::Merge) >= 0.12
BuildRequires:  perl(List::Util)
BuildRequires:  perl(Scalar::Util)
BuildRequires:  perl(Test::Deep) >= 0.106
BuildRequires:  perl(Test::Exception)
BuildRequires:  perl(Test::Warn)
Requires:       perl(Class::Accessor::Grouped) >= 0.10002
Requires:       perl(Getopt::Long::Descriptive) >= 0.086
Requires:       perl(Hash::Merge) >= 0.12
Requires:       perl(List::Util)
Requires:       perl(Scalar::Util)
%{perl_requires}

%description
This module was inspired by the excellent the DBIx::Abstract manpage.
However, in using that module I found that what I really wanted to do was
generate SQL, but still retain complete control over my statement handles
and use the DBI interface. So, I set out to create an abstract SQL
generation module.

While based on the concepts used by the DBIx::Abstract manpage, there are
several important differences, especially when it comes to WHERE clauses. I
have modified the concepts used to make the SQL easier to generate from
Perl data structures and, IMO, more intuitive. The underlying idea is for
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}

%build
%{__perl} Makefile.PL INSTALLDIRS=vendor
%{__make} %{?_smp_mflags}

%check
%{__make} test

%install
%perl_make_install
%perl_process_packlist
%perl_gen_filelist

%clean
%{__rm} -rf %{buildroot}

%files -f %{name}.files
%defattr(644,root,root,755)
%doc Changes

%changelog