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