Open 1fifoto opened 1 year ago
Update database with additional tag, action, event_value, and event_label columns.
CREATE TABLE `usage_log` (
`id` int(11) NOT NULL,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`ip_address` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
`note` longtext COLLATE utf8_unicode_ci DEFAULT NULL,
`tag` longtext COLLATE utf8_unicode_ci DEFAULT NULL,
`action` longtext COLLATE utf8_unicode_ci DEFAULT NULL,
`event_value` int(11) DEFAULT NULL,
`event_label` longtext COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Leave the current note column in the table for the time being.
Update invalid JSON by changing note's carriage-returns to '\n' string
UPDATE `usage_log`
SET `note`=REPLACE(`note`,'\n','\\n')
Extract JSON from note column and inject it into tag, action, event_value and event_label columns
UPDATE `usage_log`
SET
`tag`=JSON_EXTRACT(`note`,'$.tag'),
`action`=JSON_EXTRACT(`note`,'$.action'),
`event_value`=JSON_EXTRACT(`note`,'$.note.event_value'),
`event_label`=JSON_EXTRACT(`note`,'$.note.event_label')
In server.js decode of URL = '/api/v1/usage_log' we should extract from the
var note
into separate columns: