⚠ In case you've missed it we have migrated to our new website, with a brand new forum. For more details about the migration you can read our blog post for website migration. This forum is read-only and soon will be archived. ⚠


Suport for sqlite3

sqlite3

  • Please log in to reply
2 replies to this topic

#1 rolo2012

rolo2012

    Member

  • Members
  • PipPip
  • 12 posts

Posted 05 July 2012 - 06:46 PM

I made this model for sqlite3 the primary key may by autoincremental.

<?php
class grocery_crud_sqlite3  extends grocery_crud_model{
   
    function get_list()
    {
	 if($this->table_name === null)
	  return false;
	
	 $select = "`{$this->table_name}`.*";
	
	 //set_relation special queries
	 if(!empty($this->relation))
	 {
	  foreach($this->relation as $relation)
	  {
	   list($field_name , $related_table , $related_field_title) = $relation;
	   $unique_join_name = $this->_unique_join_name($field_name);
	   $unique_field_name = $this->_unique_field_name($field_name);
	  
    if(strstr($related_field_title,'{'))
    {
	 $related_field_title = str_replace(" ","&nbsp;",$related_field_title);
									   
								   $rfield_title=str_replace("'","\\'",$related_field_title);
								   $apertura="'||COALESCE({$unique_join_name}.";
								   $cierre=", '')||'";
								   $replace2=str_replace(array('{','}'),array($apertura,$cierre),$rfield_title);
								  
		   $select .= ", ('$replace2') as $unique_field_name";
    }
	   else
	   {	  
	    $select .= ", $unique_join_name.$related_field_title AS $unique_field_name";
	   }
	  
	   if($this->field_exists($related_field_title))
	    $select .= ", `{$this->table_name}`.$related_field_title AS '{$this->table_name}.$related_field_title'";
					    //var_dump($select);			   
	  }
	 }
	
	   
	 //set_relation_n_n special queries. We prefer sub queries from a simple join for the relation_n_n as it is faster and more stable on big tables.
	 if(!empty($this->relation_n_n))
	 {
   $select = $this->relation_n_n_queries($select);
	 }
	 
	 $this->db->select($select, false);	
	
	 if(CI_VERSION == '2.1.0' || CI_VERSION == '2.1.1')//This hack is only for the release 2.1.1 that it seems that it has lot of bugs. They didn't even change the CI_VERSION to 2.1.1!
	 {
	  $results = $this->_hack_for_CI_2_1_1()->data;
	 }
	 else
	 {
	  $results = $this->db->get($this->table_name)->result();
	 }
	
	 return $results;
    }
    protected function _hack_for_CI_2_1_1()
    {
	 //Quietly check if the query will be executed or not
	 $temp_debug_value = $this->db->db_debug;
	 $this->db->db_debug = false;
	
	 $db_result = $this->db->get($this->table_name);
	
	 //back to normal
	 $this->db->db_debug = $temp_debug_value;
	
	 /* Don't worry ONLY if the query doesn't execute correctly we will try for a second chance. */
	 if($db_result === false)
	 {
	  /* I got you! Now we will try to execute the query with the normal way and without the bug of the CI */
	  $query = str_replace("`","",$this->db->last_query());
	 
	  $results = array();
	  /**
	   * Second chance let's execute again the query with the old way as codeigniter add always AUTOMATICALLY grave_accent.  There is not another way to deal with this. I know this is an ugly solution but
	   * I couldn't figure out another solution without changing the core of CI. If anyone has another idea about this it will be appreciated.
	   */
	  $db_query = $this->db->query($query) or show_error("<b>Query failed:</b> ".mysql_error()."<br/><b>Actual query:</b> ".$query);
	 
	 
	  $results = $db_query->result_array();
	 
	 
	  $results = (object)array('data' => $results, 'num_rows' => $db_query->num_rows() );
	 
	  return $results;
	 }
	 else
	 {
	  return (object)array('data' => $db_result->result(), 'num_rows' => $db_result->num_rows());
	 }
    }
   
   
    function get_relation_array($field_name , $related_table , $related_field_title, $where_clause, $order_by, $limit = null, $search_like = null)
    {
	 $relation_array = array();
	 $field_name_hash = $this->_unique_field_name($field_name);
	
	 $related_primary_key = $this->get_primary_key($related_table);
	
	 $select = "$related_table.$related_primary_key, ";
	
	 if(strstr($related_field_title,'{'))
	 {
	  $related_field_title = str_replace(" ", "&nbsp;", $related_field_title);
			    $apertura="'||COALESCE(";//"'||COALESCE({$unique_join_name}.";
			    $cierre=", '')||'";//", '')||'";
	  $select .= "('".str_replace(array('{','}'),array($apertura,$cierre),str_replace("'","\\'",$related_field_title))."') as $field_name_hash";
	 }
	 else
	 {
	  $select .= "$related_table.$related_field_title as $field_name_hash";
	 }
	
	 $this->db->select($select,false);
	 if($where_clause !== null)
	  $this->db->where($where_clause);
	 if($where_clause !== null)
	  $this->db->where($where_clause);	
	 if($limit !== null)
	  $this->db->limit($limit);	
	
	 if($search_like !== null)
	  $this->db->having("$field_name_hash LIKE '%".$this->db->escape_like_str($search_like)."%'");
	
	 $order_by !== null
	  ? $this->db->order_by($order_by)
	  : $this->db->order_by($field_name_hash);
	
	 $results = $this->db->get($related_table)->result();
	
	 foreach($results as $row)
	 {
	  $relation_array[$row->$related_primary_key] = $row->$field_name_hash;
	 }
	
	 return $relation_array;
    }
   
    function field_exists($field,$table_name = null)
    {
	 if(empty($table_name))
	 {
		    $table_name = $this->table_name;
	 }	  
	    foreach($this->db->query("pragma table_info($table_name);")->result() as $db_field_type)
	 {
		    if($db_field_type->name==$field){
			    return true;
		    }
	 }
	   
	 return false;
    }   
   
   
	  function get_field_types_basic_table()
    {
	 return $this->get_field_types($this->table_name);
    }
   
    function get_field_types($table_name)
    {
	    $db_field_types = array();
	    foreach($this->db->query("pragma table_info($table_name);")->result() as $db_field_type)
	 {
	  $type = explode("(",$db_field_type->type);
	  $db_type = $type[0];
	 
	  if(isset($type[1]))
	  {
	   $length = substr($type[1],0,-1);
	  }
	  else
	  {
	   $length = '';
	  }
			    $db_field_types[$db_field_type->name]=new stdClass();
			    $db_field_types[$db_field_type->name]->max_length=
	  $db_field_types[$db_field_type->name]->db_max_length = $length;
			    $db_field_types[$db_field_type->name]->type=
	  $db_field_types[$db_field_type->name]->db_type = $db_type;
	  $db_field_types[$db_field_type->name]->db_null = $db_field_type->notnull == 1;
			   
			    //Parche
	  $db_field_types[$db_field_type->name]->db_extra = $db_field_type->pk?'auto_increment':'';//???
			   
			    $db_field_types[$db_field_type->name]->primary_key = $db_field_type->pk;
			    $db_field_types[$db_field_type->name]->default=$db_field_type->dflt_value;
			    $db_field_types[$db_field_type->name]->name=$db_field_type->name;
	 }
	   
	 return $db_field_types;
    }
}


#2 rolo2012

rolo2012

    Member

  • Members
  • PipPip
  • 12 posts

Posted 05 July 2012 - 06:56 PM

I used the driver for sqlite3 in codeigniter in this link http://codeigniter.com/wiki/SQLite3/

#3 imjooseo

imjooseo

    Newbie

  • Members
  • Pip
  • 3 posts

Posted 11 July 2012 - 08:13 AM

thanks! I'll test it




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users