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