public function run($request)
{
$db = new DBP_Database();
$artefacts = $db->Artefacts();
if (empty($artefacts)) {
echo '<h3>Schema is clean, nothin to drop.</h3>';
} else {
echo '<h3>Dropping:</h3>';
echo '<ul>';
foreach ($artefacts as $table => $drop) {
if (is_array($drop)) {
DBP_SQLDialect::get()->dropColumns($table, $drop);
echo "<li>column " . implode("</li><li>column ", $drop) . "</li>";
} else {
echo "<li>table {$table}</li>";
DBP_SQLDialect::get()->dropTable($table);
}
}
echo '</ul>';
}
}
function testSplitScript()
{
$script = "SELECT * FROM \"SiteTree\"";
$commands = DBP_Sql::split_script($script);
$this->assertType('array', $commands, 'Result is array');
$script = " \n SELECT * FROM \"SiteTree\"\r\n ; ";
$commands = DBP_Sql::split_script($script);
$this->assertEquals(1, count($commands), 'Return only one command');
$this->assertEquals(trim($script), $commands[0], 'Return correct command');
$script = "SELECT * FROM \"SiteTree\"\r\n WHERE \"ID\" > 15;\nUPDATE \"File\" SET \"Name\" = 'somename' WHERE \"ID\" = 79; insert into ErrorPage (ID) values ('79')";
$commands = DBP_Sql::split_script($script);
$this->assertEquals(3, count($commands), 'Return all 3 commands');
$this->assertEquals("insert into ErrorPage (ID) values ('79');", $commands[2], 'Split commands corrctly');
$script = "SELECT * FROM \"SiteTree\"\r\n WHERE \"Content\" LIKE '%;%'; INSERT INTO \"SiteTree\" (\"Content\") values ('\nUPDATE \"File\" SET \"Name\" = 'somename' WHERE \"ID\" = 79;\n')";
$commands = DBP_Sql::split_script($script);
$this->assertEquals(2, count($commands), 'Return only 2 commands');
$this->assertEquals("SELECT", substr($commands[0], 0, 6), 'Don\'t split on ; when inside quotes');
$this->assertEquals("INSERT", substr($commands[1], 0, 6), 'Don\'t split if command is inside quotes');
$specialcharacters = DBP_SQLDialect::get()->escape("\\';");
$script = "SELECT * FROM \"SiteTree\" WHERE \"Content\" LIKE 'Some text {$specialcharacters} and some more'";
$commands = DBP_Sql::split_script($script);
$this->assertEquals(1, count($commands), 'Return only 1 commands');
}
function backup($tables, $dialect)
{
global $databaseConfig;
$commands = array('/*', ' SQL Dump of ' . get_class(DB::getConn()) . ' ' . DB::getConn()->currentDatabase() . (DB::getConn() instanceof Sqlite3Database ? ' in ' . $databaseConfig['path'] : ' on ' . $databaseConfig['server']), " SQL Dialect {$dialect}", ' Created on ' . date('r'), ' Created with Database Plumber for Silverstripe', " =============================================", " DISCLAIMER: NO WARRANTY, USE AT YOUR OWN RISC", " =============================================", '*/', '');
if ($dialect == 'MySQL') {
$commands[] = "SET sql_mode = 'ANSI';";
}
foreach ($tables as $table) {
$fields = array();
if ($dialect == 'MSSQL' && ($idcol = DB::getConn()->getIdentityColumn($table))) {
$commands[] = "SET IDENTITY_INSERT \"{$table}\" ON;";
}
$commands[] = 'DELETE FROM "' . $table . '";';
foreach (DB::fieldList($table) as $name => $spec) {
$fields[] = $name;
}
foreach (DB::query('SELECT * FROM "' . $table . '"') as $record) {
$cells = array();
foreach ($record as $cell) {
if (is_null($cell)) {
$cell = 'NULL';
} else {
if (is_string($cell)) {
$cell = "'" . DBP_SQLDialect::get($dialect)->escape($cell) . "'";
}
}
$cells[] = $cell;
}
$commands[] = "INSERT INTO \"{$table}\" (\"" . implode('", "', $fields) . "\") VALUES (" . implode(", ", $cells) . ");";
}
if ($dialect == 'MSSQL' && $idcol) {
$commands[] = "SET IDENTITY_INSERT \"{$table}\" OFF;";
}
}
return $commands;
}
