/** * Add the constraints for a specific database driver: MySQL. * * @todo Too many arguments; find a way to solve this in a neater way. * * @param string $table table table to add constrains to * @param string $table1 table1 first reference table * @param string $table2 table2 second reference table * @param string $property1 property1 first column * @param string $property2 property2 second column * * @return boolean $succes whether the constraint has been applied */ protected function constrain($table, $table1, $table2, $property1, $property2) { try { $db = $this->adapter->getCell('SELECT database()'); $fks = $this->adapter->getCell("SELECT count(*)\n\t\t\t\tFROM information_schema.KEY_COLUMN_USAGE\n\t\t\t\tWHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND\n\t\t\t\tCONSTRAINT_NAME <>'PRIMARY' AND REFERENCED_TABLE_NAME IS NOT NULL", array($db, $table)); // already foreign keys added in this association table if ($fks > 0) { return FALSE; } $columns = $this->getColumns($table); if ($this->code($columns[$property1]) !== RedBean_QueryWriter_MySQL::C_DATATYPE_UINT32) { $this->widenColumn($table, $property1, RedBean_QueryWriter_MySQL::C_DATATYPE_UINT32); } if ($this->code($columns[$property2]) !== RedBean_QueryWriter_MySQL::C_DATATYPE_UINT32) { $this->widenColumn($table, $property2, RedBean_QueryWriter_MySQL::C_DATATYPE_UINT32); } $sql = "\n\t\t\t\tALTER TABLE " . $this->esc($table) . "\n\t\t\t\tADD FOREIGN KEY({$property1}) references `{$table1}`(id) ON DELETE CASCADE;\n\t\t\t"; $this->adapter->exec($sql); $sql = "\n\t\t\t\tALTER TABLE " . $this->esc($table) . "\n\t\t\t\tADD FOREIGN KEY({$property2}) references `{$table2}`(id) ON DELETE CASCADE\n\t\t\t"; $this->adapter->exec($sql); return TRUE; } catch (Exception $e) { return FALSE; } }
/** * @see RedBean_QueryWriter::addFK */ public function addFK($type, $targetType, $field, $targetField, $isDependent = FALSE) { $table = $this->getTableName($type); $tableNoQ = $this->getTableName($type, TRUE); $targetTable = $this->esc($targetType); $column = $this->esc($field); $columnNoQ = $this->esc($field, TRUE); $targetColumn = $this->esc($targetField); $targetColumnNoQ = $this->esc($targetField, TRUE); $db = $this->adapter->getCell('SELECT DATABASE()'); $fkName = 'fk_' . $tableNoQ . '_' . $columnNoQ . '_' . $targetColumnNoQ . ($isDependent ? '_casc' : ''); $cName = 'cons_' . $fkName; $cfks = $this->adapter->getCell("\n\t\t\tSELECT CONSTRAINT_NAME\n\t\t\tFROM information_schema.KEY_COLUMN_USAGE\n\t\t\tWHERE TABLE_SCHEMA ='{$db}' AND TABLE_NAME = '{$tableNoQ}' AND COLUMN_NAME = '{$columnNoQ}' AND\n\t\t\tCONSTRAINT_NAME <>'PRIMARY' AND REFERENCED_TABLE_NAME is not null\n\t\t"); $flagAddKey = FALSE; try { // No keys if (!$cfks) { $flagAddKey = TRUE; //go get a new key } // Has fk, but different setting, --remove if ($cfks && $cfks != $cName) { $this->adapter->exec("ALTER TABLE {$table} DROP FOREIGN KEY {$cfks} "); $flagAddKey = TRUE; //go get a new key. } if ($flagAddKey) { $this->adapter->exec("ALTER TABLE {$table}\n\t\t\t\tADD CONSTRAINT {$cName} FOREIGN KEY {$fkName} ( {$column} ) REFERENCES {$targetTable} (\n\t\t\t\t{$targetColumn}) ON DELETE " . ($isDependent ? 'CASCADE' : 'SET NULL') . ' ON UPDATE SET NULL ;'); } } catch (Exception $e) { // Failure of fk-constraints is not a problem } }
/** * @see RedBean_QueryWriter::addIndex */ public function addIndex($type, $name, $column) { $table = $type; $table = $this->getTableName($table); $name = preg_replace('/\\W/', '', $name); $column = $this->esc($column); if ($this->adapter->getCell("SELECT COUNT(*) FROM pg_class WHERE relname = '{$name}'")) { return; } try { $this->adapter->exec("CREATE INDEX {$name} ON {$table} ({$column}) "); } catch (Exception $e) { } }
/** * This method adds a foreign key from type and field to * target type and target field. * The foreign key is created without an action. On delete/update * no action will be triggered. The FK is only used to allow database * tools to generate pretty diagrams and to make it easy to add actions * later on. * This methods accepts a type and infers the corresponding table name. * * * @param string $type type that will have a foreign key field * @param string $targetType points to this type * @param string $field field that contains the foreign key value * @param string $targetField field where the fk points to * * @return void */ public function addFK($type, $targetType, $field, $targetField) { $table = $this->safeTable($type); $tableNoQ = $this->safeTable($type, true); $targetTable = $this->safeTable($targetType); $column = $this->safeColumn($field); $columnNoQ = $this->safeColumn($field, true); $targetColumn = $this->safeColumn($targetField); $db = $this->adapter->getCell("select database()"); $fks = $this->adapter->getCell("\n\t\t\tSELECT count(*)\n\t\t\tFROM information_schema.KEY_COLUMN_USAGE\n\t\t\tWHERE TABLE_SCHEMA ='{$db}' AND TABLE_NAME = '{$tableNoQ}' AND COLUMN_NAME = '{$columnNoQ}' AND\n\t\t\tCONSTRAINT_NAME <>'PRIMARY' AND REFERENCED_TABLE_NAME is not null\n\t\t"); if ($fks == 0) { try { $this->adapter->exec("ALTER TABLE {$table}\n\t\t\t\tADD FOREIGN KEY ( {$column} ) REFERENCES {$targetTable} (\n\t\t\t\t{$targetColumn}) ON DELETE SET NULL ON UPDATE SET NULL ;"); } catch (Exception $e) { } } }
/** * Tries to convert columns to MySQL specific types like: * datetime, ENUM etc. This method is called automatically for you and * works completely in the background. You can however if you like trigger * this method by invoking it directly. * @param string $table * @param string $column * @param string $columnType * @param string $value */ public function MySQLSpecificColumns($table, $column, $columnType, $value) { //$this->adapter->getDatabase()->setDebugMode(1); $table = $this->adapter->escape($table); $column = $this->adapter->escape($column); //Is column already datetime? if ($columnType != "datetime") { $pattern = "/^([0-9]{2,4})-([0-1][0-9])-([0-3][0-9]) (?:([0-2][0-9]):([0-5][0-9]):([0-5][0-9]))?\$/"; if (preg_match($pattern, $value)) { //Ok, value is datetime, can we convert the column to support this? $cnt = (int) $this->adapter->getCell("select count(*) as n from {$table} where\n\t\t\t\t\t{$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"); $total = (int) $this->adapter->getCell("SELECT count(*) FROM " . $this->writer->noKW($table)); //Is it safe to convert: ie are all values compatible? if ($total === $cnt) { //yes $this->adapter->exec("ALTER TABLE " . $this->writer->noKW($table) . " change " . $this->writer->noKW($column) . " " . $this->writer->noKW($column) . " datetime "); } } } }
/** * Counts the number of beans of a specific type * @param RedBean_OODBBean $bean * @return integer $count */ public function numberOf(RedBean_OODBBean $bean) { $type = $this->adapter->escape($bean->getMeta("type")); return (int) $this->adapter->getCell("SELECT count(*) FROM `{$type}`"); }
/** * Tries to convert columns to MySQL specific types like: * datetime, ENUM etc. This method is called automatically for you and * works completely in the background. You can however if you like trigger * this method by invoking it directly. * @param string $table * @param string $column * @param string $columnType * @param string $value */ public function MySQLSpecificColumns($table, $column, $columnType, $value) { $table = $this->adapter->escape($table); $column = $this->adapter->escape($column); if ($columnType != "datetime") { $pattern = "/^([0-9]{2,4})-([0-1][0-9])-([0-3][0-9]) (?:([0-2][0-9]):([0-5][0-9]):([0-5][0-9]))?\$/"; if (preg_match($pattern, $value)) { $cnt = (int) $this->adapter->getCell("select count(*) as n from {$table} where\n\t\t\t\t\t\t {$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 "); $total = (int) $this->adapter->getCell("SELECT count(*) FROM " . $this->writer->noKW($table)); if ($total === $cnt) { $this->adapter->exec("ALTER TABLE " . $this->writer->noKW($table) . " change " . $this->writer->noKW($column) . " " . $this->writer->noKW($column) . " datetime "); } } } }
/** * Counts rows in a table. * * @param string $beanType * * @return integer $numRowsFound */ public function count($beanType) { $table = $this->safeTable($beanType); $sql = "SELECT count(*) FROM $table "; return (int) $this->adapter->getCell($sql); }