From 210a13e0da4ddd46ef23d9bca1bb77da98fa4487 Mon Sep 17 00:00:00 2001 From: "Kevin M. Rosenberg" Date: Tue, 26 Mar 2002 14:12:12 +0000 Subject: [PATCH] r1662: field type optimizations --- clsql.system | 3 +- .../postgresql-socket-api.cl | 103 ++++++++++++++---- sql/package.cl | 9 +- sql/sql.cl | 11 +- sql/utils.cl | 41 +++++++ test-clsql.cl | 17 +-- 6 files changed, 146 insertions(+), 38 deletions(-) create mode 100644 sql/utils.cl diff --git a/clsql.system b/clsql.system index f361df9..33b819d 100644 --- a/clsql.system +++ b/clsql.system @@ -7,7 +7,7 @@ ;;;; Programmer: Kevin M. Rosenberg ;;;; Date Started: Feb 2002 ;;;; -;;;; $Id: clsql.system,v 1.1 2002/03/23 14:04:49 kevin Exp $ +;;;; $Id: clsql.system,v 1.2 2002/03/26 14:12:12 kevin Exp $ ;;;; ;;;; This file, part of CLSQL, is Copyright (c) 2002 by Kevin M. Rosenberg ;;;; @@ -38,6 +38,7 @@ :binary-pathname "CLSQL:sql;bin;" :components ((:file "package") (:file "sql" :depends-on ("package")) + (:file "utils" :depends-on ("package")) (:file "functional" :depends-on ("sql"))) :depends-on (:cmucl-compat) :finally-do diff --git a/interfaces/postgresql-socket/postgresql-socket-api.cl b/interfaces/postgresql-socket/postgresql-socket-api.cl index c33bb13..23d391b 100644 --- a/interfaces/postgresql-socket/postgresql-socket-api.cl +++ b/interfaces/postgresql-socket/postgresql-socket-api.cl @@ -9,7 +9,7 @@ ;;;; ;;;; Date Started: Feb 2002 ;;;; -;;;; $Id: postgresql-socket-api.cl,v 1.5 2002/03/25 23:48:46 kevin Exp $ +;;;; $Id: postgresql-socket-api.cl,v 1.6 2002/03/26 14:12:12 kevin Exp $ ;;;; ;;;; This file, part of CLSQL, is Copyright (c) 2002 by Kevin M. Rosenberg ;;;; and Copyright (c) 1999-2001 by Pierre R. Mai @@ -208,7 +208,8 @@ socket interface" (defun read-socket-sequence (string stream) "KMR -- Added to support reading from binary stream into a string" - (declare (optimize (speed 3) (safety 0))) + (declare (optimize (speed 3) (safety 0)) + (string string)) (dotimes (i (length string)) (declare (fixnum i)) (setf (char string i) (code-char (read-byte stream)))) @@ -563,47 +564,101 @@ connection, if it is still open." do (setf (aref result index) (ldb (byte 1 weight) byte)))) result)) -(defun read-field (socket type) - (let* ((length (read-socket-value 'int32 socket)) - (result (make-string (- length 4)))) - (read-socket-sequence result socket) - (case type - (:int - (parse-integer result)) - (:double - (let ((*read-default-float-format* 'double-float)) - (read-from-string result))) - (t - result)))) -(defun read-field2 (socket type) - (let* ((length (read-socket-value 'int32 socket))) +(defun read-field (socket type) + (let* ((length (- (read-socket-value 'int32 socket) 4))) (case type (:int (read-integer-from-socket socket length)) (:double (read-double-from-socket socket length)) (t - (let ((result (make-string (- length 4)))) + (let ((result (make-string length))) (read-socket-sequence result socket) result))))) +(uffi:def-constant +char-code-zero+ (char-code #\0)) +(uffi:def-constant +char-code-minus+ (char-code #\-)) +(uffi:def-constant +char-code-plus+ (char-code #\+)) +(uffi:def-constant +char-code-period+ (char-code #\.)) + (defun read-integer-from-socket (socket length) (let ((val 0) (first-char (read-byte socket)) - (negative nil)) - (if (eql first-char (char-code #\-)) - (setq negative t) - (setq val (- first-char (char-code #\0)))) + (minusp nil)) + (declare (fixnum first-char)) + (if (eql first-char +char-code-minus+) + (setq minusp t) + (setq val (- first-char +char-code-zero+))) (dotimes (i (1- length)) + (declare (fixnum i)) (setq val (+ (* 10 val) - (- (read-byte socket) (char-code #\0))))) - (if negative + (- (read-byte socket) +char-code-zero+)))) + (if minusp (- 0 val) val))) - +(defmacro ascii-digit (int) + (let ((offset (gensym))) + `(let ((,offset (- ,int +char-code-zero+))) + (declare (fixnum ,int ,offset)) + (if (and (plusp ,offset) + (< ,offset 10)) + ,offset + nil)))) + +#+ignore +(defun read-double-from-socket (socket length) + (let ((before-decimal 0) + (after-decimal 0) + (decimal-count 0) + (exponent 0) + (char (read-byte socket)) + (decimalp nil) + (minusp nil)) + (declare (fixnum first-char)) + (cond + ((eql char +char-code-minus+) + (setq minusp t) + (setq char (read-byte socket)) + (decf length)) + ((eql char +char-code-plus+) + (setq char (read-byte socket)) + (decf length))) + + (dotimes (i (1- length)) + (let ((weight (ascii-digit char))) + (cond + ((and weight (not decimalp)) ;; before decimal point + (setq before-decimal (+ weight (* 10 before-decimal)))) + ((and weight decimalp) ;; after decimal point + (setq after-decimal (+ weight (* 10 after-decimal))) + (incf decimal-count)) + ((and (eql char +char-code-period+) decimalp) + (setq decimalp t)) + ((or (eql char +char-code-e+) ;; E is for exponent + (eql char +char-code-upper-e+)) + (multiple-value-bind (num idx) + (parse-integer string :start (1+ index) :end end + :radix radix :junk-allowed junk-allowed) + (setq exponent (or num 0) + index idx) + (when (= index end) (return nil)))) + ) + (setq char (read-byte socket)))) + + + + + )) + + +(defun read-double-from-socket (socket length) + (let ((result (make-string length))) + (read-socket-sequence result socket) + (let ((*read-default-float-format* 'double-float)) + (read-from-string result)))) (defun read-cursor-row (cursor types) (let* ((connection (postgresql-cursor-connection cursor)) diff --git a/sql/package.cl b/sql/package.cl index 75e6834..9c53992 100644 --- a/sql/package.cl +++ b/sql/package.cl @@ -8,7 +8,7 @@ ;;;; Original code by Pierre R. Mai ;;;; Date Started: Feb 2002 ;;;; -;;;; $Id: package.cl,v 1.2 2002/03/24 04:01:26 kevin Exp $ +;;;; $Id: package.cl,v 1.3 2002/03/26 14:11:59 kevin Exp $ ;;;; ;;;; This file, part of CLSQL, is Copyright (c) 2002 by Kevin M. Rosenberg ;;;; and Copyright (c) 1999-2001 by Pierre R. Mai @@ -95,7 +95,12 @@ #:delete-records #:update-records #:select - #:with-database)) + #:with-database + + ;; utils.cl + #:float-to-sql-string + #:sql-escape-quotes + )) (:documentation "This is the INTERNAL SQL-Interface package of CLSQL.")) (defpackage #:clsql diff --git a/sql/sql.cl b/sql/sql.cl index ea1d732..a0fc833 100644 --- a/sql/sql.cl +++ b/sql/sql.cl @@ -8,7 +8,7 @@ ;;;; Original code by Pierre R. Mai ;;;; Date Started: Feb 2002 ;;;; -;;;; $Id: sql.cl,v 1.6 2002/03/25 23:48:46 kevin Exp $ +;;;; $Id: sql.cl,v 1.7 2002/03/26 14:11:59 kevin Exp $ ;;;; ;;;; This file, part of CLSQL, is Copyright (c) 2002 by Kevin M. Rosenberg ;;;; and Copyright (c) 1999-2001 by Pierre R. Mai @@ -21,10 +21,13 @@ (declaim (optimize (debug 3) (speed 3) (safety 1) (compilation-speed 0))) (in-package :clsql-sys) -;;;; Modified to use CMUCL-COMPAT library and to fix format strings in -;;;; error messages +;;; Modified by KMR +;;; - to use CMUCL-COMPAT library +;;; - fix format strings in error messages +;;; - use field types -;;;; Simple implementation of SQL along the lines of Harlequin's Common SQL + +;;; Simple implementation of SQL along the lines of Harlequin's Common SQL ;;; Conditions (define-condition clsql-condition () diff --git a/sql/utils.cl b/sql/utils.cl new file mode 100644 index 0000000..d2ff844 --- /dev/null +++ b/sql/utils.cl @@ -0,0 +1,41 @@ +;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*- +;;;; ************************************************************************* +;;;; FILE IDENTIFICATION +;;;; +;;;; Name: utils.cl +;;;; Purpose: SQL utility functions +;;;; Programmer: Kevin M. Rosenberg +;;;; Date Started: Mar 2002 +;;;; +;;;; $Id: utils.cl,v 1.1 2002/03/26 14:11:59 kevin Exp $ +;;;; +;;;; This file, part of CLSQL, is Copyright (c) 2002 by Kevin M. Rosenberg +;;;; +;;;; CLSQL users are granted the rights to distribute and use this software +;;;; as governed by the terms of the Lisp Lesser GNU Public License +;;;; (http://opensource.franz.com/preamble.html), also known as the LLGPL. +;;;; ************************************************************************* + +(declaim (optimize (debug 3) (speed 3) (safety 1) (compilation-speed 0))) +(in-package :clsql-sys) + + +(defun float-to-sql-string (num) + "Convert exponent character for SQL" + (substitute #\e #\f (substitute #\e #\d (write-to-string num :readably t)))) + +(defun sql-escape-quotes (s) + "Escape single quotes for SQL" + (substitute-string-for-char s #\' "''")) + +(defun substitute-string-for-char (procstr match-char subst-str) +"Substitutes a string for a single matching character of a string" + (let ((pos (position match-char procstr))) + (if pos + (concatenate 'string + (subseq procstr 0 pos) subst-str + (substitute-string-for-char + (subseq procstr (1+ pos)) match-char subst-str)) + procstr))) + + diff --git a/test-clsql.cl b/test-clsql.cl index 0507e14..1d58cd5 100644 --- a/test-clsql.cl +++ b/test-clsql.cl @@ -7,7 +7,7 @@ ;;;; Programmer: Kevin M. Rosenberg ;;;; Date Started: Mar 2002 ;;;; -;;;; $Id: test-clsql.cl,v 1.8 2002/03/25 23:48:46 kevin Exp $ +;;;; $Id: test-clsql.cl,v 1.9 2002/03/26 14:12:12 kevin Exp $ ;;;; ;;;; This file, part of CLSQL, is Copyright (c) 2002 by Kevin M. Rosenberg ;;;; @@ -59,12 +59,15 @@ (clsql:execute-command "DROP TABLE test_clsql" :database db)) (clsql:execute-command - "CREATE TABLE test_clsql (i integer, sqrt float, sqrt_str CHAR(20))" :database db) - (dotimes (i 10) - (clsql:execute-command - (format nil "INSERT INTO test_clsql VALUES (~d,~d,'~a')" - i (sqrt i) (format nil "~d" (sqrt i))) - :database db))) + "CREATE TABLE test_clsql (n integer, n_pi float, n_pi_str CHAR(20))" + :database db) + (dotimes (i 11) + (let ((n (- i 5))) + (clsql:execute-command + (format nil "INSERT INTO test_clsql VALUES (~a,~a,'~a')" + n (clsql:float-to-sql-string (* pi n)) + (clsql:float-to-sql-string (* pi n))) + :database db)))) (defun drop-test-table (db) (clsql:execute-command "DROP TABLE test_clsql")) -- 2.34.1