/**
* creates query corresponding to mysql table structure of an xform definition file
* Author : Eric Beda
*
* @return string statement for creating table structure of xform
*/
private function get_create_table_sql_query()
{
$structure = $this->form_defn;
$tbl_name = $this->table_name;
// initiate statement, set id as primary key, autoincrement
$statement = "CREATE TABLE {$tbl_name} ( id INT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY ";
// loop through xform definition array
foreach ($structure as $key => $val) {
// check if type is empty
if (empty($val['type'])) {
continue;
}
$type = $val['type'];
$field_name = $val['field_name'];
//TODO Call helper function here to shorten column name
if (strlen($field_name) > 64) {
$field_name = shorten_column_name($field_name);
}
// check if field is required
if (!empty($val['required'])) {
$required = 'NOT NULL';
} else {
$required = '';
}
if ($type == 'string' || $type == 'binary') {
$statement .= ", {$field_name} VARCHAR(300) {$required}";
}
if ($type == 'select1') {
// Mysql recommended way of handling single quotes for queries is by using two single quotes at once.
$statement .= ", {$field_name} ENUM('" . implode("','", str_replace("'", "''", $val['option'])) . "') {$required}";
}
if ($type == 'select' || $type == 'text') {
$statement .= ", {$field_name} TEXT {$required} ";
}
if ($type == 'date') {
$statement .= ", {$field_name} DATE {$required} ";
}
if ($type == 'int') {
$statement .= ", {$field_name} INT(20) {$required} ";
}
if ($type == 'geopoint') {
$statement .= "," . $field_name . " VARCHAR(150) {$required} ";
$statement .= "," . $field_name . "_point POINT {$required} ";
$statement .= "," . $field_name . "_lat DECIMAL(38,10) {$required} ";
$statement .= "," . $field_name . "_lng DECIMAL(38,10) {$required} ";
$statement .= "," . $field_name . "_acc DECIMAL(38,10) {$required} ";
$statement .= "," . $field_name . "_alt DECIMAL(38,10) {$required} ";
}
$statement .= "\n";
}
$statement .= ")";
return $statement;
}
/**
* @param string $name
* @param object $obj
*/
private function get_path($name, $obj)
{
$name .= "_" . $obj->name;
if (is_array($obj->children)) {
foreach ($obj->children as $val) {
$this->get_path($name, $val);
}
} else {
$column_name = substr($name, 1);
//shorten long column names
if (strlen($column_name) > 64) {
$column_name = shorten_column_name($column_name);
}
$this->form_data[$column_name] = $obj->content;
}
}
