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