Yupeng Fu Kian Win Ong Kevin Zhao Yannis Papakonstantinou Vicky Papavasiliou Michalis Petropoulos

1. The FORWARD SQL-Based Declarative Framework for Ajax Applications Yupeng Fu Kian Win Ong Kevin Zhao YannisPapakonstantinou Vicky Papavasiliou Michalis Petropoulos

2. Vast Majority of Web Applications: Issue simple SQL commands, reflect new state on browser Browser Submit Web Application App server data in main memory INSERT Session Objects UPDATE SELECT Session Objects Session Objects Email DELETE Database Paypal charge

3. The Paradox Most web applications are fundamentally simple; yet their development requires large amounts of code,both server-side* and client-side (JavaScript) * Java, PHP, C#, Ruby etc.

4. Where is the extraneous code expended? Low-level code addressing frictions of the web application architectures • pre-2005: Pure server-side programming • post-2005: Ajax • post-2007: browser went mobile

5. Pre-2005: Pure server-side programming: Code to plumb across layers; code to optimize data access Code with JDBC/SQL calls to combine database and application-server data Code to combine database and application server data into HTML HTML DOM JavaScript Components HTML DOM • Data access optimization questions: • A single query that gets both proposals & reviews of reviewer currently in session • or a single “proposals” query followed by one “reviews” query for each proposal • or a single “proposals” query with one “reviews” query that lists all relevant proposal id’s Browser Application Server Action Page Computation Request Parameters Java Java INSERT INTO reviews … SELECT * FROM proposals… SELECT * FROM reviews … Session Objects Session Objects Session Objects Database Server Database 5

6. 2005: Enter Ajax: A desktop feel to cloud-based applications • Asynchronous partial update of the page • Fancy JavaScript/Ajax componentsmaps, calendars, tabbed windows

7. Ajax: Mundane data coordination betweenpage state and server state (app server + database) JavaScript Components JavaScript components have different programmatic interfaces JavaScript/Java code tuned to incrementally update HTML DOM & JavaScript components HTML DOM JavaScript/Java code reflects page data & request parameters to the server JavaScript collect_args(); response_handler = function(){ partial_renderer(); } send_request(); Diffs Browser Application Server Action Page Computation Request Parameters Java Java Delta Queries SELECT * FROM proposals… SELECT * FROM reviews … INSERT INTO reviews … Session Objects Session Objects Database Server Database 7

8. For each action, yet another incremental page computation code and partial renderer Pure Server-side Model Ajax Model Partial update requires different logic and imperative programming for each user action Partial Renderer 1 Partial Renderer 2 Partial Renderer 3 Browser Application Server Action 1 Page Computation Page Computation 1 Action 1 INSERT … INSERT … Action 2 Action 2 Page Computation 2 UPDATE … UPDATE … Action 3 Page Computation 3 Action 3 DELETE … DELETE … 8

9. Mobile, iPad & HTML5 • form factor • Client data & devices; geolocation • Slower & more unreliable connections • higher need for client autonomy & low latency • HTML5 back to web-based standardization browser-based database multithreaded asynchronous processing abilities

10. Action and page computation may have enough browser-side data to fully avoid server access HTML5: Data and computations at the browser.And yet another integration, coordinationexercise JavaScript Components HTML DOM Action Page Computation HTML5 storage Low latency: Browser-side computation returns immediately available data instantly, while asynch requests to the server initiated for reads or writes Browser Application Server Action Page Computation Action Page Computation Action Session Objects Session Objects Database Server Database 10

11. Goal of theFORWARD Web Application Programming Framework • Remove architectural frictions & manual optimizations • Plumbing code addressing language heterogeneities • Data coordination, optimization code • Novelty: SQL-based declarative programming • “Unified application state” virtual database, comprising entire application state accessible via SQL++ distributed queries • Application semantics: Action -> Page cycle • Ajax page as a rendered SQL++ view that is automatically and incrementally maintained at each round Actions and pages can use just SQL++, no Java or JavaScript • Make easy things easy while difficult things possible

12. FORWARD Application: Set of declarative configurations, interpreted in a location-transparent way Browser-side FORWARD JavaScript libraries JavaScript Components HTML DOM FORWARD Interpreter (e.g., http://forward.ucsd.edu ) FORWARD Applicationnsf_reviewing Action Configuration Page Configuration Page Configuration review Action Configurationsave_review Source Configurations & SQL++ Schema Definitions Unified SQL++ Application State

13. FORWARD Application:Operation Browser-side FORWARD JavaScript libraries JavaScript Components HTML DOM FORWARD Interpreter (e.g., http://forward.ucsd.edu ) FORWARD Applicationnsf_reviewing Action Configuration Page Configuration Page Configuration review-proposals Action Configurationinsert-review Source Configurations & SQL++ Schema Definitions Unified SQL++ Application State

14. Actions Access Unified Application State via SQL++ Browser-side FORWARD JavaScript libraries JavaScript Components HTML DOM Core page & request parameters: Abstraction of relevant part of browser state SQL++ includes: Nesting Variability Identity Ordering FORWARD Interpreter (e.g., http://forward.ucsd.edu ) FORWARD Applicationnsf_reviewing Action Configuration Action Configurationinsert-review Page Configuration Page Configuration review-proposals Source Configurations & SQL++ Schema Definitions Unified SQL++ Application State Core Page Session Objects Action Outputs Database Request Parameters

15. Actions in PL/SQL dialect over the Unified Application Statewhich access the main memory also (request, session) // Insert submitted review into reviews table of the hosted database INSERT INTOhosted.reviews(proposalId, comment, rating) VALUES (request.pid, request.myComment, request.myRating) Java Java actions

16. Pages as Rendered SQL++ Views over the Unified Application State Browser-side FORWARD JavaScript libraries JavaScript Components HTML DOM FORWARD Interpreter (e.g., http://forward.ucsd.edu ) FORWARD Applicationnsf_reviewing Action Configuration Page Configuration Page Configuration review-proposals Action Configurationinsert-review Source Configurations & SQL++ Schema Definitions Unified SQL++ Application State

17. Page Configuration Follows Popular Frameworks:HTML + FORWARD statements & expressions, using SQL++ <fstmt:for query=" // proposals assigned to // currently logged-in reviewer SELECT p.id AS pid, p.title FROMhosted.proposals AS p WHERE EXISTS( SELECT * FROM hosted.assignments AS a WHEREa.proposal = p.id AND a.reviewer= session.user"> <tr> ... <td>{pid}</td> ... </tr> </fstmt:for> Overall structure reminiscent of XQuery and SQL/XML 17

18. Page Configuration: Ajax/JavaScript Components Simply by FORWARD Unit Tags <fstmt:for query=" SELECT ..."> <tr> ... <td> <funit:bar_chart> <bars> <fstmt:for query="SELECT …"> <bar> <id> {gid} </id> <value> {score} </value> </bar> </fstmt:for> </bars> </funit:bar_chart> </td> ... After each action, the FORWARD interpreter automatically & incrementally updates HTML and JavaScript components 18

19. Core Page and Request Parameters: Mirror of named page data <fstmt:forname="proposals" query=" SELECT p.id AS pid, p.title FROMhosted.proposals AS p WHERE ..."> <tr> ... <funit:editorname="myReview"> ... </funit:editor> ... <funit:selectname="myRating“ type=“integer”> ... </funit:select> ... <funit:button onclick="save_review"/> </tr> </fstmt:for> Core Page proposals Automatically inferred or explicitly declared

20. FORWARD Demo

21. System Architecture and Optimizations Browser State HTML DOM JavaScript Components Visual Page State Incremental rendering of JavaScript components FORWARD Interpreter 1 4 Argument Collectors Partial Renderers 2 Program Invoker Browser Server Page Configuration Program Email, etc. Service Visual Units Incremental view maintenance of data using modification log INSERT, UPDATE, DELETE Services Page Query in SQL++ 3 Incremental View Maintenance Modification Log Unified Application State Visual Page State = Page Query + Constants (visual template) Database State Session State Page State Core Data Request Data

22. Reduction of web programming problems into data management problems with rich prior work • Incremental Ajax page maintenance • Resolving data heterogeneities between the logic visual layers of the application • Query optimization for distributed queries over small main memory sources and a persistent database • Location transparent code development and low latency optimizations for mobile

23. Incremental Ajax maintenance HTML + Javascript State • Use incremental view maintenance as an efficient implementation of the page queries • Data model features • Nesting (decorrelation) • Variability (decorrelation) • Ordering (ordering attributes) • Deferred view maintenance • Large number of small view instances • Deletions & updates as commands that utilize provenance IDs Partial renderers of visual units Page Configuration Visual Units Page Query in SQL++ Incremental View Maintenance of Page Query

24. Incremental Rendering of JavaScript Components Visual units are provided by framework, and have renderers that call into methods of JavaScript components. BarChartUnit Insert Bar Chart new bar_chart(); ... barchart Browser State Delete Bar Chart bar Tuple Diffs bar_chart.destroy(); ... Render HTML DOM JavaScript Components Int value id Int Replace Bar 3 5 bar_chart.getBar(id); bar_chart.replace(...); ... Find most specific renderer for unit diff

25. Incremental Rendering of JavaScript Components BarChartUnit Insert Bar Chart new bar_chart(); ... Browser State Delete Bar Chart bar Tuple Diffs bar_chart.destroy(); ... Render HTML DOM JavaScript Components Int value id Int Simulate renderers when necessary 3 5

26. Resolving heterogeneities between logical and visual aspects of the page Report attribute types and logical structure dictated by page query Input attribute types dictated by input units Configuration Page (logical abstraction of page) c2v q2c Output of Page Query Visual Page v2c Types dictated by input units c2c Visually typed Core Page Atomic types dictated by what developer wants; validation errors c2t Typed core page t2r Request

27. Query optimization for distributed queries over small main memory sources and a persistent database Replacing the manual specification of data access optimizations that one does In Ruby-on-Rails, Hibernate Action (PL/SQL) Distributed Queries Page Configuration Page query Unified SQL++ Application State Core Page Action (PL/SQL) Variables Session Objects Database Request Parameters

28. Related Industrial Work on Removing Cross-Layer Frictions JavaScript Components HTML DOM ASP.NET, GWT Browser ? Application server Java (C#) Browser Mirror Java (C#) Objects Database Mirror Java (C#) Objects App Server Objects Hibernate, Entity Framework Database server SQL Data

29. Data management research community efforts: A progression towards ever more powerful declarative web application specs • Strudel • HTML pages as rendered views • WebML • From schemas to forms and reports, easily • Relational transducer • Hinted that the business logic of many web applications is captured within SQL • HILDA • Unit-based pages & logic • Client-server computation

30. Future work • More on algorithms that emulate what “a good developer would do” • Client side computation • enabled by declarativeness • location transparency • deliver low latency, disconnected operation • Optimizations for updating a myriad views • leveraging pub-sub works • Visual Do-It-Yourself development