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)