src/snac/server/database/SQL.php
/**
     * Select a snac control meta data record
     *
     * May 6 2016: Reading the code, we will return zero or many records. I'm pretty sure that anything which
     * can have an SCM can have multiple SCM records related to it.
     *
     * The query relies on table.id==fk_id where $tid is a foreign key of the record to which this applies. We
     * do not know or care what the other record is.
     *
     * 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 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 Related table name.
     *
     * @return string[][] A list of lists of fields/value as list keys matching the database field names: id,
     * version, ic_id, citation_id, sub_citation, source_data, rule_id, language_id, note. I don't think
     * calling code has any use for fk_id, so we don't return it.
     */
Select a snac control meta data record
May 6 2016: Reading the code, we will return zero or many records. I'm pretty sure that anything which
can have an SCM can have multiple SCM records related to it.

The query relies on table.id==fk_id where $tid is a foreign key of the record to which this applies. We
do not know or care what the other record is.

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 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 Related table name.

@return string[][] A list of lists of fields/value as list keys matching the database field names: id,
version, ic_id, citation_id, sub_citation, source_data, rule_id, language_id, note. I don't think
calling code has any use for fk_id, so we don't return it.
Select a snac control meta data record May 6 2016 : Reading the code we will return zero or many records . I ' m pretty sure that anything which can have an SCM can have multiple SCM records related to it . The query relies on table . id == fk_id where $tid is a foreign key of the record to which this applies . We do not know or care what the other record is . 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 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 Related table name . @return string [] [] A list of lists of fields / value as list keys matching the database field names : id version ic_id citation_id sub_citation source_data rule_id language_id note . I don ' t think calling code has any use for fk_id so we don ' t return it .
public function selectMeta($tid, $version, $fkTable)
    {
        $qq = 'select_meta';
        $this->sdb->prepare($qq,
                            'select
                            aa.id, aa.version, aa.ic_id, aa.citation_id, aa.sub_citation, aa.source_data,
                            aa.rule_id, aa.note
                            from scm as aa,
                            (select id,max(version) as version from scm 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 selectMeta ( $ tid , $ version , $ fkTable ) { $ qq = 'select_meta' ; $ this -> sdb -> prepare ( $ qq , 'select
                            aa.id, aa.version, aa.ic_id, aa.citation_id, aa.sub_citation, aa.source_data,
                            aa.rule_id, aa.note
                            from scm as aa,
                            (select id,max(version) as version from scm 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 a snac control meta data record
 May 6 2016: Reading the code, we will return zero or many records.
Select a snac control meta data record May 6 2016 : Reading the code we will return zero or many records .