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...
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.
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.
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.
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.
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.
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.
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 (:.
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.
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.
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.
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.
I did a test using pdo outside the framework and the result was the same, column names in uppercase, i think that informix pdo driveris doingit.I don't know if the same happens on linux, because i'm using windows.
Donate
