Saturday, October 4, 2008

Coldfusion RETS - Getting all of the metadata

As discussed earlier, the metadata is the key to communicating with a RETS server, it is schema for the data you will be dealing with. Once authenticated on the server, you will need to pull a boatload of metadata. Here is the abbreviated description of what needs to be pulled, and how it relates.

  • METADATA-RESOURCE : This file contains the "top level" resources on the server, in my case Property, Office, Agent, Tour and Open House. Is also tells you how many classes each resource has.
  • METADATA-CLASS : Describes the sub-groups within each resource. In my case, only property has more than one class (Property has 18). These should be familiar to anyone who has dealt with MLS data, in standard terms, the property types; Single Family, Multi-Unit, Commercial, etc.
  • METADATA-TABLE : This document, pulled for each class describes the fields such as Listing Data, List Price, etc. There is good detail in this document including datatype, length, if the value is a lookup or lookup-multi and if so, what the lookup key is, etc.
  • METADATA-LOOKUP : Pulled for each class, this document does not contain much information. It pretty much lists all of the lookup fields for the class. This can be pulled from the METADATA-TABLE document for the class.
  • METADATA-LOOKUP_TYPE : So you would think this would describe the multi-select, select, yes/no attributes of the lookup fields, I know I did. This document actually contains the values for the lookup columns. So, if your single family property METADATA-TABLE document says the field "Addtl Room 10 Flooring" is a lookup field, and has a lookup name of "AddlRoomFlooring", this document will have a section for that listing the values such as Carpet, Hardwood, Marble, etc. Good stuff indeed. For those who have not struggled to figure out the schema of a MLS database from the outside this might seem like no big deal. Those of us that have been doing this for years with no cooperation from the MLS systems, using cryptic listing forms and inconsistent interfaces might get a bit emotional when the schema comes across in a few seconds, haha.
So the plan; We grab the RESOURCE file, loop over it to grab the CLASS files for each resource, Loop over the CLASS file to grab the TABLE, LOOKUP and LOOKUP_TYPE files for each class. 34 files later (in my case), and we are ready to go. I am going to repost the metadata.cfc code also because I made some modifications in regards to error notification and file naming.



The output of this code should look something like this...

Getting METADATA-RESOURCE... Complete
Getting Class Agent... Complete
Getting METADATA-LOOKUP Agent... unknown resource
Getting METADATA-LOOKUP_TYPE Agent... unknown resource
Getting METADATA-TABLE Agent:Agent... Complete

Getting Class Office... Complete
Getting METADATA-LOOKUP Office... unknown resource
Getting METADATA-LOOKUP_TYPE Office... unknown resource
Getting METADATA-TABLE Office:Office... Complete

Getting Class Property... Complete
Getting METADATA-LOOKUP Property... Complete
Getting METADATA-LOOKUP_TYPE Property... Complete
Getting METADATA-TABLE Property:ResidentialProperty... Complete
Getting METADATA-TABLE Property:RentalHome... Complete
Getting METADATA-TABLE Property:LotsAndLand... Complete
Getting METADATA-TABLE Property:CommercialProperty... Complete
Getting METADATA-TABLE Property:DE... Complete
Getting METADATA-TABLE Property:AT... Complete
Getting METADATA-TABLE Property:MH... Complete
Getting METADATA-TABLE Property:MU... Complete
Getting METADATA-TABLE Property:RN... Complete
Getting METADATA-TABLE Property:DP... Complete
Getting METADATA-TABLE Property:VL... Complete
Getting METADATA-TABLE Property:MF... Complete
Getting METADATA-TABLE Property:OI... Complete
Getting METADATA-TABLE Property:BU... Complete
Getting METADATA-TABLE Property:CO... Complete
Getting METADATA-TABLE Property:RS... Complete
Getting METADATA-TABLE Property:OT... Complete
Getting METADATA-TABLE Property:IN... Complete

Getting Class OpenHouse... Complete
Getting METADATA-LOOKUP OpenHouse... Complete
Getting METADATA-LOOKUP_TYPE OpenHouse... Complete
Getting METADATA-TABLE OpenHouse:OpenHouse... Complete

Getting Class Tour... Complete
Getting METADATA-LOOKUP Tour... Complete
Getting METADATA-LOOKUP_TYPE Tour... Complete
Getting METADATA-TABLE Tour:Tour... Complete

Don't sweat the "Unknown Resource" response on some entries, if the table for a class has no lookup fields, there will be no lookup or lookup_type files.

This is not production code, it works like a champ but it is what I would consider testing code. Next up, a "field" object that pulls everything together from the xml files on a per field basis. For now, my intention is to leave the XML data as is, and not pull the metadata into a database. That is what xpath is for...


Michael Evangelista said...

hi james - this is great.

I think you left out a closing 'cfloop' in there, . I took an
educated guess and stuck it at the very end of the file (getallmetadata.cfm)

also, I had to change the directory structure (I guess the other option would be to wrap it in 'cfif directoryExists()' and then create the directory if not )
Anyway, shortening the subfolder path to match my previous xml directory got rid of the errors.

From there, running it was a snap.
a few items come back with 'unknown resource' but for the most part it worked instantly... off to dig through some xml files now!

thanks again - sharing code like this is what makes the CF community so great.

Michael Evangelista said...

Ok, got as far as I can... and eagerly awaiting your next moves!

I created a search CFC and it works great using a sample search query that I found in the rets docs, broken down into a handful of arguments, like this

<cfinvoke component="SearchRets" method="SearchRets">
        <cfinvokeargument name="searchtype" value="Property">
        <cfinvokeargument name="class"  value="ResidentialProperty">
        <cfinvokeargument name="query"  value="(ST=|ACT,S OLD)">
        <cfinvokeargument name="limit"  value="1">
        <cfinvokeargument name="SelectFields"  value="">

But my client wants to get data from other tables than the 'residentialProperty' shown in the example. My metadata lookup for the classtypes shows I have them all available - DE, RN, VL, MH, etc - but when I change the class in my search arguments to "DE" for example, I get "resource not found". There must be some other setting I need... or something...

What I've got to figure out now (and presumably what anybody else who follows your efforts will also be looking for) is some help with the search language and options.

I am getting the idea of using one xml file to see what options you have in another, but mostly clueless about the syntax for Rets Query Language.

Any light you can shed or code you can share in that department will be eagerly and instantly utilized!
Thanks again!

Dan said...

I've just started working on some RETS functionality as well. My biggest hurdle at this point is figuring out how to get images back from the MLS server. The MLS I'm connecting to uses Digest Authentication, so I can't use the standard CFHTTP tag and have to use CFX_HTTP5.

I can get some bits and bytes back that look like they should be turned into an image, but have no idea how to actually get the image to be readable.

I'll be watching your blog as well :)


jones said...

Nice blog...
visit also coldfusion example

Wayne said...

Hi James,

I read with great interest your posts about RETS. I'm in the midst of trying to make it work my real estate site. I see where someone got access to your cfc's but I don't see them in the posts.

Are they still available?

have there been any improvements?