[3.2.1 ] Migration refuses to populate a custom table

Discussion forum for Extension Writers regarding Extension Development.
Post Reply
User avatar
3Di
I've Been Banned!
Posts: 17538
Joined: Mon Apr 04, 2005 11:09 pm
Location: I'm with Ukraine 🇺🇦
Name: Marco
Contact:

[3.2.1 ] Migration refuses to populate a custom table

Post by 3Di »

Hi all, as per the title I am trying to populate a custom table created with a migration using a custom function which queries the USERS_TABLE, all in just one shot.

Result, the table (custom) is correctly created but populated as per my request.
I tried a lot of variations there, lurked here and there in the DB folder (migrations) for examples already done, I found something similar and .. here we go, the present migration depends on itself and phpBB 3.2.1.

That's a multiple steps process due to the chance we might work on a large board

Code: Select all

<?php

namespace threedi\pia\migrations;

/*
 * Creates the PIA's table and backups the User avatars's lot
 */
class m4_install_user_schema extends \phpbb\db\migration\migration
{
	public function effectively_installed()
	{
		/* If doesn't exists go ahead */
		return $this->db_tools->sql_table_exists($this->table_prefix . 'pia');
	}

	static public function depends_on()
	{
		return ['\phpbb\db\migration\data\v32x\v321',];
	}

	public function update_schema()
	{
		return [
			'add_tables'		=> [
				$this->table_prefix . 'pia'	=> [
					'COLUMNS'		=>	[
							'pia_user_id'				=> ['UINT', null, 'auto_increment'],
							'pia_user_avatar'			=> ['VCHAR', ''],
							'pia_user_avatar_type'			=> ['VCHAR:255', ''],
							'pia_user_avatar_width'			=> ['USINT', 0],
							'pia_user_avatar_height'		=> ['USINT', 0],
						],
					'PRIMARY_KEY'	=> 'pia_user_id',
				],
			],
			'add_columns'	=> [
				$this->table_prefix . 'users'	=>	[
					'pia_avatar_ucp'	=> ['BOOL', 1, 'after' => 'user_avatar_height']
				],
			],
			['custom', [[&$this, 'backup_user_avatar']]],
		];
	}

	public function backup_user_avatar($block = 0)
	{
		$group = 500;

		$sql = 'SELECT user_id, user_avatar, user_avatar_type, user_avatar_width, user_avatar_height
		FROM ' . $this->table_prefix . 'users' . '
		WHERE user_id <> ' . ANONYMOUS . '
			AND (user_type <> ' . USER_IGNORE . ')
		GROUP BY user_id';
		$result = $this->db->sql_query_limit($sql, $group, $block);

		$i = 0;
		while ($row = $this->db->sql_fetchrow($result))
		{
			$i++;

			$this->sql_query('INSERT INTO ' . $this->table_prefix . 'pia' . ' (pia_user_id, pia_avatar, pia_avatar_type, pia_avatar_width, pia_avatar_height) VALUES (' . (int) $row['user_id'] . ', ' . $row['user_avatar'] . ', ' . $row['user_avatar_type'] . ', ' . (int) $row['user_avatar_width'] . ', ' . (int) $row['user_avatar_height'] . ')');
		}
		$this->db->sql_freeresult($result);

		if ($i < $group)
		{
			return;
		}

		return $block + $group;
	}

Thanks.
🆓 Free support for our extensions also provided here: phpBB Studio
🚀 Looking for a specific feature or alternative option? We will rock you!
Please PM me only to request paid works. Thx. Buy me a coffee -> Image
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
Paul
Infrastructure Team Leader
Infrastructure Team Leader
Posts: 28619
Joined: Sat Dec 04, 2004 3:44 pm
Location: The netherlands.
Name: Paul Sohier
Contact:

Re: [3.2.1 ] Migration refuses to populate a custom table

Post by Paul »

You know you can do this with exactly one query?

See https://dev.mysql.com/doc/refman/5.7/en ... elect.html
User avatar
3Di
I've Been Banned!
Posts: 17538
Joined: Mon Apr 04, 2005 11:09 pm
Location: I'm with Ukraine 🇺🇦
Name: Marco
Contact:

Re: [3.2.1 ] Migration refuses to populate a custom table

Post by 3Di »

Yours, Paul, it's a good point. Thanks for the heads up.
And do you think that's the reason why it doesn't works?

Enhancing the code though will be at the due time, right now I am trying to get an alternative to the plan B I actually implemented to get rid of this problem. So I am not in "enpasse" so to speak.

Let's say that's not really The Point here, it doesn't matter if I use INSERT or UPDATE routines there, despite of the fact in my helper class the same approach it does nicely works:

Code: Select all

<?php
	/**
	 * Backup all user avatars
	 *
	 * @return void
	 */
	public function backup_user_avatar($start = 0)
	{
		$limit = 500;

		$sql = 'SELECT user_id, user_type, user_avatar, user_avatar_type, user_avatar_width, user_avatar_height
		FROM ' . USERS_TABLE . '
		WHERE user_id <> ' . ANONYMOUS . '
			AND (user_type <> ' . USER_IGNORE . ')
		GROUP BY user_id';
		$result = $this->db->sql_query_limit($sql, $limit, $start);

		$i = 0;
		while ($row = $this->db->sql_fetchrow($result))
		{
			$i++;

			$backup_row = [
				'pia_user_avatar'	=> $row['user_avatar'],
				'pia_avatar_type'	=> $row['user_avatar_type'],
				'pia_avatar_width'	=> (int) $row['user_avatar_width'],
				'pia_avatar_height'	=> (int) $row['user_avatar_height'],
			];

			$sql = 'UPDATE ' . USERS_TABLE . '
				SET ' . $this->db->sql_build_array('UPDATE', $backup_row) . '
				WHERE user_id = ' . (int) $row['user_id'];
			$this->db->sql_query($sql);
		}
		$this->db->sql_freeresult($result);

		if ($i < $limit)
		{
			return;
		}

		return $start + $limit;
	}

Same approach used phpBB and many validated extensions applied to the above code.
Just I am using new indexes created in the users table instead of the new DB table, it doesn't matter.

Code: Select all

	public function update_schema()
	{
		return [
			'add_columns'	=> [
				$this->table_prefix . 'users'	=>	[
					'pia_user_avatar'		=> ['VCHAR:255', '', 'after' => 'pia_avatar_ucp'],
					'pia_avatar_type'		=> ['VCHAR:255', '', 'after' => 'pia_user_avatar'],
					'pia_avatar_width'		=> ['USINT', 0, 'after' => 'pia_avatar_type'],
					'pia_avatar_height'		=> ['USINT', 0, 'after' => 'pia_avatar_width'],
				],
			],
		];
	}
I am asking why in migrations it DOES NOT works? I am missing something in the above code?
Is it a novel phpBB's BUG :?

I've literally followed the phpBB's Docs, Manuals, old and new phpBB migration files and what not.

I am wondering why there are no examples of what you are talking about in the phpBb code.
At least: no found.
🆓 Free support for our extensions also provided here: phpBB Studio
🚀 Looking for a specific feature or alternative option? We will rock you!
Please PM me only to request paid works. Thx. Buy me a coffee -> Image
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
User avatar
kasimi
Former Team Member
Posts: 4900
Joined: Sat Sep 10, 2011 7:12 pm
Location: Germany
Contact:

Re: [3.2.1 ] Migration refuses to populate a custom table

Post by kasimi »

I don't know why it isn't working, but some notes:
  • In your second post, the update_schema() method doesn't return the custom method.
  • If your backup column is in the same table, you can just do this:

    Code: Select all

    UPDATE phpbb_users
    SET pia_user_avatar = user_avatar
  • 3Di wrote: Mon Oct 23, 2017 8:21 am Just I am using new indexes created in the users table
    You are creating and populating columns, not indexes.
  • You can remove GROUP BY user_id from your SELECT query.
  • Careful when using the migration's sql_query() method. If the query fails, the method doesn't throw an exception but adds the error information to the $errors property. You should check if there's something in it.
User avatar
3Di
I've Been Banned!
Posts: 17538
Joined: Mon Apr 04, 2005 11:09 pm
Location: I'm with Ukraine 🇺🇦
Name: Marco
Contact:

Re: [3.2.1 ] Migration refuses to populate a custom table

Post by 3Di »

Thanks kasimi. Gotcha.

Still the question applies, I will try to find the reason why the custom method isn't called there.
I followed the Extension writers docs as well, seems like everything should be all right. Dunno.

i would prefere to use the custom table instead of the USERS_TABLE, for some reason.
🆓 Free support for our extensions also provided here: phpBB Studio
🚀 Looking for a specific feature or alternative option? We will rock you!
Please PM me only to request paid works. Thx. Buy me a coffee -> Image
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
User avatar
3Di
I've Been Banned!
Posts: 17538
Joined: Mon Apr 04, 2005 11:09 pm
Location: I'm with Ukraine 🇺🇦
Name: Marco
Contact:

Re: [3.2.1 ] Migration refuses to populate a custom table

Post by 3Di »

I believe I mis-read your reply kasimi, if so I am sorry.

Let me make the things a bit clear here..
kasimi wrote: Tue Oct 24, 2017 1:44 pm In your second post, the update_schema() method doesn't return the custom method.
The update_schema() in my second post is the one I am using instead of the one posted in the topic starter, hence it cannot return the custom method due to the fact isn't coded there in. The second post (codes) explains what I am using right now, a function and a migration which adds custom columns (yes, columns sorry :) ) to the USERS_TABLE. That's because the first migration in the topic starter (which I am not using) is "buggy" and I can't use my custom PIA table.
kasimi wrote: Tue Oct 24, 2017 1:44 pm Careful when using the migration's sql_query() method. If the query fails, the method doesn't throw an exception but adds the error information to the $errors property. You should check if there's something in it.
Should I add a

Code: Select all

$errn_ary = $this->db->get_sql_error_returned();
var_dump($errn_ary);

$errn_ary2 = $this->db->get_sql_error_sql();
var_dump($errn_ary2);

sort of bit somewhere in the first migration to check if there is something in it?
Learning by doing, you know.
Or better to use directly $this->db->sql_query() instead of the migration's sql_query()?

Thanks
🆓 Free support for our extensions also provided here: phpBB Studio
🚀 Looking for a specific feature or alternative option? We will rock you!
Please PM me only to request paid works. Thx. Buy me a coffee -> Image
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
User avatar
pierredu
Registered User
Posts: 1256
Joined: Thu Nov 01, 2012 8:04 am
Location: Paris (France)

Re: [3.2.1 ] Migration refuses to populate a custom table

Post by pierredu »

To debug this kind of code, I use a table (adequately named debug) in the database. Instead of displaying something on screen, I write it in the table with a timestamp.
User avatar
3Di
I've Been Banned!
Posts: 17538
Joined: Mon Apr 04, 2005 11:09 pm
Location: I'm with Ukraine 🇺🇦
Name: Marco
Contact:

Re: [3.2.1 ] Migration refuses to populate a custom table

Post by 3Di »

Thanks but it doesn't make any difference to me, debug on screen is faster IMO.

At least for my habits. :)
pierredu wrote: Wed Oct 25, 2017 7:33 am To debug this kind of code, I use a table (adequately named debug) in the database. Instead of displaying something on screen, I write it in the table with a timestamp.
To add, if the migration is not writing on an already created custom table, how could it write to another custom one?
🆓 Free support for our extensions also provided here: phpBB Studio
🚀 Looking for a specific feature or alternative option? We will rock you!
Please PM me only to request paid works. Thx. Buy me a coffee -> Image
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
User avatar
pierredu
Registered User
Posts: 1256
Joined: Thu Nov 01, 2012 8:04 am
Location: Paris (France)

Re: [3.2.1 ] Migration refuses to populate a custom table

Post by pierredu »

For migration files, for instance, it lets you know what really happened and in which order, because where is the screen display when executing a migration file?
User avatar
3Di
I've Been Banned!
Posts: 17538
Joined: Mon Apr 04, 2005 11:09 pm
Location: I'm with Ukraine 🇺🇦
Name: Marco
Contact:

Re: [3.2.1 ] Migration refuses to populate a custom table

Post by 3Di »

pierredu wrote: Wed Oct 25, 2017 7:35 pm For migration files, for instance, it lets you know what really happened and in which order, because where is the screen display when executing a migration file?
PHP errors will be output executing migrations as well, what's not clear to me (I will investigate after I finished to amend three more extensions and I get back to this) is how to check if there are errors in the $errors property.

Pier, still the question is, related to yours.. how would it be possible to write on another custom table if already that migration doesn't do a similar job? If it does then my topic is solved.
🆓 Free support for our extensions also provided here: phpBB Studio
🚀 Looking for a specific feature or alternative option? We will rock you!
Please PM me only to request paid works. Thx. Buy me a coffee -> Image
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
User avatar
pierredu
Registered User
Posts: 1256
Joined: Thu Nov 01, 2012 8:04 am
Location: Paris (France)

Re: [3.2.1 ] Migration refuses to populate a custom table

Post by pierredu »

There are so many reasons why a job can fail. The non-accessibility of the debug table is not very high in this array.
User avatar
3Di
I've Been Banned!
Posts: 17538
Joined: Mon Apr 04, 2005 11:09 pm
Location: I'm with Ukraine 🇺🇦
Name: Marco
Contact:

Re: [3.2.1 ] Migration refuses to populate a custom table

Post by 3Di »

Okay, today I was inspired and I did review this crap, I used the update_data() method instead of the update_schema() in order to reach my goal. Whilst there I took into consideration the hints by Paul and kasimi as well. Everything's working just fine so I can go ahead with the development of the extension the way I wanted to it to be. :)

Here's the migration as it is now, for the posterity.
Please advice if you see/think it could be improved, thanks.

Code: Select all

<?php
/**
 *
 * phpBB Initial Avatars. An extension for the phpBB Forum Software package.
 *
 * @copyright (c) 2017, 3Di, 3di.space
 * @license GNU General Public License, version 2 (GPL-2.0)
 *
 */

namespace threedi\pia\migrations;

/*
 * Adds the needed PIA_TABLE and populates it with the backup of all avatars
 */
class m4_2_install_user_schema extends \phpbb\db\migration\migration
{
	public function effectively_installed()
	{
		/* If does not exists go ahead */
		return $this->db_tools->sql_table_exists($this->table_prefix . 'pia');
	}

	static public function depends_on()
	{
		return ['\threedi\pia\migrations\m4_install_user_schema'];
	}

	public function update_schema()
	{
		return [
			'add_tables'		=> [
				$this->table_prefix . 'pia'	=> [
					'COLUMNS'		=>	[
							'pia_user_id'				=> ['UINT', null, 'auto_increment'],
							'pia_user_avatar'			=> ['VCHAR', ''],
							'pia_user_avatar_type'		=> ['VCHAR:255', ''],
							'pia_user_avatar_width'		=> ['USINT', 0],
							'pia_user_avatar_height'	=> ['USINT', 0],
						],
					'PRIMARY_KEY'	=> 'pia_user_id',
				],
			],
		];
	}

	public function update_data()
	{
		return [
			['custom', [[$this, 'backup_user_avatar']]],
		];
	}

	public function backup_user_avatar()
	{
		$sql = 'INSERT INTO ' . $this->table_prefix . 'pia' . " (pia_user_id, pia_user_avatar, pia_user_avatar_type, pia_user_avatar_width, pia_user_avatar_height) SELECT user_id, user_avatar, user_avatar_type, user_avatar_width, user_avatar_height FROM " . USERS_TABLE . ' WHERE user_id <> ' . ANONYMOUS . ' AND (user_type <> ' . USER_IGNORE . ')';
		$this->db->sql_query($sql);
	}

	public function revert_schema()
	{
		return [
			'drop_tables'		=> [
				$this->table_prefix . 'pia',
			],
		];
	}
}
Thx for the interest you all.
🆓 Free support for our extensions also provided here: phpBB Studio
🚀 Looking for a specific feature or alternative option? We will rock you!
Please PM me only to request paid works. Thx. Buy me a coffee -> Image
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
Post Reply

Return to “Extension Writers Discussion”