public function process()
{
$items = DataChangeRecord::get()->filter('Created:LessThan', $this->pruneBefore);
$max = $items->max('ID');
$query = new SQLDelete('DataChangeRecord', '"ID" < \'' . $max . '\'');
$query->execute();
$job = new PruneChangesBeforeJob($this->priorTo);
$next = date('Y-m-d 03:00:00', strtotime('tomorrow'));
$this->currentStep = $this->totalSteps;
$this->isComplete = true;
singleton(QueuedJobService)->queueJob($job, $next);
}
/**
* Delete this data object.
* $this->onBeforeDelete() gets called.
* Note that in Versioned objects, both Stage and Live will be deleted.
* @uses DataExtension->augmentSQL()
*/
public function delete()
{
$this->brokenOnDelete = true;
$this->onBeforeDelete();
if ($this->brokenOnDelete) {
user_error("{$this->class} has a broken onBeforeDelete() function." . " Make sure that you call parent::onBeforeDelete().", E_USER_ERROR);
}
// Deleting a record without an ID shouldn't do anything
if (!$this->ID) {
throw new LogicException("DataObject::delete() called on a DataObject without an ID");
}
// TODO: This is quite ugly. To improve:
// - move the details of the delete code in the DataQuery system
// - update the code to just delete the base table, and rely on cascading deletes in the DB to do the rest
// obviously, that means getting requireTable() to configure cascading deletes ;-)
$srcQuery = DataList::create($this->class, $this->model)->where("ID = {$this->ID}")->dataQuery()->query();
foreach ($srcQuery->queriedTables() as $table) {
$delete = new SQLDelete("\"{$table}\"", array('"ID"' => $this->ID));
$delete->execute();
}
// Remove this item out of any caches
$this->flushCache();
$this->onAfterDelete();
$this->OldID = $this->ID;
$this->ID = 0;
}
/**
* Return the DELETE clause ready for inserting into a query.
*
* @param SQLExpression $query The expression object to build from
* @param array $parameters Out parameter for the resulting query parameters
* @return string Completed delete part of statement
*/
public function buildDeleteFragment(SQLDelete $query, array &$parameters)
{
$text = 'DELETE';
// If doing a multiple table delete then list the target deletion tables here
// Note that some schemas don't support multiple table deletion
$delete = $query->getDelete();
if (!empty($delete)) {
$text .= ' ' . implode(', ', $delete);
}
return $text;
}
/**
* Remove all items from this many-many join. To remove a subset of items,
* filter it first.
*
* @return void
*/
public function removeAll()
{
$base = ClassInfo::baseDataClass($this->dataClass());
// Remove the join to the join table to avoid MySQL row locking issues.
$query = $this->dataQuery();
$foreignFilter = $query->getQueryParam('Foreign.Filter');
$query->removeFilterOn($foreignFilter);
$selectQuery = $query->query();
$selectQuery->setSelect("\"{$base}\".\"ID\"");
$from = $selectQuery->getFrom();
unset($from[$this->joinTable]);
$selectQuery->setFrom($from);
$selectQuery->setOrderBy();
// ORDER BY in subselects breaks MS SQL Server and is not necessary here
$selectQuery->setDistinct(false);
// Use a sub-query as SQLite does not support setting delete targets in
// joined queries.
$delete = new SQLDelete();
$delete->setFrom("\"{$this->joinTable}\"");
$delete->addWhere($this->foreignIDFilter());
$subSelect = $selectQuery->sql($parameters);
$delete->addWhere(array("\"{$this->joinTable}\".\"{$this->localKey}\" IN ({$subSelect})" => $parameters));
$delete->execute();
}
/**
* Create a database table to replay the site tree creation, based on the chronological order of the site tree version table.
*/
protected function setupStructure()
{
if (!DB::get_conn() instanceof MySQLDatabase) {
exit('This task currently only supports <strong>MySQL</strong>...');
}
$replaceArray = self::$db_columns;
unset($replaceArray['FullURL']);
$this->replaceColumnString = implode(',', array_keys($replaceArray));
$tableList = DB::table_list();
if (self::$use_temporary_table || !in_array(self::$default_table, $tableList)) {
$options = self::$use_temporary_table ? array('temporary' => true) : null;
$this->replayTable = DB::create_table(self::$default_table, self::$db_columns, null, $options);
} else {
// Delete all records from the table.
$query = new SQLDelete(self::$default_table);
$query->execute();
}
}
/**
* Remove all fixtures previously defined through {@link createObject()}
* or {@link createRaw()}, both from the internal fixture mapping and the database.
* If the $class argument is set, limit clearing to items of this class.
*
* @param String $class
*/
public function clear($limitToClass = null)
{
$classes = $limitToClass ? array($limitToClass) : array_keys($this->fixtures);
foreach ($classes as $class) {
$ids = $this->fixtures[$class];
foreach ($ids as $id => $dbId) {
if (class_exists($class)) {
$class::get()->byId($dbId)->delete();
} else {
$table = $class;
$delete = new SQLDelete("\"{$table}\"", array("\"{$table}\".\"ID\"" => $dbId));
$delete->execute();
}
unset($this->fixtures[$class][$id]);
}
}
}
/**
* Copies all values from one table to another. Will override any existing values with matching ID's.
*
* @param string $fromTable Name of SOURCE table to copy values from.
* @param string $toTable Name of DESTINATION table to copy values to.
* @param array|null $fieldMapping Array of fields to copy (and ONLY these fields). Can also specify key => value
* pairs to map between old/new names (instead of just values). Note: Leave
* empty (or pass null) to automatically assume ALL fields from source table (including ID).
* @param bool $purgeDest Ensures all data in the DESTINATION table matches the source.
* @param mixed|null $where An optional filter passed directly to ->setWhere() method on SQLSelect.
* @throws MigrationException
*/
public static function copyTable($fromTable, $toTable, array $fieldMapping = null, $purgeDest = false, $where = null)
{
if (!static::tableExists($fromTable)) {
throw new MigrationException("Table '{$fromTable}' does not exist.");
}
if (!static::tableExists($toTable)) {
throw new MigrationException("Table '{$fromTable}' does not exist.");
}
// Initialize defaults.
if ($fieldMapping === null) {
$fieldMapping = array();
}
// Normalize to empty.
if ($fieldMapping === array()) {
// If empty: Use all fields from the source.
$fieldMapping = array_keys(static::getTableColumns($fromTable));
}
// Since an ID is required to prevent duplication of data, add it now if it's not already setup.
// TODO: Should this be optional?
if (!in_array('ID', $fieldMapping)) {
$fieldMapping[] = 'ID';
}
// Separate out the source/destination fields from the field mapping to help with selection and validation (correspondingly).
$sourceFields = array_map(function ($key, $value) {
if (!is_numeric($key)) {
return $key;
}
return $value;
}, array_keys($fieldMapping), array_values($fieldMapping));
$destFields = array_values($fieldMapping);
// Validate columns in the destination first and ensure they exist first before moving forward, since you
// don't want to perform a DELETE on an entire table unless you're sure the entire operation will complete.
$destActualFields = array_keys(self::getTableColumns($toTable));
$destFieldDiff = array_diff($destFields, $destActualFields);
if (count($destFieldDiff) !== 0) {
throw new MigrationException("The field(s) '" . join(', ', $destFieldDiff) . "' do not exist in the destination table '{$toTable}'.");
}
// Purge now, if specified.
if ($purgeDest) {
$delete = new SQLDelete($toTable);
$delete->execute();
}
// Begin fetching rows and copying them over now.
$select = new SQLSelect($sourceFields, $fromTable);
if ($where !== null) {
$select->setWhere($where);
}
$result = $select->execute();
while ($sourceRow = $result->next()) {
// Convert row fields based on our mapping.
$destRow = array();
foreach ($sourceRow as $field => $value) {
if (array_key_exists($field, $fieldMapping)) {
$field = $fieldMapping[$field];
}
$destRow[$field] = $value;
}
// Update table.
static::setRowValuesOnTable($toTable, $destRow, null, true);
}
}
public function submit()
{
$this->getMap();
$sqldelete = new SQLDelete($this->table);
$sqlinsert = new SQLInsert($this->table);
foreach ($this->dimensions[0] as $row) {
$rowid = $row["ID"];
foreach ($this->dimensions[1] as $col) {
$colid = $col["ID"];
$inputname = 'mapchecked_' . $rowid . '_' . $colid;
$checked = filter_input(INPUT_POST, $inputname);
if ($checked) {
$row = array();
$row[$this->idfields[1]] = $rowid;
$row[$this->idfields[0]] = $colid;
$sqlinsert->values[] = $row;
}
}
}
$sqldelete->execute();
$sqlinsert->execute();
echo $sqldelete->query;
echo "<br>" . $sqlinsert->query;
}
public function deleteTableRow($idfields)
{
if (empty($this->values)) {
error_out("Can't delete. Couldn't find any values.");
return false;
}
$idvals = $this->findRow($idfields);
if ($idvals) {
// Found row -- update it, leaving the idvals selectors alone
$sqld = new SQLDelete($this->table, $this->values, $idvals);
$sqld->international = $this->international;
$sqld->execute();
success_out("Deleted record");
}
}
function cps4wp_query($sql)
{
global $wpdb;
// echo $sql;
$logto = 'queries';
// The end of the query may be protected against changes
$end = '';
// Remove unusefull spaces
$initial = $sql = trim($sql);
if (0 === strpos($sql, 'SELECT')) {
// clutserpoint doesnot support @ in queries
if (false !== strpos($sql, "@")) {
return false;
}
$logto = 'SELECT';
$s = new SQLSelect($sql);
return $GLOBALS['cps4wp_result'] = $s->toCps();
} elseif (0 === strpos($sql, 'UPDATE')) {
$logto = 'UPDATE';
$s = new SQLUpdate($sql);
return $GLOBALS['cps4wp_result'] = $s->toCps();
} elseif (0 === strpos($sql, 'INSERT')) {
$logto = 'INSERT';
$s = new SQLInsert($sql);
return $GLOBALS['cps4wp_result'] = $s->toCps();
} elseif (0 === strpos($sql, 'DELETE')) {
$logto = 'DELETE';
$s = new SQLDelete($sql);
return $GLOBALS['cps4wp_result'] = $s->toCps();
} elseif (0 === strpos($sql, 'SHOW TABLES')) {
$logto = 'SHOWTABLES';
$o = new SQLAbstract();
return $o->execute('SELECT DISTINCT __table FROM ' . DB_NAME);
} elseif (0 === strpos($sql, 'OPTIMIZE TABLE')) {
$logto = 'OPTIMIZE';
$sql = str_replace('OPTIMIZE TABLE', 'VACUUM', $sql);
} elseif (0 === strpos($sql, 'SET NAMES') && false !== strpos($sql, 'COLLATE')) {
$logto = 'SETNAMES';
$sql = "SET NAMES 'utf8'";
$sql = false;
//cps don't need this now
}
// Load up upgrade and install functions as required
$begin = substr($sql, 0, 3);
$search = array('SHO', 'ALT', 'DES', 'CRE', 'DRO');
if (in_array($begin, $search)) {
require_once CPS4WP_ROOT . '/driver_pgsql_install.php';
$sql = cps4wp_installing($sql, $logto);
}
// WP 2.9.1 uses a comparison where text data is not quoted
$pattern = '/AND meta_value = (-?\\d+)/';
$sql = preg_replace($pattern, 'AND meta_value = \'$1\'', $sql);
// Generic "INTERVAL xx YEAR|MONTH|DAY|HOUR|MINUTE|SECOND" handler
$pattern = '/INTERVAL[ ]+(\\d+)[ ]+(YEAR|MONTH|DAY|HOUR|MINUTE|SECOND)/';
$sql = preg_replace($pattern, "'\$1 \$2'::interval", $sql);
$pattern = '/DATE_SUB[ ]*\\(([^,]+),([^\\)]+)\\)/';
$sql = preg_replace($pattern, '($1::timestamp - $2)', $sql);
// Remove illegal characters
$sql = str_replace('`', '', $sql);
// Field names with CAPITALS need special handling
if (false !== strpos($sql, 'ID')) {
$pattern = '/ID([^ ])/';
$sql = preg_replace($pattern, 'ID $1', $sql);
$pattern = '/ID$/';
$sql = preg_replace($pattern, 'ID ', $sql);
$pattern = '/\\(ID/';
$sql = preg_replace($pattern, '( ID', $sql);
$pattern = '/,ID/';
$sql = preg_replace($pattern, ', ID', $sql);
$pattern = '/[0-9a-zA-Z_]+ID/';
$sql = preg_replace($pattern, '"$0"', $sql);
$pattern = '/\\.ID/';
$sql = preg_replace($pattern, '."ID"', $sql);
$pattern = '/[\\s]ID /';
$sql = preg_replace($pattern, ' "ID" ', $sql);
$pattern = '/"ID "/';
$sql = preg_replace($pattern, ' "ID" ', $sql);
}
// CAPITALS
// Empty "IN" statements are erroneous
$sql = str_replace('IN (\'\')', 'IN (NULL)', $sql);
$sql = str_replace('IN ( \'\' )', 'IN (NULL)', $sql);
$sql = str_replace('IN ()', 'IN (NULL)', $sql);
// Put back the end of the query if it was separated
$sql .= $end;
// For insert ID catching
if ($logto == 'INSERT') {
$pattern = '/INSERT INTO (\\w+)\\s+\\([ a-zA-Z_"]+/';
preg_match($pattern, $sql, $matches);
$GLOBALS['cps4wp_ins_table'] = $matches[1];
$match_list = split(' ', $matches[0]);
if ($GLOBALS['cps4wp_ins_table']) {
$GLOBALS['cps4wp_ins_field'] = trim($match_list[3], ' () ');
if (!$GLOBALS['cps4wp_ins_field']) {
$GLOBALS['cps4wp_ins_field'] = trim($match_list[4], ' () ');
}
}
$GLOBALS['cps4wp_last_insert'] = $sql;
} elseif (isset($GLOBALS['cps4wp_queued_query'])) {
pg_query($GLOBALS['cps4wp_queued_query']);
unset($GLOBALS['cps4wp_queued_query']);
}
// Correct quoting for PostgreSQL 9.1+ compatibility
$sql = str_replace("\\'", "''", $sql);
$sql = str_replace('\\"', '"', $sql);
if (CPS4WP_DEBUG) {
if ($initial != $sql) {
error_log('[' . microtime(true) . "] Converting :\n{$initial}\n---- to ----\n{$sql}\n---------------------\n", 3, CPS4WP_LOG . 'cps4wp_' . $logto . '.log');
} else {
error_log('[' . microtime(true) . "] {$sql}\n---------------------\n", 3, CPS4WP_LOG . 'cps4wp_unmodified.log');
}
}
return $sql;
}
