class TopicMeta(Base):
"""Model class that provides topic metadata. This model holds data that
are related to internal workings of the topic model that are not part of
the versionable records.
"""
__tablename__ = "topic_meta"
topic_id = Column(
Integer,
ForeignKey("topic.id"),
nullable=False,
primary_key=True,
autoincrement=False,
)
post_count = Column(Integer, nullable=False)
posted_at = Column(DateTime(timezone=True))
bumped_at = Column(DateTime(timezone=True))
topic = relationship("Topic",
backref=backref("meta",
uselist=False,
cascade="all,delete",
lazy=True))
class Post(Versioned, Base):
"""Model class for posts. Each content in a :class:`Topic` and metadata
regarding its poster are stored here. It has :attr:`number` which is a
sequential number specifying its position within :class:`Topic`.
"""
__tablename__ = 'post'
__table_args__ = (UniqueConstraint('topic_id', 'number'), )
id = Column(Integer, primary_key=True)
created_at = Column(DateTime(timezone=True), default=func.now())
updated_at = Column(DateTime(timezone=True), onupdate=func.now())
topic_id = Column(Integer, ForeignKey('topic.id'), nullable=False)
ip_address = Column(String, nullable=False)
ident = Column(String(32), nullable=True)
number = Column(Integer, nullable=False)
name = Column(String, nullable=False)
body = Column(Text, nullable=False)
bumped = Column(Boolean, nullable=False, index=True, default=True)
topic = relationship('Topic',
backref=backref('posts',
lazy='dynamic',
cascade='all,delete',
order_by='Post.number'))
class Rule(Base):
"""Model class that provides an IP rule."""
__tablename__ = 'rule'
id = Column(Integer, primary_key=True)
type = Column(String, nullable=False)
scope = Column(String)
created_at = Column(DateTime(timezone=True), default=func.now())
updated_at = Column(DateTime(timezone=True), onupdate=func.now())
ip_address = Column(INET, nullable=False)
active = Column(Boolean, nullable=False, default=True)
active_until = Column(DateTime(timezone=True))
description = Column(Unicode)
__mapper_args__ = {
'polymorphic_on': type,
'polymorphic_identity': 'base',
}
@classmethod
def listed(cls, ip_address, scopes=None):
scope_q = cls.scope == None
if scopes is not None:
scope_q = or_(scope_q, cls.scope.in_(scopes))
return and_(
scope_q, cls.active == True,
cls.ip_address.op('>>=')(ip_address),
or_(cls.active_until == None, cls.active_until >= func.now()))
class Board(Versioned, Base):
"""Model class for board. This model serve as a category to topic and
also holds settings regarding how posts are created and displayed. It
should always be accessed using :attr:`slug`.
"""
__tablename__ = "board"
id = Column(Integer, primary_key=True)
created_at = Column(DateTime(timezone=True), default=func.now())
updated_at = Column(DateTime(timezone=True), onupdate=func.now())
slug = Column(String(64), unique=True, nullable=False)
title = Column(Unicode(255), nullable=False)
_settings = Column("settings", JSON, nullable=False, default={})
agreements = Column(Text, nullable=True)
description = Column(Text, nullable=True)
status = Column(BoardStatusEnum, default="open", nullable=False)
def get_settings(self):
if self._settings is None:
return DEFAULT_BOARD_CONFIG
settings = DEFAULT_BOARD_CONFIG.copy()
settings.update(self._settings)
return settings
def set_settings(self, value):
self._settings = value
@declared_attr
def settings(self):
return synonym("_settings",
descriptor=property(self.get_settings,
self.set_settings))
class Ban(Base):
"""Model class that provides an IP ban."""
__tablename__ = "ban"
id = Column(Integer, primary_key=True)
scope = Column(String)
created_at = Column(DateTime(timezone=True), default=func.now())
updated_at = Column(DateTime(timezone=True), onupdate=func.now())
ip_address = Column(INET, nullable=False)
active = Column(Boolean, nullable=False, default=True)
active_until = Column(DateTime(timezone=True))
description = Column(Unicode)
@classmethod
def listed(cls, ip_address, scopes=None):
scope_q = cls.scope == None # noqa: E712
if scopes is not None:
scope_q = or_(scope_q, cls.scope.in_(scopes))
return and_(
scope_q,
cls.active == True, # noqa: E712
cls.ip_address.op(">>=")(ip_address),
or_(cls.active_until == None,
cls.active_until >= func.now()), # noqa: E712
)
@property
def duration(self):
"""Returns the duration of this ban in days."""
if not self.active_until:
return 0
secs = (self.active_until - self.created_at).total_seconds()
return round(secs / 86400)
class Banword(Base):
"""Model class for banwords."""
__tablename__ = "banword"
id = Column(Integer, primary_key=True)
created_at = Column(DateTime(timezone=True), default=func.now())
updated_at = Column(DateTime(timezone=True), onupdate=func.now())
expr = Column(String, nullable=False)
description = Column(String, nullable=True)
active = Column(Boolean, nullable=False, default=True)
class User(Base):
"""Model class that provides a user."""
__tablename__ = "user"
__table_args__ = (UniqueConstraint("username"), )
id = Column(Integer, primary_key=True)
created_at = Column(DateTime(timezone=True), default=func.now())
parent_id = Column(Integer, ForeignKey("user.id"))
username = Column(String, nullable=False)
encrypted_password = Column(String, nullable=False)
deactivated = Column(Boolean, nullable=False, index=True, default=False)
ident = Column(String, nullable=False)
ident_type = Column(IdentTypeEnum, default="ident", nullable=False)
name = Column(String, nullable=False)
parent = relationship(
"User",
remote_side=[id],
backref=backref("children",
lazy="dynamic",
cascade="all,delete",
order_by="User.id"),
)
groups = relationship(
"Group",
secondary="user_group",
order_by="Group.name",
backref=backref("users", lazy="dynamic", order_by="User.id"),
)
class DeviceLocationHistory(Base):
__tablename__ = "device_location_history"
id = Column(Integer, primary_key=True, unique=True)
mac_address = Column(String, unique=True)
coord_x = Column(Numeric, default=0)
coord_y = Column(Numeric, default=0)
last_modified = Column(DateTime(timezone=True))
hierarchy = Column(String, nullable=False)
def __init__(self, mac_address, hierarchy, last_modified, coord_x=0, coord_y=0):
self.mac_address = mac_address
self.hierarchy = hierarchy
self.last_modified = last_modified
self.coord_x = coord_x
self.coord_y = coord_y
def __repr__(self):
return "{} was at {} @ {}".format(self.mac_address, self.hierarchy, self.last_modified)
def serialize(self):
item = {
'mac_address': self.mac_address,
'hierarchy': self.hierarchy,
'coord_x': str(self.coord_x),
'coord_y': str(self.coord_y),
'last_modified': str(self.last_modified)
}
return item
def date_updated(self):
return Column(
DateTime(True),
nullable=False,
default=lambda: datetime.now(tz=current_app.config["TZ"]),
comment="timestamp for last updated",
)
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False, unique=True)
email = Column(String, nullable=False)
password = Column(String, nullable=False)
created_on = Column(DateTime(), default=datetime.now)
updated_on = Column(DateTime(),
default=datetime.now,
onupdate=datetime.now)
posts = relationship("Post", back_populates="user")
def __repr__(self):
return (f"<User(name='{self.name}', email='{self.email}',"
f" password='******')>")
class User(DeclarativeBase):
__tablename__ = 'user'
__table_args__ = {'useexisting': True}
id = Column(Integer, primary_key=True)
name = Column(String(255), nullable=False)
email = Column(String(255), nullable=False)
created = Column(DateTime(), nullable=False)
class Page(Versioned, Base):
"""Model class for pages. This model is a basis for user-accessible
content that are not part of the board itself, including individual pages,
custom CSS or board guidelines.
"""
__tablename__ = "page"
__table_args__ = (UniqueConstraint("namespace", "slug"), )
id = Column(Integer, primary_key=True)
created_at = Column(DateTime(timezone=True), default=func.now())
updated_at = Column(DateTime(timezone=True), onupdate=func.now())
namespace = Column(String, nullable=False, default="public")
title = Column(Unicode, nullable=False)
slug = Column(String, nullable=False)
body = Column(Text, nullable=False)
formatter = Column(String, nullable=False, default="markdown")
class Comment(Base):
__tablename__ = 'comments'
id = Column(Integer, nullable=False, primary_key=True, autoincrement=True)
post_id = Column(Integer, ForeignKey('posts.id'), nullable=False)
author = Column(Integer, ForeignKey('users.id'), nullable=False)
date_published = Column(DateTime(timezone=True), default=func.now())
content = Column(VARCHAR, nullable=False)
rating = Column(Integer, ForeignKey('rating.id'), nullable=False)
class Satellite(BASE):
'''
Satellites are the actual hardware that takes images. A satellite is
equipped with a sensor, so we have a foreign key pointing to the Sensor
table. The reason that we have two tables instead of one, is that several
satellites can have the same sensor installed. The canonical example for
this is the RapidEye mission that has five satellites equipped with the
same sensor.
'''
__tablename__ = 'satellite'
pk_id = Column(Integer, primary_key=True)
name = Column(String, unique=True)
short_name = Column(String, unique=True)
launching_date = Column(DateTime())
terminated = Column(DateTime())
organization_id = Column(Integer, ForeignKey('organization.pk_id'))
organization = relationship('Organization')
has_sensor = relationship('Sensor', secondary=HAS_SENSOR)
class Post(Base):
__tablename__ = 'posts'
id = Column(Integer, primary_key=True)
title = Column(String, nullable=False)
text = Column(Text, nullable=False)
author_id = Column(Integer, ForeignKey('users.id'))
created_on = Column(DateTime(), default=datetime.now)
updated_on = Column(DateTime(),
default=datetime.now,
onupdate=datetime.now)
user = relationship("User", back_populates='posts')
tags = relationship('Tag',
secondary=posts_tags_assoc_table,
back_populates='posts')
def __repr__(self):
return f"<Post(title='{self.title[:10]}')>"
class ProductImage(Base):
__tablename__ = "product_images"
id = Column(Integer, primary_key=True)
image_link = Column(String, nullable=False)
created_at= Column(DateTime(timezone=True), default=func.now(), nullable=False)
# relationship
product_id = Column(Integer, ForeignKey("products.id"))
product = relationship("ProductModel", back_populates="images")
class Group(Base):
__tablename__ = 'groups'
id = Column(UUID(as_uuid=True),
primary_key=True)
updated_timestamp = Column(DateTime(),
server_default=func.now())
def __init__(self,
id: uuid.UUID):
self.id = id
def test_example_data():
with open('test_melon_news.json') as f:
melon_news_data = json.loads(f.read())
melon_news_in_db = []
for melon_news in melon_news_data:
comment = melon_news['comment']
news = melon_news['news']
user = melon_news['user']
category = melon_news['category']
db_melon_news = crud.create_melon_news(comment, news, user, category)
model.Comment.query.delete()
model.News.query.delete()
model.User.query.delete()
model.Category.query.delete()
users_in_db = {}
categories_in_db = {}
news_in_db = {}
for user in melon_news_data['users']:
db_user = crud.create_user(user_name=user['user_name'],
email=user['email'],
user_role=user['user_role'],
password=user['password'])
users_in_db[db_user.email] = db_user
for category in melon_news_data['categories']:
db_category = crud.create_category(
category_type=category['category_type'],
description=category['description'])
categories_in_db[db_category.category_type] = db_category
for news in melon_news_data['news']:
db_news = crud.create_news(
user=users_in_db[news['email']],
category=categories_in_db[news['category_type']],
title=news['title'],
summary=news['summary'],
article_text=news['article_text'],
external_link=news['external_link'],
picture_link=news['picture_link'],
date_post=DateTime(news['date_post'][0], news['date_post'][1],
news['date_post'][2]))
news_in_db[db_news.title] = db_news
for comment in melon_news_data['comments']:
crud.create_comment(user=users_in_db[comment['email']],
news=news_in_db[comment['title']],
comment_text=comment['comment_text'])
return melon_news_in_db
class ErrorLog(BaseModel):
__tablename__ = "error_log"
code = Column(String, comment="Application Error identifier code")
user_id = Column(Integer, ForeignKey("users.id"), comment="logged in user")
user: "******" = relationship("User", foreign_keys=[user_id], uselist=False)
message = Column(String, comment="error message")
stack_trace = Column(String, comment="error stack trace")
date_added = Column(
DateTime(True),
nullable=False,
default=lambda: datetime.now(tz=current_app.config["TZ"]),
comment="row timestamp",
)
added_by_id = Column(INTEGER, ForeignKey("users.id"))
added_by: "User" = relationship("User", foreign_keys=[added_by_id])
session_id = Column(INTEGER, ForeignKey("sessions.id"))
session: "Session" = relationship("Session", foreign_keys=[session_id])
def __init__(self, e: Exception) -> None:
exc_type, exc_value, exc_tb = sys.exc_info()
trace = traceback.format_tb(exc_tb)
trace = list(
filter(
lambda x: ("\\lib\\" not in x and "/lib/" not in x and __name__
not in x),
trace,
))
ex_type = exc_type.__name__
ex_line = exc_tb.tb_lineno
ex_file = exc_tb.tb_frame.f_code.co_filename
ex_message = str(exc_value)
line_code = ""
try:
line_code = getline(ex_file, ex_line).strip()
except Exception:
pass
trace.insert(
0,
f'File "{ex_file}", line {ex_line}, line_code: {line_code} , ex: {ex_type} {ex_message}',
)
trace_str = "\n".join(list(map(str, trace)))
self.code = getattr(e, "code", None)
self.message = getattr(e, "message", getattr(e, "msg", str(e)))
self.stack_trace = trace_str
self.added_by_id = getattr(getattr(g, "identity", None), "id", None)
self.session_id = getattr(getattr(g, "session", None), "id", None)
class ESSyncStatus(Base):
"""A table with a single row that indicates the last time the
ElasticSearch index was updated.
"""
__tablename__ = 'es_sync_status'
last_update = Column(DateTime(timezone=True))
# make sure there is only one row in this table
id = Column(Integer, primary_key=True, default=1)
__table_args__ = (CheckConstraint('id = 1', name='one_row_constraint'), {
'schema': schema
})
class Requests(Base):
__tablename__ = "requests"
id = Column(Integer, primary_key=True, index=True)
origin_ip = Column(String)
origin_port = Column(Integer)
endpoint = Column(String, index=True)
method = Column(String, index=True)
date_created = Column(DateTime(timezone=True), default=func.now())
message = Column(String)
replies_to = Column(Integer, ForeignKey("requests.id"))
replies = relationship("Requests")
phone = Column(String)
class ProductReverse(Base):
metadata = MetaData()
__tablename__ = "productreverse_reg"
__table_args__ = {"useexisting": True}
id = Column('id', BIGINT, primary_key=True)
code = Column("code", BIGINT, nullable=False)
created_by = Column('created_by', BIGINT, nullable=False)
created_date = Column('created_date', BIGINT, nullable=False)
_time = Column("_time",
DateTime(timezone=True),
nullable=True,
onupdate=datetime.datetime.now)
preorder = Column("preorder", BIGINT, nullable=False)
product = Column("product", BIGINT, nullable=False)
reason = Column("reason", Text, nullable=False)
status = Column("status", BIGINT, nullable=False, default=16)
ProductReverse_tbl = Table(__tablename__, metadata, id, code, created_by,
created_date, _time, preorder, reason, status)
def __repr__(self):
return "<ProductReverse (id='%s', code='%s', created_by='%s', " \
"created_date='%s', _time='%s', preorder='%s', reason='%s', status='%s')>" % \
(self.id, self.code, self.created_by,
self.created_date, self._time, self.preorder, self.reason, self.status)
def __Publish__(self):
data = {}
for column in self.__table__.columns.keys():
value = self.__dict__[self.__table__.columns[column].name]
if self.__table__.columns[column].type == "BIGINT":
data[self.__table__.columns[column].name] = int(value)
elif self.__table__.columns[column].type == "Integer":
data[self.__table__.columns[column].name] = int(value)
elif self.__table__.columns[column].type == "NUMERIC":
data[self.__table__.columns[column].name] = float(value)
elif self.__table__.columns[column].type == "Decimal":
data[self.__table__.columns[column].name] = float(value)
elif self.__table__.columns[column].type == "time":
data[self.__table__.columns[column].name] = str(
value.strftime('%H:%M:%S'))
elif self.__table__.columns[column].type == "datetime":
data[self.__table__.columns[column].name] = str(
value.strftime('%H:%M:%S'))
else:
data[self.__table__.columns[column].name] = str(value)
return data
class ESDeletedDocument(Base):
"""A table listing documents that have been deleted and that should be
removed from the ES index.
"""
__tablename__ = 'es_deleted_documents'
document_id = Column(Integer, primary_key=True)
type = Column(String(1))
deleted_at = Column(DateTime(timezone=True),
default=func.now(),
nullable=False,
index=True)
class Product(BASE):
'''
A product is either an input or an output of the system. Once an image is
ingested it becomes a product and can be used to produce new ones. Products
that come from certain process can be ingested as well and can be used as
inputs for new processes.
'''
__tablename__ = 'product'
pk_id = Column(Integer, primary_key=True)
acquisition_date = Column(DateTime())
ingest_date = Column(DateTime())
product_path = Column(String, unique=True)
thumbnail_path = Column(String, unique=True)
legend = Column(Integer, ForeignKey('legend.pk_id'))
information_id = Column(Integer, ForeignKey('information.pk_id'))
information = relationship('Information')
product_type_id = Column(Integer, ForeignKey('product_type.pk_id'))
product_type = relationship("ProductType")
satellite_id = Column(Integer, ForeignKey('satellite.pk_id'))
satellite = relationship('Satellite')
algorithm = Column(Integer, ForeignKey('algorithm.pk_id'))
can_train = relationship('Algorithm', secondary=CAN_TRAIN_TABLE)
license_id = Column(Integer, ForeignKey('license.pk_id'))
license = relationship('License')
'''
input_product = relationship(
'Product',
secondary=PRODUCT_INPUT_TABLE,
primaryjoin=id==PRODUCT_INPUT_TABLE.c.input_product,
secondaryjoin=id==PRODUCT_INPUT_TABLE.c.output_product,
backref="output_product")
'''
type = Column(String(20))
__mapper_args__ = {
'polymorphic_on': type,
'polymorphic_identity': 'product'
}
class Activity(Tuple, DeclarativeBase):
""" Activity
An Activity represents an item in the activity screen
This is a screen that is intended to show actions that have been performed recently
or events that plugins want in this list.
see InboxAbiABC.NewActivity for documentation.
"""
__tupleType__ = inboxTuplePrefix + 'Activity'
__tablename__ = 'Activity'
id = Column(Integer, primary_key=True, autoincrement=True)
pluginName = Column(String, nullable=False)
uniqueId = Column(String, nullable=False)
userId = Column(String, nullable=False)
dateTime = Column(DateTime(True), nullable=False)
# The display properties of the task
title = Column(String, nullable=False)
description = Column(String)
iconPath = Column(String)
# The mobile-app route to open when this task is selected
routePath = Column(String)
routeParamJson = Column(String)
# Auto Delete on Time
autoDeleteDateTime = Column(DateTime(True), nullable=False)
__table_args__ = (Index("idx_Activity_pluginName_uniqueId",
pluginName,
uniqueId,
unique=True), )
class Board(Versioned, Base):
"""Model class for board. This model serve as a category to topic and
also holds settings regarding how posts are created and displayed. It
should always be accessed using :attr:`slug`.
"""
__tablename__ = 'board'
id = Column(Integer, primary_key=True)
created_at = Column(DateTime(timezone=True), default=func.now())
updated_at = Column(DateTime(timezone=True), onupdate=func.now())
slug = Column(String(64), unique=True, nullable=False)
title = Column(Unicode(255), nullable=False)
_settings = Column('settings', JsonType, nullable=False, default={})
agreements = Column(Text, nullable=True)
description = Column(Text, nullable=True)
status = Column(Enum('open',
'restricted',
'locked',
'archived',
name='board_status'),
default='open',
nullable=False)
def get_settings(self):
settings = DEFAULT_BOARD_CONFIG.copy()
settings.update(self._settings)
return settings
def set_settings(self, value):
self._settings = value
@declared_attr
def settings(cls):
return synonym('_settings', descriptor=property(cls.get_settings,
cls.set_settings))
class Catalog(BASE):
'Quick and dirty table to ingest catalog information.'
__tablename__ = 'catalog'
pk_id = Column(Integer, primary_key=True)
scene_id = Column(String)
landsat_product_id = Column(String)
sensor = Column(String)
acquisition_date = Column(DateTime())
path = Column(Integer)
row = Column(Integer)
cloud_full = Column(Float)
day_night = Column(String)
image_quality = Column(Integer)
ground_control_points_model = Column(String)
browse_url = Column(String)
def test_to_yaml_type(self):
"""Tests the utils.to_yaml_type function"""
from sqlalchemy.sql.sqltypes import Boolean, Integer, Float, String, \
Date, Time, DateTime, _Binary
self.assertEqual('str', utils.to_yaml_type(None))
self.assertEqual('bool', utils.to_yaml_type(Boolean()))
self.assertEqual('int', utils.to_yaml_type(Integer()))
self.assertEqual('float', utils.to_yaml_type(Float()))
self.assertEqual('str', utils.to_yaml_type(String()))
self.assertEqual('str', utils.to_yaml_type(Date()))
self.assertEqual('str', utils.to_yaml_type(Time()))
self.assertEqual('str', utils.to_yaml_type(DateTime()))
self.assertEqual('binary', utils.to_yaml_type(_Binary()))
class Post(Base):
__tablename__ = 'posts'
id = Column(Integer, nullable=False, primary_key=True, autoincrement=True)
author = Column(Integer, ForeignKey('users.id'), nullable=False)
date_published = Column(DateTime(timezone=True), default=func.now())
title = Column(String, nullable=False)
content = Column(VARCHAR, nullable=False)
dislikes = Column(Integer)
likes = Column(Integer)
comments = relationship("Comment", backref="post_comments")
def as_dict(self):
return {
str(c.name): str(getattr(self, c.name))
for c in self.__table__.columns
}
def factory(
*,
string_types: Strategy[TypeEngine] = strings_factory(),
binary_string_types: Strategy[TypeEngine] = binary_strings_factory(),
enum_types: Strategy[TypeEngine] = enums_factory(),
primary_keys_types: Strategy[TypeEngine] = primary_keys_factory()
) -> Strategy[TypeEngine]:
extra_types = [
Float(asdecimal=True),
Boolean(),
Date(),
DateTime(),
Interval(),
Time()
]
return strategies.one_of(string_types, binary_string_types, enum_types,
primary_keys_types,
strategies.sampled_from(extra_types))