ANN : new SQL builder module starting development

[metaperl]

From 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 [e-mail] (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

[arkturuz]

This new module, whose initial name might be MuldisX::SQLBuilder (MXSB; name subject to be changed, suggestions?)

Data::SQLize? It seems there's no module with 'sqlize' in it. It would be a good start then. And it sounds cool: 'let's sequelize that data' :)