WordPress.org

Make WordPress Core

Opened 5 years ago

Closed 2 years ago

#10918 closed feature request (maybelater)

Microsoft SQL Server Support

Reported by: pmjones Owned by: westi
Milestone: Priority: normal
Severity: normal Version: 2.9
Component: Database Keywords: dev-feedback 2nd-opinion
Focuses: Cc:

Description

For a long time, many have asked for WordPress to support other database types. Of course, this causes some issues, as it is difficult to implement database abstraction within WordPress using a conventional method like PDO (because PDO lacks PHP4 support). It would also mean restructuring queries to be more generic, which would compromise the MySQL optimizations WordPress has implemented. Finally, it would be a huge undertaking and would affect a lot of the core WordPress code.

This patch solves the database abstraction request by using SQL dialect translations, and provides support for the MS-SQL dialect. Taking this approach means there are no changes to queries or function calls in the WordPress codebase. WordPress developers and plugin developers do not have to keep track of the database backend. They can write their queries for MySQL, since that's the official WordPress database, and not have to think about it after that.

Starting with the WordPress recommendation of creating a wpdb database class for the database type in question, we have created a structure for database abstraction within WordPress, with only minimal changes to the current code (more info in readme.txt). The new wpdb class and its support classes provide support for MS-SQL by translating on-the-fly from MySQL to MS-SQL. While it's likely not optimal for MS-SQL, it does not affect MySQL optimized queries at all.

The translation system supports all SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, etc. queries, whether issued by WordPress or by a plugin. It also maps column types on-the-fly and caches them as needed. Any other queries that require translations will have it readily available. This way the mapping array should not require constant maintenance with changes to the WP core schema. Likewise, any installed plugins that create tables will update the mapping array as well, which in turn will handle any translation issues for that plugin.

One corollary benefit is that others might contribute the necessary wpdb db class, schema, and translations for other popular databases using this initial MS-SQL implementation as an example. Of course, that's up to the community.

We hope the attached patches meet with approval.

Attachments (3)

wp-abstract.tar.gz (40.8 KB) - added by pmjones 5 years ago.
10918.diff (65.8 KB) - added by westi 5 years ago.
diff file of changes
10918-2.patch (251.8 KB) - added by azaozz 5 years ago.

Download all attachments as: .zip

Change History (28)

pmjones5 years ago

westi5 years ago

diff file of changes

comment:1 westi5 years ago

  • Cc westia added

attached a diff of the changes to make it easier for people to review.

comment:2 dd325 years ago

  • Keywords dev-feedback 2nd-opinion added
  • Milestone changed from Unassigned to Future Release
  • Version set to 2.9

WordPress currently allows you to replace the database in use via utilising the override file wp-content/db.php, Thats the best way to go about this for now.

It might be worth you making the patch against the 2.8 branch, as it seems to remove a lot of 2.9 changes and includes a lot of spacing changes which makes it a bit harder to follow some of the smaller points.

azaozz5 years ago

comment:3 azaozz5 years ago

Updated patch from pmjones and agentile.

comment:4 scottporad5 years ago

Hi...I'm Scott Porad, CTO at the Cheezburger Network. We run about 40 sites on Wordpress and would find this patch very useful. Mark us as +1 for inclusion in the next release of WP.

comment:5 follow-up: miqrogroove5 years ago

-1 What's the benefit? Who's going to support this? What happens to all the plugins?

comment:6 in reply to: ↑ 5 azaozz5 years ago

Replying to miqrogroove:

-1 What's the benefit? Who's going to support this? What happens to all the plugins?

There are (AFAIK) several large sites that use it at the moment, also see the previous comment by scottporad. It might break some old/unsupported/unpopular plugins that use MySQL directly, have you found a plugin that breaks?

comment:7 westi5 years ago

  • Cc westia removed
  • Owner changed from ryan to westi
  • Status changed from new to reviewing

Once 2.9 is out the door I am going to spend sometime reviewing this to understand the full extent of the changes and how well it will integrate into WP.

comment:8 miqrogroove5 years ago

@azaozz I just wanted to know if any planning had been done for this as an official feature. The wording of the ticket strikes me a bit like, "Let's abstract the universe." Focusing on a single storage engine is something that WordPress has done very well so far.

comment:9 Denis-de-Bernardy5 years ago

  • Milestone Future Release deleted
  • Resolution set to wontfix
  • Status changed from reviewing to closed

these might break plugins:

$query = str_replace('SELECT COUNT(*)', 'SELECT COUNT(*) as Computed', $query); 

some plugins might use select count(*) as foo, column as bar from...

if ( stripos( $query, 'IF (DATEADD(') > 0 ) { 

should be more like:

if ( preg_match('{IF\s*\(\*DATEADD\(' ) { 

and many more...

closing this as wontfix. on a php5.1 server, you can add a wp-content/db.php file, that goes as so:

class my_wpdb {	
	function __construct($wpdb) {
		self::$wpdb = $wpdb;
		if ( !isset(self::$wpdb->queries) )
			self::$wpdb->queries = array();
	} # __construct()
	
	
	function __isset($var) {
		return isset(self::$wpdb->$var);
	} # __isset()
	
	
	function __unset($var) {
		unset(self::$wpdb->$var);
	} # __unset()
	
	
	function __get($var) {
		return self::$wpdb->$var;
	} # __get()
	
	
	function __set($var, $value) {
		return self::$wpdb->$var = $value;
	} # __set()
	
	
	function __call($method, $args) {
		return call_user_func_array(array(self::$wpdb, $method), $args);
	} # __call()
}

if ( $wpdb instanceof wpdb )
	$wpdb = new query_cache($wpdb);

then redefine query(), get_var(), get_row() and get_results(). do whichever translation you want as needed. and then call parent::query(), etc. as needed.

comment:10 Denis-de-Bernardy5 years ago

was meant: $wpdb = new my_wpdb($wpdb);

but you got the point. it's pluginable already. no need for this in core.

comment:11 follow-up: azaozz5 years ago

  • Milestone set to Future Release
  • Resolution wontfix deleted
  • Status changed from closed to reopened

Please don't close tickets that are under review.

comment:12 in reply to: ↑ 11 Denis-de-Bernardy5 years ago

Replying to azaozz:

Please don't close tickets that are under review.

Fair enough, but I still think that adding this to core would be a gargantuan mistake. For starters, we should support PostgreSQL first if anything. And next, the thing already is override-able with a plugin for those who need it.

comment:13 hakre5 years ago

I'm against having this in core as well. This is plugin material and the core-code is the defition on how a plugin needs to "react" to it. Otherwise most parts of the database and data layer would need to undergo a big refactoring sothat it would be usefull. I do not see any chance for that.

comment:14 hakre5 years ago

I suggest to close this ticket as invalid. There are much more things to do before this can even thought about.

comment:15 hakre5 years ago

For a ticket being under review it does not get much feedback from it's reviewer? Any chances that the reviewer can free this ticket from the review status so it's fair enough to close as wontfix?

comment:16 follow-up: dd325 years ago

There is support being worked on for IIS and MSSQL behind the scenes slowly by certain people, Its being used on a few larger websites (Corporates and Government) AFAIK.

hakre: If a core dev deems this invalid, They'll close it as such, theres nothing wrong with have a ticket open for a couple of weeks or months whilst things pan out. Its set to future release, So theres no immediate need to kill it. It may not happen before 3.3 but that doesnt make it any less wontfix or invalid than something in 3.0

comment:17 Denis-de-Bernardy5 years ago

I stand with the idea that this belongs in a plugin.

comment:18 in reply to: ↑ 16 hakre5 years ago

Replying to dd32:

There is support being worked on for IIS and MSSQL behind the scenes slowly by certain people, Its being used on a few larger websites (Corporates and Government) AFAIK.

Well that's not much concrete and therefore does not says much. I know that at least the person who opened the ticket thought about the possibility.

hakre: If a core dev deems this invalid, They'll close it as such, theres nothing wrong with have a ticket open for a couple of weeks or months whilst things pan out. Its set to future release, So theres no immediate need to kill it. It may not happen before 3.3 but that doesnt make it any less wontfix or invalid than something in 3.0

I was asking that the reviewer is reporting feedback on the reviews status-quo. Was that too much asking? If so, I feel very sorry to actually suggested some feedback to this ticket. I know that some tickets stay open even for years, so you should name it that way: "There's nothing wrong with have a ticket open for a couple of years or months." I know you will not say yes nor not to that.

comment:19 agentile4 years ago

The intention as stated in the description is to help achieve a larger user base for those who require/want to use a different database other than mysql. WP is very optimized/focused toward mysql so we didn't want to disturb that. Database abstraction is important for the future of WP and something I believe WP wants to achieve in the future to help be a solution for a much wider user base. Yes, it will be pain staking to do this, but it is something that needs to be done. This patch was created as a possible way to achieve database abstraction within WP with minimal disturbance to core code. DB abstraction is something that should be part of the core functionality of wordpress and not pushed off as a module (as that would only make for more problems down the road). I'll be more than happy to respond to comments about this or specific patch comments. Please bear in mind that it is a work in progress and an offering to the WP devs/community as a guide/possible way to achieve DB abstraction within Wordpress, how and if they implement it into core is entirely up to them. Once again I am available to discuss it with anyone concerned and would be happy to help any devs in the process of working this into core if that is what is decided.

comment:20 sc0ttkclark3 years ago

While it may be futile, I'm going to say that this solution seems to be very comprehensive and minimally invasive for implementation within core. Am I incorrect? This seems to make a lot of sense.

Is the only thing holding something like this back the testing on every release done for even more environment combinations (with the addition of additional DB engines)? Or is there something I'm missing. If it's just a matter of additional testing, is it then just the matter of lack of man power?

comment:21 sc0ttkclark3 years ago

  • Cc sc0ttkclark@… added

comment:22 follow-up: new280063 years ago

  • Severity changed from normal to major
  • Version changed from 2.9 to 3.3

What is the advance of this point at the moment? It is great to made WP with database abstraction. Is true that WP is optimized for MySQL but if you want to have more users using WP, it is necesary to do that. I would appreciate this information, please; because I'm one of the developers that need use PostgreSql.

comment:23 in reply to: ↑ 22 westi3 years ago

Please leave the Version as the first version the ticket was reported against.
Please leave changes of Severity to core developers

comment:24 dd323 years ago

  • Severity changed from major to normal
  • Version changed from 3.3 to 2.9

WordPress currently supports (out of the box) alternate Database abstractions, however, only natively supports MySQL.

For SQL Server support, have a look at the WP DB Abstraction plugin

comment:25 nacin2 years ago

  • Milestone Future Release deleted
  • Resolution set to maybelater
  • Status changed from reopened to closed
Note: See TracTickets for help on using tickets.