view release on metacpan or  search on metacpan

lib/DBIx/MyParse/Query.pm  view on Meta::CPAN

=head2 C<"SQLCOM_DELETE"> and C<"SQLCOM_DELETE_MULTI">

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

=over

=item C<getDeleteTables()>

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

=item C<getTables()>

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

=back

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

=head2 C<"SQLCOM_INSERT">, C<"SQLCOM_INSERT_SELECT">, C<"SQLCOM_REPLACE"> and C<"SQLCOM_REPLACE_SELECT">

=over

=item C<getInsertFields()>

Returns a list of the fields you are inserting to.

=item C<getInsertValues()> 

For C<"SQLCOM_INSERT"> and C<"SQLCOM_REPLACE">, C<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).

=back

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

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

=head2 C<"SQLCOM_BEGIN">

The C<"WITH_CONSISTENT_SNAPSHOT"> may be present

=head2 C<"SQLCOM_COMMIT"> and C<"SQLCOM_ROLLBACK">

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

=head2 C<"SQLCOM_SAVEPOINT">, C<"SQLCOM_ROLLBACK_TO_SAVEPOINT"> and C<"SQLCOM_RELEASE_SAVEPOINT">

=over

=item C<getSavepoint()>

Returns the name of the savepoint being referenced

=back

=head2 C<"SQLCOM_LOCK_TABLES"> and C<"SQLCOM_UNLOCK_TABLES">

You can use C<getTables()> to get a list of the tables being locked. Calling C<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.

=head2 C<"SQLCOM_DROP_TABLE">, C<"SQLCOM_TRUNCATE"> and C<"SQLCOM_RENAME_TABLE">

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

For C<"SQLCOM_RENAME_TABLE"> use C<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, C<getDatabaseName()> on the C<TABLE_ITEM> objects will return the names of
the databases.

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

=head1 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 C<getOrigCommand()>.

To determine the original table or database the query pertains to , use C<getSchemaSelect()> which
returns either a L<"DATABASE ITEM"|DBIx::MyParse::Item>, L<"TABLE_ITEM"|DBIx::MyParse::Item> or a L<"FIELD_ITEM"|DBIx::MyParse::Item> object.

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

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

	"SQLCOM_CHANGE_DB"

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

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

	"SQLCOM_DROP_DB" and "SQLCOM_CREATE_DB"

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

=head1 Dumping queries

C<print()> can be used to convert the parse tree back into SQL. C<SELECT>, C<INSERT>, C<REPLACE>, C<UPDATE> and C<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 C<print()>-ed query may have extra C<AS> clauses and an
abundance of nested brackets.

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

=head1 Modifying the parse tree

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

	$query->setCommand("SQLCOM_UPDATE");