Joomla Postgres support contribution

[dark_ixion]

Hi,

I'm totally new here so please forgive any assumptions or
misconceptions I put forward.

Basically I've spent time rewriting a lot of Joomla's SQL to be more
standards-compliant and added a PostgreSQL database handler, as well
as additional support functions to cater for database-specific syntax
like concatenation. In theory, any other database system can be added
to it now just by writing a new handler for it and adding checks and
options in the installer.

I now have it in a state where it checks for PostgreSQL in the
installer, offers it as an option from the database drop-down menu,
sets up the database, populates it with sample data and it all appears
to be up an running. The administration section works fine too from
what I can tell.

However, I'm not familiar enough with Joomla to perform the extensive
testing necessary to check its compatibility. There are no doubt a
huge list of bugs waiting to be found which I can't locate myself.

Does anyone have any advice of where I can go from here?

Thanks

Thom

[Mario Gonzalez]

2009/10/16 dark_ixion <thom...@gmail.com>:

>
> Hi,
>
> I'm totally new here so please forgive any assumptions or
> misconceptions I put forward.
>

Me too so we're two Joomla! novices trying to get PostgreSQL up and
running here, cool! :-)

> Basically I've spent time rewriting a lot of Joomla's SQL to be more
> standards-compliant and added a PostgreSQL database handler, as well
> as additional support functions to cater for database-specific syntax
> like concatenation.  In theory, any other database system can be added
> to it now just by writing a new handler for it and adding checks and
> options in the installer.
>
> I now have it in a state where it checks for PostgreSQL in the
> installer, offers it as an option from the database drop-down menu,
> sets up the database, populates it with sample data and it all appears
> to be up an running.  The administration section works fine too from
> what I can tell.
>

I don't know if you have read the mailing list, I started the thread
"PostgreSQL support" and I have been trying to create the postgres
driver. I think you've got a really advance job related to this
subject.

Is there a way you can release your patches and/or source code and
work together? Or do you rather prefer to release a final job done?

> However, I'm not familiar enough with Joomla to perform the extensive
> testing necessary to check its compatibility.  There are no doubt a
> huge list of bugs waiting to be found which I can't locate myself.
>

--
www.mgonzalez.cl/
twitter.com/gonzalemario

[dark_ixion]

On Oct 16, 2:50 pm, Mario Gonzalez <gonzalema...@gmail.com> wrote:
>
>  I don't know if you have read the mailing list, I started the thread
> "PostgreSQL support" and I have been trying to create the postgres
> driver. I think you've got a really advance job related to this
> subject.

I guess you already know that additional handlers won't work out-of-
the-box then. All those a.* style parameters in selects with a GROUP
BY clause didn't help things.

>
>  Is there a way you can release your patches and/or source code and
> work together? Or do you rather prefer to release a final job done?
>

Yes, I can release the patches, additional handler and installer files
needed to get this running. I have some tidying up of some of my
diagnostic and debug code before I do so though, and I need to change
the installer SQL to not directly insert values into identity
columns. Where would I submit this once it's ready?

Also, are there any additional guidelines to get this included in
future releases? I'm afraid that due to the sheer volume of changes
I've made, it won't be easily mergable into the trunk of 1.6.

Thom

[dark_ixion]

And here's the URL of it up and running on PostgreSQL: http://darkixion.com/joom2/

[Scott]

While I love the idea of Joomla being a multi-DB CMS I fear for third party developers.  First Postgres, then Oracle, then SQL Server, then DB2 then...well you get the point.  I almost think it would be more beneficial if a query API was developed in the Joomla core with specific database adapters built to translate the API to their own specific query syntax.  Something along the lines of what Hibernate has done with its query API (not its ORM or hql).  Joomla would have to go back to the drawing board a bit and implement some of its tables in a more generic fashion but in the long run I think it would introduce the fewest number of headaches to 3PDs and create a layer that any database adapter could implement no matter how obscure.

Thanks,

Scott

[Louis Landry]

Thom,

Would you be interested in us setting up a postgres branch off of trunk and you can work on getting this stuff working based on the 1.6 trunk?  There really isn't much chance that we are going to incorporate postgres support into the 1.5 stable tree for a multitude of reasons, but I would be very interested in seeing what can be done with respect to 1.6 or even 1.7 after it.  Great to see people not only interested but active on this topic :)

- Louis

--
Development Coordinator
Joomla! ... because open source matters.
http://www.joomla.org

[dark_ixion]

I agree that it would be preferrable for Joomla to implement a query
API or framework. Unfortunately I expect that would result in even
more extensive changes to the system. I do think that should still be
on the roadmap though.

In the meantime, third party developers would merely have to follow a
few guidelines to interact with databases without using database-
specific syntax. Individually this shouldn't incur much overhead,
unless their add-ons are bloated with hundreds of custom queries.
This also raises the question of add-on compatibility checking. If
multi-DB support were added, a check would have to be performed on the
add-on to see what it declares itself compatible with. Maybe a
manifest file that says something like "I only work with the old MySQL-
only Joomla" or "I should work with every DB". If that manifest file
isn't there, it marks it as incompatible. Just an idea.

In the end, Joomla would greatly benefit from being freed from lock-in
with a particular DBMS. I believe that an SQLite handler would also
allow those without any database system installed to still use Joomla
with a local db file.

Thom

[Joseph LeBlanc]

On Oct 16, 2009, at 10:57 AM, dark_ixion wrote:

> I agree that it would be preferrable for Joomla to implement a query
> API or framework. Unfortunately I expect that would result in even
> more extensive changes to the system. I do think that should still be
> on the roadmap though.

Take a look at the JQuery library in Joomla 1.6. This could be a
starting point for multi-DB query support in third-party extensions.

> In the meantime, third party developers would merely have to follow a
> few guidelines to interact with databases without using database-
> specific syntax. Individually this shouldn't incur much overhead,
> unless their add-ons are bloated with hundreds of custom queries.
> This also raises the question of add-on compatibility checking. If
> multi-DB support were added, a check would have to be performed on the
> add-on to see what it declares itself compatible with. Maybe a
> manifest file that says something like "I only work with the old
> MySQL-
> only Joomla" or "I should work with every DB". If that manifest file
> isn't there, it marks it as incompatible. Just an idea.

Extensions are already able to specify different SQL files for
different databases. All we should have to do is configure Joomla to
reject the installation of components that do not have an SQL file for
the database being used. (Unless this already happens.)

> In the end, Joomla would greatly benefit from being freed from lock-in
> with a particular DBMS. I believe that an SQLite handler would also
> allow those without any database system installed to still use Joomla
> with a local db file.

Yes, multi-DB support has been on the wishlist for a while. Thanks for
stepping up!

-Joe

[Joseph LeBlanc]

On Oct 16, 2009, at 11:54 AM, Joseph LeBlanc wrote:

In the meantime, third party developers would merely have to follow a

few guidelines to interact with databases without using database-

specific syntax.  Individually this shouldn't incur much overhead,

unless their add-ons are bloated with hundreds of custom queries.

This also raises the question of add-on compatibility checking.  If

multi-DB support were added, a check would have to be performed on the

add-on to see what it declares itself compatible with.  Maybe a

manifest file that says something like "I only work with the old  

MySQL-

only Joomla" or "I should work with every DB".  If that manifest file

isn't there, it marks it as incompatible.  Just an idea.

Extensions are already able to specify different SQL files for  
different databases. All we should have to do is configure Joomla to  
reject the installation of components that do not have an SQL file for  
the database being used. (Unless this already happens.)

Forgot to add: this typically applies to components, but not necessarily modules or plugins. So we would need to add a supported DB-drivers section to the manifests for modules and plugins.

-Joe

[Hannes Papenberg]

Hello folks,
Ercan and I talked a bit and we decided, that we want to include other
database drivers into Joomla 1.6. This is not meant that Joomla will
support other DBMS besides MySQL in 1.6, but that third party developers
don't have to learn a dozen different database interfaces and instead
can use the classes and functions from Joomla, no matter if they are
connecting to a MySQL, MSSQL, Postgres or Oracle DBMS. The goal is to
improve Joomla in this enterprise area and let it connect to other
systems out there.
In future versions, we want to make the SQL of Joomla generic enough
that you can use it with all those systems.

This is a proposal that very much depends on the community. You, the
community, would have to send in the database driver for your favourite
DBMS, so that we can add it to the system. The rules are pretty simple.
Take the current JDatabase class and create a driver similar to the
MySQL driver and send it in. Make sure that it is according to the
Joomla coding style guidelines. Most likely there will be a few people
that are interested in working on a driver, as we have already seen in
this thread. Please coordinate your efforts among each other so that not
two or more people do the same task. The worst would be, that we get 10
Postgres solutions send in at the end and we had to choose among those,
throwing away the hard work other people did.

We are trying to get 1.6 on the road, so we'd like to get this going
fast. That's why we'd like to have a first solution (read: a driver that
looks as if its working ;-) ) by november 1st.

Looking forward to your submissions. :-)

Hannes

[dark_ixion]

On Oct 17, 11:01 pm, Hannes Papenberg <hackwa...@googlemail.com>
wrote:

This is an exciting development! Could you tell us where we can send
our candidates in to? And in case you're interested, I've already got
all the SQL calls made into generic ones if that would be of any use
in future.

Thanks

Thom

[Hannes Papenberg]

You can send in the code in patch format to the Joomla 1.6 feature patch
tracker, that you can find on joomlacode.org. And yes, we would be
interested in those changed SQL queries, though not for 1.6. I think
this is something that we should look at in 1.7.

Hannes

dark_ixion schrieb:

[Thom Brown]

I have submitted the driver I've been using in my adapted version of Joomla.

Anyone can review it to let me know if they spot any potential issues.

Thom

2009/10/17 Hannes Papenberg <hack...@googlemail.com>:

[Ercan Özkaya]

Well, Grepping the source shows that we have about 40 instances of
CONCAT and CONCAT_WS functions. Why don't we get rid of them and
simply do it as post-processing in the models? That's probably slower
I know, but the performance hit shouldn't be that big.

On Oct 18, 2:20 am, Thom Brown <thombr...@gmail.com> wrote:
> I have submitted the driver I've been using in my adapted version of Joomla.
>
> Anyone can review it to let me know if they spot any potential issues.
>
> Thom
>

> 2009/10/17 Hannes Papenberg <hackwa...@googlemail.com>:

[Andrew Eddie]

Agree. I think CONCAT and CONCAT_WS should be avoided in the queries
themselves.

Regards,
Andrew Eddie
http://www.theartofjoomla.com - the art of becoming a Joomla developer




2009/10/21 Ercan Özkaya <ozkay...@gmail.com>:

[Mark Dexter]

With 1.6 we have the JQuery class. Could that be used to translate the
different CONCAT syntax? Mark

[Andrew Eddie]

Down the track, it could. But while in the past I've used CONCAT, I'm
really questioning the wisdom of it. About the only time I use that
is when I'm doing data migrations.

Regards,
Andrew Eddie
http://www.theartofjoomla.com - the art of becoming a Joomla developer

2009/10/22 Mark Dexter <dexter...@gmail.com>: