Database Population From Rets
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Database Population From Rets as PDF for free.
More details
- Words: 540
- Pages: 3
#!/usr/bin/ruby # require's for the librets and mysql libraries require 'librets' require 'mysql' require 'pp' include Librets # Empty value for "values". I can into a problem with line 55 which required me to put this in. values = "" mls_number = "" # Initilizes and connects to the MySQL Database mysql = Mysql.init() mysql.connect(host='<>', user='<>', passwd='<>') mysql.select_db('Duluth') # Optional: I was going off another Ruby::DBI set of instructions. Only include if you want to start from fresh each time. mysql.query("ALTER TABLE residentialpropertyphotos DROP FOREIGN KEY residentialpropertyphotos_ibfk_1;") mysql.query("DROP TABLE IF EXISTS ResidentialProperty") mysql.query("DROP TABLE IF EXISTS ResidentialPropertyPhotos") #Creates the Database to work with. I pulled the metadata from the RETS feed to determine what the field types and lengths are mysql.query("CREATE TABLE ResidentialProperty (L_ListingID INT UNSIGNED NOT NULL, PRIMARY KEY (L_ListingID), L_AskingPrice INT NOT NULL, L_Keyword2 INT NOT NULL, L_Keyword3 INT NOT NULL, L_Keyword4 INT, L_City CHAR(50) NOT NULL, L_Area varchar(50) NOT NULL, L_AddressNumber varchar(15) NOT NULL, L_AddressDirection varchar(2), L_AddressStreet varchar(50) NOT NULL, LR_Remarks11 varchar(1000), LA1_UserFirstName varchar(15) NOT NULL, LO1_OrganizationName varchar(50) NOT NULL, LO1_PhoneNumber1Area INT, LO1_PhoneNumber1 INT) Type=INNODB;") mysql.query("CREATE TABLE ResidentialPropertyPhotos (PhotoID INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (PhotoID), L_ListingID INT UNSIGNED NOT NULL, Photo_Number varchar(5) NOT NULL, Photo LONGBLOB, INDEX (L_ListingID), FOREIGN KEY (L_ListingID) REFERENCES ResidentialProperty (L_ListingID)) TYPE=INNODB;") # Creates a RETS Session session = RetsSession.new("<>") # Changes the encodeing from ASCII to ISO session.set_default_encoding(RETS_XML_ISO_ENCODING) # Login into RETS if !session.login("<>", "<>") puts "Invalid login" exit 1 end
# Not quite sure what this does puts "Action: " + session.action # Checks the version of the RETS server version = "1.0" version = "1.5" if (session.detected_rets_version == RETS_1_5) puts "RETS Version: " + version # Your DMQL syntax for your search. This search is grabbing all Active Residential listings. request = session.create_search_request("Property", "ResidentialProperty", "(L_Status=1_0)") #request.standard_names = true # Tells RETS to use standard names request.select = "L_ListingID,L_AskingPrice,L_Keyword2,L_Keyword3,L_City,L_Area,L_AddressNumber,L_A ddressDirection,L_AddressStreet,\ LR_Remarks11,LA1_UserFirstName,LO1_OrganizationName,LO1_PhoneNum ber1Area,LO1_PhoneNumber1" # Fields returned in your search request.limit = SearchRequest::LIMIT_DEFAULT # Requests the RETS Server default limit request.offset = SearchRequest::OFFSET_NONE # Indicates no offset to the RETS server request.count_type = SearchRequest::RECORD_COUNT_AND_RESULTS # Not exactly sure on this one results = session.search(request) # Executes the search columns = results.columns # Places results in columns results.each do |result| # Iterates through each row columns.each do |column| # Iterates through each column cleaned_value = mysql.escape_string(result.string(column)) values += column + "='#{cleaned_value}'," # Creates the set expression for the INSERT statement end mysql.query("INSERT INTO ResidentialProperty SET #{values.chop};") # Writes the results to the Database values = "" end # MySQL query to return all active MLS numbers results = mysql.query("Select L_ListingID FROM ResidentialProperty"); # Loop to process each MLS listing and retreive photos results.each do |row| mls_number = row[0] # Get Object request to pull listing photos get_object_request = GetObjectRequest.new("Property", "Photo") get_object_request.add_all_objects("#{mls_number}") get_object_response = session.get_object(get_object_request) # Assigns each part of the Get Object to a variable content_type_suffixes = { "image/jpeg" => "jpg", "image/gif" => "gif", "text/xml" => "xml" } get_object_response.each_object do |object_descriptor| object_key = object_descriptor.object_key
object_id = object_descriptor.object_id content_type = object_descriptor.content_type description = object_descriptor.description photo_content = mysql.escape_string(object_descriptor.data_as_string) # Insert statement to place images in database mysql.query("INSERT INTO ResidentialPropertyPhotos SET Photo_Number='#{object_id}', Photo='#{photo_content}', L_ListingID='#{mls_number}';") end end logout = session.logout mysql.close()
# Closes the RETS session # Closes the MySQL connection.
# Not quite sure what this does puts "Action: " + session.action # Checks the version of the RETS server version = "1.0" version = "1.5" if (session.detected_rets_version == RETS_1_5) puts "RETS Version: " + version # Your DMQL syntax for your search. This search is grabbing all Active Residential listings. request = session.create_search_request("Property", "ResidentialProperty", "(L_Status=1_0)") #request.standard_names = true # Tells RETS to use standard names request.select = "L_ListingID,L_AskingPrice,L_Keyword2,L_Keyword3,L_City,L_Area,L_AddressNumber,L_A ddressDirection,L_AddressStreet,\ LR_Remarks11,LA1_UserFirstName,LO1_OrganizationName,LO1_PhoneNum ber1Area,LO1_PhoneNumber1" # Fields returned in your search request.limit = SearchRequest::LIMIT_DEFAULT # Requests the RETS Server default limit request.offset = SearchRequest::OFFSET_NONE # Indicates no offset to the RETS server request.count_type = SearchRequest::RECORD_COUNT_AND_RESULTS # Not exactly sure on this one results = session.search(request) # Executes the search columns = results.columns # Places results in columns results.each do |result| # Iterates through each row columns.each do |column| # Iterates through each column cleaned_value = mysql.escape_string(result.string(column)) values += column + "='#{cleaned_value}'," # Creates the set expression for the INSERT statement end mysql.query("INSERT INTO ResidentialProperty SET #{values.chop};") # Writes the results to the Database values = "" end # MySQL query to return all active MLS numbers results = mysql.query("Select L_ListingID FROM ResidentialProperty"); # Loop to process each MLS listing and retreive photos results.each do |row| mls_number = row[0] # Get Object request to pull listing photos get_object_request = GetObjectRequest.new("Property", "Photo") get_object_request.add_all_objects("#{mls_number}") get_object_response = session.get_object(get_object_request) # Assigns each part of the Get Object to a variable content_type_suffixes = { "image/jpeg" => "jpg", "image/gif" => "gif", "text/xml" => "xml" } get_object_response.each_object do |object_descriptor| object_key = object_descriptor.object_key
object_id = object_descriptor.object_id content_type = object_descriptor.content_type description = object_descriptor.description photo_content = mysql.escape_string(object_descriptor.data_as_string) # Insert statement to place images in database mysql.query("INSERT INTO ResidentialPropertyPhotos SET Photo_Number='#{object_id}', Photo='#{photo_content}', L_ListingID='#{mls_number}';") end end logout = session.logout mysql.close()
# Closes the RETS session # Closes the MySQL connection.
Related Documents
Database Population From Rets
April 2020 8
Kcc Area Population Database
November 2019 5
Rets 1.5_doc
June 2020 0
Sampling From A Population
May 2020 7
Population
November 2019 37