[INFO] - Wish simulator implementation

[AimarIbarra]

Sup, @rolfan here!

In the end I will try to implement the wish simulator, so I need some information so I can know how to design the database.

Taking into account the following schema:

-- Wish Simulator Schema
CREATE TABLE IF NOT EXISTS wish_simulator (
    user_id BIGINT NOT NULL,
    entertwined_wishes INTEGER NOT NULL DEFAULT 0,
    limited_wishes_placeholder INTEGER NOT NULL DEFAULT 0, -- I forgor the name
    primos INTEGER NOT NULL DEFAULT 3200,
    standard_wishes_count INTEGER NOT NULL DEFAULT 0,
    limited_wishes_count INTEGER NOT NULL DEFAULT 0,
    character_list TEXT NOT NULL DEFAULT '[]',
    character_count INTEGER NOT NULL DEFAULT 0,
    weapon_list TEXT NOT NULL DEFAULT '[]',
    weapon_count INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (user_id)
);

I guess that for each user it's desired to keep track of the amount of gacha roles, both in the standard wishes and the limeted ones (which I suppose are the event wishes), the characters they have got, and the weapons. However, I don't know what the purpose of the entertwined_wishes and primos entries are, it would be nice if someone could tell me.

In addition telling me which features should be implemented would be cool.

Thank you!

[paninizer]

Apologies, I misspelled entertwined_wishes. It should be Intertwined Fate (intertwined_fate) for entertwined_wishes and Acquaint Fate (acquaint_fate) for the placeholder column. The former is used in event wishing banners, Acquaint Fate for standard banner. Then primogems is a currency that can convert to either of those wishes (160 primogems to one wish). I may also implement more columns: stardust and starglitter, with both of them being currencies obtained through wishes. More information can be found on Genshin Wiki: Starglitter Wiki, Stardust Wiki with more specific details in Wishes.

Hope that helps.

[AimarIbarra]

Apologies, I misspelled entertwined_wishes. It should be Intertwined Fate (intertwined_fate) for entertwined_wishes and Acquaint Fate (acquaint_fate) for the placeholder column. The former is used in event wishing banners, Acquaint Fate for standard banner. Then primogems is a currency that can convert to either of those wishes (160 primogems to one wish). I may also implement more columns: stardust and starglitter, with both of them being currencies obtained through wishes. More information can be found on Genshin Wiki: Starglitter Wiki, Stardust Wiki with more specific details in Wishes.

Hope that helps.

Yep, guessed that much about the misspelling, but I wasn't 100% sure. I will make an E-R diagram before making the schema (reduced to BCNF) and post it here so it can be decided if it is an appropriate design. It shouldn't take me more than 20mins considering that I already have read what seemed relevant from the wiki.

[paninizer]

Alright thanks.

[AimarIbarra]

If the database type needs to be a SQL database then I believe the following E-R represents a good design. I will later create the SQL code that in charge of implementing it and qeurying the relevant data.

[paninizer]

Second step is to write out helper functions that can query the DB. Depends on the memory needed, it may be also be advisable to implement a map of all users and their data into the bot's global data framework (whether that is needed does depend on the scale of the bot)

[paninizer]

Depending on feature needs we may need to rewrite the wish_simulator schemas further, so making it editable in the future without erasing all of the data may prove to be beneficial. I may plan on doing event banners from past versions and newcomer banner as a paid feature. Ideally, to incentivize users, a multiplayer feature may also be added where players can create decks and challenge dungeons and the top on the global leaderboard for each lunar month gets something special like spiral abyss but turn-based (will add after simulator finishes development).

[paninizer]

For now this is a good roadmap to start out with!

[AimarIbarra]

Should I design the whole schema for the bot? Having a look at the other schemas I noticed many opportunities to normalize and therefore improve the DB.

At least redundancy would be reduced, and there should be performance gains.

And a normalized DB is easier to maintain, so it may be a good idea.

[paninizer]

That would be nice! Since I'm new to SQL I don't have an exact idea how it works so that would help a lot!

[AimarIbarra]

The first thing I need is a written description of the model, which I will write right now so you can give your thoughts on it as soon as possible.

I will post it here. Shouldn't take long.

[paninizer]

The most important ones are guild settings and modlogs

Guild settings will have the guild's prefix, whether certain features are enabled, etc

Modlogs will contain the moderation history of a guild.

The other one is bot_stat which counts the total command invocation of a guild.

There's not much else right now and later features may be implemented on an indefinite basis.

[AimarIbarra]

What's the guild prefix exactly?

prefix TEXT NOT NULL DEFAULT "-",

This definition doesn't make clear the type of data it stores.

If you could give me a Rust struct describing the prefix it would be of great help :)

[paninizer]

The prefix for text-based command invocation. I will need to restrict it to VARCHAR(6) to limit user inputs.

[AimarIbarra]

Oh, ok

[paninizer]

Note that there are usually 2 event character banners for any one point of time (1 rerun, 1 new). Therefore it is best to keep that in mind while writing the SQL files.

[AimarIbarra]

Since this depends on the date I believe it is best to let the program handle it instead of storing it into the database

let active_new_banner = query_the_banner_with_name(new_banner_name);
let active_rerun_banner = query_the_banner_with_name(rerun_banner_name);

Then we would implement something to update the active banner at runtime, like signals idk.

[paninizer]

True. Then I will assume the version banners are manually programmed and read by the program?

[paninizer]

If so, then what would be an optimal format to associate with this configuration? JSON would be optimal due to readability and maintainability. As well, this file would only be read once (associating a function to reload the list of banners upon file update is also advisable, but would be harder to implement).

[paninizer]

But I also have doubts regarding JSON's performance.

[AimarIbarra]

The data would be stored inside the database, however the program would hold either the name or the id of the banner.

Another option is to reserve some banner IDs to the active banners, the new and the rerun having the IDs 0 and 1 respectively.

Or we can create a new table (something along the lines "active_banners". Then SELECT name FROM banners WHERE id = (SELECT id FROM active_banner) .

With the first option JSON is a good choice, but we can use whatever, since this won't be a bottleneck.

If we were to choose the second or third option we would just update the DB with any tool.

[AimarIbarra]

If performance is an issue we can come up with our own file format and create a program that translates JSON into it: i.e. a tiny compiler.

[AimarIbarra]

Message here so I don't forget to add tests

[AimarIbarra]

As requested: link to article