src/snac/server/database/SQL.php
/**
     * Browse Name Index
     *
     * This function contains the SQL code required to browse through the name index in postgres
     * in (mostly) alphabetical order.
     *
     * @param string $query The string to search for
     * @param string $position The position of the search term in the results: before, middle, after
     * @param string $entityType The string representation of entity type: person, corporateBody, family
     * @param int    $icid The identity constellation ID to break ties on sorting (or 0 if ignored)
     * @return string[] List of name index results in raw format
     */
Browse Name Index
This function contains the SQL code required to browse through the name index in postgres
in (mostly) alphabetical order.


Browse Name Index This function contains the SQL code required to browse through the name index in postgres in ( mostly ) alphabetical order .
public function browseNameIndex($query, $position, $entityType, $icid)
    {
        $entityQuery = "";
        if ($entityType != null && $entityType != "") {
            switch ($entityType) {
                case "person":
                    $entityQuery = "and entity_type = 'person'";
                    break;
                case "corporateBody":
                    $entityQuery = "and entity_type = 'corporateBody'";
                    break;
                case "family":
                    $entityQuery = "and entity_type = 'family'";
                    break;
            }
        }
        $result = null;
        if ($position == "after") {
            $queryStr = "select * from (select * from name_index where (name_entry = $1 and ic_id >= $2) $entityQuery order by name_entry_lower, name_entry, ic_id asc limit 20) a union all (select * from name_index where name_entry > $1 $entityQuery order by name_entry_lower, name_entry, ic_id asc limit 20) order by name_entry, ic_id asc limit 20;";
            $result = $this->sdb->query($queryStr, array($query, $icid));
        } else if ($position == "before") {
            if ($icid !== 0) {
                $queryStr = "select * from (select * from (select * from name_index where (name_entry = $1 and ic_id <= $2) $entityQuery order by name_entry_lower desc, name_entry desc, ic_id desc limit 20) a union all (select * from name_index where name_entry < $1 $entityQuery order by name_entry_lower desc, name_entry desc, ic_id desc limit 20) order by name_entry desc, ic_id desc limit 20) a order by name_entry asc, ic_id asc limit 20;";
                $result = $this->sdb->query($queryStr, array($query, $icid));
            } else {
                $queryStr = "select * from (select * from name_index where name_entry_lower <= lower($1) $entityQuery order by name_entry_lower desc, ic_id desc limit 20) a order by name_entry, ic_id asc;";
                $result = $this->sdb->query($queryStr, array($query));
            }
        } else {
            $queryStr =
                "select * from (select * from name_index where name_entry_lower >= lower($1) $entityQuery order by name_entry_lower, ic_id asc limit 10) a union all (select * from name_index where name_entry_lower < lower($1) $entityQuery order by name_entry_lower desc, ic_id desc limit 10) order by name_entry, ic_id asc;";
            $result = $this->sdb->query($queryStr, array($query));
        }
        $all = array();
        while($row = $this->sdb->fetchRow($result))
        {
            array_push($all, $row);
        }
        return $all;
    }
public function browseNameIndex ( $ query , $ position , $ entityType , $ icid ) { $ entityQuery = " " ; if ( $ entityType != null && $ entityType != " " ) { switch ( $ entityType ) { case " person " : $ entityQuery = " and entity_type = 'person' " ; break ; case " corporateBody " : $ entityQuery = " and entity_type = 'corporateBody' " ; break ; case " family " : $ entityQuery = " and entity_type = 'family' " ; break ; } } $ result = null ; if ( $ position == " after " ) { $ queryStr = " select * from (select * from name_index where (name_entry = $1 and ic_id >= $2)  $ entityQuery  order by name_entry_lower, name_entry, ic_id asc limit 20) a union all (select * from name_index where name_entry > $1  $ entityQuery  order by name_entry_lower, name_entry, ic_id asc limit 20) order by name_entry, ic_id asc limit 20; " ; $ result = $ this -> sdb -> query ( $ queryStr , array ( $ query , $ icid ) ) ; } else if ( $ position == " before " ) { if ( $ icid !== 0 ) { $ queryStr = " select * from (select * from (select * from name_index where (name_entry = $1 and ic_id <= $2)  $ entityQuery  order by name_entry_lower desc, name_entry desc, ic_id desc limit 20) a union all (select * from name_index where name_entry < $1  $ entityQuery  order by name_entry_lower desc, name_entry desc, ic_id desc limit 20) order by name_entry desc, ic_id desc limit 20) a order by name_entry asc, ic_id asc limit 20; " ; $ result = $ this -> sdb -> query ( $ queryStr , array ( $ query , $ icid ) ) ; } else { $ queryStr = " select * from (select * from name_index where name_entry_lower <= lower($1)  $ entityQuery  order by name_entry_lower desc, ic_id desc limit 20) a order by name_entry, ic_id asc; " ; $ result = $ this -> sdb -> query ( $ queryStr , array ( $ query ) ) ; } } else { $ queryStr = " select * from (select * from name_index where name_entry_lower >= lower($1)  $ entityQuery  order by name_entry_lower, ic_id asc limit 10) a union all (select * from name_index where name_entry_lower < lower($1)  $ entityQuery  order by name_entry_lower desc, ic_id desc limit 10) order by name_entry, ic_id asc; " ; $ result = $ this -> sdb -> query ( $ queryStr , array ( $ query ) ) ; } $ all = array ( ) ; while ( $ row = $ this -> sdb -> fetchRow ( $ result ) ) { array_push ( $ all , $ row ) ; } return $ all ; }
Browse Name Index
 This function contains the SQL code required to browse through the name index in postgres
 in (mostly) alphabetical order.
Browse Name Index This function contains the SQL code required to browse through the name index in postgres in ( mostly ) alphabetical order .
// Do this for safety concerns with SQL injections... // query using the ICID as well // query without the ICID, since it is meaningless