FLOAT and the update_schema()

Discussion forum for Extension Writers regarding Extension Development.
User avatar
3Di
Registered User
Posts: 12467
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milan (IT) Frankfurt (DE)
Name: Marco
Contact:

Re: FLOAT and the update_schema()

Post by 3Di » Wed Jan 04, 2017 10:06 pm

Very well, it works as expected.

I got rid of the service replacement simply deleting it from services.yml and purging the cache (always to be done when you edit an .yml file there).

The class I created it's still there but's not called anymore. I will delete it.

I amended the migration file this way, that's just an exerpt of the real one.

Code: Select all

namespace threedi\ext\migrations;

class m1_install_user_schema extends \phpbb\db\migration\container_aware_migration
{
	public function effectively_installed()
	{
		return $this->db_tools->sql_table_exists($this->table_prefix . 'blah');
	}

	static public function depends_on()
	{
		return array('\phpbb\db\migration\data\v31x\v314');
	}

	public function update_schema()
	{
		$float_type = array(
			'mysql_41'		=> 'decimal(10, 5)',
			'mysql_40'		=> 'decimal(10, 5)',
			'mssql'			=> '[float]',
			'mssqlnative'		=> '[float]',
			'oracle'		=> 'number(10, 5)',
			'sqlite'		=> 'decimal(10, 5)',
			'sqlite3'		=> 'decimal(10, 5)',
			'postgres'		=> 'decimal(10, 5)',
		);

		$tools = $this->container->get('dbal.tools');

		foreach ($float_type as $sql_layer => $type)
		{
			$tools->dbms_type_map[$sql_layer]['FLOAT'] = $type;
		}

		return array(
			'add_tables'		=> array(
				$this->table_prefix . 'blah'	=> array(
					'COLUMNS'		=> array(
							'id'				=> array('UINT', null, 'auto_increment'),
							'total'				=> array('FLOAT', '0'),
						),
					'PRIMARY_KEY'	=> 'id',
				),
			),
		);
	}
}


And I finally got what I was looking for, to translate
total float(10,5) NOT NULL default '0',

to a suitable DBAL datatype, here's the SQL export
total decimal(10,5) NOT NULL DEFAULT '0.00000',

and here's a cap from the DB
float_10_5.png
float_10_5.png (3.53 KiB) Viewed 835 times
.
Thanks kasimi and Javier, has been a very constructive discussion. :)
Marco.
Want to compensate me for my interest? Donate
Please PM me only to request paid works. Thx.
Extensions, Scripts, MOD porting, Update/Upgrades
My development's activity º PhpStorm's proud user

User avatar
canonknipser
Registered User
Posts: 1472
Joined: Thu Sep 08, 2011 4:16 am
Location: Germany
Name: Frank Jakobs
Contact:

Re: FLOAT and the update_schema()

Post by canonknipser » Thu Jan 05, 2017 12:22 pm

Following this topic with interest. Looking at your migration file, you use native float for mssql and fixed precision decimal for all other dbms? Depending on your use case, wouldn't it be better to use the equivalent definitions for all dbms to get the same results everywhere?
I just created a small test case, made a table with three different columns
  • decim decimal (10,5) as your defintion
  • float_all float as native mysql float type
  • float_10_5 float(10,5) as a mysql extension to float.
Then, i inserted in the same row the same value for every type:
  • 1.234567890
  • 12.34567890
  • 123.4567890
  • 1234.567890
  • 12345.67890
  • 123456.7890
Attached you find the result from a select afterwards:
screen34.jpg
screen34.jpg (13.12 KiB) Viewed 811 times
Greetings
Frank
phpbb.de support team member
English is not my native language
New arrival - Extensions and scripts for phpBB
no support via PM or mail

User avatar
3Di
Registered User
Posts: 12467
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milan (IT) Frankfurt (DE)
Name: Marco
Contact:

Re: FLOAT and the update_schema()

Post by 3Di » Fri Jan 06, 2017 12:56 am

canonknipser wrote:
Thu Jan 05, 2017 12:22 pm
... you use native float for mssql and fixed precision decimal for all other dbms?

Depending on your use case, wouldn't it be better to use the equivalent definitions for all dbms to get the same results everywhere?
Hello and thanks for the feedback, good points here, let me try to expand on this..
first I looked at the tools.php file in order to get a basic idea of what's going on there and I can say the first question to self was, why? The same as yours here.. I did a quick search and I found that..

1 - for Oracle = number is the correct numeric datatype phpBB uses, so do I.

2 - for mssqlnative/mssql = maybe it's not the correct one but I thought that if the Devs used it there should be at least a valid reason, so I followed.. instead of using the DECIMAL type that also maps to the SQL Servermoney and smallmoney types, which are specific fixed-precision decimal types that are stored in 8 and 4 bytes, respectively. FLOAT is the alias of DOUBLE there, usually preferred by those guys.

As of 3.2.x anyway the the support for mssql has been dropped in favor of mssql_odbc, so it is the support for sqllite - this on a side note.

3 - for every other DB I preferred to use Decimal because it's a floating point data type to be used where precision is the only option (monetary calcs, geographic DD coordinates, maths generally speaking), that's my use case here.

About the management of the DB data the PHP logic I got tries to do its best managing the queried results, hopefully. So, it's fine by me.

Regards.
Marco
Want to compensate me for my interest? Donate
Please PM me only to request paid works. Thx.
Extensions, Scripts, MOD porting, Update/Upgrades
My development's activity º PhpStorm's proud user

User avatar
canonknipser
Registered User
Posts: 1472
Joined: Thu Sep 08, 2011 4:16 am
Location: Germany
Name: Frank Jakobs
Contact:

Re: FLOAT and the update_schema()

Post by canonknipser » Fri Jan 06, 2017 12:18 pm

Hi Marco,
thanks for your explanations.
3Di wrote:
Fri Jan 06, 2017 12:56 am
for every other DB I preferred to use Decimal because it's a floating point data type to be used where precision is the only option (monetary calcs, geographic DD coordinates, maths generally speaking), that's my use case here.
As a clarification, "decimal" (or "numeric", which is a synonym in most sql dialects) is not a floating point data type, but a fixed point data type. You should prefer to use fixed point or integer data types whenever possible, because floating data types (float, real, double) are always "estimated". The "float(m,n)" datatype from mysql is a bit misleading i think, because it is still a floating data type but has a maximum number of digits after the decimal point.
You should not use float data types when you need exact results from calculations. Maybe you find this article helpful

For your use case of money calcs and geographic coordinates, fixed point (or even integer) should be the correct data type.
From my experience, eg. if you need a datatype for money holding different currencies, you should use integer data type and specify the amount in the lowest "coin" from the specific currency, and the exponent of 10 by which you have to divide that value to get the base value:
  • Cent for USD or EUR, with a exponent of 2 ("Divide by 10^2 = 100")
  • Milim for Tunesian Dinar with an exponent of 3 ("Divide by 10^3 = 1000")
  • Japanese Yen with a exponent of 0
For a reference, you can use the ISO 4217-List

For geographic coordinates, a decimal(8,5) or decimal(11,8) should be OK (i think you know (8,5) means "8 digits in total, 5 after decimal point", so that data type can hold values from -999.99999 to +999.99999)

For "maths generally speaking" there can't be a general rule, it depends on your specific math problems. Maybe even a string type is suitable to hold a exact value when numeric data types run out of scale ...


BTW, the document you linked for mssql(native) specifies the jdbc data type, not the sql data types, so for sql look at https://www.techonthenet.com/sql_server/datatypes.php or https://technet.microsoft.com/en-us/lib ... 10%29.aspx ;)
Greetings
Frank
phpbb.de support team member
English is not my native language
New arrival - Extensions and scripts for phpBB
no support via PM or mail

myersjj
Registered User
Posts: 13
Joined: Sat Dec 30, 2017 6:01 pm

Re: FLOAT and the update_schema()

Post by myersjj » Fri Jan 12, 2018 12:56 am

I've gotten this to work fine during a normal extension install.

However, when I try to run database tests under Travis CI, it fails with this kind of error (like it can't find the added type)...
It's trying to create the longitude field as FLOAT, but obviously no type gets inserted into this generated SQL :(

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL,
longitude NULL,
location varchar(255) NULL,
is_valid tinyint(1) ' at line 3

User avatar
3Di
Registered User
Posts: 12467
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milan (IT) Frankfurt (DE)
Name: Marco
Contact:

Re: FLOAT and the update_schema()

Post by 3Di » Fri Jan 12, 2018 2:12 am

I am not sure I can help here but please give us a link to your code (repository is better).
Want to compensate me for my interest? Donate
Please PM me only to request paid works. Thx.
Extensions, Scripts, MOD porting, Update/Upgrades
My development's activity º PhpStorm's proud user

myersjj
Registered User
Posts: 13
Joined: Sat Dec 30, 2017 6:01 pm

Re: FLOAT and the update_schema()

Post by myersjj » Fri Jan 12, 2018 2:24 am

Here's the link to my repository https://github.com/myersjj/phpbbMapUsers

Below is the relevant code from the migration file that works on my regular server but fails as noted on travis ci
I did notice travis seems to have $this->db->sql_layer = 'mysqli', not 'mysql_41'. Don't understand that...

Code: Select all

public function update_schema() {
		$float_type = array (
				'mysql_41' => 'decimal(11,8)',
				'mysql_40' => 'decimal(11,8)',
				'oracle' => 'number(11, 8)',
				'sqlite3' => 'decimal(11, 8)'
		);
		
		//$tools = $this->container->get ( 'dbal.tools' );
		$tools = $this->db_tools;
		
		foreach ( $float_type as $sql_layer => $type ) {
			$tools->dbms_type_map [$sql_layer] ['FLOAT'] = $type;
		}
		// seems like $this->db->sql_layer='mysqli' ??
		if ($this->db_sql_layer == 'mysqli') {
			$this->db_sql_layer = 'mysql_41';
		}
		
		return array (
				'add_tables' => array (
						$this->table_prefix . 'mapusers_geolocation' => array (
								'COLUMNS' => array (
										'user_id' => array (
												'UINT',
												'0'
										),
										'latitude' => array (
												'FLOAT',
												null
										),
										'longitude' => array (
												'FLOAT',
												null
										),
										'location' => array (
												'VCHAR:255',
												null 
										),
										'is_valid' => array (
												'BOOL',
												0 
										) 
								),
								'PRIMARY_KEY' => 'user_id' 
						) 
				) 
		);
	}
	
Last edited by myersjj on Fri Jan 12, 2018 2:48 am, edited 1 time in total.

User avatar
3Di
Registered User
Posts: 12467
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milan (IT) Frankfurt (DE)
Name: Marco
Contact:

Re: FLOAT and the update_schema()

Post by 3Di » Fri Jan 12, 2018 2:37 am

Please post chunks of code only within the CODE Bbcode, thank you. :)
It's kinda unreadable otherwise.
Want to compensate me for my interest? Donate
Please PM me only to request paid works. Thx.
Extensions, Scripts, MOD porting, Update/Upgrades
My development's activity º PhpStorm's proud user

User avatar
3Di
Registered User
Posts: 12467
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milan (IT) Frankfurt (DE)
Name: Marco
Contact:

Re: FLOAT and the update_schema()

Post by 3Di » Fri Jan 12, 2018 3:04 am

Did you try changing array('FLOAT', null), (both) to array('FLOAT', '0'),?

The schema is the same as for phpBB, the mysqli(i)'s doubt shouldn't apply here.
Want to compensate me for my interest? Donate
Please PM me only to request paid works. Thx.
Extensions, Scripts, MOD porting, Update/Upgrades
My development's activity º PhpStorm's proud user

myersjj
Registered User
Posts: 13
Joined: Sat Dec 30, 2017 6:01 pm

Re: FLOAT and the update_schema()

Post by myersjj » Fri Jan 12, 2018 3:09 am

Yes, I had tried that first, also tried plain 0 (no quotes). All fail the same way :(

User avatar
3Di
Registered User
Posts: 12467
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milan (IT) Frankfurt (DE)
Name: Marco
Contact:

Re: FLOAT and the update_schema()

Post by 3Di » Fri Jan 12, 2018 3:22 am

Code: Select all

public function update_schema() 
{
	$float_type = array (
		'mysql_41' => 'decimal(11,8)',
		'mysql_40' => 'decimal(11,8)',
		'oracle' => 'number(11, 8)',
		'sqlite3' => 'decimal(11, 8)'
	);
		
	//$tools = $this->container->get ( 'dbal.tools' );
	$tools = $this->db_tools;
		
	foreach ( $float_type as $sql_layer => $type )
	{
		$tools->dbms_type_map [$sql_layer] ['FLOAT'] = $type;
	}

	return array (
		'add_tables' => array(
			$this->table_prefix . 'mapusers_geolocation' => array(
				'COLUMNS' => array(
						'user_id' => array('UINT', 0),
						'latitude' => array ('FLOAT', 0),
						'longitude' => array ('FLOAT', 0),
						'location' => array ('VCHAR:255', ''),
						'is_valid' => array ('BOOL', 0), 
					),
				'PRIMARY_KEY' => 'user_id',
			),
		),
	);
}
Seems like the issue is near to NULL there, maybe some comma as well, try this..
Want to compensate me for my interest? Donate
Please PM me only to request paid works. Thx.
Extensions, Scripts, MOD porting, Update/Upgrades
My development's activity º PhpStorm's proud user

myersjj
Registered User
Posts: 13
Joined: Sat Dec 30, 2017 6:01 pm

Re: FLOAT and the update_schema()

Post by myersjj » Fri Jan 12, 2018 4:28 am

I tried that - same issue. Note that the TYPE is missing in the SQL when run under phpunit, but works just fine when extension is installed in a normal phpBB installation.

User avatar
3Di
Registered User
Posts: 12467
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milan (IT) Frankfurt (DE)
Name: Marco
Contact:

Re: FLOAT and the update_schema()

Post by 3Di » Fri Jan 12, 2018 5:27 am

A link to the Travis CI failing build?
Want to compensate me for my interest? Donate
Please PM me only to request paid works. Thx.
Extensions, Scripts, MOD porting, Update/Upgrades
My development's activity º PhpStorm's proud user


Post Reply

Return to “Extension Writers Discussion”

Who is online

Users browsing this forum: No registered users and 3 guests