When using mito:insert-dao, I got a nasty error in a big inserting operation:

The value ... is not of type
  (OR
   (AND #1=(SATISFIES SXQL.SQL-TYPE:SQL-EXPRESSION-LIST-P)
        #2=(SATISFIES TRIVIAL-TYPES:PROPER-LIST-P) CONS)
   (AND #1# #2# NULL))

I thought it could be useful for debugging if we let sql-expression-list-p (and other predicate functions?) give a warning which specific element produced the failure:

This examplary code

(defun sql-expression-list-p (object)
  (if (every #'sql-expression-p object)
      t
      (let ((faulty-expressions
	      nil))
	(mapc (lambda (expression)
	      (unless (sql-expression-p expression)
		(push expression faulty-expressions)))
	    object)
      (warn "The values ~A are not of type sql-expression." (reverse faulty-expressions)))))

would produce an additional warning in the standard output such as:

WARNING:
   The values ((#S(SQL-VARIABLE
                   :VALUE example-value))) are not of type sql-expression.

Do you like the idea, or is it overkill? @fukamachi

Does anyone have any ideas on how to extract all table names from a sxql query?

My use case involves pre-processing the tables before executing the query. I do not see any obvious way to do this, and trying to parse the sxql expression seems like a bit of work to do in a reliable way. Alternatively I could wrap the function with a macro and have the user specify the tables, but it would be nice, since the information is already there, to extract it if possible.

Any ideas?

Hello,
is it possible to create views on existing tables using sxql or integral?

Thank you!

so given

(sxql:insert-into :users (sxql:set= :name "user" :created_at "now()"))

produces the wrong sql

INSERT INTO users (name, created-at) VALUES ('user', 'now()')

when what i would like would be

INSERT INTO users (name, created-at) VALUES ('user', now() )

Many thanks!

I have this sxql statement in one of my projects:

(sxql:select ((:sum :amount))
  (sxql:from :transaction)
  (sxql:where (:< (:raw "cast(amount as numeric)")
                  0)))

I tried a few different ways but I can't figure out how to eliminate the :RAW use in this snippet. Is it possible and I'm not able to figure it out?

As per the title. (fields (:count :*)) works, however. The examples in the readme don't use fields in their statements, so one or the other seems out of date or bugged.

Hi, it would be nice to replace Optima with Trivia.

That's probably all there is to it:

From 8c863b3bd86d443059a341f42abbfe780b8113c6 Mon Sep 17 00:00:00 2001
From: vindarel <[email protected]>
Date: Fri, 4 Jun 2021 13:45:15 +0200
Subject: [PATCH] optima -> trivia

---From 9697385cd9867b287b9d4f5def1a316ba38bbeec Mon Sep 17 00:00:00 2001
From: vindarel <[email protected]>
Date: Fri, 4 Jun 2021 13:45:15 +0200
Subject: [PATCH] optima -> trivia

---
 src/sxql.lisp | 7 +++----
 sxql.asd      | 2 +-
 2 files changed, 4 insertions(+), 5 deletions(-)

diff --git a/src/sxql.lisp b/src/sxql.lisp
index 6b593ba..14d8eb8 100644
--- a/src/sxql.lisp
+++ b/src/sxql.lisp
@@ -3,8 +3,7 @@
   (:use :cl
         :sxql.statement
         :sxql.composed-statement
-        :sxql.clause
-        :optima)
+        :sxql.clause)
   (:shadow :primary-key
            :foreign-key
            :key)
@@ -68,7 +67,7 @@
 @export
 (defmacro select (fields &body clauses)
   `(make-statement :select
-                   ,(match fields
+                   ,(trivia:match fields
                       ((or (list* (type keyword) _)
                            (list* (list* (type keyword) _) _))
                        `(make-clause :fields
@@ -160,7 +159,7 @@
   `(make-clause :fields ,@(mapcar #'expand-op fields)))
 
 (defun convert-if-fields-clause (clause)
-  (match clause
+  (trivia:match clause
     ((or (list* (type keyword) _)
          (list* (list* (type keyword) _) _))
      (apply #'make-clause :fields clause))
diff --git a/sxql.asd b/sxql.asd
index 5fbd88b..c909445 100644
--- a/sxql.asd
+++ b/sxql.asd
@@ -16,7 +16,7 @@
   :version "0.1.0"
   :author "Eitaro Fukamachi"
   :license "BSD 3-Clause"
-  :depends-on (:optima
+  :depends-on (:trivia
                :iterate
                :cl-syntax-annot
                :trivial-types
-- 
2.17.1

thanks

Use case:

select (select 1 from foobar) as value from foobar;

For instance, I have a table named 'theTest' in MySQL. Table names are case sensitive in MySQL, so a query selecting from 'thetest' will fail.

Using the sxql syntax, I can't find how to do something like:

(with-connection (db)
  (retrieve-all
    (select :*
      (from :theTest))))

As the keyword ':theTest' becomes 'thetest' for the table name. Escaping via bars fails as well. It also seems like it is not possible to just call it as a string, such as "theTest".

How would you implement that operator? E.g:

select * from foo where name ~* 'baz';

I am currently inserting a lot of single entries into a table using datafly. I am currently having some performance issues and was wondering if something like this is possible with sxql?
insert multiple

when execute (ql:quick load "sql") two times, the second load error:
..........................
[package sxql.operator]

**++++ Error between functions:
Cannot find description of structure (UNARY-OP (NAME "NOT")) to use for inclusion.

**++++ Error between functions:
Cannot find description of structure (UNARY-OP (NAME "IS NULL")) to use for inclusion.

**++++ Error between functions:
Cannot find description of structure (UNARY-OP (NAME "NOT NULL")) to use for inclusion.

**++++ Error between functions:
Cannot find description of structure (UNARY-POSTFIX-OP (NAME "DESC")) to use for inclusion.

**++++ Error between functions:
Cannot find description of structure (UNARY-POSTFIX-OP (NAME "ASC")) to use for inclusion.

**++++ Error between functions:
Cannot find description of structure (UNARY-SPLICING-OP (NAME "DISTINCT")) to use for inclusion.

**++++ Error between functions:
Cannot find description of structure (SQL-OP (NAME "ON")) to use for inclusion.

**++++ Error between functions:
Cannot find description of structure (INFIX-OP (NAME "=")) to use for inclusion.

**++++ Error between functions:
Cannot find description of structure (INFIX-OP (NAME "!=")) to use for inclusion.

**++++ Error between functions:
Cannot find description of structure (INFIX-OP (NAME "<")) to use for inclusion.

**++++ Error between functions:
Cannot find description of structure (INFIX-OP (NAME ">")) to use for inclusion.

**++++ Error between functions:
Cannot find description of structure (INFIX-OP (NAME "<=")) to use for inclusion.

**++++ Error between functions:
Cannot find description of structure (INFIX-OP (NAME ">=")) to use for inclusion.

**++++ Error between functions:
Cannot find description of structure (INFIX-OP (NAME "@<")) to use for inclusion.

**++++ Error between functions:
Cannot find description of structure (INFIX-OP (NAME "@>")) to use for inclusion.

**++++ Error between functions:
Cannot find description of structure (INFIX-SPLICING-OP (NAME "AS")) to use for inclusion.

**++++ Error between functions:
Cannot find description of structure (INFIX-LIST-OP (NAME "IN")) to use for inclusion.

**++++ Error between functions:
Cannot find description of structure (INFIX-LIST-OP (NAME "NOT IN")) to use for inclusion.

**++++ Error between functions:
Cannot find description of structure (INFIX-OP (NAME "LIKE")) to use for inclusion.

**++++ Error between functions:
Cannot find description of structure (CONJUNCTIVE-OP (NAME "OR")) to use for inclusion.

**++++ Error between functions:
Cannot find description of structure (CONJUNCTIVE-OP (NAME "AND")) to use for inclusion.

**++++ Error between functions:
Cannot find description of structure (CONJUNCTIVE-OP (NAME "+")) to use for inclusion.

**++++ Error between functions:
Cannot find description of structure (CONJUNCTIVE-OP (NAME "-")) to use for inclusion.

*++++ Error between functions:
Cannot find description of structure (CONJUNCTIVE-OP (NAME "")) to use for inclusion.

**++++ Error between functions:
Cannot find description of structure (CONJUNCTIVE-OP (NAME "/")) to use for inclusion.

**++++ Error between functions:
Cannot find description of structure (CONJUNCTIVE-OP (NAME "%")) to use for inclusion.

**++++ Error between functions:
Cannot find description of structure (CONJUNCTIVE-OP (NAME "UNION")) to use for inclusion.

**++++ Error between functions:
Cannot find description of structure (CONJUNCTIVE-OP (NAME "UNION ALL")) to use for inclusion.

*++++ Error between functions:
Cannot find description of structure (SQL-OP (NAME "")) to use for inclusion.
.
; ** 29 errors detected, no fasl file produced.

Using returning (with multiple columns) on a insert statement generates incorrect query.

(returning :id :name)

produces

ID(`name`)

expected

`id`, `name`

Hi,

By following SxQL and cl-dbi's doc I have come to this workflow:

(defun my-query () (select …))

(let* ((query (dbi:prepare *connection*
                           (yield my-query)))
       (query (dbi:execute query)))
  (dbi:fetch[-all] query)

When my query ends with a value

    (where (:> :row.quantity 0)))) 

the SxQL object looks similar:

#<SXQL-STATEMENT:  … WHERE (row.quantity > 0)

but I need yield to get the query string, and yield replaces the 0 with a ? placeholder and returns it as a second value:

"… WHERE (row.quantity > ?)""
(0)

then on the call to dbi:execute I have to repeat the 0 argument (which is clearly an issue when the code is split into functions).

I can maybe use multiple-value-bind but I find it unnecessarily tedious. Did I miss something, or could there be a function that simplifies all this?

Thanks

There are some operators missing from :sxql.operator, so I was trying to use sxql.operator::define-op to add the @@ operator for search in postgres, so I wrote:

(sxql.operator::define-op (:aa sxql.operator::infix-op :sql-op-name "@@"))

Then I was unable to use it in a where clause, it always used the default function application syntax (e.g. @@(field-name plainto_tsquery(?))).

It turns out that this is because, when define-op interns symbols, it doesn't specify a package to intern into, and so it was defining aa-op in :cl-user, then the op wasn't found and used by the query generator.

I know this isn't in the public interface, but it is useful to be able to define operators. I don't know enough about other dialects of sql to know if @@ is supported widely enough to submit a PR to add it to sxql, but maybe just making define-op intern the symbols in :sxql.operator so they are actually usable would be a nice solution.

The union family of commands generate parentheses, which are invalid in SQLite.

e.g. from README

(union-queries
 (select (:name :birthday) (from :fulltime))
 (select (:name :birthday) (from :parttime)))
;=> #<SXQL-OP: (SELECT name, birthday FROM fulltime) UNION (SELECT name, birthday FROM parttime)>

For SQLite, this should generate:

SELECT name, birthday FROM fulltime UNION SELECT name, birthday FROM parttime

Without the brackets around the SELECT statements.

So, for Crane, I'd like to keep symbols as human-friendly as possible, which means avoiding unnecessary quoting when it's not needed (e.g., symbols that don't have dashes).

So, here's a feature request: a way to set a custom function that SxQL calls to transform a symbol to an SQL identifier.

...or at least a step in that direction. This is closely related to #36 and #37, but I thought a new issue would be a better place to discuss it. The proposal:

1. Turn sxql:make-op into a generic function that uses eql specializers on the first argument.
2. Provide a default implementation that does what the current function does (i.e. searches the sxql.operator package[1]).
3. Use a regular symbol in define-op instead of a keyword, and remove the interning.
4. Apply the same treatment to sxql:make-clause if possible.

Defining a new operator would look like this:

(define-op (aref infix-op))

(defmethod make-op ((arg (eql :aref)) &rest args)
  (apply #'make-aref-op args))

(defmethod yield ((arg aref-op))
  ;; Not actually how you'd do it.
  (values (format nil "~A[~A]"
                      (yield (aref-op-left arg))
                      (yield (aref-op-right arg)))
               '()))

I don't think precedence of user-defined ops is an issue here, because we're generating this based the lisp tree which is already in the correct structure. I'm less sure about clauses, because e.g. the select statement sorts them, and user-defined clauses wouldn't have a way to specify order.

[1] Using find-symbol and not intern, of course; using intern will leave garbage symbols laying around if you call it with an op that doesn't exist.

Is there the way to write

SELECT * from users
WHERE id NOT IN (
  SELECT id from banned_users
)

.

A mixed case column or table name is usable with the pipes :|someThing|, will translate to "someThing" (using postgres), however just as :someThing is being converted to :SOMETHING by common lisp, which is then downcased by sxql to :something - how do we work with a column that is in all caps? (db column SOMETHING)?

How do I save array or list into db? What type of field should I use? Thank you.

I am attempting to build a database that is implemented through an already constructed LISP routine in AutoCAD. The LISP routine already has a bunch of data members that complete a function/task. I am trying to add to that already established and working routine to inject the data from the members into a Database in SQL Server. I have come across this framework, and I am interested to know if this would work to solve that problem. It seems to me that the code present is already functioning, and if I were to implement it into my project, I am imagining that I would just need to utilize your functions to complete the tasks I am trying to accomplish. Here is my question: How do I go about setting this up within my AutoCAD LISP library to utilize these functions. I have gone through the readme, but it looks to me that it only explains the functions and how they can be used as well as their outputs, however I don't understand how I am to implement your code to begin using these functions. Any advice or help would be appreciated! Thank you!

(defun create-table-2 (table-name value-type)
  (create-table (table-name :if-not-exists t)
      ((id :type value-type :primary-key t))))

(defun insert-into-2 (table-name value)
  (insert-into table-name
    (set= :id (get-universal-time) :value value)))

The two functions above both pass table-name into their respective sxql macros. We can see below that table-name is treated differently by each macro. create-table uses the literal symbol table-name and insert-into uses the variable table-name.

> (create-table-2 'my-table 'foo)
#<SXQL-STATEMENT: CREATE TABLE IF NOT EXISTS table-name (
    id FOO PRIMARY KEY
)>
> (insert-into 'my-table 'foo)
#<SXQL-STATEMENT: INSERT INTO my-table foo>

I suspect this is because the create-table macro expands table-name into a quoted list but insert-into expands into the literal symbol.

(MAKE-STATEMENT :CREATE-TABLE '(TABLE-NAME :IF-NOT-EXISTS T)
                (LIST
                 (SXQL.CLAUSE:MAKE-COLUMN-DEFINITION-CLAUSE 'ID :TYPE 'INTEGER
                                                            :PRIMARY-KEY T)))

(MAKE-STATEMENT :INSERT-INTO TABLE-NAME
                (SET= :ID (GET-UNIVERSAL-TIME) :VALUE VALUE))

Any thoughts on how this might be fixed? I've tried grokking the code but my macro foo isn't there yet.

The example in the README says you can do:

(insert-into :users
  (set= :name "Jack"
        :jinbei-size "small")
  (returning :id))

But I get this:

CL-USER> (use-package :sxql)
T
CL-USER> (insert-into :users
  (set= :name "Jack"
        :jinbei-size "small")
  (returning :id))

; in: INSERT-INTO :USERS
;     (RETURNING :ID)
; 
; caught STYLE-WARNING:
;   undefined function: RETURNING

a form like this

INSERT INTO table (attr1, attr2) VALUES (val11, val12), (val21, val22);

with something like this:

(let ((itmes (list (list val11 val12) (list val21 val22)))))
  (insert-into :table '(:attr1 :attr2) items))

Is this possible?

There's no way to specify foreign keys with the create-table form.

ECL (Embeddable Common-Lisp) 16.1.3

> (ql:quickload :sxql)
To load "sxql":
  Load 1 ASDF system:
    sxql
; Loading "sxql"
[package sxql.sql-type]......
Condition of type: SIMPLE-ERROR
Attempt to redefine the structure SXQL.SQL-TYPE:SQL-EXPRESSION-LIST incompatibly with the current definition.

just seeing that create index doesn't have a if-not-exists, I tried poking around the code but apparently i have no idea what I'm doing in there.

How to fix next problem?

CL-USER> (sxql:create-table :enemy
             ((name :type 'string
                    :primary-key t)
              (age :type 'integer
                   :not-null t)
              (address :type 'text
                       :not-null nil)
              (fatal_weakness :type 'text
                              :not-null t
                              :default "None")
              (identifying_color :type '(:char 20)
                                 :unique t)))
#<SXQL-STATEMENT: CREATE TABLE "enemy" ("name" STRING PRIMARY KEY, "age" INTEGER NOT NULL, "address" TEXT, "fatal_weakness" TEXT NOT NULL DEFAULT 'None', "identifying_color" CHAR(20) UNIQUE)>
CL-USER> (sxql:yield *)
"CREATE TABLE \"enemy\" (\"name\" STRING PRIMARY KEY, \"age\" INTEGER NOT NULL, \"address\" TEXT, \"fatal_weakness\" TEXT NOT NULL DEFAULT ?, \"identifying_color\" CHAR(20) UNIQUE)"
("None")

Mysql response is:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"enemy" ("name" STRING PRIMARY KEY, "age" INTEGER NOT NULL, "address" TEXT, "fat' at line 1

Thank you!

Not clear to me how you write a parameterized query in sxql with yield and compile. Can this be explained a bit more in the documentation?

Apologies if there is a simple answer, I'm new to lisp and was just wondering how I should be doing this. In the documentation the expected us of set= is like this:

(set= :id 123 :name "Jim") 

but I'd like to be able to use it like this:

(let ((data '(:id 123 :name "Jim"))) 
  (set= data))

I've tried using values-list when passing data to set= with no luck, and was just wondering how you would do it?

Thanks
Jim

(sxql:insert-into :person
  (sxql:set= :sex "male"
        :age 25
        :full-name "Eitarow Fukamachi")) ;; Right here

;; #<SXQL-STATEMENT: INSERT INTO person SET sex = 'male', age = 25, full-name = 'Eitarow Fukamachi'>

(Ignore the SET in the statement, I'm stuck on the old version from Quicklisp)

Hi all,

The problem can be illustrated via the following:

(ql:quickload :sxql)
(in-package :sxql)
(from :Blub)
#<SXQL-CLAUSE: FROM `blub`>

(from :|Blub|)
#<SXQL-CLAUSE: FROM `blub`>

(from :"Blub")
#<SXQL-CLAUSE: FROM 'Blub'>

Obviously in example 1 it is just defaulting to lowercase. In example two, same deal (disregarding the pipes).

In example 3, it is triggering the #'yield method that applies to variables (not symbols), hence the reason for the single quotes surrounding the resultant clause string.

I'm a little too tired to work on a pull request right now, hopefully this can be fixed.

Thanks!

I don't know if anyone's actually maintaining this package anymore, but I looked at the source and can't figure out how to solve it.

The problem here is sometimes I want to use a where or limit clause, and sometimes I don't. So given that select is a macro, how do I selectively include or exclude a clause?

Here's what I tried:

(defun select-from-db (my-limit my-where)
 (select columns
  (from mytable)
  (and my-limit (where my-limit)
  (and my-where (limit my-where)))

The idea being that I can pass nil for these values, and have the clauses omitted. But this doesn't work. The package complains that "you can't have null twice" or some such.

Without duplicating my code four times, how can I selectively include or exclude clauses?

Join clauses only support a single-clause match expression:

(sxql:left-join :foo :on (:= :bar :baz))
;=> #<SXQL-CLAUSE: LEFT JOIN "foo" ON ("bar" = "baz")>

(sxql:left-join :foo :on (:and (:= :bar :baz) (:= :quux :bletch)))
;=> error, "The function := is undefined"

If you quote the subexpressions, you run into another error, because the JOIN-CLAUSE struct expects the :on clause to be of type (or null =-op). As I understand it, join conditions can be any boolean expression, and it'd be nice to support more complex conditions.

In postgres, one wants to write:

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ name ] ON table [ USING method ] ...

whereas mysql wants:

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option]
    [algorithm_option | lock_option] ...

index_col_name:
    col_name [(length)] [ASC | DESC]

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

index_type:
    USING {BTREE | HASH}
...

is there a good way to handle different output for different SQL dialects?

Hi, thank you for your contribution to this repository. May I ask does this repository supports a SQL to CommonLISP transform? Or is it possible to transform SQL to CommonLISP based on this repository?

Please consider adding :description, :author and :license information to your ASDF system. This will greatly help Quicklisp users and make it easier for them to report bugs.

More information:

http://blog.quicklisp.org/2015/05/looking-for-more-metadata.html
https://www.quicklisp.org

Use case:

SELECT id, sum(hoge) FROM foobar GROUP BY id HAVING sum(hoge) >= 88

Hello.

Is it possible to build SQL queries dynamically at run-time? Say I want to include an "order-by" clause in the query only if some "order-by" function argument is given. Is that possible?

(defun build-query (&key order-by)
       <build the SQL query here with an order-by clause only if order-by argument has a value>)

The problem I have is that sxql:order-by is a macro, so I cannot decide to apply it or not at run-time; that needs to be decided already at compile-time.

Do I have to use make-clause to build the query dynamically instead?