Table Class Annotations

With the introduction of annotations in PHP 8.0 this library is migrating towards preferring the use of those to describe table behaviours which can alter the way SQL gets generated. This is a small example that shows a class that models the Users table in the database.

namespace Local\Test;

use Nether\Database\Meta;

#[Meta\TableClass(Name: 'Users')]
class User {

	#[Meta\TypeIntBig(Unsigned: TRUE, AutoInc: TRUE)]
	#[Meta\PrimaryKey]
	public int $ID = 0;

	#[Meta\TypeVarChar(Size: 32)]
	public string $Name = '';

}

With that class annotated as such the SQL to create that table can now be generated automatically. The library also uses these annotations to configure Verse objects to work the best as they can for the table in question.

$ netherdb sql-create Local\\Test\\User

CREATE TABLE `Users` (
        `ID` BIGINT UNSIGNED AUTO_INCREMENT,
        `Name` VARCHAR(32),
        PRIMARY KEY (`ID`) USING BTREE
)
CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci
ENGINE=InnoDB

Available Annotations

All of the annotations in Nether\Database are geared towards using named arguments. Prefix and supply the arguments you want, skip the arguments that the defaults are fine for, and give zero regards about the ordering of the arguments. Thusly examples here always prefixing arguments using their name prefix. Most arguments if omitted will default to something "Actually Reasonable, Probably" depending on what database driver is being used.

Class Annotations

TableClass

This is the primary annotation that allows the system to work, telling what table this class models.

#[Nether\Database\Meta\TableClass(Name: 'Users')]
class User {

Required Arguments:

• string Name

Optional Arguments:

• string Charset
• string Collate
• string Engine
• string Comment

InsertIgnore

Nobody will admit in public that this is a good idea but if it is needed to ignore INSERT errors for whatever reason, this annotation will do that. Example: On MySQL this is done by converting INSERT into INSERT IGNORE.

#[Nether\Database\Meta\TableClass(Name: 'Users')]
#[Nether\Database\Meta\InsertIgnore]
class User {

InsertUpdate

If a table has a UNIQUE index and you try to INSERT a new row that collides with that index the query will fail because of the duplication protection doing its job. This attribute will then perform an UPDATE on that old row with whatever additional data was provided to the INSERT rather than return failure. Example: On MySQL this is done by appending a ON DUPLICATE KEY UPDATE (<ProvidedFieldData>) to the end of INSERT queries.

Requirements

• A table with a UNIQUE index.

#[Nether\Database\Meta\TableClass(Name: 'Users')]
#[Nether\Database\Meta\InsertUpdate]
class User {

InsertReuseUnique

If a table has a UNIQUE index and you try to INSERT a new row that collides with that index the query will fail because of the duplication protection doing its job. If you choose to believe that is not an actual error case, this attribute will allow the query to pass without error.

Additionally, when you INSERT new rows you always get back the new row that was inserted. In the case of UNIQUE collisions though the database does not return the last insert ID as nothing was inserted. This attribute insures that the row that already exists will be returned, ergo reused, in the case that an INSERT "fails" for this reason.

Example: On MySQL this is done by appending ON DUPLICATE KEY UPDATE <PK>=LAST_INSERT_ID(<PK>) to the end of INSERT queries which allows LAST_INSERT_ID() to do its job of fetching the pre-existing row.

Requirements

• A table with a UNIQUE index.
• A field with the PrimaryKey attribute, or
• $Verse->PrimaryKey('FieldName')

#[Nether\Database\Meta\TableClass(Name: 'Users')]
#[Nether\Database\Meta\InsertReuseUnique]
class User {

MultiFieldIndex

Property Annotations

PrimaryKey

ForeignKey

FieldIndex

TypeInt

Also: TypeIntTiny, TypeIntSmall, TypeIntMedium, TypeIntBig

TypeChar

Also: TypeVarChar

TypeText