def __init__(
self,
db_name: str = 'database.sqlite',
):
"""
Creates database connector etc.
"""
self.conn = Connector(db_name=db_name)
class ProjectionModel:
connector = Connector()
@classmethod
def insert(cls, movie_id, movie_type, date, time):
query = queries.INSERT_PROJECTION
cls.connector.execute_query_with_values(
query, (movie_id, movie_type, date, time))
@classmethod
def list_projections(cls, movie_id):
query = queries.LIST_PROJECTIONS
return cls.connector.all_values(query, (movie_id, ))
@classmethod
def list_projections_by_date(cls, movie_id, date):
query = queries.LIST_PROJECTIONS_BY_DATE
return cls.connector.all_values(query, (movie_id, date))
@classmethod
def list_projections_with_avaliable_seats(cls, movie_id):
query = queries.LIST_PROJECTIONS_WITH_AVALIABLE_SEATS
return cls.connector.all_values(query, (movie_id, ))
@classmethod
def list_projection_reserved_spots(cls, projection_id):
query = queries.LIST_PROJECTION_RESERVED_SEATS
return cls.connector.all_values(query, (projection_id, ))
@classmethod
def show_projection_info(cls, projection_id):
query = queries.GET_PROJECTION_INFO
return cls.connector.all_values(query, (projection_id, ))
class MovieModel:
connector = Connector()
@classmethod
def insert(cls, name, rating):
query = queries.INSERT_MOVIE
cls.connector.execute_query_with_values(query, (name, rating))
@classmethod
def list_movies(cls):
query = queries.LIST_MOVIES
return cls.connector.all(query)
@classmethod
def get_movie(cls, id):
query = queries.GET_MOVIE_BY_ID
return cls.connector.get(query, id)
class ReservationModel:
connector = Connector()
@classmethod
def insert(cls, user_id, projection_id, row, column):
query = queries.INSERT_RESERVATION
cls.connector.execute_query_with_values(
query, (user_id, projection_id, row, column))
@classmethod
def delete(cls, reservation_id):
query = queries.DELETE_RESERVATION
cls.connector.execute_query_with_values(query, (reservation_id, ))
@classmethod
def get_reservation(cls, user_id, projection_id, row, column):
query = queries.GET_RESERVATION_ID
return cls.connector.get_values(
query, (user_id, projection_id, row, column))[0]
@classmethod
def list_reservations(cls, projection_id):
query = queries.LIST_RESERVATIONS
return cls.connector.all_values(query, (projection_id, ))
@classmethod
def get_projection_reserved_spots(cls, projection_id):
query = queries.GET_PROJECTION_RESERVED_SPOTS
return cls.connector.all_values(query, (projection_id, ))
@classmethod
def show_projection_spots(cls, projection_id):
return Board.create(cls.get_projection_reserved_spots(projection_id))
@classmethod
@log_info
def finalize(cls, user_id, username, reservations):
return True
@classmethod
def cancel_reservation(cls, username):
reservations = find_user_last_reservation(username)
for reservation in reservations:
cls.delete(reservation)
class UserModel:
connector = Connector()
@classmethod
@hashpassword
def insert(cls, username, password):
query = queries.INSERT_USER
cls.connector.execute_query_with_values(query, (username, password))
@classmethod
@hashpassword
def is_user(cls, username, password):
query = queries.IS_USER
return True if cls.connector.get_values(
query, (username, password)) is not None else False
@classmethod
@hashpassword
def is_logged(cls, username, password):
query = queries.IS_ACTIVE_USER
result = cls.connector.get_values(query, (username, password))[0]
return True if result == 1 else False
@classmethod
@hashpassword
def logged(cls, username, password):
query = queries.SET_ACTIVE_USER
cls.connector.execute_query_with_values(query, (username, password))
@classmethod
@hashpassword
def get_id(cls, username, password):
query = queries.GET_USER_ID
return cls.connector.get_values(query, (username, password))[0]
@classmethod
@hashpassword
def logged_out(cls, username, password):
query = queries.SET_NOT_ACTIVE_USER
cls.connector.execute_query_with_values(query, (username, password))
class SplitwiseApp:
def __init__(
self,
db_name: str = 'database.sqlite',
):
"""
Creates database connector etc.
"""
self.conn = Connector(db_name=db_name)
def add_new_user(
self,
user: User,
) -> NoReturn:
self.conn.save_user_info(user)
def get_user_info(
self,
user_id: int,
) -> User:
user_info = self.conn.get_user_info_or_none(user_id)
if user_info is None:
raise KeyError(f'User with id = {user_id} does not exist')
return user_info
def get_users_of_event(
self,
event_token: str,
) -> List[User]:
users = self.conn.get_users_of_event(event_token)
if not users:
raise KeyError(
f'There are no users in event with id = {event_token}')
return users
def get_event_info(
self,
event_token: str,
) -> Event:
return self.conn.get_event_info(event_token)
def user_exists(
self,
user_id: int,
) -> bool:
return self.conn.get_user_info_or_none(user_id) is not None
def create_event(
self,
user_id: int,
event_name: str,
event_token: str = str(uuid.uuid4()),
) -> str:
"""
Token may be provided (for testing purposes mainly). If not, it will be set to uuid4()
@return: token of the created event
"""
self.conn.create_event(
event=Event(event_token, event_name),
user_id=user_id,
)
log.info(
f'User {user_id} created event "{event_name}" with token {event_token}'
)
return event_token
def add_expense(
self,
expense: Expense,
) -> int:
"""
Adds expense to database
Fields 'name', 'sum', 'lender_id', 'event_token' are required
Fields 'id', 'datetime' will be ignored
@return: expense_id
"""
expense.id = None # will be filled after expense being added to db
expense.datetime = datetime.now()
return self.conn.save_expense_info(expense)
def add_user_to_event(self, user_id: int, event_token: str) -> NoReturn:
self.conn.add_user_to_event(user_id, event_token)
def user_participates_in_event(
self,
user_id: int,
event_token: str,
) -> bool:
return self.conn.user_participates_in_event(user_id, event_token)
def get_expense(
self,
expense_id: int,
) -> Expense:
return self.conn.get_expense_info(expense_id)
def add_debt(
self,
debt: Debt,
) -> int:
return self.conn.save_debt_info(debt)
def get_final_transactions(
self,
event_token: str,
) -> Tuple[Dict, Dict]:
"""
@param event_token: event token
@return: tuple (lenders_info, debtors_info)
"""
users = self.get_users_of_event(event_token)
usernames = {user.id: user.name for user in users}
users_balance = defaultdict(lambda: {
'lent': 0,
'owed': 0,
})
event_expenses = self.conn.get_event_expenses(event_token)
event_debts = self.conn.get_debts_by_expenses(
[expense.id for expense in event_expenses])
for debt in event_debts:
users_balance[debt.lender_id]['lent'] += debt.sum
users_balance[debt.debtor_id]['owed'] += debt.sum
lenders = [(user_balance['lent'] - user_balance['owed'], user_id)
for user_id, user_balance in users_balance.items()
if user_balance['lent'] > user_balance['owed']]
debtors = [(user_balance['owed'] - user_balance['lent'], user_id)
for user_id, user_balance in users_balance.items()
if user_balance['lent'] < user_balance['owed']]
lenders.sort(reverse=True)
debtors.sort(reverse=True)
if not lenders and not debtors:
return {}, {}
lenders_deque = deque(lenders)
debtors_deque = deque(debtors)
lenders_info = defaultdict(list)
debtors_info = defaultdict(list)
lender_sum, lender_id = lenders_deque.popleft()
debtor_sum, debtor_id = debtors_deque.popleft()
while True:
payment_sum = min(lender_sum, debtor_sum)
lenders_info[lender_id].append((usernames[debtor_id], payment_sum))
debtors_info[debtor_id].append((usernames[lender_id], payment_sum))
lender_sum -= payment_sum
if lender_sum == 0:
if not lenders_deque:
break
else:
lender_sum, lender_id = lenders_deque.popleft()
debtor_sum -= payment_sum
if debtor_sum == 0:
if not debtors_deque:
break
else:
debtor_sum, debtor_id = debtors_deque.popleft()
if lenders_deque or debtors_deque:
raise RuntimeError(
'Something went wrong while calculating final transactions')
return dict(lenders_info), dict(debtors_info)
def get_user_events(
self,
user_id: int,
) -> List[Event]:
return self.conn.get_user_events(user_id)
# following methods are for testing purposes only
# please do not use them in production
def get_all_users(self) -> List[User]:
return self.conn.get_all_users()
def get_all_events(self) -> List[Event]:
return self.conn.get_all_events()
def get_all_user2event(self) -> List[Tuple[int, str]]:
return self.conn.get_all_user2event()
def transform_db():
connection = Connector()
connection.connect()
session = connection.getSession()
for i in session.run("match ()-[edge]->() set edge += {__weight__: 1}"):
print i
connection.run("match (a) set a +={__tempWeight__:log(size(()-[]->(a))+1)}")
edges = session.run('''match (b)-[c]->(a) return id(b) as b, type(c) as type, id(a) as a''')
i = 0
for edge in edges:
if i % 1000 == 0:
print i, "of 71000"
connection.run(''.join(["start a = NODE(",str(edge["a"]),'''), b = Node(
''',str(edge["b"]),") match (a) match (b) create (a)-[:",str(edge["type"]),\
"{__weight__:a.__tempWeight__}]->(b)"]))
i = i+1
connection.run("match(a) set a.__tempWeight__=null")
nodes = session.run('''
MATCH (n)
return n, keys(n) as key, ID(n) as id
''')
i = 0
values = defaultdict(list)
for node in nodes:
for key in node["key"]:
strKey = str(node["n"][key]) if isinstance(node["n"][key], int) else node["n"][key]
strKey = strKey.replace("\'", "\\\'")
values[strKey].append({"id": node["id"], "attr": key})
if i % 1000 == 0:
print i, "of 60000"
i = i + 1
values.pop('', None)
i = 0
length = len(values)
for key in values.iterkeys():
i = i + 1
subQueryNode = list()
subQueryMatch = list()
subQueryCreate = list()
result = session.run(''.join(["create (value:__value__{value:'", key, "'}) return id(value) as id "]))
for value in result:
id = value["id"]
statement = ''.join([",value=NODE(", str(id), ") match(value) "])
j = 0
for node in values[key]:
j = j + 1
nodeId = str(node["id"])
variableName = "".join(["v", str(j)])
subQueryNode.extend([variableName,"=NODE(", nodeId, "),"])
subQueryMatch.extend([" match(", variableName, ') '])
subQueryCreate.extend([" create(value)-[:",node["attr"],"]->(", variableName, ") "])
if j%100 == 0:
subQueryNode = ''.join(subQueryNode)
subQueryMatch = ''.join(subQueryMatch)
subQueryCreate = ''.join(subQueryCreate)
subQueryNode = subQueryNode[:-1]
query = ''.join(["start " , subQueryNode, statement, subQueryMatch, subQueryCreate])
connection.run(query)
subQueryNode = list()
subQueryMatch = list()
subQueryCreate = list()
subQueryNode = ''.join(subQueryNode)
subQueryMatch = ''.join(subQueryMatch)
subQueryCreate = ''.join(subQueryCreate)
subQueryNode = subQueryNode[:-1]
query = ''.join(["start ", subQueryNode, statement, subQueryMatch, subQueryCreate])
if i % 1000 == 0:
print "query: ",i, " of ", length
if j % 100 != 0:
print query
connection.run(query)
connection.run("match(a) where not a:__value__ set a = {}")
connection.run("match (a)-[edge]->() where a:__value__ set edge.__weight__ = 1")
from database.connector import Connector
if __name__ == '__main__':
connector = Connector(db_name='testdb.sqlite')
# connector.drop_all_tables()
# connector.clean_all_tables()
# event_id1 = connector.save_event_info("sochi")
# event_id2 = connector.save_event_info("ochi")
# event_id3 = connector.save_event_info("sochi")
# user_id1 = connector.save_user_info("boris", event_id1)
# user_id2 = connector.save_user_info("boris", event_id2)
# user_id3 = connector.save_user_info("roman", event_id3)
# expense_id1 = connector.save_expense_info("buy", user_id1, 12)
# expense_id2 = connector.save_expense_info("buy1", user_id2, 32)
# expense_id3 = connector.save_expense_info("buy2", user_id3, 123)
# debtor_id1 = connector.save_debtor_info(expense_id1, user_id1, 12)
# debtor_id3 = connector.save_debtor_info(expense_id2, user_id2, 1212)
# debtor_id3 = connector.save_debtor_info(expense_id3, user_id3, 123)
# connector.save_payments_info(user_id1, user_id2, 123)
# connector.save_payments_info(user_id3, user_id2, 123)
# events = connector.get_all_events()
# users = connector.get_all_users()
# expenses = connector.get_all_expenses()
# debtors = connector.get_all_debtors()
# payments = connector.get_all_payments()
# print(events)
# print(users)
# print(expenses)
# print(debtors)
# print(payments)
pass