A couple of brief notes:

1. This seems MySQL-specific: not all databases have INNER JOIN or LAST_INSERT_ID. So you wind up building abstractions for these and bingo! you've reinvented Alzabo, SPOPS or many other SQL abstraction tools.

2. I agree with one of the previous posters that having column-level security is a bad idea -- it sounds like a good idea, but when you think about it, is it an idea that you really need? I implemented something with this four years ago. It worked, but it was a nightmare. And not just implementation-wise -- it's difficult for people to understand as well.

3. I have had quite a bit of experience in creating SQL from either metadata or parameters (see SPOPS, for starters). Over that time I have come to the conclusion that it's better to use straight SQL for creating non-straightforward queries. Why?

For all the bitching about SQL, it's a standard. It's been around for years. There are large books and web tutorials written about it. In short, people know it. And your metatadata or parameters for creating these queries winds up being more confusing and less maintainable than the SQL it's supposed to generate. Sure people can learn how the metadata or parameters work, but isn't it a better idea to leverage the knowledge people already have about SQL?

Just my 2c, etc.

Chris
M-x auto-bs-mode

In reply to Re: 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.