Translated using Weblate (Slovenian)
[phpmyadmin.git] / libraries / central_columns.lib.php
blob9781f679b684d5850642a5eaf51861ac7f5164ea
1 <?php
2 /* vim: set expandtab sw=4 ts=4 sts=4: */
3 /**
4 * Functions for displaying user preferences pages
6 * @package PhpMyAdmin
7 */
8 if (! defined('PHPMYADMIN')) {
9 exit;
12 require_once './libraries/Template.class.php';
14 /**
15 * Defines the central_columns parameters for the current user
17 * @return array the central_columns parameters for the current user
18 * @access public
20 function PMA_centralColumnsGetParams()
22 static $cfgCentralColumns = null;
24 if (null !== $cfgCentralColumns) {
25 return $cfgCentralColumns;
28 $cfgRelation = PMA_getRelationsParam();
30 if (isset($cfgRelation['central_columnswork'])
31 && $cfgRelation['central_columnswork']
32 ) {
33 $cfgCentralColumns = array(
34 'user' => $GLOBALS['cfg']['Server']['user'],
35 'db' => $cfgRelation['db'],
36 'table' => $cfgRelation['central_columns'],
38 } else {
39 $cfgCentralColumns = false;
42 return $cfgCentralColumns;
45 /**
46 * get $num columns of given database from central columns list
47 * starting at offset $from
49 * @param string $db selected database
50 * @param int $from starting offset of first result
51 * @param int $num maximum number of results to return
53 * @return array list of $num columns present in central columns list
54 * starting at offset $from for the given database
56 function PMA_getColumnsList($db, $from=0, $num=25)
58 $cfgCentralColumns = PMA_centralColumnsGetParams();
59 if (empty($cfgCentralColumns)) {
60 return array();
62 $pmadb = $cfgCentralColumns['db'];
63 $GLOBALS['dbi']->selectDb($pmadb, $GLOBALS['controllink']);
64 $central_list_table = $cfgCentralColumns['table'];
65 //get current values of $db from central column list
66 if ($num == 0) {
67 $query = 'SELECT * FROM ' . PMA_Util::backquote($central_list_table) . ' '
68 . 'WHERE db_name = \'' . $db . '\';';
69 } else {
70 $query = 'SELECT * FROM ' . PMA_Util::backquote($central_list_table) . ' '
71 . 'WHERE db_name = \'' . $db . '\' '
72 . 'LIMIT ' . $from . ', ' . $num . ';';
74 $has_list = (array) $GLOBALS['dbi']->fetchResult(
75 $query, null, null, $GLOBALS['controllink']
77 PMA_handleColumnExtra($has_list);
78 return $has_list;
81 /**
82 * get the number of columns present in central list for given db
84 * @param string $db current database
86 * @return int number of columns in central list of columns for $db
88 function PMA_getCentralColumnsCount($db)
90 $cfgCentralColumns = PMA_centralColumnsGetParams();
91 if (empty($cfgCentralColumns)) {
92 return 0;
94 $pmadb = $cfgCentralColumns['db'];
95 $GLOBALS['dbi']->selectDb($pmadb, $GLOBALS['controllink']);
96 $central_list_table = $cfgCentralColumns['table'];
97 $query = 'SELECT count(db_name) FROM ' .
98 PMA_Util::backquote($central_list_table) . ' '
99 . 'WHERE db_name = \'' . $db . '\';';
100 $res = $GLOBALS['dbi']->fetchResult(
101 $query, null, null, $GLOBALS['controllink']
103 if (isset($res[0])) {
104 return $res[0];
105 } else {
106 return 0;
110 * return the existing columns in central list among the given list of columns
112 * @param string $db the selected database
113 * @param string $cols comma separated list of given columns
114 * @param boolean $allFields set if need all the fields of existing columns,
115 * otherwise only column_name is returned
117 * @return array list of columns in central columns among given set of columns
119 function PMA_findExistingColNames($db, $cols, $allFields=false)
121 $cfgCentralColumns = PMA_centralColumnsGetParams();
122 if (empty($cfgCentralColumns)) {
123 return array();
125 $pmadb = $cfgCentralColumns['db'];
126 $GLOBALS['dbi']->selectDb($pmadb, $GLOBALS['controllink']);
127 $central_list_table = $cfgCentralColumns['table'];
128 if ($allFields) {
129 $query = 'SELECT * FROM ' . PMA_Util::backquote($central_list_table) . ' '
130 . 'WHERE db_name = \'' . $db . '\' AND col_name IN (' . $cols . ');';
131 $has_list = (array) $GLOBALS['dbi']->fetchResult(
132 $query, null, null, $GLOBALS['controllink']
134 PMA_handleColumnExtra($has_list);
135 } else {
136 $query = 'SELECT col_name FROM '
137 . PMA_Util::backquote($central_list_table) . ' '
138 . 'WHERE db_name = \'' . $db . '\' AND col_name IN (' . $cols . ');';
139 $has_list = (array) $GLOBALS['dbi']->fetchResult(
140 $query, null, null, $GLOBALS['controllink']
144 return $has_list;
148 * return error message to be displayed if central columns
149 * configuration storage is not completely configured
151 * @return PMA_Message
153 function PMA_configErrorMessage()
155 return PMA_Message::error(
157 'The configuration storage is not ready for the central list'
158 . ' of columns feature.'
164 * build the insert query for central columns list given PMA storage
165 * db, central_columns table, column name and corresponding definition to be added
167 * @param string $column column to add into central list
168 * @param array $def list of attributes of the column being added
169 * @param string $db PMA configuration storage database name
170 * @param string $central_list_table central columns configuration storage table name
172 * @return string query string to insert the given column
173 * with definition into central list
175 function PMA_getInsertQuery($column, $def, $db, $central_list_table)
177 $type = "";
178 $length = 0;
179 $attribute = "";
180 if (isset($def['Type'])) {
181 $extracted_columnspec = PMA_Util::extractColumnSpec($def['Type']);
182 $attribute = trim($extracted_columnspec[ 'attribute']);
183 $type = $extracted_columnspec['type'];
184 $length = $extracted_columnspec['spec_in_brackets'];
186 if (isset($def['Attribute'])) {
187 $attribute = $def['Attribute'];
189 $collation = isset($def['Collation'])?$def['Collation']:"";
190 $isNull = ($def['Null'] == "NO")?0:1;
191 $extra = isset($def['Extra'])?$def['Extra']:"";
192 $default = isset($def['Default'])?$def['Default']:"";
193 $insQuery = 'INSERT INTO '
194 . PMA_Util::backquote($central_list_table) . ' '
195 . 'VALUES ( \'' . PMA_Util::sqlAddSlashes($db) . '\' ,'
196 . '\'' . PMA_Util::sqlAddSlashes($column) . '\',\''
197 . PMA_Util::sqlAddSlashes($type) . '\','
198 . '\'' . PMA_Util::sqlAddSlashes($length) . '\',\''
199 . PMA_Util::sqlAddSlashes($collation) . '\','
200 . '\'' . PMA_Util::sqlAddSlashes($isNull) . '\','
201 . '\'' . implode(',', array($extra, $attribute))
202 . '\',\'' . PMA_Util::sqlAddSlashes($default) . '\');';
203 return $insQuery;
207 * If $isTable is true then unique columns from given tables as $field_select
208 * are added to central list otherwise the $field_select is considered as
209 * list of columns and these columns are added to central list if not already added
211 * @param array $field_select if $isTable is true selected tables list
212 * otherwise selected columns list
213 * @param bool $isTable if passed array is of tables or columns
214 * @param string $table if $isTable is false,
215 * then table name to which columns belong
217 * @return true|PMA_Message
219 function PMA_syncUniqueColumns($field_select, $isTable=true, $table=null)
221 $cfgCentralColumns = PMA_centralColumnsGetParams();
222 if (empty($cfgCentralColumns)) {
223 return PMA_configErrorMessage();
225 $db = $_REQUEST['db'];
226 $pmadb = $cfgCentralColumns['db'];
227 $central_list_table = $cfgCentralColumns['table'];
228 $GLOBALS['dbi']->selectDb($db, $GLOBALS['userlink']);
229 $existingCols = array();
230 $cols = "";
231 $insQuery = array();
232 $fields = array();
233 $message = true;
234 if ($isTable) {
235 foreach ($field_select as $table) {
236 $fields[$table] = (array) $GLOBALS['dbi']->getColumns(
237 $db, $table, null, true, $GLOBALS['userlink']
239 foreach ($fields[$table] as $field => $def) {
240 $cols .= "'" . PMA_Util::sqlAddSlashes($field) . "',";
244 $has_list = PMA_findExistingColNames($db, trim($cols, ','));
245 foreach ($field_select as $table) {
246 foreach ($fields[$table] as $field => $def) {
247 if (!in_array($field, $has_list)) {
248 $has_list[] = $field;
249 $insQuery[] = PMA_getInsertQuery(
250 $field, $def, $db, $central_list_table
252 } else {
253 $existingCols[] = "'" . $field . "'";
257 } else {
258 if ($table === null) {
259 $table = $_REQUEST['table'];
261 foreach ($field_select as $column) {
262 $cols .= "'" . PMA_Util::sqlAddSlashes($column) . "',";
264 $has_list = PMA_findExistingColNames($db, trim($cols, ','));
265 foreach ($field_select as $column) {
266 if (!in_array($column, $has_list)) {
267 $has_list[] = $column;
268 $field = (array) $GLOBALS['dbi']->getColumns(
269 $db, $table, $column,
270 true, $GLOBALS['userlink']
272 $insQuery[] = PMA_getInsertQuery(
273 $column, $field, $db, $central_list_table
275 } else {
276 $existingCols[] = "'" . $column . "'";
280 if (! empty($existingCols)) {
281 $existingCols = implode(",", array_unique($existingCols));
282 $message = PMA_Message::notice(
283 sprintf(
285 'Could not add %1$s as they already exist in central list!'
286 ), htmlspecialchars($existingCols)
289 $message->addMessage(
290 PMA_Message::notice(
291 "Please remove them first "
292 . "from central list if you want to update above columns"
296 $GLOBALS['dbi']->selectDb($pmadb, $GLOBALS['controllink']);
297 if (! empty($insQuery)) {
298 foreach ($insQuery as $query) {
299 if (!$GLOBALS['dbi']->tryQuery($query, $GLOBALS['controllink'])) {
300 $message = PMA_Message::error(__('Could not add columns!'));
301 $message->addMessage(
302 PMA_Message::rawError(
303 $GLOBALS['dbi']->getError($GLOBALS['controllink'])
306 break;
310 return $message;
314 * if $isTable is true it removes all columns of given tables as $field_select from
315 * central columns list otherwise $field_select is columns list and it removes
316 * given columns if present in central list
318 * @param array $field_select if $isTable selected list of tables otherwise
319 * selected list of columns to remove from central list
320 * @param bool $isTable if passed array is of tables or columns
322 * @return true|PMA_Message
324 function PMA_deleteColumnsFromList($field_select, $isTable=true)
326 $cfgCentralColumns = PMA_centralColumnsGetParams();
327 if (empty($cfgCentralColumns)) {
328 return PMA_configErrorMessage();
330 $db = $_REQUEST['db'];
331 $pmadb = $cfgCentralColumns['db'];
332 $central_list_table = $cfgCentralColumns['table'];
333 $GLOBALS['dbi']->selectDb($db, $GLOBALS['userlink']);
334 $message = true;
335 $colNotExist = array();
336 $fields = array();
337 if ($isTable) {
338 $cols = '';
339 foreach ($field_select as $table) {
340 $fields[$table] = (array) $GLOBALS['dbi']->getColumnNames(
341 $db, $table, $GLOBALS['userlink']
343 foreach ($fields[$table] as $col_select) {
344 $cols .= '\'' . PMA_Util::sqlAddSlashes($col_select) . '\',';
347 $cols = trim($cols, ',');
348 $has_list = PMA_findExistingColNames($db, $cols);
349 foreach ($field_select as $table) {
350 foreach ($fields[$table] as $column) {
351 if (!in_array($column, $has_list)) {
352 $colNotExist[] = "'" . $column . "'";
357 } else {
358 $cols = '';
359 foreach ($field_select as $col_select) {
360 $cols .= '\'' . PMA_Util::sqlAddSlashes($col_select) . '\',';
362 $cols = trim($cols, ',');
363 $has_list = PMA_findExistingColNames($db, $cols);
364 foreach ($field_select as $column) {
365 if (!in_array($column, $has_list)) {
366 $colNotExist[] = "'" . $column . "'";
370 if (!empty($colNotExist)) {
371 $colNotExist = implode(",", array_unique($colNotExist));
372 $message = PMA_Message::notice(
373 sprintf(
375 'Couldn\'t remove Column(s) %1$s '
376 . 'as they don\'t exist in central columns list!'
377 ), htmlspecialchars($colNotExist)
381 $GLOBALS['dbi']->selectDb($pmadb, $GLOBALS['controllink']);
383 $query = 'DELETE FROM ' . PMA_Util::backquote($central_list_table) . ' '
384 . 'WHERE db_name = \'' . $db . '\' AND col_name IN (' . $cols . ');';
386 if (!$GLOBALS['dbi']->tryQuery($query, $GLOBALS['controllink'])) {
387 $message = PMA_Message::error(__('Could not remove columns!'));
388 $message->addMessage('<br />' . htmlspecialchars($cols) . '<br />');
389 $message->addMessage(
390 PMA_Message::rawError(
391 $GLOBALS['dbi']->getError($GLOBALS['controllink'])
395 return $message;
399 * make the columns of given tables consistent with central list of columns.
400 * Updates only those columns which are not being referenced.
402 * @param string $db current database
403 * @param array $selected_tables list of selected tables.
405 * @return true|PMA_Message
407 function PMA_makeConsistentWithList($db, $selected_tables)
409 $message = true;
410 foreach ($selected_tables as $table) {
411 $query = 'ALTER TABLE ' . PMA_Util::backquote($table);
412 $has_list = PMA_getCentralColumnsFromTable($db, $table, true);
413 $GLOBALS['dbi']->selectDb($db, $GLOBALS['userlink']);
414 foreach ($has_list as $column) {
415 $column_status = PMA_checkChildForeignReferences(
416 $db, $table, $column['col_name']
418 //column definition can only be changed if
419 //it is not referenced by another column
420 if ($column_status['isEditable']) {
421 $query .= ' MODIFY ' . PMA_Util::backquote($column['col_name']) . ' '
422 . PMA_Util::sqlAddSlashes($column['col_type']);
423 if ($column['col_length']) {
424 $query .= '(' . $column['col_length'] . ')';
427 $query .= ' ' . $column['col_attribute'];
428 if ($column['col_isNull']) {
429 $query .= ' NULL';
430 } else {
431 $query .= ' NOT NULL';
434 $query .= ' ' . $column['col_extra'];
435 if ($column['col_default']) {
436 if ($column['col_default'] != 'CURRENT_TIMESTAMP') {
437 $query .= ' DEFAULT \'' . PMA_Util::sqlAddSlashes(
438 $column['col_default']
439 ) . '\'';
440 } else {
441 $query .= ' DEFAULT ' . PMA_Util::sqlAddSlashes(
442 $column['col_default']
446 $query .= ',';
449 $query = trim($query, " ,") . ";";
450 if (!$GLOBALS['dbi']->tryQuery($query, $GLOBALS['userlink'])) {
451 if ($message === true) {
452 $message = PMA_Message::error(
453 $GLOBALS['dbi']->getError($GLOBALS['userlink'])
455 } else {
456 $message->addMessage('<br />');
457 $message->addMessage(
458 $GLOBALS['dbi']->getError($GLOBALS['userlink'])
463 return $message;
467 * return the columns present in central list of columns for a given
468 * table of a given database
470 * @param string $db given database
471 * @param string $table given table
472 * @param boolean $allFields set if need all the fields of existing columns,
473 * otherwise only column_name is returned
475 * @return array columns present in central list from given table of given db.
477 function PMA_getCentralColumnsFromTable($db, $table, $allFields=false)
479 $cfgCentralColumns = PMA_centralColumnsGetParams();
480 if (empty($cfgCentralColumns)) {
481 return array();
483 $GLOBALS['dbi']->selectDb($db, $GLOBALS['userlink']);
484 $fields = (array) $GLOBALS['dbi']->getColumnNames(
485 $db, $table, $GLOBALS['userlink']
487 $cols = '';
488 foreach ($fields as $col_select) {
489 $cols .= '\'' . PMA_Util::sqlAddSlashes($col_select) . '\',';
491 $cols = trim($cols, ',');
492 $has_list = PMA_findExistingColNames($db, $cols, $allFields);
493 if (! empty($has_list)) {
494 return (array)$has_list;
495 } else {
496 return array();
501 * update a column in central columns list if a edit is requested
503 * @param string $db current database
504 * @param string $orig_col_name original column name before edit
505 * @param string $col_name new column name
506 * @param string $col_type new column type
507 * @param string $col_attribute new column attribute
508 * @param string $col_length new column length
509 * @param int $col_isNull value 1 if new column isNull is true, 0 otherwise
510 * @param string $collation new column collation
511 * @param string $col_extra new column extra property
512 * @param string $col_default new column default value
514 * @return true|PMA_Message
516 function PMA_updateOneColumn($db, $orig_col_name, $col_name, $col_type,
517 $col_attribute,$col_length, $col_isNull, $collation, $col_extra, $col_default
519 $cfgCentralColumns = PMA_centralColumnsGetParams();
520 if (empty($cfgCentralColumns)) {
521 return PMA_configErrorMessage();
523 $centralTable = $cfgCentralColumns['table'];
524 $GLOBALS['dbi']->selectDb($cfgCentralColumns['db'], $GLOBALS['controllink']);
525 if ($orig_col_name == "") {
526 $def = array();
527 $def['Type'] = $col_type;
528 if ($col_length) {
529 $def['Type'] .= '(' . $col_length . ')';
531 $def['Collation'] = $collation;
532 $def['Null'] = $col_isNull?__('YES'):__('NO');
533 $def['Extra'] = $col_extra;
534 $def['Attribute'] = $col_attribute;
535 $def['Default'] = $col_default;
536 $query = PMA_getInsertQuery($col_name, $def, $db, $centralTable);
537 } else {
538 $query = 'UPDATE ' . PMA_Util::backquote($centralTable)
539 . ' SET col_type = \'' . PMA_Util::sqlAddSlashes($col_type) . '\''
540 . ', col_name = \'' . PMA_Util::sqlAddSlashes($col_name) . '\''
541 . ', col_length = \'' . PMA_Util::sqlAddSlashes($col_length) . '\''
542 . ', col_isNull = ' . $col_isNull
543 . ', col_collation = \'' . PMA_Util::sqlAddSlashes($collation) . '\''
544 . ', col_extra = \''
545 . implode(',', array($col_extra, $col_attribute)) . '\''
546 . ', col_default = \'' . PMA_Util::sqlAddSlashes($col_default) . '\''
547 . ' WHERE db_name = \'' . PMA_Util::sqlAddSlashes($db) . '\' '
548 . 'AND col_name = \'' . PMA_Util::sqlAddSlashes($orig_col_name)
549 . '\'';
551 if (!$GLOBALS['dbi']->tryQuery($query, $GLOBALS['controllink'])) {
552 return PMA_Message::error(
553 $GLOBALS['dbi']->getError($GLOBALS['controllink'])
556 return true;
560 * Update Multiple column in central columns list if a chnage is requested
562 * @return true|PMA_Message
564 function PMA_updateMultipleColumn()
566 $db = $_POST['db'];
567 $col_name = $_POST['field_name'];
568 $orig_col_name = $_POST['orig_col_name'];
569 $col_default = $_POST['field_default_type'];
570 $col_length = $_POST['field_length'];
571 $col_attribute = $_POST['field_attribute'];
572 $col_type = $_POST['field_type'];
573 $collation = $_POST['field_collation'];
574 $col_isNull = array();
575 $col_extra = array();
576 $num_central_fields = count($orig_col_name);
577 for ($i = 0; $i < $num_central_fields ; $i++) {
578 $col_isNull[$i] = isset($_POST['field_null'][$i]) ? 1 : 0;
579 $col_extra[$i] = isset($_POST['col_extra'][$i])
580 ? $_POST['col_extra'][$i] : '';
582 if ($col_default[$i] == 'NONE') {
583 $col_default[$i] = "";
584 } else if ($col_default[$i] == 'USER_DEFINED') {
585 $col_default[$i] = $_POST['field_default_value'][$i];
588 $message = PMA_updateOneColumn(
589 $db, $orig_col_name[$i], $col_name[$i], $col_type[$i],
590 $col_attribute[$i], $col_length[$i], $col_isNull[$i], $collation[$i],
591 $col_extra[$i], $col_default[$i]
593 if (!is_bool($message)) {
594 return $message;
597 return true;
601 * get the html for table navigation in Central columns page
603 * @param int $total_rows total number of rows in complete result set
604 * @param int $pos offset of first result with complete result set
605 * @param string $db current database
607 * @return html for table navigation in Central columns page
609 function PMA_getHTMLforTableNavigation($total_rows, $pos, $db)
611 $max_rows = $GLOBALS['cfg']['MaxRows'];
612 $pageNow = ($pos / $max_rows) + 1;
613 $nbTotalPage = ceil($total_rows / $max_rows);
614 $table_navigation_html = '<table style="display:inline-block;max-width:49%" '
615 . 'class="navigation nospacing nopadding">'
616 . '<tr>'
617 . '<td class="navigation_separator"></td>';
618 if ($pos - $max_rows >= 0) {
619 $table_navigation_html .= '<td>'
620 . '<form action="db_central_columns.php" method="post">'
621 . PMA_URL_getHiddenInputs(
624 . '<input type="hidden" name="pos" value="' . ($pos - $max_rows) . '" />'
625 . '<input type="hidden" name="total_rows" value="' . $total_rows . '"/>'
626 . '<input type="submit" name="navig"'
627 . ' class="ajax" '
628 . 'value="&lt" />'
629 . '</form>'
630 . '</td>';
632 if ($nbTotalPage > 1) {
633 $table_navigation_html .= '<td>';
634 $table_navigation_html .= '<form action="db_central_columns.php'
635 . '" method="post">'
636 . PMA_URL_getHiddenInputs(
639 . '<input type="hidden" name="total_rows" value="' . $total_rows . '"/>';
640 $table_navigation_html .= PMA_Util::pageselector(
641 'pos', $max_rows, $pageNow, $nbTotalPage
643 $table_navigation_html .= '</form>'
644 . '</td>';
646 if ($pos + $max_rows < $total_rows) {
647 $table_navigation_html .= '<td>'
648 . '<form action="db_central_columns.php" method="post">'
649 . PMA_URL_getHiddenInputs(
652 . '<input type="hidden" name="pos" value="' . ($pos + $max_rows) . '" />'
653 . '<input type="hidden" name="total_rows" value="' . $total_rows . '"/>'
654 . '<input type="submit" name="navig"'
655 . ' class="ajax" '
656 . 'value="&gt" />'
657 . '</form>'
658 . '</td>';
660 $table_navigation_html .= '</form>'
661 . '</td>'
662 . '<td class="navigation_separator"></td>'
663 . '<td>'
664 . '<span>' . __('Filter rows') . ':</span>'
665 . '<input type="text" class="filter_rows" placeholder="'
666 . __('Search this table') . '">'
667 . '</td>'
668 . '<td class="navigation_separator"></td>'
669 . '</tr>'
670 . '</table>';
672 return $table_navigation_html;
676 * function generate and return the table header for central columns page
678 * @param string $class styling class of 'th' elements
679 * @param string $title title of the 'th' elements
680 * @param integer $actionCount number of actions
682 * @return html for table header in central columns view/edit page
684 function PMA_getCentralColumnsTableHeader($class='', $title='', $actionCount=0)
686 $action = '';
687 if ($actionCount > 0) {
688 $action .= '<th class="column_action" colspan="' . $actionCount . '">'
689 . __('Action') . '</th>';
691 $tableheader = '<thead>';
692 $tableheader .= '<tr>'
693 . '<th class="' . $class . '"></th>'
694 . '<th class="" style="display:none"></th>'
695 . $action
696 . '<th class="' . $class . '" title="' . $title . '" data-column="name">'
697 . __('Name') . '<div class="sorticon"></div></th>'
698 . '<th class="' . $class . '" title="' . $title . '" data-column="type">'
699 . __('Type') . '<div class="sorticon"></div></th>'
700 . '<th class="' . $class . '" title="' . $title . '" data-column="length">'
701 . __('Length/Values') . '<div class="sorticon"></div></th>'
702 . '<th class="' . $class . '" title="' . $title . '" data-column="default">'
703 . __('Default') . '<div class="sorticon"></div></th>'
704 . '<th class="' . $class . '" title="' . $title . '" data-column="collation"'
705 . '>' . __('Collation') . '<div class="sorticon"></div></th>'
706 . '<th class="' . $class . '" title="' . $title
707 . '" data-column="attribute">'
708 . __('Attribute') . '<div class="sorticon"></div></th>'
709 . '<th class="' . $class . '" title="' . $title . '" data-column="isnull">'
710 . __('Null') . '<div class="sorticon"></div></th>'
711 . '<th class="' . $class . '" title="' . $title . '" data-column="extra">'
712 . __('A_I') . '<div class="sorticon"></div></th>'
713 . '</tr>';
714 $tableheader .= '</thead>';
715 return $tableheader;
719 * Function generate and return the table header for
720 * multiple edit central columns page
722 * @param array $header_cells headers list
724 * @return string html for table header in central columns multi edit page
726 function PMA_getCentralColumnsEditTableHeader($header_cells)
728 $html = '<table id="table_columns" class="noclick"'
729 . ' style="min-width: 100%;">';
730 $html .= '<caption class="tblHeaders">' . __('Structure');
731 $html .= '<tr>';
732 foreach ($header_cells as $header_val) {
733 $html .= '<th>' . $header_val . '</th>';
735 $html .= '</tr>';
736 return $html;
740 * build the dropdown select html for tables of given database
742 * @param string $db current database
744 * @return html dropdown for selecting table
746 function PMA_getHTMLforTableDropdown($db)
748 $GLOBALS['dbi']->selectDb($db, $GLOBALS['userlink']);
749 $tables = $GLOBALS['dbi']->getTables($db, $GLOBALS['userlink']);
750 $selectHtml = '<select name="table-select" id="table-select">'
751 . '<option value="" disabled="disabled" selected="selected">'
752 . __('Select a table') . '</option>';
753 foreach ($tables as $table) {
754 $selectHtml .= '<option value="' . htmlspecialchars($table) . '">'
755 . htmlspecialchars($table) . '</option>';
757 $selectHtml .= '</select>';
758 return $selectHtml;
762 * build dropdown select html to select column in selected table,
763 * include only columns which are not already in central list
765 * @param string $db current database to which selected table belongs
766 * @param string $selected_tbl selected table
768 * @return html to select column
770 function PMA_getHTMLforColumnDropdown($db, $selected_tbl)
772 $existing_cols = PMA_getCentralColumnsFromTable($db, $selected_tbl);
773 $GLOBALS['dbi']->selectDb($db, $GLOBALS['userlink']);
774 $columns = (array) $GLOBALS['dbi']->getColumnNames(
775 $db, $selected_tbl, $GLOBALS['userlink']
777 $selectColHtml = "";
778 foreach ($columns as $column) {
779 if (!in_array($column, $existing_cols)) {
780 $selectColHtml .= '<option value="' . htmlspecialchars($column) . '">'
781 . htmlspecialchars($column)
782 . '</option>';
785 return $selectColHtml;
789 * html to display the form that let user to add a column on Central columns page
791 * @param int $total_rows total number of rows in complete result set
792 * @param int $pos offset of first result with complete result set
793 * @param string $db current database
795 * @return html to add a column in the central list
797 function PMA_getHTMLforAddCentralColumn($total_rows, $pos, $db)
799 $columnAdd = '<table style="display:inline-block;margin-left:1%;max-width:50%" '
800 . 'class="navigation nospacing nopadding">'
801 . '<tr>'
802 . '<td class="navigation_separator"></td>'
803 . '<td style="padding:1.5% 0em">'
804 . PMA_Util::getIcon(
805 'centralColumns_add.png',
806 __('Add column')
808 . '<form id="add_column" action="db_central_columns.php" method="post">'
809 . PMA_URL_getHiddenInputs(
812 . '<input type="hidden" name="add_column" value="add">'
813 . '<input type="hidden" name="pos" value="' . $pos . '" />'
814 . '<input type="hidden" name="total_rows" value="' . $total_rows . '"/>'
815 . PMA_getHTMLforTableDropdown($db)
816 . '<select name="column-select" id="column-select">'
817 . '<option value="" selected="selected">'
818 . __('Select a column.') . '</option>'
819 . '</select></form>'
820 . '</td>'
821 . '<td class="navigation_separator"></td>'
822 . '</tr>'
823 . '</table>';
825 return $columnAdd;
829 * build html for a row in central columns table
831 * @param array $row array contains complete information of
832 * a particular row of central list table
833 * @param boolean $odd_row set true if the row is at odd number position
834 * @param int $row_num position the row in the table
835 * @param string $db current database
837 * @return html of a particular row in the central columns table.
839 function PMA_getHTMLforCentralColumnsTableRow($row, $odd_row, $row_num, $db)
841 $tableHtml = '<tr data-rownum="' . $row_num . '" id="f_' . $row_num . '" '
842 . 'class="' . ($odd_row ? 'odd' : 'even') . '">'
843 . PMA_URL_getHiddenInputs(
846 . '<input type="hidden" name="edit_save" value="save">'
847 . '<td class="nowrap">'
848 . '<input type="checkbox" class="checkall" name="selected_fld[]" '
849 . 'value="' . htmlspecialchars($row['col_name']) . '" '
850 . 'id="checkbox_row_' . $row_num . '"/>'
851 . '</td>'
852 . '<td id="edit_' . $row_num . '" class="edit center">'
853 . '<a href="#">' . PMA_Util::getIcon('b_edit.png', __('Edit')) . '</a></td>'
854 . '<td class="del_row" data-rownum = "' . $row_num . '">'
855 . '<a hrf="#">' . PMA_Util::getIcon('b_drop.png', __('Delete')) . '</a>'
856 . '<input type="submit" data-rownum = "' . $row_num . '"'
857 . ' class="edit_cancel_form" value="Cancel"></td>'
858 . '<td id="save_' . $row_num . '" style="display:none">'
859 . '<input type="submit" data-rownum = "' . $row_num . '"'
860 . ' class="edit_save_form" value="Save"></td>';
862 $tableHtml .=
863 '<td name="col_name" class="nowrap">'
864 . '<span>' . htmlspecialchars($row['col_name']) . '</span>'
865 . '<input name="orig_col_name" type="hidden" '
866 . 'value="' . htmlspecialchars($row['col_name']) . '">'
867 . PMA\Template::get('columns_definitions/column_name')
868 ->render(array(
869 'columnNumber' => $row_num,
870 'ci' => 0,
871 'ci_offset' => 0,
872 'columnMeta' => array(
873 'Field'=>$row['col_name']
875 'cfgRelation' => array(
876 'central_columnswork' => false
879 . '</td>';
880 $tableHtml .=
881 '<td name = "col_type" class="nowrap"><span>'
882 . htmlspecialchars($row['col_type']) . '</span>'
883 . PMA\Template::get('columns_definitions/column_type')
884 ->render(array(
885 'columnNumber' => $row_num,
886 'ci' => 1,
887 'ci_offset' => 0,
888 'type_upper' => /*overload*/mb_strtoupper($row['col_type']),
889 'columnMeta' => array()
891 . '</td>';
892 $tableHtml .=
893 '<td class="nowrap" name="col_length">'
894 . '<span>' . ($row['col_length']?htmlspecialchars($row['col_length']):"")
895 . '</span>'
896 . PMA\Template::get('columns_definitions/column_length')->render(
897 array(
898 'columnNumber' => $row_num,
899 'ci' => 2,
900 'ci_offset' => 0,
901 'length_values_input_size' => 8,
902 'length_to_display' => $row['col_length']
905 . '</td>';
907 $meta = array();
908 if (!isset($row['col_default']) || $row['col_default'] == '') {
909 $meta['DefaultType'] = 'NONE';
910 } else {
911 if ($row['col_default'] == 'CURRENT_TIMESTAMP'
912 || $row['col_default'] == 'NULL'
914 $meta['DefaultType'] = $row['col_default'];
915 } else {
916 $meta['DefaultType'] = 'USER_DEFINED';
917 $meta['DefaultValue'] = $row['col_default'];
920 $tableHtml .=
921 '<td class="nowrap" name="col_default"><span>' . (isset($row['col_default'])
922 ? htmlspecialchars($row['col_default']) : 'None')
923 . '</span>'
924 . PMA\Template::get('columns_definitions/column_default')
925 ->render(array(
926 'columnNumber' => $row_num,
927 'ci' => 3,
928 'ci_offset' => 0,
929 'type_upper' => /*overload*/mb_strtoupper($row['col_type']),
930 'columnMeta' => $meta
932 . '</td>';
934 $tableHtml .=
935 '<td name="collation" class="nowrap">'
936 . '<span>' . htmlspecialchars($row['col_collation']) . '</span>'
937 . PMA_generateCharsetDropdownBox(
938 PMA_CSDROPDOWN_COLLATION, 'field_collation[' . $row_num . ']',
939 'field_' . $row_num . '_4', $row['col_collation'], false
941 . '</td>';
942 $tableHtml .=
943 '<td class="nowrap" name="col_attribute">'
944 . '<span>' .
945 ($row['col_attribute']
946 ? htmlspecialchars($row['col_attribute']) : "" )
947 . '</span>'
948 . PMA\Template::get('columns_definitions/column_attribute')
949 ->render(array(
950 'columnNumber' => $row_num,
951 'ci' => 5,
952 'ci_offset' => 0,
953 'extracted_columnspec' => array(),
954 'columnMeta' => $row['col_attribute'],
955 'submit_attribute' => false,
956 'analyzed_sql' => null
958 . '</td>';
959 $tableHtml .=
960 '<td class="nowrap" name="col_isNull">'
961 . '<span>' . ($row['col_isNull'] ? __('Yes') : __('No'))
962 . '</span>'
963 . PMA\Template::get('columns_definitions/column_null')
964 ->render(array(
965 'columnNumber' => $row_num,
966 'ci' => 6,
967 'ci_offset' => 0,
968 'columnMeta' => array(
969 'Null' => $row['col_isNull']
972 . '</td>';
974 $tableHtml .=
975 '<td class="nowrap" name="col_extra"><span>'
976 . htmlspecialchars($row['col_extra']) . '</span>'
977 . PMA\Template::get('columns_definitions/column_extra')->render(
978 array(
979 'columnNumber' => $row_num,
980 'ci' => 7,
981 'ci_offset' => 0,
982 'columnMeta' => array('Extra'=>$row['col_extra'])
985 . '</td>';
987 $tableHtml .= '</tr>';
989 return $tableHtml;
993 * build html for editing a row in central columns table
995 * @param array $row array contains complete information of
996 * a particular row of central list table
997 * @param boolean $odd_row set true if the row is at odd number position
998 * @param int $row_num position the row in the table
1000 * @return html of a particular row in the central columns table.
1002 function PMA_getHTMLforCentralColumnsEditTableRow($row, $odd_row, $row_num)
1004 $tableHtml = '<tr class="' . ($odd_row ? 'odd' : 'even') . '">'
1005 . '<input name="orig_col_name[' . $row_num . ']" type="hidden" '
1006 . 'value="' . htmlspecialchars($row['col_name']) . '">'
1007 . '<td name="col_name" class="nowrap">'
1008 . PMA\Template::get('columns_definitions/column_name')
1009 ->render(array(
1010 'columnNumber' => $row_num,
1011 'ci' => 0,
1012 'ci_offset' => 0,
1013 'columnMeta' => array(
1014 'Field' => $row['col_name']
1016 'cfgRelation' => array(
1017 'central_columnswork' => false
1020 . '</td>';
1021 $tableHtml .=
1022 '<td name = "col_type" class="nowrap">'
1023 . PMA\Template::get('columns_definitions/column_type')
1024 ->render(array(
1025 'columnNumber' => $row_num,
1026 'ci' => 1,
1027 'ci_offset' => 0,
1028 'type_upper' => /*overload*/mb_strtoupper($row['col_type']),
1029 'columnMeta' => array()
1031 . '</td>';
1032 $tableHtml .=
1033 '<td class="nowrap" name="col_length">'
1034 . PMA\Template::get('columns_definitions/column_length')->render(
1035 array(
1036 'columnNumber' => $row_num,
1037 'ci' => 2,
1038 'ci_offset' => 0,
1039 'length_values_input_size' => 8,
1040 'length_to_display' => $row['col_length']
1043 . '</td>';
1044 $meta = array();
1045 if (!isset($row['col_default']) || $row['col_default'] == '') {
1046 $meta['DefaultType'] = 'NONE';
1047 } else {
1048 if ($row['col_default'] == 'CURRENT_TIMESTAMP'
1049 || $row['col_default'] == 'NULL'
1051 $meta['DefaultType'] = $row['col_default'];
1052 } else {
1053 $meta['DefaultType'] = 'USER_DEFINED';
1054 $meta['DefaultValue'] = $row['col_default'];
1057 $tableHtml .=
1058 '<td class="nowrap" name="col_default">'
1059 . PMA\Template::get('columns_definitions/column_default')
1060 ->render(array(
1061 'columnNumber' => $row_num,
1062 'ci' => 3,
1063 'ci_offset' => 0,
1064 'type_upper' => /*overload*/mb_strtoupper($row['col_default']),
1065 'columnMeta' => $meta
1067 . '</td>';
1068 $tableHtml .=
1069 '<td name="collation" class="nowrap">'
1070 . PMA_generateCharsetDropdownBox(
1071 PMA_CSDROPDOWN_COLLATION, 'field_collation[' . $row_num . ']',
1072 'field_' . $row_num . '_4', $row['col_collation'], false
1074 . '</td>';
1075 $tableHtml .=
1076 '<td class="nowrap" name="col_attribute">'
1077 . PMA\Template::get('columns_definitions/column_attribute')
1078 ->render(array(
1079 'columnNumber' => $row_num,
1080 'ci' => 5,
1081 'ci_offset' => 0,
1082 'extracted_columnspec' => array(
1083 'attribute' => $row['col_attribute']
1085 'columnMeta' => array(),
1086 'submit_attribute' => false,
1087 'analyzed_sql' => null
1089 . '</td>';
1090 $tableHtml .=
1091 '<td class="nowrap" name="col_isNull">'
1092 . PMA\Template::get('columns_definitions/column_null')
1093 ->render(array(
1094 'columnNumber' => $row_num,
1095 'ci' => 6,
1096 'ci_offset' => 0,
1097 'columnMeta' => array(
1098 'Null' => $row['col_isNull']
1101 . '</td>';
1103 $tableHtml .=
1104 '<td class="nowrap" name="col_extra">'
1105 . PMA\Template::get('columns_definitions/column_extra')->render(
1106 array(
1107 'columnNumber' => $row_num,
1108 'ci' => 7,
1109 'ci_offset' => 0,
1110 'columnMeta' => array('Extra' => $row['col_extra'])
1113 . '</td>';
1114 $tableHtml .= '</tr>';
1115 return $tableHtml;
1119 * get the list of columns in given database excluding
1120 * the columns present in current table
1122 * @param string $db selected database
1123 * @param string $table current table name
1125 * @return encoded list of columns present in central list for the given database
1127 function PMA_getCentralColumnsListRaw($db, $table)
1129 $cfgCentralColumns = PMA_centralColumnsGetParams();
1130 if (empty($cfgCentralColumns)) {
1131 return json_encode(array());
1133 $centralTable = $cfgCentralColumns['table'];
1134 if (empty($table) || $table == '') {
1135 $query = 'SELECT * FROM ' . PMA_Util::backquote($centralTable) . ' '
1136 . 'WHERE db_name = \'' . $db . '\';';
1137 } else {
1138 $GLOBALS['dbi']->selectDb($db, $GLOBALS['userlink']);
1139 $columns = (array) $GLOBALS['dbi']->getColumnNames(
1140 $db, $table, $GLOBALS['userlink']
1142 $cols = '';
1143 foreach ($columns as $col_select) {
1144 $cols .= '\'' . PMA_Util::sqlAddSlashes($col_select) . '\',';
1146 $cols = trim($cols, ',');
1147 $query = 'SELECT * FROM ' . PMA_Util::backquote($centralTable) . ' '
1148 . 'WHERE db_name = \'' . $db . '\'';
1149 if ($cols) {
1150 $query .= ' AND col_name NOT IN (' . $cols . ')';
1152 $query .= ';';
1154 $GLOBALS['dbi']->selectDb($cfgCentralColumns['db'], $GLOBALS['controllink']);
1155 $columns_list = (array)$GLOBALS['dbi']->fetchResult(
1156 $query, null, null, $GLOBALS['controllink']
1158 PMA_handleColumnExtra($columns_list);
1159 return json_encode($columns_list);
1163 * Get HTML for "check all" check box with "with selected" dropdown
1165 * @param string $pmaThemeImage pma theme image url
1166 * @param string $text_dir url for text directory
1168 * @return string $html_output
1170 function PMA_getCentralColumnsTableFooter($pmaThemeImage, $text_dir)
1172 $html_output = PMA_Util::getWithSelected(
1173 $pmaThemeImage, $text_dir, "tableslistcontainer"
1175 $html_output .= PMA_Util::getButtonOrImage(
1176 'edit_central_columns', 'mult_submit change_central_columns',
1177 'submit_mult_change', __('Edit'), 'b_edit.png', 'edit central columns'
1179 $html_output .= PMA_Util::getButtonOrImage(
1180 'delete_central_columns', 'mult_submit',
1181 'submit_mult_central_columns_remove',
1182 __('Delete'), 'b_drop.png',
1183 'remove_from_central_columns'
1185 return $html_output;
1189 * function generate and return the table footer for
1190 * multiple edit central columns page
1192 * @return html for table footer in central columns multi edit page
1194 function PMA_getCentralColumnsEditTableFooter()
1196 $html_output = '<fieldset class="tblFooters">'
1197 . '<input type="submit" '
1198 . 'name="save_multi_central_column_edit" value="' . __('Save') . '" />'
1199 . '</fieldset>';
1200 return $html_output;
1203 * Column `col_extra` is used to store both extra and attributes for a column.
1204 * This method separates them.
1206 * @param array &$columns_list columns list
1208 * @return void
1210 function PMA_handleColumnExtra(&$columns_list)
1212 foreach ($columns_list as &$row) {
1213 $vals = explode(',', $row['col_extra']);
1215 if (in_array('BINARY', $vals)) {
1216 $row['col_attribute'] = 'BINARY';
1217 } elseif (in_array('UNSIGNED', $vals)) {
1218 $row['col_attribute'] = 'UNSIGNED';
1219 } elseif (in_array('UNSIGNED ZEROFILL', $vals)) {
1220 $row['col_attribute'] = 'UNSIGNED ZEROFILL';
1221 } elseif (in_array('on update CURRENT_TIMESTAMP', $vals)) {
1222 $row['col_attribute'] = 'on update CURRENT_TIMESTAMP';
1223 } else {
1224 $row['col_attribute'] = '';
1227 if (in_array('auto_increment', $vals)) {
1228 $row['col_extra'] = 'auto_increment';
1229 } else {
1230 $row['col_extra'] = '';
1236 * build html for adding a new user defined column to central list
1238 * @param string $db current database
1240 * @return html of the form to let user add a new user defined column to the list
1242 function PMA_getHTMLforAddNewColumn($db)
1244 $addNewColumn = '<div id="add_col_div"><a href="#">'
1245 . '<span>+</span> ' . __('Add new column') . '</a>'
1246 . '<form id="add_new" style="min-width:100%;display:none" '
1247 . 'method="post" action="db_central_columns.php">'
1248 . PMA_URL_getHiddenInputs(
1251 . '<input type="hidden" name="add_new_column" value="add_new_column">'
1252 . '<table>';
1253 $addNewColumn .= PMA_getCentralColumnsTableHeader();
1254 $addNewColumn .= '<tr>'
1255 . '<td></td>'
1256 . '<td name="col_name" class="nowrap">'
1257 . PMA\Template::get('columns_definitions/column_name')
1258 ->render(array(
1259 'columnNumber' => 0,
1260 'ci' => 0,
1261 'ci_offset' => 0,
1262 'columnMeta' => array(),
1263 'cfgRelation' => array(
1264 'central_columnswork' => false
1267 . '</td>'
1268 . '<td name = "col_type" class="nowrap">'
1269 . PMA\Template::get('columns_definitions/column_type')
1270 ->render(array(
1271 'columnNumber' => 0,
1272 'ci' => 1,
1273 'ci_offset' => 0,
1274 'type_upper' => '',
1275 'columnMeta' => array()
1277 . '</td>'
1278 . '<td class="nowrap" name="col_length">'
1279 . PMA\Template::get('columns_definitions/column_length')->render(
1280 array(
1281 'columnNumber' => 0,
1282 'ci' => 2,
1283 'ci_offset' => 0,
1284 'length_values_input_size' => 8,
1285 'length_to_display' => ''
1288 . '</td>'
1289 . '<td class="nowrap" name="col_default">'
1290 . PMA\Template::get('columns_definitions/column_default')
1291 ->render(array(
1292 'columnNumber' => 0,
1293 'ci' => 3,
1294 'ci_offset' => 0,
1295 'type_upper' => '',
1296 'columnMeta' => array()
1298 . '</td>'
1299 . '<td name="collation" class="nowrap">'
1300 . PMA_generateCharsetDropdownBox(
1301 PMA_CSDROPDOWN_COLLATION, 'field_collation[0]',
1302 'field_0_4', null, false
1304 . '</td>'
1305 . '<td class="nowrap" name="col_attribute">'
1306 . PMA\Template::get('columns_definitions/column_attribute')
1307 ->render(array(
1308 'columnNumber' => 0,
1309 'ci' => 5,
1310 'ci_offset' => 0,
1311 'extracted_columnspec' => array(),
1312 'columnMeta' => array(),
1313 'submit_attribute' => false,
1314 'analyzed_sql' => null
1316 . '</td>'
1317 . '<td class="nowrap" name="col_isNull">'
1318 . PMA\Template::get('columns_definitions/column_null')
1319 ->render(array(
1320 'columnNumber' => 0,
1321 'ci' => 6,
1322 'ci_offset' => 0,
1323 'columnMeta' => array()
1325 . '</td>'
1326 . '<td class="nowrap" name="col_extra">'
1327 . PMA\Template::get('columns_definitions/column_extra')->render(
1328 array(
1329 'columnNumber' => 0,
1330 'ci' => 7,
1331 'ci_offset' => 0,
1332 'columnMeta' => array()
1335 . '</td>'
1336 . ' <td>'
1337 . '<input id="add_column_save" type="submit" '
1338 . ' value="Save"/></td>'
1339 . '</tr>';
1340 $addNewColumn .= '</table></form></div>';
1341 return $addNewColumn;
1345 * Get HTML for editing page central columns
1347 * @param array $selected_fld Array containing the selected fields
1348 * @param string $selected_db String containing the name of database
1350 * @return string HTML for complete editing page for central columns
1352 function PMA_getHTMLforEditingPage($selected_fld,$selected_db)
1354 $html = '<form id="multi_edit_central_columns">';
1355 $header_cells = array(
1356 __('Name'), __('Type'), __('Length/Values'), __('Default'),
1357 __('Collation'), __('Attributes'), __('Null'), __('A_I')
1359 $html .= PMA_getCentralColumnsEditTableHeader($header_cells);
1360 $selected_fld_safe = array();
1361 foreach ($selected_fld as $key) {
1362 $selected_fld_safe[] = PMA_Util::sqlAddSlashes($key);
1364 $columns_list = implode("','", $selected_fld_safe);
1365 $columns_list = "'" . $columns_list . "'";
1366 $list_detail_cols = PMA_findExistingColNames($selected_db, $columns_list, true);
1367 $odd_row = false;
1368 $row_num = 0;
1369 foreach ($list_detail_cols as $row) {
1370 $tableHtmlRow = PMA_getHTMLforCentralColumnsEditTableRow(
1371 $row, $odd_row, $row_num
1373 $html .= $tableHtmlRow;
1374 $odd_row = !$odd_row;
1375 $row_num++;
1377 $html .= '</table>';
1378 $html .= PMA_getCentralColumnsEditTableFooter();
1379 $html .= '</form>';
1380 return $html;