blob: ce03bc2d9a46c3b39a2a0f0d8d105e7846ad044d [file] [log] [blame]
----------------------------------------------------------------------------------
-- Copyright (c) 2013 Jürgen Rose and others.
-- All rights reserved. This program and the accompanying materials
-- are made available under the terms of the Eclipse Public License v1.0
-- which accompanies this distribution, and is available at
-- http://www.eclipse.org/legal/epl-v10.html
--
-- Contributors:
-- Jürgen Rose - initial API and implementation
----------------------------------------------------------------------------------
-- as database owner
CREATE LANGUAGE plpgsql;
-- DROP TABLE es_ae_events;
CREATE TABLE es_ae_events_json
(
id UUID NOT NULL,
instance_id VARCHAR(32),
source_timestamp TIMESTAMP,
entry_timestamp TIMESTAMP,
data TEXT,
CONSTRAINT pk_es_ae_events_json PRIMARY KEY (id)
);
-- DROP TABLE es_ae_rep;
CREATE TABLE es_ae_rep
(
ID CHAR(36) NOT NULL,
ENTRY_TIMESTAMP TIMESTAMP,
NODE_ID VARCHAR(32),
DATA BYTEA,
CONSTRAINT pk_es_ae_rep PRIMARY KEY (id)
);
-- DROP EXTENSION json_accessors
CREATE EXTENSION json_accessors;
-- DROP EXTENSION pg_trgm
CREATE EXTENSION pg_trgm;
-- DROP EXTENSION fuzzystrmatch
CREATE EXTENSION fuzzystrmatch;
-- DROP FUNCTION es_ae_extract_field(text, text);
CREATE OR REPLACE FUNCTION es_ae_extract_field(data text, attr text)
RETURNS text AS
$BODY$
DECLARE
o TEXT;
BEGIN
IF data IS NULL THEN
RETURN NULL;
END IF;
IF attr IS NULL THEN
RETURN NULL;
END IF;
IF attr IN ('id', 'sourceTimestamp', 'entryTimestamp') THEN
RETURN json_get_text(data, attr);
ELSE
SELECT json_get_object(data, 'attributes') INTO o;
IF O IS NOT NULL THEN
RETURN json_get_text(o, attr);
END IF;
END IF;
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE;
-- DROP FUNCTION es_ae_create_index(text, text);
CREATE OR REPLACE FUNCTION es_ae_create_index(property text, rettype text)
RETURNS boolean AS
$BODY$
DECLARE
idx_name TEXT;
sql_idx TEXT;
indexexists BOOLEAN;
result BOOLEAN;
BEGIN
IF rettype NOT IN ('boolean', 'long', 'double', 'string') THEN
RAISE NOTICE 'return type not recognized';
RETURN false;
END IF;
SELECT false INTO result;
-- for boolean/numbers this is straight forward
IF rettype IN ('boolean', 'long', 'double') THEN
SELECT 'es_ae_events_json_' || property || '_' || rettype || '_idx' INTO idx_name;
RAISE NOTICE 'try to create index %', idx_name;
SELECT count(*) > 0 FROM pg_class WHERE relname = idx_name INTO indexexists;
IF indexexists THEN
RAISE NOTICE 'index % already exists', idx_name;
ELSE
SELECT 'CREATE INDEX ' || idx_name || ' ON es_ae_events_json USING btree (es_variant_to_' || rettype || '(es_ae_extract_field(data, ''' || property || ''')));' INTO sql_idx;
RAISE NOTICE 'executing %', sql_idx;
EXECUTE sql_idx;
SELECT true INTO result;
END IF;
END IF;
-- for strings we have to create two indexes, a normal btree and a gist index
IF rettype = 'string' THEN
-- btree
SELECT 'es_ae_events_json_' || property || '_' || rettype || '_idx' INTO idx_name;
RAISE NOTICE 'try to create index %', idx_name;
SELECT count(*) > 0 FROM pg_class WHERE relname = idx_name INTO indexexists;
IF indexexists THEN
RAISE NOTICE 'index % already exists', idx_name;
ELSE
SELECT 'CREATE INDEX ' || idx_name || ' ON es_ae_events_json USING btree (lower(substring(es_variant_to_string(es_ae_extract_field(data, ''' || property || ''')), 1, 512)));' INTO sql_idx;
RAISE NOTICE 'executing %', sql_idx;
EXECUTE sql_idx;
SELECT true INTO result;
END IF;
-- gist
SELECT 'es_ae_events_json_' || property || '_' || rettype || '_g_idx' INTO idx_name;
RAISE NOTICE 'try to create index %', idx_name;
SELECT count(*) > 0 FROM pg_class WHERE relname = idx_name INTO indexexists;
IF indexexists THEN
RAISE NOTICE 'index % already exists', idx_name;
ELSE
SELECT 'CREATE INDEX ' || idx_name || ' ON es_ae_events_json USING gist ((lower(substring(es_variant_to_string(es_ae_extract_field(data, ''' || property || ''')), 1, 512))) gist_trgm_ops);' INTO sql_idx;
RAISE NOTICE 'executing %', sql_idx;
EXECUTE sql_idx;
SELECT true INTO result;
END IF;
END IF;
RETURN result;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
-- DROP FUNCTION es_variant_to_boolean(text);
CREATE OR REPLACE FUNCTION es_variant_to_boolean(attr text)
RETURNS boolean AS
$BODY$
BEGIN
IF attr IS NULL
THEN
RETURN NULL;
END IF;
IF attr = 'BOOL#true' THEN
RETURN true;
ELSIF attr = 'BOOL#false' THEN
RETURN false;
ELSE
RETURN NULL;
END IF;
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE;
-- DROP FUNCTION es_variant_to_double(text);
CREATE OR REPLACE FUNCTION es_variant_to_double(attr text)
RETURNS numeric AS
$BODY$
BEGIN
IF attr IS NULL
THEN
RETURN NULL;
END IF;
IF substring(attr from 1 for 6) = 'INT32#' or substring(attr from 1 for 6) = 'INT64#' or substring(attr from 1 for 6) = 'FLOAT#' THEN
RETURN substring(attr from 7)::NUMERIC;
ELSE
RETURN NULL;
END IF;
EXCEPTION
WHEN invalid_text_representation THEN RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE;
-- DROP FUNCTION es_variant_to_long(text);
CREATE OR REPLACE FUNCTION es_variant_to_long(attr text)
RETURNS bigint AS
$BODY$
BEGIN
IF attr IS NULL
THEN
RETURN NULL;
END IF;
IF substring(attr from 1 for 6) = 'INT32#' or substring(attr from 1 for 6) = 'INT64#' THEN
RETURN substring(attr from 7)::BIGINT;
ELSIF substring(attr from 1 for 6) = 'FLOAT#' THEN
RETURN trunc(substring(attr from 7)::NUMERIC)::BIGINT;
ELSE
RETURN NULL;
END IF;
EXCEPTION
WHEN invalid_text_representation THEN RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE;
-- DROP FUNCTION es_variant_to_string(text);
CREATE OR REPLACE FUNCTION es_variant_to_string(attr text)
RETURNS text AS
$BODY$
DECLARE
p INT;
t TEXT;
BEGIN
IF attr IS NULL
THEN
RETURN NULL;
END IF;
SELECT position('#' in attr) INTO p;
SELECT substring(attr from 1 for p) INTO t;
IF t = 'BOOL#' or t = 'INT32#' or t = 'INT64#' or t = 'FLOAT#' or t = 'STRING#' THEN
RETURN substring(attr from p + 1);
ELSIF t = 'NULL#' THEN
RETURN NULL;
ELSE
RETURN attr;
END IF;
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE;
CREATE INDEX es_ae_events_json_instance_id_idx ON es_ae_events_json (instance_id);
CREATE INDEX es_ae_events_json_source_timestamp_idx ON es_ae_events_json (source_timestamp);
CREATE INDEX es_ae_events_json_source_timestamp_d_idx ON es_ae_events_json (source_timestamp DESC);
CREATE INDEX es_ae_events_json_entry_timestamp_idx ON es_ae_events_json (entry_timestamp);
CREATE INDEX es_ae_events_json_entry_timestamp_d_idx ON es_ae_events_json (entry_timestamp DESC);
SELECT es_ae_create_index('monitorType', 'string');
SELECT es_ae_create_index('eventType', 'string');
SELECT es_ae_create_index('value', 'string');
SELECT es_ae_create_index('value', 'boolean');
SELECT es_ae_create_index('value', 'double');
SELECT es_ae_create_index('value', 'long');
SELECT es_ae_create_index('message', 'string');
SELECT es_ae_create_index('priority', 'long');
SELECT es_ae_create_index('source', 'string');
SELECT es_ae_create_index('actorName', 'string');
SELECT es_ae_create_index('actorType', 'string');
SELECT es_ae_create_index('severity', 'string');
SELECT es_ae_create_index('item', 'string');
SELECT es_ae_create_index('itemDescription', 'string');