DataAccess/DataMapper/Helpers/AbstractPopulationHelper.php

   1 <?php
   2
   3 namespace DataAccess\DataMapper\Helpers;
   4
   5 use Exception;
   6
   7 class AbstractPopulationHelper
   8 {
   9
  10     const REFERENCE_FORWARD = 1;
  11     const REFERENCE_BACK = 2;
  12     const REFERENCE_SELF = 3;
  13     const QUERY_TYPE_UPDATE = 'update';
  14     const QUERY_TYPE_CREATE = 'create';
  15
  16     static function getConstrutorArray($maps, $entity, $row, $db)
  17     {
  18         $constructors = array();
  19
  20         foreach($maps[$entity]['maps'] as $constructor => $mapsHelper)
  21         {
  22             switch(get_class($mapsHelper))
  23             {
  24                 case 'DataAccess\DataMapper\Helpers\IntMapsHelper':
  25                     if(!empty($row[$mapsHelper->getColumnName()]) && (string)(int)$row[$mapsHelper->getColumnName()] != $row[$mapsHelper->getColumnName()]) throw new Exception('Expected numeric value.');
  26                     $constructors[$constructor] = (int)$row[$mapsHelper->getColumnName()];
  27                     break;
  28                 case 'DataAccess\DataMapper\Helpers\VarcharMapsHelper':
  29                     $constructors[$constructor] = $row[$mapsHelper->getColumnName()];
  30                     break;
  31                 case 'DataAccess\DataMapper\Helpers\VOMapsHelper':
  32                 case 'DataAccess\DataMapper\Helpers\VOArrayMapsHelper':
  33                 case 'DataAccess\DataMapper\Helpers\EntityMapsHelper':
  34                 case 'DataAccess\DataMapper\Helpers\EntityArrayMapsHelper':
  35                     $constructors[$constructor] = $mapsHelper->populate($maps, $db, $entity, $row);
  36                     break;
  37             }
  38         }
  39         return $constructors;
  40     }
  41
  42     static function generateUpdateSaveQuery($maps, $entity, $id, $db, &$queries = array(), $extraColumns = array(), $mapsIndex = null)
  43     {
  44         $entityMapsIndex = isset($mapsIndex) ? $mapsIndex : self::getMapsNameFromEntityObject($entity, $maps);
  45
  46         if($id)
  47         {
  48             $query = sprintf('update %s set ', $maps[$entityMapsIndex]['table']);
  49         } else {
  50             $queryColumnNamesAndValues = array();
  51         }
  52
  53         foreach($maps[$entityMapsIndex]['maps'] as $mapsHelper)
  54         {
  55             $accessor = $mapsHelper->getAccessor();
  56             $property = isset($entity) ? $entity->{$accessor}() : null;
  57
  58             //sometimes children objects will be null, e.g., the banner for a simfile
  59             //just skip them.
  60             //Tricky: only skip when we're making a new entity. In the case of
  61             //existing ones we need to cater for null objects. For example a user
  62             //might change their country to null.
  63             if((!is_null($property) || (is_null($property) && $id)))
  64             {
  65                 switch(get_class($mapsHelper))
  66                 {
  67                     case 'DataAccess\DataMapper\Helpers\VOMapsHelper':
  68                         //we have a vo. Determine which way the reference is
  69                         //
  70                         //TODO: This is how I used to do this, but it failed if the property was NULL.
  71                         //I dunno if I will have to do a similar thing with entity references (next case block)
  72                         //but I'm leaving this here as a reminder if I ever have to come back to thiat.
  73                         //
  74                         //Notice I also added mapsIndex to the generateUpdateSaveQuery thing, that's important.
  75                         //When I call it in this case block you can see I added voMapsIndex as that argument.
  76                         //God I hope I can remember this stuff in the future.
  77                         //$voMapsIndex = self::getMapsNameFromEntityObject($property, $maps);
  78                         $voMapsIndex = $mapsHelper->getVOName();
  79                         $refDir = self::getReferenceDirection(
  80                             $maps[$entityMapsIndex]['table'],
  81                             $maps[$voMapsIndex]['table'],
  82                             $entityMapsIndex,
  83                             $mapsHelper->getTableName(),
  84                             $db);
  85
  86                         switch($refDir)
  87                         {
  88                             // our table stores their ID, all we do is update
  89                             // our reference.
  90                             case self::REFERENCE_FORWARD:
  91                                 $voTableId = self::findVOInDB($maps, $voMapsIndex, $property, $db);
  92
  93                                 if($id)
  94                                 {
  95                                     $query .= sprintf('%s=%u, ',
  96                                         strtolower($mapsHelper->getTableName() . '_id'),
  97                                         $voTableId);
  98                                 } else {
  99                                     // we have a forward reference to a value object.
 100                                     // see if it exists first:
 101                                     if($voTableId)
 102                                     {
 103                                         $queryColumnNamesAndValues[strtolower($mapsHelper->getTableName() . '_id')] = $voTableId;
 104                                     } else {
 105                                         //make a note that this field will need the id from another
 106                                         self::generateUpdateSaveQuery($maps, $property, NULL, $db, $queries);
 107                                         $queryColumnNamesAndValues[strtolower($mapsHelper->getTableName() . '_id')] = '%INDEX_REF_' . (count($queries)-1) . '%';
 108                                     }
 109                                 }
 110
 111                                 break;
 112                             case self::REFERENCE_SELF:
 113                                 //no need to find ids, but we need the
 114                                 //column names
 115                                 $columns = self::resolveColumnNamesAndValues($maps, $property);
 116                                 foreach($columns as $columnName=>$columnValue)
 117                                 {
 118                                     if($id)
 119                                     {
 120                                         //TODO: logic to detemine what the value is? i.e., string, int etc?
 121                                         $query .= sprintf('%s="%s", ',
 122                                             $columnName,
 123                                             $columnValue
 124                                         );
 125                                     } else {
 126                                         //TODO: logic to detemine what the value is? i.e., string, int etc?
 127                                         $queryColumnNamesAndValues[$columnName] = $db->quote($columnValue);
 128                                     }
 129                                 }
 130
 131                                 break;
 132                             case self::REFERENCE_BACK:
 133                                 $voId = self::findVOInDB($maps,
 134                                     $voMapsIndex,
 135                                     $property,
 136                                     $db,
 137                                     array(strtolower($entityMapsIndex . '_id') => $id));
 138                                 if($voId)
 139                                 {
 140                                     self::generateUpdateSaveQuery($maps, $property, $voId, $db, $queries, null, $voMapsIndex);
 141                                 } else {
 142                                     $extra = array(strtolower($entityMapsIndex . '_id') => '%MAIN_QUERY_ID%');
 143                                     self::generateUpdateSaveQuery($maps, $property, NULL, $db, $queries, $extra, $voMapsIndex);
 144                                 }
 145                                 break;
 146                         }
 147
 148                         break;
 149
 150                     // We should never update referenced entities, the db
 151                     // should always store an ID as a reference to them.
 152                     //
 153                     // In the case where we cannot find the entity in the database,
 154                     // throw an exception ?
 155                     case 'DataAccess\DataMapper\Helpers\EntityMapsHelper':
 156                         $subEntityMapsIndex = self::getMapsNameFromEntityObject($property, $maps);
 157                         $refDir = self::getReferenceDirection(
 158                             $maps[$entityMapsIndex]['table'],
 159                             $maps[$subEntityMapsIndex]['table'],
 160                             $entityMapsIndex,
 161                             $mapsHelper->getTableName(),
 162                             $db);
 163
 164                         switch($refDir)
 165                         {
 166                             // our table stores their ID, all we do is update
 167                             // our reference.
 168                             case self::REFERENCE_FORWARD:
 169                                 if($property->getId())
 170                                 {
 171                                     // we exist in db already, update our reference
 172                                     if($id)
 173                                     {
 174                                         $query .= sprintf('%s=%u, ',
 175                                             //strtolower($mapsHelper->getEntityName() . '_id'),
 176                                             strtolower($mapsHelper->getTableName() . '_id'),
 177                                             $property->getId());
 178                                     } else {
 179                                         //not in db yet. make new ref
 180                                         //$queryColumnNamesAndValues[strtolower($mapsHelper->getEntityName() . '_id')] = $property->getId();
 181                                         $queryColumnNamesAndValues[strtolower($mapsHelper->getTableName() . '_id')] = $property->getId();
 182                                     }
 183                                 } else {
 184                                     // The entity we care about references an entity that
 185                                     // has not yet been saved.
 186                                     //
 187                                     // TODO: Should we _try_ to save it? Or should
 188                                     // it be enforced that entites already exist in the db?
 189                                     // In the case of something like referencing a user entity,
 190                                     // then for sure the user should already be saved because
 191                                     // it makes no sense to assign a user at the time of simfile
 192                                     // upload, they should have already completed the process.
 193                                     // but could there be other entities where it does make sense
 194                                     // for them to be created at the time of something else ?
 195                                     throw new Exception(sprintf(
 196                                         'Could not find referenced entity, %s, in the database. Has it been saved yet?',
 197                                          $mapsHelper->getEntityName()));
 198                                 }
 199                                 break;
 200                         }
 201                         break;
 202                     case 'DataAccess\DataMapper\Helpers\IntMapsHelper':
 203                         if($id)
 204                         {
 205                             //easy case, plain values in our table.
 206                             $query .= sprintf('%s=%u, ',
 207                                 $mapsHelper->getColumnName(),
 208                                 $property);
 209                         } else {
 210                             if(is_bool($property))
 211                             {
 212                                 $property = ($property) ? '1' : '0';
 213                             }
 214                             $queryColumnNamesAndValues[$mapsHelper->getColumnName()] = $property;
 215                         }
 216                         break;
 217                     case 'DataAccess\DataMapper\Helpers\VarcharMapsHelper':
 218                         //XXX: pls magically fix all my character encoding issues.
 219                         $property = isset($property) ? mb_convert_encoding($property, "UTF-8", mb_detect_encoding($property, "UTF-8, ISO-8859-1, ISO-8859-15", true)) : NULL;
 220                         if($id){
 221                             //easy case, plain values in our table.
 222                             if(isset($property))
 223                             {
 224                                 $query .= sprintf('%s="%s", ',
 225                                     $mapsHelper->getColumnName(),
 226                                     $property);
 227                             } else {
 228                                 $query .= sprintf('%s=NULL, ',
 229                                     $mapsHelper->getColumnName());
 230                             }
 231                         } else {
 232                             $queryColumnNamesAndValues[$mapsHelper->getColumnName()] = $db->quote($property);
 233                         }
 234
 235                         break;
 236
 237                     // I am making a bit of an assumption here. In my mind it only
 238                     // makes sense for an array of VOs to be stored in a different
 239                     // table in the DB since the main row can't possibly store
 240                     // different objects.
 241                     //
 242                     // in that regard, the way this works is that mapVOArrayToIds
 243                     // simply queries the DB and returns the VO ids in order then
 244                     // I assume that the object also has them in the same order
 245                     // (which it will if it is pulled out by this mapper.
 246                     //
 247                     // in the case of setting up a new entity, the VOs should never
 248                     // exist in the first place, so we just make them.
 249                     case 'DataAccess\DataMapper\Helpers\VOArrayMapsHelper':
 250                     case 'DataAccess\DataMapper\Helpers\EntityArrayMapsHelper':
 251                         if($id && isset($property[0]))
 252                         {
 253                             // If we assume that all elements in the array are the same then
 254                             // we can just use the first one to figure out which maps entry to use
 255
 256                             $subEntityMapsIndex = self::getMapsNameFromEntityObject($property[0], $maps);
 257                             //TODO: I think this function will work with Entities too, but I should probably rename it at some point
 258                             $voIds = self::mapVOArrayToIds($maps[$subEntityMapsIndex]['table'],
 259                                 array(strtolower($entityMapsIndex . '_id'), $id),
 260                                 $db);
 261
 262                             foreach($property as $index => $propertyArrayElement)
 263                             {
 264                                 //XXX: I wanted this to only run on VOs, not entities. But there's a problem with that.
 265                                 //when creating a pack, the simfile entities need to reference the pack, and the only way for
 266                                 //that to happen is here. What I do instead is check that the entity has an id (which implies
 267                                 //it has already been created and save) if it is a IDivineEntity. If it doesn't, complain.
 268                                 //this ensures consistent behaviour with other parts of this mapper.
 269                                 if($property instanceof \Domain\Entities\IDivineEntity && !$property->getId())
 270                                 {
 271                                     throw new Exception(sprintf(
 272                                         'Could not find referenced entity, %s, in the database. Has it been saved yet?',
 273                                          $mapsHelper->getEntityName()));
 274                                 }
 275
 276                                 $extra = array(strtolower($entityMapsIndex . '_id') => $id);
 277                                 if(isset($voIds[$index]))
 278                                 {
 279                                     self::generateUpdateSaveQuery($maps, $propertyArrayElement, $voIds[$index], $db, $queries, $extra);
 280                                 } else {
 281                                     self::generateUpdateSaveQuery($maps, $propertyArrayElement, NULL, $db, $queries, $extra);
 282                                 }
 283                             }
 284
 285                             break;
 286                         } else {
 287                             foreach($property as $propertyArrayElement)
 288                             {
 289                                 //XXX: I wanted this to only run on VOs, not entities. But there's a problem with that.
 290                                 //when creating a pack, the simfile entities need to reference the pack, and the only way for
 291                                 //that to happen is here. What I do instead is check that the entity has an id (which implies
 292                                 //it has already been created and save) if it is a IDivineEntity. If it doesn't, complain.
 293                                 //this ensures consistent behaviour with other parts of this mapper.
 294                                 if($property instanceof \Domain\Entities\IDivineEntity && !$property->getId())
 295                                 {
 296                                     throw new Exception(sprintf(
 297                                         'Could not find referenced entity, %s, in the database. Has it been saved yet?',
 298                                          $mapsHelper->getEntityName()));
 299                                 }
 300
 301                                 // TODO: TRICKY! Since this is a back-reference, it
 302                                 // needs the ID of the object we're trying to save
 303                                 // to complete
 304                                 $extra = array(strtolower($entityMapsIndex . '_id') => '%MAIN_QUERY_ID%');
 305                                 self::generateUpdateSaveQuery($maps, $propertyArrayElement, NULL, $db, $queries, $extra);
 306                             }
 307                         }
 308                 }
 309             }
 310         }
 311
 312         if($id)
 313         {
 314             $queryColumnNamesAndValues = @$queryColumnNamesAndValues ?: array();
 315             $queries[] = array('id' => $id, 'prepared' => $query, 'table' => $maps[$entityMapsIndex]['table'], 'columns' => $queryColumnNamesAndValues);
 316         } else {
 317             $queryColumnNamesAndValues = array_merge($queryColumnNamesAndValues, $extraColumns);
 318             $queries[] = array('table' => $maps[$entityMapsIndex]['table'], 'columns' => $queryColumnNamesAndValues);
 319         }
 320
 321         return $queries;
 322     }
 323
 324     static private function getMapsNameFromEntityObject($entity, $maps)
 325     {
 326         //todo maybe check that $entity is vo or entity
 327
 328         $classname = get_class($entity);
 329         foreach ($maps as $entityName => $map)
 330         {
 331             if($map['class'] == $classname)
 332             {
 333                 return $entityName;
 334             }
 335         }
 336     }
 337
 338     static private function getReferenceDirection($tableA, $tableB, $nameA, $nameB, $db)
 339     {
 340         //TODO: check if tables are the same and return a constant for that
 341         //echo '!!! ' . $tableA . ' needs ' . $nameB . ' : ' . $tableB . ' needs ' . $nameA . ' !!!<br />';
 342         $dbName = $db->query('select database()')->fetchColumn();
 343         if($tableA === $tableB)
 344         {
 345             return self::REFERENCE_SELF;
 346         }
 347
 348         // first look in table A for a reference to B
 349         $statement = $db->prepare(sprintf(
 350             'SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`="%s" AND `TABLE_NAME`="%s"',
 351             $dbName,
 352             $tableA));
 353
 354         $statement->execute();
 355         $rows = $statement->fetchAll();
 356
 357         //print_r($rows);
 358
 359         foreach($rows as $row)
 360         {
 361             if($row['COLUMN_NAME'] == strtolower($nameB . '_id'))
 362             {
 363                 return self::REFERENCE_FORWARD;
 364             }
 365         }
 366
 367         // now look in table b for a reference to a
 368         $statement = $db->prepare(sprintf(
 369             'SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`="%s" AND `TABLE_NAME`="%s"',
 370             $dbName,
 371             $tableB));
 372
 373         $statement->execute();
 374         $rows = $statement->fetchAll();
 375
 376         foreach($rows as $row)
 377         {
 378             if($row['COLUMN_NAME'] == strtolower($nameA . '_id'))
 379             {
 380                 return self::REFERENCE_BACK;
 381             }
 382         }
 383     }
 384
 385     // can use this when we reference a VO
 386     static public function findVOInDB($maps, $mapsIndex, $VO, $db, $extraColumns = array())
 387     {
 388         //$mapsIndex = self::getMapsNameFromEntityObject($VO, $maps);
 389         $table = $maps[$mapsIndex]['table'];
 390
 391         //TODO: This may break everythign, but I _think_ if I pass extraColuns, it is always an id column.
 392         //I also think that this method is only called when we are trying to work out a VO (NOT a VOArray)
 393         //in which case there should be one unique row somewhere in the database that corresponds to the VO
 394         //we want, in that case we can just find it by id. Throwing in more columns causes issues trying to
 395         //update an existing VO because it uses the values of the current object, which we are trying to save,
 396         //so it never finds the existing row and therefore makes a whole new one, which ruins everything.
 397         if($extraColumns)
 398         {
 399             $columns = $extraColumns;
 400         } else {
 401             //I only had this before, I think I don't actually need to do this merge but I'm leaving it incase
 402             $columns = array_merge(self::resolveColumnNamesAndValues($maps, $VO), $extraColumns);
 403         }
 404
 405         $query = "SELECT * FROM $table where ";
 406
 407         foreach($columns as $columnName => $columnValue)
 408         {
 409             $columnValue = $db->quote($columnValue);
 410             $query .= sprintf('%s=%s AND ', $columnName, str_replace('"', '\"', $columnValue));
 411         }
 412
 413         $query = substr($query, 0, -4);
 414
 415         $statement = $db->prepare($query);
 416         $statement->execute();
 417         $row = $statement->fetch();
 418
 419         return $row['id'];
 420     }
 421
 422     // this will figure out what columns belong to an entity and
 423     // map the column names to the current entity values
 424     static public function resolveColumnNamesAndValues($maps, $entity, $originalTable = null, &$columnNamesAndValues = array())
 425     {
 426         $mapsIndex = self::getMapsNameFromEntityObject($entity, $maps);
 427
 428         // This is the name of the table that the current object
 429         // we are looking at belongs to. We need to compare this
 430         //  to original table to decide what to do.
 431         $currentTable = $maps[$mapsIndex]['table'];
 432
 433         if(!$originalTable)
 434         {
 435             // this will be the table that the VO we care about is stored in
 436             // we check all future values to make sure they belong to this table.
 437             // on the first pass we pull it out, and then on subsequent passes
 438             // it should come in through the function call.
 439             $originalTable = $currentTable;
 440         }
 441
 442         foreach($maps[$mapsIndex]['maps'] as $mapsHelper)
 443         {
 444             switch(get_class($mapsHelper))
 445             {
 446                 case 'DataAccess\DataMapper\Helpers\VOMapsHelper':
 447                     $accessor = $mapsHelper->getAccessor();
 448                     $VO = $entity->{$accessor}();
 449                     self::resolveColumnNamesAndValues($maps, $VO, $originalTable, $columnNamesAndValues);
 450                     break;
 451                 case 'DataAccess\DataMapper\Helpers\VarcharMapsHelper':
 452                 case 'DataAccess\DataMapper\Helpers\IntMapsHelper':
 453                     //is plain value.
 454
 455                     if($currentTable == $originalTable)
 456                     {
 457                         //It also keeps values in our table. Saving.
 458                         $accessor = $mapsHelper->getAccessor();
 459                         $value = $entity->{$accessor}();
 460
 461                         $columnNamesAndValues[$mapsHelper->getColumnName()] = $value;
 462                     } else {
 463                         //It does not store values in our table
 464                         //TODO: Should I try check if our table references the id of the record in the other table?
 465                     }
 466                     break;
 467             }
 468         }
 469
 470         return $columnNamesAndValues;
 471     }
 472
 473     // When we have VO arrays, it should be the case that there is another
 474     // table that references us. If we assume entries are in the db in the
 475     // same order they are in the array, it should be possible to map them up.
 476     // This way we can update existing VO entries instead of deleting and
 477     // making new ones. And if there are VOs where we can't get an ID, that
 478     // means we have to make a new one.
 479     //
 480     // Assumption: Array contains entries of all the same type
 481     static public function mapVOArrayToIds($voTable, $columnToMatch, $db)
 482     {
 483         $query = sprintf('SELECT id from %s WHERE %s=%u',
 484             $voTable,
 485             $columnToMatch[0],
 486             $columnToMatch[1]);
 487
 488         $statement = $db->prepare($query);
 489         $statement->execute();
 490         $rows = $statement->fetchAll();
 491
 492         $map = array();
 493
 494         foreach($rows as $row)
 495         {
 496             $map[] = $row['id'];
 497         }
 498
 499         return $map;
 500     }
 501 }
 502
 503 //go off and resolve all column names for this table recursively
 504 //do a check to make sure the VO we are investigating is in the same table
 505 //   -if it is: good, record its column name and value
 506 //   -if it isn't, we need to find this next VO in the db and record its ID as our column value
 507 //       -assuming a forward reference (IE our table has a column called voname_id.
 508 //       -if it doesn't, then we don't have to worry about it
 509