- postmodern-devel - mailman3.common-lisp.net

[postmodern-devel] loading trivial-utf-8
by Jens Teich 02 Aug '11

02 Aug '11

In my project trivial-utf-8 is already loaded. Postmodern does it again which leads to warnings: Warning: (DEFPARAMETER *OPTIMIZE*) being redefined in C:\Users\jens\quicklisp\dists\quicklisp\software\trivial-utf-8-20101006-darcs\trivial-utf-8.lisp (previously in C:\Users\jens\quicklisp\dists\quicklisp\software\postmodern-20110619-git\cl-postgres\trivial-utf-8.lisp). Warning: (DEFUN UTF-8-BYTE-LENGTH) being redefined in ... What is the best strategy to avoid this? Thanks Jens

2 3

[postmodern-devel] sb-md5
by Kirill Sapelkin 05 Jul '11

05 Jul '11

Hello, I have the sbcl 1.0.43 and am trying to use postmodern to connect to postgresql 9.0.4. All this is on my machine. I had commented out the bordeau-threads and closer-mop from postmodern.asd and replaced mention of md5 with sb-md5 in cl-postgres.asd. Running: (asdf:oos 'asdf:load-op :postmodern) fails. The screen scrolls so fast I can't see the beginning but can see: 0: Try recompiling messages Does that mean it coughed on messages.lisp? Can I use sbcl's sb-md5? Should I change all mention of md5 to sb-md5 in all postmodern files? Thanks. Kirill

2 1

[postmodern-devel] turning off threading support
by Kirill Sapelkin 02 Jul '11

02 Jul '11

Hello, I think I would like to turn off threading support as this is to be used only on one machine. Line 6 of postmodern.asd says: ;; Change this manually to turn threading support off or on Am real, real new at this and can't figure out what to change. If this has been covered before, please point me. Checked the archive back a year or so but could find nothing. If this is posted on the wrong list, please let me know. Thanks. Kirill

2 1

[postmodern-devel] VACUUM, ANALYZE
by John Morrison 22 Jun '11

22 Jun '11

Hi All; I am using PostModern for PostGIS databases, and need to periodically VACUUM and/or ANALYZE the database. I ended up writing SQL ops for VACUUM and ANALYZE. There are some caveats which I mention in case their behavior should be changed and/or they should be rewritten to be more elegant. I am torn between embarrassment at the attached code and my desire to contribute to PostModern (which I find so useful). Obviously, I chose to brave embarrassment. In any event, advice, suggestions, and feedback are welcomed (in decreasing order). (1) The syntax I use does not follow the SQL spec. Instead of: VACUUM FREEZE VACUUM FULL FREEZE VERBOSE table0 column0 column1 I respectively do: (:vacuum :freeze) (:vacuum 'table0 'column0 'column1 :full :freeze :verbose) Please advise if this is the Wrong Thing to do, and if so, which arg-processing macros/helpers I should be using instead of the one I have perhaps, er, misused. (2) The split-on-keywords mechanism seems to enforce order of optional SQL arguments (i.e., FREEZE must precede VERBOSE). I am not sure that is either necessary or acceptable, or I am missing something. (3) The argument-sniffing at the end of the split-on-keywords is ugly. It sniffs the args to see if there are (non-keyword)table/column optional SQL arguments (implying a list of table, and optionally columns), and only sticks in a (:vars ...) if there are any. -jm -- --- John Morrison --- john.nmi.morrison(a)gmail.com

2 2

[postmodern-devel] using dao-class on database with column names like "addr:city"
by John Morrison 14 Jun '11

14 Jun '11

Hi All; Sorry if this is a dumb newbie question. I have a PostGIS database with column names that are rather unfortunate (and created not by me but by/via many different bits of GIS software, and they sadly seem not to have mechanisms to specify rewriting of column names on the fly during the workflow). Some of the tables have column names which have colons in them. When I try and do the first Obvious Thing I can think of (name my dao-defclass slots e.g., |addr:city| in an attempt to match the column name), get-dao complains thusly: --- cut here --- No slot named addr:city in class CM-LOCATION. DAO out of sync with table, or incorrect query used. --- cut here A trip into the debugger leads me to believe that the slot name |addr:city| in the dao-class defclass gets rewritten as "addr_city", and then the get-dao from the table with column name "addr:city" (which does not seem to likewise get passed through to-sql-name, as it IS in fact an sql name) fails. (FWIW I have never had any trouble whatsoever with normally-named columns in almost all other databases I have processed.) Another Obvious Thing to try - setting *escape-sql-names-p* around the defclass (as suggested in another mailing to this list circa 2008) didn't seem to affect things either (but that could be Pilot Error). The final Obvious Thing to try was figuring out how to set the sql-name in the slot definition, but there seemed to be no initarg or other mechanisms I could spot. If some kind soul out there would point me in the right direction, I'd greatly appreciate it. Thanks in advance, -jm p.s., and then there are the database column names (e.g., "type") that are locked in various CL packages... -- --- John Morrison --- john.nmi.morrison(a)gmail.com

4 6

[postmodern-devel] TRUNCATE missing
by Nicolas Martyanoff 22 May '11

22 May '11

Hi, It seems the TRUNCATE¹ operation is missing, would it be possible to add it to postmodern? [1] http://www.postgresql.org/docs/9.0/interactive/sql-truncate.html Best regards, -- Nicolas Martyanoff http://codemore.org khaelin(a)gmail.com

2 5

[postmodern-devel] patch to make postmodern compile
by Nicolas Martyanoff 25 Apr '11

25 Apr '11

The last git version of postmodern doesn't compile on SBCL 1.0.47 because there are declaration order issues in postmodern/table.lisp. The following patch fixes these problems: Best regards, -- Nicolas Martyanoff http://codemore.org khaelin(a)gmail.com

3 4

[postmodern-devel] patch for array slicing
by Nicolas Martyanoff 25 Apr '11

25 Apr '11

Hi, I wrote a tiny patch to add support for array slicing: So now: (:[] #(1 2 4 8) 3) → "(ARRAY[1,2,4,8])[3]" (:[] #(1 2 4 8) 2 4) → "(ARRAY[1,2,4,8])[2:4]" I hope you will find it useful. Best regards, -- Nicolas Martyanoff http://codemore.org khaelin(a)gmail.com

2 1

[postmodern-devel] bug in the array interpreter
by Nicolas Martyanoff 25 Apr '11

25 Apr '11

I think I found a pretty nasty bug; the array parser loop foreven on empty arrays, eating all the RAM it can. Triggering it is really simple, just select an empty array. The effect is directly visible with: (funcall (cl-postgres::read-array-value #'parse-integer) "{}") I think there's something wrong in the following sexp in READ-ARRAY-VALUE: (loop :for x := arr :then (car x) :while (listp x) :collect (length x)) If ARR is nil, it loops forever since both (listp nil) and (car nil) yield t. I'm not sure I understand how the code works for multi-dimensional arrays, so I'd prefer not to break something trying a fix; would you mind fix it ? Thank you. Best regards, -- Nicolas Martyanoff http://codemore.org khaelin(a)gmail.com

2 1

[postmodern-devel] How to remove trailing spaces?
by Slobodan Milnović 01 Mar '11

01 Mar '11

Hi, I have some fixed length text fields, that are for now only partially filled. For these, when I select them, I get a lot of empty space after the text, and it remains so in variables. Is there a way to "prune" the empty space, for example before the DAO is instantiated, or before the result is returned in "non-DAO" selects? BTW, my reasoning in creating fixed length text fields is that postmodern should be faster than with the fields with variable length. I can still change that.

3 5

[postmodern-devel] set
by Haris 28 Feb '11

28 Feb '11

I have a query like this: (query (sql (:update 'kupci :set 'ime (parameter "ime") 'prezime (parameter "prezime") 'adresa (parameter "adresa") and I want now for the next parameter to check if something is passed, I tried (:raw (if (/= (length (parameter "sobnost")) 0) (sql (:set 'sobnost (parameter "sobnost"))))) but it doesn't work. Do I have to use sql-compile maybe ?

1 0

[postmodern-devel] :IN/:NOT-IN on an empty :SET always results in :NULL
by J.P. Larocque 27 Feb '11

27 Feb '11

Hi, I'd like to be able to write a query similar to: (let ((set ...)) (postmodern:sql (:select ... :from ... :where (:not-in ... (:set set))))) But SET could be empty. This results in the query: (SELECT ... FROM ... WHERE (... NOT IN (NULL))) (The same happens when simply (:SET) is given.) S-SQL compiles an empty set to "(NULL)", whether the empty set is expressed statically with no arguments, or with a single argument evaluating to the empty list. The problem is that "(NULL)" does not express the empty set. The PostgreSQL IN and NOT IN operators (at least for explicit sets on the right-hand side) are defined[1] to evaluate to NULL whenever: * NULL is a member of the literal set on the right-hand side, and * The left-hand side value is not a member of the set. (Or of course the left-hand side is NULL.) [1] http://www.postgresql.org/docs/8.3/static/functions-comparisons.html#AEN154… This means that (:IN x (:SET)) and (:IN x (:SET y)), for any value of x and for y being the empty list, both always result in :NULL. The same goes for :NOT-IN. (postmodern:query (:select (:in 42 (:set))) :single) => :NULL ...where the result should be NIL (is 42 is a member of the empty set? no). (postmodern:query (:select (:not-in 42 (:set))) :single) == (postmodern:query (:select (:not (:in 42 (:set)))) :single) => :NULL ...where the result should be T (is 42 not a member of the empty set? yes). This is probably going to be tricky to solve. The above part of the PostgreSQL manual doesn't say anything about expressing an empty set on the RHS of IN/NOT IN. I tried looking through the grammar[2], but it's rather unwieldy and hard for me to follow. [2] http://git.postgresql.org/gitweb?p=postgresql.git;a=blob;f=src/backend/pars… Some people[3] suggest expressing the empty set as e.g. (SELECT 1 WHERE FALSE): > select 42 in (select 1 where false); ?column? ---------- f (1 row) [3] http://groups.google.com/group/pgsql.general/msg/736022e9c06affac?hl=en But the problem with this is that this empty set is typed: > select 'foo' in (select 1 where false); ERROR: invalid input syntax for integer: "foo" So, the tricky problem is: how does one correctly compile an empty :SET S-SQL expression to an expression of an empty set, that works in any context (that is, no matter what the data type of the LHS is)? Instead, maybe some trickery can be done in the definition for :IN. It could check to see whether the RHS is an S-SQL expression for the empty set and, if so, result in "false" (or "true" for :NOT-IN). This would be ugly, but closer to correct. (Caveat: when the LHS is null at query time, the result has to be NULL. So, when the RHS of :IN is the empty set, the expander would have to return an expression which checks to see whether the compiled LHS expression is null, and if so, result in "null" instead of "false".) Any thoughts on better ways? -- J.P. Larocque <jpl(a)thoughtcrime.us>

2 1

[postmodern-devel] sql-compile
by Haris 27 Feb '11

27 Feb '11

What am I doing wrong here: (query (sql-compile `(:update 'kupci :set ,@(list 'ime (parameter "ime")) :where (:= 'id (parameter "id"))))) I get from hunchentoot log: Database error 42883: function parameter(unknown) does not exist No function matches the given name and argument types. You might need to add explicit type casts. Query: UPDATE kupci SET ime = E'a' WHERE (id = parameter(E'id'))

2 1

[postmodern-devel] strange
by Haris 26 Feb '11

26 Feb '11

I have a query like: (query (sql-compile `(:insert-into 'kupci :set ,@(list 'vrijemeunosa (:now))))) If I try to execute it from repl it works but when called within function I get Undefined function :NOW called with arguments () ?

2 3

[postmodern-devel] :order-by
by Haris 22 Feb '11

22 Feb '11

Hi. How to use :order-by (or :desc): (:select '* :from 'table :order-by 'column) doesn't work ? Thanks

2 1

[postmodern-devel] date
by Haris 20 Feb '11

20 Feb '11

Hi. I know this may not be the right place to ask such a question, can you give me a link to some postgresql mailing list, I found them many, I don't know which one to choose ? How to enter date and time in a date field ? Is there a default value for date field like current date and time ? Thanks

3 3

[postmodern-devel] update example
by Haris 17 Feb '11

17 Feb '11

Hi. Can you just give me an update example with :where statement ? Thanks

2 1

[postmodern-devel] Patch: {} syntax for arrays should be inside ''
by Stas Boukarev 15 Feb '11

15 Feb '11

According to http://www.postgresql.org/docs/current/static/arrays.html#ARRAYS-INPUT arrays should look like '{ val1 delim val2 delim ... }', but cl-postgres sends them as {val1 ...}, the attached patch corrects this. -- With Best Regards, Stas.

2 5

[postmodern-devel] encoding problem
by Nicolas Martyanoff 15 Feb '11

15 Feb '11

I have a query which inserts text containing the '’' character (unicode U+2019). Postmodern seems to generate a correct SQL query, but I get a postgresql error: Database error 08P01: invalid message format The database was created with and UTF-8 encoding. I'm not sure where the problem is, perhaps postmodern doesn't correctly encode multibyte characters. Regards, -- Nicolas Martyanoff http://codemore.org khaelin(a)gmail.com

3 13

[postmodern-devel] table name problem
by Nicolas Martyanoff 15 Feb '11

15 Feb '11

Hi, When trying to reproduce my encoding bug, I found a problem when a field is named "t". CREATE TABLE test (t TEXT NOT NULL); (postmodern:execute (:insert-into 'test :set 't "utf8 é")) Database error 42601: syntax error at or near "true" Query: INSERT INTO test (true) VALUES (E'utf8 é') [Condition of type CL-POSTGRES-ERROR:SYNTAX-ERROR-OR-ACCESS-VIOLATION] Regards, -- Nicolas Martyanoff http://codemore.org khaelin(a)gmail.com

2 1

[postmodern-devel] :set arguments
by Haris 06 Feb '11

06 Feb '11

(sql (:insert-into 'kupci :set (:raw (if (/= (length (parameter "ime")) 0) (sql ('ime (parameter "ime"))))))) For this I get an error: Invalid amount of :set arguments passed to insert-into sql operator Why is that ? Thanks

2 4

[postmodern-devel] Release 1.17
by Marijn Haverbeke 02 Feb '11

02 Feb '11

Most users are probably already using the git HEAD (which is pretty safe, since I don't push many wild experimental patches), but I just released a new version. The important changes are: * Fixes day-of-week in simple-date. * Makes the :plist query format actually work. * Adds sql-escape as an exported symbol for client code that needs to escape stuff. * Adds support for multi-dimensional arrays. * Adds the *ignore-unknown-columns* configuration variable. Cheers, Marijn

1 0

[postmodern-devel] query
by Haris 30 Jan '11

30 Jan '11

I'm sorry for multiple threads. This subject was total misunderstanding by me. The thing that was confusing me was that if I don't enter a search term into the search field then it would not return that record even if some of the fields match. The right way is a dynamic query like this: (sql (:select '* :from 'kupci :where (:and (:raw (if (parameter "ime") (sql (:like 'ime (concat-string "%" (parameter "ime") "%")))))) (:raw (if (parameter "prezime") (sql (:like 'prezime (concat-string "%" (parameter "prezime") "%")))))))) I first have to check is the search term even entered into the search field. It finally works.

1 0

[postmodern-devel] query
by Haris 30 Jan '11

30 Jan '11

I got it. I have to :or it with :is-null, like this: (sql (:select '* :from 'table :where (:or (:like 'field "%something%") (:is-null 'field))))

2 1

[postmodern-devel] query
by Haris 30 Jan '11

30 Jan '11

I know what are you talking about, I read the Practical Common Lisp, I'm reading On lisp now but I didn't read all the Postmodern docs. (I know now what :raw does) What I really want is when the field is null to return true. (:is-null 'field) and what now ? Is there an expression "true" in sql ? I should subscribe myself to a sql newsgroup.

1 0