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
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've always used MySQL but SQLite is looking more and more viable/portable/sexy.
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 »

crazycoders wrote: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?
Sure, add me kaisellgren@gmail.com or flamr43@hotmail.com and tell me who you are :)
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 have added both addresses to Windows Live Messenger if thats what you meant, waiting for you to come online
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 »

panic! wrote:I've always used MySQL but SQLite is looking more and more viable/portable/sexy.
I've used MySQL, Postgres, SQLite and MS SQL Server at various times... but I've never started out with one and needed to switch to another later. Which means database portability has never been a problem.
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! »

sure, I wasn't disagreeing with you.
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 »

Database portability is very controversed. On one side, people are tempted to simply tell their users that their software is available on RDBMS only, others take the route of versatility. Some choose to support only one RDBMS and use complex object systems that lower their performance while other use include systems to change the SQL sent to the servers to be highly efficient and still be versatile.

All in all, it's a debate of color and sex but for the computer geeks and no one will ever find a right anwser cause no need is the same.

What is interresting is to look at the tendencies. More and more, people are opting for technologies that simplify conception, development and management of software and code so that code is more readable and safe. We as developers have so many things to think about that old ways of doing our code such as:

mysql_query('SELECT * FROM table WHERE field = "'.mysql_real_escape_string($_REQUEST['fieldvalue']).'"');

is starting to weight on us because we lose most of our time building queries and writting over and over again the same code. To fix this problem and become more type safe in our queries, ORMs started appearing here and there... Great, i can now use objects to manipulate my data and i can, to some extent, change the underlying object model to use with another database system. What happens though when you need to build complex queries with agregations and mathematical or string operations? Well, no ORM to my knowledge has solved this problem apart from letting you input some code yourself into their selection functions. That defeats the point of having a layer that protects you from writting bad code.

That is why people are still coding using SQL strings and losing their time over and over again writting the same CRUD operations here and there and debugging them to see if they didn't forget something or they messed up by doing a typo.

My point, none, just wanted to say that this dilema will probably never be fixed in an efficient way in PHP because PHP is not a compiled language but an interpreted language.

If you go on the Microsoft side, they developped an incredible tool that i'm trying to replicate to the best possible extent using PHP. It is called LINQ but is far more powerful than any PHP technology can be because LINQ allows you to write code that looks like a query but is able to execute the queries on live data structures or on databases using an ORM. The code is not compiled into a SQL string, but generated live depending on the target. If LINQ detects it has to work on a memory structure to sort the data it does it, if it detects it has to work with a SQL datasource, it asks the SQL datasource to prepare a statement based on the compiled code structure. The SQL Datasource then sends the data to the SQL server, retrieves it and returns it to LINQ.

This is a major improvement i'd like to appear on all languages, unformtunately it is based a lot on type safety which has become highly important for microsoft's high level language in the past years. They understood the productive aspect of it quite a while ago. PHP will probably implement type safety in php6 but this has not been announced yet.

I think i'm rambling here... crazyone out...
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:
panic! wrote:I've always used MySQL but SQLite is looking more and more viable/portable/sexy.
I've used MySQL, Postgres, SQLite and MS SQL Server at various times... but I've never started out with one and needed to switch to another later. Which means database portability has never been a problem.
The "mistake" you make is that you are talking about Yourself. What if you have one million customers? You are just going to tell them you have never needed much portability? :)

I'm working on a project, which I want to be able to run on as many RDBMS as possible. Actually, that was quite inaccurate, I want to make sure that it runs on major RDBMS. So whether the user chooses to have SQL Server or PostgreSQL or SQLite, the script will work the same way. I might choose to support only MySQL, but then it's like saying "I'm sad that you have bought SQL Server, but you need to use MySQL with my project" :)

Actually, I think this is going to be fun - I have already started. This will be part of the core code of my project. My own SQL language. Although I'm still thinking is this a good way to go. In my project, I actually do not need to support world's most advanced SQL, probably not even procedures or such features. Or maybe I'll add those later on once I have got started ;)
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 »

kaisellgren wrote:The "mistake" you make is that you are talking about Yourself. What if you have one million customers? You are just going to tell them you have never needed much portability? :)

...

Actually, I think this is going to be fun - I have already started. This will be part of the core code of my project. My own SQL language. Although I'm still thinking is this a good way to go. In my project, I actually do not need to support world's most advanced SQL, probably not even procedures or such features. Or maybe I'll add those later on once I have got started ;)
I actually think some of the ideas here are interesting ... but it sounds like you are the one talking about yourself. ;) You can use PDO or many other solutions and get the portability you are proposing with a trivial configuration setting -- and have been able to do so in PHP for years. I should note that it has taken teams of people, some from the database vendors themselves, years to implement that functionality. It is easy to do a simple implementation, but like emailing solutions, it becomes nightmare of details if you attempt to take it much further.

This is a theme I feel like I repeat a lot -- but I have observed that it is very easy to create software that makes one programmer happy. In fact, it is easy to create software that one programmer finds fairly brilliant. You have a nice idea; you add your acceptable constraints to the design; you make the trade-offs you think reasonable. It is when you add that second or third pesky programmer that the happiness with the design seems to decrease a little...
(#10850)
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:The "mistake" you make is that you are talking about Yourself.
In your opening post you did state "Now it is your time to throw your thoughts". Were we supposed to do that in an abstract sense rather than saying what we think ourselves? :P
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:
kaisellgren wrote:The "mistake" you make is that you are talking about Yourself.
In your opening post you did state "Now it is your time to throw your thoughts". Were we supposed to do that in an abstract sense rather than saying what we think ourselves? :P
That was my thought, my thought that you were thinking about one situation :P And now you are supposed to throw your thoughts.

Isn't that how it should/could go?

Back to the topic: I find it a bit confusing if I would need to learn how to use someone's query building functionality. They can all differ some much, the functionanames, order of functions or how they work, and so on. So I definetly would not like to therefore use chainining. So how would I make queries? With the standard good ol' SQL writing. You write a line of SQL. That's it. Rather simple for programmers. And since my language would not differ much from "standard" SQL, it won't take long to udnerstand it fully. Only disadvantages of writing this SQL directly are that it's easier to make mistakes, harder to catch them, perhaps a bit slower to write queries and harder to debug. But for me these happen always fast, I seldom write faulty SQL. (actually the most advanced i usually write are joins or subqueries).

onion2k, if your client asks you to write a system that works with major RDBMS, and a system where people can create custom modules, so it has to be portable, easy to use and versatile. What would you code?
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:onion2k, if your client asks you to write a system that works with major RDBMS, and a system where people can create custom modules, so it has to be portable, easy to use and versatile. What would you code?
After picking myself up off the floor having collapsed at the thought of one of my clients knowing what a database is, I'd code a system that allows other developers to create plugins for specific target databases using a well defined interface... very similar to ADODB's approach to database abstraction in fact. It works well.

I'm sure, given time, you'll be able to think up a convoluted example of where your idea for a library would be an applicable solution. Maybe even the best solution. Until then though I'm going to continue to think it's not something I'd ever need in my toolkit.
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 »

ADODB doesn't use a very complex driver system... they work on cursors to be flexible, if you go out of the cursor methods and try to send SQL to make complex group by queries, you are simply <span style='color:blue' title='I&#39;m naughty, are you naughty?'>smurf</span> because ADODB doesn't translate the SQL, it simple makes the CRUD operations easier by forcing you to use a standard set of cursor commands.
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 »

crazycoders wrote:ADODB doesn't use a very complex driver system... they work on cursors to be flexible, if you go out of the cursor methods and try to send SQL to make complex group by queries, you are simply <span style='color:blue' title='I'm naughty, are you naughty?'>smurf</span> because ADODB doesn't translate the SQL, it simple makes the CRUD operations easier by forcing you to use a standard set of cursor commands.
That's what I want though. I don't want any of my SQL translated at all. I'm happy to write DBMS specific SQL. That's what I've been saying throughout the thread.
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:
crazycoders wrote:ADODB doesn't use a very complex driver system... they work on cursors to be flexible, if you go out of the cursor methods and try to send SQL to make complex group by queries, you are simply <span style='color:blue' title='I'm naughty, are you naughty?'>smurf</span> because ADODB doesn't translate the SQL, it simple makes the CRUD operations easier by forcing you to use a standard set of cursor commands.
That's what I want though. I don't want any of my SQL translated at all. I'm happy to write DBMS specific SQL. That's what I've been saying throughout the thread.
Let's say you have over 5000 lines of Oracle. Then you need to switch to use PostgreSQL. What happens? (Don't ask why this would happen please... thanks :) )
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 »

If you only use cursors, there are no problems. But i wouldnt want to start crunching all the records myself to make a group by (which is anyway SQL92 or SQL99 compliant but for the sake of example) myself or an average. Thats why query builders like LINQ in VS.NET are going to be more and more of a standard.
Post Reply