DBIx::MyParse::Item - Accessing the items from a DBIx::MyParse::Query parse tree
DBIx::MyParse::Query
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" $first_item->getFieldName('another_field'); my $new_item_sql = $first_item->print();# Reconstructs the item as SQL my $new_query_sql = $query->print(); # Reconstructs entire query my $one = DBIx::MyParser->newInt(1); my $pi = DBIx::MyParse->newReal(3.14); my $sum = DBIx::MyParse->newPlus($one, $pi); my $sum_sql = $sum->print();
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.
Item objects can be constructed from scratch using new(). The arguments available to the constructor can be seen in the %args hash in Item.pm.
new()
%args
Item.pm
For any get function described below, a set() function is available to modify the object.
get
set()
You can call print() on an DBIx::MyParse::Item object to print an item. Passing 1 as an argument to print() will cause the getAlias(), if any, to be appended to the output with an AS SQL clause.
print()
DBIx::MyParse::Item
1
getAlias()
AS
The following convenience functions are available to create the simplest Item types: newNull(), newInt($integer), newString($string), newReal($number), newVarbin($data). Field items can be created usind newField($field_name, $table, $database).
newNull()
newInt($integer)
newString($string)
newReal($number)
newVarbin($data)
newField($field_name, $table, $database)
Additions, substractions can be created using newPlus($arg1, $arg2), newMinus($arg1, $arg2). ORs, ANDs and NOTs can be created using newAnd($arg1, $arg2), newOr($arg1, $arg2), and newNot($arg). Equations and inequalities can be created using newEq($arg1, $arg2), newGt($arg1, $arg2) and newLt($arg1, $arg2).
newPlus($arg1, $arg2)
newMinus($arg1, $arg2)
newAnd($arg1, $arg2)
newOr($arg1, $arg2)
newNot($arg)
newEq($arg1, $arg2)
newGt($arg1, $arg2)
newLt($arg1, $arg2)
getItemType()
This returns the type of the Item as a string, to facilitate dumping and debugging.
Item
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
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.
TABLE_ITEM
DATABASE_ITEM
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.
REF_ITEM
FIELD_ITEM
HAVING
VARBIN_ITEM
PARAM_ITEM
DECIMAL_ITEM
REAL_ITEM
3.14e1
INTERVAL_ITEM
CHARSET_ITEM
JOIN_ITEM
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.
'FUNC_ITEM' and 'SUM_FUNC_ITEM' denote functions in the parse tree.
'FUNC_ITEM'
'SUM_FUNC_ITEM'
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.
getType() eq 'FUNC_ITEM'
FUNC_ITEM
The possible values are again strings (see above) and are listed in sql/item_func.h under enum Functype
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:
getType() eq 'SUM_FUNC_ITEM'
enum Sumfunctype { COUNT_FUNC,COUNT_DISTINCT_FUNC,SUM_FUNC, SUM_DISTINCT_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(). This may include both general-purpose functions and user-defined ones.
UNKNOWN_FUNC
getFuncName()
Returns the name of the function called, such as "concat_ws", "md5", etc. If the 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.
"concat_ws"
"md5"
'+'
'||'
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.
hasArguments(), getFirstArg(), getSecondArg() and getThirdArg() are provided for convenience and to increase code readibility.
hasArguments()
getFirstArg()
getSecondArg()
getThirdArg()
Some functions are not entirely supported by DBIx::MyParse, e.g. some fancy arguments may be missing from the parse tree. Unfortunately, there is no way to know if you are missing any arguments. For a list of the currently problematic functions, see DBIx::MyParse.
The functions below are fully supported, however there are oddities you need to have in mind:
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'.
'cast_as_signed'
'cast_as_unsigned'
'cast_as_binary'
'cast_as_char'
'cast_as_date'
'cast_as_time'
'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.
CAST
length
For CAST(expr AS DECIMAL), getFuncName() will return 'decimal_typecast'.
CAST(expr AS DECIMAL)
'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.
'convert'
'CHARSET_ITEM'
getCharset()
DATE_ADD()
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.
'get_add_interval'
'get_sub_interval'
'INT_ITEM'
'STRING_ITEM'
'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'.
'INTERVAL_ITEM'
getInterval()
'INTERVAL_'
'INTERVAL_DAY'
'DAY'
ADDTIME()
SUBTIME()
getFuncName() will return 'add_time' and 'sub_time' respectively, that is, with an underscore between the two words.
'add_time'
'sub_time'
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
'case'
ELSE result2
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.
'case_switch'
value
pop
expr IS NULL
expr IS NOT NULL
getFuncType() will return either 'ISNULL_FUNC' or 'ISNOTNULL_FUNC'
'ISNULL_FUNC'
'ISNOTNULL_FUNC'
expr BETWEEN value AND value
expr NOT BETWEEN value AND value
getFuncType() will return 'BETWEEN'. getFuncName() will return 'BETWEEN' or 'NOT_BETWEEN', however the case of the letters in 'BETWEEN' can vary.
'BETWEEN'
'NOT_BETWEEN'
expr IN (list)
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.
'IN_FUNC'
'NOT_IN_FUNC'
list
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'.
'FT_FUNC'
expr
'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.
'LIKE_FUNC'
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 @)
'GUSERVAR_FUNC'
'USER_VAR_ITEM'
getVarName()
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.
'SUSERVAR_FUNC'
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.
'get_system_var'
'SYSTEM_VAR_ITEM'
getVarComponent()
# =not_all_func
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'.
'DECIMAL_ITEM'
'REAL_ITEM'
'VARBIN_ITEM'
getValue()
SELECT 0x4D7953514C
'MySQL'
You can also call 'getCharset()' to obtain the charset used for a particular string, if one was specified explicitly.
'getCharset()'
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.
GROUP BY
ORDER BY
"ASC"
"DESC"
getUseIndex()
getForceIndex()
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.
USE INDEX
FORCE INDEX
IGNORE INDEX
getItemType() will return 'JOIN_ITEM'. In DBIx::MyParse, joins are a separate object, even if it is not really so in the MySQL source. This way all JOINs are represented properly nested.
'JOIN_ITEM'
DBIx::MyParse
MySQL
getJoinItems()
Will return the two sides of the join. Each side may be a 'TABLE_ITEM', a <'SUBSELECT_ITEM'> or another 'JOIN_ITEM' so please be prepared to handle all.
'TABLE_ITEM'
getJoinCond()
Returns a reference to a an Item object containing the ON join condition
ON
getJoinFields()
Returns a reference to 'FIELD_ITEM' Items for each fields that appears in the USING clause.
USING
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'.
'INNER JOIN'
getItemType() will return 'SUBSELECT_ITEM
'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
FROM
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.
'ANY_SUBS'
'IN_SUBS'
'ALL_SUBS'
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. '>'. A single-character string value is returned, not a full Item object of type "COND_ITEM" .
'>'
"COND_ITEM"
getSubselectQuery()
Returns an DBIx::MyParse::Query object that contains the parse tree of the actual subselect itself.
10 POD Errors
The following errors were encountered while parsing the POD:
You forgot a '=back' before '=head1'
'=item' outside of any '=over'
You forgot a '=back' before '=head2'
To install DBIx::MyParse, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::MyParse
CPAN shell
perl -MCPAN -e shell install DBIx::MyParse
For more information on module installation, please visit the detailed CPAN module installation guide.