I love SQLite, and so does most of the world.
It is successful and liked for many of the same reasons that the ::Tiny modules are successful and liked, although it seems to have done a better job convincing the world that just because it isn't as fully featured as a "real" database it can still be useful.
Tons of features (like concurrency, access control, sequences, and so on) that were too hard in a single file embedded environment were simply removed.
But the result of this is that SQLite doesn't behave QUITE like a regular database, and sometimes you need to behave slightly differently in order to use it properly.
I've got a number of little programs now that use SQLite data stores, mostly using Class::DBI. But it is starting to gradually rot, and it has more problems than I'm comfortable with.
Most of the other object-relational systems like Rose, Tangram, DBIx::Class, Jifty::DBI and so on, are built first for doing real work on first class databases.
So, for my purposes, they often seem to me to be a bit overengineered when you try to use them as a simple embedded data store.
Now that the CPAN::Metrics module is alive again and I'm doing more work with SQLite databases, this problem is starting to itch a LOT and it's time I scratched it.
So I've started work on a brand new object-relational module that is ONLY able to support SQLite, and has specific behaviors that align to the strengths of SQLite.
ORLite will reflect a mix of influences and techniques taken from Object::Tiny, Class::DBI, and DBIx::Class::Schema::Loader::SQLite and my private AppSpace code-generating application platform.
It also uses a couple of techniques that are normally frowned on, such as extensive string-evals, APIs that change in response to external conditions, and a connection pooling methodology that would be insane on any database that couldn't do 1000 connections a second.
The goal is two-fold. To keep the code and memory overhead as tiny as possible, and to require as little typing from both the author of an ORLite module, and the users of that module, as I can reasonably provide.
I'll be writing a bit more about ORLite in the coming days, as it gets fleshed out a bit more, but you can get some idea of the concept from the 0.01 version that is now on the CPAN.
Years ago I worked on a very impressive ORM that was written in-house (not by me). It had lots of kinks, but one of the nice things was something like this:
"Gimme a report of sales per store in Texas, per salesman."
Um, OK.
# The actual API was different
my $dataset = dataset({
data => [qw/sales store salesman/],
where => { state => 'Texas' },
group => ['salesman'],
});
Notice anything interesting? There's no FROM or complicated joins. You just told it to give you the damned data. By requiring FK constraints to describe relationships (and forcing people to use fully-qualified names if ambiguities occur), you can infer the 'from'. Sometimes the fields we asked for didn't even exist. They were virtual fields that were calculated on the fly.
The above example might have to join stores to salesman to get the salesman and join stores to orders and the latter to order_items to get the actual sales, but the programmer didn't care.
This required that we be very careful about writing out our relationships, but it worked fairly well and made the bulk of our reporting very, very simple (you'd want a more robust query then our primitive WHERE statements. Perhaps the Google Query Language might be a start).
