SCRUD

Abstract class BlackFox\SCRUD (Select|Search, Create, Read, Update, Delete) provides functionality for working with tables in a relational database.

To create a table, you must:

Create a class descendant of BlackFox\SCRUD
Describe the table fields in the $fields property
Add it to the Scheme of the Core and run the Upgrade method

Properties

Name	Description
$name	name of the table or its elements in the plural form
$code *	symbolic code of the table for use in the database
$fields	structure of table fields
$groups	fields groups dictionary, key — code, value — name
$keys *	codes of primary keys
$composition *	composition of groups and fields
$increment *	code of the auto-incrementing field

* generated automatically

By default, the symbolic code for the table in the database is formed in lowercase in the format "Namespace_Class". If desired, you can override it in the $code property.

Table structure

Property $fields is a multidimensional dictionary that describes the structure of the table fields, the keys of which are field codes, and the values are dictionaries describing the type of the field.

Keys in the dictionary that describes the field type

Name		Description	Types
TYPE	string	Symbolic code of the type, list of available types
NAME	string	Name
GROUP	string	Group
NOT_NULL	flag	Prohibit null value
INDEX	flag	Index field
PRIMARY	flag	Primary key (multiple possible)
AUTO_INCREMENT	flag	Incremental field (only one possible)
DISABLED	flag	Disable the ability to change the field in the admin section
VITAL	flag	Mark field as important, selected by default via '@'
VALUES	dictionary	Possible values	SET, ENUM
LINK	string	Link to another table	OUTER, INNER
FOREIGN	string	Mode of foreign key	OUTER
...		Other properties depending on type

$fields = [
	'ID'      => self::ID,
	'MOMENT'  => [
		'TYPE'     => 'DATETIME',
		'NAME'     => 'Moment',
		'NOT_NULL' => true,
		'DISABLED' => true,
	],
	'MESSAGE' => [
		'TYPE'  => 'TEXT',
		'NAME'  => 'Message',
		'VITAL' => true,
	],
	'AUTHOR'  => [
		'TYPE'     => 'OUTER',
		'NAME'     => 'Author',
		'LINK'     => 'BlackFox\Users',
		'NOT_NULL' => true,
		'VITAL'    => true,
	],
	'SCORE'   => [
		'TYPE'   => 'ENUM',
		'NAME'   => 'Score',
		'VALUES' => [
			'LIKE'    => 'Like',
			'DISLIKE' => 'Dislike',
		],
	],
];

Basic methods

Name	Parameters	Description
Init		Runs once in the constructor when a new instance is created after linking to the database and calculating the $code property, before running the ProvideIntegrity method. Can override properties dynamically: $fields, $code, $groups.
Create	$element	Creates a new row in the table and returns its ID.
Update	$filter, $element	Changes the values of the specified fields in rows that match the filter.
Delete	$filter	Removes rows that match the filter.
Read	$filter, $fields, $sort, $escape	Selects the first element that matches the filter.
Select	$params - dictionary with keys: SORT, FILTER, CONDITIONS, FIELDS, LIMIT, PAGE, KEY, ESCAPE, GROUP	Selects elements page by page, filtering and sorting. Returns a two-dimensional array of elements.
Search		Selects elements page by page, filtering and sorting. Performs an additional SQL query to find the total number of items that match the filter. Returns a dictionary with keys: ELEMENTS, PAGER.
GetColumn	$filter, $field, $sort, $escape	Selects a column by filtering and sorting. Returns a dictionary of elements, in which: key - element identifier, value - the value of the selected element field.
GetCell	$filter, $field, $sort, $escape	Selects and returns the value of the specified field of the first element matching filter and sorting.

Usage examples

To create a new table, create a children of SCRUD and define it's fields:

<?php
// namespace Example; // we are not using namespace here to simplify examples
class Rooms extends \BlackFox\SCRUD {
	public $fields = [
		'ID'        => self::ID,
		'TITLE'     => [
			'TYPE'     => 'STRING',
			'NAME'     => 'Title',
			'NOT_NULL' => true,
		],
	];
}

If you need to calculate some properties dynamically, use method Init as wrapper:

<?php
// namespace Example; // we are not using namespace here to simplify examples
class Rooms extends \BlackFox\SCRUD {
	public function Init() {
		$this->fields = [
			'ID'    => self::ID,
			'TITLE' => [
				'TYPE'     => 'STRING',
				'NAME'     => T([
					'en' => 'Title',
					'ru' => 'Название',
				]),
				'NOT_NULL' => true,
			],
		];
	}
}

The helper class Scheme does the migration, synchronizing the structure of table columns between your php code and database:

<?php
( new \BlackFox\Scheme([Rooms::I()]) )->Synchronize();

SCRUD allows you to Create:

$ID = Rooms::I()->Create(['TITLE' => 'Room number 567']);

It allows you to Update:

Rooms::I()->Update(5, ['TITLE' => 'Room number 5']);
Rooms::I()->Update([6, 7, 8], ['TITLE' => 'Storage']);
Rooms::I()->Update(['TITLE' => 'Unused'], ['TITLE' => 'Gum']);

It allows you to Delete:

Rooms::I()->Delete(5);
Rooms::I()->Delete([6, 7, 8]);
Rooms::I()->Delete(['TITLE' => 'Gum']);

It allows you to Read:

$room = Rooms::I()->Read(5);
// $room is array: ['ID' => 5, 'TITLE' => 'Room number 5']

$room = Rooms::I()->Read(5, ['TITLE']);
// $room is array: ['TITLE' => 'Room number 5']

$room = Rooms::I()->Read([], ['*'], ['ID' => 'DESC']);
// [] means empty condition, ['*'] means all fields, the third param is sort
// so the result is gonna be the top element by ID

It allows you to Search:

// each parameter is optional
$result = Rooms::I()->Search([
	'FIELDS' => ['ID', 'TITLE'],
	'FILTER' => [
		'ID'    => [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
		'TITLE' => 'Gum',
	],
	'LIMIT'  => 10,
	'PAGE'   => 2,
]);

The result is gonna be an array with 2 keys: ELEMENTS and PAGER. ELEMENTS is an array of rooms. PAGER is an array, that can be used to build page navigation.

If you need no page navigation you can use method Select instead:

// each parameter is optional
$rooms = Rooms::I()->Select([
	'FIELDS' => ['ID', 'TITLE'],
	'FILTER' => [
		'%TITLE' => $_REQUEST['TITLE'],
	],
]);

If you need only one column you can use method GetColumn:

$result = Rooms::I()->GetColumn([], 'TITLE');

The result is gonna be associative array with keys - IDs, values - titles

Filter

A filter is an associative array (key — condition + code of the filtered field, value — value of the filter). The structure of the filter is designed so that it can be passed directly from the form to the model without changes. It is possible to filter by fields of related elements . It is possible to attach and combine the conditions using OR|AND operators.

Instead of an associative array, you can pass value (numeric, string) or list of values , in this case the filter will be treated as an identifier or a list of identifiers.

$filter = 15;
$filter = [15, 16, 17, 18, 19, 20];
$filter = [
	'MESSAGE'       => 'test',
	'>>MOMENT'      => time() - 7 * 24 * 60 * 60,
	'~AUTHOR.LOGIN' => 'uniko',
];

Fields

Fields is a mixed array. In the simple case, this is a list of selectable fields. If you need to select fields of related elements, add a key (pointing to the linking field) with a value — a list of selectable fields for the associated element.

You can specify a pseudofield:

@ — all important fields
* — all fields

$fields = ['@'];
$fields = ['*'];
$fields = [
	'ID',
	'NAME',
	'DESCRIPTION',
];

Many-to-one

The OUTER type describes the relationship "many-to-one", in other words — it is a reference to an element in an external table.
You must specify the LINK field property:

LINK — the name of the SCRUD descendant class that describes an external table with a single primary key

namespace BlackFox;
class Log extends SCRUD {
	public $fields = [
		'ID'   => self::ID,
		// ...
		'USER' => [
			'TYPE' => 'OUTER',
			'LINK' => 'Users',
			'NAME' => 'User',
		],
		// ...
	];
}

To fetch fields from a related table add a pair key / value where key — code of field, value — list of selected fields in the related table.

$logs = \BlackFox\Log::I()->Select([
	'FIELDS' => [
		'ID',
		'USER' => ['ID', 'LOGIN', 'EMAIL'],
	],
]);

To filter the fields of the linked element specify the path, separating through the point . It supports an unlimited nesting along the chain.

$logs = \BlackFox\Log::I()->Select([
	'FILTER' => [
		'USER.LOGIN' => 'Reuniko',
	],
]);

One-to-many

The INNER type describes the relationship "one-to-many", in other words — describes a reference from an external table to an element in the current one.
You must specify the properties of the LINK and INNER_KEY fields:

LINK — the name of the SCRUD descendant class that describes the table that contains the reference field to the current table
INNER_KEY — the field code in the external table that references the current table

Field of this type is virtual — it is not present in the database, but it can be selected and filtered just like the OUTERtype.

namespace BlackFox;
class Users extends SCRUD {
	public $fields = [
		'ID'     => self::ID,
		// ...
		'GROUPS' => [
			'TYPE'  => 'INNER',
			'NAME'  => 'Groups',
			'LINK'  => 'BlackFox\Users2Groups',
			'INNER_KEY' => 'USER',
		],
	];
}

Many-to-many

In fact, a 'many-to-many' relationship is a combination of 'one-to-many' and 'many-to-one' relationships.

To create such a relationship, you must create a staging table that contains:

master key (ID)
reference to the first table (type OUTER)
reference to the second table (type OUTER)

In the linked tables you must create virtual fields of type INNER, referring to the fields in staging table.

namespace BlackFox;
class Users2Groups extends SCRUD {
	public $fields = [
		'ID'    => self::ID,
		'USER'  => [
			'TYPE'    => 'OUTER',
			'LINK'    => 'Users',
			'FOREIGN' => 'CASCADE',
		],
		'GROUP' => [
			'TYPE'    => 'OUTER',
			'LINK'    => 'Groups',
			'FOREIGN' => 'CASCADE',
		],
	];
}

Foreign keys

By default, the OUTER type describes a "lazy" link, which doesn't care about the consistency of the data in the database.
To impose a constraint (foreign key) on a link, you must add to the description of the field property FOREIGN (RESTRICT, CASCADE).

Helper class Scheme will take care of checking the presence or absence of these restrictions in the database (as well as the structure of fields, primary keys and indexes) and will provide a sorted set of SQL strings to synchronize the database structure.

<?php
namespace BlackFox;
$Scheme = new Scheme([
	User::I(),
	Groups::I(),
	Users2Groups::I(),
]);
$diffs = $Scheme->Compare();
foreach ($diffs as $diff)
	Database::I()->Query($diff['SQL']);
// or:
$Scheme->Synchronize();

Ask question