isaac_resources

A very important table! As explained on the last page, every game resource is stored here: pills, runes, items, enemies, bosses...

Query Examples:

Find all bosses:

select * from isaac_resources where type = 1;

Find everything that starts with 'A':

select name from isaac_resources where lower(name) like 'a%';

Find all resources that are tagged as spacebar item:
Tags are saved as integer array. Use the '@>' operator to see if the integer array contains a specific tag.

select name from isaac_resources where tags @> array[139];

Find everything that comes from a mod:

select name, mod from isaac_resources where mod is not null;

Same, but joining the mods table for mod names:

select isaac_resources.name, mods.name
from isaac_resources
join mods on mods.id = isaac_resources.mod
where isaac_resources.mod is not null;


mods

Very simple. Only contains a primary key and the name of the mod.


mod_url

Saves external links for mods, for example to the steam workshop page or a wiki.

Query Examples:

Get all links for the Antibirth mod:

select mods.name, mod_url.name, mod_url.url
from mods
join mod_url on mod_url.mod = mods.id
where mods.name = 'Antibirth';


videos

Stores video data - everything except the timestamp at the end is directly pulled from the youtube API.

Query Examples

Search the video title for 'Trainwreck'

select * from videos where lower(title) like '%trainwreck%';

Search for videos that are longer than an hour

select * from videos where duration > 3600;

Find the episode with the most likes

select title from videos where likes = (select max(likes) from videos);


video_submissions

This is what you call 'an isaac episode'. Saves some metadata every time a user submits an episode that ties everything together.

Query Examples

Find all submissions for the first isaac episode

select * from video_submissions where video = 'c5PLC6nmOO4';

Find the most recent submission for the first isaac episode.
Make sure to always include the "latest" flag when querying submissions, because episodes can be submitted multiple times by multiple users. Otherwise you will most likely get duplicate entries or submissions with missing data.

select * from video_submissions where video = 'c5PLC6nmOO4' and latest = true;


played_characters

Every 'isaac episode' described in the table above can have multiple played characters, so every entry in this table is what you call 'a run'. Most of the time there will only be a single character per submission, but sometimes there can be quite a few (for example episodes where Northernlion plays as Keeper or The Lost).

Query Examples

Finding all characters and deaths from the infamous "Trainwreck" episode.
As described in the last section, always check for the "latest" data.

select game_character, died_from from played_characters where video = 'JfDnrW-pnXU' and latest = true;

Finding all characters NL killed by "Bombing Himself"

select game_character, died_from from played_characters where died_from = 'BombedHimself' and latest = true;

Same as above, but with video title and sorted by release date.
Because everything references the "video" table, you can get the video title with a simple join:

select videos.title, played_characters.game_character, played_characters.died_from
from played_characters
join videos on videos.id = played_characters.video
where played_characters.died_from = 'BombedHimself'
and played_characters.latest = true
order by videos.published desc;

Find all characters NL killed on a sunday:

select videos.title, played_characters.game_character, played_characters.died_from
from played_characters
join videos on videos.id = played_characters.video
where played_characters.died_from is not null
and played_characters.latest = true
and extract(DOW from videos.published) = 5;


played_floors

Saves all floors a "played_character" from the last section went through.

Query Examples

Find all floors from the first isaac episode (in chronological order)

select * from played_floors where video = 'c5PLC6nmOO4' and latest = true order by floor_number;

Find all floors on which NL died by bombing himself

select * from played_floors where died_from = 'BombedHimself' and latest = true;

Find the floor that was visited the most

select floor, count(floor) as times_visited
from played_floors
group by floor
order by times_visited desc;

Find the floor Northernlion spent the most time on

select floor, duration
from played_floors
where latest = true
and duration = (select max(duration) from played_floors);

...including the video title

select played_floors.floor, played_floors.duration, videos.title
from played_floors
join videos on videos.id = played_floors.video
where played_floors.latest = true
and played_floors.duration = (select max(duration) from played_floors);


gameplay_events

All gameplay events. Probably the most interesting table of all.



The very general "resource_one" and "resource_two" approach for all gameplay events might make it a little confusing about what is what.
Here is a list of how gameplay events correlate with resource_one and -two:

  1. Unspecified (unused, can be ignored)
  2. Character Died
    1. ID of the enemy that killed the character
    2. -
  3. Item Collected
    1. ID of the item that was collected
    2. ID of the item source that spawned the item (for example "ItemRoom" or "DemonJudgement")
  4. Down to the next Floor!
    1. The floor on which we are from now on
    2. -
  5. Bossfight
    1. ID of the boss
    2. -
  6. Pill used
    1. ID of the Pill
    2. -
  7. Tarot Card used
    1. ID of the Tarot Card
    2. -
  8. Rune used
    1. ID of the Rune
    2. -
  9. Trinket taken
    1. ID of the Trinket that was used from now on
    2. -
  10. Floor was Cursed
    1. ID of the curse that was present on this floor
    2. -
  11. Other Consumable used
    1. ID of the consumable
    2. -
  12. Transformation Complete
    1. The ID of the item that triggered the transformation completion
    2. The ID of the transformation
  13. Transformation Progress
    1. The ID of the item that contributed to the transformation progress
    2. The ID of the transformation
    3. How far into the transformation we are: for example if this is 2, this was guppy item number 2
  14. Last Floor (unused, ignore!)
  15. Absorbed Item
    1. The ID of the item that was absorbed
    2. The ID of the thing that absorbed the item (for example the void or black rune)
  16. Character Reroll
    1. The ID of the thing that rerolled the character (for example D100 or the 6-Room)
    2. -
  17. Won the run
    1. The ID of the character who won the run (for example "Isaac" or "TheLost")
    2. The ID of the floor on which the run ended ("BasementOne" or "WombTwo")
  18. Lost the run
    1. The ID of the character who died (for example "Isaac" or "TheLost")
    2. The ID of the floor on which the run ended ("BasementOne" or "WombTwo")
  19. Item Touched (picked up and put down again right away)
    1. ID of the item that was touched
    2. ID of the item source that spawned the item (for example "ItemRoom" or "DemonJudgement")
  20. Starting Trinket
    1. ID of the trinket with which the character started
    2. -
  21. Clicker (changed character with the clicker)
    1. Is always "Clicker"
    2. The ID of the new character
  22. Reroll Transform (transformation that happened after rerolling the character)
    1. ID of the thing that rerolled the character
    2. ID of the transformation that took place
  23. Respawn (respawning after death due to extra lives)
    1. ID of the enemy that killed the character
    2. -

Query Examples

All events of an episode in chronological order

select * from gameplay_events
where video = 'c5PLC6nmOO4'
and latest = true
order by run_number, action;

The events from the first floors of all 3 runs from the 'Trainwreck' episode, in chronological order

select * from gameplay_events
where video = 'JfDnrW-pnXU'
and latest = true
and floor_number = 1
order by run_number, action;

Count Bossfights ordered by encounters

select resource_one, count(resource_one) as encounters
from gameplay_events
where event_type = 4
and latest = true
group by resource_one
order by encounters desc;

Get all items that triggered a transformation:

select resource_one as item, resource_two as transformation
from gameplay_events
where event_type = 11
and latest = true;

...with their real names instead of their ID, retrieved by a join on isaac_resources:

select items.name, transformations.name
from gameplay_events
join isaac_resources as items on gameplay_events.resource_one = items.id
join isaac_resources as transformations on gameplay_events.resource_two = transformations.id
where event_type = 11
and latest = true;

Mom-kills on sunday releases: with a simple join on the videos table

select gameplay_events.resource_one, count(gameplay_events.resource_one) as sunday_encounters
from gameplay_events
join videos on gameplay_events.video = videos.id
where gameplay_events.event_type = 4
and gameplay_events.resource_one = 'Mom'
and gameplay_events.latest = true
and extract(DOW from videos.published) = 0
group by gameplay_events.resource_one;

Find all guppy-items that were collected after already being guppy:
resource_three counts the steps towards a transformation, so a simple check on > 3 is enough:

select * from gameplay_events
where event_type = 12
and resource_three > 3
and resource_two = 'Guppy'
and latest = true;


discussion_topics

Stores general themes, discussion topics and tangents NL went on.


quotes

Stores funny NL-quotes users submitted

Query Examples

Get all quotes for an episode

select * from quotes where video = 'c5PLC6nmOO4';

Search quotes containing the f-word

select * from quotes where lower(content) like('%fridge%');

Search quotes for videos that have "cat" in the title (by joining the video table)

select videos.title, quotes.content
from quotes
join videos on quotes.video = videos.id
where lower(videos.title) like ('%cat%');


thumbnails

Stores thumbnail info that was pulled from youtube. Not all that interesting.


transformative_resources

This table is used internally to automatically determine transformation progress throughout a run.