libraries/import/ods.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * OpenDocument Spreadsheet import plugin for phpMyAdmin
   5  *
   6  * @todo    Pretty much everything
   7  * @todo    Importing of accented characters seems to fail
   8  * @package PhpMyAdmin-Import
   9  * @subpackage ODS
  10  */
  11
  12 if (! defined('PHPMYADMIN')) {
  13     exit;
  14 }
  15
  16 /**
  17  * We need way to disable external XML entities processing.
  18  */
  19 if (!function_exists('libxml_disable_entity_loader')) {
  20     return;
  21 }
  22
  23 /**
  24  * The possible scopes for $plugin_param are: 'table', 'database', and 'server'
  25  */
  26
  27 if (isset($plugin_list)) {
  28     $plugin_list['ods'] = array(
  29         'text' => __('Open Document Spreadsheet'),
  30         'extension' => 'ods',
  31         'options' => array(
  32                 array('type' => 'begin_group', 'name' => 'general_opts'),
  33                 array('type' => 'bool', 'name' => 'col_names', 'text' => __('The first line of the file contains the table column names <i>(if this is unchecked, the first line will become part of the data)</i>')),
  34                 array('type' => 'bool', 'name' => 'empty_rows', 'text' => __('Do not import empty rows')),
  35                 array('type' => 'bool', 'name' => 'recognize_percentages', 'text' => __('Import percentages as proper decimals <i>(ex. 12.00% to .12)</i>')),
  36                 array('type' => 'bool', 'name' => 'recognize_currency', 'text' => __('Import currencies <i>(ex. $5.00 to 5.00)</i>')),
  37                 array('type' => 'end_group')
  38             ),
  39         'options_text' => __('Options'),
  40         );
  41     /* We do not define function when plugin is just queried for information above */
  42     return;
  43 }
  44
  45 $i = 0;
  46 $len = 0;
  47 $buffer = "";
  48
  49 /**
  50  * Read in the file via PMA_importGetNextChunk so that
  51  * it can process compressed files
  52  */
  53 while (! ($finished && $i >= $len) && ! $error && ! $timeout_passed) {
  54     $data = PMA_importGetNextChunk();
  55     if ($data === false) {
  56         /* subtract data we didn't handle yet and stop processing */
  57         $offset -= strlen($buffer);
  58         break;
  59     } elseif ($data === true) {
  60         /* Handle rest of buffer */
  61     } else {
  62         /* Append new data to buffer */
  63         $buffer .= $data;
  64         unset($data);
  65     }
  66 }
  67
  68 unset($data);
  69
  70 /**
  71  * Disable loading of external XML entities.
  72  */
  73 libxml_disable_entity_loader();
  74
  75 /**
  76  * Load the XML string
  77  *
  78  * The option LIBXML_COMPACT is specified because it can
  79  * result in increased performance without the need to
  80  * alter the code in any way. It's basically a freebee.
  81  */
  82 $xml = simplexml_load_string($buffer, "SimpleXMLElement", LIBXML_COMPACT);
  83
  84 unset($buffer);
  85
  86 if ($xml === false) {
  87     $sheets = array();
  88     $message = PMA_Message::error(__('The XML file specified was either malformed or incomplete. Please correct the issue and try again.'));
  89     $error = true;
  90 } else {
  91     $sheets = $xml->children('office', true)->{'body'}->{'spreadsheet'}->children('table', true);
  92 }
  93
  94 $tables = array();
  95
  96 $max_cols = 0;
  97
  98 $row_count = 0;
  99 $col_count = 0;
 100 $col_names = array();
 101
 102 $tempRow = array();
 103 $tempRows = array();
 104 $rows = array();
 105
 106 /* Iterate over tables */
 107 foreach ($sheets as $sheet) {
 108     $col_names_in_first_row = isset($_REQUEST['ods_col_names']);
 109
 110     /* Iterate over rows */
 111     foreach ($sheet as $row) {
 112         $type = $row->getName();
 113         if (! strcmp('table-row', $type)) {
 114             /* Iterate over columns */
 115             foreach ($row as $cell) {
 116                 $text = $cell->children('text', true);
 117                 $cell_attrs = $cell->attributes('office', true);
 118
 119                 if (count($text) != 0) {
 120                     $attr = $cell->attributes('table', true);
 121                     $num_repeat = (int) $attr['number-columns-repeated'];
 122                     $num_iterations = $num_repeat ? $num_repeat : 1;
 123
 124                     for ($k = 0; $k < $num_iterations; $k++) {
 125                         if (! $col_names_in_first_row) {
 126                             if ($_REQUEST['ods_recognize_percentages'] && !strcmp('percentage', $cell_attrs['value-type'])) {
 127                                 $tempRow[] = (double)$cell_attrs['value'];
 128                             } elseif ($_REQUEST['ods_recognize_currency'] && !strcmp('currency', $cell_attrs['value-type'])) {
 129                                 $tempRow[] = (double)$cell_attrs['value'];
 130                             } else {
 131                                 $tempRow[] = (string)$text;
 132                             }
 133                         } else {
 134                             if ($_REQUEST['ods_recognize_percentages'] && !strcmp('percentage', $cell_attrs['value-type'])) {
 135                                 $col_names[] = (double)$cell_attrs['value'];
 136                             } else if ($_REQUEST['ods_recognize_currency'] && !strcmp('currency', $cell_attrs['value-type'])) {
 137                                 $col_names[] = (double)$cell_attrs['value'];
 138                             } else {
 139                                 $col_names[] = (string)$text;
 140                             }
 141                         }
 142
 143                         ++$col_count;
 144                     }
 145                 } else {
 146                     /* Number of blank columns repeated */
 147                     if ($col_count < count($row->children('table', true)) - 1) {
 148                         $attr = $cell->attributes('table', true);
 149                         $num_null = (int)$attr['number-columns-repeated'];
 150
 151                         if ($num_null) {
 152                             if (! $col_names_in_first_row) {
 153                                 for ($i = 0; $i < $num_null; ++$i) {
 154                                     $tempRow[] = 'NULL';
 155                                     ++$col_count;
 156                                 }
 157                             } else {
 158                                 for ($i = 0; $i < $num_null; ++$i) {
 159                                     $col_names[] = PMA_getColumnAlphaName($col_count + 1);
 160                                     ++$col_count;
 161                                 }
 162                             }
 163                         } else {
 164                             if (! $col_names_in_first_row) {
 165                                 $tempRow[] = 'NULL';
 166                             } else {
 167                                 $col_names[] = PMA_getColumnAlphaName($col_count + 1);
 168                             }
 169
 170                             ++$col_count;
 171                         }
 172                     }
 173                 }
 174             }
 175
 176             /* Find the widest row */
 177             if ($col_count > $max_cols) {
 178                 $max_cols = $col_count;
 179             }
 180
 181             /* Don't include a row that is full of NULL values */
 182             if (! $col_names_in_first_row) {
 183                 if ($_REQUEST['ods_empty_rows']) {
 184                     foreach ($tempRow as $cell) {
 185                         if (strcmp('NULL', $cell)) {
 186                             $tempRows[] = $tempRow;
 187                             break;
 188                         }
 189                     }
 190                 } else {
 191                     $tempRows[] = $tempRow;
 192                 }
 193             }
 194
 195             $col_count = 0;
 196             $col_names_in_first_row = false;
 197             $tempRow = array();
 198         }
 199     }
 200
 201     /* Skip over empty sheets */
 202     if (count($tempRows) == 0 || count($tempRows[0]) == 0) {
 203         $col_names = array();
 204         $tempRow = array();
 205         $tempRows = array();
 206         continue;
 207     }
 208
 209     /**
 210      * Fill out each row as necessary to make
 211      * every one exactly as wide as the widest
 212      * row. This included column names.
 213      */
 214
 215     /* Fill out column names */
 216     for ($i = count($col_names); $i < $max_cols; ++$i) {
 217         $col_names[] = PMA_getColumnAlphaName($i + 1);
 218     }
 219
 220     /* Fill out all rows */
 221     $num_rows = count($tempRows);
 222     for ($i = 0; $i < $num_rows; ++$i) {
 223         for ($j = count($tempRows[$i]); $j < $max_cols; ++$j) {
 224             $tempRows[$i][] = 'NULL';
 225         }
 226     }
 227
 228     /* Store the table name so we know where to place the row set */
 229     $tbl_attr = $sheet->attributes('table', true);
 230     $tables[] = array((string)$tbl_attr['name']);
 231
 232     /* Store the current sheet in the accumulator */
 233     $rows[] = array((string)$tbl_attr['name'], $col_names, $tempRows);
 234     $tempRows = array();
 235     $col_names = array();
 236     $max_cols = 0;
 237 }
 238
 239 unset($tempRow);
 240 unset($tempRows);
 241 unset($col_names);
 242 unset($sheets);
 243 unset($xml);
 244
 245 /**
 246  * Bring accumulated rows into the corresponding table
 247  */
 248 $num_tbls = count($tables);
 249 for ($i = 0; $i < $num_tbls; ++$i) {
 250     for ($j = 0; $j < count($rows); ++$j) {
 251         if (! strcmp($tables[$i][TBL_NAME], $rows[$j][TBL_NAME])) {
 252             if (! isset($tables[$i][COL_NAMES])) {
 253                 $tables[$i][] = $rows[$j][COL_NAMES];
 254             }
 255
 256             $tables[$i][ROWS] = $rows[$j][ROWS];
 257         }
 258     }
 259 }
 260
 261 /* No longer needed */
 262 unset($rows);
 263
 264 /* Obtain the best-fit MySQL types for each column */
 265 $analyses = array();
 266
 267 $len = count($tables);
 268 for ($i = 0; $i < $len; ++$i) {
 269     $analyses[] = PMA_analyzeTable($tables[$i]);
 270 }
 271
 272 /**
 273  * string $db_name (no backquotes)
 274  *
 275  * array $table = array(table_name, array() column_names, array()() rows)
 276  * array $tables = array of "$table"s
 277  *
 278  * array $analysis = array(array() column_types, array() column_sizes)
 279  * array $analyses = array of "$analysis"s
 280  *
 281  * array $create = array of SQL strings
 282  *
 283  * array $options = an associative array of options
 284  */
 285
 286 /* Set database name to the currently selected one, if applicable */
 287 if (strlen($db)) {
 288     $db_name = $db;
 289     $options = array('create_db' => false);
 290 } else {
 291     $db_name = 'ODS_DB';
 292     $options = null;
 293 }
 294
 295 /* Non-applicable parameters */
 296 $create = null;
 297
 298 /* Created and execute necessary SQL statements from data */
 299 PMA_buildSQL($db_name, $tables, $analyses, $create, $options);
 300
 301 unset($tables);
 302 unset($analyses);
 303
 304 /* Commit any possible data in buffers */
 305 PMA_importRunQuery();
 306 ?>