Database's Classes

[overras]

Hello guys. I'm trying to modify somethings at my platform. Momentantly, it works just on mySQl, but I want to be sure will work on another type of database.

Now I have queryes like that:

Code: Select all

$db->query("SELECT * FROM `[downlaod]` SET .. WHERE etc");

So, If I edit query() to work like that:

Code: Select all

$db->query('select what?' , 'table' , 'set' , 'where' , 'final_argument');

then I could use any type of database right? Because the a class is using their functions or member call them how you want. I saw this a long time ago in one of halls tutorials, but I'm not sure if this was the thing what he wanted to assure.

[a_bertrand]

This is somewhat like a query builder. However instead of creating something like that, why not use an existing framework like http://docs.doctrine-project.org/projec ... en/latest/

It would do that work for you, and do even more than what you planned.

Also, are you REALLY sure you need database abstraction in the sense to run your code on multiple different databases? In over 20 years of work I NEVER had this requirement. So for me this seems an odd goal.

[overras]

Actually, I like to work on my own code , thank you for that link, but I want to be sure and to know exactly what to do in future If I want to modify something or change something .. I think you understand. This is the reason of my fear about frameworks , I lose the control on my own application and I hate that.

[a_bertrand]

I understand that, but here you are trying to do something which is... way not easy to make it right, which costs a lot of time and with very little benefits.

So either you skip it all together and use some simplified DB access, while keeping the SQL as is, or use a true and known framework would be the other road.

Sorry really I don't see the point here to spend time on such part. It's like saying, I will invent my own language, because I don't like that function of PHP. Sure PHP is not perfect (and actually I don't like it much anymore), but would you spend time re-create your own language such that you have control of it? I doubt.

[Darius]

MySQL != MS SQL != Oracle SQL in syntax and structure.
There is no uniform way to build your query.

[overras]

MySQL != MS SQL != Oracle SQL in syntax and structure.
There is no uniform way to build your query.

Exactly. So, a query like that should work right?

Code: Select all

$db->query("SELECT $select_what FROM $table SET $set_vars $final arugement"); // final argument example: ORDER BY id

In mysql class:

Code: Select all

function query() {
      mysql_query($select_what, $table, $set_vars, $final_argument);
}

and in a mssql class same thing just in another order understand? So, will work , right?

[Jackolantern]

Doctrine was always a bit heavy for me. I can't say I liked it. For me, using the Active Record Pattern was a bit simpler. It also puts the actual query building into an abstraction layer. This allows you to expand the back-end actually interacting with the database so that you can support pretty much any database platform you want to. Most PHP MVC frameworks implement AR in some way and it allows them to support multiple databases. You can see an intro to AR in PHP here.

Of course, implementing the AR pattern is not difficult, but making it support multiple RDBMS under the covers is not trivial, and requires a good understanding of all the SQL languages used by the major RDB vendors. Several common ones to support would be: MySQL, PostgreSQL, SQL Server and Oracle. There are a lot of differences in querying between these RDBMS, however (maybe less between PostgreSQL and MySQL).

[a_bertrand]

Honestly I never tested any of those framework / libs. In C# I work with LINQ which is just perfect for my needs. In PHP? I don't care I work with standard SQL when I really need to work with PHP.

For simple queries, making a query builder based on whatever rules you want is not all that complex. Even if conditions could start to be complex like what if you want to have all the entries of a list? Or entries between 2 dates? The worse is when you go to sub-queries or even complex joins where there DBs have different syntaxes.

The time spent on those kind of things grow dramatically at that point, and I hardly see why you would need to support multiple DB systems. Even if you plan to sell your script, you usually work with 1 DB.

[Jackolantern]

Yeah, I also can't really suggest investing a lot of time to support multiple database vendors. If you make it open source, you could always drop in Zend Framework's AR system, which supports various RDBMS. Zend is made in a way that you can use only the parts you want to.

And if you aren't making it open source, there isn't much point in supporting multiple databases because, as Bertrand said, you will only be using one.

[a_bertrand]

Even for open source projects, you can simply choose one open source DB, like MySQL and people will be more than happy.