src/snac/server/database/SQL.php
/**
     * Select by status and most recent, user only
     *
     * Get a list of mainID, version for all records when the most recent matches the given status and user.
     * This will only return most recent records, and only if the status and user match. A note about
     * checking for deleted: All status values are exclusive, so if a record's most recent status is 'locked
     * editing' then it cannot not possibly also be deleted. If you ask for 'locked editing' and that is most
     * recent, and for this user, then you will get that record.
     *
     * This will not return records which are not most recent. After that, any record must match status and user.
     *
     * @param integer $appUserID User numeric ID. The most recent record must match this user id, and must
     * match $status.
     *
     * @param string $status optional The status we want, defaults to 'locked editing'. Any status is
     * supported, but records are returned only if the most recent is this status, and matches $appUserID.
     *
     * @param integer $limit Limit to the number of records. Not optional here. Must be -1 for all, or a
     * number. The higher level calling code has a default from the config.
     *
     * @param integer $offset An offset to jump into the list of records in the database. Not optional
     * here. Must be -1 for all, or a number. The higher level calling code has a default from the config.
     *
     * @param boolean $secondary Whether to search the secondary user field rather than the primary user field.
     *
     * @return string[] Associative list with keys 'version', 'ic_id'. Values are integers.
     */
Select by status and most recent, user only
Get a list of mainID, version for all records when the most recent matches the given status and user.
This will only return most recent records, and only if the status and user match. A note about
checking for deleted: All status values are exclusive, so if a record's most recent status is 'locked
editing' then it cannot not possibly also be deleted. If you ask for 'locked editing' and that is most
recent, and for this user, then you will get that record.

This will not return records which are not most recent. After that, any record must match status and user.

@param integer $appUserID User numeric ID. The most recent record must match this user id, and must
match $status.

@param string $status optional The status we want, defaults to 'locked editing'. Any status is
supported, but records are returned only if the most recent is this status, and matches $appUserID.

@param integer $limit Limit to the number of records. Not optional here. Must be -1 for all, or a
number. The higher level calling code has a default from the config.

@param integer $offset An offset to jump into the list of records in the database. Not optional
here. Must be -1 for all, or a number. The higher level calling code has a default from the config.

@param boolean $secondary Whether to search the secondary user field rather than the primary user field.

@return string[] Associative list with keys 'version', 'ic_id'. Values are integers.
Select by status and most recent user only Get a list of mainID version for all records when the most recent matches the given status and user . This will only return most recent records and only if the status and user match . A note about checking for deleted : All status values are exclusive so if a record ' s most recent status is ' locked editing ' then it cannot not possibly also be deleted . If you ask for ' locked editing ' and that is most recent and for this user then you will get that record . This will not return records which are not most recent . After that any record must match status and user . @param integer $appUserID User numeric ID . The most recent record must match this user id and must match $status . @param string $status optional The status we want defaults to ' locked editing ' . Any status is supported but records are returned only if the most recent is this status and matches $appUserID . @param integer $limit Limit to the number of records . Not optional here . Must be - 1 for all or a number . The higher level calling code has a default from the config . @param integer $offset An offset to jump into the list of records in the database . Not optional here . Must be - 1 for all or a number . The higher level calling code has a default from the config . @param boolean $secondary Whether to search the secondary user field rather than the primary user field . @return string [] Associative list with keys ' version ' ' ic_id ' . Values are integers .
public function selectEditList($appUserID, $status = 'locked editing', $limit, $offset, $secondary)
    {
        $limitStr = '';
        $offsetStr = '';
        $limitStr = $this->doLOValue('limit', $limit);
        $offsetStr = $this->doLOValue('offset', $offset);
        $queryString = sprintf(
                'select aa.version, aa.id as ic_id
                    from version_history as aa,
                        (select max(version) as version, id from version_history
                            where id in (select distinct id from version_history where user_id=$1)
                            group by id) as cc
                    where
                        aa.id=cc.id and
                        aa.version=cc.version and
                        aa.user_id=$1 and
                        aa.status = $2 %s %s', $limitStr, $offsetStr);
        if ($secondary) {
            $queryString = sprintf(
                    'select aa.version, aa.id as ic_id
                        from version_history as aa,
                            (select max(version) as version, id from version_history
                                where id in (select distinct id from version_history where user_id_secondary=$1)
                                group by id) as cc
                        where
                            aa.id=cc.id and
                            aa.version=cc.version and
                            aa.user_id_secondary=$1 and
                            aa.status = $2 %s %s', $limitStr, $offsetStr);
        }
        $this->logger->addDebug("Sending the following SQL request: " . $queryString);
        $result = $this->sdb->query($queryString,
                                    array($appUserID, $status));
        $this->logger->addDebug("Done request");
        $all = array();
        while($row = $this->sdb->fetchrow($result))
        {
            $mainID = $row['ic_id'];
            $version = $row['version'];
            $maxVersion = $this->selectCurrentVersion($mainID);
            if ($maxVersion == $version)
            {
                array_push($all, $row);
            }
        }
        return $all;
    }
public function selectEditList ( $ appUserID , $ status = 'locked editing' , $ limit , $ offset , $ secondary ) { $ limitStr = '' ; $ offsetStr = '' ; $ limitStr = $ this -> doLOValue ( 'limit' , $ limit ) ; $ offsetStr = $ this -> doLOValue ( 'offset' , $ offset ) ; $ queryString = sprintf ( 'select aa.version, aa.id as ic_id
                    from version_history as aa,
                        (select max(version) as version, id from version_history
                            where id in (select distinct id from version_history where user_id=$1)
                            group by id) as cc
                    where
                        aa.id=cc.id and
                        aa.version=cc.version and
                        aa.user_id=$1 and
                        aa.status = $2 %s %s' , $ limitStr , $ offsetStr ) ; if ( $ secondary ) { $ queryString = sprintf ( 'select aa.version, aa.id as ic_id
                        from version_history as aa,
                            (select max(version) as version, id from version_history
                                where id in (select distinct id from version_history where user_id_secondary=$1)
                                group by id) as cc
                        where
                            aa.id=cc.id and
                            aa.version=cc.version and
                            aa.user_id_secondary=$1 and
                            aa.status = $2 %s %s' , $ limitStr , $ offsetStr ) ; } $ this -> logger -> addDebug ( " Sending the following SQL request:  " . $ queryString ) ; $ result = $ this -> sdb -> query ( $ queryString , array ( $ appUserID , $ status ) ) ; $ this -> logger -> addDebug ( " Done request " ) ; $ all = array ( ) ; while ( $ row = $ this -> sdb -> fetchrow ( $ result ) ) { $ mainID = $ row [ 'ic_id' ] ; $ version = $ row [ 'version' ] ; $ maxVersion = $ this -> selectCurrentVersion ( $ mainID ) ; if ( $ maxVersion == $ version ) { array_push ( $ all , $ row ) ; } } return $ all ; }
Select by status and most recent, user only
 Get a list of mainID, version for all records when the most recent matches the given status and user.
Select by status and most recent user only Get a list of mainID version for all records when the most recent matches the given status and user .
/*
             * I think the query above works, returning the max() only when that version also has the required
             * status. However, the check below will confirm that the returned version really is the max.
             */