mirror of
https://github.com/Telecominfraproject/openafc_final.git
synced 2025-10-31 01:57:46 +00:00
405 lines
16 KiB
SQL
405 lines
16 KiB
SQL
/*
|
|
* Copyright (C) 2022 Broadcom. All rights reserved.
|
|
* The term "Broadcom" refers solely to the Broadcom Inc. corporate affiliate
|
|
* that owns the software below.
|
|
* This work is licensed under the OpenAFC Project License, a copy of which is
|
|
* included with this software program.
|
|
*
|
|
* This file creates ALS (AFC Request/Response/Config Logging System) database on PostgreSQL+PostGIS server
|
|
* This file is generated, direct editing is not recommended.
|
|
* Intended maintenance sequence is as follows:
|
|
* 1. Load (copypaste) als_db_schema/ALS.dbml into dbdiagram.io
|
|
* 2. Modify as needed
|
|
* 3. Save (copypaste) modified sources back to als_db_schema/ALS.dbml
|
|
* 4. Also export schema in PostgreSQL format as als_db_schema/ALS_raw.sql
|
|
* 5. Rectify exported schema with als_rectifier.awk (awk -f als_db_schema/als_rectifier.awk < als_db_schema/ALS_raw.sql > ALS.sql)
|
|
*/
|
|
|
|
CREATE EXTENSION postgis;
|
|
|
|
CREATE TABLE "afc_message" (
|
|
"message_id" bigserial,
|
|
"month_idx" smallint,
|
|
"afc_server" serial,
|
|
"rx_time" timestamptz,
|
|
"tx_time" timestamptz,
|
|
"rx_envelope_digest" uuid,
|
|
"tx_envelope_digest" uuid,
|
|
PRIMARY KEY ("message_id", "month_idx")
|
|
);
|
|
|
|
CREATE TABLE "rx_envelope" (
|
|
"rx_envelope_digest" uuid,
|
|
"month_idx" smallint,
|
|
"envelope_json" json,
|
|
PRIMARY KEY ("rx_envelope_digest", "month_idx")
|
|
);
|
|
|
|
CREATE TABLE "tx_envelope" (
|
|
"tx_envelope_digest" uuid,
|
|
"month_idx" smallint,
|
|
"envelope_json" json,
|
|
PRIMARY KEY ("tx_envelope_digest", "month_idx")
|
|
);
|
|
|
|
CREATE TABLE "request_response_in_message" (
|
|
"message_id" bigint,
|
|
"request_id" text,
|
|
"month_idx" smallint,
|
|
"request_response_digest" uuid,
|
|
"expire_time" timestamptz,
|
|
PRIMARY KEY ("message_id", "request_id", "month_idx")
|
|
);
|
|
|
|
CREATE TABLE "request_response" (
|
|
"request_response_digest" uuid,
|
|
"month_idx" smallint,
|
|
"afc_config_text_digest" uuid,
|
|
"customer_id" integer,
|
|
"uls_data_version_id" integer,
|
|
"geo_data_version_id" integer,
|
|
"request_json_digest" uuid,
|
|
"response_json_digest" uuid,
|
|
"device_descriptor_digest" uuid,
|
|
"location_digest" uuid,
|
|
"response_code" int,
|
|
"response_description" text,
|
|
"response_data" text,
|
|
PRIMARY KEY ("request_response_digest", "month_idx")
|
|
);
|
|
|
|
CREATE TABLE "device_descriptor" (
|
|
"device_descriptor_digest" uuid,
|
|
"month_idx" smallint,
|
|
"serial_number" text,
|
|
"certifications_digest" uuid,
|
|
PRIMARY KEY ("device_descriptor_digest", "month_idx")
|
|
);
|
|
|
|
CREATE TABLE "certification" (
|
|
"certifications_digest" uuid,
|
|
"certification_index" smallint,
|
|
"month_idx" smallint,
|
|
"ruleset_id" text,
|
|
"certification_id" text,
|
|
PRIMARY KEY ("certifications_digest", "certification_index", "month_idx")
|
|
);
|
|
|
|
CREATE TABLE "compressed_json" (
|
|
"compressed_json_digest" uuid,
|
|
"month_idx" smallint,
|
|
"compressed_json_data" bytea,
|
|
PRIMARY KEY ("compressed_json_digest", "month_idx")
|
|
);
|
|
|
|
CREATE TABLE "customer" (
|
|
"customer_id" serial,
|
|
"month_idx" smallint,
|
|
"customer_name" text,
|
|
PRIMARY KEY ("customer_id", "month_idx")
|
|
);
|
|
|
|
CREATE TABLE "location" (
|
|
"location_digest" uuid,
|
|
"month_idx" smallint,
|
|
"location_wgs84" geography(POINT,4326),
|
|
"location_uncertainty_m" real,
|
|
"location_type" text,
|
|
"deployment_type" int,
|
|
"height_m" real,
|
|
"height_uncertainty_m" real,
|
|
"height_type" text,
|
|
PRIMARY KEY ("location_digest", "month_idx")
|
|
);
|
|
|
|
CREATE TABLE "afc_config" (
|
|
"afc_config_text_digest" uuid,
|
|
"month_idx" smallint,
|
|
"afc_config_text" text,
|
|
"afc_config_json" json,
|
|
PRIMARY KEY ("afc_config_text_digest", "month_idx")
|
|
);
|
|
|
|
CREATE TABLE "geo_data_version" (
|
|
"geo_data_version_id" serial,
|
|
"month_idx" smallint,
|
|
"geo_data_version" text,
|
|
PRIMARY KEY ("geo_data_version_id", "month_idx")
|
|
);
|
|
|
|
CREATE TABLE "uls_data_version" (
|
|
"uls_data_version_id" serial,
|
|
"month_idx" smallint,
|
|
"uls_data_version" text,
|
|
PRIMARY KEY ("uls_data_version_id", "month_idx")
|
|
);
|
|
|
|
CREATE TABLE "max_psd" (
|
|
"request_response_digest" uuid,
|
|
"month_idx" smallint,
|
|
"low_frequency_mhz" smallint,
|
|
"high_frequency_mhz" smallint,
|
|
"max_psd_dbm_mhz" real,
|
|
PRIMARY KEY ("request_response_digest", "month_idx", "low_frequency_mhz", "high_frequency_mhz")
|
|
);
|
|
|
|
CREATE TABLE "max_eirp" (
|
|
"request_response_digest" uuid,
|
|
"month_idx" smallint,
|
|
"op_class" smallint,
|
|
"channel" smallint,
|
|
"max_eirp_dbm" real,
|
|
PRIMARY KEY ("request_response_digest", "month_idx", "op_class", "channel")
|
|
);
|
|
|
|
CREATE TABLE "afc_server" (
|
|
"afc_server_id" serial,
|
|
"month_idx" smallint,
|
|
"afc_server_name" text,
|
|
PRIMARY KEY ("afc_server_id", "month_idx")
|
|
);
|
|
|
|
CREATE TABLE "decode_error" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"time" timestamptz,
|
|
"msg" text,
|
|
"code_line" integer,
|
|
"data" text,
|
|
"month_idx" smallint
|
|
);
|
|
|
|
CREATE INDEX ON "afc_message" ("rx_time");
|
|
|
|
CREATE INDEX ON "afc_message" ("tx_time");
|
|
|
|
CREATE INDEX ON "rx_envelope" USING HASH ("rx_envelope_digest");
|
|
|
|
CREATE INDEX ON "tx_envelope" USING HASH ("tx_envelope_digest");
|
|
|
|
CREATE INDEX ON "request_response_in_message" ("request_id");
|
|
|
|
CREATE INDEX ON "request_response_in_message" ("request_response_digest");
|
|
|
|
CREATE INDEX ON "request_response_in_message" ("expire_time");
|
|
|
|
CREATE INDEX ON "request_response" USING HASH ("request_response_digest");
|
|
|
|
CREATE INDEX ON "request_response" ("afc_config_text_digest");
|
|
|
|
CREATE INDEX ON "request_response" ("customer_id");
|
|
|
|
CREATE INDEX ON "request_response" ("device_descriptor_digest");
|
|
|
|
CREATE INDEX ON "request_response" ("location_digest");
|
|
|
|
CREATE INDEX ON "request_response" ("response_code");
|
|
|
|
CREATE INDEX ON "request_response" ("response_description");
|
|
|
|
CREATE INDEX ON "request_response" ("response_data");
|
|
|
|
CREATE INDEX ON "device_descriptor" USING HASH ("device_descriptor_digest");
|
|
|
|
CREATE INDEX ON "device_descriptor" ("serial_number");
|
|
|
|
CREATE INDEX ON "device_descriptor" ("certifications_digest");
|
|
|
|
CREATE INDEX ON "certification" USING HASH ("certifications_digest");
|
|
|
|
CREATE INDEX ON "certification" ("ruleset_id");
|
|
|
|
CREATE INDEX ON "certification" ("certification_id");
|
|
|
|
CREATE INDEX ON "compressed_json" USING HASH ("compressed_json_digest");
|
|
|
|
CREATE INDEX ON "customer" ("customer_name");
|
|
|
|
CREATE INDEX ON "location" USING HASH ("location_digest");
|
|
|
|
CREATE INDEX ON "location" ("location_wgs84");
|
|
|
|
CREATE INDEX ON "location" ("location_type");
|
|
|
|
CREATE INDEX ON "location" ("height_m");
|
|
|
|
CREATE INDEX ON "location" ("height_type");
|
|
|
|
CREATE INDEX ON "afc_config" USING HASH ("afc_config_text_digest");
|
|
|
|
CREATE UNIQUE INDEX ON "geo_data_version" ("geo_data_version", "month_idx");
|
|
|
|
CREATE INDEX ON "geo_data_version" ("geo_data_version");
|
|
|
|
CREATE UNIQUE INDEX ON "uls_data_version" ("uls_data_version", "month_idx");
|
|
|
|
CREATE INDEX ON "uls_data_version" ("uls_data_version");
|
|
|
|
CREATE INDEX ON "max_psd" USING HASH ("request_response_digest");
|
|
|
|
CREATE INDEX ON "max_psd" ("low_frequency_mhz");
|
|
|
|
CREATE INDEX ON "max_psd" ("high_frequency_mhz");
|
|
|
|
CREATE INDEX ON "max_psd" ("max_psd_dbm_mhz");
|
|
|
|
CREATE INDEX ON "max_eirp" USING HASH ("request_response_digest");
|
|
|
|
CREATE INDEX ON "max_eirp" ("op_class");
|
|
|
|
CREATE INDEX ON "max_eirp" ("channel");
|
|
|
|
CREATE INDEX ON "max_eirp" ("max_eirp_dbm");
|
|
|
|
CREATE UNIQUE INDEX ON "afc_server" ("afc_server_name", "month_idx");
|
|
|
|
CREATE INDEX ON "afc_server" ("afc_server_name");
|
|
|
|
COMMENT ON TABLE "afc_message" IS 'AFC Request/Response message pair (contain individual requests/responses)';
|
|
|
|
COMMENT ON COLUMN "afc_message"."rx_envelope_digest" IS 'Envelope of AFC Request message';
|
|
|
|
COMMENT ON COLUMN "afc_message"."tx_envelope_digest" IS 'Envelope of AFC Response message';
|
|
|
|
COMMENT ON TABLE "rx_envelope" IS 'Envelope (constant part) of AFC Request Message';
|
|
|
|
COMMENT ON COLUMN "rx_envelope"."rx_envelope_digest" IS 'MD5 of envelope_json field in UTF8 encoding';
|
|
|
|
COMMENT ON COLUMN "rx_envelope"."envelope_json" IS 'AFC Request JSON with empty availableSpectrumInquiryRequests field';
|
|
|
|
COMMENT ON TABLE "tx_envelope" IS 'Envelope (constant part) of AFC Response Message';
|
|
|
|
COMMENT ON COLUMN "tx_envelope"."tx_envelope_digest" IS 'MD5 of envelope_json field in UTF8 encoding';
|
|
|
|
COMMENT ON COLUMN "tx_envelope"."envelope_json" IS 'AFC Response JSON with empty availableSpectrumInquiryRequests field';
|
|
|
|
COMMENT ON TABLE "request_response_in_message" IS 'Associative table for relatonship between AFC Request/Response messages and individual requests/responses. Also encapsulates variable part of requests/responses';
|
|
|
|
COMMENT ON COLUMN "request_response_in_message"."message_id" IS 'AFC request/response message pair this request/response belongs';
|
|
|
|
COMMENT ON COLUMN "request_response_in_message"."request_id" IS 'ID of request/response within message';
|
|
|
|
COMMENT ON COLUMN "request_response_in_message"."request_response_digest" IS 'Reference to otentially constant part of request/response';
|
|
|
|
COMMENT ON COLUMN "request_response_in_message"."expire_time" IS 'Response expiration time';
|
|
|
|
COMMENT ON TABLE "request_response" IS 'Potentiially constant part of request/response';
|
|
|
|
COMMENT ON COLUMN "request_response"."request_response_digest" IS 'MD5 computed over request/response with requestId and availabilityExpireTime fields set to empty';
|
|
|
|
COMMENT ON COLUMN "request_response"."afc_config_text_digest" IS 'MD5 over used AFC Config text represnetation';
|
|
|
|
COMMENT ON COLUMN "request_response"."customer_id" IS 'AP vendor';
|
|
|
|
COMMENT ON COLUMN "request_response"."uls_data_version_id" IS 'Version of used ULS data';
|
|
|
|
COMMENT ON COLUMN "request_response"."geo_data_version_id" IS 'Version of used geospatial data';
|
|
|
|
COMMENT ON COLUMN "request_response"."request_json_digest" IS 'MD5 of request JSON with empty requestId';
|
|
|
|
COMMENT ON COLUMN "request_response"."response_json_digest" IS 'MD5 of resaponse JSON with empty requesatId and availabilityExpireTime';
|
|
|
|
COMMENT ON COLUMN "request_response"."device_descriptor_digest" IS 'MD5 of device descriptor (AP) related part of request JSON';
|
|
|
|
COMMENT ON COLUMN "request_response"."location_digest" IS 'MD5 of location-related part of request JSON';
|
|
|
|
COMMENT ON COLUMN "request_response"."response_description" IS 'Optional response code short description. Null for success';
|
|
|
|
COMMENT ON COLUMN "request_response"."response_data" IS 'Optional supplemental failure information. Optional comma-separated list of missing/invalid/unexpected parameters, etc.';
|
|
|
|
COMMENT ON TABLE "device_descriptor" IS 'Information about device (e.g. AP)';
|
|
|
|
COMMENT ON COLUMN "device_descriptor"."device_descriptor_digest" IS 'MD5 over parts of requesat JSON pertinent to AP';
|
|
|
|
COMMENT ON COLUMN "device_descriptor"."serial_number" IS 'AP serial number';
|
|
|
|
COMMENT ON COLUMN "device_descriptor"."certifications_digest" IS 'Device certifications';
|
|
|
|
COMMENT ON TABLE "certification" IS 'Element of certifications list';
|
|
|
|
COMMENT ON COLUMN "certification"."certifications_digest" IS 'MD5 of certification list json';
|
|
|
|
COMMENT ON COLUMN "certification"."certification_index" IS 'Index in certification list';
|
|
|
|
COMMENT ON COLUMN "certification"."ruleset_id" IS 'Name of rules for which AP certified (equivalent of region)';
|
|
|
|
COMMENT ON COLUMN "certification"."certification_id" IS 'ID of certification (equivalent of manufacturer)';
|
|
|
|
COMMENT ON TABLE "compressed_json" IS 'Compressed body of request or response';
|
|
|
|
COMMENT ON COLUMN "compressed_json"."compressed_json_digest" IS 'MD5 hash of compressed data';
|
|
|
|
COMMENT ON COLUMN "compressed_json"."compressed_json_data" IS 'Compressed data';
|
|
|
|
COMMENT ON TABLE "customer" IS 'Customer aka vendor aka user';
|
|
|
|
COMMENT ON COLUMN "customer"."customer_name" IS 'Its name';
|
|
|
|
COMMENT ON TABLE "location" IS 'AP location';
|
|
|
|
COMMENT ON COLUMN "location"."location_digest" IS 'MD5 computed over location part of request JSON';
|
|
|
|
COMMENT ON COLUMN "location"."location_wgs84" IS 'AP area center (WGS84 coordinates)';
|
|
|
|
COMMENT ON COLUMN "location"."location_uncertainty_m" IS 'Radius of AP uncertainty area in meters';
|
|
|
|
COMMENT ON COLUMN "location"."location_type" IS 'Ellipse/LinearPolygon/RadialPolygon';
|
|
|
|
COMMENT ON COLUMN "location"."deployment_type" IS '0/1/2 for unknown/indoor/outdoor';
|
|
|
|
COMMENT ON COLUMN "location"."height_m" IS 'AP elevation in meters';
|
|
|
|
COMMENT ON COLUMN "location"."height_uncertainty_m" IS 'Elevation uncertainty in meters';
|
|
|
|
COMMENT ON COLUMN "location"."height_type" IS 'Elevation type';
|
|
|
|
COMMENT ON TABLE "afc_config" IS 'AFC Config';
|
|
|
|
COMMENT ON COLUMN "afc_config"."afc_config_text_digest" IS 'MD5 computed over text representation';
|
|
|
|
COMMENT ON COLUMN "afc_config"."afc_config_text" IS 'Text representation of AFC Config';
|
|
|
|
COMMENT ON COLUMN "afc_config"."afc_config_json" IS 'JSON representation of AFC Config';
|
|
|
|
COMMENT ON TABLE "geo_data_version" IS 'Version of geospatial data';
|
|
|
|
COMMENT ON TABLE "uls_data_version" IS 'Version of ULS data"';
|
|
|
|
COMMENT ON TABLE "max_psd" IS 'PSD result';
|
|
|
|
COMMENT ON COLUMN "max_psd"."request_response_digest" IS 'Request this result belongs to';
|
|
|
|
COMMENT ON TABLE "max_eirp" IS 'EIRP result';
|
|
|
|
COMMENT ON COLUMN "max_eirp"."request_response_digest" IS 'Request this result belongs to';
|
|
|
|
ALTER TABLE "afc_message" ADD CONSTRAINT "afc_message_afc_server_ref" FOREIGN KEY ("afc_server", "month_idx") REFERENCES "afc_server" ("afc_server_id", "month_idx");
|
|
|
|
ALTER TABLE "afc_message" ADD CONSTRAINT "afc_message_rx_envelope_digest_ref" FOREIGN KEY ("rx_envelope_digest", "month_idx") REFERENCES "rx_envelope" ("rx_envelope_digest", "month_idx");
|
|
|
|
ALTER TABLE "afc_message" ADD CONSTRAINT "afc_message_tx_envelope_digest_ref" FOREIGN KEY ("tx_envelope_digest", "month_idx") REFERENCES "tx_envelope" ("tx_envelope_digest", "month_idx");
|
|
|
|
ALTER TABLE "request_response_in_message" ADD CONSTRAINT "request_response_in_message_message_id_ref" FOREIGN KEY ("message_id", "month_idx") REFERENCES "afc_message" ("message_id", "month_idx");
|
|
|
|
ALTER TABLE "request_response_in_message" ADD CONSTRAINT "request_response_in_message_request_response_digest_ref" FOREIGN KEY ("request_response_digest", "month_idx") REFERENCES "request_response" ("request_response_digest", "month_idx");
|
|
|
|
ALTER TABLE "request_response" ADD CONSTRAINT "request_response_afc_config_text_digest_ref" FOREIGN KEY ("afc_config_text_digest", "month_idx") REFERENCES "afc_config" ("afc_config_text_digest", "month_idx");
|
|
|
|
ALTER TABLE "request_response" ADD CONSTRAINT "request_response_customer_id_ref" FOREIGN KEY ("customer_id", "month_idx") REFERENCES "customer" ("customer_id", "month_idx");
|
|
|
|
ALTER TABLE "request_response" ADD CONSTRAINT "request_response_uls_data_version_id_ref" FOREIGN KEY ("uls_data_version_id", "month_idx") REFERENCES "uls_data_version" ("uls_data_version_id", "month_idx");
|
|
|
|
ALTER TABLE "request_response" ADD CONSTRAINT "request_response_geo_data_version_id_ref" FOREIGN KEY ("geo_data_version_id", "month_idx") REFERENCES "geo_data_version" ("geo_data_version_id", "month_idx");
|
|
|
|
ALTER TABLE "request_response" ADD CONSTRAINT "request_response_request_json_digest_ref" FOREIGN KEY ("request_json_digest", "month_idx") REFERENCES "compressed_json" ("compressed_json_digest", "month_idx");
|
|
|
|
ALTER TABLE "request_response" ADD CONSTRAINT "request_response_response_json_digest_ref" FOREIGN KEY ("response_json_digest", "month_idx") REFERENCES "compressed_json" ("compressed_json_digest", "month_idx");
|
|
|
|
ALTER TABLE "request_response" ADD CONSTRAINT "request_response_device_descriptor_digest_ref" FOREIGN KEY ("device_descriptor_digest", "month_idx") REFERENCES "device_descriptor" ("device_descriptor_digest", "month_idx");
|
|
|
|
ALTER TABLE "request_response" ADD CONSTRAINT "request_response_location_digest_ref" FOREIGN KEY ("location_digest", "month_idx") REFERENCES "location" ("location_digest", "month_idx");
|
|
|
|
|
|
ALTER TABLE "max_psd" ADD CONSTRAINT "max_psd_request_response_digest_ref" FOREIGN KEY ("request_response_digest", "month_idx") REFERENCES "request_response" ("request_response_digest", "month_idx");
|
|
|
|
ALTER TABLE "max_eirp" ADD CONSTRAINT "max_eirp_request_response_digest_ref" FOREIGN KEY ("request_response_digest", "month_idx") REFERENCES "request_response" ("request_response_digest", "month_idx");
|
|
; |