db-mysql/mysql-api.lisp

   1 ;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*-
   2 ;;;; *************************************************************************
   3 ;;;; FILE IDENTIFICATION
   4 ;;;;
   5 ;;;; Name:          mysql-api.lisp
   6 ;;;; Purpose:       Low-level MySQL interface using UFFI
   7 ;;;; Programmers:   Kevin M. Rosenberg based on
   8 ;;;;                Original code by Pierre R. Mai
   9 ;;;; Date Started:  Feb 2002
  10 ;;;;
  11 ;;;; This file, part of CLSQL, is Copyright (c) 2002-2009 by Kevin M. Rosenberg
  12 ;;;; and Copyright (c) 1999-2001 by Pierre R. Mai
  13 ;;;;
  14 ;;;; CLSQL users are granted the rights to distribute and use this software
  15 ;;;; as governed by the terms of the Lisp Lesser GNU Public License
  16 ;;;; (http://opensource.franz.com/preamble.html), also known as the LLGPL.
  17 ;;;; *************************************************************************
  18
  19 (in-package #:mysql)
  20
  21 ;;;; Modifications from original code
  22 ;;;;  - Updated C-structures to conform to structures in MySQL 3.23.46
  23 ;;;;  - Changed from CMUCL interface to UFFI
  24 ;;;;  - Added and call a C-helper file to support 64-bit integers
  25 ;;;;    that are used in a few routines.
  26 ;;;;  - Removed all references to interiors of C-structions, this will
  27 ;;;;    increase robustness when MySQL's internal structures change.
  28
  29 ;;;; Type definitions
  30
  31 ;;; Basic Types
  32
  33 (uffi:def-foreign-type mysql-socket :int)
  34 (uffi:def-foreign-type mysql-bool :byte)
  35 (uffi:def-foreign-type mysql-byte :unsigned-char)
  36
  37 (uffi:def-enum mysql-net-type
  38     (:tcp-ip
  39      :socket
  40      :named-pipe))
  41
  42 (uffi:def-array-pointer mysql-row (* :unsigned-char))
  43
  44 ;;; MYSQL-FIELD
  45 (uffi:def-enum mysql-field-types
  46     (:decimal
  47      :tiny
  48      :short
  49      :long
  50      :float
  51      :double
  52      :null
  53      :timestamp
  54      :longlong
  55      :int24
  56      :date
  57      :time
  58      :datetime
  59      :year
  60      :newdate
  61      (:enum 247)
  62      (:set 248)
  63      (:tiny-blob 249)
  64      (:medium-blob 250)
  65      (:long-blob 251)
  66      (:blob 252)
  67      (:var-string 253)
  68      (:string 254)
  69      (:geometry 255)))
  70
  71 (uffi:def-enum mysql-option
  72     (:connect-timeout
  73      :compress
  74      :named-pipe
  75      :init-command
  76      :read-default-file
  77      :read-default-group))
  78
  79 (uffi:def-enum mysql-status
  80     (:ready
  81      :get-result
  82      :use-result))
  83
  84 #+(or mysql-client-v4.1 mysql-client-v5)
  85 (uffi:def-enum mysql-field-types
  86     (:ready
  87      :get-result
  88      :use-result))
  89
  90 ;;; Opaque pointers to mysql C-defined structures
  91 (uffi:def-foreign-type mysql-mysql (* :void))
  92 (uffi:def-foreign-type mysql-mysql-res (* :void))
  93 (uffi:def-foreign-type mysql-field (* :void))
  94 (uffi:def-foreign-type mysql-bind (* :void))
  95
  96 ;;;; The Foreign C routines
  97 (declaim (inline mysql-init))
  98 (uffi:def-function "mysql_init"
  99   ((mysql mysql-mysql))
 100   :module "mysql"
 101   :returning mysql-mysql)
 102
 103 ;; Need to comment this out for LW 4.2.6
 104 ;; ? bug in LW version
 105 #-lispworks (declaim (inline mysql-real-connect))
 106 (uffi:def-function "mysql_real_connect"
 107     ((mysql mysql-mysql)
 108      (host :cstring)
 109      (user :cstring)
 110      (passwd :cstring)
 111      (db :cstring)
 112      (port :unsigned-int)
 113      (unix-socket :cstring)
 114      (clientflag :unsigned-long))
 115   :module "mysql"
 116   :returning mysql-mysql)
 117
 118 (declaim (inline mysql-close))
 119 (uffi:def-function "mysql_close"
 120     ((sock mysql-mysql))
 121   :module "mysql"
 122   :returning :void)
 123
 124 (declaim (inline mysql-select-db))
 125 (uffi:def-function "mysql_select_db"
 126   ((mysql mysql-mysql)
 127    (db :cstring))
 128   :module "mysql"
 129   :returning :int)
 130
 131 (declaim (inline mysql-query))
 132 (uffi:def-function "mysql_query"
 133     ((mysql mysql-mysql)
 134      (query :cstring))
 135   :module "mysql"
 136   :returning :int)
 137
 138  ;;; I doubt that this function is really useful for direct Lisp usage,
 139 ;;; but it is here for completeness...
 140
 141 (declaim (inline mysql-real-query))
 142 (uffi:def-function "mysql_real_query"
 143     ((mysql mysql-mysql)
 144      (query :cstring)
 145      (length :unsigned-int))
 146   :module "mysql"
 147   :returning :int)
 148
 149 (declaim (inline mysql-shutdown))
 150 (uffi:def-function "mysql_shutdown"
 151   ((mysql mysql-mysql))
 152   :module "mysql"
 153   :returning :int)
 154
 155 (declaim (inline mysql-dump-debug-info))
 156 (uffi:def-function "mysql_dump_debug_info"
 157   ((mysql mysql-mysql))
 158   :module "mysql"
 159   :returning :int)
 160
 161 (declaim (inline mysql-refresh))
 162 (uffi:def-function "mysql_refresh"
 163   ((mysql mysql-mysql)
 164    (refresh-options :unsigned-int))
 165   :module "mysql"
 166   :returning :int)
 167
 168 (declaim (inline mysql-kill))
 169 (uffi:def-function "mysql_kill"
 170     ((mysql mysql-mysql)
 171      (pid :unsigned-long))
 172   :module "mysql"
 173   :returning :int)
 174
 175 (declaim (inline mysql-ping))
 176 (uffi:def-function "mysql_ping"
 177     ((mysql mysql-mysql))
 178   :module "mysql"
 179   :returning :int)
 180
 181 (declaim (inline mysql-stat))
 182 (uffi:def-function "mysql_stat"
 183   ((mysql mysql-mysql))
 184   :module "mysql"
 185   :returning :cstring)
 186
 187 (declaim (inline mysql-get-server-info))
 188 (uffi:def-function "mysql_get_server_info"
 189     ((mysql mysql-mysql))
 190   :module "mysql"
 191   :returning :cstring)
 192
 193 (declaim (inline mysql-get-host-info))
 194 (uffi:def-function "mysql_get_host_info"
 195     ((mysql mysql-mysql))
 196   :module "mysql"
 197   :returning :cstring)
 198
 199 (declaim (inline mysql-get-proto-info))
 200 (uffi:def-function "mysql_get_proto_info"
 201   ((mysql mysql-mysql))
 202   :module "mysql"
 203   :returning :unsigned-int)
 204
 205 (declaim (inline mysql-list-dbs))
 206 (uffi:def-function "mysql_list_dbs"
 207   ((mysql mysql-mysql)
 208    (wild :cstring))
 209   :module "mysql"
 210   :returning mysql-mysql-res)
 211
 212 (declaim (inline mysql-list-tables))
 213 (uffi:def-function "mysql_list_tables"
 214   ((mysql mysql-mysql)
 215    (wild :cstring))
 216   :module "mysql"
 217   :returning mysql-mysql-res)
 218
 219 (declaim (inline mysql-list-fields))
 220 (uffi:def-function "mysql_list_fields"
 221   ((mysql mysql-mysql)
 222    (table :cstring)
 223    (wild :cstring))
 224   :module "mysql"
 225   :returning mysql-mysql-res)
 226
 227 (declaim (inline mysql-list-processes))
 228 (uffi:def-function "mysql_list_processes"
 229   ((mysql mysql-mysql))
 230   :module "mysql"
 231   :returning mysql-mysql-res)
 232
 233 (declaim (inline mysql-store-result))
 234 (uffi:def-function "mysql_store_result"
 235   ((mysql mysql-mysql))
 236   :module "mysql"
 237   :returning mysql-mysql-res)
 238
 239 (declaim (inline mysql-use-result))
 240 (uffi:def-function "mysql_use_result"
 241   ((mysql mysql-mysql))
 242   :module "mysql"
 243   :returning mysql-mysql-res)
 244
 245 (declaim (inline mysql-options))
 246 (uffi:def-function "mysql_options"
 247   ((mysql mysql-mysql)
 248    (option mysql-option)
 249    (arg :cstring))
 250   :module "mysql"
 251   :returning :int)
 252
 253 (declaim (inline mysql-free-result))
 254 (uffi:def-function "mysql_free_result"
 255     ((res mysql-mysql-res))
 256   :module "mysql"
 257   :returning :void)
 258
 259 (declaim (inline mysql-fetch-row))
 260 (uffi:def-function "mysql_fetch_row"
 261     ((res mysql-mysql-res))
 262   :module "mysql"
 263   :returning (* (* :unsigned-char)))
 264
 265 (declaim (inline mysql-fetch-lengths))
 266 (uffi:def-function "mysql_fetch_lengths"
 267   ((res mysql-mysql-res))
 268   :module "mysql"
 269   :returning (* :unsigned-long))
 270
 271 (declaim (inline mysql-fetch-field))
 272 (uffi:def-function "mysql_fetch_field"
 273   ((res mysql-mysql-res))
 274   :module "mysql"
 275   :returning mysql-field)
 276
 277 (declaim (inline mysql-field-seek))
 278 (uffi:def-function "mysql_field_seek"
 279   ((res mysql-mysql-res)
 280    (offset :unsigned-int))
 281   :module "mysql"
 282   :returning :unsigned-int)
 283
 284 (declaim (inline mysql-fetch-fields))
 285 (uffi:def-function "mysql_fetch_fields"
 286   ((res mysql-mysql-res))
 287   :module "mysql"
 288   :returning mysql-field)
 289
 290 (declaim (inline mysql-fetch-field-direct))
 291 (uffi:def-function "mysql_fetch_field_direct"
 292   ((res mysql-mysql-res)
 293    (field-num :unsigned-int))
 294   :module "mysql"
 295   :returning mysql-field)
 296
 297 (declaim (inline mysql-escape-string))
 298 (uffi:def-function "mysql_escape_string"
 299     ((to (* :unsigned-char))
 300      (from (* :unsigned-char))
 301      (length :unsigned-int))
 302   :module "mysql"
 303   :returning :unsigned-int)
 304
 305 (declaim (inline mysql-debug))
 306 (uffi:def-function "mysql_debug"
 307     ((debug :cstring))
 308   :module "mysql"
 309   :returning :void)
 310
 311 (declaim (inline clsql-mysql-num-rows))
 312 (uffi:def-function "clsql_mysql_num_rows"
 313     ((res mysql-mysql-res)
 314      (p-high32 (* :unsigned-int)))
 315   :module "clsql-mysql"
 316   :returning :unsigned-int)
 317
 318 #+(or mysql-client-v4.1 mysql-client-v5)
 319 (uffi:def-foreign-type mysql-stmt-ptr :pointer-void)
 320
 321 #+(or mysql-client-v4.1 mysql-client-v5)
 322 (uffi:def-function "mysql_stmt_init"
 323     ((res mysql-mysql-res))
 324   :module "clsql-mysql"
 325   :returning mysql-stmt-ptr)
 326
 327 #+(or mysql-client-v4.1 mysql-client-v5)
 328 (uffi:def-function "mysql_stmt_prepare"
 329     ((stmt mysql-stmt-ptr)
 330      (query :cstring)
 331      (length :unsigned-long))
 332   :module "clsql-mysql"
 333   :returning :int)
 334
 335 #+(or mysql-client-v4.1 mysql-client-v5)
 336 (uffi:def-function "mysql_stmt_param_count"
 337     ((stmt mysql-stmt-ptr))
 338   :module "clsql-mysql"
 339   :returning :unsigned-int)
 340
 341 #+(or mysql-client-v4.1 mysql-client-v5)
 342 (uffi:def-function "mysql_stmt_bind_param"
 343     ((stmt mysql-stmt-ptr)
 344      (bind mysql-bind))
 345   :module "clsql-mysql"
 346   :returning :short)
 347
 348 #+(or mysql-client-v4.1 mysql-client-v5)
 349 (uffi:def-function "mysql_stmt_bind_result"
 350     ((stmt mysql-stmt-ptr)
 351      (bind mysql-bind))
 352   :module "clsql-mysql"
 353   :returning :short)
 354
 355 #+(or mysql-client-v4.1 mysql-client-v5)
 356 (uffi:def-function "mysql_stmt_result_metadata"
 357     ((stmt mysql-stmt-ptr))
 358   :module "clsql-mysql"
 359   :returning mysql-mysql-res)
 360
 361
 362 #+(or mysql-client-v4.1 mysql-client-v5)
 363 (uffi:def-function "mysql_stmt_execute"
 364     ((stmt mysql-stmt-ptr))
 365   :module "clsql-mysql"
 366   :returning :int)
 367
 368 #+(or mysql-client-v4.1 mysql-client-v5)
 369 (uffi:def-function "mysql_stmt_store_result"
 370     ((stmt mysql-stmt-ptr))
 371   :module "clsql-mysql"
 372   :returning :int)
 373
 374 #+(or mysql-client-v4.1 mysql-client-v5)
 375 (uffi:def-function "mysql_stmt_fetch"
 376     ((stmt mysql-stmt-ptr))
 377   :module "clsql-mysql"
 378   :returning :int)
 379
 380 #+(or mysql-client-v4.1 mysql-client-v5)
 381 (uffi:def-function "mysql_stmt_free_result"
 382     ((stmt mysql-stmt-ptr))
 383   :module "clsql-mysql"
 384   :returning :short)
 385
 386 #+(or mysql-client-v4.1 mysql-client-v5)
 387 (uffi:def-function "mysql_stmt_close"
 388     ((stmt mysql-stmt-ptr))
 389   :module "clsql-mysql"
 390   :returning :short)
 391
 392 #+(or mysql-client-v4.1 mysql-client-v5)
 393 (uffi:def-function "mysql_stmt_errno"
 394     ((stmt mysql-stmt-ptr))
 395   :module "clsql-mysql"
 396   :returning :unsigned-int)
 397
 398 #+(or mysql-client-v4.1 mysql-client-v5)
 399 (uffi:def-function "mysql_stmt_error"
 400     ((stmt mysql-stmt-ptr))
 401   :module "clsql-mysql"
 402   :returning :cstring)
 403
 404
 405 ;;;; Equivalents of C Macro definitions for accessing various fields
 406 ;;;; in the internal MySQL Datastructures
 407
 408
 409 (declaim (inline mysql-num-rows))
 410 (defun mysql-num-rows (res)
 411   (uffi:with-foreign-object (p-high32 :unsigned-int)
 412     (let ((low32 (clsql-mysql-num-rows res p-high32))
 413           (high32 (uffi:deref-pointer p-high32 :unsigned-int)))
 414       (if (zerop high32)
 415           low32
 416         (make-64-bit-integer high32 low32)))))
 417
 418 (uffi:def-function "clsql_mysql_affected_rows"
 419     ((mysql mysql-mysql)
 420      (p-high32 (* :unsigned-int)))
 421   :returning :unsigned-int
 422   :module "clsql-mysql")
 423
 424 (defun mysql-affected-rows (mysql)
 425   (uffi:with-foreign-object (p-high32 :unsigned-int)
 426     (let ((low32 (clsql-mysql-affected-rows mysql p-high32))
 427           (high32 (uffi:deref-pointer p-high32 :unsigned-int)))
 428       (if (zerop high32)
 429           low32
 430         (make-64-bit-integer high32 low32)))))
 431
 432 (uffi:def-function "clsql_mysql_insert_id"
 433     ((res mysql-mysql)
 434      (p-high32 (* :unsigned-int)))
 435   :returning :unsigned-int
 436   :module "clsql-mysql")
 437
 438 (defun mysql-insert-id (mysql)
 439   (uffi:with-foreign-object (p-high32 :unsigned-int)
 440   (let ((low32 (clsql-mysql-insert-id mysql p-high32))
 441         (high32 (uffi:deref-pointer p-high32 :unsigned-int)))
 442     (if (zerop high32)
 443         low32
 444       (make-64-bit-integer high32 low32)))))
 445
 446
 447 (declaim (inline mysql-num-fields))
 448 (uffi:def-function "mysql_num_fields"
 449   ((res mysql-mysql-res))
 450   :returning :unsigned-int
 451   :module "mysql")
 452
 453 (declaim (inline clsql-mysql-eof))
 454 (uffi:def-function ("mysql_eof" clsql-mysql-eof)
 455   ((res mysql-mysql-res))
 456   :returning :char
 457   :module "mysql")
 458
 459 (declaim (inline mysql-eof))
 460 (defun mysql-eof (res)
 461   (if (zerop (clsql-mysql-eof res))
 462       nil
 463     t))
 464
 465 (declaim (inline mysql-error))
 466 (uffi:def-function ("mysql_error" mysql-error)
 467   ((mysql mysql-mysql))
 468   :returning :cstring
 469   :module "mysql")
 470
 471 (declaim (inline mysql-error-string))
 472 (defun mysql-error-string (mysql)
 473   (uffi:convert-from-cstring (mysql-error mysql)))
 474
 475 (declaim (inline mysql-errno))
 476 (uffi:def-function "mysql_errno"
 477   ((mysql mysql-mysql))
 478   :returning :unsigned-int
 479   :module "mysql")
 480
 481 (declaim (inline mysql-info))
 482 (uffi:def-function ("mysql_info" mysql-info)
 483   ((mysql mysql-mysql))
 484   :returning :cstring
 485   :module "mysql")
 486
 487 (declaim (inline mysql-info-string))
 488 (defun mysql-info-string (mysql)
 489   (uffi:convert-from-cstring (mysql-info mysql)))
 490
 491 (declaim (inline clsql-mysql-data-seek))
 492 (uffi:def-function "clsql_mysql_data_seek"
 493   ((res mysql-mysql-res)
 494    (offset-high32 :unsigned-int)
 495    (offset-low32 :unsigned-int))
 496   :module "clsql-mysql"
 497   :returning :void)
 498
 499 (declaim (inline clsql-mysql-field-name))
 500 (uffi:def-function "clsql_mysql_field_name"
 501   ((res mysql-field))
 502   :module "clsql-mysql"
 503   :returning :cstring)
 504
 505 (declaim (inline clsql-mysql-field-flags))
 506 (uffi:def-function "clsql_mysql_field_flags"
 507   ((res mysql-field))
 508   :module "clsql-mysql"
 509   :returning :unsigned-int)
 510
 511 (declaim (inline clsql-mysql-field-type))
 512 (uffi:def-function "clsql_mysql_field_type"
 513   ((res mysql-field))
 514   :module "clsql-mysql"
 515   :returning :unsigned-int)
 516
 517 (defun mysql-data-seek (res offset)
 518   (multiple-value-bind (high32 low32) (split-64-bit-integer offset)
 519     (clsql-mysql-data-seek res high32 low32)))