Location: PHPKode > projects > SQLite PHP Admin > spa/tbaction.php
<?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;
}
Return current item: SQLite PHP Admin