Also for reference, there are a total of 7 installations that reported metrics to the stats server since the beta release for PostgreSQL and 0 for the Microsoft engines. Folks aren't even testing on those engines, the project should not offer support for something nobody can test or maintain.

This will close the door for joomla to intranet corporate sites. For now I'm use mysql but in near future want migrate to postgres (30K users now and ~500K can be in future). Mysql is a... no I`ll not say it.

Great CMS with highly crafted MVC, but without any other DB it`s lesser attractive.

I also think that the sites built with databases other than MySQL are most likely in corporate enviroments. Sometimes even behind a firewall (eg intranet sites).

Personally I think if we want to drop support, we could do it for 4.0. Dropping it now after some people invested quite a bit of their time to fix various issues with PostgreSQL and MSSQL would be a slap into their face. I think it's currently in a better state than it was last year at this time.

Why not make a call for maintainers in a post install message, if nobody shows to mantain them, then consider droping support.

@mbabker i think is too soon to take any conclusion from the data from stats plugin. Majoraty is still at 3.4.5 that has no stats plugin.

Also don't forget many are still stuck at 3.2.7 because of php version (e.g. CentOS 6) and they will not appear in the stats because they don't have a stats plugin.

The features have been there for over three years now and have never been truly stable.

When SQL Server support was added in 2.5, the Smart Search component never functioned with it (actually that didn't start working right until 3.1's release 15 months later).

The database schema for the tags feature was not even added to the CMS for the non-MySQL engines until after the feature was merged; beta 2 for PostgreSQL and beta 5 for SQL Server; for all intents and purposes the CMS was broken on those database engine between the merge of tags and the addition of the necessary backend support.

When volunteers do attempt to contribute fixes, those go untested because there are very few individuals testing the CMS in those environments, resulting in those patches being unmerged and constantly requiring rebasing just to be testable (#3751 is a good example of a recent item).

PostgreSQL, considering its open nature and the ability to be installed in most environments you can set up an *AMP stack (and IIRC there are a couple of those products that replace MySQL with PostgreSQL), hasn't had as many issues since its support was added in 3.0. In part because many of the major hurdles were crossed with the SQL Server support. That doesn't mean it hasn't had its share of issues or ignored items either; #6972 is an example of an open item that's waiting for leadership decision for five months now. And there's #4165 where after a year of no activity the cut and dry "update this or we're rejecting it" message has been given; I sure wouldn't want to update something for it to be ignored for another year.

This "experiment" has gone on long enough IMO. The issues I've pointed out here are just a small sampling that proves Joomla does not have the resources to provide multi-database support for the CMS application stack.

Accepting that there are just not enough people interested in non-MySQL databases within our active developer community, I think we should move towards using a third-party database abstraction layer instead of trying to do this stuff ourselves. A good choice would be Doctrine DBAL (note: not Doctrine ORM, that's a whole different question).

In Joomla 3.x we would write an adapter that would allow existing code to use the DBAL without change. This could be tested by using the DBAL + adapter to access MySQL. We would then have a high degree of confidence that it would run on Postgres, MSSQL and others, without having to formally support any code for those databases ourselves. The DBAL adapter could be a separate core-supported download (also noting that Doctrine DBAL has a PHP 5.4 minimum requirement).

In Joomla 4 we would then deprecate all our current database adapters but continue to support them for the duration of the Joomla 4 series. During the 4.x series we would gradually refactor the core code to use the DBAL natively. Anyone running into issues with our deprecated drivers for PostGres or MSSQL would be advised to switch to the DBAL adapter instead.

Then in Joomla 5 we would remove all our adapters and use the DBAL exclusively.

I realise that this involves writing more code and there is a question regarding who would be prepared to write it. But assuming volunteers step forward we would at least be replacing two adapters with one and with a clear plan to drop all our adapters prior to 5.0, resulting in significantly less code for us to maintain. Also, it looks like supporting MSSQL through a DBAL adapter would be a lot easier than trying to support it directly ourselves, especially where pagination is concerned.

In the meantime, avoiding new installs on non-MySQL databases (unless and until a DBAL adapter is written) makes a lot of sense to me.

In the meantime, avoiding new installs on non-MySQL databases (unless and until a DBAL adapter is written) makes a lot of sense to me.

I'm not sure if preventing it is a good idea. But I would support showing a warning for those who select non-MySQL in the installation. That could be done using the showon feature in the installation.

That could be done using the showon feature in the installation.

Only way you can do that is with a dummy form field or rewrite the showon handler to show/hide a message container.

Which of course assumes everyone uses our installer which they don't

Softaculous is smart enough to not offer the choice

Not to repeat myself but

https://groups.google.com/forum/m/#!searchin/joomla-dev-cms/Postgres/joomla-dev-cms/VZNVSf4IfZc

Only way you can do that is with a dummy form field or rewrite the showon handler to show/hide a message container.

Yeah, I would use a dummy form field.

Well, considering how many hacks already exist in Joomla, why not another one? :snicker:

Besides the very valid intranet scenario, postgres and mssql "contributed" to a better sql standardization in joomla. And that´s the way mysql goes with it´s newer versions. The strict_mode becomes stricter.

Well, considering how many hacks already exist in Joomla, why not another one?

It's creative use of an API

I agree with @chrisdavenport on the choice to move to Doctrine DBAL There is an open request in the Framework for that.

As for the CMS, There is a lot of chatter in various channels requesting to only support MySQL moving forward. I guess the question is whether to put Doctrine DBAL in the CMS with our own ORM mapping for existing functions; or to only support MySQL in the CMS?
I lean towards Doctrine DBAL as the solution.

Third-party DBAL is best and right solution, I think. Doctrine in this case looks delicious.

Fully disagree, this #pr is a bad move IMHO, please consider how many intranet joomla based there are, and most of them are based on non MYSQL database ( and they are non counted on stats plugin).
The real problem is that developers still write "bad" code regarding sql stuff and most of times they write using mysql sql dialect wich is not a fully "SQL standard" lingo.

If there are so many people using Joomla on non-MySQL intranets then how come they have not come forward with the bug fixes that they must have made in order to get it working? I'm not talking about extensions here, I'm talking about the core code.

I can speak only for myself, as i'm proudly running joomla on intranet with postgresql and I've shared all the fix I've experienced , why others don't share, there is not an easy answer I'm afraid, not involved in the community, closed source environment, not much familiar with the joomla github workflow etc.....

Fully disagree, this #pr is a bad move IMHO

This is the second time in ~14 months that the proposal has been raised (first Brian with comments on the mailing list and now me with actual code), and both times it results in folks who say they use these platforms coming online and voicing their displeasure with such a thought. Aside from one or two folks who contribute fixes, after the thread dies off things go back to "normal" and the environments stay pretty well unmaintained. Even the system test suite (the one where the UI and full system integration is tested) only tests MySQL support (granted SQL Server is a bit trickier to accomplish because you have to have a dedicated platform for it, but PostgreSQL is easily if not already installed in every CI environment the project uses). If the test suites were testing the non-MySQL functionality then there might be more confidence in the stability of those platforms. FWIW in the unit test suite all database functional tests run with SQLite3 except for the test cases for the database drivers themselves, proving library level support (for the most part) with a non-MySQL database engine (the only class I couldn't write tests for without major query rewriting was JCategories), but the libraries that have test coverage generally haven't been the issue; generally it's extension level code that breaks in these environments and there is next to no test coverage for anything but com_weblinks outside the libraries.

Without DEDICATED resources to keep multi-database support a viable option in Joomla, the only other alternative is to cut back on the announced support for it. How much longer is the project willing to use its user base as a grand experiment for a feature that has never been properly stable?

@mbabker To be fair, @alikon is quite a consistent contributor and tests the stuff on PostgreSQL. So he sure is the wrong recipient for your message.

It wasn't aimed directly at him, and he was included in the "one or two folks who contribute fixes" count.

Also to be fair, I too have spent my fair share of time testing specifically on SQL Server and PostgreSQL environments and did a lot of cleaning up and issue fixing in that period. So I'm fairly aware of how much effort goes into these types of fixes; I'm the one who broke backward compatibility on Smart Search for 3.0 to try and make that system function for non-MySQL platforms. Multi-database support is not easy to maintain without a well formed API, a strong test system to validate behavior across multiple platforms, and contributors constantly testing non-standard configurations (not just database level either, web servers and different PHP configurations as well). Most of the reason I abandoned my own efforts related to my old Windows system crashing and being somewhat annoyed with how much time it took to get fixes for these platforms implemented; considering that was about the same time I took over release distribution, my time was better spent on something more useful.

How can i disagree with your analysis ?
yes, is really a very dirty work, but....
as my boss love to say: "someone must do it"
it's an important feature, after all, i believe
we can't fully give up

why not something like this ?

p.s.
@mbabker, @Bakual
... till a couple of week ago i was running on vista now i'm experiencing 7 crashes...
so maybe we said the same "bad words" in different languages

Label "Language Change"?

That's an automatic label added to any PR that makes a change to a language file to aid translation

@mbabker, @Bakual, @alikon What is all of your thoughts on moving to Doctrine DBAL, as has been suggested?

I don't know Doctrine myself, but from what I hear it's a solid database abstraction layer. I certainly support using an available 3rd party library instead of trying to do and maintain the same ourself.

Fully disagree, this #pr is a bad move IMHO,

I really do not think it is a bad move. It is a good move actually. The CMS is encumbered with a lot of code that does not see much exposure.
1. There are much better third-party solutions that are more heavily tested and maintained by the wider PHP community. (Doctrine for example)
2. Joomla 4 should be moving to a more distributed package model anyway, so this makes sense as a short term goal to decouple Joomla from a specialized area of required maintainability for specific use case created PHP foundation library only specific groups are using.
3. My personal opinion: Intranets are highly specialized an in this case are a minority against the majority of environments Joomla will be installed on. If support is truly needed for these Intranet environments, it should be obligatory to be maintained by those parties. Unfortunately that is not happening, and everytime something breaks I rather resources go into more important issues. However not having a solution available is also an issue. Making PHP packages more friendly in the Joomla developer environment can go a long way to making things a simple pull package dependency in matter.

@coder4life The comment from Alikon was about this PR, not about a potential move to a 3rd party DBAL.

I have tested this item unsuccessfully on 0f25dc3

Breaking B/C !

@sovainfo Where did it break B/C?
I haven't tested but no existing installation should be affected.
Only new installations would not be able to be completed.
Existing installations instead would get a postinstall message.

Only new installations would not be able to be completed.

Which is exactly the break of B/C!

This PR should have been closed just because of the break in B/C. Obviously, this is not the proper way to deprecate functionality!

Which is exactly the break of B/C!

Technically, no. What is covered by our backward compatibility is defined here: https://developer.joomla.org/news/586-joomla-development-strategy.html#backward_compatibility
So in theory, this would be possible.

There are surely arguments against, but B/C isn't an issue here. Existing sites keep working as before.

Don't know what makes you say it is not covered by the B/C promise. The fact you consider this not a break in B/C only shows how poor that definition is described in the document you are linking to.

Although the site could be updated when this stupidity is accepted, doubt that site owners would follow that procedure. Some might, it is a no-brainer to revert it. It won't be the first time they have to correct the code provided by the project. They are used to that now.

Probably a good time to reconsider the decision to go with Joomla when you can't rely on them.

Only new installations would not be able to be completed.

Which is exactly the break of B/C!

What exactly constitutes a B/C break about it? The fact that users could install on these broken environments previously and now cannot? In that aspect, you're absolutely right, however given the circumstances described here it is probably the better option of two crap choices. Either Joomla continues to advertise multi-database support with a broken to bits application or it blocks the ability for new users to install on those broken environments. SQL Server support was added in 2.5.0, PostgreSQL in 3.0.0, and I don't believe there has been one release where there has been 100% feature support in the core platform without fatal errors. Next month will mark four years of official multi-database support, one can count in weeks or even days the amount of time that has truly been stable.

Also, face it, this isn't going to get accepted. A fuss will be made for a little bit, then attention on the major issues in this part of the application will fade away, and in three or four months it'll be like this never happened; that's generally what happens in Joomla world when someone suggests a major portion of the API is broken beyond repair. And instead of addressing the issues they're still going to exist. Sure, the thought of using something like Doctrine is getting passed around, but frankly I see that implementation getting screwed up resulting in an API that makes using half the features of the DBAL impossible without bypassing Joomla's API. Devs are already used to ignoring Joomla's API anyway, so at that point it probably won't matter.

Just another example of pull requests which are working on MySQL but ignore other databases, we have #4115. In the world of Doctrine, they have a proper schema management API that automagically and consistently builds database schemas for all supported platforms. You'd make the schema changes in one centralized location and their API would handle all the rest. Because Joomla relies on raw SQL dumps, someone has to manually build all changes in all supported platforms. If Joomla were serious about true multi-database support, that pull request wouldn't be accepted until all databases were updated. Same with #8563. Instead, what'll probably happen is they get tested on MySQL under the premise that on a rainy day the other engines will be updated to include the same optimizations, and those changes will never come.

Probably a good time to reconsider the decision to go with Joomla when you can't rely on them.

Dude, if this is the straw that broke the camel's back, you're a better person than a lot of folks on these lists.

@mbabker : Highly respect your contributions, normally of a high quality. Imagine my surprise to see this PR from your hand. My assessment was that you were trying to make a political statement, which is why I didn't react at first. Sympathize with your frustration on how things are going but disagree on punishing webmasters even more for having chosen to use Joomla on PGSQL or MSSQL. No need to make their work more difficult than it already is.

That doesn't mean I object to removing stuff from the distribution. As a matter of fact, the J3.5 was supposed to be a lighter distribution with core supported extensions. Was looking forward to a lighter core with only "components" I need installed and the opportunity to install additional stuff.

Contrary to the plans published, I want to remove all databases from core and only pick the ones you want. Also no templates and pick the ones you want. Same for Search, Redirect, Content and Account management.
All "components" should have their own repository so people could contribute to what has their interest. Hopefully resulting in a better turnaround for PR's. Including the ones you mentioned, considering it would be the responsibility for them to keep up and implement what is required.
Unfortunately this idea was not properly facilitated by PLT, in my opinion.

Considering I am not a native English/American, I am not sure I understand what you are saying with your last sentence.

https://en.wikipedia.org/wiki/Straw_that_broke_the_camel%27s_back explains the saying.

@Bakual Please reread my post. The number list is a reinforcement to reasoning why the PR is a good idea rather than a bad idea. I in no way in my post did I insinuate @alkon is suggesting a third party DBAL, that is my own analysis that we have better options on the table. Options to meet the expectations needed without the overhead of what is currently there as for better long-term goals.

Setting NeedsReview so maintainers can make a decision (or not)

Setting NeedsReview so maintainers can make a decision (or not)

Ahh, the state where items go to be forgotten about forever. See y'all next year!

I have tested this item successfully on 0f25dc3

I agree with @mbabker: MySQL especially in latest 5.6/5.7 versions has nothing to envy to PostgreSQL.

Wordpress is MySQL only
https://codex.wordpress.org/Using_Alternative_Databases
Facebook uses MySQL in enterprise environments and many others are doing it.

There is the awesome mysqlnd driver, developed by MySQL and included in newer PHP distros, there are many improvements a sysadmin can take (MariaDB, PerconaDB ecc..) MySQL is not a lock-in, there those 2 actively developed 'forks/alternate engines' of it, it's a big open source project.

So please drop that PostgreSQL and Microsoft SQL drivers from the joomla core.

If it will be real developer interest about PostgreSQL, someone will be free to fork the current driver and make it a plugin, maybe you can fork it yourself and announce that anyone willing in the github community is free to continue the PostgreSQL driver development,
Maybe in this way there will be more development too, with a separate issue tracker, in this way you can probe the real download numbers and the real developer interest about that.

Then if a team will properly test it and take care of developments, they can put it in the JED like folks are doing for wordpress:

https://it.wordpress.org/plugins/postgresql-for-wordpress/

Regarding using a DBAL Please don't! It will mean more performance overhead and instead of getting free to use any database you'll instead get locked-in to use the DBAL and its issues.

The problem isn't really the driver. Those work fine.
The problem is more in the queries we write. Some don't even use the query builder. And obviously the plain SQL files.
So even if someone wanted to fork and make a plugin, it would be useless as the issues aren't there.

@Bakual there are queries that are written in raw SQL and doesn't use the query builder in the CMS? /play inconceivable

Even some of the ones built with the query builder are atrocious to read.
Then again, I can't think of many that natively handle CASE WHEN scenarios
with elegance.

On Monday, December 28, 2015, photodude notifications@github.com wrote:

@Bakual https://github.com/Bakual there are queries that are written in
raw SQL and doesn't use the query builder in the CMS?
/play inconceivable

—
Reply to this email directly or view it on GitHub
#8564 (comment).

Agreed, at least the queries built with the query builder should be more agnostic to the underlying database and database drivers.

Regarding using a DBAL Please don't! It will mean more performance overhead and instead of getting free to use any database you'll instead get locked-in to use the DBAL and its issues.

Joomla is already using DBAL. I think that most developers are always using DBAL in they extensions instead of raw query. Trust me, DBAL is best solution between ORM with true overhead, and raw query with hardship to provide cross-database support and dynamic query generating.

there are queries that are written in raw SQL and doesn't use the query builder in the CMS?

@photodude At least I wouldn't put my hand into a fire
I would actually be surprised that every module and plugin always uses the query builder. The component models probably do use it always.

@creativeprogramming one of the points of having a solid DBAL (database abstraction layer) build on multiple database drivers is to make it so end users are "free to use any database". As @Disaron stated the CMS already uses a DBAL. the current DBAL is designed and built in-house (JDatabase). By moving to something like Doctrine DBAL Joomla can focus the meger volunteer resources on other importaint things and gain the benefits of the larger comunity that is maintaining that DBAL.

Raw SQL means being locked into only the specific databases that the queries are written for. DBAL abstracts the queries allowing for compatibility with multiple database types (database driver dependent). ORM takes that abstraction even farther adding additional benefits.

Personally I want to see the CMS built on a solid 3rd party DBAL with the option for developers to adopt an ORM for use with building 3rd party extentions, but we'll likely have to wait for Joomla 4 to get something like that. In the mean time for the Joomla 3.x branch @HermanPeeren 's Jooctine is an interesting option.

And just for reference, it's not that Joomla's DBAL is bad or that the
non-MySQL parts don't work. For an API that isn't PDO based like most
similar libraries (but does support it), what's there does work fairly well
in terms of typical day-to-day operations. A lot of what is lacking is in
support for prepared statements, full coverage of PDO, and addon features
like schema management or migrations. For a lot of smaller projects the
Framework's Database package is my goto; it's lightweight, needs minimal
config, and generally just works.

The main issue is building and distributing a scalable and extensible
application supporting multiple database drivers. Generally it has
happened. The end result is several less than performant queries where the
only optimization is getting them to work cross-driver. Also more often
than not there have been critical failures in query assembly (mostly at
extension level code, a couple oddities have existed in the query builder)
meaning non-MySQL platforms didn't work right or at all.

Slapping in a third party DBAL does a lot for the project. But
multi-database DBAL's versus applications are different things. The latter
being where the struggle lies.

On Tuesday, December 29, 2015, photodude notifications@github.com wrote:

@creativeprogramming https://github.com/creativeprogramming one of the
points of having a solid DBAL (database abstraction layer) build on
multiple database drivers is to make it so end users are "free to use any
database". As @Disaron https://github.com/Disaron stated the CMS
already uses a DBAL. the current DBAL is designed and built in-house
(JDatabase). By moving to something like Doctrine DBAL
http://jwage.com/post/31080076112/doctrine-dbal-php-database-abstraction-layer
Joomla can focus the meger volunteer resources on other importaint things
and gain the benefits of the larger comunity that is maintaining that DBAL.

Raw SQL means being locked into only the specific databases that the
queries are written for. DBAL abstracts the queries allowing for
compatibility with multiple database types (database driver dependent). ORM
takes that abstraction even farther adding additional benefits.

Personally I want to see the CMS built on a solid 3rd party DBAL with the
option for developers to adopt an ORM for use with building 3rd party
extentions, but we'll likely have to wait for Joomla 4 to get something
like that. In the mean time for the Joomla 3.x branch @HermanPeeren
https://github.com/HermanPeeren 's Jooctine
https://github.com/jooctrine/jooctrine is an interesting option.

—
Reply to this email directly or view it on GitHub
#8564 (comment).

I like Joomla because is the right compromise between:

A) a well done programming work (I like very much its Object oreinted MVC, and even more the plugin architecture) extendible and powerful to customize or build apps -on- in it
B) a 'result-oriented' CMS made to be productive and performant

Wordpress is more B) than Joomla but surely less A) (did you ever look at its code?), Drupal is maybe more A) but surely less B)... as a Joomla user I selected it for such reason (being the best compromise) and I think many other Joomla users like it for this reason.

Please don't lose that spirt.

So I like this PullRequest because is in the B) spirit, but only after having demonstrated (with a lot of work) that you do A) very well, and that you was able to do it also on database support, but now as nobody was willing to use this feature ( i mean the stats that @mbabker gave us) it has no sense to officially keep support for it: it's just slowing down development (so this PR is a very smart PR IMO).

one of the points of having a solid DBAL (database abstraction layer) build on multiple database drivers is to make it so end users are "free to use any database"

Are we sure that the average user of a CMS picks its CMS because of this 'feature'? Does this matters? Yes for a DevOps this matters, but a CMS is not made for them, if you want to think as Joomla mainly as a framework for app-development ok, but if you want to think it for what it was, is, and IMO should still be: a CMS, to compete with Wordpress or Drupal, this point is not a priority cause it will not drive any benefit (no more users, no more market share: just some internal nerdy/dev enthusiasm).

So please let's throw away trying reaching 'perfection' in programming latest best pratices, but let's try to keep/reach a good compromise between on-field efficency and a rock solid structure (in the 'well done programming' sense)

The latter is not always driven by the first, e.g. you know that sometimes you've to do 'bad' things, to achieve real life performance/efficiency, you've to de-normalize a DB to get better performance (guys, don't blame me, you know that very well, e.g. after decades of development you still haven't defined a real relational schema! FOREGIN KEYS, ON DELETE CASCADE, ON UPDATE CASCADE where is this in Joomla?)

Don't misunderstand me, I don't mean that you did wrong to not doing this, but that you did fine cause you probably found some reasons to not doing it (or better, you found no reason to do it, a part 'theorical IT best pratices', you marked this as 'unneeded' or 'too complex to implement without any drawback' and you kept things minimalistic: the KISS rule)

So I mean please keep the KISS rule in mind, and before saying 'Doctrine DBAL is the way' just because top-notch programmers like it, teachers like it, and enterprise application developers likes it let's frist try to ask, really this CMS needs it? What's the requirement? So ask yourself those questions:

1) Why we should be 'free to use any database'?

First of all let me tell that you can achieve that simply respecting the real standard to do that (DoctrineDBAL is cool but not a standard): SQL-99!

(SQL is not evil: it's STANDARD Query language)

Yes you can achieve multi database support without any query builder, but with current homemade DBAL and simply SQL strings written in SQL-99 standard (and if an RDBMS doesn't support it well is probably an RDBMS issue)

Drupal is doing it that way, check it here: > https://www.drupal.org/node/555514

Anyway let's ignore this SQL-99 solution and let me guess some other possible answers to the question "Why we should be 'free to use any database'"?:

• You fear that MySQL open source project will die.
• You fear that MySQL sucks (I think not, but ok, if it's this the issue, let's switch to the one you find as 'best' for your case, having too unsolved doubts is not good)
• You want that other developers will say: 'oh, Joomla is well programmed' ( but site owners (big one too) will still pick wordpress as a choice, due to better performance, so money will stay on the wordpress market )

A priority for Joomla, IMO, is not to be cross-database, but to achieve an higher level of performance, so is to: optimize queries, reduce number of queries (using JOINS (using INDEXES) and using more microcode-caching to avoid to query what you already can have in-memory) priority IMO, is not to allow site owners to have fun switching to PostgreSQL (and probably see that 'it still sucks' so installing wordpress).

2) Is a DBAL like DoctrineDBAL a good way to achieve better performance?

2.1) No, You'll sure get more memory usage (due to the includes, 6MB of memory per worker (http://gonzalo123.com/2011/07/11/database-abstraction-layers-in-php-pdo-versus-dbal/) and this due tho how PHP is done is not to underestimate, opcode caching still sucks, loading includes in memory at any 'new https request' is still the main bottleneck of PHP applications, includes are not 'resident/shared' like in Java or NodeJS apps but are allocated per each worker and each worker serve 1 request per time, so this is not scalable.

2.2) You'll get more delays in query execution, result fetching etc. (guys, string concatenation/manipulation has a comptational cost, you all know that, and a query builder what does it do? I don't need to benchmark it to have an answer in my mind, anyway think that even PDO vs mysqli got overhead - little but not zero: http://we-love-php.blogspot.it/2012/07/mysql-or-mysqli-or-pdo.html, imagine the overhead of DoctrineDBAL which add things like its own type-casting (buggy too: doctrine/dbal#1224) and surely more layers..

3) Ok, you think that doing more 'awesomeness' for developers will mean automagically 'better performance' in the long term due to a more readable/maintainable code, so more involment in Joomla codebase, so ask yourself this:

By moving to something like Doctrine DBAL Joomla can focus the meger volunteer resources on other importaint things and gain the benefits of the larger comunity that is maintaining that DBAL.

is it true that switching to DoctrineDBAL means 'awesomeness' and less work for core Joomla developers?

3.1) No: lots of refactoring needed
3.2) No: learning curve
3.3) No: keep in sync codebase, test it all at any new Doctrine release... (ok, there is cool stuff nowaday to do that automatically, but something can go wrong, or not?)
3.4) No: DoctringDBAL is not bug-free, security-risk free, and not perfect like any other piece of software in the world, so you'll lock-in it, and probably I'll have to wait their fix for issues to move forward when you face one (https://github.com/doctrine/dbal/issues/). Joomla shouldn't be a 'slave' but a 'master', IMO.
3.4.1)

Raw SQL means being locked into only the specific databases that the queries are written for

Raw 'SQL-99' doesn't means that, and is simple to achieve, what you get solving the 'SQL dialect issue' using a DBAL is overkilling, especially if that DBAL, like i think DoctrineDBAL is doing internally isn't using 'SQL dialects' to optimize queries (e.g. like Hibernate ORM) but is doing some manipulation using it's own dialect DQL and the query builder to get what ant the end of this computational trip? an SQL-99 string, optimized not so well like an human can do after profiling.

So I repeat my point of view: Using a DBAL means locked into only the specific DBAL query builder syntax and SQL dialects (DQL in the case of DoctrineDBAL).

Which is harder to refactor if one day you what to move out from that dependency a raw SQL string that maybe is using some dialect and you've simply to bring to SQL-XXXX standard, or to refactor a DBAL programming syntax? (e.g. consider the case that one day you may want to switch from DoctringDBAL to another new, cooler, DBAL that top-notch developers will use, for instance in 2020 or maybe next year...) that's a lock-in.

To resume:

• Using a third party DBAL is adding another layer of complexity IMO.
• Using a third party DBAL means a lot of work for core-developers (refactoring) and even for extension developers (losing also temporary some of them means big damage for Joomla community).
• SQL is not evil: 'STANDARD QUERY LANGUAGE': why achieving 'database abstraction' with it is not possible? Maybe it's SQL haten by many developers, but is still the way: DBAL are good for non-query addons like schema managment etc, but regarding what it really matters on day by day CMS performance (lot of queries) are very less mature and less powerful of raw SQL, in fact even the DBAL allow SQL dialects to build queries, so that pratice should be not proibithed (QueryBuilder is not the only way to do fine, shouldn't be mandatory, in some cases is really hard to optimize a query written with a query builder, and in some cases is really hard to achieve some standard complex queries that are good for performance with a query builder (e.g. group concat, subqueries and things like so)

Don't remove the actual Joomla lightweight DBAL but if you want to temporary drop 'support' (I mean 'support' as issue caring, testing, ensuring that is all fine with any database) for other databases I agree: supporting multiple dbs is not a priority for Joomla, why keeping granting this support if everybody (except 10 guys) is using just MySQL? Just to do a programming show or to help the project to grow? Let core-developers focus on more important matters pls.

@creativeprogramming If you like this PR test it. Testing makes up a big part of PR acceptance.

Are we sure that the average user of a CMS picks its CMS because of this 'feature'?

We don't know. Since there is zero market research by Joomla into its userbase, nobody knows what features people want in Joomla or what they're using it for. Basically what it ships with now is because someone thought it was a good idea, wrote the code for it, and got it pushed into core through the contribution process. We don't know if IT departments for large companies are picking Joomla for their intranets because of their ability to (somewhat easily) hook into their existing Microsoft Enterprise environment (LDAP with Active Directory, SQL Server access, etc.). We don't know if Joe Schmoe is just looking for a blogging platform and has no idea what on earth PHP, MySQL, or even a database is. The only data that the project has is from those using the stats plugin as part of the 3.5 beta (some optionally installing it on older versions it appears), and all that says based on the limited collection ability is that next to nobody is beta testing on non-MySQL platforms (the raw counts as of now; 17 PostgreSQL installs and one SQL Server). Not a reliable metric to go from.

if you want to think as Joomla mainly as a framework for app-development ok, but if you want to think it for what it was, is, and IMO should still be: a CMS

Except to be a CMS of any value, it has to have a powerful framework to give developers the tools they need to work in a multitude of environments. It just so happens some folks thought said framework had value beyond the Joomla community and began on a modernizing effort and shared it to the wider PHP community. Agree or not, the framework that Joomla is built on is also usable for small to medium sized applications that go beyond the scope of a CMS. And frankly this is a good thing for Joomla; in some ways it validates the structures of different parts of the API and how well they perform at their designed functions.

So please let's throw away trying reaching 'perfection' in programming latest best pratices, but let's try to keep/reach a good compromise between on-field efficency and a rock solid structure (in the 'well done programming' sense)

To stay relevant, Joomla has to continue evolving. Joomla stopped evolving after 1.5's release. Joomla 3.5 largely resembles PHP of 2008; HTML producing frontend web applications that are mostly thrown together without any rhyme nor reason or anything to validate the behaviors actually work (look how much of the full application is truly covered by any sort of automated test that is validated on EVERY change). Modern practices forces developers to make better decisions about their code's structure if they are following them. It's not about bragging about the internal structures; it should be about continuing to improve the code structures for better stability and use.

As for the whole use of a DBAL thing. Frankly, it's a requirement. Unless you're going to tell developers to work directly with PDO or mysqli* functions. Which would definitely be more performant and knock the middle man out of all the database interactions. Just focusing on the JDatabaseDriver subclasses, an abstraction layer there allows Joomla to support PHP's ext/mysql, ext/mysqli, and pdo_mysql without a lot of messy inline switches; three different variations for connecting to a MySQL database. The database API handles the leg work for connecting to the database, executing the given query, and getting the results out. You just have to give it a valid query. In the purest form, it's simple and works. You don't even need to use a query builder to use the API, $db->setQuery('SELECT id FROM jos_content')->loadResult(); works just fine. As a side effect of a well defined abstraction layer, additional options can be plugged into it for use. Joomla's database API supports connections for SQLite3 on PDO, Oracle on PDO, PHP's ext/pgsql PostgreSQL connection, PHP's ext/sqlsrv SQL Server connection, and the base PDO class knows how to assemble the connection DSN for a dozen different database types. This flexibility is great for developers who have niche use cases that need Joomla to connect to these environments. Joomla as a CMS doesn't have to support all of them in the long run, nor should it. The same speech applies to the query builder portion of the existing API too.

Joomla's DBAL, as pointed out, is lightweight and it works for the most part for the most used operations in day-to-day business. This is good, but it is not enough. It's terrible that Joomla's sample data is someone taking a SQL dump of whatever is in their database and throwing it in a .sql file. There are much better modern alternatives to managing raw SQL dumps for sample data sets. The same goes for managing the application's schema in general. And schema migrations. Doctrine is one library that's addressed all this. The database package from Laravel is just as easily another option. Frankly, if there is a third party library that has addressed everything Joomla is lacking in without major sacrifice (yes, performance needs to be considered), I'd argue that it's more harmful to the project to be wasting time implementing features that are already written in the PHP ecosystem that can be easily pulled into Joomla. Yes it means refactoring, but I would truthfully put that refactoring into the JDatabaseDriver and JDatabaseQuery classes and use the existing API as a wrapper. In part for the sake of future portability (if the need arose to move to a different library), in part to alleviate the pain that would be experienced by all devs needing to rewrite their code.

As for the whole use of a DBAL thing. Frankly, it's a requirement. Unless you're going to tell developers to work directly with PDO or mysqli* functions. Which would definitely be more performant and knock the middle man out of all the database interactions. Just focusing on the JDatabaseDriver subclasses, an abstraction layer there allows Joomla to support PHP's ext/mysql, ext/mysqli, and pdo_mysql without a lot of messy inline switches; three different variations for connecting to a MySQL database. The database API handles the leg work for connecting to the database, executing the given query, and getting the results out. You just have to give it a valid query. In the purest form, it's simple and works. You don't even need to use a query builder to use the API, $db->setQuery('SELECT id FROM jos_content')->loadResult(); works just fine

Yes I'm not 'voting' to remove the current 'lightweight DBAL' JDatabase: it's awesome (you forgot to mention the '#__table' prefix replacement, a useful thing for security), I just vote to avoid third party DBAL that are written to help 'enterprise' application developers and have lot of features not optimized for a CMS

Except to be a CMS of any value, it has to have a powerful framework to give developers the tools they need to work in a multitude of environments. It just so happens some folks thought said framework had value beyond the Joomla community and began on a modernizing effort and shared it to the wider PHP community. Agree or not, the framework that Joomla is built on is also usable for small to medium sized applications that go beyond the scope of a CMS

I agree a lot, but this project is 'https://github.com/joomla/joomla-cms', I like the idea of using Joomla because it'sa CMS powerful enough to be used - also - to develop apps, but not the idea that Joomla should aim to be the best PHP framework, but the best PHP CMS (better than Wordpress, not than Sympony).

Why you refuse to watch what your real 'competitors' are doing? Wordpress and Drupal are not switching to DoctrineDBAL... but one rely on MySQL only and the other on SQL-99, are them fool or what? Maybe you're getting too academic here, please re-read why you opted for this PR

To stay relevant, Joomla has to continue evolving. Joomla stopped evolving after 1.5's release

Let me guess that is since this date you started beeing so academic... I mean refusing interesting PRs cause of code-styling or missing tests for one of the supported db. I understand that, but I mean, if Joomla was an open source project with a bit of 'commercial' spirit, such interesting PRs will have be improved and merged by a core team member paid to do that.

Missing founds for that? I guess that if you ask to pay 1$ monthly to have the license to remove a content footer string like 'This site is powered by the free version Joomla Framework' many 'site owners' will agree to pay this to support your work and get the license.

Evolving is fine, if I should develop an application nowadays I'll go for node.js frameworks, not PHP so I want Joomla to 'evolve' as a CMS primary, not as a Framework (don't blame me, i'm just 'voting' for that, ignore me if I'm the only one).

Maybe you're getting too academic here, I just want to point out the why you opened this PR to close my OT around DBALs: #4115 and #8563: 2 example of high value performance optimizations PR refused for those principles (but really, evolving means also caring performance aspects, performance best pratices like indexing a db properly or avoiding redundand queries, not just coding style best pratices, why granting a 'teoric' cross-database support is more important than improving Joomla effective performance? Is this evolving, or, as you are getting stuck in a limbo due to too strict acceptance rules, is this 'not evolving' too?

If you think that using DoctrineDBAL will automagically solve your cross-database issues at 100% I fear that you'll be wrong, just check out their issues...

Yes it means refactoring, but I would truthfully put that refactoring into the JDatabaseDriver and JDatabaseQuery classes and use the existing API as a wrapper. In part for the sake of future portability (if the need arose to move to a different library), in part to alleviate the pain that would be experienced by all devs needing to rewrite their code.

Makes senses, but this will be a DBALAL ('Database Abstraction Layer' Abstraction Layer) or a DBAL-API if you like, don't under estimate the complexity of wrapping a third party DBAL in a way that the API will not be locked in to it (not a 1 to 1 function mapping but a complete, generic, DBAL API) and the overhead of doing that. For what advantage? Schema managment? Maybe it will be easier to develop an in-house solution for schema managment.

I just want to point out the why you opened this PR to close my OT around DBALs: #4115 and #8563

Me opening the PR has nothing to do with performance optimizations or busted code. The DBAL itself is generally OK for all drivers. What is busted is the CMS' implementation of multi-database support throughout the application. Extension level code is broken constantly because of poor database queries. Adoption of non-MySQL platforms is minimal at best. Time and effort are best spent elsewhere at this point. There's no point in trying to fake supporting something that doesn't work.

My issue specifically in #8563 is basically highlighted throughout this pull request. The community surrounding the CMS doesn't even see the value in ensuring contributions work for all supported environments. NEVER should this project accept ANY patch that hacks internal performance improvements for one database only. It gives a sign that other engines are second class citizens and that the contributors and supposed leadership aren't as concerned with those environments. One of Joomla's marketing points right now is its multi-database support. Therefore ANY change should be valid for ALL supported environments. Or quit the charade of supporting multiple database platforms and optimize the crap out of everything for MySQL. This thread has made clear that neither is going to happen; the project is going to keep pretending to be multi-database friendly while treating non-MySQL engines as second class citizens.

Schema managment? Maybe it will be easier to develop an in-house solution for schema managment.

Why? What is the benefit of Joomla writing yet another schema management class(es) in 2016 in a PHP ecosystem where those types of classes are a dime a dozen? What is the benefit to Joomla of NOT evaluating third party libraries to improve different parts of the core API (what benefit is there to keeping Joomla's DBAL over Eloquent or Doctrine; what benefit is there to Joomla's local filesystem only API over Flysystem; what benefit is there to Joomla's HTTP client over Guzzle?). Frankly it's irresponsible to not keep all options on the table. Maybe it does evaluate that there are better reasons to write/maintain code in house versus consume a third party library, but that evaluation would give explicit reasoning to support it.

+1

Wouldn't mind replacing the current Joomla schema management with that of Doctrine DBAL. From the documentation on Doctrine DBAL consider that far superior. Agree with @mbabker that you need to look for the better solutions.

Have my doubts on whether replacing the querybuilder is going to be a welcome change. Same applies to the DBAL for the DML statements. Not in favor of chaining the Joomla DBAL with Doctrine DBAL either.

Too bad this project didn't deliver on the distribution packages. A much lighter core with a possible replacement of MySQL with PgSQL or MsSQL would have been welcome. Obviously, any of the parts taken out should be available as an option to install.