- CHANGES AND ENHANCEMENTS
- Enhancements in SQL::Statement 1.xx
- Enhancements in SQL::Statement 2.xx
- RESOURCES AND CONTRIBUTIONS
SQL::Statement::Roadmap - Planned Enhancements for SQL::Statement and SQL::Parser
Jens Rehsack - June 2010
This document gives a high level overview of the future of SQL::Statement, SQL::Parser and it's impact.
The planned enhancements cover testing, performance, reliability, extensibility and more.
SQL::Statement 1.xx will not receive big changes, but a few enhancements may help us to design SQL::Statement 2.xx much better.
SQL::Statement misses some functions, types, operators etc. It's supported to add missing functionality - but the implementation wasn't picked up during the modernizing of column evaluation. See RT#52397 for some more information.
This should be done before SQL::Statement 1.xx reaches the end of it's road.
The SQL::Parser is implemented based on a lot of regular expressions and some manual developed logic. This creates some issues like RT#53416 or RT#55190. Further, trailing
; makes SQL::Parser croaking. It should be proved what can be fixed without deep changes and what has to wait.
SQL::Statement 1.xx will not receive additional time to improve it's performance. The performance is good as it is and improvement requires design changes.
Bugs will be fixed - when possible. SQL::Statement 1.28 is much more reliable than SQL::Statement 1.15. And even if no extra effort will put into increase reliability, all remarked issues are thankful taken to learn how to design SQL::Statement 2.xx better.
SQL::Statement 1.xx is highly extensible, even if a more object oriented design would improve that. The 1.xx branch will not receive redesign to reach higher extensibility on coding level.
Concerning the procedural design of SQL::Statement 1.xx a rewrite of the basic components is required.
The SQL::Parser needs to be modified to be able to use a http://en.wikipedia.org/wiki/Backus_Naur_Form. This would allow users and developers to rely on many different SQL dialects. It further allows better extensibility from feature point of view without loosing ANSI SQL compatibility.
SQL::Statement should be reduced to a simple coordinating engine. The executing tasks should be organized into separated commands. This will reduce side effects and will open the door for higher level optimizations, reliability improvements or sub-selects (or other calculated tables).
There are a bigger amount of missing features, and not all table backends will be able to support each new feature. The most of the wanted features need additional discussing and everyone is welcome to do it on the mailto:firstname.lastname@example.org.
Locking table within SQL scripts to manually control table consistence over several operations. The current locking support is restricted to one statement.
Executing statements on a temporary copy of the table data.
The easiest way to implement this would be to create a SQL::Statement::RAM on
BEGIN TRANSACTION and write the entire table back on
COMMIT or discard on
Better performance could be reached when the implementation allows to memorize the single modification and apply them at
COMMIT. This needs more discussion.
Adding, removing or modifying columns isn't supported for once created tables. A generic
ALTER TABLE seems to rely on the implementation of the transaction support - until better ideas are provided.
Currently some table backends have implicitely support to access defined rows quicker than fetching each row and evaluating the where clause against the row data.
An interface would be required to configure fetching to return only rows matching a restricted where clause. Another (probably better) way to support indices would be to fetch index entries at first and have an interface to the table fetching lines based on an index key.
In most cases queries can be re-expressed without using sub-selects. But anyway, there are circumstances where sub-selects are required.
The first implementation will do the sub-select before the primary statement will be executed without any further optimization. Hopefully a later version will provide better Performance with some optimizations.
Currently the only variable I can imagine is
ROWNUM. More suggestions are very welcome.
In SQL::Statement 1.xx the function
RUN () provides SQL script execution. This function may have limitations and side effects (at least when the executed SQL touched the same tables as the primary statement).
I plan to improve the SQL script support to remove the side effects one the one hand and have more flexible and easier way to execute them.
Finally it should be possible to execute a script via:
$dbh->do( join( ";", @script ) );
Most important when doing complicated things is having callback functions for several events. While real triggers won't be possible for SQL::Statement and underlying pseudo-databases, callbacks could be provided via triggers.
There are several performance optimizations wanted for SQL::Statement 2.xx.
The first one should be done on a very high level (query optimization) by implementing algebraic evaluation of queries and clean implementation of typical database algorithms. With respect to the basic optimization rule premature optimization is the root of all evil, it's primarily targeted to have adequate fast, reliable implemtation of many algorithms (e.g. early incomplete evaluation to reduce amount of rows, transpose where clause to evaluate constants first) and a clever controller choosing the right algorithm for a specific query.
The second optimization goal means: implementing most expensive methods in XS. This requires a good performance test suite as well as some real world use cases.
This is one of the primary goals of SQL::Statement. I hope to reach it using test driven development and I hope I get some more todo's from the users for this.
The currently high level of extensibility should be increased on coding level. This will be done by redesigning the entire parser and executing engine using object oriented techniques and design patterns.
Many tests in SQL::Statement are not well organized. The tests should be reorganized into several parts:
- Basic API
This part shall test the entire basic API of SQL::Statement, SQL::Parser and probably the entire engine command classes.
- DBI / Table API
This part should test if the API to DBI drivers work (maybe an empty test driver will be needed for that).
This part should test the functionality of the SQL::Parser and the SQL::Statement engine.
This part should be used to implement full use cases (ideally got from real world projects) to allow doing optimizations.
Our priorities are localized to our current issues and proof of concept fixes for upcoming SQL::Statement 2.xx.
Any additional priorities (as missing features, the SQL::Statement rewrite) will come later and can be modified by (paying) users.
See http://dbi.perl.org/contributing for how you can help.
If your company has benefited from the DBI or SQL::Statement, please consider if it could make a donation to The Perl Foundation "DBI Development" or "SQL::Statement Development" fund at http://dbi.perl.org/donate to secure future development.
Alternatively, if your company would benefit from a specific new DBI or SQL::Statement feature, please consider sponsoring its development through the options listed in the section "Commercial Support from the Author" on http://dbi.perl.org/support/.
Using such targeted financing allows you to contribute to DBI development (including SQL::Statement and PurePerl DBI drivers) and rapidly get something specific and directly valuable to you in return.
1 POD Error
The following errors were encountered while parsing the POD:
- Around line 65:
alternative text 'http://en.wikipedia.org/wiki/Backus_Naur_Form' contains non-escaped | or /