90 lines
3.2 KiB
PL/PgSQL
90 lines
3.2 KiB
PL/PgSQL
BEGIN;
|
|
|
|
SET ROLE situation_role;
|
|
|
|
CREATE TABLE IF NOT EXISTS situation.projects (
|
|
project_id TEXT PRIMARY KEY,
|
|
display_name TEXT NOT NULL,
|
|
recorded_at_unix_millis BIGINT NOT NULL,
|
|
CHECK (project_id <> ''),
|
|
CHECK (display_name <> '')
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS situation.project_aliases (
|
|
project_id TEXT NOT NULL,
|
|
alias TEXT NOT NULL,
|
|
recorded_at_unix_millis BIGINT NOT NULL,
|
|
PRIMARY KEY (project_id, alias),
|
|
CHECK (project_id <> ''),
|
|
CHECK (alias <> '')
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS situation.active_project_focus (
|
|
actor_id TEXT NOT NULL,
|
|
principal_id TEXT NOT NULL,
|
|
project_id TEXT NOT NULL,
|
|
project_display_name TEXT NOT NULL,
|
|
provenance TEXT NOT NULL,
|
|
confidence_millis BIGINT NOT NULL,
|
|
source_utterance_id TEXT NOT NULL,
|
|
updated_at_unix_millis BIGINT NOT NULL,
|
|
PRIMARY KEY (actor_id, principal_id),
|
|
CHECK (provenance IN ('EXPLICIT','INHERITED_FOCUS','ENTITY_REFERENCE','TASK_REFERENCE','REPOSITORY_REFERENCE','INFERRED','USER_CONFIRMED','AMBIGUOUS')),
|
|
CHECK (confidence_millis BETWEEN 0 AND 1000)
|
|
);
|
|
|
|
INSERT INTO situation.projects(project_id, display_name, recorded_at_unix_millis)
|
|
VALUES ('assistant', 'Assistant', 1), ('upo', 'УПО', 1)
|
|
ON CONFLICT (project_id) DO NOTHING;
|
|
|
|
INSERT INTO situation.project_aliases(project_id, alias, recorded_at_unix_millis)
|
|
VALUES
|
|
('assistant', 'assistant', 1),
|
|
('assistant', 'домовой', 1),
|
|
('assistant', 'ассистент', 1),
|
|
('upo', 'упо', 1),
|
|
('upo', 'upo', 1)
|
|
ON CONFLICT (project_id, alias) DO NOTHING;
|
|
|
|
RESET ROLE;
|
|
|
|
SET ROLE interaction_role;
|
|
|
|
ALTER TABLE interaction.relations DROP CONSTRAINT IF EXISTS relations_kind_check;
|
|
ALTER TABLE interaction.relations
|
|
ADD CONSTRAINT relations_kind_check
|
|
CHECK (kind IN ('CORRECTS','RESPONDS_TO','REQUESTS_COGNITION','COGNITION_FALLBACK','SUPERSEDES','CLARIFIES'));
|
|
|
|
CREATE TABLE IF NOT EXISTS interaction.utterance_interpretations (
|
|
interpretation_id TEXT PRIMARY KEY,
|
|
utterance_id TEXT NOT NULL,
|
|
speech_act TEXT NOT NULL,
|
|
confidence_millis BIGINT NOT NULL,
|
|
source TEXT NOT NULL,
|
|
assertion_verification TEXT NOT NULL,
|
|
requested_outcome TEXT NOT NULL DEFAULT '',
|
|
clarification TEXT NOT NULL,
|
|
recorded_at_unix_millis BIGINT NOT NULL,
|
|
CHECK (speech_act IN ('DIRECTIVE','ASSERTION','DECISION','PREFERENCE','QUESTION','HYPOTHESIS','CORRECTION','REQUEST_TO_INVESTIGATE','CONFIRMATION','REJECTION','CANCELLATION','CONVERSATIONAL','UNKNOWN')),
|
|
CHECK (confidence_millis BETWEEN 0 AND 1000),
|
|
CHECK (assertion_verification IN ('UNVERIFIED','VERIFIED','REJECTED','NOT_APPLICABLE')),
|
|
CHECK (clarification IN ('NOT_REQUIRED','REQUIRED'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS interaction.project_references (
|
|
project_reference_id TEXT PRIMARY KEY,
|
|
utterance_id TEXT NOT NULL,
|
|
project_id TEXT NOT NULL,
|
|
project_display_name TEXT NOT NULL,
|
|
role TEXT NOT NULL,
|
|
provenance TEXT NOT NULL,
|
|
confidence_millis BIGINT NOT NULL,
|
|
recorded_at_unix_millis BIGINT NOT NULL,
|
|
CHECK (role IN ('PRIMARY','RELATED','REFERENCE_SOURCE')),
|
|
CHECK (provenance IN ('EXPLICIT','INHERITED_FOCUS','ENTITY_REFERENCE','TASK_REFERENCE','REPOSITORY_REFERENCE','INFERRED','USER_CONFIRMED','AMBIGUOUS')),
|
|
CHECK (confidence_millis BETWEEN 0 AND 1000)
|
|
);
|
|
|
|
RESET ROLE;
|
|
COMMIT;
|