class Topic(db.Model):
__tablename__ = "topics"
id = db.Column(UUID(as_uuid=True),
primary_key=True,
default=uuid.uuid4,
unique=True)
subject = db.Column(db.String(128), nullable=True)
description = db.Column(db.Text, nullable=True)
created_by = db.Column(
UUID(as_uuid=True),
db.ForeignKey("users.id"),
)
updated_by = db.Column(
UUID(as_uuid=True),
db.ForeignKey("users.id"),
)
created_at = db.Column(ISO8601DateTime,
nullable=False,
default=datetime.datetime.now)
updated_at = db.Column(ISO8601DateTime,
nullable=False,
default=datetime.datetime.now,
onupdate=datetime.datetime.now)
deleted_at = db.Column(ISO8601DateTime, nullable=True)
messages = db.relationship("Message", lazy="dynamic")
creator = db.relationship(User, primaryjoin=(created_by == User.id))
updator = db.relationship(User, primaryjoin=(updated_by == User.id))
def __init__(self, subject, description, created_by, updated_by):
self.subject = subject
self.description = description
self.created_by = created_by
self.updated_by = updated_by
def json(self) -> Dict:
return {
"id": str(self.id),
"subject": self.subject,
"description": self.description,
"created_by": self.creator.json(),
"updated_by": self.updator.json(),
"created_at": self.created_at,
"updated_at": self.updated_at,
"deleted_at": self.deleted_at,
"messages_count": len(self.messages.all()),
"messages": self.paginated_messages
}
@property
def paginated_messages(self) -> List[Dict]:
"""Paginated messages representation."""
from app.api.rest.messages.models import Message
_ordered_msgs = self.messages.order_by(Message.created_at.desc())
_paginated_msgs = _ordered_msgs.paginate(
page=1,
per_page=current_app.config.get("COMMENTS_PER_PAGE"),
error_out=False).items
return [message.json() for message in _paginated_msgs]
@classmethod
def find(cls, **kwargs) -> "Topic":
"""Find a database entry that matches given keyword argument."""
keys = list(kwargs.keys())
if (len(keys) == 1 and keys[0] in cls.__table__.columns):
return cls.query.filter_by(**kwargs).first()
@classmethod
def find_all(cls, page: int) -> List[Dict]:
"""Find all topics in the database that are not deleted yet."""
if not page:
raise ValueError
topics = (cls.query.filter_by(deleted_at=None).order_by(
func.lower(cls.subject)).paginate(
page=page,
per_page=current_app.config.get("TOPICS_PER_PAGE"),
error_out=False))
pagination_data = (topics.has_next, topics.next_num,
[topic.json() for topic in topics.items])
return pagination_data
def insert(self) -> None:
"""Insert into the database."""
db.session.add(self)
db.session.commit()
def delete(self) -> None:
"""Mark a topic as deleted in the database."""
self.deleted_at = datetime.datetime.now()
db.session.commit()
class MotosModel(db.Model):
__tablename__ = 'motos'
id = db.Column(db.Integer, primary_key=True)
model = db.Column(db.String(30),nullable=False)
active = db.Column(db.Boolean,nullable=False)
charge = db.Column(db.Integer,nullable=False)
position = db.Column(db.String(50),nullable=False)
latitude = db.Column(db.Float,nullable=False)
longitude = db.Column(db.Float,nullable=False)
plate = db.Column(db.String(8), unique=True, nullable=False)
def __init__(self, model, active, charge, latitude, longitude, plate, position="Passeig de Gracia, 55, Barcelona"):
self.model = model
self.active = active
self.charge = charge
self.latitude = latitude
self.longitude = longitude
self.position = position
self.plate = plate
def json(self):
return {
'id': self.id,
'model': self.model,
'active': self.active,
'charge': self.charge,
'latitude': self.latitude,
'longitude': self.longitude,
'plate':self.plate
}
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
@classmethod
def find_by_id(cls, id):
return MotosModel.query.filter_by(id=id).first()
@classmethod
def is_active(cls, id):
moto = cls.query.filter_by(id=id).first()
return moto.active
@classmethod
def change_status(cls, id):
moto = cls.query.filter_by(id=id).first()
if moto.active is True:
moto.active = False
elif moto.active is False:
moto.active = True
db.session.add(moto)
db.session.commit()
@classmethod
def distMotoUser(cls, user, moto):
# Ej:
coordUser = [41.386422, 2.16407] # UB #0.45 km
coordMoto = [41.387872, 2.170001] # ECI - Pz Cat.
# coordMoto = [moto.latitude,moto.longitude]
# coordUser = [user.latitude, user.longitude]
slat = radians(coordUser[0])
slon = radians(coordUser[1])
elat = radians(coordMoto[0])
elon = radians(coordMoto[1])
# Distance in kilometer
return 6371.01 * acos(sin(slat) * sin(elat) + cos(slat) * cos(elat) * cos(slon - elon))
@classmethod
def modify_bike(cls, id, modified_bike):
bike = cls.query.filter_by(id=id).first()
if bike.model != modified_bike.model:
bike.model = modified_bike.model
if bike.active != modified_bike.active:
bike.active = modified_bike.active
if bike.charge != modified_bike.charge:
bike.charge = modified_bike.charge
if bike.latitude != modified_bike.latitude:
bike.latitude = modified_bike.latitude
if bike.longitude != modified_bike.longitude:
bike.longitude = modified_bike.longitude
if bike.plate != modified_bike.plate:
bike.plate = modified_bike.plate
db.session.add(bike)
db.session.commit()
class ItemModel(db.Model):
__tablename__ = 'items'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80))
price = db.Column(db.Float(precision=2 ))
store_id = db.Column(db.Integer, db.ForeignKey('stores.id'))
store = db.relationship('StoreModel')
def __init__(self, name ,price, store_id):
self.name = name
self.price = price
self.store_id = store_id
def json(self):
return {'name': self.name, 'price': self.price}
@classmethod
def find_by_name(cls, name):
return cls.query.filter_by(name=name).first() # SELECT * FROM items WHERE name=name LIMIT=1
# connection = sqlite3.connect('data.db')
# cursor = connection.cursor()
#
# query = "SELECT * FROM items WHERE name=?"
# result = cursor.execute(query,(name,))
# row = result.fetchone()
# connection.close()
#
# if row:
# #return {'item':{name: row[0], 'price': row[1]}}
# #return cls(row[0], row[1]) # 'cls' calls the __init__() and row[0]=name, row[1]=price
# return cls(*row) # bir üstteki return ile aynı işlevde
# After Implementing SQLAlchemy
# def insert(self):
# connection = sqlite3.connect('data.db')
# cursor = connection.cursor()
#
# query = "INSERT INTO items VALUES (?, ?)"
# cursor.execute(query, (self.name, self.price))
#
# connection.commit()
# connection.close()
# INSERT METHOD IN SQLAlchemy
def save_to_db(self):
db.session.add(self)
db.session.commit()
# def update(self):
# connection = sqlite3.connect('data.db')
# cursor = connection.cursor()
#
# query = "UPDATE items SET price=? WHERE name=?"
# cursor.execute(query, (self.price, self.name))
#
# connection.commit()
# connection.close()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
class ClientModel(db.Model):
__tablename__ = 'clients'
client_id = db.Column(db.Integer, primary_key=True)
# Personal
nombre = db.Column(db.String(50), nullable=False)
# Bancario
iban = db.Column(db.String(34), nullable=False)
# Únicos
dni_nie = db.Column(db.String(9), nullable=False, unique=True)
email = db.Column(db.String(50), nullable=False, unique=True)
# Provisional
password = db.Column(db.String(20), nullable=False)
# Para la referencia many_to_many con la tabla de motos.
motos = db.relationship('MotoModel',
secondary=relations_table,
backref=db.backref('clients', lazy='dynamic'))
rr = db.relationship('ReservedRunningModel',
backref='client',
uselist=False)
def __init__(self, nombre, iban, dni_nie, email, password):
self.nombre = nombre
self.email = email
self.iban = iban
self.password = password
self.dni_nie = dni_nie
def json(self):
return {
"client_id": self.client_id,
"nombre": self.nombre,
"email": self.email,
"iban": self.iban,
"dni_nie": self.dni_nie
}
def json_profile(self):
data = {
'nombre': self.nombre,
'iban': self.iban,
'dni_nie': self.dni_nie,
'email': self.email
}
return data
def set_name(self, name):
self.nombre = name
db.session.commit()
def set_iban(self, iban):
self.iban = iban
db.session.commit()
def set_dni_nie(self, dni_nie):
self.dni_nie = dni_nie
db.session.commit()
def set_email(self, email):
self.email = email
db.session.commit()
def save_to_db(self):
"""Saves instance to DB
"""
db.session.add(self)
db.session.commit()
def delete_from_db(self):
"""Deletes instance from DB
"""
db.session.delete(self)
db.session.commit()
# Metodo que encuentra al cliente segun su email y lo retorna
@classmethod
def find_by_email(cls, email):
return ClientModel.query.filter_by(email=email).first()
# Metodo que encuentra al cliente segun su dni y lo retorna
@classmethod
def find_by_dni(cls, dni_nie):
return ClientModel.query.filter_by(dni_nie=dni_nie).first()
# Metodo que comprueba si la contraseña es correcta
def verify_password(self, password):
return self.password.__eq__(password)
class JournalModel(db.Model):
__tablename__ = 'journals'
username = db.Column(db.Integer,
db.ForeignKey('users.username'),
primary_key=True)
date = db.Column(db.String(10), primary_key=True)
content = db.Column(db.Text)
private = db.Column(db.Boolean)
timestamp = db.Column(db.DateTime)
def __init__(self, username, date, content, private=False):
self.username = username
self.date = str(date)
self.content = content
self.private = private
def save_to_db(self):
self.timestamp = datetime.datetime.utcnow()
db.session.add(self)
db.session.commit()
def is_editable(self):
# TODO: Impose restritions on edit-allowed time limit and validate for well-formed date string
return True
def to_dict(self):
return {
"username": self.username,
"date": self.date,
"content": self.content,
"private": self.private
}
def __lt__(self, other):
return other.date < self.date
@classmethod
def retrieve_by_username(cls, username, count, daterange):
if username:
user_query = cls.query.filter_by(username=username)
date_descending = cls.date.desc()
if count == 0:
return user_query.order_by(date_descending).all()
elif count:
return user_query.order_by(date_descending).limit(count).all()
else:
start_date = str(datetime.date.today() -
datetime.timedelta(daterange))
return user_query.filter(
cls.date >= start_date).order_by(date_descending).all()
@classmethod
def retrieve_exact_post(cls, username, date):
if username and date:
return cls.query.filter_by(username=username, date=date).first()
@classmethod
def convert_date(cls, date_string):
if type(date_string) is datetime.date:
return date_string
datelist = date_string.split('-')
if len(datelist) == 3:
return datetime.date(*[int(dateunit) for dateunit in datelist])
raise ValueError('Invalid date string provided')
@classmethod
def is_valid_payload(cls, payload):
required = ['username', 'date', 'content']
return all(key in payload for key in required)
class ATSCandidate(db.Model):
"""
A candidate from an ATS. May or may not be linked to a GT candidate.
"""
__tablename__ = 'ats_candidate'
id = db.Column(db.Integer, primary_key=True)
# ID in ATS table
ats_account_id = db.Column(db.Integer)
# Candidate ID in the remote ATS
ats_remote_id = db.Column(db.String(100))
# getTalent candidate ID, if linked
gt_candidate_id = db.Column(db.Integer)
# ID into the ATSCandidateProfile table
profile_id = db.Column(db.Integer)
# ID into an ATS-specific table
ats_table_id = db.Column(db.Integer)
added_at = db.Column(db.TIMESTAMP, default=datetime.datetime.utcnow)
updated_at = db.Column(db.TIMESTAMP, default=datetime.datetime.utcnow)
def to_dict(self):
"""
Return a dict representation of this object.
:rtype: dict
"""
return {
'id': self.id,
'ats_account_id': self.ats_account_id,
'ats_remote_id': self.ats_remote_id,
'gt_candidate_id': self.gt_candidate_id
}
@classmethod
def get_all(cls, account_id):
"""
Return all ATS candidates associated with this account.
:param int account_id: primary key of the account.
:rtype list|None: list of candidates.
"""
candidate_list = cls.query.filter_by(ats_account_id=account_id).all()
if not candidate_list:
return
return_list = []
for candidate in candidate_list:
item = candidate.to_dict()
profile = ATSCandidateProfile.get(candidate.profile_id)
item.update(profile.to_dict())
return_list.append({candidate.id: item})
return return_list
@classmethod
def get_all_as_json(cls, account_id):
"""
Return all ATS candidates associated with this account as JSON.
:param int account_id: primary key of the account.
:rtype str|None: JSON list of candidates.
"""
candidates = ATSCandidate.get_all(account_id)
if not candidates:
return
return json.dumps(candidates)
@classmethod
def get_by_ats_id(cls, account_id, ats_id):
"""
Retrive a candidate by ATS account and remote ATS id.
:param int account_id: primary key of the account.
:param int ats_id: Id of the candidate in the remote ATS.
:rtype list: A candidate.
"""
# TODO Test this.
return cls.query.filter(cls.ats_account_id == account_id,
cls.ats_remote_id == ats_id).first()
def __repr__(self):
return "<ATS Candidate (id = %r)>" % self.ats_remote_id
class ItemModel(db.Model):
__tablename__ = 'items'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80))
price = db.Column(db.Float(precision=2))
store_id = db.Column(db.Integer, db.ForeignKey('stores.id'))
store = db.relationship('StoreModel')
def __init__(self, name, price, store_id):
self.name = name
self.price = price
self.store_id = store_id
def json(self):
return {'name': self.name, 'price': self.price}
@classmethod
def find_by_name(cls, name):
# without sqlalchemy:
'''
connection = sqlite3.connect('data.db')
cursor = connection.cursor()
query = "SELECT * FROM items WHERE name=?"
result = cursor.execute(query, (name,))
row = result.fetchone()
connection.close()
if row:
return cls(*row) # row[0], row[1]
'''
# with sqlalchemy:
# returns a ItemModel
# ItemModel.query.filter_by(name=name, id=1) equals to ItemModel.query.filter_by(name=name).filter_by(id=1)...
# equals to:
return cls.query.filter_by(
name=name).first() # = SELECT * FROM items WHERE name=name LIMIT 1
'''
def insert(self):
connection = sqlite3.connect('data.db')
cursor = connection.cursor()
query = "INSERT INTO items VALUES (?, ?)"
cursor.execute(query, (self.name, self.price))
connection.commit()
connection.close()
def update(self):
connection = sqlite3.connect('data.db')
cursor = connection.cursor()
query = "UPDATE items SET price=? WHERE name=?"
cursor.execute(query, (self.price, self.name))
connection.commit()
connection.close()
'''
def save_to_db(self): # = insert + update
db.session.add(self)
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
class RecipeModel(db.Model):
"""
This recipe model class
represents the recipe model
"""
__tablename__ = 'recipes'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80))
description = db.Column(db.Text)
date_created = db.Column(db.DateTime, default=db.func.current_timestamp())
date_modified = db.Column(db.DateTime, default=db.func.current_timestamp())
category_id = db.Column(db.Integer, db.ForeignKey('categories.id'))
created_by = db.Column(db.Integer,
db.ForeignKey('users.id'),
nullable=False)
def json(self):
"""
This method jsonifies the recipe model
"""
return {'name': self.name, 'description': self.description}
@property
def get_url(self):
return url_for(request.endpoint, _external=True)
@classmethod
def find_by_name(cls, name):
"""
This class method returns the recipe by name
"""
return cls.query.filter_by(name=name).first()
@classmethod
def find_by_category(cls, category_id):
"""
This class method returns the recipe by category id
"""
return cls.query.filter_by(category_id=category_id).first()
@classmethod
def find_by_id(cls, id):
"""
This class method returns the recipe by id
"""
return cls.query.filter_by(id=id).first()
@classmethod
def row_count(cls):
"""
This class method returns the number of rows
"""
return cls.query.count()
def save_to_db(self):
"""This method saves recipe to the database"""
db.session.add(self)
db.session.commit()
def delete_from_db(self):
"""
This method deletes recipe from the database
"""
db.session.delete(self)
db.session.commit()
class User(db.Model):
'''
auth_level 1 - basic user
auth_level 2 - expert user
auth_level 3 - admin
'''
__tablename__ = 'fossil_finder_users'
id = db.Column(db.Integer, primary_key=True)
auth_level = db.Column(db.Integer, nullable=False)
group_code = db.Column(db.String(255), index=True, unique=True)
group_name = db.Column(db.String(255))
email = db.Column(db.String(255), index=True, unique=True)
password_hash = db.Column(db.String(255), index=True)
verified = db.Column(db.Boolean, nullable=False, default=False)
active = db.Column(db.Boolean, nullable=False, default=True)
def generate_token(self, exp=36000):
s = Serializer(app.config['SERIALIZER_KEY'], expires_in=exp)
return s.dumps({'id': self.id})
def generate_password_hash(self, password):
try:
password_hash = bcrypt.hashpw(password, bcrypt.gensalt())
self.password_hash = password_hash
db.session.commit()
except:
db.session.rollback()
def verify_password(self, password):
return bcrypt.hashpw(
password,
self.password_hash) == self.password_hash and self.verified
def login(self):
session['token'] = self.generate_token()
g.current_user = self
def verify(self):
try:
self.verified = True
db.session.add(self)
db.session.commit()
except:
db.session.rollback()
@classmethod
def from_group_code(cls, group_code):
return cls.query.filter(cls.group_code == group_code).first()
@classmethod
def from_token(cls, token):
s = Serializer(app.config['SERIALIZER_KEY'])
try:
data = s.loads(token)
except SignatureExpired:
return None
except BadSignature:
return None
user = cls.query.get(data['id'])
return user
@classmethod
def from_email(cls, email):
return cls.query.filter(cls.email == email).first()
@classmethod
def create(cls, email=None, password=None, auth_level=1):
if not email or not password:
return
try:
user = cls(email=email, auth_level=1)
db.session.add(user)
db.session.commit()
user.generate_password_hash(password)
return user
except:
db.session.rollback()
return None
class SubscriberModel(db.Model):
__tablename__ = 'subscriber'
id = db.Column(db.Integer, primary_key=True)
subscriber_id = db.Column(db.Integer,
db.ForeignKey('user.id', ondelete='CASCADE'))
notification_id = db.Column(
db.Integer, db.ForeignKey('notification.id', ondelete='CASCADE'))
subscribername = db.Column(db.String(80))
email = db.Column(db.String(80))
def __init__(self, notification_id, subscribername=None):
print(subscribername)
user = UserModel.find_by_username(str(subscribername))
#self.subscriber_id = subscriber_id
self.notification_id = notification_id
if subscribername == None:
self.subscribername = user.username
else:
self.subscribername = subscribername
self.email = user.email
def json(self):
return {
'id': self.id,
'subscriber_id': self.subscriber_id,
'notification_id': self.notification_id,
'subscribername': self.subscribername,
'email': self.email
}
@classmethod
def update(self, id, param):
group_subscriber = cls.find({"id": id})
if group_subscriber:
db.update(group_subscriber).values(**param)
'''
@classmethod
def find_by_subscriber_id(cls, subscriber_id):
return cls.query.filter_by(subscriber_id=subscriber_id).first()
'''
@classmethod
def find_by_notification_id(cls, id):
return cls.query.filter_by(notification_id=id).first()
@classmethod
def findById(cls, group_subscriber_id):
return cls.query.filter_by(id=group_subscriber_id).first()
@classmethod
def find(cls, **queryArguments):
return list(cls.query.filter_by(**queryArguments))
@classmethod
def findOne(cls, **queryArguments):
return cls.query.filter_by(**queryArguments).first()
def save_to_db(self):
db.session.add(self)
self.commit()
@staticmethod
def commit():
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
class Project(db.Model, Serializrable):
__tablename__ = 'project'
id = db.Column(db.Integer,
db.Sequence('id_seq'),
primary_key=True,
autoincrement=True)
pro_name = db.Column(db.String(80), nullable=False, unique=True)
res_name = db.Column(db.String(80), nullable=False)
create_name = db.Column(db.String(80), nullable=False)
finish_time = db.Column(db.String(8), nullable=False)
create_time = db.Column(db.DateTime, default=datetime.datetime.now)
company = db.Column(db.String(80))
category = db.Column(db.String(80))
postcode = db.Column(db.String(80))
contact = db.Column(db.String(80))
tele_phone = db.Column(db.String(80))
u_email = db.Column(db.String(80))
address = db.Column(db.String(256))
#检查单
#check_id = db.Column(db.Integer)
program = db.relationship('Program', backref='pro', lazy=True)
def __repr__(self):
return '<Project %r>' % self.pro_name
class PasswordRecoveryModel(db.Model):
__tablename__ = 'password_recovery'
__table_args__ = (db.UniqueConstraint('key'), )
SIZE = 32
VALID_UNTIL = timedelta(hours=1)
user_id = db.Column(db.Integer(),
db.ForeignKey('users.id'),
primary_key=True)
key = db.Column(db.String(SIZE), nullable=False)
time = db.Column(db.DateTime(), nullable=False)
def __init__(self, user_id, key=None):
self.user_id = user_id
self.time = datetime.now()
self.key = self.generate_key() if key is None else key
def json(self):
return {
'user_id': self.user_id,
'valid_until': (self.time + self.VALID_UNTIL).strftime('%H:%M:%S')
}
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
def update_from_db(self, data):
"""
Updates through a dictionary with paris of string of name of attribute and it's value. Following same structure
as json(). In case of wanting to modify an attribute of an enum use the string name of one of the values.
Will raise Exception in case of invalid enum value if it isn't contained inside the possible values of the enum.
"""
for attr, newValue in data.items():
if newValue is not None:
cls = getattr(self, attr)
# Checks if value is of the attribute that's trying to be modified is an Enum
if isinstance(cls, Enum):
# Checks if the enum doesn't contain the newValue
if newValue not in type(cls).__members__:
raise Exception(
f"Enum {type(cls).__name__} doesn't have value: {newValue}"
)
# Gets the object of the enum with same name as newValue
setattr(self, attr, type(cls)[newValue])
else:
setattr(self, attr, newValue)
db.session.commit()
def send_email(self, email, url_root):
message = f"Has sol·licitat una recuperació de contrasenya. Accedeix a {url_root}reset?key={self.key} "\
f"per canviar de contrasenya. \n L'enllaç deixarà de ser vàlid en {self.VALID_UNTIL} o si es torna " \
f"a solicitar un canvi en la mateixa compte."
send_email(email, 'Password recovery', message)
def has_time_expired(self):
return self.time + self.VALID_UNTIL < datetime.now()
@classmethod
def find_by_id(cls, user_id):
cls.clean_expired_keys()
return cls.query.filter_by(user_id=user_id).first()
@classmethod
def find_by_key(cls, key):
cls.clean_expired_keys()
return cls.query.filter_by(key=key).first()
@classmethod
def clean_expired_keys(cls):
"""
Cleans all entries that their time has expired. Will be called every time a query to the model is made.
Expiration time is decided through constant class variable VALID_UNTIL.
"""
time = datetime.now() - cls.VALID_UNTIL
cls.query.filter(cls.time <= time).delete()
@classmethod
def generate_key(cls):
"""
Generates a random key avoiding duplicating keys using the most secure random generator of the OS.
The key will be made by a combination of uppercase and lowercase letters and numbers.
"""
new_key = ''.join(random.SystemRandom().choice(string.ascii_letters +
string.digits)
for _ in range(cls.SIZE))
while cls.query.filter_by(key=new_key).count() != 0:
new_key = ''.join(
random.SystemRandom().choice(string.ascii_letters +
string.digits)
for _ in range(cls.SIZE))
return new_key
class StoreModel(db.Model
): # tells SQLAlchemy it's something to save/add to db
# tell ALchemy which table items will be stored in
__tablename__ = "stores"
# tell ALchemy which columns it will contain
# creates an index & makes it easier to search
id = db.Column(db.Integer, primary_key=True) # not used in prior code
name = db.Column(db.String(80)) # can limit size of username
# do a back reference to the ItemModel
# allows a store to see which items are in the items DB
# knows it is a many-to-1 relationship (list of items)
# items = db.relationship('ItemModel')
items = db.relationship(
'ItemModel',
lazy='dynamic') #don't create obj for each item in ItemModel yet
# self.items is no longer a list of items
def __init__(self, name):
"""
Upon creation of StoreModel, will have attribuate "name"
"""
# these items must match the columns above
# if they're not created above, they won't be stored to the DB
self.name = name
def json(self):
"""
Return JSON representation of the model.
"""
# return {'name': self.name, 'items': self.items}
# return {'name': self.name, 'items': [item.json for item in self.items]}
# with lazy='dynamic' self.items is a query builder in items table
# so until calling JSON method we're not looking into the table
return {
'name': self.name,
'items': [item.json() for item in self.items.all()]
}
@classmethod
def find_by_name(cls, name):
"""
This function acts like the GET method - will return information
from the database.
"""
return cls.query.filter_by(name=name).first() # returns ItemModel obj
def save_to_db(self): # changed from insert once SQLAlchemy got involved
"""
This function takes in an ItemModel object and saves it to the DB.
"""
# this is helpful for both update and insert
db.session.add(self) # session = coll of objs to add to DB
db.session.commit()
def del_from_db(self):
"""
This function deletes an ItemModel object from the DB.
"""
db.session.delete(self)
db.session.commit()
class EnderecoModel(db.Model):
__tablename__ = 'enderecos'
id = db.Column(db.Integer, primary_key=True)
cep = db.Column(db.String(9))
rua = db.Column(db.String(80))
bairro = db.Column(db.String(80))
cidade = db.Column(db.String(80))
estado = db.Column(db.String(50))
complemento = db.Column(db.String(40))
numero = db.Column(db.String(20))
user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
user = db.relationship('UserModel')
def __init__(self, cep, rua, bairro, numero, cidade, estado, complemento,
user_id):
self.cep = cep
self.rua = rua
self.bairro = bairro
self.cidade = cidade
self.numero = numero
self.estado = estado
self.complemento = complemento
self.user_id = user_id
def json(self):
return {
'id': self.id,
'cep': self.cep,
'rua': self.rua,
'bairro': self.bairro,
'cidade': self.cidade,
'estado': self.estado,
'numero': self.numero,
'complemento': self.complemento,
'user_id': self.user_id
}
@classmethod
def find_by_name(cls, cep):
return cls.query.filter_by(cep=cep).first()
@classmethod
def find_by_id_unique(cls, id):
return cls.query.filter_by(id=id).first()
@classmethod
def find_by_id(cls, _id):
return cls.query.filter_by(user_id=_id).all()
@classmethod
def find_all(cls):
return cls.query.all()
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
class User(db.Model):
''' Represents a User '''
required_attributes = ["email", "password"]
_id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(50))
token = db.Column(db.String,
default=key_generator,
nullable=False,
unique=True)
email = db.Column(db.String(50), nullable=False, unique=True)
auth_email = db.Column(db.String(100), db.ForeignKey('user_auth.email'))
auth = db.relationship('UserAuth', foreign_keys=[auth_email])
admin = db.Column(db.Boolean, default=False, nullable=False)
owned_problems = db.relationship('Problem')
owned_courses = db.relationship('Course')
course_participations = db.relationship('CourseParticipation')
solutions = db.relationship('Solution')
@property
def solution_qnt(self):
return len(self.solutions)
@property
def courses(self):
return [
participation.course
for participation in self.course_participations
]
problem_service = ProblemService()
api_fields = {
"id":
fields.Integer(attribute='_id'),
"token":
fields.String,
"email":
fields.String,
"ownedProblems":
fields.Nested(Problem.api_fields, attribute='owned_problems'),
"name":
fields.String
}
def add_problem(self, name, description, tip, publish, tests, tags=None):
problem = self.problem_service.create_problem(name, description, tip,
tags)
self.owned_problems.append(problem)
db.session.add(problem)
problem.add_tests(tests)
if publish:
self.problem_service.create_publish_request(problem)
db.session.commit()
return problem
def try_solution(self, problem_key, code, tests):
solution = self.problem_service.create_solution(
self, problem_key, code, tests)
for participation in self.course_participations:
participation.course.add_solution(solution)
db.session.commit()
return solution
def create_course(self, name, description, language=None, problems=None):
if problems:
course = Course(name=name,
description=description,
language=language,
_problems=problems)
else:
course = Course(name=name,
description=description,
language=language)
self.owned_courses.append(course)
db.session.add(course)
db.session.commit()
course.add_member(self)
return course
class Author_Relationships(db.Model):
__tablename__ = 'author_relationships'
# AuthorRelationship_id = db.Column(db.Integer, primary_key=True)
AuthorRelationship_id = db.Column(db.String(100), primary_key=True)
authorServer1_id = db.Column(db.Integer)
author1_id = db.Column(db.String(100))
author1_name = db.Column(db.String(60))
authorServer2_id = db.Column(db.Integer)
author2_id = db.Column(db.String(100))
author2_name = db.Column(db.String(60))
relationship_type = db.Column(
db.Integer
) # if 1, author1 is following author 2, if 2 then author2 is following author1, if 3 then both are friends
# db.PrimaryKeyConstraint(authorServer1_id, author1_id)
# db.PrimaryKeyConstraint(authorServer2_id, author2_id)
def __new__(cls, datum=None):
"""
Input: For datum, see comments in __init__
Description:
Checks whether the keys are inside datum dictionary.
If not found, then it returns None
"""
# When the DB will query and retrieve objects, __new__ will have to called to create the objects and datum wont be provided
if datum == None:
return super(Author_Relationships, cls).__new__(cls)
if ('AuthorRelationship_id' and 'authorServer1_id' and 'author1_id'
and 'authorServer2_id' and 'author2_id'
and 'relationship_type') not in datum.keys():
return None
else:
return super(Author_Relationships, cls).__new__(cls)
def __init__(self, datum=None):
"""
Input:
datum is a dictionary with keys as column names and values as their corresponding values.
eg, datum['author1_id']=3, etc
Description:
This constructor sets the values of fields based on datum dictionary. If any field
is missing from datum, its default value will be inserted.
TODO:
"""
if datum == None:
self.AuthorRelationship_id = uuid.uuid4().hex
return
self.AuthorRelationship_id = datum['AuthorRelationship_id']
self.author1_id = datum['author1_id']
self.author2_id = datum['author2_id']
self.authorServer1_id = datum['authorServer1_id']
self.authorServer2_id = datum['authorServer2_id']
self.relationship_type = datum['relationship_type']
if "author1_name" in datum.keys():
self.author1_name = datum["author1_name"]
if "author2_name" in datum.keys():
self.author2_name = datum["author2_name"]
def insert(self):
"""
Call this method for inserting an Author_Relationships row into the DB.
Before inserting, it makes sure that the authors, servers are all present in their respective DB.
"""
willInsert = True
if Authors.query.filter(
Authors.author_id == self.author1_id).all() == []:
willInsert = False
if Authors.query.filter(
Authors.author_id == self.author2_id).all() == []:
willInsert = False
if Servers.query.filter(
Servers.server_index == self.authorServer1_id).all() == []:
willInsert = False
if Servers.query.filter(
Servers.server_index == self.authorServer2_id).all() == []:
willInsert = False
if willInsert is True:
db.session.add(self)
db.session.commit()
return True #Returns true as it succesfully inserted the row.
return False #In case insertion failed
def updateRow(self):
"""
Call this method to update any changes made to any rows in the DB, such as changing the relationship type.
"""
self.insert()
@staticmethod
def deleteRowsByQuery(query_param):
"""
Read query method's description for query_param.
This method uses static method query for first retrieving a set of rows that matches the query given in query_param
and then deletes
"""
rows = Author_Relationships.query(query_param)
if rows == []:
return
for row in rows:
db.session.delete(row)
db.session.commit()
@staticmethod
def query(query_param):
"""
query param is a dictionary containing query information.
Types of queries:
1) query_param['server_author_1']=[server1_obj, author1_obj]
query_param['server_author_2']=[server2_obj, author2_obj]
2) query_param['server_author_1']=[server1_obj, author1_obj]
3) query_param['server_author_1']=[server1_obj, author1_obj]
query_param['relationship_type']=relationship_type value
4) query_param['server_author_2']=[server2_obj, author2_obj]
5) query_param['server_author_2']=[server2_obj, author2_obj]
query_param['relationship_type']=relationship_type value
6) query_param['relationship_type']=relationship_type value
7) query_param={} // This gives back all the rows
8) query_param["author_ids"]=[author1_id, author2_id] #Add test later
9) query_param["server_author_id1"]=[server1_index, author1_id, type]
10) query_param["server_author_id2"]=[server2_index, author2_id, type]
11) query_param["server_author_id1"]=[server1_index, author1_id]
query_param["server_author_id2"]=[server2_index, author2_id]
"""
if query_param == {}:
return db.session.query(Author_Relationships).all()
if "areFollowers" in query_param.keys():
author1_id, author2_id = query_param["areFollowers"]
results = db.session.query(Author_Relationships).filter(
Author_Relationships.author1_id == author1_id,
Author_Relationships.author2_id == author2_id,
Author_Relationships.relationship_type == 1).all()
results = db.session.query(Author_Relationships).filter(
Author_Relationships.author1_id == author1_id,
Author_Relationships.author2_id == author2_id,
Author_Relationships.relationship_type == 2).all()
return results
if "areFriends" in query_param.keys():
author1_id, author2_id = query_param["areFriends"]
results = db.session.query(Author_Relationships).filter(
Author_Relationships.author1_id == author1_id,
Author_Relationships.author2_id == author2_id,
Author_Relationships.relationship_type == 3).all()
return results
if ("server_author_id1"
in query_param.keys()) and ("server_author_id2"
in query_param.keys()):
server1_id, author1_id = query_param["server_author_id1"]
server2_id, author2_id = query_param["server_author_id2"]
results = db.session.query(Author_Relationships).filter(
Author_Relationships.author1_id == author1_id,
Author_Relationships.author2_id == author2_id,
Author_Relationships.authorServer1_id == server1_id,
Author_Relationships.authorServer2_id == server2_id,
).all()
print "printing all!"
print db.session.query(Author_Relationships).all()
return results
if "server_author_id1" in query_param.keys():
server1_id, author1_id, relationship_type = query_param[
"server_author_id1"]
results = db.session.query(Author_Relationships).filter(
Author_Relationships.author1_id == author1_id,
Author_Relationships.authorServer1_id == server1_id,
Author_Relationships.relationship_type ==
relationship_type).all()
return results
if "server_author_id2" in query_param.keys():
server2_id, author2_id, relationship_type = query_param[
"server_author_id2"]
results = db.session.query(Author_Relationships).filter(
Author_Relationships.author2_id == author2_id,
Author_Relationships.authorServer2_id == server2_id,
Author_Relationships.relationship_type ==
relationship_type).all()
return results
if ("server_author_1"
in query_param.keys()) and ("server_author_2"
in query_param.keys()):
server1, author1 = query_param["server_author_1"]
server2, author2 = query_param["server_author_2"]
results = db.session.query(Author_Relationships).filter(
Author_Relationships.author1_id == author1,
Author_Relationships.author2_id == author2,
Author_Relationships.authorServer1_id == server1,
Author_Relationships.authorServer2_id == server2).all()
return results
###### For querying with author1
if "server_author_1" in query_param.keys():
server1, author1 = query_param["server_author_1"]
if "relationship_type" in query_param.keys():
relationship_type = query_param["relationship_type"]
results = db.session.query(Author_Relationships).filter(
Author_Relationships.author1_id == author1,
Author_Relationships.authorServer1_id == server1,
Author_Relationships.relationship_type ==
relationship_type).all()
else:
results = db.session.query(Author_Relationships).filter(
Author_Relationships.author1_id == author1,
Author_Relationships.authorServer1_id == server1,
).all()
return results
###### For querying with author2
if "server_author_2" in query_param.keys():
server2, author2 = query_param["server_author_2"]
if "relationship_type" in query_param.keys():
relationship_type = query_param["relationship_type"]
results = db.session.query(Author_Relationships).filter(
Author_Relationships.author2_id == author2,
Author_Relationships.authorServer2_id == server2,
Author_Relationships.relationship_type ==
relationship_type).all()
else:
results = db.session.query(Author_Relationships).filter(
Author_Relationships.author2_id == author2,
Author_Relationships.authorServer2_id == server2,
).all()
return results
###### For a given relationship_type get all the rows.
if "relationship_type" in query_param.keys():
relationship_type = query_param["relationship_type"]
results = db.session.query(Author_Relationships).filter(
Author_Relationships.relationship_type ==
relationship_type).all()
return results
print "returning None"
return None
class UserAuth(db.Model):
email = db.Column(db.String(100), primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('user._id'))
user = db.relationship('User', foreign_keys=[user_id])
class ItemModel(db.Model):
__tablename__ = 'items'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80))
price = db.Column(db.Float(precision=2))
store_id = db.Column(
db.Integer,
db.ForeignKey('stores.id')) # set foreign_key of id(stores) table
store = db.relationship('StoreModel') # works as a JOIN in SQLAlchemy
def __init__(self, name, price,
store_id): # there is_no "id" parameter, it won't be used
self.name = name
self.price = price
self.store_id = store_id
def json(self
): # to return a_basic_json_representation(ItemModel)..i.e.item
return {
'name': self.name,
'price': self.price
} # that_is a_dictionary representing_an_item
@classmethod # should_be_a_classMethod BCZ it_returns an_object_of_type_"ItemModel"
def find_by_name(cls, name): # as supposed to a DICTIONARY
# returns the "ItemModel_object"
# return ItemModel.query.filter_by(name=name).first() # SELEST * FROM items WHERE name=name LIMIT_1
#/.filetr_by(id=1)
return cls.query.filter_by(name=name).first()
# connection = sqlite3.connect('data.db')
# cursor = connection.cursor()
# query = "SELECT * FROM items WHERE name=?"
# result = cursor.execute(query, (name,))
# row = result.fetchone()
# connection.close()
# if row:
# # return {'item': {'name': row[0], 'price': row[1]}} # returns a Dictionary
# return cls(*row) # cls(row[0], row[1]) # return an_object_of_type_"ItemModel" instead_of_dictionary With_arg_unpacking
# def insert(self): # pass "item" as_it_self as_an_argument_of_function_of_ItemModel
# connection = sqlite3.connect('data.db')
# cursor = connection.cursor()
# query = "INSERT INTO items VALUES (?, ?)"
# # cursor.execute(query, (item['name'], item['price']))
# cursor.execute(query, (self.name, self.price))
# connection.commit()
# connection.close()
def save_to_db(self): # contains_for_both_INSERT_and_UPDATE
db.session.add(self)
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
class StoreModel(db.Model):
__tablename__ = 'stores'
# having an id for an entity is useful
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80))
#back reference does the opposite
# allows a store to see which items are in the items database with a store id
# equal to its own id
# this is saying we have a relationship with ItemModel, and then SQLAlchemy says
# okay there is a relationship there. whats the relationship?
# it goes into the item model and finds the store id there and says
# "aha there is a store id in the item, which means that 1 item is related to a store.
# and thus there could be more than 1 item related to the same store"
# hence it knwos the store variable in item.oy is a single store (only one store an item is related to)
# but the items variable in the store.py (below) can be many items so therefore items is a list of
# item models. (many to 1). can be many items with the same store id
# Whenever we create a StoreModel, this relationship is also created
# meaning, if we have many stores and we have many items, whenever we create a StoreModel
# we are going to go and create an object for each item in the db that matches that store id
# now if we have a few items that is fine, but alot can be expensive
# so to avoid that, that we can tell SQLAlchemy to not to that i.e. do not go into the items
# table and create an object for each item YET.
# we do that by lazy = 'dynamic'.
items = db.relationship('ItemModel', lazy='dynamic')
def __init__(self, name):
self.name = name
# returns JSON representation of a model, basically a dictionary
# Because we used lazy = 'dynamic', we have to do self.items.all()
# why? when we use lazy = 'dynamic', self.items is no longer a list of items
# instead it is a query builder that has the ability to look into the items table
# this means that until we call the json method, we are not looking into the table
# which means creating stores is very simple
# However it also that everytime we call the json method, we have to go in to the
# table so then it will be slower that if we create a store, load up all the items and then call
# the json method manytimes for free essentially.
# so if we use lazy = 'dynamic', everytime we call the json method, we have to go into the table
# so then that is slower. so there is a trade off there between the speed of creation of the store
# and speed of calling the json method, thats upto you which to call
# we stick with this in this case, cuz our StoreModel will get created when we want to access the data
# (thats how we are doing it in the Store Resource)
def json(self):
return {
'name': self.name,
'items': [item.json() for item in self.items.all()]
}
@classmethod
def find_by_name(cls, name):
return cls.query.filter_by(name=name).first()
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
class ItemModel(db.Model):
__tablename__ = 'geometry_columns'
id = db.Column(db.Integer, primary_key=True)
idm = db.Column(db.String(80))
date = db.Column(db.String(80))
name = db.Column(db.String(80))
telephone = db.Column(db.String(80))
email = db.Column(db.String)
categorie = db.Column(db.String)
toelichting = db.Column(db.String)
XCoordinaat = db.Column(db.Float)
YCoordinaat = db.Column(db.Float)
image = db.Column(db.String)
status = db.Column(db.String)
nearestaddress = db.Column(db.String)
nearestpostal = db.Column(db.String)
nearestplace = db.Column(db.String)
def __init__(self, idm, date, name, telephone, email, categorie,
toelichting, XCoordinaat, YCoordinaat, image, status,
nearestaddress, nearestpostal, nearestplace):
self.idm = idm
self.date = date
self.name = name
self.telephone = telephone
self.email = email
self.categorie = categorie
self.toelichting = toelichting
self.XCoordinaat = XCoordinaat
self.YCoordinaat = YCoordinaat
self.image = image
self.status = status
self.nearestaddress = nearestaddress
self.nearestpostal = nearestpostal
self.nearestplace = nearestplace
def json(self):
#return{'id':self.idm,'date':self.date,'name':self.name, 'telephone':self.telephone,'email':self.email,'categorie':self.categorie,'toelichting':self.toelichting,'XCoordinaat':self.XCoordinaat,'YCoordinaat':self.YCoordinaat,'image':self.image,'status':self.status,'nearestaddress':self.nearestaddress,'nearestpostal':self.nearestpostal,'nearestplace':self.nearestplace}
#FeatureCollection(data['features'])
#return (data1)
return {
'type': 'Feature',
'id': self.idm,
"properties": {
'date': self.date,
"name": self.name,
'telephone': self.telephone,
'email': self.email,
'categorie': self.categorie,
'toelichting': self.toelichting,
'image': self.image,
'status': self.status,
'nearestaddress': self.nearestaddress,
'nearestpostal': self.nearestpostal,
'nearestplace': self.nearestplace
},
'geometry': {
'type': 'Point',
'coordinates': [self.XCoordinaat, self.YCoordinaat]
}
}
@classmethod
def find_by_name(cls, name):
return cls.query.filter_by(
name=name).first() #SELECT * FROM items WHERE name=name LIMIT 1
@classmethod
def find_by_categorie(cls, categorie):
return cls.query.filter_by(
categorie=categorie
) #SELECT * FROM items WHERE categorie=categorie LIMIT 1
def save_to_db(self):
db.session.add(self)
db.session.commit()
#@classmethod
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
class TransactionModel(db.Model):
__tablename__ = 'transaction'
id = db.Column('transaction_id', db.Integer, primary_key=True)
categoryId = db.Column('category_id', db.Integer,
db.ForeignKey('category.category_id'))
transactionDate = db.Column(DATETIME, nullable=False, default=func.now())
description = db.Column('description', db.String(32))
amount = db.Column('amount', db.Numeric, nullable=False)
def __init__(self, param_dict):
self.categoryId = param_dict['categoryId']
self.transactionDate = param_dict['transactionDate']
self.description = param_dict['description']
self.amount = param_dict['amount']
@classmethod
def find_by_id(cls, _id):
return cls.query.filter_by(id=_id).first()
@classmethod
def find_by_params(cls, queryParams):
query = cls.query
if queryParams['startDate']:
query = query.filter(
func.date(TransactionModel.transactionDate) >= func.date(
queryParams['startDate']))
if queryParams['endDate']:
query = query.filter(
func.date(TransactionModel.transactionDate) <= func.date(
queryParams['endDate']))
if queryParams['categoryName']:
cat = CategoryModel.find_by_name(queryParams['categoryName'])
if cat:
query = query.filter(TransactionModel.categoryId == cat.id)
else:
return []
return query
@classmethod
def find_all(cls):
return cls.query.all()
def update(self):
db.session.query(TransactionModel).filter(TransactionModel.id == self.id) \
.update(
{
'categoryId': self.categoryId,
'transactionDate': self.transactionDate,
'description': self.description,
'amount': self.amount
}
)
db.session.commit()
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
def json(self):
resp = {
'id':
self.id,
'categoryId':
self.categoryId,
'transactionDate':
self.transactionDate.replace(microsecond=0).isoformat()
if self.transactionDate else datetime.now().replace(
microsecond=0).isoformat(),
'description':
self.description,
'amount':
"{0:.2f}".format(float(self.amount))
}
if self.categoryId:
cat = CategoryModel.find_by_id(self.categoryId)
if cat:
resp['categoryName'] = cat.name
return resp
class ItemModel(db.Model):
# specify table name and columns
__tablename__ = 'items'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80)) # 80 character maximum to limit the size
price = db.Column(
db.Float(precision=2)) # 2 numbers after the decimal point
store_id = db.Column(
db.Integer, db.ForeignKey('stores.id')
) # add a store id to link the items and its belonging store
store = db.relationship('StoreModel') # how SQLAlchemy does join
def __init__(self, name, price, store_id):
self.name = name
self.price = price
self.store_id = store_id
# return a JSON representation of model, basically a dictionary
def json(self):
return {'name': self.name, 'price': self.price}
# move following methods from item.py in resource to here, models, since they don't belong to a resource
# keep this as class method since it will return a dictionary other than a model object
@classmethod
def find_by_name(cls, name):
### use SQLAlchemy
# connection = sqlite3.connect('data.db')
# cursor = connection.cursor()
#
# query = "SELECT * FROM items WHERE name = ?"
# result = cursor.execute(query, (name,))
# row = result.fetchone()
# connection.close()
#
# if row:
# return cls(*row) # parse all elements in row to a item model
###
# SQLAlchemy will transit a row to ItemModel if it can, .query is using a query builder
# return ItemModel.query.filter_by(name = name).first()
return cls.query.filter_by(
name=name).first() # SELECT * FROM items WHERE name = name LIMIT 1
# return a item model object
# modify following 2 methods as not class method since they can use item model object directly
def save_to_db(self):
### use SQLAlchemy
# connection = sqlite3.connect('data.db')
# cursor = connection.cursor()
#
# query = "INSERT INTO items VALUES (?, ?)"
# cursor.execute(query, (self.name, self.price))
#
# connection.commit()
# connection.close()
###
# save the model into database, SQLAlchemy can automatically translate model to row in a database, so we just tell it the object - self
db.session.add(self)
db.session.commit()
# it can update so we change this method to do insertion and updating, then we don't need another separate update method but we create another delete_from_db method for better use
### don't need
# def update(self):
# connection = sqlite3.connect('data.db')
# cursor = connection.cursor()
#
# query = "UPDATE items SET price = ? WHERE name = ?"
# cursor.execute(query, (self.price, self.name)) # match the values in-order
#
# connection.commit()
# connection.close()
###
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
class RuntimeModel(db.Model):
__tablename__ = 'runtimes'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80))
type = db.Column(db.String(80))
user_name = db.Column(db.String(30))
user_password = db.Column(db.String(30))
user_hostname = db.Column(db.String(30))
dockerfile = db.Column(db.String(5000))
image_name = db.Column(db.String(1000))
error = db.Column(db.String(1000))
#endpoint_id = db.Column(db.Integer, db.ForeignKey('endpoints.id'))
#endpoint = db.relationship('EndpointModel') # now every RuntimeModel has a property endpoint which matches with a endpoint_id
def __init__(self,
name,
type,
user_name=None,
user_password=None,
user_hostname=None,
dockerfile=None,
error=None):
self.name = name
self.type = type
self.user_name = user_name
self.user_password = user_password
self.user_hostname = user_hostname
self.dockerfile = dockerfile
self.error = error
def json(self):
if (self.error):
return {
'id': self.id,
'type': self.type,
'name': self.name,
'error': self.error
}
return {'id': self.id, 'type': self.type, 'name': self.name}
@classmethod
def find_by_name(cls, name):
return RuntimeModel.query.filter_by(name=name).first()
@classmethod
def find_by_id(cls, id):
return RuntimeModel.query.filter_by(id=id).first()
def insert_or_update(self):
db.session.add(self)
db.session.commit()
def delete(self):
db.session.delete(self)
db.session.commit()
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True)
email = db.Column(db.String(120), unique=True)
class AppointmentModel(db.Model, BaseMethods):
__tablename__ = 'appointments'
id = db.Column(db.Integer, primary_key=True)
# Relationships
doctorId = db.Column(db.Integer,
db.ForeignKey('doctors.id',
onupdate='CASCADE',
ondelete='CASCADE'),
nullable=False)
doctor = db.relationship('DoctorModel')
patientId = db.Column(db.Integer,
db.ForeignKey('patients.id',
onupdate='CASCADE',
ondelete='CASCADE'),
nullable=False)
patient = db.relationship('PatientModel')
appointmentDate = db.Column(db.DateTime, nullable=False)
reason = db.Column(db.String(200))
createdAt = db.Column(db.DateTime, nullable=False)
canceledAt = db.Column(db.DateTime)
updatedOn = db.Column(db.DateTime)
status = db.Column(db.String(3), server_default='ACT')
def __init__(self, doctor_id, patient_id, appointment_date, reason,
created_at, canceled_at, updated_on, status):
super(AppointmentModel, self).__init__()
self.doctorId = doctor_id
self.patientId = patient_id
self.appointmentDate = (datetime.now() + timedelta(hours=2)) if (
appointment_date is None) else appointment_date
self.reason = reason
self.createdAt = datetime.now().strftime('%Y-%m-%d %H:%M:%S') if (
created_at is None) else created_at
self.canceledAt = canceled_at
self.updatedOn = datetime.now().strftime('%Y-%m-%d %H:%M:%S') if (
updated_on is None) else updated_on
self.status = status
def __repr__(self):
return 'Appointment: %r' % self.reason
def json(self, role_id):
if role_id == 1:
return {
'id': self.id,
'patient': self.patient.json(),
'appointmentDate': self.appointmentDate,
'reason': self.reason,
'canceledAt': self.canceledAt,
'status': self.status
}
else:
return {
'id': self.id,
'doctor': self.doctor.json(),
'appointmentDate': self.appointmentDate,
'reason': self.reason,
'canceledAt': self.canceledAt,
'status': self.status
}
@classmethod
def find_by_patient_id(cls, patient_id):
return cls.query.filter_by(patientId=patient_id).all()
@classmethod
def find_by_doctor_id(cls, doctor_id):
return cls.query.filter_by(doctorId=doctor_id).all()
@classmethod
def find_by_appointment_date(cls, appointment_date):
return cls.query.filter_by(appointmentDate=appointment_date).first()
class ItemModel(db.Model):
__tablename__ = 'items'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80))
price = db.Column(db.String(80))
store_id = db.Column(db.Integer, db.ForeignKey('stores.id'))
store = db.relationship('StoreModel')
def __init__(self, name, price, store_id):
self.name = name
self.price = price
self.store_id = store_id
def __repr__(self):
return f"ItemModel({self.name},{self.price})"
def json(self):
return {
'name': self.name,
'price': self.price,
'store_id': self.store_id
}
@classmethod
def get_by_name(cls, name):
"""connecton = sqlite3.connect('data.db')
cursor = connecton.cursor()
query = "SELECT * from items where Name =?"
result = cursor.execute(query, (name,))
row = result.fetchone()
if row:
return cls(*row)"""
return cls.query.filter_by(name=name).first()
def save_to_db(self):
"""connecton = sqlite3.connect('data.db')
cursor = connecton.cursor()
query = "INSERT into items values (?,?) "
cursor.execute(query, (self.name, self.price))
connecton.commit()
connecton.close() """
db.session.add(self)
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
class UserModel(db.Model):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True, nullable=False)
email = db.Column(db.String(120), unique=True, nullable=False)
passwd_hash = db.Column(db.String(120), nullable=False)
registered_on = db.Column(db.DateTime, nullable=False)
confirmed = db.Column(db.Boolean, nullable=False, default=False)
confirmed_on = db.Column(db.DateTime, nullable=True)
trips = db.relationship("TripsModel",
passive_deletes="all",
backref="parent")
#images = db.relationship("ImagesModel", passive_deletes="all", backref="parent")
def __init__(self,
email,
confirmed=False,
registered_on=None,
confirmed_on=None):
# self.id = id
self.email = email
self.username = email.split('@')[0]
self.passwd_hash = None
if registered_on is None:
self.registered_on = datetime.datetime.now()
else:
self.registered_on = registered_on
self.confirmed = confirmed
self.confirmed_on = confirmed_on
# set password hash
def set_password(self, password):
self.passwd_hash = generate_password_hash(password)
# set username
def set_username(self):
username_taken = UserModel.check_user(username=self.username)
if username_taken:
self.username += str(random.randint(0, 1000))
self.set_username()
else:
return 'Username set'
@classmethod
def is_password(cls, hashed_passwd, password):
return check_password_hash(hashed_passwd, password)
@classmethod
def check_user(cls, username=None, email=None):
if username:
return cls.query.filter_by(username=username).first()
if email:
return cls.query.filter_by(email=email).first()
@classmethod
def delete_all(cls):
try:
num_rows_deleted = db.session.query(cls).delete()
db.session.commit()
return {'message': '{} row(s) deleted'.format(num_rows_deleted)}
except:
return {'message': 'Something went wrong'}
@classmethod
def find_user_id(cls, username):
user_record = cls.query.filter_by(username=username).first()
return user_record.id
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete(self):
db.session.delete(self)
db.session.commit()
from flask_wtf import FlaskForm
from wtforms import StringField, TextAreaField
from wtforms.validators import DataRequired
from db import db
table = db.Table(
"sign_ins",
db.Column("first_name", db.String(length=255)),
db.Column("last_name", db.String(length=255)),
db.Column("street_and_house_number", db.Text),
db.Column("plz_and_city", db.Text),
db.Column("phone_number", db.String(length=255)),
db.Column("signed_in_at", db.DateTime),
)
class Form(FlaskForm):
first_name = StringField(
"Vorname",
validators=[DataRequired(message="Bitte trag deinen Vornamen ein")],
)
last_name = StringField(
"Nachname",
validators=[DataRequired(message="Bitte trag deinen Nachnamen ein")],
)
street_and_house_number = StringField(
"Straße und Hausnummer",
validators=[
DataRequired(message="Bitte trag deine Straße und Hausnummer ein")
],
class UserModel(db.Model):
__tablename__ = "users"
id = db.Column(db.Integer, primary_key=True, autoincrement=False)
#username = db.Column(db.String(80), nullable=True, unique=True)
password = db.Column(db.String(200), nullable=True)
temporary_password = db.Column(db.String(200), nullable=True)
email = db.Column(db.String(80), nullable=False, unique=True)
phone = db.Column(db.String(15), unique=True, nullable=True)
name = db.Column(db.String(80))
birth_date = db.Column(db.Date, nullable=True)
gender = db.Column(db.String(1))
confirmation = db.relationship("ConfirmationModel",
lazy="dynamic",
cascade="all, delete-orphan")
@property
def most_recent_confirmation(self) -> "ConfirmationModel":
# ordered by expiration time (in descending order)
return self.confirmation.order_by(db.desc(
ConfirmationModel.expire_at)).first()
@classmethod
def find_by_username(cls, username: str) -> "UserModel":
return cls.query.filter_by(username=username).first()
@classmethod
def find_by_email(cls, email: str) -> "UserModel":
return cls.query.filter_by(email=email).first()
@classmethod
def find_by_phone(cls, phone: str) -> "UserModel":
return cls.query.filter_by(phone=phone).first()
@classmethod
def find_by_id(cls, _id: int) -> "UserModel":
return cls.query.filter_by(id=_id).first()
def send_confirmation_email(self) -> Response:
# configure e-mail contents
subject = "Registration Confirmation"
link = request.url_root[:-1] + url_for(
"confirmation", confirmation_id=self.most_recent_confirmation.id)
# string[:-1] means copying from start (inclusive) to the last index (exclusive), a more detailed link below:
# from `http://127.0.0.1:5000/` to `http://127.0.0.1:5000`, since the url_for() would also contain a `/`
# https://stackoverflow.com/questions/509211/understanding-pythons-slice-notation
text = f"Please click the link to confirm your registration: {link}"
html = f"<html>Please click the link to confirm your registration: <a href={link}>link</a></html>"
# send e-mail with MailGun
return Mailgun.send_email([self.email], subject, text, html)
def send_sms(self) -> MessageInstance:
text = gettext("user_sms_text_code").format(
str(self.most_recent_confirmation.code))
return Twilio.send_sms(number="+351" + self.phone, body=text)
def save_to_db(self) -> None:
db.session.add(self)
db.session.commit()
def delete_from_db(self) -> None:
db.session.delete(self)
db.session.commit()
class PersonModel(db.Model):
__tablename__ = 'Persons'
person_id = db.Column(db.Integer, primary_key=True)
id = db.Column(UUID(as_uuid=True), default=uuid.uuid4, nullable=False)
first_name = db.Column(db.String(100), nullable=False)
middle_name = db.Column(db.String(100))
last_name = db.Column(db.String(100), nullable=False)
email = db.Column(db.String(1000), nullable=False)
age = db.Column(db.Integer, nullable=False)
version = db.Column(db.Integer, nullable=False)
latest = db.Column(db.Boolean, nullable=False)
def __init__(self, id, first_name, middle_name, last_name, email, age, version, latest):
self.id = id
self.first_name = first_name
self.middle_name = middle_name
self.last_name = last_name
self.email = email
self.age = age
self.version = version
self.latest = latest
def __repr__(self):
return "PersonModel(person_id=%s, id=%s, first_name=%s, middle_name=%s, last_name=%s, email=%s, age=%s, version=%s, latest=%s)" \
% (self.person_id, self.id, self.first_name, self.middle_name, self.last_name, self.email, self.age, self.version, self.latest)
def json(self):
return {
"first_name": self.first_name,
"last_name": self.last_name,
"middle_name": self.middle_name,
"age": self.age,
"email": self.email,
"id": self.id,
"version": self.version
}
@classmethod
def find_by_id(cls, id) -> "PersonModel":
return cls.query.filter_by(id=id, latest=True).first()
@classmethod
def find_by_id_and_version(cls, id, version) -> "PersonModel":
return cls.query.filter_by(id=id, version=version).first()
@classmethod
def is_unique(cls, first_name, middle_name, last_name) -> "PersonModel":
result = cls.query.filter_by(first_name=first_name, middle_name=middle_name, last_name=last_name).first()
return not result
@classmethod
def find_all(cls) -> List["PersonModel"]:
return cls.query.filter_by(latest=True).all()
def save_to_db(self) -> None:
db.session.add(self)
db.session.commit()
def delete_from_db(self) -> None:
db.session.delete(self)
db.session.commit()
