You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
542 lines
22 KiB
542 lines
22 KiB
create table if not exists discordchannel
|
|
(
|
|
discordid varchar(20) not null
|
|
constraint discordchannel_pkey
|
|
primary key,
|
|
name varchar(100) not null,
|
|
broadcastgame boolean default false not null,
|
|
sendlogs boolean default false not null,
|
|
acceptgamecommands boolean default false not null,
|
|
acceptadmincommands boolean default false not null,
|
|
priority smallint default 0 not null,
|
|
guildid varchar(20) default NULL::character varying,
|
|
webhookid varchar(20) default NULL::character varying,
|
|
webhooktoken varchar(128) default NULL::character varying,
|
|
constraint discordchannel_webhookpair
|
|
check (((webhookid IS NULL) AND (webhooktoken IS NULL)) OR
|
|
((webhookid IS NOT NULL) AND (webhooktoken IS NOT NULL)))
|
|
);
|
|
|
|
alter table discordchannel
|
|
owner to gacha;
|
|
|
|
create index if not exists discordchannel_guildid
|
|
on discordchannel (guildid)
|
|
where (guildid IS NOT NULL);
|
|
|
|
create index if not exists discordchannel_acceptsgamecommands
|
|
on discordchannel (acceptgamecommands)
|
|
where (acceptgamecommands IS TRUE);
|
|
|
|
create index if not exists discordchannel_acceptsadmincommands
|
|
on discordchannel (acceptadmincommands)
|
|
where (acceptadmincommands IS TRUE);
|
|
|
|
create table if not exists gender
|
|
(
|
|
id varchar(8) not null
|
|
constraint gender_pkey
|
|
primary key,
|
|
name varchar(100) not null
|
|
constraint gender_name_key
|
|
unique
|
|
);
|
|
|
|
alter table gender
|
|
owner to gacha;
|
|
|
|
create table if not exists player
|
|
(
|
|
id serial
|
|
constraint player_pkey
|
|
primary key,
|
|
name varchar(100) not null,
|
|
genderid varchar(8) not null
|
|
constraint player_genderid_fkey
|
|
references gender
|
|
on update cascade on delete restrict,
|
|
currency integer not null,
|
|
joinedat timestamp with time zone default now() not null,
|
|
lastactive timestamp with time zone default now() not null,
|
|
lastdaily timestamp with time zone
|
|
);
|
|
|
|
alter table player
|
|
owner to gacha;
|
|
|
|
create table if not exists discorduser
|
|
(
|
|
discordid varchar(20) not null
|
|
constraint discorduser_pkey
|
|
primary key,
|
|
username varchar(32) not null,
|
|
discriminator varchar(4) not null,
|
|
playerid integer
|
|
constraint discorduser_playerid_fkey
|
|
references player
|
|
on update cascade on delete set null,
|
|
lastactive timestamp with time zone default now() not null
|
|
);
|
|
|
|
alter table discorduser
|
|
owner to gacha;
|
|
|
|
create table if not exists unittier
|
|
(
|
|
id varchar(8) not null
|
|
constraint unittier_pkey
|
|
primary key,
|
|
name varchar(100) not null
|
|
constraint unittier_name_key
|
|
unique,
|
|
pullweight integer not null,
|
|
recallcost integer not null
|
|
);
|
|
|
|
alter table unittier
|
|
owner to gacha;
|
|
|
|
create table if not exists unit
|
|
(
|
|
id serial
|
|
constraint unit_pkey
|
|
primary key,
|
|
name varchar(50) not null,
|
|
subtitle varchar(50) not null,
|
|
description text not null,
|
|
tierid varchar(8) not null
|
|
constraint unit_tierid_fkey
|
|
references unittier
|
|
on update cascade on delete restrict,
|
|
basehealth integer not null
|
|
constraint unit_basehealth_check
|
|
check (basehealth > 0),
|
|
basestrength integer not null
|
|
constraint unit_basestrength_check
|
|
check (basestrength > 0),
|
|
constraint unit_name_subtitle_key
|
|
unique (name, subtitle)
|
|
);
|
|
|
|
alter table unit
|
|
owner to gacha;
|
|
|
|
create table if not exists summonedunit
|
|
(
|
|
instanceid serial
|
|
constraint summonedunit_pkey
|
|
primary key,
|
|
playerid integer not null
|
|
constraint summonedunit_playerid_fkey
|
|
references player
|
|
on update cascade on delete cascade,
|
|
unitid integer not null
|
|
constraint summonedunit_unitid_fkey
|
|
references unit
|
|
on update cascade on delete cascade,
|
|
summonedat timestamp with time zone default now() not null,
|
|
waspulled boolean default false not null,
|
|
wasrecalled boolean default false not null,
|
|
resummonings timestamp with time zone[],
|
|
desummonedat timestamp with time zone,
|
|
digestedbyinstanceid integer
|
|
constraint summonedunit_digestedbyinstanceid_fkey
|
|
references summonedunit
|
|
on update cascade on delete set null,
|
|
wasdigested boolean,
|
|
wasreleased boolean,
|
|
currenthealth integer not null,
|
|
maxhealth integer not null
|
|
constraint summonedunit_maxhealth_check
|
|
check (maxhealth > 0),
|
|
strength integer not null
|
|
constraint summonedunit_strength_check
|
|
check (strength > 0),
|
|
constraint summonedunit_currenthealthbounds
|
|
check ((currenthealth >= (- maxhealth)) AND (currenthealth <= maxhealth)),
|
|
constraint summonedunit_exactlyoneorigin
|
|
check (((waspulled IS TRUE) OR (wasrecalled IS TRUE)) AND
|
|
(NOT ((waspulled IS FALSE) AND (wasrecalled IS FALSE)))),
|
|
constraint summonedunit_exactlyonefate
|
|
check ((((wasdigested IS TRUE) OR (wasreleased IS TRUE)) = (desummonedat IS NOT NULL)) AND
|
|
(((wasdigested IS NULL) OR (wasreleased IS NULL)) = (desummonedat IS NULL))),
|
|
constraint summonedunit_digesterfordigestedonly
|
|
check ((digestedbyinstanceid IS NULL) OR (wasdigested IS TRUE))
|
|
);
|
|
|
|
alter table summonedunit
|
|
owner to gacha;
|
|
|
|
create unique index if not exists summonedunit_oneinstanceperunitperplayer
|
|
on summonedunit (playerid, unitid)
|
|
where (desummonedat IS NULL);
|
|
|
|
create or replace function getguildidsabletousegamecommands() returns SETOF character varying
|
|
stable
|
|
rows 1
|
|
language sql
|
|
as
|
|
$$
|
|
SELECT DISTINCT guildId
|
|
FROM DiscordChannel
|
|
WHERE guildId IS NOT NULL
|
|
AND acceptGameCommands IS TRUE;
|
|
$$;
|
|
|
|
alter function getguildidsabletousegamecommands() owner to gacha;
|
|
|
|
create or replace procedure checkgamecommandin(requestedchannel character varying, requestedguild character varying)
|
|
language plpgsql
|
|
as
|
|
$$
|
|
DECLARE
|
|
channelAcceptsGameCommands BOOLEAN;
|
|
channelAcceptsAdminCommands BOOLEAN;
|
|
channelSendsMessages BOOLEAN;
|
|
channelIsKnown BOOLEAN;
|
|
guildSupportsGameCommands BOOLEAN;
|
|
guildSupportsAdminCommands BOOLEAN;
|
|
guildSupportsMessages BOOLEAN;
|
|
guildIsKnown BOOLEAN;
|
|
recommendedChannelId DiscordChannel.DiscordId%TYPE;
|
|
BEGIN
|
|
SELECT acceptGameCommands, acceptAdminCommands, broadcastGame OR sendLogs, TRUE
|
|
INTO channelAcceptsGameCommands, channelAcceptsAdminCommands, channelSendsMessages, channelIsKnown
|
|
FROM DiscordChannel
|
|
WHERE discordId = requestedChannel
|
|
LIMIT 1;
|
|
IF channelAcceptsGameCommands IS NOT TRUE THEN
|
|
SELECT discordId, acceptGameCommands, acceptAdminCommands, broadcastGame OR sendLogs, TRUE
|
|
INTO recommendedChannelId, guildSupportsGameCommands, guildSupportsAdminCommands, guildSupportsMessages, guildIsKnown
|
|
FROM DiscordChannel
|
|
WHERE guildId = requestedGuild
|
|
ORDER BY CASE TRUE
|
|
WHEN acceptGameCommands THEN 3
|
|
WHEN acceptAdminCommands THEN 2
|
|
WHEN broadcastGame OR sendLogs THEN 1
|
|
ELSE 0
|
|
END DESC, priority DESC
|
|
LIMIT 1;
|
|
IF guildSupportsGameCommands IS TRUE THEN
|
|
RAISE EXCEPTION 'Can''t use game commands in this channel' USING
|
|
ERRCODE = 'VGBCG',
|
|
DETAIL = CASE TRUE
|
|
WHEN channelAcceptsGameCommands THEN format(
|
|
'This channel (<#%s>) can only be used to send admin commands, not game commands.',
|
|
requestedChannel)
|
|
WHEN channelSendsMessages THEN format(
|
|
'This channel (<#%s>) is only used to receive broadcasts, not send commands.',
|
|
requestedChannel)
|
|
WHEN channelIsKnown THEN format(
|
|
'This channel (<#%s>) is unused.', requestedChannel)
|
|
ELSE format('This channel (<#%s>) is not known to the system.', requestedChannel)
|
|
END,
|
|
HINT = format(
|
|
'Try sending messages to the channel <#%s> in this guild, which does allow game commands.',
|
|
recommendedChannelId);
|
|
ELSE
|
|
RAISE EXCEPTION 'Can''t use game commands in this guild' USING
|
|
ERRCODE = 'VGBGG',
|
|
DETAIL = CASE TRUE
|
|
WHEN guildSupportsAdminCommands THEN
|
|
format('This guild (ID %) only has channels used to send admin commands, ' ||
|
|
'not game commands.', requestedGuild)
|
|
WHEN guildSupportsMessages THEN
|
|
format('This guild (ID %) only has channels used to receive broadcasts, ' ||
|
|
'not send commands.', requestedGuild)
|
|
WHEN guildIsKnown THEN
|
|
format('This guild (ID %) only has unused channels.', requestedGuild)
|
|
ELSE format('This guild (ID %) is not known to the system.', requestedGuild)
|
|
END,
|
|
HINT = 'As game commands are normally only visible when a guild allows them, ' ||
|
|
'this guild may have been removed from the system incorrectly. ' ||
|
|
'Ask an admin to check what''s going on.';
|
|
END IF;
|
|
END IF;
|
|
END;
|
|
$$;
|
|
|
|
alter procedure checkgamecommandin(varchar, varchar) owner to gacha;
|
|
|
|
create or replace function getguildidsabletouseadmincommands() returns SETOF character varying
|
|
stable
|
|
rows 1
|
|
language sql
|
|
as
|
|
$$
|
|
SELECT DISTINCT guildId
|
|
FROM DiscordChannel
|
|
WHERE guildId IS NOT NULL
|
|
AND acceptAdminCommands IS TRUE;
|
|
$$;
|
|
|
|
alter function getguildidsabletouseadmincommands() owner to gacha;
|
|
|
|
create or replace procedure checkadmincommandin(requestedchannel character varying, requestedguild character varying)
|
|
language plpgsql
|
|
as
|
|
$$
|
|
DECLARE
|
|
channelAcceptsAdminCommands BOOLEAN;
|
|
channelAcceptsGameCommands BOOLEAN;
|
|
channelSendsMessages BOOLEAN;
|
|
channelIsKnown BOOLEAN;
|
|
guildSupportsAdminCommands BOOLEAN;
|
|
guildSupportsGameCommands BOOLEAN;
|
|
guildSupportsMessages BOOLEAN;
|
|
guildIsKnown BOOLEAN;
|
|
recommendedChannelId DiscordChannel.DiscordId%TYPE;
|
|
BEGIN
|
|
SELECT acceptAdminCommands, acceptGameCommands, broadcastGame OR sendLogs, TRUE
|
|
INTO channelAcceptsAdminCommands, channelAcceptsGameCommands, channelSendsMessages, channelIsKnown
|
|
FROM DiscordChannel
|
|
WHERE discordId = requestedChannel
|
|
LIMIT 1;
|
|
IF channelAcceptsAdminCommands IS NOT TRUE THEN
|
|
SELECT discordId, acceptAdminCommands, acceptGameCommands, broadcastGame OR sendLogs, TRUE
|
|
INTO recommendedChannelId, guildSupportsAdminCommands, guildSupportsGameCommands, guildSupportsMessages, guildIsKnown
|
|
FROM DiscordChannel
|
|
WHERE guildId = requestedGuild
|
|
ORDER BY CASE TRUE
|
|
WHEN acceptAdminCommands THEN 3
|
|
WHEN acceptGameCommands THEN 2
|
|
WHEN broadcastGame OR sendLogs THEN 1
|
|
ELSE 0
|
|
END DESC, priority DESC
|
|
LIMIT 1;
|
|
IF guildSupportsAdminCommands IS TRUE THEN
|
|
RAISE EXCEPTION 'Can''t use admin commands in this channel' USING
|
|
ERRCODE = 'VGBCA',
|
|
DETAIL = CASE TRUE
|
|
WHEN channelAcceptsGameCommands THEN format(
|
|
'This channel (<#%s>) can only be used to send admin commands, not game commands.',
|
|
requestedChannel)
|
|
WHEN channelSendsMessages THEN format(
|
|
'This channel (<#%s>) is only used to receive broadcasts, not send commands.',
|
|
requestedChannel)
|
|
WHEN channelIsKnown THEN format(
|
|
'This channel (<#%s>) is unused.', requestedChannel)
|
|
ELSE format('This channel (<#%s>) is not known to the system.', requestedChannel)
|
|
END,
|
|
HINT = format(
|
|
'Try sending messages to the channel <#%s> in this guild, which does allow game commands.',
|
|
recommendedChannelId);
|
|
ELSE
|
|
RAISE EXCEPTION 'Can''t use admin commands in this guild' USING
|
|
ERRCODE = 'VGBGA',
|
|
DETAIL = CASE TRUE
|
|
WHEN guildSupportsGameCommands THEN
|
|
format('This guild (ID %s) only has channels used to send game commands, ' ||
|
|
'not admin commands.', requestedGuild)
|
|
WHEN guildSupportsMessages THEN
|
|
format('This guild (ID %s) only has channels used to receive broadcasts, ' ||
|
|
'not send commands.', requestedGuild)
|
|
WHEN guildIsKnown THEN
|
|
format('This guild (ID %s) only has unused channels.', requestedGuild)
|
|
ELSE format('This guild (ID %s) is not known to the system.', requestedGuild)
|
|
END,
|
|
HINT = 'As admin commands are normally only visible when a guild allows them, ' ||
|
|
'this guild may have been removed from the system incorrectly. ' ||
|
|
'Ask an admin to check what''s going on.';
|
|
END IF;
|
|
END IF;
|
|
END;
|
|
$$;
|
|
|
|
alter procedure checkadmincommandin(varchar, varchar) owner to gacha;
|
|
|
|
create or replace function getregisterablegenders(OUT id character varying, OUT name character varying) returns SETOF record
|
|
stable
|
|
strict
|
|
language sql
|
|
as
|
|
$$
|
|
SELECT Gender.id, Gender.name
|
|
FROM Gender
|
|
$$;
|
|
|
|
alter function getregisterablegenders(out varchar, out varchar) owner to gacha;
|
|
|
|
create or replace function getinvokingdiscorduser(forid character varying, newusername character varying,
|
|
newdiscriminator character varying) returns discorduser
|
|
strict
|
|
language sql
|
|
as
|
|
$$
|
|
INSERT INTO DiscordUser (discordId, username, discriminator, lastActive)
|
|
VALUES (forId, newUsername, newDiscriminator, NOW())
|
|
ON CONFLICT (discordId) DO UPDATE SET username = newUsername,
|
|
discriminator = newDiscriminator,
|
|
lastActive = NOW()
|
|
RETURNING *
|
|
$$;
|
|
|
|
alter function getinvokingdiscorduser(varchar, varchar, varchar) owner to gacha;
|
|
|
|
create or replace function getinvokingplayer(forid character varying, newusername character varying,
|
|
newdiscriminator character varying) returns player
|
|
strict
|
|
language sql
|
|
as
|
|
$$
|
|
UPDATE Player
|
|
SET lastActive = NOW()
|
|
FROM GetInvokingDiscordUser(forId, newUsername, newDiscriminator) AS InvokingDiscordUser
|
|
WHERE id = InvokingDiscordUser.playerId
|
|
RETURNING ROW (Player.*)
|
|
$$;
|
|
|
|
alter function getinvokingplayer(varchar, varchar, varchar) owner to gacha;
|
|
|
|
create or replace function updateplayerregistration(forid character varying, newusername character varying,
|
|
newdiscriminator character varying, newplayername character varying,
|
|
newgenderid character varying, OUT resultid integer,
|
|
OUT wascreated boolean) returns record
|
|
strict
|
|
language plpgsql
|
|
as
|
|
$$
|
|
DECLARE
|
|
playerId Player.id%TYPE;
|
|
BEGIN
|
|
SELECT InvokingDiscordUser.playerId
|
|
INTO STRICT playerId
|
|
FROM GetInvokingDiscordUser(forId, newUsername, newDiscriminator)
|
|
AS InvokingDiscordUser;
|
|
IF playerId IS NOT NULL THEN
|
|
wasCreated = FALSE;
|
|
UPDATE Player
|
|
SET name = newPlayerName,
|
|
genderId = newGenderId,
|
|
lastActive = NOW()
|
|
WHERE id = playerId
|
|
RETURNING id INTO resultId;
|
|
ELSE
|
|
wasCreated = TRUE;
|
|
INSERT INTO Player (name, genderId, currency, joinedAt, lastActive)
|
|
VALUES (newPlayerName, newGenderId, 100, NOW(), NOW())
|
|
RETURNING id INTO resultId;
|
|
UPDATE DiscordUser
|
|
SET playerId = resultId
|
|
WHERE discordId = forId;
|
|
END IF;
|
|
END;
|
|
$$;
|
|
|
|
alter function updateplayerregistration(varchar, varchar, varchar, varchar, varchar, out integer, out boolean) owner to gacha;
|
|
|
|
create or replace function unlinkdiscorduserfromplayer(forid character varying, newusername character varying,
|
|
newdiscriminator character varying) returns integer
|
|
strict
|
|
language plpgsql
|
|
as
|
|
$$
|
|
DECLARE
|
|
oldPlayerId Player.id%TYPE = NULL;
|
|
BEGIN
|
|
SELECT playerId
|
|
INTO oldPlayerId
|
|
FROM GetInvokingDiscordUser(forId, newUsername, newDiscriminator);
|
|
|
|
IF oldPlayerId IS NULL THEN
|
|
RETURN NULL;
|
|
END IF;
|
|
|
|
UPDATE DiscordUser
|
|
SET playerId = NULL
|
|
WHERE discordId = forId;
|
|
|
|
RETURN oldPlayerId;
|
|
END;
|
|
|
|
$$;
|
|
|
|
alter function unlinkdiscorduserfromplayer(varchar, varchar, varchar) owner to gacha;
|
|
|
|
create or replace function command_join(requestedchannel character varying, requestedguild character varying,
|
|
forid character varying, newusername character varying,
|
|
newdiscriminator character varying, newplayername character varying,
|
|
newgenderid character varying, OUT resultid integer,
|
|
OUT newplayername character varying, OUT newgendername character varying,
|
|
OUT wascreated boolean) returns record
|
|
strict
|
|
language sql
|
|
as
|
|
$$
|
|
CALL CheckGameCommandIn(requestedChannel, requestedGuild);
|
|
|
|
SELECT NewRegistration.resultId, newPlayerName, Gender.name, NewRegistration.wasCreated
|
|
FROM UpdatePlayerRegistration(forId, newUsername, newDiscriminator, newPlayerName, newGenderId) AS NewRegistration
|
|
LEFT JOIN Gender ON Gender.id = newGenderId
|
|
$$;
|
|
|
|
alter function command_join(varchar, varchar, varchar, varchar, varchar, varchar, varchar, out integer, out varchar, out varchar, out boolean) owner to gacha;
|
|
|
|
create or replace function command_unjoin(requestedchannel character varying, requestedguild character varying,
|
|
forid character varying, newusername character varying,
|
|
newdiscriminator character varying) returns integer
|
|
strict
|
|
language sql
|
|
as
|
|
$$
|
|
CALL CheckGameCommandIn(requestedChannel, requestedGuild);
|
|
|
|
SELECT UnlinkDiscordUserFromPlayer(forId, newUsername, newDiscriminator)
|
|
$$;
|
|
|
|
alter function command_unjoin(varchar, varchar, varchar, varchar, varchar) owner to gacha;
|
|
|
|
create or replace function command_pull(requestedchannel character varying, requestedguild character varying,
|
|
forid character varying, newusername character varying,
|
|
newdiscriminator character varying, count integer)
|
|
returns TABLE
|
|
(
|
|
summonedunitinstanceid integer,
|
|
summonedunitid integer,
|
|
summonedunitname character varying,
|
|
summonedunitsubtitle character varying,
|
|
summonedunittiername character varying,
|
|
firsttimepull boolean,
|
|
wasalreadysummoned boolean
|
|
)
|
|
strict
|
|
rows 10
|
|
language plpgsql
|
|
as
|
|
$$
|
|
DECLARE
|
|
playerId Player.id%TYPE;
|
|
cost Player.currency%TYPE;
|
|
oldCurrency Player.currency%TYPE;
|
|
playerLastDaily Player.lastDaily%TYPE;
|
|
BEGIN
|
|
CALL CheckGameCommandIn(requestedChannel, requestedGuild);
|
|
SELECT InvokingPlayer.id, InvokingPlayer.currency, InvokingPlayer.lastDaily
|
|
INTO playerId, oldCurrency, playerLastDaily
|
|
FROM GetInvokingPlayer(forId, newUsername, newDiscriminator) AS InvokingPlayer;
|
|
IF NOT FOUND THEN
|
|
RAISE EXCEPTION USING
|
|
ERRCODE = 'VGNYJ',
|
|
MESSAGE = 'Not yet joined',
|
|
DETAIL = 'You haven''t joined the game yet, and can''t use this command until you do.',
|
|
HINT = 'Use the /join command to join the game!';
|
|
END IF;
|
|
cost = 10 * count;
|
|
UPDATE Player SET currency = currency - cost WHERE id = playerId AND currency >= cost;
|
|
IF NOT FOUND THEN
|
|
RAISE EXCEPTION USING
|
|
ERRCODE = 'VGNEC',
|
|
MESSAGE = 'Not enough currency',
|
|
DETAIL = format('Pulling %s heroines would cost %s currency, but you only have %s currency.',
|
|
count, cost, oldCurrency),
|
|
HINT = CASE playerLastDaily IS NULL OR playerLastDaily < NOW() - '1 day'::interval
|
|
WHEN TRUE THEN 'Try using the /daily command to get some more currency for today!'
|
|
ELSE format('Wait %s and you can use the /daily command to get some more currency!',
|
|
(playerLastDaily + '1 day'::interval) - NOW())
|
|
END;
|
|
END IF;
|
|
END;
|
|
$$;
|
|
|
|
alter function command_pull(varchar, varchar, varchar, varchar, varchar, integer) owner to gacha;
|
|
|
|
|