{"routes": {"columns": [[0, "rid", "INTEGER", 0, null, 1], [1, "dst_apid", "INTEGER", 0, null, 0], [2, "dst_ap", "varchar(4)", 0, null, 0], [3, "src_apid", "bigint", 0, null, 0], [4, "src_ap", "varchar(4)", 0, null, 0], [5, "alid", "bigint", 0, null, 0], [6, "airline", "varchar(4)", 0, null, 0], [7, "codeshare", "TEXT", 0, null, 0]], "primary_keys": ["rid"], "foreign_keys": [[0, 0, "airlines", "alid", "alid", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "airports", "src_apid", "apid", "NO ACTION", "NO ACTION", "NONE"], [2, 0, "airports", "dst_apid", "apid", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "airports": {"columns": [[0, "apid", "INTEGER", 0, null, 1], [1, "name", "TEXT", 1, null, 0], [2, "city", "TEXT", 0, null, 0], [3, "country", "TEXT", 0, null, 0], [4, "x", "REAL", 0, null, 0], [5, "y", "REAL", 0, null, 0], [6, "elevation", "bigint", 0, null, 0], [7, "iata", "character varchar(3)", 0, null, 0], [8, "icao", "character varchar(4)", 0, null, 0]], "primary_keys": ["apid"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "airlines": {"columns": [[0, "alid", "INTEGER", 0, null, 1], [1, "name", "TEXT", 0, null, 0], [2, "iata", "varchar(2)", 0, null, 0], [3, "icao", "varchar(3)", 0, null, 0], [4, "callsign", "TEXT", 0, null, 0], [5, "country", "TEXT", 0, null, 0], [6, "active", "varchar(2)", 0, null, 0]], "primary_keys": ["alid"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}}
CREATE TABLE routes (
    rid INTEGER   PRIMARY KEY,
    dst_apid INTEGER,
    dst_ap varchar(4),
    src_apid bigint,
    src_ap varchar(4),
    alid bigint,
    airline varchar(4),
    codeshare TEXT,
    FOREIGN KEY (alid) REFERENCES airlines(alid),
    FOREIGN KEY (src_apid) REFERENCES airports(apid),
    FOREIGN KEY (dst_apid) REFERENCES airports(apid)
);

CREATE TABLE airports (
    apid INTEGER   PRIMARY KEY,
    name TEXT NOT NULL,
    city TEXT,
    country TEXT,
    x REAL,
    y REAL,
    elevation bigint,
    iata character varchar(3),
    icao character varchar(4)
);

CREATE TABLE airlines (
    alid INTEGER   PRIMARY KEY,
    name TEXT,
    iata varchar(2),
    icao varchar(3),
    callsign TEXT,
    country TEXT,
    active varchar(2)
);