App-RoboBot

 view release on metacpan or  search on metacpan

share/migrations/deploy/base.sql  view on Meta::CPAN

    name        text not null,
    created_at  timestamp with time zone not null default now(),
    updated_at  timestamp with time zone,

    PRIMARY KEY (id)
);
CREATE UNIQUE INDEX ON robobot.networks (lower(name));

CREATE TABLE robobot.channels (
    id          serial not null,
    network_id  integer not null references robobot.networks (id) on update cascade on delete cascade,
    name        text not null,
    log_enabled boolean not null default true,
    extradata   jsonb not null default '{}'::jsonb,
    created_at  timestamp with time zone not null default now(),
    updated_at  timestamp with time zone,

    PRIMARY KEY (id)
);
CREATE UNIQUE INDEX ON robobot.channels (network_id, lower(name));

share/migrations/deploy/p-achievements-20161128144040.sql  view on Meta::CPAN

-- Deploy robobot:p-achievements-20161128144040 to pg
-- requires: base

BEGIN;

CREATE TABLE robobot.achievements (
    id          serial not null,
    name        text not null,
    description text not null,
    query       text not null,
    created_by  integer not null references robobot.nicks (id) on update cascade on delete cascade,
    created_at  timestamp with time zone not null default now(),

    PRIMARY KEY (id)
);
CREATE UNIQUE INDEX ON robobot.achievements (lower(name));
CREATE INDEX ON robobot.achievements (created_by);

CREATE TABLE robobot.achievement_nicks (
    achievement_id  integer not null references robobot.achievements (id) on update cascade on delete cascade,
    nick_id         integer not null references robobot.nicks (id) on update cascade on delete cascade,
    created_at      timestamp with time zone not null default now(),

    PRIMARY KEY (achievement_id, nick_id)
);
CREATE INDEX ON robobot.achievement_nicks (nick_id);

COMMIT;

share/migrations/deploy/p-alarms-20161128153112.sql  view on Meta::CPAN

-- Deploy robobot:p-alarms-20161128153112 to pg
-- requires: base

BEGIN;

CREATE TABLE robobot.alarms_alarms (
    id              serial not null,
    channel_id      integer not null references robobot.channels (id) on update cascade on delete cascade,
    name            text not null,
    next_emit       timestamp with time zone not null,
    recurrence      interval,
    exclusions      jsonb not null default '{}'::jsonb,
    is_expression   boolean not null default false,
    is_suspended    boolean not null default false,
    message         text,
    created_by      integer not null references robobot.nicks (id) on update cascade on delete cascade,
    created_at      timestamp with time zone not null default now(),

    PRIMARY KEY (id)
);
CREATE UNIQUE INDEX ON robobot.alarms_alarms (channel_id, lower(name));
CREATE INDEX ON robobot.alarms_alarms (created_by);

COMMIT;

share/migrations/deploy/p-auth-20161128164909.sql  view on Meta::CPAN

-- Deploy robobot:p-auth-20161128164909 to pg
-- requires: base

BEGIN;

CREATE TABLE robobot.auth_permissions (
    permission_id   serial not null,
    network_id      integer not null references robobot.networks (id) on update cascade on delete cascade,
    nick_id         integer references robobot.nicks (id) on update cascade on delete cascade,
    command         text not null,
    state           text not null,
    granted_by      integer not null references robobot.nicks (id) on update cascade on delete restrict,
    created_at      timestamp with time zone not null default now(),
    updated_at      timestamp with time zone,

    PRIMARY KEY (permission_id)
);
CREATE UNIQUE INDEX ON robobot.auth_permissions (network_id, nick_id, lower(command));
CREATE INDEX ON robobot.auth_permissions (nick_id);
CREATE INDEX ON robobot.auth_permissions (lower(command));
CREATE INDEX ON robobot.auth_permissions (granted_by);

share/migrations/deploy/p-autoreply-20161128171050.sql  view on Meta::CPAN

-- Deploy robobot:p-autoreply-20161128171050 to pg
-- requires: base

BEGIN;

CREATE TABLE robobot.autoreply_autoreplies (
    id          serial not null,
    channel_id  integer not null references robobot.channels (id) on update cascade on delete cascade,
    name        text not null,
    condition   text not null,
    response    text not null,
    created_by  integer not null references robobot.nicks (id) on update cascade on delete cascade,
    created_at  timestamp with time zone not null default now(),

    PRIMARY KEY (id)
);
CREATE UNIQUE INDEX ON robobot.autoreply_autoreplies (channel_id, lower(name));
CREATE INDEX ON robobot.autoreply_autoreplies (channel_id);
CREATE INDEX ON robobot.autoreply_autoreplies (created_by);

COMMIT;

share/migrations/deploy/p-channellink-20161128173743.sql  view on Meta::CPAN

-- Deploy robobot:p-channellink-20161128173743 to pg
-- requires: base

BEGIN;

CREATE TABLE robobot.channel_links (
    parent_channel_id   integer not null references robobot.channels (id) on update cascade on delete cascade,
    child_channel_id    integer not null references robobot.channels (id) on update cascade on delete cascade,
    created_by          integer not null references robobot.nicks (id) on update cascade on delete cascade,
    created_at          timestamp with time zone not null default now(),

    PRIMARY KEY (parent_channel_id, child_channel_id),

    CONSTRAINT "no same-channel linking" CHECK (parent_channel_id != child_channel_id)
);
CREATE INDEX ON robobot.channel_links (child_channel_id);
CREATE INDEX ON robobot.channel_links (created_by);

COMMIT;

share/migrations/deploy/p-factoids-20161128182038.sql  view on Meta::CPAN

-- Deploy robobot:p-factoids-20161128182038 to pg
-- requires: base

BEGIN;

CREATE TABLE robobot.factoids (
    id          serial not null,
    network_id  integer not null references robobot.networks (id) on update cascade on delete cascade,
    name        text not null,
    factoid     text not null,
    terms       tsvector not null,
    created_by  integer not null references robobot.nicks (id) on update cascade on delete cascade,
    created_at  timestamp with time zone not null default now(),
    updated_by  integer references robobot.nicks (id) on update cascade on delete set null,
    updated_at  timestamp with time zone,

    PRIMARY KEY (id)
);
CREATE UNIQUE INDEX ON robobot.factoids (network_id, lower(name));
CREATE INDEX ON robobot.factoids (lower(name));
CREATE INDEX ON robobot.factoids (created_by);
CREATE INDEX ON robobot.factoids (updated_by);

COMMIT;

share/migrations/deploy/p-fakequotes-20161128183237.sql  view on Meta::CPAN

-- Deploy robobot:p-fakequotes-20161128183237 to pg
-- requires: base

BEGIN;

CREATE TABLE robobot.fakequotes_people (
    id          serial not null,
    network_id  integer not null references robobot.networks (id) on update cascade on delete cascade,
    name        text not null,

    PRIMARY KEY (id)
);
CREATE UNIQUE INDEX ON robobot.fakequotes_people (network_id, lower(name));

CREATE TABLE robobot.fakequotes_phrases (
    id          serial not null,
    person_id   integer not null references robobot.fakequotes_people (id) on update cascade on delete cascade,
    phrase      text not null,

    PRIMARY KEY (id)
);
CREATE INDEX ON robobot.fakequotes_phrases (person_id);

CREATE TABLE robobot.fakequotes_terms (
    id          serial not null,
    person_id   integer not null references robobot.fakequotes_people (id) on update cascade on delete cascade,
    term_type   text not null,
    term        text not null,

    PRIMARY KEY (id)
);
CREATE INDEX ON robobot.fakequotes_terms (person_id);
CREATE INDEX ON robobot.fakequotes_terms (lower(term_type));

COMMIT;

share/migrations/deploy/p-github-20161128190436.sql  view on Meta::CPAN

    last_issue  integer,
    created_at  timestamp with time zone not null default now(),
    polled_at   timestamp with time zone,

    PRIMARY KEY (repo_id)
);
CREATE UNIQUE INDEX ON robobot.github_repos (owner_name, repo_name);
CREATE INDEX ON robobot.github_repos (polled_at);

CREATE TABLE robobot.github_repo_channels (
    repo_id     integer not null references robobot.github_repos (repo_id) on update cascade on delete cascade,
    channel_id  integer not null references robobot.channels (id) on update cascade on delete cascade,

    PRIMARY KEY (repo_id, channel_id)
);
CREATE INDEX ON robobot.github_repo_channels (channel_id);

COMMIT;

share/migrations/deploy/p-karma-20161128200656.sql  view on Meta::CPAN

-- Deploy robobot:p-karma-20161128200656 to pg
-- requires: base

BEGIN;

CREATE TABLE karma_karma (
    id              serial not null,
    nick_id         integer not null references robobot.nicks (id) on update cascade on delete cascade,
    karma           integer not null,
    from_nick_id    integer not null references robobot.nicks (id) on update cascade on delete cascade,
    created_at      timestamp with time zone not null default now(),

    PRIMARY KEY (id)
);
CREATE INDEX ON robobot.karma_karma (nick_id);
CREATE INDEX ON robobot.karma_karma (from_nick_id);

COMMIT;

share/migrations/deploy/p-location-20161128204426.sql  view on Meta::CPAN

-- Deploy robobot:p-location-20161128204426 to pg
-- requires: base

BEGIN;

CREATE TABLE robobot.locations (
    location_id serial not null,
    network_id  integer not null references robobot.networks (id) on update cascade on delete cascade,
    nick_id     integer not null references robobot.nicks (id) on update cascade on delete cascade,
    loc_name    text not null,
    loc_message text,
    created_at  timestamp with time zone not null default now(),

    PRIMARY KEY (location_id)
);
CREATE INDEX ON robobot.locations (network_id);
CREATE INDEX ON robobot.locations (nick_id);
CREATE INDEX ON robobot.locations (created_at);

share/migrations/deploy/p-logger-20161128205533.sql  view on Meta::CPAN

-- Deploy robobot:p-logger-20161128205533 to pg
-- requires: base

BEGIN;

CREATE TABLE robobot.logger_log (
    id              serial not null,
    channel_id      integer not null references robobot.channels (id) on update cascade on delete cascade,
    nick_id         integer not null references robobot.nicks (id) on update cascade on delete cascade,
    message         text not null,
    has_expression  boolean not null default false,
    posted_at       timestamp with time zone not null default now(),

    PRIMARY KEY (id)
);
CREATE INDEX ON robobot.logger_log (channel_id, posted_at);
CREATE INDEX ON robobot.logger_log (nick_id, posted_at);

COMMIT;

share/migrations/deploy/p-macros-20161128210159.sql  view on Meta::CPAN

-- Deploy robobot:p-macros-20161128210159 to pg
-- requires: base

BEGIN;

CREATE TABLE robobot.macros (
    macro_id    serial not null,
    network_id  integer not null references robobot.networks (id) on update cascade on delete restrict,
    name        text not null,
    arguments   jsonb not null default '{}'::jsonb,
    definition  text not null,
    is_locked   boolean not null default false,
    defined_by  integer not null references robobot.nicks (id) on update cascade on delete restrict,
    defined_at  timestamp with time zone not null default now(),

    PRIMARY KEY (macro_id)
);
CREATE UNIQUE INDEX ON robobot.macros (network_id, lower(name));
CREATE INDEX ON robobot.macros (defined_by);

COMMIT;

share/migrations/deploy/p-madlibs-20161128211213.sql  view on Meta::CPAN

-- Deploy robobot:p-madlibs-20161128211213 to pg
-- requires: base

BEGIN;

CREATE TABLE robobot.madlibs_madlibs (
    id              serial not null,
    madlib          text not null,
    placeholders    text[] not null,
    created_by      integer not null references robobot.nicks (id) on update cascade on delete restrict,
    created_at      timestamp with time zone not null default now(),

    PRIMARY KEY (id)
);
CREATE INDEX ON robobot.madlibs_madlibs (created_by);

CREATE TABLE robobot.madlibs_results (
    id              serial not null,
    madlib_id       integer not null references robobot.madlibs_madlibs (id) on update cascade on delete restrict,
    network_id      integer not null references robobot.networks (id) on update cascade on delete restrict,
    nick_id         integer not null references robobot.nicks (id) on update cascade on delete cascade,
    words           text[],
    filled_in       text,
    started_at      timestamp with time zone not null default now(),
    completed_at    timestamp with time zone,

    PRIMARY KEY (id)
);
CREATE INDEX ON robobot.madlibs_results (madlib_id);
CREATE INDEX ON robobot.madlibs_results (network_id, completed_at);
CREATE INDEX ON robobot.madlibs_results (nick_id);

share/migrations/deploy/p-markov-20161128212720.sql  view on Meta::CPAN

-- Deploy robobot:p-markov-20161128212720 to pg
-- requires: base

BEGIN;

CREATE TABLE robobot.markov_phrases (
    id          serial not null,
    nick_id     integer not null references robobot.nicks (id) on update cascade on delete cascade,
    structure   text not null,
    phrase      text not null,
    used_count  integer not null default 1,
    created_at  timestamp with time zone not null default now(),
    updated_at  timestamp with time zone,

    PRIMARY KEY (id)
);
CREATE INDEX ON robobot.markov_phrases (nick_id);
CREATE INDEX ON robobot.markov_phrases (structure);

CREATE TABLE robobot.markov_sentence_forms (
    id              serial not null,
    nick_id         integer not null references robobot.nicks (id) on update cascade on delete cascade,
    structure       text not null,
    structure_jsonb jsonb,
    used_count      integer not null default 1,
    created_at      timestamp with time zone not null default now(),
    updated_at      timestamp with time zone,

    PRIMARY KEY (id)
);
CREATE INDEX ON robobot.markov_sentence_forms (nick_id);

CREATE TABLE robobot.markov_neighbors (
    phrase_id   integer not null references robobot.markov_phrases (id) on update cascade on delete cascade,
    neighbor_id integer not null references robobot.markov_phrases (id) on update cascade on delete cascade,
    occurrences integer not null default 1,
    created_at  timestamp with time zone not null default now(),
    updated_at  timestamp with time zone,

    PRIMARY KEY (phrase_id, neighbor_id)
);
CREATE INDEX ON robobot.markov_neighbors (neighbor_id);

COMMIT;

share/migrations/deploy/p-memos-20161128214110.sql  view on Meta::CPAN

-- Deploy robobot:p-memos-20161128214110 to pg
-- requires: base

BEGIN;

CREATE TABLE robobot.memo_memos (
    memo_id         serial not null,
    from_nick_id    integer not null references robobot.nicks (id) on update cascade on delete cascade,
    to_nick_id      integer not null references robobot.nicks (id) on update cascade on delete cascade,
    message         text not null,
    created_at      timestamp with time zone not null default now(),
    delivered_at    timestamp with time zone,

    PRIMARY KEY (memo_id)
);
CREATE INDEX ON robobot.memo_memos (from_nick_id);
CREATE INDEX ON robobot.memo_memos (to_nick_id, delivered_at);
CREATE INDEX ON robobot.memo_memos (delivered_at);

share/migrations/deploy/p-net-urls-20161128224203.sql  view on Meta::CPAN

-- Deploy robobot:p-net-urls-20161128224203 to pg
-- requires: base

BEGIN;

CREATE TABLE robobot.urltitle_urls (
    url_id          serial not null,
    channel_id      integer not null references robobot.channels (id) on update cascade on delete cascade,
    nick_id         integer not null references robobot.nicks (id) on update cascade on delete cascade,
    title           text,
    original_url    text not null,
    final_url       text not null,
    linked_at       timestamp with time zone not null default now(),

    PRIMARY KEY (url_id)
);
CREATE INDEX ON robobot.urltitle_urls (channel_id);
CREATE INDEX ON robobot.urltitle_urls (nick_id);
CREATE INDEX ON robobot.urltitle_urls (original_url);

share/migrations/deploy/p-skills-20161128215019.sql  view on Meta::CPAN

-- Deploy robobot:p-skills-20161128215019 to pg
-- requires: base

BEGIN;

CREATE TABLE robobot.skills_skills (
    skill_id    serial not null,
    name        text not null,
    description text,
    created_by  integer references robobot.nicks (id) on update cascade on delete set null,
    created_at  timestamp with time zone not null default now(),

    PRIMARY KEY (skill_id)
);
CREATE UNIQUE INDEX ON robobot.skills_skills (lower(name));
CREATE INDEX ON robobot.skills_skills (created_by);

CREATE TABLE robobot.skills_levels (
    level_id    serial not null,
    name        text not null,
    description text,
    sort_order  integer not null default 0,

    PRIMARY KEY (level_id)
);
CREATE UNIQUE INDEX ON robobot.skills_levels (lower(name));
CREATE INDEX ON robobot.skills_levels (sort_order);

CREATE TABLE robobot.skills_related (
    skill_id    integer not null references robobot.skills_skills (skill_id) on update cascade on delete cascade,
    related_id  integer not null references robobot.skills_skills (skill_id) on update cascade on delete cascade,

    CONSTRAINT "no self-relations" CHECK (skill_id != related_id),

    PRIMARY KEY (skill_id, related_id)
);
CREATE INDEX ON robobot.skills_related (related_id);

CREATE TABLE robobot.skills_nicks (
    skill_id        integer not null references robobot.skills_skills (skill_id) on update cascade on delete cascade,
    nick_id         integer not null references robobot.nicks (id) on update cascade on delete cascade,
    skill_level_id  integer not null references robobot.skills_levels (level_id) on update cascade on delete cascade,

    PRIMARY KEY (skill_id, nick_id)
);
CREATE INDEX ON robobot.skills_nicks (nick_id);
CREATE INDEX ON robobot.skills_nicks (skill_level_id);

COMMIT;

share/migrations/deploy/p-thinge-20161128223332.sql  view on Meta::CPAN

CREATE TABLE robobot.thinge_tags (
    id          serial not null,
    tag_name    text not null,

    PRIMARY KEY (id)
);
CREATE UNIQUE INDEX ON robobot.thinge_tags (lower(tag_name));

CREATE TABLE robobot.thinge_thinges (
    id          serial not null,
    network_id  integer not null references robobot.networks (id) on update cascade on delete restrict,
    type_id     integer not null references robobot.thinge_types (id) on update cascade on delete restrict,
    thinge_num  integer not null,
    thinge_url  text not null,
    deleted     boolean not null default false,
    added_by    integer not null references robobot.nicks (id) on update cascade on delete restrict,
    added_at    timestamp with time zone not null default now(),

    PRIMARY KEY (id)
);
CREATE UNIQUE INDEX ON robobot.thinge_thinges (network_id, type_id, thinge_num);
CREATE UNIQUE INDEX ON robobot.thinge_thinges (network_id, type_id, thinge_url);
CREATE INDEX ON robobot.thinge_thinges (type_id);
CREATE INDEX ON robobot.thinge_thinges (added_by);

CREATE TABLE robobot.thinge_thinge_tags (
    thinge_id   integer not null references robobot.thinge_thinges (id) on update cascade on delete cascade,
    tag_id      integer not null references robobot.thinge_tags (id) on update cascade on delete cascade,

    PRIMARY KEY (thinge_id, tag_id)
);
CREATE INDEX ON robobot.thinge_thinge_tags (tag_id);

COMMIT;

share/migrations/deploy/p-variables-20161128195027.sql  view on Meta::CPAN

-- Deploy robobot:p-variables-20161128195027 to pg
-- requires: base

BEGIN;

CREATE TABLE robobot.global_vars (
    id          serial not null,
    network_id  integer not null references robobot.networks (id) on update cascade on delete cascade,
    var_name    text not null,
    var_values  text[] not null,
    created_by  integer not null references robobot.nicks (id) on update cascade on delete cascade,
    created_at  timestamp with time zone not null default now(),
    updated_at  timestamp with time zone,

    PRIMARY KEY (id)
);
CREATE UNIQUE INDEX ON robobot.global_vars (network_id, var_name);
CREATE INDEX ON robobot.global_vars (created_by);

COMMIT;

share/migrations/deploy/p-voting-20161128224521.sql  view on Meta::CPAN

-- Deploy robobot:p-voting-20161128224521 to pg
-- requires: base

BEGIN;

CREATE TABLE robobot.voting_polls (
    poll_id     serial not null,
    channel_id  integer not null references robobot.channels (id) on update cascade on delete cascade,
    name        text not null,
    can_writein boolean not null default false,
    created_by  integer not null references robobot.nicks (id) on update cascade on delete cascade,
    created_at  timestamp with time zone not null default now(),
    closed_at   timestamp with time zone,

    PRIMARY KEY (poll_id)
);
CREATE INDEX ON robobot.voting_polls (channel_id);
CREATE INDEX ON robobot.voting_polls (created_by);

CREATE TABLE robobot.voting_poll_choices (
    choice_id   serial not null,
    poll_id     integer not null references robobot.voting_polls (poll_id) on update cascade on delete cascade,
    name        text not null,
    is_writein  boolean not null default false,
    writein_by  integer references robobot.nicks (id) on update cascade on delete cascade,
    writein_at  timestamp with time zone,

    PRIMARY KEY (choice_id)
);
CREATE UNIQUE INDEX ON robobot.voting_poll_choices (poll_id, name);
CREATE INDEX ON robobot.voting_poll_choices (writein_by);

CREATE TABLE robobot.voting_votes (
    vote_id     serial not null,
    choice_id   integer not null references robobot.voting_poll_choices (choice_id) on update cascade on delete restrict,
    nick_id     integer not null references robobot.nicks (id) on update cascade on delete restrict,
    voted_at    timestamp with time zone not null default now(),

    PRIMARY KEY (vote_id)
);
CREATE INDEX ON robobot.voting_votes (choice_id);
CREATE INDEX ON robobot.voting_votes (nick_id);

COMMIT;