Hi everyone.
I plan to add PDO support to groceryCRUD.
In order to do that, I need to change any MySQL specific query into the more generic one.
Below is some MySQL specific query that exists on /application/models/grocery_crud_model.php:
- CONCAT <-- work around: use PHP instead of query
- COALESCE <-- work around: use PHP instead of query
- GROUP_CONCAT <-- work around: use PHP instead of query
- SHOW COLUMNS FROM table_name <-- work around: do "SELECT * FROM table_name", get the key array. But this will only work if the table is not empty. OR even better $this->db->list_field (http://ellislab.com/codeigniter/user-guide/database/fields.html)
If any of you find some specific MySQL specific query, please post it here.
And if you have a better workaround, please also post it here.
It is gonna be cool if groceryCRUD can support PDO, but it gonna be cooler if we can also contribute to this "big leap".
If you want to use PDO, try to change /application/config/database.php into something like this:
$db['default']['hostname'] = 'mysql:host=localhost;port=3306;dbname=module_devel'; $db['default']['username'] = 'root'; $db['default']['password'] = 'toor'; $db['default']['database'] = 'module_devel'; $db['default']['dbdriver'] = 'pdo'; $db['default']['dbprefix'] = ''; $db['default']['pconnect'] = TRUE; $db['default']['db_debug'] = TRUE; $db['default']['cache_on'] = FALSE; $db['default']['cachedir'] = ''; $db['default']['char_set'] = 'utf8'; $db['default']['dbcollat'] = 'utf8_general_ci'; $db['default']['swap_pre'] = ''; $db['default']['autoinit'] = TRUE; $db['default']['stricton'] = FALSE;
UPDATE: here is the fork https://github.com/goFrendiAsgard/grocery-crud
I've also make a pull request https://github.com/scoumbourdis/grocery-crud/pull/191
It is working with CodeIgniter 3.0 and sqlite.
The changes are:
- Rename /application/libraries/grocery_crud.php into /application/libraries/Grocery_CRUD.php
- Rename grocery_crud class into Grocery_CRUD
- Change
$this->db->query("SHOW COLUMNS FROM `{$this->table_name}`")into
this->db->field_data($this->table_name)
and adjust the script below that. However, there is not "NULL" information when we use $this->db->field_data
-
Remove $this->db->limit(1) in db_delete function in the model, since it will produce error in sqlite.
-
Change
return $this->db->field_exists($field,$table_name);
into
$field_data_list = $this->db->field_data($table_name); foreach($field_data_list as $field_data){ if($field_data->name == $field) return TRUE; } return FALSE;Probably CodeIgniter guys doesn't complete $this->db->field_exists for pdo sqlite yet. However using $this->db->field_data() to emulate $this->db->field_exists is possible
UPDATE:
Today I try to play with postgre SQL with no luck. Below are the problems:
- postgre (and other standard SQL) use double quote ("), while mysql & sqlite use backtick (`). Does anyone know how to force CodeIgniter db driver to show escape character?
- postgre doesn't provide primary key information. So, user must explicitly do $crud->set_primary_key() for all the tables, otherwise the model will assume first field as primary key.
UPDATE:
[member="web-johnny"] has make a new repository (https://github.com/scoumbourdis/grocery-crud-databases). The repository contains some model skeletons. With this approach, I don't need to fully mess around with "generic SQL". So I fork the repository and here it is: https://github.com/goFrendiAsgard/grocery-crud-databases
- I add an "examples" controller. This controller will automatically detect your db-driver and choose appropriate model for you.
- I also add "grocery_crud_generic_model". I change backtick (`) into $this->db->protect_identifier(). Of course it cannot handle everything, but at least it should help.
- This is incomplete yet, so that I don't make a pull request yet.
UPDATE:
I've make some progress on PDO library. All "select" work for MySQL & sqlite. Postgre's primary key can be detected automatically.
https://github.com/goFrendiAsgard/grocery-crud-databases/compare
UPDATE:
The first pull request has been made: https://github.com/scoumbourdis/grocery-crud-databases/pull/1
- The select mode of all examples is fully working,
- The edit, insert, and delete operation is not tested yet.
- The test has been performed by using sqlite, mysql, and postgresql. PDO driver used for all of the case
UPDATE:
https://github.com/scoumbourdis/grocery-crud-databases is ready.... Now you can use groceryCRUD with PDO driver for MySQL, Sqlite & Postgree. However it is not fully tested yet. Please report if something doesn't work as it should.
Right now, enum & set field doesn't work on postgree. So, you might consider to use relation_n_n & relation instead
UPDATE:
Today I try to use GC 1.4 and CI 3.0 in No-CMS development branch. Some bugs occurred, especially when I try to use a table with a relation to itself. I've finally able to manage this and put another pull request https://github.com/scoumbourdis/grocery-crud-databases/pull/3
