These forums are read-only and for archival purposes only!
Please join our new forums at discourse.kohanaframework.org
Database builder - drivers
  • I am using kohana/orm for Oracle database application. To make it work I had to write a driver for Oracle; in my case I wrote oci8 driver and oci8/result driver - Kohana makes it easy ;-).

    But to make ORM work I had to override query/builder/select.php. ORM uses builder for query generation, and oracle is not friendly with mysql flavor of SQL (LIMIT and OFFSET in particular), so compile method has to be overriden.

    Are there any plans to have builder driven by drivers so the code could be cleaner/better/more modular.

  • You could try using the LEAP ORM for Kohana. It is available on github at https://github.com/spadefoot/kohana-orm-leap. There is already a driver for Oracle, as well as for the other major databases. It will allow you to use either oci8 or the PDO driver.

  • One of my biggest problem with the kohana database module is that during the query compilation process it only takes the target DBMS into account only from the value and identifier escaping point of view. It doesn't care about the different SQL dialects of the databases. Furthermore it is primarily designed for MySQL, which does have offset-limit clauses, but these clauses are not the part of the SQL standard.

    Once a guy has wrote an MS-SQL driver for kohana, and he has also been fucked up by the offset-limit clause since its MS-SQL equivalent is SELECT TOP limit, offset. Finally he ended up with manipulating the already compiled SQL string in the driver, with regex, before execution (afair). Painful hack :P It will be even more painful for an oracle driver, since you will have to manipulate the WHERE clause of the SQL string and add rownum-related conditions. I'm afraid you won't be able to do it with regex since the context-sensitive parts of the SQL language come into play here, therefore probably you won't be able to do it with regex.

    The database module imo needs a major refactoring. It is not the only problem with it. I have wanted to write a proposal about it for more than one year right now, but it seems to be a pretty boring job.

  • Crystal, I would agree with you regarding the difficulty in adapting Kohana's database module to used with different database dialects. Regular expression just don't cut it as you said. This is why you may want to take a look at the LEAP ORM. It completely decouples this problem. It is not a hack to Kohana's database module.....it is a completely rewritten module that allows for multiple database dialects as plugins.

    In the case of MS-SQL, LEAP offers a plugin explicitly for MS-SQL. It is a clean implementation and will handle many of the problems you are currently dealing with when using Kohana's ORM. LEAP will escape identifiers the Microsoft way and not the MySQL way. It will handle composite keys, which tend to be more common in database using SQL-Server.

    I believe the LEAP ORM is the answer to your problem, for it was an answer to your belieft that "[t]he database module ... needs a major refactoring."

  • Furthermore it is primarily designed for MySQL, which does have offset-limit clauses, but these clauses are not the part of the SQL standard.

    We officially support SQL-92 (which correct, doesn't have limit or offset words). However the concepts do exist in SQL:2008: http://en.wikipedia.org/wiki/Select_(SQL)#Limiting_result_rows

    I don't think including these methods in the query builder is going too far. We strive to support the sql standard concepts, not a specific implementation (mysql). MS-SQL is known to have bad limit implementation, and the point of the driver is to translate the concept into the specific server implementation.

    The reasons you gave are great examples of why a "transparent" driver system is generally a bad idea (unless it implements the standard to a "T").

  • @Crystal

    Not so much boring as "what's the better way to do this?". :)

    The whole thing is quite a niche and exotic cause from my point of view. When are you going to run an application on 9 databases? all different I might add. What's even the point if you're not even gonna run optimized code for that database? (ie. because you're running "compatible" code all the time) You could only support X and Y both of which support a certain way of working that would save you an extra statement, but because you're compatible with some god knows what primitive thing out there you have to do with two statements instead.

    IMO the database module should just provide a nice interface to all the basic functionality, running an expression and so on. Maybe support some shortcuts for basic things such inserting, since it can make the code clearer by passing an associative array, instead of two lists, and so on. The whole idea being that the module would guarantee under the hood you're using the latest/best/most-secure strategy for communicating with the database.

    The entire "support all databases" mechanism should be a separate system, since it is essentially a system rather then an actual feature, and seems to mess with the basic database functionality, since it's all build on the premise that you're gonna use the system, not use the DB module to do database work. And when you're not using it you either have a very basic interface, with all sorts of parameters, random parameter ordering, NULL's you have to pass, etc. It's like you're accessing some internal method—and I guess in some sense you are.

  • It is not a hack to Kohana's database module.....it is a completely rewritten module that allows for multiple database dialects as plugins.

    ty but i wrote my own database wrapper lib and my ORM on the top of that already.

    The reasons you gave are great examples of why a "transparent" driver system is generally a bad idea

    The problem can be solved by moving the query compilation process to the driver layer: you can have an abstract query compiler that supports the SQL standard in general, and the drivers can have a driver-specific query compiler subclass that handles the DBMS-specific parts.

  • It's still very hard to do "right" with a driver based system. Like @Akki says, how many times have you switched database engines on a project? My experience has been zero. There isn't that much benefit to it, and if there was (say if a distributable product wanted to do it) there would be people out there that might take the task on.

    The benefit to work ratio is very low on a feature like this.

  • When are you going to run an application on 9 databases?

    Never. The aim of database wrapper solutions is to write less vendor-locked code so it causes less headache to migrate from one DBMS to an other one.

    IMO the database module should just provide a nice interface to all the basic functionality, running an expression and so on

    IMO the API of the database wrapper should support every operations that exist in at least one DBMS. If the user wants to execute a query with some features that the target DBMS doesn't support then an exception should be thrown by the driver during the query compilation. It is the same idea how the Java Collection Framework handles the differences between the similar data structure implementations. Taking only the greatest common subset of the databases' features is not the way to go.

    The ideal driver development process for such wrapper would be the following: if you write a new driver and you find a feature of the given DBMS that the API doesn't support yet, then you should definitely add that operation to the API (or ask the API layer maintainer to do that). Then you have to examine all existing drivers if this unique feature of the newly supported DBMS exists in them, and if not, then if it can be worked around/simulated somehow. If not, then - as the worst case - the driver should throw an exception.

    If the wrapper supports only some very common operations then it's close to be useless, since you can use it only in a few cases, and in all other cases you need to write plain SQL string. I often hear that simple projects don't need complicated queries so a basic wrapper fits their needs - personally I have neved seen such a small project. This case mainly happens when the developer doesn't have a solid SQL knowledge and he runs dozens of simple queries instead of one complex one - and of course it results in bad quality software. Even an 5k loc application will probably run 1-2 complicated queries.

  • how many times have you switched database engines on a project?

    Well.. once :)

    But an other good use case of a DBMS wrapper is when you really need to build your query. I mean if the query parts are created during some more or less complex business logic. In such case it's better to have a query builder than doing string concatenation without it.

  • Nobody said eliminate object based query builders ;) The point would be to eliminate adapter pattern database drivers.

    Reducing code complexity is a good goal. There isn't much benefit to having an abstracted adapter pattern database module.

  • @Crystal

    What if there are two or more approaches to the problem, both yelding better results then the "vendor-independent" version. How would you specify that IF method A fails, fallback to method B? The obvious solution would be try / catch but then how would that be different or better then just writing the damn code for each one. :)

    I often hear that simple projects don't need complicated queries so a basic wrapper fits their needs - personally I have never seen such a small project.

    Second this.

    Never. The aim of database wrapper solutions is to write less vendor-locked code so it causes less headache to migrate from one DBMS to an other one.

    The option of manually migrating doesn't really sound that bad to be honest. Supposedly you're migrating because you know what you're doing, or you're migrating because of some feature. Both of which are pretty incompatible with the idea of having vendor-independent code. Since you're migrating, obviously there's a certain vendor specific feature (not necessarily language feature) that's driving you.

    Sound more like the system is pushing you into a "if you switch, you gain nothing" rather then "help you switch" as advertised. ;)

    @zombor

    (say if a distributable product wanted to do it)

    Well, the rainbow doesn't really land on the pot of gold even for those. ;)

    Anyone making any kind of "product" is going to have to be conscious of the terrors of userland. You're going to have expectations like high maintenance (since there's more "user" then you'll have hair on your head), performance (since you're likely to have competition), and constantly improving to stay sharp and remain in the market.

    You could use this system, at first, it's a valid approach to the problem. But how long before the requirements weigh down and cause you to re-write the entire thing into a less magical version that you can fine tune and customize to you're user's many needs.

    The whole "choosing to support ALL but (potentially) be mediocre at everything" VS. "supporting one/two really well" is a whole can of worms in and of itself.

  • The whole "choosing to support ALL but (potentially) be mediocre at everything" VS. "supporting one/two really well" is a whole can of worms in and of itself.

    I agree, and you'll see that in Kohana itself and other products I work on.

  • @Akki

    When are you going to run an application on 9 databases?

    Kohana should be flexible enough to allow users needing to interact with multiple databases to do so. I have worked on a number of projects where I have had to pull data from multiple databases using different dialects. For instance, a Web site may be using a MySQL backend but it may need to insert orders in an ERP system that is running MS-SQL. Since the ERP's database cannot be touch because of warranty reasons, many companies have to use multiple databases in the same application. I believe it really depends on what the programmer is working on....because I know many programmers that only need one database and I know other programmers that need to communicate with many databases. It is important that the Kohana framework consider people that need to work on projects that do not require such communicates, especially if the Kohana community wants to attract big name companies to use the framework. Therefore, there are reasons to run an application on more than one database....although it may only be 2 or 3 at a time and not 9.

  • That still doesn't mean we have to provide a transparent adapter pattern based system that simply limits you as a developer.

  • @Spadefoot I'd say chances are if you're going to NEED to use a combination of 2 or more databases, in a new project, at least one of them is going to be a NoSQL solution. Does the current SQL abstraction help then? :)The only real scenario I can think of where you would be using multiple SQL database engines at once is when supporting or communicating with a legacy infrastructure. But often times said infrastructure is either old or pushed to the limit, so can you afford to write fairly dumb code against it?

  • I agree with Crystal 100%. I do not see reduction in code complexity in current approach. But completely opposite if you want to implement some other DB engine. I can say from my example, and as you can see Crystal also had to write additional code for basic framework functionality. Standards are standards and reality is something different; take browsers and standards implementation there. You cannot ignore browser quirks and just write standard code...

    And btw I also have project where there are two DB engines used (Oracle and MySQL), and I would like to be able to use same code consistently without increasing code complexity.

Howdy, Stranger!

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

In this Discussion