/**
* 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);
}
}
}
/**
* 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;
}
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();
}
}
}
}
/**
* 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();
}
}
$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.";
}
}
$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);
if ($translation != "") {
$update_lang = new SQLUpdate('translation_text');
