Create views for often - used expressions

[bendlas]

I'm thinking of a defview form, that gets a cql table, like

(defview customer-sales
  (-> (table :customer)
      (join (table :sales) (where (= :sales.customer :customer.id)))))

It creates a view customer_sales_view in the DB with the compiled sql and sets a :view "customer_sales_view" key on the cql table. If the view already exists, it checks whether the definition of the view equals the compiled sql.

When you use a table with a :view key in an expression, or deref directly, the compiler uses the precreated database view in the generated sql.

Pros

• efficient: CQL only needs to compile once, DB Server only needs to parse and compile once, DB Server can pre-optimize
• explicit: It gives visibility to the main building blocks in a given piece of data layer code
• familiar: It supports the workflow, DB programmers are used to. Using views for reused subexpressions is regarded a sign of good database engineering.
• reduces need for automatic memoizing of compiled expressions

Cons

• first DDL statement in ClojureQL
• preexisting views might get overwritten, maybe add a special token to generated view names
• not all db implementations might handle views equally efficient

The first one is the biggie. I think it's best to punt the horrid issues of automatic schema upgrade, by refusing to automatically replace a view, if it's different to the one we want to intern. Yes, this means we need to compare two token streams in defview.

---

What do you think of it?

[ghost]

+1, it's a worthwhile idea