def update_review(self, review):
tape_id = review['tape_id']
user_id = review['user_id']
review_table = self.tables.get_review_table()
_log.info(review)
validation = self.validate_review(review)
if validation != 'valid':
return validation
if self.check_reveiew_exist(review) is None:
response = {
'msg': 'Review does not exist for this tape',
'code': 400
}
return response
self.connection.execute(
review_table.update().where(and_(review_table.c.tape_id == tape_id,
review_table.c.user_id == user_id )), review)
review_id_query = select([review_table.c.id]).where(and_(
review_table.c.tape_id == tape_id, review_table.c.user_id == user_id
))
review_id = self.connection.execute(review_id_query).scalar()
review['id'] = review_id
response = {
'msg': json.dumps(review),
'code': 200
}
return response
def init_database(self):
_log.info('Initializing to database')
self.tables = Tables(self.db, self.conn, self.meta)
# Tables created here if they don't exist
self.tables.create_tables()
# Tables populated only if they are empty
self.tables.populate_tables()
def on_loan_for_and_at(self, loan_date, loan_duration):
query = 'SELECT * FROM users JOIN borrows ON users.id = borrows.user_id WHERE (borrows.borrow_date + \''+ str(loan_duration) + 'day\'::interval) < \''+ str(loan_date)+'\'AND (borrows.return_date > \''+str(loan_date)+'\' OR borrows.return_date IS NULL)'
_log.info(query)
loan_res = self.connection.execute(query)
users = []
for res in loan_res:
user = User(input_tuple=res)
users.append(user.return_as_dict())
users = [dict(t) for t in {tuple(d.items()) for d in users}]
return users
def create_tables(self):
_log.info('Creating tables')
## USERS table schema
users_table = Table(
'users', self.meta,
Column('id',
Integer,
Sequence('users_id_seq', metadata=self.meta),
primary_key=True), Column('name', String, nullable=False),
Column('email', String), Column('phone', String),
Column('address', String))
users_table.create(self.db, checkfirst=True)
self.users_table = users_table
## TAPES table schema
tapes_table = Table(
'tapes', self.meta,
Column('id',
Integer,
Sequence('tapes_id_seq', metadata=self.meta),
primary_key=True), Column('title', String, nullable=False),
Column('director', String), Column('type', String),
Column('release_date', Date), Column('eidr', String))
tapes_table.create(self.db, checkfirst=True)
self.tapes_table = tapes_table
## BORROW table schema
borrow_table = Table(
'borrows', self.meta,
Column('id',
Integer,
Sequence('borrows_id_seq', metadata=self.meta),
primary_key=True),
Column('user_id', Integer, ForeignKey('users.id'), nullable=False),
Column('tape_id', Integer, ForeignKey('tapes.id'), nullable=False),
Column('borrow_date', Date), Column('return_date', Date))
borrow_table.create(self.db, checkfirst=True)
self.borrow_table = borrow_table
## REVIEW table schema
review_table = Table(
'reviews', self.meta,
Column('id',
Integer,
Sequence('reviews_id_seq', metadata=self.meta),
primary_key=True),
Column('user_id', Integer, ForeignKey('users.id'), nullable=False),
Column('tape_id', Integer, ForeignKey('tapes.id'), nullable=False),
Column('rating', Integer, nullable=False))
review_table.create(self.db, checkfirst=True)
self.review_table = review_table
def on_loan_for_and_at(self, loan_date, loan_duration):
query = 'SELECT * FROM tapes JOIN borrows ON tapes.id = borrows.tape_id WHERE (borrows.borrow_date + \'' + str(
loan_duration) + 'day\'::interval) < \'' + str(
loan_date) + '\'AND (borrows.return_date > \'' + str(
loan_date) + '\' OR borrows.return_date IS NULL)'
_log.info(query)
loan_res = self.connection.execute(query)
tapes = []
for res in loan_res:
tape = Tape(input_tuple=res)
tapes.append(tape.return_as_dict())
tapes = [dict(t) for t in {tuple(d.items()) for d in tapes}]
return tapes
def delete_and_populate(self):
trans = self.conn.begin()
# Deletes from all tables
for table in reversed(self.meta.sorted_tables):
delete_string = table.delete()
_log.info(delete_string)
self.conn.execute(delete_string)
# Resets the counting sequence
self.conn.execute('ALTER SEQUENCE users_id_seq RESTART WITH 1')
self.conn.execute('ALTER SEQUENCE tapes_id_seq RESTART WITH 1')
self.conn.execute('ALTER SEQUENCE borrows_id_seq RESTART WITH 1')
self.conn.execute('ALTER SEQUENCE reviews_id_seq RESTART WITH 1')
trans.commit()
self.tables.populate_tables()
return ('Tables Deleted and repopulated!')
def __init__(self):
_log.info('Connecting to database')
# Get the config for the database
with open(os.environ['DB_CONFIG'], 'r') as file:
config = json.load(file)
# Gets the connection varibles from the config file
user = config['POSTGRES_USER']
db = config['POSTGRES_DB']
password = config['POSTGRES_PASSWORD']
host = config['POSTGRES_HOST']
connection_string = 'postgresql+psycopg2://' + \
user + ':' + password + '@' + host + '/' + db
_log.info(connection_string)
self.db = create_engine(connection_string)
self.conn = self.db.connect()
self.meta = MetaData(self.db)
self.connection_string = connection_string
def populate_tables(self):
# Data base is only populated if it's empty before
if (self.utils.count_rows(self.users_table) == 0
and self.utils.count_rows(self.tapes_table) == 0
and self.utils.count_rows(self.borrow_table) == 0):
_log.info('Populating tables')
json_service = Json_service()
if len(json_service.get_users()) == 0:
json_service.read_json()
for user in json_service.get_users():
insert_query = insert(self.users_table).values(
name=user.name,
email=user.email,
phone=user.phone,
address=user.address)
self.connection.execute(insert_query)
for tape in json_service.get_tape_list():
insert_query = insert(self.tapes_table).values(
title=tape.title,
director=tape.director,
type=tape.type,
release_date=tape.release_date,
eidr=tape.eidr)
self.connection.execute(insert_query)
for borrow in json_service.get_borrow_list():
insert_query = insert(self.borrow_table).values(
user_id=borrow.user_id,
tape_id=borrow.id,
borrow_date=borrow.borrow_date,
return_date=borrow.return_date)
self.connection.execute(insert_query)