⚠ 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 is an archived forum. ⚠

  •     

profile picture

Suport for sqlite3



rolo2012

rolo2012
  • profile picture
  • Member

Posted 05 July 2012 - 18: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;
}
}

rolo2012

rolo2012
  • profile picture
  • Member

Posted 05 July 2012 - 18:56 PM

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

imjooseo

imjooseo
  • profile picture
  • Member

Posted 11 July 2012 - 08:13 AM

thanks! I'll test it