class User(BaseTable):
__tablename__ = "users"
id = db.Column(db.BigInteger, primary_key=True, autoincrement=True)
username = db.Column(db.String(50), nullable=False)
email = db.Column(db.EmailType, nullable=False)
is_active = db.Column(db.Boolean(), default=True)
is_superuser = db.Column(db.Boolean(), default=False)
first_name = db.Column(db.String(255))
last_name = db.Column(db.String(255))
phone_number = db.Column(db.Unicode(20))
country_code = db.Column(db.Unicode(20))
hashed_password = db.Column(db.String(), nullable=False)
uq_username = db.UniqueConstraint("username")
uq_email = db.UniqueConstraint("email")
ix_username = db.Index(f"ix_{__tablename__}_username", "username")
ix_email = db.Index(f"ix_{__tablename__}_email", "email")
oauth2_clients = relationship("OAuth2Client", back_populates="owner_id")
@classmethod
async def get_by_email(cls, email: str) -> Optional[User]:
return await cls.query.where(User.email == email).gino.first()
@classmethod
async def get_by_username(cls, username: str) -> Optional[User]:
return await cls.query.where(User.username == username).gino.first()
@classmethod
async def get_by_email_or_username(cls,
email_or_username: str,
prefer: str = None) -> Optional[User]:
if prefer == "email":
result = await cls.get_by_email(email_or_username
) or await cls.get_by_username(
email_or_username)
else:
result = await cls.get_by_username(
email_or_username) or await cls.get_by_email(email_or_username)
return result
@classmethod
async def authenticate(cls,
email_or_username: str,
password: str,
prefer: str = None) -> Optional[User]:
user = await cls.get_by_email_or_username(email_or_username,
prefer=prefer)
if not user:
return None
if not verify_password(password, user.hashed_password):
return None
return user
@classmethod
def create(cls, **values):
password: Optional[str] = values.pop("password", None)
if password:
values["hashed_password"] = get_password_hash(password)
return super().create(**values)
class WellStat(WellBase):
__tablename__ = "wellstats"
name = db.Column(db.String(50), primary_key=True)
type = db.Column(db.String(25), nullable=False) # numeric, string, date
numeric_value = db.Column(db.Numeric(19, 2))
string_value = db.Column(db.Numeric(19, 2))
date_value = db.Column(db.Numeric(19, 2))
comments = db.Column(db.JSONB(), nullable=False, server_default="{}")
class RuntimeStat(Base):
__tablename__ = "runtime_stats"
id = db.Column(db.BigInteger(), primary_key=True)
hole_direction = db.Column(db.String(25))
data_type = db.Column(db.String(25))
operation = db.Column(db.String(50))
name = db.Column(db.String(50))
time = db.Column(db.Numeric(19, 2))
count = db.Column(db.Integer())
class FracParameters(WellBase):
__tablename__ = "frac_parameters"
fluid_bbl = db.Column(db.Integer())
proppant_lb = db.Column(db.Integer())
fluid_bbl_ft = db.Column(db.Integer())
proppant_lb_ft = db.Column(db.Integer())
lateral_length = db.Column(db.Integer())
lateral_length_type = db.Column(db.String(25))
gen = db.Column(db.Integer())
gen_name = db.Column(db.String(10))
provider = db.Column(db.String())
provider_last_update_at = db.Column(db.DateTime(timezone=True))
class TestModel(BaseTable):
__tablename__ = "test"
id = db.Column(db.BigInteger, primary_key=True, autoincrement=True)
username = db.Column(db.String(50), nullable=False)
email = db.Column(db.EmailType, nullable=False)
is_active = db.Column(db.Boolean(), default=True)
is_superuser = db.Column(db.Boolean(), default=False)
first_name = db.Column(db.String(255))
last_name = db.Column(db.String(255))
phone_number = db.Column(db.Unicode(20))
country_code = db.Column(db.Unicode(20))
hashed_password = db.Column(db.String())
uq_username = db.UniqueConstraint("username")
uq_email = db.UniqueConstraint("email")
ix_username = db.Index("ix_test_username", "username")
ix_email = db.Index("ix_test_email", "email")
class WellDepth(WellBase):
__tablename__ = "depths"
name = db.Column(db.String(50), index=True, primary_key=True)
value = db.Column(db.Integer())
property_name = db.Column(db.String(50), index=True)
aggregate_type = db.Column(db.String(25), index=True)
grid_id = db.Column(db.Integer(), index=True)
formation = db.Column(db.String(50), index=True)
into_formation_feet = db.Column(db.Integer())
into_formation_percent = db.Column(db.Float())
above_next_formation_feet = db.Column(db.Integer())
above_next_formation_percent = db.Column(db.Float())
overlap_feet = db.Column(db.Integer())
overlap_percent = db.Column(db.Float())
in_target = db.Column(db.Boolean())
assignment_method = db.Column(db.String()) # TODO: enum
class KnownEntity(Base):
__tablename__ = "known_entities"
entity_id = db.Column(db.String(100),
primary_key=True) # ex. 42461409160000
entity_type = db.Column(db.String(100), primary_key=True) # ex. api14
ihs_last_seen_at = db.Column(db.DateTime(timezone=True))
enverus_last_seen_at = db.Column(db.DateTime(timezone=True))
fracfocus_last_seen_at = db.Column(db.DateTime(timezone=True))
ix_known_entities_all = db.Index( # to avoid heap access
"ix_known_entities_all",
"entity_type",
"entity_id",
"ihs_last_seen_at",
"enverus_last_seen_at",
"fracfocus_last_seen_at",
)
class Area(Base):
__tablename__ = "areas"
id = db.Column(db.Integer(), primary_key=True, autoincrement=True)
area = db.Column(db.String(25), unique=True, nullable=False)
h_last_run_at = db.Column(db.DateTime(timezone=True))
v_last_run_at = db.Column(db.DateTime(timezone=True))
providers = db.Column(db.JSONB(), nullable=False, server_default="[]")
# entity_type = db.Column(
# db.ChoiceType(EntityType, impl=db.String()), primary_key=True
# )
# hole_direction = db.Column(
# db.ChoiceType(HoleDirection, impl=db.String()), primary_key=True
# )
@classmethod
def _is_ready(cls, last_run_at: Optional[datetime], cooldown_hours: int):
if last_run_at:
utcnow = datetime.now().astimezone(pytz.utc)
threshold = utcnow - timedelta(hours=cooldown_hours)
return last_run_at < threshold
else:
return True
@classmethod
async def next_available(
cls, hole_dir: HoleDirection) -> Tuple[Area, str, bool, int]:
""" Get the properties describing the next available execution time of
the given hole direction and entity type """
if hole_dir == HoleDirection.H:
cooldown = conf.PRODSTATS_H_COOLDOWN
elif hole_dir == HoleDirection.V:
cooldown = conf.PRODSTATS_V_COOLDOWN
else:
cooldown = ONE_WEEK
attr = f"{hole_dir.value.lower()}_last_run_at"
area_obj = await cls.query.order_by( # get stalest area for given hole_dir
getattr(cls, attr).asc().nullsfirst()).gino.first()
is_ready = cls._is_ready(getattr(area_obj, attr), cooldown)
logger.info(
f"({cls.__name__}[{hole_dir}]) next available: {area_obj.area} {is_ready=}" # noqa
)
return area_obj, attr, is_ready, cooldown
@classmethod
async def df(cls) -> pd.DataFrame:
records = await cls.query.gino.all()
return pd.DataFrame([x.to_dict() for x in records],
columns=cls.c.names).set_index("area")
class ProdStat(Base):
__tablename__ = "prodstats"
api10 = db.Column(db.String(10), primary_key=True)
name = db.Column(db.String(50), primary_key=True, index=True)
value = db.Column(db.Numeric(19, 2))
property_name = db.Column(db.String(50), index=True)
aggregate_type = db.Column(db.String(25), index=True)
is_peak_norm = db.Column(db.Boolean())
is_ll_norm = db.Column(db.Boolean())
ll_norm_value = db.Column(db.Integer())
includes_zeroes = db.Column(db.Boolean())
start_date = db.Column(db.Date())
end_date = db.Column(db.Date())
start_month = db.Column(db.Integer())
end_month = db.Column(db.Integer())
comments = db.Column(db.JSONB(), nullable=False, server_default="{}")
ix_prodstat_api10_prop_agg = db.Index("ix_prodstat_api10_prop_agg",
"api10", "property_name",
"aggregate_type")
class ProdHeader(Base):
__tablename__ = "production_header"
api10 = db.Column(db.String(10), primary_key=True)
primary_api14 = db.Column(db.String(14), index=True)
entity12 = db.Column(db.String(12), nullable=False)
status = db.Column(db.String())
first_prod_date = db.Column(db.Date())
last_prod_date = db.Column(db.Date())
prod_months = db.Column(db.Integer())
prod_days = db.Column(db.Integer())
peak_norm_months = db.Column(db.Integer())
peak_norm_days = db.Column(db.Integer())
peak30_oil = db.Column(db.Integer())
peak30_gas = db.Column(db.Integer())
peak30_date = db.Column(db.Date())
peak30_month = db.Column(db.Integer())
perfll = db.Column(db.Integer())
perf_upper = db.Column(db.Integer())
perf_lower = db.Column(db.Integer())
oil_pdp_last3mo_per30kbbl = db.Column(db.Integer())
boe_pdp_last3mo_per30kbbl = db.Column(db.Integer())
products = db.Column(db.String())
provider = db.Column(db.String())
provider_last_update_at = db.Column(db.DateTime(timezone=True))
related_well_count = db.Column(db.Integer())
related_wells = db.Column(db.JSONB(), nullable=False, server_default="[]")
comments = db.Column(db.JSONB(), nullable=False, server_default="{}")
class OAuth2Client(BaseTable):
__tablename__ = "oauth2_clients"
id = db.Column(db.BigInteger, primary_key=True, autoincrement=True)
client_id = db.Column(db.String(50))
hashed_client_secret = db.Column(db.String(150))
owner_id = db.Column(db.BigInteger, db.ForeignKey("users.id"))
owner = relationship("User", back_populates="oauth2_clients")
ix_owner_id = db.Index(f"ix_{__tablename__}_owner_id", "owner_id")
ix_client_id = db.Index(f"ix_{__tablename__}_client_id", "client_id")
uq_client_id = db.UniqueConstraint("client_id")
@classmethod
async def get_by_client_id(cls, client_id: str) -> Optional[OAuth2Client]:
return await cls.query.where(cls.client_id == client_id).gino.first()
@classmethod
async def get_by_owner(cls, owner_id: int) -> List[OAuth2Client]:
return await cls.query.where(cls.owner_id == owner_id).gino.all()
@classmethod
async def authenticate(cls, client_id: str,
client_secret: str) -> Optional[User]:
client = (await OAuth2Client.load(
owner=User.on(OAuth2Client.owner_id == User.id)
).where(OAuth2Client.client_id == client_id).gino.one_or_none())
if not client:
return None
if not verify_password(client_secret, client.hashed_client_secret):
return None
return client.owner
@classmethod
def create(cls, **values):
secret: Optional[str] = values.pop("client_secret", None)
if secret:
values["hashed_client_secret"] = get_password_hash(secret)
return super().create(**values)
class Survey(WellBase):
__tablename__ = "surveys"
survey_type = db.Column(db.String(50))
survey_method = db.Column(db.String(50))
survey_date = db.Column(db.Date())
survey_top = db.Column(db.Integer())
survey_top_uom = db.Column(db.String(10))
survey_base = db.Column(db.Integer())
survey_base_uom = db.Column(db.String(10))
# NOTE: alembic doesn't play nicely with spatial created by geoalchemy2. It
# will autogenerate them, then delete them in the next migration. The
# workaround here is to disable the automatic index creation by
# geoalchemy2 (spatial_index=False) and explictly add the gist indices
# using sqlalchemy.
wellbore = db.Column(
db.Geometry("LINESTRING", srid=4326, spatial_index=False))
lateral_only = db.Column(
db.Geometry("LINESTRING", srid=4326, spatial_index=False))
stick = db.Column(db.Geometry("LINESTRING", srid=4326,
spatial_index=False))
bent_stick = db.Column(
db.Geometry("LINESTRING", srid=4326, spatial_index=False))
ix_survey_wellbore = db.Index("ix_survey_wellbore",
"wellbore",
postgresql_using="gist")
ix_survey_lateral_only = db.Index("ix_survey_lateral_only",
"lateral_only",
postgresql_using="gist")
ix_survey_stick = db.Index("ix_survey_stick",
"stick",
postgresql_using="gist")
ix_survey_bent_stick = db.Index("ix_survey_bent_stick",
"bent_stick",
postgresql_using="gist")
class ProdMonthly(Base):
__tablename__ = "production_monthly"
api10 = db.Column(db.String(10), primary_key=True)
prod_date = db.Column(db.Date(), primary_key=True)
prod_month = db.Column(db.Integer())
days_in_month = db.Column(db.Integer())
prod_days = db.Column(db.Integer())
peak_norm_month = db.Column(db.Integer())
peak_norm_days = db.Column(db.Integer()) # currently prod_day
oil = db.Column(db.Integer())
gas = db.Column(db.Integer())
water = db.Column(db.Integer())
boe = db.Column(db.Integer())
water_cut = db.Column(db.Numeric(19, 2))
oil_percent = db.Column(db.Numeric(19, 2))
gor = db.Column(db.Integer())
oil_per1k = db.Column(db.Integer())
gas_per1k = db.Column(db.Integer())
water_per1k = db.Column(db.Integer())
boe_per1k = db.Column(db.Integer())
gas_per3k = db.Column(db.Integer())
oil_per3k = db.Column(db.Integer())
water_per3k = db.Column(db.Integer())
boe_per3k = db.Column(db.Integer())
gas_per5k = db.Column(db.Integer())
oil_per5k = db.Column(db.Integer())
water_per5k = db.Column(db.Integer())
boe_per5k = db.Column(db.Integer())
gas_per7500 = db.Column(db.Integer())
oil_per7500 = db.Column(db.Integer())
water_per7500 = db.Column(db.Integer())
boe_per7500 = db.Column(db.Integer())
gas_per10k = db.Column(db.Integer())
oil_per10k = db.Column(db.Integer())
water_per10k = db.Column(db.Integer())
boe_per10k = db.Column(db.Integer())
oil_avg_daily = db.Column(db.Integer())
gas_avg_daily = db.Column(db.Numeric(19, 2))
water_avg_daily = db.Column(db.Numeric(19, 2))
boe_avg_daily = db.Column(db.Numeric(19, 2))
comments = db.Column(db.JSONB(), nullable=False, server_default="{}")
class WellLocation(WellBase):
__tablename__ = "well_locations"
name = db.Column(db.String(50), index=True, primary_key=True)
block = db.Column(db.String(50))
section = db.Column(db.String(50))
abstract = db.Column(db.String(50))
survey = db.Column(db.String(50))
metes_bounds = db.Column(db.String(50))
lon = db.Column(db.Float())
lat = db.Column(db.Float())
geom = db.Column(db.Geometry("POINT", srid=4326, spatial_index=False))
ix_well_location_geom = db.Index("ix_well_location_geom",
"geom",
postgresql_using="gist")
class WellBase(Base):
api14 = db.Column(db.String(14), index=True, primary_key=True)
class WellLink(WellBase):
__tablename__ = "well_links"
name = db.Column(db.String(50), index=True, primary_key=True)
value = db.Column(db.String())
class IPTest(WellBase):
__tablename__ = "ip_tests"
test_number = db.Column(db.Integer(), primary_key=True)
test_date = db.Column(db.Date(), index=True)
type_code = db.Column(db.String(10))
test_method = db.Column(db.String())
completion = db.Column(db.Integer())
oil = db.Column(db.Integer())
oil_per10k = db.Column(db.Integer())
oil_uom = db.Column(db.String(10))
gas = db.Column(db.Integer())
gas_per10k = db.Column(db.Integer())
gas_uom = db.Column(db.String(10))
water = db.Column(db.Integer())
water_per10k = db.Column(db.Integer())
water_uom = db.Column(db.String(10))
choke = db.Column(db.String(25))
depth_top = db.Column(db.Integer())
depth_top_uom = db.Column(db.String(10))
depth_base = db.Column(db.Integer())
depth_base_uom = db.Column(db.String(10))
sulfur = db.Column(db.Boolean())
oil_gravity = db.Column(db.Float())
oil_gravity_uom = db.Column(db.String(10))
gor = db.Column(db.Integer())
gor_uom = db.Column(db.String(10))
perf_upper = db.Column(db.Integer())
perf_upper_uom = db.Column(db.String(10))
perf_lower = db.Column(db.Integer())
perf_lower_uom = db.Column(db.String(10))
perfll = db.Column(db.Integer())
perfll_uom = db.Column(db.String(10))
class TestModel(Base):
__tablename__ = "test_model"
id = db.Column(db.Integer(), index=True, primary_key=True)
name = db.Column(db.String())
class WellHeader(WellBase):
__tablename__ = "wells"
api10 = db.Column(db.String(10), index=True)
well_name = db.Column(db.String())
hole_direction = db.Column(db.String(1))
status = db.Column(db.String(50))
is_producing = db.Column(db.Boolean(), index=True)
operator = db.Column(db.String(), index=True)
operator_alias = db.Column(db.String(), index=True)
hist_operator = db.Column(db.String(), index=True)
hist_operator_alias = db.Column(db.String(), index=True)
tvd = db.Column(db.Integer())
md = db.Column(db.Integer())
perfll = db.Column(db.Integer())
lateral_length = db.Column(db.Integer())
ground_elev = db.Column(db.Integer())
kb_elev = db.Column(db.Integer())
comp_date = db.Column(db.Date())
spud_date = db.Column(db.Date())
permit_date = db.Column(db.Date())
permit_number = db.Column(db.String())
permit_status = db.Column(db.String())
rig_release_date = db.Column(db.Date()) # rr_date
last_activity_date = db.Column(db.Date())
basin = db.Column(db.String(50), index=True) # basin
sub_basin = db.Column(db.String(50), index=True) # basin
state = db.Column(db.String(50)) # state_name
state_code = db.Column(db.String(10)) # state_name
county = db.Column(db.String(50), index=True) # county_name
county_code = db.Column(db.String(10)) # county_name
provider_status = db.Column(db.String(50))
provider = db.Column(db.String())
provider_last_update_at = db.Column(db.DateTime(timezone=True))
basin_holedir_isprod_idx = db.Index("ix_well_basin_holedir_isprod",
"basin", "hole_direction",
"is_producing")
basin_status_idx = db.Index("ix_well_basin_status", "basin", "status")