SQL

The SQL tab runs your own queries on a game version's game data, for the questions the other tabs can't answer: a join between tables, a count, a sort. Queries are written in SQLite's dialect of SQL. The game data is opened read-only, so nothing you run can change or break it.

Open it

On the version's Game page, open Game data, then SQL. The editor already holds the first example query, ready to run.

Run a query

1. Type a query in the editor, or change the one that's there.
2. Click Run, or press Ctrl+Enter (Cmd+Enter on a Mac).

The editor colours the SQL and suggests completions as you type:

• the columns of the tables your query already names;
• every table name;
• common SQL keywords and functions;
• inside single quotes, the names of records in the Language chosen in the Game page's header: typing 'Bouf suggests Bouftou.

Read the results

Next to the buttons, the tab shows the number of rows, stopped at the limit when the query had more rows than the tab displays, and how long the query took, in milliseconds.

The results table has one column per selected column, in the query's order, and two columns can share a name. Nested values show as JSON. Long values are cut: point at a cell to read it in full. A query that finds nothing shows "The query returned no rows."

The tab displays up to 500 rows. To see further rows, narrow the query with WHERE, or page through it with LIMIT and OFFSET.

Examples

Examples opens a menu of example queries grouped by topic. Picking one replaces what's in the editor, and the notes about that topic appear under the editor: which tables hold what, and the traps to avoid.

Several examples look for French names, with conditions such as n.language = 'fr'. Change fr to en, de, es or pt for another language.

Writing your own queries

• _catalog lists every table, with its number of rows and whether it's derived:

  SELECT name, row_count, derived FROM _catalog ORDER BY row_count DESC;

• Names go through _names, 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.

• Put text between single quotes, as in 'monsters'. Double quotes only name a table or a column.

• To search names or texts, the full-text indexes _names_fts and text_fr_fts (one per language) are much faster than LIKE '%...%'. The names and text examples show how to use them.

The Tables tab of Game data shows every table's columns and their types, which helps while you write.

Read-only and limits

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.

When the editor holds several statements separated by semicolons, all of them run, and the tab shows the result of the last one.

When a query fails

A red alert shows the reason as SQLite gives it: a text starting with datacenter: that names the problem, such as no such column: nmae for a mistyped column name. When Asterobot gave no reason, the alert says The query failed. Fix the query and run it again.

Use a query in a script

A script runs the same kind of query with query() from asterobot:gamedata, on the game data of its bot's game version. The SQL tab is the quickest place to get a query right before putting it in a script: SQL queries explains the rest, including parameters.