[pg-cvs] CVS update: pg/parsers.lisp pg/pg-tests.lisp pg/CREDITS - pg-cvs@common-lisp.net - mailman3.common-lisp.net

7 Sep 2004

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

    