Lennox Stevenson’s Blog

In [93]:

import sqlite3

import pandas as pd
import plotly.io as pio
from plotly import express as px

pio.renderers.default = "plotly_mimetype+notebook_connected"

In [94]:

db_file = "thaumaturge_dpr.db"


def query(query: str) -> pd.DataFrame:
    con = sqlite3.connect(db_file)
    df = pd.read_sql_query(query, con)
    con.close()
    return df

In [95]:

seed_query = """
DROP TABLE IF EXISTS player;
CREATE TABLE player (
    level INTEGER,
    charisma INTEGER,
    strength INTEGER,
    esoteric_lore_training INTEGER,
    weapon_proficiency INTEGER,
    weapon_specialization INTEGER,
    weapon_tracking INTEGER,
    weapon_damage_dice INTEGER
);
INSERT INTO player (
    level,
    charisma,
    strength,
    esoteric_lore_training,
    weapon_proficiency,
    weapon_specialization,
    weapon_tracking,
    weapon_damage_dice
) VALUES
(1,4,3,2,2,0,0,1),
(2,4,3,2,2,0,1,1),
(3,4,3,4,2,0,1,1),
(4,4,3,4,2,0,1,2),
(5,4,4,4,4,2,1,2),
(6,4,4,4,4,2,1,2),
(7,4,4,6,4,2,1,2),
(8,4,4,6,4,2,1,2),
(9,4,4,6,4,2,1,2),
(10,5,4,6,4,2,2,2),
(11,5,4,6,4,2,2,2),
(12,5,4,6,4,2,2,3),
(13,5,4,6,6,3,2,3),
(14,5,4,6,6,3,2,3),
(15,5,5,8,6,3,2,3),
(16,5,5,8,6,3,3,3),
(17,5,5,8,6,3,3,3),
(18,5,5,8,6,3,3,3),
(19,5,5,8,6,3,3,4),
(20,6,5,8,6,3,3,4);

DROP TABLE IF EXISTS monster;
CREATE TABLE monster (
    level INTEGER,
    extreme_ac INTEGER,
    hard_ac INTEGER,
    moderate_ac INTEGER,
    low_ac INTEGER,
    dc INTEGER
);
delete from monster
;
INSERT INTO monster (level, extreme_ac, hard_ac, moderate_ac, low_ac, dc)
VALUES
(0,19,16,15,13,14),
(1,19,16,15,13,15),
(2,21,18,17,15,16),
(3,22,19,18,16,18),
(4,24,21,20,18,19),
(5,25,22,21,19,20),
(6,27,24,23,21,22),
(7,28,25,24,22,23),
(8,30,27,26,24,24),
(9,31,28,27,25,26),
(10,33,30,29,27,27),
(11,34,31,30,28,28),
(12,36,33,32,30,30),
(13,37,34,33,31,31),
(14,39,36,35,33,32),
(15,40,37,36,34,34),
(16,42,39,38,36,35),
(17,43,40,39,37,36),
(18,45,42,41,39,38),
(19,46,43,42,40,39),
(20,48,45,44,42,40),
(21,49,46,45,43,42),
(22,51,48,47,45,44),
(23,52,49,48,46,46),
(24,54,51,50,48,48);

DROP TABLE IF EXISTS level_diff;
CREATE TABLE level_diff (
    value INTEGER
);
INSERT INTO level_diff
(value)
VALUES
(-4),
(-3),
(-2),
(-1),
(0),
(1),
(2),
(3),
(4);


DROP TABLE IF EXISTS weapon_die;
CREATE TABLE weapon_die (
    die TEXT,
    value DECIMAL(1,2)
);
INSERT INTO weapon_die
(die, value)
VALUES
('d4', '2.5'),
('d6', '3.5'),
('d8', '4.5'),
('d10', '5.5')
"""

con = sqlite3.connect(db_file)
with con:
    for q in seed_query.split(";"):
        con.execute(q + ";")
con.close()

In [96]:

q = "select level, dc from monster order by level"
df = query(q)
fig = px.line(
    df,
    x="level",
    y="dc",
    labels={"level": "Monster Level", "dc": "Monster DC"},
    title="Monster DC by Level",
    markers=True,
)
fig.show()

In [97]:

q = "select level, extreme_ac, hard_ac, moderate_ac, low_ac from monster order by level"
df = query(q)
df = df.melt(id_vars=["level"], var_name="tier", value_name="ac")
fig = px.line(
    df,
    x="level",
    y="ac",
    color="tier",
    labels={"level": "Monster Level", "ac": "Monster AC", "tier": "Tier"},
    title="Monster AC by Level",
    markers=True,
)
fig.show()

In [98]:

level_diff_cte = """
level_diff as """

In [99]:

q = """
select
    level_diff.value as level_diff,
    player.level as level,
    monster.dc - player.level - player.charisma - player.esoteric_lore_training as success,
    monster.dc + 10 - player.level - player.charisma - player.esoteric_lore_training as critical
from level_diff
cross join player
join monster on player.level - level_diff.value = monster.level
"""
df = query(q)
fig = px.line(
    df,
    x="level",
    y="success",
    color="level_diff",
    labels={
        "level": "Player level",
        "success": "d20 success minimum",
        "level_diff": "Player - monster level",
    },
    title="Exploit vulnerability success rate by player and monster level",
    markers=True,
)
fig.show()

In [100]:

q = """
with
    player_attack as (
        select
            level,
            player.level
            + player.strength
            + player.weapon_proficiency
            + player.weapon_tracking as attack_bonus
        from player
    )
select
    level_diff.value as level_diff,
    player.level as level,
    monster.extreme_ac - player.attack_bonus as extreme,
    monster.hard_ac - player.attack_bonus as hard,
    monster.moderate_ac - player.attack_bonus as moderate,
    monster.low_ac - player.attack_bonus as low
from level_diff
cross join player_attack as player
join monster on player.level + level_diff.value = monster.level

"""
df = query(q)
df = df.melt(
    id_vars=["level", "level_diff"], var_name="tier", value_name="min_success_roll"
)
fig = px.line(
    df,
    x="level",
    y="min_success_roll",
    color="level_diff",
    labels={
        "level": "Player Level",
        "min_success_roll": "d20 success minimum",
        "level_diff": "Player - monster level",
    },
    facet_col="tier",
    facet_col_wrap=2,
    markers=True,
)
fig.show()

In [101]:

q = """
select
    weapon_die.die as die,
    player.level as level,
    weapon_die.value * player.weapon_damage_dice
    + player.strength
    + 2
    + player.weapon_specialization
    + 2
    + floor(player.level / 2) as damage
from player
cross join weapon_die
"""
df = query(q)
fig = px.line(
    df,
    x="level",
    y="damage",
    color="die",
    labels={
        "level": "Player Level",
        "damage": "Damage per strike",
        "die": "Weapon die size",
    },
    markers=True,
)
fig.show()

index.ipynb