crime_data/common/models.py
# coding: utf-8
import datetime
from decimal import Decimal
import flask_restful as restful
from crime_data.extensions import db
from sqlalchemy import (BigInteger, Boolean, Column, DateTime, Float,
ForeignKey, Integer, SmallInteger, String, Text,
UniqueConstraint, func, text)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import backref, relationship
# db = SQLAlchemy()
#Base = declarative_base()
#metadata = Base.metadata
class RefRace(db.Model):
__tablename__ = 'ref_race'
race_id = db.Column(db.SmallInteger, primary_key=True)
race_code = db.Column(db.String(2), nullable=False, unique=True)
race_desc = db.Column(db.String(100), nullable=False)
sort_order = db.Column(db.SmallInteger)
start_year = db.Column(db.SmallInteger)
end_year = db.Column(db.SmallInteger)
notes = db.Column(db.String(1000))
class ArsonMonth(db.Model):
__tablename__ = 'arson_month'
__table_args__ = (
UniqueConstraint('agency_id', 'data_year', 'month_num', 'data_home'), )
arson_month_id = db.Column(db.BigInteger, primary_key=True)
data_year = db.Column(db.SmallInteger, nullable=False)
month_num = db.Column(db.SmallInteger, nullable=False)
data_home = db.Column(db.String(1), nullable=False)
source_flag = db.Column(db.String(1), nullable=False)
reported_flag = db.Column(db.String(1))
ddocname = db.Column(db.String(100))
month_included_in = db.Column(db.SmallInteger)
report_date = db.Column(db.DateTime(True))
prepared_date = db.Column(db.DateTime(True))
orig_format = db.Column(db.String(1))
update_flag = db.Column(db.String(1))
did = db.Column(db.BigInteger)
ff_line_number = db.Column(db.BigInteger)
agency_id = db.Column(db.Integer,
db.ForeignKey('ref_agency.agency_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
agency = db.relationship('RefAgency')
class ArsonMonthBySubcat(db.Model):
__tablename__ = 'arson_month_by_subcat'
__table_args__ = (
db.UniqueConstraint('arson_month_id', 'subcategory_id'), )
id = db.Column(db.Integer,
primary_key=True,
server_default=text(
"nextval('arson_month_by_subcat_id_seq'::regclass)"))
reported_count = db.Column(db.Integer)
reported_status = db.Column(db.SmallInteger)
unfounded_count = db.Column(db.Integer)
unfounded_status = db.Column(db.SmallInteger)
actual_count = db.Column(db.Integer)
actual_status = db.Column(db.SmallInteger)
cleared_count = db.Column(db.Integer)
cleared_status = db.Column(db.SmallInteger)
juvenile_cleared_count = db.Column(db.Integer)
juvenile_cleared_status = db.Column(db.SmallInteger)
uninhabited_count = db.Column(db.Integer)
uninhabited_status = db.Column(db.SmallInteger)
est_damage_value = db.Column(db.BigInteger)
est_damage_value_status = db.Column(db.SmallInteger)
arson_month_id = db.Column(db.Integer,
db.ForeignKey('arson_month.arson_month_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
subcategory_id = db.Column(db.Integer,
db.ForeignKey(
'arson_subcategory.subcategory_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
arson_month = db.relationship('ArsonMonth')
subcategory = db.relationship('ArsonSubcategory')
class ArsonSubcategory(db.Model):
__tablename__ = 'arson_subcategory'
subcategory_id = db.Column(db.BigInteger, primary_key=True)
subcategory_name = db.Column(db.String(100))
subcategory_code = db.Column(db.String(20), unique=True)
subcat_xml_path = db.Column(db.String(4000))
subclass_id = db.Column(db.Integer,
db.ForeignKey(
'arson_subclassification.subclass_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
subclass = db.relationship('ArsonSubclassification')
class ArsonSubclassification(db.Model):
__tablename__ = 'arson_subclassification'
subclass_id = db.Column(db.SmallInteger, primary_key=True)
subclass_name = db.Column(db.String(100))
subclass_code = db.Column(db.String(20), unique=True)
subclass_xml_path = db.Column(db.String(4000))
class AsrAgeRange(db.Model):
__tablename__ = 'asr_age_range'
age_range_id = db.Column(db.BigInteger, primary_key=True)
age_range_name = db.Column(db.String(20))
age_range_code = db.Column(db.String(20), unique=True)
juvenile_flag = db.Column(db.String(1), nullable=False)
ff_sort_order = db.Column(db.String(3))
age_sex = db.Column(db.String(1))
xml_code = db.Column(db.String(2001))
class AsrAgeSexSubcat(db.Model):
__tablename__ = 'asr_age_sex_subcat'
__table_args__ = (UniqueConstraint('asr_month_id', 'offense_subcat_id',
'age_range_id'), )
id = db.Column(
db.Integer,
primary_key=True,
server_default=text("nextval('asr_age_sex_subcat_id_seq'::regclass)"))
arrest_count = db.Column(db.Integer)
arrest_status = db.Column(db.SmallInteger)
active_flag = db.Column(db.String(1))
prepared_date = db.Column(db.DateTime(True))
report_date = db.Column(db.DateTime(True))
ff_line_number = db.Column(db.BigInteger)
age_range_id = db.Column(db.Integer,
db.ForeignKey('asr_age_range.age_range_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
asr_month_id = db.Column(db.Integer,
db.ForeignKey('asr_month.asr_month_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
offense_subcat_id = db.Column(db.Integer,
db.ForeignKey(
'asr_offense_subcat.offense_subcat_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
age_range = db.relationship('AsrAgeRange')
asr_month = db.relationship('AsrMonth')
offense_subcat = db.relationship('AsrOffenseSubcat')
class AsrEthnicity(db.Model):
__tablename__ = 'asr_ethnicity'
ethnicity_id = db.Column(db.BigInteger, primary_key=True)
ethnicity_name = db.Column(db.String(100))
ethnicity_code = db.Column(db.String(20), unique=True)
ff_sort_order = db.Column(db.String(3))
class AsrEthnicityOffense(db.Model):
__tablename__ = 'asr_ethnicity_offense'
__table_args__ = (db.UniqueConstraint('asr_month_id', 'offense_subcat_id',
'ethnicity_id', 'juvenile_flag'), )
id = db.Column(db.Integer,
primary_key=True,
server_default=text(
"nextval('asr_ethnicity_offense_id_seq'::regclass)"))
juvenile_flag = db.Column(db.String(1), nullable=False)
arrest_count = db.Column(db.Integer)
arrest_status = db.Column(db.SmallInteger)
prepared_date = db.Column(db.DateTime(True))
report_date = db.Column(db.DateTime(True))
ff_line_number = db.Column(db.BigInteger)
asr_month_id = db.Column(db.Integer,
db.ForeignKey('asr_month.asr_month_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
ethnicity_id = db.Column(db.Integer,
db.ForeignKey('asr_ethnicity.ethnicity_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
offense_subcat_id = db.Column(db.Integer,
db.ForeignKey(
'asr_offense_subcat.offense_subcat_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
asr_month = db.relationship('AsrMonth')
ethnicity = db.relationship('AsrEthnicity')
offense_subcat = db.relationship('AsrOffenseSubcat')
class AsrMonth(db.Model):
__tablename__ = 'asr_month'
__table_args__ = (db.UniqueConstraint('agency_id', 'data_year',
'month_num', 'data_home'), )
asr_month_id = db.Column(db.BigInteger, primary_key=True)
data_year = db.Column(db.SmallInteger, nullable=False)
month_num = db.Column(db.SmallInteger, nullable=False)
source_flag = db.Column(db.String(1), nullable=False)
reported_flag = db.Column(db.String(1))
orig_format = db.Column(db.String(1))
update_flag = db.Column(db.String(1))
ff_line_number = db.Column(db.BigInteger)
ddocname = db.Column(db.String(100))
did = db.Column(db.BigInteger)
data_home = db.Column(db.String(1), nullable=False)
agency_id = db.Column(db.Integer,
db.ForeignKey('ref_agency.agency_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
agency = db.relationship('RefAgency')
class AsrJuvenileDisposition(AsrMonth):
__tablename__ = 'asr_juvenile_disposition'
asr_month_id = db.Column(db.Integer,
db.ForeignKey('asr_month.asr_month_id',
deferrable=True,
initially='DEFERRED'),
primary_key=True)
report_date = db.Column(db.DateTime(True))
prepared_date = db.Column(db.DateTime(True))
handled_within_dept = db.Column(db.Integer)
juvenile_court = db.Column(db.Integer)
welfare_agency = db.Column(db.Integer)
other_police = db.Column(db.Integer)
adult_court = db.Column(db.Integer)
ff_line_number = db.Column(db.BigInteger)
class AsrOffense(db.Model):
__tablename__ = 'asr_offense'
offense_id = db.Column(db.BigInteger, primary_key=True)
offense_name = db.Column(db.String(100))
offense_code = db.Column(db.String(20), unique=True)
total_flag = db.Column(db.String(1))
offense_cat_id = db.Column(db.Integer,
db.ForeignKey(
'asr_offense_category.offense_cat_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
offense_cat = db.relationship('AsrOffenseCategory')
class AsrOffenseCategory(db.Model):
__tablename__ = 'asr_offense_category'
offense_cat_id = db.Column(db.BigInteger, primary_key=True)
offense_cat_name = db.Column(db.String(100))
offense_cat_code = db.Column(db.String(20), unique=True)
class AsrOffenseSubcat(db.Model):
__tablename__ = 'asr_offense_subcat'
offense_subcat_id = db.Column(db.BigInteger, primary_key=True)
offense_subcat_name = db.Column(db.String(100))
offense_subcat_code = db.Column(db.String(20), unique=True)
srs_offense_code = db.Column(db.String(3))
master_offense_code = db.Column(db.SmallInteger)
total_flag = db.Column(db.String(1))
adult_juv_flag = db.Column(db.String(1))
offense_id = db.Column(db.Integer,
db.ForeignKey('asr_offense.offense_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
offense = db.relationship('AsrOffense')
class AsrRaceOffenseSubcat(db.Model):
__tablename__ = 'asr_race_offense_subcat'
__table_args__ = (db.UniqueConstraint('asr_month_id', 'offense_subcat_id',
'race_id', 'juvenile_flag'), )
id = db.Column(db.Integer,
primary_key=True,
server_default=text(
"nextval('asr_race_offense_subcat_id_seq'::regclass)"))
juvenile_flag = db.Column(db.String(1), nullable=False)
arrest_count = db.Column(db.Integer)
arrest_status = db.Column(db.SmallInteger)
active_flag = db.Column(db.String(1))
prepared_date = db.Column(db.DateTime(True))
report_date = db.Column(db.DateTime(True))
ff_line_number = db.Column(db.BigInteger)
asr_month_id = db.Column(db.Integer,
db.ForeignKey('asr_month.asr_month_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
offense_subcat_id = db.Column(db.Integer,
db.ForeignKey(
'asr_offense_subcat.offense_subcat_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
race_id = db.Column(db.Integer,
db.ForeignKey('ref_race.race_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
asr_month = db.relationship('AsrMonth')
offense_subcat = db.relationship('AsrOffenseSubcat')
race = db.relationship('RefRace')
class CrimeType(db.Model):
__tablename__ = 'crime_type'
crime_type_id = db.Column(db.SmallInteger, primary_key=True)
crime_type_name = db.Column(db.String(50))
crime_type_sort_order = db.Column(db.SmallInteger)
crime_flag = db.Column(db.String(1))
class CtArrestee(db.Model):
__tablename__ = 'ct_arrestee'
arrestee_id = db.Column(db.BigInteger, primary_key=True)
age = db.Column(db.SmallInteger)
sex_code = db.Column(db.String(1))
ethnicity_id = db.Column(db.Integer,
db.ForeignKey('nibrs_ethnicity.ethnicity_id',
deferrable=True,
initially='DEFERRED'),
index=True)
incident_id = db.Column(db.Integer,
db.ForeignKey('ct_incident.incident_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
race_id = db.Column(db.Integer,
db.ForeignKey('ref_race.race_id',
deferrable=True,
initially='DEFERRED'),
index=True)
ethnicity = db.relationship('NibrsEthnicity')
incident = db.relationship('CtIncident')
race = db.relationship('RefRace')
class CtIncident(db.Model):
__tablename__ = 'ct_incident'
incident_id = db.Column(db.BigInteger, primary_key=True)
data_year = db.Column(db.SmallInteger, nullable=False)
incident_number = db.Column(db.String(15))
incident_date = db.Column(db.DateTime(True))
source_flag = db.Column(db.String(1), nullable=False)
ddocname = db.Column(db.String(100))
report_date = db.Column(db.DateTime(True))
prepared_date = db.Column(db.DateTime(True))
report_date_flag = db.Column(db.String(1))
incident_hour = db.Column(db.SmallInteger)
cleared_except_flag = db.Column(db.String(1))
update_flag = db.Column(db.String(1))
ff_line_number = db.Column(db.BigInteger)
data_home = db.Column(db.String(1), nullable=False)
orig_format = db.Column(db.String(1))
unknown_offender = db.Column(db.String(1))
did = db.Column(db.BigInteger)
nibrs_incident_id = db.Column(db.BigInteger)
agency_id = db.Column(db.Integer,
db.ForeignKey('ref_agency.agency_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
ct_month_id = db.Column(db.Integer,
db.ForeignKey('ct_month.ct_month_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
agency = db.relationship('RefAgency')
ct_month = db.relationship('CtMonth')
class CtMonth(db.Model):
__tablename__ = 'ct_month'
ct_month_id = db.Column(db.BigInteger, primary_key=True)
month_num = db.Column(db.SmallInteger, nullable=False)
data_year = db.Column(db.SmallInteger, nullable=False)
reported_status = db.Column(db.String(1))
reported_count = db.Column(db.Integer)
update_flag = db.Column(db.String(1))
ff_line_number = db.Column(db.BigInteger)
ddocname = db.Column(db.String(100))
did = db.Column(db.BigInteger)
data_home = db.Column(db.String(1), nullable=False)
orig_format = db.Column(db.String(1))
agency_id = db.Column(db.Integer,
db.ForeignKey('ref_agency.agency_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
agency = db.relationship('RefAgency')
class CtOffender(db.Model):
__tablename__ = 'ct_offender'
offender_id = db.Column(db.BigInteger, primary_key=True)
age = db.Column(db.SmallInteger)
sex_code = db.Column(db.String(1))
ethnicity_id = db.Column(db.Integer,
db.ForeignKey('nibrs_ethnicity.ethnicity_id',
deferrable=True,
initially='DEFERRED'),
index=True)
incident_id = db.Column(db.Integer,
db.ForeignKey('ct_incident.incident_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
race_id = db.Column(db.Integer,
db.ForeignKey('ref_race.race_id',
deferrable=True,
initially='DEFERRED'),
index=True)
ethnicity = db.relationship('NibrsEthnicity')
incident = db.relationship('CtIncident')
race = db.relationship('RefRace')
class CtOffense(db.Model):
__tablename__ = 'ct_offense'
offense_id = db.Column(db.BigInteger, primary_key=True)
ct_offense_flag = db.Column(db.String(1))
incident_id = db.Column(db.Integer,
db.ForeignKey('ct_incident.incident_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
location_id = db.Column(db.Integer,
db.ForeignKey('nibrs_location_type.location_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
offense_type_id = db.Column(db.Integer,
db.ForeignKey(
'nibrs_offense_type.offense_type_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
incident = db.relationship('CtIncident')
location = db.relationship('NibrsLocationType')
offense_type = db.relationship('NibrsOffenseType')
class CtProperty(db.Model):
__tablename__ = 'ct_property'
property_id = db.Column(db.BigInteger, primary_key=True)
stolen_value = db.Column(db.BigInteger)
recovered_flag = db.Column(db.String(1))
date_recovered = db.Column(db.DateTime(True))
recovered_value = db.Column(db.BigInteger)
incident_id = db.Column(db.Integer,
db.ForeignKey('ct_incident.incident_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
prop_desc_id = db.Column(db.Integer,
db.ForeignKey('nibrs_prop_desc_type.prop_desc_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
incident = db.relationship('CtIncident')
prop_desc = db.relationship('NibrsPropDescType')
class CtVictim(db.Model):
__tablename__ = 'ct_victim'
__table_args__ = (UniqueConstraint('incident_id', 'victim_type_id'), )
id = db.Column(
db.Integer,
primary_key=True,
server_default=text("nextval('ct_victim_id_seq'::regclass)"))
incident_id = db.Column(db.Integer,
db.ForeignKey('ct_incident.incident_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
victim_type_id = db.Column(db.Integer,
db.ForeignKey(
'nibrs_victim_type.victim_type_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
incident = db.relationship('CtIncident')
victim_type = db.relationship('NibrsVictimType')
class CtWeapon(db.Model):
__tablename__ = 'ct_weapon'
ct_weapon_id = db.Column(db.BigInteger, primary_key=True)
incident_id = db.Column(db.Integer,
db.ForeignKey('ct_incident.incident_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
weapon_id = db.Column(db.Integer,
db.ForeignKey('nibrs_weapon_type.weapon_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
incident = db.relationship('CtIncident')
weapon = db.relationship('NibrsWeaponType')
class HcBiasMotivation(db.Model):
__tablename__ = 'hc_bias_motivation'
__table_args__ = (UniqueConstraint('offense_id', 'bias_id'), )
id = db.Column(
db.Integer,
primary_key=True,
server_default=text("nextval('hc_bias_motivation_id_seq'::regclass)"))
bias_id = db.Column(db.Integer,
db.ForeignKey('nibrs_bias_list.bias_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
offense_id = db.Column(db.Integer,
db.ForeignKey('hc_offense.offense_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
bias = db.relationship('NibrsBiasList')
offense = db.relationship('HcOffense')
class HcIncident(db.Model):
__tablename__ = 'hc_incident'
incident_id = db.Column(db.BigInteger, primary_key=True)
incident_no = db.Column(db.String(20))
incident_date = db.Column(db.DateTime(True))
data_home = db.Column(db.String(1))
source_flag = db.Column(db.String(1))
ddocname = db.Column(db.String(100))
report_date = db.Column(db.DateTime(True))
prepared_date = db.Column(db.DateTime(True))
victim_count = db.Column(db.SmallInteger)
adult_victim_count = db.Column(db.SmallInteger)
incident_status = db.Column(db.SmallInteger)
juvenile_victim_count = db.Column(db.SmallInteger)
offender_count = db.Column(db.SmallInteger)
adult_offender_count = db.Column(db.SmallInteger)
juvenile_offender_count = db.Column(db.SmallInteger)
update_flag = db.Column(db.String(1))
ff_line_number = db.Column(db.BigInteger)
orig_format = db.Column(db.String(1))
did = db.Column(db.BigInteger)
nibrs_incident_id = db.Column(db.BigInteger)
agency_id = db.Column(db.Integer,
db.ForeignKey('ref_agency.agency_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
hc_quarter_id = db.Column(db.Integer,
db.ForeignKey('hc_quarter.hc_quarter_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
offender_ethnicity_id = db.Column(
db.Integer,
db.ForeignKey('nibrs_ethnicity.ethnicity_id',
deferrable=True,
initially='DEFERRED'),
index=True)
offender_race_id = db.Column(db.Integer,
db.ForeignKey('ref_race.race_id',
deferrable=True,
initially='DEFERRED'),
index=True)
agency = db.relationship('RefAgency')
hc_quarter = db.relationship('HcQuarter')
offender_ethnicity = db.relationship('NibrsEthnicity')
offender_race = db.relationship('RefRace')
class HcOffense(db.Model):
__tablename__ = 'hc_offense'
offense_id = db.Column(db.BigInteger, primary_key=True)
victim_count = db.Column(db.SmallInteger)
nibrs_offense_id = db.Column(db.BigInteger)
incident_id = db.Column(db.Integer,
db.ForeignKey('hc_incident.incident_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
location_id = db.Column(db.Integer,
db.ForeignKey('nibrs_location_type.location_id',
deferrable=True,
initially='DEFERRED'),
index=True)
offense_type_id = db.Column(
db.Integer,
db.ForeignKey('nibrs_offense_type.offense_type_id',
deferrable=True,
initially='DEFERRED'),
index=True)
incident = db.relationship('HcIncident')
location = db.relationship('NibrsLocationType')
offense_type = db.relationship('NibrsOffenseType')
class HcQuarter(db.Model):
__tablename__ = 'hc_quarter'
__table_args__ = (UniqueConstraint('agency_id', 'quarter_num', 'data_year',
'data_home'), )
quarter_num = db.Column(db.SmallInteger, nullable=False)
data_year = db.Column(db.SmallInteger, nullable=False)
reported_status = db.Column(db.String(1))
reported_count = db.Column(db.BigInteger)
hc_quarter_id = db.Column(db.BigInteger, primary_key=True)
update_flag = db.Column(db.String(1))
orig_format = db.Column(db.String(1))
ff_line_number = db.Column(db.BigInteger)
ddocname = db.Column(db.String(100))
did = db.Column(db.BigInteger)
data_home = db.Column(db.String(1), nullable=False)
agency_id = db.Column(db.Integer,
db.ForeignKey('ref_agency.agency_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
agency = db.relationship('RefAgency')
class HcVictim(db.Model):
__tablename__ = 'hc_victim'
__table_args__ = (UniqueConstraint('offense_id', 'victim_type_id'), )
id = db.Column(
db.Integer,
primary_key=True,
server_default=text("nextval('hc_victim_id_seq'::regclass)"))
offense_id = db.Column(db.Integer,
db.ForeignKey('hc_offense.offense_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
victim_type_id = db.Column(db.Integer,
db.ForeignKey(
'nibrs_victim_type.victim_type_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
offense = db.relationship('HcOffense')
victim_type = db.relationship('NibrsVictimType')
class HtMonth(db.Model):
__tablename__ = 'ht_month'
__table_args__ = (
UniqueConstraint('agency_id', 'data_year', 'month_num', 'data_home'), )
ht_month_id = db.Column(db.BigInteger, primary_key=True)
data_year = db.Column(db.SmallInteger, nullable=False)
month_num = db.Column(db.SmallInteger, nullable=False)
data_home = db.Column(db.String(1), nullable=False)
source_flag = db.Column(db.String(1), nullable=False)
ddocname = db.Column(db.String(100))
report_date = db.Column(db.DateTime(True))
prepared_date = db.Column(db.DateTime(True))
prepared_by_user = db.Column(db.String(100))
prepared_by_email = db.Column(db.String(200))
orig_format = db.Column(db.String(1), nullable=False)
total_reported_count = db.Column(db.Integer)
total_unfounded_count = db.Column(db.Integer)
total_actual_count = db.Column(db.Integer)
total_cleared_count = db.Column(db.Integer)
total_juvenile_cleared_count = db.Column(db.Integer)
update_flag = db.Column(db.String(1))
reported_flag = db.Column(db.String(1))
did = db.Column(db.BigInteger)
ff_line_number = db.Column(db.BigInteger)
agency_id = db.Column(db.Integer,
db.ForeignKey('ref_agency.agency_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
agency = db.relationship('RefAgency')
class HtMonthOffenseSubcat(db.Model):
__tablename__ = 'ht_month_offense_subcat'
__table_args__ = (UniqueConstraint('offense_subcat_id', 'ht_month_id'), )
id = db.Column(db.Integer,
primary_key=True,
server_default=text(
"nextval('ht_month_offense_subcat_id_seq'::regclass)"))
reported_count = db.Column(db.Integer)
reported_status = db.Column(db.SmallInteger)
unfounded_count = db.Column(db.Integer)
unfounded_status = db.Column(db.SmallInteger)
actual_count = db.Column(db.Integer)
actual_status = db.Column(db.SmallInteger)
cleared_count = db.Column(db.Integer)
cleared_status = db.Column(db.SmallInteger)
juvenile_cleared_count = db.Column(db.Integer)
juvenile_cleared_status = db.Column(db.SmallInteger)
ht_month_id = db.Column(db.Integer,
db.ForeignKey('ht_month.ht_month_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
offense_subcat_id = db.Column(db.Integer,
db.ForeignKey(
'reta_offense_subcat.offense_subcat_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
ht_month = db.relationship('HtMonth')
offense_subcat = db.relationship('RetaOffenseSubcat')
class NibrsActivityType(db.Model):
__tablename__ = 'nibrs_activity_type'
activity_type_id = db.Column(db.SmallInteger, primary_key=True)
activity_type_code = db.Column(db.String(2))
activity_type_name = db.Column(db.String(100))
class NibrsAge(db.Model):
__tablename__ = 'nibrs_age'
age_id = db.Column(db.SmallInteger, primary_key=True)
age_code = db.Column(db.String(2))
age_name = db.Column(db.String(100))
class NibrsArrestType(db.Model):
__tablename__ = 'nibrs_arrest_type'
arrest_type_id = db.Column(db.SmallInteger, primary_key=True)
arrest_type_code = db.Column(db.String(1))
arrest_type_name = db.Column(db.String(100))
class NibrsArrestee(db.Model):
__tablename__ = 'nibrs_arrestee'
arrestee_id = db.Column(db.BigInteger, primary_key=True)
arrestee_seq_num = db.Column(db.BigInteger)
arrest_num = db.Column(db.String(12))
arrest_date = db.Column(db.DateTime(True))
multiple_indicator = db.Column(db.String(1))
age_num = db.Column(db.SmallInteger)
sex_code = db.Column(db.String(1))
resident_code = db.Column(db.String(1))
under_18_disposition_code = db.Column(db.String(1))
clearance_ind = db.Column(db.String(1))
ff_line_number = db.Column(db.BigInteger)
age_range_low_num = db.Column(db.SmallInteger)
age_range_high_num = db.Column(db.SmallInteger)
age_id = db.Column(db.Integer,
db.ForeignKey('nibrs_age.age_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
arrest_type_id = db.Column(
db.Integer,
db.ForeignKey('nibrs_arrest_type.arrest_type_id',
deferrable=True,
initially='DEFERRED'),
index=True)
ethnicity_id = db.Column(db.Integer,
db.ForeignKey('nibrs_ethnicity.ethnicity_id',
deferrable=True,
initially='DEFERRED'),
index=True)
incident_id = db.Column(db.Integer,
db.ForeignKey('nibrs_incident.incident_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
offense_type_id = db.Column(db.Integer,
db.ForeignKey(
'nibrs_offense_type.offense_type_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
race_id = db.Column(db.Integer,
db.ForeignKey('ref_race.race_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
age = db.relationship('NibrsAge', backref='arrestees', lazy=False)
arrest_type = db.relationship('NibrsArrestType',
lazy=False,
backref='arrestees')
ethnicity = db.relationship('NibrsEthnicity',
lazy=False,
backref='arrestees')
incident = db.relationship('NibrsIncident',
backref=backref('arrestees',
lazy=False))
offense_type = db.relationship('NibrsOffenseType', backref='arrestees')
race = db.relationship('RefRace', lazy=False, backref='arrestees')
class NibrsArresteeWeapon(db.Model):
__tablename__ = 'nibrs_arrestee_weapon'
nibrs_arrestee_weapon_id = db.Column(db.BigInteger, primary_key=True)
arrestee_id = db.Column(db.Integer,
db.ForeignKey('nibrs_arrestee.arrestee_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
weapon_id = db.Column(db.Integer,
db.ForeignKey('nibrs_weapon_type.weapon_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
arrestee = db.relationship('NibrsArrestee')
weapon = db.relationship('NibrsWeaponType')
class NibrsAssignmentType(db.Model):
__tablename__ = 'nibrs_assignment_type'
assignment_type_id = db.Column(db.SmallInteger, primary_key=True)
assignment_type_code = db.Column(db.String(1))
assignment_type_name = db.Column(db.String(100))
class NibrsBiasList(db.Model):
__tablename__ = 'nibrs_bias_list'
bias_id = db.Column(db.SmallInteger, primary_key=True)
bias_code = db.Column(db.String(2))
bias_name = db.Column(db.String(100))
class NibrsBiasMotivation(db.Model):
__tablename__ = 'nibrs_bias_motivation'
__table_args__ = (UniqueConstraint('bias_id', 'offense_id'), )
id = db.Column(db.Integer,
primary_key=True,
server_default=text(
"nextval('nibrs_bias_motivation_id_seq'::regclass)"))
bias_id = db.Column(db.Integer,
db.ForeignKey('nibrs_bias_list.bias_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
offense_id = db.Column(db.Integer,
db.ForeignKey('nibrs_offense.offense_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
bias = db.relationship('NibrsBiasList')
offense = db.relationship('NibrsOffense')
class NibrsCircumstance(db.Model):
__tablename__ = 'nibrs_circumstances'
circumstances_id = db.Column(db.SmallInteger, primary_key=True)
circumstances_type = db.Column(db.String(1))
circumstances_code = db.Column(db.SmallInteger)
circumstances_name = db.Column(db.String(100))
class NibrsClearedExcept(db.Model):
__tablename__ = 'nibrs_cleared_except'
cleared_except_id = db.Column(db.SmallInteger, primary_key=True)
cleared_except_code = db.Column(db.String(1))
cleared_except_name = db.Column(db.String(100))
class NibrsCriminalAct(db.Model):
__tablename__ = 'nibrs_criminal_act'
__table_args__ = (UniqueConstraint('criminal_act_id', 'offense_id'), )
criminal_act_id = db.Column(db.Integer,
db.ForeignKey(
'nibrs_criminal_act_type.criminal_act_id',
deferrable=True,
initially='DEFERRED'),
primary_key=True,
nullable=False,
index=True)
offense_id = db.Column(db.Integer,
db.ForeignKey('nibrs_offense.offense_id',
deferrable=True,
initially='DEFERRED'),
primary_key=True,
nullable=False,
index=True)
criminal_act = db.relationship('NibrsCriminalActType',
backref='criminal_acts')
offense = db.relationship('NibrsOffense', backref='criminal_acts')
class NibrsCriminalActType(db.Model):
__tablename__ = 'nibrs_criminal_act_type'
criminal_act_id = db.Column(db.SmallInteger, primary_key=True)
criminal_act_code = db.Column(db.String(1))
criminal_act_name = db.Column(db.String(100))
class NibrsDrugMeasureType(db.Model):
__tablename__ = 'nibrs_drug_measure_type'
drug_measure_type_id = db.Column(db.SmallInteger, primary_key=True)
drug_measure_code = db.Column(db.String(2))
drug_measure_name = db.Column(db.String(100))
class NibrsEd(db.Model):
__tablename__ = 'nibrs_eds'
id = db.Column(
db.Integer,
primary_key=True,
server_default=text("nextval('nibrs_eds_id_seq'::regclass)"))
ddocname = db.Column(db.String(100))
data_year = db.Column(db.SmallInteger)
month_num = db.Column(db.SmallInteger)
relative_rec_num = db.Column(db.Integer)
segment_action_type = db.Column(db.String(1))
ori = db.Column(db.String(9))
incident_num = db.Column(db.String(12))
level = db.Column(db.String(1))
offense_code = db.Column(db.String(3))
person_seq_num = db.Column(db.String(3))
type_prop_loss = db.Column(db.String(1))
data_element_num = db.Column(db.String(3))
error_num = db.Column(db.SmallInteger)
data_field = db.Column(db.String(12))
error_msg = db.Column(db.String(79))
submission_ser_num = db.Column(db.Integer)
class NibrsEthnicity(db.Model):
__tablename__ = 'nibrs_ethnicity'
ethnicity_id = db.Column(db.SmallInteger, primary_key=True)
ethnicity_code = db.Column(db.String(1))
ethnicity_name = db.Column(db.String(100))
hc_flag = db.Column(db.String(1))
class NibrsGrpbArrest(db.Model):
__tablename__ = 'nibrs_grpb_arrest'
grpb_arrest_id = db.Column(db.BigInteger, primary_key=True)
arrest_num = db.Column(db.String(15))
arrest_date = db.Column(db.DateTime(True))
arrest_seq_num = db.Column(db.SmallInteger)
city = db.Column(db.String(4))
arrest_type_id = db.Column(db.SmallInteger)
offense_type_id = db.Column(db.BigInteger)
sex_code = db.Column(db.String(1))
resident_code = db.Column(db.String(1))
under_18_disposition_code = db.Column(db.String(1))
age_num = db.Column(db.SmallInteger)
arrest_year = db.Column(db.SmallInteger)
ff_line_number = db.Column(db.BigInteger)
data_home = db.Column(db.String(1))
ddocname = db.Column(db.String(100))
did = db.Column(db.BigInteger)
age_range_low_num = db.Column(db.SmallInteger)
age_range_high_num = db.Column(db.SmallInteger)
age_id = db.Column(db.Integer,
db.ForeignKey('nibrs_age.age_id',
deferrable=True,
initially='DEFERRED'),
index=True)
agency_id = db.Column(db.Integer,
db.ForeignKey('ref_agency.agency_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
ethnicity_id = db.Column(db.Integer,
db.ForeignKey('nibrs_ethnicity.ethnicity_id',
deferrable=True,
initially='DEFERRED'),
index=True)
nibrs_month_id = db.Column(db.Integer,
db.ForeignKey('nibrs_month.nibrs_month_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
race_id = db.Column(db.Integer,
db.ForeignKey('ref_race.race_id',
deferrable=True,
initially='DEFERRED'),
index=True)
age = db.relationship('NibrsAge')
agency = db.relationship('RefAgency')
ethnicity = db.relationship('NibrsEthnicity')
nibrs_month = db.relationship('NibrsMonth')
race = db.relationship('RefRace')
class NibrsGrpbArrestWeapon(db.Model):
__tablename__ = 'nibrs_grpb_arrest_weapon'
nibrs_grpb_arrest_weapon_id = db.Column(db.BigInteger, primary_key=True)
grpb_arrest_id = db.Column(db.Integer,
db.ForeignKey(
'nibrs_grpb_arrest.grpb_arrest_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
weapon_id = db.Column(db.Integer,
db.ForeignKey('nibrs_weapon_type.weapon_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
grpb_arrest = db.relationship('NibrsGrpbArrest')
weapon = db.relationship('NibrsWeaponType')
class NibrsIncident(db.Model):
__tablename__ = 'nibrs_incident'
incident_id = db.Column(db.BigInteger, primary_key=True)
incident_number = db.Column(db.String(15))
cargo_theft_flag = db.Column(db.String(1))
submission_date = db.Column(db.DateTime(True))
incident_date = db.Column(db.DateTime(True))
report_date_flag = db.Column(db.String(1))
incident_hour = db.Column(db.SmallInteger)
cleared_except_date = db.Column(db.DateTime(True))
incident_status = db.Column(db.SmallInteger)
data_home = db.Column(db.String(1))
ddocname = db.Column(db.String(100))
orig_format = db.Column(db.String(1))
ff_line_number = db.Column(db.BigInteger)
did = db.Column(db.BigInteger)
agency_id = db.Column(db.Integer,
db.ForeignKey('ref_agency.agency_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
cleared_except_id = db.Column(db.Integer,
db.ForeignKey(
'nibrs_cleared_except.cleared_except_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
nibrs_month_id = db.Column(db.Integer,
db.ForeignKey('nibrs_month.nibrs_month_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
agency = db.relationship('RefAgency')
cleared_except = db.relationship('NibrsClearedExcept', lazy=False)
nibrs_month = db.relationship('NibrsMonth')
class NibrsInjury(db.Model):
__tablename__ = 'nibrs_injury'
injury_id = db.Column(db.SmallInteger, primary_key=True)
injury_code = db.Column(db.String(1))
injury_name = db.Column(db.String(100))
class NibrsJustifiableForce(db.Model):
__tablename__ = 'nibrs_justifiable_force'
justifiable_force_id = db.Column(db.SmallInteger, primary_key=True)
justifiable_force_code = db.Column(db.String(1))
justifiable_force_name = db.Column(db.String(100))
class NibrsLocationType(db.Model):
__tablename__ = 'nibrs_location_type'
location_id = db.Column(db.BigInteger, primary_key=True)
location_code = db.Column(db.String(2))
location_name = db.Column(db.String(100))
class NibrsMonth(db.Model):
__tablename__ = 'nibrs_month'
__table_args__ = (
UniqueConstraint('agency_id', 'month_num', 'data_year', 'data_home'), )
nibrs_month_id = db.Column(db.BigInteger, primary_key=True)
month_num = db.Column(db.SmallInteger, nullable=False)
data_year = db.Column(db.SmallInteger, nullable=False)
reported_status = db.Column(db.String(1))
report_date = db.Column(db.DateTime(True))
prepared_date = db.Column(db.DateTime(True))
update_flag = db.Column(db.String(1), nullable=False)
orig_format = db.Column(db.String(1), nullable=False)
ff_line_number = db.Column(db.BigInteger)
data_home = db.Column(db.String(1))
ddocname = db.Column(db.String(50))
did = db.Column(db.BigInteger)
agency_id = db.Column(db.Integer,
db.ForeignKey('ref_agency.agency_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
agency = db.relationship('RefAgency')
class NibrsOffender(db.Model):
__tablename__ = 'nibrs_offender'
offender_id = db.Column(db.BigInteger, primary_key=True)
offender_seq_num = db.Column(db.SmallInteger)
age_num = db.Column(db.SmallInteger)
sex_code = db.Column(db.String(1))
ff_line_number = db.Column(db.BigInteger)
age_range_low_num = db.Column(db.SmallInteger)
age_range_high_num = db.Column(db.SmallInteger)
age_id = db.Column(db.Integer,
db.ForeignKey('nibrs_age.age_id',
deferrable=True,
initially='DEFERRED'),
index=True)
ethnicity_id = db.Column(db.Integer,
db.ForeignKey('nibrs_ethnicity.ethnicity_id',
deferrable=True,
initially='DEFERRED'),
index=True)
incident_id = db.Column(db.Integer,
db.ForeignKey('nibrs_incident.incident_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
race_id = db.Column(db.Integer,
db.ForeignKey('ref_race.race_id',
deferrable=True,
initially='DEFERRED'),
index=True)
age = db.relationship('NibrsAge', backref='offenders', lazy=False)
ethnicity = db.relationship('NibrsEthnicity', backref='offenders')
incident = db.relationship('NibrsIncident',
backref=backref('offenders',
lazy=False))
race = db.relationship('RefRace', lazy=False, backref='offenders')
class NibrsOffense(db.Model):
__tablename__ = 'nibrs_offense'
offense_id = db.Column(db.BigInteger, primary_key=True)
attempt_complete_flag = db.Column(db.String(1))
num_premises_entered = db.Column(db.SmallInteger)
method_entry_code = db.Column(db.String(1))
ff_line_number = db.Column(db.BigInteger)
incident_id = db.Column(db.Integer,
db.ForeignKey('nibrs_incident.incident_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
location_id = db.Column(db.Integer,
db.ForeignKey('nibrs_location_type.location_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
offense_type_id = db.Column(db.Integer,
db.ForeignKey(
'nibrs_offense_type.offense_type_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
incident = db.relationship('NibrsIncident',
backref=backref('offenses',
lazy=False))
location = db.relationship('NibrsLocationType',
lazy=False,
backref='offenses')
offense_type = db.relationship('NibrsOffenseType',
lazy=False,
backref='offenses')
class NibrsOffenseType(db.Model):
__tablename__ = 'nibrs_offense_type'
offense_type_id = db.Column(db.BigInteger, primary_key=True)
offense_code = db.Column(db.String(5))
offense_name = db.Column(db.String(100))
crime_against = db.Column(db.String(100))
ct_flag = db.Column(db.String(1))
hc_flag = db.Column(db.String(1))
hc_code = db.Column(db.String(5))
offense_category_name = db.Column(db.String(100))
class NibrsPropDescType(db.Model):
__tablename__ = 'nibrs_prop_desc_type'
prop_desc_id = db.Column(db.SmallInteger, primary_key=True)
prop_desc_code = db.Column(db.String(2))
prop_desc_name = db.Column(db.String(100))
class NibrsPropLossType(db.Model):
__tablename__ = 'nibrs_prop_loss_type'
prop_loss_id = db.Column(db.SmallInteger, primary_key=True)
prop_loss_name = db.Column(db.String(100))
class NibrsProperty(db.Model):
__tablename__ = 'nibrs_property'
property_id = db.Column(db.BigInteger, primary_key=True)
stolen_count = db.Column(db.SmallInteger)
recovered_count = db.Column(db.SmallInteger)
ff_line_number = db.Column(db.BigInteger)
incident_id = db.Column(db.Integer,
db.ForeignKey('nibrs_incident.incident_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
prop_loss_id = db.Column(db.Integer,
db.ForeignKey('nibrs_prop_loss_type.prop_loss_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
incident = db.relationship('NibrsIncident',
backref=backref('property',
lazy=False))
prop_loss = db.relationship('NibrsPropLossType',
lazy=False,
backref='property')
class NibrsPropertyDesc(db.Model):
__tablename__ = 'nibrs_property_desc'
property_value = db.Column(db.BigInteger)
date_recovered = db.Column(db.DateTime(True))
nibrs_prop_desc_id = db.Column(db.BigInteger, primary_key=True)
prop_desc_id = db.Column(db.Integer,
db.ForeignKey('nibrs_prop_desc_type.prop_desc_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
property_id = db.Column(db.Integer,
db.ForeignKey('nibrs_property.property_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
prop_desc = db.relationship('NibrsPropDescType')
property = db.relationship('NibrsProperty')
class NibrsRelationship(db.Model):
__tablename__ = 'nibrs_relationship'
relationship_id = db.Column(db.SmallInteger, primary_key=True)
relationship_code = db.Column(db.String(2))
relationship_name = db.Column(db.String(100))
class NibrsSumMonthTemp(db.Model):
__tablename__ = 'nibrs_sum_month_temp'
id = db.Column(db.Integer,
primary_key=True,
server_default=text(
"nextval('nibrs_sum_month_temp_id_seq'::regclass)"))
nibrs_month_id = db.Column(db.BigInteger)
agency_id = db.Column(db.BigInteger)
month_num = db.Column(db.SmallInteger)
data_year = db.Column(db.SmallInteger)
reported_status = db.Column(db.String(1))
report_date = db.Column(db.DateTime(True))
prepared_date = db.Column(db.DateTime(True))
orig_format = db.Column(db.String(1))
ff_line_number = db.Column(db.BigInteger)
data_home = db.Column(db.String(1))
ddocname = db.Column(db.String(50))
did = db.Column(db.BigInteger)
nibrs_ct_flag = db.Column(db.String(1))
nibrs_hc_flag = db.Column(db.String(1))
nibrs_leoka_flag = db.Column(db.String(1))
nibrs_arson_flag = db.Column(db.String(1))
nibrs_ht_flag = db.Column(db.String(1))
class NibrsSuspectUsing(db.Model):
__tablename__ = 'nibrs_suspect_using'
__table_args__ = (UniqueConstraint('suspect_using_id', 'offense_id'), )
id = db.Column(
db.Integer,
primary_key=True,
server_default=text("nextval('nibrs_suspect_using_id_seq'::regclass)"))
offense_id = db.Column(db.Integer,
db.ForeignKey('nibrs_offense.offense_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
suspect_using_id = db.Column(db.Integer,
db.ForeignKey(
'nibrs_using_list.suspect_using_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
offense = db.relationship('NibrsOffense')
suspect_using = db.relationship('NibrsUsingList')
class NibrsSuspectedDrug(db.Model):
__tablename__ = 'nibrs_suspected_drug'
est_drug_qty = db.Column(Float(53))
nibrs_suspected_drug_id = db.Column(db.BigInteger, primary_key=True)
drug_measure_type_id = db.Column(
db.Integer,
db.ForeignKey('nibrs_drug_measure_type.drug_measure_type_id',
deferrable=True,
initially='DEFERRED'),
index=True)
property_id = db.Column(db.Integer,
db.ForeignKey('nibrs_property.property_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
suspected_drug_type_id = db.Column(
db.Integer,
db.ForeignKey('nibrs_suspected_drug_type.suspected_drug_type_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
drug_measure_type = db.relationship('NibrsDrugMeasureType')
property = db.relationship('NibrsProperty')
suspected_drug_type = db.relationship('NibrsSuspectedDrugType')
class NibrsSuspectedDrugType(db.Model):
__tablename__ = 'nibrs_suspected_drug_type'
suspected_drug_type_id = db.Column(db.SmallInteger, primary_key=True)
suspected_drug_code = db.Column(db.String(1))
suspected_drug_name = db.Column(db.String(100))
class NibrsUsingList(db.Model):
__tablename__ = 'nibrs_using_list'
suspect_using_id = db.Column(db.SmallInteger, primary_key=True)
suspect_using_code = db.Column(db.String(1))
suspect_using_name = db.Column(db.String(100))
class NibrsVictim(db.Model):
__tablename__ = 'nibrs_victim'
victim_id = db.Column(db.BigInteger, primary_key=True)
victim_seq_num = db.Column(db.SmallInteger)
outside_agency_id = db.Column(db.BigInteger)
age_num = db.Column(db.SmallInteger)
sex_code = db.Column(db.String(1))
resident_status_code = db.Column(db.String(1))
agency_data_year = db.Column(db.SmallInteger)
ff_line_number = db.Column(db.BigInteger)
age_range_low_num = db.Column(db.SmallInteger)
age_range_high_num = db.Column(db.SmallInteger)
activity_type_id = db.Column(
db.Integer,
db.ForeignKey('nibrs_activity_type.activity_type_id',
deferrable=True,
initially='DEFERRED'),
index=True)
age_id = db.Column(db.Integer,
db.ForeignKey('nibrs_age.age_id',
deferrable=True,
initially='DEFERRED'),
index=True)
assignment_type_id = db.Column(
db.Integer,
db.ForeignKey('nibrs_assignment_type.assignment_type_id',
deferrable=True,
initially='DEFERRED'),
index=True)
ethnicity_id = db.Column(db.Integer,
db.ForeignKey('nibrs_ethnicity.ethnicity_id',
deferrable=True,
initially='DEFERRED'),
index=True)
incident_id = db.Column(db.Integer,
db.ForeignKey('nibrs_incident.incident_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
race_id = db.Column(db.Integer,
db.ForeignKey('ref_race.race_id',
deferrable=True,
initially='DEFERRED'),
index=True)
victim_type_id = db.Column(db.Integer,
db.ForeignKey(
'nibrs_victim_type.victim_type_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
activity_type = db.relationship('NibrsActivityType', backref='victims')
age = db.relationship('NibrsAge', backref='victims', lazy=False)
assignment_type = db.relationship('NibrsAssignmentType', backref='victims')
ethnicity = db.relationship('NibrsEthnicity', backref='victims')
incident = db.relationship('NibrsIncident',
backref=backref('victims',
lazy=False))
race = db.relationship('RefRace', lazy=False, backref='victims')
victim_type = db.relationship('NibrsVictimType',
lazy=False,
backref='victims')
class NibrsVictimCircumstance(db.Model):
__tablename__ = 'nibrs_victim_circumstances'
__table_args__ = (UniqueConstraint('victim_id', 'circumstances_id'), )
id = db.Column(
db.Integer,
primary_key=True,
server_default=text(
"nextval('nibrs_victim_circumstances_id_seq'::regclass)"))
circumstances_id = db.Column(db.Integer,
db.ForeignKey(
'nibrs_circumstances.circumstances_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
justifiable_force_id = db.Column(
db.Integer,
db.ForeignKey('nibrs_justifiable_force.justifiable_force_id',
deferrable=True,
initially='DEFERRED'),
index=True)
victim_id = db.Column(db.Integer,
db.ForeignKey('nibrs_victim.victim_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
circumstances = db.relationship('NibrsCircumstance')
justifiable_force = db.relationship('NibrsJustifiableForce')
victim = db.relationship('NibrsVictim')
class NibrsVictimInjury(db.Model):
__tablename__ = 'nibrs_victim_injury'
__table_args__ = (UniqueConstraint('victim_id', 'injury_id'), )
# id = db.Column(
# db.Integer,
# primary_key=True,
# server_default=text("nextval('nibrs_victim_injury_id_seq'::regclass)"))
injury_id = db.Column(db.Integer,
db.ForeignKey('nibrs_injury.injury_id',
deferrable=True,
initially='DEFERRED'),
primary_key=True,
nullable=False,
index=True)
victim_id = db.Column(db.Integer,
db.ForeignKey('nibrs_victim.victim_id',
deferrable=True,
initially='DEFERRED'),
primary_key=True,
nullable=False,
index=True)
injury = db.relationship('NibrsInjury')
victim = db.relationship('NibrsVictim', backref="injuries")
class NibrsVictimOffenderRel(db.Model):
__tablename__ = 'nibrs_victim_offender_rel'
nibrs_victim_offender_id = db.Column(db.BigInteger, primary_key=True)
offender_id = db.Column(db.Integer,
db.ForeignKey('nibrs_offender.offender_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
relationship_id = db.Column(db.Integer,
db.ForeignKey(
'nibrs_relationship.relationship_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
victim_id = db.Column(db.Integer,
db.ForeignKey('nibrs_victim.victim_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
offender = db.relationship('NibrsOffender', backref="relationships")
relationship_ = db.relationship('NibrsRelationship',
backref="relationships")
victim = db.relationship('NibrsVictim', backref="relationships")
class NibrsVictimOffense(db.Model):
__tablename__ = 'nibrs_victim_offense'
__table_args__ = (UniqueConstraint('victim_id', 'offense_id'), )
id = db.Column(db.Integer,
primary_key=True,
server_default=text(
"nextval('nibrs_victim_offense_id_seq'::regclass)"))
offense_id = db.Column(db.Integer,
db.ForeignKey('nibrs_offense.offense_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
victim_id = db.Column(db.Integer,
db.ForeignKey('nibrs_victim.victim_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
offense = db.relationship('NibrsOffense')
victim = db.relationship('NibrsVictim')
class NibrsVictimType(db.Model):
__tablename__ = 'nibrs_victim_type'
victim_type_id = db.Column(db.SmallInteger, primary_key=True)
victim_type_code = db.Column(db.String(1))
victim_type_name = db.Column(db.String(100))
class NibrsWeapon(db.Model):
__tablename__ = 'nibrs_weapon'
nibrs_weapon_id = db.Column(db.BigInteger, primary_key=True)
offense_id = db.Column(db.Integer,
db.ForeignKey('nibrs_offense.offense_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
weapon_id = db.Column(db.Integer,
db.ForeignKey('nibrs_weapon_type.weapon_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
weapon = db.relationship('NibrsWeaponType', backref='weapons')
offense = db.relationship('NibrsOffense', backref='weapons')
class NibrsWeaponType(db.Model):
__tablename__ = 'nibrs_weapon_type'
weapon_id = db.Column(db.SmallInteger, primary_key=True)
weapon_code = db.Column(db.String(3))
weapon_name = db.Column(db.String(100))
shr_flag = db.Column(db.String(1))
class OffenseClassification(db.Model):
__tablename__ = 'offense_classification'
classification_id = db.Column(db.SmallInteger, primary_key=True)
classification_name = db.Column(db.String(50))
class_sort_order = db.Column(db.SmallInteger)
class PeEmployeeData(db.Model):
__tablename__ = 'pe_employee_data'
__table_args__ = (UniqueConstraint('agency_id', 'data_year'), )
agency_id = db.Column(db.Integer,
db.ForeignKey('ref_agency.agency_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True,
primary_key=True)
data_year = db.Column(db.SmallInteger, nullable=False, primary_key=True)
reported_flag = db.Column(db.String(1))
male_officer = db.Column(db.Integer)
male_civilian = db.Column(db.Integer)
male_total = db.Column(db.Integer)
female_officer = db.Column(db.Integer)
female_civilian = db.Column(db.Integer)
female_total = db.Column(db.Integer)
officer_rate = db.Column(db.Integer)
civilian_rate = db.Column(db.Integer)
class RefUniversity(db.Model):
__tablename__ = 'ref_university'
university_id = db.Column(db.BigInteger, primary_key=True)
university_abbr = db.Column(db.String(20))
university_name = db.Column(db.String(100), unique=True)
class RefUniversityCampus(db.Model):
__tablename__ = 'ref_university_campus'
campus_id = db.Column(db.BigInteger, primary_key=True)
campus_name = db.Column(db.String(100))
university_id = db.Column(db.Integer,
db.ForeignKey('ref_university.university_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
university = db.relationship('RefUniversity')
class RefAgencyCounty(db.Model):
__tablename__ = 'ref_agency_county'
__table_args__ = (UniqueConstraint('agency_id', 'county_id',
'metro_div_id', 'data_year'), )
core_city_flag = db.Column(db.String(1))
data_year = db.Column(db.SmallInteger, nullable=False, primary_key=True)
population = db.Column(db.BigInteger)
census = db.Column(db.BigInteger)
legacy_county_code = db.Column(db.String(20))
legacy_msa_code = db.Column(db.String(20))
source_flag = db.Column(db.String(1))
change_timestamp = db.Column(db.DateTime(True))
change_user = db.Column(db.String(100))
agency_id = db.Column(db.Integer,
db.ForeignKey('ref_agency.agency_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True,
primary_key=True)
county_id = db.Column(db.Integer,
db.ForeignKey('ref_county.county_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True,
primary_key=True)
metro_div_id = db.Column(db.Integer,
db.ForeignKey('ref_metro_division.metro_div_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True,
primary_key=True)
agency = db.relationship('RefAgency', backref='county_associations')
county = db.relationship('RefCounty', backref='agency_associations')
metro_div = db.relationship('RefMetroDivision')
class RefAgency(db.Model):
__tablename__ = 'ref_agency'
agency_id = db.Column(db.BigInteger, primary_key=True)
ori = db.Column(db.String(9), nullable=False, unique=True)
legacy_ori = db.Column(db.String(9), nullable=False)
ucr_agency_name = db.Column(db.String(100))
ncic_agency_name = db.Column(db.String(100))
pub_agency_name = db.Column(db.String(100))
special_mailing_group = db.Column(db.String(1))
special_mailing_address = db.Column(db.String(1))
agency_status = db.Column(db.String(1), nullable=False)
judicial_dist_code = db.Column(db.String(4))
fid_code = db.Column(db.String(2))
added_date = db.Column(db.DateTime(True))
change_timestamp = db.Column(db.DateTime(True))
change_user = db.Column(db.String(100))
legacy_notify_agency = db.Column(db.String(1))
dormant_year = db.Column(db.SmallInteger)
agency_type_id = db.Column(db.Integer,
db.ForeignKey('ref_agency_type.agency_type_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
campus_id = db.Column(db.Integer,
db.ForeignKey('ref_university_campus.campus_id',
deferrable=True,
initially='DEFERRED'),
index=True)
city_id = db.Column(db.Integer,
db.ForeignKey('ref_city.city_id',
deferrable=True,
initially='DEFERRED'),
index=True)
department_id = db.Column(db.Integer,
db.ForeignKey('ref_department.department_id',
deferrable=True,
initially='DEFERRED'),
index=True)
field_office_id = db.Column(
db.Integer,
db.ForeignKey('ref_field_office.field_office_id',
deferrable=True,
initially='DEFERRED'),
index=True)
population_family_id = db.Column(
db.Integer,
db.ForeignKey('ref_population_family.population_family_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
state_id = db.Column(db.Integer,
db.ForeignKey('ref_state.state_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
submitting_agency_id = db.Column(
db.Integer,
db.ForeignKey('ref_submitting_agency.agency_id',
deferrable=True,
initially='DEFERRED'),
index=True)
tribe_id = db.Column(db.Integer,
db.ForeignKey('ref_tribe.tribe_id',
deferrable=True,
initially='DEFERRED'),
index=True)
agency_type = db.relationship('RefAgencyType', lazy=False)
campus = db.relationship('RefUniversityCampus')
city = db.relationship('RefCity', lazy=False)
department = db.relationship('RefDepartment', lazy=False)
field_office = db.relationship('RefFieldOffice', lazy=False)
population_family = db.relationship('RefPopulationFamily', lazy=False)
state = db.relationship('RefState', lazy=False)
submitting_agency = db.relationship('RefSubmittingAgency', lazy=False)
tribe = db.relationship('RefTribe')
counties = db.relationship('RefCounty',
secondary=RefAgencyCounty.__table__,
collection_class=set)
class RefAgencyCoveredBy(db.Model):
__tablename__ = 'ref_agency_covered_by'
__table_args__ = (UniqueConstraint('agency_id', 'data_year'), )
id = db.Column(db.Integer,
primary_key=True,
server_default=text(
"nextval('ref_agency_covered_by_id_seq'::regclass)"))
data_year = db.Column(db.SmallInteger, nullable=False)
agency_id = db.Column(db.Integer,
db.ForeignKey('ref_agency.agency_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
covered_by_agency_id = db.Column(db.Integer,
db.ForeignKey('ref_agency.agency_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
agency = db.relationship(
'RefAgency',
primaryjoin='RefAgencyCoveredBy.agency_id == RefAgency.agency_id')
covered_by_agency = db.relationship(
'RefAgency',
primaryjoin=
'RefAgencyCoveredBy.covered_by_agency_id == RefAgency.agency_id')
class RefAgencyDataContent(db.Model):
__tablename__ = 'ref_agency_data_content'
__table_args__ = (UniqueConstraint('agency_id', 'data_year'), )
id = db.Column(db.Integer,
primary_key=True,
server_default=text(
"nextval('ref_agency_data_content_id_seq'::regclass)"))
data_year = db.Column(db.SmallInteger, nullable=False)
reporting_type = db.Column(db.String(1))
nibrs_ct_flag = db.Column(db.String(1))
nibrs_hc_flag = db.Column(db.String(1))
nibrs_leoka_flag = db.Column(db.String(1))
nibrs_arson_flag = db.Column(db.String(1))
summary_rape_def = db.Column(db.String(1))
nibrs_ht_flag = db.Column(db.String(1))
agency_id = db.Column(db.Integer,
db.ForeignKey('ref_agency.agency_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
agency = db.relationship('RefAgency')
class RefAgencyPoc(db.Model):
__tablename__ = 'ref_agency_poc'
__table_args__ = (UniqueConstraint('agency_id', 'poc_id'), )
id = db.Column(
db.Integer,
primary_key=True,
server_default=text("nextval('ref_agency_poc_id_seq'::regclass)"))
agency_id = db.Column(db.BigInteger, nullable=False)
primary_poc_flag = db.Column(db.String(1))
poc_id = db.Column(db.Integer,
db.ForeignKey('ref_poc.poc_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
poc = db.relationship('RefPoc')
class RefAgencyType(db.Model):
__tablename__ = 'ref_agency_type'
agency_type_id = db.Column(db.SmallInteger, primary_key=True)
agency_type_name = db.Column(db.String(100))
default_pop_family_id = db.Column(
db.Integer,
db.ForeignKey('ref_population_family.population_family_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
default_pop_family = db.relationship('RefPopulationFamily')
class RefCampusPopulation(db.Model):
__tablename__ = 'ref_campus_population'
__table_args__ = (UniqueConstraint('campus_id', 'data_year'), )
id = db.Column(db.Integer,
primary_key=True,
server_default=text(
"nextval('ref_campus_population_id_seq'::regclass)"))
data_year = db.Column(db.SmallInteger, nullable=False)
population = db.Column(db.BigInteger)
source_flag = db.Column(db.String(1), nullable=False)
census = db.Column(db.BigInteger)
change_timestamp = db.Column(db.DateTime(True))
change_user = db.Column(db.String(100))
reporting_population = db.Column(db.BigInteger)
campus_id = db.Column(db.Integer,
db.ForeignKey('ref_university_campus.campus_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
campus = db.relationship('RefUniversityCampus')
class RefCity(db.Model):
__tablename__ = 'ref_city'
__table_args__ = (UniqueConstraint('city_name', 'state_id'), )
city_id = db.Column(db.BigInteger, primary_key=True)
city_name = db.Column(db.String(100))
state_id = db.Column(db.Integer,
db.ForeignKey('ref_state.state_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
state = db.relationship('RefState')
class RefContinent(db.Model):
__tablename__ = 'ref_continent'
continent_id = db.Column(db.SmallInteger, primary_key=True)
continent_desc = db.Column(db.String(50))
class RefCountry(db.Model):
__tablename__ = 'ref_country'
country_id = db.Column(db.SmallInteger, primary_key=True)
country_desc = db.Column(db.String(50))
continent_id = db.Column(db.Integer,
db.ForeignKey('ref_continent.continent_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
continent = db.relationship('RefContinent')
class RefCounty(db.Model):
__tablename__ = 'ref_county'
county_id = db.Column(db.BigInteger, primary_key=True)
county_name = db.Column(db.String(100))
county_ansi_code = db.Column(db.String(5))
county_fips_code = db.Column(db.String(5))
legacy_county_code = db.Column(db.String(5))
comments = db.Column(db.String(1000))
state_id = db.Column(db.Integer,
db.ForeignKey('ref_state.state_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
state = db.relationship('RefState', back_populates='counties')
class RefCountyPopulation(db.Model):
__tablename__ = 'ref_county_population'
__table_args__ = (UniqueConstraint('county_id', 'data_year'), )
data_year = db.Column(db.SmallInteger, nullable=False, primary_key=True)
population = db.Column(db.BigInteger)
source_flag = db.Column(db.String(1), nullable=False)
change_timestamp = db.Column(db.DateTime(True))
change_user = db.Column(db.String(100))
reporting_population = db.Column(db.BigInteger)
county_id = db.Column(db.Integer,
db.ForeignKey('ref_county.county_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True,
primary_key=True)
county = db.relationship('RefCounty')
class RefStatePopulation(db.Model):
__tablename__ = 'ref_state_population'
__tableargs__ = (UniqueConstraint('state_id', 'data_year'), )
state_id = db.Column(db.Integer,
db.ForeignKey('ref_state.state_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True,
primary_key=True)
data_year = db.Column(db.SmallInteger, nullable=False, primary_key=True)
population = db.Column(db.BigInteger)
source_flag = db.Column(db.String(1), nullable=False)
census = db.Column(db.BigInteger)
change_timestamp = db.Column(db.DateTime(True))
change_user = db.Column(db.String(100))
reporting_population = db.Column(db.BigInteger)
class RefDepartment(db.Model):
__tablename__ = 'ref_department'
department_id = db.Column(db.SmallInteger, primary_key=True)
department_name = db.Column(db.String(100), nullable=False)
active_flag = db.Column(db.String(1), nullable=False)
sort_order = db.Column(db.SmallInteger)
class RefDivision(db.Model):
__tablename__ = 'ref_division'
division_id = db.Column(db.SmallInteger, primary_key=True)
division_code = db.Column(db.String(2))
division_name = db.Column(db.String(100))
division_desc = db.Column(db.String(100))
region_id = db.Column(db.Integer,
db.ForeignKey('ref_region.region_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
region = db.relationship('RefRegion', lazy=False)
class RefFieldOffice(db.Model):
__tablename__ = 'ref_field_office'
field_office_id = db.Column(db.BigInteger, primary_key=True)
field_office_code = db.Column(db.String(10))
field_office_name = db.Column(db.String(100))
field_office_alpha_code = db.Column(db.String(2))
field_office_numeric_code = db.Column(db.String(10))
class RefGlobalLocation(db.Model):
__tablename__ = 'ref_global_location'
global_location_id = db.Column(db.BigInteger, primary_key=True)
global_location_desc = db.Column(db.String(50))
country_id = db.Column(db.Integer,
db.ForeignKey('ref_country.country_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
country = db.relationship('RefCountry')
class RefMetroDivPopulation(db.Model):
__tablename__ = 'ref_metro_div_population'
__table_args__ = (UniqueConstraint('metro_div_id', 'data_year'), )
id = db.Column(db.Integer,
primary_key=True,
server_default=text(
"nextval('ref_metro_div_population_id_seq'::regclass)"))
data_year = db.Column(db.SmallInteger, nullable=False)
population = db.Column(db.BigInteger)
source_flag = db.Column(db.String(1), nullable=False)
census = db.Column(db.BigInteger)
change_timestamp = db.Column(db.DateTime(True))
change_user = db.Column(db.String(100))
reporting_population = db.Column(db.BigInteger)
metro_div_id = db.Column(db.Integer,
db.ForeignKey('ref_metro_division.metro_div_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
metro_div = db.relationship('RefMetroDivision')
class RefMetroDivision(db.Model):
__tablename__ = 'ref_metro_division'
metro_div_id = db.Column(db.BigInteger, primary_key=True)
metro_div_name = db.Column(db.String(100))
msa_flag = db.Column(db.String(1))
metro_div_omb_code = db.Column(db.String(5))
legacy_msa_code = db.Column(db.String(5))
msa_id = db.Column(db.Integer,
db.ForeignKey('ref_msa.msa_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
msa = db.relationship('RefMsa')
class RefMsa(db.Model):
__tablename__ = 'ref_msa'
__table_args__ = (UniqueConstraint('msa_name', 'msa_omb_code'), )
msa_id = db.Column(db.BigInteger, primary_key=True)
msa_name = db.Column(db.String(100))
msa_omb_code = db.Column(db.String(5))
class RefParentPopulationGroup(db.Model):
__tablename__ = 'ref_parent_population_group'
parent_pop_group_id = db.Column(db.BigInteger, primary_key=True)
parent_pop_group_code = db.Column(db.String(2))
parent_pop_group_desc = db.Column(db.String(100))
publication_name = db.Column(db.String(100))
population_family_id = db.Column(
db.Integer,
db.ForeignKey('ref_population_family.population_family_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
population_family = db.relationship('RefPopulationFamily')
class RefPoc(db.Model):
__tablename__ = 'ref_poc'
poc_id = db.Column(db.BigInteger, primary_key=True)
poc_name = db.Column(db.String(200))
poc_title = db.Column(db.String(200))
poc_email = db.Column(db.String(200))
poc_phone1 = db.Column(db.String(50))
poc_phone2 = db.Column(db.String(50))
mailing_address_1 = db.Column(db.String(150))
mailing_address_2 = db.Column(db.String(150))
mailing_address_3 = db.Column(db.String(150))
mailing_address_4 = db.Column(db.String(150))
zip_code = db.Column(db.String(10))
city_name = db.Column(db.String(100))
poc_fax1 = db.Column(db.String(20))
poc_fax2 = db.Column(db.String(20))
state_id = db.Column(db.Integer,
db.ForeignKey('ref_state.state_id',
deferrable=True,
initially='DEFERRED'),
index=True)
state = db.relationship('RefState')
class RefPocRole(db.Model):
__tablename__ = 'ref_poc_role'
poc_role_id = db.Column(db.SmallInteger, primary_key=True)
poc_role_name = db.Column(db.String(100))
class RefPocRoleAssign(db.Model):
__tablename__ = 'ref_poc_role_assign'
__table_args__ = (UniqueConstraint('poc_id', 'poc_role_id'), )
id = db.Column(
db.Integer,
primary_key=True,
server_default=text("nextval('ref_poc_role_assign_id_seq'::regclass)"))
poc_id = db.Column(db.Integer,
db.ForeignKey('ref_poc.poc_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
poc_role_id = db.Column(db.Integer,
db.ForeignKey('ref_poc_role.poc_role_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
poc = db.relationship('RefPoc')
poc_role = db.relationship('RefPocRole')
class RefPopulationFamily(db.Model):
__tablename__ = 'ref_population_family'
population_family_id = db.Column(db.SmallInteger, primary_key=True)
population_family_name = db.Column(db.String(100))
population_family_desc = db.Column(db.String(200))
sort_order = db.Column(db.SmallInteger)
class RefPopulationGroup(db.Model):
__tablename__ = 'ref_population_group'
population_group_id = db.Column(db.BigInteger, primary_key=True)
population_group_code = db.Column(db.String(2))
population_group_desc = db.Column(db.String(150))
publication_name = db.Column(db.String(100))
parent_pop_group_id = db.Column(
db.Integer,
db.ForeignKey('ref_parent_population_group.parent_pop_group_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
parent_pop_group = db.relationship('RefParentPopulationGroup')
class RefState(db.Model):
__tablename__ = 'ref_state'
state_id = db.Column(db.SmallInteger, primary_key=True)
state_name = db.Column(db.String(100))
state_code = db.Column(db.String(2))
state_abbr = db.Column(db.String(2))
state_postal_abbr = db.Column(db.String(2))
state_fips_code = db.Column(db.String(2))
state_pub_freq_months = db.Column(db.SmallInteger)
division_id = db.Column(db.Integer,
db.ForeignKey('ref_division.division_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
division = db.relationship('RefDivision', lazy=False)
counties = db.relationship('RefCounty',
lazy='dynamic',
back_populates='state')
class RefSubmittingAgency(db.Model):
__tablename__ = 'ref_submitting_agency'
agency_id = db.Column(db.BigInteger, primary_key=True)
sai = db.Column(db.String(9))
agency_name = db.Column(db.String(150))
notify_agency = db.Column(db.String(1))
agency_email = db.Column(db.String(200))
agency_website = db.Column(db.String(2000))
comments = db.Column(db.String(2000))
state_id = db.Column(db.Integer,
db.ForeignKey('ref_state.state_id',
deferrable=True,
initially='DEFERRED'),
index=True)
state = db.relationship('RefState')
class RefTribe(db.Model):
__tablename__ = 'ref_tribe'
tribe_id = db.Column(db.BigInteger, primary_key=True)
tribe_name = db.Column(db.String(100), unique=True)
class RefTribePopulation(db.Model):
__tablename__ = 'ref_tribe_population'
__table_args__ = (UniqueConstraint('tribe_id', 'data_year'), )
id = db.Column(db.Integer,
primary_key=True,
server_default=text(
"nextval('ref_tribe_population_id_seq'::regclass)"))
data_year = db.Column(db.SmallInteger, nullable=False)
population = db.Column(db.BigInteger)
source_flag = db.Column(db.String(1), nullable=False)
census = db.Column(db.BigInteger)
change_timestamp = db.Column(db.DateTime(True))
change_user = db.Column(db.String(100))
reporting_population = db.Column(db.BigInteger)
tribe_id = db.Column(db.Integer,
db.ForeignKey('ref_tribe.tribe_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
tribe = db.relationship('RefTribe')
class RetaMonth(db.Model):
__tablename__ = 'reta_month'
__table_args__ = (UniqueConstraint('agency_id', 'data_year', 'month_num',
'data_home', 'source_flag'), )
reta_month_id = db.Column(db.BigInteger, primary_key=True)
data_year = db.Column(db.SmallInteger, nullable=False)
month_num = db.Column(db.SmallInteger, nullable=False)
data_home = db.Column(db.String(1), nullable=False)
source_flag = db.Column(db.String(1), nullable=False)
reported_flag = db.Column(db.String(1), nullable=False)
ddocname = db.Column(db.String(100))
month_included_in = db.Column(db.SmallInteger)
report_date = db.Column(db.DateTime(True))
prepared_date = db.Column(db.DateTime(True))
prepared_by_user = db.Column(db.String(100))
prepared_by_email = db.Column(db.String(200))
orig_format = db.Column(db.String(1), nullable=False)
total_reported_count = db.Column(db.Integer)
total_unfounded_count = db.Column(db.Integer)
total_actual_count = db.Column(db.Integer)
total_cleared_count = db.Column(db.Integer)
total_juvenile_cleared_count = db.Column(db.Integer)
leoka_felony = db.Column(db.SmallInteger)
leoka_accident = db.Column(db.SmallInteger)
leoka_assault = db.Column(db.Integer)
leoka_status = db.Column(db.SmallInteger)
update_flag = db.Column(db.String(1))
did = db.Column(db.BigInteger)
ff_line_number = db.Column(db.BigInteger)
agency_id = db.Column(db.Integer,
db.ForeignKey('ref_agency.agency_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
agency = db.relationship('RefAgency')
class RetaMonthOffenseSubcat(db.Model):
__tablename__ = 'reta_month_offense_subcat'
__table_args__ = (UniqueConstraint('offense_subcat_id', 'reta_month_id'), )
id = db.Column(
db.Integer,
primary_key=True,
server_default=text(
"nextval('reta_month_offense_subcat_id_seq'::regclass)"))
reported_count = db.Column(db.Integer)
reported_status = db.Column(db.SmallInteger)
unfounded_count = db.Column(db.Integer)
unfounded_status = db.Column(db.SmallInteger)
actual_count = db.Column(db.Integer)
actual_status = db.Column(db.SmallInteger)
cleared_count = db.Column(db.Integer)
cleared_status = db.Column(db.SmallInteger)
juvenile_cleared_count = db.Column(db.Integer)
juvenile_cleared_status = db.Column(db.SmallInteger)
offense_subcat_id = db.Column(db.Integer,
db.ForeignKey(
'reta_offense_subcat.offense_subcat_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
reta_month_id = db.Column(db.Integer,
db.ForeignKey('reta_month.reta_month_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
offense_subcat = db.relationship('RetaOffenseSubcat')
reta_month = db.relationship('RetaMonth')
class RetaOffense(db.Model):
__tablename__ = 'reta_offense'
offense_id = db.Column(db.BigInteger, primary_key=True)
offense_name = db.Column(db.String(100), nullable=False)
offense_code = db.Column(db.String(20), nullable=False, unique=True)
offense_xml_path = db.Column(db.String(1000))
offense_sort_order = db.Column(db.BigInteger)
classification_id = db.Column(
db.Integer,
db.ForeignKey('offense_classification.classification_id',
deferrable=True,
initially='DEFERRED'),
index=True)
offense_category_id = db.Column(
db.Integer,
db.ForeignKey('reta_offense_category.offense_category_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
classification = db.relationship('OffenseClassification')
offense_category = db.relationship('RetaOffenseCategory')
class RetaOffenseCategory(db.Model):
__tablename__ = 'reta_offense_category'
offense_category_id = db.Column(db.SmallInteger, primary_key=True)
offense_category_name = db.Column(db.String(50))
offense_category_sort_order = db.Column(db.SmallInteger, nullable=False)
crime_type_id = db.Column(db.Integer,
db.ForeignKey('crime_type.crime_type_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
crime_type = db.relationship('CrimeType', backref='categories')
class RetaOffenseSubcat(db.Model):
__tablename__ = 'reta_offense_subcat'
offense_subcat_id = db.Column(db.BigInteger, primary_key=True)
offense_subcat_name = db.Column(db.String(100), nullable=False)
offense_subcat_code = db.Column(db.String(20), nullable=False, unique=True)
offense_subcat_xml_path = db.Column(db.String(1000))
offense_subcat_sort_order = db.Column(db.BigInteger)
part = db.Column(db.String(1))
crime_index_flag = db.Column(db.String(1))
offense_id = db.Column(db.Integer,
db.ForeignKey('reta_offense.offense_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
offense = db.relationship('RetaOffense', backref='subcategories')
class ShrCircumstance(db.Model):
__tablename__ = 'shr_circumstances'
circumstances_id = db.Column(db.SmallInteger, primary_key=True)
circumstances_code = db.Column(db.String(2), nullable=False)
sub_code = db.Column(db.String(1))
circumstances_name = db.Column(db.String(100), nullable=False)
sub_name = db.Column(db.String(100))
current_flag = db.Column(db.String(1))
class ShrIncident(db.Model):
__tablename__ = 'shr_incident'
__table_args__ = (
UniqueConstraint('shr_month_id', 'incident_num', 'data_home'), )
incident_id = db.Column(db.BigInteger, primary_key=True)
homicide_code = db.Column(db.String(1))
incident_num = db.Column(db.String(3))
incident_status = db.Column(db.SmallInteger)
update_flag = db.Column(db.String(1))
data_home = db.Column(db.String(1))
prepared_date = db.Column(db.DateTime(True))
report_date = db.Column(db.DateTime(True))
ddocname = db.Column(db.String(100))
ff_line_number = db.Column(db.BigInteger)
orig_format = db.Column(db.String(1))
did = db.Column(db.BigInteger)
nibrs_incident_id = db.Column(db.BigInteger)
shr_month_id = db.Column(db.Integer,
db.ForeignKey('shr_month.shr_month_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
situation_id = db.Column(db.Integer,
db.ForeignKey('shr_situation.situation_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
shr_month = db.relationship('ShrMonth')
situation = db.relationship('ShrSituation')
class ShrMonth(db.Model):
__tablename__ = 'shr_month'
__table_args__ = (
UniqueConstraint('agency_id', 'data_year', 'month_num', 'data_home'), )
shr_month_id = db.Column(db.BigInteger, primary_key=True)
data_year = db.Column(db.SmallInteger, nullable=False)
month_num = db.Column(db.SmallInteger, nullable=False)
data_home = db.Column(db.String(1), nullable=False)
source_flag = db.Column(db.String(1))
reported_flag = db.Column(db.String(1))
orig_format = db.Column(db.String(1))
update_flag = db.Column(db.String(1))
ff_line_number = db.Column(db.BigInteger)
ddocname = db.Column(db.String(100))
did = db.Column(db.BigInteger)
agency_id = db.Column(db.Integer,
db.ForeignKey('ref_agency.agency_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
agency = db.relationship('RefAgency')
class ShrOffender(db.Model):
__tablename__ = 'shr_offender'
offender_id = db.Column(db.BigInteger, primary_key=True)
offender_num = db.Column(db.String(20))
age_num = db.Column(db.SmallInteger)
sex_code = db.Column(db.String(1))
nibrs_offense_id = db.Column(db.BigInteger)
nibrs_offender_id = db.Column(db.BigInteger)
age_id = db.Column(db.Integer,
db.ForeignKey('nibrs_age.age_id',
deferrable=True,
initially='DEFERRED'),
index=True)
ethnicity_id = db.Column(db.Integer,
db.ForeignKey('nibrs_ethnicity.ethnicity_id',
deferrable=True,
initially='DEFERRED'),
index=True)
race_id = db.Column(db.Integer,
db.ForeignKey('ref_race.race_id',
deferrable=True,
initially='DEFERRED'),
index=True)
age = db.relationship('NibrsAge')
ethnicity = db.relationship('NibrsEthnicity')
race = db.relationship('RefRace')
class ShrOffense(db.Model):
__tablename__ = 'shr_offense'
offense_id = db.Column(db.BigInteger, primary_key=True)
nibrs_offense_id = db.Column(db.BigInteger)
circumstances_id = db.Column(
db.Integer,
db.ForeignKey('shr_circumstances.circumstances_id',
deferrable=True,
initially='DEFERRED'),
index=True)
incident_id = db.Column(db.Integer,
db.ForeignKey('shr_incident.incident_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
offender_id = db.Column(db.Integer,
db.ForeignKey('shr_offender.offender_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
relationship_id = db.Column(
db.Integer,
db.ForeignKey('shr_relationship.relationship_id',
deferrable=True,
initially='DEFERRED'),
index=True)
victim_id = db.Column(db.Integer,
db.ForeignKey('shr_victim.victim_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
weapon_id = db.Column(db.Integer,
db.ForeignKey('nibrs_weapon_type.weapon_id',
deferrable=True,
initially='DEFERRED'),
index=True)
circumstances = db.relationship('ShrCircumstance')
incident = db.relationship('ShrIncident')
offender = db.relationship('ShrOffender')
relationship_ = db.relationship('ShrRelationship')
victim = db.relationship('ShrVictim')
weapon = db.relationship('NibrsWeaponType')
class ShrRelationship(db.Model):
__tablename__ = 'shr_relationship'
relationship_id = db.Column(db.SmallInteger, primary_key=True)
relationship_code = db.Column(db.String(2))
relationship_name = db.Column(db.String(100))
class ShrSituation(db.Model):
__tablename__ = 'shr_situation'
situation_id = db.Column(db.SmallInteger, primary_key=True)
situation_code = db.Column(db.String(1))
situation_name = db.Column(db.String(100))
class ShrVictim(db.Model):
__tablename__ = 'shr_victim'
victim_id = db.Column(db.BigInteger, primary_key=True)
victim_num = db.Column(db.String(20))
age_num = db.Column(db.SmallInteger)
sex_code = db.Column(db.String(1))
nibrs_victim_id = db.Column(db.BigInteger)
nibrs_offense_id = db.Column(db.BigInteger)
age_id = db.Column(db.Integer,
db.ForeignKey('nibrs_age.age_id',
deferrable=True,
initially='DEFERRED'),
index=True)
ethnicity_id = db.Column(db.Integer,
db.ForeignKey('nibrs_ethnicity.ethnicity_id',
deferrable=True,
initially='DEFERRED'),
index=True)
race_id = db.Column(db.Integer,
db.ForeignKey('ref_race.race_id',
deferrable=True,
initially='DEFERRED'),
index=True)
age = db.relationship('NibrsAge')
ethnicity = db.relationship('NibrsEthnicity')
race = db.relationship('RefRace')
class SuppLarcenyType(db.Model):
__tablename__ = 'supp_larceny_type'
larceny_type_id = db.Column(db.BigInteger, primary_key=True)
larceny_type_name = db.Column(db.String(100), nullable=False)
larceny_type_code = db.Column(db.String(20), nullable=False)
larceny_xml_name = db.Column(db.String(100))
class SuppMonth(db.Model):
__tablename__ = 'supp_month'
__table_args__ = (
UniqueConstraint('agency_id', 'data_year', 'month_num', 'data_home'), )
supp_month_id = db.Column(db.BigInteger, primary_key=True)
data_year = db.Column(db.SmallInteger, nullable=False)
month_num = db.Column(db.SmallInteger, nullable=False)
data_home = db.Column(db.String(1), nullable=False)
source_flag = db.Column(db.String(1))
reported_flag = db.Column(db.String(1), nullable=False)
report_date = db.Column(db.DateTime(True))
prepared_date = db.Column(db.DateTime(True))
ddocname = db.Column(db.String(100))
orig_format = db.Column(db.String(1), nullable=False)
mv_stolen_local_rec_local = db.Column(db.BigInteger)
mv_stolen_local_rec_other = db.Column(db.BigInteger)
mv_tot_local_stolen_rec = db.Column(db.BigInteger)
mv_stolen_other_rec_local = db.Column(db.BigInteger)
mv_stolen_status = db.Column(db.SmallInteger)
update_flag = db.Column(db.String(1))
did = db.Column(db.BigInteger)
ff_line_number = db.Column(db.BigInteger)
agency_id = db.Column(db.Integer,
db.ForeignKey('ref_agency.agency_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
agency = db.relationship('RefAgency')
class SuppOffense(db.Model):
__tablename__ = 'supp_offense'
offense_id = db.Column(db.BigInteger, primary_key=True)
offense_name = db.Column(db.String(100), nullable=False)
offense_code = db.Column(db.String(20), nullable=False)
class SuppOffenseSubcat(db.Model):
__tablename__ = 'supp_offense_subcat'
offense_subcat_id = db.Column(db.BigInteger, primary_key=True)
offense_subcat_name = db.Column(db.String(100), nullable=False)
offense_subcat_code = db.Column(db.String(20), nullable=False)
offense_subcat_xml_name = db.Column(db.String(100))
offense_id = db.Column(db.Integer,
db.ForeignKey('supp_offense.offense_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
offense = db.relationship('SuppOffense')
class SuppPropByOffenseSubcat(db.Model):
__tablename__ = 'supp_prop_by_offense_subcat'
__table_args__ = (UniqueConstraint('supp_month_id', 'offense_subcat_id'), )
id = db.Column(
db.Integer,
primary_key=True,
server_default=text(
"nextval('supp_prop_by_offense_subcat_id_seq'::regclass)"))
actual_count = db.Column(db.Integer)
actual_status = db.Column(db.SmallInteger)
stolen_value = db.Column(db.BigInteger)
stolen_value_status = db.Column(db.SmallInteger)
offense_subcat_id = db.Column(db.Integer,
db.ForeignKey(
'supp_offense_subcat.offense_subcat_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
supp_month_id = db.Column(db.Integer,
db.ForeignKey('supp_month.supp_month_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
offense_subcat = db.relationship('SuppOffenseSubcat')
supp_month = db.relationship('SuppMonth')
class SuppPropertyByTypeValue(db.Model):
__tablename__ = 'supp_property_by_type_value'
__table_args__ = (UniqueConstraint('prop_type_id', 'supp_month_id'), )
id = db.Column(
db.Integer,
primary_key=True,
server_default=text(
"nextval('supp_property_by_type_value_id_seq'::regclass)"))
stolen_value = db.Column(db.BigInteger)
stolen_value_status = db.Column(db.SmallInteger)
recovered_value = db.Column(db.BigInteger)
recovered_value_status = db.Column(db.SmallInteger)
prop_type_id = db.Column(db.Integer,
db.ForeignKey('supp_property_type.prop_type_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
supp_month_id = db.Column(db.Integer,
db.ForeignKey('supp_month.supp_month_id',
deferrable=True,
initially='DEFERRED'),
nullable=False,
index=True)
prop_type = db.relationship('SuppPropertyType')
supp_month = db.relationship('SuppMonth')
class SuppPropertyType(db.Model):
__tablename__ = 'supp_property_type'
prop_type_id = db.Column(db.BigInteger, primary_key=True)
prop_type_name = db.Column(db.String(100), nullable=False)
prop_type_code = db.Column(db.String(20), nullable=False)
prop_type_code_num = db.Column(db.SmallInteger, nullable=False)