Improving the detection of auto-incrementing primary keys [done]

[nac]

The native format of nuBuilder primary keys (PKs) is a VARCHAR, which is populated using PHP's uniqid() function.
The reasons for this are explained in https://nubuilder.blogspot.com/2010/09/nubuilder-primary-keys.html
However, some of us may have to deal with pre-existing tables which use auto-incrementing integers (usually bigint) as PKs.
Happily nuBuilder will detect tables of this type (in most cases) and the correct PKs will be generated. This is achieved by the function

Code: Select all

db_is_auto_id($table, $primaryKey)

The return value is currently determined by the expression:

Code: Select all

return $row->Extra == 'auto_increment';

However, if we are using an updateable view, for example, the Extra COLUMN is empty and so this does not work.
The work around, if the developer knows that all integer values used as primary keys are auto-increment, is to use the row type and look for 'int', i.e. if it is 'int' it is 'auto_increment'.
This gives a modified return expression of:

Code: Select all

return ($row->Extra == 'auto_increment' || str_contains($row->Type, 'int'));

If we wish to preserve full conformity with the legacy code, we can introduce a new global $nuConfig variable which we will call
$nuConfigIntegerPKsAuto , which would have a default value of false. The expression then becomes

Code: Select all

return ($row->Extra == 'auto_increment' || ($nuConfigIntegerPKsAuto && str_contains($row->Type, 'int')));

so that the RHS of the expression only evaluates to true if $nuConfigIntegerPKsAuto is set to true in the configuration settings.
The modification of the function db_is_auto_id() along these lines would be most welcome.

No doubt there are other ways but this has been tested and it works.

Thanks

Neil

[steven]

Hi Neil,

Where is $nuConfigIntegerPKsAuto set?

I get this when I try to clone a record (using PHP version 8.2.12)

pks.PNG

Steven

[nac]

Hi Steven,

The variable $nuConfigIntegerPKsAuto was a suggestion that I had included in my proposal. It would need to be added to the nuBuilder code and to the table zzzzsys_config. Kevin and I had a conversation about this last at the end of November 2023. I did not realise that any action had been taken on this suggestion at all.

Neil

[kev1n]

There's no need to declare $nuConfigIntegerPKsAuto using the function below.

But for now, if you want to make use of the new pk detection, declare it in nuconfig.php.

E.g.

Code: Select all

$nuConfigIntegerPKsAuto = '1';

Updated db_is_auto_id():

Code: Select all

function db_is_auto_id($table, $primaryKey) {

	global $nuConfigIntegerPKsAuto;

      // other code here

	$row = db_fetch_object($stmt);
	return ($row->Extra == 'auto_increment' || ($nuConfigIntegerPKsAuto && str_contains($row->Type, 'int')));

}

[steven]

Hi all,

I could be wrong but I think later versions of PHP don't like unset variables.


Steven

[kev1n]

See Github update: https://github.com/nuBuilder/nuBuilder- ... e.php#L223

[kev1n]

I could be wrong but I think later versions of PHP don't like unset variables.

Correct, but not for global variables.

[steven]

kev,

That's good to know.

Thanks


Steven

[nac]

Thanks all round and to Kevin for adding this feature.
Neil