src/snac/server/database/SQL.php
/**
     * Select for Language object.
     *
     * This is not language controlled vocabulary. That is in the vocabulary table. This table links vocab id
     * (language, script) to another table. Language objects are denormalized views of link and vocab tables.
     *
     * Note: This always gets the most recent <= $version for a given subquery id.
     *
     * Select fields for a language object knowing a fkID value of the related table. This relies on the
     * language.fk_id==orig_table.id. $fkID is a foreign key of the record to which this language
     * applies. This (mostly) does not know or care what the other record is. Note that for the
     * "foreign-key-across-all-tables" to work, all the tables must use the same sequence (that is: id_seq).
     *
     * 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 $fkID A foreign key to record in another 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.
     *
     * @return string[] A list of location fields as list with keys matching the database field names.
     */
Select for Language object.
This is not language controlled vocabulary. That is in the vocabulary table. This table links vocab id
(language, script) to another table. Language objects are denormalized views of link and vocab tables.

This always gets the most recent <= $version for a given subquery id.

Select fields for a language object knowing a fkID value of the related table.

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 $fkID A foreign key to record in another 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.

@return string[] A list of location fields as list with keys matching the database field names.
Select for Language object . This is not language controlled vocabulary . That is in the vocabulary table . This table links vocab id ( language script ) to another table . Language objects are denormalized views of link and vocab tables . This always gets the most recent < = $version for a given subquery id . Select fields for a language object knowing a fkID value of the related table . 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 $fkID A foreign key to record in another 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 . @return string [] A list of location fields as list with keys matching the database field names .
public function selectLanguage($fkID, $version, $fkTable)
    {
        $qq = 'select_language';
        $query = 'select aa.version, aa.ic_id, aa.id, aa.language_id, aa.script_id, aa.vocabulary_source, aa.note
        from language as aa,
        (select id,max(version) as version from language 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';
        $this->sdb->prepare($qq, $query);
        $result = $this->sdb->execute($qq, array($fkID, $version, $fkTable));
        $all = array();
        while($row = $this->sdb->fetchrow($result))
        {
            array_push($all, $row);
        }
        $this->sdb->deallocate($qq);
        return $all;
    }
public function selectLanguage ( $ fkID , $ version , $ fkTable ) { $ qq = 'select_language' ; $ query = 'select aa.version, aa.ic_id, aa.id, aa.language_id, aa.script_id, aa.vocabulary_source, aa.note
        from language as aa,
        (select id,max(version) as version from language 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' ; $ this -> sdb -> prepare ( $ qq , $ query ) ; $ result = $ this -> sdb -> execute ( $ qq , array ( $ fkID , $ version , $ fkTable ) ) ; $ all = array ( ) ; while ( $ row = $ this -> sdb -> fetchrow ( $ result ) ) { array_push ( $ all , $ row ) ; } $ this -> sdb -> deallocate ( $ qq ) ; return $ all ; }
Select for Language object.
Select for Language object .