Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Foreign key links break for compound foreign keys #1098

Closed
simonw opened this issue Nov 15, 2020 · 5 comments
Closed

Foreign key links break for compound foreign keys #1098

simonw opened this issue Nov 15, 2020 · 5 comments

Comments

@simonw
Copy link
Owner

simonw commented Nov 15, 2020

Reported on Twitter here: https://twitter.com/ZaneSelvans/status/1328093641395548161

Maybe I'm doing something wrong here but the automatically generated links based foreign key relationships seem to be working here for utility_id_eia, but not for plant_id_eia & generator_id which seems odd: https://pudl-datasette-xl7xwcpe2a-uc.a.run.app/pudl/generators_eia860

Right now it seems like they're trying to, but with only one of the two keys, so it gives "Error 500. You did not supply a value for binding 2." Maybe only create the links when it's a simple foreign key?

@simonw
Copy link
Owner Author

simonw commented Nov 15, 2020

Need to replicate this in the fixtures, then fix it.

@simonw
Copy link
Owner Author

simonw commented Nov 15, 2020

Schema for that broken example:

CREATE TABLE generators_eia860 (
	id INTEGER NOT NULL, 
	plant_id_eia INTEGER, 
	generator_id TEXT, 
	report_date DATE, 
	operational_status_code TEXT, 
	operational_status TEXT, 
	ownership_code TEXT, 
	utility_id_eia INTEGER, 
	capacity_mw FLOAT, 
	summer_capacity_mw FLOAT, 
	winter_capacity_mw FLOAT, 
	energy_source_code_1 TEXT, 
	energy_source_code_2 TEXT, 
	energy_source_code_3 TEXT, 
	energy_source_code_4 TEXT, 
	energy_source_code_5 TEXT, 
	energy_source_code_6 TEXT, 
	fuel_type_code_pudl TEXT, 
	multiple_fuels BOOLEAN, 
	deliver_power_transgrid BOOLEAN, 
	syncronized_transmission_grid BOOLEAN, 
	turbines_num INTEGER, 
	planned_modifications BOOLEAN, 
	planned_net_summer_capacity_uprate_mw FLOAT, 
	planned_net_winter_capacity_uprate_mw FLOAT, 
	planned_uprate_date DATE, 
	planned_net_summer_capacity_derate_mw FLOAT, 
	planned_net_winter_capacity_derate_mw FLOAT, 
	planned_derate_date DATE, 
	planned_new_prime_mover_code TEXT, 
	planned_energy_source_code_1 TEXT, 
	planned_repower_date DATE, 
	other_planned_modifications BOOLEAN, 
	other_modifications_date DATE, 
	planned_retirement_date DATE, 
	carbon_capture BOOLEAN, 
	startup_source_code_1 TEXT, 
	startup_source_code_2 TEXT, 
	startup_source_code_3 TEXT, 
	startup_source_code_4 TEXT, 
	technology_description TEXT, 
	turbines_inverters_hydrokinetics TEXT, 
	time_cold_shutdown_full_load_code TEXT, 
	planned_new_capacity_mw FLOAT, 
	cofire_fuels BOOLEAN, 
	switch_oil_gas BOOLEAN, 
	nameplate_power_factor FLOAT, 
	minimum_load_mw FLOAT, 
	uprate_derate_during_year BOOLEAN, 
	uprate_derate_completed_date DATE, 
	current_planned_operating_date DATE, 
	summer_estimated_capability_mw FLOAT, 
	winter_estimated_capability_mw FLOAT, 
	retirement_date DATE, 
	PRIMARY KEY (id), 
	FOREIGN KEY(plant_id_eia, generator_id) REFERENCES generators_entity_eia (plant_id_eia, generator_id), 
	FOREIGN KEY(utility_id_eia) REFERENCES utilities_entity_eia (utility_id_eia), 
	CHECK (multiple_fuels IN (0, 1)), 
	CHECK (deliver_power_transgrid IN (0, 1)), 
	CHECK (syncronized_transmission_grid IN (0, 1)), 
	CHECK (planned_modifications IN (0, 1)), 
	CHECK (other_planned_modifications IN (0, 1)), 
	CHECK (carbon_capture IN (0, 1)), 
	CHECK (cofire_fuels IN (0, 1)), 
	CHECK (switch_oil_gas IN (0, 1)), 
	CHECK (uprate_derate_during_year IN (0, 1))
);

https://pudl-datasette-xl7xwcpe2a-uc.a.run.app/pudl/generators_entity_eia is:

CREATE TABLE generators_entity_eia (
	plant_id_eia INTEGER NOT NULL, 
	generator_id TEXT NOT NULL, 
	prime_mover_code TEXT, 
	duct_burners BOOLEAN, 
	operating_date DATE, 
	topping_bottoming_code TEXT, 
	solid_fuel_gasification BOOLEAN, 
	pulverized_coal_tech BOOLEAN, 
	fluidized_bed_tech BOOLEAN, 
	subcritical_tech BOOLEAN, 
	supercritical_tech BOOLEAN, 
	ultrasupercritical_tech BOOLEAN, 
	stoker_tech BOOLEAN, 
	other_combustion_tech BOOLEAN, 
	bypass_heat_recovery BOOLEAN, 
	rto_iso_lmp_node_id TEXT, 
	rto_iso_location_wholesale_reporting_id TEXT, 
	associated_combined_heat_power BOOLEAN, 
	original_planned_operating_date DATE, 
	operating_switch TEXT, 
	previously_canceled BOOLEAN, 
	PRIMARY KEY (plant_id_eia, generator_id), 
	FOREIGN KEY(plant_id_eia) REFERENCES plants_entity_eia (plant_id_eia), 
	CHECK (duct_burners IN (0, 1)), 
	CHECK (solid_fuel_gasification IN (0, 1)), 
	CHECK (pulverized_coal_tech IN (0, 1)), 
	CHECK (fluidized_bed_tech IN (0, 1)), 
	CHECK (subcritical_tech IN (0, 1)), 
	CHECK (supercritical_tech IN (0, 1)), 
	CHECK (ultrasupercritical_tech IN (0, 1)), 
	CHECK (stoker_tech IN (0, 1)), 
	CHECK (other_combustion_tech IN (0, 1)), 
	CHECK (bypass_heat_recovery IN (0, 1)), 
	CHECK (associated_combined_heat_power IN (0, 1)), 
	CHECK (previously_canceled IN (0, 1))
);

@simonw simonw modified the milestones: Datasette 0.52, Datasette Next Nov 28, 2020
@simonw
Copy link
Owner Author

simonw commented Nov 29, 2020

For the moment I'm going to solve this by teaching Datasette's internal introspection methods - in particular these ones - to ignore compound foreign keys entirely:

def get_outbound_foreign_keys(conn, table):
infos = conn.execute(f"PRAGMA foreign_key_list([{table}])").fetchall()
fks = []
for info in infos:
if info is not None:
id, seq, table_name, from_, to_, on_update, on_delete, match = info
fks.append(
{"column": from_, "other_table": table_name, "other_column": to_}
)
return fks
def get_all_foreign_keys(conn):
tables = [
r[0] for r in conn.execute('select name from sqlite_master where type="table"')
]
table_to_foreign_keys = {}
for table in tables:
table_to_foreign_keys[table] = {"incoming": [], "outgoing": []}
for table in tables:
infos = conn.execute(f"PRAGMA foreign_key_list([{table}])").fetchall()
for info in infos:
if info is not None:
id, seq, table_name, from_, to_, on_update, on_delete, match = info
if table_name not in table_to_foreign_keys:
# Weird edge case where something refers to a table that does
# not actually exist
continue
table_to_foreign_keys[table_name]["incoming"].append(
{"other_table": table, "column": to_, "other_column": from_}
)
table_to_foreign_keys[table]["outgoing"].append(
{"other_table": table_name, "column": from_, "other_column": to_}
)
return table_to_foreign_keys

@simonw simonw closed this as completed in deb0be4 Nov 29, 2020
simonw added a commit that referenced this issue Nov 29, 2020
@simonw
Copy link
Owner Author

simonw commented Nov 29, 2020

@simonw
Copy link
Owner Author

simonw commented Nov 29, 2020

Demo of the fix: https://latest.datasette.io/fixtures/foreign_key_references (the compound foreign key columns do not link to anything)

@simonw simonw removed this from the Datasette Next milestone Nov 29, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant