1SQL::Statement::RoadmapU(s3e)r Contributed Perl DocumentaStQiLo:n:Statement::Roadmap(3)
2
6 SQL::Statement::Roadmap - Planned Enhancements for SQL::Statement and
7 SQL::Parser
8 Jens Rehsack - June 2010
10
12 This document gives a high level overview of the future of
13 SQL::Statement, SQL::Parser and its impact.
14 The planned enhancements cover testing, performance, reliability,
16 extensibility and more.
17
19 Enhancements in SQL::Statement 1.xx
20 SQL::Statement 1.xx will not receive big changes, but a few
21 enhancements may help us to design SQL::Statement 2.xx much better.
22 CREATE and DROP of FUNCTION, KEYWORD, OPERATOR, TYPE
24 SQL::Statement is missing some functions, types, operators etc. It's
26 supported to add missing functionality - but the implementation was not
27 picked up during the modernizing of column evaluation. See RT#52397 for
28 some more information.
29 This should be done before SQL::Statement 1.xx reaches the end of its
31 road.
32 Parser improvements
34 The SQL::Parser is implemented based on a lot of regular expressions
36 and some manually developed logic. This creates some issues like
37 RT#53416 or RT#55190. Further, trailing ";" causes SQL::Parser to
38 croak. We need to decide what can be fixed without internal design
39 changes and what has to wait.
40 Performance
42 There is no intention to work on performance improvements in
44 SQL::Statement 1.xx. The performance is good as it is and improvement
45 requires design changes.
46 Reliability
48 Bugs will be fixed - where possible. SQL::Statement 1.28 is much more
50 reliable than SQL::Statement 1.15. Even if a bug cannot be fixed all
51 issues are gratefully received as they will be considered in the design
52 process for SQL::Statement 2.xx better.
53 Extensibility
55 SQL::Statement 1.xx is highly extensible, even if a more object
57 oriented design would improve that. The 1.xx branch will not be
58 redesigned for greater extensibility on a coding level.
59 Enhancements in SQL::Statement 2.xx
61 Concerning the procedural design of SQL::Statement 1.xx a rewrite of
62 the basic components is required.
63 SQL::Parser rewrite
65 The SQL::Parser needs to be modified to be able to use a Backus Naur
67 Form <http://en.wikipedia.org/wiki/Backus_Naur_Form>. This would allow
68 users and developers to rely on many different SQL dialects. This will
69 allow better extensibility from a feature point of view without losing
70 ANSI SQL compatibility.
71 SQL::Statement rewrite
73 SQL::Statement should be reduced to a simple coordinating engine. The
75 executing tasks should be organized into separated commands. This will
76 reduce side effects and will open the door for higher level
77 optimizations, reliability improvements or sub-selects (or other
78 calculated tables).
79 Features
81 There is a large list of missing features but not all table backends
83 will be able to support each new feature. The most popular requested
84 features need additional discussion and everyone is welcome to do it on
85 the mailto:dbi-dev@perl.org.
86 LOCK TABLE
88 Locking table within SQL scripts to manually control table consistence
90 over several operations. The current locking support is restricted to
91 one statement.
92 Transaction support
94 Executing statements on a temporary copy of the table data.
96 The easiest way to implement this would be to create a
98 SQL::Statement::RAM on "BEGIN TRANSACTION" and write the entire table
99 back on "COMMIT" or discard on "ROLLBACK".
100 Better performance could be achieved in cases where the implementation
102 is enabled to memorize pending modifications and apply them at
103 "COMMIT". On the other hand there are already capabilities to improve
104 some operations, which might create confusion in case of transactions.
105 This needs more discussion.
107 ALTER TABLE
109 Adding, removing or modifying columns is not supported for created
111 tables. A generic "ALTER TABLE" seems to rely on the implementation of
112 the transaction support - until better ideas are provided.
113 Indices
115 Currently some table backends have implicit support to access specified
117 rows quicker than fetching each row and evaluating the where clause
118 against the row data.
119 An interface would be required to configure fetching to return only
121 rows matching a restricted where clause. Another (probably better) way
122 to support indices would be to fetch index entries at first and have an
123 interface to the table fetching lines based on an index key.
124 Sub-Selects
126 In most cases queries can be re-expressed without using sub-selects.
128 But in any case, there are circumstances where sub-selects are
129 required.
130 The first implementation will do the sub-select before the primary
132 statement is executed without any further optimization. Hopefully a
133 later version will provide better Performance with some optimization.
134 Query based variables
136 Currently the only variable I can imagine is "ROWNUM". More suggestions
138 are very welcome.
139 Better SQL Script support
141 In SQL::Statement 1.xx the function "RUN ()" provides SQL script
143 execution. This function may have limitations and side effects (at
144 least when the executed SQL touched the same tables as the primary
145 statement).
146 I plan to improve the SQL script support to remove the side effects on
148 the one hand and have a more flexible and easier way to execute them.
149 Finally it should be possible to execute a script via:
151 $dbh->do( join( ";", @script ) );
153 Trigger support
155 Most important when doing complicated things is having callback
157 functions for several events. While real triggers will not be possible
158 for SQL::Statement and underlying pseudo-databases, callbacks could be
159 provided via triggers.
160 Performance
162 There are several performance optimizations required for SQL::Statement
164 2.xx.
165 The first one should be done on a very high level (query optimization)
167 by implementing algebraic evaluation of queries and clean
168 implementation of typical database algorithms. With respect to the
169 basic optimization rule premature optimization is the root of all evil,
170 it is primarily targeted to have an adequately fast, reliable
171 implementation of many algorithms (e.g. early incomplete evaluation to
172 reduce amount of rows, transpose where clause to evaluate constants
173 first) and a clever controller choosing the right algorithm for a
174 specific query.
175 The second optimization goal means: implementing most expensive methods
177 in XS. This requires a good performance test suite as well as some real
178 world usage cases.
179 Reliability
181 This is one of the primary goals of SQL::Statement. I hope to reach it
183 using test driven development and I hope I get some more todo's from
184 the users for this.
185 Extensibility
187 The currently high level of extensibility should be increased on a
189 coding level. This will be done by redesigning the entire parser and
190 execution engine using object oriented techniques and design patterns.
191 Testing
193 Many tests in SQL::Statement are not well organized. The tests should
195 be reorganized into several parts:
196 Basic API
198 This part should test the entire basic API of SQL::Statement,
199 SQL::Parser and probably the entire engine command classes.
200 DBI / Table API
202 This part should test if the API to DBI drivers work (maybe an
203 empty test driver will be needed for that).
204 Functionality
206 This part should test the functionality of the SQL::Parser and the
207 SQL::Statement engine.
208 Performance
210 This part should be used to implement full usage cases (ideally
211 from real world projects) to allow for testing optimizations.
212
214 Our priorities are localized to our current issues and proof of concept
215 fixes for upcoming SQL::Statement 2.xx.
216 Any additional priorities (as missing features, the SQL::Statement
218 rewrite) will come later and can be modified by (paying) users.
219
221 See <http://dbi.perl.org/contributing> for how you can help.
222 If your company has benefited from the DBI or SQL::Statement, please
224 consider if it could make a donation to The Perl Foundation "DBI
225 Development" or "SQL::Statement Development" fund at
226 <http://dbi.perl.org/donate> to secure future development.
227 Alternatively, if your company would benefit from a specific new DBI or
229 SQL::Statement feature, please consider sponsoring its development
230 through the options listed in the section "Commercial Support from the
231 Author" on <http://dbi.perl.org/support/>.
232 Using such targeted financing allows you to contribute to DBI
234 development (including SQL::Statement and PurePerl DBI drivers) and
235 rapidly get something specific and directly valuable to you in return.
236 Thank you.
238perl v5.32.0 2020-10-22 SQL::Statement::Roadmap(3)