expenses/php/db.php

<?
    $DB_FILE  = "$PROJECT_PATH/database.db";
    $db = null;

    function initDatabaseConnection() {
        global $DB_FILE, $db;

        if(!isset($db) || $db == null) {
            $db = new SQLite3($DB_FILE);
            $db->busyTimeout(3000);
            $db->exec("PRAGMA foreign_keys = ON"); // enforce foreign keys
        }
    }

    function closeDatabaseConnection() {
        global $db;

        if(isset($db)) {
            if($db != null) {
                $db->close();
            }
            unset($db);
        }
    }

    function bindVariables($sql /* [arg1 [,arg2[,...]]] */) {
        global $db;

        $args = func_get_args();
        $skip = true;
        foreach($args as $arg) {
            if($skip) { // skip first argument
                $skip = false;
                continue;
            }
            $sql = preg_replace("/\?/", $db->escapeString($arg), $sql, 1);
        }
        return $sql;
    }
    
    function dbUpdate($sql /* [arg1 [,arg2[,...]]] */) {
        global $db;

        $stmt = $db->prepare($sql);
        if($stmt) {
			$args = func_get_args();
			$i = 0;
			foreach($args as $arg) {
				if($i > 0) { // skip first argument which is $sql
					$stmt->bindValue($i, $arg, getDbType($arg));
				}
				$i++;
			}
			$success = $stmt->execute();
			if($success) {
				return $db->changes();
			}
		}
		return false;
    }
    
    function dbQuery($sql /* [arg1 [,arg2[,...]]] */) {
        global $db;

        $stmt = $db->prepare($sql);
        if($stmt) {
			$args = func_get_args();
			$i = 0;
			foreach($args as $arg) {
				if($i > 0) { // skip first argument which is $sql
					$stmt->bindValue($i, $arg, getDbType($arg));
				}
				$i++;
			}
			$resultSet = $stmt->execute();
			if($resultSet) {
				$rows = array();
				while($row = $resultSet->fetchArray()) {
					array_push($rows, $row);
				}
				return $rows;
			}
		}
		return false;
    }
    
    function getDbType($arg) {
		$phpType = gettype($arg);
		if($phpType == "string")       return SQLITE3_TEXT;
		else if($phpType == "integer") return SQLITE3_INTEGER;
		else if($phpType == "double")  return SQLITE3_FLOAT;
		else if($phpType == "NULL")    return SQLITE3_NULL;
		else die("Unsupported type \"$phpType\"");
	}

    /*function dbQuery($sql) {
        global $db;

        $rows = array();
        $resultSet = $db->query($sql);
		while($row = $resultSet->fetchArray()) {
            array_push($rows, $row);
		}
        return $rows;
    }*/

?>