Fork me on GitHub
#clojure
<
2016-06-13
>
bcbradley00:06:13

holy crap clojure has support for natural join?

bcbradley00:06:22

i can actually mix functional programming and database semantics

bcbradley00:06:27

thats incredibly powerful

Tim00:06:17

@bcbradley: what are you referring to ?

richiardiandrea00:06:11

Yes it has a very powerful db-like set of functions in clojure.set plus a contrib library with an abstraction layer (datalog query language) over in-memory dbs

gowder02:06:07

Thanks @gfredericks (tried to delete that because I managed to dig up the function for doing so on stackoverflow, but thanks anyway! :-) )

bcbradley04:06:30

@tmtwd talking about join, select, project, union, difference, intersection, index and rename. They operate specifically on sets of maps, with the intention that those maps contain the same keys-- essentially rows in a relational table. With an SQL you have a lot of various ways to operate on a database, division, multiple kinds of joins, aggregates-- it can be very complex. With clojure, if you want to incorporate some sort of functionality or structure in your query, you aren't restrained to a specific subset of the core library-- sets of maps are strictly data structures, so you can use filter, map, concat and all the other tools you'd normally use. That is what I meant by blending functional and database semantics.

joshjones14:06:37

@bcbradley clojure is such a great language for manipulating data that in my current project I have been trying to decide just how much logic to put into a query, versus in clojure code. I think that a query that joins and does aggregates, even a simple one, will usually be more maintainable and clear in SQL. If you're doing very complex manipulation that SQL does not easily provide, then doing it on the clojure side is the clear choice. But if it's simple aggregation, grouping by, joining ... then SQL makes more sense... IMO anyway. If you or anyone has thoughts I'd love to hear.

zcaudate14:06:50

@joshjones: what do you think about honeysql?

joshjones14:06:33

I researched the options for SQL in clojure, and there are pros and cons to using DSLs like honeysql, korma, etc. ... ultimately I decided to use yesql -- I can write the query out in a separate file so it doesn't co-mingle with the clj code

pesterhazy15:06:45

query builders are fundamentally a bad idea, according to some people

pesterhazy15:06:58

but I think it ultimately depends on your needs

zcaudate15:06:59

about honeysql

zcaudate15:06:14

i agree on you about korma… but honeysql is fundamentally different

joshjones16:06:26

@zcaudate - I watched the whole presentation, and liked the power of honeysql when it comes to generating and composing queries. Since my current project is in the early stages, I will do some parallel work between yesql and honeysql ... currently I prefer the simplicity of simply writing the query, even if it means some duplication. But as the project grows I can absolutely see utilizing the power that honeysql provides.

joshjones16:06:25

In my previous job, we had a team of SQL writers and our dev team had to also write and work on the same queries. One of the benefits of actual SQL query strings (such as yesql does) is that non-devs and devs can work together on queries. My previous company would simply not function if it were not set up this way. So there are advantages to both approaches.

joshjones16:06:31

and thank you again @zcaudate for the video -- I don't know how I missed this, but it will be a great resources as I explore honeysql further

mpenet16:06:39

@joshjones: same here, we favor prepared statements in most cases. only realy use of map based dsl we have is to handle migrations, ddl stuff

mpenet16:06:47

I actually wrote a similar dsl for CQL (cassandra) and I end up almost never using it.

mpenet16:06:33

Also generating queries this way at run/query time isn't "free", you end up limiting yourself to using them for pstatements and then you just give up altogether, well at least that was my process.

joshjones16:06:38

also just to be clear, yesql separates the sql from the code, so there's no string composition, no ugly "inline sql" ... I think the yesql rationale says it very well: "Clojure is a great language for writing DSLs, but we don't need a new one. SQL is already a mature DSL." https://github.com/krisajenkins/yesql

mpenet16:06:36

yesql approach seems less damaging, I had mostly honeysql/korma & co in mind

joshjones16:06:15

it's very easy -- write quer(y|ies) in a file(s), call yesql's defqueries function, and then you have access to every query you wrote, as a native clojure function.

seancorfield16:06:19

How does Yesql work out when you need to dynamically compose queries at runtime?

seancorfield16:06:32

Do you have a big cond with complex-condition-1 query-1 complex-condition-2 query-2 etc?

mpenet16:06:35

@seancorfield: I didn't check in a long time, but originally it generated an ast (map), that gets turned into a string via a multimethod, there's not caching whatsoever

mpenet16:06:06

well that's the short version, it's a lot more steps, but the bulk of the work is this

mpenet16:06:37

the composition is just merging/conj'ing maps

seancorfield16:06:29

Now I'm confused. That's how Yesql works? I thought it had SQL in separate files?

mpenet16:06:45

nevermind, I read honeysql

mpenet16:06:53

I ll go back to my cave 😆

seancorfield16:06:12

So my question remains: if you're using Yesql, how do deal with situations where you need to compose complex queries dynamically at runtime? (Directed at folks who use Yesql)

mpenet16:06:19

I am not sure yesql support any kind of composition, does it?

seancorfield16:06:02

I ask because apart from generic crud stuff (which Java.jdbc handles out of the box), nearly all the SQL we do has to be dynamically built at runtime based on sometimes dozens of conditions.

joshjones16:06:36

@seancorfield - just to clarify, you mean adding to a where clause, for example? So, if I do not know ahead of time how many conditions, then I think yesql does not do this. As I mentioned, my project is currently very simple and I can determine ahead of time what I need to filter by, etc. At my previous job, yesql would not have worked for this reason (we also had to build the query, sometimes quite painfully so) But I think this tries to address some of it: https://medium.com/@trinity/dynamic-sql-generation-in-clojure-27a08f56f97e#.uyhk77jm7

joshjones16:06:35

we would have to compose the select, the joins, the ... mostly everything. I think a DSL, or a string composition strategy is best in clojure for this.

joshjones16:06:16

My guess is that honeysql would shine in your use case, @seancorfield ...

stathissideris16:06:06

@seancorfield: I have also used honeysql successfully for generating complex queries whose parts/columns etc were unknown ahead of time. It's not perfect, I found working around its limitations relatively simple, and it does give you the option of escaping to "raw" SQL if you need to

minimal18:06:20

Hugsql is like yesql with support for composition. Not yet used it myself. http://www.hugsql.org/#faq-yesql

bcbradley19:06:26

I prefer to do dynamic things in clojure and more static things in datalog or some SQL. Building complex queries out of the small set of relational table support that clojure provides isn't wise-- implementing the remaining relational algebra operators out of those would be wiser. However, I think the wisest approach is to not bottle yourself into thinking in the restraints of relational algebra-- you have every function that operates on data structures in clojure at your disposal, and can form queries that cannot be expressed succinctly or at all in SQL by leveraging the expressive power of clojure.

zane20:06:08

@seancorfield: Yeah, my understanding is that YeSQL is explicitly not for the use case where you need to dynamically generate your queries.

seancorfield21:06:12

(FYI Fumiko - who gave that talk - works on my team and it's our usage of honeysql she is describing in that talk!)

lvh22:06:02

Does anyone have any experience talking to Azure from Clojure? I tried to use the Java SDK, but turns out that’s unusable: https://github.com/Azure/azure-sdk-for-java/issues/786

hiredman22:06:08

the group ids and artifact ids here https://github.com/Azure/azure-sdk-for-java/blob/master/azure/pom.xml#L48 for the individual parts seem to work, but the artifact described in the pom (com.microsoft.azure/azure) doesn't

hiredman22:06:58

lein found [com.microsoft.azure/azure-client-runtime "1.0.0-SNAPSHOT"]