/** * 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; } }