SWI-Prolog -- Manual

Documentation
- Reference manual
- Packages
  - Constraint Query Language A high level interface to SQL databases
    - library(cql/cql): CQL - Constraint Query Language
      - Examples
        
        Simple INSERT
        
        Simple INSERT with retrieval of identity of the inserted
        
        Simple DELETE
        
        Simple SELECT
        
        Simple UPDATE
        
        WHERE with arithmetic comparison
        
        Simple INNER JOIN
        
        Arithmetic UPDATE with an INNER JOIN and a WHERE restriction
        
        Confirm row does not exist
        
        Aggregation - Count
        
        Aggregation - Sum
        
        Aggregation - Average
        
        Maximum Value
        
        Minimum Value
        
        Aggregation requiring GROUP BY
        
        INNER JOIN with an aggregation sub-query where the sub-query is constrained by a shared variable from the main query
        
        INNER JOIN in an aggregation sub-query
        
        Negation
        
        EXISTS
        
        Left Outer Join
        
        List-based Restrictions
        
        Compile time in-list constraint
        
        Disjunction resulting in OR in WHERE clause
        
        Disjunction resulting in different joins (implemented as a SQL UNION)
        
        Disjunction resulting in different SELECT attributes (implemented as separate ODBC queries)
        
        ORDER BY
        
        DISTINCT
        
        SELECT with NOT NULL restriction
        
        First N
        
        Self JOIN
        
        Removing null comparisions
        
        Three table JOIN
        
        Three table JOIN with NOLOCK locking hint
        
        SELECT with LIKE
        
        Writing exceptions directly to the database
        
        TOP N is Parametric
        
        Using compile_time_goal/1
        
        ON
        
        Expressions In Where Restrictions
        
        Explicitly avoid the "No WHERE restriction" message
        
        HAVING
        
        INSERT and UPDATE value in-line formatting
        
        Negations in WHERE Clauses
        
        Predicate-generated Attribute Values
        
        INSERT from SELECT

1.7.31 Removing null comparisions

Use the ignore_if_null wrapper in your CQL to 'filter out' null input values. This is a useful extension for creating user-designed searches.

{[],
 se_lt_x :: [a-UserName,
             b-ignore_if_null(SearchKey),
             ...]}

At runtime, if SearchKey is bound to a value other than {null} then the query will contain WHERE ... b = ?. If, however, SearchKey is bound to {null}, then this comparison will be omitted.

Disjunctions

In general, don't use ignore_if_null in disjunctions. Consider this query:

SearchKey = '%ELSTON%',
{[],
 se_lt_x :: [a-UserName,
             b-RealName],
 ( RealName =~ SearchKey
 ; UserName =~ SearchKey)}

The query means "find a user where the UserName contains ELSTON OR the RealName contain ELSTON". If !SearchKey is {null} then RealName=~ {null} will fail, which is correct. If ignore_if_null was used, the test would succeed, which means the disjunction would always succeed i.e. the query would contain no restriction, which is clearly not the intended result. FIXME: Mike, what is this all about?