{ "content": [ { "type": "text", "text": "# SQL::Parser (perldoc)\n\n## NAME\n\nSQL::Parser -- validate and parse SQL strings\n\n## SYNOPSIS\n\nuse SQL::Parser; # CREATE A PARSER OBJECT\nmy $parser = SQL::Parser->new();\n$parser->feature( $class, $name, $value ); # SET OR FIND STATUS OF\nmy $hasfeature = $parser->feature( $class, $name ); # A PARSER FEATURE\n$parser->dialect( $dialectname ); # SET OR FIND STATUS OF\nmy $currentdialect = $parser->dialect; # A PARSER DIALECT\n\n## DESCRIPTION\n\nSQL::Parser is part of the SQL::Statement distribution and, most interaction with the parser\nshould be done through SQL::Statement. The methods shown above create and modify a parser\nobject. To use the parser object to parse SQL and to examine the resulting structure, you should\nuse SQL::Statement.\n\n## Sections\n\n- **NAME**\n- **SYNOPSIS**\n- **DESCRIPTION**\n- **METHODS**\n- **Supported SQL syntax**\n- **Subclassing SQL::Parser**\n- **The parse structure** (3 subsections)\n- **SUPPORT** (1 subsections)\n\nUse structuredContent.sections for detailed options, examples, and full documentation.\n" } ], "structuredContent": { "command": "SQL::Parser", "section": "", "mode": "perldoc", "summary": "SQL::Parser -- validate and parse SQL strings", "synopsis": "use SQL::Parser; # CREATE A PARSER OBJECT\nmy $parser = SQL::Parser->new();\n$parser->feature( $class, $name, $value ); # SET OR FIND STATUS OF\nmy $hasfeature = $parser->feature( $class, $name ); # A PARSER FEATURE\n$parser->dialect( $dialectname ); # SET OR FIND STATUS OF\nmy $currentdialect = $parser->dialect; # A PARSER DIALECT", "tldr_summary": null, "tldr_examples": [], "tldr_source": null, "flags": [], "examples": [], "see_also": [], "section_outline": [ { "name": "NAME", "lines": 2, "subsections": [] }, { "name": "SYNOPSIS", "lines": 9, "subsections": [] }, { "name": "DESCRIPTION", "lines": 11, "subsections": [] }, { "name": "METHODS", "lines": 83, "subsections": [] }, { "name": "Supported SQL syntax", "lines": 9, "subsections": [] }, { "name": "Subclassing SQL::Parser", "lines": 15, "subsections": [] }, { "name": "The parse structure", "lines": 10, "subsections": [ { "name": "parse", "lines": 24 }, { "name": "parse", "lines": 38 }, { "name": "structure", "lines": 70 } ] }, { "name": "SUPPORT", "lines": 41, "subsections": [ { "name": "Business support and maintenance", "lines": 18 } ] } ], "sections": { "NAME": { "content": "SQL::Parser -- validate and parse SQL strings\n", "subsections": [] }, "SYNOPSIS": { "content": "use SQL::Parser; # CREATE A PARSER OBJECT\nmy $parser = SQL::Parser->new();\n\n$parser->feature( $class, $name, $value ); # SET OR FIND STATUS OF\nmy $hasfeature = $parser->feature( $class, $name ); # A PARSER FEATURE\n\n$parser->dialect( $dialectname ); # SET OR FIND STATUS OF\nmy $currentdialect = $parser->dialect; # A PARSER DIALECT\n", "subsections": [] }, "DESCRIPTION": { "content": "SQL::Parser is part of the SQL::Statement distribution and, most interaction with the parser\nshould be done through SQL::Statement. The methods shown above create and modify a parser\nobject. To use the parser object to parse SQL and to examine the resulting structure, you should\nuse SQL::Statement.\n\nImportant Note: Previously SQL::Parser had its own hash-based interface for parsing, but that is\nnow deprecated and will eventually be phased out in favor of the object-oriented parsing\ninterface of SQL::Statement. If you are unable to transition some features to the new interface\nor have concerns about the phase out, please contact me. See \"The Parse Structure\" for details\nof the now-deprecated hash method if you still need them.\n", "subsections": [] }, "METHODS": { "content": "new()\nCreate a new parser object\n\nuse SQL::Parser;\nmy $parser = SQL::Parser->new();\n\nThe new() method creates a SQL::Parser object which can then be used to parse and validate the\nsyntax of SQL strings. It takes two optional parameters - 1) the name of the SQL dialect that\nwill define the syntax rules for the parser and 2) a reference to a hash which can contain\nadditional attributes of the parser. If no dialect is specified, 'AnyData' is the default.\n\nuse SQL::Parser;\nmy $parser = SQL::Parser->new( $dialectname, \\%attrs );\n\nThe dialectname parameter is a string containing any valid dialect such as 'ANSI', 'AnyData',\nor 'CSV'. See the section on the dialect() method below for details.\n\nThe \"attrs\" parameter is a reference to a hash that can contain error settings for the\nPrintError and RaiseError attributes.\n\nAn example:\n\nuse SQL::Parser;\nmy $parser = SQL::Parser->new('AnyData', {RaiseError=>1} );\n\nThis creates a new parser that uses the grammar rules\ncontained in the .../SQL/Dialects/AnyData.pm file and which\nsets the RaiseError attribute to true.\n\ndialect()\n$parser->dialect( $dialectname ); # load a dialect configuration file\nmy $dialect = $parser->dialect; # get the name of the current dialect\n\nFor example:\n\n$parser->dialect('AnyData'); # loads the AnyData config file\nprint $parser->dialect; # prints 'AnyData'\n\nThe $dialectname parameter may be the name of any dialect configuration file on your system.\nUse the $parser->list('dialects') method to see a list of available dialects. At a minimum it\nwill include \"ANSI\", \"CSV\", and \"AnyData\". For backwards compatibility 'Ansi' is accepted as a\nsynonym for 'ANSI', otherwise the names are case sensitive.\n\nLoading a new dialect configuration file erases all current parser features and resets them to\nthose defined in the configuration file.\n\nfeature()\nFeatures define the rules to be used by a specific parser instance. They are divided into the\nfollowing classes:\n\n* validcommands\n* validoptions\n* validcomparisonoperators\n* validdatatypes\n* reservedwords\n\nWithin each class a feature name is either enabled or disabled. For example, under\n\"validdatatypes\" the name \"BLOB\" may be either disabled or enabled. If it is not enabled\n(either by being specifically disabled, or simply by not being specified at all) then any SQL\nstring using \"BLOB\" as a data type will throw a syntax error \"Invalid data type: 'BLOB'\".\n\nThe feature() method allows you to enable, disable, or check the status of any feature.\n\n$parser->feature( $class, $name, 1 ); # enable a feature\n\n$parser->feature( $class, $name, 0 ); # disable a feature\n\nmy $feature = $parser->feature( $class, $name ); # return status of a feature\n\nFor example:\n\n$parser->feature('reservedwords','FOO',1); # make 'FOO' a reserved word\n\n$parser->feature('validdatatypes','BLOB',0); # disallow 'BLOB' as a\n# data type\n\n# determine if the LIKE\n# operator is supported\nmy $LIKE = $parser->feature('validcomparisonoperators','LIKE');\n\nSee the section below on \"Backwards Compatibility\" for use of the feature() method with\nSQL::Statement 0.1x style parameters.\n", "subsections": [] }, "Supported SQL syntax": { "content": "The SQL::Statement distribution can be used to either just parse SQL statements or to execute\nthem against actual data. A broader set of syntax is supported in the parser than in the\nexecutor. For example the parser allows you to specify column constraints like PRIMARY KEY.\nCurrently, these are ignored by the execution engine. Likewise syntax such as RESTRICT and\nCASCADE on DROP statements or LOCAL GLOBAL TEMPORARY tables in CREATE are supported by the\nparser but ignored by the executor.\n\nTo see the list of Supported SQL syntax formerly kept in this pod, see SQL::Statement.\n", "subsections": [] }, "Subclassing SQL::Parser": { "content": "In the event you need to either extend or modify SQL::Parser's default behavior, the following\nmethods may be overridden:\n\n\"$self-\">\"getbtwn($string)\"\nProcesses the BETWEEN...AND... predicates; default converts to 2 range predicates.\n\n\"$self-\">\"getin($string)\"\nProcess the IN (...list...) predicates; default converts to a series of OR'd '=' predicate,\nor AND'd '<>' predicates for NOT IN.\n\n\"$self-\">\"transformsyntax($string)\"\nAbstract method; default simply returns the original string. Called after replbtwn() and\nreplin(), but before any further predicate processing is applied. Possible uses include\nconverting other predicate syntax not recognized by SQL::Parser into user-defined functions.\n", "subsections": [] }, "The parse structure": { "content": "This section outlines the now-deprecated hash interface to the parsed structure. It is included\nfor backwards compatibility only. You should use the SQL::Statement object interface to the\nstructure instead. See SQL::Statement.\n\nParse Structures\n\nHere are some further examples of the data structures returned by the structure() method after a\ncall to parse(). Only specific details are shown for each SQL instance, not the entire\nstructure.\n", "subsections": [ { "name": "parse", "content": "Once a SQL::Parser object has been created with the new() method, the parse() method can be used\nto parse any number of SQL strings. It takes a single required parameter -- a string containing\na SQL command. The SQL string may optionally be terminated by a semicolon. The parse() method\nreturns a true value if the parse is successful and a false value if the parse finds SQL syntax\nerrors.\n\nExamples:\n\n1) my $success = $parser->parse('SELECT * FROM foo');\n\n2) my $sql = 'SELECT * FROM foo';\nmy $success = $parser->parse( $sql );\n\n3) my $success = $parser->parse(qq!\nSELECT id,phrase\nFROM foo\nWHERE id < 7\nAND phrase <> 'bar'\nORDER BY phrase;\n!);\n\n4) my $success = $parser->parse('SELECT * FRoOM foo ');\n\nIn examples #1,#2, and #3, the value of $success will be true because the strings passed to the" }, { "name": "parse", "content": "In example #4, however, the value of $success will be false because the string contains a SQL\nsyntax error ('FRoOM' instead of 'FROM').\n\nIn addition to checking the return value of parse() with a variable like $success, you may use\nthe PrintError and RaiseError attributes as you would in a DBI script:\n\n* If PrintError is true, then SQL syntax errors will be sent as\nwarnings to STDERR (i.e. to the screen or to a file if STDERR\nhas been redirected). This is set to true by default which\nmeans that unless you specifically turn it off, all errors\nwill be reported.\n\n* If RaiseError is true, then SQL syntax errors will cause the\nscript to die, (i.e. the script will terminate unless wrapped\nin an eval). This is set to false by default which means\nthat unless you specifically turn it on, scripts will\ncontinue to operate even if there are SQL syntax errors.\n\nBasically, you should leave PrintError on or else you will not be warned when an error occurs.\nIf you are simply validating a series of strings, you will want to leave RaiseError off so that\nthe script can check all strings regardless of whether some of them contain SQL errors. However,\nif you are going to try to execute the SQL or need to depend that it is correct, you should set\nRaiseError on so that the program will only continue to operate if all SQL strings use correct\nsyntax.\n\nIMPORTANT NOTE #1: The parse() method only checks syntax, it does NOT verify if the objects\nlisted actually exist. For example, given the string \"SELECT model FROM cars\", the parse()\nmethod will report that the string contains valid SQL but that will not tell you whether there\nactually is a table called \"cars\" or whether that table contains a column called 'model'. Those\nkinds of verifications are performed by the SQL::Statement module, not by SQL::Parser by itself.\n\nIMPORTANT NOTE #2: The parse() method uses rules as defined by the selected dialect\nconfiguration file and the feature() method. This means that a statement that is valid in one\ndialect may not be valid in another. For example the 'CSV' and 'AnyData' dialects define 'BLOB'\nas a valid data type but the 'ANSI' dialect does not. Therefore the statement 'CREATE TABLE foo\n(picture BLOB)' would be valid in the first two dialects but would produce a syntax error in the\n'ANSI' dialect.\n" }, { "name": "structure", "content": "After a SQL::Parser object has been created and the parse() method used to parse a SQL string,\nthe structure() method returns the data structure of that string. This data structure may be\npassed on to other modules (e.g. SQL::Statement) or it may be printed out using, for example,\nthe Data::Dumper module.\n\nThe data structure contains all of the information in the SQL string as parsed into its various\ncomponents. To take a simple example:\n\n$parser->parse('SELECT make,model FROM cars');\nuse Data::Dumper;\nprint Dumper $parser->structure;\n\nWould produce:\n\n$VAR1 = {\n'columndefs' => [\n{ 'type' => 'column',\n'value' => 'make', },\n{ 'type' => 'column',\n'value' => 'model', },\n],\n'command' => 'SELECT',\n'tablenames' => [\n'cars'\n]\n};\n\n\n'SELECT make,model, FROM cars'\n\ncommand => 'SELECT',\ntablenames => [ 'cars' ],\ncolumnnames => [ 'make', 'model' ],\n\n'CREATE TABLE cars ( id INTEGER, model VARCHAR(40) )'\n\ncolumndefs => {\nid => { datatype => INTEGER },\nmodel => { datatype => VARCHAR(40) },\n},\n\n'SELECT DISTINCT make FROM cars'\n\nsetquantifier => 'DISTINCT',\n\n'SELECT MAX (model) FROM cars'\n\nsetfunction => {\nname => 'MAX',\narg => 'models',\n},\n\n'SELECT * FROM cars LIMIT 5,10'\n\nlimitclause => {\noffset => 5,\nlimit => 10,\n},\n\n'SELECT * FROM vars ORDER BY make, model DESC'\n\nsortspeclist => [\n{ make => 'ASC' },\n{ model => 'DESC' },\n],\n\n\"INSERT INTO cars VALUES ( 7, 'Chevy', 'Impala' )\"\n\nvalues => [ 7, 'Chevy', 'Impala' ],\n" } ] }, "SUPPORT": { "content": "You can find documentation for this module with the perldoc command.\n\nperldoc SQL::Parser\nperldoc SQL::Statement\n\nYou can also look for information at:\n\n* RT: CPAN's request tracker\n\n\n\n* AnnoCPAN: Annotated CPAN documentation\n\n\n\n* CPAN Ratings\n\n\n\n* Search CPAN\n\n\n\nWhere can I go for help?\nFor questions about installation or usage, please ask on the dbi-users@perl.org mailing list or\npost a question on PerlMonks (, where Jeff is known as jZed). Jens\ndoes not visit PerlMonks on a regular basis.\n\nIf you have a bug report, a patch or a suggestion, please open a new report ticket at CPAN (but\nplease check previous reports first in case your issue has already been addressed). You can mail\nany of the module maintainers, but you are more assured of an answer by posting to the dbi-users\nlist or reporting the issue in RT.\n\nReport tickets should contain a detailed description of the bug or enhancement request and at\nleast an easily verifiable way of reproducing the issue or fix. Patches are always welcome, too.\n\nWhere can I go for help with a concrete version?\nBugs and feature requests are accepted against the latest version only. To get patches for\nearlier versions, you need to get an agreement with a developer of your choice - who may or not\nreport the the issue and a suggested fix upstream (depends on the license you have chosen).\n", "subsections": [ { "name": "Business support and maintenance", "content": "For business support you can contact Jens via his CPAN email address rehsackATcpan.org. Please\nkeep in mind that business support is neither available for free nor are you eligible to receive\nany support based on the license distributed with this package.\n\nAUTHOR & COPYRIGHT\nThis module is\n\ncopyright (c) 2001,2005 by Jeff Zucker and\ncopyright (c) 2007-2020 by Jens Rehsack.\n\nAll rights reserved.\n\nThe module may be freely distributed under the same terms as Perl itself using either the \"GPL\nLicense\" or the \"Artistic License\" as specified in the Perl README file.\n\nJeff can be reached at: jzuckerATcpan.org Jens can be reached at: rehsackATcpan.org or via\ndbi-devATperl.org\n" } ] } } } }