/**
* Queries for all records from the target table that are associated with
* the specified record.
*
* @param \Bedrock\Model\Record $record the record to find associations with
* @param string $targetTableName the name of the target table from which to retrieve associated Records
* @param array $limit
*
* @throws \Bedrock\Model\Query\Exception if the query fails
* @return \Bedrock\Model\ResultSet the corresponding ResultSet
*/
public function associated($record, $targetTableName, $limit = array()) {
try {
if($this->_target == self::TARGET_PROCEDURE) {
throw new \Bedrock\Model\Query\Exception('Stored procedure queries cannot have associated records.');
}
// Setup
$result = new \Bedrock\Model\ResultSet();
$connection = \Bedrock\Common\Registry::get('database')->getConnection();
$recordTable = $record->getTable();
$recordTableName = $recordTable->getProperty('name');
$targetTable = new \Bedrock\Model\Table(array('name' => $targetTableName));
$targetTable->load();
// Verify Table Association
$mappings = $recordTable->getMappings();
if(array_key_exists($targetTableName, $mappings)) {
// Query for Associated Records
switch($mappings[$targetTableName]) {
default:
case \Bedrock\Model\Table::MAP_TYPE_ONE_ONE:
case \Bedrock\Model\Table::MAP_TYPE_ONE_MANY:
$foreignKeys = $targetTable->getForeignKeys();
$query = \Bedrock\Model\Query::from($targetTableName)->where($foreignKeys[$recordTableName]->name, '=', $record->{$record->getPrimaryKey()});
if(count($limit)) {
$query = $query->limit($limit['start'], $limit['count']);
}
$result = $query->execute();
break;
case \Bedrock\Model\Table::MAP_TYPE_MANY_ONE:
$foreignKeys = $recordTable->getForeignKeys();
$query = \Bedrock\Model\Query::from($targetTableName)->where($targetTable->getPrimaryKey()->name, '=', $record->{$foreignKeys[$targetTableName]->name});
if(count($limit)) {
$query = $query->limit($limit['start'], $limit['count']);
}
$result = $query->execute();
break;
case \Bedrock\Model\Table::MAP_TYPE_MANY_MANY:
$mappingTableName = \Bedrock\Model\Utilities::getMappingTableName($recordTableName, $targetTableName);
$query = 'SELECT t.* FROM ' . $targetTableName . ' t LEFT JOIN ' . $mappingTableName . ' m ON t.' . $targetTable->getPrimaryKey()->name . ' = m.' . $targetTable->getPrimaryKey()->name . '_' . $targetTableName . ' WHERE m.' . $recordTable->getPrimaryKey()->name . '_' . $recordTableName . ' = :recordPrimaryKey';
if(count($limit)) {
$query .= ' LIMIT ' . $limit['start'] . ', ' . $limit['count'];
}
$statement = $connection->prepare($query);
$statement->execute(array(':recordPrimaryKey' => $record->{$record->getPrimaryKey()}));
$results = $statement->fetchAll(\PDO::FETCH_ASSOC);
$records = array();
if($results) {
foreach($results as $result) {
$records[] = new \Bedrock\Model\Record($targetTableName, $result);
}
}
$result = new \Bedrock\Model\ResultSet($records);
$result->setCountAll(count($records));
break;
}
}
return $result;
}
catch(\PDOException $ex) {
\Bedrock\Common\Logger::exception($ex);
throw new \Bedrock\Model\Query\Exception('There was a problem with the database connection, associated records could not be retrieved.');
}
catch(\Exception $ex) {
\Bedrock\Common\Logger::exception($ex);
throw new \Bedrock\Model\Query\Exception('Could not retrieve associated records for the specified Record object.');
}
}
/**
* Imports table schemas into the current database (overwrites any existing
* tables and records).
*
* @param string $importSource the file containing the definitions to import
* @param string $importType the format used
* @param boolean $sourceIsFile whether or not the specified source is a file
* @return boolean whether or not the import was successful
*/
public function importTableSchemas($importSource, $importType = \Bedrock\Model::FORMAT_SQL, $sourceIsFile = true) {
try {
// Setup
$result = true;
\Bedrock\Common\Logger::info('Importing table schemas for database "' . $this->_name . '"...');
$this->load();
if(!is_file($importSource)) {
throw new \Bedrock\Model\Exception('The import source specified is invalid: "' . $importSource . '"');
}
switch($importType) {
default:
case \Bedrock\Model::FORMAT_SQL:
$lines = file($importSource);
$updated = false;
foreach($lines as $line) {
$line = trim($line);
if(preg_match("/^CREATE\s+(?:TEMPORARY\s+)?TABLE\s+(?:IF NOT EXISTS\s+)?([^\s]+)/i", $line, $matches)) {
$tableName = trim($matches[1], '`');
foreach($this->_tables as $key => $table) {
if($table->getProperty('name') == $tableName) {
$result &= $this->_tables[$key]->importSchema($line, \Bedrock\Model::FORMAT_SQL, false);
$updated = true;
}
}
if(!$updated) {
$newTable = new \Bedrock\Model\Table();
$result &= $this->_tables[$tableName] = $newTable->importSchema($line, \Bedrock\Model::FORMAT_SQL, false);
$updated = false;
}
}
}
break;
case \Bedrock\Model::FORMAT_XML:
$xml = simplexml_load_file($importSource);
$updated = false;
foreach($xml as $xmlTable) {
foreach($this->_tables as $key => $table) {
if($table->getProperty('name') == $xmlTable['name']) {
$result &= $this->_tables[$key]->importSchema($xmlTable->asXML(), \Bedrock\Model::FORMAT_XML, false);
$updated = true;
}
}
if(!$updated) {
$newTable = new \Bedrock\Model\Table(array(), $this);
$result &= $this->_tables[$xmlTable['name']] = $newTable->importSchema($xmlTable->asXML(), \Bedrock\Model::FORMAT_XML, false);
$updated = false;
}
}
break;
case \Bedrock\Model::FORMAT_YAML:
$importData = file_get_contents($importSource);
$yaml = new \Bedrock\Common\Data\YAML($importData);
$updated = false;
foreach($yaml->tables as $yamlTable) {
foreach($this->_tables as $key => $table) {
if($table->getProperty('name') == $yamlTable['name']) {
$yamlTable = new \Bedrock\Common\Data\YAML(array('table' => $yamlTable), true);
$result &= $this->_tables[$key]->importSchema((string) $yamlTable, \Bedrock\Model::FORMAT_YAML, false);
$updated = true;
}
}
if(!$updated) {
$newTable = new \Bedrock\Model\Table(array(), $this);
$yamlTable = new \Bedrock\Common\Data\YAML(array('table' => $yamlTable), true);
$result &= $this->_tables[$yamlTable['name']] = $newTable->importSchema((string) $yamlTable, \Bedrock\Model::FORMAT_YAML, false);
$updated = false;
}
}
break;
case \Bedrock\Model::FORMAT_CSV:
$importData = file_get_contents($importSource);
$csv = new \Bedrock\Common\Data\CSV($importData, ', ', true);
$updated = false;
$arrayTable = array();
$lastRowNum = count($csv) - 1;
foreach($csv as $rowNum => $row) {
if($row['class'] == 'table' || $rowNum == $lastRowNum) {
if(count($arrayTable)) {
foreach($this->_tables as $key => $table) {
if($table->getProperty('name') == $arrayTable[0]['name']) {
$csvTable = new \Bedrock\Common\Data\CSV($arrayTable, ', ', true);
$result &= $this->_tables[$key]->importSchema((string) $csvTable, \Bedrock\Model::FORMAT_CSV, false);
$updated = true;
break;
}
}
if(!$updated) {
$newTable = new \Bedrock\Model\Table(array(), $this);
$csvTable = new \Bedrock\Common\Data\CSV($arrayTable, ', ', true);
$result &= $this->_tables[$arrayTable[0]['name']] = $newTable->importSchema((string) $csvTable, \Bedrock\Model::FORMAT_CSV, false);
$updated = false;
}
}
$arrayTable = array();
}
$arrayTable[] = $row;
}
break;
}
return $result;
}
catch(\Exception $ex) {
\Bedrock\Common\Logger::exception($ex);
}
}
