/** * @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::wipeAll */ public function wipeAll() { foreach ($this->getTables() as $t) { foreach ($this->getKeys($t) as $k) { $this->adapter->exec("ALTER TABLE \"{$k['FKTABLE_NAME']}\" DROP FOREIGN KEY \"{$k['FK_NAME']}\""); } $this->adapter->exec("DROP TABLE \"{$t}\""); } }
/** * @see RedBean_QueryWriter::wipeAll */ public function wipeAll() { $this->adapter->exec('SET CONSTRAINTS ALL DEFERRED'); foreach ($this->getTables() as $t) { $t = $this->esc($t); $this->adapter->exec("DROP TABLE IF EXISTS {$t} CASCADE "); } $this->adapter->exec('SET CONSTRAINTS ALL IMMEDIATE'); }
/** * @see RedBean_QueryWriter::wipeAll */ public function wipeAll() { $this->adapter->exec('PRAGMA foreign_keys = 0 '); foreach ($this->getTables() as $t) { try { $this->adapter->exec("DROP TABLE IF EXISTS `{$t}`"); } catch (Exception $e) { } try { $this->adapter->exec("DROP TABLE IF EXISTS `{$t}`"); } catch (Exception $e) { } } $this->adapter->exec('PRAGMA foreign_keys = 1 '); }
/** * Removes all tables and views from the database. */ public function wipeAll() { $this->adapter->exec('PRAGMA foreign_keys = 0 '); foreach ($this->getTables() as $t) { try { $this->adapter->exec("drop table if exists`{$t}`"); } catch (Exception $e) { } try { $this->adapter->exec("drop view if exists`{$t}`"); } catch (Exception $e) { } } $this->adapter->exec('PRAGMA foreign_keys = 1 '); }
/** * @see RedBean_QueryWriter::wipeAll */ public function wipeAll() { $this->adapter->exec('SET FOREIGN_KEY_CHECKS = 0;'); foreach ($this->getTables() as $t) { try { $this->adapter->exec("DROP TABLE IF EXISTS `{$t}`"); } catch (Exception $e) { } try { $this->adapter->exec("DROP VIEW IF EXISTS `{$t}`"); } catch (Exception $e) { } } $this->adapter->exec('SET FOREIGN_KEY_CHECKS = 1;'); }
/** * 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 "); } } } }
/** * Removes all relations for a bean * @param RedBean_OODBBean $bean * @param <type> $type */ public function clearRelations(RedBean_OODBBean $bean, $type) { $this->oodb->store($bean); $table = $this->getTable(array($bean->getMeta("type"), $type)); $idfield = $this->writer->getIDField($bean->getMeta("type")); if ($type == $bean->getMeta("type")) { $property2 = $type . "2_id"; $cross = 1; } else { $cross = 0; } $property = $bean->getMeta("type") . "_id"; $sql = "DELETE FROM `{$table}`\n\t\tWHERE " . $this->adapter->escape($property) . " = " . $this->adapter->escape($bean->{$idfield}); if ($cross) { $sql .= " OR " . $this->adapter->escape($property2) . " = " . $this->adapter->escape($bean->{$idfield}); } try { $this->adapter->exec($sql); } catch (RedBean_Exception_SQL $e) { if ($e->getSQLState() != "42S02" && $e->getSQLState() != "42S22") { throw $e; } } }
/** * This method takes an array with key=>value pairs. * Each record that has a complete match with the array is * deleted from the table. * @param string $table * @param array $crits * @return integer $affectedRows */ public function deleteByCrit($table, $crits) { $table = $this->noKW($this->adapter->escape($table)); $values = array(); foreach ($crits as $key => $val) { $key = $this->noKW($this->adapter->escape($key)); $values[] = $this->adapter->escape($val); $conditions[] = $key . "= ? "; } $sql = "DELETE FROM {$table} WHERE " . implode(" AND ", $conditions); return (int) $this->adapter->exec($sql, $values); }
/** * 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 "); } } } }
/** * @see RedBean_QueryWriter::addColumn */ public function addColumn($type, $column, $field) { $table = $type; $type = $field; $table = $this->esc($table); $column = $this->esc($column); $type = isset($this->typeno_sqltype[$type]) ? $this->typeno_sqltype[$type] : ''; $this->adapter->exec("ALTER TABLE [{$table}] ADD [{$column}] {$type} "); }
/** * Counts rows in a table. * Uses SQLite optimization for deleting all records (i.e. no WHERE) * * @param string $beanType * * @return integer $numRowsFound */ public function wipe($type) { $table = $this->safeTable($type); $this->adapter->exec("DELETE FROM $table"); }
/** * Drops all tables in database */ public function wipeAll() { $this->adapter->exec('SET FOREIGN_KEY_CHECKS=0;'); foreach ($this->getTables() as $t) { try { $this->adapter->exec("drop table if exists`{$t}`"); } catch (Exception $e) { } try { $this->adapter->exec("drop view if exists`{$t}`"); } catch (Exception $e) { } } $this->adapter->exec('SET FOREIGN_KEY_CHECKS=1;'); }
/** * Adds a Unique index constrain to the table. * @param string $table * @param string $col1 * @param string $col2 * @return void */ public function addUniqueIndex($table, $columns) { $name = "UQ_" . sha1(implode(',', $columns)); $sql = "CREATE UNIQUE INDEX IF NOT EXISTS {$name} ON {$table} (" . implode(",", $columns) . ")"; $this->adapter->exec($sql); }
/** * Adds a Unique index constrain to the table. * @param string $table * @param string $col1 * @param string $col2 * @return void */ public function addUniqueIndex($table, $columns) { sort($columns); //else we get multiple indexes due to order-effects foreach ($columns as $k => $v) { $columns[$k] = "`" . $this->adapter->escape($v) . "`"; } $table = $this->check($table); $r = $this->adapter->get("SHOW INDEX FROM `{$table}`"); $name = "UQ_" . sha1(implode(',', $columns)); if ($r) { foreach ($r as $i) { if ($i["Key_name"] == $name) { return; } } } $sql = "ALTER IGNORE TABLE `{$table}`\n ADD UNIQUE INDEX `{$name}` (" . implode(",", $columns) . ")"; $this->adapter->exec($sql); }