def delete_duplicates(cursor, city, used_cities):
"""Once all the checks are done, we can delete the rows from the database.
Remove the first ID from the list so that one of the rows remains.
"""
deleted_rows = 0
geography_dir = Path(MYCROFT_DB_DIR).joinpath("geography_schema")
sql = get_sql_from_file(
str(geography_dir.joinpath("delete_duplicate_cities.sql")))
if used_cities:
sql += " and id not in %(used_cities)s"
args = dict(
city_ids=tuple(city["city_ids"]),
max_population=city["max_population"],
used_cities=tuple(used_cities),
)
else:
args = dict(
city_ids=tuple(city["city_ids"]),
max_population=city["max_population"],
)
request = DatabaseRequest(sql, args)
result = cursor.delete(request)
deleted_rows += result
print(f"Deleted {deleted_rows} from the geography.city table")
def _get_agreement_content_id(self, agreement_id: str) -> int:
request = DatabaseRequest(sql=get_sql_from_file(
path.join(SQL_DIR, 'get_agreement_content_id.sql')),
args=dict(agreement_id=agreement_id))
result = self.cursor.select_one(request)
return result['content_id']
def _build_db_request(self,
sql_file_name: str,
args: dict = None,
sql_vars: dict = None):
"""Build a DatabaseRequest object containing a query and args"""
sql = get_sql_from_file(path.join(self.sql_dir, sql_file_name))
if sql_vars is not None:
sql = sql.format(**sql_vars)
return DatabaseRequest(sql, args)
def get_duplicate_cities(cursor):
"""Get a list of the cities that appear multiple times in the city table."""
geography_dir = Path(MYCROFT_DB_DIR).joinpath("geography_schema")
sql = get_sql_from_file(
str(geography_dir.joinpath("get_duplicated_cities.sql")))
request = DatabaseRequest(sql)
result = cursor.select_all(request)
print("Removing duplicate cities from the geography.city table")
print(f"found {len(result)} duplicated cities")
return result
def get_account_defaults(cursor, city):
"""Get any device.account_default rows that use one of the duplicated cities."""
device_dir = Path(MYCROFT_DB_DIR).joinpath("device_schema")
sql = get_sql_from_file(
str(device_dir.joinpath("get_device_defaults_for_city.sql")))
args = dict(city_ids=tuple(city["city_ids"]))
request = DatabaseRequest(sql, args)
result = cursor.select_all(request)
if result:
print(f"found {len(result)} device defaults for {city['city_name']}")
return result
def remove(self, agreement: Agreement):
"""AGREEMENTS SHOULD NEVER BE REMOVED! ONLY USE IN TEST CODE!"""
if environ['SELENE_ENVIRONMENT'] == 'dev':
content_id = self._get_agreement_content_id(agreement.id)
if content_id is not None:
large_object = self.db.lobject(content_id)
large_object.unlink()
request = DatabaseRequest(sql=get_sql_from_file(
path.join(SQL_DIR, 'delete_agreement.sql')),
args=dict(agreement_id=agreement.id))
self.cursor.delete(request)
log_msg = 'deleted {} agreement version {}'
_log.info(log_msg.format(agreement.type, agreement.version))
def _add_agreement(self, agreement: Agreement, content_id: int) -> str:
date_range = DateRange(agreement.effective_date, None)
request = DatabaseRequest(sql=get_sql_from_file(
path.join(SQL_DIR, 'add_agreement.sql')),
args=dict(agreement_type=agreement.type,
version=agreement.version,
date_range=date_range,
content_id=content_id))
result = self.cursor.insert_returning(request)
_log.info('added {} agreement version {} starting {}'.format(
agreement.type, agreement.version, agreement.effective_date))
return result['id']
def expire(self, agreement: Agreement, expire_date: date):
active_agreement = self.get_active_for_type(agreement.type)
if active_agreement is not None:
date_range = DateRange(active_agreement.effective_date,
expire_date)
request = DatabaseRequest(sql=get_sql_from_file(
path.join(SQL_DIR, 'expire_agreement.sql')),
args=dict(agreement_type=agreement.type,
date_range=date_range))
self.cursor.update(request)
log_msg = 'set expire date of active {} agreement to {}'
_log.info(log_msg.format(agreement.type, expire_date))
else:
_log.info('no active {} agreement to expire'.format(
agreement.type))
def get_active(self):
agreements = []
request = DatabaseRequest(sql=get_sql_from_file(
path.join(SQL_DIR, 'get_current_agreements.sql')))
for row in self.cursor.select_all(request):
content = self._get_agreement_content(row['content_id'])
agreements.append(
Agreement(id=row['id'],
type=row['agreement'],
version=row['version'],
content=content,
effective_date=row['effective_date']))
if not agreements and not self.skip_no_agreement_error:
_log.error('no agreements found with effective date of today')
return agreements
def _build_db_batch_request(self, sql_file_name: str, args: List[dict]):
"""Build a DatabaseBatchRequest object containing a query and args"""
return DatabaseBatchRequest(sql=get_sql_from_file(
path.join(self.sql_dir, sql_file_name)),
args=args)
def _get_open_dataset_agreement_by_device_id(self, device_id: str):
query = DatabaseRequest(sql=get_sql_from_file(
path.join(SQL_DIR, 'get_open_dataset_agreement_by_device_id.sql')),
args=dict(device_id=device_id))
return self.cursor.select_one(query)
def get_device_settings_by_device_id(self, device_id):
query = DatabaseRequest(sql=get_sql_from_file(
path.join(SQL_DIR, 'get_device_settings_by_device_id.sql')),
args=dict(device_id=device_id))
return self.cursor.select_one(query)
