EllisLab/CodeIgniter — pull request #868 by narfbg (2012-01-05 20:13:14+00:00)
Title: Numerous improvements to the Oracle (oci8) driver and DB_driver

Changes include:

- Method/property visibility declarations in `CI_DB_driver` and all of the oci8 driver components.
- `oci_execute()` related:
- Moved the call from `num_rows()` to `_data_seek()`, as it's purpose is to reset the internal pointer to zero. It's now only called by `CI_DB_oci8_driver::_execute()` (as it should). `_data_seek()` is left there for consistency and isn't called anywhere. This was the main reason for every performance problem with this driver.
- If we're using PHP version >= 5.3.2 - we'll now utilize the new `OCI_NO_AUTO_COMMIT` mode flag instead of `OCI_DEFAULT`, which is left as an alias.
- Now only pass the mode flag if we are in the middle of a transaction (see the notes about `OCI_NO_AUTO_COMMIT` in [php.net](http://www.php.net/manual/en/function.oci-execute.php) for details). `OCI_COMMIT_ON_SUCCESS` is the default, so it doesn't need to be explicitly set.
- `num_rows()` related:
- Now must be called explicitly (used to be automatically called by `CI_DB_driver`) to avoid `result_array` be set every time while it might not be needed (another possible performance glitch).
- The `num_rows` property is now set to `NULL` by default, so we can have a way to know if 0 is the actual count or we have to get it. Also put multiple hooks for this to be set by other methods. Of course - this still doesn't allow `num_rows()` to be used to check before actual data is fetched (as in other database drivers), because we still need to actually do a `count()` on the result set. I hope that Oracle will some day release a solution for this.
- `curs_id` is now passed to `CI_DB_oci8_result` - there are checks for it in at least 3 methods and yet until now it was always `NULL`.
- `free_result()` now really frees `stmt_id` and/or `curs_id`, if they are valid resources. Until now it did a check if `result_id` is a resource and try to clear it, but that's actually a boolean value.
- Added `custom_result_object()` and all of the row-fetching methods instead of using the parents from `CI_DB_result`. The new ones only fetch rows when needed, follow `current_row` on their own (we don't want to use `_data_seek()` and cast from/to arrays/objects when needed. This should also significantly improve performance for those who like to deal with single rows at a time.
- `CI_DB_oci8_forge`:
- Fixed a bug in `_create_table()` where it could fail if `AUTO_INCREMENT` is used as Oracle doesn't support it. There are sequences, that combined with triggers can achieve the same functionality, but that's way too complicated to be implemented with `CREATE TABLE` at once.
- Added `DROP TABLE` support.
- Added `_list_databases()` support in `CI_DB_oci8_utility`.
- Fixed issue #413, where `_error_message()` and `_error_number()` only returned connection-related errors.
- Fixed some typos and cleaned up some comments, spaces, `@access` lines, unneeded expressions, etc.

I know it's a rather large patch to review/merge, but in order to avoid calling `oci_execute()` multiple times - pretty much all of it should be done at once, so there's no way around it. Plus it should solve every oci8-related issue so far reported, although most of them are fixed long ago and are just waiting to be closed (e.g. #182, #682, #112).

I've tested most of the stuff (actually, all but DB forge and DB utility) - it works, so ... I hope it doesn't take too much time to review. Any suggestions are welcome. :)