content: {- |
     Load this module in GHCi and use the ":pSQL" command
     to evaluate the queries. 
  -}
  module Select
  
  where
  
  -- import HaskellDB operators and
  -- functions.
  import Database.HaskellDB
  
  -- Import table type and definition directly.
  import DB1.Int_tbl (Int_tbl, int_tbl)
  import DB1.String_tbl (String_tbl, string_tbl)
  
  -- Import Hdb_t1 directly so we can use its columns
  -- as labels in projections easily.
  import DB1.Hdb_t1 (t1f01, t1f02, t1f05, t1f06
                    , t1f09, t1f10, t1f13, t1f14, t1f21, t1f22)
  
  -- Import columns on tables with a qualified name.
  import qualified DB1.Int_tbl as Int_tbl
  import qualified DB1.String_tbl as String_tbl
  
  import DB1.String_tbl (String_tbl, string_tbl)
  import qualified DB1.String_tbl as String_tbl
  
  -- Select query examples
  
  -- | Demonstrates use of aggregate count() function
  -- in a projection.
  demoCount = do
    t1 <- table int_tbl
    t2 <- table int_tbl
    order [asc t1 Int_tbl.f02]
    project $ Int_tbl.f02 << count(t1 ! Int_tbl.f02)
                # Int_tbl.f01 << (t2 ! Int_tbl.f01) 
  
  -- | Demonstrate using the 'unique' operator. This query
  -- will be grouped by all the columns in the projection.
  uniqueUsage = do
    t1 <- table int_tbl
    t2 <- project $ Int_tbl.f01 << t1 ! Int_tbl.f01 
    unique
    return t2
  
  -- | Shows the use of the copyAll operator.
  -- This query projects all the columns in string_tbl.
  copyAllUsage = do 
    t2 <- table string_tbl
    project $ copyAll t2 
  
  -- | Shows usage of the copy operator. This
  -- query projects columns from int_tbl and string_tbl. Note
  -- that copy will NOT rename columns, so you can get column
  -- collisions if you are not careful.
  copyUsage = do 
    t2 <- table string_tbl
    t1 <- table int_tbl
    project $ copy Int_tbl.f01 t1 
                # copy String_tbl.f02 t2 
  
  -- | Select all columns from Int_tbl
  select1 = do
    intTbl <- table int_tbl
    project $ copyAll intTbl
  
  -- | Select some columns from Int_tbl
  -- and some from String_tbl. No restriction is
  -- specified between the two tables, so 
  -- this will be a cross join. 
  select2 = do
    intTbl <- table int_tbl
    strTbl <- table string_tbl
    project $ t1f05 << intTbl ! Int_tbl.f01 
            # t1f02 << strTbl ! String_tbl.f02
            # t1f05 << intTbl ! Int_tbl.f03
            # t1f02 << strTbl ! String_tbl.f04
              
  -- | Select values from Int_tbl and 
  -- cast them to text. 
  select3 = do
    intTbl <- table int_tbl
    project $ t1f02 << cast "text" (intTbl ! Int_tbl.f01) 
  
  -- | Select values from Int_tbl and String_tbl, cast
  -- the int values to text, and join them to related
  -- values on String_tbl. Finally, project two columns
  -- from the join.
  select4 = do
    intTbl <- table int_tbl
    strTbl <- table string_tbl
    restrict $ cast "text" (intTbl ! Int_tbl.f01) .==. 
             strTbl ! String_tbl.f01
    project $ t1f05 << intTbl ! Int_tbl.f01
            # t1f06 << intTbl ! Int_tbl.f02
            # t1f01 << strTbl ! String_tbl.f01
            # t1f02 << strTbl ! String_tbl.f02
  