Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

Comment on

( #3333=superdoc: print w/ replies, xml ) Need Help??

I just released a new CPAN module, and it is rather nice. Other people have even agreed.

DBIx::PreQL

Less terrible dynamic SQL

Why?

Managing database interaction is a major part of what software developers doing. We've tried everything from ORM, to programmatic query builders to embedding SQL in our code. Each of these attempts suffer as the complexity of the application and the queries increases.

When what started as a simple script with 2 distinct queries grows into a web hosted service with a full blown AJAX UI, database queries proliferate, and many of them wind up being nearly identical, except for a few lines. Get widgets in stock. Get discontinued widgets in stock. Get widgets of model type foo. Get count of widgets in stock. Get count of each model type of discontinued widgets in stock. Blah blah blah.

To stay sane in the face of all that, we need some automation.

Most existing solutions try to abstract and hide the SQL from the programmer. In my experience this just makes it harder to debug errors when the occur.

What is it?

DBIx::PreQL takes an opposite approach from most SQL automation tools--instead of trying to hide the SQL behind abstraction layers, it adds a tiny bit of markup to existing SQL.

What does it do?

DBIx::PreQL consumes a marked up query and a hash of data. Based on the mark up and the data, each line is considered for inclusion or deletion.

Tags:

Tag Meaning ----|---------------------- * Always include line & Include line if ALL parameters and dependencies are true. | Include line if ANY parameters and dependencies are true. # Never include line, i.e. this line is a comment

Placeholders and Dependencies:

Symbol | Meaning | Example | In Query | Bind Var --------|-------------------|---------|--------------|---------- ?? Named Placeholder ?foo? '?' YES !! Dependency !foo! Empty string NO !~! Negated Dependency !~foo! Empty string NO

Example:

my $sql = <<'END'; # TAG CODE with PLACEHOLDERS and DEPENDENCIES * SELECT & count(*), !want_count! & some, !want_data! & stuff, !want_data! * WHERE * foo = ?foo? & AND bar = ?bar? & ORDER BY someting !want_data! & LIMIT ?page_size? !want_data! & OFFSET ?offset? !want_data! END my ($query, @bind_vars) = DBIx::PreQL->build_query( query => $sql, data => { foo => $foo, bar => $bar, want_count => !!$count_only, want_data => !$count_only, page_size => $page_size, $offset => $page_size * $page_number, }, );

Given  $foo='Foolike', $bar='Barlike', $count_only=0, $page_size=10, $page_number=3, we get the following query and bind vars:

SELECT some, stuff WHERE foo = ? AND bar = ? ORDER BY someting LIMIT ? OFFSET ? BINDVARS: 'Foolike', 'Barlike', 3, 30

This is a short intro to the feature set. There are some nice tools for working with NULLs, the need to inject literal SQL and lists. Download it, try it and see them for yourself.

CPAN - DBIx::PreQL Github - https://github.com/daotoad/DBIx-PreQL


TGI says moo


In reply to Announcing: DBIx::PreQL - Less terrible dynamic SQL by TGI

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • Outside of code tags, you may need to use entities for some characters:
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others meditating upon the Monastery: (5)
    As of 2014-07-26 08:11 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      My favorite superfluous repetitious redundant duplicative phrase is:









      Results (175 votes), past polls