Import rebrickable.com/downloads into an SQLite database using a pure-SQLite script

import_rebrickable.sql

-- Usage:
-- 1. Download and extract all files from https://rebrickable.com/downloads/
-- 2. Pipe this script into SQLite CLI with this command:
--        cat import_rebrickable.sql | sqlite3 output_rebrickable_database.db

.bail on
.mode csv
.separator ","

PRAGMA foreign_keys = ON;


CREATE TABLE `colors` (
    `id` INTEGER NOT NULL PRIMARY KEY,
    `name` TEXT NOT NULL,
    `rgb` TEXT NOT NULL,
    `__is_trans` TEXT,
    `num_parts` INTEGER NOT NULL,
    `num_sets` INTEGER NOT NULL,
    `__year_from` TEXT,
    `__year_to` TEXT
) STRICT;
.import --csv --skip 1 colors.csv colors

ALTER TABLE `colors` ADD COLUMN `is_trans` INTEGER NOT NULL DEFAULT 0 CHECK (`is_trans` IN (0, 1));
ALTER TABLE `colors` ADD COLUMN `year_from` INTEGER;
ALTER TABLE `colors` ADD COLUMN `year_to` INTEGER;
UPDATE `colors` SET `is_trans` = (`__is_trans` = 'True');
UPDATE `colors` SET `year_from` = IIF(`__year_from` = '', NULL, CAST(`__year_from` AS INTEGER));
UPDATE `colors` SET `year_to` = IIF(`__year_to` = '', NULL, CAST(`__year_to` AS INTEGER));
ALTER TABLE `colors` DROP COLUMN `__is_trans`;
ALTER TABLE `colors` DROP COLUMN `__year_from`;
ALTER TABLE `colors` DROP COLUMN `__year_to`;


CREATE TABLE `themes` (
    `id` INTEGER NOT NULL PRIMARY KEY,
    `name` TEXT NOT NULL,
    `__parent_id` TEXT
) STRICT;
.import --csv --skip 1 themes.csv themes

ALTER TABLE `themes` ADD COLUMN `parent_id` INTEGER REFERENCES `themes` (`id`);
UPDATE `themes` SET `parent_id` = IIF(`__parent_id` = '', NULL, CAST(`__parent_id` AS INTEGER));
ALTER TABLE `themes` DROP COLUMN `__parent_id`;


CREATE TABLE `part_categories` (
    `id` INTEGER NOT NULL PRIMARY KEY,
    `name` TEXT NOT NULL
) STRICT;
.import --csv --skip 1 part_categories.csv part_categories


CREATE TABLE `minifigs` (
    `fig_num` TEXT NOT NULL PRIMARY KEY CHECK (`fig_num` LIKE 'fig-%'),
    `name` TEXT NOT NULL,
    `num_parts` INTEGER NOT NULL,
    `img_url` TEXT
) STRICT;
.import --csv --skip 1 minifigs.csv minifigs

UPDATE `minifigs` SET `img_url` = NULL WHERE `img_url` = '';


CREATE TABLE `parts` (
    `part_num` TEXT NOT NULL PRIMARY KEY,
    `name` TEXT NOT NULL,
    `part_cat_id` INTEGER NOT NULL REFERENCES `part_categories` (`id`),
    `part_material` TEXT NOT NULL
) STRICT;
.import --csv --skip 1 parts.csv parts


CREATE TABLE `sets` (
    `set_num` TEXT NOT NULL PRIMARY KEY,
    `name` TEXT NOT NULL,
    `year` INTEGER NOT NULL,
    `theme_id` INTEGER NOT NULL REFERENCES `themes` (`id`),
    `num_parts` INTEGER NOT NULL,
    `img_url` TEXT
) STRICT;
.import --csv --skip 1 sets.csv sets

UPDATE `sets` SET `img_url` = NULL WHERE `img_url` = '';


CREATE TABLE `part_relationships` (
    `rel_type` TEXT NOT NULL CHECK (`rel_type` IN ('P', 'R', 'B', 'M', 'T', 'A')),
    `child_part_num` TEXT NOT NULL REFERENCES `parts` (`part_num`),
    `parent_part_num` TEXT NOT NULL REFERENCES `parts` (`part_num`)
) STRICT;
.import --csv --skip 1 part_relationships.csv part_relationships


CREATE TABLE `elements` (
    `element_id` TEXT NOT NULL PRIMARY KEY,
    `part_num` TEXT NOT NULL REFERENCES `parts` (`part_num`),
    `color_id` INTEGER NOT NULL REFERENCES `colors` (`id`),
    `__design_id` TEXT
) STRICT;
.import --csv --skip 1 elements.csv elements

ALTER TABLE `elements` ADD COLUMN `design_id` INTEGER;
UPDATE `elements` SET `design_id` = IIF(`__design_id` = '', NULL, CAST(`__design_id` AS INTEGER));
ALTER TABLE `elements` DROP COLUMN `__design_id`;


CREATE TABLE `inventories` (
    `id` INTEGER NOT NULL PRIMARY KEY,
    `version` INTEGER NOT NULL,
    `__set_or_fig_num` TEXT
) STRICT;
.import --csv --skip 1 inventories.csv inventories

ALTER TABLE `inventories` ADD COLUMN `set_num` TEXT REFERENCES `sets` (`set_num`);
ALTER TABLE `inventories` ADD COLUMN `fig_num` TEXT REFERENCES `minifigs` (`fig_num`);
UPDATE `inventories` SET `set_num` = `__set_or_fig_num` WHERE `__set_or_fig_num` NOT LIKE 'fig-%';
UPDATE `inventories` SET `fig_num` = `__set_or_fig_num` WHERE `__set_or_fig_num` LIKE 'fig-%';
ALTER TABLE `inventories` DROP COLUMN `__set_or_fig_num`;


CREATE TABLE `inventory_parts` (
    `inventory_id` INTEGER NOT NULL REFERENCES `inventories` (`id`),
    `part_num` TEXT NOT NULL REFERENCES `parts` (`part_num`),
    `color_id` INTEGER NOT NULL REFERENCES `colors` (`id`),
    `quantity` INTEGER NOT NULL,
    `__is_spare` TEXT,
    `img_url` TEXT
) STRICT;
.import --csv --skip 1 inventory_parts.csv inventory_parts

UPDATE `inventory_parts` SET `img_url` = NULL WHERE `img_url` = '';

ALTER TABLE `inventory_parts` ADD COLUMN `is_spare` INTEGER NOT NULL DEFAULT 0 CHECK (`is_spare` IN (0, 1));
UPDATE `inventory_parts` SET `is_spare` = (`__is_spare` = 'True');
ALTER TABLE `inventory_parts` DROP COLUMN `__is_spare`;


CREATE TABLE `inventory_minifigs` (
    `inventory_id` INTEGER NOT NULL REFERENCES `inventories` (`id`),
    `fig_num` TEXT NOT NULL REFERENCES `minifigs` (`fig_num`),
    `quantity` INTEGER NOT NULL
) STRICT;
.import --csv --skip 1 inventory_minifigs.csv inventory_minifigs


CREATE TABLE `inventory_sets` (
    `inventory_id` INTEGER NOT NULL REFERENCES `inventories` (`id`),
    `set_num` TEXT NOT NULL REFERENCES `sets` (`set_num`),
    `quantity` INTEGER NOT NULL
) STRICT;
.import --csv --skip 1 inventory_sets.csv inventory_sets