diff options
Diffstat (limited to 'day9/task5_vue/backend/database')
| -rw-r--r-- | day9/task5_vue/backend/database/database.py | 29 | ||||
| -rw-r--r-- | day9/task5_vue/backend/database/field_types.py | 128 | ||||
| -rw-r--r-- | day9/task5_vue/backend/database/scheme.py | 46 | ||||
| -rw-r--r-- | day9/task5_vue/backend/database/validators.py | 96 | ||||
| -rw-r--r-- | day9/task5_vue/backend/database/wrappers.py | 149 |
5 files changed, 448 insertions, 0 deletions
diff --git a/day9/task5_vue/backend/database/database.py b/day9/task5_vue/backend/database/database.py new file mode 100644 index 0000000..87982cf --- /dev/null +++ b/day9/task5_vue/backend/database/database.py @@ -0,0 +1,29 @@ +from backend.database.wrappers import Wrapper, MySQLWrapper + +import logging + + +def initialize_databases(configs, schemes): + for config in configs: + if config['type'] == 'mysql': + logger = logging.getLogger('backendDebug') + + logger.debug('Connected') + logger.debug(f'Trying to connect to database "{config["db_name"]}@{config["host"]}"...') + + wrapper = MySQLWrapper(config['host'], config['username'], config['password'], config['db_name']) + wrappers[config['name']] = wrapper + + cursor = wrapper.connection.cursor() + for scheme in schemes: + logger.debug(f'Preparing table "{scheme.meta["name"]}"...') + cursor.execute('START TRANSACTION; {} COMMIT;'.format(scheme.get_create_line())) + wrapper.schemes[scheme.meta['name']] = scheme + cursor.close() + + +def get_wrapper_for(database_name) -> Wrapper: + return wrappers.get(database_name, None) + + +wrappers = {} diff --git a/day9/task5_vue/backend/database/field_types.py b/day9/task5_vue/backend/database/field_types.py new file mode 100644 index 0000000..d0b2c3b --- /dev/null +++ b/day9/task5_vue/backend/database/field_types.py @@ -0,0 +1,128 @@ +from abc import ABC, abstractmethod + + +class Field(ABC): + def __init__(self, validators=None): + self.validators = [] + if validators is not None: + self.validators.extend(validators) + + def _validate_attributes(self): + pass + + @property + @abstractmethod + def sql_line(self): + pass + + def validate(self, value): + for validator in self.validators: + validator.validate(value, self) + + +class TextField(Field): + def __init__(self, max_length, is_variable_length, nullable, default, validators=None): + super().__init__(validators) + + self.max_length = max_length + self.is_variable_length = is_variable_length + self.nullable = nullable + self.default = default + + self.data_type = str + + @property + def sql_line(self): + request = [ + ('varchar' if self.is_variable_length else 'char') + f'({self.max_length})', + 'NULL' if self.nullable else 'NOT NULL' + ] + + if self.default is not None or self.nullable: + request.append(f'DEFAULT "{self.default}"') + + return ' '.join(request) + + +class IntegerField(Field): + def __init__(self, max_length, nullable, is_auto_increment, default, validators=None): + super().__init__(validators) + + self.max_length = max_length + self.nullable = nullable + self.is_auto_increment = is_auto_increment + self.default = default + + self.data_type = int + + @property + def sql_line(self): + request = [ + 'int' + (f'({self.max_length})' if self.max_length is not None else ''), + 'NULL' if self.nullable else 'NOT NULL' + ] + + if self.default is not None: + request.append(f'DEFAULT "{self.default}"') + + if self.is_auto_increment: + request.append('AUTO_INCREMENT') + + return ' '.join(request) + + +class DateField(Field): + def __init__(self, nullable, default, validators=None): + super().__init__(validators) + + self.nullable = nullable + self.default = default + + self.data_type = str + + @property + def sql_line(self): + request = ['date', 'NULL' if self.nullable else 'NOT NULL'] + + if self.default is not None: + request.append(f'DEFAULT "{self.default}"') + + return ' '.join(request) + + +class TimeField(Field): + def __init__(self, nullable, default, validators=None): + super().__init__(validators) + + self.nullable = nullable + self.default = default + + self.data_type = str + + @property + def sql_line(self): + request = ['time', 'NULL' if self.nullable else 'NOT NULL'] + + if self.default is not None: + request.append(f'DEFAULT "{self.default}"') + + return ' '.join(request) + + +class DatetimeField(Field): + def __init__(self, nullable, default, validators=None): + super().__init__(validators) + + self.nullable = nullable + self.default = default + + self.data_type = str + + @property + def sql_line(self): + request = ['datetime', 'NULL' if self.nullable else 'NOT NULL'] + + if self.default is not None: + request.append(f'DEFAULT "{self.default}"') + + return ' '.join(request) diff --git a/day9/task5_vue/backend/database/scheme.py b/day9/task5_vue/backend/database/scheme.py new file mode 100644 index 0000000..f783f79 --- /dev/null +++ b/day9/task5_vue/backend/database/scheme.py @@ -0,0 +1,46 @@ +from backend.database.validators import ValidationError + + +class DatabaseScheme: + meta = {} + fields = {} + + @classmethod + def get_create_line(cls) -> str: + fields = cls.fields + meta = cls.meta + + lines = [f'`{name}` {field.sql_line}' for name, field in fields.items()] + + if 'primary_key' in meta: + primary_key = ', PRIMARY KEY (`{}`)'.format(meta['primary_key']) + else: + primary_key = '' + + return 'CREATE TABLE IF NOT EXISTS `{name}` (\n{fields}{primary_key}\n);'.format( + name=meta['name'], fields=',\n'.join(lines), primary_key=primary_key + ) + + @classmethod + def validate(cls, data: dict) -> dict: + data_fields = set(data.keys()) + scheme_fields = set(cls.fields.keys()) + + defined_fields = scheme_fields & data_fields + not_defined_fields = scheme_fields ^ defined_fields + + validation_results = {'error': False} + for field in not_defined_fields: + validation_results['error'] = True + validation_results[field] = 'Field is not defined' + + for field in defined_fields: + try: + cls.fields[field].validate(data[field]) + except ValidationError as e: + validation_results['error'] = True + validation_results[field] = str(e) + else: + validation_results[field] = None + + return validation_results diff --git a/day9/task5_vue/backend/database/validators.py b/day9/task5_vue/backend/database/validators.py new file mode 100644 index 0000000..e4b7310 --- /dev/null +++ b/day9/task5_vue/backend/database/validators.py @@ -0,0 +1,96 @@ +from abc import ABC, abstractmethod +import re + + +class ValidationError(Exception): + pass + + +class Validator(ABC): + @staticmethod + @abstractmethod + def validate(value, field_object): + pass + + +class ValidateNull(Validator): + @staticmethod + def validate(value, field_object): + if value is None and not field_object.nullable: + raise ValidationError('Value cannot be NULL') + + +class ValidateType(Validator): + @staticmethod + def validate(value, field_object): + if field_object.nullable and value is None: + return + if not isinstance(value, field_object.data_type): + raise ValidationError('Value is of wrong type') + + +class ValidateLength(Validator): + @staticmethod + def validate(value, field_object): + if len(str(value)) > field_object.max_length: + raise ValidationError('Value has too many digits') + + +class ValidateTime(Validator): + @staticmethod + def validate(value, field_object): + match = re.fullmatch(r'(\d\d):(\d\d):(\d\d)', value) + if not match: + raise ValidationError('Wrong time format') + else: + hour, minute, second = map(int, match.groups()) + if hour not in range(0, 24): + raise ValidationError('Wrong hour value') + + if minute not in range(0, 60): + raise ValidationError('Wrong minute value') + + if second not in range(0, 60): + raise ValidationError('Wrong second value') + + +class ValidateDate(Validator): + @staticmethod + def validate(value, field_object): + match = re.fullmatch(r'(\d\d\d\d)-(\d\d)-(\d\d)', value) + if not match: + raise ValidationError('Wrong date format') + else: + year, month, day = map(int, match.groups()) + if year < 0: + raise ValidationError('Wrong year value') + + if month not in range(1, 12): + raise ValidationError('Wrong month value') + + if month == 2: + if year % 4 == 0 and year % 100 != 0 or year % 400 == 0: + febr_range = range(1, 29) + else: + febr_range = range(1, 28) + if day not in febr_range: + raise ValidationError('Wrong day value') + else: + days_count = { + 1: 31, 3: 31, 4: 30, 5: 31, + 6: 30, 7: 31, 8: 31, 9: 30, + 10: 31, 11: 30, 12: 31 + }.get(month) + if day not in range(1, days_count): + raise ValidationError('Wrong day value') + + +class ValidateDatetime(Validator): + @staticmethod + def validate(value, field_object): + datetime = value.split() + if len(datetime) != 2: + raise ValidationError('Wrong datetime format') + else: + ValidateDate.validate(datetime[0], {}) + ValidateTime.validate(datetime[1], {})
\ No newline at end of file diff --git a/day9/task5_vue/backend/database/wrappers.py b/day9/task5_vue/backend/database/wrappers.py new file mode 100644 index 0000000..824d19e --- /dev/null +++ b/day9/task5_vue/backend/database/wrappers.py @@ -0,0 +1,149 @@ +from abc import ABC, abstractmethod + +import MySQLdb + + +class Wrapper(ABC): + def __init__(self): + self.schemes = {} + + @abstractmethod + def clear_table(self, table_name): + pass + + @abstractmethod + def get_column_names(self): + pass + + @abstractmethod + def insert_one(self, table_name, row, field_names=None): + pass + + @abstractmethod + def update(self, table_name, expressions, conditions): + pass + + @abstractmethod + def delete_from(self, table_name, conditions): + pass + + @abstractmethod + def get_data(self, table_name): + pass + + @abstractmethod + def get_rows(self, table_name, conditions): + pass + + +class MySQLWrapper(Wrapper): + def __init__(self, host, username, password, db_name): + super().__init__() + self.connection = MySQLdb.connect( + host=host, + user=username, + passwd=password, + db=db_name + ) + + def clear_table(self, table_name): + cursor = self.connection.cursor() + cursor.execute(f"START TRANSACTION; DELETE FROM `{table_name}`; COMMIT;") + cursor.close() + + def get_column_names(self): + cursor = self.connection.cursor() + cursor.execute('DESCRIBE table_task1;') + table_structure = cursor.fetchall() + table_headers = [field[0] for field in table_structure] + return table_headers + + def insert_one(self, table_name, row, field_names=None): + cursor = self.connection.cursor() + + if field_names is not None: + field_names_formatted = [] + for name in field_names: + if name != 'NULL' or not name.isnumeric(): + name = f'`{name}`' + field_names_formatted.append(name) + field_names_formatted = f'({",".join(field_names_formatted)})' + else: + field_names_formatted = '' + + row_formatted = [] + for value in row: + if value == 'NULL' or value.isnumeric(): + row_formatted.append(value) + else: + row_formatted.append(f'"{value}"') + + request = "START TRANSACTION; INSERT INTO `{}` {} VALUES ({}); COMMIT;".format( + table_name, field_names_formatted, ",".join(row_formatted) + ) + + print(request) + + cursor.execute(request) + cursor.close() + + def update(self, table_name, expressions, conditions): + cursor = self.connection.cursor() + + expressions_formatted = [] + for field_name, value in expressions.items(): + if value != 'NULL' or not value.isnumeric(): + value = f'"{value}"' + expressions_formatted.append(f'`{field_name}`={value}') + + conditions_formatted = [] + for field_name, value in conditions.items(): + if value != 'NULL' or not value.isnumeric(): + value = f'"{value}"' + conditions_formatted.append(f'`{field_name}`={value}') + + cursor.execute("START TRANSACTION; UPDATE `{}` SET {} WHERE {}; COMMIT;".format( + table_name, ','.join(expressions_formatted), ' AND '.join(conditions_formatted) + )) + + cursor.close() + + def delete_from(self, table_name, conditions): + cursor = self.connection.cursor() + + conditions_formatted = [] + for field_name, value in conditions.items(): + if value != 'NULL' or not value.isnumeric(): + value = f'"{value}"' + conditions_formatted.append(f'`{field_name}`={value}') + + cursor.execute("START TRANSACTION; DELETE FROM `{}` WHERE {}; COMMIT;".format( + table_name, ' AND '.join(conditions_formatted) + )) + + cursor.close() + + def get_data(self, table_name): + cursor = self.connection.cursor() + + cursor.execute(f'SELECT * FROM `{table_name}`;') + content = list(map(list, cursor.fetchall())) + + cursor.close() + return content + + def get_rows(self, table_name, conditions): + cursor = self.connection.cursor() + + conditions_formatted = [] + for field_name, value in conditions.items(): + if value != 'NULL' or not value.isnumeric(): + value = f'"{value}"' + conditions_formatted.append(f'`{field_name}`={value}') + + cursor.execute(f'SELECT * FROM `{table_name}` WHERE {" AND ".join(conditions_formatted)};') + content = list(map(list, cursor.fetchall())) + + cursor.close() + + return content |