1. Alzabo and SPOPS are large because they implement a lot of functionality across different databases. (And in the case of SPOPS, across different datasources like LDAP.) They're also large because they handle many of the outlying cases that any homebrewed SQL generation mechanism will eventually have to deal with if it's used for a non-trivial application.

Most large modules like these also ensure that you don't need to know everything about them to start using them. There are huge swaths of SPOPS that most people never need to care about. All they need to do is feed a configuration to a particular method and they can start accessing their database using straightforward methods. This doesn't prevent you from writing your own more complicated access, but it takes care of the common 90% usage.

The KISS principle is excellent, but difficult problems often call for larger solutions. The fact that entire companies and productlines have been built on simplifying database access tells me this is a difficult problem.

2. That's fine if you're keeping this MySQL-specific. You might want to mention it :-)

3. As for non-straightforward queries using straight SQL, maybe we have different definitions for "non-straightforward". When I see something like:

%Q = (
    country             => 'Ukraine',
    age__ge             => 25,
    join_date__date_ge  => "-1 DAY",
    _balance__gt        => 5.50,
    goods               => ["Monitor LG", "CDROM 52x"],
    -order              => ["_balance DESC", "age"],
    );

[download]

This screams complicated. Based on my own painful excursions into generating complex SQL from this sort of information, I see the temptations that this can offer and am trying to warn you away from them.

4. Column-level security is when only certain users can update certain columns. Your statement that "ADMIN _can_ set balance for any USER..." leads me to believe you're doing this. Apologies if I was mistaken.

Don't get me wrong, I'm not saying that what you're doing stinks. If it works for you and makes your code easier to maintain, fantastic.

Good luck.

Chris
M-x auto-bs-mode

In reply to Re (3): Magical SQL by lachoy
in thread Magical SQL by powerman

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!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • 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, details, 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, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            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.