Building New SQL (2013) [pdf]

Identity is hard. If you assign an integer to every row, as the article suggests, you have to decide how that integer is effected by update operations. If the reference is by identity (ie the new row gets the same id) then you have to worry about stability of ids in materialised views (eg if some part of the view is recalculated how we decide whether the new rows are altered versions of the old rows or brand new rows). If the reference is by value (ie new rows always get new ids) then you will need some separate way of assigning identity to entities which don't have a natural primary key (eg users).

The data model I'm currently working on is an append-only event log with incrementally maintained views. Since we don't have to worry about update to the base rows I'm leaning towards having references be by value and relying on the maintenance algorithm to ensure that changed ids are propagated nicely. In cases where actual identities are required they will need to be supplied in the event log so that they are stable (eg user signup messages must contain enough unique information to create an id for that user).

I haven't been able to dig up much research in this area so I'm all ears if anyone has any thoughts.

The central problem with SQL is a lack of modularity and reusability. The syntax is easy to learn, it's just onerous to do complex things.

Dev teams and analysts use text files, Gists, and emails to save hundreds of useful queries containing similar patterns, subqueries, and clauses that could easily be repurposed if the language had any reusability features. There's a lot of waste and it can feel like a big task to ask a simple question.

That said, it's very easy to learn SQL and the author's suggestions don't seem to maintain that learning curve. I disagree that "human language mimicking" is a problem -- SQL syntax actually makes a lot of sense (I'm "SELECTing FROM a table and ORDERing the results" etc).

What we need is a higher level language or tool to describe relations in a database -- something like an ORM or XML used in legacy BI tools. SQL still lives underneath. IBM Cognos, SAP Business Objects, Microstrategy, and others tried this to varying levels of success. Another commenter (siganakis) gets it right: "I would prefer a syntax layer that can be compiled / transformed back to SQL."

We're actually working on something very similar to that at Looker (http://looker.com) if anyone is interested in hearing more about it, but we aren't quite at the point where we can release the language spec to the world yet.

I like the approach explained here:

http://www.try-alf.org/blog/2013-10-21-relations-as-first-cl...

Is something like:

requester_city = 'Paris' solution = suppliers

# 1). A supplier may only see information about the suppliers located # in the same city than himself. solution = restrict(solution, city: requester_city)

# 2) The supplier's `status` is sensitive and should not be displayed. solution = allbut(solution, [:status])

# 3). The country name must be displayed together with the supplier's city. solution = join(solution, cities)

The good idea is that is composable. You can query by all the solution =

I think that the biggest problem with SQL is more around the actual syntax of the language and how verbose it feels to write complicated queries.

I would prefer a syntax layer that can be compiled / transformed back to SQL but that does basic things like having a query start with the tables, then joins, then groupings then the final projection.

Also a less cumbersome way to use the "WITH" statement to form named sub-queries.

Perhaps something like:

    SELECT 
        COUNT(*) as columns,
        column_type,
        table_name
    FROM (
        SELECT  c.id, 
                c.type AS column_type,
                t.name AS table_name
        FROM tables t
        INNER JOIN columns c
        ON t.id = c.table_id
        WHERE t.system=false;
    ) a
    HAVING COUNT(*) > 1
    ORDER BY columns DESC

Being re-written as:

    # Use ":="  to replace WITH for named ephermal views
    # Replace "WHERE" with "?", "SELECT" with "|>" at the end
    non_system := tables 
        ? system=false 
        |> name:table_name, is:table_id

    # Replace INNER JOIN with "*="
    non_system_columns := non_system.table_id *= columns.table_id
        |> c.id, c.type:column_type

    # GROUP BY columns are automatically generated by non-aggregated columns
    column_types := non_system_columns
        |> COUNT(*):columns DESC, column_type, table_name

So the final query may look like:

    non_system := tables ? system=false 
        |> name:table_name, is:table_id
    non_system_columns := non_system.table_id *= columns.table_id
        |> c.id, c.type:column_type
    
    non_system_columns 
        |> COUNT(*):columns DESC, column_type, table_name

Any thoughts on this?

A new SQL is long overdue. Too many folks throw the baby out with the bath-water and lose the relational algebra that makes databases awesome, but I've been using enough LINQ to see that I really want more competition in the space of relational languages - there's a lot of room for improvement.

Maybe it's just the informal writing style and concentration on deficiencies in current implementations of SQL, but the whole things reads as if the author gave only a cursory glance at any relational model theory.

Aren't multi-table indexes otherwise known as indexable views?

Plus, I can't see any mention of how exactly the data is stored on disk. A lot of stuff about the language used to access it, but nothing about the storage which is 99% of any database engine. One of the purposes of a properly normalised RDBMS (eg. key tables/star schemas/etc) is to minimise the number of bytes on disk in order to optimise the slowest component - reading from disk.

I had similar ideas when I was in college. There are quite some projects trying to do similar things: http://htsql.org/

and my naive and outdated blog post from college http://dhruvb.com/blog/posts/specialized-syntax-for-quick-sq...

Implicit joins idea seems has already been expressed in SPARQL http://thefigtrees.net/lee/sw/sparql-faq#benefits

In my view, a good query language should support tool-free DSL-ization. Meaning that it should be easy to express data model entities in a DSL for a specific data model. I also think that a data architect should provide as one of the most important artifacts of his/her data model, a query DSL for the data in his/her model. This way the users of the data model will be using a type-safe DSL to get the data.

So to me the biggest limitation of SQL or even SPARQL or the authors' proposal in the referenced article -- is the lack of DSL construction features that can reflect a relational or graph oriented data model

Is this a joke? The language used certainly seems inadequate.

Other than that, aren't there already other "completely relational" systems like Dataphor (and some arcane IBM products) .. that failed to gain traction?

Is it allowed to put this on github and get people adapting and thinking about solutions to their issues and thoughts and changes.

Event adding more examples would get a good thing. Generally it looks pretty good to me though.

What's up with the line 'We like sex.' on page 12 under Arithmetic?