#!/bin/sh

###############################################################################
# Copyright 2002 W3C (MIT, INRIA, Keio), All Rights Reserved.
# W3C liability, trademark, document use and software licensing rules apply.
# Written by Eric Prud'hommeaux for the World Wide Web Consortium
# 
# OrderTracking4 - Shipping department view
# Tests:
#   - multiple join on Customers and Addresses
#   - optional properties ?sFirst ?sLast ?shipStreet ?shipCity ?shipState
# 
# Part of W3C/Rdf/test perl library test suite.
# See http://www.w3.org/1999/02/26-modules/
# $Id: OrderTracking4-alg.sh,v 1.1 2003/10/17 00:28:41 eric Exp $
###############################################################################

algae $* \
-a"
ns testDB=<http://localhost/OrderTracking#>
attach <http://www.w3.org/1999/02/26-modules/algae#dynamic> testDB:test1 (
                    class=\"W3C::Rdf::SqlDB\"
                    properties=\"OrderTracking.prop\")
ask testDB:test1 (
       ?o	testDB:Orders_id		?orderId .
       ?o	testDB:Orders_customer		?c .
       ?o	testDB:Orders_orderDate		20020907 .
       ?c	testDB:Customers_givenName	?first .
       ?c	testDB:Customers_familyName	?last .
       ?c	testDB:Customers_billingAddress	?billAddr .
       ?billAddr testDB:Addresses_street	?billStreet .
       ?billAddr testDB:Addresses_city		?billCity .
       ?billAddr testDB:Addresses_state		?billState .
       ~?o	testDB:Orders_shippingAddress	?shipAddr .
       ~?shipAddr testDB:Addresses_street	?shipStreet .
       ~?shipAddr testDB:Addresses_city		?shipCity .
       ~?shipAddr testDB:Addresses_state	?shipState .
       ~?shipAddr testDB:Addresses_contact	?signer .
       ~?signer	testDB:Customers_givenName	?sFirst .
       ~?signer	testDB:Customers_familyName	?sLast .
      )
collect (?orderId ?first ?last ?billStreet ?billCity ?billState ?sFirst ?sLast ?shipStreet ?shipCity ?shipState) 
" \

# Table Results:
# +-------+------+----------+----------------+----------+---------+--------+--------+-----------------+----------+---------+
# |orderId| first|      last|      billStreet|  billCity|billState|  sFirst|   sLast|       shipStreet|  shipCity|shipState|
# |-------|------|----------|----------------|----------|---------|--------|--------|-----------------|----------|---------|
# | "2185"|"Biff"|"Thompson"|"123 Elm Street"|"EdgeCity"|     "AV"|    NULL|    NULL|             NULL|      NULL|     NULL|
# | "2187"|"Chip"|"Thompson"|"123 Elm Street"|"EdgeCity"|     "AV"|    NULL|    NULL|             NULL|      NULL|     NULL|
# | "3183"|"Chip"|"Thompson"|"123 Elm Street"|"EdgeCity"|     "AV"|"Eustis"|"Walker"|"245 King Street"|"EdgeCity"|     "AV"|
# +-------+------+----------+----------------+----------+---------+--------+--------+-----------------+----------+---------+

# SQL Query:
# SELECT Orders_0.id AS o_id,
#        Customers_0.id AS c_id,
#        Customers_0.givenName AS first_givenName,
# Customers_0.familyName AS last_familyName,
# Addresses_0.id AS billAddr_id,
#        Addresses_0.street AS billStreet_street,
# Addresses_0.city AS billCity_city,
# Addresses_0.state AS billState_state,
# Addresses_1.id AS shipAddr_id,
#        Addresses_1.street AS shipStreet_street,
# Addresses_1.city AS shipCity_city,
# Addresses_1.state AS shipState_state,
# Customers_1.id AS signer_id,
#        Customers_1.givenName AS sFirst_givenName,
# Customers_1.familyName AS sLast_familyName
# FROM Orders AS Orders_0
#      INNER JOIN Customers AS Customers_0 ON Orders_0.customer=Customers_0.id
#      INNER JOIN Addresses AS Addresses_0 ON Customers_0.billingAddress=Addresses_0.id
#      LEFT OUTER JOIN Addresses AS Addresses_1 ON (Orders_0.shippingAddress=Addresses_1.id)
#      LEFT OUTER JOIN Customers AS Customers_1 ON (Addresses_1.contact=Customers_1.id)
# WHERE Orders_0.orderDate="20020907"