#lang scribble/doc @(require scribble/manual scribble/struct (for-label scheme) (for-label (except-in "../private/structs.ss" field))) @(define-syntax-rule (type-table (hd-sql hd-scm) (sql scm) ...) (let ((spacer (hspace 10)) (to-flow (λ (e) (make-flow (list (make-paragraph (list e))))))) (make-table 'centered (list (list (to-flow (bold hd-sql)) (to-flow spacer) (to-flow (bold hd-scm))) (list (to-flow (scheme sql)) (to-flow spacer) (to-flow (scheme scm))) ...)))) @; ------------------------------------------------------------------------------ @title[#:style '(toc)]{MySQL API} @defmodule[(planet jaz/mysql:1)]{Provides all of the procedures for connecting to, querying, and updating MySQL databases.} @; --------------------------------------------------- @section{Database Connections} @defproc[(connection? [x any]) boolean?]{Returns @scheme[#t] if @scheme[x] represents a MySQL database connection, @scheme[#f] otherwise.} @defparam[current-connection connection (or/c connection? #f)]{A parameter that determines the default database connection to use when one is not explicitly provided to the procedures that communicate with the database server.} @defproc[(connect [host string?] [port exact-nonnegative-integer?] [user string?] [password string?] [#:schema schema (or/c string? #f) #f] [#:use-ssl? use-ssl? boolean? #f] [#:set-current? set-current? boolean? #t]) connection?]{Connects to a MySQL server instance, using the supplied parameters. If @scheme[schema] is not supplied, the connection will initially not be assoicated with any schema. If @scheme[use-ssl?] is @scheme[#t], the Client Library will attempt to use an SSL-tunelled TCP connection. This depends upon both the MySQL server having SSL support and the client having the proper OpenSSL libraries. If these conditions are not met, the procedure will raise @scheme[exn:fail]. If @scheme[set-current?] is @scheme[#t], the @scheme[connection] returned by this procedure will become the new value of the @scheme[current-connection] parameter.} @defproc[(close-connection! [con connection?]) void?]{Closes the provided connection.} @defproc[(connection-open? [con connection?]) boolean?]{Returns @scheme[#t] if @scheme[con] represents a connection that has not been explicitly closed by the @scheme[close-connection!] procedure.} @; ----------------------------------------------------- @section{Basic Queries and Query Results} @subsection{Basic Queries} @deftogether[[@defproc[(query* [#:connection con connection? (current-connection)] [sql string?]) (listof query-result?)]{} @defproc[(query [#:connection con connection? (current-connection)] [sql string?]) query-result?]{} @defproc[(query0 [#:connection con connection? (current-connection)] [sql string?]) query-result?]{}]]{Queries the database and returns the query results. Since a single SQL string may contain multiple SQL statements (delimited by semicolons), @scheme[query*] is the most general of the above procedures, as it will return one @scheme[query-result] for each SQL statement in the @scheme[sql] argument. @scheme[query] returns only the @scheme[query-result] corresponding to the last SQL statement in @scheme[sql], whereas @scheme[query0] returns only the @scheme[query-result] corresponding to the first SQL statement in @scheme[sql].} @subsection{Query Results} A basic SQL query will return a value that satisfies @scheme[query-result?] (or a list of such values, if the @scheme[query*] procedure is used). Any such value will additionally satisfy either @scheme[result-set?] or @scheme[side-effect?]. @defproc[(query-result? [x any]) boolean?]{Returns @scheme[#t] if @scheme[x] represents the results of an SQL statement, @scheme[#f] otherwise.} @subsubsection{Result Sets} @defproc[(result-set? [x any]) boolean?]{Returns @scheme[#t] if @scheme[x] represents the results of an SQL statement that returns row data, @scheme[#f] otherwise.} @defproc[(result-set-rows [rs result-set?]) (listof (vectorof any))]{Returns a list of rows, each of which is a vector.} @defproc[(result-set-field-names [rs result-set?]) (vectorof string?)]{Returns a vector containing the names of the result set's fields, in order.} @defproc[(in-result-set [rs result-set?]) (sequence?)]{Returns a sequence of rows, each of which is a vector.} @defproc[(in-result-set/hash [rs result-set?]) (sequence?)]{Returns a sequence of rows, each of which is a hash table. The hash table's keys are symbols representing the names of the result set's fields, and the values are the row data.} @subsubsection{Side Effects} @defproc[(side-effect? [x any]) boolean?]{Returns @scheme[#t] if @scheme[x] represents the results of an SQL statement that is evaluated for its side effects (e.g., an @scheme[UPDATE] or @scheme[INSERT] statement), @scheme[#f] otherwise.} @defproc[(side-effect-affected-rows [eff side-effect?]) exact-nonnegative-integer?]{Returns the number of rows that were modified by the query that produced @scheme[eff].} @defproc[(side-effect-insert-id [eff side-effect?]) (or/c exact-positive-integer? #f)]{Returns the primary key that was automatically generated by the INSERT query that produced @scheme[eff] or @scheme[#f] if no such key was generated.} @defproc[(side-effect-warning-count [eff side-effect?]) exact-nonnegative-integer?]{Returns the number of warnings generated by the query that produced @scheme[eff].} @defproc[(side-effect-message [eff side-effect?]) (or/c string? #f)]{Returns a status message generated by the query that produced @scheme[eff] or @scheme[#f] if no message was generated.} @; ----------------------------------------------------- @section{Prepared Statements} @defproc[(prepared-statement? [x any]) boolean?]{Returns @scheme[#t] if @scheme[x] represents a MySQL prepared statement, @scheme[#f] otherwise.} @defproc[(prepare [#:connection con connection? (current-connection)] [sql string?]) prepared-statement?]{Prepares a single, parameterized SQL statement for execution. The SQL statement may use question marks (?) in place of SQL value expressions. Each question mark denotes a parameter that may be supplied at execution time. For example, the following: @hspace[5] @scheme[(define stmt (prepare "UPDATE foo_tbl SET name = ? WHERE id = ?"))] ... defines @scheme[stmt] as a prepared statement with two parameters.} @defproc[(prepared-statement-parameter-count [stmt prepared-statement?]) exact-nonnegative-integer?]{Returns the number of parameters in the given @scheme[stmt].} @defproc[(execute [stmt prepared-statement?] [arguments (listof any)]) query-result?]{Executes a @scheme[prepared-statement], substituting the provided @scheme[arguments] for the parameters of @scheme[stmt] in positional order. The length of the arguments list must be equal to the number of parameters in @scheme[stmt], otherwise @scheme[exn:fail:contract] is raised. Note that not all scheme values are valid as parameter values (e.g., output ports). If such a value is encountered, @scheme[exn:fail:contract] is raised.} @; ------------------------------------------------- @section{Query Catamorphisms} @defproc[(query/foldl [#:connection con connection? (current-connection)] [proc procedure?] [init any/c] [sql string?]) any/c]{Executes @scheme[sql], left-folding @scheme[proc] over the resulting rows, using @scheme[init] as the initial seed. This call is semantically identical to: @schemeblock[(let ((result (query #:connection con sql))) (if (result-set? result) (foldl (λ (x xs) (apply proc (append x (list xs)))) init (map vector->list (result-set-rows result))) result))] ... but is far more efficient in both time and space. Note that the result of the query may be a @scheme[side-effect], in which case no iteration occurs and the result is simply returned.} @defproc[(query/map [#:connection con connection? (current-connection)] [proc procedure?] [sql? string?]) (or/c side-effect? list?)]{Executes @scheme[sql], mapping @scheme[proc] over the resulting rows. This call is semantically identical to: @schemeblock[(let ((result (query #:connection con sql))) (if (result-set? result) (map (λ (xs) (apply proc xs)) (map vector->list (result-set-rows result))) result))] ... but is far more efficient in time and space.} @defproc[(query/map/filter [#:connection con connection? (current-connection)] [proc procedure?] [pred? procedure?] [sql string?]) (or/c side-effect? list?)]{Executes @scheme[sql], mapping @scheme[proc] over the rows in the result set that satisfy @scheme[pred?]. This call is semantically identical to: @schemeblock[(let ((result (query #:connection con sql))) (if (result-set? result) (map (λ (xs) (apply proc xs)) (filter (λ (xs) (apply pred? xs)) (map vector->list (result-set-rows result)))) result))] ... but is far more efficient in time and space.} @deftogether[[@defproc[(execute/foldl [proc procedure?] [init any/c] [stmt prepared-statement?] [args list?]) any/c]{} @defproc[(execute/map [proc procedure?] [stmt prepared-statement?] [args list?]) (or/c side-effect list?)]{} @defproc[(execute/map/filter [proc procedure?] [pred? procedure?] [stmt prepared-statement?] [args list?]) (or/c side-effect? list?)]{}]]{These procedures behave exactly like their @scheme[query/...] counterparts, except they take prepared statements and argument lists, rather than SQL strings.} @section{MySQL → Scheme Type Mapping} MySQL types are mapped to scheme types as follows: @type-table[["MySQL Type" "Scheme Type"] [NULL sql-null?] [BIT exact-integer?] [*INT exact-integer?] [FLOAT real?] [DOUBLE real?] [DECIMAL/NUMERIC real?] [NUMERIC real?] [DATETIME srfi-19:date?] [TIMESTAMP srfi-19:date?] [DATE srfi-19:date?] [TIME srfi-19:time?] [YEAR exact-integer?] [CHAR string?] [VARCHAR string?] [*TEXT string?] [BINARY bytes?] [VARBINARY bytes?] [*BLOB bytes?]] @subsection{SQL NULL} The SQL NULL value is represented by the unique value @scheme[sql-null]. @defproc[(sql-null? [x any]) boolean?]{Returns @scheme[#t] if @scheme[x] is the unique value @scheme[sql-null], @scheme[#f] otherwise.} @subsection{Precision of Real Numbers in Prepared Statements} If you provide a real number as a parameter value to a prepared statement, the MySQL Client Library will send it to the server as a decimal string. To provide sufficient precision, the library will consult the @scheme[decimals] field of the parameter packet it received from the server (see @link["http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol#Parameter_Packet_.28Tentative_Description.29"]{MySQL Internals: Client/Server Protocol}). If @scheme[decimals] is positive, it will encode the value as: @hspace[5] @scheme[(real->decimal-string real-value (add1 decimals))] Otherwise, it will default to: @hspace[5] @scheme[(number->string (exact->inexact real-value))] @subsection{Dates} The SRFI 19 @scheme[date] struct type contains both date and time of day information, as well as timezone data. When an SQL @scheme[DATE] is mapped to an SRFI 19 @scheme[date], the time of day data is zeroed out (and therefore corresponds to the very start of the day). When an SRFI 19 @scheme[date] is mapped back to an SQL @scheme[DATE], the time of day data is simply ignored. When the MySQL Client Library creates an SRFI 19 @scheme[date] value, its timezone offset is set to the offset of the local machine's current locale. No automatic timezone adjustment is performed between client and server. @subsection{Times} SQL @scheme[TIME] values are mapped to SRFI 19 @scheme[time] structs, using the @scheme['time-duration] type. When mapped back to an SQL @scheme[TIME], the SRFI-19 @scheme[time] is rounded to the nearest second. @subsection{Character Encoding} The MySQL Client Library communicates with the server using the UTF-8 encoding. (Note that this is completely independent of the encoding you have chosen for your database tables and/or columns. The MySQL server will re-encode your data into the appropriate character encoding for storage.) Although MySQL allows users to change the character encoding of the client/server communication channel using the @scheme[SET NAMES] statement, you should not do this, as the client library has no way of detecting the change (short of parsing all SQL strings, looking for @scheme[SET NAMES] statements, which would be unreasonably expensive). Consequently, changing the encoding of the channel will likely result in data corruption. @; --------------------------------------------------- @section{An Important Note on BLOBs} Binary data can be sent to the MySQL server using either a normal (string-based) query or via a prepared statement. In the former case, the @scheme[format-sql] procedure, exported by @scheme[format.ss], is helpful to ensure proper encoding and escaping. In particular, because the character encoding of the connection is ignored by the server for the purpose of decoding data destined for binary storage, MySQL query strings, in their full generality, aren't really strings (in the Scheme sense), at all. They're binary data. In order to treat them as strings (for ease of use), the @scheme[format-sql] procedure will hex encode byte strings, using the MySQL syntax. Note that hex encoding data doubles its original size, so if you plan to send large amounts of data to the server, it is best to use prepared statements. With prepared statements, you can use virtually any kind of Scheme data you want as your parameter argument. However, the only sensible choices for binary data are byte strings and input ports. Input ports may be preferable for sending large amounts of data that does not all need to be kept in RAM at once. The client library will only read about 1MB of data at a time from an input port. The library will treat input port arguments to prepared statements as "long data" (see @link["http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol#Long_Data_Packet_.28Tentative_Description.29"]{MySQL Internals: Client/Server Protocol}, but don't take it too seriously, since the description of the packet is incorrect), which is sent to the server separately from the main execution packet and may be broken up into multiple packets, if necessary.