Often SQL is the only liferaft one can find when sifting through foreign code.

()-()
 \"/
  `                                                     

ignatz++. Sing it, brother. Testify.

But I agree about MySQL-specific. This is not good, but I prefer simple and fast code instead of compatible with all world. So, if I can make it portable and non MySQL-specific without make it slow and complicated, i do it.

And I don't try to make some sort of abstraction layer for any type of SQL queries. Non-straightforward queries MUST be done using straight SQL, and I say it already in main post.

This idea is a lazy automagicall replacement only for simple SQL queries. And as I write before our survey show what >95% of used SQL queries in typical CGI scripts ARE simple and can be done using this interface. In fact, size of my CGIs now 50% less with this interface!

And, please, explain what do you mean as column-level security?

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

Yes, you right. Things like this one: join_date__date_ge => "-1 DAY" are too complicated for my eyes too!. And maybe this task must be solved in different way. Or removed at all, and queries like this must be executed straightforward.

The __date_ge was born as solution for this problem: in too many cases using just "field=value" insufficient, we need also "field<=value", "field > DATE_SUB(NOW(), INTERVAL value)" ans so on. But accepting plain SQL from user this way:
script.cgi?field=>5
is too insecure.

So, we still looking for more simple solution for this! And this is a main reason to post here.