mpenet / hayt

CQL3 DSL for Clojure

Hayt

CQL3 DSL for Clojure.

What?

Hayt is a thin query DSL for CQL.

It works in 2 simple steps, first the query api generates a map (AST) representation of the query, allowing you to compose/modify it at will, then there's a compilation step that will generate a raw string for you to use in prepared statements or normal queries.

Both are decoupled, so you can just use the AST directly and make your own api on top of it if that's what you like.

What's in the box?

• Complete CQL 3.1.1+ coverage including some features in Cassandra trunk, DDL, CQL Functions, counter , triggers and collections operations
• Support for both Raw queries and Prepared Statements generation
• Great performance (lots of transducing and fiddling with StringBuilder)
• Extensive test coverage
• Decoupled query compiler, allowing you to build your own DSL in minutes
• Highly composable using simple maps or the functions provided
• Extensible Clojure data types support
• Constantly kept up to date with Cassandra changes, almost daily
• No (exposed) macros

Installation

Please check the Changelog first if you are upgrading.

Note: while in beta the API is still subject to changes.

Usage

This should be familiar if you know Korma or ClojureQL. One of the major differences is that Hayt doesn't expose macros.

(use 'qbits.hayt)

(select :foo
        (where {:bar 2}))

(update :some-table
         (set-columns {:bar 1
                       :baz (inc-by 2)})
         (where [[= :foo :bar]
                 [> :moo 3]
                 [:> :meh 4]
                 [:in :baz [5 6 7]]]))

All these functions do is generate maps, if you want to build your own DSL on top of it or use maps directly, feel free to do so.

(select :users
        (columns :a :b)
        (where [[= :foo :bar]
                [> :moo 3]
                [:> :meh 4]
                [:in :baz [5 6 7]]])})

;; generates the following

>> {:select :users
    :columns [:a :b]
    :where [[= :foo :bar]
            [> :moo 3]
            [:> :meh 4]
            [:in :baz [5 6 7]]]}

Since Queries are just maps they are composable using the usual merge into assoc etc.

(def base (select :foo (where {:foo 1})))

(merge base
       (columns :bar :baz)
       (where {:bar 2})
       (order-by [:bar :asc])
       (using :ttl 10000))

To compile the queries just use ->raw

(->raw (select :foo))
> "SELECT * FROM foo;"


(->raw {:select :foo :columns [:a :b]})
> "SELECT a, b FROM foo;"

;; or if you want full control of what's prepared you can use
;; `cql-raw` with ->raw compilation:


;; here `?` is an alias to `(cql-raw "?")`

(->raw (select :foo (where {:bar 1 :baz ?})))
> "SELECT * FROM foo WHERE bar = 1 AND baz = ?;"


;; and named parameters using keywords

(->raw (select :foo (where {:bar 1 :baz :named)}))
> "SELECT * FROM foo WHERE bar = 1 AND baz = :named;"

When compiling with ->raw we take care of the encoding/escaping required by CQL. This process is also open via the qbits.hayt.CQLEntities protocol for both values and identifiers. We also supply a joda-time codec for you to use if you require to in qbits.hayt.codec.joda-time. This codec is a good example of how to handle custom type encoding.

If you are curious about what else it can do, head to the codox documentation or the tests.

License

Copyright @Max Penet

Distributed under the Eclipse Public License, the same as Clojure.

hayt's Issues

Materialized views

Thanks for hayt & alia!

It seems Materialized views are missing so far.

IF EXISTS for Delete statement

:if-exists is not included for :delete in qbits.hayt.cql, but it is part of the spec. Reference:
http://docs.datastax.com/en/cql/3.1/cql/cql_reference/delete_r.html

Happy to do a PR if you like, but it is just 10 characters.

Date/time range queries

This is a question and not an issue.

I don't see any realistic examples of queries that use dates in the test suite. I'm after
something like

(where :ts [> date1] [<= date2])

However, there seem to be a date formatting issue:

(select "calculations" (where :time [> '2013-01-01 00:00:00+0200']) (allow-filtering))

NumberFormatException Invalid number: 2013-01-01  clojure.lang.LispReader.readNumber (LispReader.java:258)
NumberFormatException Invalid number: 00:00:00+0200  clojure.lang.LispReader.readNumber (LispReader.java:258)
RuntimeException Unmatched delimiter: ]  clojure.lang.Util.runtimeException (Util.java:219)
RuntimeException Unmatched delimiter: )  clojure.lang.Util.runtimeException (Util.java:219)
{:allow-filtering true}RuntimeException Unmatched delimiter: )  clojure.lang.Util.runtimeException (Util.java:219)

Adding Group By clause

Are you interested in adding Group By to hayt? I would be happy to work on a PR, if your interested.

flatland/update & clojure 1.7 issue

So this might be more a PSA than a bug report - and ill resist the temptation to raise a bug report for the use of flatland :) - but hayt use of flatland mean's I couldn't compile under 1.7.0-alpha2.

Caused by:
IllegalAccessError update does not exist
clojure.core/refer (core.clj:4058)
...
clojure.core/use (core.clj:5773)
flatland.useful.datatypes/loading--4958--auto-- (datatypes.clj:1)
flatland.useful.datatypes__init.load (:1)
flatland.useful.datatypes__init. (:-1)
...

In a nutshell if I load flatland.map the ns has an update fn, if I load hayt which load flatland.datatypes which refers to flatland.map .. it doesn't. Presumably to do with ns load order and clojure 1.7 but still quite confusing..

My hack (which I place at the top of the ns loading hayt):

(require '[flatland.useful.map])
(in-ns 'flatland.useful.map)
(defn flatland.useful.map/update
  [m key f & args]
  (apply clojure.core/update m key f args))

Ponder whether to deprecate cql-value encoding of Keywords as Strings

This would allow to use them as placeholders for named prepared statement values (see https://issues.apache.org/jira/browse/CASSANDRA-6033)

for the following query

(->raw (select :foo (where {:bar :baz})))

current result

"SELECT FROM foo WHERE bar = 'baz';"

possible new behavior:

"SELECT FROM foo WHERE bar = :baz;"

also note it's possible to handle named values using cql-raw atm, it just not very pretty:

(->raw (select :foo (where {:bar (cql-raw :baz)})))

This would be a breaking change, meaning if I go forward with this this would probably be for 2.0 (c* 2.0.1 support including other breaking changes, including variadic IN, preparable USING, etc...)

`alter-type` not listed as entry clause

https://github.com/mpenet/hayt/blob/master/src/clj/qbits/hayt/cql.clj#L621

I understand this to be incorrect - :alter-type should be listed, as per docs - but could some one confirm? If so I am happy to submit PR. Currently, alter-type seems to be broken, and there are no tests for it either.

add support for secondary index on collections (c* 2.1)

apache/cassandra@c2294aa

apache/cassandra@d12a0d7

Unordered maps can lead to undefined behaviour in prepared statements

As far as I know the order of the entries in a map is not specified. Thus the code

(use 'qbits.hayt)
(->raw
 (insert :users
         (values {:b ?, :a ?})))

could output either one of the following statements:

"INSERT INTO users (a, b) VALUES (?, ?);"

or

"INSERT INTO users (b, a) VALUES (?, ?);"

Analoguous to the where syntax, I would allow:

(->raw
 (insert :users
         (values [[:a ?] [:b ?]])))

This should be backwards compatible. Currently it throws:

ClassCastException clojure.lang.PersistentVector cannot be cast to java.util.Map$Entry clojure.lang.APersistentMap$KeySeq.first (APersistentMap.java:152)

I'm currently using the following workaround, but it's quite dangerous:

(defn map->ordered-entries [m ks]
  (map (into {} (map (fn [entry] [(.getKey entry) entry]) m)) ks))
(->raw
 (insert :users
         (values (map->ordered-entries {:b 2, :a 1} [:b :a]))))

I'm using [cc.qbits/hayt "2.0.0-beta1"].

Decrementing counters produces wrong CQL

When updating a counter with {:counterfield (dec-by 1)} the cql produced is like counterfield = 1 - counterfield

I guess dec-by maybe should be changed from remove-head to remove-tail in src/clj/qbits/hayt/utils.clj:109?

optional validation: ns with prismatic schema for clauses/statements

These would be optional, in a separate namespace, potentially with a dev dep even.

Right now hayt basically does blind translation of values in statements, there is no validation at all for performance reason (you can generate garbage).

We could add Schemas for every clause/statement.
This would be an optional layer that would allow people to debug their queries and have somewhat readable error messages.

the api would mimic prismatic schema calls:

(require 'hayt.schemas)

(s/valdate (select (batch (where))) 
;; throws an ex-info with error map in second arg

(s/check (select (batch (where)))
;; return nil if ok, or an error map 

allow optional preprocessing/caching of some statement fragments when used via ->raw

This only makes sense for complex queries using ->raw compilation, and be useful for the rare cases where prepared statement are not usable (ex when you have dynamic list of columns etc etc).

Some emit'ing calls to be cached (ex for "SELECT "...), this could very likely speed up the query generation a lot since most queries only have variations in SET/WHERE/VALUES parts, the rest is easily cachable, and the cached values would require just a map lookup + string concat with the rest.

This could be achieved via type hints, or metadata on the clause we want cached, possibly with a cache strategy (from core.memoize for instance). or this could be activated globally and detect calls that render exactly the same for an emit and do some "JIT".

I can imagine this being very interesting for columns, using, limit all statement verbs and likely a lot more.

To be profiled, maybe hotspot does this for us already.

queries should ignore nil

Hi there,

I'm trying to use batch queries in forms like the following, and it would be really nice if queries ignored nil statements:

(batch (queries 
             (insert … ) ; A normal statement
             (when something (insert …) ; A conditional statement
...

TTL is `int`

Hi, in current Cassandra it looks (not sure if it got changed though), that TTL is int, not long.

For example, 350 literal has type Long in Clojure, which causes errors. Maybe it makes sense to perform a conversion?

BATCH INSERT is misformatted

Calling ->raw on {:batch [{:insert :table, :values {:col 1}}]} produces a string starting with BATCHINSERT with no space between BATCH and INSERT on 4.0.0-beta6.

insert with column of map type not working if key is a keyword

I've got a table which has a map column (e.g. features) which can have items in the the form <text,int>.

The thing is, if I do something like
(hayt/insert values (hayt/values {:features {:some_key 1}}), it is not stringifying the keyword :some_key into 'some_key' in the map

This used to work before but is broken now. We're on alia 1.7.1 and trying to upgrade to alia 2.0.0-beta7 so it's pulling whichever version of hayt is associated.

Any ideas? Thank you.

UPDATE ... USING generates bad CQL

(require '[qbits.hayt :as h])
(h/->raw (h/update :foo (h/set-columns {:id 10}) (h/using :ttl 100)))
; => "UPDATE foo  USING TTL 100SET id = 10;"
(h/->raw (h/update :foo (h/set-columns {:id 10}) (h/using :ttl 100 :timestamp 1000)))
; => "UPDATE foo  USING TTL 100 AND TIMESTAMP 1000SET id = 10;"

i.e. there is no space after the USING clause and before the SET clause

How to delete a key from a map?

This is a question and not an issue. I can't find how to delete a key from a map:

DELETE todo['2013-9-22 12:01'] FROM users WHERE user_id = 'frodo';

Malformed query when updating data into user defined type

I am trying to create a user defined type and insert data into a field that is using this type. Instead of using the user defined type within a map, I am using it within a list. Here is the type that I am creating:

(create-type :extension
  (if-not-exists)
  (column-definitions [[:type :varchar]
                       [:provider :varchar]
                       [:value :varchar]
                       [:description :text]]))

I am then adding a field extensions to the user_profiles table which is a list of extensions:

(alter-table "user_profiles"
  (add-column :extensions (list-type (frozen :extension))))

To insert data into this new field, I am using the following query:

(update "user_profiles"
  (set-columns {:extensions [(user-type {:type "foo"
                                         :provider "bar"
                                         :value "foobar"
                                         :description "barfoo"}))]})
  (where [[:id 123]])

The raw query for this looks as follows:

UPDATE "user_profiles" SET extensions = {:type : 'foo', :provider : 'bar', :value : 'foobar', :description : 'barfoo'}  extensions WHERE id = 123;

There happens to be the extra word extension in the raw query after the values which causes the query to fail. Could this be a bug in hayt or am I using the dsl in an incorrect way?

add UDT and TUPLE support

Compiler warnings on Clojure 1.7.0-alpha2+

WARNING: update already refers to: #'clojure.core/update in namespace: qbits.hayt.dsl.statement, being replaced by: #'qbits.hayt.dsl.statement/update

Cannot specify multiple identical clauses

When using an update table query, you are unable to specify multiple clauses of the same type. This is due to the into squashing the maps together in alter-table.

(alter-table :foo
             (alter-column :bar :int)
             (alter-column :baz :int))

Produces: "ALTER TABLE foo ALTER baz TYPE int;"

Time for 2.0?

I see that Hayt has been an RC3 for a few months, with one minor issue resolved. Any plans for 2.0 GA soon? If so, I can delay Cassaforte 2.0 release to depend on Hayt 2.0.