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; }