Database I/O Manager (DbIo) Plugin [Support Thread]

The Database I/O Manager (DbIO) enables you to input (or import) and output (or export) information to and from your database using a .csv file as the "transport method".

It seems like I spent a significant portion of time this year providing clients with "import/export" scripts and DbIo is the culmination of my effort to create an extendable I/O script. Yes, there are other I/O scripts (like EZ-Populate and Apsona) but I wanted a script that holds to these principles:

1. Use Zen Cart's built-in processing, especially the database interfaces.
2. Use a class-based approach, enabling a higher degree of code reuse.
3. Let the database drive the data; use the database settings to guide "proper" input processing.
4. Keep the data-pool consistent; broadly define the character-encoding of the .CSV repositories.
5. Keep to, where possible, the EP-4 interface described in its wiki.
6. Multi-language awareness.
7. Be data-driven. If you've installed a plugin that simply adds a field to an existing table (like products or orders), that field is a candidate for any I/O operation on that database table without additional coding.

Like any process that changes information in your database: Make a backup of your database prior to performing a "full-import" (which actually changes your database)!

Check out the readme for operational details, but my goal is to make the on-screen instructions "good enough" for most users.

I'll post the plugin's download link once I receive it.

Once reviewed, the plugin can be downloaded from https://www.zen-cart.com/downloads.php?do=file&id=2091.

Sounds great. In EP it will list all products in every instance of it when it's been linked to multiple categories which can be inconvenient when making changes. Will this only list the product in the master category or have a setting for that?

lankeeyankee:

Sounds great. In EP it will list all products in every instance of it when it's been linked to multiple categories which can be inconvenient when making changes. Will this only list the product in the master category or have a setting for that?

For a products' import/export, each product is listed one time ... and associated to its master-category-id by a caret (^) separated list of category names (specified in the site's default language).

I'm excited the work might be able to help me with this.
https://www.zen-cart.com/showthread.php?190417-EasyPopulate-4-0-Support-Thread&p=1312654#post1312654

Zean:

I'm excited the work might be able to help me with this.
https://www.zen-cart.com/showthread.php?190417-EasyPopulate-4-0-Support-Thread&p=1312654#post1312654

You've got three (3) requests in that post:

1. Include, and process, the categories' sort-order in a products' export. DbIo doesn't export that value either for its products' report (it would actually be a set of values, right, since each category has its own sort-order). What bearing does a categories' sort-order have on a product's definition? I can see including the categories' sort-order with a categories I/O handler, but for products?
2. Include sort-orders in the basic attributes' export. DbIo's "basic" products-attributes I/O report does not include those fields, either. For this report, the idea is to keep the processed fields to a minimum.
3. Include sort-orders in the detailed attributes' export. DbIo, like EP-4, includes only the attribute-specific sort order (the v_products_options_sort_order) since that report is dealing with the products_attributes table. The option-specific sort order is included in the ProductsOptions handler's processing and the option-value-specific sort order is included in the ProductsOptionsValues handler.

lat9:

Once reviewed, the plugin can be downloaded from https://www.zen-cart.com/downloads.php?do=file&id=2091.

v1.0.0 is now available for download.

Not sure which way to go to correct my issue. I have this error when I navigate to the plug in.

The DbIo Manager does not support your installation, due to a mismatch between your DB_CHARSET (utf-8) and CHARSET (utf-8) value.

What would be the best way to resolve my mismatch issue to allow me to use the plugin?

GearStudios:

Not sure which way to go to correct my issue. I have this error when I navigate to the plug in. [QUOTE]The DbIo Manager does not support your installation, due to a mismatch between your DB_CHARSET (utf-8) and CHARSET (utf-8) value.

What would be the best way to resolve my mismatch issue to allow me to use the plugin?[/QUOTE]
You can correct the setting (in your configure.php files) for DB_CHARSET to read

define ('DB_CHARSET', 'utf8');

instead of

define ('DB_CHARSET', 'utf-8');

There's no intervening minus-sign (-) in that definition.

That's awesome! Fixed the issue! Thank you! +10

Great plugin however when I exported my products it added the source code of my Admin page at the end! Weird ...

icecold:

Great plugin however when I exported my products it added the source code of my Admin page at the end! Weird ...

Not weird, but a bug. If you edit your copy of /YOUR_ADMIN/dbio_manager.php, starting at line 224, find:

                        case 'download':
                            $fp = fopen ($action_filename, 'r');
                            if ($fp === false) {
                                $_SESSION['dbio_message'] = array ( 'error', sprintf (DBIO_CANT_OPEN_FILE, $action_filename) );
                            } else {
                                if (strpos ($_SERVER['HTTP_USER_AGENT'], 'MSIE') !== false) {
                                    header('Content-Type: "application/octet-stream"');
                                    header('Content-Disposition: attachment; filename="' . $dbio_files[$_POST['filename_hash']]['filename_only'] . '"');
                                    header('Expires: 0');
                                    header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
                                    header("Content-Transfer-Encoding: binary");
                                    header('Pragma: public');
                                    header("Content-Length: " . $dbio_files[$_POST['filename_hash']]['bytes']);
                                } else {
                                    header('Content-Type: "application/octet-stream"');
                                    header('Content-Disposition: attachment; filename="' . $dbio_files[$_POST['filename_hash']]['filename_only'] . '"');
                                    header("Content-Transfer-Encoding: binary");
                                    header('Expires: 0');
                                    header('Pragma: no-cache');
                                    header("Content-Length: " . $dbio_files[$_POST['filename_hash']]['bytes']);
                                }
                                fpassthru ($fp);
                                fclose ($fp);
                            }
                            break;

and change to:

                        case 'download':
                            $fp = fopen ($action_filename, 'r');
                            if ($fp === false) {
                                $_SESSION['dbio_message'] = array ( 'error', sprintf (DBIO_CANT_OPEN_FILE, $action_filename) );
                            } else {
                                if (strpos ($_SERVER['HTTP_USER_AGENT'], 'MSIE') !== false) {
                                    header('Content-Type: "application/octet-stream"');
                                    header('Content-Disposition: attachment; filename="' . $dbio_files[$_POST['filename_hash']]['filename_only'] . '"');
                                    header('Expires: 0');
                                    header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
                                    header("Content-Transfer-Encoding: binary");
                                    header('Pragma: public');
                                    header("Content-Length: " . $dbio_files[$_POST['filename_hash']]['bytes']);
                                } else {
                                    header('Content-Type: "application/octet-stream"');
                                    header('Content-Disposition: attachment; filename="' . $dbio_files[$_POST['filename_hash']]['filename_only'] . '"');
                                    header("Content-Transfer-Encoding: binary");
                                    header('Expires: 0');
                                    header('Pragma: no-cache');
                                    header("Content-Length: " . $dbio_files[$_POST['filename_hash']]['bytes']);
                                }
                                fpassthru ($fp);
                                fclose ($fp);
                                exit ();
                            }
                            break;

I'll get this queued for the next version's release.

I've just submitted v1.0.1 to the Plugins for review, containing the following changes (identified by their GitHub issue number):

#69: Enable a handler to perform some post-processing for the record-set just imported.
#63: Clarify some of the handlers' instructions.
#68: Missing function importGetFieldValue.
#67: Unwanted HTML included in CSV-download in some configurations.
#60: Missing "Customers" handler's language file.
#62: Only default language exported by "Products" handler on multi-lingual stores.
#64: v_categories_name field not properly encoded by the "Products" export.
#65: Empty dates should be allowed when a date/datetime field is nullable.
#53: Allow a handler to be conditionally enabled.

v1.0.1 is now available for download from the plugins.

I've just submitted v1.1.0 to the Plugins for review. This version of DbIo adds the capability to REMOVE a product's database record and to use either the product's ID or its model-number as a "key" on import.

Changes for the following issues (identified by GitHub issue numbers) are included:

#7: Enable handlers to support "commands" (like REMOVE); the "Products" import now recognizes the REMOVE command.
#70: Fixed-header import misses "special import" fields.
#71: An incomplete import record was not being flagged.
#72: Don't post-process imports that have previously failed.
#73: Enable a handler to specify an alternate key; supports the "Products" import to key to either the product's ID or model-number.
#74: Enable a handler to specify a minimum base-class version.
#75: Don't gather export filters until needed; a performance enhancement for store's with a large number of categories and/or manufacturers.
#78: Correct PHP Notice issues.

What a great module. Very well made. I am having difficulties doing the 'ordersproducts' as I get the blank screen with generic error message (WARNING: An Error occurred.........). Unfotunately nothing is in log files. I do have modified products table, namely extra fields. Perhaps it is. Orders export runs perfect, so the customer details are exporting okay.

@HeathenMagic, I don't understand why no log was generated for that error. That said, you could try enabling the DbIo debug, rerunning that OrdersProducts report and then sending me (it'll be a big file) the resultant log (it'll be the newest file with a name similar to /YOUR_ADMIN/dbio/logs/dbio-OrdersProducts-*.log).

lat9:

I've just submitted v1.1.0 to the Plugins for review. This version of DbIo adds the capability to REMOVE a product's database record and to use either the product's ID or its model-number as a "key" on import.

Changes for the following issues (identified by GitHub issue numbers) are included:

#7: Enable handlers to support "commands" (like REMOVE); the "Products" import now recognizes the REMOVE command.
#70: Fixed-header import misses "special import" fields.
#71: An incomplete import record was not being flagged.
#72: Don't post-process imports that have previously failed.
#73: Enable a handler to specify an alternate key; supports the "Products" import to key to either the product's ID or model-number.
#74: Enable a handler to specify a minimum base-class version.
#75: Don't gather export filters until needed; a performance enhancement for store's with a large number of categories and/or manufacturers.
#78: Correct PHP Notice issues.

v1.1.0 is now available for download.

lat9:

@HeathenMagic, I don't understand why no log was generated for that error. That said, you could try enabling the DbIo debug, rerunning that OrdersProducts report and then sending me (it'll be a big file) the resultant log (it'll be the newest file with a name similar to /YOUR_ADMIN/dbio/logs/dbio-OrdersProducts-*.log).

Slight update: The OrdersProducts report was being run with filters on the "Orders ID" and, unfortunately, that resulted in the error:

PHP Fatal error:  1052:Column 'orders_id' in where clause is ambiguous

The correction is to properly "alias" that orders_id field. Along the way, I also found that the OrdersProducts report wasn't passing its output information through the encoding process (so that non-ASCII characters could get "mangled" in the output file), so that's been corrected as well.

I've got the update staged for DbIo's next version (most likely in early January): https://raw.githubusercontent.com/lat9/dbio/master/YOUR_ADMIN/includes/classes/dbio/DbIoOrdersProductsHandler.php

lat9:

Slight update: The OrdersProducts report was being run with filters on the "Orders ID" and, unfortunately, that resulted in the error:

PHP Fatal error:  1052:Column 'orders_id' in where clause is ambiguous

The correction is to properly "alias" that orders_id field. Along the way, I also found that the OrdersProducts report wasn't passing its output information through the encoding process (so that non-ASCII characters could get "mangled" in the output file), so that's been corrected as well.

I've got the update staged for DbIo's next version (most likely in early January): https://raw.githubusercontent.com/lat9/dbio/master/YOUR_ADMIN/includes/classes/dbio/DbIoOrdersProductsHandler.php

I've just submitted v1.1.1 of the DbIo to the Zen Cart plugins for review; I'll post back when it's available.