class CMDR(object):
'''
Created, Modified, Deleted, Replication.
'''
created = db.Column(db.DateTime(), default=datetime.utcnow, nullable=False)
modified = db.Column(db.DateTime(), default=None, onupdate=datetime.utcnow)
deleted = db.Column(db.DateTime(), default=None, onupdate=datetime.utcnow)
class User(CommonModel, db.Model):
'''Users, many are present in the database.'''
__tablename__ = 'users'
user_id = db.Column(UUID, primary_key=True, default=new_uuid)
# email, telephone, whatever
credentials = db.Column(JSONB, nullable=False)
secrets = db.Column(Text) # pw hash
# app personal settings
settings = db.Column(JSONB, nullable=False)
# public stuff: nickname, social links, etc.
social = db.Column(JSONB)
PUBLIC = (user_id, credentials, settings, social)
def __init__(self, credentials, secrets, settings=None, social=None):
self.credentials = credentials
self.secrets = secrets
self.settings = settings or {}
self.social = social or {}
@classmethod
def find_by_credentials(cls, credentials, secrets):
query = cls.query.filter(
text('credentials = :credentials and secrets = :secrets'))
query = query.params(credentials=PGJson(credentials), secrets=secrets)
return query.order_by(cls.created).first()
class Summary(CommonModel, db.Model):
'''Results of calculations over observations'''
__tablename__ = 'summaries'
summary_id = db.Column(UUID, primary_key=True, default=new_uuid)
# metadata: description, etc.
properties = db.Column(JSONB, nullable=False)
geometry = db.Column(Geometry('POLYGON'), nullable=False)
observations = relationship('Observation', secondary=observation_summary)
def __init__(self, properties, geometry, observations=None):
self.properties = properties
self.geometry = geometry
self.observations = observations or []
def as_public_dict(self):
return {
'created': self.created.isoformat(),
'summary_id': self.summary_id,
'properties': self.properties,
'geometry': repr(self.geometry),
}
def __repr__(self):
return '<Summary %r>' % self.summary_id
class Session(CMDR, db.Model):
'''
Sessions
'''
session_id = db.Column(UUID, primary_key=True)
expires = db.Column(db.DateTime(), default=datetime.utcnow, nullable=False)
user_id = db.Column(UUID)
tokens = db.Column(JSONB) # Related tokens (i.e. FCM, bla ba)
def __init__(self):
pass
class User(CMDR, db.Model):
'''
Users, many are present in the database.
'''
user_id = db.Column(UUID, primary_key=True)
credentials = db.Column(JSONB) # password, email, whatever
settings = db.Column(JSONB) # app personal settings
social = db.Column(JSONB) # public stuff: nick name, social links, etc.
def __init__(self):
pass
class Species(CommonModel, db.Model):
'''Species table (maps species to labels)'''
__tablename__ = 'species'
species_id = db.Column(UUID, primary_key=True, default=new_uuid)
# scientific, common, etc
names = db.Column(JSONB, nullable=False)
# label bingo: vision, user, etc
labels = db.Column(JSONB, nullable=False)
PUBLIC = (species_id, names, labels)
def __init__(self, names, labels):
self.names = names
self.labels = labels
class Session(CommonModel, db.Model, DeletableMixin):
'''User sessions'''
__tablename__ = 'sessions'
session_id = db.Column(UUID, primary_key=True, default=new_uuid)
expires = db.Column(db.DateTime(), default=datetime.utcnow, nullable=False)
user_id = db.Column(UUID, ForeignKey('users.user_id'))
# Related tokens (i.e. pubnub_channel, FCM, etc)
tokens = db.Column(JSONB)
user = relationship('User')
def __init__(self, user, tokens=None):
self.user_id = user.user_id
self.user = user
self.tokens = tokens or {}
class Summary(CommonModel, db.Model):
'''Results of calculations over observations'''
__tablename__ = 'summaries'
summary_id = db.Column(UUID, primary_key=True, default=new_uuid)
# metadata: description, etc.
properties = db.Column(JSONB, nullable=False)
geometry = db.Column(Geometry('POLYGON'), nullable=False)
observations = relationship('Observation', secondary=observation_summary)
PUBLIC = (summary_id, properties, geometry)
def __init__(self, properties, geometry, observations=None):
self.properties = properties
self.geometry = geometry
self.observations = observations or []
def modified(cls):
return db.Column(
db.DateTime(),
nullable=False,
server_default=text("(now() at time zone 'utc')"),
onupdate=text("(now() at time zone 'utc')"),
)
class Observation(CMDR, db.Model):
'''
An observation by a user.
'''
observation_id = db.Column(UUID, primary_key=True)
user_id = db.Column(UUID)
position = db.Column(Geometry('POINT'), nullable=False)
radius = db.Column(Float, default=1.0, nullable=False)
def __init__(self, user_id, location):
self.id = str(uuid.uuid1())
self.user_id = user_id
self.location = location
def __repr__(self):
return '<Observation %r>' % self.observation_id
class Observation(CommonModel, db.Model):
'''An observation by a user. Timestamped, geostamped, public.'''
__tablename__ = 'observations'
observation_id = db.Column(UUID, primary_key=True, default=new_uuid)
user_id = db.Column(UUID, ForeignKey('users.user_id'))
geometry = db.Column(Geometry('POLYGON'), nullable=False)
# photography information: url, license, etc
media = db.Column(JSONB, nullable=False)
# meta/descriptive properties: vision labels, user labels, description
properties = db.Column(JSONB, nullable=False)
species_id = db.Column(UUID, ForeignKey('species.species_id'))
user = relationship('User')
species = relationship('Species')
geometry_center = column_property(
geometry.ST_Centroid().ST_AsGeoJSON().cast(JSONB))
PUBLIC = (observation_id, geometry, media, properties, species, user)
def __init__(self, user, geometry, media, properties=None, species=None):
self.user_id = user.user_id if user is not None else None
self.user = user
self.geometry = geometry
self.media = media
self.properties = properties or {}
self.species_id = species.species_id if species else None
self.species = species
class Species(CommonModel, db.Model):
'''Species table (maps species to labels)'''
__tablename__ = 'species'
species_id = db.Column(UUID, primary_key=True, default=new_uuid)
# scientific, common, etc
names = db.Column(JSONB, nullable=False)
# label bingo: vision, user, etc
labels = db.Column(JSONB, nullable=False)
def __init__(self, names, labels):
self.names = names
self.labels = labels
def as_public_dict(self):
return {
'species_id': self.species_id,
'names': self.names,
'labels': self.labels,
}
def __repr__(self):
return '<Species %r>' % self.observation_id
class Session(CommonModel, db.Model):
'''User sessions'''
__tablename__ = 'sessions'
session_id = db.Column(UUID, primary_key=True, default=new_uuid)
expires = db.Column(
db.DateTime(),
default=datetime.utcnow,
nullable=False
)
user_id = db.Column(UUID, ForeignKey('users.user_id'))
# Related tokens (i.e. pubnub_channel, FCM, etc)
tokens = db.Column(JSONB)
user = relationship('User')
def __init__(self, user, tokens=None):
self.user_id = user.user_id
self.user = user
self.tokens = tokens or {}
def as_public_dict(self):
return {
'session_id': self.session_id,
'expires': self.expires,
'user': self.user.as_public_dict()
}
def __repr__(self):
return '<Session %r>' % self.session_id
@classmethod
def delete(cls, session_id):
return cls.query.filter(
cls.session_id == str(session_id)).delete()
@classmethod
def find_by_id(cls, session_id):
query = cls.query.filter(cls.session_id == str(session_id))
return query.order_by(cls.created).first()
class Observation(CommonModel, db.Model):
'''An observation by a user. Timestamped, geostamped, public.'''
__tablename__ = 'observations'
observation_id = db.Column(UUID, primary_key=True, default=new_uuid)
user_id = db.Column(UUID, ForeignKey('users.user_id'))
geometry = db.Column(Geometry('POLYGON'), nullable=False)
# photography information: url, license, etc
media = db.Column(JSONB, nullable=False)
# meta/descriptive properties: vision labels, user labels, description
properties = db.Column(JSONB, nullable=False)
species_id = db.Column(UUID, ForeignKey('species.species_id'))
user = relationship('User')
species = relationship('Species')
geometry_center = column_property(
geometry.ST_Centroid().ST_AsGeoJSON().cast(JSONB))
def __init__(self, user, geometry, media, properties=None, species=None):
self.user_id = user.user_id if user is not None else None
self.user = user
self.geometry = geometry
self.media = media
self.properties = properties or {}
self.species_id = species.species_id if species else None
self.species = species
def as_public_dict(self):
return {
'created': self.created.isoformat(),
'observation_id': self.observation_id,
'geometry': repr(self.geometry),
'media': self.media,
'properties': self.properties,
'species': self.species.as_public_dict() if self.species else None,
'author': self.user.social if self.user is not None else {
'nickname': 'Unknown'},
}
def __repr__(self):
return '<Observation %r>' % self.observation_id
@classmethod
def find_by_id(cls, observation_id):
query = cls.query.filter(cls.observation_id == str(observation_id))
return query.order_by(cls.created).first()
def deleted(cls):
return db.Column(
db.DateTime(),
server_default=text('NULL'),
)
