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

[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

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

29 Jan '11

I made query like this: (sql (:select '* :from 'kupci :where (:and (:raw (if 'ime (sql (:like 'ime (concat-string "%" (parameter "ime") "%"))) t)) (:raw (if 'prezime (sql (:like 'prezime (concat-string "%" (parameter "prezime") %"))) t))))) but it doesn't work. For example, if (:like ...) for 'ime field passes and 'prezime field is null, record will not be selected. Is the test (if 'prezime ...) ok to test that the field is not null ? Or do I have to do something like (if (/= 'prezime nil) ...) ?

2 1

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

28 Jan '11

Hi. I made a query like this: (sql (:select '* :from 'kupci :where (:and (:like 'ime (concat-string "%" (parameter "ime") "%")) (:like 'prezime (concat-string "%" (parameter "prezime") "%")))) I want not to apply "like" if the field in database entry is empty (null). Is there some predefined way to do this or I have to program it myself ? (parameter is a hunchentoot parameter after submitting a form) Thanks

2 1

[postmodern-devel] Date format of alist(s)
by Phil Marneweck 28 Jan '11

28 Jan '11

Hi Postmodern is returning dates like this (:DATE-OF-TERMINATION .255611203200) all of a sudden instead of simpledate objects or date string. What did I change to get this behavior or is it a setting in postgresql that has changed? Regards

3 5

[postmodern-devel] plists
by Haris 26 Jan '11

26 Jan '11

git clone http://marijnhaverbeke.nl/git/postmodern :plists works ok now. Thanks

1 0

[postmodern-devel] syncing the socket
by Andy Chambers 26 Jan '11

26 Jan '11

Hi, My bulk copier is somewhat working now but the next time I try to issue a query on the same connection (using cl-postgres:exec-query), I'm getting "Unexpected message received: Z". I thought that putting a "try-to-sync" after sending my CopyDone message should prevent this but apparently it doesn't. Any ideas what's going on? -- Andy Chambers

2 1

[postmodern-devel] plists
by Haris 26 Jan '11

26 Jan '11

I tried git clone https://github.com/marijnh/Postmodern/commit/1627b8a8f8d19181112ba9d6b9eb54… but it doesn't work. What exactly should I write to get this from git ?

2 1

[postmodern-devel] plists
by Haris 26 Jan '11

26 Jan '11

Hi. I tried (query "select * from table1;" :plists) but I get: Error: Undefined operator NIL in form (NIL (CL-POSTGRES:EXEC-QUERY *DATABASE* "select * from table1;" (QUOTE POSTMODERN::SYMBOL-PLIST-ROW-READER))) What does that mean ? I'm using Lispworks/Windows. I also tried (query "select * from table1;" :alists) and that works ok. > I was living in Zagreb ... I'm from Zagreb too.

4 4

[postmodern-devel] plists
by Haris 25 Jan '11

25 Jan '11

Hi. I didn't quite understand the :plists argument. I want for select to return me lists of plists. I tried this: (query "select * from table1;" :plists t) but it doesn't work ? P.S. What's with the Croatia example ? I'm from Croatia.

2 1

[postmodern-devel] Minor bug in the Simple Date reference manual
by Sebastian Tennant 25 Jan '11

25 Jan '11

Hi Marijn, There's a very minor bug in the Simple Date reference manual: http://marijnhaverbeke.nl/postmodern/simple-date.html The HTML title tag reads "Postmodern reference manual" whereas I belive it should read "Simple Date reference manual". It's noticeable if/when you create browser bookmarks for the reference manual of each component part of Postmodern. Seb -- Emacs' AlsaPlayer - Music Without Jolts Lightweight, full-featured and mindful of your idyllic happiness. http://home.gna.org/eap

2 1

[postmodern-devel] Can interval be zero?
by Slobodan Milnović 25 Jan '11

25 Jan '11

Hi, I have an situation where I have an interval of "zero" time that I would like to put with the other data in the table. When I try with the (simple-date:encode-interval :millisecond 0) I get the object > (simple-date:encode-interval :millisecond 0) > #<SIMPLE-DATE:INTERVAL P> But, when I try to insert this object into the table with the insert-dao, I get the following error (I have left out other fields, for other non-zero values of interval the insert works fine). Database error 22007: invalid input syntax for type interval: "" Query: INSERT INTO mytable (someinterval) VALUES (interval E'') RETURNING somevalue [Condition of type CL-POSTGRES-ERROR:INVALID-DATETIME-FORMAT] I'm using latest postmodern from the git repository https://github.com/marijnh/Postmodern.git For now I'll use (simple-date:encode-interval :millisecond 1), but I'm wondering if this is an issue with postmodern or postgresql?

3 5

[postmodern-devel] Bulk Copying
by Andy Chambers 25 Jan '11

25 Jan '11

Hi All, I'm trying to add support for bulk copying to postmodern. Does anyone have any ideas on how it should be done. Here's my suggestion... 1) Add message primitives (we seem to already have copy-done-message) (define-message copy-data-message #\d (data) (string data)) (define-message copy-fail-message #\f (reason) (string reason)) 2) Add a "bulk-copier" class with slots for db ; the connection we're using table ; the table we're copying from/to from/to ; whether we're copying from or to a table header ; whether to include a header row in output/input ;; plus slots for the CSV options delimiter null-str quote-str escape-str 3) A function open-copier creates an instance of the above class and sends the corresponding copy statement to Postgres before returning the object. 4) A function copy-row takes a row (i.e. a simple list of Lisp values), and writes them to the stream attached to the copier. 5) A function copy-done lets Postgres know we're finished and commits the change to the database. Anyone got any hints for debugging this low-level stuff? I seem to be able to open a copier (and send the copy statement to pg) but when I send the complete-command message in copy-done, my code is hanging. -- Andy Chambers

2 1

[postmodern-devel] id field
by Haris 24 Jan '11

24 Jan '11

Then I don't need primary key ? If I choose my primary key to be an integer then I have to keep track of numbers and automatically insert number incremented by one ? I worked long ago with Access and it automatically inserts number incremented by one as id. I guess than this is part of Access program and not the underlaying database ?

2 1

[postmodern-devel] id field
by Haris 24 Jan '11

24 Jan '11

Hi. When I try to insert some data in table with (query "insert into ...") it asks me also to enter 'id' field which is a primary key. Shouldn't primary key field be automatically filed ? I made my table with pgadmin 3. Thanks

2 1

[postmodern-devel] Syntax help requested
by Sabra Crolleton 17 Jan '11

17 Jan '11

Hello everyone, I was doing some maintenance and decided to write some meta-functions in s-sql and ran into an issue with "any". In this particular situation I was trying to apply it to pg_index.indkey which is an int2vector. I wanted to get the indexed columns and their attributes from a table (Not the indexes, the indexed columns) The following sql string seems to work, but I wanted to put it into s-sql form. (defun list-indexed-column-and-attributes (table-name) "List the indexed columns and their attributes in a table" (when (table-exists-p table-name) (query "SELECT pg_attribute.attname, format_type(pg_attribute.atttypid, pg_attribute.atttypmod) FROM pg_index, pg_class, pg_attribute WHERE pg_class.oid = $1::regclass AND indrelid = pg_class.oid AND pg_attribute.attrelid = pg_class.oid AND pg_attribute.attnum = any(pg_index.indkey)" table-name))) I can't seem to figure out how to generate the s-sql equivalent of "any(pg_index.indkey)". I ended up with the following, completely avoiding "any". It seems to work, but because postgresql seems to be able to have up to 32 indexed columns per table, it looks clumsy. Any suggestions as to how I can do this better? Sabra (defun list-indexed-column-and- attributes-pm (table-name) "List the indexed columns and their attributes in a table" (query (:select 'pg_attribute.attname (:format_type 'pg_attribute.atttypid 'pg_attribute.atttypmod) :from 'pg_index 'pg_class 'pg_attribute :where (:and (:= 'pg_class.oid (:type table-name :regclass)) (:= 'indrelid 'pg_class.oid) (:= 'pg_attribute.attrelid 'pg_class.oid) (:or (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 0)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 1)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 2)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 3)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 4)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 5)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 6)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 7)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 8)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 9)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 10)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 11)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 12)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 13)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 14)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 15)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 16)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 17)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 18)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 19)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 20)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 21)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 22)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 23)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 24)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 25)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 26)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 27)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 28)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 29)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 30)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 31)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 22)))))))

2 2

[postmodern-devel] Point type
by Slobodan Milnović 02 Jan '11

02 Jan '11

Hi, What would be the best representation of the point type? So far, I have been using following for query: (:[] 'point-column 0) (:[] 'point-column 1) and this for update: 'point-column (:type (concatenate 'string (write-to-string x-coordinate) "," (write-to-string y-coordinate)) point) It works, but seems a bit of a kludge. And how about more complex data types that use points like these http://www.postgresql.org/docs/8.4/static/datatype-geometric.html ?

2 1