Joomla! Forum - community, help and support

I did some work to implement the PostgreSQL layer for J! and core database structure as well and end up at a well running front page (with no sample data yet). 

As expected, digging further down I found that most of SQL queries are extremely MySQL specific.

I think the first step should be to check all SQL in J! core and make them to be less (not at all, indeed) DB specific. Sometimes additional PHP code is involved, of course. The JQuery, discussed here is the next step. Extensions/modules/whatsoever developers should be warned to use stricter SQL and to avoid DB specific functions/structures. Some incompatibility will raise but since this would be done in a major Joomla! release it is acceptable.

Can we start the first step? 

Regards,
Plamen Petkov

p.s. I am not a part of any dev team, I am just a well experienced in PostgreSQL and it is a pity Joomla! still does not support this DB. 

Hi,

I suggest you join forces with the folks working on the "inofficial" MSSQL layer to find *all* -- or more -- of the MySQL specific queries that won't work in other DBMS's, and create a project on JoomlaCode.
Feel free to submit your code to the developer team. I'm sure they'll have a look at it if time permits.

Queries aren't limited to appear in the core, there are a few 3PD-extensions to consider as well, so this is nothing that would happen next week.
This also includes a full set of SQl-scripts for the installer to create the database and to add the example data, and since a gazillion extensions use MySQL statements in their (XML-) installer files, too, it'd be nice to have a SQL parser available to "transcribe" MySQL to whatever-SQL.
IIRC the new XML installer files may potentially contain SQL for other RDBS than MySQL(i), and provided 3PDs then add PostgreSQL, MSSQL, Oracle, Sybase, or even MS-Access to their installers, this could work.

Unless some kind of query-builder makes it into the Joomla! Framework, virtually any (old) extension that does not use the new database API is likely to fail anyway.

Have fun,
CirTap

Could you please point me the right place to check ?  Couldn't find it... 


Regarding the parser fot transcribing/translating MySQL to any-other-db syntax... I think this is a wrong approach! It is a non-sense to give a birth to something NON-standard and after that to make efforts to translate it to something Standard. Just make him a standard from the beginning, from the core!

I still think the right move is:

1. (Re)Write the Joomla! core SQL queries as much as standard. Do not use DB specific syntax *at all*.
2. Keep the *current* functionality, if using non-standard SQL syntax, outside the SQL, use PHP for that.
3. Do all of this for the next major Joomla! release and announce a possible 3rd Party Incompatibility!   

I'll give u an axample how easy I made 3-4 core non-standard SQLs a standard ones. The first problem was the "CASE WHEN" SQL structure... Well, I just added a "(" and ")" after the conditional statement.  The second issue was a CONCAT_WS()  EXTREMELY, VERY BADLY STANDARD BREAKING function. I moved the concatenation outside the SQL... and voila. Now the front page is working just fine with all sample data. 

Of course, much more heavy work must be done, I know that. It is a matter of months. But still, the approach is not to translate the buggy code, but to create a clear code. Parsers/analyzers are always buggy. For instance, I couldn't imagine how a parser would solve the "CASE WHEN" problem mentioned above?  Never! And the CONCAT_WS() ?!?!  Wow... I don't want even to think about it :-)

Additionally, if 3d Parties would like to use their own database as a backend for their components/modules/whatsoever, it is not a problem! They still can, because it doesn't matter, by means of Joomla! doesn't care about what DB storage components are using, does it? They should only follow the simple rule when using the Joomla! core DB back end - use standard or fail with incompatibility!



IMHO.

Plamen

Hi,

plamendp wrote: Could you please point me the right place to check ?  Couldn't find it...

I was thinking of this post: http://forum.joomla.org/index.php/topic,142051.0.html which is about a (working?) MSSQL database class.
Pe7er mentioned some in http://forum.joomla.org/index.php/topic,180358.0.html
There's http://joomlacode.org/gf/project/pdo/ and maybe http://joomlacode.org/gf/project/msconnect/ (MSSQL)
The PDO developer's confession: "I've been talking about this for years, and now I am in the position to actually do something about it. "

Since all DB vendors invent extensions to the ANSI/ISO standards, and improve their own engines with each version, teaching it new tricks, one will always run into trouble with literal sql code -- you don't need CONCAT_WS() to break a query, a simple LIMIT will do.
At the end you need a "sql builder" like those provided by Zend_Db or PEAR::MDB which adds another layer in between your application and the database, and you'd never be able to use the full power of a specific db engine w/o using "proprietary" code.

You probably do have a reason for wanting PostgreSQL support, and somehow I doubt it's because of writing standards compliant SQL code *only*.
Maybe it's habit, maybe you believe PSQL is better, maybe you like it's "SQL features", maybe it's because your ISP or those of your clients, only provide PostgreSQL servers.
All of this made me use MySQL for some 10+ yrs, depite I am in the lucky position to have access to an environment that'd allow me to run any other db server as well. Unfortunately I can't "pull" every of my clients to this environment; I don't even want to.

plamendp wrote: Additionally, if 3d Parties would like to use their own database as a backend for their components/modules/whatsoever, it is not a problem! They still can, because it doesn't matter, by means of Joomla! doesn't care about what DB storage components are using, does it?

well, as I regular user I'd consider this indeed a huge problem, if my ISP simply doesn't support the 3PD's choice of server. I'd be realy pi$$ed if my whole site runs fine with MySQL and this one nice-to-have extension would require another engine; why doesn't this 3PD support MySQL, or to stick with your argument, "standards SQL" for that matter?

Joomla! might hypothetically not bother should it support different servers some day in the future, but if there's no such server to connect to, it doesn't help neither that 3pd developer (to sell his extensiion) nor the user (me) who might want to use it.
Lots of ISPs (at least in Germany) provide MySQL only, some PostgreSQL per individual request. MSSQL on the ther hand appears to be a bigger issue for Intranet sites and host running ASP sites.
It's not like each J! user has access to a dedicated server, so their choices are usually pretty limited.

Don't get me wrong, I agree with you, and I see the benefit and a need for this. It's just that I don't happen to have this need.
And for the records: you're preaching to the choir, but you're also barking the wrong tree
There's nothing I can do to change this.

Have fun,
CirTap

Thanks for the links and your comments. I got your point (about barking and everything) 

Plamen

I agree with many of the points here.  What would be helpful for me is a document that outlines what the standard is and what is supported in various DBs.  I've not used much other than MySQL for a while, and so I use mysql.com for my reference point.  I don't know what is non-standard, so it is hard to avoid it, even if I wanted to put the effort into it.

There was a JQuery class at some point, but it wasn't ready for 1.5.  This might make it into 1.6 or 2.0.

Ian

Hi!

I'm considering using Joomla for my site.

But, being a Interbase/Firebird expert, I would like to run it using Firebird as a back-end.
Not that I think something bad about MySQL but FB would allow to integrate Joomla to other applications at DB level using triggers, stored procedures and updatable views without XML-RPC etc.

So if somebody is going to start (or already) cross-db project for Joomla I could take part.

For the project I see the following main problems to address:

1. Database connectivity. Why not adodb?
2. Data type mapping: not all database have boolean for example.
3. Syntax differences.

Among them the syntax is the most hard, IMHO.

There are some possible solutions:
1. Extracting all SQL code to resources like language resources for English, Russian, etc.
2. Adapter-like functions for specific syntactical features like getting current date and time, concatenation etc (like adodb)
3. Complete query builder
4. ORM

As for Postgress it also has its strengths like very good multi-core/CPU scalability, even clustering, etc.

So.
If anybody is interested you can count on my 2-3 hours a week translating SQL tricks to Firebird and index optimization for it.

Maxim Shiryaev

@Ianmac:
I think MySQL sticks pretty closely to the standard, and where it doesn't it mentions it in the documentation.

@maxim:
So far as I have looked into it, adodb doesn't deal with the various non-standard implimentations of SQL, it just lets you pass a query to whatever db you happen to be connected to at the time without all those nasty "mysql_query" type db-specific functions in your code. I've always thought this to be a rather weak implimentation of db abstraction as using a SQL standard clause like "LIMIT 0, 100" will break MSSQL, so the programmer still has to know what db their code will be used with so they can write "safe" queries.
A better way (in my humble opinion) is for the abstraction layer to transparently translate a standard SQL query into the syntax required by the particular db engine so the programmer doesn't have to know anything about the db being used, and his code will work on any engine so long as he writes standard-compliant SQL.

On one of my posts in the thread at:
http://forum.joomla.org/index.php?topic ... icseen#new
I posted up a mssql interface file which attempts to do just that for certain queries. It's not finished, and there's probably better ways of going about this, but it's a start.
Perhaps a query builder would be a good way to go; the JQuery class sounds like it could be useful, but we could do with some sort of organisation and core-team directive here as to what the architecture should look like.

Hello
:-Lightinthedark

MaximShiryaev wrote: Not that I think something bad about MySQL but FB would allow to integrate Joomla to other applications at DB level using triggers, stored procedures and updatable views without XML-RPC etc.

As for Postgress it also has its strengths like very good multi-core/CPU scalability, even clustering, etc.

you're not up to date with MySQL development: this is all available as of MySQL 5 -- not sure about multi-core/CPU, but there are a different flavours of MySQL available.

just for the records

CirTap

So far a full-featured, DB-aware, non-avoidable (i.e. user NOT allowed to set the query string directly) query builder sounds most reasonable to me. It seems we are reinventing the wheel.. Where is JQuery btw ? I can't find anything about it here ?  Any coding available ? A project ?


Plamen

plamendp wrote: It seems we are reinventing the wheel..

aren't people always doing this? different, larger, smaller, faster, better?

plamendp wrote: Where is JQuery btw ? I can't find anything about it here ?  Any coding available ? A project

it's been part in some early version, but as Ian said, vanished since, as it wasn't ready/doable, or for whatever reason.
I expect it to come back in a future version; no idea when nor what it will be called then.

CirTap

I would just like to state the obvious and say that any sort of JQuery type of class would have to be very flexible if it's to work. There's got to be scope for subqueries, joins, column aliases, batch queries, and probably several other things I haven't mentioned. If you can't do everything with the query builder that you can with direct queries, then that's going to be a headache for developers. When you've got a moderate to large database, being able to do exactly the query that you want can make the difference between a query taking seconds and minutes.
I expect everyone else knows this (but if you don't agree I'd be interested to know why), but I just wanted to make sure that everyone appreciates the need for effeciency and flexibility for the developers to get the best from the application.

Hello
:-Lightinthedark

lightinthedark:

You are perfectly right... if such a Query Builder is supposed to be used for big/complex 3rd party extensions.

Few years ago I started & developed a Real Estate component, fairly complex and realy huge, including the administration backend. Firstly, I thought it is possible to use the Joomla! DB backend (MySQL) for everything... but ... very quickly end up at a conclusion that I shall use Joomla! as a pure CMS framework only (user interface, user related issues, design, etc., you know, common CMS things) and for everything else to use a separate database. Since I am a PostgreSQL fan I wrote the whole component using PEAR & Postgresql, but nothing would stop me to use MySQL. That way, Joomla! (1.0.11 at the time) happened to fullfill all my requirements as a CMS framework and I was happy.

Huh.. the point is: big, complex, huge components & extensions is supposed to come with its own, application specific database backend, be it MySQL or anything else. These kind of projects happen to use extremely complex queries (like one of mine: about 30 rows with 4-level sub-selects!). Of course JQuery could not be of help in this case! Not even slightest help!!!

The final point is: Joomla! Query Builder must be flexible and powerfull up to some, acceptable, limited extend with main goal to serve the Joomla! CMS Framework and 3rd party extensions with limited complexity, eliminating the "too much MySQL effect", which is the subject of this thread 

Finaly, let me rimind you that my post is starting with "You are perfectly right..."   

Plamen

This topic may be of interest:
http://forum.joomla.org/index.php/topic,193382.0.html
As may this:
http://forum.joomla.org/index.php/topic,142051.30.html

If the Joomla framework is to be separated from the CMS a bit more, then it seems logical that it will be used for big/complex 3rd party extensions or maybe even applications which don't touch the CMS at all.

However it gets done, I do agree that Joomla needs to be less MySQL tied.

Hello
:-Lightinthedark

Hi,
Without wishing to be accused of spamming, I thought I'd just put up a link to the 1-hour old project to create a JQuery class for 1.5's architecture which will hopefully resolve these db connectivity issues in time for 1.6

The project can be found at http://joomlacode.org/gf/project/jquery_1_5/

Hi folks,

I're relaunched the PDO Driver project on JoomlaCode:

http://joomlacode.org/gf/project/pdo/

I have a first implementation that I'm uploading after next week, as I have some personal things that are keeping me from finishing testing.

I think this might be the best place to start regarding a query builder, but that's just IMHO

Hi,

I like PDO. Mostly because it is a part (extension) of PHP itself. The only problem is that it is a  "PHP 5+" only. Which, OTOH, can be considered a temporary one. I think in a year PHP 4.x would be obsoleted or just become too old :-)

JQuery and PDO-driver  projects can be, and I think they should be, completely separate projects. As far as PDO (and PDO driver project for that matter) provides data-access abstraction only and JQuery is intended to provide SQL-implementation abstraction they can be, but not  necessarily, used together at the application level. 

However, now I realize that I have to reconsider the JQuery design to reflect the fact that Joomla! might, in the future switch to another DB driver. Well, that's another story...

See also:
http://forum.joomla.org/index.php/topic,195747.0.html

Plamen

Seriously looking for a quote on a standardized data abstraction layer completed with a mssql driver.

Also check out this thred :http://forum.joomla.org/index.php/topic ... #msg924058

Not sure of the main differences here...but I think everyone's looking for the same results.

CirTap wrote: it'd be nice to have a SQL parser available to "transcribe" MySQL to whatever-SQL.

Forget it! Mysql's dialect is too far from posix so you'll need some AI to get it done.
Better work on clean borders !

a) PHP code itself is only allowed to do DML queries.
b) DDL+DCL should be hand-written for each target RDMBS - the installer needs a clean
way for picking the right one.

CirTap wrote: IIRC the new XML installer files may potentially contain SQL for other RDBS than MySQL(i), and provided 3PDs then add PostgreSQL, MSSQL, Oracle, Sybase, or even MS-Access to their installers, this could work.

It should contain completely separate schema files for each target RDMBS and then pick
the right one depending on selected RDMBS type. If a schema is missing, the install has to
fail before anything's done.

cu

I have not even thought about letting anyone on the board know about this.

Sorry...

But, I got my system working (in-house MSsql + website MYsql) with a bridge. Basically whenever there's a change in the in-house exe program, it pushes the change over the the MYsql. When there's a change on the J! site, it sets a flag. Then the in-house system checks it every 10 minutes for flags...loads the changes...resets the flag. Pretty easy.

Now we have a 4000+ memeber site in J! and I don't have to worry about what the in-house system admins want to do. F 'em.

Any more news on this? Were linked to here by the PostgreSQL wiki.

http://joomlacode.org/gf/project/joomlaonanydb

Ian

Thank you, sir Though, it were a bit unclear where to start.

joomla supports postgresql or not ,if support i want plugin for jumla1.6 sothat joomla 1.6 can communicate with postgresql.if you have pluigin kindly mail me [email protected]

i think if we want to us mysl
we must connect to mysql with ODBC access.

Hi all
Are there any new news? I've been looking for information but I have not found anything, I thank you if there is good news, because this post has more than 3 years. I need to connect J! PostgreSQL

Greetings
Alejandro
LatinAmerica

Mac OS X - Lion - uses PostgreSQL as the default database (see http://www.apple.com/macosx/server/specs.html under the Web Hosting heading).
Does, or will, Joomla! support PostgreSQL?
Bob