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