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.

Re: Having a built-in own SQL language?

Post by kaisellgren »

@onion2k: Okay. Let's say SQLite. Tell me how do you take the year with SQLite?? No way to do it according to documentation.

Whether I have to drop SQL DBMS that lack of features, or I have to limit my functionality or I have to find a complex way around it. All dates functions are now ported to MySQL, PostgreSQL, SQL Server, MaxDB and Oracle btw.

On a side note: anyone knows if Sybase is offering a trial or freeware solution?
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 »

jshpro2 wrote:If you wrote your query object with an implicit interface instead of an explicit interface, you wouldn't have to know very much about SQL, just string manipulation in general.. granted you could build a bad query string with an implicit interface so you loose some control at the benefit of extra abstraction, then you treat all SQL commands the same, implement sub queries as a composite pattern. You would still need a way to make sure different parts of the query got constructed in the right order, which would still imply your abstract query class knowing at least something about the SQL implementation, since the user of your query object could call things in any order
Ok.

I've been looking a lot at these DBMS: SQL Server, Oracle, MySQL & PostgreSQL. I must say they all provide pretty compherensive function set. If I just support those four, would you call the system portable? At least it could have greater functionality.
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 are able to make a layer that abstract the differences between the 4 major databases but still keeping out the really unportable/unsharable stuff, then i think it would be nice.
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: Okay. Let's say SQLite. Tell me how do you take the year with SQLite?? No way to do it according to documentation.
This is exactly what I'm getting at. Using database specific functions gives you a massive performance boost, to the point where for some apps they'll be a factor in choosing which DBMS to go with. If, for example, you wanted to write a logging and reporting application then obviously SQLite wouldn't be a good choice. Using a library that means you can't really use the functions even if they're available would be an equally poor choice.

Database functions are a good thing. Any library that blocks or discourages using them has no place in anyone's toolkit.
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:@onion2k: Okay. Let's say SQLite. Tell me how do you take the year with SQLite?? No way to do it according to documentation.
This is exactly what I'm getting at. Using database specific functions gives you a massive performance boost, to the point where for some apps they'll be a factor in choosing which DBMS to go with. If, for example, you wanted to write a logging and reporting application then obviously SQLite wouldn't be a good choice. Using a library that means you can't really use the functions even if they're available would be an equally poor choice.

Database functions are a good thing. Any library that blocks or discourages using them has no place in anyone's toolkit.
I know. There will always be a line, but I am right now trying to balance things out. To make a choice and draw the line and have great functionality yet portability.
Selkirk
Forum Commoner
Posts: 41
Joined: Sat Aug 23, 2003 10:55 am
Location: Michigan

Re: Having a built-in own SQL language?

Post by Selkirk »

In two decades of development, I've only had the issue of switching DBs come up once. I managed the conversion project. The actual conversion took 6 weeks for 350,000 lines of code. Excess abstraction in this area is unwarranted in my experience. Make an attempt to segregate DB access into a separate layer. Don't bother making that layer compatible across databases, unless that compatibility is a core product feature. That's time better spent elsewhere.
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 »

Selkirk wrote:In two decades of development, I've only had the issue of switching DBs come up once. I managed the conversion project. The actual conversion took 6 weeks for 350,000 lines of code. Excess abstraction in this area is unwarranted in my experience. Make an attempt to segregate DB access into a separate layer. Don't bother making that layer compatible across databases, unless that compatibility is a core product feature. That's time better spent elsewhere.
That, plus the fact that if you know you'll need database portability in advance, you'll write your software differently. I mean, stored procedures and/or views can really encapsulate a lot of logic in the specific database, without you having to use those special (db-specific) features in your queries. If I'd have to take multiple RDBMS's into account, I'd probably write an API of stored procedures on each of them and let the application call that API.
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 »

@Selrik: We also must not forget that we are also talking about making our script working across multiple RDBMS. Not just switching, but telling the clients that our script works in these RDBMS instead of telling them our script just works on MySQL (an example).

@webaddict: Yup that is a good road to go with.

I've been thinking about the following situation lately.

I support 4 RDBMS MySQL, PostgreSQL , SQL Server, Oracle and perhaps Sybase. I make this own language which i try to make as standard SQL -like as possible. So its easy for my developers. Then I make a system where SQL queries are in one file. eg "plugin_guestbook_sql_postgresql.php" then basically there would be 4 or 5 files for each dbms: _mysql.php _oracle.php etc. And the file has a class with functions for those SQL queries.

So whats so good about this?
- reusability: if u need to use one query more than once, u just call the same method instead of writing same thing again and again.
- all queries would be in one easy place instead of splitting them up all over the source code in many files.
- the DBAL (database abstraction layer) would not need to translate anything (or any other translator system or data layer), because it uses the specific query file that is targeted for the specfic RDBMS, so, there is NO SPEED DECREASE compared to normal projects that suport only 1 RDBMS.
- its portable. u can use the script in 4 (or 5) RDBMS.
- its later easily portable to another dbms. i just uopdate my translator class.
- the translator class could also debug some queries, and throw thoughts about developers sql queries, tell him he could do this insetad if he wants etc. and help him to fix possible imcompatibility issues.
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 »

Strange, i will probably get flammed for that, but i try to use as little as possible SPs in application programming. Mainly due to portability problem between:

1) Versions of the same RDBMS (obsoletion and deprecation of features)
2) Different RDBMS don't support the same langauge.... ANYWHERE...
- Oracle has their own language
- MS has t-sql
- MySQL has c style coding
- Postgre i think has also c style but different api for sure

I have always seen triggers (Which can be composed of RDBMS specific code), SPs and User Functions as a No-Go and always kept my applications and application apis SP free or SQL99 compliant.

Sadly, it is not always possible, think of the dreaded LIMIT/TOP difference in MS/MYAB and oracle and PG probably have their own keywords too i don't know.
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 »

So you choose portability over performance? That's a pretty strange decision in my opinion.
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 never had problems relative to performance with my software... and i've had systems with 250 requests per second on the web and systems on MSSQL running 800 users at once.

Performance impact are usually in terms of upgradability or development. I find SP to be more time consuming in terms of development than in terms of runnable performance.
Post Reply