Import_(development)

Marek Skiba edited this page Dec 27, 2016 · 6 revisions
Clone this wiki locally

I

General Information

Import plug-ins should be placed in the libraries/plugins/import directory. Plug-ins should build upon the following code framework. Official plug-ins need to have external string definitions to keep translation efforts centralized. The definition should be added to the appropriate language file (located in the lang/ directory), if you create plug-ins for your own personal use, you can place the strings directly into this file and they will be displayed correctly.

For more detailed documentation of relevant functions and classes, please see phpMyAdmin's auto-generated phpDoc page.

Type-Detection API

The type-detection module analyzes the contents of an import and determines the appropriate MySQL data types for each column of each table.

This can be useful in several scenarios

  • The MySQL knowledge required of the user is now lowered considerably (i.e. they don't have to manually create the databases/tables/etc.)
  • You don't know the sized or types of the data and there is simply too much to shift through by hand
  • And (as an extension of the last example) it is a time saver. Even if you do know the sizes/types of each column of data you are importing you no longer have to type them all in.

Using the new type-detection module allows you to add automatic structure creation to a new import module of your creation or an existing one. API documentation follows directly below. There is example usage at the end of this page.

Documentation

The type-detection API is rather simple. The most difficult part of implementing this functionality will be converting the raw import data into a format that the type-detection module can work with. This is explained below.

There are only two functions that you will utilize:

  • PMA_analyzeTable(&$table)

This function analyzes a table of data to determine the best-fit MySQL data types. It is used in the analysis stage.

  • PMA_buildSQL($db_name, &$tables, &$analyses = NULL, &$additional_sql = NULL, $options = NULL)

This function creates the necessary SQL statements to accomplish the import with the desired options. It is used in the execution stage.

For definitions of what's passed to these functions, please see this usage example.

Import Stages

There are three distinct stages that your plug-in must follow in order to implement type-detection. They are as follows:

  • Accumulation Stage

You must convert the raw import data into the type-detection format. The format can be seen in the second code example on this page.

The way you go about this depends on the file format you are working with. That is for you to determine, but generally speaking you work "upwards," by which I mean cell -> row -> table -> database, accumulating each as you progress.

  • Analysis Stage

Each table is analyzed and the best-fit types are determined. You need to store the return values of calls to this function in an dedicated array.

  • Execution Stage

The data and the analyses are used to create SQL statements which are executed.

Example Templates

Normal Import Module Template

This template shows how to create a normal import module (as opposed to one with type-detection/auto-structure-creation).

/* vim: set expandtab sw=4 ts=4 sts=4: */
/**
 * [Name] import plugin for phpMyAdmin
 *
 * @package    PhpMyAdmin-Import
 * @subpackage [Name]
 */
if (! defined('PHPMYADMIN')) {
    exit;
}
/* Get the import interface */
require_once "libraries/plugins/ImportPlugin.class.php";
/**
 * Handles the import for the [Name] format
 *
 * @package PhpMyAdmin-Import
 */
class Import[Name] extends ImportPlugin
{
    /**
     * optional - declare variables and descriptions
     *
     * @var type
     */
    private $_myOptionalVariable;
    /**
     * Constructor
     */
    public function __construct()
    {
        $this->setProperties();
    }
    /**
     * Sets the import plugin properties.
     * Called in the constructor.
     *
     * @return void
     */
    protected function setProperties()
    {
       // set properties
       $props = 'libraries/properties/';
       // include the main class for properties for the import plug-ins
       include_once "$props/plugins/ImportPluginProperties.class.php";
       // include the group properties classes
       include_once "$props/options/groups/OptionsPropertyRootGroup.class.php";
       include_once "$props/options/groups/OptionsPropertyMainGroup.class.php";
       // include the needed single property items
       include_once "$props/options/items/RadioPropertyItem.class.php";
       $importPluginProperties = new ImportPluginProperties();
       $importPluginProperties->setText('[name]');             // the name of your plug-in
       $importPluginProperties->setExtension('[ext]');         // extension this plug-in can handle
       $importPluginProperties->setOptionsText(__('Options'));
       // create the root group that will be the options field for
       // $importPluginProperties
       // this will be shown as "Format specific options"
       $importSpecificOptions = new OptionsPropertyRootGroup();
       $importSpecificOptions->setName("Format Specific Options");
       // general options main group
       $generalOptions = new OptionsPropertyMainGroup();
       $generalOptions->setName("general_opts");
       // optional :
       // create primary items and add them to the group
       // type - one of the classes listed in libraries/properties/options/items/
       // name - form element name
       // text - description in GUI
       // size - size of text element
       // len  - maximal size of input
       // values - possible values of the item
       $leaf = new RadioPropertyItem();
       $leaf->setName("structure_or_data");
       $leaf->setValues(
           array(
               'structure' => __('structure'),
               'data' => __('data'),
               'structure_and_data' => __('structure and data')
           )
       );
       $generalOptions->addProperty($leaf);
       // add the main group to the root group
       $importSpecificOptions->addProperty($generalOptions);
       // set the options for the import plugin property item
       $importPluginProperties->setOptions($importSpecificOptions);
       $this->properties = $importPluginProperties;
    }
    /**
     * This method is called when any PluginManager to which the observer
     * is attached calls PluginManager::notify()
     *
     * @param SplSubject $subject The PluginManager notifying the observer
     *                            of an update.
     *
     * @return void
     */
    public function update (SplSubject $subject)
    {
    }
    /**
     * Handles the whole import logic
     *
     * @return void
     */
    public function doImport()
    {
        // get globals (others are optional)
        global $error, $timeout_passed, $finished;
        $buffer = '';
        while (! ($finished && $i >= $len) && ! $error && ! $timeout_passed) {
            $data = PMA_importGetNextChunk();
            if ($data === false) {
                // subtract data we didn't handle yet and stop processing
                $offset -= strlen($buffer);
                break;
            } elseif ($data === true) {
                // Handle rest of buffer
            } else {
                // Append new data to buffer
                $buffer .= $data;
            }
            // PARSE $buffer here, post sql queries using:
            PMA_importRunQuery($sql, $verbose_sql_with_comments);
        } // End of import loop
        // Commit any possible data in buffers
        PMA_importRunQuery();
    }
    // optional:
    /* Getters and Setters */
    /**
     * Getter description
     *
     * @return type
     */
    private function _getMyOptionalVariable()
    {
        return $this->_myOptionalVariable;
    }
    /**
     * Setter description
     *
     * @param type $my_optional_variable description
     *
     * @return void
     */
    private function _setMyOptionalVariable($my_optional_variable)
    {
        $this->_myOptionalVariable = $my_optional_variable;
    }
} 
?>

Import Module with Type-Detection Template

This template shows how to create an import module with type-detection/auto-structure-creation.

<?php
/* vim: set expandtab sw=4 ts=4 sts=4: */
/**
 * [Name] import plugin for phpMyAdmin
 *
 * @package    PhpMyAdmin-Import
 * @subpackage [Name]
 */
if (! defined('PHPMYADMIN')) {
    exit;
}
/* Get the import interface */
require_once "libraries/plugins/ImportPlugin.class.php";
/**
 * Handles the import for the [Name] format
 *
 * @package PhpMyAdmin-Import
 */
class Import[Name] extends ImportPlugin
{
    /**
     * optional - declare variables and descriptions
     *
     * @var type
     */
    private $_myOptionalVariable;
    /**
     * Constructor
     */
    public function __construct()
    {
        $this->setProperties();
    }
    /**
     * Sets the import plugin properties.
     * Called in the constructor.
     *
     * @return void
     */
    protected function setProperties()
    {
       // set properties
       $props = 'libraries/properties/';
       // include the main class for properties for the import plug-ins
       include_once "$props/plugins/ImportPluginProperties.class.php";
       // include the group properties classes
       include_once "$props/options/groups/OptionsPropertyRootGroup.class.php";
       include_once "$props/options/groups/OptionsPropertyMainGroup.class.php";
       // include the needed single property items
       include_once "$props/options/items/RadioPropertyItem.class.php";
       $importPluginProperties = new ImportPluginProperties();
       $importPluginProperties->setText('[name]');             // the name of your plug-in
       $importPluginProperties->setExtension('[ext]');         // extension this plug-in can handle
       $importPluginProperties->setOptionsText(__('Options'));
       // create the root group that will be the options field for
       // $importPluginProperties
       // this will be shown as "Format specific options"
       $importSpecificOptions = new OptionsPropertyRootGroup();
       $importSpecificOptions->setName("Format Specific Options");
       // general options main group
       $generalOptions = new OptionsPropertyMainGroup();
       $generalOptions->setName("general_opts");
       // optional :
       // create primary items and add them to the group
       // type - one of the classes listed in libraries/properties/options/items/
       // name - form element name
       // text - description in GUI
       // size - size of text element
       // len  - maximal size of input
       // values - possible values of the item
       $leaf = new RadioPropertyItem();
       $leaf->setName("structure_or_data");
       $leaf->setValues(
           array(
               'structure' => __('structure'),
               'data' => __('data'),
               'structure_and_data' => __('structure and data')
           )
       );
       $generalOptions->addProperty($leaf);
       // add the main group to the root group
       $importSpecificOptions->addProperty($generalOptions);
       // set the options for the import plugin property item
       $importPluginProperties->setOptions($importSpecificOptions);
       $this->properties = $importPluginProperties;
    }
    /**
     * This method is called when any PluginManager to which the observer
     * is attached calls PluginManager::notify()
     *
     * @param SplSubject $subject The PluginManager notifying the observer
     *                            of an update.
     *
     * @return void
     */
    public function update (SplSubject $subject)
    {
    }
    /**
     * Handles the whole import logic
     *
     * @return void
     */
    public function doImport()
    {
       // Increase the memory limit to at least 128MB
       ini_set('memory_limit', '128M');
       $i = 0;
       $len = 0;
       $buffer = "";
       /**
        * Read in the file via PMA_importGetNextChunk so that
        * it can process compressed files
        */
       while (! ($finished && $i >= $len) && ! $error && ! $timeout_passed) {
           $data = PMA_importGetNextChunk();
           if ($data === FALSE) {
               /* subtract data we didn't handle yet and stop processing */
               $offset -= strlen($buffer);
               break;
           } else if ($data === TRUE) {
               /* Handle rest of buffer */
           } else {
               /* Append new data to buffer */
               $buffer .= $data;
               unset($data);
           }
    
           /**
            * Do NOT use the PMA_importRunQuery() function here like you
            * would in a normal import module, we aren't done yet.
            */
       }
       // Here we initialize the accumulators
       /*
        * A single table should have this structure:
        *
        * array(string table_name, array() column_names, array()() rows)
        *
`         * INSERT INTO `Example_Table` (`Product`, `Qtr_1`, `Qtr_2`) VALUES `
        * ('Prod 1',  744.60,  162.00),
        * ('Prod 2', 5079.20, 1249.63),
        * ('Prod 3', 1267.75, 1024.00),
        * ('Prod 4', 1408.50, 1756.84),
        * ('Prod 5', 4728.36,  756.42),
        * ('Prod 6',  943.89,  349.60);
        *
        * Explicity declaring a table of this type using the SQL sample
        * above would result in this:
        *
        * $table = array(
        *            "Example_Table",                      // Table name
        *            array(                                // Column names
        *              "Product",                          // First column
        *              "Qtr_1",                            // Second column
        *              "Qtr_2"                             // Third column
        *            ),
        *            array(                                // Data rows
        *              array('Prod 1',  744.60,  162.00),  // Row 1
        *              array('Prod 2', 5079.20, 1249.63),  // Row 2
        *              array('Prod 3', 1267.75, 1024.00),  // Row 3
        *              array('Prod 4', 1408.50, 1756.84),  // Row 4
        *              array('Prod 5', 4728.36,  756.42),  // Row 5
        *              array('Prod 6',  943.89,  349.60)   // Row 6
        *            )
        *          );
        */
       // This will contain all of the data once complete
       $tables = array();
       // Generally, you will need to accumulate one row at a time
       $tempRow = array();
       // Accumulate tempRows here
       $rows = array();
       // Either generate or import column names
       $col_names = array();
       /**
        * Once all the tables have been accumulated, we can analyze them.
        *
        * Alternatively, you can analyze each table as you go along, before
        * you append them to the $tables array.
        */
       // Obtain the best-fit MySQL types for each column in each table
       $analyses = array();
       $len = count($tables);
       for ($i = 0; $i < $len; ++$i) {
           $analyses[] = PMA_analyzeTable($tables[$i]);
       }
       /**
        * Now that we have formatted the data and analyzed it all,
        * we can construct the SQL queries needed to create the
        * structures and insert the data.
        *
        * The following if-else block checks to see if a database
        * is selected. If one is, then we tell PMA_buildSQL not
        * to create a database, and we set the database name to.
        * If not, then we define our own name and set options to NULL.
        */
       // Set database name to the currently selected one, if one is selected
       if (strlen($db)) {
           $db_name = $db;
           $options = array('create_db' => false);
       } else {
           $db_name = 'DN_NAME'; // If you want the module to create a data for the new data,
                                 // put the desired name here
           $options = NULL;
       }
       /**
        * Any additional SQL statements that you need to execute after the database
        * is created (if necessary), but before the data is inserted.
        *
        * This should be an array of strings.
        */
       $additional_sql = NULL;
       // Create and execute necessary SQL statements from data
       /**
        * string $db_name (no backquotes)
        *
        * array $table = array(string table_name, array() column_names, array()() rows)
        * array $tables = array of "$table"s (horrible grammar, I know =P)
        *
        * array $analysis = array(array() column_types, array() column_sizes)
        * array $analyses = array of "$analysis"s
        *
        * array $additional_sql = array of SQL strings
        *
        * array $options = an associative array of options
        */
       PMA_buildSQL($db_name, $tables, $analyses, $additional_sql, $options);
       // Commit any possible data in buffers
       PMA_importRunQuery();
   }
   // optional:
   /* Getters and Setters */
   /**
    * Getter description
    *
    * @return type
    */
   private function _getMyOptionalVariable()
   {
       return $this->_myOptionalVariable;
   }
   /**
    * Setter description
    *
    * @param type $my_optional_variable description
    *
    * @return void
    */
   private function _setMyOptionalVariable($my_optional_variable)
   {
       $this->_myOptionalVariable = $my_optional_variable;
   }
}
?>