Problem
I am quite tired of writing SQL that isn't modular or easy. There are no variables or ways to combine them like functions or variables. Is there no a SASS or LESS where writing CSS was a bit more modular or dynamic? I am not asking for a miracle replacement for SQL, merely a language or library that compiles to SQL which would make it easier for everyone to write it without too much pain.
Sketch
This is what I want to talk about, another language or library. My
idea is to implement this in a functional language such as Haskell
or implement it with the prototyping Python
or Javascript
or
Common Lisp
to make it easy for me. I do want this language to have
a lot of editor support like Emacs
, so in Emacs Lisp
I thought to
implement this. Ease of writing is one key point so choosing which
language will serve as a prototype is important.
Of course, I can create a syntax or grammar for this but it would be better to see how it plays as a library first. So let's role with that without being grandeur. However, I do want to stress being functional in a sense where there is no side-effect or the fact that I am being obsessed with it.
So let's start with some sketches.
Snippets
Let's start with hypothetical use case: create two tables and join
their there fields. Let say we Person
and Relationship
which is a
table of people and their relationship to each other respectively. How
about a quick schema to make this clearer.
- person
- personid
- firstname
- lastname
- birthdate
- relationship
- personid
- relatedpersonid
Please ignore the fact that I hate writing in all caps as SQL does. Let's assume the compiler also converts the naming convention to it's proper name. Moving on, let's create a pseudo syntax in creating these tables. I'll be sketching in Python because it is easy.
birth_date = Field('birth_date') # Short constructor, positional person = Table.make( # Long constructor, keywords name = 'person', alias = 'p', # For later fields = [ Field.make( name = 'person_id' ), Field('last_name'), Field('first_date'), birth_date ] ) relationship = Table( name = 'person_relationship', alias = 'pr', fields = [ Field.make('person_id'), Field.make('related_person_id') ] )
I hope the snippet is readable enough and the ideas here are gotten across
- Variables
- Field and Table data type
- Constructor function
- Function keywords
- Strings
- Quick constructor with strings
- Flatten list of lists(Wish list)
So our next step is to create the query for each one. Let's see.
print(Query.craft(person)) # Crafting from a normal table # out >> select p.person_id, p.last_name, p.first_name, p.birth_date from person p print(Query.craft(relationship)) # likewise # out >> select pr.person_id, pr.related_person_id from person_relationship pr all_person = Query.make( name = 'everyone', # Name functions as an id tables = [ person ] ) print(Query.craft(all_person)) # ditto people_relationships = Query.make( name = 'everyelse', tables = [ person, relationship ] ) print(Query.craft(people_relationships)) # Outer join # out >> # select # p.person_id, p.last_name, p.first_name, p.birth_date, # pr.person_id, pr.related_person_id # from # person p, person_relationship pr # Astute readers will realize that there are two person_ids here which will fail person_relations = Link.make( # A way to refactor ON clauses name = 'p_and_pr', source = Table.getField('person_id', person), # Still experimental target = Table.getField('person_id', people_relationships) ) relations_person = Link.make( name = 'pr_and_p', source = people_relationships, target = person ) everyone = Query.make( name = 'everyone', table = [ person, relationship, Table.setAlias('np', person) ], link = [ person_relations, relations_person ] ) print(Query.craft(everyone)) # out >> # select # * # from # person p # inner join person_relations pr on p.person_ id = pr.person_id # inner join person np on np.person_id = pr.related_person_id
I hope I got my point across with the following ideas.
- Query and Link data type
- .craft() function
- Mutation creates another copy
Just a quaint idea to merge these two queries but I still have some
problems specially with the Link
type. I want the tables and the
join clause to be separated. Ideally, you want to declare the tables
only and the join clauses can automatically inferred but obviously it
is more verbose at the moment and harder to join.
Assuming this works, the idea is now to declare the tables on the top, create a auto link mechanism, select the fields and make them queries. After that base is okay, having Emacs support them would be awesome.
Insights
Assuming this is works, the library should allow for extension and
expansion. Given the base types, one can create new personalized types
from it. With the vast majority of SQL vendor syntax, the language
should allow not just to compile to SQL but to the vendor specific SQL
and if willing compiles to an XML file of tables and automatic queries
just for the MyBatis
or for some third party dynamic SQL shiznit.
I may work on this on Haskell
as my first project but I really want
to stress the point that SQL should not be a pain to write. Anyway,
I'm out of ideas anyway but this will haunt me until I implement this properly.