public function testBackupAndRestoreDatabase()
{
chdir(COMMON_ROOT . DIRECTORY_SEPARATOR . 'protected' . DIRECTORY_SEPARATOR . 'commands');
// Create new database (zurmo_temp).
if (RedBeanDatabase::getDatabaseType() == 'mysql') {
$this->assertTrue(DatabaseCompatibilityUtil::createDatabase('mysql', $this->temporaryDatabaseHostname, $this->temporaryDatabaseUsername, $this->temporaryDatabasePassword, $this->temporaryDatabasePort, $this->temporaryDatabaseName));
$connection = @mysql_connect($this->temporaryDatabaseHostname . ':' . $this->temporaryDatabasePort, $this->temporaryDatabaseUsername, $this->temporaryDatabasePassword);
$this->assertTrue(is_resource($connection));
@mysql_select_db($this->temporaryDatabaseName);
@mysql_query("create table temptable (temptable_id int(11) unsigned not null)", $connection);
@mysql_query("insert into temptable values ('5')", $connection);
@mysql_query("insert into temptable values ('10')", $connection);
$result = @mysql_query("SELECT count(*) from temptable");
$totalRows = mysql_fetch_row($result);
@mysql_close($connection);
$this->assertEquals(2, $totalRows[0]);
$command = "php zurmocTest.php database backup {$this->databaseBackupTestFile} mysql ";
$command .= "{$this->temporaryDatabaseHostname} {$this->temporaryDatabaseName} ";
$command .= "{$this->temporaryDatabasePort} {$this->temporaryDatabaseUsername} {$this->temporaryDatabasePassword}";
if (!IS_WINNT) {
$command .= ' 2>&1';
}
exec($command, $output);
sleep(2);
$this->assertTrue(is_file($this->databaseBackupTestFile));
//Drop database, and restore it from backup.
$this->assertTrue(DatabaseCompatibilityUtil::createDatabase('mysql', $this->temporaryDatabaseHostname, $this->temporaryDatabaseUsername, $this->temporaryDatabasePassword, $this->temporaryDatabasePort, $this->temporaryDatabaseName));
// Ensure that database don't exist
$connection = @mysql_connect($this->temporaryDatabaseHostname . ':' . $this->temporaryDatabasePort, $this->temporaryDatabaseUsername, $this->temporaryDatabasePassword);
$this->assertTrue(is_resource($connection));
@mysql_select_db($this->temporaryDatabaseName, $connection);
$result = @mysql_query("SELECT count(*) from temptable", $connection);
$this->assertFalse($result);
// Now restore database
$command = "php zurmocTest.php database restore {$this->databaseBackupTestFile} mysql ";
$command .= "{$this->temporaryDatabaseHostname} {$this->temporaryDatabaseName} ";
$command .= "{$this->temporaryDatabasePort} {$this->temporaryDatabaseUsername} {$this->temporaryDatabasePassword}";
if (!IS_WINNT) {
$command .= ' 2>&1';
}
exec($command, $output);
sleep(2);
$connection = @mysql_connect($this->temporaryDatabaseHostname . ':' . $this->temporaryDatabasePort, $this->temporaryDatabaseUsername, $this->temporaryDatabasePassword);
$this->assertTrue(is_resource($connection));
$result = @mysql_select_db($this->temporaryDatabaseName, $connection);
$this->assertTrue($result);
$result = @mysql_query("SELECT count(*) from temptable", $connection);
$this->assertTrue(is_resource($result));
$totalRows = mysql_fetch_row($result);
$result = @mysql_query("SELECT * from temptable", $connection);
$rows1 = mysql_fetch_row($result);
$rows2 = mysql_fetch_row($result);
@mysql_close($connection);
$this->assertEquals(2, $totalRows[0]);
$this->assertEquals(5, $rows1[0]);
$this->assertEquals(10, $rows2[0]);
}
}
public function testMapHintTypeIntoDatabaseColumnType()
{
if (RedBeanDatabase::getDatabaseType() == 'mysql') {
$databaseColumnType = DatabaseCompatibilityUtil::mapHintTypeIntoDatabaseColumnType('blob');
$this->assertEquals('BLOB', $databaseColumnType);
$databaseColumnType = DatabaseCompatibilityUtil::mapHintTypeIntoDatabaseColumnType('longblob');
$this->assertEquals('LONGBLOB', $databaseColumnType);
$databaseColumnType = DatabaseCompatibilityUtil::mapHintTypeIntoDatabaseColumnType('boolean');
$this->assertEquals('TINYINT(1)', $databaseColumnType);
$databaseColumnType = DatabaseCompatibilityUtil::mapHintTypeIntoDatabaseColumnType('date');
$this->assertEquals('DATE', $databaseColumnType);
$databaseColumnType = DatabaseCompatibilityUtil::mapHintTypeIntoDatabaseColumnType('datetime');
$this->assertEquals('DATETIME', $databaseColumnType);
$databaseColumnType = DatabaseCompatibilityUtil::mapHintTypeIntoDatabaseColumnType('string');
$this->assertEquals('VARCHAR(255)', $databaseColumnType);
$databaseColumnType = DatabaseCompatibilityUtil::mapHintTypeIntoDatabaseColumnType('text');
$this->assertEquals('TEXT', $databaseColumnType);
$databaseColumnType = DatabaseCompatibilityUtil::mapHintTypeIntoDatabaseColumnType('longtext');
$this->assertEquals('LONGTEXT', $databaseColumnType);
$databaseColumnType = DatabaseCompatibilityUtil::mapHintTypeIntoDatabaseColumnType('id');
$this->assertEquals('INT(11) UNSIGNED', $databaseColumnType);
try {
$databaseColumnType = DatabaseCompatibilityUtil::mapHintTypeIntoDatabaseColumnType('invalidType');
$this->fail();
} catch (NotSupportedException $e) {
// Do nothing
}
}
}
public static function dropStoredFunctionsAndProcedures()
{
assert('RedBeanDatabase::isSetup()');
if (RedBeanDatabase::getDatabaseType() == 'mysql') {
try {
$rows = ZurmoRedBean::getAll("select routine_name, routine_type from information_schema.routines;");
foreach ($rows as $row) {
ZurmoRedBean::exec("drop {$row['routine_type']} if exists {$row['routine_name']}");
}
} catch (Exception $e) {
if (isset($row)) {
echo "Failed to drop {$row['routine_type']} {$row['routine_name']}.\n";
}
throw $e;
}
if (YII_DEBUG) {
ZurmoRedBean::exec("drop procedure if exists write_log");
}
} else {
throw new NotSupportedException();
}
}
protected static function createIndex($tableName, $indexName, $columns = array())
{
assert('RedBeanDatabase::isSetup()');
assert('$tableName != ""');
assert('$indexName != ""');
assert('!empty($columns)');
if (RedBeanDatabase::getDatabaseType() == 'mysql') {
try {
$rows = R::getAll("SHOW INDEX FROM {$tableName}");
if (!empty($rows)) {
foreach ($rows as $row) {
// Delete only first index in sequence
if ($row['Key_name'] == $indexName && $row['Seq_in_index'] == '1') {
R::exec("DROP INDEX {$indexName} ON {$tableName}");
}
}
}
$columnsString = implode(",", $columns);
// Not Coding Standard
R::exec("ALTER TABLE {$tableName} ADD INDEX {$indexName} ({$columnsString});");
} catch (Exception $e) {
echo "Failed to add {$indexName} on {$tableName}.\n";
throw $e;
}
} else {
throw new NotSupportedException();
}
}
public static function resolveUnsignedByHintType($hint, $assumeSigned = false, $hintName = null)
{
if (RedBeanDatabase::getDatabaseType() == 'mysql') {
$integerHintTypes = array_keys(static::resolveIntegerMaxAllowedValuesByType());
if (in_array($hint, $integerHintTypes) && (!$assumeSigned || StringUtil::endsWith($hintName, '_id'))) {
return "UNSIGNED";
}
return null;
}
throw new NotSupportedException();
}
/**
* Map hint type into database valid type
* @param string $hintType
* @throws NotSupportedException
* @return string
*/
public static function mapHintTypeIntoDatabaseColumnType($hintType, $length = null)
{
$databaseColumnType = '';
if (RedBeanDatabase::getDatabaseType() == 'mysql') {
if (isset($length) && $length > 0 && $length < 255) {
if ($hintType == 'string') {
$databaseColumnType = "VARCHAR({$length})";
}
} else {
switch ($hintType) {
case 'blob':
$databaseColumnType = "BLOB";
break;
case 'longblob':
$databaseColumnType = "LONGBLOB";
break;
case 'boolean':
$databaseColumnType = "TINYINT(1)";
break;
case 'date':
$databaseColumnType = "DATE";
break;
case 'datetime':
$databaseColumnType = "DATETIME";
break;
case 'string':
$databaseColumnType = "VARCHAR(255)";
break;
case 'text':
$databaseColumnType = "TEXT";
break;
case 'longtext':
$databaseColumnType = "LONGTEXT";
break;
case 'id':
$databaseColumnType = "INT(11) UNSIGNED";
break;
}
}
} else {
throw new NotSupportedException();
}
if ($databaseColumnType == '') {
throw new NotSupportedException();
}
return $databaseColumnType;
}
