{ "content": [ { "type": "text", "text": "# SQL::Statement::Roadmap (info)\n\n## NAME\n\nSQL::Statement::Roadmap - Planned Enhancements for SQL::Statement and SQL::Parser Jens Rehsack - June 2010\n\n## SYNOPSIS\n\nThis document gives a high level overview of the future of\nSQL::Statement, SQL::Parser and its impact.\nThe planned enhancements cover testing, performance, reliability,\nextensibility and more.\n\n## Sections\n\n- **NAME**\n- **SYNOPSIS**\n- **CHANGES AND ENHANCEMENTS**\n- **PRIORITIES**\n- **RESOURCES AND CONTRIBUTIONS**\n\nUse structuredContent.sections for detailed options, examples, and full documentation.\n" } ], "structuredContent": { "command": "SQL::Statement::Roadmap", "section": "", "mode": "info", "summary": "SQL::Statement::Roadmap - Planned Enhancements for SQL::Statement and SQL::Parser Jens Rehsack - June 2010", "synopsis": "This document gives a high level overview of the future of\nSQL::Statement, SQL::Parser and its impact.\nThe planned enhancements cover testing, performance, reliability,\nextensibility and more.", "tldr_summary": null, "tldr_examples": [], "tldr_source": null, "flags": [], "examples": [], "see_also": [], "section_outline": [ { "name": "NAME", "lines": 5, "subsections": [] }, { "name": "SYNOPSIS", "lines": 6, "subsections": [] }, { "name": "CHANGES AND ENHANCEMENTS", "lines": 194, "subsections": [] }, { "name": "PRIORITIES", "lines": 6, "subsections": [] }, { "name": "RESOURCES AND CONTRIBUTIONS", "lines": 19, "subsections": [] } ], "sections": { "NAME": { "content": "SQL::Statement::Roadmap - Planned Enhancements for SQL::Statement and\nSQL::Parser\n\nJens Rehsack - June 2010\n", "subsections": [] }, "SYNOPSIS": { "content": "This document gives a high level overview of the future of\nSQL::Statement, SQL::Parser and its impact.\n\nThe planned enhancements cover testing, performance, reliability,\nextensibility and more.\n", "subsections": [] }, "CHANGES AND ENHANCEMENTS": { "content": "Enhancements in SQL::Statement 1.xx\nSQL::Statement 1.xx will not receive big changes, but a few\nenhancements may help us to design SQL::Statement 2.xx much better.\n\nCREATE and DROP of FUNCTION, KEYWORD, OPERATOR, TYPE\n\nSQL::Statement is missing some functions, types, operators etc. It's\nsupported to add missing functionality - but the implementation was not\npicked up during the modernizing of column evaluation. See RT#52397 for\nsome more information.\n\nThis should be done before SQL::Statement 1.xx reaches the end of its\nroad.\n\nParser improvements\n\nThe SQL::Parser is implemented based on a lot of regular expressions\nand some manually developed logic. This creates some issues like\nRT#53416 or RT#55190. Further, trailing \";\" causes SQL::Parser to\ncroak. We need to decide what can be fixed without internal design\nchanges and what has to wait.\n\nPerformance\n\nThere is no intention to work on performance improvements in\nSQL::Statement 1.xx. The performance is good as it is and improvement\nrequires design changes.\n\nReliability\n\nBugs will be fixed - where possible. SQL::Statement 1.28 is much more\nreliable than SQL::Statement 1.15. Even if a bug cannot be fixed all\nissues are gratefully received as they will be considered in the design\nprocess for SQL::Statement 2.xx better.\n\nExtensibility\n\nSQL::Statement 1.xx is highly extensible, even if a more object\noriented design would improve that. The 1.xx branch will not be\nredesigned for greater extensibility on a coding level.\n\nEnhancements in SQL::Statement 2.xx\nConcerning the procedural design of SQL::Statement 1.xx a rewrite of\nthe basic components is required.\n\nSQL::Parser rewrite\n\nThe SQL::Parser needs to be modified to be able to use a Backus Naur\nForm . This would allow\nusers and developers to rely on many different SQL dialects. This will\nallow better extensibility from a feature point of view without losing\nANSI SQL compatibility.\n\nSQL::Statement rewrite\n\nSQL::Statement should be reduced to a simple coordinating engine. The\nexecuting tasks should be organized into separated commands. This will\nreduce side effects and will open the door for higher level\noptimizations, reliability improvements or sub-selects (or other\ncalculated tables).\n\nFeatures\n\nThere is a large list of missing features but not all table backends\nwill be able to support each new feature. The most popular requested\nfeatures need additional discussion and everyone is welcome to do it on\nthe mailto:dbi-dev@perl.org.\n\nLOCK TABLE\n\nLocking table within SQL scripts to manually control table consistence\nover several operations. The current locking support is restricted to\none statement.\n\nTransaction support\n\nExecuting statements on a temporary copy of the table data.\n\nThe easiest way to implement this would be to create a\nSQL::Statement::RAM on \"BEGIN TRANSACTION\" and write the entire table\nback on \"COMMIT\" or discard on \"ROLLBACK\".\n\nBetter performance could be achieved in cases where the implementation\nis enabled to memorize pending modifications and apply them at\n\"COMMIT\". On the other hand there are already capabilities to improve\nsome operations, which might create confusion in case of transactions.\n\nThis needs more discussion.\n\nALTER TABLE\n\nAdding, removing or modifying columns is not supported for created\ntables. A generic \"ALTER TABLE\" seems to rely on the implementation of\nthe transaction support - until better ideas are provided.\n\nIndices\n\nCurrently some table backends have implicit support to access specified\nrows quicker than fetching each row and evaluating the where clause\nagainst the row data.\n\nAn interface would be required to configure fetching to return only\nrows matching a restricted where clause. Another (probably better) way\nto support indices would be to fetch index entries at first and have an\ninterface to the table fetching lines based on an index key.\n\nSub-Selects\n\nIn most cases queries can be re-expressed without using sub-selects.\nBut in any case, there are circumstances where sub-selects are\nrequired.\n\nThe first implementation will do the sub-select before the primary\nstatement is executed without any further optimization. Hopefully a\nlater version will provide better Performance with some optimization.\n\nQuery based variables\n\nCurrently the only variable I can imagine is \"ROWNUM\". More suggestions\nare very welcome.\n\nBetter SQL Script support\n\nIn SQL::Statement 1.xx the function \"RUN ()\" provides SQL script\nexecution. This function may have limitations and side effects (at\nleast when the executed SQL touched the same tables as the primary\nstatement).\n\nI plan to improve the SQL script support to remove the side effects on\nthe one hand and have a more flexible and easier way to execute them.\n\nFinally it should be possible to execute a script via:\n\n$dbh->do( join( \";\", @script ) );\n\nTrigger support\n\nMost important when doing complicated things is having callback\nfunctions for several events. While real triggers will not be possible\nfor SQL::Statement and underlying pseudo-databases, callbacks could be\nprovided via triggers.\n\nPerformance\n\nThere are several performance optimizations required for SQL::Statement\n2.xx.\n\nThe first one should be done on a very high level (query optimization)\nby implementing algebraic evaluation of queries and clean\nimplementation of typical database algorithms. With respect to the\nbasic optimization rule premature optimization is the root of all evil,\nit is primarily targeted to have an adequately fast, reliable\nimplementation of many algorithms (e.g. early incomplete evaluation to\nreduce amount of rows, transpose where clause to evaluate constants\nfirst) and a clever controller choosing the right algorithm for a\nspecific query.\n\nThe second optimization goal means: implementing most expensive methods\nin XS. This requires a good performance test suite as well as some real\nworld usage cases.\n\nReliability\n\nThis is one of the primary goals of SQL::Statement. I hope to reach it\nusing test driven development and I hope I get some more todo's from\nthe users for this.\n\nExtensibility\n\nThe currently high level of extensibility should be increased on a\ncoding level. This will be done by redesigning the entire parser and\nexecution engine using object oriented techniques and design patterns.\n\nTesting\n\nMany tests in SQL::Statement are not well organized. The tests should\nbe reorganized into several parts:\n\nBasic API\nThis part should test the entire basic API of SQL::Statement,\nSQL::Parser and probably the entire engine command classes.\n\nDBI / Table API\nThis part should test if the API to DBI drivers work (maybe an\nempty test driver will be needed for that).\n\nFunctionality\nThis part should test the functionality of the SQL::Parser and the\nSQL::Statement engine.\n\nPerformance\nThis part should be used to implement full usage cases (ideally\nfrom real world projects) to allow for testing optimizations.\n", "subsections": [] }, "PRIORITIES": { "content": "Our priorities are localized to our current issues and proof of concept\nfixes for upcoming SQL::Statement 2.xx.\n\nAny additional priorities (as missing features, the SQL::Statement\nrewrite) will come later and can be modified by (paying) users.\n", "subsections": [] }, "RESOURCES AND CONTRIBUTIONS": { "content": "See for how you can help.\n\nIf your company has benefited from the DBI or SQL::Statement, please\nconsider if it could make a donation to The Perl Foundation \"DBI\nDevelopment\" or \"SQL::Statement Development\" fund at\n to secure future development.\n\nAlternatively, if your company would benefit from a specific new DBI or\nSQL::Statement feature, please consider sponsoring its development\nthrough the options listed in the section \"Commercial Support from the\nAuthor\" on .\n\nUsing such targeted financing allows you to contribute to DBI\ndevelopment (including SQL::Statement and PurePerl DBI drivers) and\nrapidly get something specific and directly valuable to you in return.\n\nThank you.\n\nperl v5.30.3 2020-10-23 SQL::Statement::Roadmap(3pm)", "subsections": [] } } } }