def create_model(self, query: Query) -> str:
sql_encoded_content = self._encode_model(query)
columns = self.get_model_definition(query)
base_query = 'INSERT INTO {} '.format(query.get_model_name())
key_list = []
value_list = []
print("Creating model with: {}".format(query.get_content()))
for key in columns:
value = sql_encoded_content[key]
print(key, value)
key_list.append(key)
value_list.append(value)
key_string = "(" + ' , '.join(key_list) + ")"
value_string = "(" + ' , '.join(value_list) + ")"
sql_query = base_query + key_string + ' VALUES ' + value_string + ';'
print("Query is {}".format(sql_query))
connection = sqlite3.connect(self.cnx.get_path(), detect_types=sqlite3.PARSE_DECLTYPES)
cursor = connection.cursor()
cursor.execute(sql_query)
connection.commit()
print("Model created {}".format(cursor.lastrowid))
if cursor.lastrowid:
return self._decode_model(sql_encoded_content, query)
def get_model_definition(self, query: Query) -> list:
print("In get_model_definition for {}".format(query.get_model_name()))
sql_query = 'PRAGMA TABLE_INFO({})'.format(query.get_model_name())
connection = sqlite3.connect(self.cnx.get_path())
cursor = connection.cursor()
cursor.execute(sql_query)
columns_tuples = cursor.fetchall()
columns = [tup[1] for tup in columns_tuples]
print("Columns are {}".format(columns))
return columns
def retrieve_by_pk(self, query: Query) -> Optional[dict]:
print("In retrieve_by_pk")
for field_name, field_value in query.get_fields().items():
if field_value.is_primary():
pk_name = field_name
break
sql_query = "SELECT * FROM {table} WHERE {pk_name} = '{pk_value}';".format(table=query.get_model_name(),
pk_name=pk_name,
pk_value=query.get_content())
print("Query is {}".format(sql_query))
connection = sqlite3.connect(self.cnx.get_path(), detect_types=sqlite3.PARSE_DECLTYPES)
cursor = connection.cursor()
cursor.execute(sql_query)
result = cursor.fetchall()
if result:
model = result[0]
print(model)
columns = self.get_model_definition(query)
model_dict = {column: model[index] for index, column in enumerate(columns)}
return self._decode_model(model_dict, query)
else:
raise exceptions.DoesNotExist()
def _decode_model(self, sql_encoded_content: dict, query: Query) -> dict:
decoded_content = {}
for field_name, field_item in query.get_fields().items():
encoded_value = sql_encoded_content[field_name]
if field_item.get_type() == "ObjectField":
print("Trying to load object field {}".format(encoded_value[1:-1]))
decoded = json.loads(encoded_value[1:-1])
decoded_content[field_name] = decoded
elif field_item.get_type() == "JSONField":
print("Trying to load json field {}".format(encoded_value[1:-1]))
decoded = json.loads(encoded_value[1:-1])
decoded_content[field_name] = decoded
elif field_item.get_type() == "StringField":
decoded = encoded_value[1:-1]
decoded_content[field_name] = decoded
elif field_item.get_type() == "DateTimeField":
decoded = encoded_value[1:-1]
decoded_content[field_name] = decoded
# TODO, return datetime object
elif field_item.get_type() == "IntegerField":
decoded = int(encoded_value)
decoded_content[field_name] = decoded
else:
raise Exception("Unsupported field type")
return decoded_content
def _get_defition_from_db(cls) -> None:
"""
Retrieves the model fields from the DB in a list of field names in the correct order
"""
Model.__new__(cls, internal=True)
# Here we create the query and pass it to the executor
query = Query(model_name=cls.model_name,
model_fields=None,
action="definition")
response = cls.executor.execute(query)
def _initialise_in_db(cls) -> None:
"""
This will add the model definition to the DB
"""
Model.__new__(cls, internal=True)
# Here we create the query and pass it to the executor
query = Query(model_name=cls.model_name,
model_fields=cls.model_fields,
action="initialise")
response = cls.executor.execute(query)
def delete(self) -> bool:
"""
Delete this model item from the DB
Once deleted, this model instance will be unusable
"""
query = Query(model_name=self._model_name,
model_fields=self._model_fields,
action="delete",
content=self._content)
response = self._executor.execute(query)
self._deleted = True
def update_model(self, query: Query) -> dict:
# Retrieve the list of column names in the correct order
columns = self.get_model_definition(query)
# Determine the name of the primary key
for field_name in query.get_fields().keys():
field_value = query.get_fields()[field_name]
if field_value.is_primary():
pk_name = field_name
break
# Encode the content into an SQL format
sql_encoded_content = self._encode_model(query)
# Create list of 'key=value' strings
key_value_list = []
for key in columns:
print("Key is {}".format(key))
value = sql_encoded_content[key]
print(key, value)
key_value_list.append('{}={}'.format(key, value))
key_value_string = ' , '.join(key_value_list)
pk_value = sql_encoded_content[pk_name]
sql_query = 'UPDATE {model_name} SET {key_value_string} WHERE {pk_name} = {pk_value}'.format(model_name=query.get_model_name(),
key_value_string=key_value_string,
pk_name=pk_name,
pk_value=pk_value)
print("Query is {}".format(sql_query))
connection = sqlite3.connect(self.cnx.get_path(), detect_types=sqlite3.PARSE_DECLTYPES)
cursor = connection.cursor()
cursor.execute(sql_query)
connection.commit()
if cursor.rowcount:
return self._decode_model(sql_encoded_content, query)
else:
raise Exception("Model update failed")
def delete_model(self, query: Query) -> bool:
print("In delete_model")
# Determine the name and value of the primary key
for field_name in query.get_fields().keys():
field_value = query.get_fields()[field_name]
if field_value.is_primary():
pk_name = field_name
pk_value = query.get_content()[field_name]
break
sql_query = "DELETE FROM {table} WHERE {pk_name} = '{pk_value}';".format(table=query.get_model_name(),
pk_name=pk_name,
pk_value=pk_value)
print("The query is {}".format(sql_query))
connection = sqlite3.connect(self.cnx.get_path(), detect_types=sqlite3.PARSE_DECLTYPES)
cursor = connection.cursor()
cursor.execute(sql_query)
result = cursor.fetchall()
print("Result is {}".format(result))
connection.commit()
def retrieve(cls, primary_key_value) -> Optional[ModelInstance]:
"""
Retrieve a single model by primary key
"""
Model.__new__(cls, internal=True)
query = Query(model_name=cls.model_name,
model_fields=cls.model_fields,
action="retrieve",
content=primary_key_value)
response = cls.executor.execute(query)
return ModelInstance(model_name=cls.model_name,
model_fields=cls.model_fields,
model_content=response) if response else None
def _encode_model(self, query: Query):
encoded_content = {}
for field_name, field_item in query.get_fields().items():
value = query.get_content()[field_name]
print("In encode, item is {}".format(field_name))
if field_item.get_type() == "ObjectField":
encoded = json.dumps(value.serialise()).replace('\'', '\"')
encoded_content[field_name] = "'{}'".format(encoded)
elif field_item.get_type() == "JSONField":
encoded = json.dumps(value).replace('\'', '\"')
encoded_content[field_name] = "'{}'".format(encoded)
elif field_item.get_type() == "StringField" or field_item.get_type() == "DateTimeField":
encoded = value
encoded_content[field_name] = "'{}'".format(encoded)
else:
encoded = value
encoded_content[field_name] = str(encoded)
return encoded_content
def retrieve_all(cls) -> ModelSet:
"""
Retrieve all of the model items from the db and returns them in a model set
"""
Model.__new__(cls, internal=True)
query = Query(model_name=cls.model_name,
model_fields=cls.model_fields,
action="all")
response = cls.executor.execute(query)
print("Retrieve all response is {}".format(response))
return ModelSet([
ModelInstance(model_name=cls.model_name,
model_fields=cls.model_fields,
model_content=i) for i in response
])
def execute(self, query: Query):
"""
Here we will route the queries to the correct adapter
"""
router = {
'retrieve': self.adapter.retrieve_by_pk,
'all': self.adapter.retrieve_all,
'create': self.adapter.create_model,
'update': self.adapter.update_model,
'delete': self.adapter.delete_model,
'filter': self.adapter.filter_models,
'initialise': self.adapter.initialise_model,
'definition': self.adapter.get_model_definition
}
return router[query.get_action()](query)
def save(self) -> bool:
"""
Update all of the rows for this model item in the db
"""
if self._deleted:
print("Raising exception")
raise exceptions.InstanceDeletedException(
"Attempting to save {} after deletion".format(
self._model_name))
#fields_to_update = {key: self.content[key] for key in self._staged_changes}
query = Query(model_name=self._model_name,
model_fields=self._model_fields,
action="update",
content=self._content)
response = self._executor.execute(query)
print("Saving the {0}, there are changes to fields {1}".format(
self._model_name, self._staged_changes))
def create(cls, **kwargs) -> ModelInstance:
"""
Create a model item in the DB
"""
Model.__new__(cls, internal=True)
# Initialise the content of this model
content = dict()
# Here we validate that the model is being initialised with enough information
for field_name, definition in cls.model_fields.items():
if field_name in kwargs:
# Check if it is in the kwargs
definition.validate(kwargs[field_name])
print("Field name {} is class {}".format(
field_name, kwargs[field_name].__class__.__name__))
# If it is a foreign key (the value is a ModelInstance)
if isinstance(kwargs[field_name], ModelInstance):
print("Is model instance")
# We convert the value to the primary key and primary value
primary_key = kwargs[field_name].get_primary_key()
print("Primary key is {}".format(primary_key))
content[primary_key] = kwargs[field_name].get(primary_key)
continue
content[field_name] = kwargs[field_name]
elif definition.has_default():
# Check if it has a default value
content[field_name] = definition.get_default()
else:
raise Exception(
"{} missing as parameter and has no default".format(
field_name))
# Here we create the query and pass it to the executor
query = Query(model_name=cls.model_name,
model_fields=cls.model_fields,
action="create",
content=content)
response = cls.executor.execute(query)
return ModelInstance(model_name=cls.model_name,
model_fields=cls.model_fields,
model_content=response) if response else None
def retrieve_all(self, query: Query) -> list:
print("Getting models")
sql_query = "SELECT * FROM {table};".format(table=query.get_model_name())
print("Query is {}".format(sql_query))
print("Path is {}".format(self.cnx.get_path()))
connection = sqlite3.connect(self.cnx.get_path(), detect_types=sqlite3.PARSE_DECLTYPES)
cursor = connection.cursor()
cursor.execute(sql_query)
models = cursor.fetchall()
# Models is a list of tuples, so we will convert it into a list of dictionaries
columns = self.get_model_definition(query)
list_of_models = []
for model in models:
model_dict = {column: model[index] for index, column in enumerate(columns)}
decoded_dict = self._decode_model(model_dict, query)
list_of_models.append(decoded_dict)
print(list_of_models)
return list_of_models
def initialise_model(self, query: Query) -> None:
field_list = []
add_to_end = [] # Foreign keys need an extra bit of string added to the end
print("Query fields are {}".format(query.get_fields()))
for key, value in query.get_fields().items():
is_foreign = False
if value.get_type() == "ForeignKey":
is_foreign = True
# It is a foreign key, so we will get the primary key of the foreign key
reference = value.get_reference()
reference_fields = reference.get_fields()
reference_model_name = reference.get_model_name()
for reference_field_name, reference_field in reference_fields.items():
if reference_field.is_primary():
# We will overwrite the key, value, with that of the reference primary key for the rest of this process
key = reference_field_name
value = reference_field
foreign_query = 'FOREIGN KEY({key}) REFERENCES {reference_table}(id)'.format(key=key,
reference_table=reference_model_name)
add_to_end.append(foreign_query)
if value.get_type() == "StringField":
sql_type = "TEXT"
elif value.get_type() == "IntegerField":
sql_type = "INTEGER"
elif value.get_type() == "DateTimeField":
sql_type = "TIMESTAMP"
elif value.get_type() == "ObjectField":
sql_type = "TEXT"
elif value.get_type() == "JSONField":
sql_type = "TEXT"
else:
raise exceptions.UnsupportedAdapterField()
if value.is_primary() and not is_foreign:
sql_type += " PRIMARY KEY"
field_string = "{} {}".format(key, sql_type)
field_list.append(field_string)
joined_fields = "(" + ','.join(field_list + add_to_end) + ")"
sql_query = 'CREATE TABLE IF NOT EXISTS {} '.format(query.get_model_name()) + joined_fields + ';'
print("Query is {}".format(sql_query))
with sqlite3.connect(self.cnx.get_path(), detect_types=sqlite3.PARSE_DECLTYPES) as conn:
conn.cursor().execute(sql_query)
if conn:
conn.close