# bidirectional spreadsheet formulas - alfa.di. nfmmacedo/talks/ bidirectional spreadsheet

Post on 28-Aug-2018

223 views

Embed Size (px)

TRANSCRIPT

Bidirectional Spreadsheet Formulas

Nuno Macedo Hugo Pacheco Nuno Sousa Alcino Cunha

VL/HCC 2014August 1, Melbourne, Australia

Introduction Framework Synthesis Conclusions

Spreadsheet Formulas

spreadsheet formulas define computations and are at the coreof spreadsheets;

allow the calculation of values at an target cell from a set ofsource cells.

A B C = A + B10 5 15

Nuno Macedo, Hugo Pacheco, Nuno Sousa, Alcino Cunha 2 / 21

Introduction Framework Synthesis Conclusions

Spreadsheet Formulas

spreadsheet formulas define computations and are at the coreof spreadsheets;

allow the calculation of values at an target cell from a set ofsource cells.

A B C = A + B10 10 15

Nuno Macedo, Hugo Pacheco, Nuno Sousa, Alcino Cunha 2 / 21

Introduction Framework Synthesis Conclusions

Spreadsheet Formulas

spreadsheet formulas define computations and are at the coreof spreadsheets;

allow the calculation of values at an target cell from a set ofsource cells.

A B C = A + B10 10 20

Nuno Macedo, Hugo Pacheco, Nuno Sousa, Alcino Cunha 2 / 21

Introduction Framework Synthesis Conclusions

Spreadsheet Formulas

in some scenarios it is helpful to update the target cell andhave the changes reflected in the source cells

interchangeable formats; reverse formulas; fix errors; what-if scenarios;

A B C = A + B10 5 15

not supported natively by spreadsheet systems.

Nuno Macedo, Hugo Pacheco, Nuno Sousa, Alcino Cunha 3 / 21

Introduction Framework Synthesis Conclusions

Spreadsheet Formulas

in some scenarios it is helpful to update the target cell andhave the changes reflected in the source cells

interchangeable formats; reverse formulas; fix errors; what-if scenarios;

A B C = A + B10 5 20

not supported natively by spreadsheet systems.

Nuno Macedo, Hugo Pacheco, Nuno Sousa, Alcino Cunha 3 / 21

Introduction Framework Synthesis Conclusions

Spreadsheet Formulas

in some scenarios it is helpful to update the target cell andhave the changes reflected in the source cells

interchangeable formats; reverse formulas; fix errors; what-if scenarios;

A B C = A + B10 10 20

not supported natively by spreadsheet systems.

Nuno Macedo, Hugo Pacheco, Nuno Sousa, Alcino Cunha 3 / 21

Introduction Framework Synthesis Conclusions

Example

profit forecasting example:=D2+E2

=#F2*G2-G2

=IF(H2>0;#H2;"Loss")=RIGHT(#B2;4)

Nuno Macedo, Hugo Pacheco, Nuno Sousa, Alcino Cunha 4 / 21

Introduction Framework Synthesis Conclusions

Design Principles

spreadsheets are widely used by non-proficient users; focus on seamless integration:

intuitive to traditional users; conservative as to not affect standard behavior; transparent to be predictable to the user.

Nuno Macedo, Hugo Pacheco, Nuno Sousa, Alcino Cunha 5 / 21

Introduction Framework Synthesis Conclusions

Challenges

formulas are typically non-injective; multiple ways to define backward transformations; to uphold the previous principles the user should be able tocontrol and inspect the chosen one.

Nuno Macedo, Hugo Pacheco, Nuno Sousa, Alcino Cunha 6 / 21

Introduction Framework Synthesis Conclusions

Bidirectional Transformation

data transformation abounds in software engineering; often performed in both ways: maintainability problem; extensive work has been done on bidirectional transformation; a single artifact specifies both forward and backwardtransformations.

Nuno Macedo, Hugo Pacheco, Nuno Sousa, Alcino Cunha 7 / 21

Introduction Framework Synthesis Conclusions

Lenses

lenses are one of the most popular approaches; designed for asymmetrical scenarios;

A Bget

update

get (put (v , s)) = v (Acceptable)put (get s, s) = s (Stable)

the transformation get entails the putback put.

Nuno Macedo, Hugo Pacheco, Nuno Sousa, Alcino Cunha 8 / 21

Introduction Framework Synthesis Conclusions

Lenses

lenses are one of the most popular approaches; designed for asymmetrical scenarios;

A B

B'

get

update

get (put (v , s)) = v (Acceptable)put (get s, s) = s (Stable)

the transformation get entails the putback put.

Nuno Macedo, Hugo Pacheco, Nuno Sousa, Alcino Cunha 8 / 21

Introduction Framework Synthesis Conclusions

Lenses

lenses are one of the most popular approaches; designed for asymmetrical scenarios;

A B

B'A'

get

put

update

get (put (v , s)) = v (Acceptable)put (get s, s) = s (Stable)

the transformation get entails the putback put.

Nuno Macedo, Hugo Pacheco, Nuno Sousa, Alcino Cunha 8 / 21

Introduction Framework Synthesis Conclusions

Lenses

lenses are one of the most popular approaches; designed for asymmetrical scenarios;

A B

B'A'

get

put

update

get (put (v , s)) = v (Acceptable)put (get s, s) = s (Stable)

the transformation get entails the putback put.

Nuno Macedo, Hugo Pacheco, Nuno Sousa, Alcino Cunha 8 / 21

Introduction Framework Synthesis Conclusions

Online Setting

source cell updates automatically trigger target updates; target updates should also trigger updates on source cells; no cyclic dependencies; system converges to consistent state:

Acceptable: after update propagation no forwardcomputation is required;

Stable: no update means no forward computation.

Nuno Macedo, Hugo Pacheco, Nuno Sousa, Alcino Cunha 9 / 21

Introduction Framework Synthesis Conclusions

Conservative Updating

user controls which cells are susceptible to updates; #-marks on source cells: C = #A + B; example: changes on profit shall not alter production costs; conservative: no # marks result in standard behavior.

Nuno Macedo, Hugo Pacheco, Nuno Sousa, Alcino Cunha 10 / 21

Introduction Framework Synthesis Conclusions

Scheme

spreadsheet formula f : A1 ... An B ; for each #-marked cell, a putback is generated; behavior depends on the set of marked cells.

Nuno Macedo, Hugo Pacheco, Nuno Sousa, Alcino Cunha 11 / 21

Introduction Framework Synthesis Conclusions

Example: Addition

B = #A1 + A2

put+A2 : B (A1 A2) A1put+A2 (b, (a1, a2)) = b a2

B = #A1 +#A2

put+ : B (A1 A2) A1put+ (b, (a1, a2)) = b / 2put+ : B (A1 A2) A2put+ (b, (a1, a2)) = b / 2

Nuno Macedo, Hugo Pacheco, Nuno Sousa, Alcino Cunha 12 / 21

Introduction Framework Synthesis Conclusions

Example: Length

B = LEN(#A)

putLEN () : B A AputLEN () (b, a) = if b 6 LEN (a) then LEFT (a, b)

else a & REPEAT ("A", b LEN (a))

Nuno Macedo, Hugo Pacheco, Nuno Sousa, Alcino Cunha 13 / 21

Introduction Framework Synthesis Conclusions

Formula Chaining

nested formulas (B = g (f (A))) are assumed to bedecomposed into chains (B = g (X ),X = f (A));

composition relies on spreadsheets reactive nature;

A B C = LEN(#B) D = A + #C10 hello 5 15

some restrictions needed to be sound: dependencies must forma tree whose nodes are value cells.

Nuno Macedo, Hugo Pacheco, Nuno Sousa, Alcino Cunha 14 / 21

Introduction Framework Synthesis Conclusions

Formula Chaining

nested formulas (B = g (f (A))) are assumed to bedecomposed into chains (B = g (X ),X = f (A));

composition relies on spreadsheets reactive nature;

A B C = LEN(#B) D = A + #C10 hello 5 12

some restrictions needed to be sound: dependencies must forma tree whose nodes are value cells.

Nuno Macedo, Hugo Pacheco, Nuno Sousa, Alcino Cunha 14 / 21

Introduction Framework Synthesis Conclusions

Formula Chaining

nested formulas (B = g (f (A))) are assumed to bedecomposed into chains (B = g (X ),X = f (A));

composition relies on spreadsheets reactive nature;

A B C = LEN(#B) D = A + #C10 hello 2 12

some restrictions needed to be sound: dependencies must forma tree whose nodes are value cells.

Nuno Macedo, Hugo Pacheco, Nuno Sousa, Alcino Cunha 14 / 21

Introduction Framework Synthesis Conclusions

Formula Chaining

nested formulas (B = g (f (A))) are assumed to bedecomposed into chains (B = g (X ),X = f (A));

composition relies on spreadsheets reactive nature;

A B C = LEN(#B) D = A + #C10 he 2 12

some restrictions needed to be sound: dependencies must forma tree whose nodes are value cells.

Nuno Macedo, Hugo Pacheco, Nuno Sousa, Alcino Cunha 14 / 21

Introduction Framework Synthesis Conclusions

Invariants: Range

formulas are not surjective; updates to values outside the range render the systeminconsistent;

A B C = LEN(#B) D = A + #C10 hello -5 5

unlike unidirectional scenario, the user may not be aware ofthe range of the formulas;

aggravated by chains of formulas.

Nuno Macedo, Hugo Pacheco, Nuno Sousa, Alcino Cunha 15 / 21

Introduction Framework Synthesis Conclusions

Invariants: User

the user may wish to further control update propagations; may introduce constraints into (source) cells; must be propagated to target cells in order to restrict updates.

A B C = LEN(#B) D = #A + #C> 0 > 0 > 00 0 0

Nuno Macedo, Hugo Pacheco, Nuno Sousa, Alcino Cunha 16 / 21

Introduction Framework Synthesis Conclusions

Invariant Language

tradeoff between expressiveness and effective manipulation; should be propagated throug