class SlackInstallation(BaseModel):
"""Keep track of each installation of Slack integration"""
__tablename__ = "slack_installation"
def __repr__(self): # pragma: no cover
return f"<SlackInstallation: {self.workspace_name}>"
# Attributes
access_token = db.Column(EncryptedType(db.String, SECRET_KEY, AesEngine,
"pkcs5"),
nullable=False)
authorizing_user_id = db.Column(db.String(300), nullable=False)
bot_access_token = db.Column(EncryptedType(db.String, SECRET_KEY,
AesEngine, "pkcs5"),
nullable=False)
bot_user_id = db.Column(EncryptedType(db.String, SECRET_KEY, AesEngine,
"pkcs5"),
nullable=False)
scope = db.Column(db.String(800), nullable=False)
workspace_id = db.Column(db.String(20), index=True, nullable=False)
workspace_name = db.Column(db.String(255), nullable=False)
state = db.Column(db.String(20), nullable=False, default="installed")
auth_response = db.Column("auth_response", db.JSON)
# Relationships
github_summary_users = db.relationship("GitHubSummaryUser",
back_populates="installation")
github_summary_config = db.relationship(
"GitHubSummaryConfiguration",
back_populates="slack_installation",
uselist=False)
class User(Base):
__tablename__ = "users"
id = Column(Integer, primary_key=True, nullable=False)
user_uid = Column(BigInteger, nullable=False)
username = Column(EncryptedType(type_in=String, key=CONFIG.BOT_SECRET_KEY),
nullable=False)
first_seen = Column(DateTime,
nullable=False,
default=func.current_timestamp())
last_seen = Column(DateTime,
nullable=False,
default=func.current_timestamp())
uni_id = Column(EncryptedType(type_in=String, key=CONFIG.BOT_SECRET_KEY),
nullable=True)
verified_at = Column(EncryptedType(type_in=DateTime,
key=CONFIG.BOT_SECRET_KEY),
nullable=True)
messages = relationship("LoggedMessage",
back_populates="user",
order_by=LoggedMessage.created_at)
karma_changes = relationship("KarmaChange",
back_populates="user",
order_by=KarmaChange.created_at)
class Event(Base):
__tablename__ = 'Event'
date = Column(EncryptedType(String, encryptionKey, AesEngine, 'pkcs5'))
title = Column(EncryptedType(String, encryptionKey, AesEngine, 'pkcs5'))
description = Column(
EncryptedType(String, encryptionKey, AesEngine, 'pkcs5'))
id = Column(Integer, primary_key=True)
class Vehicle(Base):
__tablename__ = 'Vehicle'
plate = Column(EncryptedType(String, encryptionKey, AesEngine, 'pkcs5'))
color = Column(EncryptedType(String, encryptionKey, AesEngine, 'pkcs5'))
make_model = Column(
EncryptedType(String, encryptionKey, AesEngine, 'pkcs5'))
id = Column(Integer, primary_key=True)
class Candidate(db.Model):
Sno = db.Column(db.Integer, primary_key=True)
Name = db.Column(EncryptedType(db.String(30),secret_key,AesEngine,'pkcs5'), nullable=False)
Register = db.Column(db.String(30), unique=True, nullable=False)
email = db.Column(EncryptedType(db.String(30),secret_key,AesEngine,'pkcs5'), nullable=False)
DOB= db.Column(db.String(30), nullable=False)
Count = db.Column(db.Integer, nullable=False)
class Account(db.Model):
__tablename__ = 'accounts'
id = db.Column(db.Integer, primary_key=True)
title = db.Column(EncryptedType(db.String(120), get_secret_key),
unique=True)
balance = db.Column(EncryptedType(db.Float(precision=2, as_decimal=True),
get_secret_key),
default=.0)
daily_limit = db.Column(EncryptedType(
db.Float(precision=0, as_decimal=True), get_secret_key),
default=.0)
def spent_today(self):
today = date.today()
today_start = tz.localize(datetime(today.year, today.month, today.day))
today_start_utc = today_start.astimezone(pytz.utc)
amount = db.session.query(func.sum(Transaction.amount)).filter(
Transaction.created >= today_start_utc,
Transaction.credit_id == self.id).scalar()
if amount is None:
amount = 0.0
return amount
class User(Base):
__tablename__ = 'user'
id = sa.Column(sa.Integer, primary_key=True)
username = sa.Column(
EncryptedType(sa.Unicode, test_key, encryption_engine))
access_token = sa.Column(
EncryptedType(sa.String, test_key, encryption_engine))
is_active = sa.Column(
EncryptedType(sa.Boolean, test_key, encryption_engine))
accounts_num = sa.Column(
EncryptedType(sa.Integer, test_key, encryption_engine))
phone = sa.Column(
EncryptedType(PhoneNumberType, test_key, encryption_engine))
color = sa.Column(EncryptedType(ColorType, test_key,
encryption_engine))
date = sa.Column(EncryptedType(sa.Date, test_key, encryption_engine))
time = sa.Column(EncryptedType(sa.Time, test_key, encryption_engine))
datetime = sa.Column(
EncryptedType(sa.DateTime, test_key, encryption_engine))
enum = sa.Column(
EncryptedType(sa.Enum('One', name='user_enum_t'), test_key,
encryption_engine))
class User(db.Model):
"""A user capable of listening to voicemails"""
__tablename__ = 'user'
email = db.Column(EncryptedType(db.String, flask_secret_key),
primary_key=True)
google_token = db.Column(EncryptedType(db.String, flask_secret_key))
authenticated = db.Column(EncryptedType(db.Boolean, flask_secret_key),
default=False)
def is_active(self):
"""True, as all users are active."""
return True
def get_id(self):
"""Return the email address to satisfy Flask-Login's requirements."""
return self.email
def is_authenticated(self):
"""Return True if the user is authenticated."""
return self.authenticated
def is_anonymous(self):
"""False, as anonymous users aren't supported."""
return False
class Location(SqlAlchemyBase):
__tablename__ = 'locations'
id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)
name = sa.Column(sa.String)
location_type_id = sa.Column(sa.Integer,
sa.ForeignKey('location_types.id'))
location_type = sa.orm.relationship("LocationType")
address = sa.Column(
EncryptedType(sa.String, config.sqlalchemy_secret_key, AesEngine,
'pkcs5'))
port = sa.Column(
EncryptedType(sa.String, config.sqlalchemy_secret_key, AesEngine,
'pkcs5'))
username = sa.Column(
EncryptedType(sa.String, config.sqlalchemy_secret_key, AesEngine,
'pkcs5'))
password = sa.Column(
EncryptedType(sa.String, config.sqlalchemy_secret_key, AesEngine,
'pkcs5'))
subtype_id = sa.Column(sa.Integer, sa.ForeignKey('subtypes.id'))
subtype = sa.orm.relationship("Subtype")
database = sa.Column(
EncryptedType(sa.String, config.sqlalchemy_secret_key, AesEngine,
'pkcs5'))
queries = sa.orm.relationship("Query")
created_date = sa.Column(sa.DateTime, default=datetime.datetime.now)
class Patient(db.Model):
"""A patient record capable of taking surveys"""
__tablename__ = "patients"
id = db.Column(db.Integer, primary_key=True)
mrn = db.Column(EncryptedType(db.String, flask_secret_key))
fullname = db.Column(EncryptedType(db.String, flask_secret_key))
age = db.Column(EncryptedType(db.String, flask_secret_key))
phone = db.Column(EncryptedType(db.String, flask_secret_key))
email = db.Column(EncryptedType(db.String, flask_secret_key))
location = db.Column(ChoiceType(LOCATION_CHOICES))
program = db.Column(ChoiceType(PROGRAM_CHOICES))
year = db.Column(db.Integer)
user_id = db.Column(db.Integer, db.ForeignKey("user.id"))
surveys = db.relationship(
"SurveyResponse",
backref="patient",
lazy="dynamic",
cascade="all, delete-orphan",
)
responses = db.relationship(
"QuestionResponse",
backref="patient",
lazy="dynamic",
cascade="all, delete-orphan",
)
progress = db.relationship(
"Progress", backref="patient", lazy="dynamic", cascade="all, delete-orphan"
)
creation_time = db.Column(db.DateTime, default=func.now())
def to_dict(self):
return {col.name: getattr(self, col.name) for col in self.__table__.columns}
class Chat():
date = db.Column(db.DateTime, primary_key=True, default=dt.now(tz.utc))
users = db.Column(EncryptedType(JSONType, get_crypto_key, AesEngine, 'pkcs5'), nullable=False)
messages = db.Column(EncryptedType(JSONType, get_crypto_key, AesEngine, 'pkcs5'), nullable=False)
def __init__(self):
self.users = []
self.messages = []
class Voter(db.Model): #DATABASE MODEL
Sno = db.Column(db.Integer, primary_key=True)
Name = db.Column(EncryptedType(db.String(30),secret_key,AesEngine,'pkcs5'), nullable=False)
email = db.Column(EncryptedType(db.String(30),secret_key,AesEngine,'pkcs5'), nullable=False)
DOB= db.Column(db.String(30), nullable=False)
Register = db.Column(db.Integer, unique=True, nullable=False)
Valid= db.Column(db.Boolean,nullable=False)
Voted= db.Column(db.Boolean,nullable=False)
Password=db.Column(db.String(120), nullable=False)
def _re_encrypt_row(
self,
conn: Connection,
row: RowProxy,
table_name: str,
columns: Dict[str, EncryptedType],
) -> None:
"""
Re encrypts all columns in a Row
:param row: Current row to reencrypt
:param columns: Meta info from columns
"""
re_encrypted_columns = {}
for column_name, encrypted_type in columns.items():
previous_encrypted_type = EncryptedType(
type_in=encrypted_type.underlying_type,
key=self._previous_secret_key)
try:
unencrypted_value = previous_encrypted_type.process_result_value(
self._read_bytes(column_name, row[column_name]),
self._dialect)
except ValueError as exc:
# Failed to unencrypt
try:
encrypted_type.process_result_value(
self._read_bytes(column_name, row[column_name]),
self._dialect)
logger.info(
"Current secret is able to decrypt value on column [%s.%s],"
" nothing to do",
table_name,
column_name,
)
return
except Exception:
raise Exception from exc
re_encrypted_columns[
column_name] = encrypted_type.process_bind_param(
unencrypted_value,
self._dialect,
)
set_cols = ",".join([
f"{name} = :{name}" for name in list(re_encrypted_columns.keys())
])
logger.info("Processing table: %s", table_name)
conn.execute(
text(f"UPDATE {table_name} SET {set_cols} WHERE id = :id"),
id=row["id"],
**re_encrypted_columns,
)
class User(db.Model):
__tablename__ = "users"
id = db.Column(db.Integer, primary_key=True)
user_name = db.Column(db.String(50), nullable=False, unique=True)
email = db.Column(db.String(120), nullable=False)
password = db.Column(EncryptedType(db.String, key), nullable=False)
pin = db.Column(db.String(4), nullable=True)
question = db.Column(db.String(50), nullable=True)
answer = db.Column(EncryptedType(db.String, key), nullable=True)
image = db.Column(EncryptedType(db.String, key), nullable=True)
def __repr__(self):
return f'{self.user_name}'
class User(MyCrtDb.Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
username = Column(String(128), unique=True, nullable=False)
password = Column(String(512), nullable=False)
email = Column(String(128))
access_key= Column(EncryptedType(String, SECRET_KEY, AesEngine), nullable=False)
secret_key = Column(EncryptedType(String, SECRET_KEY, AesEngine), nullable=False)
notificationLife = Column(Integer())
def __repr__(self):
return '<User %r %r %r %r %r %r' % (self.username, self.password , self.email, self.access_key, self.secret_key, self.notificationLife)
def is_authenticated(self):
return True
def is_active(self):
return self.active
def is_anonymous(self):
return False
def get_id(self):
return self.id
def hash_password(self, password):
self.password = pwd_context.hash(password)
def verify_password(self, password):
return pwd_context.verify(password, self.password)
def generate_auth_token(self, expiration=7200):
serializer = Serializer(SECRET_KEY, expires_in=expiration)
return serializer.dumps({ 'id' : self.id })
def get_keys(self):
newDict = {'access_key': self.access_key,
'secret_key': self.secret_key}
return newDict
@staticmethod
def verify_auth_token(token):
serializer = Serializer(SECRET_KEY)
try:
data = serializer.loads(token)
except SignatureExpired:
return None # valid token, expired
except BadSignature:
return None # invalid token
user = User.query.get(data['id'])
return user
class Blacklist(db.Model):
id = db.Column(db.Integer, primary_key=True)
ip = db.Column(EncryptedType(db.String, key, AesEngine, 'pkcs5'),
unique=True,
nullable=False)
last = db.Column(EncryptedType(db.String, key, AesEngine, 'pkcs5'),
unique=False,
nullable=False) # Sist gang en request ble gjort
last_bad_login = db.Column(EncryptedType(db.String, key, AesEngine,
'pkcs5'),
unique=False,
nullable=False)
# start = db.Column(db.String, unique=False, nullable=False) # Når det begynte å bli for mange requests
frequent_request_count = db.Column(
EncryptedType(db.Integer, key, AesEngine, 'oneandzeroes'),
unique=False,
nullable=False
) # Hvor mange ganger brukeren har sent for hyppige requests
wrong_password_count = db.Column(
EncryptedType(db.Integer, key, AesEngine, 'oneandzeroes'),
unique=False,
nullable=False
) # Hvor mange ganger brukeren har skrevet inn feil passord
blocked_until = db.Column(EncryptedType(db.String, key, AesEngine,
'pkcs5'),
unique=False,
nullable=True)
blocked_login_until = db.Column(EncryptedType(db.String, key, AesEngine,
'pkcs5'),
unique=False,
nullable=True)
reason = db.Column(EncryptedType(db.String, key, AesEngine, 'pkcs5'),
unique=False,
nullable=True)
class SystemLoginAttempt(db.Model):
__tablename__ = 'system_login_attempts'
id = db.Column(db.Integer, primary_key=True)
at = db.Column(db.DateTime, default=datetime.utcnow)
success = db.Column(db.Boolean)
from_where = db.Column(EncryptedType(db.String(120), get_secret_key))
username = db.Column(EncryptedType(db.String(120), get_secret_key))
info = db.Column(EncryptedType(db.String(120), get_secret_key))
class User(Base):
__tablename__ = 'user'
id = Column(BigInteger, primary_key=True)
language_code = Column(String(2))
email = Column(EncryptedType(String, Config.SECRET_KEY))
password = Column(EncryptedType(String, Config.SECRET_KEY))
save_credentials = Column(Boolean(create_constraint=False))
file_format = Column(Enum(FileFormat), default=FileFormat.pdf)
cache_expire_date = Column(TIMESTAMP(timezone=False),
default=datetime.now())
now_caching = Column(Boolean(create_constraint=False), default=False)
cache_built = Column(Boolean(create_constraint=False), default=False)
login_error = Column(Boolean(create_constraint=False), default=False)
book_collection = relationship('Manga', secondary=lambda: user_manga_table)
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
with op.batch_alter_table('sshkeys', schema=None) as batch_op:
batch_op.add_column(
sa.Column('private',
EncryptedType(sa.String, get_vault_password(), AesEngine,
'pkcs5'),
nullable=False,
server_default=''))
batch_op.add_column(
sa.Column('public',
EncryptedType(sa.String, get_vault_password(), AesEngine,
'pkcs5'),
nullable=False,
server_default=''))
class AWSKey(db.Model):
id = db.Column(db.Integer, primary_key=True)
id_user = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
account_id = db.Column(db.Unicode(16), nullable=True)
key = db.Column(db.Unicode(20), nullable=False)
secret = db.Column(EncryptedType(db.String, secret_key), nullable=False)
pretty = db.Column(db.Unicode(63), nullable=True)
billing_bucket_name = db.Column(db.Unicode(63), nullable=True)
last_fetched = db.Column(db.DateTime())
error_status = db.Column(db.Unicode(32), nullable=True)
import_s3 = db.Column(db.Boolean(), default=False)
is_valid_key = db.Column(db.Boolean(), server_default='1')
last_duration = db.Column(db.Integer, nullable=True)
next_s3_bill_import = db.Column(db.DateTime(), nullable=True)
def get_boto_session(self):
return boto3.Session(
aws_access_key_id=self.key,
aws_secret_access_key=self.secret,
)
def get_aws_user_id(self):
try:
if not self.account_id:
sts = self.get_boto_session().client('sts')
self.account_id = sts.get_caller_identity()['Account']
self.is_valid_key = True
db.session.commit()
except botocore.exceptions.ClientError, e:
if e.response['Error']['Code'] == 'InvalidClientTokenId':
self.is_valid_key = False
db.session.commit()
else:
raise
return self.account_id
class KarmaChange(Base):
__tablename__ = 'karma_changes'
karma_id = Column(Integer,
ForeignKey('karma.id'),
primary_key=True,
nullable=False)
user_id = Column(Integer,
ForeignKey('users.id'),
primary_key=True,
nullable=False)
message_id = Column(Integer,
ForeignKey('messages.id'),
primary_key=True,
nullable=False)
created_at = Column(DateTime, nullable=False)
reasons = Column(EncryptedType(type_in=ScalarListType(str),
key=CONFIG['BOT_SECRET_KEY']),
nullable=True)
change = Column(Integer, nullable=False)
score = Column(Integer, nullable=False)
karma = relationship('Karma', back_populates='changes')
user = relationship('User', back_populates='karma_changes')
message = relationship('LoggedMessage', back_populates='karma')
@hybrid_property
def local_time(self):
return utc.localize(self.created_at).astimezone(
timezone('Europe/London'))
class Karma(Base):
__tablename__ = "karma"
id = Column(Integer, primary_key=True, nullable=False)
name = Column(String, nullable=False)
added = Column(
EncryptedType(type_in=DateTime, key=CONFIG["BOT_SECRET_KEY"]),
nullable=False,
default=datetime.utcnow(),
)
pluses = Column(Integer, nullable=False, default=0)
minuses = Column(Integer, nullable=False, default=0)
neutrals = Column(Integer, nullable=False, default=0)
changes = relationship("KarmaChange",
back_populates="karma",
order_by=KarmaChange.created_at.asc())
@hybrid_property
def net_score(self):
return self.pluses - self.minuses
@hybrid_property
def total_karma(self):
return self.pluses + self.minuses + self.neutrals
class Team(Base):
__tablename__ = 'team'
id = sa.Column(sa.Integer, primary_key=True)
key = sa.Column(sa.String(50))
name = sa.Column(
EncryptedType(sa.Unicode, lambda: self._team_key,
encryption_engine, padding_mechanism))
class Appointments(db.Model):
__tablename__ = 'appointments'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
date_created = db.Column(db.DateTime, nullable=False, default=dt.now(tz.utc))
date_scheduled = db.Column(db.DateTime, nullable=False)
created_by = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
created_for = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
service_id = db.Column(db.Integer, db.ForeignKey('catalog_services.id'), nullable=False)
service_supp_id = db.Column(db.Integer, db.ForeignKey('services_supplement.id'), nullable=True)
emp_assigned = db.Column(db.Integer, db.ForeignKey('user_x_employees_assigned.id'), nullable=False)
emp_accepted = db.Column(db.Boolean, nullable=True, default=False)
conversation_id = db.Column(db.JSON, nullable=True)
usr_attendance = db.Column(db.DateTime, nullable=True)
emp_attendance = db.Column(db.DateTime, nullable=True)
ended_with_survey = db.Column(db.DateTime, nullable=True)
cancelled = db.Column(db.Boolean, nullable=True, default=False)
cancelled_by = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=True)
cancelled_reason = db.Column(EncryptedType(db.Text, get_crypto_key, AesEngine, 'pkcs5'), nullable=True)
def __repr__(self):
return jsonify(
id = self.id,
service_id = self.service_id,
created_for = self.created_for,
emp_assigned = self.emp_assigned,
date_scheduled = self.date_scheduled
)
class Device(db.Model):
__tablename__ = "device"
hostname = Column(String, nullable=False, primary_key=True)
ip_address = Column(String, nullable=False, unique=True)
site = Column(String, nullable=False)
role = Column(String, nullable=False)
device_type = Column(String, nullable=False)
os = Column(String, nullable=False)
username = Column(String, nullable=False)
password = Column(EncryptedType(String, _key), nullable=False)
def __init__(self, hostname, ip_address, site, role, device_type, os,
username, password):
self.hostname = hostname
self.ip_address = ip_address
self.site = site
self.role = role
self.device_type = device_type
self.os = os
self.username = username
self.password = password
def as_dict(self):
return {c.name: getattr(self, c.name) for c in self.__table__.columns}
class Cookies(db.Model):
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(EncryptedType(db.Integer, key, AesEngine,
'oneandzeroes'),
unique=False,
nullable=False)
ip = db.Column(EncryptedType(db.String, key, AesEngine, 'pkcs5'),
unique=False,
nullable=False)
session_cookie = db.Column(EncryptedType(db.String, key, AesEngine,
'pkcs5'),
unique=True,
nullable=False)
valid_to = db.Column(EncryptedType(db.String, key, AesEngine, 'pkcs5'),
unique=False,
nullable=False)
class Channel(db.Model):
"""
A communication channel used to interact with the user and the system.
Attributes:
:param @id: Id to populate the database.
:param @name: The name or address of the channel
:param @agent_id: The id of the agent containing this channel
:param @agent: Tha object agent containing this channel
:param @social_network: The name of the social network used
"""
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80), nullable=True)
token = db.Column(EncryptedType(db.String, key), nullable=True)
agent_id = db.Column(db.Integer, db.ForeignKey('agent.id'), nullable=False)
agent = db.relationship('Agent', backref=db.backref('channels', lazy=True))
social_network = db.Column(db.Enum(SocialNetwork),
default=SocialNetwork.telegram,
nullable=False)
def __repr__(self):
return f"<Channel {self.name}>"
class Post(db.Model):
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(200), nullable=False)
admin_id = db.Column(db.Integer, db.ForeignKey("admin.id"), nullable=False)
category_id = db.Column(db.Integer,
db.ForeignKey("category.id"),
nullable=False)
link = db.Column(db.String(200), nullable=False)
content = db.Column(db.Text, nullable=False)
abstract = db.Column(db.String(400), nullable=False)
img = db.Column(
EncryptedType(db.String(500), Config.SECRET_KEY, AesEngine, 'pkcs5'))
is_published = db.Column(db.Boolean)
is_sponsored = db.Column(db.Boolean)
category = db.relationship('Category',
backref=db.backref("posts", lazy=True))
created = db.Column(db.DateTime, default=datetime.utcnow())
updated = db.Column(db.DateTime, onupdate=datetime.utcnow())
@hybrid_property
def image_url(self):
return display_image(self.img, "/static/upload/posts/")
def upgrade():
op.add_column(
'dbs',
sa.Column(
'password',
EncryptedType(sa.String(1024)),
nullable=True))
class KarmaChange(Base):
__tablename__ = "karma_changes"
karma_id = Column(Integer,
ForeignKey("karma.id"),
primary_key=True,
nullable=False)
user_id = Column(Integer,
ForeignKey("users.id"),
primary_key=True,
nullable=False)
message_id = Column(Integer,
ForeignKey("messages.id"),
primary_key=True,
nullable=False)
created_at = Column(DateTime, nullable=False)
reasons = Column(
EncryptedType(type_in=ScalarListType(str),
key=CONFIG["BOT_SECRET_KEY"]),
nullable=True,
)
change = Column(Integer, nullable=False)
score = Column(Integer, nullable=False)
karma = relationship("Karma", back_populates="changes")
user = relationship("User", back_populates="karma_changes")
message = relationship("LoggedMessage", back_populates="karma")
@hybrid_property
def local_time(self):
return utc.localize(self.created_at).astimezone(
timezone("Europe/London"))
