libraries/central_columns.lib.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * Functions for displaying user preferences pages
   5  *
   6  * @package PhpMyAdmin
   7  */
   8 if (! defined('PHPMYADMIN')) {
   9     exit;
  10 }
  11
  12 /**
  13  * Defines the central_columns parameters for the current user
  14  *
  15  * @return array    the central_columns parameters for the current user
  16  * @access  public
  17  */
  18 function PMA_centralColumnsGetParams()
  19 {
  20     static $cfgCentralColumns = null;
  21
  22     if (null !== $cfgCentralColumns) {
  23         return $cfgCentralColumns;
  24     }
  25
  26     $cfgRelation = PMA_getRelationsParam();
  27
  28     if (isset($cfgRelation['central_columnswork'])
  29         && $cfgRelation['central_columnswork']
  30     ) {
  31         $cfgCentralColumns = array(
  32             'user'  => $GLOBALS['cfg']['Server']['user'],
  33             'db'    => $cfgRelation['db'],
  34             'table' => $cfgRelation['central_columns'],
  35         );
  36     } else {
  37         $cfgCentralColumns = false;
  38     }
  39
  40     return $cfgCentralColumns;
  41 }
  42
  43 /**
  44  * get $num columns of given database from central columns list
  45  * starting at offset $from
  46  *
  47  * @param string $db   selected database
  48  * @param int    $from starting offset of first result
  49  * @param int    $num  maximum number of results to return
  50  *
  51  * @return array list of $num columns present in central columns list
  52  * starting at offset $from for the given database
  53  */
  54 function PMA_getColumnsList($db, $from=0, $num=25)
  55 {
  56     $cfgCentralColumns = PMA_centralColumnsGetParams();
  57     if (empty($cfgCentralColumns)) {
  58         return array();
  59     }
  60     $pmadb = $cfgCentralColumns['db'];
  61     $GLOBALS['dbi']->selectDb($pmadb, $GLOBALS['controllink']);
  62     $central_list_table = $cfgCentralColumns['table'];
  63     //get current values of $db from central column list
  64     if ($num == 0) {
  65         $query = 'SELECT * FROM ' . PMA_Util::backquote($central_list_table) . ' '
  66             . 'WHERE db_name = \'' . $db . '\';';
  67     } else {
  68         $query = 'SELECT * FROM ' . PMA_Util::backquote($central_list_table) . ' '
  69             . 'WHERE db_name = \'' . $db . '\''
  70             . 'LIMIT ' . $from . ', ' . $num . ';';
  71     }
  72     $has_list = (array) $GLOBALS['dbi']->fetchResult(
  73         $query, null, null, $GLOBALS['controllink']
  74     );
  75     return $has_list;
  76 }
  77
  78 /**
  79  * get the number of columns present in central list for given db
  80  *
  81  * @param string $db current database
  82  *
  83  * @return int number of columns in central list of columns for $db
  84  */
  85 function PMA_getCentralColumnsCount($db)
  86 {
  87     $cfgCentralColumns = PMA_centralColumnsGetParams();
  88     if (empty($cfgCentralColumns)) {
  89         return 0;
  90     }
  91     $pmadb = $cfgCentralColumns['db'];
  92     $GLOBALS['dbi']->selectDb($pmadb);
  93     $central_list_table = $cfgCentralColumns['table'];
  94     $query = 'SELECT count(db_name) FROM ' .
  95                PMA_Util::backquote($central_list_table) . ' '
  96             . 'WHERE db_name = \'' . $db . '\';';
  97     $res = $GLOBALS['dbi']->fetchResult($query);
  98     return $res[0];
  99 }
 100 /**
 101  * return the existing columns in central list among the given list of columns
 102  *
 103  * @param string  $db        the selected database
 104  * @param string  $cols      comma separated list of given columns
 105  * @param boolean $allFields set if need all the fields of existing columns,
 106  * otherwise only column_name is returned
 107  *
 108  * @return array list of columns in central columns among given set of columns
 109  */
 110 function PMA_findExistingColNames($db, $cols, $allFields=false)
 111 {
 112     $cfgCentralColumns = PMA_centralColumnsGetParams();
 113     if (empty($cfgCentralColumns)) {
 114         return array();
 115     }
 116     $pmadb = $cfgCentralColumns['db'];
 117     $GLOBALS['dbi']->selectDb($pmadb, $GLOBALS['controllink']);
 118     $central_list_table = $cfgCentralColumns['table'];
 119     if ($allFields) {
 120         $query = 'SELECT * FROM ' . PMA_Util::backquote($central_list_table) . ' '
 121             . 'WHERE db_name = \'' . $db . '\' AND col_name IN (' . $cols . ');';
 122     } else {
 123         $query = 'SELECT col_name FROM '
 124             . PMA_Util::backquote($central_list_table) . ' '
 125             . 'WHERE db_name = \'' . $db . '\' AND col_name IN (' . $cols . ');';
 126     }
 127     $has_list = (array) $GLOBALS['dbi']->fetchResult(
 128         $query, null, null, $GLOBALS['controllink']
 129     );
 130     return $has_list;
 131 }
 132
 133 /**
 134  * return error message to be displayed if central columns
 135  * configuration storage is not completely configured
 136  *
 137  * @return PMA_Message
 138  */
 139 function PMA_configErrorMessage()
 140 {
 141     return PMA_Message::error(
 142         __(
 143             'The configuration storage is not ready for the central list'
 144             . ' of columns feature.'
 145         )
 146     );
 147 }
 148
 149 /**
 150  * build the insert query for central columns list given PMA storage
 151  * db, central_columns table, column name and corresponding definition to be added
 152  *
 153  * @param string $column             column to add into central list
 154  * @param array  $def                list of attributes of the column being added
 155  * @param string $db                 PMA configuration storage database name
 156  * @param string $central_list_table central columns configuration storage table name
 157  *
 158  * @return string query string to insert the given column
 159  * with definition into central list
 160  */
 161 function PMA_getInsertQuery($column, $def, $db, $central_list_table)
 162 {
 163     $type = "";
 164     $length = 0;
 165     if (isset($def['Type'])) {
 166         $extracted_columnspec = PMA_Util::extractColumnSpec($def['Type']);
 167         $type = $extracted_columnspec['type'];
 168         $length = $extracted_columnspec['spec_in_brackets'];
 169     }
 170     $collation = isset($def['Collation'])?$def['Collation']:"";
 171     $isNull = ($def['Null'] == "NO")?0:1;
 172     $extra = isset($def['Extra'])?$def['Extra']:"";
 173     $default = isset($def['Default'])?$def['Default']:"";
 174     $insQuery = 'INSERT INTO '
 175     . PMA_Util::backquote($central_list_table) . ' '
 176     . 'VALUES ( \'' . PMA_Util::sqlAddSlashes($db) . '\' ,'
 177     . '\'' . PMA_Util::sqlAddSlashes($column) . '\',\''
 178     . PMA_Util::sqlAddSlashes($type) . '\','
 179     . '\'' . PMA_Util::sqlAddSlashes($length) . '\',\''
 180     . PMA_Util::sqlAddSlashes($collation) . '\','
 181     . '\'' . PMA_Util::sqlAddSlashes($isNull) . '\','
 182     . '\'' . PMA_Util::sqlAddSlashes($extra) . '\',\''
 183     . PMA_Util::sqlAddSlashes($default) . '\');';
 184     return $insQuery;
 185 }
 186
 187 /**
 188  * If $isTable is true then unique columns from given tables as $field_select
 189  * are added to central list otherwise the $field_select is considered as
 190  * list of columns and these columns are added to central list if not already added
 191  *
 192  * @param array  $field_select if $isTable is true selected tables list
 193  * otherwise selected columns list
 194  * @param bool   $isTable      if passed array is of tables or columns
 195  * @param string $table        if $isTable is false,
 196  * then table name to which columns belong
 197  *
 198  * @return true|PMA_Message
 199  */
 200 function PMA_syncUniqueColumns($field_select, $isTable=true, $table=null)
 201 {
 202     $cfgCentralColumns = PMA_centralColumnsGetParams();
 203     if (empty($cfgCentralColumns)) {
 204         return PMA_configErrorMessage();
 205     }
 206     $db = $_REQUEST['db'];
 207     $pmadb = $cfgCentralColumns['db'];
 208     $central_list_table = $cfgCentralColumns['table'];
 209     $GLOBALS['dbi']->selectDb($db, $GLOBALS['userlink']);
 210     $existingCols = array();
 211     $cols = "";
 212     $insQuery = array();
 213     $fields = array();
 214     $message = true;
 215     if ($isTable) {
 216         foreach ($field_select as $table) {
 217             $fields[$table] = (array) $GLOBALS['dbi']->getColumns(
 218                 $db, $table, null, true, $GLOBALS['userlink']
 219             );
 220             foreach ($fields[$table] as $field => $def) {
 221                 $cols .= "'" . PMA_Util::sqlAddSlashes($field) . "',";
 222             }
 223         }
 224
 225         $has_list = PMA_findExistingColNames($db, trim($cols, ','));
 226         foreach ($field_select as $table) {
 227             foreach ($fields[$table] as $field => $def) {
 228                 if (!in_array($field, $has_list)) {
 229                     $has_list[] = $field;
 230                     $insQuery[] = PMA_getInsertQuery(
 231                         $field, $def, $db, $central_list_table
 232                     );
 233                 } else {
 234                     $existingCols[] = "'" . $field . "'";
 235                 }
 236             }
 237         }
 238     } else {
 239         if ($table == null) {
 240             $table = $_REQUEST['table'];
 241         }
 242         foreach ($field_select as $column) {
 243             $cols .= "'" . PMA_Util::sqlAddSlashes($column) . "',";
 244         }
 245          $has_list = PMA_findExistingColNames($db, trim($cols, ','));
 246         foreach ($field_select as $column) {
 247             if (!in_array($column, $has_list)) {
 248                 $has_list[] = $column;
 249                 $field = (array) $GLOBALS['dbi']->getColumns(
 250                     $db, $table, $column,
 251                     true, $GLOBALS['userlink']
 252                 );
 253                 $insQuery[] = PMA_getInsertQuery(
 254                     $column, $field, $db, $central_list_table
 255                 );
 256             } else {
 257                 $existingCols[] = "'" . $column . "'";
 258             }
 259         }
 260     }
 261     if ($existingCols) {
 262         $existingCols = implode(",", array_unique($existingCols));
 263         $message = PMA_Message::notice(
 264             sprintf(
 265                 __(
 266                     'Could not add %1$s as they already exist in central list!'
 267                 ), htmlspecialchars($existingCols)
 268             )
 269         );
 270         $message->addMessage(
 271             PMA_Message::notice(
 272                 "Please remove them first "
 273                 . "from central list if you want to update above columns"
 274             )
 275         );
 276     }
 277     $GLOBALS['dbi']->selectDb($pmadb, $GLOBALS['controllink']);
 278     if ($insQuery) {
 279         foreach ($insQuery as $query) {
 280             if (!$GLOBALS['dbi']->tryQuery($query, $GLOBALS['controllink'])) {
 281                 $message = PMA_Message::error(__('Could not add columns!'));
 282                 $message->addMessage(
 283                     PMA_Message::rawError(
 284                         $GLOBALS['dbi']->getError($GLOBALS['controllink'])
 285                     )
 286                 );
 287                 break;
 288             }
 289         }
 290     }
 291     return $message;
 292 }
 293
 294 /**
 295  * if $isTable is true it removes all columns of given tables as $field_select from
 296  * central columns list otherwise $field_select is columns list and it removes
 297  * given columns if present in central list
 298  *
 299  * @param array $field_select if $isTable selected list of tables otherwise
 300  * selected list of columns to remove from central list
 301  * @param bool  $isTable      if passed array is of tables or columns
 302  *
 303  * @return true|PMA_Message
 304  */
 305 function PMA_deleteColumnsFromList($field_select, $isTable=true)
 306 {
 307     $cfgCentralColumns = PMA_centralColumnsGetParams();
 308     if (empty($cfgCentralColumns)) {
 309         return PMA_configErrorMessage();
 310     }
 311     $db = $_REQUEST['db'];
 312     $pmadb = $cfgCentralColumns['db'];
 313     $central_list_table = $cfgCentralColumns['table'];
 314     $GLOBALS['dbi']->selectDb($db, $GLOBALS['userlink']);
 315     $message = true;
 316     $colNotExist = array();
 317     $fields = array();
 318     if ($isTable) {
 319         $cols = '';
 320         foreach ($field_select as $table) {
 321             $fields[$table] = (array) $GLOBALS['dbi']->getColumnNames(
 322                 $db, $table, $GLOBALS['userlink']
 323             );
 324             foreach ($fields[$table] as $col_select) {
 325                 $cols .= '\'' . PMA_Util::sqlAddSlashes($col_select) . '\',';
 326             }
 327         }
 328         $cols = trim($cols, ',');
 329         $has_list = PMA_findExistingColNames($db, $cols);
 330         foreach ($field_select as $table) {
 331             foreach ($fields[$table] as $column) {
 332                 if (!in_array($column, $has_list)) {
 333                     $colNotExist[] = "'" . $column . "'";
 334                 }
 335             }
 336         }
 337
 338     } else {
 339         $cols = '';
 340         foreach ($field_select as $col_select) {
 341             $cols .= '\'' . PMA_Util::sqlAddSlashes($col_select) . '\',';
 342         }
 343         $cols = trim($cols, ',');
 344         $has_list = PMA_findExistingColNames($db, $cols);
 345         foreach ($field_select as $column) {
 346             if (!in_array($column, $has_list)) {
 347                 $colNotExist[] = "'" . $column . "'";
 348             }
 349         }
 350     }
 351     if ($colNotExist) {
 352             $colNotExist = implode(",", array_unique($colNotExist));
 353             $message = PMA_Message::notice(
 354                 sprintf(
 355                     __(
 356                         'Couldn\'t remove Column(s) %1$s '
 357                         . 'as they don\'t exist in central columns list!'
 358                     ), htmlspecialchars($colNotExist)
 359                 )
 360             );
 361     }
 362     $GLOBALS['dbi']->selectDb($pmadb, $GLOBALS['controllink']);
 363
 364     $query = 'DELETE FROM ' . PMA_Util::backquote($central_list_table) . ' '
 365                 . 'WHERE db_name = \'' . $db . '\' AND col_name IN (' . $cols . ');';
 366
 367     if (!$GLOBALS['dbi']->tryQuery($query, $GLOBALS['controllink'])) {
 368         $message = PMA_Message::error(__('Could not remove columns!'));
 369         $message->addMessage('<br />' . htmlspecialchars($cols) . '<br />');
 370         $message->addMessage(
 371             PMA_Message::rawError(
 372                 $GLOBALS['dbi']->getError($GLOBALS['controllink'])
 373             )
 374         );
 375     }
 376     return $message;
 377 }
 378
 379 /**
 380  * make the columns of given tables consistent with central list of columns.
 381  * Updates only those columns which are not being referenced.
 382  *
 383  * @param string $db              current database
 384  * @param array  $selected_tables list of selected tables.
 385  *
 386  * @return true|PMA_Message
 387  */
 388 function PMA_makeConsistentWithList($db, $selected_tables)
 389 {
 390     $message = true;
 391     foreach ($selected_tables as $table) {
 392         $query = 'ALTER TABLE ' . PMA_Util::backquote($table);
 393         $has_list = PMA_getCentralColumnsFromTable($db, $table, true);
 394         $GLOBALS['dbi']->selectDb($db, $GLOBALS['userlink']);
 395         foreach ($has_list as $column) {
 396             $column_status = PMA_checkChildForeignReferences(
 397                 $db, $table, $column['col_name']
 398             );
 399             //column definition can only be changed if
 400             //it is not referenced by another column
 401             if ($column_status['isEditable']) {
 402                 $query .= ' MODIFY ' . PMA_Util::backquote($column['col_name']) . ' '
 403                     . PMA_Util::sqlAddSlashes($column['col_type']);
 404                 if ($column['col_length']) {
 405                     $query .= '(' . $column['col_length'] . ')';
 406                 }
 407                 if ($column['col_isNull']) {
 408                     $query .= ' NULL';
 409                 } else {
 410                     $query .= ' NOT NULL';
 411                 }
 412                 $query .= ' ' . $column['col_extra'];
 413                 if ($column['col_default']) {
 414                     if ($column['col_default'] != 'CURRENT_TIMESTAMP') {
 415                         $query .= ' DEFAULT \'' . PMA_Util::sqlAddSlashes(
 416                             $column['col_default']
 417                         ) . '\'';
 418                     } else {
 419                         $query .= ' DEFAULT ' . PMA_Util::sqlAddSlashes(
 420                             $column['col_default']
 421                         );
 422                     }
 423                 }
 424                 $query .= ',';
 425             }
 426         }
 427         $query = trim($query, " ,") . ";";
 428         if (!$GLOBALS['dbi']->tryQuery($query, $GLOBALS['userlink'])) {
 429             if ($message === true) {
 430                 $message = PMA_Message::error(
 431                     $GLOBALS['dbi']->getError($GLOBALS['userlink'])
 432                 );
 433             } else {
 434                 $message->addMessage('<br />');
 435                 $message->addMessage(
 436                     $GLOBALS['dbi']->getError($GLOBALS['userlink'])
 437                 );
 438             }
 439         }
 440     }
 441     return $message;
 442 }
 443
 444 /**
 445  * return the columns present in central list of columns for a given
 446  * table of a given database
 447  *
 448  * @param string  $db        given database
 449  * @param string  $table     given table
 450  * @param boolean $allFields set if need all the fields of existing columns,
 451  * otherwise only column_name is returned
 452  *
 453  * @return array columns present in central list from given table of given db.
 454  */
 455 function PMA_getCentralColumnsFromTable($db, $table, $allFields=false)
 456 {
 457     $cfgCentralColumns = PMA_centralColumnsGetParams();
 458     if (empty($cfgCentralColumns)) {
 459         return array();
 460     }
 461     $GLOBALS['dbi']->selectDb($db, $GLOBALS['userlink']);
 462     $fields = (array) $GLOBALS['dbi']->getColumnNames(
 463         $db, $table, $GLOBALS['userlink']
 464     );
 465     $cols = '';
 466     foreach ($fields as $col_select) {
 467         $cols .= '\'' . PMA_Util::sqlAddSlashes($col_select) . '\',';
 468     }
 469     $cols = trim($cols, ',');
 470     $has_list = PMA_findExistingColNames($db, $cols, $allFields);
 471     if (isset($has_list) && $has_list) {
 472         return (array)$has_list;
 473     } else {
 474         return array();
 475     }
 476 }
 477
 478 /**
 479  * update a column in central columns list if a edit is requested
 480  *
 481  * @param string $db            current database
 482  * @param string $orig_col_name original column name before edit
 483  * @param string $col_name      new column name
 484  * @param string $col_type      new column type
 485  * @param string $col_length    new column length
 486  * @param int    $col_isNull    value 1 if new column isNull is true, 0 otherwise
 487  * @param string $collation     new column collation
 488  * @param string $col_extra     new column extra property
 489  * @param string $col_default   new column default value
 490  *
 491  * @return true|PMA_Message
 492  */
 493 function PMA_updateOneColumn($db, $orig_col_name, $col_name, $col_type,
 494     $col_length, $col_isNull, $collation, $col_extra, $col_default
 495 ) {
 496     $cfgCentralColumns = PMA_centralColumnsGetParams();
 497     if (empty($cfgCentralColumns)) {
 498         return PMA_configErrorMessage();
 499     }
 500     $centralTable = $cfgCentralColumns['table'];
 501     $GLOBALS['dbi']->selectDb($cfgCentralColumns['db'], $GLOBALS['controllink']);
 502     if ($orig_col_name == "") {
 503         $def = array();
 504         $def['Type'] = $col_type;
 505         if ($col_length) {
 506             $def['Type'] .= '(' . $col_length . ')';
 507         }
 508         $def['Collation'] = $collation;
 509         $def['Null'] = $col_isNull?__('YES'):__('NO');
 510         $def['Extra'] = $col_extra;
 511         $def['Default'] = $col_default;
 512         $query = PMA_getInsertQuery($col_name, $def, $db, $centralTable);
 513     } else {
 514         $query = 'UPDATE ' . PMA_Util::backquote($centralTable)
 515                 . ' SET col_type = \'' . PMA_Util::sqlAddSlashes($col_type) . '\''
 516                 . ',col_name = \'' . PMA_Util::sqlAddSlashes($col_name) . '\''
 517                 . ', col_length = \'' . PMA_Util::sqlAddSlashes($col_length) . '\''
 518                 . ', col_isNull = ' . $col_isNull
 519                 . ', col_collation = \'' . PMA_Util::sqlAddSlashes($collation) . '\''
 520                 . ', col_extra = \'' . PMA_Util::sqlAddSlashes($col_extra) . '\''
 521                 . ', col_default = \'' . PMA_Util::sqlAddSlashes($col_default) . '\''
 522                 . ' WHERE db_name = \'' . PMA_Util::sqlAddSlashes($db) . '\' '
 523                 . 'AND col_name = \'' . PMA_Util::sqlAddSlashes($orig_col_name)
 524                 . '\'';
 525     }
 526     if (!$GLOBALS['dbi']->tryQuery($query, $GLOBALS['controllink'])) {
 527         return PMA_Message::error(
 528             $GLOBALS['dbi']->getError($GLOBALS['controllink'])
 529         );
 530     }
 531      return true;
 532 }
 533
 534 /**
 535  * get the html for table navigation in Central columns page
 536  *
 537  * @param int    $total_rows total number of rows in complete result set
 538  * @param int    $pos        offset of first result with complete result set
 539  * @param string $db         current database
 540  *
 541  * @return html for table navigation in Central columns page
 542  */
 543 function PMA_getHTMLforTableNavigation($total_rows, $pos, $db)
 544 {
 545     $max_rows = $GLOBALS['cfg']['MaxRows'];
 546     $pageNow = ($pos / $max_rows) + 1;
 547     $nbTotalPage = ceil($total_rows / $max_rows);
 548     $table_navigation_html = '<table style="display:inline-block;max-width:49%" '
 549         . 'class="navigation nospacing nopadding">'
 550         . '<tr>'
 551         . '<td class="navigation_separator"></td>';
 552     if ($pos - $max_rows >= 0) {
 553         $table_navigation_html .= '<td>'
 554             . '<form action="db_central_columns.php" method="post">'
 555             . PMA_URL_getHiddenInputs(
 556                 $db
 557             )
 558             . '<input type="hidden" name="pos" value="' . ($pos - $max_rows) . '" />'
 559             . '<input type="hidden" name="total_rows" value="' . $total_rows . '"/>'
 560             . '<input type="submit" name="navig"'
 561             . ' class="ajax" '
 562             . 'value="&lt" />'
 563             . '</form>'
 564             . '</td>';
 565     }
 566     if ($nbTotalPage > 1) {
 567         $table_navigation_html .= '<td>';
 568         $table_navigation_html .= '<form action="db_central_columns.php'
 569             . '" method="post">'
 570             . PMA_URL_getHiddenInputs(
 571                 $db
 572             )
 573             . '<input type="hidden" name="total_rows" value="' . $total_rows . '"/>';
 574         $table_navigation_html .= PMA_Util::pageselector(
 575             'pos', $max_rows, $pageNow, $nbTotalPage
 576         );
 577         $table_navigation_html .= '</form>'
 578             . '</td>';
 579     }
 580     if ($pos + $max_rows < $total_rows) {
 581         $table_navigation_html .= '<td>'
 582             . '<form action="db_central_columns.php" method="post">'
 583             . PMA_URL_getHiddenInputs(
 584                 $db
 585             )
 586             . '<input type="hidden" name="pos" value="' . ($pos + $max_rows) . '" />'
 587             . '<input type="hidden" name="total_rows" value="' . $total_rows . '"/>'
 588             . '<input type="submit" name="navig"'
 589             . ' class="ajax" '
 590             . 'value="&gt" />'
 591             . '</form>'
 592             . '</td>';
 593     }
 594     $table_navigation_html .= '</form>'
 595         . '</td>'
 596         . '<td class="navigation_separator"></td>'
 597         . '<td>'
 598         . '<span>' . __('Filter rows') . ':</span>'
 599         . '<input type="text" class="filter_rows" placeholder="'
 600         . __('Search this table') . '">'
 601         . '</td>'
 602         . '<td class="navigation_separator"></td>'
 603         . '</tr>'
 604         . '</table>';
 605
 606     return $table_navigation_html;
 607 }
 608
 609 /**
 610  * function generate and return the table header for central columns page
 611  *
 612  * @param string  $class       styling class of 'th' elements
 613  * @param string  $title       title of the 'th' elements
 614  * @param integer $actionCount number of actions
 615  *
 616  * @return html for table header in central columns view/edit page
 617  */
 618 function PMA_getCentralColumnsTableHeader($class='', $title='', $actionCount=0)
 619 {
 620     $action = '';
 621     if ($actionCount > 0) {
 622         $action .= '<th colspan="' . $actionCount . '">' . __('Action') . '</th>';
 623     }
 624     $tableheader = '<thead>';
 625     $tableheader .= '<tr>'
 626         . $action
 627         . '<th class="" style="display:none"></th>'
 628         . '<th class="' . $class . '" title="' . $title . '" data-column="name">'
 629         . __('Name') . '<div class="sorticon"></div></th>'
 630         . '<th class="' . $class . '" title="' . $title . '" data-column="type">'
 631         . __('Type') . '<div class="sorticon"></div></th>'
 632         . '<th class="' . $class . '" title="' . $title . '" data-column="length">'
 633         . __('Length/Values') . '<div class="sorticon"></div></th>'
 634         . '<th class="' . $class . '" title="' . $title . '" data-column="collation"'
 635         . '>' . __('Collation') . '<div class="sorticon"></div></th>'
 636         . '<th class="' . $class . '" title="' . $title . '" data-column="isnull">'
 637         . __('Null') . '<div class="sorticon"></div></th>'
 638         . '<th class="' . $class . '" title="' . $title . '" data-column="extra">'
 639         . __('Extra') . '<div class="sorticon"></div></th>'
 640         . '<th class="' . $class . '" title="' . $title . '" data-column="default">'
 641         . __('Default') . '<div class="sorticon"></div></th>'
 642         . '</tr>';
 643     $tableheader .= '</thead>';
 644     return $tableheader;
 645 }
 646
 647 /**
 648  * build the dropdown select html for tables of given database
 649  *
 650  * @param string $db current database
 651  *
 652  * @return html dropdown for selecting table
 653  */
 654 function PMA_getHTMLforTableDropdown($db)
 655 {
 656     $GLOBALS['dbi']->selectDb($db, $GLOBALS['userlink']);
 657     $tables = $GLOBALS['dbi']->getTables($db, $GLOBALS['userlink']);
 658     $selectHtml = '<select name="table-select" id="table-select">'
 659         . '<option value="" disabled="disabled" selected="selected">'
 660         . __('Select a table') . '</option>';
 661     foreach ($tables as $table) {
 662         $selectHtml .= '<option value="' . htmlspecialchars($table) . '">'
 663             . htmlspecialchars($table) . '</option>';
 664     }
 665     $selectHtml .= '</select>';
 666     return $selectHtml;
 667 }
 668
 669 /**
 670  * build dropdown select html to select column in selected table,
 671  * include only columns which are not already in central list
 672  *
 673  * @param string $db           current database to which selected table belongs
 674  * @param string $selected_tbl selected table
 675  *
 676  * @return html to select column
 677  */
 678 function PMA_getHTMLforColumnDropdown($db, $selected_tbl)
 679 {
 680     $existing_cols = PMA_getCentralColumnsFromTable($db, $selected_tbl);
 681     $GLOBALS['dbi']->selectDb($db, $GLOBALS['userlink']);
 682     $columns = (array) $GLOBALS['dbi']->getColumnNames(
 683         $db, $selected_tbl, $GLOBALS['userlink']
 684     );
 685     $selectColHtml = "";
 686     foreach ($columns as $column) {
 687         if (!in_array($column, $existing_cols)) {
 688             $selectColHtml .= '<option value="' . htmlspecialchars($column) . '">'
 689                 . htmlspecialchars($column)
 690                 . '</option>';
 691         }
 692     }
 693     return $selectColHtml;
 694 }
 695
 696 /**
 697  * html to display the form that let user to add a column on Central columns page
 698  *
 699  * @param int    $total_rows total number of rows in complete result set
 700  * @param int    $pos        offset of first result with complete result set
 701  * @param string $db         current database
 702  *
 703  * @return html to add a column in the central list
 704  */
 705 function PMA_getHTMLforAddCentralColumn($total_rows, $pos, $db)
 706 {
 707     $columnAdd = '<table style="display:inline-block;margin-left:1%;max-width:50%" '
 708         . 'class="navigation nospacing nopadding">'
 709         . '<tr>'
 710         . '<td class="navigation_separator"></td>'
 711         . '<td style="padding:1.5% 0em">'
 712         . PMA_Util::getIcon(
 713             'centralColumns_add.png',
 714             __('Add column')
 715         )
 716         . '<form id="add_column" action="db_central_columns.php" method="post">'
 717         . PMA_URL_getHiddenInputs(
 718             $db
 719         )
 720         . '<input type="hidden" name="add_column" value="add">'
 721         . '<input type="hidden" name="pos" value="' . $pos . '" />'
 722         . '<input type="hidden" name="total_rows" value="' . $total_rows . '"/>'
 723         . PMA_getHTMLforTableDropdown($db)
 724         . '<select name="column-select" id="column-select">'
 725         . '<option value="" selected="selected">'
 726         . __('Select a column.') . '</option>'
 727         . '</select></form>'
 728         . '</td>'
 729         . '<td class="navigation_separator"></td>'
 730         . '</tr>'
 731         . '</table>';
 732
 733     return $columnAdd;
 734 }
 735
 736 /**
 737  * build html for a row in central columns table
 738  *
 739  * @param array   $row     array contains complete information of
 740  * a particular row of central list table
 741  * @param boolean $odd_row set true if the row is at odd number position
 742  * @param int     $row_num position the row in the table
 743  * @param string  $db      current database
 744  *
 745  * @return html of a particular row in the central columns table.
 746  */
 747 function PMA_getHTMLforCentralColumnsTableRow($row, $odd_row, $row_num, $db)
 748 {
 749     $tableHtml = '<tr data-rownum="' . $row_num . '" id="f_' . $row_num . '" '
 750         . 'class="' . ($odd_row ? 'odd' : 'even') . '">'
 751         . PMA_URL_getHiddenInputs(
 752             $db
 753         )
 754         . '<input type="hidden" name="edit_save" value="save">'
 755         . '<td id="edit_' . $row_num . '" class="edit center">'
 756         . '<a href="#">' . PMA_Util::getIcon('b_edit.png', __('Edit')) . '</a></td>'
 757         . '<td class="del_row" data-rownum = "' . $row_num . '">'
 758         . '<a hrf="#">' . PMA_Util::getIcon('b_drop.png', __('Delete')) . '</a>'
 759         . '<input type="submit" data-rownum = "' . $row_num . '"'
 760         . ' class="edit_cancel_form" value="Cancel"></td>'
 761         . '<td id="save_' . $row_num . '" style="display:none">'
 762         . '<input type="submit" data-rownum = "' . $row_num . '"'
 763         . ' class="edit_save_form" value="Save"></td>';
 764
 765     $tableHtml .=
 766         '<td name="col_name" class="nowrap">'
 767         . '<span>' . htmlspecialchars($row['col_name']) . '</span>'
 768         . '<input name="orig_col_name" type="hidden" '
 769         . 'value="' . htmlspecialchars($row['col_name']) . '">'
 770         . PMA_getHtmlForColumnName(
 771             $row_num, 0, 0, array('Field'=>$row['col_name']),
 772             array('central_columnswork'=>false)
 773         )
 774         . '</td>';
 775     $tableHtml .=
 776         '<td name = "col_type" class="nowrap"><span>'
 777         . htmlspecialchars($row['col_type']) . '</span>'
 778         . PMA_getHtmlForColumnType(
 779             $row_num, 1, 0, /*overload*/mb_strtoupper($row['col_type']), array()
 780         )
 781         . '</td>';
 782     $tableHtml .=
 783         '<td class="nowrap" name="col_length">'
 784         . '<span>' . ($row['col_length']?htmlspecialchars($row['col_length']):"")
 785         . '</span>'
 786         . PMA_getHtmlForColumnLength($row_num, 2, 0, 8, $row['col_length'])
 787         . '</td>';
 788
 789     $tableHtml .=
 790         '<td name="collation" class="nowrap">'
 791         . '<span>' . htmlspecialchars($row['col_collation']) . '</span>'
 792         . PMA_getHtmlForColumnCollation(
 793             $row_num, 3, 0, array('Collation'=>$row['col_collation'])
 794         )
 795         . '</td>';
 796     $tableHtml .=
 797         '<td class="nowrap" name="col_isNull">'
 798         . '<span>' . ($row['col_isNull'] ? __('Yes') : __('No'))
 799         . '</span>'
 800         . PMA_getHtmlForColumnNull($row_num, 4, 0, array('Null'=>$row['col_isNull']))
 801         . '</td>';
 802
 803     $tableHtml .=
 804         '<td class="nowrap" name="col_extra"><span>'
 805         . htmlspecialchars($row['col_extra']) . '</span>'
 806         . '<select name="col_extra"><option value=""></option>'
 807         . '<option value="auto_increment">' . __('auto_increment') . '</option>'
 808         . '<option value="on update CURRENT_TIMESTAMP">'
 809         . __('on update CURRENT_TIMESTAMP') . '</option></select>'
 810         . '</td>';
 811     $meta = array();
 812     if (!isset($row['col_default']) || $row['col_default'] == '') {
 813         $meta['DefaultType'] = 'NONE';
 814     } else {
 815         if ($row['col_default'] == 'CURRENT_TIMESTAMP'
 816             || $row['col_default'] == 'NULL'
 817         ) {
 818             $meta['DefaultType'] = $row['col_default'];
 819         } else {
 820             $meta['DefaultType'] = 'USER_DEFINED';
 821             $meta['DefaultValue'] = $row['col_default'];
 822         }
 823     }
 824     $tableHtml .=
 825         '<td class="nowrap" name="col_default"><span>' . (isset($row['col_default'])
 826         ? htmlspecialchars($row['col_default']) : 'None')
 827         . '</span>'
 828         . PMA_getHtmlForColumnDefault(
 829             $row_num, 5, 0, /*overload*/mb_strtoupper($row['col_type']), '', $meta
 830         )
 831         . '</td>';
 832     $tableHtml .= '</tr>';
 833     return $tableHtml;
 834 }
 835
 836 /**
 837  * get the list of columns in given database excluding
 838  * the columns present in current table
 839  *
 840  * @param string $db    selected database
 841  * @param string $table current table name
 842  *
 843  * @return encoded list of columns present in central list for the given database
 844  */
 845 function PMA_getCentralColumnsListRaw($db, $table)
 846 {
 847     $cfgCentralColumns = PMA_centralColumnsGetParams();
 848     if (empty($cfgCentralColumns)) {
 849         return json_encode(array());
 850     }
 851     $centralTable = $cfgCentralColumns['table'];
 852     if (empty($table) || $table == '') {
 853         $query = 'SELECT * FROM ' . PMA_Util::backquote($centralTable) . ' '
 854                 . 'WHERE db_name = \'' . $db . '\';';
 855     } else {
 856         $GLOBALS['dbi']->selectDb($db, $GLOBALS['userlink']);
 857         $columns = (array) $GLOBALS['dbi']->getColumnNames(
 858             $db, $table, $GLOBALS['userlink']
 859         );
 860         $cols = '';
 861         foreach ($columns as $col_select) {
 862             $cols .= '\'' . PMA_Util::sqlAddSlashes($col_select) . '\',';
 863         }
 864         $cols = trim($cols, ',');
 865         $query = 'SELECT * FROM ' . PMA_Util::backquote($centralTable) . ' '
 866                 . 'WHERE db_name = \'' . $db . '\' '
 867                 . 'AND col_name NOT IN (' . $cols . ');';
 868     }
 869     $GLOBALS['dbi']->selectDb($cfgCentralColumns['db'], $GLOBALS['controllink']);
 870     $columns_list = (array)$GLOBALS['dbi']->fetchResult(
 871         $query, null, null, $GLOBALS['controllink']
 872     );
 873     return json_encode($columns_list);
 874 }
 875
 876 /**
 877  * build html for adding a new user defined column to central list
 878  *
 879  * @param string $db current database
 880  *
 881  * @return html of the form to let user add a new user defined column to the list
 882  */
 883 function PMA_getHTMLforAddNewColumn($db)
 884 {
 885     $addNewColumn = '<div id="add_col_div"><a href="#">'
 886         . '<span>+</span> ' . __('Add new column') . '</a>'
 887         . '<form id="add_new" style="min-width:100%;display:none" '
 888         . 'method="post" action="db_central_columns.php">'
 889         . PMA_URL_getHiddenInputs(
 890             $db
 891         )
 892         . '<input type="hidden" name="add_new_column" value="add_new_column">'
 893         . '<table>';
 894     $addNewColumn .= PMA_getCentralColumnsTableHeader();
 895     $addNewColumn .= '<tr>'
 896         . '<td name="col_name" class="nowrap">'
 897         .  PMA_getHtmlForColumnName(
 898             0, 0, 0, array(), array('central_columnswork'=>false)
 899         )
 900         . '</td>'
 901         . '<td name = "col_type" class="nowrap">'
 902         .  PMA_getHtmlForColumnType(0, 1, 0, '', array())
 903         . '</td>'
 904         . '<td class="nowrap" name="col_length">'
 905         . PMA_getHtmlForColumnLength(0, 2, 0, 8, '')
 906         . '</td>'
 907         . '<td name="collation" class="nowrap">'
 908         . PMA_getHtmlForColumnCollation(
 909             0, 3, 0, array()
 910         )
 911         . '</td>'
 912         . '<td class="nowrap" name="col_isNull">'
 913         . PMA_getHtmlForColumnNull(0, 4, 0, array())
 914         . '</td>'
 915         . '<td class="nowrap" name="col_extra">'
 916         . '<select name="col_extra"><option value="">'
 917         . '<option value="auto_increment">' . __('auto_increment') . '</option>'
 918         . '<option value="on update CURRENT_TIMESTAMP">'
 919         . __('on update CURRENT_TIMESTAMP') . '</option></select>'
 920         . '</td>'
 921         . '<td class="nowrap" name="col_default">'
 922         . PMA_getHtmlForColumnDefault(0, 5, 0, '', '', array())
 923         . '</td>'
 924         . ' <td>'
 925         . '<input id="add_column_save" type="submit" '
 926         . ' value="Save"/></td>'
 927         . '</tr>';
 928     $addNewColumn .= '</table></form></div>';
 929     return $addNewColumn;
 930 }
 931 ?>