[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
Edited by Marcelo Rivera