Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
Informix Database
  • Hi everyone, I'm doing database migration (mysql for Informix). Is it possible use Auth lib and Orm with fuelphp and informix ? 
    Thanks
  • HarroHarro
    Accepted Answer
    I feel very sorry for you... ;)

    Jokes aside, Fuel comes with an ANSI SQL driver, that will not work with Informix because of the different SQL dialect that Informix is using.

    It is possible to write your own driver (someone recently made one for MS-SQL, and that is a lot more complex since it doesn't have LIMIT/OFFSET selects). If you want to go that route, let me know, I can give you some pointers.

    But migrations will still be an issue, since all SQL is hardcoded in DBUtil. Not sure if that is a problem, in larger environments where you typically find Informix or Oracle, DBA's will not let you alter the schema anyway...
  • Hi Harro, thank you for answering,

    You were right when you said "sorry" =)

    The driver that you have mentioned, is for ORM or Auth.
    It would be helpful if you could give some tips.

    thank you so much
  • HarroHarro
    Accepted Answer
    For both.

    Fuel uses drivers to abstract the DB interaction away from the DB class. Currently, Fuel has drivers for MySQL, MySQLi and PDO (ANSI SQL only).

    Everything that interacts with the database, including the ORM, uses DB methods, so once there is a driver, everything in Fuel can talk to the database this driver gives access to.
  • If I create a new driver, it will be enough or would be necessary to change the query builder too?
    Would Orm work with just a new driver ?

    Thanks
  • marcoseteixeiramarcoseteixeira
    Accepted Answer
    Hi Harro and Anderson, I'm having the same problem, the company I work began using Informix. What are the tips you could give me?
  • HarroHarro
    Accepted Answer
    The driver will convert the generic method calls into SQL specific for the platform the driver is for.

    For example, the methods limit(25) and offset(10) will set the variables limit and offset both to 10.

    The MySQL driver will convert that to "LIMIT 25 OFFSET 10" in the generated query, the Informix driver however will need to generate "SKIP 10 FIRST 25". This will be transparent to the upper layers, both the DB class and the ORM class on top.

    Note that (as said) you also might have to extend the DBUtil class if you require schema alterations, since that has quite a bit of hardcoded SQL generation going on.

    @andersoncdz,

    If you're happy with the driver and you are willing/allowed to open source it, we have no problem accepting it into the framework. In that case we do have to think about how to maintain it, since none of the team has informix handy to test changes.
  • Hello Harro, 

    Thank you for your answer. 
    I had a look at how the fuelphp driver works, both Orm and DBClass. 
    As far as I could see, the whole process gets to a point, which is the Query Builder. 
    In this case, and please correct me if I'm wrong, I would have to adapt Query Builder to Informix, right? 

    Thank you
  • HarroHarro
    Accepted Answer
    You start with a fuel/core/databases/informix folder. In there you create:

    connection.php, containing

    namespace Fuel\Core;
    class Database_Informix_Connection extends \Database_Pdo_Connection {}

    so you can re-use the PDO driver.

    query.php, containing

    namespace Fuel\Core;
    class Database_Informix_Query extends \Database_Query {}

    so you can re-use the generic query driver (don't think that contains anything special)

    and create a buider folder, to contain your informix custom builder scripts. For example, you will have to overload the SELECT driver, because LIMIT/OFFSET works different in Informix. To do so, create select.php in the builder folder, and start with:

    namespace Fuel\Core;
    class Database_Informix_Builder_Select extends \Database_Pdo_Builder_Select {

        /**
         * This Informix specific version translates LIMIT and OFFSET into Informix SQL dialect
         *
         * @param   object  Database instance
         * @return  string
         */
        public function compile(\Database_Connection $db)
        {
            // replace this with your code
            return parent::compile($db);
        }
    }

    You can check what is in the parent compile() method to see how the SQL is being generation. You then have to decide if you are going to write your own generator (which is wise when the dialect is completely different), or use regex to replace stuff in the SQL the PDO driver returns. If you do, make sure your regex' are fool proof!

    One final step: every class you create, you have to add to fuel\core\bootstrap.php, so the autoloader knows where to load them from.
  • HarroHarro
    Accepted Answer
    p.s. If you fork the fuel/core repo on github, and make your changes in there, you can rebase when you're done and send us a pull request, if you decide to make your driver public.

    I see that you can download a developers edition of Informix for free. If you decide to make your driver public, I am willing to install that here, and test a few of our applications on Informix. That would give you a good indication of the robustness of your driver.
  • I created the Informix folder and connection file, it  works fine.

    I also created the query.php and added it to bootstrap, but the main query builder remains being loaded. You know what it could be?
  • HarroHarro
    Accepted Answer
    Debug Database_Connection::classname().

    It returns the name of the builder class based on the name of the driver class. If the name of the driver is "Database_Informix_Connection", it should return "Database_Informix_Builder_Select" of you do a DB::select() call or an ORM select query.

    Note that I forgot to mention that because of this, all "builder" classes must be present. So for the ones you don't have to change, like maybe "join", "delete", "update" or "insert", just extend them from their PDO equivalent (like the select example above) and leave them otherwise empty.
  • HarroHarro
    Accepted Answer
    Your db.php config is ok?

        /**
         * Base Informix config
         */
        'default' => array(
            'type'        => 'informix',
            'connection'  => array(
                'persistent' => false,
                'compress'   => false,
            ),
            'identifier'   => '"',
            'table_prefix' => '',
            'charset'      => 'utf8',
            'collation'    => false,
            'enable_cache' => true,
            'profiling'    => false,
            'readonly'     => false,
        ),

    You may need to check the indentifier, I think the default in Informix is a double-quote, but you may have to check with your DBA. The identifier is used to escape column names and table names, to avoid collisions with reserved words.
  • Harro Hi,
    I checked my db config, I like how you showed me.
    In my configuration file I overload the quoted_identifier method to remove quotes of alias, because seems that alias can not be quoted in informix . I do not know if it was the best option (: but it worked.

    Now I am trying to overload query builder and query the file, but without success so far. I've created the folder builder and query.php file inside the informix folder.

    The Informix driver I'm using returns idexes of the result in uppercase, it caused an undefined index error in ORM hydrate (:.

    Thank you very much




  • HarroHarro
    Accepted Answer
    You need to create all the files in /fuel/core/database/informix/builder, so delete.php, insert.php, join.php, select.php and where.php.

    Just start by defining the empty classes as extensions of their global equivalent, so

    namespace Fuel\Core;

    class Database_Informix_Builder_Delete extends \Database_Query_Builder_Delete
    {
    }

    and so on, and define them in fuel/core/bootstrap.php. Each of these classes represents a type of SQL query (join is something special), and each has a compile() method that will generate the SQL from the data stored inside itself.

    So if you need to change the way a SELECT is generated (for example because LIMIT/OFFSET is handled differently), you need to overload the compile() method in the Database_Informix_Builder_Select class. Either by coding a new generator (if the difference is huge), or by calling the parent and then use a regex to alter the generated SQL.
  • Hi Harro,
    I extended the classes of the query builder, but DB class always return Database_Query_Builder_Select:

    db.php    
    public static function select($columns = NULL)
        {
            return new \Database_Query_Builder_Select(func_get_args());
        }

    I think that's why I am unable to give overload, please correct me if I'm wrong.

  • HarroHarro
    Accepted Answer
    You might be right.

    I wonder what else went wrong where, as I got your examples from a MSSQL driver set I have worked on myself...

    I'll have to dive into it over the weekend, I feel this is going to be a bit of work...
  • HarroHarro
    Accepted Answer
    Just pushed an update to the DB code to 1.8/develop: https://github.com/fuel/core/commit/dbe536aa369f5a0287030518af36702873a71f5a

    I've moved the creation of the Query Builder class instances from the DB class to the connection driver. This means individual drivers like yours can now return \Database_Informix_Builder_Select instead of \Database_Query_Builder_Select.

    You only need to create your own select() method in \Database_Informix_Connection to do so, which will overload the default method. For query builder classes you need need any changes, you don't need to overload anything, so there is no longer a requirement to create all builder classes.
  • HarroHarro
    Accepted Answer
    Just pushed further database driver updates to 1.8/develop today.

    All schema operations (from DBUtil) are now abstracted as well, and can be overloaded per driver if custom SQL is required (in your case, to Database_Informix_Schema, which needs to extend Database_Schema).
  • I have no words to thank you for the help, I believe this can help others who are going through the same problem.
    I hope to finish the migration this week, when get finished, I will try to make a pull request.

    cheers
  • HarroHarro
    Accepted Answer
    Great, Thanks.

    In the meantime, more updates, and the start of more pdo backed drivers.
  • HarroHarro
    Accepted Answer
    Question: does this make any sense to you guys? http://code.activestate.com/recipes/576621/

    The Informix PDO connection driver needs a custom list_tables() and list_columns() I think...
  • I did overload the select method, and returned the Informix Database Builder_Select and it worked like a charm.
    I also think PDO connection driver needs custom list_tables () and list_columns (). One of the problems I had here was array indices in uppercase , this caused a problem in hydrate  method . This problem occurred using PDO driver for windows(i'm using a vm ): with widows ).
     Eg:
    string(13) "SAN0220280.01"
        ["DESCRIPTION"]=> // uppercase index
        string(47) "FIL PE BR LAM RESP IMP 220 MM X 28 G/M2 2 CORES"
    On Hydrate show undefined index "description" in lowercase

    So to solve my problem for now I've used this method:
    protected function array_change_key_case recursive ($ array) {return array_map (function ($ item) {if (is_array ($ item)) $ item = $ this-> array_change_key_case recursive ($ item, CASE_LOWER); return $ item;}, array_change_key_case ($ arr)); }.
    I believe it is not the best way, for now I'm using this workarround to find a better solution (: because I believe this may have an impact on driver performance.

    Great work with DB and Connection, save my life, and great work with fuelphp. I've been using along 1 year.

    thanks

    ps: I apologize for my poor English
  • HarroHarro
    Accepted Answer
    weird, is that a driver bug? Or are all column names in Informix uppercase?
  • I did a test using pdo outside the framework and the result was the same, column names in uppercase, i think that informix pdo driver is doing it.I don't know if the same happens on linux, because i'm using windows.
  • HarroHarro
    Accepted Answer
    You can tell PDO to return in lowercase using:

    PDO::ATTR_CASE => PDO::CASE_LOWER

    You can pass it to the PDO constructor in _connect() like is done here with the compress attribute: https://github.com/fuel/core/blob/1.8/develop/classes/database/mysql/connection.php#L51

    so

    $this->_config['attrs'][\PDO::ATTR_CASE] = \PDO::CASE_LOWER;

    This is only for results though. What happens if you use lowercase column names in a SELECT?

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion