[Dbix-class] ANN : new SQL builder module starting development

[Darren Duncan]

All,

I just wanted to give you advance notice of my short term plans regarding 
database-related Perl modules I intend to make, but in this case haven't written 
yet, in case you want to contribute to them from an early point.

My new first priority for development, after I flesh out a critical section of 
the concrete syntax of my Muldis D language, is to create a new Perl module 
whose function is to generate SQL from Perl data structures.

This new module, whose initial name might be MuldisX::SQLBuilder (MXSB; name 
subject to be changed, suggestions?), is my own "yet another" take on the SQL 
generation problem space that has numerous varied solutions already, and it is 
aimed squarely at being an alternative to these modules, among others: 
SQL::Abstract, Rose::DB::Object::QueryBuilder, SQL::Generator, and some 
Producers of SQL::Translator.  Where applicable, some "Adopt" modules can also 
be made which wrap MXSB and provide the APIs of these older modules, to assist 
migration.

If you wish to contribute to the development of MuldisX::SQLBuilder, then please 
join the email discussion list muldis-db-devel at mm.DarrenDuncan.net (see 
http://mm.DarrenDuncan.net/mailman/listinfo ), which is its main forum, and 
contribute your ideas there.

In the near future I'll start a public GIT repository for this project where you 
can get the source and which can facilitate contributions.

So the obvious question is, what would be compelling about MXSB such that people 
would adopt it versus a more traditional alternative.

Here are a few of its features:
  - Generate SQL stored procedures, functions, triggers : this is probably 
MXSB's single most significant and unique feature.
  - Generate SQL views, both normal and materialized.
  - Generate table unique keys, foreign keys, and join conditions ranging 
multiple columns.
  - Arbitrary check constraints.
  - Arbitrarily complex queries including sub-queries, unions, grouping, 
summaries, quotas, hierarchical, stored routine calls, multi-table joins, inner 
and outer joins, and more.
  - Full named bind parameters support.
  - Virtual / generated table columns.
  - Abstracted syntax for a large number of built-in type literals and 
operators, including boolean, numeric, text, binary, relational, temporal, and more.
  - Support for collection-typed field values.
  - Requires renaming of columns as appropriate so that the non-table-qualified 
column names in every rowset are distinct; a simple solution to the problem of 
duplicate names; that is, enforces this aspect of good practice.
  - Input data structures declare what version of the input language they are 
written in, for forward/backward compatibility.
  - Every other useful feature of the traditional alternatives.

How you use MuldisX::SQLBuilder is essentially the same as how you use the likes 
of SQL::Abstract et al.  It provides one or more functions that take Perl data 
structures as inputs and that results in a SQL string (plus extra meta-data 
where applicable) that you can then pass to DBI et al for execution.  It does 
*not* actually talk to DBI nor any database, but leaves that to users.

The format of the Perl data structures that MXSB accepts for input loosely 
resemble those of SQL::Abstract et al, in that they are trees of Perl arrayrefs 
and hashrefs and scalars etc (generally no special objects).  MXSB is unique 
however in that its accepted input formats are mostly defined externally to the 
module, and formally, by the Muldis D language spec, specifically the 
HDMD_Perl5_STD dialect ( 
http://search.cpan.org/dist/Muldis-D/lib/Muldis/D/Dialect/HDMD_Perl5_STD.pod ), 
which differs significantly from the formats that other SQL-gen modules take, 
though it was strongly influenced to be like them where reasonable.  But MXSB 
actually defines and uses a delta of HDMD_Perl5_STD as its input, adding or 
changing a few features to make MXSB easier to use and adapt by its target 
audience; for example it adds the ability to attach raw SQL fragments to the 
input where they might be necessary to get around any of MXSB's limitations.

While my separate Muldis::Rosetta project actually will be a functional DBMS or 
DBI-wrapper and implement all of Muldis D, MuldisX::SQLBuilder will just address 
a subset of the language, what is easily implementable in SQL over DBI, without 
much complexity and without needing to generate Perl code also to fill in the 
gaps in SQL's functionality.  Therefore, the amount of functionality you get 
with MXSB depends on what DBMS you are generating SQL for; if that DBMS doesn't 
support a feature natively, you can't use it from MXSB without implementing the 
difference yourself.  So MXSB is alike to SQL::Abstract et al in this way.

I anticipate that MXSB will require Perl 5.8.1+ and use the latest Moose, but 
otherwise would have relatively few direct dependencies.

By default the license of MXSB would be the LGPLv3+, same as my Set::Relation 
and Muldis::Rosetta modules, though if contributors insist otherwise I can 
instead make it the Artistic v2+, which is the same license as Parrot and 
Rakudo, and which is the "modern 'same version as Perl'" license.

Thank you. -- Darren Duncan