dataunitylab / relational-playground Goto Github PK

Right now there isn't an explicit choice on whether expressions are evaluated using set or bag semantics. It may be helpful to have this be an option and it would likely also help support #3.

Data should be randomly generated with more columns and rows and possibly additional relations.

Show to use the system with some documentation or possibly a tour.

Some queries, such as the one below, don't properly highlight the specific expression whose data will be displayed below. The title displayed at the top of the table is also sometimes incorrect.

SELECT * FROM Doctor JOIN Patient ON Doctor.id=Patient.primaryDoctor WHERE Doctor.salary > 150000

This is one step towards #3. Sorting is probably the easiest operator to implement next. Sort is written as τ with a list of columns to sort by as a subscript. Keep in mind that ascending order is the default in SQL. I would also suggest including a small arrow next to each column to indicate the sort order (i.e. ↑ and ↓)

This consists of two parts:

1. Some way of calculating the order each expression will be executed and the number of steps. (The easiest way is probably to actually execute the expression and record steps.)
2. Controls to step forward and backward through each execution step. This should just change which expression is highlighted using the existing highlighting mechanism.

Note that users should still be able to click on expressions to see how they will be evaluated. In this case, the current step in query execution should be synchronized with whatever expression has been clicked.

Unfortunately this requires modification to the parser library, but I've implemented them in JavaScriptor/js-sql-parser#25. I can release a version of this and then the changes here shouldn't be too painful.

Currently clicking on some relations directly does nothing and selecting some expressions (e.g. joins is challenging). Since the relations can be viewed on the right, instead the expression the relation is contained in should be used.

The tool could also be useful for explaining some simple query optimizations. For example, pushing a filter through a join. This would probably best take the form of a series of optimization rules which can be toggled on and off with some clear way to see how the expression changes.

There are many other types of operations that can be expressed in relational algebra. This should probably end up behind a toggle since ordering requires lists which changes semantics. An example are those below:

• Sorting
• Aggregation
• Extended projection (expressions)
• Duplicate elimination

When an expression is selected and the user types a new query, the expression should be deselected when the new relational algebra expression appears.

This is low priority, but contexts in React are a nice way of making values like the ReactGA object which are essentially "global" available throughout the app without having to pass props around everywhere.

This package is quite large and is only used for the drop-down menu when selecting tables. It should be fairly straightforward to replace it with a native <select> element.

SQL queries such as SELECT d.id FROM Doctor AS d should be supported. While the SQL parser supports this, there is currently no support in the relational algebra or query execution for having tables with different names.

This should help avoid bugs when state is mistakenly reused and mutated later.

Examples of useful libraries:

https://github.com/immutable-js/immutable-js
https://github.com/kitten/extendable-immutable
https://github.com/immerjs/immer

The query that the user has typed should be stored in the URL so that it can be bookmarked or shared. When a page is loaded, the URL should be checked for a query and the query populated if needed.

Currently, only unary operators are supported. Extending support for binary operators will allow for things like joins, set difference/union, etc. The first one to be implemented should probably be cross-product since it's likely to be the most generally useful.

react-table has gone through a lot of updates. As can be seen in #1354, it's not possible to upgrade further without some significant changes.

Currently only AND is supported, but OR should be as well. This will require looking at the conditions in the parsed query and deciding on a structure to represent the expression. This structure would then be used to display the relational algebra expression as well as evaluate the query when the expression is selected.

It's important to understand how students are interacting with the tool. Below are examples of a few things that we may want to log.

• Which datasets are commonly used
• Queries entered
• What types of expressions are clicked on
• What browsing features of tables are used

This is just for fun, but use-dark-mode looks like an easy way to do this perhaps with react-dark-mode-toggle-2.

Let's try having the option to visualize the relational algebra expressions as a tree. Something like Treant might be helpful here.