65 lines
2.1 KiB
PL/PgSQL
65 lines
2.1 KiB
PL/PgSQL
BEGIN;
|
|
|
|
|
|
INSERT INTO version_history (component, from_version, to_version, author)
|
|
VALUES ('CUSTOMCMD', 0, 1, 'Initial Creation');
|
|
|
|
CREATE TABLE customcmds(
|
|
commandid INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
communityid INTEGER NOT NULL REFERENCES communities(communityid) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
name TEXT NOT NULL,
|
|
response TEXT NOT NULL,
|
|
cooldown_bucket_size INTEGER DEFAULT 1,
|
|
cooldown_bucket_dur INTEGER,
|
|
permlevel INTEGER,
|
|
maskperms BOOLEAN DEFAULT FALSE,
|
|
creatorid INTEGER NOT NULL REFERENCES user_profiles(profileid) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
description TEXT,
|
|
docstring TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
_timestamp TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE UNIQUE INDEX customcmds_community_name ON customcmds (communityid, name);
|
|
|
|
CREATE TABLE customcmd_aliases(
|
|
aliasid INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
commandid INTEGER REFERENCES customcmds (commandid) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
alias TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
CREATE UNIQUE INDEX custom_cmd_aliases_command_alias ON customcmd_aliases (commandid, alias);
|
|
|
|
-- Problem: Each alias should be unique in its community, not just for the command.
|
|
-- Additionally, should the name always be available as an alias?
|
|
-- Are we allowed to create an alias that clobbers another command?
|
|
-- If we allow names to be associated via table only, then we have no canonical name (first name?) and no check that each command as "at least one" name.
|
|
-- TODO: Will need to fix this later.
|
|
-- Will start without aliases.
|
|
|
|
-- TODO: Command usage log so we can see the most commonly used commands
|
|
|
|
CREATE VIEW
|
|
customcmd_info
|
|
AS
|
|
SELECT
|
|
cmds.commandid,
|
|
cmds.communityid,
|
|
cmds.name,
|
|
cmds.response,
|
|
cmds.cooldown_bucket_size,
|
|
cmds.cooldown_bucket_dur,
|
|
cmds.permlevel,
|
|
cmds.maskperms,
|
|
cmds.creatorid,
|
|
cmds.description,
|
|
cmds.docstring,
|
|
cmds.created_at,
|
|
cmds._timestamp,
|
|
aliases.aliasid,
|
|
aliases.alias
|
|
FROM customcmds AS cmds
|
|
LEFT JOIN customcmd_aliases AS aliases USING (commandid);
|
|
|
|
|
|
COMMIT;
|