def storeFeatures(id, object_type, feature):
"""
Stores a list of tuple of features inside the object_features table. For each feature inside the features tuple, it first queries the features_lkp table to check if that feature already exists inside the table.
If it does, it retrieves that feature's feature_id. Otherwise, it inserts that feature into the features_lkp table and returns the newly inserted feature's feature_id.
It then uses the feature's feature_id and the id of the object to create a linking row inside the object_features table
Keyword arguements:
id -- ID of the Object that the features are being linked against.
object_type -- Object Type of the Object that the features are being linked against.
features -- Tuple of features that are going to be linked against the object.
"""
try:
#Only insert if the feature doesn't exist.
check_feature_exists = f"SELECT feature_id FROM features_lkp WHERE UPPER( description ) = '{str.upper( feature )}'"
cur.execute(check_feature_exists, "")
row = cur.fetchone()
if row == None:
feature_id = returnNextSerialID('object_features', 'feature_id')
#We need to store the new feature inside the features table.
cur.execute(
f""" INSERT INTO features_lkp( feature_id, description) VALUES( {feature_id}, '{cleanForSQL(feature)}' )""",
"")
else:
feature_id = row[0]
#Once we've acquired the feature_id...
object_features_insert_statement = f""" INSERT INTO object_features( id, object_type, feature_id, entered_when, entered_who )
VALUES( {id}, {object_type}, {feature_id}, current_timestamp, 1 )"""
cur.execute(object_features_insert_statement, "")
return True
except (Exception, psycopg2.DatabaseError) as error:
print("Error in INSERTING Feature " + feature + "\n" + "Error: " +
error)
return False
def storeFile(self, commit):
try:
#Need to check if we are saving an image or a document.
if self.file_type == FILE_TYPE_Images:
#This means that we are saving an image.
image_id = returnNextSerialID('images', 'image_id')
File.checkAndStoreDocType(self, False)
if self.image_date_taken is not None:
insert_image_date_taken = f"to_timestamp( '{convertJSONDate(self.image_date_taken)}', 'YYYY-MM-DD HH24:MI:SS' )"
else:
insert_image_date_taken = "NULL"
images_insert_statement = f""" INSERT into images( advert_id, date_taken, url )
VALUES( {ifNotNone(self.image_advert_id, "NULL")}, {insert_image_date_taken}, '{self.image_images_url}' )"""
cur.execute(images_insert_statement, "")
cur.execute(
f""" INSERT INTO files( file_id, file_type, object_type, keyval1, keyval2, document_type, entered_when )
VALUES( {image_id}, '{FILE_TYPE_Images}', {self.object_type}, '{self.keyval1}', '{self.keyval2}', {self.document_type}, current_timestamp ) """,
"")
if commit:
conn.commit()
return True
elif self.file_type == FILE_TYPE_Documents:
pass
else:
raise ValueError(str(self) + " has an invalid file type")
except (ValueError, Exception, psycopg2.DatabaseError) as error:
print("Error in INSERTING File with File ID " + str(image_id) +
"\n" + "Error " + error)
return False
def checkAndStoreDocType(self, commit):
try:
if not checkRowExists(
f"SELECT 1 FROM document_type WHERE UPPER(description) = '{self.document_type_description}'"
):
document_type = returnNextSerialID('document_type',
'document_type')
cur.execute(
f""" INSERT INTO document_type(description)
VALUES( '{self.document_type_description}' )""",
"")
if commit:
conn.commit()
self.document_type = document_type
else:
cur.execute(
f"SELECT document_type FROM document_type WHERE UPPER(description) = '{self.document_type_description}'",
"")
result = cur.fetchone()
self.document_type = result[0]
except (Exception, psycopg2.DatabaseError) as error:
print("Error in Checking Description of File with File ID " +
str(self.file_id) + " with Document Type Description " +
self.document_type_description + "\n" + "Error: " + error)
return None
def storeSuburb(self, suburbName, state, postCode):
try:
new_suburb_id = returnNextSerialID('suburbs', 'suburb_id')
cur.execute(
""" INSERT INTO suburbs( name, state, is_completed, postcode )
VALUES( %s, %s, %s, %s ) """,
(suburbName, state, False, postCode))
return new_suburb_id
except (Exception, psycopg2.DatabaseError) as error:
print(error)
def storeContactDetails(self, commit):
"""
This Function stores a new set of contact details into the contact_details table.
It first checks to see if the contact_details_id variable has been obtained for the class instance, if it has not, it's retrieved from the contact_details_type_lkp table.
if that contact_details_type doesn't exist yet, its inserted into the contact_details_type_lkp table.
It then stores the details into the contact_details table.
:param self: Contact Details Instance.
:param commit: If True, then the insert is instantly commited. Otherwise no commit is done.
...
:return: True if the insert succeeds, False otherwise.
"""
try:
#First, check to see if the contact_details_type has already been stored inside the contact_details_type_lkp table
if self.contact_details_id is None:
if not checkRowExists(
f"SELECT 1 FROM contact_details_type_lkp WHERE UPPER( description ) = UPPER( '{self.contact_details_type}' ) "
):
self.contact_details_id = returnNextSerialID(
'contact_details_type_lkp', 'contact_details_type')
#If the contact_details_type hasn't been stored...
contact_details_type_insert_statement = f"""INSERT INTO contact_details_type_lkp( description )
VALUES( '{self.contact_details_type}' ) """
cur.execute(contact_details_type_insert_statement, "")
else:
#Get the matching contact_details_id from the contact_details_type_lkp table.
cur.execute(
f"SELECT contact_details_type FROM contact_details_type_lkp WHERE description = '{self.contact_details_type}'",
"")
row = cur.fetchone()
self.contact_details_id = row[0]
#Once we've gotten the contact_details_id....
cur.execute(
f""" INSERT INTO contact_details( contact_details_type, object_id, object_type, details, entered_when )
VALUES( {self.contact_details_id}, {self.object_id}, {self.object_type}, '{self.details}', current_timestamp ) """,
"")
if commit:
conn.commit()
return True
except (Exception, psycopg2.DatabaseError) as error:
print("Error in INSERTING Contact Details Type " +
str(self.contact_details_type) + " for Object with ID " +
str(self.object_id) + " with Object Type " +
str(self.object_type) + "\n" + "Error: " + error)
return False
def storeAddress(self):
try:
address_id = returnNextSerialID('address', 'address_id')
cur.execute(
"""INSERT INTO address( full_address, street_name, street_number, suburb_id, zone, lot_number, latlong )
VALUES( %s, %s, %s, %s, %s, %s, %s )""",
(self.full_address, self.street_name, self.street_number,
self.suburb_id, self.zone, self.lot_number,
VarIf(self.long is None or self.lat is None, None,
f"SRID=4326;Point({self.long} {self.lat})")))
return address_id
except (Exception, psycopg2.DatabaseError) as error:
print("Error in INSERTING Address for Address ID " + "\n" +
"Error: " + error)
return None
def saveProperty(self, commit):
#store the propertyObject inside a text file in case we want to debug afterwards.
debug = open("debug.txt", "w")
debug.write(json.dumps(self.rawJson))
try:
#Handle the address first, if required.
if self.address_object.address_id is None:
new_address_id = self.address_object.storeAddress()
if new_address_id is None:
return None
#Get the next property id
next_property_id = returnNextSerialID('properties', 'property_id')
if not self.saveSalesHistory(next_property_id):
return None
#Save the Files next.
for item in self.files:
item.keyval1 = next_property_id
item.storeFile(False)
#Store the Features Next
for feature in self.features:
storeFeatures(next_property_id, OBJECT_Property, feature)
#Now save the property.
cur.execute(
"""INSERT INTO properties( domain_prop_id, shape_of_land, on_market, address_id, area_size, num_bedrooms, num_bathrooms, entered_when, raw_json )
VALUES( %s, %s, %s, %s, %s, %s, %s, current_timestamp, %s )""",
(self.domain_property_id, self.landShape, self.onMarket,
new_address_id, self.areaSize, self.numBedrooms,
self.numBathrooms, self.rawJson))
if commit:
conn.commit()
return next_property_id
except (Exception, psycopg2.DatabaseError) as error:
print("Error in INSERTING Property with Domain Property ID " +
str(self.domain_property_id) + "\n" + error)
return None
def storeAgency(self):
try:
#If the Agency ID hasn't been set for this Agency, it means that hasn't been saved yet into the database...
if self.agency_id is None:
self.agency_id = returnNextSerialID('agencies', 'agency_id')
if not self.getPrincipalAgent():
return None
#Convert the data inside the Agency object into Classes
self.convertToClasses()
#Save all contact details first.
for contact in self.contact_details_converted:
contact.storeContactDetails(False)
#Store the links to the agency banner, website and standard logo.
self.agency_website.storeContactDetails(False)
self.agency_banner.storeFile(False)
self.agency_logo_standard.storeFile(False)
#Store the address first so that we can link the address to the correct row inside the agencies table.
address_id = self.address.storeAddress()
agency_json = json.dumps(self.raw_agency_json)
cur.execute(
"""INSERT INTO agencies( description, num_sale, num_rent, entered_when, raw_agency_json, name, principal_agent, domain_agency_id, address_id )
VALUES( %s, %s, %s, current_timestamp, %s, %s, %s, %s, %s)""",
(self.agency_description, self.num_sale, self.num_rent,
cleanForSQL(agency_json), self.name, self.principal_agent_id,
self.domain_agency_id, address_id))
return self.agency_id
except (Exception, psycopg2.DatabaseError) as error:
print("Error in INSERTING New Agency for Agency " + self.name +
"\n" + error)
return None
def storeAgent(self, commit):
try:
self.agent_id = returnNextSerialID('agents', 'agent_id')
#Stores information contained inside the Agent Object into the Agents table.
cur.execute(
"""INSERT INTO agents( domain_agent_id, entered_when, first_name, last_name, profile_text )
VALUES( %s, current_timestamp, %s, %s, %s )""",
(self.domain_agent_id, cleanForSQL(self.first_name),
cleanForSQL(self.last_name), self.profile_text))
#Store the link between the Agent and the Agency inside the agencies_agent table.
cur.execute(
"""INSERT INTO agencies_agent( agency_id, agent_id, entered_when )
VALUES( %s, %s, current_timestamp)""",
(self.agency_id, self.agent_id))
if self.email is not None:
#Store the agent's emaia
contactDetails_email = ContactDetails("agent_email",
OBJECT_Agent,
self.agent_id,
self.email)
if not contactDetails_email.storeContactDetails(False):
raise Exception(psycopg2.DatabaseError)
if self.phone is not None:
#Store the agent's phone number
contactDetails_phone = ContactDetails("agent_phone_number",
OBJECT_Agent,
self.agent_id,
self.phone)
if not contactDetails_phone.storeContactDetails(False):
raise Exception(psycopg2.DatabaseError)
if self.facebook_url is not None:
contactDetails_facebook = ContactDetails(
"agent_facebook_url", OBJECT_Agent, self.agent_id,
self.facebook_url)
#Attempt to save the facebook details
if not contactDetails_facebook.storeContactDetails(False):
raise Exception(psycopg2.DatabaseError)
if self.twitter_url is not None:
contactDetails_twitter = ContactDetails(
"agent_twitter_url", OBJECT_Agent, self.agent_id,
self.twitter_url)
#Attempt to save the twitter details
if not contactDetails_twitter.storeContactDetails(False):
raise Exception(psycopg2.DatabaseError)
if self.photo is not None:
#Save the mugshot and agent photo.
file_agentPhoto = File(FILE_TYPE_Images, OBJECT_Agent,
self.agent_id, None, "agent_photo")
file_agentPhoto.addImageDetails(None, None, self.photo)
if not file_agentPhoto.storeFile(False):
raise Exception(psycopg2.DatabaseError)
if self.mugshot_url is not None:
file_agentMugShot = File(FILE_TYPE_Images, OBJECT_Agent,
self.agent_id, None, "agent_mugshot")
file_agentMugShot.addImageDetails(None, None, self.mugshot_url)
if not file_agentMugShot.storeFile(False):
raise Exception(psycopg2.DatabaseError)
if commit:
conn.commit()
return self.agent_id
except (Exception, psycopg2.DatabaseError) as error:
print("Error in INSERTING New Agent " + self.first_name + " " +
self.last_name + "\n" + error)
return None