Location: PHPKode > scripts > SQLite DB Handler > sqlite-db-handler/sqlite_class.php
<?php
// +----------------------------------------------------------------------+
// | Proprietary License  Onidesk  I.C.S. -  PORTUGAL                     |
// +----------------------------------------------------------------------+
// | Author: José Augusto Ferreira Franco < http://onidesk.sytes.net>     |
// +----------------------------------------------------------------------+
//


/**
 * @version 1.0
 * @author   : José Augusto Ferreira Franco <hide@address.com>
 * @Webname :  Webmaster Guto Ferreira
 */



/***
  *@  Definição da classe
  *@
  *@  Class defenition
  **/

class DB{

 var $conn;
 var $table;

/***
  *@ Método constructor
  *@
  *@  Constructor method
  **/

 function db()
 {
 $this->conn= NULL;
 }
 
 /***
  *@ Cria  a base de dados
  *@
  *@ Builds database
  **/

 
 function Create_db($dbase)
 {
 if($this->isExtension($dbase)==true){
 $this->conn = @sqlite_open($dbase,0666,$error);
 }
 $conn = $this->conn;
 if(!is_resource($this->conn)&&(!file_exists($dbase)))
 {
 unset($this->conn);
 @unlink(basename($dbase));
 die($this->alert(10));
 }

 return $conn;
 }
 
 
 /**@
  *@ (pt) Limita o número de extensões para a base de addos
  *@ (en) Limits databases extensions
  **/

  function isExtension($db)
  {
   list($db1,$db2) = array('db','txt');
   if(is_string($db)){
   list($name,$ext) = explode(".",$db);
   if(($ext != $db1) && ($ext != $db2)){
   die($this->alert(11));
   }else{
   return true;
    }
   }
 }
 
/**@
  *@ (pt) Cria a tabela e respectivos campos
  *@ (en) Creates table and respective fields
  **/
  function Create_Table($values){
 foreach($values as $Rows => $Type){
 $arrfields[] = $Rows;
 $dataType[]  = $Type;
 $arrValues[] = "".$Rows." ".$Type."";
 }
 
  /// nome da tabela
 $table = $values["TABLE"];
 
 
$arr_rows = implode(", ",$arrfields);

 ///// nome dos campos da tabela
$this->fields = substr($arr_rows,10,strlen($arr_rows));


$strFields = implode(", ",$arrValues);

 /// campos e tipo de dados
$Fields = substr($strFields,strlen($table)+7,strlen($strFields));

if($this->Check_Table($table)!=true){
$gen_table = $this->Exec_Query("CREATE TABLE ".$table." (".$Fields.")");
}

  if(!$gen_table){
  print $this->alert(12);
  }
  return $gen_table;
 } 
/**
  *@ Insere os dados na tabela
  *@ Inserts data on table
  **/
 
 function Insert($table,$fields)
{
 if(is_array($fields)){
       foreach($fields as $rows => $values){
       $arrRows[] = "".$rows."";
       $arrValues[]= "'".$values."'";
       }

    $strRows = implode(", ", $arrRows);
    $strValues = implode(", ", $arrValues);
 }
 $query =  "INSERT INTO ".$table."($strRows) VALUES ($strValues);";
 $insert = $this->Exec_Query($query);

 if(!$insert){
 print $this->alert(13);
  }
 return $insert;
 }



 function Select($args){
  if($args["ORDER"]!=''){
  $order = "ORDER BY ".$args["ORDER"];    /////  $args["ORDER"] podemos informar também a disposição aquí ex. "ORDER"=>"name ASC"
  }
  if(is_array($args)){
  if(is_resource($args["RESOURCE"])){
  switch($args["QUERY"]){
  case 1:
  $query = "SELECT ".$args["ROWS"]." FROM ".$args["TABLE"]." WHERE ".$args["AFFECT"]." ='".$args["CURRENT"]."' ".$order.";";
  break;
  case 2:
  $query = "SELECT ".$args["ROWS"]." FROM ".$args["TABLE"]." WHERE ".$args["AFFECT"]." ='".$args["CURRENT"]."' AND ".$args["NEWAFFECT"]."='".$args["NEWCURRENT"]."' ".$order.";";
   ///// PODEMOS CONTINUAR A CONSTRUIR AS CONSULTAS(queries) DESEJADAS
  break;
  case 3:
  $query = "SELECT * FROM ".$args["TABLE"].";";
  break;
  default:
  $query = $args["SQL"];
  }
  return $query;
    }
  }else{
  return false;
  }
}



function GetData($query,$rows=array()){
$temp = array();

while($data=sqlite_fetch_array($query,SQLITE_ASSOC)) {
	foreach($rows as $lines ){
		$temp[] = $data[$lines];
	}
   @sqlite_next($query);
}
return $temp;
}
 
/**@
  *@ (pt)  Executa uma pedido à base de dados
  *@ (en) Performs a query to database
  **/
 
 function Exec_Query($sql)
 {
  return sqlite_query($this->conn,$sql);
 }
 
 
 /**
  *@ (pt)  Permite fazer uma alteração à tabela
  *@ (en) Allows to altear the table
      **/
   function  Alter_Table($args=array())
     {

      $strRows = $this->Data_Types($args['rows']);
      $rows = ereg_replace("[ \t]+|[()0-9]*","",$strRows); // retira os caracteres tipo (int)


   $query .= "BEGIN TRANSACTION;";
   $query .= "CREATE TEMPORARY TABLE {$args['table']}_bkp ({$args['rows']});";
   $query .= "INSERT INTO {$args['table']}_bkp SELECT {$args['save']} FROM {$args['table']};";
   $query .= "DROP TABLE $this->table;";
   $query .= "CREATE TABLE {$args['table']} ({$args['rows']});";
   $query .= "INSERT INTO {$args['table']} SELECT {$rows} FROM {$args['table']}_bkp;";
   $query .= "DROP TABLE {$args['table']}_bkp;";
   $query .= "COMMIT;";

       $_query = explode(";",$query);
        for($i=0;$i<8;$i++)
        {
         $transaction = $_query[$i];
         $exec = $this->Exec_Query($transaction);
        }
       $results = $this->Fetch_Results($exec,0);
       return $results;
     }



/**
  *@ (pt) Permite 'varrer' de dados permitidos no SQLite
  *@ (en) Allows 'to sweep' all data types allowed on SQLite
  **/

   function Data_Types($arr_rows)
     {
       $Data_Types = array("INTEGER"   => "",
                           "FLOAT"     => "",
                           "BLOB"      => "",
                           "BOOLEAN"   => "",
                           "CHARACTER" => "",
                           "CLOB"      => "",
                           "KEY"       => "",
                           "NATIONAL"  => "",
                           "NUMERIC"   => "",
                           "NVARCHAR"  => "",
                           "PRIMARY"   => "",
                           "TEXT"      => "",
                           "TIMESTAMP" => "",
                           "UNIQUE"    => "",
                           "VARCHAR"   => "",
                           "VARYING"   => "");

       $rows = strtr($arr_rows,$Data_Types);

       return $rows;

      }
      
      



 
 

/**@
  *@ (pt) Avalia a existência da tabela
  *@ (en) Checks for table existence
  **/

   function Check_Table($table)
      {
       $result = $this->Exec_Query("SELECT name FROM sqlite_master WHERE type='table' AND name='".$table."'");
       if(sqlite_num_rows($result) > 0){
       return true;
       }
      }
      
 /**
  *@ (pt) Forma como os dados são devolvidos
  *@ (en) Way as data is returned
  **/
  function Fetch_Results($query,$type)
      {
       switch($type){
       case '0':  # retorna os indices associativos (campos nomeados)
       $result = sqlite_fetch_all($query,SQLITE_ASSOC);
       break;
       case '1': # retorna apenas os indices numéricos
       $result = sqlite_fetch_all($query,SQLITE_NUM);
       break;
       case '2':  # retorna os indices associativos ou numéricos
       $result = sqlite_fetch_all($query,SQLITE_BOTH);
       break;
       case '3': #Returna um array de tipo de colunas de determinda tabela
       $result = sqlite_fetch_column_types($this->table,$this->handle, SQLITE_ASSOC);
       break;
       case '4':
       $result = sqlite_fetch_single($query);
       break;
       default:
       return true;
       }
       return $result;
     }


/**
  *@ (pt)  Aumenta a performance do SQLite
  *@ (en)  Increase the performance of SQLite
  **/
   function Turbo()
     {
      $maxMode = $this->Exec_Query($this->Pragma(10,'off'));
      $maxMode = $this->Exec_Query($this->Pragma(11,'off'));
      $maxMode = $this->Exec_Query($this->Pragma(12,'true'));
      return $maxMode;
     }


/**
  *@ (pt) Generaliza as claúsulas para performance do SQLITE
  *@ (en) Generalizes Pragma statements for SQLITE performance
  **/
  function Pragma($num,$arg='')
    {
     $pragma[10]= "PRAGMA default_synchronous = ".$arg;
     $pragma[11]= "PRAGMA empty_result_callbacks = ".$arg;
     $pragma[12]= "PRAGMA auto_vacuum = ".$arg;
     return $pragma[$num];
    }



/**@
  *@ (pt) Fecha a ligação com  a base de dados
  *@ (en) Closes the connection with database
  **/

   function Close()
     {
      return sqlite_close($this->conn);
     }

/**@
  *@ (pt) Apaga o valor das váriáveis da memória
  *@ (en) Deletes the variables values from memory
  **/

   function noBuffer()
     {
       unset($this->conn);
       unset($this->db);
       unset($this->table);
       unset($this->fields);
     }

/**@
  *@ (pt) Define as mensagens utilizadas na classe
  *@ (en) Defines messages used on this class
  **/

 
 
  function alert($num)
  {
   switch(lang){
   case 'pt':
   $alert[10]= "Erro ao tentar criar a base de dados";
   $alert[11]= "Apenas as extensões *.db e *.txt são válidas";
   $alert[12]= "Erro ao tentar criar a tabela";
   $alert[13]= "Erro ao inserir";
   break;
   case 'en':
   $alert[10]= "Error when trying to create database";
   $alert[11]= "Only extensions *.db and *.txt are valid";
   $alert[12]= "Error when trying to buil table";
   $alert[13]= "Error on inserting";
   break;
   default:
   $alert[10]= "Erro ao tentar criar a base de dados";
   $alert[11]= "Apenas as extensões *.db e *.txt são válidas";
   $alert[12]= "Erro ao tentar criar a tabela";
   $alert[13]= "Erro ao inserir";
     }
   return $alert[$num];
  }


}// fecha a classe


?>
Return current item: SQLite DB Handler