src/snac/server/database/SQL.php
/**
     * Core function for delete
     *
     * Do the real work for set/clear is_deleted. The only difference between setting and clearing is the
     * value put into is_deleted, so it makes sense to have one function doing both.
     *
     * @param string $table A valid table name, created from internal data only, since there is a risk here of
     * SQL injection attack.
     *
     * @param integer $id The id (table.id) of the record we are deleting. This field is not unique, so we
     * must constrain by id,version as is conventional practice.
     *
     * @param integer $newVersion The max version of the record to delete. We delete the record with the matching
     * table.id and the version <= to $newVersion as is conventional practice.
     *
     * @param string $operation Either 'set', or 'clear'. Set changes is_delete to 't'. Clear changes is_deleted to 'f'.
     *
     */
Core function for delete
Do the real work for set/clear is_deleted. The only difference between setting and clearing is the
value put into is_deleted, so it makes sense to have one function doing both.

@param string $table A valid table name, created from internal data only, since there is a risk here of
SQL injection attack.

@param integer $id The id (table.id) of the record we are deleting. This field is not unique, so we
must constrain by id,version as is conventional practice.

@param integer $newVersion The max version of the record to delete. We delete the record with the matching
table.id and the version <= to $newVersion as is conventional practice.

@param string $operation Either 'set', or 'clear'. Set changes is_delete to 't'. Clear changes is_deleted to 'f'.
Core function for delete Do the real work for set / clear is_deleted . The only difference between setting and clearing is the value put into is_deleted so it makes sense to have one function doing both . @param string $table A valid table name created from internal data only since there is a risk here of SQL injection attack . @param integer $id The id ( table . id ) of the record we are deleting . This field is not unique so we must constrain by id version as is conventional practice . @param integer $newVersion The max version of the record to delete . We delete the record with the matching table . id and the version < = to $newVersion as is conventional practice . @param string $operation Either ' set ' or ' clear ' . Set changes is_delete to ' t ' . Clear changes is_deleted to ' f ' .
public function sqlCoreDeleted($table, $id, $newVersion, $operation)
    {
        $selectSQL =
                   "select aa.* from $table as aa,
                   (select id, max(version) as version from $table where version<=$1 and id=$2 group by id) as bb
                   where aa.version=bb.version and aa.id=bb.id";
        $result = $this->sdb->query($selectSQL, array($newVersion, $id));
        $xx = 0;
        $row = $this->sdb->fetchrow($result);
        if ($secondRow = $this->sdb->fetchrow($result))
        {
            printf("Error: sqlSetDeleted() selects multiple rows: %s for table: $table id: $id newVersion: $newVersion \n",
                   count($row));
            return;
        }
        if (count($row) == 0)
        {
            printf("Error: sqlSetDeleted() fails to select a row for table: $table id: $id newVersion: $newVersion\n");
            return;
        }
        $row['is_deleted'] = 'f';
        if ($operation == 'set')
        {
            $row['is_deleted'] = 't';
        }
        $row['version'] = $newVersion;
        $columnString = '';
        $placeHolderString = '';
        $xx = 1;     
        $tween = ''; 
        foreach ($row as $key => $value)
        {
            $columnString .="$tween$key";
            $placeHolderString .= "$tween\$$xx";
            $xx++;
            $tween = ", ";
        }
        $updateSQL = "insert into $table ($columnString) values ($placeHolderString) returning id";
        $newResult = $this->sdb->query($updateSQL, array_values($row));
    }
public function sqlCoreDeleted ( $ table , $ id , $ newVersion , $ operation ) { $ selectSQL = " select aa.* from  $ table  as aa,
                   (select id, max(version) as version from  $ table  where version<=$1 and id=$2 group by id) as bb
                   where aa.version=bb.version and aa.id=bb.id " ; $ result = $ this -> sdb -> query ( $ selectSQL , array ( $ newVersion , $ id ) ) ; $ xx = 0 ; $ row = $ this -> sdb -> fetchrow ( $ result ) ; if ( $ secondRow = $ this -> sdb -> fetchrow ( $ result ) ) { printf ( " Error: sqlSetDeleted() selects multiple rows: %s for table:  $ table  id:  $ id  newVersion:  $ newVersion \n " , count ( $ row ) ) ; return ; } if ( count ( $ row ) == 0 ) { printf ( " Error: sqlSetDeleted() fails to select a row for table:  $ table  id:  $ id  newVersion:  $ newVersion \n " ) ; return ; } $ row [ 'is_deleted' ] = 'f' ; if ( $ operation == 'set' ) { $ row [ 'is_deleted' ] = 't' ; } $ row [ 'version' ] = $ newVersion ; $ columnString = '' ; $ placeHolderString = '' ; $ xx = 1 ; $ tween = '' ; foreach ( $ row as $ key => $ value ) { $ columnString .= " $ tween $ key " ; $ placeHolderString .= " $ tween \$ $ xx " ; $ xx ++ ; $ tween = " ,  " ; } $ updateSQL = " insert into  $ table  ( $ columnString ) values ( $ placeHolderString ) returning id " ; $ newResult = $ this -> sdb -> query ( $ updateSQL , array_values ( $ row ) ) ; }
Core function for delete
 Do the real work for set/clear is_deleted.
Core function for delete Do the real work for set / clear is_deleted .
// printf("\ntable: $table id: $id newVersion: $newVersion\n"); /* This is a crude way to test for multiple rows.
             *
             * This happened when some inserts during testing went wrong. Might be something to test for,
             * and/or add a primary key constraint. There can be only one ic_id for a given id.
             */ /*
             * This should never happen. Calling code has already checked for one of these records, and
             * wouldn't be calling us if there wasn't something to operate on. Still, when called with wrong
             * arguments (an upstream bug), this has happened.
             */ // Default to clearing, that is un-delete. /*
         * Dynamically build an insert statement "column string" and matching "place holder string". We could
         * assume the order or columns and keys would be invariant, as is defined in SQL and php. However, it
         * is not too outlandish to think that something will break one of those two foundational language
         * definitions.
         *
         * Use the "tween idiom" to handle the comma separators. By prefixing the tween, we don't need to
         * clean trailing tween/separator from the strings after the loop. The tween string starts empty, and
         * is set at the end of the loop. The tween is always set at the end of the loop, which is a trifle
         * brute force, but se assume that setting the tween is less CPU than checking to see if the tween is
         * empty. In other words, there is no point in optimizing the loop below.
         */ // Counting numbers start at 1, and place holders start with $1 (indexes start at zero) // Tweens always start empty. // printf("del SQL: $updateSQL array: %s\n", var_export($row, 1));