[Sprint/MindsOne] (feat): migrate helpdesk to cassandra

closes #201, #134 (closed), #132 (closed), #133 (closed)

Cassandra schema:

CREATE TABLE minds.helpdesk_categories (
    uuid uuid PRIMARY KEY,
    branch text,
    parent uuid,
    title text
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';


CREATE TABLE minds.helpdesk_faq (
    uuid uuid PRIMARY KEY,
    answer text,
    category_uuid uuid,
    question text,
    score int,
    votes_down set<varint>,
    votes_up set<varint>
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';
CREATE INDEX score ON minds.helpdesk_faq (score);

CREATE MATERIALIZED VIEW minds.helpdesk_faq_by_category_uuid AS
    SELECT category_uuid, uuid, answer, question, score, votes_down, votes_up
    FROM minds.helpdesk_faq
    WHERE uuid IS NOT NULL AND category_uuid IS NOT NULL
    PRIMARY KEY (category_uuid, uuid)
    WITH CLUSTERING ORDER BY (uuid ASC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

ElasticSearch schema:

{"minds_helpdesk":{"aliases":{},"mappings":{"question":{"properties":{"answer":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"category_uuid":{"type":"text"},"question":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"score":{"type":"integer"},"suggest":{"type":"completion","analyzer":"simple","preserve_separators":true,"preserve_position_increments":true,"max_input_length":50},"uuid":{"type":"text"}}}},"settings":{"index":{"creation_date":"1548335276665","number_of_shards":"5","number_of_replicas":"1","uuid":"49VLKlXoQ9SbzYxCDm_G4g","version":{"created":"5061199"},"provided_name":"minds_helpdesk"}}}}

For migrating existing data from cockroachdb, only this command should be needed: php cli.php Migrations HelpDesk [--dry]

There's also a CLI for calculating scores: php cli.php HelpdeskScores