korma / korma Goto Github PK
View Code? Open in Web Editor NEWTasty SQL for Clojure.
Home Page: http://sqlkorma.com
Tasty SQL for Clojure.
Home Page: http://sqlkorma.com
It would be great if at least MaxPoolSize would be configurable and maybe many other C3PO options as well.
Thanks.
I'm not sure if I'm doing something wrong, but I'm seeing some bad behavior with 0.3.0-alpha2:
user=> (dry-run (update article (set-fields {:slug "foo" :title "bar"}) (where {:slug "foo"})))
dry run :: UPDATE "article" SET "title" = ?, "slug" = ? WHERE ("article"."slug" = ?) :: [foo bar foo][{:id 1}]
user=> (dry-run (update article (set-fields {:title "bar" :keywords "a"}) (where {:slug "foo"})))
dry run :: UPDATE "article" SET "title" = ?, "keywords" = ? WHERE ("article"."slug" = ?) :: [foo bar a]
user=> article
{:table "article", :name "article", :pk :id, :db nil, :transforms (), :prepares (), :fields [], :rel {}}
Let me know if I can provide more information.
Korma allows only one relationship between two entities. There could be an option like :alias passed to the relationship function.
(modify table "TOP 5")
so I can say (defn top [this n](modify this %28str "TOP " n)))
The clojure.java.jdbc library accepts connection parameters as URLs, but Korma assumes it's always a map.
When i add a (with entity) join of a belongs-to relation to a query i get the fields mixed up, how do i get the entity nested in another map?
Consider this example:
(defentity users
(belongs-to accounts))
(select users
(with accounts))
=> [{:id 1 :id_2 1 :username "username" :pass "password"}]
I expect something like this
=> [{:id 1 :account {:id 1 :username "username" :pass "password"}}]
using hsqldb.
this select works:
SELECT o.id FROM "order" o;
but this select does not work:
SELECT order.id FROM "order";
i think, to solve this, korma could introduce
alias-naming for tables.
another solution would be to avoid reserved word
"order" as a table-name and make quoting of
table-names optional.
this select works:
SELECT order_.id FROM order_;
but this select does not work:
SELECT order_.id FROM "order_";
SELECT "order_".id FROM "order_";
improvement ?
see: http://boss-level.com/?p=96, http://gist.github.com/1177043
"Explicit connections prevent composition. So wrap all db functions in
a fn that checks if a connection exists, and only if not does it make one
that way the outermost fn that needs a db creates the connection and those
internal use it."
maybe the link could also be helpful for adding basic transaction-management.
What I see missing below is in the "FROM" statement.
It should read
FROM bmprdstr_sql AS boms, imitmidx_sql AS itemsbut the "imitmidx_sql" alias is missing.
(defentity items (table :imitmidx_sql :items)) #'clojure.core/items (defentity boms (table :bmprdstr_sql :boms) (pk :comp_item_no) (has-many items {:fk :item_no})) clojure.core=> clojure.core=> #'clojure.core/boms clojure.core=> (defentity items (table :imitmidx_sql :items)) #'clojure.core/items (defentity boms (table :bmprdstr_sql :boms) (has-many items )) clojure.core=> clojure.core=> #'clojure.core/boms (def kspec (-> (select* boms) (join :right :items :items.item_no :comp_item_no) (fields :item_no :seq_no :comp_item_no :items.item_desc_1 :items.item_desc_2 :qty_per_par :reference_1 :reference_2 :reference_3) (order :seq_no))) #'clojure.core/kspec (defn kgetspec [item_no] (exec (-> kspec (where {:item_no [like item_no]})))) #'clojure.core/kgetspec clojure.core=> clojure.core=> (kgetspec "SOP-00003") Failure to execute query with SQL: SELECT boms.item_no, boms.seq_no, boms.comp_item_no, items.item_desc_1, items.item_desc_2, boms.qty_per_par, boms.reference_1, boms.reference_2, boms.reference_3 FROM bmprdstr_sql AS boms LEFT JOIN items ON items.item_no = boms.comp_item_no WHERE (boms.item_no LIKE ?) ORDER BY boms.seq_no DESC :: (SOP-00003) SQLException: Message: Invalid object name 'items'. SQLState: S0002 Error Code: 208 nil clojure.core=>
Is it possible to run transactions using korma?
If not, that needs to be there for korma to be usable in any kind of production system I've ever worked with.
It would be nice to have a way to disable the returning of results on insert tables. For example, if you might want to have a write-only table for logging purposes, but returning results on insert requires select permission.
A function like this would help:
When deleting a record, the delete method returns nil if successful,
rendering it useless for testing for success (if (delete ....))
.
Any "truthy" value works for me (record id if convenient)
Cheers
Greg
The culprit is the line 271 in sql.clj
https://github.com/ibdknox/Korma/blob/master/src/korma/internal/sql.clj#L271
I believe replacing :left with type should solve the issue.
Hello.
I try write select query inside defmacro, but I get error into aggregate function.
it's work fine:
(select tables (aggregate (count :*) :qty))
but it isn't:
(defmacro count-macro[] `(select tables (aggregate (count :*) :qty)))
(count-macro)
UnsupportedOperationException count not supported on this type: Keyword clojure.lang.RT.countFrom (RT.java:538)
because macro "(aggregate (count :) :qty)" expands to (korma.core/aggregate (clojure.core/count :) :qty))
(macroexpand '(count-macro))
(let* [query__2564__auto__ (clojure.core/-> (korma.core/select* easypet.server/tables) (korma.core/aggregate (clojure.core/count :*) :qty))] (korma.core/exec query__2564__auto__))
I get around this by using:
(defmacro count-macro[] `(select tables (aggregate (korma.sql.fns/agg-count :*) :qty)))
How do run a transaction/rollback?
Related to #14, the convenience oracle
method assumes the url connecting to an oracle database will follow one type of connection, but not service name mappings, which are growing more popular with Oracle's Exadata system.
Possible service name mapping:
jdbc:oracle:thin:scott/tiger@//myhost:1521/myservicename
see this link for some more references (also mentioned in #14)
This works:
(select sometable
(where (and {:event_time [> (java.sql.Timestamp. 1)] })))
This doesn't work:
(def c {:event_time [> (java.sql.Timestamp. 1)] })
(select sometable
(where (and c)))
Gives this kind of stacktrace.
Caused by: java.lang.RuntimeException: java.lang.ClassCastException: java.sql.Timestamp cannot be cast to java.lang.Number
LazySeq.java:47 clojure.lang.LazySeq.sval
LazySeq.java:56 clojure.lang.LazySeq.seq
RT.java:450 clojure.lang.RT.seq
core.clj:122 clojure.core/seq
core.clj:2450 clojure.core/dorun
core.clj:2465 clojure.core/doall
engine.clj:213 korma.sql.engine/pred-map
core.clj:2096 clojure.core/map[fn]
LazySeq.java:42 clojure.lang.LazySeq.sval
Caused by: java.lang.ClassCastException: java.sql.Timestamp cannot be cast to java.lang.Number
Numbers.java:198 clojure.lang.Numbers.gt
core.clj:875 clojure.core/>
engine.clj:208 korma.sql.engine/pred-vec
core.clj:2096 clojure.core/map[fn]
LazySeq.java:42 clojure.lang.LazySeq.sval
I am getting a following stacktrace with 0.3.0-beta7 and 0.3.0-beta8, beta6 worked just fine. The only difference is change of version number in project.clj.
Project in question is this https://github.com/jsyrjala/ruuvitracker_server and command is lein ring server
Copying 29 files to /some/path/lib/dev
Exception in thread "main" java.lang.Exception: Too many arguments to def (core.clj:305)
at clojure.lang.Compiler.analyzeSeq(Compiler.java:5376)
at clojure.lang.Compiler.analyze(Compiler.java:5190)
at clojure.lang.Compiler.analyze(Compiler.java:5151)
at clojure.lang.Compiler.eval(Compiler.java:5428)
at clojure.lang.Compiler.load(Compiler.java:5857)
at clojure.lang.RT.loadResourceScript(RT.java:340)
at clojure.lang.RT.loadResourceScript(RT.java:331)
at clojure.lang.RT.load(RT.java:409)
at clojure.lang.RT.load(RT.java:381)
at clojure.core$load$fn__4519.invoke(core.clj:4915)
at clojure.core$load.doInvoke(core.clj:4914)
at clojure.lang.RestFn.invoke(RestFn.java:408)
at clojure.core$load_one.invoke(core.clj:4729)
at clojure.core$load_lib.doInvoke(core.clj:4766)
at clojure.lang.RestFn.applyTo(RestFn.java:142)
at clojure.core$apply.invoke(core.clj:542)
at clojure.core$load_libs.doInvoke(core.clj:4800)
at clojure.lang.RestFn.applyTo(RestFn.java:137)
at clojure.core$apply.invoke(core.clj:544)
at clojure.core$use.doInvoke(core.clj:4892)
at clojure.lang.RestFn.invoke(RestFn.java:408)
at ruuvi_server.models.entities$eval873$loading__4414__auto____874.invoke(entities.clj:1)
at ruuvi_server.models.entities$eval873.invoke(entities.clj:1)
at clojure.lang.Compiler.eval(Compiler.java:5424)
at clojure.lang.Compiler.eval(Compiler.java:5415)
at clojure.lang.Compiler.load(Compiler.java:5857)
at clojure.lang.RT.loadResourceScript(RT.java:340)
at clojure.lang.RT.loadResourceScript(RT.java:331)
at clojure.lang.RT.load(RT.java:409)
at clojure.lang.RT.load(RT.java:381)
at clojure.core$load$fn__4519.invoke(core.clj:4915)
at clojure.core$load.doInvoke(core.clj:4914)
at clojure.lang.RestFn.invoke(RestFn.java:408)
at clojure.core$load_one.invoke(core.clj:4729)
at clojure.core$load_lib.doInvoke(core.clj:4766)
at clojure.lang.RestFn.applyTo(RestFn.java:142)
at clojure.core$apply.invoke(core.clj:542)
at clojure.core$load_libs.doInvoke(core.clj:4800)
at clojure.lang.RestFn.applyTo(RestFn.java:137)
at clojure.core$apply.invoke(core.clj:542)
at clojure.core$require.doInvoke(core.clj:4881)
at clojure.lang.RestFn.invoke(RestFn.java:408)
at ruuvi_server.tracker_api$eval867$loading__4414__auto____868.invoke(tracker_api.clj:1)
at ruuvi_server.tracker_api$eval867.invoke(tracker_api.clj:1)
at clojure.lang.Compiler.eval(Compiler.java:5424)
at clojure.lang.Compiler.eval(Compiler.java:5415)
at clojure.lang.Compiler.load(Compiler.java:5857)
at clojure.lang.RT.loadResourceScript(RT.java:340)
at clojure.lang.RT.loadResourceScript(RT.java:331)
at clojure.lang.RT.load(RT.java:409)
at clojure.lang.RT.load(RT.java:381)
at clojure.core$load$fn__4519.invoke(core.clj:4915)
at clojure.core$load.doInvoke(core.clj:4914)
at clojure.lang.RestFn.invoke(RestFn.java:408)
at clojure.core$load_one.invoke(core.clj:4729)
at clojure.core$load_lib.doInvoke(core.clj:4766)
at clojure.lang.RestFn.applyTo(RestFn.java:142)
at clojure.core$apply.invoke(core.clj:542)
at clojure.core$load_libs.doInvoke(core.clj:4800)
at clojure.lang.RestFn.applyTo(RestFn.java:137)
at clojure.core$apply.invoke(core.clj:542)
at clojure.core$require.doInvoke(core.clj:4881)
at clojure.lang.RestFn.invoke(RestFn.java:408)
at ruuvi_server.api$eval848$loading__4414__auto____849.invoke(api.clj:1)
at ruuvi_server.api$eval848.invoke(api.clj:1)
at clojure.lang.Compiler.eval(Compiler.java:5424)
at clojure.lang.Compiler.eval(Compiler.java:5415)
at clojure.lang.Compiler.load(Compiler.java:5857)
at clojure.lang.RT.loadResourceScript(RT.java:340)
at clojure.lang.RT.loadResourceScript(RT.java:331)
at clojure.lang.RT.load(RT.java:409)
at clojure.lang.RT.load(RT.java:381)
at clojure.core$load$fn__4519.invoke(core.clj:4915)
at clojure.core$load.doInvoke(core.clj:4914)
at clojure.lang.RestFn.invoke(RestFn.java:408)
at clojure.core$load_one.invoke(core.clj:4729)
at clojure.core$load_lib.doInvoke(core.clj:4766)
at clojure.lang.RestFn.applyTo(RestFn.java:142)
at clojure.core$apply.invoke(core.clj:542)
at clojure.core$load_libs.doInvoke(core.clj:4800)
at clojure.lang.RestFn.applyTo(RestFn.java:137)
at clojure.core$apply.invoke(core.clj:542)
at clojure.core$require.doInvoke(core.clj:4881)
at clojure.lang.RestFn.invoke(RestFn.java:408)
at ruuvi_server.core$eval842$loading__4414__auto____843.invoke(core.clj:1)
at ruuvi_server.core$eval842.invoke(core.clj:1)
at clojure.lang.Compiler.eval(Compiler.java:5424)
at clojure.lang.Compiler.eval(Compiler.java:5415)
at clojure.lang.Compiler.load(Compiler.java:5857)
at clojure.lang.RT.loadResourceScript(RT.java:340)
at clojure.lang.RT.loadResourceScript(RT.java:331)
at clojure.lang.RT.load(RT.java:409)
at clojure.lang.RT.load(RT.java:381)
at clojure.core$load$fn__4519.invoke(core.clj:4915)
at clojure.core$load.doInvoke(core.clj:4914)
at clojure.lang.RestFn.invoke(RestFn.java:408)
at clojure.core$load_one.invoke(core.clj:4729)
at clojure.core$load_lib.doInvoke(core.clj:4766)
at clojure.lang.RestFn.applyTo(RestFn.java:142)
at clojure.core$apply.invoke(core.clj:542)
at clojure.core$load_libs.doInvoke(core.clj:4800)
at clojure.lang.RestFn.applyTo(RestFn.java:137)
at clojure.core$apply.invoke(core.clj:542)
at clojure.core$require.doInvoke(core.clj:4881)
at clojure.lang.RestFn.invoke(RestFn.java:436)
at user$eval1.invoke(NO_SOURCE_FILE:1)
at clojure.lang.Compiler.eval(Compiler.java:5424)
at clojure.lang.Compiler.eval(Compiler.java:5414)
at clojure.lang.Compiler.eval(Compiler.java:5391)
at clojure.core$eval.invoke(core.clj:2382)
at clojure.main$eval_opt.invoke(main.clj:235)
at clojure.main$initialize.invoke(main.clj:254)
at clojure.main$null_opt.invoke(main.clj:279)
at clojure.main$main.doInvoke(main.clj:354)
at clojure.lang.RestFn.invoke(RestFn.java:421)
at clojure.lang.Var.invoke(Var.java:369)
at clojure.lang.AFn.applyToHelper(AFn.java:163)
at clojure.lang.Var.applyTo(Var.java:482)
at clojure.main.main(main.java:37)
Caused by: java.lang.Exception: Too many arguments to def
at clojure.lang.Compiler$DefExpr$Parser.parse(Compiler.java:415)
at clojure.lang.Compiler.analyzeSeq(Compiler.java:5369)
... 118 more
just to remind you to check out bonecp (http://jolbox.com/) as an alternative to c3p0.
In my leiningen projects I exlcude the org.slf4j/slf4j-api dependency to include a more recent version like this:
:dependencies [ [com.jolbox/bonecp "0.7.1.RELEASE"
:exclusions [org.slf4j/slf4j-api]]
[org.slf4j/slf4j-api "1.6.4"]
[ch.qos.logback/logback-classic "1.0.0"]
[org.clojure/tools.logging "0.2.3"]]
This configration lets me direct all logging that's done by the libs (bonecp, h2 database, etc...) I use as well as org.clojure/tools.logging to logback. For legacy libs you can use the slf4j bridges like "jcl-over-slf4j".
Hi Chris
It's an issue for UNION, UNION ALL, INTERSECT support.
If I have an Oracle database that requires connecting via service_name, I don't see Korma handling it. I'm using pretty standard service_name urls as shown here, but nothing gives:
(defdb qa { :classname "oracle.jdbc.OracleDriver"
:subprotocol "oracle:thin"
:subname "@//prodserver:1521/prodserver.world"
:user "user"
:password "pass"})
This is different and new to our group, because we aren't using SID names to identify databases, so when trying to connect to this, I get a very large stacktrace:
korma-emo.core=> (require 'korma_emo.core :reload)
WARN com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1 20111108 155946,140 com.mchange.v2.resourcepool.BasicResourcePool ] com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@7de4f1 -- Acquisition Attempt Failed!!!
Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception: Failure to execute query with SQL:
java.sql.SQLException: Listener refused the connection with the following error:
ORA-12504, TNS:listener was not given the SID in CONNECT_DATA
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:419)
at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:536)
at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:228)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:134)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)
SELECT * FROM my_table WHERE (my_table.my_key = ?) :: (93)
SQLException:
Message: Connections could not be acquired from the underlying database!
SQLState: null
Error Code: 0
nil
korma-emo.core=> at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12504, TNS:listener was not given the SID in CONNECT_DATA
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:386)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1054)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:308)
... 12 more
WARN com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2 20111108 155946,140 com.mchange.v2.resourcepool.BasicResourcePool ] com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@a5f0c7 -- Acquisition Attempt Failed!!!
Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception:
java.sql.SQLException: Listener refused the connection with the following error:
ORA-12504, TNS:listener was not given the SID in CONNECT_DATA
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:419)
at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:536)
at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:228)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:134)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12504, TNS:listener was not given the SID in CONNECT_DATA
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:386)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1054)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:308)
... 12 more
WARN com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0 20111108 155946,140 com.mchange.v2.resourcepool.BasicResourcePool ] com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@1419cee -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception:
java.sql.SQLException: Listener refused the connection with the following error:
ORA-12504, TNS:listener was not given the SID in CONNECT_DATA
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:419)
at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:536)
at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:228)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:134)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12504, TNS:listener was not given the SID in CONNECT_DATA
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:386)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1054)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:308)
... 12 more
korma-emo.core=>
The docs page specifies use korma.db
in the db section but does not specify use korma.core
in the entities section. Without this, trying to use defentities
and all the query macros don't work.
I have some Oracle tables that I'd like to query, but since LIMIT doesn't work the same way in Oracle, I'm using Oracle's ROWNUM Pseudocolumn:
user=> (select iunit (where {:rownum [< 10]}))
Failure to execute query with SQL:
SELECT * FROM iunit WHERE (iunit.rownum < ?) :: (10)
SQLSyntaxErrorException:
Message: ORA-01747: invalid user.table.column, table.column, or column specification
SQLState: 42000
Error Code: 1747
nil
What confuses me is that I get a weird error with the <=
predicate on it
user=> (sql-only (select iunit (where {:rownum [<= 10]})))
CompilerException java.lang.RuntimeException: No such var: korma.internal.sql/pred-<=, compiling:(NO_SOURCE_PATH:21)
insert using multiple values returns the same single GENERATED_KEY that an insert using a single value would
Not sure if it's feasible but it would be great to receive a vector all all added keys
In addition, the key returned is the first key of the insert, not the last key - again not sure if it's controllable which id would get returned
This is using MySql 5.1.x
When an aggregate is defined on a field that exists on an entity referred to by a with
clause that is in a has-many
relationship with the primary entity the generated SQL fails because the fields are requested later with a secondary query.
(defentity timestamps
(entity-fields :timestamp))
(defentity fragments
(has-many timestamps {:fk "fragmentid"}))
(-> (select* fragments)
(with timestamps)
(aggregate (min :timestamps.timestamp) :pub_date :fragmentid)
(exec))
; => Failure to execute query with SQL:
;SELECT MIN(timestamps.timestamp) AS pub_date FROM fragments GROUP BY fragments.fragmentid :: []
;MySQLSyntaxErrorException:
; Message: Unknown column 'timestamps.timestamp' in 'field list'
; SQLState: 42S22
; Error Code: 1054
;()
I tried running the following:
(def uri-counts (-> (select* uris
(aggregate (count :*) :cnt :uri)
(order :cnt :DESC))))
and I got:
count not supported on this type: Keyword
[Thrown class java.lang.UnsupportedOperationException]
I think the issue is that the count function is calling clojure.core.count instead of resolving to the appropriate aggregate function.
It complects the generation of SQL and connection pooling.
This causes problems like #62; I would like to use the SQL generation facilities without running into bugs in the hidden connection pooling implementation that can't be disabled.
Explicitly defined foreign keys in has-many relations cause the foreign key to appear in the generated SQL as a keyword (with preceding colon):
(-> (select* fragments) (with timestamps) (limit 1) exec)
; => (Failure to execute query with SQL:
; SELECT * FROM timestamps WHERE :fragmentid = 1 :: ()
You can currently define an existing schema with defentity
, and use this information to write SELECT
, DELETE
and UPDATE
SQL statements. It should also be possible to CREATE TABLE
the table represented by that entity, so you can create your table with Korma, not just query it. Sadly, create-entity
does something different, so (to-sql (create-entity my-already-defined-entity))
does not work.
I would like this feature because I have a Database class in college and we have to write schema definitions for some assignments. I would like to be able to use Korma to write the schema, but it can't right now. Using Korma would be easier than writing CREATE TABLE SQL statements manually for a few reasons:
Is it possible to use expressions in WHERE like "WHERE login_date < NOW - INTERVAL 5 DAY". I am specifically interested in date manipulation and I can't find out how to do it.
Following statement returns a wrong query:
(sql-only (select event (with tracker event-location)))
"SELECT \"events\".* FROM \"events\" LEFT JOIN \"trackers\" ON \"trackers\".\"id\" = \"events\".\"tracker_id\""
There should be an additional LEFT JOIN event_locations ON events.id = event_locations.event_id
. This happens in 0.3.0-beta2, earlier version 0.2.1 and 0.2.2-SNAPSHOT didn't have this particular problem. The same thing happens if I change has-one to has-many.
Dry run looks like this, so korma is not making an additional query to event_locations -table either.
user=> (dry-run (select event (with tracker event-location)))
dry run :: SELECT "events".* FROM "events" LEFT JOIN "trackers" ON "trackers"."id" = "events"."tracker_id" :: []
[{:id 1}]
user=>
I have following entity mappings.
(defentity tracker
(table :trackers)
(pk :id)
(entity-fields :id :tracker_identifier :name)
)
(defentity event-location
(table :event_locations)
(pk :id)
(entity-fields :latitude :longitude)
)
(defentity event
(pk :id)
(table :events)
(entity-fields :id :event_time :created_on)
(belongs-to tracker {:fk :tracker_id})
(has-one event-location {:fk :event_id})
)
Hi Chris,
it looks like the "src/log4j.xml" file screws up my logging
configuration in a project in which I use Korma. In that project
I have this logging configuration in "resources/log4j.properties":
log4j.rootLogger=INFO, console
log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.Threshold=INFO
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %-5p %m%n
Those setting don't have any effect until I remove
Korma's "src/log4j.xml". If I remember correctly, I read
somewhere that libraries shouldn't ship with Log4j
configurations, because they can cause trouble.
My question is, could I have run into such a case? Would it make
sense to ship Korma without a Log4j config? Any suggestions how I
can get my config working with Korma?
Thanks, Roman.
i've been working on supporting postgis (spatial sql) in a small korma 'extension' (convert to/from geometry types, macros for some spatial functions).
everything went smooth, until i realized:
i can't do 'spatial joins'.
so a basic 'spatial join' looks something like this:
SELECT lines.* FROM lines, polygons
WHERE
ST_INTERSECTS(lines.geometry, polygon.geometry)
SELECT lines.* FROM lines JOIN polygons ON ( ST_WITHIN(lines.geometry, polygons.geom) )
is also a valid postgis query, but not a common idiom.
now i see 2 posiblities to support these kind of queries
A) support a way to supply a function to (join ..)
B) support a way to sneak an extra table into the FROM clause
It seems to me, that A) would need a bit of work within korma, so i propose B)
my proposed (rather hackish) change to korma.internal.sql/join-clause line 269:
(if (= "TABLE" join-type) (str ", " table) (str join on-clause))))
so join-clause now handles a join-type :table that translates into the table name only - i could then output a fake :pk and :fk, so no change to any function signatures is needed. works for me, but probably not very tasty.
This would allow me todo something like this:
(select lines-ent
(spatial-join :polygon)
(where (st-intersects :geometry :polygon.geometry)))
with (spatial-join :polygon) leading to (join :table :polygon :ignore :ignore)
It looks like korma doesn't quote table and column names. When using h2 with lobos for example, the create statements look like this in the h2 trace log:
/*SQL t:3*/CREATE TABLE \"image\" (\"id\" INTEGER AUTO_INCREMENT, \"original\" INTEGER, \"src\" VARCHAR(255),
\"alt\" VARCHAR(512), \"width\" SMALLINT, \"height\" SMALLINT,
CONSTRAINT \"image_primary_key_id\" PRIMARY KEY (\"id\"));
But korma's selects against the same table look like:
SELECT * FROM image WHERE (image.id = ?) [42102-161]
Which turns out are not the same thing. Korma would need to do something like this instead to reference the same objects:
SELECT * FROM "image" WHERE ("image"."id" = 1)
connection definition on sqlkorma site contains invalid option :username
(defdb prod (postgres {:db "korma"
:username "db"
:password "dbpass"}))
should be an :user
case statements...
http://www.postgresql.org/docs/8.3/static/functions-conditional.html
The following query:
select * from user AS u;
is incorrect in Oracle, it has to be:
select * from user u;
MSSQL uses SELECT TOP n ...
as opposed to SELECT ... LIMIT n
[postgresql "8.4-701.jdbc4"]
is currently a dependency, but this makes things confusing when I want to require korma and use a different version of the postgresql driver.
If it's used for testing, should it just be in dev-dependencies?
I could of course be very wrong, I'm new to Clojure.
Currently korma uses automatically c3p0 connection pool and there doesn't seem to be a way disable or change that.
My software already has a configured connection pool and I'd like to use that, but korma creates a new one. Also out of the box the c3p0 uses default minPoolSize 3, so korma will use at least 3 additional connections (this can be changed via c3p0.properties file, though.)
https://github.com/ibdknox/Korma/blob/master/src/korma/db.clj
The preferred solution would be that defdb can get the datasource as paramter and korma would use that without wrapping it to c3p0 connection pool. For example:
(defdb db (postgres {:datasource (create-my-connection-pool db-spec)} ))
Just like the postgres, etc ones.
Hi I'm trying use sqlite...I'm using version 0.2.1 but seems than sqlite isn't implemented in this version..I can't found ...now reading korma/db.clj in the line 112 It's defined...isn't the same version??...is sqlite pleanty functional with korma?..thanks
Ran into an issue trying to generate a query using the distinct modifier. The query being generated was: select [DISTINCT] users.name from users. I believe I have a fix if you would like me to send a pull request.
(as-sql
(-> (select* "users")
(fields :name)
(modifier "DISTINCT")))
There is an issue with MySQL which is more on MySQL side but still. The thing is that MySQL doesn't by default use double quotes to quote field names. It uses backtiks. And double quotes are reserved for string literals. So, basically out of the box Korma doesn't work with MySQL. There is a workaround which is to run (exec-raw "set sql_mode='ANSI_QUOTES'") before executing any queries. But I'm not sure if this will work with multiple connections from pool, unless one would run the aforementioned query before executing any other query. Also it may be possible to change this in MySQL config but that is not always an option. So, what I would like to see is an ability to set quoting character in Korma itself. And maybe even predefined quoting characters for different database types.
I was trying to update this line:
{:SIGNALID 4206M, :NAME "SR.FASTBCT-AS", :DESCRIPTION nil, :POSITION 1M, :MACHINESUBPARTID 769M}
Following command
(update signal (set-fields {:DESCRIPTION "New description"}) (where (= :SIGNALID 4206M)))
fails with
UPDATE SIGNALMENU SET DESCRIPTION = ? WHERE SIGNALMENU."SIGNALID" = ? :: (4206M New description)
SQLException:
Message: ORA-01722: invalid number
SQLState: 42000
Error Code: 1722
If I understand it right, parameters for WHERE clause should go last.
Here's the result of dry-run:
dry run :: UPDATE SIGNALMENU SET DESCRIPTION = ? WHERE SIGNALMENU."SIGNALID" = ? :: [4206M New description]
[{:id 1}]
Requesting a way to define composite primary keys in defentity, eg
(defentity stat
(pk :user_id :card_id))
Is this something you might add in the future?
When korma decides to use a secondary query on an entity with a has-many relationship, the resulting query is not shown by the sql-only
function. E.g:
(defentity timestamps)
(defentity fragments
(has-many timestamps {:fk "fragmentid"}))
(-> (select* fragments) (with timestamps) exec sql-only)
; => "SELECT * FROM fragments"
wishlistd.server=> (select wishlist (with wish))
(Failure to execute query with SQL:
SELECT * FROM wish WHERE wishlist_code = qocosihi :: ()
PSQLException:
Message: ERROR: column "qocosihi" does not exist
Position: 42
SQLState: 42703
Error Code: 0
{:wish nil, :code "qocosihi", :title "Default", :created_at #<Timestamp 2012-03-03 16:24:21.613188>})
Using version 0.2.1
Full code: http://hastebin.com/lefanamafo.clojure
ps it doesn't seem like it is using the entity-fields either, I get all fields on default.
When executing the following bit of code to update the ACTIVE column (a boolean column):
(update test-entity
(set-fields {:ACTIVE false})
(where {:APP_GUID "1234"}))
The following error is thrown:
Failure to execute query with SQL:
UPDATE test SET ACTIVE IS FALSE WHERE (test."APP_GUID" = ?) :: (1234)
JdbcSQLException:
Message: Syntax error in SQL statement "UPDATE TEST SET ACTIVE IS[*] FALSE WHERE (TEST.""APP_GUID"" = ?) "; expected "., ="; SQL statement:
UPDATE test SET ACTIVE IS FALSE WHERE (test."APP_GUID" = ?) [42001-161]
SQLState: 42001
Error Code: 42001
I think it's because for H2, "ACTIVE IS FALSE" needs to be replaced with "ACTIVE = FALSE". The following works correctly:
(exec-raw "update test set ACTIVE = false where (test."APP_GUID" = '1234')")
Hi
It would be great if we had "having" support in queries.
Something like following:
(subselect posts
(with users)
(fields :users.user_id :users.username :posts.poster_ip)
(aggregate (count :post_id) :post_ip_count)
(order :users.user_id :asc)
(group :users.user_id :users.username :posts.poster_ip)
(having {:post_ip_count [> 1]}))
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.