def _force_remote(cursor: sqlite3.Cursor, local_asset: Asset,
full_insert: str) -> None:
"""Force the remote entry into the database by deleting old one and doing the full insert.
May raise an sqlite3 error if something fails.
"""
cursor.executescript('PRAGMA foreign_keys = OFF;')
if local_asset.asset_type == AssetType.ETHEREUM_TOKEN:
token = EthereumToken.from_asset(local_asset)
cursor.execute(
'DELETE FROM ethereum_tokens WHERE address=?;',
(token.ethereum_address, ), # type: ignore # token != None
)
else:
cursor.execute(
'DELETE FROM common_asset_details WHERE asset_id=?;',
(local_asset.identifier, ),
)
cursor.execute(
'DELETE FROM assets WHERE identifier=?;',
(local_asset.identifier, ),
)
cursor.executescript('PRAGMA foreign_keys = ON;')
# Insert new entry. Since identifiers are the same, no foreign key constrains should break
executeall(cursor, full_insert)
AssetResolver().clean_memory_cache(local_asset.identifier.lower())
def _init_db(cursor: sqlite3.Cursor):
dir_path = os.path.dirname(os.path.realpath(__file__))
with open(os.path.join(dir_path, 'init.sql'), 'r') as initfile:
query = initfile.read()
cursor.executescript(query)
print('Database initialized!')
def execute_scripts(file_paths: List[str], cursor: sqlite3.Cursor):
for path in file_paths:
try:
file = open(path)
script = file.read()
cursor.executescript(script)
print("Executed file: " + path)
except OSError as err:
print("Error while executing file: " + path, err)
except sqlite3.Error as err:
print("Error while executing file: " + path, err)
def initialize_database(cur: sqlite3.Cursor):
# Make some fresh tables using executescript()
cur.executescript('''
DROP TABLE IF EXISTS Companies;
DROP TABLE IF EXISTS Productions;
DROP TABLE IF EXISTS Cities;
DROP TABLE IF EXISTS Artists;
DROP TABLE IF EXISTS Operas;
DROP TABLE IF EXISTS Casts;
CREATE TABLE Cities (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
city TEXT,
country TEXT,
UNIQUE (city, country)
);
CREATE TABLE Companies (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
company TEXT UNIQUE
);
CREATE TABLE Productions (
id INTEGER NOT NULL PRIMARY KEY UNIQUE,
city_id,
company_id INTEGER,
opera_id,
performances INTEGER
);
CREATE TABLE Operas (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
title TEXT,
composer TEXT,
UNIQUE (title, composer)
);
CREATE TABLE Artists (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
name TEXT UNIQUE
);
CREATE TABLE Casts (
artist_id INTEGER,
production_id INTEGER,
UNIQUE (artist_id, production_id)
);
''')
def create_tables(cursor: Cursor):
''' Create all tables required by marksman
Args:
cursor (Cursor): a sqlite3 Cursor object
'''
logger.info('Starting creation of tables')
try:
cursor.executescript(
___('''
DROP TABLE IF EXISTS students ;
CREATE TABLE students(
roll INTEGER NOT NULL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL
);
DROP TABLE IF EXISTS exams ;
CREATE TABLE exams(
uid INTEGER NOT NULL PRIMARY KEY,
name TEXT NOT NULL
);
DROP TABLE IF EXISTS marks ;
CREATE TABLE marks(
student INTEGER NOT NULL,
exam INTEGER NOT NULL,
marks INTEGER NOT NULL,
FOREIGN KEY (student)
REFERENCES students(roll)
ON DELETE CASCADE,
FOREIGN KEY (exam)
REFERENCES exams(uid)
ON DELETE CASCADE,
PRIMARY KEY (student,exam)
);
'''))
except Exception as err:
logger.warning(
'Failed to create tables. Delete the database file and try again')
logger.exception(err)
else:
logger.info('Successfully created tables')
def initialize(cur: sqlite3.Cursor):
cur.executescript('''
CREATE TABLE IF NOT EXISTS CitiesCopy(
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
city TEXT,
country TEXT,
lat REAL,
lng REAL,
UNIQUE (city, country)
);
INSERT INTO CitiesCopy(id, city, country)
SELECT id, city, country
FROM Cities;
DROP TABLE Cities;
ALTER TABLE CitiesCopy RENAME TO Cities;
''')
def create_tables(cursor: sqlite3.Cursor):
create_railroad_stations_query = """
CREATE TABLE IF NOT EXISTS [r_transportation_railroad_stations]( -- Table with all stations
[actuality] BOOL NOT NULL DEFAULT 1,
[code] VARCHAR(6) PRIMARY KEY NOT NULL,
[name] VARCHAR(60) NOT NULL,
[railroad_code] VARCHAR(3) REFERENCES r_transportation_railroads([code]) ON DELETE CASCADE NOT NULL,
[type] VARCHAR(2) NOT NULL);"""
cursor.execute(create_railroad_stations_query)
create_station_operations_query = """
CREATE TABLE IF NOT EXISTS [r_transportation_station_operations]( -- Table with operations allowed for a station
[station_code] VARCHAR(6) REFERENCES r_transportation_railroad_stations([code]) ON DELETE CASCADE,
[operation_code] VARCHAR(3) REFERENCES r_transportation_operations([code]) ON DELETE CASCADE);
CREATE UNIQUE INDEX IF NOT EXISTS [duplicate_preventing_operations] -- Index preventing adding duplicates of operation to the same station
ON [r_transportation_station_operations](
[station_code],
[operation_code]);
"""
cursor.executescript(create_station_operations_query)
create_transit_distances_query = """
CREATE TABLE IF NOT EXISTS [r_transportation_transit_distances]( -- Table of distances between stations to transit points / transit points
[code_from] VARCHAR(6) REFERENCES r_transportation_railroad_stations([code]) ON DELETE CASCADE,
[code_to] VARCHAR(6) REFERENCES r_transportation_railroad_stations([code]) ON DELETE CASCADE,
[transit_distance] INTEGER);
CREATE UNIQUE INDEX IF NOT EXISTS [duplicate_preventing_transit] -- Index preventing adding duplicates of distance between the same stations
ON [r_transportation_transit_distances](
[code_from],
[code_to]);
"""
cursor.executescript(create_transit_distances_query)
create_railroad_parts_query = """
CREATE TABLE IF NOT EXISTS [r_transportation_railroad_parts]( -- Table of railroad parts
[code] VARCHAR(6) PRIMARY KEY NOT NULL,
[name] VARCHAR(100) NOT NULL,
[railroad_code] VARCHAR(3) REFERENCES r_transportation_railroads([code]) ON DELETE CASCADE);"""
cursor.execute(create_railroad_parts_query)
create_railroad_part_distances_query = """
CREATE TABLE IF NOT EXISTS [r_transportation_railroad_part_distances]( -- Table of distances between two stations of one railroad part
[part_code] VARCHAR(6) REFERENCES r_transportation_railroad_parts([code]) ON DELETE CASCADE,
[code_from] VARCHAR(6) REFERENCES r_transportation_railroad_stations([code]) ON DELETE CASCADE,
[code_to] VARCHAR(6) REFERENCES r_transportation_railroad_stations([code]) ON DELETE CASCADE,
[distance_between_stations] INTEGER);
CREATE UNIQUE INDEX IF NOT EXISTS [duplicate_preventing_part_distances] -- Index preventing adding duplicates of distance between the same stations
ON [r_transportation_railroad_part_distances](
[code_from],
[code_to]);"""
cursor.executescript(create_railroad_part_distances_query)
def sqlite3_schema(cursor: sqlite3.Cursor) -> None:
"""SQLite3 Schema for USDA's FoodData Central database"""
cursor.executescript('''
CREATE TABLE acquisition_sample (
"fdc_id_of_sample_food" INT REFERENCES food(fdc_id),
"fdc_id_of_acquisition_food" INT REFERENCES food(fdc_id),
PRIMARY KEY(fdc_id_of_sample_food, fdc_id_of_acquisition_food)
);
CREATE TABLE agricultural_acquisition (
"fdc_id" INT NOT NULL PRIMARY KEY,
"acquisition_date" TEXT
CHECK(acquisition_date IS NULL OR
acquisition_date GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]' IS 1),
"market_class" TEXT,
"treatment" TEXT,
"state" TEXT
);
CREATE TABLE branded_food (
"fdc_id" INT NOT NULL PRIMARY KEY REFERENCES food(fdc_id),
"brand_owner" TEXT, -- XXX Inconsistent names
"gtin_upc" TEXT,
"ingredients" TEXT,
"serving_size" REAL,
"serving_size_unit" TEXT
CHECK(serving_size_unit IN ('g', 'ml')),
"household_serving_fulltext" TEXT,
"branded_food_category" TEXT,
"data_source" TEXT
CHECK(data_source IN ('GDSN', 'LI')),
"modified_date" TEXT
CHECK(modified_date IS NULL OR
modified_date GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]' IS 1),
"available_date" TEXT
CHECK(available_date IS NULL OR
available_date GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]' IS 1)
);
CREATE INDEX idx_branded_food_gtin_upc ON branded_food (gtin_upc);
CREATE INDEX idx_branded_food_branded_food_category ON branded_food (branded_food_category);
CREATE TABLE food (
fdc_id INT NOT NULL PRIMARY KEY,
data_type TEXT,
description TEXT,
food_category_id INT REFERENCES food_category(id),
publication_date TEXT
CHECK(publication_date IS NULL OR
publication_date GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]' IS 1)
);
CREATE INDEX idx_food_data_type ON food (data_type);
CREATE INDEX idx_food_food_category_id ON food (food_category_id);
CREATE TABLE food_attribute (
"id" INT NOT NULL PRIMARY KEY,
"fdc_id" INT REFERENCES food(fdc_id),
"seq_num" INT,
"food_attribute_type_id" INT REFERENCES food_attribute_type(id),
"name" TEXT,
"value" TEXT
);
CREATE INDEX idx_food_attribute_fdc_id ON food_attribute (fdc_id);
CREATE INDEX idx_food_attribute_food_attribute_type_id ON food_attribute (food_attribute_type_id);
CREATE TABLE food_attribute_type (
"id" INT NOT NULL PRIMARY KEY,
"name" TEXT,
"description" TEXT
);
CREATE TABLE food_calorie_conversion_factor (
"food_nutrient_conversion_factor_id" INT NOT NULL PRIMARY KEY REFERENCES food_nutrient_conversion_factor(id),
"protein_value" REAL,
"fat_value" REAL,
"carbohydrate_value" REAL
);
CREATE TABLE food_category (
"id" INT NOT NULL PRIMARY KEY,
"code" TEXT,
"description" TEXT
);
CREATE TABLE food_component (
"id" INT NOT NULL PRIMARY KEY,
"fdc_id" INT REFERENCES food(fdc_id),
"name" TEXT,
"pct_weight" REAL,
"is_refuse" TEXT
CHECK(is_refuse IN ('Y', 'N')),
"gram_weight" REAL,
"data_points" INT,
"min_year_acquired" TEXT
CHECK(min_year_acquired IS NULL OR
min_year_acquired GLOB '[0-9][0-9][0-9][0-9]' IS 1)
);
CREATE INDEX idx_food_component_fdc_id ON food_component (fdc_id);
-- XXX Field Descriptions describes "food_fat_conversion_factor" but there is no table for it.
-- XXX File is missing?
CREATE TABLE food_nutrient (
"id" INT NOT NULL PRIMARY KEY,
"fdc_id" INT REFERENCES food(fdc_id),
"nutrient_id" INT REFERENCES nutrient(id),
"amount" REAL,
"data_points" INT,
"derivation_id" INT REFERENCES food_nutrient_derivation(id),
-- XXX Missing standard_error from Field Descriptions
"min" REAL,
"max" REAL,
"median" REAL,
"footnote" TEXT,
"min_year_acquired" TEXT
CHECK(min_year_acquired IS NULL OR
min_year_acquired GLOB '[0-9][0-9][0-9][0-9]' IS 1)
);
CREATE INDEX idx_food_nutrient_fdc_id ON food_nutrient (fdc_id);
CREATE INDEX idx_food_nutrient_nutrient_id ON food_nutrient (nutrient_id);
CREATE INDEX idx_food_nutrient_derivation_id ON food_nutrient (derivation_id);
CREATE TABLE food_nutrient_conversion_factor (
"id" INT NOT NULL PRIMARY KEY,
"fdc_id" INT REFERENCES food(fdc_id)
);
CREATE INDEX idx_food_nutrient_conversion_factor_fdc_id ON food_nutrient_conversion_factor (fdc_id);
CREATE TABLE food_nutrient_derivation (
"id" INT NOT NULL PRIMARY KEY,
"code" TEXT,
"description" TEXT,
"source_id" INT REFERENCES food_nutrient_source(id)
);
CREATE INDEX idx_food_nutrient_derivation_source_id ON food_nutrient_derivation (source_id);
CREATE TABLE food_nutrient_source (
"id" INT NOT NULL PRIMARY KEY,
"code" INT UNIQUE, -- Code for source (4=calculated). XXX FK to ?
"description" TEXT
);
CREATE TABLE food_portion (
"id" INT NOT NULL PRIMARY KEY,
"fdc_id" INT REFERENCES food(fdc_id),
"seq_num" INT,
"amount" REAL,
"measure_unit_id" INT REFERENCES measure_unit(id),
"portion_description" TEXT,
"modifier" TEXT,
"gram_weight" REAL,
"data_points" INT,
"footnote" TEXT,
"min_year_acquired" TEXT
CHECK(min_year_acquired IS NULL OR
min_year_acquired GLOB '[0-9][0-9][0-9][0-9]' IS 1)
);
CREATE INDEX idx_food_portion_fdc_id ON food_portion (fdc_id);
CREATE INDEX idx_food_portion_measure_unit_id ON food_portion (measure_unit_id);
CREATE TABLE food_protein_conversion_factor (
"food_nutrient_conversion_factor_id" INT NOT NULL PRIMARY KEY REFERENCES food_nutrient_conversion_factor(id),
"value" REAL
);
CREATE TABLE foundation_food (
"fdc_id" INT NOT NULL PRIMARY KEY REFERENCES food(fdc_id),
"NDB_number" INT UNIQUE,
"footnote" TEXT
);
CREATE TABLE input_food (
"id" INT NOT NULL PRIMARY KEY,
"fdc_id" INT REFERENCES food(fdc_id),
"fdc_id_of_input_food" INT REFERENCES food(fdc_id),
"seq_num" INT,
"amount" REAL,
"sr_code" INT, -- NDB code of SR food XXX but not a FK
"sr_description" TEXT,
"unit" TEXT, -- Unit of measure (but inconsistent)
"portion_code" INT, -- Code for portion description XXX FK?
"portion_description" TEXT,
"gram_weight" REAL,
"retention_code" INT,
"survey_flag" INT
);
CREATE INDEX idx_input_food_fdc_id ON input_food (fdc_id);
CREATE INDEX idx_input_food_fdc_id_of_input_food ON input_food (fdc_id_of_input_food);
CREATE TABLE lab_method (
"id" INT NOT NULL PRIMARY KEY,
"description" TEXT,
"technique" TEXT
);
CREATE TABLE lab_method_code (
"id" INT NOT NULL PRIMARY KEY,
"lab_method_id" INT REFERENCES lab_method(id),
"code" TEXT
);
CREATE INDEX idx_lab_method_code_lab_method_id ON lab_method_code (lab_method_id);
CREATE TABLE lab_method_nutrient (
"id" INT NOT NULL PRIMARY KEY,
"lab_method_id" INT REFERENCES lab_method(id),
"nutrient_id" INT -- XXX this constraint fails: REFERENCES nutrient(id)
);
CREATE INDEX idx_lab_method_nutrient_lab_method_id ON lab_method_nutrient (lab_method_id);
CREATE TABLE market_acquisition (
"fdc_id" INT NOT NULL PRIMARY KEY REFERENCES food(fdc_id),
"brand_description" TEXT,
"expiration_date" TEXT
CHECK(expiration_date IS NULL OR
expiration_date GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]' IS 1),
"label_weight" REAL,
"location" TEXT,
"acquisition_date" TEXT
CHECK(acquisition_date IS NULL OR
acquisition_date GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]' IS 1),
"sales_type" TEXT,
"sample_lot_nbr" INT,
"sell_by_date" TEXT
CHECK(sell_by_date IS NULL OR
sell_by_date GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]' IS 1),
"store_city" TEXT,
"store_name" TEXT,
"store_state" TEXT,
"upc_code" TEXT
);
CREATE TABLE measure_unit (
"id" INT NOT NULL PRIMARY KEY,
"name" TEXT UNIQUE
);
CREATE TABLE nutrient (
"id" INT NOT NULL PRIMARY KEY,
"name" TEXT,
"unit_name" TEXT,
"nutrient_nbr" INT UNIQUE,
"rank" INT -- XXX Not documented
);
-- XXX Missing table nutrient_analysis_details per Field Descriptions
CREATE TABLE nutrient_incoming_name (
"id" INT NOT NULL PRIMARY KEY,
"name" TEXT,
"nutrient_id" INT REFERENCES nutrient(id)
);
CREATE INDEX idx_nutrient_incoming_name_nutrient_id ON nutrient_incoming_name (nutrient_id);
CREATE TABLE retention_factor (
"id" INT NOT NULL PRIMARY KEY,
"code" TEXT,
"food_group_id" INT REFERENCES food_category(id),
"description" TEXT
);
CREATE INDEX idx_retention_factor_food_group_id ON retention_factor (food_group_id);
CREATE TABLE sample_food (
"fdc_id" INT NOT NULL PRIMARY KEY REFERENCES food(fdc_id)
);
CREATE TABLE sr_legacy_food (
"fdc_id" INT NOT NULL PRIMARY KEY REFERENCES food(fdc_id),
"NDB_number" INT UNIQUE -- XXX doc says starts at 100k but not in practice
);
CREATE TABLE sub_sample_food (
"fdc_id" INT NOT NULL PRIMARY KEY REFERENCES food(fdc_id),
"fdc_id_of_sample_food" INT REFERENCES food(fdc_id)
);
CREATE INDEX idx_sub_sample_food_fdc_id_of_sample_food ON sub_sample_food (fdc_id_of_sample_food);
CREATE TABLE sub_sample_result (
"food_nutrient_id" INT NOT NULL PRIMARY KEY REFERENCES food_nutrient(id),
"adjusted_amount" REAL,
"lab_method_id" INT REFERENCES lab_method(id), -- XXX cannot use this because of broken refs: REFERENCES lab_method(id),
"nutrient_name" TEXT
);
CREATE INDEX idx_sub_sample_result_lab_method_id ON sub_sample_result (lab_method_id);
CREATE TABLE survey_fndds_food (
"fdc_id" INT NOT NULL PRIMARY KEY REFERENCES food(fdc_id),
"food_code" INT UNIQUE,
"wweia_category_code" INT REFERENCES wweia_food_category(wweia_food_category_code),
"start_date" TEXT
CHECK(start_date IS NULL OR
start_date GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]' IS 1),
"end_date" TEXT
CHECK(end_date IS NULL OR
end_date GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]' IS 1)
);
CREATE INDEX idx_survey_fndds_food_wweia_category_code ON survey_fndds_food (wweia_category_code);
CREATE TABLE wweia_food_category (
"wweia_food_category_code" INT NOT NULL PRIMARY KEY,
"wweia_food_category_description" TEXT
);
''')
def callback(cursor: Cursor):
cursor.executescript(sql) # Runs multiple sqls.
