Skip to content

Instantly share code, notes, and snippets.

@designermonkey
Last active December 17, 2015 17:29
Show Gist options
  • Save designermonkey/5646377 to your computer and use it in GitHub Desktop.
Save designermonkey/5646377 to your computer and use it in GitHub Desktop.
Symphony CMS table schemas in Laravel format. I don't expect these all to be used, but they are here for a starting point.
<?php
/**
* Authors Table
*
* Removed some 'default null' statements for some columns as they will always contain values
* Renamed `user_type` to `author_type`
* Removed `enum` for types in place of `id` link to `author_types` table
*/
Schema::create('authors', function($table)
{
$table->increments('id')->unsigned();
$table->string('username', 100)->default('');
$table->string('password', 150);
$table->string('first_name', 100);
$table->string('last_name', 100);
$table->string('email', 255);
$table->datetime('last_seen')->default('0000-00-00 00:00:00');
$table->integer('author_type')->unsigned()->default(2);
$table->enum('primary', array('yes', 'no'))->default('no');
$table->string('default_area', 255)->nullable();
$table->enum('auth_token_active', array('yes', 'no'))->default('no');
$table->string('language', 15)->nullable();
$table->primary('id');
$table->unique('username');
$table->unique('email');
$table->index('author_type');
});
/**
* Author Types Table
*
* Allows for fine grained Author types
*/
Schema::create('author_types', function($table)
{
$table->increments('id')->unsigned();
$table->string('name', 255);
$table->string('handle', 255);
$table->primary('id');
$table->unique('name');
});
/**
* Forgoten Passwords Table
*
* Change 'expiry' to timestamp
*/
Schema::create('forgotpass', function($table)
{
$table->integer('author_id')->unsigned();
$table->string('token', 6);
$table->timestamp('expiry');
$table->primary('author_id');
});
<?php
/**
* Sections Table
*
* Removed a default for 'name' as it will always be set
*/
Schema::create('sections', function($table)
{
$table->increments('id')->unsigned();
$table->string('name', 255);
$table->string('handle', 255);
$table->integer('sortorder')->unsigned()->default('1');
$table->enum('hidden', array('yes', 'no'))->default('no');
$table->string('navigation_group', 255)->default('Content');
$table->primary('id');
$table->unique('handle');
});
/**
* Fields Table
*
* Changed label to name
* Changed element_name to handle
* Changed location to three columns (We may move to this in layout)
*/
Schema::create('fields', function($table)
{
$table->increments('id')->unsigned();
$table->integer('section_id')->unsigned()->default('0');
$table->string('name', 255);
$table->string('handle', 50);
$table->string('type', 100);
$table->enum('required', array('yes', 'no'))->default('yes');
$table->integer('sortorder')->unsigned()->default('1');
$table->enum('location', array('left', 'middle', 'right'))->default('left');
$table->enum('show_column', array('yes', 'no'))->default('no');
$table->primary('id');
$table->index(array('handle', 'type', 'section_id'));
});
/**
* Entries Table
*
* Removed UTC date columns as all dates should be stored as UTC
* Changed remaining date columns to Laravel style timestamps
*/
Schema::create('entries', function($table)
{
$table->increments('id')->unsigned();
$table->integer('section_id')->unsigned();
$table->integer('author_id')->unsigned();
$table->timestamps();
$table->primary('id');
$table->index('section_id');
$table->index('author_id');
});
/**
* Associations Table
*
* Renamed to just `associations` from `sections_associations`
* Removed section references as they are implied by the field IDs
* Removed some 'default null' statements for some columns as they will always contain values
*/
Schema::create('associations', function($table)
{
$table->increments('id')->unsigned();
$table->integer('parent_field_id')->unsigned();
$table->integer('child_field_id')->unsigned();
$table->enum('hide_association', array('yes', 'no'))->default('no');
$table->primary('id');
$table->unique(array('parent_field_id', 'child_field_id'));
});
<?php
/**
* Author Field Schema
*
* Changed some column names
*/
Schema::create('fields_schema_author', function($table)
{
$table->increments('id')->unsigned();
$table->integer('field_id')->unsigned();
$table->enum('allow_multiple', array('yes', 'no'))->default('no');
$table->enum('default_current_author', array('yes', 'no'))->default('no');
$table->string('author_types', 255)->default('developer, manager, author');
$table->primary('id');
$table->unique('field_id');
});
/**
* Author Field Data
*
* Multiple entries can exist for the same `entry_id`
*/
Schema::create('filds_data_author', function($table)
{
$table->increments('id')->unsigned();
$table->integer('field_id')->unsigned();
$table->integer('entry_id')->unsigned();
$table->integer('author_id')->unsigned();
$table->primary('id');
$table->index('field_id');
$table->index('entry_id');
$table->index('author_id');
});
<?php
/**
* Checkbox Field Schema
*
* Changed some column names
*/
Schema::create('fields_schema_checkbox', function($table)
{
$table->increments('id')->unsigned();
$table->integer('field_id')->unsigned();
$table->enum('default_state', array('on', 'off'))->default('on');
$table->string('description', 255)->nullable();
$table->primary('id');
$table->unique('field_id');
});
/**
* Checkbox Field Data
*
* Only a single entry can exist for the `entry_id`
*/
Schema::create('filds_data_checkbox', function($table)
{
$table->increments('id')->unsigned();
$table->integer('field_id')->unsigned();
$table->integer('entry_id')->unsigned();
$table->enum('value', array('yes', 'no')); // The default needs to be set by the schema for each field
$table->primary('id');
$table->index('field_id');
$table->unique('entry_id');
$table->index('value');
});
<?php
/**
* Date Field Schema
*
* Changed some column names
*/
Schema::create('fields_schema_date', function($table)
{
$table->increments('id')->unsigned();
$table->integer('field_id')->unsigned();
$table->enum('pre_populate', array('yes', 'no'))->default('no');
$table->primary('id');
$table->unique('field_id');
});
/**
* Date Field Data
*
* Only a single entry can exist for the `entry_id`
*/
Schema::create('filds_data_date', function($table)
{
$table->increments('id')->unsigned();
$table->integer('field_id')->unsigned();
$table->integer('entry_id')->unsigned();
$table->string('value', 100);
$table->datetime('date');
$table->primary('id');
$table->index('field_id');
$table->unique('entry_id');
$table->index('value');
$table->index('date');
});
<?php
/**
* Taglist Field Schema
*
* Changed some column names
*/
Schema::create('fields_schema_number', function($table)
{
$table->increments('id')->unsigned();
$table->integer('field_id')->unsigned();
$table->primary('id');
$table->unique('field_id');
});
/**
* Taglist Field Data
*
* Only a single entry can exist for the `entry_id`
*/
Schema::create('filds_data_number', function($table)
{
$table->increments('id')->unsigned();
$table->integer('field_id')->unsigned();
$table->integer('entry_id')->unsigned();
$table->addColumn('double', 'value', array()); // A special case as Laravel doesn't allow us to define double
$table->primary('id');
$table->index('field_id');
$table->unique('entry_id');
$table->index('value');
});
<?php
/**
* Selectbox Field Schema
*
* Changed some column names
*/
Schema::create('fields_schema_select', function($table)
{
$table->increments('id')->unsigned();
$table->integer('field_id')->unsigned();
$table->enum('allow_multiple', array('yes', 'no'))->default('no');
$table->enum('show_association', array('yes', 'no'))->default('yes');
$table->enum('sort_options', array('yes', 'no'))->default('no');
$table->text('static_options');
$table->integer('dynamic_options')->unsigned()->nullable();
$table->primary('id');
$table->unique('field_id');
});
/**
* Selectbox Field Data
*
* Multiple entries can exist for the same `entry_id`
*/
Schema::create('filds_data_select', function($table)
{
$table->increments('id')->unsigned();
$table->integer('field_id')->unsigned();
$table->integer('entry_id')->unsigned();
$table->string('handle', 255);
$table->string('value', 255);
$table->primary('id');
$table->index('field_id');
$table->index('entry_id');
$table->index('handle');
$table->index('value');
});
<?php
/**
* Taglist Field Schema
*
* Changed some column names
*/
Schema::create('fields_schema_taglist', function($table)
{
$table->increments('id')->unsigned();
$table->integer('field_id')->unsigned();
$table->string('validator', 255)->nullable();
$table->string('pre_populate_source', 15);
$table->primary('id');
$table->unique('field_id');
$table->index('pre_populate_source');
});
/**
* Taglist Field Data
*/
Schema::create('filds_data_taglist', function($table)
{
$table->increments('id')->unsigned();
$table->integer('field_id')->unsigned();
$table->integer('entry_id')->unsigned();
$table->text('handle');
$table->text('value');
$table->primary('id');
$table->index('field_id');
$table->index('entry_id');
$table->index('handle');
$table->index('value');
});
<?php
/**
* Text Field Schema
*
* Replaces: Text Input, Textarea, and Text Box (based on Text Box)
*
* Changed some column names
*/
Schema::create('fields_schema_text', function($table)
{
$table->increments('id')->unsigned();
$table->integer('field_id')->unsigned();
$table->integer('column_length')->unsigned();
$table->enum('size', array('single', 'small', 'medium', 'large', 'huge'))->default('single');
$table->string('formatter', 255)->nullable();
$table->string('validator', 255)->nullable();
$table->integer('max_length')->default(0);
$table->enum('cdata_wrap', array('yes', 'no'))->default('no');
$table->enum('create_handle', array('yes', 'no'))->default('yes');
$table->primary('id');
$table->unique('field_id');
});
/**
* Text Field Data
*/
Schema::create('filds_data_text', function($table)
{
$table->increments('id')->unsigned();
$table->integer('field_id')->unsigned();
$table->integer('entry_id')->unsigned();
$table->text('handle');
$table->text('value');
$table->text('value_formatted');
$table->primary('id');
$table->index('field_id');
$table->index('entry_id');
$table->index('handle');
$table->index('value');
$table->index('value_formatted');
});
<?php
/**
* Upload Field Schema
*
* Changed some column names
*/
Schema::create('fields_schema_upload', function($table)
{
$table->increments('id')->unsigned();
$table->integer('field_id')->unsigned();
$table->string('destination', 255);
$table->string('validator', 255)->nullable();
$table->primary('id');
$table->unique('field_id');
});
/**
* Upload Field Data
*
* Only a single entry can exist for the `entry_id`
*/
Schema::create('filds_data_upload', function($table)
{
$table->increments('id')->unsigned();
$table->integer('field_id')->unsigned();
$table->integer('entry_id')->unsigned();
$table->string('file', 255);
$table->integer('size')->unsigned();
$table->string('mimetype', 100)->nullable();
$table->string('meta', 255)->nullable();
$table->primary('id');
$table->index('field_id');
$table->unique('entry_id');
$table->index('file');
$table->index('mimetype');
});
<?php
/**
* Pages Table
*
* Removed some 'default null' statements for some columns as they will always contain values
*/
Schema::create('pages', function($table)
{
$table->increments('id')->unsigned();
$table->integer('parent')->unsigned()->nullable();
$table->string('title', 255);
$table->string('handle', 255);
$table->string('path', 255)->nullable();
$table->string('params', 255)->nullable();
$table->text('data_sources')->nullable();
$table->text('events')->nullable();
$table->integer('sortorder')->unsigned()->default('1');
$table->primary('id');
$table->index('parent');
});
/**
* Pages Types Table
*/
Schema::create('pages_types', function($table)
{
$table->increments('id')->unsigned();
$table->integer('page_id')->unsigned();
$table->string('type', 50);
$table->primary('id');
$table->unique(array('page_id', 'type'));
});
<?php
/**
* Cache Table
*
* Complete change to Laravel style table
*/
Schema::create('cache', function($table)
{
$table->string('key');
$table->text('value');
$table->timestamp('expiration');
$table->unique('key');
$table->index('expiry');
$table->index('hash');
});
/**
* Sessions table
*
* Complete change to Laravel style table
*/
Schema::create('sessions', function($table)
{
$table->string('id');
$table->text('payload');
$table->integer('last_activity');
$table->unique('id');
});
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment