def listen(self):
try:
self.listeningSocket.bind(("127.0.0.1", 0))
except socket.error as msg:
Logger.error('[GAME] Bind failed. Error : ' + str(sys.exc_info()))
return (False)
self.listeningSocket.listen(1)
def start(self):
try:
self.socket.bind(("127.0.0.1", 12345))
except socket.error as msg:
Logger.error('[REALM] Bind failed. Error : ' + str(sys.exc_info()))
return (False)
self.socket.listen(16)
Logger.info("[REALM] Listening on port 12345...")
while True:
fd, addr = self.socket.accept()
ip, port = str(addr[0]), str(addr[1])
Logger.info('[REALM] New incomming connection: ' + ip + ':' + port)
newClient = RealmLocalClient(fd, addr, ip, port)
newClient.start()
self.socket.close()
class TableController:
""" Class that contains all methods to handle with choosen table menu with
following functionality: add, modify, delete record, sort by columns, find record"""
def __init__(self, tableName, database, themeWindow, returnEvent):
self.tableName = tableName
self.database = database
self.themeWindow = themeWindow
# Start logger
self.logger = Logger(__name__, loggingLevel="debug")
self.logger.debug("TableController logger has started.")
self.addWindow = None
self.modifyWindow = None
self.columnNames = self.database.getColumns(tableName)
self.tableData = self.database.getRawData(tableName)
self.data = self.database.getData(tableName)
self.model = TableModel()
if len(self.data) == 0:
messagebox.showwarning("Empty table", "This table has no records!")
self.data["_"] = dict()
self.data["_"]["_"] = "Empty table"
else:
for key, records in self.data.items():
for col, value in records.items():
if isinstance(value, datetime.date):
self.data[key][col] = str(value)
self.model.importDict(self.data)
# Widgets
self.content = Frame(self.themeWindow,
bg="#B7B9B8",
bd=4,
relief=RAISED,
width=self.themeWindow.winfo_width() - 80,
height=self.themeWindow.winfo_height() - 80)
self.content.place(x=40, y=40)
self.content.grid(row=0, column=0)
self.content.bind("<<goback>>", lambda _: returnEvent(None))
self.content.update()
# Canvas with options menu
self.topCanvas = Canvas(self.content,
bg="white",
bd=1,
relief=RAISED,
width=int(self.content.winfo_width()),
height=int(self.content.winfo_height() / 12))
self.topCanvas.pack(fill='both', side=TOP)
self.backButton = Button(self.topCanvas,
text=" < ",
command=self.back,
width=9)
self.backButton.pack(fill='both', side=LEFT)
self.showButton = Button(self.topCanvas,
text="Refresh table",
command=self.refreshTable,
width=22)
self.showButton.pack(fill='both', side=LEFT)
# Canvas with data
self.middleFrame = Frame(self.content)
self.middleFrame.pack(fill='both', side=TOP)
self.table = CustomTable(self.middleFrame, model=self.model)
self.table.show()
# Canvas with DML buttons
self.bottomCanvas = Canvas(self.content,
bg="white",
bd=1,
relief=FLAT,
width=int(self.content.winfo_width()),
height=int(self.content.winfo_height() / 5))
self.bottomCanvas.pack(fill='both', side=TOP)
self.buttonAdd = Button(self.bottomCanvas,
text=" ADD ",
command=self.add,
width=24,
height=3,
bd=5)
self.buttonAdd.pack(side=LEFT)
self.buttonModify = Button(self.bottomCanvas,
text=" MODIFY ",
command=self.modify,
width=25,
height=3,
bd=5)
self.buttonModify.pack(side=LEFT)
self.buttonDelete = Button(self.bottomCanvas,
text=" DELETE ",
command=lambda: self.delete(self.tableName),
width=25,
height=3,
bd=5)
self.buttonDelete.pack(side=LEFT)
def back(self):
""" Go back to main window """
self.content.event_generate("<<goback>>")
def backEvent(self, event):
if self.addWindow is not None:
self.addWindow.addWindow.destroy()
self.addWindow = None
if self.modifyWindow is not None:
self.modifyWindow.modifyWindow.destroy()
self.modifyWindow = None
self.refreshTable()
def add(self):
""" Go to add window """
if self.addWindow is None:
self.logger.debug("Starting add window.")
self.addWindow = AddController(self.themeWindow, self.tableName,
self.database, self.backEvent)
def modify(self):
""" Go to modify window """
if self.table.startrow != self.table.endrow:
messagebox.showwarning('Modify error',
'Please select only one record!')
else:
selectedRow = self.table.currentrow
if self.modifyWindow is None:
self.logger.debug("Starting modify window.")
self.modifyWindow = ModifyController(
self.themeWindow, self.tableName, self.database,
self.model.getRecName(selectedRow), self.data,
self.backEvent)
def delete(self, tableName):
""" Delete selected records """
for no, i in enumerate(self.table.multiplerowlist):
recName = self.model.getRecName(i)
deletedRecord = list()
for column, value in self.data[recName].items():
deletedRecord.append(value)
try:
self.database.deleteRecord(tableName, deletedRecord)
except Exception as e:
self.logger.error(
f"Can not delete selected records! Error = {e}")
errorNo = int(e.__str__().split()[0][1:-1])
if errorNo == 1451:
messagebox.showerror(
"Can not delete selected records!",
f"There are bounds including selected record.")
else:
messagebox.showerror("Can not delete selected records!",
f"Error {e}")
return
confirm = messagebox.askyesno(
"Deleting record confirmation",
f"Are You sure that You want to delete {len(self.table.multiplerowlist)} records?"
)
if confirm:
self.database.connection.commit()
else:
self.database.connection.rollback()
self.model.createEmptyModel()
self.refreshTable()
return
def refreshTable(self):
self.data = self.database.getData(self.tableName)
if len(self.data) == 0:
messagebox.showwarning("Empty table", "This table has no records!")
self.data["_"] = dict()
self.data["_"]["_"] = "Empty table"
else:
for key, records in self.data.items():
for col, value in records.items():
if isinstance(value, datetime.date):
self.data[key][col] = str(value)
self.model.importDict(self.data)
self.table.redraw()
class ModifyController:
def __init__(self, themeWindow, tableName, database, selectedRecord, data,
backEvent):
self.themeWindow = themeWindow
self.tableName = tableName
self.database = database
self.selectedRecord = selectedRecord
self.data = data
self.backEvent = backEvent
# Start logger
self.logger = Logger(__name__, loggingLevel="debug")
self.logger.debug("ModifyController logger has started.")
self.modifyWindow = Toplevel(self.themeWindow)
self.modifyWindow.title("Modify an existing record.")
self.modifyWindow.protocol('WM_DELETE_WINDOW', self.goBack)
self.modifyWindow.bind("<<back>>", lambda _: self.backEvent(None))
self.helpWindow = None
self.newRecord = list()
self.emptyCols = 0
self.emptyButton = IntVar()
self.colNames = self.database.getColumns(self.tableName)
self.colTypes = self.database.getColumnTypes(self.tableName)
self.colKeys = self.database.getColumnKeys(self.tableName)
self.colNulls = self.database.getColumnNullable(self.tableName)
self.colConstraints = self.database.getTableCheckConstraint(
self.tableName)
print(f"DEBUG = {self.colConstraints}")
self.colFrame = Frame(self.modifyWindow,
bd=4,
relief=RAISED,
width=self.themeWindow.winfo_width(),
height=self.themeWindow.winfo_height() - 40)
self.colFrame.pack(fill='both', side=TOP)
self.entries = list()
if self.tableName in ["wlasciciel_biblioteka"]:
for no, col in enumerate(self.colNames):
Label(self.colFrame, text=col,
font=("Arial Bold", 12)).grid(row=no, column=0)
combo = ttk.Combobox(self.colFrame,
values=self.initComboValues(
self.tableName, col[0]))
combo.grid(row=no, column=1, columnspan=2, padx=20, pady=10)
if data[self.selectedRecord][col[0]] is not None:
combo.set(data[self.selectedRecord][col[0]])
self.entries.append(combo)
elif self.tableName in ["autor_ksiazka"]:
for no, col in enumerate(self.colNames):
Label(self.colFrame, text=col,
font=("Arial Bold", 12)).grid(row=no, column=0)
vals = self.initComboValues(self.tableName, col[0])
combo = ttk.Combobox(self.colFrame, values=vals)
combo.grid(row=no, column=1, columnspan=2, padx=20, pady=10)
valueHelper = Button(
self.colFrame,
text="?",
command=lambda b=col[0], c=combo: self.showHelp(b, c))
valueHelper.grid(row=no, column=3)
if data[self.selectedRecord][col[0]] is not None:
combo.set(data[self.selectedRecord][col[0]])
self.entries.append(combo)
else:
for no, col in enumerate(self.colNames):
if col[0][-2:] == "id" and self.colKeys[col[0]][0] == 'PRI':
self.emptyCols += 1
continue
if self.colKeys[col[0]][0] == 'PRI':
_text = self.colNames[no][0] + "*"
else:
_text = self.colNames[no][0]
Label(self.colFrame, text=_text,
font=("Arial Bold", 12)).grid(row=no, column=0)
if self.colTypes[col[0]] == 'date':
entry = DateEntry(self.colFrame, date_pattern='y/mm/dd')
entry.grid(row=no,
column=1,
columnspan=2,
padx=20,
pady=10)
if self.colNulls[col[0]] == 'YES':
emptyButton = Checkbutton(self.colFrame,
text="Empty",
variable=self.emptyButton,
command=self.clicked)
emptyButton.grid(row=no, column=3)
self.emptyDate = entry
else:
if self.colKeys[col[0]][0] == 'MUL':
vals = self.database.executeStatement(
f"SELECT {self.colKeys[col[0]][2]} FROM {self.colKeys[col[0]][1]}"
)
entry = ttk.Combobox(self.colFrame, values=vals)
entry.grid(row=no,
column=1,
columnspan=2,
padx=20,
pady=10)
else:
if col[0] in self.colConstraints.keys():
entry = ttk.Combobox(
self.colFrame,
values=self.colConstraints[col[0]])
else:
entry = Entry(self.colFrame, width=20)
entry.grid(row=no,
column=1,
columnspan=2,
padx=20,
pady=10)
if data[self.selectedRecord][col[0]] is not None:
if self.colTypes[col[0]] == 'date':
entry.set_date(data[self.selectedRecord][col[0]])
else:
entry.insert(END, data[self.selectedRecord][col[0]])
self.entries.append(entry)
self.oldRecord = list()
for entry in self.entries:
self.oldRecord.append(entry.get())
if self.emptyCols > 0:
if self.tableName == "pracownicy":
self.oldRecord.insert(
0,
self.database.executeStatement(
f"SELECT `{self.colNames[0][0]}` FROM `{self.tableName}`"
+
f"WHERE `{self.colNames[2][0]}` = \"{self.oldRecord[1]}\" AND"
+ f"`{self.colNames[3][0]}` = \"{self.oldRecord[2]}\"")
[0][0])
else:
self.oldRecord.insert(
0,
self.database.executeStatement(
f"SELECT `{self.colNames[0][0]}` FROM `{self.tableName}`"
+
f"WHERE `{self.colNames[1][0]}` = \"{self.oldRecord[0]}\" AND"
+ f"`{self.colNames[2][0]}` = \"{self.oldRecord[1]}\"")
[0][0])
self.buttonFrame = Frame(self.modifyWindow,
bd=4,
relief=RAISED,
width=self.themeWindow.winfo_width(),
height=40)
self.buttonFrame.pack(fill='both', side=TOP)
self.addButton = Button(self.buttonFrame,
text="Modify",
command=self.checkEntry)
self.addButton.pack(side=LEFT)
self.cancelButton = Button(self.buttonFrame,
text="Cancel",
command=self.goBack)
self.cancelButton.pack(side=LEFT)
def clicked(self):
if self.emptyButton.get() == 0:
self.emptyDate.configure(state='enabled')
else:
self.emptyDate.configure(state='disabled')
def checkEntry(self):
self.newRecord.clear()
for i in range(self.emptyCols):
self.newRecord.append("")
for entry in self.entries:
value = entry.get()
if len(value) == 10 and value[4] == "/" and value[7] == "/":
value = value.replace("/", "-", 2)
self.newRecord.append(value)
for no, col in enumerate(self.colNulls.values()):
if col == 'YES':
if self.emptyButton.get() == 1:
self.newRecord[no] = ""
if self.emptyCols == 1:
self.newRecord[0] = self.oldRecord[0]
if self.tableName == "autorzy" and self.database.executeStatement(
f"SELECT `data_smierci`" +
f"FROM `autorzy` WHERE `autor_id` = \"{self.newRecord[0]}\""
)[0][0] == None:
self.oldRecord[-1] = ""
try:
self.database.modifyRecord(self.tableName, self.oldRecord,
self.newRecord)
except Exception as e:
self.logger.error(f"Exception! e = {e}")
try:
errorNo = int(e.__str__().split()[0][1:-1])
except:
errorNo = 0
if errorNo == 1048:
messagebox.showerror("Can not add a record to database!",
f"{e.__str__().split(',')[1][:-2]}")
elif errorNo == 1062:
messagebox.showerror(
"Can not add a record to database!",
f"Primary column value (with '*') is duplicate!")
elif errorNo == 0:
messagebox.showerror("Can not add a record to database!",
f"{e}")
else:
messagebox.showerror("Can not add a record to database!",
f"{e.__str__().split(',')[1][:-2]}")
self.newRecord = list()
return
confirm = messagebox.askyesno(
"Modify record confirmation",
"Are You sure that You want to modify this record in database?")
if confirm:
self.database.connection.commit()
self.goBack()
else:
self.database.connection.rollback()
self.themeWindow.focus_set()
def goBack(self):
self.modifyWindow.event_generate("<<back>>")
def initComboValues(self, tableName, col):
return self.database.executeStatement(
f"SELECT DISTINCT `{col}` FROM `{tableName}`")
def showHelp(self, column, combo):
if self.helpWindow is not None:
return
def select():
atr = self.listbox.get(self.listbox.curselection())
if self.column == "autorzy_autor_id":
atr = atr.split(" ")
id = self.database.executeStatement(
f"SELECT `autor_id` " +
f"FROM `autorzy` WHERE `imie` = \"{atr[0]}\" " +
f"AND `nazwisko` = \"{atr[1]}\" ")
else:
id = self.database.executeStatement(
f"SELECT `ksiazka_id` " +
f"FROM `ksiazki` WHERE `tytul` = \"{atr}\" ")
self.combo.set(id)
exit()
return
def exit():
self.helpWindow.destroy()
self.helpWindow = None
self.column = column
self.combo = combo
self.helpWindow = Toplevel(self.modifyWindow)
self.helpWindow.protocol('WM_DELETE_WINDOW', exit)
vals = list()
if self.column == "autorzy_autor_id":
temp_vals = self.database.executeStatement(
f"SELECT `imie`, `nazwisko` FROM `autorzy`")
for val1, val2 in temp_vals:
vals.append(val1 + " " + val2)
else:
temp_vals = self.database.executeStatement(
f"SELECT `tytul` FROM `ksiazki`")
for val1 in temp_vals:
vals.append(val1[0])
self.listbox = Listbox(self.helpWindow)
self.listbox.pack(side=TOP)
for val in vals:
self.listbox.insert("end", val)
self.button = Button(self.helpWindow, text="Select", command=select)
self.button.pack(side=TOP)
class LogonController:
def __init__(self, mainWindow, eventF, width=300, height=200):
# Initialize logger
self.logger = Logger(__name__, loggingLevel="debug")
self.logger.debug("LogonController logger has started.")
# Create sub window with signing info
self.tlLogon = Toplevel(mainWindow)
self.tlLogon.title("Sign In")
mainWindow.update()
# Calculate offset of sub window
xOffset = (mainWindow.winfo_width() / 2) - (width / 2)
yOffset = (mainWindow.winfo_height() / 2) - (height / 2)
self.tlLogon.geometry(
f"{width}x{height}+{int(xOffset)}+{int(yOffset)}")
self.tlLogon.lift()
self.tlLogon.resizable(0, 0)
self.tlLogon.attributes("-topmost", True)
self.tlLogon.bind("<FocusIn>", self.eventHandleFocus)
self.tlLogon.protocol('WM_DELETE_WINDOW', self.doNothing)
self.tlLogon.bind('<<signed>>', lambda database: eventF(self.dataBase))
# Other widgets
self.content = Frame(self.tlLogon)
self.content.place(x=self.tlLogon.winfo_width() / 12,
y=self.tlLogon.winfo_height() / 6)
self.labelAddress = Label(self.content,
text="Adress:",
font=("Arial Bold", 12))
self.labelAddress.grid(row=0, column=0)
self.textAddress = Entry(self.content, width=20)
self.textAddress.grid(row=0, column=1)
self.textAddress.insert(END, "localhost") # TODO: REMOVE IT LATER
self.labelUser = Label(self.content,
text="Username:"******"Arial Bold", 12))
self.labelUser.grid(row=1, column=0)
self.textUser = Entry(self.content, width=20)
self.textUser.grid(row=1, column=1)
self.textUser.insert(END, "root") # TODO: REMOVE IT LATER
self.labelPassword = Label(self.content,
text="Password:"******"Arial Bold", 12))
self.labelPassword.grid(row=2, column=0)
self.textPassword = Entry(self.content, show='*', width=20)
self.textPassword.grid(row=2, column=1)
self.labelDatabase = Label(self.content,
text="Database Name:",
font=("Arial Bold", 12))
self.labelDatabase.grid(row=3, column=0)
self.textDatabase = Entry(self.content, width=20)
self.textDatabase.grid(row=3, column=1)
self.textDatabase.insert(END, "ZBD_project")
self.buttonLogin = Button(self.content,
text="Sign in",
command=self.clickLogin)
self.buttonLogin.grid(row=4, column=1)
self.labelDatabase = Label(self.content,
text="Incorrect login input!",
font=("Arial Bold", 8),
fg="red")
self.labelDatabase.grid(row=5, column=1)
self.labelDatabase.grid_remove()
def clickLogin(self):
self.logger.debug("Attempt to sign in.")
address = self.textAddress.get()
user = self.textUser.get()
password = self.textPassword.get()
databaseName = self.textDatabase.get()
if self.signInToDatabase(address, user, password, databaseName):
self.labelDatabase.grid_remove()
self.logger.info("Signed in!")
self.tlLogon.event_generate("<<signed>>")
else:
self.labelDatabase.grid()
def signInToDatabase(self, address, user, password, databaseName):
try:
dataBase = Database(host=address,
user=user,
password=password,
database=databaseName)
except ConnectionRefusedError:
self.logger.error("Could not connect to data base system!")
return False
self.dataBase = dataBase
return True
def eventHandleFocus(self, event):
if event.widget == self.tlLogon:
self.tlLogon.focus_set()
def doNothing(self):
pass
class Database:
def __init__(self, host, user, password="", database="zbd_project"):
"""Initialize database object"""
# Initialize logger
self.logger = Logger(__name__, loggingLevel="debug")
self.logger.debug("Database logger has started.")
self.connection = None
# Initialize database connection
try:
self.logger.debug("Trying to attempt connection with database.")
self.connection = pymysql.connect(host=host, user=user, passwd=password, database=database)
except Exception as e:
self.logger.critical(f"Could not connect to database! Error = {e}")
raise ConnectionRefusedError(f"Could not connect to database! Error = {e}")
self.logger.info("Database connection is stable.")
self.config = dict()
try:
with open("config.json") as f:
self.config = {**self.config, **json.load(f)}
except IOError as e:
self.logger.error(f"Error occured while reading config from .json file!. Error = {e}")
exit(-1)
self.logger.debug("Config has been read.")
# Initialize global cursor
self.logger.debug("Creating global cursor.")
self.cursor = self.connection.cursor()
self.logger.debug("Global cursor created.")
def __del__(self):
""" Close connection with database """
# Close database connection
if self.connection:
self.logger.info("Closing database connection.")
try:
self.connection.close()
except:
pass
def executeStatement(self, statement):
""" Execute an statement using database cursor"""
try:
self.logger.debug(f"Executing statement = {statement}")
self.cursor.execute(statement)
except Exception as e:
self.logger.error(f"Could not realize an execute statement. Statement = {statement}. Error = {e}")
raise Exception(e)
result = self.cursor.fetchall()
self.logger.debug(f"Result = {result}")
return result
def getTableNames(self):
""" Get all table names read from config file """
return self.config["table_names"]
def getRawData(self, tableName):
""" Gets data and return its in list form """
statement = "SELECT * FROM " + tableName + ";"
return self.executeStatement(statement)
def getColumns(self, tableName):
"""Create and execute statement"""
tableName = "'" + tableName + "'"
statement = "SELECT column_name FROM information_schema.columns WHERE table_name = " + tableName + ";"
return self.executeStatement(statement)
def getColumnTypes(self, tableName):
"""Create and execute statement"""
tableName = "'" + tableName + "'"
statement = "SELECT column_name, data_type FROM information_schema.columns WHERE table_name = " + tableName + ";"
columns = self.executeStatement(statement)
res = {}
for column in columns:
res[column[0]] = column[1]
return res
"""Create dictionary res[columnName] = [keyType, refTable, refColumn]"""
def getColumnKeys(self, tableName):
tableName = "'" + tableName + "'"
statement = "SELECT column_name, column_key FROM information_schema.columns WHERE table_name = " + tableName + ";"
columns = self.executeStatement(statement)
res = {}
for column in columns:
name = column[0]
key = column[1]
if key == 'MUL':
statement = "SELECT referenced_table_name, referenced_column_name FROM information_schema.key_column_usage WHERE table_name = " + tableName + " AND column_name = " + "'" + name + "'" + ";"
tmp = self.executeStatement(statement)[0]
refTable = tmp[0]
refColumn = tmp[1]
else:
refTable = 'NULL'
refColumn = 'NULL'
res[name] = [key, refTable, refColumn]
return res
def getColumnNullable(self, tableName):
"""Create and execute statement"""
tableName = "'" + tableName + "'"
statement = "SELECT column_name, IS_NULLABLE FROM information_schema.columns WHERE table_name = " + tableName + ";"
columns = self.executeStatement(statement)
res = {}
for column in columns:
res[column[0]] = column[1]
return res
def getTableCheckConstraint(self, tableName):
"""Create and execute statement"""
tableName = "'" + tableName + "'"
statement = "SELECT constraint_name, check_clause FROM information_schema.check_constraints WHERE table_name = " + tableName + ";"
columns = self.executeStatement(statement)
res = {}
for column in columns:
values = column[1]
values = values[22:-1]
values = list(values.split(","))
newValues = []
for val in values:
newValues.append(val[1:-1])
res[column[0]] = newValues
return res
def getData(self, tableName):
""" Gets data and return it in dict form """
rowData = self.getRawData(tableName)
columns = self.getColumns(tableName)
result = dict()
for noR, row in enumerate(rowData):
result[f"rec{noR + 1}"] = dict()
for noC, column in enumerate(columns):
result[f"rec{noR + 1}"][f"{column[0]}"] = row[noC]
return result
def addRecord(self, tableName, values):
self.logger.debug(f"Adding new record to table {tableName}. Values = {values}")
try:
"""Check if values are in correct format"""
self.logger.debug("Checking if values are in correct format.")
columnTypes = self.getColumnTypes(tableName)
tmp = self.getColumns(tableName)
columnNames = []
for column in tmp:
newColumn = str(column)
newColumn = newColumn[2:-3]
columnNames.append(newColumn)
for i in range(len(columnTypes)):
name = columnNames[i]
type = columnTypes[name]
value = values[i]
value = str(value)
if value in ('NULL', None, ''):
continue
elif type == 'int':
if not re.match('^[0-9]+$', value):
raise Exception(f"Wrong {name}. Make sure {name} contains only digits.")
elif type == 'date':
condition = self.executeStatement(f"SELECT (SELECT CURDATE()) < '{value}'")
if not re.match('^[0-9]{4}-[0-9]{2}-[0-9]{2}$', value):
raise Exception(f"Wrong {name}. Make sure {name} is in 'YYYY-MM-DD' format.")
elif condition == ((1,),):
raise Exception(f"Wrong {name}. Date can't be from the future.")
self.logger.debug("Creating addRecord statement.")
"""Create columns_str string to hold columns names ready to put into mysql question"""
columns_str = "("
for column in columnNames:
newColumn = "`" + column + "`"
columns_str += newColumn + ", "
columns_str = columns_str[:-2] + ")"
"""Create values_str string to hold columns values ready to put into mysql question"""
values_str = "("
for v in values:
newValue = v
if newValue in ('NULL', None, ''):
newValue = 'NULL'
else:
newValue = "'" + str(v) + "'"
values_str += newValue + ", "
values_str = values_str[:-2] + ")"
"""Create statement"""
tableName = "`" + tableName + "`"
statement = "INSERT INTO " + tableName + columns_str + " VALUES " + values_str + ";"
self.logger.debug(f"AddRecord statement created succesfully. Statement = {statement}")
"""Executing statement"""
self.executeStatement(statement)
self.logger.debug(f"Record {values[0]} added succesfully.")
return True
except Exception as e:
self.logger.error(f"Could not realize an addRecord function. Error = {e}")
raise Exception(e)
def modifyRecord(self, tableName, oldValues, values):
self.logger.debug(f"Modifying record {oldValues} from {tableName}. New values = {values}")
try:
"""Check if values are in correct format"""
self.logger.debug("Checking if values are in correct format.")
columnTypes = self.getColumnTypes(tableName)
tmp = self.getColumns(tableName)
columnNames = []
for column in tmp:
newColumn = str(column)
newColumn = newColumn[2:-3]
columnNames.append(newColumn)
for i in range(len(columnTypes)):
name = columnNames[i]
type = columnTypes[name]
value = values[i]
value = str(value)
if value in ('NULL', None, ''):
continue
elif type == 'int':
if not re.match('^[0-9]+$', value):
raise Exception(f"Wrong {name}. Make sure {name} contains only digits.")
elif type == 'date':
condition = self.executeStatement(f"SELECT (SELECT CURDATE()) < '{value}'")
if not re.match('^[0-9]{4}-[0-9]{2}-[0-9]{2}$', value):
raise Exception(f"Wrong {name}. Make sure {name} is in 'YYYY-MM-DD' format.")
elif condition == ((1,),):
raise Exception(f"Wrong {name}. Date can't be from the future.")
self.logger.debug("Creating modifyRecord statement.")
"""Create columns names ready to put into mysql question"""
columns = []
tmp = self.getColumns(tableName)
for column in tmp:
newColumn = str(column)
newColumn = "`" + newColumn[2:-3] + "`"
columns.append(newColumn)
"""Create set_str ready to put into mysql question"""
set_str = ""
for i in range(len(columns)):
if str(values[i]) in ('NULL', None, ''):
set_str += columns[i] + " = "
set_str += 'NULL' + ", "
else:
set_str += columns[i] + " = "
set_str += "'" + str(values[i]) + "', "
set_str = set_str[:-2]
"""Create where_str ready to put into mysql question"""
where_str = ""
for i in range(len(columns)):
oldValue = oldValues[i]
if oldValue in ('NULL', None, ''):
where_str += columns[i] + " IS NULL AND "
else:
where_str += columns[i] + " = "
oldValue = str(oldValues[i])
where_str += "'" + oldValue + "' AND "
where_str = where_str[:-5]
"""Create statement"""
tableName = "`" + tableName + "`"
statement = "UPDATE " + tableName + " SET " + set_str + " WHERE " + where_str + ";"
self.logger.debug(f"ModifyRecord statement created succesfully. Statement = {statement}")
"""Executing statement"""
self.executeStatement(statement)
self.logger.debug(f"Record {oldValues[0]} modified succesfully.")
return True
except Exception as e:
self.logger.error(f"Could not realize an modifyRecord function. Error = {e}")
raise Exception(e)
def deleteRecord(self, tableName, values):
self.logger.debug(f"Deleting record {values[0]} from {tableName}.")
try:
self.logger.debug("Creating deleteRecord statement.")
"""Create columns names ready to put into mysql question"""
columns = []
tmp = self.getColumns(tableName)
for column in tmp:
newColumn = str(column)
newColumn = "`" + newColumn[2:-3] + "`"
columns.append(newColumn)
"""Create where_str ready to put into mysql question"""
where_str = ""
for i in range(len(columns)):
value = values[i]
if value in ('NULL', None, ''):
where_str += columns[i] + " IS NULL AND "
else:
where_str += columns[i] + " = "
value = str(values[i])
where_str += "'" + value + "' AND "
where_str = where_str[:-5]
"""Create statement"""
tableName = "`" + tableName + "`"
statement = "DELETE FROM " + tableName + " WHERE " + where_str + ";"
self.logger.debug(f"DeleteRecord statement created succesfully. Statement = {statement}")
"""Executing statement"""
self.executeStatement(statement)
self.logger.debug(f"Record {values[0]} deleted succesfully.")
return True
except Exception as e:
self.logger.error(f"Could not realize an deleteRecord function. Error = {e}")
raise Exception(e)
def borrowBook(self, libraryName, workerName, workerSurname,
readerName, readerSurname, bookTitle,
bookDate, bookGenre, comments):
self.logger.debug("Borrowing book.")
try:
self.executeStatement(f"CALL borrowBook('{libraryName}', '{workerName}', '{workerSurname}',"
f"'{readerName}', '{readerSurname}', '{bookTitle}',"
f"'{bookDate}', '{bookGenre}', '{comments}');")
self.logger.debug("Book borrowed succesfully.")
return True
except Exception as e:
self.logger.error(f"Could not realize an borrowBook function. Error = {e}")
raise Exception(e)
def findBestBook(self, bookDateYear):
self.logger.debug("Finding best book.")
try:
bestBookBorrowCount = self.executeStatement(f"SELECT findBestBookBorrowCount({bookDateYear});")
bestBookTitle = self.executeStatement(f"SELECT findBestBookTitle({bookDateYear});")
self.logger.debug(f"Book found succesfully. BookID = {bestBookTitle}. Count = {bestBookBorrowCount}")
return [bestBookTitle, bestBookBorrowCount]
except Exception as e:
self.logger.error(f"Could not realize an findBestBook function. Error = {e}")
raise Exception(e)
def generateDataBase(self):
self.logger.debug(f"Generating new data base started.")
names = ['Piotr', 'Maciej', 'Jan', 'Jakub', 'Karol', 'Joanna', 'Marta', 'Magda', 'Natalia', 'Olga']
surnames = ['Krol', 'Nowak', 'Zima', 'Leszczyk', 'Karol', 'Nowaczyk', 'Kowalczyk', 'Wozniak', 'Mazur', 'Krawczyk']
dates = ['1970-05-05', '1971-12-25', '1972-08-01', '1973-07-17', '1974-10-11', '1975-01-22', '1976-03-30', '1977-11-28', '1978-09-03', '1979-04-14']
libraryNames = ['Czytam_ksiazki', 'Czytanie_jest_fajne', 'Ksiazki_sa_super', 'Biblioteka_dziecieca', 'Biblioteka_szkolna', 'Biblioteka_miejska', 'Warto_czytac', 'Super_Biblioteka', 'Biblioteka_na_rynku', 'Biblioteka_na_Kwiatowej']
adresses = ['Kwiatowa_10', 'Rzeczypospolitej_5', 'Dluga_4', 'Krutka_1', 'Poznanska_12', 'Wroclawska_43', 'Zawila_3', 'Czysta_21', 'Powstancow_2', 'Brudna_31']
genres = ['fantasy', 'horror', 'klasyka', 'kryminal', 'sensacja', 'thriller', 'literatura_mlodziezowa', 'literatura_obyczajowa', 'romans', 'powiesc_historyczna']
operationTypes = ['wypozyczenie', 'zwrot', 'przedluzenie']
comments = ['pierwszy raz od dawna', 'sprawna operacja', 'bez uwag']
booksTitles = ['Szeptucha', 'Polska_odwraca_oczy', 'On', 'Zycie_na_pelnej_petardzie', 'Okularnik', 'Najgorszy_czlowiek_na_swiecie', 'Inna_dusza', 'Ksiegi_Jakubowe', 'Gniew', 'Trociny']
firmNames = ['Januszpol', 'Toyota', 'Adidas', 'Walkman', 'Red_Bull', 'Junkers', 'Ikea', 'Tesco', 'CCC', 'Bakoma']
workersFunctions = ['szef', 'sprzatanie', 'obsluga_bazy', 'kucharz']
self.logger.debug("Random collumn values created succesfully.")
"""Generate authors"""
self.logger.debug("Generating authors. Putting records to `autorzy` table.")
for i in range(10):
author_id = 'NULL'
name = random.choice(names)
surname = surnames[i]
birthday = random.choice(dates)
tmp = random.choice([True, False])
if tmp:
deathday = 'NULL'
else:
deathYear = birthday[:4]
deathYear = int(deathYear)
tmp = random.randint(1,40)
deathYear = deathYear + tmp
deathYear = str(deathYear)
tmp = random.choice(dates)
deathday = deathYear + tmp[4:]
values = [author_id, name, surname, birthday, deathday]
self.addRecord('autorzy', values)
self.logger.debug("Authors records put into 'autorzy' table succesfully.")
"""Generate libraries"""
self.logger.debug("Generating libraries. Putting records to `biblioteki` table.")
for i in range(10):
name = libraryNames[i]
adres = adresses[i]
values = [name, adres]
self.addRecord('biblioteki', values)
self.logger.debug("Libraries records put into 'biblioteki' table succesfully.")
"""Generate readers"""
self.logger.debug("Generating readers. Putting records to `czytelnicy` table.")
for i in range(10):
reader_id = 'NULL'
name = random.choice(names)
surname = surnames[i]
values = [reader_id, name, surname]
self.addRecord('czytelnicy', values)
self.logger.debug("Readers records put into 'czytelnicy' table succesfully.")
"""Generate sections"""
self.logger.debug("Generating sections. Putting records to `dzialy` table.")
for i in range(10):
name = 'dzial' + str(i)
location = 'lokalizacja' + str(i)
values = [name, location]
self.addRecord('dzialy', values)
self.logger.debug("Sections records put into 'dzialy' table succesfully.")
"""Generate genres"""
self.logger.debug("Generating genres. Putting records to `gatunki` table.")
for i in range(10):
name = genres[i]
values = [name]
self.addRecord('gatunki', values)
self.logger.debug("Genres records put into 'gatunki' table succesfully.")
"""Generate books"""
self.logger.debug("Generating books. Putting records to `ksiazki` table.")
for i in range(10):
book_id = 'NULL'
title = booksTitles[i]
date = random.choice(dates)
genre = random.choice(genres)
values = [book_id, title, date, genre]
self.addRecord('ksiazki', values)
self.logger.debug("Books records put into 'ksiazki' table succesfully.")
"""Generate bookstands"""
self.logger.debug("Generating bookstands. Putting records to `regaly` table.")
for i in range(10):
number = i
capacity = random.randint(5,9)
booksCount = 0
section = random.randint(0,9)
section = 'dzial' + str(section)
values = [number, capacity, booksCount, section]
self.addRecord('regaly', values)
self.logger.debug("Bookstands records put into 'regaly' table succesfully.")
"""Generate ovners"""
self.logger.debug("Generating ovners. Putting records to `wlasciciele` table.")
tmp = random.randint(1000000000,2000000000)
for i in range(10):
nip = tmp + i
if random.randint(0,1) == 1:
firmName = firmNames[i]
name = 'NULL'
surname = 'NULL'
else:
firmName = 'NULL'
name = random.choice(names)
surname = surnames[i]
values = [nip, firmName, name, surname]
self.addRecord('wlasciciele', values)
self.logger.debug("Ovners records put into 'wlasciciele' table succesfully.")
"""Generate ovner-library"""
self.logger.debug("Generating ovner-library. Putting records to `wlasciciel_biblioteka` table.")
tmp = self.getRawData('wlasciciele')
nips = [record[0] for record in tmp]
tmp = self.getRawData('biblioteki')
libraries = [record[0] for record in tmp]
for i in range(len(libraries)):
nip = random.choice(nips)
library = libraries[i]
values = [nip, library]
self.addRecord('wlasciciel_biblioteka', values)
self.logger.debug("Ovner-library records put into 'wlasciciel_biblioteka' table succesfully.")
"""Generate author-book"""
self.logger.debug("Generating author-book. Putting records to `autor_ksiazka` table.")
tmp = self.getRawData('autorzy')
authors_ids = [record[0] for record in tmp]
tmp = self.getRawData('ksiazki')
books_ids = [record[0] for record in tmp]
for i in range(len(books_ids)):
author_id = random.choice(authors_ids)
book_id = books_ids[i]
values = [author_id, book_id]
self.addRecord('autor_ksiazka', values)
self.logger.debug("Author-book records put into 'autor_ksiazka' table succesfully.")
"""Generate specimens"""
self.logger.debug("Generating specimens. Putting records to `egzemplarze` table.")
tmp = self.getRawData('ksiazki')
books_ids = [record[0] for record in tmp]
tmp = self.getRawData('regaly')
bookstandsNumbers = [record[0] for record in tmp]
for _ in range(10):
specimen_id = 'NULL'
book_id = random.choice(books_ids)
bookstandNumber = random.choice(bookstandsNumbers)
"""Increment bookstand books count"""
statement = "SELECT `liczba_ksiazek` FROM `regaly` WHERE `numer` = " + str(bookstandNumber) + ";"
booksCount = self.executeStatement(statement)
statement = "SELECT `pojemnosc` FROM `regaly` WHERE `numer` = " + str(bookstandNumber) + ";"
capacity = self.executeStatement(statement)
if booksCount < capacity:
statement = "UPDATE `regaly` SET `liczba_ksiazek` = `liczba_ksiazek` + 1 WHERE `numer` = " + str(bookstandNumber) + ";"
self.executeStatement(statement)
"""Add specimen"""
values = [specimen_id, book_id, bookstandNumber]
self.addRecord('egzemplarze', values)
else:
self.logger.error(f"Could not add specimen to bookstand {bookstandNumber}. Not enough space on the bookstand")
return False
self.logger.debug("Specimens records put into 'egzemplarze' table succesfully.")
"""Generate workers. Disable FK checking"""
self.logger.debug("Generating workers. Putting records to `pracownicy` table.")
self.executeStatement("SET FOREIGN_KEY_CHECKS = 0;")
self.logger.debug("FOREIGN_KEY_CHECKS disabled")
for i in range(10):
worker_id = 'NULL'
if i != 0:
boss_id = random.choice(['NULL', random.randint(0, i - 1)])
else:
boss_id = 'NULL'
name = random.choice(names)
surname = surnames[i]
function = random.choice(workersFunctions)
values = [worker_id, boss_id, name, surname, function]
self.addRecord('pracownicy', values)
self.executeStatement("SET FOREIGN_KEY_CHECKS = 1;")
self.logger.debug("FOREIGN_KEY_CHECKS enabled")
self.logger.debug("Workers records put into 'pracownicy' table succesfully.")
"""Generate operations"""
self.logger.debug("Generating operations. Putting records to `historia_operacji` table.")
tmp = self.getRawData('biblioteki')
libraries = [record[0] for record in tmp]
tmp = self.getRawData('pracownicy')
workers_ids = [record[0] for record in tmp]
tmp = self.getRawData('czytelnicy')
readers_ids = [record[0] for record in tmp]
tmp = self.getRawData('egzemplarze')
specimens_ids = [record[0] for record in tmp]
for _ in range(10):
operation_id = 'NULL'
date = random.choice(dates)
library = random.choice(libraries)
worker_id = random.choice(workers_ids)
reader_id = random.choice(readers_ids)
specimen_id = random.choice(specimens_ids)
operationType = random.choice(operationTypes)
delay = random.choice(['NULL', random.randint(1,20)])
comment = random.choice(['NULL', random.choice(comments)])
values = [operation_id, date, library, worker_id, reader_id, specimen_id, operationType, delay, comment]
self.addRecord('historia_operacji', values)
self.logger.debug("Operations records put into 'historia_operacji' table succesfully.")
"""Commit"""
self.connection.commit()
class ProcedureController:
def __init__(self, rootWindow, database, backEvent):
self.rootWindow = rootWindow
self.backEvent = backEvent
self.database = database
# Start logger
self.logger = Logger(__name__, loggingLevel="debug")
self.logger.debug("ProcedureController logger has started.")
self.mainLabels = ["Library", "Book title", "Genre", "Publishing date"]
self.personalLabels = [
"Reader's surname", "Reader's name", "Employee's surname",
"Employee's name", "Comment(optional)"
]
self.procedureWindow = Toplevel(self.rootWindow)
self.procedureWindow.title("Check a book.")
self.procedureWindow.protocol('WM_DELETE_WINDOW', self.goBack)
self.procedureWindow.bind("<<back>>", lambda _: self.backEvent())
self.mainFrame = Frame(self.procedureWindow,
bd=4,
relief=RAISED,
width=self.procedureWindow.winfo_width() - 40,
height=self.procedureWindow.winfo_height() - 40)
self.mainFrame.pack(fill='both', side=TOP)
self.comboBoxes = list()
self.frames = list()
for lab in self.mainLabels:
self.frames.append(Frame(self.mainFrame))
if len(self.comboBoxes) == 0:
self.comboBoxes.append(
extendedComboBox(self.database, self.frames[-1], lab))
self.comboBoxes[-1].combo.configure(state='readonly')
else:
self.comboBoxes.append(
extendedComboBox(self.database, self.frames[-1], lab))
self.comboBoxes[-2].setChild(self.comboBoxes[-1])
self.comboBoxes[-1].disable()
self.frames[-1].pack(fill='both', side=TOP)
# self.frames.append(Frame(self.mainFrame,
# width=self.procedureWindow.winfo_width() - 40,
# height=20 * 4
# ))
# Label(self.frames[no], text=lab, font=("Arial Bold", 12)).grid(row=0, column=0, columnspan=3)
# entry = Entry(self.frames[no], width=20)
# entry.grid(row=0, column=3, columnspan=2)
# self.entries.append(entry)
# self.frames[no].pack(fill='both', side=TOP)
self.restFrame = Frame(self.mainFrame,
width=self.procedureWindow.winfo_width() - 40,
height=20 * 6)
self.entries = dict()
for no, lab in enumerate(self.personalLabels):
#print(no, lab)
l = Label(self.restFrame, text=lab, font=("Arial Bold", 12))
l.grid(row=no, column=0, columnspan=3)
if lab == "Comment(optional)":
# self.commentEntry = Entry(self.restFrame, width=20)
# self.commentEntry.grid(row=no, column=3, columnspan=2)
self.commentEntry = scrolledtext.ScrolledText(self.restFrame,
width=40,
height=10)
self.commentEntry.grid(row=no, column=3, columnspan=2)
continue
if lab == "Reader's surname":
val = database.executeStatement(
"SELECT `nazwisko` FROM `pracownicy`")
elif lab == "Reader's name":
val = database.executeStatement(
"SELECT `imie` FROM `pracownicy`")
elif lab == "Employee's surname":
val = database.executeStatement(
"SELECT `nazwisko` FROM `pracownicy`")
elif lab == "Employee's name":
val = database.executeStatement(
"SELECT `imie` FROM `pracownicy`")
entry = ttk.Combobox(self.restFrame, values=([""] + list(val)))
entry.configure(state='readonly')
entry.grid(row=no, column=3, columnspan=2, padx=5, pady=10)
entry.bind("<<ComboboxSelected>>", lambda _: self.refreshValues())
self.entries[l.cget("text")] = entry
self.restFrame.pack(fill='both', side=BOTTOM)
self.buttonFrame = Frame(self.procedureWindow,
bd=4,
relief=RAISED,
width=self.procedureWindow.winfo_width() - 40,
height=40)
self.buttonFrame.pack(fill='both', side=TOP)
self.hireButton = Button(self.buttonFrame,
text="Hire",
command=self.applyProcedure)
self.hireButton.pack(side=LEFT)
self.cancelButton = Button(self.buttonFrame,
text="Cancel",
command=self.goBack)
self.cancelButton.pack(side=LEFT)
def goBack(self):
self.procedureWindow.event_generate("<<back>>")
def applyProcedure(self):
args = dict()
for label, entry in self.entries.items():
if entry.get() == "":
self.logger.error("Not all entries are filled")
messagebox.showerror("Not all mandatory entries are filled!",
"Not all mandatory entries are filled!")
return
else:
args[label] = entry.get()
for combo in self.comboBoxes:
if combo.combo.get() == "":
self.logger.error("Not all entries are filled")
messagebox.showerror("Not all mandatory entries are filled!",
"Not all mandatory entries are filled!")
return
else:
args[combo.label.cget("text")] = combo.combo.get()
args["Comment(optional)"] = self.commentEntry.get('1.0', END)
try:
self.database.borrowBook(args["Library"], args["Employee's name"],
args["Employee's surname"],
args["Reader's name"],
args["Reader's surname"],
args["Book title"],
args["Publishing date"], args["Genre"],
args["Comment(optional)"])
except Exception as e:
errorNo = int(e.__str__().split()[0][1:-1])
if errorNo == 1644:
self.logger.error(f"{e.__str__().split(',')[1][:-2]}")
messagebox.showerror("Error while hiring a book.",
f"{e.__str__().split(',')[1][:-2]}")
return
else:
self.logger.error(f"{e}")
messagebox.showerror("Error while hiring a book.", f"{e}")
return
self.database.connection.commit()
self.goBack()
def refreshValues(self):
for lab, en in self.entries.items():
if lab == "Reader's surname":
if en.get() is not "":
val = self.database.executeStatement(
f"SELECT `imie` FROM `czytelnicy` " +
f"WHERE `nazwisko` = \"{en.get()}\"")
else:
val = self.database.executeStatement(
f"SELECT `imie` FROM `czytelnicy`")
self.entries["Reader's name"].configure(values=([""] +
list(val)))
#self.entries[lab].set("")
elif lab == "Reader's name":
if en.get() is not "":
val = self.database.executeStatement(
f"SELECT `nazwisko` FROM `czytelnicy` " +
f"WHERE `imie` = \"{en.get()}\"")
else:
val = self.database.executeStatement(
f"SELECT `nazwisko` FROM `czytelnicy`")
self.entries["Reader's surname"].configure(values=([""] +
list(val)))
# self.entries[lab].set("")
elif lab == "Employee's surname":
if en.get() is not "":
val = self.database.executeStatement(
f"SELECT `imie` FROM `pracownicy` " +
f"WHERE `nazwisko` = \"{en.get()}\"")
else:
val = self.database.executeStatement(
f"SELECT `imie` FROM `pracownicy`")
self.entries["Employee's name"].configure(values=([""] +
list(val)))
# self.entries[lab].set("")
elif lab == "Employee's name":
if en.get() is not "":
val = self.database.executeStatement(
f"SELECT `nazwisko` FROM `pracownicy` " +
f"WHERE `imie` = \"{en.get()}\"")
else:
val = self.database.executeStatement(
f"SELECT `nazwisko` FROM `pracownicy`")
self.entries["Employee's surname"].configure(
values=([""] + list(val)))
class App:
def __init__(self, configPath, width=640, height=480):
# Initialize logger
self.logger = Logger(__name__, loggingLevel="debug")
self.logger.debug(
"==================================================================="
)
self.logger.debug("Application logger has started.")
self.config = dict()
try:
with open(configPath) as f:
self.config = {**self.config, **json.load(f)}
except IOError as e:
self.logger.error(
f"Error occured while reading config from .json file!. Error = {e}"
)
exit(-1)
self.logger.debug("Config has been read.")
# Initialize app window
self.logger.debug("Initializing application window.")
self.window = Tk()
self.window.title("Database Application.")
self.logger.debug("Application window has been initialized.")
self.window.geometry(f"{int(width)}x{int(height)}+0+0")
self.window.resizable(0, 0)
self.window.attributes("-topmost", True)
# Setting a theme picture
self.theme = Canvas(width=width, height=height, bg='black')
self.theme.grid(column=0, row=0)
self.imagesDict = dict()
self.imagesDict["themeC"] = PhotoImage(file="theme.gif")
self.imagesDict["themeG"] = PhotoImage(file="grayscale_theme.gif")
self.logger.debug("Images has been loaded.")
self.imageOnCanvas = self.theme.create_image(
0, 0, image=self.imagesDict["themeG"], anchor=NW)
self.logger.debug("Setting grayscale theme.")
def __del__(self):
# Delete dataBase object to close connection with database
if self.database:
self.logger.debug("Deleting database object.")
try:
del self.database
except:
pass
self.logger.info("Closing application.")
self.logger.debug(
"==================================================================="
)
def main(self):
self.logonWindow = LogonController(self.window, self.logonEvent)
self.window.mainloop()
def logonEvent(self, database):
try:
self.logonWindow.tlLogon.destroy()
except Exception as e:
self.logger.error(
f"An error occurred while destroying logon window! Exception = {e}"
)
self.window.attributes("-topmost", True)
self.window.attributes("-topmost", False)
self.theme.itemconfig(self.imageOnCanvas,
image=self.imagesDict["themeC"])
self.logger.debug("Setting coloured theme.")
self.database = database
self.mainWindow = MainController(self.window, self.database,
self.logoutEvent)
def logoutEvent(self, _):
try:
self.mainWindow.content.destroy()
except Exception as e:
self.logger.error(
f"An error occurred while destroying logon window! Exception = {e}"
)
self.logger.info("Signed out")
self.theme.itemconfig(self.imageOnCanvas,
image=self.imagesDict["themeG"])
self.database = None
self.logonWindow = LogonController(self.window, self.logonEvent)