phpBB2Refugees.com Logo
Not affiliated with or endorsed by the phpBB Group

Register •  Login 

Continue the legacy...

Welcome to all phpBB2 Refugees!Wave Smilie

This site is intended to continue support for the legacy 2.x line of the phpBB2 bulletin board package. If you are a fan of phpBB2, please, by all means register, post, and help us out by offering your suggestions. We are primarily a community and support network. Our secondary goal is to provide a phpBB2 MOD Author and Styles area.

[DEV] Experimental: Replace phpBB2 DBAL w/phpBB3 DBAL


 
Search this topic... | Search MOD Development... | Search Box
Register or Login to Post    Index » MOD Development  Previous TopicPrint TopicNext Topic
Author Message
Murmur
Board Member



Joined: 20 Aug 2009

Posts: 57
Location: California


flag
PostPosted: Sun Sep 20, 2009 2:25 am 
Post subject: Experimental: Replace phpBB2 DBAL w/phpBB3 DBAL

THIS IS NOT A MOD. Do not apply these instructions to a working site. This test will almost certainly break your phpBB2.

Since I like some of the functions that come with phpBB3's DBAL, I decided to see how difficult it would be to replace phpBB2's DBAL. I expected it would be prohibitively difficult and so was surprised when the index screen came up after about an hour of tinkering.

I originally took many more steps than are actually required to reach a running state, so these instructions are distilled further and surprisingly simple. I have tested these instructions on a clean phpBB2 install with encouraging result.

1) copy phpBB3's includes/db directory to phpBB2 same location.

2) Replace the contents of phpBB2's includes/db.php with the code below:
(alternately you can rename the original and create a new one with the code below. If you skip over step 4, this will allow to switch back and forth between the two DBALs relatively easily.)

Code:

<?php
/***************************************************************************
*                                 db.php
*                            -------------------
*   begin                : Saturday, Feb 13, 2001
*   copyright            : (C) 2001 The phpBB Group
*   email                : support@phpbb.com
*
*   $Id: db.php 5283 2005-10-30 15:17:14Z acydburn $
*
*   Experimental mod: additional (c) 2001-9 The phpBB Group
*   BB3 to BB2 DBAL discussion at http://www.phpbb2refugees.com/viewtopic.php?t=474
*
***************************************************************************/

/***************************************************************************
*
*   This program is free software; you can redistribute it and/or modify
*   it under the terms of the GNU General Public License as published by
*   the Free Software Foundation; either version 2 of the License, or
*   (at your option) any later version.
*
***************************************************************************/

if ( !defined('IN_PHPBB') )
{
   exit;
}

switch($dbms)
{
   case 'mysql':
      include($phpbb_root_path . 'includes/db/mysql.'.$phpEx);
      class dbal_bridge extends dbal_mysql {
       function sql_numrows($result) {
       return $this->sql_affectedrows($result);
    }}
      break;

   case 'mysqli':
      include($phpbb_root_path . 'includes/db/mysqli.'.$phpEx);
      class dbal_bridge extends dbal_mysqli {
       function sql_numrows($result) {
       return $this->sql_affectedrows($result);
    }}
      break;

   case 'postgres':
      include($phpbb_root_path . 'includes/db/postgres.'.$phpEx);
      class dbal_bridge extends dbal_postgres {
       function sql_numrows($result) {
       return $this->sql_affectedrows($result);
    }}
      break;

   case 'mssql':
      include($phpbb_root_path . 'includes/db/mssql.'.$phpEx);
      class dbal_bridge extends dbal_mssql {
       function sql_numrows($result) {
       return $this->sql_affectedrows($result);
    }}
      break;

   case 'oracle':
      include($phpbb_root_path . 'includes/db/oracle.'.$phpEx);
      class dbal_bridge extends dbal_oracle {
       function sql_numrows($result) {
       return $this->sql_affectedrows($result);
    }}
      break;

   case 'sqlite':
      include($phpbb_root_path . 'includes/db/sqlite.'.$phpEx);
      class dbal_bridge extends dbal_sqlite {
       function sql_numrows($result) {
       return $this->sql_affectedrows($result);
    }}
      break;

   case 'firebird':
      include($phpbb_root_path . 'includes/db/firebird.'.$phpEx);
      class dbal_bridge extends dbal_firebird {
       function sql_numrows($result) {
       return $this->sql_affectedrows($result);
    }}
      break;

   case 'mssql_odbc':
      include($phpbb_root_path . 'includes/db/mssql_odbc.'.$phpEx);
      class dbal_bridge extends dbal_mssql_odbc {
       function sql_numrows($result) {
       return $this->sql_affectedrows($result);
    }}
      break;
}



// Make the database connection.
$db = new dbal_bridge();         //$sql_db();
$db->sql_connect($dbhost, $dbuser, $dbpasswd, $dbname, false, false, false);
if(!$db->db_connect_id)
{
   message_die(CRITICAL_ERROR, "Could not connect to the database");
}


?>


3) change the setting in config.php for $dbms to the appropriate/valid phpBB3 db type (probably 'mysql')

4) optionally delete phpBB2 /db directory

5) load index.php. Poof! It should be running (which is not necessarily to say "working").

-----

I have taken it no further than this. ALOT of additional testing and changes probably need to be done. For example, there are some BB3 globals in the *sql.php files which BB2 does not set...
Back to top
Holger
Board Member



Joined: 19 Jan 2009

Posts: 509
Location: Hanover


flag
PostPosted: Sun Sep 20, 2009 8:44 am 
Post subject: Re: Experimental: Replace phpBB2 DBAL w/phpBB3 DBAL

What advantages does the phpBB2 DBAL have?
Back to top
Mr. Craven
Board Member



Joined: 01 Aug 2009

Posts: 4
Location: North Carolina


flag
PostPosted: Sun Sep 20, 2009 3:10 pm 
Post subject: Re: Experimental: Replace phpBB2 DBAL w/phpBB3 DBAL

What is a DBAL?
Back to top
drathbun
Board Member



Joined: 24 Jul 2008

Posts: 653
Location: Texas


flag
PostPosted: Sun Sep 20, 2009 5:10 pm 
Post subject: Re: Experimental: Replace phpBB2 DBAL w/phpBB3 DBAL

Holger wrote:
What advantages does the phpBB2 DBAL have?

I, too, would be interested in this. Were you doing this merely as an experiment to see if it would work? Or do you anticipate some sort of performance / security boost?
Mr. Craven wrote:
What is a DBAL?

Database Abstraction Layer icon_smile.gif

It's how phpBB2 (and 3) manage to talk to lots of different databases even if the syntax is different. I have a post about the DBAL if you want to read more details.

_________________
phpBBDoctor Blog
Back to top
Dog Cow
Board Member



Joined: 18 Nov 2008

Posts: 378


flag
PostPosted: Sun Sep 20, 2009 6:20 pm 
Post subject: Re: Experimental: Replace phpBB2 DBAL w/phpBB3 DBAL

Holger wrote:
What advantages does the phpBB2 DBAL have?

Take a look at the docs: http://www.phpbb.com/community/docs/coding-guidelines.html#sql

- sql_escape()
- sql_build_array()
- sql_in_set()

These three I considered important and useful enough to copy into my DBAL. icon_smile.gif

_________________
Moof!
Lincoln's Tomb, Oak Ridge Cemetery, Springfield IL Mac 512K Blog Mac GUI
Back to top
Murmur
Board Member



Joined: 20 Aug 2009

Posts: 57
Location: California


flag
PostPosted: Mon Sep 21, 2009 5:51 am 
Post subject: Re: Experimental: Replace phpBB2 DBAL w/phpBB3 DBAL

Encouraging but not perfect....

Security:
Dog Cow pointed out that BB3 enhances security when the BB3 guidelines are followed. Some of the functions are also convenient for the programmer.

Performance:
I've no comparison to make here. Unless BB3's DBAL is vastly superior to BB2's, my hunch is that BB3 is probably a bit slower. With extra checks going on and the added abstraction of some functions (array to query to execution), I'd guess slower.

Advantages:
In addition to what Dog Cow mentioned, I would add a couple:

1) presumably BB3's DBAL is more up-to-date regarding the latest versions of the supported DBs.

2) support for firebird is added (if any one cares).

3) a new DBAL could conceivably extend the life of BB2 as databases continue to evolve

Results so far:

Most screens seem to work Ok. Had to add a hack (below) to workaround a fairly major problem. Was experimenting with screens that add records rather than just update. Post a reply works, edit a post also good. Add a new topic does not. Only tried a few admin functions. Add a forum or category - OK. Update site config - good.

First Major Hurdle:

BB3 apparently completely lacks sql_numrows(). It uses sql_affectedrows instead (something to do with compatibility with different db's). Ideally, all instances of "sql_numrows" would be replaced with affectedrows in BB2, but I'm trying to avoid major changes to the code base until absolutely necessary. Try to keep the test code in as few places as possible for now. So I added a wrapper function to db.php. At least it would be a wrapper function if $db wasn't an object (not a fan of OOP). For the time being I put an ugly class extension in db.php. It does the trick for numrows.

Create New Topic still broke. I'll look at that later.

My Motivations??

I'm pursuing it as an experiment at this point. My motivation was not security or performance but rather a more complete database API than phpbb2 offers. There are those factors, of course, security and performance, as well as longevity - but API is what I had in mind. I thought to try BB3's DBAL before others (like PEAR) on the assumption that it was close kin to BB2 and might therefore require less work to port.

---
Oh. The updated db.php is up in the first post if any one wants to test it.
Back to top
Holger
Board Member



Joined: 19 Jan 2009

Posts: 509
Location: Hanover


flag
PostPosted: Mon Sep 21, 2009 6:31 am 
Post subject: Re: Experimental: Replace phpBB2 DBAL w/phpBB3 DBAL

Wow, thank you for the infos!
Back to top
Murmur
Board Member



Joined: 20 Aug 2009

Posts: 57
Location: California


flag
PostPosted: Wed Sep 23, 2009 3:55 am 
Post subject: Re: Experimental: Replace phpBB2 DBAL w/phpBB3 DBAL

The good news is porting the DBAL is certainly doable.

The bad news is that it's not a clean, drop-in replacement.

BB2 defines a constant "SQL_LAYER" which it uses through out the code base to conditionally adjust SQL syntax for the various dbs it supports. There are 21 such tweaks sprinkled through out BB2.

As a grand project to update BB2, updating the DBAL is probably essential. As a MOD for phpBB 2.0.23, it would be large and ugly with 35 or more editing changes.

Dog Cow's method of borrowing functions is probably the way to go for those people hacking their own version.

This is not a wasted effort, I hope. It can serve as a preliminary review of what to expect if we/someone decides to pursue a MOD or new release.
Back to top
ABDev
Board Member



Joined: 01 Jun 2009

Posts: 37


flag
PostPosted: Wed Sep 23, 2009 12:19 pm 
Post subject: Re: Experimental: Replace phpBB2 DBAL w/phpBB3 DBAL

More interesting : integrate the db_class class from CH, using Run Stats MOD from Ptirhiik too icon_smile.gif.
Here is two examples, to show you the advantages :

File index.php :

Code:
$sql = "SELECT c.cat_id, c.cat_title, c.cat_order
   FROM " . CATEGORIES_TABLE . " c
   ORDER BY c.cat_order";
if( !($result = $db->sql_query($sql)) )
{
   message_die(GENERAL_ERROR, 'Could not query categories list', '', __LINE__, __FILE__, $sql);
}


Will become :

Code:
$sql = 'SELECT * FROM ' . CATEGORIES_TABLE . ' ORDER BY cat_order';
$result = $db->sql_query($sql, false, __LINE__, __FILE__);


Or here, admin/admin_forums.php :

Code:
         //
         // Default permissions of public ::
         //
         $field_sql = "";
         $value_sql = "";
         while( list($field, $value) = each($forum_auth_ary) )
         {
            $field_sql .= ", $field";
            $value_sql .= ", $value";

         }

         // There is no problem having duplicate forum names so we won't check for it.
         $sql = "INSERT INTO " . FORUMS_TABLE . " (forum_id, forum_name, cat_id, forum_desc, forum_order, forum_status, prune_enable" . $field_sql . ")
            VALUES ('" . $next_id . "', '" . str_replace("\'", "''", $HTTP_POST_VARS['forumname']) . "', " . intval($HTTP_POST_VARS[POST_CAT_URL]) . ", '" . str_replace("\'", "''", $HTTP_POST_VARS['forumdesc']) . "', $next_order, " . intval($HTTP_POST_VARS['forumstatus']) . ", " . intval($HTTP_POST_VARS['prune_enable']) . $value_sql . ")";
         if( !$result = $db->sql_query($sql) )
         {
            message_die(GENERAL_ERROR, "Couldn't insert row in forums table", "", __LINE__, __FILE__, $sql);
         }


Will become :

Code:
         // init ary
         $forum_fields = array();

         // permissions
         while ( list($field, $value) = each($forum_auth_ary) )
         {
            $forum_fields += array($field => $value);
         }

         // parse data
         $prune_enable = request_var('prune_enable', TYPE_NO_HTML);

         // prepare sql query
         $forum_fields += array(
            'forum_id' => $next_id,
            'forum_name' => $forum_name,
            'cat_id' => request_var(POST_CAT_URL, TYPE_INT),
            'forum_desc' => request_var('forumdesc', TYPE_NO_HTML),
            'forum_order' => $next_order,
            'forum_status' => request_var('forum_status', TYPE_NO_HTML),
            'prune_enable' => $prune_enable,
         );

         // there is no problem having duplicate forum names, so we won't check for it
         $sql = 'INSERT INTO ' . FORUMS_TABLE . ' (' . $db->sql_fields('fields', $forum_fields) . ')
            VALUES (' . $db->sql_fields('values', $forum_fields) . ')';
         $db->sql_query($sql, false, __LINE__, __FILE__);


It's more simple after for the MODs installations icon_smile.gif.
PS : The request_var() function is from reddog, which is inspirated from the _read() function from Ptirhiik one else : http://www.reddevboard.com/forum/viewtopic.php?t=2497
PS 2 : It's not very difficult to do that, I did it on my current project.
Back to top
Dog Cow
Board Member



Joined: 18 Nov 2008

Posts: 378


flag
PostPosted: Wed Sep 23, 2009 10:55 pm 
Post subject: Re: Experimental: Replace phpBB2 DBAL w/phpBB3 DBAL

I remember that about a year ago, I tried to back-port the phpBB 3 "Explain" feature into my site, and gave up due to its complexity. I ended up using a little hack posted way back when on the phpBB Tweaks topic.

Also, most all queries in phpBB 2 would have to be changed, because you can have a custom error message written if the query fails. In phpBB 3, this is done differently.

In any case, it's a noble effort, because building queries by string concatenation really isn't the way to go if you want good security. Using binding or building queries by column (think active record) is really the best way to handle things.

Another long-term project I have for my site is to rewrite the DBAL again to be more like active record, where you make a query such as the following:

Code:

$db->select('column_1, column_2');
$db->from('table_foo');
$db->where('column_2', 'EQUALS', 'bar');
$db->execute();

I don't have all of the specifics worked out, but that's the direction I'd like to go. The DBAL would handle all the rest, such as escaping and what-not. I also need better support for connecting to multiple databases in one page load, and handling cross-db joins. There are now 5 databases behind my site, each responsible for different features.

_________________
Moof!
Lincoln's Tomb, Oak Ridge Cemetery, Springfield IL Mac 512K Blog Mac GUI
Back to top
Mrs Moo Moo
Board Member



Joined: 09 Mar 2009

Posts: 21



PostPosted: Fri Oct 02, 2009 5:19 am 
Post subject: Re: Experimental: Replace phpBB2 DBAL w/phpBB3 DBAL

actually this would be interesting.
Good luck with it, Murmur. icon_smile.gif
Back to top
Murmur
Board Member



Joined: 20 Aug 2009

Posts: 57
Location: California


flag
PostPosted: Sat Oct 03, 2009 2:24 am 
Post subject: Re: Experimental: Replace phpBB2 DBAL w/phpBB3 DBAL

Thanks, Moo. But the project is effectively on hold. But at least the scale of the thing is clearer.

It seems likely to me that such an undertaking would come in one of three forms:

1) a BIG mod to modernize the DBAL (perhaps in response to growing incompatibilities with newer versions of the supported DBs).

2) an assumption of phpbb 2.0.23 by a team devoted to carrying it into the future. This has been discussed at length in other threads. Some would say that CH fulfills this role.

3) Its incorporation into a larger CMS-type project, like Icy Phoenix.

At any rate the changes necessary are big enough to justify one of the above. I was hoping that it would be a simple affair. The big hurdle is in deciding whether 2.0.23 is to be treated like a historical artifact or the starting point for something new. If the former, 2.0.23 will die a slow death and continue on only in those other projects which have absorbed it (CH, Icy Phoenix, PHP-Nuke's bridge, etc). This would also be predictive of a dismal future for phpBB2 Refugees.

I'll post my two cents worth in the "grand future" thread linked above. Personally, I've invested so much time in my own derivative project, I'd hate to see phpBB2 wither away...
Back to top
Display posts from previous:   
Register or Login to Post    Index » MOD Development  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT
 
Jump to:  

Index • About • FAQ • Rules • Privacy • Search •  Register •  Login 
Not affiliated with or endorsed by the phpBB Group
Powered by phpBB2 © phpBB Group
Generated in 0.0141 seconds using 16 queries. (SQL 0.0014 Parse 0.0006 Other 0.0121)
phpBB Customizations by the phpBBDoctor.com
Template Design by DeLFlo and MomentsOfLight.com Moments of Light Logo