Defining SQL schema with PHP

README.md

Defining SQL Schema in PHP

<?php
return [
  'tables' => [
    'posts' => [
      'columns' => [
        'id' => ['type' => 'int', 'autoIncrement' => true],
        'title' => ['type' => 'text'],
        // ...
      ],
      'primaryKey' => 'id',
    ],
    // ...
  ],
];

Why?

Preprocessing

With the schema in this format we can easily prefix table names without the magical "prefix_" placeholder and string substitution.

Automatic migrations

Declarative definition means you can just do diffs b/w configurations to migrate one schema to another without manually writing SQL migration code.

Diffing algorithm would need to be safe/conservative to retain backwards compatibility with old code:

• Never delete tables/columns
• Only increase the size of columns (varchar 50 => 255, int => bigint, text => mediumtext)

NB: The migration of data would still require imperative code...

Foreign keys

Foreign keys can be supported more declaratively and in a terser, clearer manner compared to sql.

Better defaults

We can decide better defaults that we think make sense for us. If we rely on raw SQL, we cannot rely on defaults since those may be different depending on the user's server configuration and MySQL version.

Some examples of defaults that are better for us than the current MySQL defaults:

• InnoDB over MyISAM, even for old MySQL versions.
• Non-nullable column values.
• UTF-8 charset
• etc...

Column.php

<?php
namespace Elgg\Schema;

/**
 * A facade for tersely instantiating all the different types of columns
 */
class Column {
  public static function bool(): BoolColumn {}

  public static function blob(int $size): BlobColumn {}

  public static function enum(array $options): EnumColumn {}

  public static function int(int $size): IntColumn {}

  public static function references(string $table, string $column): ReferenceColumn {}

  public static function text(int $size): TextColumn {}

  public static function timestamp(): TimestampColumn {}

  public static function varchar(int $size): VarcharColumn {}

  public function autoIncrement(int $start): IntColumn {}

  public function fulltext(): TextColumn {}

  public function index(): Column {}

  public function nullable(): Column {}

  public function unique(): Column {}
  
  public function unsigned(): IntColumn {}

  public function value($value): Column {}

}

Database.php

<?php
namespace Elgg\Schema;

class Database {
  public static function tables(array $tables): Database {}
}

Key.php

<?php
namespace Elgg\Schema;

class Key {
  public static function columns(array $columns): Key {}
  
  public static function foreign(array $columns): ForeignKey {}
  
  public static function fulltext(array $columns): Key {}
  
  public static function unique(array $columns): Key {}
}

schema.php

<?php

// Use builders to avoid typos and such
use Elgg\Schema\Column;
use Elgg\Schema\Table;
use Elgg\Schema\Database;
use Elgg\Schema\Key;

return Database::tables([
  'access_collection_membership' => Table::columns([
    'user_guid'            => Column::references('entities', 'guid'),
    'access_collection_id' => Column::references('access_collections', 'id'),
  ])->primaryKey(['user_guid', 'access_collection_id']),
  
  'access_collections' => Table::columns([
    'id'         => Column::int()->autoIncrement(3),
    'name'       => Column::text(),
    'owner_guid' => Column::references('entities', 'guid')->index(),
    'site_guid'  => Column::references('entities', 'guid')->value(0)->index(),
  ])->primaryKey('id'),
  
  'annotations' => Table::columns([
    'id'           => Column::int()->autoIncrement(1),
    'entity_guid'  => Column::references('entities', 'guid')->index(),
    'name_id'      => Column::references('metastrings', 'id')->index(),
    'value_id'     => Column::references('metastrings', 'id')->index(),
    'value_type'   => Column::enum(['integer', 'text']),
    'owner_guid'   => Column::references('entities', 'guid')->index(),
    'access_id'    => Column::references('access_collections', 'id')->index(),
    'time_created' => Column::timestamp(),
    'is_enabled'   => Column::boolean()->value(true),
  ])->primaryKey('id'),
  
  'config' => Table::columns([
    'name'      => Column::varchar(255),
    'value'     => Column::text(),
    'site_guid' => Column::references('entities', 'guid'),
  ])->primaryKey(['name', 'site_guid']),
  
  'datalists' => Table::columns([
    'name'  => Column::varchar(255),
    'value' => Column::text(),
  ])->primaryKey('name'),

  'entities' => Table::columns([
    'guid'           => Column::bigint()->unsigned()->autoIncrement(1),
    'type'           => Column::enum(['object', 'user', 'group', 'site'])->index(),
    'subtype'        => Column::references('entity_subtypes', 'id')->nullable()->value(null)->index(),
    'owner_guid'     => Column::references('entities', 'guid')->index(),
    'site_guid'      => Column::references('entities', 'guid')->index(),
    'container_guid' => Column::references('entities', 'guid')->index(),
    'access_id'      => Column::references('access_collections', 'id')->index(),
    'time_created'   => Column::timestamp()->index(),
    'time_updated'   => Column::timestamp()->index(),
    'last_action'    => Column::timestamp()->value(0),
    'is_enabled'     => Column::boolean()->value(true),
  ])->primaryKey('guid'),

  'entity_relationships' => Table::columns([
    'id'           => Column::int()->autoIncrement(1),
    'guid_one'     => Column::references('entities', 'guid')->index(),
    'relationship' => Column::varchar(50)->index(),
    'guid_two'     => Column::references('entities', 'guid')->index(),
    'time_created' => Column::timestamp(),
  ])->primaryKey('id')->keys([
    'guid_one' => Key::unique(['guid_one', 'relationship', 'guid_two']),
  ]),
  
  'entity_subtypes' => Table::columns([
    'id'      => Column::int()->autoIncrement(1),
    'type'    => Column::enum(['object', 'user', 'group', 'site']),
    'subtype' => Column::varchar(255),
    'class'   => Column::varchar(50)->value(''),
  ])->primaryKey('id')->keys([
    'type' => Key::unique(['type', 'subtype']),
  ]),
  
  'groups_entity' => Table::columns([
    'guid'        => Column::references('entities', 'guid'),
    'name'        => Column::text()->index(),
    'description' => Column::text()->index(),
  ])->primaryKey('guid')->keys([
    'name_2' => Key::fulltext(['name', 'description']),
  ]),
  
  'hmac_cache' => Table::columns([
    'hmac' => Column::varchar(255),
    'ts'   => Column::timestamp()->index(),
  ])->primaryKey('hmac'),
  
  'metadata' => Table::columns([
    'id'           => Column::int()->autoIncrement(1),
    'entity_guid'  => Column::references('entities', 'guid')->index(),
    'name_id'      => Column::references('metastrings', 'id')->index(),
    'value_id'     => Column::references('metastrings', 'id')->index(),
    'value_type'   => Column::enum(['integer', 'text']),
    'owner_guid'   => Column::references('entities', 'guid')->index(),
    'access_id'    => Column::references('access_collections', 'id')->index(),
    'time_created' => Column::timestamp(),
    'is_enabled'   => Column::boolean()->value(true),
  ])->primaryKey('id'),
  
  'metastrings' => Table::columns([
    'id'     => Column::int()->autoIncrement(1),
    'string' => Column::text()->index(),
  ])->primaryKey('id'),
  
  'objects_entity' => Table::columns([
    'guid'  => Column::references('entities', 'guid'),
    'title' => Column::text(),
    'description' => Column::text(),
  ])->primaryKey('guid')->keys([
    'title' => Key::fulltext(['title', 'description']),
  ]),
  
  'private_settings' => Table::columns([
    'id'          => Column::int()->autoIncrement(1),
    'entity_guid' => Column::references('entities', 'guid'),
    'name'        => Column::varchar(128)->index(),
    'value'       => Column::text()->index(),
  ])->primaryKey('id')->keys([
    'entity_guid' => Key::unique(['entity_guid', 'name']),
  ]),
  
  'queue' => Table::columns([
    'id'        => Column::int()->autoIncrement(1),
    'name'      => Column::varchar(255)->index(),
    'data'      => Column::blob(),
    'timestamp' => Column::timestamp(),
    'worker'    => Column::varchar(32)->nullable(),
  ])->primaryKey('id')->keys([
    'retrieve' => Key::columns(['timestamp', 'worker']),
  ]),
  
  'river' => Table::columns([
    'id'            => Column::int()->autoIncrement(1),
    'type'          => Column::varchar(8)->index(),
    'subtype'       => Column::varchar(32),
    'action_type'   => Column::varchar(32)->index(),
    'access_id'     => Column::references('access_collections', 'id')->index(),
    'view'          => Column::text(),
    'subject_guid'  => Column::references('entities', 'guid')->index(),
    'object_guid'   => Column::references('entities', 'guid')->index(),
    'target_guid'   => Column::references('entities', 'guid')->index(),
    'annotation_id' => Column::references('annotations', 'id')->index(),
    'posted'        => Column::timestamp()->index(),
    'is_enabled'    => Column::boolean()->value(true),
  ])->primaryKey('id'),
  
  'sites_entity' => Table::columns([
    'guid'        => Column::references('entities', 'guid'),
    'name'        => Column::text(),
    'description' => Column::text(),
    'url'         => Column::varchar(255)->unique(),
  ])->primaryKey('guid')->keys([
    'name' => Key::fulltext(['name', 'description', 'url']),
  ]),
  
  'system_log' => Table::columns([
    'id'                => Column::int()->autoIncrement(1),
    'object_id'         => Column::int()->index(),
    'object_class'      => Column::varchar(50)->index(),
    'object_type'       => Column::varchar(50)->index(),
    'object_subtype'    => Column::varchar(50)->index(),
    'event'             => Column::varchar(50)->index(),
    'performed_by_guid' => Column::references('entities', 'guid')->onDelete('none')->index(),
    'owner_guid'        => Column::references('entities', 'guid')->onDelete('none'),
    'access_id'         => Column::references('access_collections', 'id')->onDelete('none')->index(),
    'is_enabled'        => Column::boolean()->value(true),
    'time_created'      => Column::timestamp(),
    'ip_address'        => Column::varchar(46),
  ])->primaryKey('id'),
  
  'users_apisessions' => Table::columns([
    'id'        => Column::int()->autoIncrement(1),
    'user_guid' => Column::references('entities', 'guid'),
    'site_guid' => Column::references('entities', 'guid'),
    'token'     => Column::varchar(40)->nullable(),
    'expires'   => Column::timestamp(),
  ])->primaryKey('id')->keys([
    'user_guid' => Key::unique(['user_guid', 'site_guid']),
  ]),
  
  'users_entity' => Table::columns([
    'guid'             => Column::references('entities', 'guid'),
    'name'             => Column::text()->fulltext(),
    'username'         => Column::varchar(128)->value('')->unique(),
    'password'         => Column::varchar(32)->value('')->comment('Legacy password hashes')->index(),
    'salt'             => Column::varchar(8)->value('')->comment('Legacy password salts'),
    // 255 is recommended by PHP.net to hold future hash formats 
    'password_hash'    => Column::varchar(255)->value(''),
    'email'            => Column::text()->index(),
    'language'         => Column::varchar(6)->value(''),
    'is_banned'        => Column::boolean()->value(false),
    'is_admin'         => Column::boolean()->value(false)->index(),
    'last_action'      => Column::timestamp()->value(0)->index(),
    'prev_last_action' => Column::timestamp()->value(0),
    'last_login'       => Column::timestamp()->value(0)->index(),
    'prev_last_login'  => Column::timestamp()->value(0),
  ])->primaryKey('guid')->keys([
    'name_2'       => Key::fulltext(['name', 'username']),
  ]),
  
  'users_remember_me_cookies' => Table::columns([
    'code'      => Column::varchar(32),
    'guid'      => Column::references('entities', 'guid'),
    'timestamp' => Column::timestamp()->index(),
  ])->primaryKey('code'),
  
  'users_sessions' => Table::columns([
    'session' => Column::varchar(255),
    'ts'      => Column::timestamp()->value(0)->index(),
    'data'    => Column::blob(),
  ])->primaryKey('session'),
]);

Table.php

<?php
namespace Elgg\Schema;

class Table {
  public static function columns(array $nameToColumnConfig): Table {}
  
  public function primaryKey($config): Table {}
  
  public function keys(array $nameToKeyConfig): Table {}
}