Enhancing the sql interfacade Evaluation conventions

Enhancing the SQL Interfacade

• Evaluation conventions

• Implications for software design

• A possible solution

Sources for SQLEDDI

• The SQLEDDI interpreter was developed for CS233

• Consult worksheets 6-7 on the CS233 website for:

• details of how to run / download eden + SQL0/EDDI

• pointers to documentation for EDDI and SQL0

• These slides are essentially the file BeyondSQL0.ppt

• You are advised to study worksheets 6 and 7 - they

• supply the background for the CS319 lectures on SQL.

Interface or Interfacade?

• Have an SQL0 interface serving as an SQL ‘interfacade’

• SQL0 interface : decent interface to EDDI + not SQL

• SQL interfacade : real SQL + obscure interface to EDDI

• Improving the link involves

• understanding how SQL is related to relational theory

• exposing the ugly and complex semantics of SQL

Evaluation Conventions 1

• Three evaluation conventions in EDDI

• no multiple rows

• strict type checking on domains and attributes

• use of natural join

• Can change these via the Uneddifying Interface

• See Worksheet 6 Questions 3-6 for illustration

Evaluation Conventions 2

• Standard SQL violates all three evaluation conventions:

• allows duplicate rows - implements two types of selection: SELECT DISTINCT and SELECT

• dispenses with type checking on attributes

• uses “unnatural” join

• Issue: How to implement standard SQL using EDDI?

The Uneddifying Interface

• Worksheet 6 questions 3-6 expose many relevant issues by exercising The Uneddifying Interface:

• issues for multiple rows and for the implementation of SELECT DISTINCT / SELECT

• implications of more liberal type checking

• problematic aspects of unnatural join

• Summarise these in turn ...

Multiple rows

• There is no syntactic support in EDDI for distinguishing SELECT from SELECT DISTINCT: ‘distinct’ refers to the evaluation context not the query

• There is no support in EDDI for relations that are anything other than sets of tuples: is the standard SQL intention to have multisets of tuples?

• “EDDI allows multiple rows” is not an invariant assertion about the contents of relation tables: multiplicity once introduced is not eliminated

Loose type checking

• Loose type checking raises the key issue:

• What is meant by the value of a relation?

• EDDI : [name, price, qnt] are part of the value of ‘apple’

• The set of tuple values alone doesn’t define the relation

• Are X+Y and Y+X the same relation if they are only compatible wrt domains not wrt attributes?

• If YES, there are unpleasant implications ...

Unnatural join

• Consider the unnatural join: ‘apple * allfruits’

• Joining [name, price, qnt] and [name, begin, end] leads to [name_1, price, qnt, name_2, begin, end]

• Issue: is name_1 of the same type as name? etc

• To implement unnatural join as an algebraic operator would like to ensure e.g. (X*Y)*Z  X*(Y*Z), or at very least that the two expressions are union-compatible

• Issue: how to process the attribute names?

It’s no longer pure algebra

• Implications of loose type checking and unnatural join:

• values of algebraic expressions aren’t definable in a context-dependent way e.g. can’t determine how to name attributes without considering other issues, such as the context and the order of evaluation

• laws of relational algebra and substitution properties that we expect of pure algebra (e.g. if X=3, then X*2 is the same thing as 3*2 = 6) no longer operate

• Practical implications for implementing standard SQL?

Implementing standard SQL?

• The ‘obvious’ implementation strategy is

• - build an EDDI core intepreter with unnatural join

• - translate SELECT * FROM R, S into ?R*S;

• This FAILS because of the difficulty of matching

• - the attribute names generated by an unnatural join

• with

• - the attribute names associated with an SQL query

Implementing standard SQL ...

• Consider query such as

• SELECT DISTINCT apple.name, A.name FROM allfruits A, apple, allfruits B WHERE B.name<>'granny' AND A.name<>B.name;

• Table tagging (‘allfruits A’) and attribute annotation (‘apple.name’) are necessary syntactic features to support unnatural join

A possible implementation 1

• Unnatural join is implemented by referring to the FROM:

• “FROM allfruits A, apple, allfruits B …”

• Associated attributes are

• [name,begin,end], [name,price,qnt], [name,begin,end]

• Must disambiguate wherever natural join might operate:

• name  A.name, apple.name, B.name

• begin  A.begin, B.begin

• Will assume that the SQL query is formulated so that this disambiguation is precisely what is essential: i.e.

• tag a table only if it shares an attribute with other table

A possible implementation 2

• Now construct the natural join of the relations to be joined with attributes appropriately renamed:

• For instance:

• “FROM allfruits A, apple, allfruits B …”

• translates to the natural join of the three relations

• derived from allfruits, apple, allfruits resp. by renaming:

• name >> A_name, begin >> A_begin, end >> A_end

• name >> apple_name, price, qnt

• name >> B_name, begin >> B_begin, end >> B_end

• Translate attribute references as A_begin, apple_name

A possible implementation 3

• To deal with the distinction between SELECT and SELECT DISTINCT, introduce a pseudo relational operator “Makedistinct” to convert a multiset of tuples into a set of tuples.

• This makes it possible to use queries such as that framed above in conjunction with view creation, as in

• CREATE VIEW XXDIST AS (SELECT DISTINCT apple.name, A.name FROM allfruits A, apple, allfruits B WHERE B.name<>'granny' AND A.name<>B.name);

THE EDDI TRANSLATION OF THE SQL QUERY:

• THE EDDI TRANSLATION OF THE SQL QUERY:

• SELECT DISTINCT apple.name, A.name FROM allfruits A, apple, allfruits B WHERE B.name<>'granny' AND A.name<>B.name

• %eddi

• A_1 is allfruits % name >> A_name, begin >> A_begin, end >> A_end;

• %eddi

• apple_1 is apple % name >> apple_name, price, qnt;

• %eddi

• B_1 is allfruits % name >> B_name, begin >> B_begin, end >> B_end;

• %eddi

• expr_1 is ((A_1 * apple_1 * B_1) : B_name != "granny" . (A_1 * apple_1 * B_1) :

• A_name != B_name) % apple_name, A_name

• %eddi

• distexpr_1 is Makedistinct(expr_1);

• %eddi

• ?(distexpr_1);

The SQLEDDI translator

• A variant of the SQLEDDI environment that supports the translation of a richer subset of standard SQL according to the conventions outlined above can be derived from the basic SQL0 interface by modifying the SQL_to_EDDI translator and setting the evaluation conventions for the EDDI interpreter appropriately. The resulting ‘toysql’ interpreter can be studied by invoking tkeden with the startup configurations Run1.eden / Run4.eden in the ~empublic/projects/sqleddiBeynon2002 directory