src/snac/server/database/SQL.php
/**
     * Select list of place_link
     *
     * Select a place. This relies on table.id==fk_id where $tid is a foreign key of the record to which this
     * place applies. We do not know or care what the other record is, and that works because all the ids come
     * from a single SQL sequence and therefore are unique.
     *
     * Constrain sub query where fk_id, but group by id and return max(version) by id. Remember, our unique
     * key is always id,version. Joining the fk_id constrained subquery with the table on id and version gives
     * us all of the relevant id,version records, and nothing else.
     *
     * An old bug grouped the subquery in fk_id, and then joined on fk_id, which was wrong. It had the effect
     * of only returning record(s) for the overall max version, so the bug was only apparent when there were
     * at least 2 versions in group of records.
     *
     * @param integer $tid A foreign key to record in the other table.
     *
     * @param integer $version The constellation version. For edits this is max version of the
     * constellation. For published, this is the published constellation version.
     *
     * @param string $fkTable Name of the related table, matches $tid aka fk_id aka fkID.
     *
     * @return string[] A list of fields/value as list keys matching the database field names: id,
     * version, ic_id, confirmed, geo_place_id, fk_table, fk_id, from_type, to_type
     */
Select list of place_link
Select a place. This relies on table.id==fk_id where $tid is a foreign key of the record to which this
place applies. We do not know or care what the other record is, and that works because all the ids come
from a single SQL sequence and therefore are unique.

Constrain sub query where fk_id, but group by id and return max(version) by id. Remember, our unique
key is always id,version. Joining the fk_id constrained subquery with the table on id and version gives
us all of the relevant id,version records, and nothing else.

An old bug grouped the subquery in fk_id, and then joined on fk_id, which was wrong. It had the effect
of only returning record(s) for the overall max version, so the bug was only apparent when there were
at least 2 versions in group of records.

@param integer $tid A foreign key to record in the other table.

@param integer $version The constellation version. For edits this is max version of the
constellation. For published, this is the published constellation version.

@param string $fkTable Name of the related table, matches $tid aka fk_id aka fkID.

@return string[] A list of fields/value as list keys matching the database field names: id,
version, ic_id, confirmed, geo_place_id, fk_table, fk_id, from_type, to_type
Select list of place_link Select a place . This relies on table . id == fk_id where $tid is a foreign key of the record to which this place applies . We do not know or care what the other record is and that works because all the ids come from a single SQL sequence and therefore are unique . Constrain sub query where fk_id but group by id and return max ( version ) by id . Remember our unique key is always id version . Joining the fk_id constrained subquery with the table on id and version gives us all of the relevant id version records and nothing else . An old bug grouped the subquery in fk_id and then joined on fk_id which was wrong . It had the effect of only returning record ( s ) for the overall max version so the bug was only apparent when there were at least 2 versions in group of records . @param integer $tid A foreign key to record in the other table . @param integer $version The constellation version . For edits this is max version of the constellation . For published this is the published constellation version . @param string $fkTable Name of the related table matches $tid aka fk_id aka fkID . @return string [] A list of fields / value as list keys matching the database field names : id version ic_id confirmed geo_place_id fk_table fk_id from_type to_type
public function selectPlace($tid, $version, $fkTable)
    {
        $qq = 'select_place';
        $this->sdb->prepare($qq,
                         'select
                         aa.id, aa.version, aa.ic_id, aa.confirmed, aa.original,
                         aa.geo_place_id, aa.type, aa.role, aa.note, aa.score, aa.fk_table, aa.fk_id
                         from place_link as aa,
                         (select id,max(version) as version from place_link where fk_id=$1 and fk_table=$3 and version<=$2 group by id) as bb
                         where not is_deleted and aa.id=bb.id and aa.version=bb.version');
        $result = $this->sdb->execute($qq, array($tid, $version, $fkTable));
        $all = array();
        while($row = $this->sdb->fetchrow($result))
        {
            array_push($all, $row);
        }
        $this->sdb->deallocate($qq);
        return $all;
    }
public function selectPlace ( $ tid , $ version , $ fkTable ) { $ qq = 'select_place' ; $ this -> sdb -> prepare ( $ qq , 'select
                         aa.id, aa.version, aa.ic_id, aa.confirmed, aa.original,
                         aa.geo_place_id, aa.type, aa.role, aa.note, aa.score, aa.fk_table, aa.fk_id
                         from place_link as aa,
                         (select id,max(version) as version from place_link where fk_id=$1 and fk_table=$3 and version<=$2 group by id) as bb
                         where not is_deleted and aa.id=bb.id and aa.version=bb.version' ) ; $ result = $ this -> sdb -> execute ( $ qq , array ( $ tid , $ version , $ fkTable ) ) ; $ all = array ( ) ; while ( $ row = $ this -> sdb -> fetchrow ( $ result ) ) { array_push ( $ all , $ row ) ; } $ this -> sdb -> deallocate ( $ qq ) ; return $ all ; }
Select list of place_link
 Select a place.
Select list of place_link Select a place .