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;
 }
Пример #4
0
 /**
  * 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;
 }
Пример #11
0
 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;
}
Пример #13
0
 $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.";
     }
Пример #14
0
 $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);