interfaces/postgresql/postgresql-usql.cl

   1 ;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*-
   2 ;;;; *************************************************************************
   3 ;;;; FILE IDENTIFICATION
   4 ;;;;
   5 ;;;; Name:          postgresql-usql.sql
   6 ;;;; Purpose:       PostgreSQL interface for USQL routines
   7 ;;;; Programmers:   Kevin M. Rosenberg and onShore Development Inc
   8 ;;;; Date Started:  Mar 2002
   9 ;;;;
  10 ;;;; $Id: postgresql-usql.cl,v 1.1 2002/04/01 05:27:55 kevin Exp $
  11 ;;;;
  12 ;;;; This file, part of CLSQL, is Copyright (c) 2002 by Kevin M. Rosenberg
  13 ;;;; and by onShore Development Inc.
  14 ;;;;
  15 ;;;; CLSQL users are granted the rights to distribute and use this software
  16 ;;;; as governed by the terms of the Lisp Lesser GNU Public License
  17 ;;;; (http://opensource.franz.com/preamble.html), also known as the LLGPL.
  18 ;;;; *************************************************************************
  19
  20 (declaim (optimize (debug 3) (speed 3) (safety 1) (compilation-speed 0)))
  21 (in-package :clsql-postgresql)
  22
  23 (defmethod database-list-tables ((database postgresql-database)
  24                                  &key (system-tables nil))
  25   (let ((res (mapcar #'car (database-query
  26                             "SELECT tablename FROM pg_tables"
  27                             database nil))))
  28     (if (not system-tables)
  29         (remove-if #'(lambda (table)
  30                        (equal (subseq table 0 3)
  31                               "pg_")) res)
  32       res)))
  33
  34
  35
  36 (defmethod database-list-attributes (table (database postgresql-database))
  37   (let* ((relname (etypecase table
  38                     (clsql::sql-ident
  39                      (string-downcase
  40                       (symbol-name (slot-value table 'clsql::name))))
  41                     (string table)))
  42          (result
  43           (mapcar #'car
  44                   (database-query
  45                    (format nil
  46                            "SELECT attname FROM pg_class,pg_attribute WHERE pg_class.oid=attrelid AND relname='~A'" relname)
  47                    database nil))))
  48     (if result
  49         (reverse
  50          (remove-if #'(lambda (it) (member it '("cmin"
  51                                                 "cmax"
  52                                                 "xmax"
  53                                                 "xmin"
  54                                                 "oid"
  55                                                 "ctid"
  56                                                 ;; kmr -- added tableoid
  57                                                 "tableoid") :test #'equal))
  58                     result)))))
  59
  60 (defmethod database-attribute-type (attribute table
  61                                     (database postgresql-database))
  62   (let ((result
  63           (mapcar #'car
  64                   (database-query
  65                    (format nil
  66                            "SELECT pg_type.typname FROM pg_type,pg_class,pg_attribute WHERE pg_class.oid=pg_attribute.attrelid AND pg_class.relname='~A' AND pg_attribute.attname='~A' AND pg_attribute.atttypid=pg_type.oid"
  67                            table attribute)
  68                    database nil))))
  69     (if result
  70         (intern (string-upcase (car result)) :keyword) nil)))
  71
  72
  73 (defmethod database-create-sequence (sequence-name
  74                                      (database postgresql-database))
  75   (database-execute-command
  76    (concatenate 'string "CREATE SEQUENCE " (sql-escape sequence-name)) database))
  77
  78 (defmethod database-drop-sequence (sequence-name
  79                                    (database postgresql-database))
  80   (database-execute-command
  81    (concatenate 'string "DROP SEQUENCE " (sql-escape sequence-name)) database))
  82
  83 (defmethod database-sequence-next (sequence-name
  84                                    (database postgresql-database))
  85   (parse-integer
  86    (caar
  87     (database-query
  88      (concatenate 'string "SELECT NEXTVAL ('" (sql-escape sequence-name) "')")
  89      database nil))))
  90
  91 (defmethod database-output-sql ((expr clsql-sys::sql-typecast-exp)
  92                                 (database postgresql-database))
  93   (with-slots (clsql-sys::modifier clsql-sys::components)
  94     expr
  95     (if clsql-sys::modifier
  96         (progn
  97           (clsql-sys::output-sql clsql-sys::components database)
  98           (write-char #\: clsql-sys::*sql-stream*)
  99           (write-char #\: clsql-sys::*sql-stream*)
 100           (write-string (symbol-name clsql-sys::modifier)
 101                         clsql-sys::*sql-stream*)))))
 102
 103 (defmethod database-output-sql-as-type ((type (eql 'integer)) val
 104                                         (database postgresql-database))
 105   ;; typecast it so it uses the indexes
 106   (when val
 107     (make-instance 'clsql-sys::sql-typecast-exp
 108                    :modifier 'int8
 109                    :components val)))