Create mysql tables from rets metadata

> Note: This page documents capabilities available in the older 1.x version. Please see this repository’s README file for the new 2.x version documentation.

Purpose

To take the field formats advertised by a RETS server and create a MySQL table according to those definitions.

Example

$connect = $rets->Login();

// getting system meta data
$system = $rets->GetSystemMetadata();

// list of available resources
$resources = $system->getResources();

// array of available property types
$classes = $rets->GetClassesMetadata('Property');

// manually setting resouce and class (class = Property Type)
$resource = "Property";
$class = "RE_1";

$table_name = "rets_".strtolower($resource)."_".strtolower($class);
$fields = $rets->GetTableMetadata($resource,$class);

$sql = create_table_sql_from_metadata($table_name, $fields, "L_ListingID");
echo $sql;

The Code

function create_table_sql_from_metadata($table_name, $rets_metadata, $key_field, $field_prefix = "") {
	$sql_query = "CREATE TABLE ".$table_name." (\n";
	foreach ($rets_metadata as $field) {
		$cleaned_comment = addslashes($field->getLongName());
		$sql_make = "\t`" . $field_prefix . $field->getSystemName()."` ";
		if ($field->getInterpretation() == "LookupMulti") {
			$sql_make .= "TEXT";
		} elseif ($field->getInterpretation() == "Lookup") {
			$sql_make .= "VARCHAR(50)";
		} elseif ($field->getDataType() == "Int" || $field->getDataType() == "Small" || $field->getDataType() == "Tiny") {
			$sql_make .= "INT(".$field->getMaximumLength().")";
		} elseif ($field->getDataType() == "Long") {
			$sql_make .= "BIGINT(".$field->getMaximumLength().")";
		} elseif ($field->getDataType() == "DateTime") {
			$sql_make .= "DATETIME default '0000-00-00 00:00:00' NOT NULL";
		} elseif ($field->getDataType() == "Character" && $field->getMaximumLength() <= 255) {
			$sql_make .= "VARCHAR(".$field->getMaximumLength().")";
		} elseif ($field->getDataType() == "Character" && $field->getMaximumLength() > 255) {
			$sql_make .= "TEXT";
		} elseif ($field->getDataType() == "Decimal") {
			$pre_point = ($field->getMaximumLength() - $field->getPrecision());
			$post_point = !empty($field->getPrecision()) ? $field->getPrecision() : 0;
			$sql_make .= "DECIMAL({$field->getMaximumLength()},{$post_point})";
		} elseif ($field->getDataType() == "Boolean") {
			$sql_make .= "CHAR(1)";
		} elseif ($field->getDataType() == "Date") {
			$sql_make .= "DATE default '0000-00-00' NOT NULL";
		} elseif ($field->getDataType() == "Time") {
			$sql_make .= "TIME default '00:00:00' NOT NULL";
		} else {
			$sql_make .= "VARCHAR(255)";
		}
		$sql_make .=  " COMMENT '".$cleaned_comment."',\n";
		$sql_query .= $sql_make;
	}
	$sql_query .=  "PRIMARY KEY(`".$field_prefix.$key_field."`) )";
	return $sql_query;
}

1. Other Notes

The above code uses MySQL’s COMMENT feature. This allows you to have a short description that goes along with each field which in this case gets filled in with the most user-friendly (usually) description of the field available from the RETS server. This is very handy when viewed through applications such as phpMyAdmin which will include the field name and the value of COMMENT when viewing a table to make referencing field values easier. To access those COMMENT values directly in SQL, use “SHOW FULL FIELDS FROM $table_name”