php/sqliteA.php

<?php
outBegin('start of php '. __FILE__ . ' sapi=' . php_sapi_name());
#phpinfo();
out("class_exists(SQLite3) = " . class_exists('SQLitE3'));
$db = new SQLite3('mysqlitedb.db');
out("sqlite version" . a2s($db->version()));
# query("drop table tt1");
# query("drop view tt1Pa");

$results = query('SELECT * FROM tt1Pa');

if ( ! $results and preg_match('/^no such table: /', $db->lastErrorMsg())) {
        out("table does not exist, trying to create");
        $cre = query('create table tt1(name, txt, pa references tt1(rowid))');
        $cre = query('create unique index tt1INa on tt1 (name)');
        $cre = query("create trigger tt1Tr1 after update on tt1 for each row begin update tt1 set txt = new.txt || ' <--updTri--< ' || old.txt  where rowid = old.rowid; end;");
        $cre = query(" create view tt1Pa as with  pp as ( select rowid pk, i.*, '' paCh from tt1 i "
            . " union all select c.pk, c.name, c.txt, p.pa, paCh || p.rowid || '>' || p.name || ' ' from pp c join tt1 p on c.pa = p.rowid"
            . ") select pk, name, txt, max(paCh) paCh from pp group by pk, name, txt");
        $ins = query("insert into tt1 values('walter', 'das bin ich', null)");
        $p = $db->lastInsertRowId();
        $ins = query("insert into tt1 select 'gregor', 's1', $p union all select 'david', 's2', $p union all select 'florian', 's3', $p");
        $p = $db->querySingle("select rowid from tt1 where name = 'david'");
        $ins = query("insert into tt1 select 'cyril', 'e3', $p union all select 'merlin', 'e5', $p");
        $p = $db->querySingle("select rowid from tt1 where name = 'florian'");
        $ins = query("insert into tt1 select 'alissia', 'e4', $p");
        $ins = query("update tt1 set txt = 'updated' where name = 'walter'");
        out("inserted " . $db->lastInsertRowId() . ", changes ". $db->changes());
        $results = query('SELECT * FROM tt1Pa');
    }
out("--- metadata ---");
query("select * from sqlite_master");
query("pragma table_info('tt1')");
query("pragma foreign_key_list('tt1')");
outEnd();

function query($sql) {
    global $db;
    $res = $db->query($sql);
    if (! $res) { 
        out("sqlite3 error code=" . $db->lastErrorCode() . " msg=" . $db->lastErrorMsg());
        out(".     sql = " . $sql);
    } else if (! get_class($res)) {
        out("result = $res from $sql");
    } else if ($res->numColumns() <= 0 ) {
        out("result ok, " . $res->numColumns() . " numColumns from $sql");
    } else {
        out('--- ' .get_class($res) . " with " . $res->numColumns() . ' columns', 1);
        for ($cx = 0; $res->columnName($cx); $cx++)
            out(' ' . $res->columnName($cx), 0);
        out(" from sql $sql", 2);
        for ($rx=0; $row = $res->fetchArray(SQLITE3_ASSOC);) {
            out(++$rx . a2s($row));
            }
        out("--- $rx rows fetched");
     }
    return $res;
}
    
function a2s($a) {
    $r = '';
    foreach($a as $na => $va) 
        $r .= " $na=$va";
    return $r;
}

function out($txt, $fun=3) {
    if (php_sapi_name() == 'cli') {
        if ($fun >= 2)
            $txt .= "\n";
    } else {
        if ($fun >= 2)
            $txt .= "</li>\n";
        if ($fun == 1 or $fun == 3)
            $txt = '<li>' . $txt;
    }
    echo $txt;
}
function outBegin($txt) {
    if (php_sapi_name() !== 'cli') 
        $txt = "<h1>$txt</h1>\n<ul>";
    echo "$txt\n";    
}
function outEnd() {
    if (php_sapi_name() !== 'cli') {
        echo "</ul>\n<h2>source " . __file__ . "</h2>";
        highlight_file( __file__ );
        }
}

# echo "set_error_handler " . set_error_handler('myErr') . "\n";
# echo "after set\n";
# $results = $db->query('SELECT bar FROM foo');
# echo "restore_error_handler " . restore_error_handler(). "\n";
# echo "after restpr\n";

function myErr($no, $txt, $fi, $li, $con) {
    echo 'myErr handles ' . $no .': ' . $txt . "\n";
    echo '    line ' . $li . ' in ' . $fi . "\n";
#    var_dump($con);
    return 1;
}

?>