Having a built-in own SQL language?

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Having a built-in own SQL language?

Post by kaisellgren »

Hi,

I am pretty experienced in PHP nowadays. I am even more experienced with different databases. I am working on a complete suite that works with all major DBMS such as MySQL, PostgreSQL, Oracle, MaxDB, ...

The problem is not the portability, the problem I am facing is How to do it the best way. Currently I have a pretty wild idea, which I don't see anyone using yet: Own SQL language built-in the PHP script. Well now probably most people think "it's not used by anyone, because it's complicated". Actually, it's not complicated at all for its users. The people who code modules for my script only need to crawl through the documentation for about 10 minutes to find out what the language can do and how it works. Since module writters already know PHP and SQL, it would not be such a big deal.

The language itself is somewhat "standard SQL". An example query:

Code: Select all

SELECT "name","email" FROM "members" WHERE "id"="1" LIMIT 5 OFFSET 10;
That query does not make much sense, but that's not the point. I wanted to show that basic SQL is somewhat same. The MySQL's LIMIT -style is not available, and many other minor changes are applied to the language. Basically I am constructing a set of features that work in all major DBMS, and in some cases I would provide something that is not usable in some SQL languages by default. For example SQLite can not do "ALTER TABLE DROP blah". But with my system it would be possible. In addition to that, I could add my own functionality too that could fit my project fantastically ;)

Let's look at the pros and cons.

Pros
- Portability: works in all major DBMS. Module writters do not need to worry about anything. They can safely test their SQL on any DBMS they like.
- Complicated SQL Select queries are possible. Unlike with OOP chaining or other DB systems where you use premade methods to construct queries, in typical SQL clauses you can easily write whatever you want and it will work - regardless it is complicated SELECT query or not.

Cons
- Some people are stereotypes and think at first "Oh my god, I have to learn ANOTHER SQL language. Sigh!". Therefore, I might lose some potential module writters.

Now it is your time to throw your thoughts. Pros and cons, I want to hear both from you! Not just applauses "Great idea!" or negative feedback "You are such a stupid man".

Constructive feedback, thank you. :)
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Having a built-in own SQL language?

Post by onion2k »

It's an admirable idea, but I think you're probably underestimating the amount of work involved. SQL is a very big language even if you ignore all the DBMS specific extensions to it. Writing something that unified all the variations would be an epic task.
User avatar
panic!
Forum Regular
Posts: 516
Joined: Mon Jul 31, 2006 7:59 am
Location: Brighton, UK

Re: Having a built-in own SQL language?

Post by panic! »

I think having adapters like Rails and other frameworks use kind of serves this purpose but at a scripting language level rather than query language level i.e via some sort of abstraction technique like activerecord...I swear I've mentioned activerecord 999999 times today, I apologise.
User avatar
panic!
Forum Regular
Posts: 516
Joined: Mon Jul 31, 2006 7:59 am
Location: Brighton, UK

Re: Having a built-in own SQL language?

Post by panic! »

..that said, I think it's a great idea!
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Having a built-in own SQL language?

Post by alex.barylski »

I'm not sure I see the benefit say compared to OO SQL interface? They (as I understand) abstract the SQL.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Having a built-in own SQL language?

Post by Christopher »

Looks a little like LINQ...
(#10850)
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Having a built-in own SQL language?

Post by josh »

Take a look at java hibernate's HQL or PHP Doctrines DQL, these are all examples of OQL or object query langauges for abstracting the actual string manipulation of generating your SQL query.

Panic!, If I'm using active record and I just need an aggregate function for a report, how do I avoid loading 5,000 records with active record? You are still going to always need SQL
DrTom
Forum Commoner
Posts: 60
Joined: Wed Aug 02, 2006 8:40 am
Location: Las Vegas

Re: Having a built-in own SQL language?

Post by DrTom »

jshpro2 wrote: Panic!, If I'm using active record and I just need an aggregate function for a report, how do I avoid loading 5,000 records with active record? You are still going to always need SQL
Depends on the active records implementation, but lmost all active record implementations include
aggregate methods for performing various calculations on columns. Including the famous Ruby implementation.

However, the point still stands, you do always need SQL.
User avatar
panic!
Forum Regular
Posts: 516
Joined: Mon Jul 31, 2006 7:59 am
Location: Brighton, UK

Re: Having a built-in own SQL language?

Post by panic! »

I agree that you will always need SQL, but I've written a few applications (more and more since abstraction techniques are getting better and more refined) where I've not had to use any SQL in the actual PHP code.

You will always need to know SQL for actual database admin stuff, I don't think anyone could survive without.
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: Having a built-in own SQL language?

Post by kaisellgren »

Yes it looks a little bit like LINQ, but it would be still different though.

I looked at PHP Doctrines DQL, I was not going to use object oriented chaining like what saw in http://www.prodevtips.com/2008/09/23/dql-recipes/

Code: Select all

function getProducts(){ 
  $prod = $this->q->select('c.*, p.*, COUNT(p.id) AS num_prod') 
  ->from('Cart c')->leftJoin('c.Prod p')->where('c.id = 1') 
  ->groupBy('p.id')->fetchArray(); 
}
No, I was going for something like:

Code: Select all

$db -> prepare('SELECT "name","email" FROM "members" WHERE "id"=#',$id);
$db -> exec($cachettl,$return);
Where the escaping is handled automatically and the replaceable # could be any character you wish, an example of the same line using ?

Code: Select all

$db -> prepare('SELECT "name","email" FROM "members" WHERE "id"=?',$id,'?');
$db -> exec($cachettl,$return);
Or @

Code: Select all

$db -> prepare('SELECT "name","email" FROM "members" WHERE "id"=@',$id,'@');
$db -> exec($cachettl,$return);
By default # seems to be great, because you rarely need to use that in your SQL query and it's easily accessible in most keyboard designs (unlike some other scenarios where people forget other coders around the world) ;)

The preparement would handle escaping, it would also add automatically the prefix and bind values as well as port the SQL to the DBMS that is being used.

EDIT: And of course you can do other things with the prepare clause such as binding multiple values, so, instead of $id you type array($id,$name,...).
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Having a built-in own SQL language?

Post by onion2k »

To be honest, neither of the two examples you've given there are as readable as SQL.
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: Having a built-in own SQL language?

Post by kaisellgren »

onion2k wrote:To be honest, neither of the two examples you've given there are as readable as SQL.
The first one was something I was not even going to use. The second one I might use though.

What do you suggest? Something that is portable, easily readable, flexible, etc.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Having a built-in own SQL language?

Post by onion2k »

kaisellgren wrote:What do you suggest? Something that is portable, easily readable, flexible, etc.
I don't really believe portability between database engines is terribly important. I've been a web developer for over a decade and I've never had any reason to switch database. And if I did then I'd probably be happy to recode the SQL myself. If it was likely to be an issue, or if I had to write an application that targeted several databases, I'd put the SQL into an object that could be replaced easily. Using an intermediary language wouldn't be something I'd choose as a solution.

Writing readable code is very important to me. Not so much in the sense of reading it and immediately knowing what it does, more in the sense of reading it and knowing that is definitely the code that is being executed. If I wrote a query in your proposed language I wouldn't know exactly what was happening at the database unless I figured out how your code was translating the query into SQL. I think that's a problem.

As for flexibility... that comes back to the same issue as portability really. I'm willing to forego some flexibility if it saves me loads of effort. Using Swiftmailer to do HTML email, or FPDF to make PDF files, are examples of where I value ease of use over complete flexibility to do whatever I want. When it comes to database queries though the flexibility of SQL is very useful, and I probably wouldn't be too happy giving that up... I use an abstraction layer at the moment (ADODB Lite) but only because it still feels like I have total control of queries.

In short, I propose using SQL. I don't really see any way to improve on it.

EDIT: That's not to say you shouldn't write your library. It'd be a fun challenge I imagine. And I'm certainly not the only potential customer for it...
crazycoders
Forum Contributor
Posts: 260
Joined: Tue Oct 28, 2008 7:48 am
Location: Montreal, Qc, Canada

Re: Having a built-in own SQL language?

Post by crazycoders »

I need to speak with you i have an ongoing project that is very very very similar and am at the third version and it is not a language interpreter but more of a Microsoft LINQ technology copy where selection of data from a datasource is made using an object oriented chainable query system. This system can work on memory data such as arrays or datasources such as mysql results but i intend to make it completly cross database with the different plugins.

Would you be interrested of discussing this with me?
webaddict
Forum Commoner
Posts: 60
Joined: Wed Mar 14, 2007 6:55 am
Location: The Netherlands

Re: Having a built-in own SQL language?

Post by webaddict »

onion2k wrote:I don't really believe portability between database engines is terribly important. I've been a web developer for over a decade and I've never had any reason to switch database. And if I did then I'd probably be happy to recode the SQL myself. If it was likely to be an issue, or if I had to write an application that targeted several databases, I'd put the SQL into an object that could be replaced easily. Using an intermediary language wouldn't be something I'd choose as a solution.
I agree with that. I haven't been coding for a decade, but for the better part of it anyway. I've only once encountered a situation where my customer asked me to use a different DBMS. Well, to be honest, that customer was me and my colleagues, because we wanted to use PostgreSQL instead of MySQL. Even that transition went rather smoothly, because all we had to do is alter some SQL, which really wasn't much of a hassle.

Long story short: it takes a lot of time writing code so you change your DBMS instantly and without changing your queries, while the situation seldomly occurs. Sounds to me that that time is wasted, since it probably is faster rewriting the application to use the new DBMS than it is to write an abstraction which knows of all database quirks and special functionality.
onion2k wrote:EDIT: That's not to say you shouldn't write your library. It'd be a fun challenge I imagine. And I'm certainly not the only potential customer for it...
If it's done, and it works on multiple databases and it's not too slow, I'd consider using it. I just don't want to spend the time.
Post Reply