tables.php
<?php
// File tables.php / FirebirdWebAdmin
// Purpose acting with the tables of the selected database
// Author Lutz Brueckner <irie@gmx.de>
// Copyright (c) 2000-2006 by Lutz Brueckner,
// published under the terms of the GNU General Public Licence v.2,
// see file LICENCE for details
require './inc/script_start.inc.php';
//
// script is called from the create table form
//
if (have_panel_permissions($s_login['user'], 'tb_create', true)) {
if (isset($_POST['tb_create_doit'])) {
$s_create_table = $_POST['tb_create_table'];
// this is the first step, $s_create_num is still unset
if (!isset($s_create_num) || empty($s_create_num)) {
if (isset($_POST['tb_create_num']) && (int) $_POST['tb_create_num'] > 0) {
$s_create_num = $_POST['tb_create_num'];
$s_coldefs = array();
}
}
// second step, get the column definitions,
// build the sql-statement and
// create the table
else {
$rwords = array();
for ($idx = 0; $idx < $s_create_num; ++$idx) {
// save the form values into $s_coldefs
save_coldef($idx);
// interbase keywords are not allowed as column names
if (in_array(strtoupper($s_coldefs[$idx]['name']), get_reserved_words(SERVER_FAMILY, SERVER_VERSION))) {
$rwords[] = strtoupper($s_coldefs[$idx]['name']);
}
}
if (count($rwords) > 0) {
$cnt = count($rwords);
$warning = ($cnt == 1) ? sprintf($WARNINGS['NAME_IS_KEYWORD'], $rwords[0])
: sprintf($WARNINGS['NAMES_ARE_KEYWORDS'], implode(', ', $rwords));
} else {
// build the CREATE TABLE sql-statement
$sql = "CREATE TABLE $s_create_table (\n";
// loop over the number of columns
// and build the <col_def> parts of the query
for ($idx = 0; $idx < $s_create_num; ++$idx) {
$sql .= build_coldef($idx);
if (isset($s_coldefs[$idx]['primary'])) { // collect fieldnames for the PRIMARY KEY
$pkeys[] = $s_coldefs[$idx]['name'];
}
$sql .= ",\n";
}
if (isset($pkeys)) {
$sql .= "\tPRIMARY KEY\t(".implode(', ', $pkeys)."),\n";
}
$sql = substr($sql, 0, -2); // remove the last ',\n'
$sql .= "\n);";
}
}
}
}
//
// cancel button on the create table panel was pressed
//
if (isset($_POST['tb_create_cancel'])) {
$s_create_num = '';
$s_coldefs = array();
}
//
// script is called from the modify table form
//
if (isset($_POST['tb_modify_doit'])) {
if ($_POST['tb_modify_name'] != '') {
$s_modify_name = $_POST['tb_modify_name'];
set_panel_title('tb_modify', $ptitle_strings['tb_modify'].': '.$s_modify_name);
}
}
//
// prevent bypassing of the $HIDE_PANEL setting for the modify column actions
//
if ((!have_panel_permissions($s_login['user'], 'tb_modify'))
&& (isset($_POST['tb_modify_del']) || isset($_POST['tb_modadd_doit']) || isset($_POST['tb_modcol_doit']))) {
die('bad boy = ' . isset($_POST['tb_modify_del']) . ' / ' .
isset($_POST['tb_modadd_doit']) .' / '. isset($_POST['tb_modcol_doit']));
}
//
// script is called via the Ready button on the modify table form
//
if (isset($_POST['tb_modify_ready'])) {
$s_modify_name = '';
set_panel_title('tb_modify', $ptitle_strings['tb_modify']);
}
//
// script is called via the Add Column button on the modify table form
//
if (isset($_POST['tb_modify_add'])) {
$s_coldefs['add'] = array();
$col_add_flag = true;
}
//
// add the new column to the table
//
if (isset($_POST['tb_modadd_doit'])) {
save_coldef('add');
// interbase keywords are not allowed as column names
if (in_array(strtoupper($s_coldefs['add']['name']), get_reserved_words(SERVER_FAMILY, SERVER_VERSION))) {
$warning = sprintf($WARNINGS['NAME_IS_KEYWORD'], strtoupper($s_coldefs['add']['name']));
// show the add-column form again
$col_add_flag = true;
} else {
$sql = "ALTER TABLE $s_modify_name ADD \n";
$sql .= build_coldef('add', 'alter');
$sql .= ';';
$add_flag = true;
}
}
//
// script is called via the Delete Column button from the modify table form
//
if (isset($_POST['tb_modify_del'])
&& isset($_POST['tb_modify_dname']) && !empty($_POST['tb_modify_dname'])) {
$cname = $_POST['tb_modify_dname'];
$deps = get_dependencies(OT_RELATION, $s_modify_name, $cname);
if (count($deps) > 0) {
$message = sprintf($MESSAGES['HAVE_DEPENDENCIES'], $tb_strings['Column'], $cname, dependencies_string($deps));
} else {
$drop_statement = count(table_columns($s_modify_name)) > 1
? 'ALTER TABLE '.$s_modify_name.' DROP '.$cname
: 'DROP TABLE '.$s_modify_name;
if ($s_cust['askdel'] == true) {
$s_confirmations['column'] =
array('msg' => sprintf($MESSAGES['CONFIRM_COLUMN_DELETE'], $cname, $s_modify_name),
'sql' => $drop_statement, );
} else {
$sql = $drop_statement;
}
}
}
//
// script is called via the Modify Column button from the modify table form
//
if (isset($_POST['tb_modify_col'])
&& isset($_POST['tb_modify_mname']) && !empty($_POST['tb_modify_mname'])) {
$s_modify_col = $_POST['tb_modify_mname'];
foreach ($s_fields[$s_modify_name] as $field) {
if ($field['name'] == $s_modify_col) {
$s_coldefs['mod'] = $field;
$s_coldefs['mod']['foreign_cols'] = $s_coldefs['mod']['primary_cols'] = $s_coldefs['mod']['unique_cols'] = 0;
if (isset($field['foreign'])) {
$s_coldefs['mod']
= array_merge($s_coldefs['mod'],
get_column_fk_defs($field['foreign'], $s_foreigns[$field['foreign']]['index']));
$s_coldefs['mod']['foreign_cols'] = $s_foreigns[$field['foreign']]['cols'];
}
if (isset($field['primary'])) {
$s_coldefs['mod']['primary_cols'] = $s_primaries[$field['primary']]['cols'];
}
if (isset($field['unique'])) {
$s_coldefs['mod']['unique_cols'] = $s_uniques[$field['unique']]['cols'];
}
$s_coldefs['old'] = $s_coldefs['mod'];
break;
}
}
if (isset($s_coldefs['mod']['domain'])) {
$warning = $WARNINGS['CAN_NOT_ALTER_DOMAINS'];
$s_coldefs = array();
} else {
$s_coldefs['mod']['pk_del'] = $s_coldefs['mod']['fk_del'] = $s_coldefs['mod']['uq_del'] = false;
$col_mod_flag = true;
$js_stack .= js_request_table_columns();
}
}
//
// altering the column definitions
//
if (isset($_POST['tb_modcol_doit'])) {
$mod_flag = false;
$sql = 'ALTER TABLE '.$s_modify_name.' ';
save_coldef('mod');
if (!isset($s_coldefs['mod']['domain']) || $s_coldefs['mod']['domain'] != 'Yes') {
if (datatype_is_modified($s_coldefs['old'], $s_coldefs['mod'])) {
// build sql for altering datatype definition
$sql .= 'ALTER '.$s_modify_col.' TYPE '.build_datatype($s_coldefs['mod']).', ';
$mod_flag = true;
}
// the delete primary key checkbox is checked
if ($s_coldefs['mod']['pk_del'] == true) {
$sql .= 'DROP CONSTRAINT '.$s_coldefs['old']['primary'].', ';
$mod_flag = true;
}
// the delete foreign key checkbox is checked
if ($s_coldefs['mod']['fk_del'] == true) {
$sql .= 'DROP CONSTRAINT '.$s_coldefs['old']['fk_name'].', ';
$mod_flag = true;
}
// the delete unique constraint checkbox is checked
if ($s_coldefs['mod']['uq_del'] == true) {
$sql .= 'DROP CONSTRAINT '.$s_coldefs['old']['unique'].', ';
$mod_flag = true;
}
// delete and recreate the foreign key constraint
elseif (column_fk_is_modified($s_coldefs['old'], $s_coldefs['mod'])) {
if (isset($s_coldefs['old']['fk_name']) && !empty($s_coldefs['old']['fk_name'])) {
$sql .= 'DROP CONSTRAINT '.$s_coldefs['old']['fk_name'].', ';
}
$sql .= 'ADD';
if (!empty($s_coldefs['mod']['fk_name'])) {
$sql .= ' CONSTRAINT '.$s_coldefs['mod']['fk_name'];
}
$sql .= ' FOREIGN KEY ('.$s_coldefs['mod']['name'].')'
.' REFERENCES '.$s_coldefs['mod']['fk_table'].' ';
if (isset($s_coldefs['mod']['fk_column']) && $s_coldefs['mod']['fk_column'] != '') {
$sql .= '('.$s_coldefs['mod']['fk_column'].') ';
}
if (isset($s_coldefs['mod']['on_update']) && $s_coldefs['mod']['on_update'] != '') {
$sql .= ' ON UPDATE '.$s_coldefs['mod']['on_update'];
}
if (isset($s_coldefs['mod']['on_delete']) && $s_coldefs['mod']['on_delete'] != '') {
$sql .= ' ON DELETE '.$s_coldefs['mod']['on_delete'].', ';
}
$mod_flag = true;
}
}
// build sql for altering column domain
if ((isset($s_coldefs['mod']['domain']) && $s_coldefs['mod']['domain'] == 'Yes')
&& $_POST['cd_def_domainmod'] != $s_coldefs['mod']['type']) {
$s_coldefs['mod']['type'] = $_POST['cd_def_domainmod'];
$sql .= 'ALTER '.$s_modify_col.' '.$s_coldefs['mod']['type'].', ';
$mod_flag = true;
}
// build sql for changing column position
if ($_POST['tb_modcol_pos'] != '') {
$sql .= 'ALTER '.$s_modify_col.' POSITION '.$_POST['tb_modcol_pos'].', ';
$mod_flag = true;
}
// build sql for renaming of the column
if ($_POST['cd_def_namemod'] != $s_modify_col) {
// interbase keywords are not allowed as column names
if (in_array(strtoupper($s_coldefs['mod']['name']), get_reserved_words(SERVER_FAMILY, SERVER_VERSION))) {
$warning = sprintf($WARNINGS['NAME_IS_KEYWORD'], strtoupper($s_coldefs['mod']['name']));
} else {
$sql .= 'ALTER '.$s_modify_col.' TO '.$_POST['cd_def_namemod'].', ';
$mod_flag = true;
}
}
if ($mod_flag) {
$sql = substr($sql, 0, -2); // remove the trailing ', '
$sql .= ';';
} else {
$sql = '';
}
if ($s_wt['table'] == $s_modify_name) {
$s_watch_buffer = '';
}
}
//
// script is called from the delete table form
//
if (have_panel_permissions($s_login['user'], 'tb_delete')) {
if (isset($_POST['tb_delete_doit'])
&& isset($_POST['tb_delete_name']) && !empty($_POST['tb_delete_name'])) {
$tname = $_POST['tb_delete_name'];
$deps = get_dependencies(OT_RELATION, $tname);
if (count($deps) > 0) {
$message = sprintf($MESSAGES['HAVE_DEPENDENCIES'], $tb_strings['Table'], $tname, dependencies_string($deps));
} else {
$quote = identifier_quote($s_login['dialect']);
$tstr = $quote.$tname.$quote;
$drop_statement = ($s_tables[$tname]['is_view'] == true) ? 'DROP VIEW '.$tstr : 'DROP TABLE '.$tstr;
if ($s_cust['askdel'] == true) {
$s_confirmations['table'] =
array('msg' => sprintf($MESSAGES['CONFIRM_TABLE_DELETE'], $tname),
'sql' => $drop_statement, );
} else {
$sql = $drop_statement;
}
if ($s_modify_name == $_POST['tb_delete_name']) {
$s_modify_name = '';
}
if ($s_wt['table'] == $_POST['tb_delete_name']) {
$s_wt['table'] = '';
$s_watch_buffer = '';
}
if ($s_enter_name == $_POST['tb_delete_name']) {
$s_enter_name = '';
$s_enter_values = array();
}
}
}
}
//
// drop multi-fields from table (tb_dropfields)
// author: valmor flores (https://github.com/valmorflores/)
//
if (isset($_POST['tb_dropfields_doit'])) {
save_coldef('dropfields');
// Data from confirmation form
$fields = $_POST['tb_checked_fields'];
$tablename = $_POST['tb_table_name'];
// Command
$sql = "ALTER TABLE $tablename \n";
$sql .= " ";
$i = 0;
foreach ($fields as $row_field) {
$sql .= ++$i>1? ',' : ' ';
$sql .= " DROP " . $row_field . "\n";
}
$sql .= ';';
$add_flag = true;
}
//
// drop multi-tables from table (tb_droptables)
// author: valmor flores (https://github.com/valmorflores/)
//
if (isset($_POST['tb_droptables_doit'])) {
save_coldef('droptables');
// Data from confirmation form
$tables = $_POST['tb_checked_tables'];
// Command
$sql = "";
foreach ($tables as $tablename) {
$sql .= "DROP TABLE $tablename;\n";
}
//echo 'Debug:' . $sql;
$add_flag = true;
}
// 'Open All' button
if (isset($_POST['tb_table_open'])) {
$s_tables = toggle_all_tables($s_tables, 'open');
}
// 'Close All' button
if (isset($_POST['tb_table_close'])) {
$s_tables = toggle_all_tables($s_tables, 'close');
}
// deleting a subject is confirmed
if (isset($_POST['confirm_yes'])
&& isset($s_confirmations[$_POST['confirm_subject']])) {
$sql = $s_confirmations[$_POST['confirm_subject']]['sql'];
unset($s_confirmations[$_POST['confirm_subject']]);
}
// deleting a subject is canceled
if (isset($_POST['confirm_no'])
&& isset($s_confirmations[$_POST['confirm_subject']])) {
unset($s_confirmations[$_POST['confirm_subject']]);
}
//
// perform the sql-statement in $sql
//
if ($sql != '') {
if (DEBUG) {
add_debug('$sql: '.$sql, __FILE__, __LINE__);
}
$trans = fbird_trans(TRANS_WRITE, $dbhandle);
if (fbird_query($trans, $sql)) {
fbird_commit($trans);
$s_tables_valid = false;
$s_create_table = '';
$s_create_num = 0;
$s_coldefs = array();
$s_modify_col = '';
} else {
$fb_error = fbird_errmsg();
fbird_rollback($trans);
if (isset($mod_flag) && $mod_flag == true) {
$col_mod_flag = true;
}
if (isset($add_flag) && $add_flag == true) {
$col_add_flag = true;
}
}
}
if (have_panel_permissions($s_login['user'], 'tb_show')) {
// include the javascript for detail requests
$js_stack .= js_request_details();
//
// Reload button from the tb_show panel
//
if (isset($_POST['tb_show_reload'])) {
$s_tables_counts = (boolean) get_request_data('tb_show_counts');
$s_tables_cnames = (boolean) get_request_data('tb_show_cnames');
$s_tables_def = (boolean) get_request_data('tb_show_def');
$s_tables_comp = (boolean) get_request_data('tb_show_comp');
$s_tables_comment = (boolean) get_request_data('tb_show_comments');
$s_tables_valid = false;
}
$js_stack .= js_request_comment_area();
}
// init $s_domain for the columns form if necessary
if ($s_domains_valid == false &&
($s_create_num > 0 || isset($col_add_flag))) {
include_once './inc/domains.inc.php';
$s_domains = get_domain_definitions($s_domains);
$s_domains_valid = true;
}
// add javascript for the columns form
if ($s_connected === true &&
($s_create_num > 0 || isset($col_add_flag))) {
$js_stack .= js_collations($s_charsets)
.js_request_table_columns();
}
//
// setup $s_tables[] and $s_fields[] if necessary
//
if (($s_connected) && ($s_tables_valid == false)) {
include_once './inc/get_tables.inc.php';
if (get_tables($dbhandle)) {
$s_tables_valid = true;
}
}
//
// print out all the panels
//
$s_page = 'Tables';
$panels = $s_tables_panels;
// Input extra panel
// Open selectable tables
if (isset($_POST['btn_viewselectable'])) {
require "./panels/tb_showselectable.php";
}
require './inc/script_end.inc.php';
//
// mark all tables as opened or closed in $s_tables
//
function toggle_all_tables($tables, $status)
{
foreach (array_keys($tables) as $name) {
if (!$tables[$name]['is_view']) {
$tables[$name]['status'] = $status;
}
}
return $tables;
}
function get_column_fk_defs($cname, $iname)
{
global $dbhandle;
$defs = array('fk_name' => $cname);
$trans = fbird_trans(TRANS_READ, $dbhandle);
$sql = 'SELECT RDB$UPDATE_RULE,'
.' RDB$DELETE_RULE'
.' FROM RDB$REF_CONSTRAINTS'
." WHERE RDB\$CONSTRAINT_NAME='".$cname."'";
$res = @fbird_query($trans, $sql) or fb_error(__FILE__, __LINE__, $sql);
if ($res && $row = fbird_fetch_row($res)) {
fbird_free_result($res);
}
$defs['on_update'] = trim($row[0]);
$defs['on_delete'] = trim($row[1]);
$sql = 'SELECT I2.RDB$RELATION_NAME,'
.' SE.RDB$FIELD_NAME'
.' FROM RDB$INDICES I1'
.' INNER JOIN RDB$INDICES I2 ON I1.RDB$FOREIGN_KEY=I2.RDB$INDEX_NAME'
.' INNER JOIN RDB$INDEX_SEGMENTS SE ON I2.RDB$INDEX_NAME=SE.RDB$INDEX_NAME'
." WHERE I1.RDB\$INDEX_NAME='".$iname."'";
$res = @fbird_query($trans, $sql) or fb_error(__FILE__, __LINE__, $sql);
if ($res && $row = fbird_fetch_row($res)) {
fbird_free_result($res);
}
$defs['fk_table'] = trim($row[0]);
$defs['fk_column'] = trim($row[1]);
fbird_commit($trans);
return $defs;
}