Skip to content

Latest commit

 

History

History
275 lines (230 loc) · 11.2 KB

DATABASE.md

File metadata and controls

275 lines (230 loc) · 11.2 KB
Raw

Database and Schema

Setup Database

Database setup and migrations are using phinx - read the documentation

cd data
./phinx/bin/phinx migrate

If you have trouble doing this, you can try to import the sample empty database dump file data/db/create.mysql directly.

Generate Test Data

Use faker to seed the database. Phinx has integration for this.

cd data
./phinx seed:run

Tables

Notes on the tables and schema

Where tables have fields named 'key' and 'value':

  • 'value' is automatically serialized when saving: arrays with json_encode, objects using serialize
  • methods in the model for retrieval will normally automatically de-serialize 'value' for the given 'key'

#### UUIDs

  • Throughout the database tables there are references to fields named 'UUID'.
  • What is a UUID?
  • UUIDs are used as a alternative form of identifying data items which is not dependent on the underlying database.
  • UUIDs are more secure than the easily-guessable auto-increment values and should normally be referenced over internal database 'id' value where possible.
  • UUIDs are generated by a function 'uuid' in the String Helper which uses faker's UUID generator.
  • Government health warning: It is important to note identical values for a UUID might be used, although the probability of this occurring is very small.
  • When generating UUIDs in the models, code should be written to ensure that the UUID is unique for the type of data being stored, although this clearly has a performance cost as the size of the database increases and will need refactoring later. e.g having UUIDs in a registry table with an in_use flag

phinxlog

  • Used by phinx to store migration data.
  • Independent from main application functioning.
CREATE TABLE `phinxlog` (
  `version` bigint(20) NOT NULL,
  `migration_name` varchar(100) DEFAULT NULL,
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `end_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `breakpoint` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

audit

  • Used to track and keep a history of changes within the database.
  • Shouldn't be used within the application other than to store changes.
CREATE TABLE `audit` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL COMMENT 'UUID',
  `users_uuid` varchar(36) DEFAULT NULL COMMENT 'User UUID',
  `ip` varchar(16) DEFAULT NULL COMMENT 'IP-Address',
  `agent` varchar(255) DEFAULT NULL COMMENT 'User-Agent',
  `created` datetime NOT NULL COMMENT 'Created',
  `actor` varchar(128) DEFAULT NULL COMMENT 'Actor',
  `event` varchar(128) DEFAULT NULL COMMENT 'Event',
  `description` varchar(255) DEFAULT NULL COMMENT 'Description',
  `old` text COMMENT 'Old Value',
  `new` text COMMENT 'New Value',
  `debug` text COMMENT 'Debug Information',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

config_data

CREATE TABLE `config_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL COMMENT 'UUID',
  `key` varchar(255) NOT NULL COMMENT 'Key',
  `value` text COMMENT 'Value',
  `type` varchar(32) DEFAULT NULL COMMENT 'Type',
  `options` text COMMENT 'Options',
  `rank` int(11) DEFAULT '9999' COMMENT 'Rank',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • Used to store application configuration data which is configurable within the application itself
  • Uses key/value storage system
  • type is an optional identifier for the type of data (e.g. a hint at how to display the item)
  • options are a list of optional settings for the data (e.g. list of acceptable values)
  • rank is the priority of the item in relation to others (e.g. admin ui settings order)

users

  • Used to store application user information
  • 'scopes' is a comma-separated list of scopes the user belongs to, e.g. user (default), api (for api-access), admin (for admin access) etc
  • The users table should only contain the absolute minimum data required for a user to sign-up to the application - any other supplemental data should be stored elsewhere, e.g. in the users_data table
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL COMMENT 'UUID',
  `password` varchar(16) NOT NULL COMMENT 'Password',
  `email` varchar(255) NOT NULL COMMENT 'Email',
  `firstname` varchar(128) NOT NULL COMMENT 'First Name(s)',
  `lastname` varchar(128) NOT NULL COMMENT 'Last Name(s)',
  `scopes` varchar(64) NOT NULL DEFAULT 'user' COMMENT 'Account Scopes',
  `status` varchar(32) NOT NULL DEFAULT 'NEW' COMMENT 'Account Status',
  `password_question` varchar(255) NOT NULL COMMENT 'Password Hint Question',
  `password_answer` varchar(255) NOT NULL COMMENT 'Password Hint Answer',
  `created` datetime NOT NULL COMMENT 'Created',
  `login_count` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Login Count',
  `login_last` datetime DEFAULT NULL COMMENT 'Last Login',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

'status'

Please refer to the User Model code for latest list of status types:

  • Note that when an email account is confirmed, a boolean flag email_confirmed gets set in the table users_data

users_data

  • Used to store general key/value data on a per-user basis
CREATE TABLE `users_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL COMMENT 'UUID',
  `users_uuid` varchar(36) NOT NULL COMMENT 'User UUID',
  `key` varchar(255) NOT NULL COMMENT 'Key',
  `value` text COMMENT 'Value',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  UNIQUE KEY `users_uuid` (`users_uuid`,`key`),
  CONSTRAINT `users_data_ibfk_1` FOREIGN KEY (`users_uuid`) REFERENCES `users` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

oauth2_apps

A registered user if in the group 'api' can register an application stored in the table below. This application can then be used to access the api and issue OAuth2 tokens on behalf of users. The status must be 'approved' to use the api.

CREATE TABLE `oauth2_apps` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created` datetime NOT NULL COMMENT 'Created',
  `users_uuid` varchar(36) NOT NULL COMMENT 'User UUID',
  `client_id` varchar(36) NOT NULL COMMENT 'Client Id',
  `client_secret` varchar(36) NOT NULL COMMENT 'Client Secret',
  `name` varchar(255) NOT NULL COMMENT 'Application Name',
  `logo_url` tinytext COMMENT 'Logo Image URL',
  `description` text COMMENT 'Description',
  `scope` text COMMENT 'Allowed Scopes',
  `callback_uri` text COMMENT 'Callback URI',
  `redirect_uris` text COMMENT 'Redirect URIs',
  `status` varchar(16) NOT NULL DEFAULT 'NEW' COMMENT 'Status',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `client_id` (`client_id`),
  UNIQUE KEY `client_secret` (`client_secret`),
  UNIQUE KEY `client_id_2` (`client_id`,`client_secret`),
  KEY `users_uuid` (`users_uuid`),
  CONSTRAINT `oauth2_apps_ibfk_1` FOREIGN KEY (`users_uuid`) REFERENCES `users` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

oauth2_tokens

Tokens created for API access on behalf of users.

CREATE TABLE `oauth2_tokens` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL COMMENT 'UUID',
  `created` datetime NOT NULL COMMENT 'Created',
  `expires` datetime DEFAULT NULL COMMENT 'Expires',
  `users_uuid` varchar(36) NOT NULL COMMENT 'User UUID',
  `client_id` varchar(36) NOT NULL COMMENT 'Client Id',
  `token` varchar(36) NOT NULL COMMENT 'Token Value',
  `type` varchar(16) NOT NULL COMMENT 'Token Type',
  `scope` text COMMENT 'Allowed Scopes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  UNIQUE KEY `token` (`token`),
  UNIQUE KEY `client_id` (`client_id`,`users_uuid`,`type`),
  KEY `users_uuid` (`users_uuid`),
  CONSTRAINT `oauth2_tokens_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `oauth2_apps` (`client_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `oauth2_tokens_ibfk_2` FOREIGN KEY (`users_uuid`) REFERENCES `users` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

reports

Raw SQL that can be run to generate reports, export to CSV.

CREATE TABLE `reports` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL COMMENT 'UUID',
  `users_uuid` varchar(36) NOT NULL COMMENT 'User UUID',
  `scopes` varchar(64) NOT NULL DEFAULT 'user' COMMENT 'Account Scopes',
  `key` varchar(255) NOT NULL COMMENT 'Key',
  `name` varchar(255) NOT NULL COMMENT 'Name',
  `description` text COMMENT 'Description',
  `query` text COMMENT 'Query',
  `options` text COMMENT 'Extra Options',
  `created` datetime NOT NULL COMMENT 'Created',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `users_uuid` (`users_uuid`),
  CONSTRAINT `reports_ibfk_1` FOREIGN KEY (`users_uuid`) REFERENCES `users` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

assets

Media file and upload storage. Each file should have some minimal metadata, and where it can be obtained from (filesystem and URL) as well as tags and categories which can be used to determine additional actions when used.

  • uuid - the only unique id column in the table other than internal id
  • user_uuid - optional owner/originator of file
  • key - an id linked to user_uuid, can occur more than once
  • groups - list of groups the item belongs to, e.g. SKUs
  • name - name to display
  • description - descriptive text
  • filename - server filename path
  • size - filesize
  • type - file type
  • categories - list of categories the file belongs to (can be used to determine image resizing sizes for example)
  • tags - arbitrary list of tags to describe file
  • url - URL to the original asset file
  • metadata - any additional metadata, e.g copyright info
CREATE TABLE `assets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL COMMENT 'UUID',
  `users_uuid` varchar(36) NOT NULL COMMENT 'User UUID',
  `key` varchar(255) DEFAULT NULL COMMENT 'Key',
  `groups` varchar(255) DEFAULT NULL COMMENT 'Groups',
  `name` varchar(255) DEFAULT NULL COMMENT 'Name',
  `description` text COMMENT 'Description',
  `filename` text NOT NULL COMMENT 'Filename',
  `size` int(11) NOT NULL COMMENT 'File Size',
  `type` varchar(255) DEFAULT NULL COMMENT 'Mime Type',
  `categories` text COMMENT 'Categories',
  `tags` text COMMENT 'Tags',
  `created` datetime NOT NULL COMMENT 'Created',
  `updated` datetime NOT NULL COMMENT 'Updated',
  `url` text NOT NULL COMMENT 'URL',
  `metadata` text NOT NULL COMMENT 'Additional Metadata',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `users_uuid` (`users_uuid`,`key`),
  KEY `type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;