Informix Support

[fukawi2]

We have a strong need to have F3 support Informix databases. Would this support be accepted as a patch, or is there an explicit desire to not add additional databases to the supported list?

[sn0opy]

Well, you're the first one requesting it and you will probably be the only one maintaining it in the future. So it's kinda up to you.

[fukawi2]

Unfortunately our ERP system uses it... It's certainly not my DB of choice! (But F3 is definitely my framework of choice :))

Assuming the code is styled properly, tested etc then it could be accepted then?

[sn0opy]

Well, yeah, maybe. Also depends on the other contributors and especially @bcosca to decide it. But if you need it anyway, why shouldn't you also open source it?

[Rayne]

Depending on the desired integration, you could also publish and maintain it as plugin for F3. See the list of known and maintained plugins for reference: https://fatfreeframework.com/development#UserPlugins

[fukawi2]

Would database support be possible in a plugin? I don't see any other plug-ins providing additional database support. The file lib/db/sql.php doesn't mention "plugin" anywhere, and it seems like the schema() function in that file would be one of the main things that need support added to it.

[sn0opy]

There are no real plugins. Plugins in F3 are just code additions to F3 that aren't in the official repository. A pretty similar example is this plugin.

https://github.com/ikkez/f3-schema-builder

[fukawi2]

Sorry if I'm sounding dumb here (I'm obviously not much of a developer) but that's plugin is adding totally new functionality to F3... To add a new database is additional functionality to existing features, so it would have to touch parts of the core code (ie, lib/db/sql.php)?

[ikkez]

Well yeah it depends. But since there is an Informix PDO adapter available, it could be possible to add it to lib/db/sql.php yes. I'm not quite sure how to install that pdo_informix extention,.. the resources seems to be a bit old. Is it still maintained?

[fukawi2]

@ikkez I have pdo_informix working (it wasn't too painful, but it definitely wasn't a simple (yum|apt-get) install command). I do have to write all the SQL manually at the moment, hence I opened this issue :)

I'm not sure on it's maintenance status, but it is definitely working. I imagine it lives on in the slow-turning world of IBM somewhere.

[ikkez]

It looks like the pdo package is still maintained. Last update was 2 month ago: https://pecl.php.net/package/PDO_INFORMIX It's just a matter of how good this package is and if most important parts of the PDO interface were implemented. Well I am afraid that I cannot really help you on coding and testing this. But I can guide you to do this. If you got it working on your side, we can review this and apply it to the core. To start, fetch the edge dev version of the framework from https://github.com/bcosca/fatfree-core At first, have a look at the db/sql.php file. To use your informix pdo adapter, you need to specify it with the correct connection string. From the docs (http://php.net/manual/de/ref.pdo-informix.connection.php) it should be something like this: $db = new \DB\SQL("informix:host=host.domain.com; service=9800; database=common_db", "testuser", "tespass");. If it does not fail, it probably worked ;) jump in with xdebug and see what value $this->engine is like after this line https://github.com/bcosca/fatfree-core/blob/master/db/sql.php#L481 Probably it's informix. Use that name to adjust all the switch and if conditions in the class.. most important is the schema method. Its command maybe looks a bit confusing a first, but let's see mysql for example: 'mysql'=>[ 'SHOW columns FROM'.$this->dbname.'.'.$table.'', 'Field','Type','Default','Null','YES','Key','PRI'], First array key is the sql command to fetch the table schema. 2nd (Field) is the name of the column that contains the field name. 3rd (Type) is the name of the column that contains the field type (i.e. integer, text, varchar). 4th (Default) the column name that contains a possible default value. 5th key (Null) is the column name that contains information about the columns nullable flag. The 6th key (YES) belongs to the previous nullable field. This simply defines which value in the nullable column (5th key) makes the field NULL (i.e. if column Null contains YES, the field is NULL). Same story for the 6th and 7th key. Here the 6th key Key tells the schema which column is used to lookup the primary key flag, and the 7th key (PRI) is the value in the 6th column that defines that column as primary key. That's it. If you got this working, you are on the right way. You can copy and modify the unit tests for the db engine from https://github.com/bcosca/fatfree/blob/dev/app/sql.php and do tests on common tasks. If is seems to work all good we can continue on the sql mapper ;)

[fukawi2]

Thanks for the explanation. That combined with this code that someone wrote for Yii framework has given me a good starting point, however one more question... What if a table doesn't have a primary key? It appears the database administrator who designed the DB I'm working with was just some random guy they found on the street :-/

[ikkez]

hey @fukawi2 just a quick question: have you worked on this or has informix integration become meaningless for you in the meantime?

[fukawi2]

I did start working on it, but it quickly became well over my head. I'm still using F3, but making my queries manually using $f3->get('DB')->exec("SELECT foo FROM bar WHERE X =?", $params)

I would definitely still love to see it in and would use it (I have 2 projects already using our Informix DB) but I don't have the skills to implement it sorry :(

[ikkez]

I don't see this comming anymore due to lack of interest even from others.. please reopen if there's still demand for it.