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