/**
* @return MySQLiAdapter|PDOAdapter
* @throws Exception
*/
private function getAdapter()
{
// SS version >= 3.2
if (class_exists('MySQLiConnector') && class_exists('PDOConnector')) {
$connector = DB::get_connector();
if ($connector instanceof MySQLiConnector) {
$connProperty = new ReflectionProperty('MySQLiConnector', 'dbConn');
$connProperty->setAccessible(true);
$conn = $connProperty->getValue($connector);
return new MySQLiAdapter($conn);
} else {
if ($connector instanceof PDOConnector) {
$connProperty = new ReflectionProperty('PDOConnector', 'pdoConnection');
$connProperty->setAccessible(true);
$conn = $connProperty->getValue($connector);
return new PDOAdapter($conn);
}
}
} else {
$db = DB::getConn();
if ($db instanceof MySQLDatabase) {
$connProperty = new ReflectionProperty('MySQLDatabase', 'dbConn');
$connProperty->setAccessible(true);
$conn = $connProperty->getValue($db);
return new MySQLiAdapter($conn);
}
}
throw new Exception('connection cannot be found');
}
public function testFilter()
{
if (DB::getConn() instanceof MySQLDatabase) {
$baseQuery = FulltextFilterTest_DataObject::get();
$this->assertEquals(3, $baseQuery->count(), "FulltextFilterTest_DataObject count does not match.");
// First we'll text the 'SearchFields' which has been set using an array
$search = $baseQuery->filter("SearchFields:fulltext", 'SilverStripe');
$this->assertEquals(1, $search->count());
$search = $baseQuery->exclude("SearchFields:fulltext", "SilverStripe");
$this->assertEquals(2, $search->count());
// Now we'll run the same tests on 'OtherSearchFields' which should yield the same resutls
// but has been set using a string.
$search = $baseQuery->filter("OtherSearchFields:fulltext", 'SilverStripe');
$this->assertEquals(1, $search->count());
$search = $baseQuery->exclude("OtherSearchFields:fulltext", "SilverStripe");
$this->assertEquals(2, $search->count());
// Search on a single field
$search = $baseQuery->filter("ColumnE:fulltext", 'Dragons');
$this->assertEquals(1, $search->count());
$search = $baseQuery->exclude("ColumnE:fulltext", "Dragons");
$this->assertEquals(2, $search->count());
} else {
$this->markTestSkipped("FulltextFilter only supports MySQL syntax.");
}
}
public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
{
// Actives locales defined on a SiteConfig are there as a global setting
if ($this->owner instanceof SiteConfig) {
return;
}
// In admin, show everthing anyway
if ($this->isAdminBackend()) {
return;
}
// Find in set is only compatible with MySql
$c = DB::getConn();
if (!$c instanceof MySQLDatabase) {
return;
}
$locale = $dataQuery->getQueryParam('Fluent.Locale') ?: Fluent::current_locale();
$from = $query->getFrom();
$where = $query->getWhere();
$column = 'ActiveLocales';
$table = null;
// Check on which table is the ActiveLocales field
foreach ($from as $fromTable => $conditions) {
if ($table === null) {
$table = $fromTable;
}
$db = DataObject::custom_database_fields($fromTable);
if ($db && isset($db[$column])) {
$table = $fromTable;
break;
}
}
$identifier = "\"{$table}\".\"{$column}\"";
$where[] = "{$identifier} IS NULL OR FIND_IN_SET ('{$locale}', {$identifier}) > 0";
$query->setWhere($where);
}
function delete($country)
{
$conn = DB::getConn();
$stmt = $conn->prepare("DELETE FROM country WHERE id = :id");
$stmt->bindParam("id", $country->id);
$stmt->execute();
}
function waitUntilIndexingFinished()
{
$db = DB::getConn();
if (method_exists($db, 'waitUntilIndexingFinished')) {
DB::getConn()->waitUntilIndexingFinished();
}
}
public function getRandomQuestion()
{
$qid = Session::get('QACaptchaField.Retry');
if (isset($qid) && $qid) {
// Retry the same question
return DataObject::get_by_id('QACaptchaQuestion', $qid);
}
// Provide empty answer field - the question has not been answered yet
$this->setValue('');
// Get a comma separated list of past questions
$backlog = Session::get('QACaptchaField.Backlog');
if (!$backlog) {
$backlog = array();
}
$sqlBacklog = implode($backlog, ', ');
// Get questions that have not been used before
$random = DataObject::get('QACaptchaQuestion', $sqlBacklog ? "\"QACaptchaQuestion\".\"ID\" NOT IN ({$sqlBacklog})" : '', DB::getConn()->random());
if (!($random && $random->exists())) {
// We have ran out of questions - reset the list
$backlog = array();
Session::clear('QACaptchaField.Backlog');
$random = DataObject::get('QACaptchaQuestion', '', DB::getConn()->random());
}
if ($random && $random->exists()) {
$q = $random->First();
// Add the question to backlog
$backlog[] = $q->ID;
Session::set('QACaptchaField.Backlog', $backlog);
return $q;
}
}
function Dates()
{
Requirements::themedCSS('archivewidget');
$results = new DataObjectSet();
$container = BlogTree::current();
$ids = $container->BlogHolderIDs();
$stage = Versioned::current_stage();
$suffix = !$stage || $stage == 'Stage' ? "" : "_{$stage}";
$monthclause = method_exists(DB::getConn(), 'formattedDatetimeClause') ? DB::getConn()->formattedDatetimeClause('"Date"', '%m') : 'MONTH("Date")';
$yearclause = method_exists(DB::getConn(), 'formattedDatetimeClause') ? DB::getConn()->formattedDatetimeClause('"Date"', '%Y') : 'YEAR("Date")';
$sqlResults = DB::query("\n\t\t\tSELECT DISTINCT CAST({$monthclause} AS " . DB::getConn()->dbDataType('unsigned integer') . ") AS \"Month\", {$yearclause} AS \"Year\"\n\t\t\tFROM \"SiteTree{$suffix}\" INNER JOIN \"BlogEntry{$suffix}\" ON \"SiteTree{$suffix}\".\"ID\" = \"BlogEntry{$suffix}\".\"ID\"\n\t\t\tWHERE \"ParentID\" IN (" . implode(', ', $ids) . ")\n\t\t\tORDER BY \"Year\" DESC, \"Month\" DESC;");
if ($this->ShowLastYears == 0) {
$cutOffYear = 0;
} else {
$cutOffYear = (int) date("Y") - $this->ShowLastYears;
}
$years = array();
if (Director::get_current_page()->ClassName == 'BlogHolder') {
$urlParams = Director::urlParams();
$yearParam = $urlParams['ID'];
$monthParam = $urlParams['OtherID'];
} else {
$date = new DateTime(Director::get_current_page()->Date);
$yearParam = $date->format("Y");
$monthParam = $date->format("m");
}
if ($sqlResults) {
foreach ($sqlResults as $sqlResult) {
$isMonthDisplay = true;
$year = $sqlResult['Year'] ? (int) $sqlResult['Year'] : date('Y');
$isMonthDisplay = $year > $cutOffYear;
// $dateFormat = 'Month'; else $dateFormat = 'Year';
$monthVal = isset($sqlResult['Month']) ? (int) $sqlResult['Month'] : 1;
$month = $isMonthDisplay ? $monthVal : 1;
$date = DBField::create('Date', array('Day' => 1, 'Month' => $month, 'Year' => $year));
if ($isMonthDisplay) {
$link = $container->Link('date') . '/' . $sqlResult['Year'] . '/' . sprintf("%'02d", $monthVal);
} else {
$link = $container->Link('date') . '/' . $sqlResult['Year'];
}
if ($isMonthDisplay || !$isMonthDisplay && !in_array($year, $years)) {
$years[] = $year;
$current = false;
$children = new DataObjectSet();
$LinkingMode = "link";
if ($isMonthDisplay && $yearParam == $year && $monthParam == $month || !$isMonthDisplay && $yearParam == $year) {
$LinkingMode = "current";
$current = true;
if ($this->ShowChildren && $isMonthDisplay) {
$filter = $yearclause . ' = ' . $year . ' AND ' . $monthclause . ' = ' . $month;
$children = DataObject::get('BlogEntry', $filter, "Date DESC");
}
}
$results->push(new ArrayData(array('Date' => $date, 'Year' => $year, 'Link' => $link, 'NoMonth' => !$isMonthDisplay, 'LinkingMode' => $LinkingMode, 'Children' => $children)));
unset($children);
}
}
}
return $results;
}
public function run($request)
{
// transfer values for changed column name
foreach (array('SiteTree', 'SiteTree_Live', 'SiteTree_versions') as $table) {
DB::query("UPDATE \"{$table}\" SET \"CanViewType\" = 'Viewers';");
DB::query("UPDATE \"{$table}\" SET \"CanEditType\" = 'Editors';");
}
//Debug::message('Moved SiteTree->Viewers to SiteTree->CanViewType');
//Debug::message('Moved SiteTree->Editors to SiteTree->CanEditType');
// convert has_many to many_many
$pageIDs = DB::query("SELECT ID FROM SiteTree")->column('ID');
foreach ($pageIDs as $pageID) {
$page = DataObject::get_by_id('SiteTree', $pageID);
if ($page->ViewersGroup && DataObject::get_by_id("Group", $page->ViewersGroup)) {
$page->ViewerGroups()->add($page->ViewersGroup);
}
if ($page->EditorsGroup && DataObject::get_by_id("Group", $page->EditorsGroup)) {
$page->EditorGroups()->add($page->EditorsGroup);
}
$page->destroy();
unset($page);
}
//Debug::message('SiteTree->ViewersGroup to SiteTree->ViewerGroups (has_one to many_many)');
//Debug::message('SiteTree->EditorsGroup to SiteTree->EditorGroups (has_one to many_many)');
// rename legacy columns
foreach (array('SiteTree', 'SiteTree_Live', 'SiteTree_versions') as $table) {
foreach (array('Viewers', 'Editors', 'ViewersGroup', 'EditorsGroup') as $field) {
DB::getConn()->dontRequireField($table, $field);
}
}
}
public function run($request)
{
//check if OrderCouponModifier exists
if (DB::getConn()->hasTable("OrderCouponModifier")) {
$this->log("OrderCouponModifier table exists");
$query = DB::query("SELECT *\n\t\t\t\tFROM OrderModifier\n\t\t\t\tINNER JOIN `OrderAttribute` ON `OrderModifier`.`ID` = `OrderAttribute`.`ID`\n\t\t\t\tINNER JOIN `OrderCouponModifier` ON `OrderModifier`.`ID` = `OrderCouponModifier`.`ID`\n\t\t\t\tINNER JOIN `Order` ON `OrderAttribute`.`OrderID` = `Order`.`ID`;");
foreach ($query as $row) {
$newdata = $row;
$newdata["ClassName"] = "OrderDiscountModifier";
$newdata["ID"] = 0;
if (!OrderDiscountModifier::get()->filter("OrderID", $newdata["OrderID"])->exists()) {
$newmodifier = new OrderDiscountModifier($newdata);
$newmodifier->write();
$discount = Discount::get()->byID($newdata["CouponID"]);
//set up discount values
if ($discount && $discount->exists()) {
$newmodifier->Discounts()->add($discount, array("DiscountAmount" => $newmodifier->Amount));
}
$this->log("Migrated modifier for Order " . $newdata["OrderID"] . " " . $newmodifier->ID);
}
}
} else {
$this->log("OrderCouponModifier table does not exist");
}
}
function performance($request)
{
$markers = array();
$q = substr(GoogleAnalyzer::get_sapphire_version(), 0, 3) == '2.3' ? '`' : '"';
$metrics = $request->requestVar('metrics');
$filters = null;
$eventfiltersql = "{$q}GoogleLogEvent{$q}.{$q}PageID{$q} = 0";
$page = SiteTree::get()->byID((int) $request->param('ID'));
if ($page) {
$url = trim($page->Link(), '/');
if (!empty($url)) {
$url .= '/';
}
$filters = 'ga:pagePath==/' . $url;
$eventfiltersql .= " OR {$q}GoogleLogEvent{$q}.{$q}PageID{$q} = " . (int) $page->ID;
$allversions = $q == '"' ? '"WasPublished" = 1 AND ' . DB::getConn()->datetimeDifferenceClause('"LastEdited"', date('Y-m-d 23:59:59', strtotime('-1 Year'))) . ' > 0' : "{$q}WasPublished{$q} = 1 AND {$q}LastEdited{$q} > '" . date('Y-m-d 23:59:59', strtotime('-1 Year')) . "'";
foreach ($page->allVersions() as $version) {
$markers[] = array(strtotime($version->LastEdited) * 1000, 'Updated', 'Long descr.');
}
}
$events = DataObject::get('GoogleLogEvent', $eventfiltersql);
if ($events) {
foreach ($events as $event) {
$markers[] = array(strtotime($event->Created) * 1000, $event->Title, 'Long descr.');
}
}
$store = new GoogleDataStore(GoogleConfig::get_google_config('profile'), GoogleConfig::get_google_config('email'), GoogleConfig::get_google_config('password'));
$data = $store->fetchPerformance(array('dimensions' => 'ga:date', 'metrics' => 'ga:visits,ga:pageviews', 'sort' => '-ga:date', 'filters' => $filters));
return json_encode(array('series' => $data, 'markers' => $markers));
}
function run($request)
{
$db = DB::getConn();
if ($db instanceof PostgreSQLDatabase) {
$exist = DB::query("SELECT column_name FROM information_schema.columns WHERE table_name ='PickUpOrDeliveryModifier' AND column_name = 'PickupOrDeliveryType'")->numRecords();
} else {
// default is MySQL - broken for others, each database conn type supported must be checked for!
$exist = DB::query("SHOW COLUMNS FROM \"PickUpOrDeliveryModifier\" LIKE 'PickupOrDeliveryType'")->numRecords();
}
if ($exist > 0) {
$defaultOption = PickUpOrDeliveryModifierOptions::get()->filter(array("IsDefault" => 1))->First();
$modifiers = PickUpOrDeliveryModifier::get()->filter(array("OptionID" => 0));
if ($modifiers->count()) {
foreach ($modifiers as $modifier) {
if (!isset($modifier->OptionID) || !$modifier->OptionID) {
if (!isset(self::$options_old_to_new[$modifier->Code])) {
$option = PickUpOrDeliveryModifierOptions::get()->filter(array("Code" => $modifier->Code))->First();
if (!$option) {
$option = $defaultOption;
}
self::$options_old_to_new[$modifier->Code] = $option->ID;
}
$myOption = self::$options_old_to_new[$modifier->Code];
// USING QUERY TO UPDATE
DB::query("UPDATE \"PickUpOrDeliveryModifier\" SET \"OptionID\" = " . $myOption . " WHERE \"PickUpOrDeliveryModifier\".\"ID\" = " . $modifier->ID);
DB::alteration_message('Updated modifier #' . $modifier->ID . ' from code to option ID ' . $myOption, 'edited');
}
}
}
}
DB::alteration_message("<hr />COMPLETED<hr />", "created");
}
function delete($state)
{
$conn = DB::getConn();
$stmt = $conn->prepare("DELETE FROM state WHERE id = :id");
$stmt->bindParam("id", $state->id);
$stmt->execute();
}
function delete($picture_commentary)
{
$conn = DB::getConn();
$stmt = $conn->prepare("DELETE FROM picture_commentary WHERE id = :id");
$stmt->bindParam("id", $picture_commentary->id);
$stmt->execute();
}
function testReadOnlyTransaction()
{
if (DB::getConn()->supportsTransactions() == true) {
$page = new Page();
$page->Title = 'Read only success';
$page->write();
DB::getConn()->startTransaction('READ ONLY');
try {
$page = new Page();
$page->Title = 'Read only page failed';
$page->write();
} catch (Exception $e) {
//could not write this record
//We need to do a rollback or a commit otherwise we'll get error messages
DB::getConn()->transactionRollback();
}
DB::getConn()->endTransaction();
$success = DataObject::get('Page', "\"Title\"='Read only success'");
$fail = DataObject::get('Page', "\"Title\"='Read only failed'");
//This page should be in the system
$this->assertTrue(is_object($success) && $success->exists());
//This page should NOT exist, we had 'read only' permissions
$this->assertFalse(is_object($fail) && $fail->exists());
}
}
function testReadOnlyTransaction()
{
if (DB::getConn()->supportsTransactions() == true && DB::getConn() instanceof PostgreSQLDatabase) {
$page = new Page();
$page->Title = 'Read only success';
$page->write();
DB::getConn()->transactionStart('READ ONLY');
try {
$page = new Page();
$page->Title = 'Read only page failed';
$page->write();
} catch (Exception $e) {
//could not write this record
//We need to do a rollback or a commit otherwise we'll get error messages
DB::getConn()->transactionRollback();
}
DB::getConn()->transactionEnd();
DataObject::flush_and_destroy_cache();
$success = DataObject::get('Page', "\"Title\"='Read only success'");
$fail = DataObject::get('Page', "\"Title\"='Read only page failed'");
//This page should be in the system
$this->assertTrue(is_object($success) && $success->exists());
//This page should NOT exist, we had 'read only' permissions
$this->assertFalse(is_object($fail) && $fail->exists());
} else {
$this->markTestSkipped('Current database is not PostgreSQL');
}
}
function save()
{
$sets = $keys = $vals = array();
if ($this->id) {
foreach ($this->data as $key => $val) {
$val = str_replace("'", "''", $val);
$sets[$key] = "\"{$key}\" = '{$val}'";
}
$query = 'UPDATE "' . $this->table . '" SET ' . implode(', ', $sets) . ' WHERE "ID" = \'' . $this->id . '\'';
} else {
foreach ($this->data as $key => $val) {
if ($key == 'ID' && empty($val)) {
continue;
}
$val = str_replace("'", "''", $val);
$keys[] = "\"{$key}\"";
$vals[] = "'{$val}'";
}
$query = 'INSERT INTO "' . $this->table . '" (' . implode(', ', $keys) . ') VALUES (' . implode(', ', $vals) . ')';
}
DB::query($query);
if ($this->id) {
$this->id = $this->data['ID'] ? $this->data['ID'] : $this->id;
} else {
$this->id = DB::getConn()->getGeneratedID($this->table);
}
}
public static function checkConn()
{
if (!self::$BDL) {
return DB::getConn();
}
return self::$BDL;
}
function check($favorite)
{
$conn = DB::getConn();
$stmt = DB::getConn()->query("SELECT `id`,`id_user`,`id_place` FROM favorite WHERE id_user = '******' and id_place = '{$favorite->id_place}'");
//$stmt->bindParam("id",$favorite->id);
return $stmt->fetchAll(PDO::FETCH_OBJ);
}
private function checkAndConnectDB()
{
global $databaseConfig;
if (!DB::getConn() && $databaseConfig) {
DB::connect($databaseConfig);
}
}
/** Delete the flighthistory for a specific VID, this removes all datapoints for the flight */
public static function deleteFlightHistory($CONN, $VID)
{
$prepare = parent::getConn()->prepare("DELETE FROM `flightHistory` WHERE `CONN` = :CONN `VID` = :VID");
$prepare->bindParam(':CONN', $CONN);
$prepare->bindParam(':VID', $VID, PDO::PARAM_INT);
$prepare->execute();
}
public function run($request)
{
$db = DB::getConn();
if (!$db instanceof MySQLDatabase) {
echo '<h3>This task only appies to MySQL databases. This installation is using a ' . get_class($db) . '</h3>';
return;
}
$oldschema = array();
$newschema = array();
$renamed = 0;
$current = DB::getConn()->currentDatabase();
foreach (DB::getConn()->tableList() as $lowercase => $dbtablename) {
$oldschema[] = $dbtablename;
}
DB::getConn()->selectDatabase('tmpdb');
$test = new SapphireTest();
$test->create_temp_db();
foreach (DB::getConn()->tableList() as $lowercase => $dbtablename) {
$newschema[] = $dbtablename;
}
$test->kill_temp_db();
DB::getConn()->selectDatabase($current);
echo "<ul>\n";
foreach ($newschema as $table) {
if (in_array(strtolower($table), $oldschema)) {
echo "<li>renaming {$table}</li>";
$db->renameTable(strtolower($table), $table);
$renamed++;
}
}
echo "</ul>\n";
echo "<p>{$renamed} tables renamed.</p>\n";
}
/**
* This will bind a new class dynamically so we can hook into manipulation
* and capture it. It creates a new PHP file in the temp folder, then
* loads it and sets it as the active DB class.
*/
public static function bind_manipulation_capture()
{
global $databaseConfig;
$current = \DB::getConn();
if (!$current || !$current->currentDatabase() || @$current->isManipulationLoggingCapture) {
return;
}
// If not yet set, or its already captured, just return
$type = get_class($current);
$file = TEMP_FOLDER . "/.cache.CLC.{$type}";
$dbClass = 'AuditLoggerManipulateCapture_' . $type;
if (!is_file($file)) {
file_put_contents($file, "<?php\n\t\t\t\tclass {$dbClass} extends {$type} {\n\t\t\t\t\tpublic \$isManipulationLoggingCapture = true;\n\n\t\t\t\t\tpublic function manipulate(\$manipulation) {\n\t\t\t\t\t\t\\SilverStripe\\Auditor\\AuditHook::handle_manipulation(\$manipulation);\n\t\t\t\t\t\treturn parent::manipulate(\$manipulation);\n\t\t\t\t\t}\n\t\t\t\t}\n\t\t\t");
}
require_once $file;
/** @var SS_Database $captured */
$captured = new $dbClass($databaseConfig);
// Framework 3.2+ ORM needs some dependencies set
if (method_exists($captured, 'setConnector')) {
$captured->setConnector($current->getConnector());
$captured->setQueryBuilder($current->getQueryBuilder());
$captured->setSchemaManager($current->getSchemaManager());
}
// The connection might have had it's name changed (like if we're currently in a test)
$captured->selectDatabase($current->currentDatabase());
\DB::setConn($captured);
}
/**
* The process to automatically construct data object output configurations, executed on project build.
*/
public function requireDefaultRecords()
{
parent::requireDefaultRecords();
// Grab the list of data objects that have been completely removed.
foreach (DB::getConn()->tableList() as $table) {
// Delete existing output configurations for these data objects.
if (!class_exists($table)) {
$existing = DataObjectOutputConfiguration::get_one('DataObjectOutputConfiguration', "IsFor = '" . Convert::raw2sql($table) . "'");
$this->deleteConfiguration($table, $existing);
}
}
// Grab the list of all data object types, along with any inclusions/exclusions defined.
$objects = ClassInfo::subclassesFor('DataObject');
$inclusions = self::$custom_inclusions;
$exclusions = array_unique(array_merge(self::$exclusions, self::$custom_exclusions));
// Check existing output configurations for these data objects.
foreach ($objects as $object) {
$existing = DataObjectOutputConfiguration::get_one('DataObjectOutputConfiguration', "IsFor = '" . Convert::raw2sql($object) . "'");
// Delete existing output configurations for invalid data objects, or for those excluded.
if ($existing && (self::$disabled || get_parent_class($object) !== 'DataObject' || ClassInfo::classImplements($object, 'TestOnly') || count($inclusions) > 0 && !in_array($object, $inclusions) || count($inclusions) === 0 && in_array($object, $exclusions))) {
$this->deleteConfiguration($object, $existing);
} else {
if (!$existing && !self::$disabled && get_parent_class($object) === 'DataObject' && !ClassInfo::classImplements($object, 'TestOnly') && (count($inclusions) > 0 && in_array($object, $inclusions) || count($inclusions) === 0 && !in_array($object, $exclusions))) {
$this->addConfiguration($object);
}
}
}
}
private function artefacts()
{
$oldschema = array();
$newschema = array();
$current = DB::getConn()->currentDatabase();
foreach (DB::getConn()->tableList() as $lowercase => $dbtablename) {
$oldschema[$dbtablename] = DB::getConn()->fieldList($dbtablename);
}
DB::getConn()->selectDatabase('tmpdb');
$test = new SapphireTest();
$test->create_temp_db();
foreach (DB::getConn()->tableList() as $lowercase => $dbtablename) {
$newschema[$lowercase] = DB::getConn()->fieldList($dbtablename);
}
$test->kill_temp_db();
DB::getConn()->selectDatabase($current);
$artefacts = array();
foreach ($oldschema as $table => $fields) {
if (!isset($newschema[strtolower($table)])) {
$artefacts[$table] = $table;
continue;
}
foreach ($fields as $field => $spec) {
if (!isset($newschema[strtolower($table)][$field])) {
$artefacts[$table][$field] = $field;
}
}
}
return $artefacts;
}
public function testCreateWithTransaction()
{
if (DB::getConn()->supportsTransactions() == true) {
DB::getConn()->transactionStart();
$obj = new TransactionTest_Object();
$obj->Title = 'First page';
$obj->write();
$obj = new TransactionTest_Object();
$obj->Title = 'Second page';
$obj->write();
//Create a savepoint here:
DB::getConn()->transactionSavepoint('rollback');
$obj = new TransactionTest_Object();
$obj->Title = 'Third page';
$obj->write();
$obj = new TransactionTest_Object();
$obj->Title = 'Fourth page';
$obj->write();
//Revert to a savepoint:
DB::getConn()->transactionRollback('rollback');
DB::getConn()->transactionEnd();
$first = DataObject::get('TransactionTest_Object', "\"Title\"='First page'");
$second = DataObject::get('TransactionTest_Object', "\"Title\"='Second page'");
$third = DataObject::get('TransactionTest_Object', "\"Title\"='Third page'");
$fourth = DataObject::get('TransactionTest_Object', "\"Title\"='Fourth page'");
//These pages should be in the system
$this->assertTrue(is_object($first) && $first->exists());
$this->assertTrue(is_object($second) && $second->exists());
//These pages should NOT exist, we reverted to a savepoint:
$this->assertFalse(is_object($third) && $third->exists());
$this->assertFalse(is_object($fourth) && $fourth->exists());
} else {
$this->markTestSkipped('Current database does not support transactions');
}
}
function up()
{
echo "Starting Migration Proc ...<BR>";
//check if migration already had ran ...
$migration = DataObject::get_one("Migration", "Name='{$this->title}'");
if (!$migration) {
DB::getConn()->transactionStart();
try {
// migrate records from Live to Stage table
DB::query("DELETE FROM JobPage");
DB::query("\nINSERT INTO JobPage\n\t\t(ID,\n\t\tJobPostedDate,\n\t\tJobCompany,\n\t\tJobMoreInfoLink,\n\t\tJobLocation,\n\t\tFoundationJob,\n\t\tExpirationDate,\n\t\tActive,\n\t\tJobInstructions2Apply,\n\t\tLocationType,\n\t\tTitle,\n\t\tContent)\nSELECT \tJobPage_Live.ID,\n\t\tJobPostedDate,\n\t\tJobCompany,\n\t\tJobMoreInfoLink,\n\t\tJobLocation,\n\t\tFoundationJob,\n\t\tExpirationDate,\n\t\tActive,\n\t\tJobInstructions2Apply,\n\t\tLocationType,\n\t\tTitle,\n\t\tContent\nFROM \tJobPage_Live\nINNER JOIN Page_Live on Page_Live.Id = JobPage_Live.Id\nINNER JOIN SiteTree_Live on SiteTree_Live.Id = JobPage_Live.Id");
DB::query("DROP TABLE JobPage_Live");
DB::query("DROP TABLE JobPage_versions");
// delete orphan records
DB::query("delete from Page where Id in (select Id from SiteTree where ClassName = 'JobPage')");
DB::query("delete from SiteTree where ClassName = 'JobPage'");
DB::query("delete from Page_Live where Id in (select Id from SiteTree_Live where ClassName = 'JobPage')");
DB::query("delete from SiteTree_Live where ClassName = 'JobPage'");
DB::query("delete from Page_versions where Id in (select Id from SiteTree_versions where ClassName = 'JobPage')");
DB::query("delete from SiteTree_versions where ClassName = 'JobPage'");
DB::getConn()->transactionEnd();
} catch (Exception $e) {
DB::getConn()->transactionRollback();
return;
}
$migration = new Migration();
$migration->Name = $this->title;
$migration->Description = $this->description;
$migration->Write();
}
echo "Ending Migration Proc ...<BR>";
}
function Dates()
{
Requirements::themedCSS('archivewidget');
$results = new DataObjectSet();
$container = BlogTree::current();
$ids = $container->BlogHolderIDs();
$stage = Versioned::current_stage();
$suffix = !$stage || $stage == 'Stage' ? "" : "_{$stage}";
$monthclause = method_exists(DB::getConn(), 'formattedDatetimeClause') ? DB::getConn()->formattedDatetimeClause('"Date"', '%m') : 'MONTH("Date")';
$yearclause = method_exists(DB::getConn(), 'formattedDatetimeClause') ? DB::getConn()->formattedDatetimeClause('"Date"', '%Y') : 'YEAR("Date")';
if ($this->DisplayMode == 'month') {
$sqlResults = DB::query("\n\t\t\t\tSELECT DISTINCT CAST({$monthclause} AS " . DB::getConn()->dbDataType('unsigned integer') . ") AS \"Month\", {$yearclause} AS \"Year\"\n\t\t\t\tFROM \"SiteTree{$suffix}\" INNER JOIN \"BlogEntry{$suffix}\" ON \"SiteTree{$suffix}\".\"ID\" = \"BlogEntry{$suffix}\".\"ID\"\n\t\t\t\tWHERE \"ParentID\" IN (" . implode(', ', $ids) . ")\n\t\t\t\tORDER BY \"Year\" DESC, \"Month\" DESC;");
} else {
$sqlResults = DB::query("\n\t\t\t\tSELECT DISTINCT {$yearclause} AS \"Year\" \n\t\t\t\tFROM \"SiteTree{$suffix}\" INNER JOIN \"BlogEntry{$suffix}\" ON \"SiteTree{$suffix}\".\"ID\" = \"BlogEntry{$suffix}\".\"ID\"\n\t\t\t\tWHERE \"ParentID\" IN (" . implode(', ', $ids) . ")\n\t\t\t\tORDER BY \"Year\" DESC");
}
if ($sqlResults) {
foreach ($sqlResults as $sqlResult) {
$isMonthDisplay = $this->DisplayMode == 'month';
$monthVal = isset($sqlResult['Month']) ? (int) $sqlResult['Month'] : 1;
$month = $isMonthDisplay ? $monthVal : 1;
$year = $sqlResult['Year'] ? (int) $sqlResult['Year'] : date('Y');
$date = DBField::create('Date', array('Day' => 1, 'Month' => $month, 'Year' => $year));
if ($isMonthDisplay) {
$link = $container->Link('date') . '/' . $sqlResult['Year'] . '/' . sprintf("%'02d", $monthVal);
} else {
$link = $container->Link('date') . '/' . $sqlResult['Year'];
}
$results->push(new ArrayData(array('Date' => $date, 'Link' => $link)));
}
}
return $results;
}
function create_assoc_table()
{
if (!DB::getConn()->hasTable($this->associations_table_name)) {
$r = $this->connection->query($this->sql['assoc_table']);
}
return true;
}
/**
* Get the DB connection in a SS 3.1 and 3.2+ compatible way
* @param string $name
* @return SS_Database
*/
public static function DBConn($name = 'default')
{
if (method_exists('DB', 'get_conn')) {
return DB::get_conn($name);
}
return DB::getConn($name);
}
function delete($district)
{
$conn = DB::getConn();
$stmt = $conn->prepare("DELETE FROM district WHERE id = :id");
$stmt->bindParam("id", $district->id);
$stmt->execute();
}
