Issues

ZF-1694: Class to execute a SQL script

Description

This is a feature request for a class or method that executes a SQL script.

The class should recognize the syntax supported by SQL scripts of each respective RDBMS brand, if possible.

This should be written as a proposal using our development process.

Comments

Resolving as "Won't Fix," pending reasonable justification for reopening.

Reopening with target release set to none until we get more feedback on this feature. If it is determined that we will not implement this feature in any future release, it may be closed with 'Won't Fix'.

Please categorize/fix as needed.

This doesn't appear to have been fixed in 1.5.0. Please update if this is not correct.

Ralph, please evaluate and decide if/how we should act on this.

This is difficult, def. not something that we could handle as a feature via Issue tracker.

At current, the availability of multiple statements per query in any of the php db extensions is varied across the board. Even in PDO, there are different procedures on how to execute multiple statements with a single api call (whether to execute or not).

This means that each query in a single script would need to be parsed out. That being the case, one would have to have an SQL Tokenizer or Parser to identify when a query stops (assuming the user didnt change the delimiter).

That said, in order to do those tasks, a major component would need to be proposed and developed, and IMO, its gonna be a large undertaking.

So, that said, should i close this for now?

I logged this issue to keep note of a users' feature request a long time ago. But I wouldn't recommend doing it within the scope of the ZF project. It'd be a major project in itself.

Imagine that you need to accommodate script behavior including the following:

  • Duplicating SQL script builtin commands, such as {{USE}} and {{`CONNECT}} and {{CHARSET}} and {{DELIMITER}}. You shouldn't execute these against the database server.
  • SQL statements that can't be prepared statements, like {{START TRANSACTION}}. This set of statements may vary by version of the database.
  • SQL statements that contain semicolon, either inside string literals or in the body of a trigger or stored procedure.
  • SQL statements that are terminated by newline instead of semicolon, such as {{DELIMITER}}.

...and that's just for MySQL. Imagine implementing and testing a whole different set of syntax and rules like the above for Oracle, PostgreSQL, and all other databases supported by Zend_Db.