/ 
DBIx-MyParse-0.88
River stage one • 1 direct dependent • 1 total dependent
/ DBIx::MyParse::Query

NAME

DBIx::MyParse::Query - Access the parse tree produced by DBIx::MyParse

SYNOPSIS

        use DBIx::MyParse;
        my $parser = DBIx::MyParse->new();
        my $query = $parser->parse("INSERT INTO table VALUES (1)");
        print $query->getCommand();

	$query->setCommand("SQLCOM_REPLACE");	# Replace INSERT with SELECT
	$query->print();			# Print modified query as SQL

DESCRIPTION

This module attempts to provide structured access to the parse tree that is produced by MySQL's SQL parser. Since the parser itself is not exactly perfectly structured, please make sure you read this entire document before attempting to make sense of DBIx::MyParse::Query objects.

METHODS

getCommand()

Returns, as string, the name of SQL command that was parsed. All possible values can be found in enum enum_sql_command in sql/sql_lex.h from the MySQL source.

The commands that are currently supported (that is, a parse tree is created for them) are as follows:

"SQLCOM_SELECT",	"SQLCOM_DO"
"SQLCOM_INSERT",	"SQLCOM_INSERT_SELECT"
"SQLCOM_REPLACE",	"SQLCOM_REPLACE_SELECT"
"SQLCOM_UPDATE",	"SQLCOM_UPDATE_MULTI"
"SQLCOM_DELETE",	"SQLCOM_DELETE_MULTI"

"SQLCOM_BEGIN",		"SQLCOM_COMMIT",	"SQLCOM_ROLLBACK",
"SQLCOM_SAVEPOINT",	"SQLCOM_ROLLBACK_TO_SAVEPOINT", "SQLCOM_RELEASE_SAVEPOINT"

"SQLCOM_DROP_DB",	"SQLCOM_CREATE_DB",	"SQLCOM_DROP_TABLE",	"SQLCOM_RENAME_TABLE"

Please note that the returned value is a string, and not an integer. Please read the section COMMANDS below for notes on individual commands

getOrigCommand()

For DESCRIBE, SHOW TABLES, SHOW TABLE STATUS, SHOW DATABASES and SHOW FIELDS, the MySQL parser will rewrite the original query into a SELECT query. The original query type is preserved in getOrigCommand() and the possible values are as follows:

"SQLCOM_SHOW_FIELDS", "SQLCOM_SHOW_TABLES", "SQLCOM_SHOW_TABLE_STATUS", "SQLCOM_SHOW_DATABASES"

Please see the section "Information Schema Queries" below for more information.

getOptions()

Returns a reference to an array containing, as strings, the various options specified for the query, such as HIGH_PRIORITY, LOW_PRIORITY, DELAYED, IGNORE and the like. Some of the options are not returned with the names you expect, but rather using their internal MySQL names. Some options may be returned more than once.

SQL_NO_CACHE may be returned even if not explicitly present in the query, if the query contains uncacheable elements, eg NOW().

getOption($option_name)

Returns true if $option_name was specified for the query

ERROR HANDLING

If there has been a parse error, getCommand() eq "SQLCOM_ERROR". From there on, you can:

getError()

Returns the error code as string, in English, as defined in include/mysql_error.h.

getErrno()

Returns the error code as integer.

getErrstr()

Returns the entire error message, in the language of the MySQL installation. This is the same text the mysql client will print for an identical error.

getSQLState()

Returns a null-terminated string containing the SQLSTATE error code. The error code consists of five characters. "00000" means "no error". The values are specified by ANSI SQL and ODBC.

The parser is supposed to only report syntax errors with "ER_PARSE_ERROR", however due to the way MySQL works, this is not always the case. Sometimes the parser would do things during the actual parsing process that common sense would say should be done after. Therefore, expect other errors as well. For a list of possible values, please see:

http://dev.mysql.com/doc/refman/5.0/en/error-handling.html

COMMANDS

"SQLCOM_SELECT"

getSelectItems()

Returns a reference to the array of the items the SELECT query will return, each being a Item object.

Valid options are "SELECT_DISTINCT", "TL_READ_HIGH_PRIORITY", "SELECT_STRAIGHT_JOIN" "SELECT_SMALL_RESULT", "SELECT_BIG_RESULT", "OPTION_BUFFER_RESULT" "OPTION_FOUND_ROWS", "OPTION_TO_QUERY_CACHE" (force caching), "SQL_NO_CACHE", "TL_WRITE" (FOR UPDATE), "TL_READ_WITH_SHARED_LOCKS" (LOCK IN SHARE MODE), "WITH_ROLLUP", "WITH_CUBE", "SELECT_DESCRIBE", "DESCRIBE_NORMAL", "DESCRIBE_EXTENDED"

getTables()

Rreturns a reference to the array of tables specified in the query. Each table is also an Item object for which getType() eq "TABLE_ITEM" which contains information on the Join type, join conditions, indexes, etc. See DBIx::MyParse::Item for information on how to extract the individual properties.

getWhere()

Returns an Item object that is the root of the tree containing all the WHERE conditions.

getHaving()

Operates the same way as getWhere() but for the HAVING clause.

getGroup()

Returns a reference to an array containing one Item object for each GROUP BY condition.

getOrder()

Returns a reference to an array containing the individual Items from the ORDER BY clause.

<getLimit()

Returns a reference to a two-item array containing the two parts of the LIMIT clause as Item objects.

"SQLCOM_DO"

getSelectItems() will return the expressions being executed.

"SQLCOM_UPDATE" and "SQLCOM_UPDATE_MULTI"

my $array_ref = $query->getUpdateFields()

Returns a reference to an array containing the fields that the query would update.

my $array_ref = $query->getUpdateValues()

Returns a reference to an array containing the values that will be assigned to the fields being updated.

getTables(), getWhere(), getOrder() and getLimit() can also be used for update queries.

For "SQLCOM_UPDATE", getTables() will return a reference to a one-item array containg a TABLE_ITEM object describing the table being updated. For "SQLCOM_UPDATE_MULTI", the array can include several tables or JOIN_ITEMs.

"SQLCOM_DELETE" and "SQLCOM_DELETE_MULTI"

For a multiple-table delete, getCommand() eq "SQLCOM_DELETE"

getDeleteTables()

Will return a reference to an array contwaining the table(s) we are deleting records from.

getTables()

For a multiple-table delete, getTables() will return the tables listed in the FROM clause, which are used to provide referential integrity. Those may include JOIN_ITEMs.

getWhere(), getOrder() and getLimit() can also be used.

"SQLCOM_INSERT", "SQLCOM_INSERT_SELECT", "SQLCOM_REPLACE" and "SQLCOM_REPLACE_SELECT"

getInsertFields()

Returns a list of the fields you are inserting to.

getInsertValues()

For "SQLCOM_INSERT" and "SQLCOM_REPLACE", getInsertValues() will return a reference to an array, containing one sub-array for each row being inserted or replaced (even if there is only one row).

For "SQLCOM_INSERT_SELECT" and "SQLCOM_REPLACE_SELECT", getSelectItems(), getTables(), getWhere() and the other SELECT-related properties will describe the SELECT query used to provide values for the INSERT.

If ON DUPLICATE KEY UPDATE is also specified, then getUpdateFields() and getUpdateValues() will also be defined.

"SQLCOM_BEGIN"

The "WITH_CONSISTENT_SNAPSHOT" may be present

"SQLCOM_COMMIT" and "SQLCOM_ROLLBACK"

The "CHAIN", "NO_CHAIN", "RELEASE" and "NO_RELEASE" options may be present

"SQLCOM_SAVEPOINT", "SQLCOM_ROLLBACK_TO_SAVEPOINT" and "SQLCOM_RELEASE_SAVEPOINT"

getSavepoint()

Returns the name of the savepoint being referenced

"SQLCOM_LOCK_TABLES" and "SQLCOM_UNLOCK_TABLES"

You can use getTables() to get a list of the tables being locked. Calling getOptions() returns a list of lock types so that the first lock type in the list corresponds to the first table and so on in a one-to-one relationship.

"SQLCOM_DROP_TABLE", "SQLCOM_TRUNCATE" and "SQLCOM_RENAME_TABLE"

For "SQLCOM_DROP_TABLE" and "SQLOM_TRUNCATE", use getTables() to obtain a reference to an array of TABLE_ITEM objects for each table being dropped or truncated.

For "SQLCOM_RENAME_TABLE" use getTables() to obtain a reference to an array containing the tables being renamed. The first (index 0) and all even-numbered (2,3,4, etc.) items of the array will be the table names you are renaming FROM and the odd-numbered array items (1,2,3, etc.) will be the table names you are renaming TO. MySQL allows a one-at-a-time table rename between databases. In this case, getDatabaseName() on the TABLE_ITEM objects will return the names of the databases.

The following options may be present: "DROP_IF_EXISTS", "DROP_TEMPORARY", "DROP_RESTRICT" and "DROP_CASCADE".

Information Schema Queries

The following queries

"SQLCOM_SHOW_FIELDS", "SQLCOM_SHOW_TABLES",
"SQLCOM_SHOW_TABLE_STATUS", "SQLCOM_SHOW_DATABASES"

are rewritten internally by the MySQL parser into SELECT queries. To determine the original query, use getOrigCommand().

To determine the original table or database the query pertains to , use getSchemaSelect() which returns either a "DATABASE ITEM", "TABLE_ITEM" or a "FIELD_ITEM" object.

To determine the contents of any LIKE operator, use getWild() which will return a string.

If you are actually interested in what result columns are expected from you, you can use getSelectItems() as with any other query for C"<SQLCOM_SHOW_FIELDS>" and "SQLCOM_SHOW_TABLE_STATUS". "SQLCOM_SHOW_TABLES" and "SQLCOM_SHOW_DATABASES" require that you only return a single column with table/database names. The FULL attribute to "SHOW TABLES" is not supported at this time.

"SQLCOM_CHANGE_DB"

For "USE database", no such rewriting takes places, so getCommand() eq "SQLCOM_CHANGE_DB". However, the actual database being changed to is still found in the object that getSchemaSelect() returns.

Please see t/show.t for more examples on how to parse those queries.

"SQLCOM_DROP_DB" and "SQLCOM_CREATE_DB"

also uses getSchemaSelect(). A "DROP_IF_EXISTS" or "CREATE_IF_NOT_EXISTS" option may be present.

Dumping queries

print() can be used to convert the parse tree back into SQL. SELECT, INSERT, REPLACE, UPDATE and DELETE statements are supported. Please note that the returned string may be very different from the orginal query due to internal transformations that MySQL applies during parsing. Also, the print()-ed query may have extra AS clauses and an abundance of nested brackets.

isPrintable() can be used to test whether calling print() would be meaningful.

Modifying the parse tree

For every get method, there is a corresponding set method that updates the parse tree, e.g.

$query->setCommand("SQLCOM_UPDATE");

Also, any arrayrefs returned from get methods can be modified and (since references are used) the results will be reflected in the original object. If you do not want this to happen, you do need to dereference the arrayref and assign it to a new array, e.g.:

my $items = $query->getItems();
my @items_copy = @{$items};