Location: PHPKode > scripts > Sqlite3 database manager > sqlite3-database-manager/dbed.php
<?php
/*
 * sqlite3 dbed v1.15
 *
 * made anno 2009 by hide@address.com
 *
 * v1.01 (25.01.2009)
 *	- saved as utf-8 and added utf-8 encoding into html template
 *
 * v1.02 (26.01.2009)
 *	- fixed add action and errormessages with this operation
 *
 * v1.03 (27.01.2009)
 *	- added paginating into view action (30 entries per page), configurable via ini-file
 *	- added different backgrounds with css onto view action
 *
 * v1.04 (28.01.2009)
 *	- added "defaulttable" ini-variable
 *
 * v1.05 (02.02.2009)
 *	- added "back" buttons into add and edit pages
 *	- added "search" action
 *	- added "search" link onto "view" page
 *	- fixed "raw" action
 *
 * v1.06 (04.02.2009)
 *	- littlebit optimized "search" (removed duplicates)
 *	- added "search_results" ini setting, which allows to change results output format - horisontally or vertically
 *
 * v1.07 (06.02.2009)
 *	- added "list_results" ini setting
 *	- fixed bug with search, also added field names into results
 *	- "raw sql" is in menu only for users with certain permissions
 *	- enchanced index-page
 *	- added textarea for TEXT and VARCHAR(200) > fieldtypes on add and edit pages
 *
 * v1.08 (07.02.2009)
 *	- added new ini setting 'show_default_table' which shows defaulttable (if defined)
 *	- added default table onto do_index() index page with "add" and "search" links
 *	- replaced ul/li lists with tables on do_view page
 *
 * v1.09 (08.02.2009)
 *	- changed "back" buttons with javascript actions on "add" and "search" pages
 *	- changed structure action-function from "do_browse" to "do_structure"
 *
 * v1.10 (10.02.2009)
 *	- added database backup
 *	- added global submenu
 *	- added id-field into editpage (not changeable)
 *	- added some comments
 *
 * v1.11 (11.02.2009)
 *	- changed index-page to show count of rows of default table
 *	- better view for raw-sql page
 *	- added select listbox with sql-query templates onto "raw-sql" page
 *
 * v1.12 (14.02.2009)
 *	- added results pages to add and edit actions, where is possible to view saved data
 *
 * v1.13 (13.09.2009) by Walter :-)
 *	- improved layout and design several pages
 *	- removed search_results and list_results from ini and script, back to one standard presentation
 *
 * v1.14 (14.09.2009)
 *	- made mainmenu and submenus depending from user-permissions
 *
 * v1.15 (16.09.2009)
 *	- added edit and delete buttons after adding data into tables page
 **/

// configuration file
define('file_ini', 'dbed.ini.php');

$ini = array();

#if(get_magic_quotes_gpc())
#	$_POST = array_map('stripslashes', $_POST);

// read config file if it exists
if(is_file(file_ini))
	$ini = parse_ini_file(file_ini, 1);

// if config settings not found gives error
if(empty($ini))
	exit("can't find ini-file with configuration settings :(");

// start with sessions
session_start();

// set charset to utf-8
header('Content-type: text/html; Charset: UTF-8');

// logout, if isset "?logout" get variable
if(isset($_REQUEST['logout']))
{
	session_destroy();
	header('Location: '.$_SERVER['SCRIPT_NAME']);
	die();
}

$menu = '';

// login
if(empty($_SESSION['user']))
{
	if(!empty($_POST) && isset($ini['users'][$_POST['user']]) && strcasecmp($_POST['pass'], $ini['users'][$_POST['user']])==0)
	{
		$_SESSION['user'] = $_POST['user'];
		$path = $_SERVER['SCRIPT_NAME'];
		if(isset($ini['defaulttable']) && $ini['show_default_table'])
			$path .= '?a=view&tbl='.$ini['defaulttable'];

		header('Location: '.$path);
	}
	echo parse_tpl(get_tpl('html'), array('menu'=>'','body'=>get_tpl('login')));
	die();
}
else
{
	// making menu 
	$rawlink = "";
	if(is_permitted($_SESSION['user'], 'raw'))
		$rawlink = ' <input type="button" value="raw sql" onclick="window.location.href=\'?a=raw\'"> ';

	$backuplink = "";
	if(is_permitted($_SESSION['user'], 'backup'))
		$backuplink = ' <input type="button" value="backup" onclick="window.location.href=\'?a=backup\'"> ';

	$menu = parse_tpl(get_tpl('menu'), array('rawlink'=>$rawlink, 'backuplink'=>$backuplink));

	// adding submenu
	$menu .= parse_tpl(get_tpl('submenu'), array('tablename'=>$ini['defaulttable']));
}

// create a SQLite3 database file with PDO and return a database handle (Object Oriented)
try
{
	$dbHandle = new PDO('sqlite:'.$ini['dbname']);
}
catch(PDOException $exception)
{
	die($exception->getMessage());
}

$actions = array('index','raw','list','view','structure','add','edit','drop','delete','search', 'backup');
$action = (isset($_GET['a']) && in_array($_GET['a'], $actions)) ? $_GET['a'] : $actions[0];

// check if user is permitted to do certain action 
if(is_permitted($_SESSION['user'], $action))
{
	// if yes, then make function name from get-parameter
	$func = 'do_' . $action;
	// call function which generates html
	$html = $func(&$dbHandle);
	// echo it
	echo parse_tpl(get_tpl('html'), array('body'=>$html, 'menu'=>$menu));
}
else
{
	exit('action '.htmlspecialchars($action).' not permitted!');
}

// default page
function do_index($db)
{
	$t = $GLOBALS['ini']['defaulttable'];
	$d = $GLOBALS['ini']['dbname'];

	$last_modified = filemtime("$d");
	$day = date("j", $last_modified);
	$month = date("n", $last_modified);
	$year = date("Y", $last_modified);
	$hour = date("H", $last_modified);
	$minute = date("i", $last_modified);

	$result = $db->query('SELECT COUNT(*) AS t FROM '.$GLOBALS['ini']['defaulttable']);
	$total_rows = $result->fetch(PDO::FETCH_ASSOC);
	
	$o = '';
	$o .= '<br><table cellpadding="0" cellspacing="10">';
	$o .= '<tr><td align="right">User:</td><td>'. htmlspecialchars($_SESSION['user']) .'</td></tr>';
	$o .= '<tr><td align="right">Database:</td><td>'. $GLOBALS['ini']['dbname'] .'</td></tr>';
	$o .= '<tr><td align="right">Filesize:</td><td>'. filesize($d) .' bytes</td></tr>';
	$o .= '<tr><td align="right">Last update:</td><td>'.$day.'-'.$month.'-'.$year.'&nbsp;'.$hour.':'.$minute.'</td></tr>';
	$o .= '<tr><td align="right">Active table:</td><td>'. $GLOBALS['ini']['defaulttable'] .'</td></tr>';
	$o .= '<tr><td align="right">Records:</td><td>'. intval($total_rows['t']) .'</td></tr>';
	$o .= '</table><br>';
	return $o;
}

// deletes table from database
function do_drop($db)
{
	$query='drop table '.strip_tags($_GET['tbl']);
	$db->query($query);
	header('Location:?');
}

// shows all tables with view, search and structure links
function do_list($db)
{
	$query='select name from sqlite_master where type="table"';
	$result = $db->query($query);
	$rows = $result->fetchAll(PDO::FETCH_ASSOC);
	if(empty($rows))
	{
		return 'there are no tables yet';
	}
	else
	{
		$o = '<table cellpadding="10" cellspacing="0" bgcolor="#f7f9f9" style="border-collapse: collapse;">';
		foreach($rows as $row)
		{
			$o .= '<tr><td valign="top" style="border: 1px solid #adc6c8">'.$row['name'].'</td><td valign="top" style="border: 1px solid #adc6c8"><a href="?a=view&amp;tbl='.$row['name'].'">view</a> &nbsp;|&nbsp; <a href="?a=search&amp;tbl='.$row['name'].'">search</a> &nbsp;|&nbsp; <a href="?a=structure&amp;tbl='.$row['name'].'">structure</a></td></tr>';
		}
		$o .= '</table>';
		return $o;
	}
}

// lists data from table
function do_view($db)
{
	$o = '';
	$perpage = (isset($_REQUEST['perpage'])) ? $_REQUEST['perpage'] : $GLOBALS['ini']['itemsperpage'];
	$sth = $db->prepare('SELECT COUNT(*) AS total FROM '.$_REQUEST['tbl']);
	$sth->execute();
	$result = $sth->fetch(PDO::FETCH_ASSOC);
	$pager = pager($result['total'], $perpage, 'page', $_SERVER['SCRIPT_NAME'].'?a=view&tbl='.$_REQUEST['tbl'].'&page=__CURRENT_PAGE__', '<b>%u</b>', 20);
	$q='SELECT * FROM '.strip_tags($_GET['tbl']).' LIMIT '.$pager['limit'];
	$result = $db->query($q);
	if($db->errorCode()<>0)
	{
		$o .= 'err...'.var_export($db->errorInfo(),1);
	}
	else
	{
		$o .= '<p>'hide@address.com(" - ", $pager['navbar']).'</p>';
		$rows = $result->fetchAll(PDO::FETCH_ASSOC);
		foreach($rows as $f=>$row)
		{
			$first = array_shift($g=$row);
			$o .= '<table width="800" cellpadding="5" cellspacing="0" bgcolor="#f7f9f9" style="border-collapse: collapse;">';
			foreach($row as $key=>$it)
			{
				if(strlen($it))
					$o .= sprintf('<tr><td width="150" align="right" valign="top" style="border: 1px solid #adc6c8">%s</td><td width="650" style="border: 1px solid #adc6c8">%s</td></tr>', $key, $it);
			}

			if((is_permitted($_SESSION['user'],'edit')) || (is_permitted($_SESSION['user'],'delete')))
			{
				$o .= '<tr><td colspan="2" align="right" style="border: 1px solid #adc6c8">';
				if(is_permitted($_SESSION['user'],'edit'))
				{
					$o .= '<a href="?a=edit&amp;tbl='.$_GET['tbl'].'&amp;id='.$first.'">edit</a>';			
				}
				if(is_permitted($_SESSION['user'],'delete'))
				{
					$o .= ' | <a href="?a=delete&amp;tbl='.$_GET['tbl'].'&amp;id='.$first.'" onclick="return confirm(\'Are you sure to delete this entry?\')">delete</a>';
				}
				$o .= '&nbsp;</td></tr>';
			}

			$o .= '</table><br>';
		}
		$o .= '<p>'hide@address.com(" - ", $pager['navbar']).'</p>';
	}
	return $o;
}

// record editing in database
function do_edit($db)
{
	// first we search for primary key from table structure
	$query='PRAGMA table_info('.esc($_GET['tbl']).')';
	$result = $db->query($query);
	$rowsa = $result->fetchAll(PDO::FETCH_ASSOC);
	$id = false;
	foreach($rowsa as $r)
	{
		if($r['pk'])
			$id = $r['name'];
	}

	if(count($_POST))
	{
		$fields = array_keys($_POST['f']);
		$values = array_values($_POST['f']);
		$q = "UPDATE ".strip_tags($_GET['tbl'])." SET ".a_eq_b($fields, $values)." WHERE ".$id.'='.$_GET['id'];
		$res = $db->query($q);
		if($res->errorCode()<>'0000')
		{
			$msg = 'There was error: <pre>'.$res->errorCode().print_r($db->errorInfo(), 1).'</pre>';
			echo $q . $msg;
		}
		else
		{
			$msg  = '<p><b>Successfully updated!</b></p>';
			$msg .= '<table width="800" cellpadding="5" cellspacing="0" bgcolor="#f7f9f9" style="border-collapse: collapse;">';

			$msg .= '<tr><td width="150" align="right" valign="top" style="border: 1px solid #adc6c8">id</td><td width="650" style="border: 1px solid #adc6c8">'.$_GET['id'].'</td></tr>';

			foreach($fields as $k=>$value)
			{
				$msg .= '<tr><td width="150" align="right" valign="top" style="border: 1px solid #adc6c8">'.$value.'</td><td width="650" style="border: 1px solid #adc6c8">'.$_POST['f'][$value].'</td></tr>';
			}

			if((is_permitted($_SESSION['user'],'edit')) || (is_permitted($_SESSION['user'],'delete')))
			{
				$msg .= '<tr><td colspan="2" align="right" style="border: 1px solid #adc6c8">';
				if(is_permitted($_SESSION['user'],'edit'))
				{
					$msg .= '<a href="?a=edit&amp;tbl='.strip_tags($_GET['tbl']).'&amp;id='.$_GET['id'].'">edit</a>';			
				}
				if(is_permitted($_SESSION['user'],'delete'))
				{
					$msg .= ' | <a href="?a=delete&amp;tbl='.strip_tags($_GET['tbl']).'&amp;id='.$_GET['id'].'" onclick="return confirm(\'Are you sure to delete this entry?\')">delete</a>';
				}
				$msg .= '&nbsp;</td></tr>';
			}

			$msg .= '</table>';
		}
		return $msg;

		header('Location: ?a=view&tbl='.$_GET['tbl']);
		die();
	}
	else
	{
		$q2 = 'SELECT * FROM '.$_GET['tbl'].' WHERE '.$id.'='.esc(intval($_GET['id']));
		$result = $db->query($q2);
		$rows = $result->fetch(PDO::FETCH_ASSOC);
	}

	$o .= '<form action="?a=edit&amp;tbl='.htmlspecialchars($_GET['tbl']).'&amp;'.$id.'='.$_GET['id'].'" method="post">';
	if(empty($result))
	{
		$o .= 'no fields in table';
	}
	else
	{
		$o .= "<p>Edit record in table <b>'".htmlspecialchars($_GET['tbl'])."'</b></p>";
		$o .= '<table cellpadding="0" cellspacing="0" bgcolor="#f7f9f9" style="border: 1px solid #adc6c8; border-spacing: 10px 10px; padding: 10px 15px;">';
		$cnt=-1;
		foreach($rows as $f=>$row)
		{
			$cnt++;
			$i = $f==$id ? 'p' : 'f';
			$value = isset($_POST['f']) ? $_POST['f'][$row['name']] : $row;
			if($i == 'p')
			{
				$o .= '<tr><td align="right">'.$id.'</td><td>'.$_GET['id'].'</td></tr>';
				continue;
			}
			$o .= '<tr><td align="right">'.htmlspecialchars($f).'</td>';
			if(strtolower($rowsa[$cnt]['type'])=='text' OR field_length($rowsa[$cnt]['type']) > 200)
				$o .= '<td><textarea name="'.$i.'['.htmlspecialchars($f).']" cols="35" rows="3">'.htmlspecialchars($value).'</textarea></td></tr>';
			else
				$o .= '<td><input type="text" name="'.$i.'['.htmlspecialchars($f).']" value="'.htmlspecialchars($value).'" size="35"></td></tr>';
		}
		$o .= '</table>';
	}
	$o .= '<p><input type="submit" value="Update"> <input type=button value="Back" onClick="history.go(-1)"></p>';
	$o .= '</form>';
	return $o;
}

// deletes entry from table
function do_delete($db)
{
	$query='PRAGMA table_info('.esc($_GET['tbl']).')';
	$result = $db->query($query);
	$rows = $result->fetchAll(PDO::FETCH_ASSOC);
	$id = false;
	foreach($rows as $r)
	{
		if($r['pk'])
			$id = $r['name'];
	}
	$q = 'DELETE FROM '.$_GET['tbl'].' WHERE '.$id.'='.esc($_GET['id']);
	$db->query($q);
	header('Location:?a=view&tbl='.strip_tags($_GET['tbl']));
}

// shows table structure
function do_structure($db)
{
	$o = '<p>';
	if(is_permitted($_SESSION['user'],'drop'))
		$o .= '<a href="?a=drop&amp;tbl='.htmlspecialchars($_GET['tbl']).'" onclick="return confirm(\'Are You sure to delete whole table?\')">drop table</a> &nbsp;|&nbsp; ';
	if(is_permitted($_SESSION['user'],'add'))
		$o .= '<a href="?a=add&amp;tbl='.htmlspecialchars($_GET['tbl']).'">add data</a>';
	$o .= '</p>';

	$query='PRAGMA table_info('.esc($_GET['tbl']).')';
	$result = $db->query($query);
	if(empty($result))
	{
		$o .= 'no rows in table';
	}
	else
	{
		$rows = $result->fetchAll(PDO::FETCH_ASSOC);
		$o .= '<table cellpadding="10" cellspacing="0" bgcolor="#f7f9f9" style="border-collapse: collapse;">';
		$o .= '<tr><td style="border: 1px solid #adc6c8"><b>name</td><td style="border: 1px solid #adc6c8"><b>type</b></td><td style="border: 1px solid #adc6c8"><b>primary key</b></td></tr>';
		foreach($rows as $f=>$row)
			$o .= '<tr><td style="border: 1px solid #adc6c8">'.$row['name'].'</td><td style="border: 1px solid #adc6c8">'.($row['type']?$row['type']:'unknown').'</td><td style="border: 1px solid #adc6c8">'.($row['pk']?'y':'n').'</td></tr>';
		$o .= '</table>';
		$o .= '<p><a href="?a=list">back to list tables</a></p>';
	}
	return $o;
}

// adds data into table
function do_add($db)
{
	$o = "<p>Add record to table <b>'".htmlspecialchars($_GET['tbl'])."'</b></p>";
	
	if(!empty($_POST))
	{
		$fields = array_keys($_POST['f']);
		$values = array_values($_POST['f']);
		$q = "INSERT INTO ".strip_tags($_GET['tbl'])." (".join(',', $fields).") VALUES(".join(',', array_map("esc", $values)).")";
		$res = $db->query($q);

		if($res->errorCode()<>'0000')
		{
			$msg = '<br>There was error: <pre>'.$res->errorCode().print_r($db->errorInfo(), 1).'</pre>';
			echo $q . $msg;
		}
		else
		{
			$last_id = $db->lastInsertId();
			$msg = '<p><b>Successfully added!</b></p>';
			$msg .= '<table width="800" cellpadding="5" cellspacing="0" bgcolor="#f7f9f9" style="border-collapse: collapse;">';
			foreach($fields as $k=>$value)
			{
				$msg .= '<tr><td width="150" align="right" valign="top" style="border: 1px solid #adc6c8">'.$value.'</td><td width="650" style="border: 1px solid #adc6c8">'.$_POST['f'][$value].'</td></tr>';
			}
			$msg .= '<tr><td width="150" align="right" valign="top" style="border: 1px solid #adc6c8"></td><td width="650" style="border: 1px solid #adc6c8"><a href="?a=edit&tbl='.strip_tags($_GET['tbl']).'&id='.$last_id.'">edit</a> - <a href="?a=delete&tbl='.strip_tags($_GET['tbl']).'&id='.$last_id.'" onclick="return confirm(\'Are You Sure that You want to delete this?\')">delete</a></td></tr>';
			#
			# add links for delete and edit (see lines 302~314)
			#

			$msg .= '</table>';
		}
		return $msg;
	}

	$o .= '<form action="?a=add&amp;tbl='.htmlspecialchars($_GET['tbl']).'" method="post">';

	$query='PRAGMA table_info('.esc($_GET['tbl']).')';
	$result = $db->query($query);
	
	if(empty($result))
	{
		$o .= 'no fields in table';
	}
	else
	{		
		$rows = $result->fetchAll(PDO::FETCH_ASSOC);
		$o .= '<table cellpadding="0" cellspacing="0" bgcolor="#f7f9f9" style="border: 1px solid #adc6c8; border-spacing: 10px 10px; padding: 10px 15px;">';
		foreach($rows as $f=>$row)
		{
			$i = !empty($row['pk']) ? 'p' : 'f';
			if($i == 'p')
				continue;

			$o .= '<tr><td align="right">'.htmlspecialchars($row['name']).'</td>';
			if(strtolower($row['type'])=='text' OR field_length($row['type']) > 200)
				$o .= '<td><textarea name="'.$i.'['.htmlspecialchars($row['name']).']" cols="35" rows="3">'.htmlspecialchars($_POST['f'][$row['name']]).'</textarea></td></tr>';
			else
				$o .= '<td><input type="text" name="'.$i.'['.htmlspecialchars($row['name']).']" value="'.htmlspecialchars($_POST['f'][$row['name']]).'" size="35"></td></tr>';
		}
		$o .= '</table>';
	}
	$o .= '<br><input type="submit" value="Save"> <input type=button value="Back" onClick="history.go(-1)">';
	$o .= '</form>';
	return $o;
}

// raw sql executing
function do_raw($db)
{
	$msg = "<p>Enter your SQL command:</p>";
	if(isset($_POST['sqlcmd']) && strlen($_POST['sqlcmd']))
	{
		$stmnt = $db->query($_POST['sqlcmd']);
		if($db->errorCode()!='0000')
		{
			$msg = 'There was error: <pre>'.print_r($db->errorInfo(), 1).'</pre>';
		}
		else
		{
			$rows = $stmnt->fetchAll(PDO::FETCH_ASSOC);
			$msg = sprintf('<p>Found total <b>%s</b> rows</p>', count($rows));

			$msg .= '<table cellpadding="5" cellspacing="0" bgcolor="#f7f9f9" style="border-collapse: collapse;">';
			foreach($rows as $row)
			{
				$msg .= '<tr><td align="left" valign="top" style="border: 1px solid #adc6c8">'.join('</td><td align="left" valign="top" style="border: 1px solid #adc6c8">', $row);
			}
			$msg .= '</td></tr></table><br>';

		}	
	}
	return parse_tpl(get_tpl('rawform'), array('msg'=>$msg, 'defaulttable'=>$GLOBALS['ini']['defaulttable']));
}

// searching
function do_search($db)
{
	$output = '';

	$query='PRAGMA table_info('.esc($_GET['tbl']).')';
	$result = $db->query($query);
	$rows = $result->fetchAll(PDO::FETCH_ASSOC);

	if(empty($rows))
		return 'rows not found';

	$id = false;

	foreach($rows as $r)
	{
		if($r['pk'])
			$id = $r['name'];
	}

	$methods=array(1=>'exactly equal', 'not equal', 'contains');

	if(!empty($_POST))
	{
		$sql_where = ' WHERE '.$_REQUEST['field'];
		
		if($_POST['method']==1)
			$sql_where .= '=';

		if($_POST['method']==2)
			$sql_where .=' <> ';

		if($_POST['method']==3)
			$sql_where .= ' LIKE '. esc('%'.$_POST['kword'].'%');
		else
			$sql_where .= esc($_POST['kword']);
	
		$sql = 'SELECT * FROM '.$_REQUEST['tbl'].$sql_where;
		$o = '<p><pre>'.$sql.'</pre></p>';

		$q = $db->query($sql);
		if($q->errorCode<>'0000')
		{
			$items = $q->fetchAll(PDO::FETCH_ASSOC);
			foreach($items as $k=>$item)
			{
				$output .= '<table width="800" cellpadding="5" cellspacing="0" bgcolor="#f7f9f9" style="border-collapse: collapse;">';
				foreach($item as $key=>$it)
				{
					if(strlen($it))
						$output .= sprintf('<tr><td width="150" align="right" valign="top" style="border: 1px solid #adc6c8">%s</td><td width="650" style="border: 1px solid #adc6c8">%s</td></tr>', $key, $it);
				}

				$link_id = array_shift($item);

				if((is_permitted($_SESSION['user'],'edit')) || (is_permitted($_SESSION['user'],'delete')))
				{
					$output .= '<tr><td colspan="2" align="right" style="border: 1px solid #adc6c8">';
					if(is_permitted($_SESSION['user'],'edit'))
					{
						$output .= '<a href="?a=edit&amp;tbl='.htmlspecialchars($_GET['tbl']).'&amp;id='.$link_id .'">edit</a>';			
					}
					if(is_permitted($_SESSION['user'],'delete'))
					{
						$output .= ' | <a href="?a=delete&amp;tbl='.htmlspecialchars($_GET['tbl']).'&amp;id='.$link_id .'" onclick="return confirm(\'Are you sure to delete this entry?\')">delete</a>';
					}
					$output .= '&nbsp;</td></tr>';
				}
				$output .= '</table><br>';
			}
		}
	}

	if(empty($_POST))
	{
		$o .= '<br>';
	}

	$o .= '<form action="?a=search&amp;tbl='.$_REQUEST['tbl'].'" method="post"><select name="field">';

	foreach($rows as $r)
	{
		$o .= '<option value="'.$r['name'].'">'.$r['name'].'</option>';
	}
	$o .= '</select>&nbsp;<select name="method">';
	
	foreach($methods as $k=>$m)
	{
		$o .= '<option value="'.$k.'">'.$m.'</option>';
	}
	$o .= '</select>&nbsp;';
	$o .= '<input type="text" size="20" name="kword" value="'.htmlspecialchars($_POST['kword']).'">&nbsp;<input type="submit" value="Search">&nbsp;<input type=button value="Back" onClick="history.go(-1)"></form>';
	$o .= $output;

	return $o;
}

// gives downloadable file
function do_backup()
{
	$fn = date("Y-m-d_H-i-s").'.sqlite3';
	header('Content-type: application/octet-stream');
	header('Content-Disposition: attachment; filename="'.$fn.'"');
	readfile($GLOBALS['ini']['dbname']);
	exit;
}

// returns quoted variable for using in sql query
function esc($field)
{
	return "'".$field."'";
}

// variable var1 is equal with value var2, used with edit (update sql)
function a_eq_b($var1, $var2)
{
	$r = array();
	if(count($var1)==count($var2))
		foreach($var1 as $k=>$v1)
			$r[] = $v1.'='.esc($var2[$k]);

	return join(',', $r);
}

// outputs php array as preformated
function print_pre($var)
{
	print_r('<pre>'.$var.'</pre>');
}

/*
 * @param  mixed  $array   - lehtedele jaotatav massiiv või elmentide arv
 * @param  int    $perpage - mitut asja ühel leheküljel kuvada
 * @param  string $pagevar - GET muutuja aadressiribal
 * @param  string $pageurl - sprintf formaadis lehekülje aadress
 * @param  string $pageactive-sprintf formaadis tekst aktiivse lehe jaoks
 * @param  string $maxlinks- mitut linki korraga kuvada (orienteeruv suurus)
 */
function pager($array, $perpage, $pagevar='p', $pageurl="?p=__CURRENT_PAGE__", $pageactive="<b>%u</b>", $maxlinks=10)
{
	// jagatavate elementide koguarv
	$total = is_array($array) ? count($array) : intval($array);

	// aktiivne lehekülg
	$page = isset($_GET[$pagevar]) ? $_GET[$pagevar] : 1;

	if($perpage<=0)
		$perpage = 10;

	// lehekülgede koguarv
	$pages = ceil($total / $perpage);
		
	// kui page muutuja on väiksem 1-st või suurem lehtede 
	// koguarvust siis aktiivseks leheks määratakse 1
	if($page > $pages || $page < 1)
		$page = 1;

	// leiame lingiriba alguspunkti
	$halfstart = $page - ($maxlinks / 2);
	$start = $halfstart < 1 ? 1 : $halfstart;

	// leiame lingiriba lõpp-punkti
	$halfend = $page + ($maxlinks / 2);
	$end = $halfend > $pages ? $pages : $halfend;

	// tagastatav massiiv
	$return = array();

	// navigeerimismenüü loome ainult siis kui lehti on vähemalt 1
	if($pages > 1)
	{
		if($page>1)
		{
			$url = str_replace('__CURRENT_PAGE__',1,$pageurl);
			$return['navbar'][] = sprintf('<a href="%s">%s</a>', $url, '|&lt;-');
		}

		for($i=$start; $i<=$end; $i++)
		{
			if($page==$i)
			{
				$return['navbar'][] = sprintf($pageactive, $i);
			}
			else
			{
				$url = str_replace('__CURRENT_PAGE__',$i,$pageurl);
				$return['navbar'][] = sprintf('<a href="%s">%u</a>', $url, $i);
			}
		}
		if($page<$pages)
		{
			$url = str_replace('__CURRENT_PAGE__',$pages,$pageurl);
			$return['navbar'][] = sprintf('<a href="%s">%s</a>', $url, '-&gt;');
		}

	}

	// limit päringus kasutamiseks
	$limit_1 = ($page-1)*$perpage;

	// $limit_2 asemel võiks panna $perpage 
	// kuid igaks juhuks arvutab selle ka välja, kuna 
	// see on abiks nt massiivi korral
	$limit_2 = $limit_1 + $perpage > $total ? $total-$limit_1: $perpage;
	$return['limit'] = $limit_1 . ', '.$limit_2;

	// aktiivne lehekülg
	$return['current'] = $page;

	// lehekülgede arv kokku
	$return['total'] = $total;

	return $return;
}

// function checks that user is allowed to execute certain operation
function is_permitted($user, $action="index")
{
	global $ini;
	return isset($ini['users'][$user]) && in_array($action, explode(',', $ini['permissions'][$user]));
}

// returns table fieldtype length
function field_length($type)
{
	preg_match('/.*(\d{3})/',$type, $matches);
	return intval($matches[1]);
}
/**
 * parse_template - asendab muutujas (mis nüüd sisaldab templatefaili sisu) tagid nende
 * 
 * @param string $source - template sisu (näiteks read_template tulemus)
 * @param array $vars - massiiv muutujatest ja nende väärtustest
 * 
 * @return string with html template
 
 * @author Anti Veeranna (hide@address.com)
 */
function parse_tpl($source,$vars)
{
	// kontrollime, kas $vars argument on ikka array
	if (is_array($vars))
	{
		// tsükkel üle $vars kõigi elementide
		foreach($vars as $key => $value)
		{
			// moodustame tagi nime. Kui $key väärtuseks on näiteks "name",
			// siis tagi nimeks saab "{VAR:name}"
			$tag = "<VAR:" . $key . ">"; 

			// asendame tagi tema väärtusega
			$source = str_replace($tag,$value,$source);
		};
	};

	// tagastame $source, kus tagid on nüüd juba nende väärtustega asendatud
	return $source;
}

function get_tpl($name)
{
		$mainmenu = $submenu = "";
	if(is_permitted($_SESSION['user'], 'list'))
		$mainmenu .= <<<html
	<input type="button" value="list tables" onclick="window.location.href='?a=list'">
html;


	if(is_permitted($_SESSION['user'], 'view'))
		$submenu .= <<<html
		<input type="button" value="list all" onclick="window.location.href='?a=view&amp;tbl=<VAR:tablename>'"> 
html;

	if(is_permitted($_SESSION['user'], 'add'))
		$submenu .= <<<html
		<input type="button" value="add" onclick="window.location.href='?a=add&amp;tbl=<VAR:tablename>'"> 
html;

	if(is_permitted($_SESSION['user'], 'search'))
		$submenu .= <<<html
		<input type="button" value="search" onclick="window.location.href='?a=search&amp;tbl=<VAR:tablename>'"> 
html;

	$templates = array(
'login'=><<<tpl
<fieldset><legend>dbEdit</legend>
<form action="$s" method="post"><br>
	<table>
		<tr><td align="right">Username:</td><td><input type="text" name="user"></td></tr>
		<tr><td align="right">Password:</td><td><input type="password" name="pass"></td></tr>
		<tr><td></td><td align="center"><br><input type="submit" value="login"></td></tr>
	</table>
	</form>
</fieldset>
tpl
		,
'rawform'=><<<rawform
<VAR:msg>
<form action="?a=raw" method="post">
	<textarea cols="60" rows="6" name="sqlcmd" id="sqlcmd">{$_POST['sqlcmd']}</textarea><br><br>
	<select onchange="document.getElementById('sqlcmd').value=this.value">
		<option value="">Choose standard command...</option>
		<option value="SELECT * FROM <VAR:defaulttable>">SELECT - search data from table</option>
		<option value="VACUUM">VACUUM - compacts database</option>
		<option value="ANALYZE <VAR:defaulttable>">ANALYZE - gives info about indexes</option>
		<option value="SELECT * FROM sqlite_master">SELECT sqlite_master - info from hidden table</option>
		<option value="EXPLAIN SELECT * FROM <VAR:defaulttable>">EXPLAIN SELECT - info about query</option>
	</select>
	<input type="submit" value="lets go!">
</form>
rawform
	,
'searchform'=><<<searchform
	<VAR:msg>
	<form action="?a=search" method="post">
		<input type="text" name="txt" value="{$_POST['txt']}"> from (table){$tables} {$fields}
		<br><input type="submit" value="lets go!">
	</form>
searchform
	,
'menu'=><<<menu
<form>
<input type="button" value="home" onclick="window.location.href='?a=index'"> 
{$mainmenu} 
<VAR:rawlink> 
<VAR:backuplink> 
<input type="button" value="logout" onclick="window.location.href='?logout'"> 
</form><hr>
menu
	,
'submenu'=><<<submenu
<form><b><VAR:tablename></b>: 
{$submenu}</form><hr>
submenu
	,
'html'=><<<html
<html>
<head>
<title>dbEdit</title>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body bgcolor="#bdd1d3" link="#2d4648" vlink="#2d4648" alink="#2d4648"><br>
<div align="center">
	<table cellpadding="25" cellspacing="0" bgcolor="#dee8e9" style="border: 3px solid #5b8c91">
		<tr>
			<td align="center" valign="top">
		<VAR:menu>
		<VAR:body>
			</td>
		</tr>
	</table>
</div>
</body>
</html>
html
	);
	$tpl = isset($templates[$name]) ? $templates[$name] : 'tpl not found';
	return $tpl;
}
?>
Return current item: Sqlite3 database manager