<?php
/***********************************************************************
Copyright (c) 2011, Andrew V. Pleshakov (hide@address.com)
All rights reserved.
This file is part of SPA (SQLite PHP Admin)
See file LICENSE.txt for details
************************************************************************/
require_once('include.php');
if (!isset($pla_db)) {
header('Location: main.php');
die;
}
//////////////////// CREATE TABLE
if (isset($_POST['create'])) {
$name = $_POST['tablename'];
$columns = intval($_POST['columns']);
$pks = array();
$sql = "CREATE TABLE '$name' (\n";
for ($i=1; $i<=$columns; $i++) {
if(trim($_POST["name{$i}"]) == '') raiseError('Empty column name');
$sql .= "\t\"{$_POST['name'.$i]}\" {$_POST['type'.$i]}";
if (is_numeric($_POST['size'.$i])) $sql .= "({$_POST['size'.$i]})";
if (isset($_POST['notnull'.$i])) $sql .= ' NOT NULL';
if (isset($_POST['unique'.$i])) $sql .= ' UNIQUE';
if (isset($_POST['pk'.$i])) $pks[] = '"'.$_POST["name{$i}"].'"';
if(trim($_POST['dflt'.$i]) != '') $sql .= ' DEFAULT \''.($pla_db->escapeString(trim($_POST['dflt'.$i])))."'";
if ($i < $columns) $sql .= ",\n";
// $sql .= "\n";
}
// allow compound primary key
if(count($pks)) $sql .= ",\nPRIMARY KEY (".implode(', ',$pks).')';
$sql .= ");\n";
$_SESSION['lquery'] = $sql;
if(!$pla_db->exec($sql)) raiseError($pla_db->error);
header("Location: index.php?bd=1");
die;
}
/////////////// ADD COLUMN(s) ////////////////////////////////////////
if (isset($_POST['add_columns'])) {
$table = $_POST['tablename'];
$columns = intval($_POST['columns']);
$position = intval($_POST['position']);
$new_cols = $pks = array();
for ($i=1; $i<=$columns; $i++) {
if(trim($_POST["name{$i}"]) == '') raiseError('Empty column name');
$new = "\n\t\"{$_POST['name'.$i]}\" {$_POST['type'.$i]}";
if (is_numeric($_POST['size'.$i])) $new .= "({$_POST['size'.$i]})";
if (isset($_POST['notnull'.$i])) $new .= ' NOT NULL';
if (isset($_POST['unique'.$i])) $new .= ' UNIQUE';
if (isset($_POST['pk'.$i])) $pks[] = '"'.$_POST["name{$i}"].'"';
if(trim($_POST['dflt'.$i]) != '') $new .= ' DEFAULT \''.($pla_db->escapeString(trim($_POST['dflt'.$i])))."'";
$new_cols[] = $new;
}
// There is new column definition in $new_cols
$cols = $pla_db->getTableColumns($table); // all columns in table
$mycols = array();
foreach($cols as $row)
$mycols[] = '"'.$row['name'].'"'; // all columns of table
$schema = $pla_db->getSchema($table); // schema for CREATE TABLE query
$n = count($schema);
$newschema = array();
for($i = 0;$i <= $n; $i++) {
if($i == $position)
foreach($new_cols as $row)
$newschema[] = $row;
if($i < $n)
$newschema[] = "\n\t".$schema[$i];
}
// ---------- include in table Primary keys from $pks array
if(count($pks)) {
$pks = implode(', ',$pks);
$done = false;
foreach($newschema as $k => $val)
if(preg_match('/^PRIMARY/i',trim($val))) {
$newschema[$k] = substr($val,0,strpos($val,')')).', '.$pks.')';
$done = true;
break;
}
if(!$done) // new Primary key(s)
$newschema[] = 'PRIMARY KEY('.$pks.')';
}
$newschema = implode(', ',$newschema);
$mycols = implode(', ',$mycols);
// in newschema we have new schema for table
$temptable = uniqid('tbl');
// $sql = "CREATE TEMPORARY TABLE '$temptable'({$newschema})";
// if(!$pla_db->exec($sql))
// raiseError('Bad description: '.$pla_db->error."<hr />$newschema"); // bad columns' definitions?
$sql = "BEGIN TRANSACTION;\n" .
"\tCREATE TEMPORARY TABLE '$temptable'({$newschema});\n" .
"\tINSERT OR ROLLBACK INTO '$temptable'($mycols)\n\t\tSELECT $mycols FROM '$table';\n" .
"\tDROP TABLE '$table';\n" .
"\tCREATE TABLE '$table'($newschema);\n" .
"\tINSERT INTO '$table'($mycols) SELECT $mycols FROM '$temptable';\n" .
"\tDROP TABLE '$temptable';\n" .
"COMMIT;\n";
if(DEBUG_) file_put_contents('add_columns.sql',$sql);
if(!$pla_db->exec($sql))
raiseError($pla_db->error."<hr />$newschema");
header("Location: index.php?tb=".urlencode($table));
die;
}
///////////////// EDIT COLUMN /////////////////////////////////////////
if (isset($_POST['edit_col'])) {
$table = $_POST['tablename'];
$colname = $_POST['column']; // old name of column (it may have been changed)
$pks = '';
if(!trim($_POST['name'])) raiseError('Empty column name');
$new = "\n\t\"{$_POST['name']}\" {$_POST['type']}";
if (is_numeric($_POST['size'])) $new .= "({$_POST['size']})";
if (isset($_POST['notnull'])) $new .= ' NOT NULL';
if (isset($_POST['unique'])) $new .= ' UNIQUE';
if (isset($_POST['pk'])) $pks = '"'.$_POST['name'].'"';//$new .= ' PRIMARY KEY';
if(trim($_POST['dflt']) != '') $new .= ' DEFAULT \''.($pla_db->escapeString(trim($_POST['dflt'.$i])))."'";
$schema = $pla_db->getSchema($table); // schema for CREATE TABLE query
foreach($schema as $i => $sch)
if(preg_match('/^\s*(\'|"|\[)?'.$colname.'\b/U', $sch))
$schema[$i] = $new; // edited column
else $schema[$i] = "\n\t".$schema[$i];
if($colname != trim($_POST['name'])) // name has been changed
foreach($schema as $i => $sch)
cleanupEd($schema,$i,$sch,$colname,$_POST['name']);
// Drop strings UNIQUE and PRIMARY KEY if there is no such checks
foreach($schema as $i => $sch)
if(cleanupEdDel($schema,$i,$sch,$colname))
unset($schema[$i]);
if($pks) {
$done = false;
foreach($schema as $k => $val)
if(preg_match('/^PRIMARY/i',trim($val))) {
$schema[$k] = substr($val,0,strpos($val,')')).', '.$pks.')';
$done = true;
break;
}
if(!$done) // new Primary key(s)
$schema[] = 'PRIMARY KEY('.$pks.')';
}
$schema = implode(', ',$schema);
// in schema we have new schema for table
$temptable = uniqid('tbl');
// $sql = "CREATE TEMPORARY TABLE '$temptable'({$schema})";
// if(!$pla_db->exec($sql))
// raiseError('Bad column definition: '.$pla_db->error."<hr />$schema"); // bad columns' definitions?
$sql = "BEGIN TRANSACTION;\n" .
"\tCREATE TEMPORARY TABLE '$temptable'({$schema});\n" .
"\tINSERT OR ROLLBACK INTO '$temptable'\n\t\tSELECT * FROM '$table';\n" .
"\tDROP TABLE '$table';\n" .
"\tCREATE TABLE '$table'($schema);\n" .
"\tINSERT INTO '$table' SELECT * FROM '$temptable';\n" .
"\tDROP TABLE '$temptable';\n" .
"COMMIT;\n";
if(DEBUG_) file_put_contents('edit_columns.sql',$sql);
if(!$pla_db->exec($sql))
raiseError($pla_db->error."<hr />$schema");
header("Location: index.php?tb=".urlencode($table));
die;
}
///////////////// RENAME TABLE //////////////////////////////
if (isset($_GET['rename'])) {
$table = urldecode(trim($_GET['table']));
$newname = urldecode(trim($_GET['newname']));
if(!$newname)
raiseError('New table name was not specified');
if($newname == $table)
raiseError('You can not move table to itself');
$schema = $pla_db->getSchema($table); // schema for CREATE TABLE query
$schema = implode(', ',$schema);
$sql = "BEGIN TRANSACTION;\n".
"\tCREATE TABLE '$newname'({$schema});\n" .
"\tINSERT OR ROLLBACK INTO '$newname'\n\t\tSELECT * FROM '$table';\n" .
"\tDROP TABLE '$table';\n" .
"COMMIT;\n";
if(DEBUG_) file_put_contents('rename_table.sql',$sql);
if(!$pla_db->exec($sql))
raiseError($pla_db->error);
header("Location: index.php?tb=".$newname);
die;
}
//////////////////////////////////////////////////////////////////
if (isset($_GET['action'])) {
$action = $_GET['action'];
$colname = $_GET['object'];
$table = $_GET['table'];
switch ($action) {
case 'schema':
$table = urldecode($table);
$tar['table'] = $table;
$tar['schema'] = '';
$pla_db->query("SELECT sql FROM SQLITE_MASTER WHERE tbl_name='$table'");
while($row = $pla_db->fetchArray())
$tar['schema'] .= $row[0]."\n";
die(design_render('table/schema',$tar));
case 'add_col':
$columns = intval($_GET['columns']); // num of columns to add
$tar['position'] = intval($_GET['position']);
$table = urldecode($table);
$tar['table'] = $table;
$tar['form'] = createForm($columns, 'add_columns',$table,'Add column(s)');
$tar['h3'] = 'Add columns to table';
die(design_render('table/addcol',$tar));
case 'edit_col':
$table = urldecode($table);
$tar['table'] = $table;
$schema = $pla_db->getSchema($table); // schema for CREATE TABLE query
foreach($schema as $i => $sch)
if(preg_match('/^(\'|"|\[)?'.$colname.'\b/U', $sch))
$descr = $sch;
$cols = $pla_db->getTableColumns($table); // all columns in table
$mycol = array();
foreach($cols as $row)
if($row['name'] == $colname)
$mycol = $row; //
$tar['columns'] = print_column($table,$colname,$descr,$mycol);
die(design_render('table/edcol',$tar));
case 'del_col':
$table = urldecode($table);
$cols = $pla_db->getTableColumns($table); // all columns in table
$newcols = array();
foreach($cols as $row)
if($row['name'] != $colname)
$newcols[] = '"'.$row['name'].'"'; // new columns for table
if(!count($newcols)) raiseError("You can not delete last column ($colname) of the table");
$schema = $pla_db->getSchema($table); // schema for CREATE TABLE query
foreach($schema as $i => $sch)
if(preg_match('/^(\'|"|\[)?'.$colname.'\b/U', $sch))
unset($schema[$i]);
else $schema[$i] = "\n\t".$schema[$i];
foreach($schema as $i => $sch)
if(cleanupDel($schema,$i,$sch,$colname))
unset($schema[$i]);
$temptable = uniqid('tbl');
$newcols = implode(', ',$newcols);
$schema = implode(', ',$schema);
$sql = "BEGIN TRANSACTION;\n".
"\tCREATE TEMPORARY TABLE '$temptable'({$schema});\n" .
"\tINSERT OR ROLLBACK INTO '$temptable'\n\t\tSELECT $newcols FROM '$table';\n" .
"\tDROP TABLE '$table';\n" .
"\tCREATE TABLE '$table'($schema);\n" .
"\tINSERT INTO '$table' SELECT * FROM '$temptable';\n" .
"\tDROP TABLE '$temptable';\n" .
"COMMIT;\n";
if(DEBUG_) file_put_contents('drop_column.sql',$sql);
if(!$pla_db->exec($sql))
raiseError($pla_db->error);
default: // of switch statement
}
$loc = 'dbase.php';
if(isset($_SERVER['HTTP_REFERER'])) $loc = $_SERVER['HTTP_REFERER'];
header("Location: $loc");
die;
}
///////////////////////////////////////////////////////////////////////
if(!@$_POST['tablename']) raiseError('You did not specified table name','Internal error');
$name = $_POST['tablename'];
$columns = $_POST['columns'];
$tar['table'] = $name;
$tar['h3'] = 'Create a new table';
$tar['form'] = createForm($columns, 'create', $name);
die(design_render('table/addcol',$tar));
/////////////////////////////////////////////////////////////////
function createForm($columns, $vid, $name, $vid_i = 'Create table') {
// columns - number of columns, vid - action after submitting, name - table name
// Note: form is not closed for adding fields
$ar['colinfo'] = '';
for ($i=1; $i<=$columns; $i++) {
$rr['i'] = $i;
$rr['name'] = "name{$i}";
$rr['type'] = "type{$i}";
$rr['size'] = "size{$i}";
$rr['notnull'] = "notnull{$i}";
$rr['unique'] = "unique{$i}";
$rr['pk'] = "pk{$i}";
$rr['dflt'] = "dflt{$i}";
$ar['colinfo'] .= design_render('extra/colinfo',$rr);
}
$ar['columns'] = $columns;
$ar['name'] = $name;
$ar['vid'] = $vid;
$ar['vid_i'] = $vid_i;
return design_render('extra/form',$ar);
}
function print_column($table,$colname,$descr,$val) {
$descr = trim($descr);
$d2 = strtoupper($val['type']);
$s = strpos($d2,'(');
if($s) {
$type = trim(substr($d2,0,$s));
$s = strpos($type,' '); // may be TEXT DEFAULT '('
if($s) $type = substr($type,0,$s);
}
else {
$s = strpos($d2,' ');
if($s) $type = substr($d2,0,$s);
else $type = $d2;
}
if(preg_match('/CHAR/',$type)) $type = 'VARCHAR';
if(preg_match('/TEXT/',$type)) $type = 'TEXT';
if(preg_match('/INT/',$type)) $type = 'INTEGER'; // default value, but... nevertheless
// if(preg_match('/(PRIMARY|UNIQUE|DEFAULT)/',$type))
// $type = 'NUMERIC'; // typeless?
$ar = array();
$ar['size'] = $ar['dflt'] = $ar['pk'] = $ar['uq'] = $ar['nn'] = '';
if(intval($val['pk'])) $ar['pk'] = 'checked="checked" ';
if(stripos($descr,'UNIQUE')) $ar['uq'] = 'checked="checked" ';
if($val['notnull'] == 'NOT NULL') $ar['nn'] = 'checked="checked" ';
if(preg_match('/\((\d+)\)/i',$descr,$mat))
$ar['size'] = $mat[1];
// if(preg_match('/DEFAULT (\'|")?(.+)(\'|")?/i',$descr,$mat))
// $dflt = $mat[2]; // default value
$ar['dflt'] = $val['dflt_value'];
$ar['table'] = $table;
$ar['colname'] = $colname;
$ar['type'] = $type;
return design_render('extra/form_ed',$ar);
}
////////////////////////////////////////////////////////
function cleanupDel(&$schema,$key,$val,$name) {
$val = trim($val);
if(!preg_match('/^(UNIQUE|PRIMARY|FOREIGN)/i',$val)) return false;
// if(preg_match('/^FOREIGN/i',$val)) $val = substr($val,0,(strpos($val,')')+1));
// preg_match("/(\'|\"|\[)?({$name})(\'|\"|\])?/i",$val,$mat);
if(preg_match("/(\"{$name}\")/i",$val,$mat1)) $mat = "/^(.+)(\"{$name}\")(.+)$/i";
elseif (preg_match("/(\'{$name}\')/i",$val,$mat2)) $mat = "/^(.+)(\'{$name}\')(.+)$/i";
elseif (preg_match("/\b({$name})\b/i",$val,$mat3)) $mat = "/(.+)\b({$name})\b(.+)/i";
elseif (preg_match("/(\[{$name}\])/i",$val,$mat2)) $mat = "/^(.+)(\[{$name}\])(.+)$/i";
else return false; // there is no $name in expression!?
$val= preg_replace($mat,'$1$3',$val);
$val = preg_replace("/^(.+)(\[\s*\])(.*)$/U",'$1$3',$val);
$val = preg_replace("/^(.+)(,\s*,)(.*)$/U",'$1,$3',$val);
$val = preg_replace("/^(.+)(\(\s*,)(.*)$/U",'$1($3',$val);
$val = preg_replace("/^(.+)(,\s*\))(.*)$/U",'$1)$3',$val);
$schema[$key] = $val;
return preg_match("/\(\s*\)/U",$val); // (), ( ), ( ) etc.
// if(strpos($val,'()')) return true; // empty - remove all expression
// return false;
}
////////////////////////////////////////////////////////
function cleanupEd(&$schema,$key,$val,$oldname,$newname) {
// change old column name to a new one for FOREIGN KEY
$val = trim($val);
if(!preg_match('/^FOREIGN/i',$val)) return false;
if(preg_match("/(\"{$oldname}\")/i",$val,$mat1)) $mat = "/^(.+)(\"{$oldname}\")(.+)$/i";
elseif (preg_match("/(\'{$oldname}\')/i",$val,$mat2)) $mat = "/^(.+)(\'{$oldname}\')(.+)$/i";
elseif (preg_match("/\b({$oldname})\b/i",$val,$mat3)) $mat = "/(.+)\b({$oldname})\b(.+)/i";
elseif (preg_match("/(\[{$oldname}\])/i",$val,$mat2)) $mat = "/^(.+)(\[{$oldname}\])(.+)$/i";
else return false; // there is no $name in expression!?
$val= preg_replace($mat,"$1\"{$newname}\"$3",$val);
$schema[$key] = $val;
return false;
}
////////////////////////////////////////////////////////
function cleanupEdDel(&$schema,$key,$val,$name) {
// Delete column name from UNIQUE or PRIMARY KEY statement
// for we are supposed to make UNIQUE or PRIMARY in main column definition
$val = trim($val);
if(!preg_match("/^(PRIMARY|UNIQUE)/i",$val)) return false;
if(preg_match("/(\"{$name}\")/i",$val,$mat1)) $mat = "/^(.+)(\"{$name}\")(.+)$/i";
elseif (preg_match("/(\'{$name}\')/i",$val,$mat2)) $mat = "/^(.+)(\'{$name}\')(.+)$/i";
elseif (preg_match("/\b({$name})\b/i",$val,$mat3)) $mat = "/(.+)\b({$name})\b(.+)/i";
elseif (preg_match("/(\[{$name}\])/i",$val,$mat2)) $mat = "/^(.+)(\[{$name}\])(.+)$/i";
else return false; // there is no $name in expression!?
$val = preg_replace($mat,'$1$3',$val);
$val = preg_replace("/^(.+)(\[\s*\])(.*)$/U",'$1$3',$val);
$val = preg_replace("/^(.+)(,\s*,)(.*)$/U",'$1,$3',$val);
$val = preg_replace("/^(.+)(\(\s*,)(.*)$/U",'$1($3',$val);
$val = preg_replace("/^(.+)(,\s*\))(.*)$/U",'$1)$3',$val);
// $val = str_replace('[]','',$val);
// $val = str_replace(array('(,',',)',',,'),array('(',')',','),$val);
$schema[$key] = $val;
return preg_match("/\(\s*\)/U",$val); // (), ( ), ( ) etc.
// if(strpos($val,'()')) return true; // empty - remove all expression
// $schema[$key] = $val;
// return false;
}