/**
* Construct a query (to be used as a sub-select) to view the fields of the given form. It always will return the id of the form as well
* @param string $form
* @param mixed $fields. Either a string, the field, or an array of string, the fields.
* @param mixed $id. Defaults to null. If non-null it is the id that we wish to limit to.
* @param boolean $parent. Defaults to false. If true, we include the parent id as a referenced field
* If it is scalar and non-boolean, it is consider to be the ID of the parent, and then we get all forms with parent the given id.
* @param callback $field_refernece_callback. A callback function whose first arguement is the form, the second arguements
* is the field and which returns the way the field value should be references as a field. If the callback is null (the default) then
* the reference used is "$form+$field"
* @param integer $mod_time. Defaults to -1. If non-negative, we only list the requested fields for an id if at least one of them has a modification
* time greater than or equal to $mod_time. If the form storage has no way of tracking modifucation time, all entries are listed.
* @param mixed $user The user id of the user to limit the results to so it only returns results limited to that user. This can be a single value or an array
* @returns string the query or false on failed.
*/
public function getRequiredFieldsQuery($form, $fields, $id = null, $parent = false, $field_reference_callback = null, $mod_time = -1, $user = false)
{
if ($fields === null) {
$fields = array();
}
if (is_string($fields)) {
$fields = array($fields);
}
if (!is_array($fields)) {
I2CE::raiseError("Invalid fields");
return false;
}
if (!($options = $this->getStorageOptions($form))) {
I2CE::raiseError("No valid multi_flat storage options for {$form}");
return false;
}
if (!is_array($this->databases) || count($this->databases) == 0) {
I2CE::raiseError("No databases specified");
return false;
}
$formObj = $this->getFormObj($form);
if (!$formObj instanceof I2CE_Form) {
I2CE::raiseError("Could not instantiate form {$form}");
return false;
}
if (!in_array($form, $this->componentized_forms)) {
I2CE::raiseError("Form {$form} is not specified as being componentized. Valid componentized forms:" . print_r($this->componentized_forms, true));
return false;
}
$table = '';
$options->setIfIsSet($table, 'table');
$table = trim($table);
if (strlen($table) == 0) {
if (array_key_exists('table_prefix', $this->global_options) && is_string($this->global_options['table_prefix']) && strlen(trim($this->global_options['table_prefix'])) > 0) {
$table_prefix = trim($this->global_options['table_prefix']);
} else {
$table_prefix = 'hippo_';
}
$table = $table_prefix . $form;
}
if (strlen($table) == 0) {
I2CE::raiseError("No table specified for {$form}");
return false;
}
$id_ref = false;
if ($field_reference_callback != null) {
if (!is_string($id_ref = call_user_func($field_reference_callback, $form, 'id'))) {
I2CE::raiseError("Invalid field reference callback function");
return false;
}
} else {
$id_ref = "`{$form}+id`";
}
$id_qry = 'id';
if ($options->is_parent('id')) {
$id_qry = $this->getFieldData($options->id);
}
$form_prepended = true;
$options->setIfIsSet($form_prepended, "id/form_prepended");
$unions = array();
$p_componentized = false;
if (is_scalar($id)) {
if (($pos = strrpos($id, '@')) === false) {
//get the last @ sign
I2CE::raiseError("No component specified in id: {$id} for form {$form}");
return false;
}
$component = substr($id, $pos + 1);
$id_no_comp = substr($id, 0, $pos);
if (strlen($component) == 0) {
I2CE::raiseError("zero length component specified in id:{$id}");
return false;
}
if (!array_key_exists($component, $this->databases)) {
I2CE::raiseError("Component {$component} is not associated to a database in id {$id} for form {$form}");
return false;
}
$databases = array($component => $this->databases[$component]);
$componentParentForms = array_intersect(I2CE_Form::getAllowedParentForms($form), $this->componentized_forms);
} else {
if (!is_bool($parent) && is_scalar($parent)) {
list($p_form, $p_id) = explode('|', $parent, 2);
if (strlen($p_form) == 0) {
I2CE::raiseError("sNo parent form given in {$parent}");
return false;
}
if (strlen($p_id) == 0) {
I2CE::raiseError("No parent id given in {$parent}");
return false;
}
if (in_array($p_form, $this->componentized_forms)) {
$p_componentized = true;
if (($pos = strrpos($p_id, '@')) === false) {
//get the last @ sign
I2CE::raiseError("No component specified in parent id: {$parent}");
return false;
}
$component = substr($p_id, $pos + 1);
$p_id_no_comp = substr($p_id, 0, $pos);
if (strlen($component) == 0) {
I2CE::raiseError("zero length component specified in parent id:{$parent}");
return false;
}
if (!array_key_exists($component, $this->databases)) {
I2CE::raiseError("Component {$component} is not associated to a database in parent id: {$parent}");
return false;
}
$databases = array($component => $this->databases[$component]);
$componentParentForms = array($p_form);
} else {
$componentParentForms = array_intersect(I2CE_Form::getAllowedParentForms($form), $this->componentized_forms);
}
} else {
$databases = $this->databases;
$componentParentForms = array_intersect(I2CE_Form::getAllowedParentForms($form), $this->componentized_forms);
}
}
//now verify that the databases and associated table are indeed present:
$db = MDB2::singleton();
foreach ($databases as $component => $database) {
$check_qry = "SELECT null FROM information_schema.TABLES WHERE TABLE_SCHEMA = '" . addslashes($database) . "'" . " AND TABLE_NAME = '" . addslashes($table) . "'";
$result = $db->query($check_qry);
if (I2CE::pearError($result, "Cannot execute query:\n{$check_qry}")) {
return false;
}
if ($result->numRows() > 0) {
//the table exists.
continue;
}
unset($databases[$component]);
}
if (count($databases) == 0) {
I2CE::raiseError("No databases defined for multi-flat formstorage on table {$table}");
return false;
}
foreach ($databases as $component => $database) {
$select_list = array();
if ($form_prepended) {
$select_list[] = "CONCAT( SUBSTRING(" . $id_qry . "," . (strlen($form) + 2) . "),'@','" . mysql_real_escape_string($component) . "') AS {$id_ref}";
} else {
$select_list[] = "CONCAT( " . $id_qry . ",'@','" . mysql_real_escape_string($component) . "') AS {$id_ref}";
}
foreach ($formObj as $field => $fieldObj) {
if (!in_array($field, $fields)) {
continue;
}
if (!$fieldObj->isInDB()) {
continue;
}
if ($options->is_parent("fields/{$field}")) {
$data = $options->traverse("fields/{$field}");
if ($data->is_scalar('enabled') && !$data->enabled) {
continue;
}
$f_qry = $this->getFieldData($data, $database, $table);
} else {
if (!$this->hasColumn($field, $database, $table)) {
$f_qry = ' NULL ';
} else {
$f_qry = "`{$field}`";
//default to the field name
}
}
if ($fieldObj instanceof I2CE_FormField_MAPPED) {
$comp_map_forms = array_intersect($fieldObj->getSelectableForms(), $this->componentized_forms);
$f_qry = $fieldObj->getSQLComponentization($f_qry, $comp_map_forms, $component);
}
$f_ref = false;
if ($field_reference_callback != null) {
if (!is_string($f_ref = call_user_func($field_reference_callback, $form, $field))) {
I2CE::raiseError("Invalid field reference callback function");
return false;
}
} else {
$f_ref = "`{$form}+{$field}`";
}
$select_list[] = "{$f_qry} AS {$f_ref}";
}
$wheres = array();
if (is_array($mod_time) && array_key_exists('mod_time', $mod_time)) {
$mod_time = $mod_time['mod_time'];
}
$get_mod_time = is_scalar($mod_time) && $mod_time >= 0 || in_array('last_modified', $fields);
if ($get_mod_time) {
if ($options->is_scalar('last_modified/enabled') && !$options->last_modified->enabled) {
$mod_qry = "NULL";
} else {
if ($options->is_scalar('last_modified/col') && $options->last_modified->col) {
if ($this->hasColumn($options->last_modified->col, $database, $table)) {
$mod_qry = $options->last_modified->col;
} else {
$mod_qry = 'NULL';
}
} else {
if ($options->is_scalar('last_modified/function') && $options->last_modified->function) {
$mod_qry = $options->last_modified->function;
} else {
if ($this->hasColumn('last_modified', $database, $table)) {
$mod_qry = " last_modified ";
} else {
//perhaps we are using a hippo_XXX table which does not have a last_modified column
$mod_qry = 'NULL';
}
}
}
}
if ($field_reference_callback !== null) {
if (!is_string($mod_ref = call_user_func($field_reference_callback, $form, 'last_modified'))) {
I2CE::raiseError("Invalid parent reference callback function:\nlast_modified --> {$mod_ref}");
return false;
}
} else {
$mod_ref = '`' . $form . '+last_modified`';
}
$select_list[] = "{$mod_qry} AS {$mod_ref}";
if (is_scalar($mod_time) && $mod_time >= 0) {
$wheres[] = "({$mod_qry} IS NULL OR {$mod_qry} > FROM_UNIXTIME(" . $mod_time . "))";
}
}
if ($parent !== false || in_array($parent, $fields)) {
$p_qry = false;
$p_ref = false;
if ($field_reference_callback != null) {
if (!is_string($p_ref = call_user_func($field_reference_callback, $form, 'parent'))) {
I2CE::raiseError("Invalid field reference callback function");
return false;
}
} else {
$p_ref = "`{$form}+parent`";
}
if (!$options->is_scalar('parent/enabled') || $options->parent->enabled) {
if ($options->is_parent('parent')) {
$p_qry = $this->getFieldData($options->parent);
}
if ($p_qry === false) {
$p_qry = 'parent';
}
} else {
$p_qry = '0';
}
$select_list[] = I2CE_List::componentizeQuery($p_qry, $componentParentForms, $component) . " AS {$p_ref}";
if (!is_bool($parent) && is_scalar($parent)) {
if (!$p_componentized) {
$wheres[] = " ( " . I2CE_List::componentizeQuery($p_qry, $componentParentForms, $component) . " = '" . mysql_real_escape_string($parent) . "' ) ";
} else {
$wheres[] = " ( {$p_qry} = '" . mysql_real_escape_string($p_form . '|' . $p_id_no_comp) . "') ";
}
}
}
//want either select id as `form+id`, name as `form+name` from table
//or select id as `form+id`, name as `form+name` from (select * from table where id=5)
//select substr(my_id,1,4) as `form+id`, surname as `form+name` from (select * from table where id=5)
//or with a function..
//select id as `form+id`, substr(surname,1,3) as `form+name` from table
//select id as `form+id`, substr(surname,1,3) as `form+name` from (select * from table where id=5)
//select substr(my_id,1,4) as `form+id`, surname as `form+name` from table wher `form+id`
$qry = 'SELECT ' . implode(',', $select_list) . " FROM `{$database}`.`{$table}`";
if (is_scalar($id)) {
if ($form_prepended) {
$wheres[] = " ({$id_qry} ='" . mysql_real_escape_string($form . '|' . $id_no_comp) . "') ";
} else {
$wheres[] = " ({$id_qry} ='" . mysql_real_escape_string($id_no_comp) . "') ";
}
}
if (count($wheres) > 0) {
$qry .= ' WHERE (' . implode("AND", $wheres) . ")";
}
$unions[] = $qry;
}
if (count($databases) > 1) {
foreach ($unions as &$union) {
$union = '(' . $union . ')';
}
//I2CE::raiseError("QRY:" . implode('UNION' ,$unions));
return implode('UNION', $unions);
} else {
reset($unions);
//I2CE::raiseError("QRY:" . current($unions));
return current($unions);
}
}
/**
* Componentizes the given $db_value based on component
* @param string $db_ref. The reference to the data
* @param array $forms of stirng. The form names which we wish to componentize.
* @param string $component The component we wish to encode
* @returns string The componentized db_value
*/
public function getSQLComponentization($db_ref, $forms, $component)
{
return I2CE_List::componentizeQuery($db_ref, $forms, $component);
}
