search

statusengine / worker

PHP worker process that writes all event data to a storage backend
https://statusengine.org/worker/#overview
GNU General Public License v3.0
9 stars 8 forks source link

[CrateDB] Replace ON DUPLICATE KEY UPDATE with new Syntax ON CONFLICT #10

Closed nook24 closed 6 years ago

nook24 commented 6 years ago

Replace CrateDB SQL Statements which use ON DUPLICATE KEY UPDATE with INSERT INTO ON CONFLICT https://crate.io/docs/crate/reference/en/latest/sql/statements/insert.html#on-conflict-do-update-set

New Syntax Example

INSERT INTO statusengine_servicestatus
    (hostname, service_description, status_update_time, output, long_output, perfdata, current_state, current_check_attempt, max_check_attempts, last_check, next_check, is_passive_check, last_state_change, last_hard_state_change, last_hard_state, is_hardstate, last_notification, next_notification, notifications_enabled, problem_has_been_acknowledged, acknowledgement_type, passive_checks_enabled, active_checks_enabled, event_handler_enabled, flap_detection_enabled, is_flapping, latency, execution_time, scheduled_downtime_depth, process_performance_data, obsess_over_service, normal_check_interval, retry_check_interval, check_timeperiod, node_name, last_time_ok, last_time_warning, last_time_critical, last_time_unknown, current_notification_number, percent_state_change, event_handler, check_command)
    VALUES('a47af17f-15d9-41af-ba9d-cb89c8d88e18','c3b06d13-cb3b-47d1-9342-33a4e41bc7d0',1537949703,'OK - load average: 0.63, 0.98, 1.07','','load1=0.630;20.000;35.000;0; load5=0.980;25.000;28.000;0; load15=1.070;25.000;28.000;0;',0,1,3,1537949703,1537949763,true,1537172439,1535117522,0,true,0,0,true,false,0,true,true,true,false,false,0.198347,0.01918,0,true,true,60,60,'41012866-6114-4853-9caf-6ffd19954e50','staging-nightly',1537949703,1535117522,1535117462,1537172439,0,0,'','some we replace is this auto updatE?'), ('b383f0b7-acfa-4337-9456-b3220b876ed6','94c964e2-5256-4977-bd7b-fbed81e764d3',1537949703,'UNKNOWN: Unknown - woher soll ich denn das wissen','','',3,3,3,1537949703,1537950603,true,1524728151,1524728151,3,true,0,1537949703,true,false,0,true,true,true,false,false,0.217184,0.002269,0,false,true,900,60,'41012866-6114-4853-9caf-6ffd19954e50','staging-nightly',0,0,0,1537949703,0,0,'','d4f3524b-d90f-4562-a2fb-c70d823f66ef!3!Unknown - woher soll ich denn das wissen'), ('0cf3730c-4e57-401d-9fa9-c9f698c4e405','f24a6407-3c39-4d1a-af93-a8e9fadc97ef',1537949703,'Remote command execution failed: @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@','','',3,3,3,1537949643,1537949763,true,1521197992,1521197992,3,true,0,1537949643,true,false,0,true,true,true,false,false,0.302169,0.040419,0,true,true,60,60,'41012866-6114-4853-9caf-6ffd19954e50','staging-nightly',1520682803,0,1521197992,1537949643,0,0,'','fd52f68f-e0f9-4c69-9f69-bc1b2d97188a!monitoring!sudo /usr/lib/nagios/plugins/check_zfs -w 80 -c 90 -p rpool'), ('9a456851-57ca-4770-b335-bd9276183642','9d56148a-7067-4566-b5bc-d4b53bbca297',1537949703,'OK: Alles OK','','',0,1,3,1537948803,1537950603,true,1506687048,1506687048,0,true,0,0,true,false,0,true,true,true,false,false,0.328132,0.001923,0,false,true,900,60,'41012866-6114-4853-9caf-6ffd19954e50','staging-nightly',1537948803,0,0,1506687048,0,0,'','d4f3524b-d90f-4562-a2fb-c70d823f66ef!0!Alles OK'), ('9a456851-57ca-4770-b335-bd9276183642','9d56148a-7067-4566-b5bc-d4b53bbca297',1537949703,'OK: Alles OK','','',0,1,3,1537949703,1537950603,true,1506687048,1506687048,0,true,0,0,true,false,0,true,true,true,false,false,0.327552,0.001741,0,false,true,900,60,'41012866-6114-4853-9caf-6ffd19954e50','staging-nightly',1537949703,0,0,1506687048,0,0,'','d4f3524b-d90f-4562-a2fb-c70d823f66ef!0!Alles OK')
    ON CONFLICT (hostname, service_description) DO UPDATE SET status_update_time = excluded.status_update_time, output = excluded.output, long_output = excluded.long_output, perfdata = excluded.perfdata, current_state = excluded.current_state, current_check_attempt = excluded.current_check_attempt, max_check_attempts = excluded.max_check_attempts, last_check = excluded.last_check, next_check = excluded.next_check, is_passive_check = excluded.is_passive_check, last_state_change = excluded.last_state_change, last_hard_state_change = excluded.last_hard_state_change, last_hard_state = excluded.last_hard_state, is_hardstate = excluded.is_hardstate, last_notification = excluded.last_notification, next_notification = excluded.next_notification, notifications_enabled = excluded.notifications_enabled, problem_has_been_acknowledged = excluded.problem_has_been_acknowledged, acknowledgement_type = excluded.acknowledgement_type, passive_checks_enabled = excluded.passive_checks_enabled, active_checks_enabled = excluded.active_checks_enabled, event_handler_enabled = excluded.event_handler_enabled, flap_detection_enabled = excluded.flap_detection_enabled, is_flapping = excluded.is_flapping, latency = excluded.latency, execution_time = excluded.execution_time, scheduled_downtime_depth = excluded.scheduled_downtime_depth, process_performance_data = excluded.process_performance_data, obsess_over_service = excluded.obsess_over_service, normal_check_interval = excluded.normal_check_interval, retry_check_interval = excluded.retry_check_interval, check_timeperiod = excluded.check_timeperiod, node_name = excluded.node_name, last_time_ok = excluded.last_time_ok, last_time_warning = excluded.last_time_warning, last_time_critical = excluded.last_time_critical, last_time_unknown = excluded.last_time_unknown, current_notification_number = excluded.current_notification_number, percent_state_change = excluded.percent_state_change, event_handler = excluded.event_handler, check_command = excluded.check_command ;

Old Syntax

Short Example
INSERT INTO table (col1, col2, col3) VALUES(?,?, ?)
ON DUPLICATE KEY UPDATE
col2=VALUES(col2), col3=VALUES(col3);

New one

INSERT INTO table (col1, col2, col3) VALUES(?,?,?)
ON CONFLICT (col1)
DO UPDATE SET col2 = excluded.col2, col3 = excluded.col3;