class NibrsIncidentRepresentation(db.Model, CreatableModel): __tablename__ = 'nibrs_incident_representation' incident_representation_id = db.Column(db.BigInteger, primary_key=True) incident_id = db.Column(db.BigInteger, db.ForeignKey(models.NibrsIncident.incident_id)) representation = db.Column(JSONB) incident = db.relationship(models.NibrsIncident, uselist=False, backref=backref('representation', uselist=False)) @classmethod def regenerate(cls): """Generates or replaces cached representations for all records.""" for incident in models.NibrsIncident.query: if not incident.representation: incident.representation = cls(incident=incident) incident.representation.generate() models.NibrsIncident.query.session.commit() @classmethod def fill(cls, batch_size=None): """Generates cached representations for records that lack them. Using a `batch_size` helps for large operations that may fail.""" finished = False batch_no = 0 while not finished: finished = True qry = models.NibrsIncident.query.filter( models.NibrsIncident.representation == None).limit(batch_size) for incident in qry: finished = False # until the query comes back empty incident.representation = cls(incident=incident) incident.representation.generate() models.NibrsIncident.query.session.commit() logging.warning( "Batch #{batch_no} of #{batch_size} complete".format( batch_no=batch_no, batch_size=batch_size)) batch_no += 1 def generate(self): """Generates and caches output for a single NibrsIncident.""" from crime_data.common import marshmallow_schemas _schema = marshmallow_schemas.NibrsIncidentSchema() self.representation = _schema.dump(self.incident).data
class RegionLK(db.Model): __tablename__ = 'region_lk' def getByName(region_name=None): """ A method to find a region by its region_name """ query = RegionLK.query if region_name: query = query.filter(func.lower(RegionLK.region_name) == func.lower(region_name)) return query region_code = db.Column(db.SmallInteger, primary_key=True) region_name = db.Column(db.String(50)) region_desc = db.Column(db.String(100))
class StateLK(db.Model): __tablename__ = 'state_lk' def get(region_code=None): """ A method to find a state by its region_code """ query = StateLK.query if region_code: query = query.filter(StateLK.region_code == region_code) return query state_id = db.Column(db.Integer, primary_key=True) state_abbr = db.Column(db.String(4)) state_name = db.Column(db.String(50)) state_fips_code = db.Column(db.Integer) region_code = db.Column(db.SmallInteger)
class BaseModel(db.Model): __abstract__ = True idx = db.Column(db.Integer, primary_key=True)
class ArsonSummary(db.Model): __tablename__ = 'arson_summary' arson_summary_id = db.Column(db.Integer, nullable=False, primary_key=True) grouping_bitmap = db.Column(db.Integer) year = db.Column(db.SmallInteger) state_id = db.Column(db.Integer) state_abbr = db.Column(db.Text) agency_id = db.Column(db.Integer) ori = db.Column(db.Text) subcategory_code = db.Column(db.Text) subcategory_name = db.Column(db.Text) reported = db.Column(db.Integer) unfounded = db.Column(db.Integer) actual = db.Column(db.Integer) cleared = db.Column(db.Integer) juvenile_cleared = db.Column(db.Integer) uninhabited = db.Column(db.Integer) est_damage_value = db.Column(db.Integer)
class AgencyParticipation(db.Model, FilterableModel): """Represents agency participation for a single month.""" __tablename__ = 'agency_participation' year = db.Column(db.SmallInteger, nullable=False, primary_key=True) state_name = db.Column(db.String) state_abbr = db.Column(db.String) agency_id = db.Column(db.Integer, nullable=False, primary_key=True) agency_ori = db.Column(db.String) agency_name = db.Column(db.String) agency_population = db.Column(db.BigInteger) population_group_code = db.Column(db.String) population_group = db.Column(db.String) reported = db.Column(db.SmallInteger, nullable=False) months_reported = db.Column(db.SmallInteger, nullable=False) nibrs_reported = db.Column(db.SmallInteger, nullable=False) nibrs_months_reported = db.Column(db.SmallInteger, nullable=False) covered = db.Column(db.SmallInteger) participated = db.Column(db.SmallInteger) nibrs_participated = db.Column(db.SmallInteger)
class HtSummary(db.Model): """Collects rollups of multiple HtAgency reports. You can use this table to get counts of human trafficking for a given agency, a specific state or national on a single or all years. Note that counts from US Territories are not available in this table (the FBI says they shouldn't be included.""" class Meta: __tablename__ = 'ht_summary' ht_summary_id = db.Column(db.Integer, primary_key=True) grouping_bitmap = db.Column(db.Integer) year = db.Column(db.SmallInteger) ori = db.Column(db.Text) agency_id = db.Column(db.BigInteger) agency_name = db.Column(db.Text) agencies = db.Column(db.Integer) population = db.Column(db.BigInteger) state_id = db.Column(db.Integer) state_abbr = db.Column(db.Text) months_reported = db.Column(db.SmallInteger) sex_acts = db.Column(db.Integer) sex_acts_cleared = db.Column(db.Integer) sex_acts_juvenile_cleared = db.Column(db.Integer) servitude = db.Column(db.Integer) servitude_cleared = db.Column(db.Integer) servitude_juvenile_cleared = db.Column(db.Integer) @classmethod def grouped_by_state(cls, year=None, state_abbr=None): query = HtSummary.query query = query.filter(HtSummary.state_id != None) query = query.filter(HtSummary.agency_id == None) if year is not None: query = query.filter(HtSummary.year == year) if state_abbr is not None: query = query.filter(HtSummary.state_abbr == state_abbr) query = query.order_by(HtSummary.year, HtSummary.state_abbr) query = query.filter(HtSummary.year <= MAX_AGENCY_YEAR) return query
class HtAgency(db.Model, FilterableModel): """Represents human trafficking counts reported by a single agency in a given year""" class Meta: __tablename__ = 'ht_agency' id = db.Column(db.Integer, primary_key=True) year = db.Column(db.SmallInteger) ori = db.Column(db.Text) agency_id = db.Column(db.BigInteger) agency_name = db.Column(db.Text) population = db.Column(db.BigInteger) state_id = db.Column(db.Integer) state_abbr = db.Column(db.Text) months_reported = db.Column(db.SmallInteger) sex_acts = db.Column(db.Integer) sex_acts_cleared = db.Column(db.Integer) sex_acts_juvenile_cleared = db.Column(db.Integer) servitude = db.Column(db.Integer) servitude_cleared = db.Column(db.Integer) servitude_juvenile_cleared = db.Column(db.Integer)
class CdeAgency(db.Model, FilterableModel): """A class for the denormalized cde_agencies table""" __tablename__ = 'cde_agencies' __table_args__ = (UniqueConstraint('agency_id'), ) agency_id = db.Column(db.BigInteger, primary_key=True) ori = db.Column(db.String(9)) legacy_ori = db.Column(db.String(9)) agency_name = db.Column(db.Text) short_name = db.Column(db.Text) agency_type_id = db.Column(db.String(1)) agency_type_name = db.Column(db.String(100)) # FIXME: can add associations when we need them tribe_id = db.Column(db.BigInteger) campus_id = db.Column(db.BigInteger) city_id = db.Column(db.BigInteger) city_name = db.Column(db.Text) state_id = db.Column(db.SmallInteger) state_abbr = db.Column(db.String(2)) primary_county_id = db.Column(db.BigInteger) primary_county = db.Column(db.Text) primary_county_fips = db.Column(db.String(5)) agency_status = db.Column(db.String(1)) submitting_agency_id = db.Column(db.BigInteger) submitting_sai = db.Column(db.String(9)) submitting_name = db.Column(db.Text) submitting_state_abbr = db.Column(db.String(2)) start_year = db.Column(db.SmallInteger) dormant_year = db.Column(db.SmallInteger) revised_rape_start = db.Column(db.SmallInteger) current_nibrs_start_year = db.Column(db.SmallInteger) current_year = db.Column(db.SmallInteger) population = db.Column(db.BigInteger) population_group_code = db.Column(db.String(2)) population_group_desc = db.Column(db.Text) population_source_flag = db.Column(db.String(1)) suburban_area_flag = db.Column(db.String(1)) core_city_flag = db.Column(db.String(1)) months_reported = db.Column(db.SmallInteger) nibrs_months_reported = db.Column(db.SmallInteger) past_10_years_reported = db.Column(db.SmallInteger) covered_by_id = db.Column(db.BigInteger) covered_by_ori = db.Column(db.String(9)) covered_by_name = db.Column(db.Text) staffing_year = db.Column(db.SmallInteger) total_officers = db.Column(db.Integer) total_civilians = db.Column(db.Integer) icpsr_zip = db.Column(db.String(5)) icpsr_lat = db.Column(db.Float) icpsr_lng = db.Column(db.Float)
class AgencyClassificationCounts(db.Model): __tablename__ = 'agency_classification_view' id = db.Column(db.BigInteger, autoincrement=True, primary_key=True) year = db.Column(db.SmallInteger) agency_id = db.Column(db.BigInteger) state_postal_abbr = db.Column(db.Text) ori = db.Column(db.Text) pub_agency_name = db.Column(db.Text) classification = db.Column(db.Text) reported = db.Column(db.BigInteger) unfounded = db.Column(db.BigInteger) actual = db.Column(db.BigInteger) cleared = db.Column(db.BigInteger) juvenile_cleared = db.Column(db.BigInteger) def get(self, state = None, agency = None, year = None, county = None, classification = None): """Get Agency Sums given a state/year/county/agency ori, etc.""" query = AgencyClassificationCounts.query if state: query = query.filter(func.lower(AgencyClassificationCounts.state_postal_abbr) == state.lower()) if county: subq = (db.session.query(models.RefAgencyCounty.agency_id) .select_from(models.RefAgencyCounty) .join(models.RefCounty, and_(models.RefAgencyCounty.county_id == models.RefCounty.county_id)) .filter(models.RefCounty.county_fips_code == county) ) if year: subq = subq.filter(models.RefAgencyCounty.data_year == year) query = query.filter(AgencyClassificationCounts.agency_id.in_(subq.subquery())) if agency: query = query.filter(AgencyClassificationCounts.ori == agency) if year: query = query.filter(AgencyClassificationCounts.year == year) if classification: query = query.filter(func.lower(AgencyClassificationCounts.classification) == func.lower(classification)) query = query.join(AgencyParticipation, and_(AgencyParticipation.agency_id == AgencyClassificationCounts.agency_id, AgencyParticipation.year == AgencyClassificationCounts.year)).filter(AgencyParticipation.months_reported == 12) query = query.order_by(AgencyClassificationCounts.year.desc()) # Agency reported 12 Months. query = query.filter(AgencyClassificationCounts.year <= MAX_AGENCY_YEAR) return query
class ArrestsNational(db.Model): """Estimated data about national arrest totals""" __tablename__ = 'asr_national' id = db.Column(db.BigInteger, autoincrement=True, primary_key=True) year = db.Column(db.SmallInteger) population = db.Column(db.BigInteger) total_arrests = db.Column(db.BigInteger) homicide = db.Column(db.BigInteger) rape = db.Column(db.BigInteger) robbery = db.Column(db.BigInteger) aggravated_assault = db.Column(db.BigInteger) burglary = db.Column(db.BigInteger) larceny = db.Column(db.BigInteger) motor_vehicle_theft = db.Column(db.BigInteger) arson = db.Column(db.BigInteger) violent_crime = db.Column(db.BigInteger) property_crime = db.Column(db.BigInteger) other_assault = db.Column(db.BigInteger) forgery = db.Column(db.BigInteger) fraud = db.Column(db.BigInteger) embezzlement = db.Column(db.BigInteger) stolen_property = db.Column(db.BigInteger) vandalism = db.Column(db.BigInteger) weapons = db.Column(db.BigInteger) prostitution = db.Column(db.BigInteger) other_sex_offenses = db.Column(db.BigInteger) drug_abuse = db.Column(db.BigInteger) gambling = db.Column(db.BigInteger) against_family = db.Column(db.BigInteger) dui = db.Column(db.BigInteger) liquor_laws = db.Column(db.BigInteger) drunkenness = db.Column(db.BigInteger) disorderly_conduct = db.Column(db.BigInteger) vagrancy = db.Column(db.BigInteger) other = db.Column(db.BigInteger) suspicion = db.Column(db.BigInteger) curfew_loitering = db.Column(db.BigInteger)
class RetaEstimated(db.Model): """ Estimated data loaded from a CSV data file created from published data tables from the _Crime in the United States_ reports. """ __tablename__ = 'reta_estimated' __table_args__ = ( UniqueConstraint('year', 'state_id'), ) estimate_id = db.Column(db.Integer, primary_key=True) year = db.Column(db.SmallInteger) state_id = db.Column(db.SmallInteger, db.ForeignKey(RefState.state_id, deferrable=True, initially='DEFERRED'), nullable=True) state_abbr = db.Column(db.String(2)) population = db.Column(db.BigInteger) violent_crime = db.Column(db.BigInteger) homicide = db.Column(db.BigInteger) rape_legacy = db.Column(db.BigInteger) rape_revised = db.Column(db.BigInteger) robbery = db.Column(db.BigInteger) aggravated_assault = db.Column(db.BigInteger) property_crime = db.Column(db.BigInteger) burglary = db.Column(db.BigInteger) larceny = db.Column(db.BigInteger) motor_vehicle_theft = db.Column(db.BigInteger) caveats = db.Column(db.Text) state = relationship(RefState)
class ParticipationRate(db.Model): __tablename__ = 'participation_rates' participation_id = db.Column(db.Integer, nullable=False, primary_key=True) year = db.Column(db.SmallInteger, nullable=False) state_id = db.Column(db.Integer, db.ForeignKey(RefState.state_id, deferrable=True, initially='DEFERRED'), nullable=True) county_id = db.Column(db.Integer, db.ForeignKey(RefCounty.county_id, deferrable=True, initially='DEFERRED'), nullable=True) state_name = db.Column(db.String) county_name = db.Column(db.String) total_agencies = db.Column(db.Integer) participating_agencies = db.Column(db.Integer) participation_rate = db.Column(db.Float) nibrs_participating_agencies = db.Column(db.Integer) nibrs_participation_rate = db.Column(db.Float) covered_agencies = db.Column(db.Integer) covered_rate = db.Column(db.Float) total_population = db.Column(db.BigInteger) participating_population = db.Column(db.BigInteger) nibrs_participating_population = db.Column(db.BigInteger)
class RefRegion(db.Model): __tablename__ = 'ref_region' region_id = db.Column(db.SmallInteger, primary_key=True) region_code = db.Column(db.String(2)) region_name = db.Column(db.String(100)) region_desc = db.Column(db.String(100))