1SQL::Statement::RoadmapU(s3e)r Contributed Perl DocumentaStQiLo:n:Statement::Roadmap(3)
2
3
4

NAME

6       SQL::Statement::Roadmap - Planned Enhancements for SQL::Statement and
7       SQL::Parser
8
9       Jens Rehsack - June 2010
10

SYNOPSIS

12       This document gives a high level overview of the future of
13       SQL::Statement, SQL::Parser and its impact.
14
15       The planned enhancements cover testing, performance, reliability,
16       extensibility and more.
17

CHANGES AND ENHANCEMENTS

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
23       CREATE and DROP of FUNCTION, KEYWORD, OPERATOR, TYPE
24
25       SQL::Statement is missing some functions, types, operators etc. It's
26       supported to add missing functionality - but the implementation wasn't
27       picked up during the modernizing of column evaluation. See RT#52397 for
28       some more information.
29
30       This should be done before SQL::Statement 1.xx reaches the end of its
31       road.
32
33       Parser improvements
34
35       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
41       Performance
42
43       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
47       Reliability
48
49       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
54       Extensibility
55
56       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
60   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
64       SQL::Parser rewrite
65
66       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
72       SQL::Statement rewrite
73
74       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
80       Features
81
82       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
87       LOCK TABLE
88
89       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
93       Transaction support
94
95       Executing statements on a temporary copy of the table data.
96
97       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
101       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
106       This needs more discussion.
107
108       ALTER TABLE
109
110       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
114       Indices
115
116       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
120       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
125       Sub-Selects
126
127       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
131       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
135       Query based variables
136
137       Currently the only variable I can imagine is "ROWNUM". More suggestions
138       are very welcome.
139
140       Better SQL Script support
141
142       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
147       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
150       Finally it should be possible to execute a script via:
151
152           $dbh->do( join( ";", @script ) );
153
154       Trigger support
155
156       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
161       Performance
162
163       There are several performance optimizations required for SQL::Statement
164       2.xx.
165
166       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
176       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
180       Reliability
181
182       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
186       Extensibility
187
188       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
192       Testing
193
194       Many tests in SQL::Statement are not well organized. The tests should
195       be reorganized into several parts:
196
197       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
201       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
205       Functionality
206           This part should test the functionality of the SQL::Parser and the
207           SQL::Statement engine.
208
209       Performance
210           This part should be used to implement full usage cases (ideally
211           from real world projects) to allow for testing optimizations.
212

PRIORITIES

214       Our priorities are localized to our current issues and proof of concept
215       fixes for upcoming SQL::Statement 2.xx.
216
217       Any additional priorities (as missing features, the SQL::Statement
218       rewrite) will come later and can be modified by (paying) users.
219

RESOURCES AND CONTRIBUTIONS

221       See <http://dbi.perl.org/contributing> for how you can help.
222
223       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
228       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
233       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
237       Thank you.
238
239
240
241perl v5.30.0                      2019-07-26        SQL::Statement::Roadmap(3)
Impressum