content: 
  -- Section 14 in Foundation
  
  
  {-# LANGUAGE OverloadedStrings #-}
  module Language.SQL.SimpleSQL.SQL2011DataManipulation (sql2011DataManipulationTests) where
  
  import Language.SQL.SimpleSQL.TestTypes
  import Language.SQL.SimpleSQL.Syntax
  import Language.SQL.SimpleSQL.TestRunners
  import Data.Text (Text)
  
  sql2011DataManipulationTests :: TestItem
  sql2011DataManipulationTests = Group "sql 2011 data manipulation tests"
      [
  
  
  {-
  14 Data manipulation
  
  
  14.1 <declare cursor>
  
  <declare cursor> ::=
    DECLARE <cursor name> <cursor properties>
        FOR <cursor specification>
  
  14.2 <cursor properties>
  
  <cursor properties> ::=
    [ <cursor sensitivity> ] [ <cursor scrollability> ] CURSOR
        [ <cursor holdability> ]
        [ <cursor returnability> ]
  
  <cursor sensitivity> ::=
      SENSITIVE
    | INSENSITIVE
    | ASENSITIVE
  
  <cursor scrollability> ::=
      SCROLL
    | NO SCROLL
  
  <cursor holdability> ::=
      WITH HOLD
    | WITHOUT HOLD
  
  <cursor returnability> ::=
      WITH RETURN
    | WITHOUT RETURN
  
  14.3 <cursor specification>
  
  <cursor specification> ::=
    <query expression> [ <updatability clause> ]
  
  <updatability clause> ::=
    FOR { READ ONLY | UPDATE [ OF <column name list> ] }
  
  14.4 <open statement>
  
  <open statement> ::=
    OPEN <cursor name>
  
  14.5 <fetch statement>
  
  <fetch statement> ::=
       FETCH [ [ <fetch orientation> ] FROM ] <cursor name> INTO <fetch target list>
  
  <fetch orientation> ::=
      NEXT
    | PRIOR
    | FIRST
    | LAST
    | { ABSOLUTE | RELATIVE } <simple value specification>
  
  <fetch target list> ::=
       <target specification> [ { <comma> <target specification> }... ]
  
  
  14.6 <close statement>
  
  <close statement> ::=
    CLOSE <cursor name>
  
  14.7 <select statement: single row>
  
  <select statement: single row> ::=
    SELECT [ <set quantifier> ] <select list>
        INTO <select target list>
        <table expression>
  
  <select target list> ::=
    <target specification> [ { <comma> <target specification> }... ]
  
  14.8 <delete statement: positioned>
  
  <delete statement: positioned> ::=
       DELETE FROM <target table> [ [ AS ] <correlation name> ]
           WHERE CURRENT OF <cursor name>
  
  <target table> ::=
      <table name>
    | ONLY <left paren> <table name> <right paren>
  
  14.9 <delete statement: searched>
  
  <delete statement: searched> ::=
    DELETE FROM <target table>
        [ FOR PORTION OF <application time period name>
          FROM <point in time 1> TO <point in time 2> ]
        [ [ AS ] <correlation name> ]
        [ WHERE <search condition> ]
  -}
  
       s "delete from t"
       $ Delete [Name Nothing "t"] Nothing Nothing
  
      ,s "delete from t as u"
       $ Delete [Name Nothing "t"] (Just (Name Nothing "u")) Nothing
  
      ,s "delete from t where x = 5"
       $ Delete [Name Nothing "t"] Nothing
         (Just $ BinOp (Iden [Name Nothing "x"]) [Name Nothing "="] (NumLit "5"))
  
  
      ,s "delete from t as u where u.x = 5"
       $ Delete [Name Nothing "t"] (Just (Name Nothing "u"))
         (Just $ BinOp (Iden [Name Nothing "u", Name Nothing "x"]) [Name Nothing "="] (NumLit "5"))
  
  {-
  14.10 <truncate table statement>
  
  <truncate table statement> ::=
    TRUNCATE TABLE <target table> [ <identity column restart option> ]
  
  <identity column restart option> ::=
      CONTINUE IDENTITY
    | RESTART IDENTITY
  -}
  
      ,s "truncate table t"
       $ Truncate [Name Nothing "t"] DefaultIdentityRestart
  
      ,s "truncate table t continue identity"
       $ Truncate [Name Nothing "t"] ContinueIdentity
  
      ,s "truncate table t restart identity"
       $ Truncate [Name Nothing "t"] RestartIdentity
  
  
  {-
  14.11 <insert statement>
  
  <insert statement> ::=
    INSERT INTO <insertion target> <insert columns and source>
  
  <insertion target> ::=
    <table name>
  
  <insert columns and source> ::=
      <from subquery>
    | <from constructor>
    | <from default>
  
  <from subquery> ::=
    [ <left paren> <insert column list> <right paren> ]
        [ <override clause> ]
        <query expression>
  
  <from constructor> ::=
    [ <left paren> <insert column list> <right paren> ]
        [ <override clause> ]
        <contextually typed table value constructor>
  
  <override clause> ::=
      OVERRIDING USER VALUE
    | OVERRIDING SYSTEM VALUE
  
  <from default> ::=
    DEFAULT VALUES
  
  <insert column list> ::=
    <column name list>
  -}
  
      ,s "insert into t select * from u"
       $ Insert [Name Nothing "t"] Nothing
         $ InsertQuery $ toQueryExpr $ makeSelect
           {msSelectList = [(Star, Nothing)]
           ,msFrom = [TRSimple [Name Nothing "u"]]}
  
      ,s "insert into t(a,b,c) select * from u"
       $ Insert [Name Nothing "t"] (Just [Name Nothing "a", Name Nothing "b", Name Nothing "c"])
         $ InsertQuery $ toQueryExpr $ makeSelect
           {msSelectList = [(Star, Nothing)]
           ,msFrom = [TRSimple [Name Nothing "u"]]}
  
      ,s "insert into t default values"
       $ Insert [Name Nothing "t"] Nothing DefaultInsertValues
  
      ,s "insert into t values(1,2)"
       $ Insert [Name Nothing "t"] Nothing
         $ InsertQuery $ Values [[NumLit "1", NumLit "2"]]
  
      ,s "insert into t values (1,2),(3,4)"
       $ Insert [Name Nothing "t"] Nothing
         $ InsertQuery $ Values [[NumLit "1", NumLit "2"]
                                ,[NumLit "3", NumLit "4"]]
  
      ,s
        "insert into t values (default,null,array[],multiset[])"
       $ Insert [Name Nothing "t"] Nothing
         $ InsertQuery $ Values [[Iden [Name Nothing "default"]
                                 ,Iden [Name Nothing "null"]
                                 ,Array (Iden [Name Nothing "array"]) []
                                 ,MultisetCtor []]]
  
  
  {-
  14.12 <merge statement>
  
  <merge statement> ::=
    MERGE INTO <target table> [ [ AS ] <merge correlation name> ]
        USING <table reference>
        ON <search condition> <merge operation specification>
  
  merge into t
    using t
    on a = b
    merge operation specification
  
  merge into t as u
  using (table factor | joined expression)
  
   MERGE INTO tablename USING table_reference ON (condition)
     WHEN MATCHED THEN
     UPDATE SET column1 = value1 [, column2 = value2 ...]
     WHEN NOT MATCHED THEN
     INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...
  
  merge into t23
  using t42
  on t42.id = t23.id
  when matched then
      update
      set     t23.col1 = t42.col1
  when not matched then
      insert  (id, col1)
      values  (t42.id, t42.col1)
  
  
  
  MERGE INTO TableA u
  
  USING (SELECT b.Key1, b.ColB1, c.ColC1
  
  FROM TableB b
  
  INNER JOIN TableC c ON c.KeyC1 = b.KeyB1
  
  ) s
  
  ON (u.KeyA1 = s.KeyA1)
  
  WHEN MATCHED THEN
  
  UPDATE SET u.ColA1 = s.ColB1, u.ColA2 = s.ColC1
  
  
  MERGE INTO Department 
  USING NewDept AS ND 
  ON nd.Department_Number = Department.
  Department_Number 
  WHEN MATCHED THEN UPDATE 
  SET budget_amount = nd.Budget_Amount 
  WHEN NOT MATCHED THEN INSERT 
  VALUES 
  (nd.Department_Number, nd.Department_
  Name, nd.Budget_Amount, 
   nd.Manager_Employee_Number);
  
  
  MERGE INTO Orders2 
  USING Orders3 
  ON ORDERS3.Order_Number = Orders2.
  Order_Number 
  WHEN NOT MATCHED THEN INSERT 
  Orders3.order_number, Orders3.
  invoice_number, 
   Orders3.customer_number, Orders3.
  initial_order_date, 
   Orders3.invoice_date, Orders3.
  invoice_amount);
  
  MERGE INTO Orders2 
  USING Orders3 
  ON ORDERS3.Order_Number = Orders2.
  Order_Number AND 1=0 
  WHEN NOT MATCHED THEN INSERT 
  (Orders3.order_number, Orders3.invoice_number, 
   Orders3.customer_number, Orders3.
  initial_order_date, 
   Orders3.invoice_date, Orders3.
  invoice_amount);
  
  MERGE INTO Department 
  USING NewDept AS ND 
  ON nd.Department_Number = Department.
  Department_Number 
  WHEN MATCHED THEN UPDATE 
  SET budget_amount = nd.Budget_Amount 
  LOGGING ALL ERRORS WITH NO LIMIT;
  
  
  MERGE INTO Department 
  USING 
   (SELECT Department_Number,
  department_name, 
          Budget_Amount, 
  Manager_Employee_Number 
      FROM NewDept 
      WHERE Department_Number IN 
  (SELECT Department_Number 
          FROM Employee)) AS m
  ON m.Department_Number = Department.
  Department_Number 
  WHEN MATCHED THEN UPDATE 
  SET budget_amount = m.Budget_Amount 
  WHEN NOT MATCHED THEN INSERT 
  (m.Department_Number, m.Department_
  Name, m.Budget_Amount, 
  m.Manager_Employee_Number) 
  LOGGING ALL ERRORS WITH NO LIMIT;
  
   
  MERGE INTO Customers AS c
  USING      Moved     AS m
        ON   m.SSN      = c.SSN
  WHEN MATCHED
  THEN UPDATE
  SET        Street     = m.Street,
             HouseNo    = m.HouseNo,
             City       = m.City;
  
  MERGE INTO CentralOfficeAccounts AS C    -- Target
  USING BranchOfficeAccounts AS B          -- Source
     ON C.account_nbr = B.account_nbr
  WHEN MATCHED THEN                        -- On match update
       UPDATE SET C.company_name = B.company_name,
                  C.primary_contact = B.primary_contact,
                  C.contact_phone = B.contact_phone
  WHEN NOT MATCHED THEN                    -- Add missing
       INSERT (account_nbr, company_name, primary_contact, contact_phone)
       VALUES (B.account_nbr, B.company_name, B.primary_contact, B.contact_phone);
   
  SELECT account_nbr, company_name, primary_contact, contact_phone 
  FROM CentralOfficeAccounts;
  
  
  
  MERGE INTO CentralOfficeAccounts AS C   -- Target
  USING BranchOfficeAccounts AS B         -- Source
     ON C.account_nbr = B.account_nbr
  WHEN MATCHED                            -- On match update
   AND (C.company_name <> B.company_name  -- Additional search conditions
     OR C.primary_contact <> B.primary_contact
     OR C.contact_phone <> B.contact_phone) THEN                        
       UPDATE SET C.company_name = B.company_name,
                  C.primary_contact = B.primary_contact,
                  C.contact_phone = B.contact_phone
  WHEN NOT MATCHED THEN                   -- Add missing
       INSERT (account_nbr, company_name, primary_contact, contact_phone)
       VALUES (B.account_nbr, B.company_name, B.primary_contact, B.contact_phone);
  
  
  
  MERGE INTO CentralOfficeAccounts AS C   -- Target
  USING BranchOfficeAccounts AS B         -- Source
     ON C.account_nbr = B.account_nbr
  WHEN MATCHED                            -- On match update
   AND (C.company_name <> B.company_name  -- Additional search conditions
     OR C.primary_contact <> B.primary_contact
     OR C.contact_phone <> B.contact_phone) THEN                        
       UPDATE SET C.company_name = B.company_name,
                  C.primary_contact = B.primary_contact,
                  C.contact_phone = B.contact_phone
  WHEN NOT MATCHED THEN                   -- Add missing
       INSERT (account_nbr, company_name, primary_contact, contact_phone)
       VALUES (B.account_nbr, B.company_name, B.primary_contact, B.contact_phone)
  WHEN SOURCE NOT MATCHED THEN            -- Delete missing from source
       DELETE;
   
  SELECT account_nbr, company_name, primary_contact, contact_phone 
  FROM CentralOfficeAccounts; 
  
  
  
  
  <merge correlation name> ::=
    <correlation name>
  
  <merge operation specification> ::=
    <merge when clause>...
  
  <merge when clause> ::=
      <merge when matched clause>
    | <merge when not matched clause>
  
  <merge when matched clause> ::=
    WHEN MATCHED [ AND <search condition> ]
        THEN <merge update or delete specification>
  
  <merge update or delete specification> ::=
      <merge update specification>
    | <merge delete specification>
  
  <merge when not matched clause> ::=
    WHEN NOT MATCHED [ AND <search condition> ]
        THEN <merge insert specification>
  
  <merge update specification> ::=
    UPDATE SET <set clause list>
  
  <merge delete specification> ::=
    DELETE
  
  <merge insert specification> ::=
    INSERT [ <left paren> <insert column list> <right paren> ]
        [ <override clause> ]
        VALUES <merge insert value list>
  
  <merge insert value list> ::=
    <left paren>
        <merge insert value element> [ { <comma> <merge insert value element> }... ]
        <right paren>
  
  <merge insert value element> ::=
      <value expression>
    | <contextually typed value specification>
  
  14.13 <update statement: positioned>
  
  <updatestatement: positioned> ::=
       UPDATE <target table> [ [ AS ] <correlation name> ]
           SET <set clause list>
           WHERE CURRENT OF <cursor name>
  
  14.14 <update statement: searched>
  
  <update statement: searched> ::=
    UPDATE <target table>
        [ FOR PORTION OF <application time period name>
          FROM <point in time 1> TO <point in time 2> ]
        [ [ AS ] <correlation name> ]
        SET <set clause list>
        [ WHERE <search condition> ]
  -}
  
  
      ,s "update t set a=b"
       $ Update [Name Nothing "t"] Nothing
         [Set [Name Nothing "a"] (Iden [Name Nothing "b"])] Nothing
  
      ,s "update t set a=b, c=5"
       $ Update [Name Nothing "t"] Nothing
         [Set [Name Nothing "a"] (Iden [Name Nothing "b"])
         ,Set [Name Nothing "c"] (NumLit "5")] Nothing
  
  
      ,s "update t set a=b where a>5"
       $ Update [Name Nothing "t"] Nothing
         [Set [Name Nothing "a"] (Iden [Name Nothing "b"])]
         $ Just $ BinOp (Iden [Name Nothing "a"]) [Name Nothing ">"] (NumLit "5")
  
  
      ,s "update t as u set a=b where u.a>5"
       $ Update [Name Nothing "t"] (Just $ Name Nothing "u")
         [Set [Name Nothing "a"] (Iden [Name Nothing "b"])]
         $ Just $ BinOp (Iden [Name Nothing "u",Name Nothing "a"])
                        [Name Nothing ">"] (NumLit "5")
  
      ,s "update t set (a,b)=(3,5)"
       $ Update [Name Nothing "t"] Nothing
         [SetMultiple [[Name Nothing "a"],[Name Nothing "b"]]
                      [NumLit "3", NumLit "5"]] Nothing
  
  
  
  {-
  14.15 <set clause list>
  
  <set clause list> ::=
    <set clause> [ { <comma> <set clause> }... ]
  
  <set clause> ::=
      <multiple column assignment>
    | <set target> <equals operator> <update source>
  
  <set target> ::=
      <update target>
    | <mutated set clause>
  
  <multiple column assignment> ::=
    <set target list> <equals operator> <assigned row>
  
  <set target list> ::=
    <left paren> <set target> [ { <comma> <set target> }... ] <right paren>
  
  <assigned row> ::=
    <contextually typed row value expression>
  
  <update target> ::=
      <object column>
    | <object column>
        <left bracket or trigraph> <simple value specification> <right bracket or trigraph>
  
  <object column> ::=
    <column name>
  
  <mutated set clause> ::=
    <mutated target> <period> <method name>
  
  <mutated target> ::=
      <object column>
    | <mutated set clause>
  
  <update source> ::=
      <value expression>
    | <contextually typed value specification>
  
  14.16 <temporary table declaration>
  
  <temporary table declaration> ::=
    DECLARE LOCAL TEMPORARY TABLE <table name> <table element list>
        [ ON COMMIT <table commit action> ROWS ]
  
  declare local temporary table t (a int) [on commit {preserve | delete} rows]
  
  14.17 <free locator statement>
  
  <free locator statement> ::=
    FREE LOCATOR <locator reference> [ { <comma> <locator reference> }... ]
  
  <locator reference> ::=
      <host parameter name>
    | <embedded variable name>
    | <dynamic parameter specification>
  
  14.18 <hold locator statement>
  
  <hold locator statement> ::=
    HOLD LOCATOR <locator reference> [ { <comma> <locator reference> }... ]
  -}
  
  
     ]
  
  s :: HasCallStack => Text -> Statement -> TestItem
  s src ast = testStatement ansi2011 src ast
  