foreign.smli

(*
 * Licensed to Julian Hyde under one or more contributor license
 * agreements.  See the NOTICE file distributed with this work
 * for additional information regarding copyright ownership.
 * Julian Hyde licenses this file to you under the Apache
 * License, Version 2.0 (the "License"); you may not use this
 * file except in compliance with the License.  You may obtain a
 * copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
 * either express or implied.  See the License for the specific
 * language governing permissions and limitations under the
 * License.
 *
 * Tests for foreign data sources ("scott" and "foodmart").
 *)
Sys.set ("printLength", 64);
> val it = () : unit
Sys.set ("lineWidth", 78);
> val it = () : unit

scott;
> val it = {bonus=<relation>,dept=<relation>,emp=<relation>,salgrade=<relation>}
>   : {bonus:{comm:real, ename:string, job:string, sal:real} list,
>      dept:{deptno:int, dname:string, loc:string} list,
>      emp:
>          {comm:real, deptno:int, empno:int, ename:string, hiredate:string,
>           job:string, mgr:int, sal:real} list,
>      salgrade:{grade:int, hisal:real, losal:real} list}
#dept scott;
> val it = <relation> : {deptno:int, dname:string, loc:string} list
scott.dept;
> val it = <relation> : {deptno:int, dname:string, loc:string} list
from d in scott.dept;
> val it =
>   [{deptno=10,dname="ACCOUNTING",loc="NEW YORK"},
>    {deptno=20,dname="RESEARCH",loc="DALLAS"},
>    {deptno=30,dname="SALES",loc="CHICAGO"},
>    {deptno=40,dname="OPERATIONS",loc="BOSTON"}]
>   : {deptno:int, dname:string, loc:string} list
from d in scott.dept yield {d.dname, d.loc};
> val it =
>   [{dname="ACCOUNTING",loc="NEW YORK"},{dname="RESEARCH",loc="DALLAS"},
>    {dname="SALES",loc="CHICAGO"},{dname="OPERATIONS",loc="BOSTON"}]
>   : {dname:string, loc:string} list
foodmart;
> val it =
>   {account=<relation>,agg_c_10_sales_fact_1997=<relation>,
>    agg_c_14_sales_fact_1997=<relation>,
>    agg_c_special_sales_fact_1997=<relation>,
>    agg_g_ms_pcat_sales_fact_1997=<relation>,
>    agg_l_03_sales_fact_1997=<relation>,agg_l_04_sales_fact_1997=<relation>,
>    agg_l_05_sales_fact_1997=<relation>,agg_lc_06_sales_fact_1997=<relation>,
>    agg_lc_100_sales_fact_1997=<relation>,agg_ll_01_sales_fact_1997=<relation>,
>    agg_pl_01_sales_fact_1997=<relation>,category=<relation>,
>    currency=<relation>,customer=<relation>,days=<relation>,
>    department=<relation>,employee=<relation>,employee_closure=<relation>,
>    expense_fact=<relation>,inventory_fact_1997=<relation>,
>    inventory_fact_1998=<relation>,position=<relation>,product=<relation>,
>    product_class=<relation>,promotion=<relation>,region=<relation>,
>    reserve_employee=<relation>,salary=<relation>,sales_fact_1997=<relation>,
>    sales_fact_1998=<relation>,sales_fact_dec_1998=<relation>,store=<relation>,
>    store_ragged=<relation>,time_by_day=<relation>,warehouse=<relation>,
>    warehouse_class=<relation>}
>   : {
>      account:
>              {account_description:string, account_id:int, account_parent:int,
>               account_rollup:string, account_type:string,
>               custom_members:string} list,
>      agg_c_10_sales_fact_1997:
>                               {customer_count:int, fact_count:int,
>                                month_of_year:int, quarter:string,
>                                store_cost:real, store_sales:real, the_year:int,
>                                unit_sales:real} list,
>      agg_c_14_sales_fact_1997:
>                               {customer_id:int, fact_count:int,
>                                month_of_year:int, product_id:int,
>                                promotion_id:int, quarter:string,
>                                store_cost:real, store_id:int, store_sales:real,
>                                the_year:int, unit_sales:real} list,
>      agg_c_special_sales_fact_1997:
>                                    {customer_id:int, fact_count:int,
>                                     product_id:int, promotion_id:int,
>                                     store_cost_sum:real, store_id:int,
>                                     store_sales_sum:real, time_month:int,
>                                     time_quarter:string, time_year:int,
>                                     unit_sales_sum:real} list,
>      agg_g_ms_pcat_sales_fact_1997:
>                                    {customer_count:int, fact_count:int,
>                                     gender:string, marital_status:string,
>                                     month_of_year:int, product_category:string,
>                                     product_department:string,
>                                     product_family:string, quarter:string,
>                                     store_cost:real, store_sales:real,
>                                     the_year:int, unit_sales:real} list,
>      agg_l_03_sales_fact_1997:
>                               {customer_id:int, fact_count:int,
>                                store_cost:real, store_sales:real, time_id:int,
>                                unit_sales:real} list,
>      agg_l_04_sales_fact_1997:
>                               {customer_count:int, fact_count:int,
>                                store_cost:real, store_sales:real, time_id:int,
>                                unit_sales:real} list,
>      agg_l_05_sales_fact_1997:
>                               {customer_id:int, fact_count:int, product_id:int,
>                                promotion_id:int, store_cost:real, store_id:int,
>                                store_sales:real, unit_sales:real} list,
>      agg_lc_06_sales_fact_1997:
>                                {city:string, country:string, fact_count:int,
>                                 state_province:string, store_cost:real,
>                                 store_sales:real, time_id:int, unit_sales:real} list,
>      agg_lc_100_sales_fact_1997:
>                                 {customer_id:int, fact_count:int,
>                                  product_id:int, quarter:string,
>                                  store_cost:real, store_sales:real,
>                                  the_year:int, unit_sales:real} list,
>      agg_ll_01_sales_fact_1997:
>                                {customer_id:int, fact_count:int,
>                                 product_id:int, store_cost:real,
>                                 store_sales:real, time_id:int, unit_sales:real} list,
>      agg_pl_01_sales_fact_1997:
>                                {customer_id:int, fact_count:int,
>                                 product_id:int, store_cost_sum:real,
>                                 store_sales_sum:real, time_id:int,
>                                 unit_sales_sum:real} list,
>      category:
>               {category_description:string, category_id:string,
>                category_parent:string, category_rollup:string} list,
>      currency:
>               {conversion_ratio:real, currency:string, currency_id:int,
>                date:string} list,
>      customer:
>               {account_num:int, address1:string, address2:string,
>                address3:string, address4:string, birthdate:string, city:string,
>                country:string, customer_id:int, customer_region_id:int,
>                date_accnt_opened:string, education:string, fname:string,
>                fullname:string, gender:string, houseowner:string, lname:string,
>                marital_status:string, member_card:string, mi:string,
>                num_cars_owned:int, num_children_at_home:int, occupation:string,
>                phone1:string, phone2:string, postal_code:string,
>                state_province:string, total_children:int, yearly_income:string} list,
>      days:{day:int, week_day:string} list,
>      department:{department_description:string, department_id:int} list,
>      employee:
>               {birth_date:string, department_id:int, education_level:string,
>                employee_id:int, end_date:string, first_name:string,
>                full_name:string, gender:string, hire_date:string,
>                last_name:string, management_role:string, marital_status:string,
>                position_id:int, position_title:string, salary:real,
>                store_id:int, supervisor_id:int} list,
>      employee_closure:{distance:int, employee_id:int, supervisor_id:int} list,
>      expense_fact:
>                   {account_id:int, amount:real, category_id:string,
>                    currency_id:int, exp_date:string, store_id:int, time_id:int} list,
>      inventory_fact_1997:
>                          {product_id:int, store_id:int, store_invoice:real,
>                           supply_time:int, time_id:int, units_ordered:int,
>                           units_shipped:int, warehouse_cost:real,
>                           warehouse_id:int, warehouse_sales:real} list,
>      inventory_fact_1998:
>                          {product_id:int, store_id:int, store_invoice:real,
>                           supply_time:int, time_id:int, units_ordered:int,
>                           units_shipped:int, warehouse_cost:real,
>                           warehouse_id:int, warehouse_sales:real} list,
>      position:
>               {management_role:string, max_scale:real, min_scale:real,
>                pay_type:string, position_id:int, position_title:string} list,
>      product:
>              {brand_name:string, cases_per_pallet:int, gross_weight:real,
>               low_fat:bool, net_weight:real, product_class_id:int,
>               product_id:int, product_name:string, recyclable_package:bool,
>               shelf_depth:real, shelf_height:real, shelf_width:real, sku:int,
>               srp:real, units_per_case:int} list,
>      product_class:
>                    {product_category:string, product_class_id:int,
>                     product_department:string, product_family:string,
>                     product_subcategory:string} list,
>      promotion:
>                {cost:real, end_date:string, media_type:string,
>                 promotion_district_id:int, promotion_id:int,
>                 promotion_name:string, start_date:string} list,
>      region:
>             {region_id:int, sales_city:string, sales_country:string,
>              sales_district:string, sales_district_id:int, sales_region:string,
>              sales_state_province:string} list,
>      reserve_employee:
>                       {birth_date:string, department_id:int,
>                        education_level:string, employee_id:int,
>                        end_date:string, first_name:string, full_name:string,
>                        gender:string, hire_date:string, last_name:string,
>                        marital_status:string, position_id:int,
>                        position_title:string, salary:real, store_id:int,
>                        supervisor_id:int} list,
>      salary:
>             {currency_id:int, department_id:int, employee_id:int,
>              overtime_paid:real, pay_date:string, salary_paid:real,
>              vacation_accrued:real, vacation_used:real} list,
>      sales_fact_1997:
>                      {customer_id:int, product_id:int, promotion_id:int,
>                       store_cost:real, store_id:int, store_sales:real,
>                       time_id:int, unit_sales:real} list,
>      sales_fact_1998:
>                      {customer_id:int, product_id:int, promotion_id:int,
>                       store_cost:real, store_id:int, store_sales:real,
>                       time_id:int, unit_sales:real} list,
>      sales_fact_dec_1998:
>                          {customer_id:int, product_id:int, promotion_id:int,
>                           store_cost:real, store_id:int, store_sales:real,
>                           time_id:int, unit_sales:real} list,
>      store:
>            {coffee_bar:bool, first_opened_date:string, florist:bool,
>             frozen_sqft:int, grocery_sqft:int, last_remodel_date:string,
>             meat_sqft:int, prepared_food:bool, region_id:int, salad_bar:bool,
>             store_city:string, store_country:string, store_fax:string,
>             store_id:int, store_manager:string, store_name:string,
>             store_number:int, store_phone:string, store_postal_code:string,
>             store_sqft:int, store_state:string, store_street_address:string,
>             store_type:string, video_store:bool} list,
>      store_ragged:
>                   {coffee_bar:bool, first_opened_date:string, florist:bool,
>                    frozen_sqft:int, grocery_sqft:int, last_remodel_date:string,
>                    meat_sqft:int, prepared_food:bool, region_id:int,
>                    salad_bar:bool, store_city:string, store_country:string,
>                    store_fax:string, store_id:int, store_manager:string,
>                    store_name:string, store_number:int, store_phone:string,
>                    store_postal_code:string, store_sqft:int,
>                    store_state:string, store_street_address:string,
>                    store_type:string, video_store:bool} list,
>      time_by_day:
>                  {day_of_month:int, fiscal_period:string, month_of_year:int,
>                   quarter:string, the_date:string, the_day:string,
>                   the_month:string, the_year:int, time_id:int,
>                   week_of_year:int} list,
>      warehouse:
>                {stores_id:int, wa_address1:string, wa_address2:string,
>                 wa_address3:string, wa_address4:string, warehouse_city:string,
>                 warehouse_class_id:int, warehouse_country:string,
>                 warehouse_fax:string, warehouse_id:int, warehouse_name:string,
>                 warehouse_owner_name:string, warehouse_phone:string,
>                 warehouse_postal_code:string, warehouse_state_province:string} list,
>      warehouse_class:{description:string, warehouse_class_id:int} list}
#days foodmart;
> val it = <relation> : {day:int, week_day:string} list

from d in scott.dept
where d.deptno elem (from e in scott.emp
                     where e.job elem ["ANALYST", "PRESIDENT"]
                     yield e.deptno);
> val it =
>   [{deptno=10,dname="ACCOUNTING",loc="NEW YORK"},
>    {deptno=20,dname="RESEARCH",loc="DALLAS"}]
>   : {deptno:int, dname:string, loc:string} list

from d in scott.dept
where d.deptno notelem (from e in scott.emp
                        where e.job notelem ["ANALYST", "PRESIDENT"]
                        yield e.deptno);
> val it = [{deptno=40,dname="OPERATIONS",loc="BOSTON"}]
>   : {deptno:int, dname:string, loc:string} list

(*) Functions
(*) Clerks get a 20% raise each year; everyone else gets 5%
val emp2 =
  from e in scott.emp
  yield {e.deptno, e.job, e.ename,
    salIn = fn year => e.sal
       + e.sal
         * (year - 2019.0)
         * if e.job = "CLERK" then 0.2 else 0.05};
> val emp2 =
>   [{deptno=20,ename="SMITH",job="CLERK",salIn=fn},
>    {deptno=30,ename="ALLEN",job="SALESMAN",salIn=fn},
>    {deptno=30,ename="WARD",job="SALESMAN",salIn=fn},
>    {deptno=20,ename="JONES",job="MANAGER",salIn=fn},
>    {deptno=30,ename="MARTIN",job="SALESMAN",salIn=fn},
>    {deptno=30,ename="BLAKE",job="MANAGER",salIn=fn},
>    {deptno=10,ename="CLARK",job="MANAGER",salIn=fn},
>    {deptno=20,ename="SCOTT",job="ANALYST",salIn=fn},
>    {deptno=10,ename="KING",job="PRESIDENT",salIn=fn},
>    {deptno=30,ename="TURNER",job="SALESMAN",salIn=fn},
>    {deptno=20,ename="ADAMS",job="CLERK",salIn=fn},
>    {deptno=30,ename="JAMES",job="CLERK",salIn=fn},
>    {deptno=20,ename="FORD",job="ANALYST",salIn=fn},
>    {deptno=10,ename="MILLER",job="CLERK",salIn=fn}]
>   : {deptno:int, ename:string, job:string, salIn:real -> real} list
from e in emp2
  yield {e.ename, e.job, salIn2020 = e.salIn 2020.0, salIn2021 = e.salIn 2021.0};
> val it =
>   [{ename="SMITH",job="CLERK",salIn2020=960.0,salIn2021=1120.0},
>    {ename="ALLEN",job="SALESMAN",salIn2020=1680.0,salIn2021=1760.0},
>    {ename="WARD",job="SALESMAN",salIn2020=1312.5,salIn2021=1375.0},
>    {ename="JONES",job="MANAGER",salIn2020=3123.75,salIn2021=3272.5},
>    {ename="MARTIN",job="SALESMAN",salIn2020=1312.5,salIn2021=1375.0},
>    {ename="BLAKE",job="MANAGER",salIn2020=2992.5,salIn2021=3135.0},
>    {ename="CLARK",job="MANAGER",salIn2020=2572.5,salIn2021=2695.0},
>    {ename="SCOTT",job="ANALYST",salIn2020=3150.0,salIn2021=3300.0},
>    {ename="KING",job="PRESIDENT",salIn2020=5250.0,salIn2021=5500.0},
>    {ename="TURNER",job="SALESMAN",salIn2020=1575.0,salIn2021=1650.0},
>    {ename="ADAMS",job="CLERK",salIn2020=1320.0,salIn2021=1540.0},
>    {ename="JAMES",job="CLERK",salIn2020=1140.0,salIn2021=1330.0},
>    {ename="FORD",job="ANALYST",salIn2020=3150.0,salIn2021=3300.0},
>    {ename="MILLER",job="CLERK",salIn2020=1560.0,salIn2021=1820.0}]
>   : {ename:string, job:string, salIn2020:real, salIn2021:real} list

"end";
> val it = "end" : string

(*) End foreign.smli