Skip to content

Instantly share code, notes, and snippets.

@tmihalicka
Last active April 7, 2017 14:40
Show Gist options
  • Save tmihalicka/f54e771df98b4e04948d2a861daf63e0 to your computer and use it in GitHub Desktop.
Save tmihalicka/f54e771df98b4e04948d2a861daf63e0 to your computer and use it in GitHub Desktop.
# Event Stream
CREATE TABLE `event_streams` (
`no` BIGINT(20) NOT NULL AUTO_INCREMENT,
`real_stream_name` VARCHAR(150) NOT NULL,
`stream_name` CHAR(41) NOT NULL,
`metadata` TEXT NOT NULL,
`category` VARCHAR(150),
CHECK (`metadata` IS NOT NULL OR JSON_VALID(`metadata`)),
PRIMARY KEY (`no`),
UNIQUE KEY `ix_rsn` (`real_stream_name`),
KEY `ix_cat` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
# Transaction Stream (my aggregate root is Transaction)
CREATE TABLE `$tableName` (
`no` BIGINT(20) NOT NULL AUTO_INCREMENT,
`event_id` CHAR(36) COLLATE utf8_bin NOT NULL,
`event_name` VARCHAR(100) COLLATE utf8_bin NOT NULL,
`payload` TEXT NOT NULL,
`metadata` TEXT NOT NULL,
`created_at` DATETIME(6) NOT NULL,
`aggregate_version` INT(11) UNSIGNED GENERATED ALWAYS AS (JSON_EXTRACT(metadata, '$._aggregate_version')) STORED,
`aggregate_id` CHAR(36) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(metadata, '$._aggregate_id'))) STORED,
`aggregate_type` VARCHAR(150) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(metadata, '$._aggregate_type'))) STORED,
CHECK (`payload` IS NOT NULL OR JSON_VALID(`payload`)),
CHECK (`metadata` IS NOT NULL OR JSON_VALID(`metadata`)),
PRIMARY KEY (`no`),
UNIQUE KEY `ix_event_id` (`event_id`),
UNIQUE KEY `ix_unique_event` (`aggregate_type`, `aggregate_id`, `aggregate_version`),
KEY `ix_query_aggregate` (`aggregate_type`,`no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment