NAME

DBIx::MyParse::Item - Accessing the items from a DBIx::MyParse::Query parse tree

SYNOPSIS

use DBIx::MyParse;
use DBIx::MyParse::Query;
use DBIx::MyParse::Item;

my $parser = DBIx::MyParse->new();
my $query = $parser->parse("SELECT field_name FROM table_name");
my $item_list = $query->getSelectItems();
my $first_item = $item_list->[0];
print $first_item->getItemType();	# Prints "FIELD_ITEM"
print $first_item->getFieldName()	# Prints "field_name"

DESCRIPTION

MySQL uses a few dozen Item objects to store the various nodes possible in a parse tree. For the sake of simplicity, we only use a single object type in Perl to represent the same information.

METHODS

getItemType()

This returns the type of the Item as a string, to facilitate dumping and debugging.

if ($item->getItemType() eq 'FIELD_ITEM') { ... }	# Correct
if ($item->getItemType() == FIELD_ITEM) { ... }	# Will not work

Some values are listed in enum Type in sql/item.h in the MySQL source.

enum Type {FIELD_ITEM, FUNC_ITEM, SUM_FUNC_ITEM, STRING_ITEM,
	INT_ITEM, REAL_ITEM, NULL_ITEM, VARBIN_ITEM,
	COPY_STR_ITEM, FIELD_AVG_ITEM, DEFAULT_VALUE_ITEM,
	PROC_ITEM,COND_ITEM, REF_ITEM, FIELD_STD_ITEM,
	FIELD_VARIANCE_ITEM, INSERT_VALUE_ITEM,
	SUBSELECT_ITEM, ROW_ITEM, CACHE_ITEM, TYPE_HOLDER,
	PARAM_ITEM
};

From those, the following are explicitly supported and are likely to occur during parsing:

'FIELD_ITEM',
'FUNC_ITEM', 'SUM_FUNC_ITEM',
'STRING_ITEM', 'INT_ITEM', 'DECIMAL_ITEM', 'NULL_ITEM', 'REAL_ITEM'
'REF_ITEM', 'COND_ITEM', 'PARAM_ITEM', 'VARBIN_ITEM', 'DEFAULT_VALUE_ITEM'
'ROW_ITEM'

In addition, DBIx::MyParse defines its own TABLE_ITEM in case a table, rather than a field, is being referenced. DATABASE_ITEM may also be returned.

REF_ITEM is a FIELD_ITEM that is used in a HAVING clause. VARBIN_ITEM is created when a Hex value is passed to MySQL (e.g. 0x5061756c). PARAM_ITEM is a ?-style placeholder. All decimal values are returned as DECIMAL_ITEM. REAL_ITEM is only returned if you use exponential notation (e.g. 3.14e1). INTERVAL_ITEM is returned as an argument to some date and time functions. CHARSET_ITEM is returned as an argument to some cast functions. JOIN_ITEM is returned for joins.

getAlias()

Returns the name of the Item if provided with an AS clause, such as SELECT field AS alias. If no AS clause is present, than (sort of) the SQL that produced the Item is returned. This is the same string that the mysql client would show as column headings if you execute the query manually.

FUNCTIONS

'FUNC_ITEM' and 'SUM_FUNC_ITEM' denote functions in the parse tree.

getFuncType()

if getType() eq 'FUNC_ITEM', you can call getFuncType() to determine what type of function it is. For MySQL, all operators are also of type FUNC_ITEM.

The possible values are again strings (see above) and are listed in sql/item_func.h under enum Functype

enum Functype {
	UNKNOWN_FUNC,EQ_FUNC,EQUAL_FUNC,NE_FUNC,LT_FUNC,LE_FUNC,
	GE_FUNC,GT_FUNC,FT_FUNC,
	LIKE_FUNC,NOTLIKE_FUNC,ISNULL_FUNC,ISNOTNULL_FUNC,
	COND_AND_FUNC, COND_OR_FUNC, COND_XOR_FUNC, BETWEEN, IN_FUNC,
	INTERVAL_FUNC, ISNOTNULLTEST_FUNC,
	SP_EQUALS_FUNC, SP_DISJOINT_FUNC,SP_INTERSECTS_FUNC,
	SP_TOUCHES_FUNC,SP_CROSSES_FUNC,SP_WITHIN_FUNC,
	SP_CONTAINS_FUNC,SP_OVERLAPS_FUNC,
	SP_STARTPOINT,SP_ENDPOINT,SP_EXTERIORRING,
	SP_POINTN,SP_GEOMETRYN,SP_INTERIORRINGN,
	NOT_FUNC, NOT_ALL_FUNC, NOW_FUNC, VAR_VALUE_FUNC
};

if getType() eq 'SUM_FUNC_ITEM', getFuncType() can be any of the aggregate functions listed in enum Sumfunctype in sql/item_sum.h:

enum Sumfunctype {
	COUNT_FUNC,COUNT_DISTINCT_FUNC,SUM_FUNC,AVG_FUNC,MIN_FUNC,
	MAX_FUNC,UNIQUE_USERS_FUNC,STD_FUNC,VARIANCE_FUNC,SUM_BIT_FUNC,
	UDF_SUM_FUNC,GROUP_CONCAT_FUNC
};

For MySQL, all functions not specifically listed above are UNKNOWN_FUNC and you must call getFuncName().

getFuncName()

Returns the name of the function called, such as "concat_ws", "md5", etc. If $item is not a function, but an operator, the symbol of the operator is returned, such as "+" or "||". The name of the function will be lowercase regardless of the orginal case in the SQL string.

getArguments()

Returns a reference to an array containing all the arguments to the function/operator. Each item from the array is an DBIx::MyParse::Item object, even if it is a simple string or a field name.

SPECIAL FUNCTIONS

CAST(expr AS type (length)), CONVERT(expr, type), SELECT BINARY expr

getFuncName() will return 'cast_as_signed', 'cast_as_unsigned', 'cast_as_binary', 'cast_as_char', 'cast_as_date', 'cast_as_time', or 'cast_as_datetime'.

The thing being CAST'ed will be returned as the first array item from getArguments(). If there is a length, it will be returned as the second argument.

For CAST(expr AS DECIMAL), getFuncName() will return 'decimal_typecast'.

CONVERT(expr USING charset)

getFuncName() will return 'convert'. The second item returned by getArguments() will be of type 'CHARSET_ITEM' and you can call getCharset() on it.

DATE_ADD() and DATE_SUB()

getFuncName() will return 'get_add_interval' and 'get_sub_interval' respectively. The second item returned by getArguments() will show the quantity of intervals that are to be added or substrated. This can be an 'INT_ITEM' for round interval and 'STRING_ITEM' for partial intervals, e.g. '5.55' MINUTE.

The last argument will be of type 'INTERVAL_ITEM' and you can call getInterval() on it to determine the actual interval being used. A string will be returned, as listed on the table in section 12.5 of the MySQL manual, except that all strings are returned prefixed with 'INTERVAL_' e.g. a day interval will be returned at 'INTERVAL_DAY' and not just DAY.

CASE WHEN condition THEN result1 ELSE result2 END

For this form of CASE, getFuncName() will return 'case'. If getArguments() returns an odd number of arguments, this means that an ELSE result2 clause is present, and it will be the last argument.

CASE value WHEN compare_value THEN result ELSE result2 END

For this form of CASE, getFuncName() will return 'case_switch'. If getArguments() returns an even number of arguments, this means that an ELSE result2 clause is present, and it will be the last argument. The value you are comparing against will be the last argument once you have pop-ed out the ELSE result2 clause, if present.

expr IS NULL and expr IS NOT NULL

getFuncType() will return either 'ISNULL_FUNC' or 'ISNOTNULL_FUNC'

expr BETWEEN value AND value and expr NOT BETWEEN value AND value

getFuncType() will return either 'BETWEEN' or 'NOT_BETWEEN'.

expr IN (list) and expr NOT IN (list)

getFuncType() will return either 'IN_FUNC' or 'NOT_IN_FUNC'. The first argument is the value you are examining, the rest are the values you are comparing against. If list contains just one value, MySQL will internally convert the entire expression to a simle equality or inequality.

MATCH(list) AGAINST (expr)

getFuncType() will return 'FT_FUNC'. The thing you are looking for, expr will be the first item from the argument list. The rest of the arguments will be of type 'FIELD_ITEM'.

expr LIKE expr ESCAPE string

getFuncType() will return 'LIKE_FUNC'. If an escape string is defined, it will appear as the third argument of the function.

SELECT @user_var

getFuncType() will return 'GUSERVAR_FUNC'. The first argument will be an Item of type 'USER_VAR_ITEM'. Call getVarName() on it to obtain the name of the user variable (without the leading @)

SELECT @user_var := value

getFuncType() will return 'SUSERVAR_FUNC'. The first argument will be of type 'USER_VAR_ITEM'. The second one will contain the value being assigned.

SELECT @@component.system_var

getFuncName() will return 'get_system_var'. The first argument will be of type 'SYSTEM_VAR_ITEM'. You can call getVarComponent() to obtain the component name and getVarName() to obtain the name of the variable. See section "5.2.4.1. Structured System Variables" in the MySQL manual.

# =not_all_func

LITERAL VALUES

For 'STRING_ITEM', 'INT_ITEM', 'DECIMAL_ITEM', 'REAL_ITEM' and 'VARBIN_ITEM' you can call getValue(). Please note that the value of 'VARBIN_ITEM' is returned in a binary form, not as an integer or a hex string. This is consistent with the behavoir of SELECT 0x4D7953514C, which returns 'MySQL'.

You can also call 'getCharset()' to obtain the charset used for a particular string, if one was specified explicitly.

FIELDS, TABLES and DATABASES

getDatabaseName()

if $item is FIELD_ITEM, REF_ITEM or a TABLE_ITEM, getDatabaseName() returns the database the field belongs to, if it was explicitly specified. If it was not specified explicitly, such as was given previously with a "USE DATABASE" command, getDatabaseName() will return undef. This may change in the future if we incorporate some more of MySQL's logic that resolves table names.

getTableName()

Returns the name of the table for a FIELD_ITEM or TABLE_ITEM object. For FIELD_ITEM, the table name must be explicitly specified with "table_name.field_name" notation. Otherwise returns undef and does not attempt to guess the name of the table.

getFieldName()

Returns the name of the field for a FIELD_ITEM object.

getDirection()

For an FIELD_ITEM used in GROUP BY or ORDER BY, the function will return either the string "ASC" or the string "DESC" depending on the group/ordering direction. Default is "ASC" and will be returned even if the query does not specify a direction explicitly.

getUseIndex(), getForceIndex() and getIgnoreIndex()

Returns a reference to an array containing one string for each index mentioned in the USE INDEX, FORCE INDEX or IGNORE INDEX clause for the table in question.

JOINS

getItemType() will return 'JOIN_ITEM'. In DBIx::MyParse, joins are a separate object, even if it is not really so in the MySQL source. The reason for that is that this way all nested joins work and all ambiguities are resolved.

getJoinItems()

Will return the two sides of the join. Each side may be either a 'TABLE_ITEM' or a <'SUBSELECT_ITEM'> so please be prepared to handle both.

getJoinCond()

Returns a reference to a an Item object containing the join condition

getJoinFields()

Returns a reference to 'FIELD_ITEM' Items for each fields that appears in the USING clause.

$item-getJoinType()>

Returns, as string, the type of join that will be used. Possible values are:

"JOIN_TYPE_LEFT"
"JOIN_TYPE_RIGHT"
"JOIN_TYPE_STRAIGHT"
"JOIN_TYPE_NATURAL"

If undef is returned, this means 'INNER JOIN'.

SUBQUERIES/SUBSELECTS

getItemType() will return 'SUBSELECT_ITEM

getSubselectType()

Returns one of the following, depending on the context where the subquery was seen:

"SINGLEROW_SUBS"
"IN_SUBS"
"EXISTS_SUBS"
"ANY_SUBS"
"ALL_SUBS"

If undef is returned, this means a subquery in the FROM clause, e.g. derived table

getSubselectExpr()

For subselect types 'ANY_SUBS', 'IN_SUBS' and 'ALL_SUBS', will return the Item that is being checked against the data returned by the subquery.

getSubselectCond()

For subselect types 'ANY_SUBS' and 'ALL_SUBS' will return the function used to match the expression against the data returned by the subquery, e.g. <''>>. Please note that a string value is returned, not a full Item object.

getSubselectQuery()

Returns an DBIx::MyParse::Query object that contains the parse tree of the actual subselect itself.