def createMapView(table, report): rows = db.exec_sql(f""" CREATE MATERIALIZED VIEW map AS SELECT srnumber, requesttype, nc, latitude, longitude, createddate FROM {table} WHERE latitude IS NOT NULL AND longitude IS NOT NULL WITH DATA; """) db.exec_sql(""" CREATE INDEX map_nc_index ON map(nc); CREATE INDEX map_requesttype_index ON map(requesttype); CREATE INDEX map_createddate_index ON map(createddate); """) report.append({ 'description': 'create map view', 'rowsAffected': rows.rowcount })
def createVisView(table, report): rows = db.exec_sql(f""" CREATE MATERIALIZED VIEW vis AS SELECT requesttype, requestsource, nc, cd, createddate, _daystoclose FROM {table} WITH DATA; """) db.exec_sql(""" CREATE INDEX vis_nc_index ON vis(nc); CREATE INDEX vis_cd_index ON vis(cd); CREATE INDEX vis_requesttype_index ON vis(requesttype); CREATE INDEX vis_createddate_index ON vis(createddate); """) report.append({ 'description': 'create vis view', 'rowsAffected': rows.rowcount })
def switchPrimaryKey(table, report): db.exec_sql(f""" ALTER TABLE {table} DROP COLUMN id; ALTER TABLE {table} ADD PRIMARY KEY (srnumber); """) report.append({ 'description': 'switched primary key column to srnumber', 'rowsAffected': 'N/A' })
def resetDatabase(self): log('\nResetting database.') db.exec_sql(f""" DROP TABLE IF EXISTS {Ingest.__tablename__} CASCADE; DROP TABLE IF EXISTS metadata; CREATE TABLE metadata AS SELECT * FROM (VALUES (NOW())) as vals(last_pulled); """) Base.metadata.create_all(db.engine)
def dropDuplicates(table, report): rows = db.exec_sql(f""" DELETE FROM {table} a USING {table} b WHERE a.id < b.id AND a.srnumber = b.srnumber; """) report.append({ 'description': 'dropped duplicate rows by srnumber', 'rowsAffected': rows.rowcount })
def removeInvalidClosedDates(table, report): result = db.exec_sql(f""" UPDATE {table} SET closeddate = NULL WHERE closeddate::timestamp < createddate::timestamp; """) report.append({ 'description': 'removed invalid closed dates', 'rowsAffected': result.rowcount })
def fixHistoricCulturalNorth(table, report): result = db.exec_sql(f""" UPDATE {table} SET nc = 128 WHERE nc = 0 AND ncname = 'HISTORIC CULTURAL NORTH NC' """) report.append({ 'description': 'fix nc code for Historic Cultural North NC', 'rowsAffected': result.rowcount })
def fixNorthWestwood(table, report): result = db.exec_sql(f""" UPDATE {table} SET nc = 127 WHERE nc = 0 AND ncname = 'NORTH WESTWOOD NC' """) report.append({ 'description': 'fix nc code for North Westwood NC', 'rowsAffected': result.rowcount })
def setDaysToClose(table, report): result = db.exec_sql(f""" UPDATE {table} SET _daystoclose = EXTRACT ( EPOCH FROM (closeddate::timestamp - createddate::timestamp) / (60 * 60 * 24) ); """) report.append({ 'description': 'set _daystoclose column', 'rowsAffected': result.rowcount })
def itemQuery(self, requestNumber, table=default_table): ''' Returns a single request by its requestNumber. ''' if not requestNumber or not isinstance(requestNumber, str): return {'Error': 'Missing request number'} rows = db.exec_sql(f""" SELECT * FROM {table} WHERE srnumber = '{requestNumber}' """) rows = [dict(row) for row in rows] if len(rows) > 0: return rows[0] else: return {'Error': 'Request number not found'}
async def lastPulled(self): rows = db.exec_sql('SELECT last_pulled FROM metadata') return rows.first()[0]