/** * Creates a view with name $viewID based on $refType bean type * and then left-joining the specified types in $types in the given * order. * * @param string $viewID desired name of the view * @param string $refType first bean type to be used as base * @param array $types array with types to be left-joined in view * * @return boolean $success whether we created a new view (false if already exists) */ public function createView($viewID, $refType, $types) { if ($this->oodb->isFrozen()) { return false; } $history = array(); $tables = array_flip($this->writer->getTables()); $refTable = $refType; //$this->writer->safeTable($refType, true); $currentTable = $refTable; $history[$refType] = $refType; foreach ($types as $t) { if (!isset($history[$t])) { $history[$t] = $t; $connection = array($t, $currentTable); sort($connection); $connection = implode("_", $connection); $connectionTable = $this->writer->safeTable($connection, true); if (isset($tables[$connectionTable])) { //this connection exists $srcPoint = $this->writer->safeTable($connection) . "." . $this->writer->safeColumn($currentTable . "_id"); //i.e. partic_project.project_id $dstPoint = $this->writer->safeTable($currentTable) . "." . $this->writer->safeColumn($this->writer->getIDField($currentTable)); //i.e. project.id $joins[$connection] = array($srcPoint, $dstPoint); //now join the type $srcPoint = $this->writer->safeTable($connection) . "." . $this->writer->safeColumn($t . "_id"); $dstPoint = $this->writer->safeTable($t) . "." . $this->writer->safeColumn($this->writer->getIDField($t)); $joins[$t] = array($srcPoint, $dstPoint); } else { //this connection does not exist $srcPoint = $this->writer->safeTable($t) . "." . $this->writer->safeColumn($currentTable . "_id"); $dstPoint = $this->writer->safeTable($currentTable) . "." . $this->writer->safeColumn($this->writer->getIDField($currentTable)); $joins[$t] = array($srcPoint, $dstPoint); } } //now set the new refTable $currentTable = $t; } try { $rs = (bool) $this->writer->createView($refType, $joins, $viewID); } catch (Exception $e) { throw new RedBean_Exception_SQL('Could not create view, types does not seem related (yet)..'); } return $rs; }
/** * Performs the actual optimization. In this case the optimizer looks * at the size of the column and the size of the value. If the value size is * smaller than the column size it tries to convert the column to a smaller * size. Next, it counts if there is any different between the smaller column * and the original column. If no differences are found the original column * gets replaced. * Like the other optimizers, this optimizer returns TRUE if it thinks * further optimizations can happen, FALSE otherwise. * * @return boolean $yesNo advance to next optimizer */ public function optimize() { //get the type of the current value $type = $this->writer->scanType($this->value); //get all the fields in the table $fields = $this->writer->getColumns($this->table); //If the column for some reason does not occur in fields, return if (!in_array($this->column, array_keys($fields))) { return false; } //get the type we got in the field of the table $typeInField = $this->writer->code($fields[$this->column]); //Is the type too wide? if ($type < $typeInField) { try { @$this->adapter->exec("alter table " . $this->writer->safeTable($this->table) . " drop __test"); } catch (Exception $e) { } //Try to re-fit the entire column; by testing it. $type = $this->writer->typeno_sqltype[$type]; //Add a test column. @$this->adapter->exec("alter table " . $this->writer->safeTable($this->table) . " add __test " . $type); //Copy the values and see if there are differences. @$this->adapter->exec("update " . $this->writer->safeTable($this->table) . " set __test=" . $this->writer->safeColumn($this->column) . ""); $rows = $this->adapter->get("select " . $this->writer->safeColumn($this->column) . " as a, __test as b from " . $this->writer->safeTable($this->table)); $diff = 0; foreach ($rows as $row) { $diff += $row["a"] != $row["b"]; } if (!$diff) { //No differences; shrink column. @$this->adapter->exec("alter table " . $this->writer->safeTable($this->table) . " change " . $this->writer->safeColumn($this->column) . " " . $this->writer->safeColumn($this->column) . " " . $type); } //Throw away test column; we don't need it anymore! @$this->adapter->exec("alter table " . $this->writer->safeTable($this->table) . " drop __test"); } return false; }
/** * Performs the actual optimization. In this case the optimizer first * scans the value. If the value if of type 'datetime' and the column * is not it tries to make the column datetime. If the column is 'datetime' * and the value 'datetime' it blocks further optimization. If the value * is NOT 'datetime' then it immediately returns true, thus allowing further * optimization. * * @return boolean $yesNo advance to next optimizer */ public function optimize() { if (!$this->matchesDateTime($this->value)) { return true; } //get the type of the current value $type = $this->writer->scanType($this->value); //get all the fields in the table $fields = $this->writer->getColumns($this->table); //If the column for some reason does not occur in fields, return //print_r($fields); if (!in_array($this->column, array_keys($fields))) { return false; } //get the type we got in the field of the table $typeInField = $this->writer->code($fields[$this->column]); //Is column already datetime? if ($typeInField != "datetime") { if ($this->matchesDateTime($this->value)) { //Ok, value is datetime, can we convert the column to support this? $cnt = (int) $this->adapter->getCell("select count(*) as n from " . $this->writer->safeTable($this->table) . " where\n\t\t\t\t\t\t {$this->column} regexp '[0-9]{4}-[0-1][0-9]-[0-3][0-9] [0-2][0-9]:[0-5][0-9]:[0-5][0-9]'\n\t\t\t\t\t\t OR {$this->column} IS NULL"); $total = (int) $this->adapter->getCell("SELECT count(*) FROM " . $this->writer->safeTable($this->table)); //Is it safe to convert: ie are all values compatible? if ($total === $cnt) { //yes $this->adapter->exec("ALTER TABLE " . $this->writer->safeTable($this->table) . " change " . $this->writer->safeColumn($this->column) . " " . $this->writer->safeColumn($this->column) . " datetime "); } //No further optimization required. return false; } //Further optimization could be useful. return true; } else { //yes column is datetime, if value is stop further optimizing return false; } }