Update of /project/pg/cvsroot/pg In directory common-lisp.net:/tmp/cvs-serv10481 Modified Files: parsers.lisp pg-tests.lisp CREDITS Log Message: Add support for the SQL NUMERIC type, thanks to Risto Sakari Laakso. Was previously being parsed as an integer, but is in fact a fix-precision floating-point number. Date: Tue Sep 7 14:52:19 2004 Author: emarsden Index: pg/parsers.lisp diff -u pg/parsers.lisp:1.5 pg/parsers.lisp:1.6 --- pg/parsers.lisp:1.5 Fri Aug 13 18:45:07 2004 +++ pg/parsers.lisp Tue Sep 7 14:52:19 2004 @@ -72,7 +72,7 @@ ("char16" . ,'text-parser) ("text" . ,'text-parser) ("varchar" . ,'text-parser) - ("numeric" . ,'integer-parser) + ("numeric" . ,'numeric-parser) ("int2" . ,'integer-parser) ("int4" . ,'integer-parser) ("int8" . ,'integer-parser) @@ -96,7 +96,34 @@ ;; see `man pgbuiltin' for details on PostgreSQL builtin types (defun integer-parser (str) (parse-integer str)) -;; FIXME switch to a specialized float parser +;; from Risto Sakari Laakso <rlaakso@cc.hut.fi> +;; +;; http://www.postgresql.org/docs/7.4/static/datatype.html#DATATYPE-NUMERIC-DEC... +;; +;; NUMERIC(precision, scale) +;; +;; The scale of a numeric is the count of decimal digits in the +;; fractional part, to the right of the decimal point. The precision of a +;; numeric is the total count of significant digits in the whole number, that +;; is, the number of digits to both sides of the decimal point. +(defun numeric-parser (str) + (let ((dot-pos (position #\. str)) + integer-part + (decimal-part 0)) + ;; parse up to #\., or whole string if #\. not present + (setq integer-part (parse-integer (subseq str 0 dot-pos))) + ;; if #\. present .. + (when dot-pos + (let* ((decimal-str (subseq str (1+ dot-pos))) + (dec-str-len (length decimal-str))) + + ;; if has at least one digit after #\. + (when (> dec-str-len 0) + ;; parse integer after #\. and divide by 10^(digits), i.e. ".023" => 23/1000 + (setq decimal-part (/ (parse-integer decimal-str) (expt 10 dec-str-len)))))) + (+ integer-part decimal-part))) + +;; FIXME switch to a specialized float parser that conses less (defun float-parser (str) (declare (type simple-string str)) (let ((*read-eval* nil)) Index: pg/pg-tests.lisp diff -u pg/pg-tests.lisp:1.8 pg/pg-tests.lisp:1.9 --- pg/pg-tests.lisp:1.8 Fri Aug 13 18:50:37 2004 +++ pg/pg-tests.lisp Tue Sep 7 14:52:19 2004 @@ -82,6 +82,32 @@ (when created (pg-exec conn "DROP TABLE count_test_float"))))))) +(defun test-insert/numeric () + (format *debug-io* "Testing INSERT & SELECT on NUMERIC ...~%") + (with-test-connection (conn) + (let ((res nil) + (sum 0) + (created nil)) + (unwind-protect + (progn + (pg-exec conn "CREATE TABLE count_test_numeric(key int, val numeric(10,2))") + (setq created t) + (loop :for i :from 1 :to 1000 + :for sql = (format nil "INSERT INTO count_test_numeric VALUES(~d, ~f)" + i i) + :do (pg-exec conn sql)) + (setq res (pg-exec conn "SELECT count(val) FROM count_test_numeric")) + (assert (eql 1000 (first (pg-result res :tuple 0)))) + (setq res (pg-exec conn "SELECT sum(key) FROM count_test_numeric")) + (assert (eql 500500 (first (pg-result res :tuple 0)))) + ;; this iterator does the equivalent of the sum(key) SQL statement + ;; above, but on the client side. + (pg-for-each conn "SELECT val FROM count_test_numeric" + (lambda (tuple) (incf sum (first tuple)))) + (assert (eql 500500 sum))) + (when created + (pg-exec conn "DROP TABLE count_test_numeric")))))) + (defun test-date () (format *debug-io* "Testing DATE and TIMESTAMP parsing ...~%") (with-test-connection (conn) @@ -355,7 +381,7 @@ (pg-exec conn "DROP TABLE foo"))) (defun test () - (let ((*pg-client-encoding* "UNICODE")) + (let (#+nil (*pg-client-encoding* "UNICODE")) (with-test-connection (conn) (format t "Running pg.lisp tests against backend ~a~%" (pg-backend-version conn)) ;; client encoding supported since PostgreSQL v7.1 @@ -373,6 +399,7 @@ (test-simple) (test-insert) (test-insert/float) + (test-insert/numeric) (test-date) (test-booleans) (test-integrity) Index: pg/CREDITS diff -u pg/CREDITS:1.3 pg/CREDITS:1.4 --- pg/CREDITS:1.3 Wed Aug 11 15:26:41 2004 +++ pg/CREDITS Tue Sep 7 14:52:19 2004 @@ -28,4 +28,5 @@ Brian Mastenbrook: Implemented MD5 authentication support - +Risto Sakari Laakso: + Provided a parser for the NUMERIC type