⚠ 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

GroceryCRUD with Various Database Support.



goFrendiAsgard

goFrendiAsgard
  • profile picture
  • Member

Posted 15 June 2013 - 00:03 AM

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


goFrendiAsgard

goFrendiAsgard
  • profile picture
  • Member

Posted 15 June 2013 - 01:51 AM

Surprisingly enough: I found the problem was in $this->db->field_data(); Which is a CodeIgniter db library function.

Maybe CodeIgniter 3?

FURTHER INVESTIGATION for CodeIgniter 2.1.3:

CodeIgniter mysqli driver use "DESCRIBE table_name" when $this->db->field_data() called
while PDO driver use "SELECT TOP 1 FROM table_name". <-- this will produce error

 

Is there any generic way to call "DESCRIBE" which will work in all database?

However, this problem seems to not being occur in CodeIgniter 3.0 dev

 

If you want to integrate groceryCRUD with CodeIgniter 3.0 dev, please do the following:

  • Rename /application/libraries/grocery_crud.php into  /application/libraries/Grocery_CRUD.php
  • Rename grocery_crud class in the  /application/libraries/Grocery_CRUD.php into Grocery_CRUD


davidoster

davidoster
  • profile picture
  • Member

Posted 15 June 2013 - 06:52 AM

Hello [member=gofrendiasgard].

First of all for the uninitiated lets say that PDO means PHP Data Objects.

 

With this initiative you do, you try to address a common issue to most of the developers that use Grocery CRUD, the ability to have Grocery CRUD running with any database that CodeIgniter supports or even better any database that PHP supports.

So in essence we are talking about a huge thing if we try to make it happen.

This will be really helpful to all of us.

 

So, I call all the developers that have the knowledge, to help out with this.

 

 

Now, in order to be more specific:

I urge you to :

1. make a github to host the project

2. lets make the project as an extension to Grocery CRUD and not a replacement

3. lets have a different version for, GC 1.3.3 and CI 2.1.3

4. lets have a different version for, GC 1.4 and CI 3.0.dev

5. any other issues?

 

I would like to point two things

a.currently these are the drivers that PDO supports.

 

b. PDO has a specific way of handling transactions, we need to check that, this is compatible with the model code of the Grocery CRUD library and I believe that only [member=web-johnny] can gives us a definitive answer.


goFrendiAsgard

goFrendiAsgard
  • profile picture
  • Member

Posted 15 June 2013 - 10:52 AM

Hi [member="davidoster"]. Yes, I plan to fork groceryCRUD and give a pull request once it is done,

Since groceryCRUD itself has "set_model" function, I think it will be good to make another "PDO model", instead of modifying the already stable "groceryCRUD_model".

For now I'll only focus on how to make PDO work with groceryCRUD 1.4 and CI 3.0.
Honestly, I am not sure if it is gonna work. But once I'm able to make a simple prototype, I'll post the github link here.


davidoster

davidoster
  • profile picture
  • Member

Posted 15 June 2013 - 12:39 PM

For now I'll only focus on how to make PDO work with groceryCRUD 1.4 and CI 3.0.

Honestly, I am not sure if it is gonna work. But once I'm able to make a simple prototype, I'll post the github link here.

 

Just to say that both GC 1.4 and CI 3.0 are not stable enough. You might end up having a perfectly good code that you wrote that:

1. doesn't work NOW with the current GC 1.4 & Ci 3.0 BUT it could work later

2. does work NOW with the current GC 1.4 & Ci 3.0 BUT it won't work later

 

Just think about these before selecting GC 1.4 and CI 3.0.

Personal opinion: I would work first with the stable versions to see if it is doable.


goFrendiAsgard

goFrendiAsgard
  • profile picture
  • Member

Posted 15 June 2013 - 12:57 PM

You might probably right with "instability" issue.

But PDO drive in CodeIgniter 2.1.3 is "hopeless".
Doing "$this->db->field_data();" with PDO driver will always produce an error.
But CodeIgniter 3.0 seems to have a "stable enough" PDO support (with the same API).

Right now I'm working with CodeIgniter 3.0 and groceryCRUD 1.3.3.
So far, there is no problem (I use PDO driver to connect to MySQL database). So, what I need to do is repair some query so that it is also works for generic database. I'll post the fork link (maybe tomorrow) 


goFrendiAsgard

goFrendiAsgard
  • profile picture
  • Member

Posted 15 June 2013 - 15:01 PM

[member="davidoster"] and [member="web-johnny"]. Please see my edited first post.
What do you think?
I still have a doubt to submit a pull request.

I've also make a pull request.

 

But this is not finished yet. I need to make sure it CONCAT, COALESCE and GROUP_CONCAT also work generally. Otherwise, I should implement some workaround


web-johnny

web-johnny
  • profile picture
  • Administrator
  • 1,166 posts

Posted 16 June 2013 - 10:24 AM

Hello [member='goFrendiAsgard'],

 

I've seen  your pull request but I can't accept it :(

 

1. It seems that they are lot of file changes at pull request.

2. It seems that it is a bit incomplete

3. Can you please create an extension rather than change the basic model function? You can simply create a model that extends the basic model.

 

What do you think about that?

 

Anyway really thanks for all your effort and I hope that we can easily move to all kind of drivers in grocery CRUD. This is my first priority for later (after the release of 1.4 that it is a bit un-stable now and I can't release it)

 

Regards
Johnny


goFrendiAsgard

goFrendiAsgard
  • profile picture
  • Member

Posted 16 June 2013 - 14:16 PM

Hi [member="web-johnny"]. Yes, I can understand, that's why I firstly doubt about it.
I'll try to make an extension. I'll try to work on it again.


goFrendiAsgard

goFrendiAsgard
  • profile picture
  • Member

Posted 16 June 2013 - 14:48 PM

Hi again, [member="web-johnny"] I've make another pull request. https://github.com/scoumbourdis/grocery-crud/pull/193

 

I renamed the library name to match it class and added postgree & sqlite database schema.

 

I've also make an "extension" https://github.com/goFrendiAsgard/grocery-crud/commit/0ea8f5e1a8221abcc3d014d2ae276a415fa99c2c

This extension is actually work with sqlite3, but doesn't work with postgresql.

 

It is not an easy task, isn't it?  :D


web-johnny

web-johnny
  • profile picture
  • Administrator
  • 1,166 posts

Posted 16 June 2013 - 15:05 PM

Hello [member='goFrendiAsgard'],

 

It seems that this pull request is closed. I have an idea. I will create a new repository for all the models/databases extensions so people can easily download the extension of the database that they need.

 

At the future of course this will be included to grocery CRUD master release. What do you think about that?

 

Thanks

Johnny


web-johnny

web-johnny
  • profile picture
  • Administrator
  • 1,166 posts

Posted 16 June 2013 - 15:47 PM

There you go: :) https://github.com/scoumbourdis/grocery-crud-databases

 

So now this will be a different repository supported by the author of grocery CRUD (ehhmmm that's me B) ) so it will be at least updated.

 

At the master grocery CRUD I want to do those changes to implement those databases automatically with Adapter Pattern at the future, so for now this will be a good solution.

 

What do you think?

 

Cheers
Johnny


goFrendiAsgard

goFrendiAsgard
  • profile picture
  • Member

Posted 16 June 2013 - 15:56 PM

[member="web-johnny"]

Wow, you are genius...
This seems to be a good start. Now I (and everyone else who want to contribute) doesn't need to think "generic SQL" or any "one for all" approach.

 

This also mean that people doesn't need to mess around with CodeIgniter 3.0 since it is not stable yet.
CodeIgniter 2.1.3 already has some good driver despite of their broken PDO.

I'll work again with it later.

Cheers ...


goFrendiAsgard

goFrendiAsgard
  • profile picture
  • Member

Posted 16 June 2013 - 16:00 PM

This also make [member="davidoster"]'s suggestion doable.
Work with groceryCRUD 1.3.3 and CI 2.1.3

 

Just to say that both GC 1.4 and CI 3.0 are not stable enough. You might end up having a perfectly good code that you wrote that:

1. doesn't work NOW with the current GC 1.4 & Ci 3.0 BUT it could work later

2. does work NOW with the current GC 1.4 & Ci 3.0 BUT it won't work later

 

Just think about these before selecting GC 1.4 and CI 3.0.

Personal opinion: I would work first with the stable versions to see if it is doable.


goFrendiAsgard

goFrendiAsgard
  • profile picture
  • Member

Posted 17 June 2013 - 01:18 AM

Hi again, [member="web-johnny"], can you please merge this pull request: https://github.com/scoumbourdis/grocery-crud/pull/194

 

That will make groceryCRUD work with CodeIgniter 3.0 dev.

 

Not much changes, I only rename library file name and the respective class.

 

cheers :)


goFrendiAsgard

goFrendiAsgard
  • profile picture
  • Member

Posted 17 June 2013 - 23:13 PM

New models https://github.com/goFrendiAsgard/grocery-crud-databases/tree/master/application/models

See the update information at my first post.


goFrendiAsgard

goFrendiAsgard
  • profile picture
  • Member

Posted 18 June 2013 - 17:40 PM

[member="web-johnny"]: My first pull request https://github.com/scoumbourdis/grocery-crud-databases/pull/1


goFrendiAsgard

goFrendiAsgard
  • profile picture
  • Member

Posted 20 June 2013 - 07:46 AM

https://github.com/s...-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


goFrendiAsgard

goFrendiAsgard
  • profile picture
  • Member

Posted 22 June 2013 - 16:31 PM

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


Andrés Bott

Andrés Bott
  • profile picture
  • Member

Posted 03 November 2013 - 04:21 AM

Hi, i wanted to ask how is the status to use GC in CI.

Today i've tried GC1.4.1 with the grocery-crud-databases, on CI 2.1.4 and CI 3.0-dev and wasn't able to make it work with the sample pdo:sqlite database. any help here??

thanks.