[RC] Support of SQLite 3.0+

[Boris Berdichevski]

http://www.borisba.com/photos/phpbb/mod ... .0-3.0.zip
Please copy phpbb 3.0.12 original + add/overwrite files from the ZIP. Then you can install a new your forum.

[Boris Berdichevski]

After publishing phpBB 3.1.0 the MOD will be not actually. The version supports SQLite 3 already.

[Boris Berdichevski]

Here is an Ascraeus!
It supports SQLite 3.0, you don't need my MOD yet!
But if you want to convert your DB from 3.0.12 to 3.1, you must for addition follow changes:

phpbb/db/migration/data/v310/rc5.php

Find

Code: Select all

'\phpbb\db\migration\data\v310\remove_acp_styles_cache',

Add after

Code: Select all

'\phpbb\db\migration\data\v310\user_reminded_time',

Add a new file \phpbb\db\migration\data\v310\user_reminded_time.php

Code: Select all

<?php
/**
*
* This file is part of the phpBB Forum Software package.
*
* @copyright (c) phpBB Limited <https://www.phpbb.com>
* @license GNU General Public License, version 2 (GPL-2.0)
*
* For full copyright and license information, please see
* the docs/CREDITS.txt file.
*
*/

namespace phpbb\db\migration\data\v310;

class user_reminded_time extends \phpbb\db\migration\migration
{
	static public function depends_on()
	{
		return array('\phpbb\db\migration\data\v30x\release_3_0_11');
	}

	public function update_schema()
	{
		return array(
			'fill_columns'	=> array(
				$this->table_prefix . 'users'	=> array(
					'user_reminded_time'		=> array('UINT', 0),
				),
			),
		);
	}

	public function revert_schema()
	{
		return array(
			'fill_columns'	=> array(
				$this->table_prefix . 'users'	=> array(
					'user_reminded_time'		=> array('UINT', 0),
				),
			),
		);
	}
}

phpbb\db\migration\helper.php

Find

Code: Select all

'add_index'			=> 2,

Add after

Code: Select all

'fill_columns'		=> 2,

phpbb\db\tools.php

Find

Code: Select all

// Change columns?

Insert before

Code: Select all

		// Fill columns?
		if (!empty($schema_changes['fill_columns']))
		{
			foreach ($schema_changes['fill_columns'] as $table => $columns)
			{
				foreach ($columns as $column_name => $column_data)
				{
					// If the column exists we change it, else we add it ;)
					if ($column_exists = $this->sql_column_exists($table, $column_name))
					{
						$result = $this->sql_column_fill($table, $column_name, $column_data, true);
					}
				}
			}
		}

Find

Code: Select all

	/**
	* Change column type (not name!)
	*/

Insert before

Code: Select all

	/**
	* Fill column
	*/
	function sql_column_fill($table_name, $column_name, $column_data, $check_isnull)
	{
		$statements = array();
		
		if ($check_isnull)
		{
			$where = 'WHERE ' . $column_name . ' ISNULL';
		}
		else
		{
			$where = '';
		}

		switch ($this->sql_layer)
		{
			case 'sqlite':
			case 'sqlite3':
				$statements[] = 'UPDATE ' . $table_name . ' set ' . $column_name . ' = ' . $column_data[1] . ' ' . $where;
				
		}
		
		return $this->_sql_run_sql($statements);
	}

Instead all this you can run sqlite3 on server and do sql statement:

Code: Select all

UPDATE phpbb_users SET user_reminded_time = 0 WHERE user_reminded_time ISNULL;

[Boris Berdichevski]

Original driver of sqlite3 from 3.1.x has a defect. Fix is here.

[DarElik]

Original driver of sqlite3 from 3.1.x has a defect. Fix is here.

I've been meaning to reply to this for awhile now, but had been forgetting to.

That does not actually fix the problem, it only hides the error with the call to set_error_handling(0) that you put in. The original driver file has sql_query() function correct, the problem is that calls to sql_fetchfield() never have their $result freed and SQLite3 still sees the database (or table) locked until the script(s) exit.

Actual fix would be to mod the function sql_fetchfield() in [root]/phpbb/db/driver/driver.php to free the result before returning the field. Like this:

In [root]/phpbb/db/driver/driver.php
Find:

Code: Select all

			return (isset($row[$field])) ? $row[$field] : false;

Add Before:

Code: Select all

			$this->sql_freeresult($query_id);

This is also the problem with phpBB 3.0.x versions also in [root]/includes/db/dbal.php -- Other database layers may not have a problem with open queries, but SQLite3 (and possibly SQLite 2) does. IMO, it would have been good practice to make sure all open queries were closed anyway, but the standard way of doing it all along appears that everybody will free a result from sql_fetchrow() but not from sql_fetchfield() (even though sql_fetchfield() actually calls sql_fetchrow() itself), but I digress.

Another note about your fix - the call you make to $this->dbo->busyTimeout() is a connection level method. You do not have to call that in the sql_query() function. It should be called just once, right after you connect to the database.
(Aside from the fact that your for( $nc=0; $nc< $this->n_count; $nc++) loop just kept setting that busyTimeout over and over, but no waiting was ever done, it just kept looping through for $this->n_count attempts, trying the query each time).

It also doesn't need to be that large of a timeout, 5000ms is fine.

Try this:

In [root]/phpbb/db/driver/sqlite3.php
Find:

Code: Select all

var $n_msec = 250000;

Replace With:

Code: Select all

var $n_msec = 5000;

Find:

Code: Select all

			$this->db_connect_id = true;

Add After:

Code: Select all

			$this->dbo->busyTimeout($this->n_msec);

That should make it behave properly.
If you make those changes, your original sql_query() function should be just fine.

--------------------------

Other things I've found you may want to look at:

In the keyword_search() function in [root]/phpBB3/search/fulltext_native.php

Code: Select all

				case 'sqlite':
				case 'sqlite3':
					$sql_array_count['SELECT'] = ($type == 'posts') ? 'DISTINCT p.post_id' : 'DISTINCT p.topic_id';
					$sql = 'SELECT COUNT(' . (($type == 'posts') ? 'post_id' : 'topic_id') . ') as total_results
							FROM (' . $this->db->sql_build_query('SELECT', $sql_array_count) . ')';

				// no break

				default:
					$sql_array_count['SELECT'] = ($type == 'posts') ? 'COUNT(DISTINCT p.post_id) AS total_results' : 'COUNT(DISTINCT p.topic_id) AS total_results';
					$sql = (!$sql) ? $this->db->sql_build_query('SELECT', $sql_array_count) : $sql;

Does the SQLite3 case really need to be there? If I recall correctly, SQLite3 can handle COUNT(DISTINCT ...) fine. (I'll test more on this later)

Same thing in the author_search() function in the same file:

Code: Select all

					{
						if ($this->db->get_sql_layer() == 'sqlite' || $this->db->get_sql_layer() == 'sqlite3')
						{
							$sql = 'SELECT COUNT(topic_id) as total_results
								FROM (SELECT DISTINCT t.topic_id';
						}
						else
						{
							$sql = 'SELECT COUNT(DISTINCT t.topic_id) as total_results';
						}

						$sql .= ' FROM ' . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p
							WHERE $sql_author
								$sql_topic_id
								$sql_firstpost
								$post_visibility
								$sql_fora
								AND t.topic_id = p.topic_id
								$sql_time" . (($this->db->get_sql_layer() == 'sqlite' || $this->db->get_sql_layer() == 'sqlite3') ? ')' : '');
					}

Not sure you're going to need either of the sqlite3 conditional tests.

Anyway, that's my 2 cents worth.

[Boris Berdichevski]

Ok, I changed it, it works.
But I cannot found

Code: Select all

[root]/phpBB3/search/fulltext_native.php

[Boris Berdichevski]

Ok, right is

Code: Select all

[root]/phpBB3/phpbb/search/fulltext_native.php

I've deleted 'sqlie3'. But this is kernel of phpBB, need open bugtrack to change!

[DarElik]

Oops. You're right. I had a '3' after phpbb. My bad.

Yeah, it should be [root]/phpBB/search/fulltext_native.php

Sorry.