sqlite2 support

[IIyx]

I've made class to handle sqlite2 support/ But it isn't working properly. Need help.

Code: Select all

<?php

require_once dirname(__FILE__)."/../pfccontainer.class.php";

class pfcContainer_sqlite extends pfcContainerInterface
{
  var $_db = null;
  var $_sql_create_table = '
  CREATE TABLE "%table%" (
  "server" %fieldtype_server% NOT NULL default "",
  "group" %fieldtype_group% NOT NULL default "",
  "subgroup" %fieldtype_subgroup% NOT NULL default "",
  "leaf" %fieldtype_leaf% NOT NULL default "",
  "leafvalue" %fieldtype_leafvalue% NOT NULL,
  "timestamp" %fieldtype_timestamp% NOT NULL default 0,
  PRIMARY KEY  ("server","group","subgroup","leaf"));
  ';

  #  CREATE INDEX "%table%_idx" ON "%table%" ("server","group","subgroup","timestamp");
 
  function pfcContainer_sqlite()
  {
    pfcContainerInterface::pfcContainerInterface();
  }

  function getDefaultConfig()
  {   
    $cfg = pfcContainerInterface::getDefaultConfig();
    $cfg["sqlite_database"] = 'phpfreechat.sqlite';
    $cfg["sqlite_table"]    = 'phpfreechat';
    // advanced parameters (don't touch if you don't know what your are doing)
    $cfg["sqlite_fieldtype_server"] = 'varchar(32)';
    $cfg["sqlite_fieldtype_group"] = 'varchar(64)';
    $cfg["sqlite_fieldtype_subgroup"] = 'varchar(128)';
    $cfg["sqlite_fieldtype_leaf"] = 'varchar(128)';
    $cfg["sqlite_fieldtype_leafvalue"] = 'text';
    $cfg["sqlite_fieldtype_timestamp"] = 'int(11)';
    return $cfg;
  }

  function init(&$c)
  {
    $errors = pfcContainerInterface::init($c);

    // connect to the db
    $db = $this->_connect($c);
    if ($db === FALSE)
    {
      $errors[] = _pfc("sqlite container: connect error");
      return $errors;
    }

    // create the table if it doesn't exists
    $table_exists = false;
   
    $query = 'SELECT * FROM sqlite_master WHERE type="table" AND name="'.$c->container_cfg_sqlite_table.'"';
    $result = sqlite_query($db, $query);
    $table_exists = sqlite_fetch_array($result);

    if (!$table_exists) {   
    // create the table if it doesn't exists
    $query = $this->_sql_create_table;
    $query = str_replace('%table%',               $c->container_cfg_sqlite_table,$query);
    $query = str_replace('%fieldtype_server%',    $c->container_cfg_sqlite_fieldtype_server,$query);
    $query = str_replace('%fieldtype_group%',     $c->container_cfg_sqlite_fieldtype_group,$query);
    $query = str_replace('%fieldtype_subgroup%',  $c->container_cfg_sqlite_fieldtype_subgroup,$query);
    $query = str_replace('%fieldtype_leaf%',      $c->container_cfg_sqlite_fieldtype_leaf,$query);
    $query = str_replace('%fieldtype_leafvalue%', $c->container_cfg_sqlite_fieldtype_leafvalue,$query);
    $query = str_replace('%fieldtype_timestamp%', $c->container_cfg_sqlite_fieldtype_timestamp,$query);
    $result = sqlite_query($query, $db);
    if ($result === FALSE)
    {
      $errors[] = _pfc("sqlite container: create table error '%s'",sqlite_last_error($db));
      return $errors;
    }
    }
    return $errors;
  }

  function _connect($c = null)
  {
    if (!$this->_db)
    {
      if ($c == null) $c =& pfcGlobalConfig::Instance();
      $this->_db = sqlite_open($c->container_cfg_sqlite_database);
    }
    return $this->_db;
  }

  function setMeta($group, $subgroup, $leaf, $leafvalue = NULL)
  {
    $c =& pfcGlobalConfig::Instance();     
     
    $server = $c->serverid;   
    $db = $this->_connect();

    if ($leafvalue == NULL){$leafvalue="";};
   
    $sql_count = 'SELECT COUNT(*) AS C FROM '.$c->container_cfg_sqlite_table.' WHERE "server"="'.$server.'" AND "group"="'.$group.'" AND "subgroup"="'.$subgroup.'" AND "leaf"="'.$leaf.'" LIMIT 1';
    $sql_insert= 'REPLACE INTO '.$c->container_cfg_sqlite_table.' ("server", "group", "subgroup", "leaf", "leafvalue", "timestamp") VALUES ("'.$server.'", "'.$group.'", "'.$subgroup.'", "'.$leaf.'", "'.addslashes($leafvalue).'", "'.time().'")';
    $sql_update='UPDATE '.$c->container_cfg_sqlite_table.' SET "leafvalue"="'.addslashes($leafvalue).'", "timestamp"="'.time().'" WHERE  "server"="'.$server.'" AND "group"="'.$group.'" AND "subgroup"="'.$subgroup.'" AND "leaf"="'.$leaf.'"';
    $res = sqlite_query($sql_count, $db);
    $row = sqlite_fetch_array($res, SQLITE_ASSOC);
    if( $row['C'] == 0 )
    {
      sqlite_query($sql_insert, $db);
      return 0; // value created
    }
    else
    {
      if ($sql_update != "")
      {
        sqlite_query($sql_update, $db);
      }
      return 1; // value overwritten
    }
  }

 
  function getMeta($group, $subgroup = null, $leaf = null, $withleafvalue = false)
  {
    $c =& pfcGlobalConfig::Instance();     

    $ret = array();
    $ret["timestamp"] = array();
    $ret["value"]     = array();
   
    $server = $c->serverid;   
    $db = $this->_connect();
   
    $sql_where = "";
    $sql_group_by = "";
    $value = "leafvalue";
   
    if ($group != NULL)
    {
      $sql_where   .= ' AND "group"="'.$group.'"';
      $value        = "subgroup";       
      $sql_group_by = 'GROUP BY '.$value;
    }   
   
    if ($subgroup != NULL)
    {
      $sql_where   .= ' AND "subgroup"="'.$subgroup.'"';
      $value        = "leaf";       
      $sql_group_by = "";
    }
   
    if ($leaf != NULL)
    {
      $sql_where   .= ' AND "leaf"="'.$leaf.'"';
      $value        = "leafvalue";
      $sql_group_by = "";
    }
   
    $sql_select='SELECT '.$value.', timestamp FROM '.$c->container_cfg_sqlite_table.' WHERE "server"="'.$server.'" '.$sql_where.' '.$sql_group_by.' ORDER BY timestamp';
   
    if ($sql_select != "")
    {
      $thisresult = sqlite_query($sql_select, $db);
      if (sqlite_num_rows($thisresult))
      {
      while ($regel = sqlite_fetch_array($thisresult))
        {
          $ret["timestamp"][] = $regel["timestamp"];
          if ($value == "leafvalue")
          {
            if ($withleafvalue)
              $ret["value"][]     = $regel[$value];
            else
              $ret["value"][]     = NULL;
          }
          else
            $ret["value"][] = $regel[$value];
        }       
      }
      else
        return $ret;
    }
    return $ret;
  }


  function incMeta($group, $subgroup, $leaf)
  {
    $c =& pfcGlobalConfig::Instance();     
     
    $server = $c->serverid;   
    $db = $this->_connect();
    $time = time();

    // search for the existing leafvalue
    $sql_count = 'SELECT COUNT(*) AS C FROM '.$c->container_cfg_sqlite_table.' WHERE "server"="'.$server.'" AND "group"="'.$group.'" AND "subgroup"="'.$subgroup.'" AND "leaf"="'.$leaf.'" LIMIT 1';
    $res = sqlite_query($sql_count, $db);
    $row = sqlite_fetch_array($res, SQLITE_ASSOC);
    if( $row['C'] == 0 )
    {
      $leafvalue = 1;
      $sql_insert='REPLACE INTO '.$c->container_cfg_sqlite_table.' ("server", "group", "subgroup", "leaf", "leafvalue", "timestamp") VALUES ("'.$server.'", "'.$group.'", "'.$subgroup.'", "'.$leaf.'", "'.$leafvalue.'", "'.$time.'")';
      sqlite_query($sql_insert, $db);
    }
    else
    {
     
      $res = sqlite_query('SELECT leafvalue FROM '.$c->container_cfg_sqlite_table.' WHERE ROWID=LAST_INSERT_ROWID();', $db);     
      $row = sqlite_fetch_array($res, SQLITE_ASSOC);
      $leafvalue = $row['leafvalue'] + 1;   
      $sql_update='UPDATE '.$c->container_cfg_sqlite_table.' SET "leafvalue"="'.$leafvalue.'", "timestamp"="'.$time.'" WHERE  "server"="'.$server.'" AND "group"="'.$group.'" AND "subgroup"="'.$subgroup.'" AND "leaf"="'.$leaf.'"';
      sqlite_query($sql_update, $db);
      $res = sqlite_query('SELECT LAST_INSERT_ROWID();', $db);     
      $row = sqlite_fetch_array($res, SQLITE_ASSOC);
      $leafvalue = $row['LAST_INSERT_ROWID()'];
    }
   
    $ret["value"][]     = $leafvalue;
    $ret["timestamp"][] = $time;

    return $ret;
  }


  function rmMeta($group, $subgroup = null, $leaf = null)
  {
    $c =& pfcGlobalConfig::Instance();     
   
    $server = $c->serverid;   
    $db = $this->_connect();
   
    $sql_delete = 'DELETE FROM '.$c->container_cfg_sqlite_table.' WHERE "server"="'.$server.'"';
   
    if($group != NULL)
      $sql_delete .= ' AND "group"="'.$group.'"';
   
    if($subgroup != NULL)
      $sql_delete .= ' AND "subgroup"="'.$subgroup.'"';

    if ($leaf != NULL)
      $sql_delete .= ' AND "leaf"="'.$leaf.'"';
   
    sqlite_query($sql_delete, $db);
    return true;
  }

  function encode($str)
  {
    return addslashes(urlencode($str));
  }
 
  function decode($str)
  {
    return urldecode(stripslashes($str));
  }
 
}

?>