[Rets-dev] How to calculate field sizes

[Sergio Del Rio]

Your best bet is to use the Maximum Length and Data Type columns returned by
the metadata.

Now, for LookupMulti and Lookup, you are correct, we put in the maximum
length of the key value that we return in that field.  So, for those cases,
here is what I would recommend:

1. For LookupMulti - You need to either store these into a lookup table as
this will return a comma separated list of lookup keys or just store them as
a long string.  If you want to know how big the maximum size string would
be, take the key length (11 digits) and multiply it by MaxSelect and then
add in 2 * MaxSelect to account for the comma and space that is returned.

2. For Lookup, you will only ever get one key returned in the column
consisting of an 11 digit number (although the max number possible would be
15).

This is what happens if you are pulling COMPACT data.  If you are pulling
COMPACT-DECODED, you could use similar logic but calculate based on maximum
decoded values.  The comprehensive list of decoded values can be found in
the Lookup Type metadata for this calculation.  Typically, our longest
Lookup or Lookup-Multi string is 30 characters with a very few exceptions.

For all other columns, the Maximum Length and Data Type should be
sufficient. You will see a few set to 4000 as this is variable data and this
is what our database returns for this kind of data.  In reality you could
probably allocate 255 characters for this and be fairly safe as long as you
put some truncation into your code just in case it should ever exceed that
size.

Regards,
Sergio Del Rio
Templates 4 Business Inc.

-----Original Message-----
From: rets-dev-bounces at rets.org [mailto:rets-dev-bounces at rets.org] On Behalf
Of Allen Schmidt Sr
Sent: March 22, 2007 4:42 PM
To: Rets-dev at rets.org
Subject: [Rets-dev] How to calculate field sizes

General question:

We pull MRIS data using a Python script. Works great for data and
images. We used to use Conduit in console mode and pulled the whole
file onto the filesystem and then used Python to parse it apart into
MySQL tables. The table had been defined long ago and still used the
old names we had like LIST_NUMB, etc.
The current script pulls all the data but I only use maybe 30 of the
fields for our online application.

Now I would like to pull LOTS more data fields and use the
system/standard names (I get them confused.)

I want to define the DDL for a new table that more closely matches the
data I want to pull in. So I want to only define the db fields using
the exact max field size. How can I do that? Is guess simple question
is does anyone have a MySQL table structure for MRIS data they wanna
share??

Out side of that, I notice that in the metadata, for a lookup field,
it only shows the field size as the lookup table ID rather than the
actual largest possible size of the data in that field.

I think I am rambling now. Anyone understand what I am going for?

Thanks for the time.

Allen
_______________________________________________
Rets-dev mailing list
Rets-dev at rets.org
http://lists.rets.org/mailman/listinfo/rets-dev