function get_entity_by_id_object($id)
{
$dbq = new DBSelector();
$tables = get_entity_tables_by_id($id);
if ($tables) {
foreach ($tables as $table) {
$dbq->add_field($table, '*');
$dbq->add_table($table);
$dbq->add_relation('entity.id = ' . $table . '.id');
}
}
$dbq->add_relation('entity.id = ' . $id);
return $dbq;
}
/**
* Move all the fields of one table into another table for a specific type
*
* This method is for denormalizing Reason tables. For example, a type may use a common table
* like meta, datetime, or chunk. For performance reasons, it can be desirable to collapse
* these tables into a single table just for that type. This method will do that.
*
* @param integer $type The ID of the type whose fields we are moving
* @param string $source_table The name of the table we are moving fields FROM
* @param string $destination_table The name of the table we are moving fields TO
* @param integer $user_id The Reason ID of the user who is doing this move
* @return boolean Success
*
* @todo Add limit to ensure fields are only created that don't already exist
*/
function reason_move_table_fields($type, $source_table, $destination_table, $user_id)
{
// Sanity checks
if(empty($type))
{
trigger_error('No type provided in reason_move_table_fields()');
return false;
}
if(empty($source_table))
{
trigger_error('No source table provided in reason_move_table_fields()');
return false;
}
if(!is_string($source_table))
{
trigger_error('Source table provided not a string in reason_move_table_fields()');
return false;
}
if(empty($destination_table))
{
trigger_error('No destination table provided in reason_move_table_fields()');
return false;
}
if(!is_string($destination_table))
{
trigger_error('Destination table provided not a string in reason_move_table_fields()');
return false;
}
if('entity' == $source_table || 'entity' == $destination_table)
{
trigger_error('reason_move_table_fields() cannot move fields into or out of the entity table.');
return false;
}
if(is_object($type))
{
$type_id = $type->id();
}
elseif(is_numeric($type))
{
$type_id = (integer) $type;
}
else
{
$type_id = id_of($type);
}
if(empty($type_id))
{
trigger_error('Invalid type specified in reason_move_table_fields().');
return false;
}
if(is_object($type))
$type_entity = $type;
else
$type_entity = new entity($type_id);
$type_vals = $type_entity->get_values();
if(empty($type_vals))
{
trigger_error('Type specified (id '.$type_id.') is not a Reason entity in reason_move_table_fields().');
return false;
}
if($type_entity->get_value('type') != id_of('type'))
{
trigger_error('Type specified (id '.$type_id.') is not a Type entity in reason_move_table_fields().');
return false;
}
if($type_entity->get_value('state') != 'Live')
{
trigger_error('Type specified (id '.$type_id.') is not a live entity in reason_move_table_fields().');
return false;
}
if(empty($user_id))
{
trigger_error('No user id specified in reason_move_table_fields().');
return false;
}
$user = new entity($user_id);
if(!$user->get_values() || $user->get_value('type') != id_of('user'))
{
trigger_error('Invalid user ID specified in reason_move_table_fields().');
return false;
}
// check for table existence
$es = new entity_selector();
$es->add_type(id_of('content_table'));
$es->add_relation('`name` = "'.addslashes($source_table).'"');
$source_table_result = $es->run_one();
if(empty($source_table_result))
{
trigger_error('Source table "'.$source_table.'" does not exist in reason_move_table_fields()');
return false;
}
$es = new entity_selector();
$es->add_type(id_of('content_table'));
$es->add_relation('`name` = "'.addslashes($destination_table).'"');
$destination_table_result = $es->run_one();
if(empty($destination_table_result))
{
trigger_error('Destination table "'.$destination_table.'" does not exist in reason_move_table_fields()');
return false;
}
$source_table_entity = current($source_table_result);
$destination_table_entity = current($destination_table_result);
// ensure type uses both tables
$type_tables = get_entity_tables_by_type( $type_id );
if(!in_array($source_table, $type_tables))
{
trigger_error('Source table "'.$source_table.'" not part of the type in reason_move_table_fields()');
return false;
}
if(!in_array($destination_table, $type_tables))
{
trigger_error('Destination table "'.$destination_table.'" not part of the type in reason_move_table_fields()');
return false;
}
$es = new entity_selector();
$es->add_type(id_of('type'));
$es->add_left_relationship($destination_table_entity->id(),relationship_id_of('type_to_table'));
$es->add_relation('`entity`.`id` != "'.addslashes($type_id).'"');
$other_types = $es->run_one();
if(!empty($other_types))
{
trigger_error(count($other_types).' other type(s) share the destination table with the type specified in reason_move_table_fields(). reason_move_table_fields() can only move fields into single-type tables.');
return false;
}
// get the fields in the old table
$es = new entity_selector();
$es->add_type(id_of('field'));
$es->add_left_relationship($source_table_entity->id(), relationship_id_of('field_to_entity_table'));
$source_table_fields = $es->run_one();
if(empty($source_table_fields))
{
trigger_error('Source table '.$source_table.' does not appear to have any fields associated with it in Reason. Unable to move its content in reason_move_table_fields()');
}
$q = 'DESCRIBE `'.addslashes($destination_table).'`';
$handle = db_query( $q, 'Unable to describe destination table in reason_move_table_fields()' );
$raw_dest_cols = array();
while($row = mysql_fetch_assoc($handle))
{
$raw_dest_cols[] = $row['Field'];
}
foreach($source_table_fields as $k=>$field)
{
if(in_array($field->get_value('name'),$raw_dest_cols))
{
trigger_error($field->get_value('name').' field is already in destination table. Unable to accomplish reason_move_table_fields().');
return false;
}
$tmp_field_name = $field->get_value('name').'_move_tmp';
if(in_array($tmp_field_name,$raw_dest_cols))
{
trigger_error($tmp_field_name.' field already in destination table. There appears to have been an error in a previous attempt to run reason_move_table_fields(). Please drop this column in MySQL and try again.');
return false;
}
$source_table_fields[$k]->set_value('_field_move_temp_name',$field->get_value('name').'_move_tmp');
}
// Done with sanity checks
// map old to temp field names & create new fields
$query_parts = array();
foreach($source_table_fields as $k=>$field)
{
$source_table_fields[$k]->set_value('_field_move_temp_name',$field->get_value('name').'_move_tmp');
$q = 'ALTER TABLE `'.addslashes($destination_table).'` ADD '.addslashes( $field->get_value('_field_move_temp_name') ).' '. $field->get_value('db_type');
db_query( $q, 'Unable to create new field '.$field->get_value('_field_move_temp_name').' in reason_move_table_fields()' );
$values = array();
foreach($field->get_values() as $f=>$v)
{
if($f != 'name' && $f != 'id' && strpos($f,'_') !== 0)
{
$values[$f] = $v;
}
}
$id = reason_create_entity( id_of('master_admin'), id_of('field'), $user_id, $field->get_value('_field_move_temp_name'), $values);
$source_table_fields[$k]->set_value('_new_field_id',$id);
$query_parts[] = '`'.addslashes($destination_table).'`.`'.addslashes($field->get_value('_field_move_temp_name')).'` = `'.addslashes($source_table).'`.`'.addslashes($field->get_value('name')).'`';
}
// copy content of old fields to new fields
$q = 'UPDATE `'.addslashes($destination_table).'`, `'.addslashes($source_table).'`, `entity` SET '.implode(' , ',$query_parts).' WHERE `'.addslashes($destination_table).'`.`id` = `'.addslashes($source_table).'`.`id` AND `'.addslashes($destination_table).'`.`id` = `entity`.`id` AND `entity`.`type` = "'.addslashes($type_id).'";';
db_query($q,'Attempt to move data between fields');
// zap source table's type-to-table relationship for this type
$conditions = array(
'entity_a' => $type_id,
'entity_b' => $source_table_entity->id(),
'type' => relationship_id_of('type_to_table'),
);
delete_relationships( $conditions );
// create new field-to-table relationship for new fields and update field names in new table -- remove temp flag
foreach($source_table_fields as $field)
{
create_relationship( $field->get_value('_new_field_id'), $destination_table_entity->id(), relationship_id_of( 'field_to_entity_table' ) );
$q = 'ALTER TABLE `'.addslashes($destination_table).'` CHANGE '.addslashes($field->get_value('_field_move_temp_name')).' '.addslashes( $field->get_value('name') ).' '.$field->get_value('db_type') ;
db_query( $q, 'Unable to change field name of '.$field->get_value('_field_move_temp_name').' in reason_move_table_fields()' );
reason_update_entity( $field->get_value('_new_field_id'), $user_id, array('name' => $field->get_value('name') ), false );
}
// delete the rows from the source table
$q = 'DELETE `'.addslashes($source_table).'` FROM `'.addslashes($source_table).'`, `entity` WHERE `'.addslashes($source_table).'`.`id` = `entity`.`id` AND `entity`.`type` = "'.addslashes($type_id).'"';
db_query($q,'Attempt to delete rows from '.$source_table.' in reason_move_table_fields()');
get_entity_tables_by_id( $type_id, false );
return true;
}
