MySQL and Depriciated Code

Do not post support requests, bug reports or feature requests. Discuss phpBB here. Non-phpBB related discussion goes in General Discussion!
Scam Warning
matholum
Registered User
Posts: 10
Joined: Mon Aug 03, 2009 9:15 am

MySQL and Depriciated Code

Post by matholum » Mon Aug 03, 2009 9:23 am

Hello everyone,

A while back, I went to a MySQL channel on IRC and got chewed out for some depreciated code, part of phpBB, that I was using. Now with phpBB3, the code is still there. The problem I am referring to is the implicit join statements that are done using commas. My question is... is this being dealt with?

I started to make a fix a while back but ended up stopping for a while. If it is however being worked on, I could either help or stop what I was doing already. There are just so many instances of this however and will be alot of work either way. This is really crucial to mod I am working on though and need to figure out which way I need to go...

If anyone knows anything, please let me know... ^_

Acyd Burn
Consultant
Consultant
Posts: 5830
Joined: Wed Dec 05, 2001 8:31 pm
Location: Behind You
Name: Meik Sievertsen

Re: MySQL and Depriciated Code

Post by Acyd Burn » Mon Aug 03, 2009 10:14 am

Could you be a bit more specific? Maybe quoting the code in question?

matholum
Registered User
Posts: 10
Joined: Mon Aug 03, 2009 9:15 am

Re: MySQL and Depriciated Code

Post by matholum » Mon Aug 03, 2009 10:38 am

There are many, many cases of this, but here is one...

Code: Select all

//file:   root/includes/functions_user.php
//lines: 319-322

$sql = 'SELECT r.post_id, p.topic_id
		FROM ' . REPORTS_TABLE . ' r, ' . POSTS_TABLE . ' p
		WHERE r.user_id = ' . $user_id . '
			AND p.post_id = r.post_id';
the part that is depreciated is the 'FROM _ x, _ y WHERE x._ = y._' this should be 'FROM _ x JOIN _ y ON x._ = y._ WHERE...'

There seems to be many opinions around on which is better, implicit or explicit... however it doesn't really matter as implicit is depreciated as of MySQL 5 I believe... and those in the MySQL community seem to be pretty adamant about it...

Acyd Burn
Consultant
Consultant
Posts: 5830
Joined: Wed Dec 05, 2001 8:31 pm
Location: Behind You
Name: Meik Sievertsen

Re: MySQL and Depriciated Code

Post by Acyd Burn » Mon Aug 03, 2009 10:45 am

Ah. Yes, we do implicit joins and i was not aware they will become deprecated. But i hope they will still be "doable", because we do not only support MySQL, but numerous DBMS - and all of them support this. ;)

User avatar
Kellanved
Former Team Member
Posts: 2635
Joined: Wed Jan 26, 2005 2:48 pm
Location: Meta-level

Re: MySQL and Depriciated Code

Post by Kellanved » Mon Aug 03, 2009 10:45 am

In all major DBMS systems (even MySQL 6) the comma and the inner join are semantically equivalent. Until recently, there was a strong performance reason for using the comma syntax.

It is true that the comma syntax is technically deprecated, but some of the DBMS supported by phpBB do not understand the newer syntax yet. We don't expect support for classic implicit joins to vanish within the lifespan of phpBB3.
Nocando is in Idontwanna county. No support via PM

Acyd Burn
Consultant
Consultant
Posts: 5830
Joined: Wed Dec 05, 2001 8:31 pm
Location: Behind You
Name: Meik Sievertsen

Re: MySQL and Depriciated Code

Post by Acyd Burn » Mon Aug 03, 2009 10:46 am

I think Henry explained it a lot better than i did. :)

matholum
Registered User
Posts: 10
Joined: Mon Aug 03, 2009 9:15 am

Re: MySQL and Depriciated Code

Post by matholum » Mon Aug 03, 2009 10:58 am

That makes sense... I knew they were the same, and that you dealt with multiple DBMSs. I didn't know however that the newer syntax wasn't completely supported. The way they taught my DB classes led you to think it was standard. What ones don't support it, do you know?

matholum
Registered User
Posts: 10
Joined: Mon Aug 03, 2009 9:15 am

Re: MySQL and Depriciated Code

Post by matholum » Mon Aug 03, 2009 12:08 pm

ANSI standard SQL specifies four types of JOINs
http://en.wikipedia.org/wiki/Join_(SQL)

This article mentions that this is the standard and that Inner is the default. It also mentions all the following in the references...

# SQL SERVER - Introduction to JOINs - Basic of JOINs
# Sybase ASE 15 Joins
# MySQL 5.0 Joins
# Oracle Joins - Quick Reference
# PostgreSQL 8.3 Joins
# Joins in Microsoft SQL Server
# Joins in MaxDB 7.6
# Joins in Oracle 11g

I think this is most of the DBMS. I looked in the features page here for phpBB and it seems the only ones not mentioned are Firebird and SQLite.

I looked up firefird and, if done correctly by only stating the relation in the on, and criterea in the where clause, it seems it has support too...
http://www.ibphoenix.com/main.nfs?a=ibp ... _semantics

I then looked up information on SQLite and found a page that mentions what is not supported. In it is says that RIGHT OUTER JOIN and FULL OUTER JOIN are not supported. This is ok however as what we want is the standard, INNER join.
http://www.sqlite.org/omitted.html

i did all this rather quick and didn't go into much detail, however it seems it is all supported. I would expect that the default type of join would be though as it is a standard. The first page mentions that it is just expressed in a different way. Another way to look at it is that if it is a standard and something doesn't support it, then it is that DBMS's problem, not yours. ^_^

So, Hopefully I am right and it should be ok to do it. I was working on a patch that would go through and find all the instances and fix them. I was just a little stuck on how to go about changing it. I think it would be well worth it to use non-depreciated code it is just a matter of changing it.

What are some of your guys' thoughts? Did I miss something?

User avatar
Kellanved
Former Team Member
Posts: 2635
Joined: Wed Jan 26, 2005 2:48 pm
Location: Meta-level

Re: MySQL and Depriciated Code

Post by Kellanved » Mon Aug 03, 2009 12:25 pm

I remember postgres prior to 7.4 having issues with explicit inner joins.( IIRC, newer postgres versions just translate explicit inner joins to the older implicit type). The reality is that the old, implicit join is the de-facto standard, which works the same for all major DBMS. In fact, most DBMS strive for SQL92 compliance, which allows both types equally.

In the future, with MSSQL removing implicit join support, we might be forced to move to the explicit syntax. However, for an application like phpBB3 with an active MODding community and a long development history, the established syntax is just much better.

The implicit outer join syntax (*=, =*), which is more likely to vanish soon, is not used by phpBB at all.

Future versions (i.e. 4.x) won't use queries in the core at all, but will completely abstract from them.
Nocando is in Idontwanna county. No support via PM

matholum
Registered User
Posts: 10
Joined: Mon Aug 03, 2009 9:15 am

Re: MySQL and Depriciated Code

Post by matholum » Mon Aug 03, 2009 12:38 pm

Oh... I didn't think of old versions. Is that really necessary? Couldn't you just require a newer version? I wouldn't think anyone would have a problem with that since it is open source and free. Usually the argument for not upgrading say, an OS is that you have to download it. As this is all internet related that isn't a problem. I know sometimes certain things don't work on newer versions of things, however phpBB will work and as a bonus more features will be supported. Then you could level the playing field and have explicit joins supported and non-depreciated code around the board.

I didn't see some of your post before I responded... It seems that this may be something looming in the future and it may not hurt to get started. How you will get around queries is beyond me though so that is the only snag. I think that most DBMS have this support and it can become the new standard for things... but yes, a standard is important.

As for the MODing community, I don't think the actual way the queries are written will cause much of an issue. most people change the table structure, not the commands. However I may be wrong...

Roberdin
Registered User
Posts: 203
Joined: Sun Nov 24, 2002 3:56 pm
Location: London, United Kingdom

Re: MySQL and Depriciated Code

Post by Roberdin » Mon Aug 03, 2009 3:30 pm

matholum wrote:Oh... I didn't think of old versions. Is that really necessary? Couldn't you just require a newer version? I wouldn't think anyone would have a problem with that since it is open source and free.
Except that most phpBB installations are hosted on shared servers, where other users of the server would not be happy if their host upgraded the database to a version which doesn't support their existing applications; i.e., users do not necessarily have control over the version installed.

Also, it would make the phpBB code more complicated because for each SQL statement involving joins, phpBB would have to check, "are we using MySQL, MS SQL, Firebird, et c.?", and use the syntax correct to the appropriate one. This would be a lot of work in terms of maintenance. There are a few examples of this at the moment -- but most SQL queries in phpBB are standard, and luckily phpBB can avoid nasty statements like

Code: Select all

	// Handle large objects differently for Oracle and MSSQL
	switch ($db->sql_layer)
	{
		case 'oracle':
			$sql = 'SELECT search_time, search_keywords
				FROM ' . SEARCH_RESULTS_TABLE . '
				WHERE dbms_lob.getlength(search_keywords) > 0
				ORDER BY search_time DESC';
		break;

		case 'mssql':
		case 'mssql_odbc':
			$sql = 'SELECT search_time, search_keywords
				FROM ' . SEARCH_RESULTS_TABLE . '
				WHERE DATALENGTH(search_keywords) > 0
				ORDER BY search_time DESC';
		break;

		default:
			$sql = 'SELECT search_time, search_keywords
				FROM ' . SEARCH_RESULTS_TABLE . '
				WHERE search_keywords <> \'\'
				ORDER BY search_time DESC';
		break;
	}
Imagine if phpBB had to do that for every query involving joins. ;)
matholum wrote:however phpBB will work and as a bonus more features will be supported. Then you could level the playing field and have explicit joins supported and non-depreciated code around the board.
For example? Don't forget, phpBB supports lots of database server types!
Roberdin

matholum
Registered User
Posts: 10
Joined: Mon Aug 03, 2009 9:15 am

Re: MySQL and Depriciated Code

Post by matholum » Tue Aug 04, 2009 4:45 am

I was under the impression that, as it is standard SQL, the JOIN statement was overall supported and would not require special code. It is the same as everything else, the same as a implicit comma join. I may be wrong about it, but what would the sense of it being a standard be if it were not?

As for the benefits and more features that I mentioned, one is the thing you mentioned with Postgre. Most DBMS add more features with newer releases and also adopt the newer standards. Part of the reason standards are important are so that you don't have to do funky code like what you had in that example. It is true though that sometime people can't control what version they may have, i didn't think of that, however I think at some point you just have to say that you are raising the bar and that certain versions are not supported. It is awesome that phpBB works on so much, but it would be like saying you want a video game to work on a ps3 all the way down to the atari, and by doing so you would have a hard time meeting the requirements and standards all the way through. I guess that what I am trying to say is that by leaving the spectrum so wide you run into problems nearly every way you go. I completely agree though, if you have to do special code for each DBMS then it is not worth it. If, as I suspect is the case, it is just a different way to write the query and all is well you just have to drop a few older versions, I think it is worth it. If they are using old versions of their server, why not use an older version of phpBB. Honestly, if they can't meet standards and are using such an old version then they probably will have other problems anyway... I understand where you are coming from though.

matholum
Registered User
Posts: 10
Joined: Mon Aug 03, 2009 9:15 am

Re: MySQL and Depriciated Code

Post by matholum » Tue Aug 04, 2009 5:18 am

It is all just food for thought though. The reason I am wondering, and the thing that lead me to it all, can be found here... http://www.phpbb.com/community/viewtopi ... &t=1723945

Acyd Burn
Consultant
Consultant
Posts: 5830
Joined: Wed Dec 05, 2001 8:31 pm
Location: Behind You
Name: Meik Sievertsen

Re: MySQL and Depriciated Code

Post by Acyd Burn » Tue Aug 04, 2009 10:37 am

matholum wrote:I was under the impression that, as it is standard SQL, the JOIN statement was overall supported and would not require special code. It is the same as everything else, the same as a implicit comma join. I may be wrong about it, but what would the sense of it being a standard be if it were not?
Even MySQL4 and MySQL5 differ in it's use of FROM syntax if more than one table is used in conjunction with Joins. ;) It is not always that easy - especially if you want to support SQLite. :/

User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Re: MySQL and Depriciated Code

Post by drathbun » Tue Aug 04, 2009 4:02 pm

In my opinion, the bottom line is that only standards purists really care. The two are functionally equivalent, and there's no issue with having code written the way it is.
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image

Post Reply

Return to “phpBB Discussion”