log parsing with sqlite

log-views.sql

CREATE TABLE log_ingest (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  log TEXT UNIQUE NOT NULL,
  CHECK ( instr(log, " ") )
);

-- find entry
CREATE VIEW _logs_0 AS
  SELECT
    id,
    log,
    instr(log, ' ') AS pos
  FROM
    log_ingest;

CREATE VIEW _logs_1 AS
  SELECT
    id,
    trim(substr(log, pos), ' ') as log,
    substr(log, 0, pos) as entry
  FROM
    _logs_0;

-- find src
CREATE VIEW _logs_2 AS
  SELECT
    id,
    log,
    entry,
    instr(log, ' ') AS pos
  FROM
    _logs_1;


CREATE VIEW _logs_3 AS
  SELECT
    id,
    trim(substr(log, pos), ' ') as log,
    entry,
    substr(log, 0, pos) as src
  FROM
    _logs_2;

-- find tag
CREATE VIEW _logs_4 AS
  SELECT
    id,
    log,
    entry,
    src,
    instr(log, ' ') AS pos
  FROM
    _logs_3;

CREATE VIEW _logs_5 AS
  SELECT
    id,
    trim(substr(log, pos), ' ') as log,
    entry,
    src,
    trim(substr(log, 0, pos), ':') as tag
  FROM
    _logs_4;

-- finalize
CREATE VIEW logs AS
  SELECT
    id,
    entry,
    src,
    tag,
    log as msg
  FROM
    _logs_5;