Automatic Database Table creation amendment

Automatic Database Table creation/amendment

Maphper can be instructed to automatically construct database tables. This is done on the fly, you just need to tell maphper you want to use this behaviour. When you construct your Database Data source, set editmode to true:

$pdo = new PDO('mysql:dbname=maphpertest;host=127.0.0.1', 'username', 'password');
$blogSource = new \Maphper\DataSource\Database($pdo, 'blogs', 'id', ['editmode' => true]);
$blogs = new \Maphper\Maphper($blogSource); 

n.b. 'editmode => true' is shorthand for \Maphper\DataSoruce\Database::EDIT_STRUCTURE | \Maphper\DataSoruce\Database::EDIT_INDEX | \Maphper\DataSoruce\Database::EDIT_OPTIMISE;

The available flags can be interchanged using bitwise or e.g \Maphper\DataSoruce\Database::EDIT_STRUCTURE | \Maphper\DataSoruce\Database::EDIT_INDEX will enable structure and index modification but will not allow column optimisation.

The three options for editmode are:

\Maphper\DataSoruce\Database::EDIT_STRUCTURE - When this is set, Maphper automatically creates tables that don't exist, creates columns when writing to properties that don't yet exist and changes data types on out-of-bounds columns:

$blog = new stdClass;
$blog->title = 'A blog';
$blog->date = new \DateTime();
$blogs[] = $blog;

For database mappers, this will issue a CREATE TABLE statement that creates a table called blogs with the columns id INT auto_increment, title VARCHAR, date DATETIME.

Type juggling

Maphper will use the strictest possible type when creating a table. For instance:

$blog = new stdClass;
$blog->title = 1;
$blogs[] = $blog;

This would create a title column as an integer because only an integer has been stored in it. However, if another record was added to the table after it was created with a different type:

$blog = new stdClass;
$blog->title = 'Another blog';
$blogs[] = $blog;

This would issue an ALTER TABLE query and change the title colum to varchar. Similarly if a very long string was added as the title the column would be changed to LONGBLOG. This is all done on the fly and behind the scenes, as the developer you don't need to worry about the table structure at all.

Indexes

\Maphper\DataSoruce\Database::EDIT_INDEX When this is set, Maphper will automatically add indexes to columns used in WHERE, ORDER and GROUP statements. If a mutli-column where is done, a multi-column index is also added.

Database optimisation

\Maphper\DataSoruce\Database::EDIT_OPTIMISE when this is set, Maphper automatically periodically optimises database tables. For example, a column set to VARCHAR(255) where the longest entry is 7 characters will be changed to VARCHAR(7) or a VARCHAR(255) column that has 3 records with values 1,2,3 will be converted to INT(11). This will also automatically delete any columns that have NULL in every record.

Currently optimisation happens once every 500 times the DataSource is created. In future versions this value will be configurable.