public function testMultipleRowInsert()
{
$query = SQLInsert::create('"SQLInsertTestBase"');
$query->addRow(array('"Title"' => 'First Object', '"Age"' => 10, '"Description"' => 'First the worst'));
$query->addRow(array('"Title"' => 'Second object', '"Age"' => 12));
$sql = $query->sql($parameters);
// Only test this case if using the default query builder
if (get_class(DB::get_conn()->getQueryBuilder()) === 'DBQueryBuilder') {
$this->assertSQLEquals('INSERT INTO "SQLInsertTestBase" ("Title", "Age", "Description") VALUES (?, ?, ?), (?, ?, ?)', $sql);
}
$this->assertEquals(array('First Object', 10, 'First the worst', 'Second object', 12, null), $parameters);
$query->execute();
$this->assertEquals(2, DB::affected_rows());
// Check inserted objects are correct
$firstObject = DataObject::get_one('SQLInsertTestBase', array('"Title"' => 'First Object'), false);
$this->assertNotEmpty($firstObject);
$this->assertEquals($firstObject->Title, 'First Object');
$this->assertEquals($firstObject->Age, 10);
$this->assertEquals($firstObject->Description, 'First the worst');
$secondObject = DataObject::get_one('SQLInsertTestBase', array('"Title"' => 'Second object'), false);
$this->assertNotEmpty($secondObject);
$this->assertEquals($secondObject->Title, 'Second object');
$this->assertEquals($secondObject->Age, 12);
$this->assertEmpty($secondObject->Description);
}
public function build_query()
{
$setstring = "";
// If insertIfAbsent is set, check for the row
if ($this->insertIfAbsent) {
$sel = new SQLSelect($this->table, $this->schema);
$sel->selectfields[] = "1";
$sel->wherearray = $this->selectors;
$val = $sel->execute();
if ($val == NULL || empty($val) || $val[1] == array()) {
$ins = new SQLInsert($this->table, $this->values);
$q = $ins->build_query();
$this->query = $q;
return $this->query;
}
}
// Perform the UPDATE
foreach ($this->values as $key => $value) {
// Skip selectors
if (isset($this->selectors[$key])) {
continue;
}
// Build 'SET' string
if ($setstring != "") {
$setstring .= ",";
}
$setstring .= $key . "=" . dbize($value, $this->get_column_datatype($key));
}
if ($setstring != "") {
$setstring = " SET " . $setstring;
}
// Add selectors to WHERE clause
foreach ($this->selectors as $key => $value) {
$this->wherefields[] = "{$key} = " . dbize($value, $this->get_column_datatype($key));
}
$wherestring = $this->wherefields_to_string();
if ($setstring != "" && $wherestring != "") {
$query = "UPDATE " . $this->table_with_schema() . $setstring . $wherestring;
$this->query = $query;
}
return $this->query;
}
/**
* @param SQLInsert $query
* @param array $parameters
* @return string
*/
protected function buildInsertQuery(SQLInsert $query, array &$parameters)
{
// Multi-row insert requires SQLite specific syntax prior to 3.7.11
// For backwards compatibility reasons include the "union all select" syntax
$nl = $this->getSeparator();
$into = $query->getInto();
// Column identifiers
$columns = $query->getColumns();
// Build all rows
$rowParts = array();
foreach ($query->getRows() as $row) {
// Build all columns in this row
$assignments = $row->getAssignments();
// Join SET components together, considering parameters
$parts = array();
foreach ($columns as $column) {
// Check if this column has a value for this row
if (isset($assignments[$column])) {
// Assigment is a single item array, expand with a loop here
foreach ($assignments[$column] as $assignmentSQL => $assignmentParameters) {
$parts[] = $assignmentSQL;
$parameters = array_merge($parameters, $assignmentParameters);
break;
}
} else {
// This row is missing a value for a column used by another row
$parts[] = '?';
$parameters[] = null;
}
}
$rowParts[] = implode(', ', $parts);
}
$columnSQL = implode(', ', $columns);
$sql = "INSERT INTO {$into}{$nl}({$columnSQL}){$nl}SELECT " . implode("{$nl}UNION ALL SELECT ", $rowParts);
return $sql;
}
/**
* Execute a complex manipulation on the database.
* A manipulation is an array of insert / or update sequences. The keys of the array are table names,
* and the values are map containing 'command' and 'fields'. Command should be 'insert' or 'update',
* and fields should be a map of field names to field values, NOT including quotes.
*
* The field values could also be in paramaterised format, such as
* array('MAX(?,?)' => array(42, 69)), allowing the use of raw SQL values such as
* array('NOW()' => array()).
*
* @see SQLWriteExpression::addAssignments for syntax examples
*
* @param array $manipulation
*/
public function manipulate($manipulation)
{
if (empty($manipulation)) {
return;
}
foreach ($manipulation as $table => $writeInfo) {
if (empty($writeInfo['fields'])) {
continue;
}
// Note: keys of $fieldValues are not escaped
$fieldValues = $writeInfo['fields'];
// Switch command type
switch ($writeInfo['command']) {
case "update":
// Build update
$query = new SQLUpdate("\"{$table}\"", $this->escapeColumnKeys($fieldValues));
// Set best condition to use
if (!empty($writeInfo['where'])) {
$query->addWhere($writeInfo['where']);
} elseif (!empty($writeInfo['id'])) {
$query->addWhere(array('"ID"' => $writeInfo['id']));
}
// Test to see if this update query shouldn't, in fact, be an insert
if ($query->toSelect()->count()) {
$query->execute();
break;
}
// ...if not, we'll skip on to the insert code
// ...if not, we'll skip on to the insert code
case "insert":
// Ensure that the ID clause is given if possible
if (!isset($fieldValues['ID']) && isset($writeInfo['id'])) {
$fieldValues['ID'] = $writeInfo['id'];
}
// Build insert
$query = new SQLInsert("\"{$table}\"", $this->escapeColumnKeys($fieldValues));
$query->execute();
break;
default:
user_error("SS_Database::manipulate() Can't recognise command '{$writeInfo['command']}'", E_USER_ERROR);
}
}
}
public function reset_table($rows, $selectors = array(), $primary_key = "")
{
$current_cols = $this->get_columns();
// The table doesn't exist: make it
if (!$current_cols || empty($current_cols)) {
$datatypes = $this->get_column_datatypes();
$cr = new SQLCreate($this->table, $datatypes, $primary_key);
$cr->debug = $this->debug;
$cr->execute();
}
if (empty($selectors)) {
// No selectors: Always insert the rows
foreach ($rows as $rownum => $row) {
$ins = new SQLInsert($this->table, $row);
$ins->debug = $this->debug;
$ins->execute();
}
} else {
// Selectors exist: update the given rows
foreach ($rows as $rownum => $row) {
$row_selectors = array();
foreach ($selectors as $selkey) {
if (isset($row[$selkey])) {
$row_selectors[$selkey] = $row[$selkey];
}
}
if (!empty($row_selectors)) {
$upd = new SQLUpdate($this->table, $row, $row_selectors, true);
$upd->debug = $this->debug;
$upd->execute();
} else {
$ins = new SQLInsert($this->table, $row);
$ins->debug = $this->debug;
$ins->execute();
}
}
}
}
/**
* Ensures that a blank base record exists with the basic fixed fields for this dataobject
*
* Does nothing if an ID is already assigned for this record
*
* @param string $baseTable Base table
* @param string $now Timestamp to use for the current time
*/
protected function writeBaseRecord($baseTable, $now)
{
// Generate new ID if not specified
if ($this->isInDB()) {
return;
}
// Perform an insert on the base table
$insert = new SQLInsert('"' . $baseTable . '"');
$insert->assign('"Created"', $now)->execute();
$this->changed['ID'] = self::CHANGE_VALUE;
$this->record['ID'] = DB::get_generated_id($baseTable);
}
/**
* Builds a query from a SQLInsert expression
*
* @param SQLInsert $query The expression object to build from
* @param array $parameters Out parameter for the resulting query parameters
* @return string Completed SQL string
*/
protected function buildInsertQuery(SQLInsert $query, array &$parameters)
{
$nl = $this->getSeparator();
$into = $query->getInto();
// Column identifiers
$columns = $query->getColumns();
$sql = "INSERT INTO {$into}{$nl}(" . implode(', ', $columns) . ")";
// Values
$sql .= "{$nl}VALUES";
// Build all rows
$rowParts = array();
foreach ($query->getRows() as $row) {
// Build all columns in this row
$assignments = $row->getAssignments();
// Join SET components together, considering parameters
$parts = array();
foreach ($columns as $column) {
// Check if this column has a value for this row
if (isset($assignments[$column])) {
// Assigment is a single item array, expand with a loop here
foreach ($assignments[$column] as $assignmentSQL => $assignmentParameters) {
$parts[] = $assignmentSQL;
$parameters = array_merge($parameters, $assignmentParameters);
break;
}
} else {
// This row is missing a value for a column used by another row
$parts[] = '?';
$parameters[] = null;
}
}
$rowParts[] = '(' . implode(', ', $parts) . ')';
}
$sql .= $nl . implode(",{$nl}", $rowParts);
return $sql;
}
/**
* Writes the fixture into the database directly using a database manipulation.
* Does not use blueprints. Only supports tables with a primary key.
*
* @param String $table Existing database table name
* @param String $identifier Unique identifier for this fixture type
* @param Array $data Map of properties
* @return Int Database identifier
*/
public function createRaw($table, $identifier, $data)
{
$fields = array();
foreach ($data as $fieldName => $fieldVal) {
$fields["\"{$fieldName}\""] = $this->parseValue($fieldVal);
}
$insert = new SQLInsert("\"{$table}\"", $fields);
$insert->execute();
$id = DB::get_generated_id($table);
$this->fixtures[$table][$identifier] = $id;
return $id;
}
/**
* Sets the values for multiple rows on a database table by the ID column.
* Useful when fields have been removed from the class' `$db` property,
* and therefore are no longer accessible through the ORM.
* Returns false if the table or any of the rows do not exist.
* Returns true if the SQL query was executed.
*
* @param string $table
* @param array $values Ex: array('FieldName' => value)
* @param int|null $id Note: Null only works here if $insert = true.
* @param bool $insert Allows insertion of a new record if the ID provided is null or doesn't exist.
* NOTE: If an "ID" field is passed, that ID value will be retained.
* @return boolean Will return true if anything was changed, false otherwise.
*/
public static function setRowValuesOnTable($table, array $values, $id = null, $insert = false)
{
// TODO: This should maybe throw an exception instead.
if (!self::tableColumnsExist($table, array_keys($values))) {
return false;
}
// Assume it exists unless we're actually going to allow inserting. Then we'll really check for sure.
$exists = true;
if ($insert) {
// Ensure the ID we're checking now is the same as the one we're inserting to help prevent issues with duplicate keys.
$checkID = $id;
if (isset($values['ID'])) {
$checkID = $values['ID'];
}
$select = new SQLSelect('COUNT(*)', $table, array('ID' => $checkID));
$result = $select->execute();
$exists = (bool) (int) $result->value();
}
// Pull out an ID (if applicable).
if ($id === null && array_key_exists('ID', $values)) {
$id = $values['ID'];
}
if ($exists) {
// Generate an execute an UPDATE query.
$update = new SQLUpdate($table, $values, array('ID' => $id));
$update->execute();
return true;
} elseif ($insert) {
// Generate an INSERT query instead.
$insert = new SQLInsert($table, $values);
$insert->execute();
return true;
}
// Nothing was done.
return false;
}
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 addTableRow()
{
if ($this->table == "") {
error_out("No table set");
return false;
}
// Get SQL colums
$columns = $this->getTableColumns();
if (!$columns) {
error_out("Can't save. Couldn't find table columns.");
return false;
}
$this->tablecolumns = $columns;
// Save the data to the columns
if (empty($this->values)) {
error_out("Can't save. Couldn't find any values.");
return false;
} else {
$sqli = new SQLInsert($this->table, $this->values);
$sqli->international = $this->international;
// $sqli->build_query();
// echo $sqli->query;
$sqli->execute();
}
}
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;
}
$unvalue = $username->value;
$unmatches = db_get("SELECT 1 FROM login_user WHERE upper(username) = upper('{$unvalue}')", 'column');
if (!empty($unmatches[1])) {
// Check if email is taken
$form->errors[] = "Username already taken.";
$form->valid = false;
}
if ($form->valid == true) {
// Hash the password
$hash = db_hash_password($_REQUEST['password1']);
if ($hash) {
// Insert the values
$firstname = $_REQUEST["firstname"];
$lastname = $_REQUEST["lastname"];
$insertfields = array("username" => $unvalue, "pass" => $hash, "email" => $emailvalue, "firstname" => $firstname, "lastname" => $lastname, "pending" => 1);
$ins = new SQLInsert("login_user", $insertfields);
$ins->execute();
//db_execute("INSERT INTO login_user (username, pass, email, firstname, lastname) VALUES('$unvalue', '$hash', '$emailvalue','".$_REQUEST["firstname"]."','".$_REQUEST["lastname"]."')");
//die("Created user $unvalue");
$subject = "Access Request for {$unvalue}";
$url = "http://" . $_SERVER["SERVER_NAME"] . "/shared/login/approveordeny.php?username="******"{$firstname} {$lastname} has requested access to the " . $system_name . ". <br><br><a href='" . $url . "'>Approve or deny</a> {$url}";
$headers = "From: " . $admin_email . "\r\n";
$headers .= "Reply-To: " . $admin_email . "\r\n";
$headers .= "MIME-Version: 1.0\r\n";
$headers .= "Content-Type: text/html; charset=ISO-8859-1\r\n";
mail($admin_email, $subject, $body, $headers);
die("Thank you for requesting access to the " . $system_name . ". An email has been sent to the administrator to approve your request.");
} else {
$form->errors[] = "Error saving password. Please retry.";
}
$values = array();
foreach (array('text_name', 'text_description') as $field) {
$value = filter_input(INPUT_POST, $field);
if ($value) {
$values[$field] = $value;
}
}
if ($element_id) {
// Update the metadata
$update_ste = new SQLUpdate('survey_text_element');
$update_ste->values = $values;
$update_ste->selectors['id'] = $element_id;
$update_ste->execute();
} else {
// Update the metadata
$add_ste = new SQLInsert('survey_text_element');
$add_ste->values = $values;
$add_ste->execute();
$selectidq = new SQLSelect('survey_text_element');
$selectidq->selectfields['id'] = 'id';
$selectidq->wherearray = $values;
$res = $selectidq->execute();
$element_id = $res["ID"][0];
}
if ($element_id) {
// Update the languages
$posslanguages = $records->getPossLanguages();
foreach ($posslanguages as $language) {
$translation = filter_input(INPUT_POST, $language['KEYCODE']);
if ($translation) {
$translation = trim($translation);
