northwind.sql

-- Create the northwind database

CREATE DATABASE northwind;

-- CockroachDB only

USE northwind;

--
-- Name: customer_customer_demo; 
--

CREATE TABLE customer_customer_demo (
	customer_id character varying(5) NOT NULL,
	customer_type_id character varying(5) NOT NULL
);

--
-- Name: customer_demographics;
--

CREATE TABLE customer_demographics (
	customer_type_id character varying(5) NOT NULL,
	customer_desc text
);

--
-- Name: customers;
--

CREATE TABLE IF NOT EXISTS customers (
	customer_id character varying(5) NOT NULL,
	company_name character varying(40) NOT NULL,
	contact_name character varying(30),
	contact_title character varying(30),
	address character varying(60),
	city character varying(15),
	region character varying(15),
	postal_code character varying(10),
	country character varying(15),
	phone character varying(24),
	fax character varying(24)
);

--
-- Name: employees;
--

CREATE TABLE IF NOT EXISTS employees (
	employee_id smallint NOT NULL,
	last_name character varying(20) NOT NULL,
	first_name character varying(10) NOT NULL,
	title character varying(30),
	title_of_courtesy character varying(25),
	birth_date date,
	hire_date date,
	address character varying(60),
	city character varying(15),
	region character varying(15),
	postal_code character varying(10),
	country character varying(15),
	home_phone character varying(24),
	extension character varying(4),
	photo bytea,
	notes text,
	reports_to smallint,
	photo_path character varying(255)
);

--
-- Name: employee_territories;
--

CREATE TABLE IF NOT EXISTS employee_territories (
	employee_id smallint NOT NULL,
	territory_id character varying(20) NOT NULL
);

--
-- Name: order_details;
--

CREATE TABLE IF NOT EXISTS order_details (
	order_id smallint NOT NULL,
	product_id smallint NOT NULL,
	unit_price real NOT NULL,
	quantity smallint NOT NULL,
	discount real NOT NULL
);

--
-- Name: orders;
--

CREATE TABLE IF NOT EXISTS orders (
	order_id smallint NOT NULL,
	customer_id character varying(5),
	employee_id smallint,
	order_date date,
	required_date date,
	shipped_date date,
	ship_via smallint,
	freight real,
	ship_name character varying(40),
	ship_address character varying(60),
	ship_city character varying(15),
	ship_region character varying(15),
	ship_postal_code character varying(10),
	ship_country character varying(15)
);

--
-- Name: products;
--

CREATE TABLE IF NOT EXISTS products (
	product_id smallint NOT NULL,
	product_name character varying(40) NOT NULL,
	supplier_id smallint,
	category_id smallint,
	quantity_per_unit character varying(20),
	unit_price real,
	units_in_stock smallint,
	units_on_order smallint,
	reorder_level smallint,
	discontinued integer NOT NULL
);

--
-- Name: region;
--

CREATE TABLE IF NOT EXISTS region (
	region_id smallint NOT NULL,
	region_description character varying(60) NOT NULL
);

--
-- Name: shippers;
--

CREATE TABLE IF NOT EXISTS shippers (
	shipper_id smallint NOT NULL,
	company_name character varying(40) NOT NULL,
	phone character varying(24)
);

--
-- Name: suppliers;
--

CREATE TABLE IF NOT EXISTS suppliers (
	supplier_id smallint NOT NULL,
	company_name character varying(40) NOT NULL,
	contact_name character varying(30),
	contact_title character varying(30),
	address character varying(60),
	city character varying(15),
	region character varying(15),
	postal_code character varying(10),
	country character varying(15),
	phone character varying(24),
	fax character varying(24),
	homepage text
);

--
-- Name: territories;
--

CREATE TABLE IF NOT EXISTS territories (
	territory_id character varying(20) NOT NULL,
	territory_description character varying(60) NOT NULL,
	region_id smallint NOT NULL
);

--
-- Name: us_states;
--

CREATE TABLE IF NOT EXISTS us_states (
	state_id smallint NOT NULL,
	state_name character varying(100),
	state_abbr character varying(2),
	state_region character varying(50)
);

--
-- Name: transactions;
--

CREATE TABLE IF NOT EXISTS transactions (
    lane VARCHAR,
    id UUID,
    event INT,
    ts TIMESTAMP,
    PRIMARY KEY (lane, id, event)
);