web/editor/test/sqlite/play.php
<?php
/**
 * http://zetcode.com/databases/sqlitephptutorial/
 */

$dbhandle = sqlite_open('test.db', 0666, $error);

//$stm = "DROP TABLE diagram";
//$ok = sqlite_exec($dbhandle, $stm, $error);
//
//
//$stm = "CREATE TABLE diagram (
//id INTEGER PRIMARY KEY,
//title VARCHAR(255),
//description TEXT,
//public BOOL,
//createdDate DATETIME NOT NULL,
//lastUpdate DATETIME NOT NULL,
//size INT UNSIGNED COMMENT 'The size of diagram in bytes'
//)";
//
////$stm = "CREATE TABLE Friends(Id integer PRIMARY KEY, 
////       Name text UNIQUE NOT NULL, Sex text CHECK(Sex IN ('M', 'F')))";
//$ok = sqlite_exec($dbhandle, $stm, $error);
//
//if (!$ok)
//   die("Cannot execute query. $error");
//
//$stm1 = "INSERT INTO diagram (title, description, createdDate, lastUpdate, size) 
//    VALUES('test', 'Test description', '2012-11-23 12:34:56', '2012-11-24 13:34:56', 1023)";
//$ok1 = sqlite_exec($dbhandle, $stm1);
//if (!$ok1) die("Cannot execute statement.");
//
//$stm1 = "INSERT INTO diagram (title, description, createdDate, lastUpdate, size) 
//    VALUES('test2', 'Test description2', '2012-11-23 12:34:56', '2012-11-24 13:34:56', 1023)";
//$ok1 = sqlite_exec($dbhandle, $stm1);
//if (!$ok1) die("Cannot execute statement.");
//
//echo "Table diagram created successfully";
//
//
//$query = "SELECT * FROM diagram";
//$result = sqlite_query($dbhandle, $query);
//if (!$result) die("Cannot execute query.");
//
//while ($row = sqlite_fetch_array($result, SQLITE_ASSOC)) {
//    echo "\n" . $row['id']  . " : " . $row['title'];
//}


class Diagram {

	public $id;
	public $title;
	public $description;
	public $public;
	public $createdDate;
	public $lastUpdate;

	function loadFromSQL($row) {
		$this->id = is_null($row['id']) ? null : $row['id'];
		$this->title = is_null($row['title']) ? null : $row['title'];
		$this->description = is_null($row['description']) ? null : $row['description'];
		$this->public = is_null($row['public']) ? null : $row['public'];
		$this->createdDate = is_null($row['createdDate']) ? null : $row['createdDate'];
		$this->lastUpdate = is_null($row['lastUpdate']) ? null : $row['lastUpdate'];
	}
}


class User {
	public $id;
	public $email;
	public $password;
	public $name;
	public $createdDate;
	public $lastLoginDate;
	public $lastLoginIP;
	public $lastBrowserType;
	public $admin;

	function loadFromSQL($row) {
		$this->id = is_null($row['id']) ? null : $row['id'];
		$this->email = is_null($row['email']) ? null : $row['email'];
		$this->password = is_null($row['password']) ? null : $row['password'];
		$this->name = is_null($row['name']) ? null : $row['name'];
		$this->createdDate = is_null($row['createdDate']) ? null : $row['createdDate'];
		$this->lastLoginDate = is_null($row['lastLoginDate']) ? null : $row['lastLoginDate'];
		$this->lastLoginIP = is_null($row['lastLoginIP']) ? null : $row['lastLoginIP'];
		$this->lastBrowserType = is_null($row['lastBrowserType']) ? null : $row['lastBrowserType'];
		$this->admin = is_null($row['admin']) ? null : $row['admin'];
	}
}

function diagramCreate($dbhandle, $title, $description, $public){
    $stm1 = sprintf("INSERT INTO diagram (title, description, public, createdDate, lastUpdate) 
        VALUES('%s', '%s', '%s', '%s', '%s')", 
            $title, $description, $public, gmdate('Y-m-d H:i:s'), gmdate('Y-m-d H:i:s'));
    #print($stm1);
    $ok1 = sqlite_exec($dbhandle, $stm1);
    if (!$ok1) die("Cannot execute statement.");    
}

function diagramGetById($dbhandle, $diagramId) {
    $d = false;
    $query = sprintf("SELECT * FROM diagram where id=%d", $diagramId);
    $result = sqlite_query($dbhandle, $query);
    if ($result) {
        $row = sqlite_fetch_array($result, SQLITE_ASSOC);
        if($row){
            $d = new Diagram();
            $d->loadFromSQL($row);
        }
    }
        
    return $d;
}


function diagramGetAll($dbhandle) {
    $diagrams = array();
    $query = "SELECT * FROM diagram ORDER BY title";
    $result = sqlite_query($dbhandle, $query);
    if ($result) {
        while($row = sqlite_fetch_array($result, SQLITE_ASSOC)){
            #print_r($row);
            $d = new Diagram();
            $d->loadFromSQL($row);
            $diagrams[] = $d;
        }
    }
        
    return $diagrams;
}

function diagramDeleteById($dbhandle, $diagramId){
    $query = sprintf("delete FROM diagram where id=%d", $diagramId);
    $result = sqlite_query($dbhandle, $query);
    if ($result) {
        
    }
}

if($_REQUEST['action'] == 'init'){    
    /*
    $stm = "DROP TABLE diagram";
    $ok = sqlite_exec($dbhandle, $stm, $error);
    if (!$ok) die("Cannot execute statement.");
    */
    
//    $stm = "CREATE TABLE diagram (
//    id INTEGER PRIMARY KEY,
//    title VARCHAR(255),
//    description TEXT,
//    public BOOL,
//    createdDate DATETIME NOT NULL,
//    lastUpdate DATETIME NOT NULL,
//    size INT UNSIGNED COMMENT 'The size of diagram in bytes'
//    )";   
//    $ok = sqlite_exec($dbhandle, $stm, $error);
//    if (!$ok) die("Cannot execute statement.");
    
//    
//    $stm1 = "INSERT INTO diagram (title, description, createdDate, lastUpdate, size) 
//        VALUES('test', 'Test description', '2012-11-23 12:34:56', '2012-11-24 13:34:56', 1023)";
//    $ok1 = sqlite_exec($dbhandle, $stm1);
//    if (!$ok1) die("Cannot execute statement.");
//
//    $stm1 = "INSERT INTO diagram (title, description, createdDate, lastUpdate, size) 
//        VALUES('test2', 'Test description2', '2012-11-23 12:34:56', '2012-11-24 13:34:56', 1023)";
//    $ok1 = sqlite_exec($dbhandle, $stm1);
//    if (!$ok1) die("Cannot execute statement.");

    #echo "Table diagram created successfully";    
    header('Location: ./play.php');
}else if($_REQUEST['action'] == 'populate'){
//    $stm1 = "INSERT INTO diagram (title, description, createdDate, lastUpdate, size) 
//        VALUES('test', 'Test description', '2012-11-23 12:34:56', '2012-11-24 13:34:56', 1023)";
//    $ok1 = sqlite_exec($dbhandle, $stm1);
//    if (!$ok1) die("Cannot execute statement.");
//
//    $stm1 = "INSERT INTO diagram (title, description, createdDate, lastUpdate, size) 
//        VALUES('test2', 'Test description2', '2012-11-23 12:34:56', '2012-11-24 13:34:56', 1023)";
//    $ok1 = sqlite_exec($dbhandle, $stm1);
//    if (!$ok1) die("Cannot execute statement.");  
    
    diagramCreate($dbhandle, 'Test', 'Simple description', true);
    header('Location: ./play.php');
}else if($_REQUEST['action'] == 'delete'){
    diagramDeleteById($dbhandle, $_REQUEST['diagramId']);
    header('Location: ./play.php');
}else{//default view?>
    <html>
        <body>
            <table border="1">
                <tr>
                    <td>id</td>
                    <td>title</td>
                    <td>description</td>
                    <td>public</td>
                    <td>createdDate</td>
                    <td>lastUpdate</td>
                    <td>delete</td>
                </tr>
            <?
            $diagrams = diagramGetAll($dbhandle);
            foreach($diagrams as $diagram){?>
                <tr>
                    <td><?=$diagram->id?></td>
                    <td><?=$diagram->title?></td>
                    <td><?=$diagram->description?></td>
                    <td><?=$diagram->public?></td>
                    <td><?=$diagram->createdDate?></td>
                    <td><?=$diagram->lastUpdate?></td>
                    <td><a href="?action=delete&diagramId=<?=$diagram->id?>">delete</a></td>
                </tr>
            <?}?>
            </table> 
            <a href="?action=init">Init</a> | 
            <a href="?action=populate">Populate</a>
        </body>
    </html>
<?}?>
        


<?
sqlite_close($dbhandle);
?>