CodeIgniter Forums

Full Version: Low level database drivers are hacked with irregular functionality levels is a pretty serious bug.
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

[eluser]Unknown[/eluser]
The SQLSRV driver for instance calls field_metadata then sets primary_key=0; same with postgresql driver, same with mysqli - each driver has a different functionality level. I think certain metadata should be cached at a higher level (forge?) if you want metadata on a table or column in a table - you can call and cache the table metadata once.

It is not safe to assume the database drivers (mysql,mysqli,postgres,sqlsrv,odbc) return the same functionality for the same routines. If you use anything other than MySql - expect your code to be broken.

I think it may be wise to consider handling pagination and other functions at a higher level. Type conversions as well. What do you do with a 64bit unsigned int? even 64bit php uses a signed 64bit int - so that won't work.

If you assume the limit,offset functions work in all databases - you make a grave assumption - and again split the drivers up - I may suggest taking a look at say ADODB PHP/python or Doctrine to give insight on normalization of such routines.

auto_increment doesn't exist on all databases either - you can't assume it does.

Constraints and Keys are separate with sqlsrv 2.0 driver - you can have a unique constraint on a column(s) and have a unique key on a column - but if you don't check for both and have structure to hold both - you may skip one functionality layer.

So who here is handling the database drivers and normalization of functionality? I would like to suggest an emulation layer to accomplish common goals with any database (excel via odbc, sqllite on android, etc) - anything that can not be equalized should be marked as a function that is specific to say mysql Smile

if limit only works on postgres and mysql - we can emulate it on others, provide an alternate compatible pagination routine(what adodb and its framework does) - if you cannot read all table attributes - perhaps allow hooks to hardcode - or place the data into a table or array if it cannot be derived that would be called by your model to present such data.

Anyways - for the most part i've seen code that is 90% mysql - which is great when you use mysql, but i assume the drivers for other sql servers are there for a reason - I'd like to help with sqlsrv and postgres since they are so similar. This would probably require structural changes to the higher level db class since some functionality objects only make sense.

If you cache the table metadata once - or hardcode it into an include - the DB layer needs to consider that when the forge class alters the table - to reload or warn of possible mismatch.

Grocery CRUD man - has done just this- cache the table metadata and not call a metadata routine every time on a column- this improves performance - but this really doesn't belong in the code - the caching object belongs in the driver. If you called alter table - you would create cache incoherency.

1. Disable functionality that is hardcoded like sql_srv_metadata returning primary_key=0; that just really messes with folks who are using the functions and then try to move to another platform. Or declare it to a silly number like 696969 or NOT_IMPLEMENTED. this would affect every driver result except mysql. all of them.

2. build table object cache in higher level DB class (non database specific).

3. Build translation table of types in each low level driver. High level DB driver uses PHP types - low level driver handles conversion back and forth of datatypes to each driver. Try to normalize so if you store a timestamp because you want to move it quickly to a session expire you don't map it to a similar datetime that may lose resolution.

4. figure out how the other guys do pagination - relying on limit may provide problems for cross platform - with modern browsers and caching there perhaps is a way to cache the objects like (enumerate,primary key(s)) or create a temp table. Let ajax deal with it.

5. unit test db driver through each database specific driver so as new versions come out we know that we expect THIS and we got THAT.

6. Document the problems in the CI documentation so people don't have to hunt down why this doesn't really work the same way - right now. Matrix of features that work,work partially would really help. attach to each function in the DB class for reference.