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:

  1. Create descendant class from BlackFox\SCRUD
  2. Describe the table fields in the $fields property
  3. Run the Synchronize heir method

It is recommended that you place the Synchronize method in the Upgrade method of the corresponding module. This will allow the engine to synchronize fields of all tables in the module together.

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 growing 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
Synchronize Synchronizes table fields
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.

Usage examples

To create a new table, create a children of SCRUD and define it's fields:
class Rooms extends \BlackFox\SCRUD {
    public $fields = [
        'ID'    => self::ID,
        'TITLE' => [
            'TYPE'     => 'STRING',
            'NAME'     => 'Room number',
            'NOT_NULL' => true,
        ],
    ];
}
It does all migration stuff by synchronizing table columns from your php code to database:
Rooms::I()->Synchronize();
It 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).

If the table has constraints (foreign keys), then in addition to the Synchronize method the methods DropConstraints and CreateConstraints should be placed in the Upgrade method of the corresponding module so that the call of DropConstraints follows before calling Synchronize all related tables, and the call of CreateConstraints follows after calling Synchronize all related tables.

namespace BlackFox;
class Core extends \BlackFox\ACore {
	// ...
	public function Upgrade() {
		Users2Groups::I()->DropConstraints();

		Users::I()->Synchronize();
		Groups::I()->Synchronize();
		Users2Groups::I()->Synchronize();

		Users2Groups::I()->CreateConstraints();
	}
}
Ask question