using phrets to create mysql tables from rets: script

[Jean-Michel]

Here is a raw example.

<?php

/* Raw example on how to use phrets to generate the necessary sql code
for mysql tables generation from rets.
Works for me*/

require ('phrets0.6.1.php');


$rets = new phRETS;

$Host = 'http://demo.crt.realtors.org:6103/rets/login';
$Account = 'Joe';
$Password = 'Schmoe';
$User_Agent = 'RETS_class/1.0';
$Rets_Version = 'RETS/1.5' ;



$rets->AddHeader("Accept", "*/*");
$rets->AddHeader("RETS-Version", "$Rets_Version");
$rets->AddHeader("User-Agent", "$User_Agent");
$rets->SetParam("cookie_file", "phrets_cookies.txt");
//$rets->SetParam("debug_mode", TRUE); // ends up in rets_debug.txt


$rets->Connect($Host,$Account,$Password);
$ResourcesInfo1 = $rets->GetMetadataInfo();
$MetadataInfo1 = $rets->GetMetadataTypes();
/*testing4GetTables*/
function GetTables($ResourcesInfo, $MetadataInfo) {
/* Put toghether all the rets info needed for database tables creation
in an array. */
foreach ($MetadataInfo as $key => $value){
$Resource = $value['Resource'];
foreach ($value['Data'] as $key){
$tables[$key['Description']] = array(
'ResourceID' => $Resource,
'Description' => $key['Description'],
'ClassName' => $key['ClassName'] ,
'KeyField' => $ResourcesInfo[$Resource]['KeyField'],
'ClassCount' => $ResourcesInfo[$Resource]['ClassCount']
);
}

}
return $tables;
}

function CreateMysqlTables ($ResourcesInfo, $MetadataInfo, $class){
/*function providing the mysql code needed to create mysqltables.
to be run as the script on the commande line for nicer output*/
$table = GetTables($ResourcesInfo, $MetadataInfo);

foreach ($table as $key => $value){

$TablesAndFields[$value['Description']] = $class-
>GetMetadata($value['ResourceID'],$value['ClassName']);
}

foreach ($TablesAndFields as $key => $value){
$find = array ('/ /', '/-/');
$TableName = preg_replace($find, "_", $key);
echo "\n\r CREATE TABLE IF NOT EXISTS $TableName (\n\r";
foreach ( $value as $key2 => $value2){
echo "`$value2[SystemName]` ";
ConvertTypeToMysql($value2[DataType], $value2[MaximumLength]);
}
echo "`MyNewTablesTimeStamp` timestamp NOT NULL default
CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,\n\r";
echo " PRIMARY KEY (".$table[$key]['KeyField']."));\n\r";
}
}

function ConvertTypeToMysql($datatype, $datalenght){
/* This function to convert mls datatype and datalenght to mysql.
Change to fit your needs works for me: db2 to mysql (flexmls)*/
switch ($datatype){
case "Character":
if ($datalenght >= 255){
echo "text collate utf8_unicode_ci default NULL, \n\r";

}
elseif (!$datalenght){
echo "$datatype(25) collate utf8_unicode_ci default NULL, \n\r";

}
else {
echo "$datatype($datalenght) collate utf8_unicode_ci default NULL,
\n\r";

}

break;

case "Decimal":

echo "$datatype($datalenght,0) default NULL, \n\r";
break;

case "Int":

echo "$datatype($datalenght) default NULL, \n\r";
break;

case "Long":
/* note:if I put this case after case ("Date" or "Boolean"): it won't
work, Why? is Long a Boolean?'*/
echo "LONGTEXT collate utf8_unicode_ci default NULL, \n\r";
break;

case ("Date" or "Boolean"):

echo "$datatype default NULL, \n\r";
break;


}

}
CreateMysqlTables ($ResourcesInfo1, $MetadataInfo1, $rets);

$rets->Disconnect();





?>

Jean-Michel

[Troy Davisson]

Very nice.

Long is a long integer.  Not sure why it wouldn't be working for you.

One other small thing to note (that I ran into over the weekend with code somewhat similar to that) is to check the Interpretation value that comes back.  If that's Lookup or LookupMulti, the type and length of the field will often not be correct.  I've seen servers where the Lookup values are numbers and so they advertise a Int field of a couple digits long, but if you get a COMPACT-DECODED format of the data, it obviously won't work in that field type.  Also, I've seen servers (note: same server as that one) where a field is advertised as Int with enough length to hold 1 LookupMulti value even though the field may contain a large collection of IDs which won't work if you're simply storing those IDs or getting COMPACT-DECODED.


Troy Davisson

[digamel]

Are you using this script to create tables on your local server?
If so, what variables are used to connect?

On Aug 4, 12:47 pm, "Troy Davisson" <troy.davis...@gmail.com> wrote:
> Very nice.
>
> Long is a long integer.  Not sure why it wouldn't be working for you.
>
> One other small thing to note (that I ran into over the weekend with code
> somewhat similar to that) is to check the Interpretation value that comes
> back.  If that's Lookup or LookupMulti, the type and length of the field
> will often not be correct.  I've seen servers where the Lookup values are
> numbers and so they advertise a Int field of a couple digits long, but if
> you get a COMPACT-DECODED format of the data, it obviously won't work in
> that field type.  Also, I've seen servers (note: same server as that one)
> where a field is advertised as Int with enough length to hold 1 LookupMulti
> value even though the field may contain a large collection of IDs which
> won't work if you're simply storing those IDs or getting COMPACT-DECODED.
>
> Troy Davisson
>

[Troy Davisson]

I'm not sure I understand the question.

A script like what he posted would read the metadata information from the RETS server and take various values from responses to generate MySQL tables automatically, ready for data to be inserted into.  In cases of older FTP feeds, you'd usually have to manually type out your CREATE TABLE statements using either a best guess or PDF/HTML documentation provided by the MLS.

[Troy Arnold]

On Mon, Apr 29, 2013 at 01:10:01AM -0700, Lance McDowell wrote:
> I think he's asking at what point in the script do you actually pass
> connection creds to the Mysql DB. I'm wondering the same thing.

I haven't been following this thread but it looks as if the script is meant
to be run on the command line. That is, its output is the raw SQL
necessary to create the tables. This gives maximum flexibility.

You can run the script and capture its output for massaging or for use in
another tool:
$ ./rets2mysql.sh > mydb.sql

or do it interactively by piping the output directly to MySQL:
$ ./rets2mysql.sh | mysql yourdb -u yourdbusername -p

When the script runs you will be prompted for the MySQL password for the
'yourdbusername' user on the 'yourdb' database.


-t