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