Flow/Database

flow_definition
CREATE TABLE `flow_definition` ( `definition_id` binary(16) NOT NULL,  `definition_wiki` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,  `definition_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,  `definition_type` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,  `definition_options` blob,  PRIMARY KEY (`definition_id`),  UNIQUE KEY `flow_definition_unique_name` (`definition_wiki`,`definition_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 mysql> explain SELECT * FROM `flow_definition` WHERE definition_name = 'discussion' AND definition_wiki = 'git_my_wiki' LIMIT 1; ++-+-+---+-+-+-+-+--+---+ ++-+-+---+-+-+-+-+--+---+ ++-+-+---+-+-+-+-+--+---+ 1 row in set (0.02 sec)
 * stores a list of flow definition, eg, discussion, topic
 * index `flow_definition_unique_name` is mainly used for flow definition_id look up, example query:
 * id | select_type | table          | type  | possible_keys               | key                         | key_len | ref         | rows | Extra |
 * 1 | SIMPLE     | flow_definition | const | flow_definition_unique_name | flow_definition_unique_name | 196     | const,const |    1 |       |

flow_workflow
CREATE TABLE `flow_workflow` ( `workflow_id` binary(16) NOT NULL,  `workflow_wiki` varchar(16) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,  `workflow_namespace` int(11) NOT NULL,  `workflow_page_id` int(10) unsigned NOT NULL,  `workflow_title_text` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,  `workflow_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,  `workflow_last_update_timestamp` binary(14) NOT NULL,  `workflow_user_id` bigint(20) unsigned NOT NULL,  `workflow_user_text` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,  `workflow_lock_state` int(10) unsigned NOT NULL,  `workflow_definition_id` binary(16) NOT NULL,  PRIMARY KEY (`workflow_id`),  KEY `flow_workflow_lookup` (`workflow_wiki`,`workflow_namespace`,`workflow_title_text`,`workflow_definition_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 mysql> explain SELECT * FROM `flow_workflow`   WHERE workflow_definition_id = '����bD�@�6���,' AND workflow_wiki = 'git_my_wiki' AND workflow_namespace = '3' AND workflow_title_text = 'Test'  ORDER BY workflow_id DESC LIMIT 1; ++-+---+--+--+--+-+-+--+-+ ++-+---+--+--+--+-+-+--+-+ ++-+---+--+--+--+-+-+--+-+ 1 row in set (0.00 sec)
 * defines a flow instance, which is based on flow_definition
 * index `flow_workflow_lookup` is used to look up a particular workflow on a page based on definition_id, example query:
 * id | select_type | table        | type | possible_keys        | key                  | key_len | ref                     | rows | Extra       |
 * 1 | SIMPLE     | flow_workflow | ref  | flow_workflow_lookup | flow_workflow_lookup | 837     | const,const,const,const |    1 | Using where |

flow_subscription
CREATE TABLE `flow_subscription` ( `subscription_workflow_id` int(10) unsigned NOT NULL,  `subscription_user_id` bigint(20) unsigned NOT NULL,  `subscription_create_timestamp` varchar(14) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,  `subscription_last_updated` varchar(14) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,  UNIQUE KEY `flow_subscription_unique_user_workflow` (`subscription_workflow_id`,`subscription_user_id`),  KEY `flow_subscription_lookup` (`subscription_user_id`,`subscription_last_updated`,`subscription_workflow_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
 * this table is currently not used but will be used in future to support user to flow subscription model

flow_topic_list
CREATE TABLE `flow_topic_list` ( `topic_list_id` binary(16) NOT NULL,  `topic_id` binary(16) DEFAULT NULL,  UNIQUE KEY `flow_topic_list_pk` (`topic_list_id`,`topic_id`),  KEY `flow_topic_list_topic_id` (`topic_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 mysql> explain SELECT * FROM `flow_topic_list`   WHERE topic_list_id = '��\rጫ�������,'  ORDER BY topic_id DESC LIMIT 500; ++-+-+--+++-+---+--+--+ ++-+-+--+++-+---+--+--+ ++-+-+--+++-+---+--+--+ 1 row in set (0.00 sec) mysql> explain SELECT * FROM `flow_topic_list`,`flow_tree_revision`,`flow_revision`  WHERE (tree_rev_id = rev_id) AND (tree_rev_descendant_id = topic_id) AND topic_list_id = '��\rጫ�������,'  ORDER BY rev_id DESC LIMIT 500; ++-+++-+---+-++--+---+ ++-+++-+---+-++--+---+ ++-+++-+---+-++--+---+ 3 rows in set (0.00 sec)
 * discussion flow to topic flow association so we can pull a list of topics for a particular discussion
 * index `flow_topic_list_topic_id` used to pull a list of topic_id for a topic_list_id, example query:
 * id | select_type | table          | type | possible_keys      | key                | key_len | ref   | rows | Extra                    |
 * 1 | SIMPLE     | flow_topic_list | ref  | flow_topic_list_pk | flow_topic_list_pk | 16      | const |    1 | Using where; Using index |
 * id | select_type | table             | type   | possible_keys                               | key                               | key_len | ref                                        | rows | Extra                                                     |
 * 1 | SIMPLE     | flow_topic_list    | ref    | flow_topic_list_pk,flow_topic_list_topic_id | flow_topic_list_pk                | 16      | const                                      |    1 | Using where; Using index; Using temporary; Using filesort |
 * 1 | SIMPLE     | flow_tree_revision | ref    | PRIMARY,flow_tree_descendant_id_revisions   | flow_tree_descendant_id_revisions | 16      | git_my_wiki.flow_topic_list.topic_id       |    1 |                                                           |
 * 1 | SIMPLE     | flow_revision      | eq_ref | PRIMARY                                     | PRIMARY                           | 16      | git_my_wiki.flow_tree_revision.tree_rev_id |    1 |                                                           |

mysql> explain SELECT * FROM `flow_topic_list` WHERE topic_id = '��\rጫ�������,' LIMIT 1; ++-+-+--+--+--+-+---+--+-+ ++-+-+--+--+--+-+---+--+-+ ++-+-+--+--+--+-+---+--+-+ 1 row in set (0.02 sec)
 * index `flow_topic_list_topic_id` is used to look up the topic_list_id for a topic_id, example query:
 * id | select_type | table          | type | possible_keys            | key                      | key_len | ref   | rows | Extra       |
 * 1 | SIMPLE     | flow_topic_list | ref  | flow_topic_list_topic_id | flow_topic_list_topic_id | 17      | const |    1 | Using where |

flow_tree_revision
flow_tree_revision | CREATE TABLE `flow_tree_revision` ( `tree_rev_descendant_id` binary(16) NOT NULL,  `tree_rev_id` binary(16) NOT NULL,  `tree_orig_create_time` varchar(12) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,  `tree_orig_user_id` bigint(20) unsigned NOT NULL,  `tree_orig_user_text` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,  `tree_parent_id` binary(16) DEFAULT NULL,  PRIMARY KEY (`tree_rev_id`),  UNIQUE KEY `flow_tree_descendant_id_revisions` (`tree_rev_descendant_id`,`tree_rev_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 mysql> explain SELECT * FROM `flow_tree_revision` JOIN `flow_revision` `rev` ON ((tree_rev_id = rev_id))  WHERE tree_rev_descendant_id = '� a�`�盾V���,'  ORDER BY rev_id DESC LIMIT 1; ++-+++---+---+-++--+--+ ++-+++---+---+-++--+--+ ++-+++---+---+-++--+--+ 2 rows in set (0.01 sec)
 * topic/post content revisions, it has one to many relation to flow_revision table
 * index `flow_tree_descendant_id_revisions` - mainly joins with flow_revision table and accessed by either `tree_rev_descendant_id` example query:
 * id | select_type | table             | type   | possible_keys                             | key                               | key_len | ref                                        | rows | Extra                                        |
 * 1 | SIMPLE     | flow_tree_revision | ref    | PRIMARY,flow_tree_descendant_id_revisions | flow_tree_descendant_id_revisions | 16      | const                                      |    1 | Using where; Using temporary; Using filesort |
 * 1 | SIMPLE     | rev                | eq_ref | PRIMARY                                   | PRIMARY                           | 16      | git_my_wiki.flow_tree_revision.tree_rev_id |    1 |                                              |

flow_header_revision
CREATE TABLE `flow_header_revision` ( `header_workflow_id` binary(16) NOT NULL,  `header_rev_id` binary(16) NOT NULL,  PRIMARY KEY (`header_workflow_id`,`header_rev_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 mysql> explain SELECT * FROM `flow_header_revision` JOIN `flow_revision` `rev` ON ((header_rev_id = rev_id))  WHERE header_workflow_id = '��\rጫ�������,'  ORDER BY rev_id DESC LIMIT 1; ++-+--++---+-+-++--+---+ ++-+--++---+-+-++--+---+ ++-+--++---+-+-++--+---+ 2 rows in set (0.00 sec)
 * header content revisions, it has one to many relation to flow_revision table
 * The primary key is used to look up rev_id by header flow_id, example query:
 * id | select_type | table               | type   | possible_keys | key     | key_len | ref                                            | rows | Extra                                                     |
 * 1 | SIMPLE     | flow_header_revision | ref    | PRIMARY       | PRIMARY | 16      | const                                          |    1 | Using where; Using index; Using temporary; Using filesort |
 * 1 | SIMPLE     | rev                  | eq_ref | PRIMARY       | PRIMARY | 16      | git_my_wiki.flow_header_revision.header_rev_id |    1 |                                                           |

flow_revision
CREATE TABLE `flow_revision` ( `rev_id` binary(16) NOT NULL,  `rev_type` varchar(16) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,  `rev_user_id` int(10) unsigned NOT NULL,  `rev_user_text` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',  `rev_parent_id` binary(16) DEFAULT NULL,  `rev_flags` tinyblob NOT NULL,  `rev_content` mediumblob NOT NULL,  `rev_change_type` varbinary(255) DEFAULT NULL,  `rev_mod_state` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,  `rev_mod_user_id` bigint(20) unsigned DEFAULT NULL,  `rev_mod_user_text` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,  `rev_mod_timestamp` varchar(14) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,  `rev_last_edit_id` binary(16) DEFAULT NULL,  `rev_edit_user_id` bigint(20) unsigned DEFAULT NULL,  `rev_edit_user_text` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,  `rev_mod_reason` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`rev_id`), UNIQUE KEY `flow_revision_unique_parent` (`rev_parent_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
 * individual revision storage for header/topic/post
 * This table is primarily accessed by the primary key rev_id

flow_tree_node
CREATE TABLE `flow_tree_node` ( `tree_ancestor_id` binary(16) NOT NULL,  `tree_descendant_id` binary(16) NOT NULL,  `tree_depth` smallint(6) NOT NULL,  UNIQUE KEY `flow_tree_node_pk` (`tree_ancestor_id`,`tree_descendant_id`),  UNIQUE KEY `flow_tree_constraint` (`tree_descendant_id`,`tree_depth`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 mysql> explain SELECT tree_ancestor_id,tree_descendant_id FROM `flow_tree_node`  WHERE tree_ancestor_id IN ('� ۺ�\0��u&gt;���,','� d�盾V���,','� de\n3盾V���,','� c�g�盾V���,','� a�`�盾V���,'); ++-++---+---+---+-+--+--+--+ ++-++---+---+---+-+--+--+--+ ++-++---+---+---+-+--+--+--+ 1 row in set (0.02 sec) mysql> explain SELECT tree_ancestor_id, tree_depth FROM `flow_tree_node` WHERE tree_descendant_id = '� d�盾V���,'; ++-++--+--+--+-+---+--+--+ ++-++--+--+--+-+---+--+--+ ++-++--+--+--+-+---+--+--+ 1 row in set (0.00 sec)
 * closure table implementation of tree storage in sql
 * example queries:
 * id | select_type | table         | type  | possible_keys     | key               | key_len | ref  | rows | Extra                    |
 * 1 | SIMPLE     | flow_tree_node | range | flow_tree_node_pk | flow_tree_node_pk | 16      | NULL |    5 | Using where; Using index |
 * id | select_type | table         | type | possible_keys        | key                  | key_len | ref   | rows | Extra                    |
 * 1 | SIMPLE     | flow_tree_node | ref  | flow_tree_constraint | flow_tree_constraint | 16      | const |    1 | Using where; Using index |