class Result(Base):
__tablename__ = 'results'
id = Cl(BigInteger, primary_key=True)
request_id = Cl(BigInteger, Fk('requests.id'))
request = relationship("Request", back_populates="result")
query = Cl(Text, nullable=False)
rate = Cl(Float)
report_type = Cl(Enum(ReportTypeEnum), default=ReportTypeEnum.table)
def __init__(self, request_id, query, rate, report_type):
self.request_id = request_id
self.query = query
self.rate = rate
self.report_type = report_type
def __repr__(self):
return '<id {}>'.format(self.id)
def __str__(self):
return '<Request id=%s>' % self.id
def to_dict(self):
return {
'id': self.id,
'request_id': self.request_id,
'query': self.query,
'rate': self.rate,
'report_type': ReportTypeEnum2int[self.report_type]
}
class Message(SqlAlchemyBase):
__tablename__ = 'messages'
id = Cl(sql.Integer, autoincrement=True, primary_key=True, nullable=False)
text = Cl(sql.Text, nullable=False)
author_id = Cl(sql.Integer, sql.ForeignKey('users.id', ondelete='CASCADE'))
author = orm.relation('User')
chat_id = Cl(sql.Integer, sql.ForeignKey('chats.id', ondelete='CASCADE'))
chat = orm.relation('Chat')
class User(SqlAlchemyBase, UserMixin):
__tablename__ = 'users'
id = Cl(sql.Integer, autoincrement=True, primary_key=True, nullable=False)
login = Cl(sql.String(64), nullable=False, unique=True)
username = Cl(sql.String(64), nullable=False, unique=True)
password = Cl(sql.String(128), nullable=False)
chats = orm.relationship('Chat', secondary='chat_to_user')
def __init__(self, *args, **kwargs):
SqlAlchemyBase.__init__(self, *args, **kwargs)
self.password = generate_password_hash(self.password)
def set_password(self, password):
self.password = generate_password_hash(password)
def check_password(self, other_passport):
return check_password_hash(self.password, other_passport)
class Table(Base):
__tablename__ = "tables"
__table_args__ = (UniqueConstraint('schema',
'name',
name="_schema_name_uc"),
Index('table_name_trgm_index',
text('name gist_trgm_ops'),
postgresql_using="gist"))
id = Cl(BigInteger, primary_key=True)
schema = Cl(Text)
name = Cl(Text, nullable=False)
column = relationship("Column", back_populates="table")
def __init__(self, schema, name):
self.schema = schema
self.name = name
def __str__(self):
return "%s.%s" % (self.schema, self.name)
class Request(Base):
__tablename__ = 'requests'
id = Cl(BigInteger, primary_key=True)
request = Cl(Text, nullable=True)
created_on = Cl(DateTime, server_default=now())
session = Cl(Text)
result = relationship("Result", back_populates="request")
def __init__(self, request, session):
self.request = request
self.session = session
def __repr__(self):
return '<id {}>'.format(self.id)
def __str__(self):
return '<Request id=%s>' % self.id
def to_dict(self):
return {'id': self.id, 'request': self.request}
class ForeignKey(Base):
__tablename__ = "foreign_keys"
__table_args__ = (UniqueConstraint('name', 'column_id',
'foreign_column_id'), )
id = Cl(BigInteger, primary_key=True)
name = Cl(Text, nullable=False)
column_id = Cl(BigInteger, Fk("columns.id"))
foreign_column_id = Cl(BigInteger, Fk("columns.id"))
column = relationship("Column", foreign_keys=[column_id])
foreign_column = relationship("Column", foreign_keys=[foreign_column_id])
def __init__(self, name, column_id, foreign_column_id):
self.name = name
self.column_id = column_id
self.foreign_column_id = foreign_column_id
def __str__(self):
return '"%s" FOREIGN KEY (column_name) REFERENCES table_name(column_name)' % self.name
class Column(Base):
__tablename__ = "columns"
__table_args__ = (UniqueConstraint('table_id',
'name',
name="_table_name_uc"), )
id = Cl(BigInteger, primary_key=True)
name = Cl(Text, nullable=False)
data_type = Cl(Text, nullable=False) # TODO: change in ln2sql
table_id = Cl(BigInteger, Fk("tables.id"))
table = relationship("Table", back_populates="column")
# fk = relationship("ForeignKey", back_populates="column")
# ffk = relationship("ForeignKey", back_populates="foreign_column")
def __init__(self, name, data_type, table_id):
self.name = name
self.data_type = data_type
self.table_id = table_id
def __str__(self):
return "%s %s" % (self.name, self.column_type)
class ChatToUser(SqlAlchemyBase):
__tablename__ = 'chat_to_user'
id = Cl(sql.Integer, autoincrement=True, primary_key=True, nullable=False)
user_id = Cl(sql.Integer, sql.ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
chat_id = Cl(sql.Integer, sql.ForeignKey('chats.id', ondelete='CASCADE'), nullable=False)