Import/Export class

AbanteCart Array/XML/CSV DATA Import/Export Engine (AData class)


What is AData Import/Export Engine or Class?

To provide maximum data flexibility, quick access and update of the database in abantecart, we have create easy and flexible class AData. This class helps get quick access to any database table or record in the database and receive easy to read XML, CSV or Array formats. Similar way, XML, CSV or Array records can be saved back to the database. 
AData is easy, fast and robust to deal with any database aspect of AbanteCart. You do not need to deal with database selects, updates and deletes. Data can be accessed in the batch with complete inset or with partial update. Important aspect to mention that data is imported (updated) in the same format is it is exported. To see format of the data, you can simply export the data, review, update or add in the same format and import back to the database.


Where AData is used

AData is used in the data import/export feature of AbanteCart and load of load of extension data. It can be used in any other part of future application development for import and export of data to and from various data formats. 
Using AData data can be connected and mapped to any other third party application or service as SOAP, XML feed or other.


Opportunity

With use of AbanteCart AData, there is a number of applications that can improve AbanteCart data management and communication processed. Applications can have quick data read and save with any AbanteCart database table using relationship based on primary key and foreign key.


How it works

Below are main interface functions to AData

Export Methods:

exportData

(request array, [ skip display of relationship IDs ])

Function to produce multidimensional associative array with data based on the request input array.

-request is an array providing list of tables and restrictions to the data to be exported from the database. See below is example of the request array structure. Request array can contain special parameters to set limited data extract (similar to SQL where clause).

Additional parameters: filter and start_id / end_id filter can contain any SQL (where) like portion to limit the result. start_id / end_id are 2 parameters to set a range of unique table IDs (if applicable)

- skip display of relationship IDs. By default, result array will not show relationship ID key and value on each nested element. This is not needed for the export, since all nested nodes of data gets relationship ID key and value from parent node. If pass as FALSE relationship ID key and value will be added on each node.

- result array. Result array is returned in a form of multidimensional associative array where each nested level represent child table and rows for related table. See below is example of the return array structure.

Example of request array
$request  = array (
    'categories' => array
       (
           'start_id' => 30,
           'end_id' => 50,
           'tables' => Array
               (
                   'category_descriptions' => 'category_descriptions',
                   'categories_to_stores' => 'categories_to_stores',
               )
       ),
   'products' => array
       (
           'start_id' => 60,
           'end_id' => 80,
           'tables' => array
               (
                   'product_descriptions' => 'product_descriptions',
                   'product_options' => array (
                       'tables' => array (
                           'product_option_descriptions' => 'product_option_descriptions',
                            'product_option_values' => array (
                                'tables' => array (
                                    'product_option_value_descriptions' => 'product_option_value_descriptions'
                                )
                            ),
                       )
                   ),
                   'product_specials' => 'product_specials',
               )
       )
);
request with the filter
$request  = array (
    'categories' => array
       (
           'start_id' => 30,
           'end_id' => 50,
    'filter' => 'status = 1 order by sort_order desc',
           'tables' => Array
               (
                   'category_descriptions' => 'category_descriptions',
                   'categories_to_stores' => 'categories_to_stores',
               )
       ),
}
Example Return Array:
Array
(
   [timestamp] => 05/14/2012 19:08:28
   [tables] => Array
       (
           [0] => Array
               (
                   [name] => categories
                   [rows] => Array
                       (
                           [0] => Array
                               (
                                   [category_id] => 36

            …

            [tables] => Array
                                       (
                                           [0] => Array
                                               (
                                                   [name] => category_descriptions
                                                   [rows] => Array

                       (
                          …
      )
)
                                           [1] => Array
                           (
                                                           [name] => categories_to_stores
                                                           [rows] => Array
                                                                 (
                              …
                                  )

                       )

                       )

                 )

              …

                         )

          ...

       )

    )

importData

(import data array, [ method test or commit (default) ])

Function to process multidimensional associative input array and perform action on the database tables with the data provided (insert/update/delete).

- input array. Input array is provided in a form of multidimensional associative array where each nested level represent child table and rows for related table. See below is example of the input array structure. On each data level, additional special key “action”. Action can be insert, update or delete. Note. insert or update action can be determined automatically, but to improve performance (speed) of update/insert we suggest you provided action as much as possible.

NOTE: If you import data with table autoincrement index columns provided (example: product_id), default action will be update. In case, new inserts required with autoincrement columns values, action "insert" needs to be specified.

- method. By default importData will perform an action on the database. With passing test, you can first check input before effecting the data in the database. NOTE: SQL run time errors can not be detected, but all other data related issues can be caught with this test.

- return. Array with statuses for each table. In the test mode array returned with all SQLs to be performed on the database.

Example Import Array:
Array
(
   [timestamp] => 05/14/2012 13:14:22
   [tables] => Array
       (
           [0] => Array
               (
                   [name] => products
                   [rows] => Array
                       (
                           [0] => Array
                               (
                                   [product_id] => 80

            …

            [tables] => Array

            (

                [0] => Array
                                               (
                                                   [name] => product_descriptions
                                                   [rows] => Array

                    (

    ...

)

                )

                ...

                [7] => Array
                                               (
                                                   [name] => product_options
                                                   [rows] => Array
                                                       (

                    [product_option_id] => 777

…

                    [tables] => Array

                    (

[0] => Array

(

                                                    [name] => product_option_descriptions
                                                    [rows] => Array

                            (

                                ...

)

)

...

)

                        )

                )

                   ...

)

array2XML

(array from exportData method, [ file name]) - Function to convert array generated by exportData to XML. Function can return XML string or save XML to file specified.

Example use
$this->data = new AData();
        $array_new = $this->data->exportData($request);
        $xml = $this->data->array2XML( $array_new );

ML2ArrayFromFile

( file_path string ) - Function to process XML provided in the file and update the database. Function returns array with status of each row and table action. Example of XML is provided below, but it also can be extracted with generateXML function.

XML2Array

(XML string ) - Same as XML2ArrayFromFile but with XML string input 

Example use
$this->data = new AData();
$array1 = $this->data->XML2ArrayFromFile( $path_to_file );
$array2 = $this->data->XML2Array( $xml_str );

CSV2ArrayFromFile

( file_path string, delimiter_index int ) - Function to convert CSV file to nested array. Returned array may be used to update the database.

Example use
$this->data = new AData();
$array = $this->data->CSV2ArrayFromFile( $path_to_file, 0 ); // 0 - comma as delimiter

array2CSV

( data_array, file_name string [, delimiter_index int, format csv or txt, enclosure_symbol, escape_symbol, as_file bool ]) - Provide CSV data from nested array.

Returns ZIP archive with CSV files. Each file contains data for whole section (categories, products, etc.).

- data_array - array with nested structure.

- file_name - name of the zip archive file.

- delimiter_index - index of the prefered delimiter symbol: 0 - comma, 1 - semicolon, 2 - TAB symbol.

- enclosure_symbol - columns in CSV file will be enclosed with this symbol.

- escape_symbol - chars in data that are used as enclosure will be escaped with this symbol.

- as_file - return as path to file or as file contents.

Example use
$this->data = new AData();
    $result = $this->data->array2CSV( $array_new, $fileName, 0 );

There is a model tool/table_relationships.php that stored tables relationship map. You need to add (hook) to this data if you have new tables to process with AData. Here is why we have this. Because AbanteCart default mysql engine is MyISAM there is no database based relationship. Relationship in tables is supported programmatically. This is primarily due to the fact that not all hosting providers support InnoDB and true database relationship can not be done.

TODO:

- Implement ‘all’ for the request array to include all nested tables automatically. Example:

'categories' => ‘all’

This will include all related tables based on table config.

- BUG in special case. some keys like language_id is not carried all the way to end nodes.

I think we need to stack main node input and pass it. Need better solution.

- Implement special method to import Resource Library file to local repository from remote location.