Gacha game centered around vore.
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.

543 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;