# phpman > perldoc > SQL::Statement::Roadmap ## NAME [SQL::Statement::Roadmap](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement%3A%3ARoadmap/markdown) - Planned Enhancements for [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) and [SQL::Parser](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AParser/markdown) Jens Rehsack - June 2010 ## SYNOPSIS This document gives a high level overview of the future of [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown), [SQL::Parser](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AParser/markdown) and its impact. The planned enhancements cover testing, performance, reliability, extensibility and more. ## CHANGES AND ENHANCEMENTS ### Enhancements in [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) 1.xx [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) 1.xx will not receive big changes, but a few enhancements may help us to design [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) 2.xx much better. CREATE and DROP of FUNCTION, KEYWORD, OPERATOR, TYPE [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) is missing some functions, types, operators etc. It's supported to add missing functionality - but the implementation was not picked up during the modernizing of column evaluation. See RT#52397 for some more information. This should be done before [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) 1.xx reaches the end of its road. Parser improvements The [SQL::Parser](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AParser/markdown) is implemented based on a lot of regular expressions and some manually developed logic. This creates some issues like RT#53416 or RT#55190. Further, trailing ";" causes [SQL::Parser](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AParser/markdown) to croak. We need to decide what can be fixed without internal design changes and what has to wait. Performance There is no intention to work on performance improvements in [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) 1.xx. The performance is good as it is and improvement requires design changes. Reliability Bugs will be fixed - where possible. [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) 1.28 is much more reliable than [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) 1.15. Even if a bug cannot be fixed all issues are gratefully received as they will be considered in the design process for [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) 2.xx better. Extensibility [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) 1.xx is highly extensible, even if a more object oriented design would improve that. The 1.xx branch will not be redesigned for greater extensibility on a coding level. ### Enhancements in [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) 2.xx Concerning the procedural design of [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) 1.xx a rewrite of the basic components is required. [SQL::Parser](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AParser/markdown) rewrite The [SQL::Parser](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AParser/markdown) needs to be modified to be able to use a Backus Naur Form <>. This would allow users and developers to rely on many different SQL dialects. This will allow better extensibility from a feature point of view without losing ANSI SQL compatibility. [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) rewrite [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) 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). Features There is a large list of missing features but not all table backends will be able to support each new feature. The most popular requested features need additional discussion and everyone is welcome to do it on the mailto:. LOCK TABLE Locking table within SQL scripts to manually control table consistence over several operations. The current locking support is restricted to one statement. Transaction support Executing statements on a temporary copy of the table data. The easiest way to implement this would be to create a [SQL::Statement::RAM](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement%3A%3ARAM/markdown) on "BEGIN TRANSACTION" and write the entire table back on "COMMIT" or discard on "ROLLBACK". Better performance could be achieved in cases where the implementation is enabled to memorize pending modifications and apply them at "COMMIT". On the other hand there are already capabilities to improve some operations, which might create confusion in case of transactions. This needs more discussion. ALTER TABLE Adding, removing or modifying columns is not supported for created tables. A generic "ALTER TABLE" seems to rely on the implementation of the transaction support - until better ideas are provided. Indices Currently some table backends have implicit support to access specified 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. Sub-Selects In most cases queries can be re-expressed without using sub-selects. But in any case, there are circumstances where sub-selects are required. The first implementation will do the sub-select before the primary statement is executed without any further optimization. Hopefully a later version will provide better Performance with some optimization. Query based variables Currently the only variable I can imagine is "ROWNUM". More suggestions are very welcome. Better SQL Script support In [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) 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 on the one hand and have a more flexible and easier way to execute them. Finally it should be possible to execute a script via: $dbh->do( join( ";", @script ) ); Trigger support Most important when doing complicated things is having callback functions for several events. While real triggers will not be possible for [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) and underlying pseudo-databases, callbacks could be provided via triggers. Performance There are several performance optimizations required for [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) 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 is primarily targeted to have an adequately fast, reliable implementation 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 usage cases. Reliability This is one of the primary goals of [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown). I hope to reach it using test driven development and I hope I get some more todo's from the users for this. Extensibility The currently high level of extensibility should be increased on a coding level. This will be done by redesigning the entire parser and execution engine using object oriented techniques and design patterns. Testing Many tests in [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) are not well organized. The tests should be reorganized into several parts: Basic API This part should test the entire basic API of [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown), [SQL::Parser](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AParser/markdown) 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). Functionality This part should test the functionality of the [SQL::Parser](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AParser/markdown) and the [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) engine. Performance This part should be used to implement full usage cases (ideally from real world projects) to allow for testing optimizations. ## PRIORITIES Our priorities are localized to our current issues and proof of concept fixes for upcoming [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) 2.xx. Any additional priorities (as missing features, the [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) rewrite) will come later and can be modified by (paying) users. ## RESOURCES AND CONTRIBUTIONS See <> for *how you can help*. If your company has benefited from the DBI or [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown), please consider if it could make a donation to The Perl Foundation "DBI Development" or "[SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) Development" fund at <> to secure future development. Alternatively, if your company would benefit from a specific new DBI or [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) feature, please consider sponsoring its development through the options listed in the section "Commercial Support from the Author" on <>. Using such targeted financing allows you to contribute to DBI development (including [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) and PurePerl DBI drivers) and rapidly get something specific and directly valuable to you in return. Thank you.