[Dbix-class] RFC: proposed QDRDBMS temporary module

[Darren Duncan]

All,

To set a context for this email, I will remind you, or tell you for 
the first time if you hadn't already heard it, that the current plans 
for my Rosetta RDBMS have it being written in Perl 6 only due to that 
language's much stronger support for its needs.  And so, while you 
will be able to use it in pure Perl 5 programs, your stack will need 
to include v6.pm and its dependencies, and those currently represent 
a fast changing pre-alpha environment.

Meanwhile, I have come to the point in my current paid $job that I 
want to use some Rosetta functionality "yesterday" in further 
evolutions of my in-production Perl 5 code.

And so, I propose to write a "quick and dirty but stable" initial 
subset of some core Rosetta-esque functionality as a Perl 5 module so 
I can use that now.  This will be released on CPAN for the benefit of 
others to try out, probably under the name of "QDRDBMS" (Quick and 
Dirty Relational Database Management System), and will represent 
maybe 10% of what Rosetta will do, though an important and 
representational 10% nonetheless.  It will be deprecated in favor of 
Rosetta.

The QDRDBMS will fundamentally be a SQL-generating DBI wrapper, and 
will more closely resemble existing DBI wrappers than the actual 
Rosetta will, though still having some key differences such that I 
don't use an existing module instead.

What features it adapts from Rosetta:

1.  The input AST will be fully atomic, and not include SQL fragments.

2.  Arbitrary depth subqueries are supported; in fact, subqueries are 
integral to how the querying works.

3.  Queries are defined using nested calls to fundamental relational 
operators like project() and join() and restrict() and group() etc 
rather than a single select() with a large number of arguments.

4.  The result of each relational operation is a set rather than a 
bag; every element returned from a query is unique.  Every "table" 
has a unique constraint on it so it won't store duplicates.  All 
columns have distinct names too.

5.  There are no SQL nulls; immediately following the assignment of 
any foo to bar, foo equals bar, regardless of what foo previously 
contained.  Every "column" in a "table" has a not-null constraint on 
it.  Every outer-join will have appropriate default defined values in 
fields where it would otherwise be null in a typical outer-join query.

6.  Nested transactions will partially be supported.

7.  A semblence of strong data typing is provided, but very few 
actual data types.

8.  A limited range of multi-valued data types are supported, such as 
using additional "row sets" as a field value.  This can be useful 
when querying for both parts of a one-to-many relationship; one main 
result row exists for the "one" side, and a field can include the 
related "many" rows.  (This is also one way to return the result of 
an outer-join, where the optional side is represented by a field 
containing a set of zero or one items.)

9.  The module can install its own database schema, and in fact this 
is recommended; regardless, the module will not verify that any 
pre-existing schema is correct.

10.  Primary, unique, and foreign keys can be defined for tables as 
far as the underlying SQL DBMS will enforce them, but we won't 
enforce them ourselves if it doesn't.

11.  Everything in the AST will use name-spaces, at least System:: vs 
User::, and all built-ins, like string or math ops, will be accessed 
using our own names, which are alphanumeric.

12.  The licence will be GPL 2+ (3+ later when its ready) with 
linking exceptions so other free software code that uses it can keep 
their own licenses.

13.  Placeholders plus bind variables will be used in generated SQL 
if possible.

14.  Limited recursive query support.

Some ways that QDRDBMS differs from Rosetta:

1.  There will be just the bare minimum of POD or other 
documentation, or next to no POD at all.  There will also be 
practially no test suite or examples.

2.  The object implementation will be old-school and simple, using 
blessed hashes that don't enforce any privacy.

3.  It has no standalone pure Perl implementation; it initially 
requires DBI plus a separate pre-existing SQL database to use, and 
moreover it may initially only work with MySQL 4.1+ plus InnoDB; it 
should still be easy enough to extend it to use non-SQL alternatives 
like BDB or LDAP etc, though.

4.  Most idioms of the underlying database will not be abstracted 
away, such as whether character strings are case sensitive or not. 
But we will try to enforce Unicode strings where at all possible.

5.  It can not reverse engineer an existing database schema.

6.  A lot fewer operators and data types will be supported.

7.  Any pieces for gluing the core to specific backends will be 
bundled with it in the same distro, like practically all DBI wrappers 
today do; with Rosetta, each will be separate, like with DBI itself.

8.  Support for cursors or blob handles is left out.

9.  No support for defining stored procedures or triggers.

10.  Various other missing or unsupported or poorly supported things.

11.  It will require Perl 5.8.x or later, possibly 5.8.8+ to keep it simple.

12.  It will not come with a shell.

13.  All SQL identifiers will be generated as case-insensitive 
barewords, rather than quoted case-sensitive delimiters as Rosetta 
will.

14.  If a "table" is defined in the schema that includes attributes 
that are "table" as well, this will be implemented as a split 
one-to-many table, even if the underlying DBMS supports actual 
multi-valued attributes.

Now, the QDRDBMS module is intended to be temporary, providing 
functionality that I want to use now and that you may find useful 
now.  While it can be further evolved by myself or other interested 
parties that want to use it now, ultimately it will be deprecated in 
favor of Rosetta and Perl 6, and so may be deleted from CPAN when 
Rosetta is stable and can do everything it does.

(Of course, by then, Rosetta may not be named Rosetta anymore, but 
something else which is better for long term use; I do plan to rename 
Rosetta anyway.)

Regarding the "RFC" nature of this email, feel free to say whatever 
you want now, or feel free to wait until I actually release this. 
Or, perhaps the best piece of feedback I can get short term is what 
to name the module, knowing that it is meant to be temporary.  I 
thought "quick and dirty" would work well enough for the purpose, 
like it did for QDOS back in the '80s.

Thank you.

-- Darren Duncan