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]
