SQL queries

Some questions need a join, a count or a sort that the lookups can't do. query() runs SQL on the bot's game data and hands back the result. Queries are written in SQLite's dialect of SQL, the same one the game version's SQL tab runs.

query()

query(sql, params, options) resolves to an object with three properties:

Rows are arrays rather than objects because column names can repeat, so read the values by position:

const result = await query(
  "SELECT language, name FROM _names WHERE record_table = ? AND record_id = ? ORDER BY language",
  ["monsters", 147],
);

for (const [language, name] of result?.rows ?? []) {
  botInfo(language, name);
}

This query lists the names of one monster in every language the game data holds, which is also how a script reads a language other than its own. query() resolves to undefined when the bot's game version has no game data, hence result?.rows.

Read-only

The game data is opened read-only. INSERT, UPDATE, DELETE, CREATE and every other statement that writes fail, and ATTACH is refused too, so a query can't reach any other database file.

Every statement in the string runs, and query() returns the result of the last one: SELECT 1; SELECT 2 returns 2. Since nothing can be written, the statements before the last one only cost time.

Parameters

Write ? wherever a value goes, and pass the values in the params array, in the same order. A parameter keeps its type, so a number is compared as a number, whereas a value written into the SQL between quotes, like '147', is text.

params has to be an array: anything else rejects with gamedata.query params must be an array. Pass numbers and strings, and convert BigInt ids with Number() first.

Writing the SQL

• Put text between single quotes, as in 'monsters'. Double quotes only name a table or a column, so "monsters" is never a string, and a mistyped column name gives an error instead of a wrong result.
• Names go through the _names table, with the columns record_table, record_id, name_id, name, language and ordinal. A record can have several names, and ordinal 0 is its main one.
• The texts of one language are in the table text_ followed by the language code, such as text_fr, with an id and a text column.
• In a derived table, whose name contains two underscores, each row points to its parent row with _parent_id.
• _names_fts is a full-text index of the names, much faster than LIKE '%...%'. The SQL tab's Examples show how to use it.

Values come back the way they're stored: numbers as regular numbers, never BigInt, text as strings, and an empty value as null. A column holding nested data comes as an array or an object. A computed column, such as COUNT(*), comes back as the database computed it.

Limits and errors

Try it in the SQL tab first

The game version's SQL tab runs queries on the same data, with the same limits, and shows the result as a table. It's the quickest way to get a query right before putting it in a script.

1. In Game Manager, open the bot's game version, go to its Game data tab, then to SQL.
2. Type a query, or pick one from Examples. They're grouped by topic: explore, names, monsters, items, harvesting, navigation, crafting, maps, quests and text.
3. Click Run, or press Ctrl+Enter. Next to the buttons, the tab shows the number of rows, stopped at the limit when some were left out, and how long the query took.
4. When the query returns what you want, copy it into your script, and replace the values you typed with ? and params.

The tab has no parameters, so write the values into the SQL while you try a query there. It also shows up to 500 rows, where a script gets 200 unless it passes a limit. When a query fails, the tab shows the same text a script would get. SQL describes the tab in full.

A complete example

This package adds a button that lists the sub-areas where a monster lives, with a join the lookups can't do:

import { session, botInfo } from "asterobot:bot";
import { query } from "asterobot:gamedata";
import { send } from "asterobot:protocol";

const SUB_AREAS_OF_MONSTER = `
  SELECT DISTINCT area.name
    FROM _names monster
    JOIN monsters__subareas link ON link._parent_id = monster.record_id
    JOIN _names area ON area.record_table = 'sub_areas'
                    AND area.record_id = link.value
                    AND area.language = monster.language
   WHERE monster.record_table = 'monsters'
     AND monster.language = ?
     AND monster.name = ?`;

export const actions = {
  subAreas: {
    label: "Find where a monster lives",
    args: {
      monster: { type: "string", label: "Monster name" },
    },
    async run({ monster }) {
      const result = await query(SUB_AREAS_OF_MONSTER, [session.language, monster]);
      if (result === undefined) throw new Error("No game data for this bot's game version");

      const areas = result.rows.map(([name]) => name);
      botInfo(`${monster}:`, areas.length > 0 ? areas.join(", ") : "no sub-area found");
    },
  },
};

export default async function behavior(launch) {
  // A new game connection starts with this message. MITM bots never launch
  // with "initial": the Dofus client has already identified their session.
  if (launch.reason === "initial") {
    await send("IdentificationRequest", {
      ticketKey: session.gameToken,
      languageCode: session.language,
    });
  }
}

The query takes the script's language as its first parameter, so type the monster's French name for now. The button appears under Package settings on the bot's Settings tab once the package runs, as in Lookups.