mysql.lsp
Module: Mysql
Author: Jeff OberVersion: 1.05 beta
Location: http://static.artfulcode.net/newlisp/mysql.lsp
Package definition: http://static.artfulcode.net/newlisp/mysql.qwerty
A new MySQL module to replace the distribution standard module (requires newlisp 10).
The Mysql module has been written from scratch utilizing some of the more recent features of newLisp, such as FOOP and reference returns. One of its major design goals was to simplify use as well as broaden the features of the standard MySQL module, while at the same time allowing the creation of new, anonymous instances at run-time.The Mysql module differs from the distribution standard module in several important ways. Most obviously, it uses FOOP wrappers for MySQL types. It also requires clients to free results instances; in the standard module, only the base MYSQL instance itself must be freed (using MySQL:close-db).
The significance of this is that it is much simpler to create multiple connections (without having to duplicate the entire context at compile time). Result sets are completely independent of each other, and several may be maintained in any state at once. This also means that a spawned process may be given its own Mysql instance to use without having to worry about other processes' instances interfering. Using the standard module, the entire context would need to be cloned at compile time and given a static symbol reference (e.g., (new 'MySQL 'db)) in order to run multiple instances or connections to a server.
Moreover, because this module uses unpack and MySQL C API accessor functions, there is no need for the client to calculate member offsets in MySQL compound types. So long as newLisp was compiled for the same target as the libmysqlclient library (both are 32 bit or both are 64 bit), everything should work out of the box. Additionally, MySQL errors are now checked in the connect and query functions and re-thrown as interpreter errors. Instead of checking for nil returns and a using MySQL:error to get the error message, standard error handling with the catch function may be used.
This module has been tested with MySQL version 5 and 5.1 and newLisp version 10.0.1. It requires newLisp 10.0 or later.
Changelog
1.05 • Mysql:query now checks if client mistakenly sent single, non-list, argument for format-args1.04 • fixed error in documentation example • changed Mysql:query to allow lists as format parameters • backward-incompatible change to Mysql:query parameter list • added Mysql:coerce-type as an independent function
1.03 • fixed truncation bug when inserting binary data in Mysql:query
1.02 • field types are now correctly distinguished when MySQL is compiled with 64-bit pointers • refactored MysqlResult:get-row
1.01 • fixed invalid function in Mysql:tables, Mysql:fields, and Mysql:databases
1.0 • initial release
Known bugs
• None (at the moment); please let me know if you find any!example:
• Imperative usage
(setf db (Mysql)) ; initialize Mysql instance
(:connect db "localhost" "user" "secret" "my_database") ; connect to a server
(setf result (:query db "SELECT * FROM some_table")) ; evaluate a query
(setf rows (:fetch-all result)) ; generate a result
(:close-db db) ; free the database
• Functional usage with the 'mysql context
(mysql:on-connect '("localhost" "user" "secret" "my_database")
(lambda (db err)
(if err (throw-error err))
(mysql:row-iter db "SELECT * FROM some_table" nil
(lambda (row)
(println row)))))Mysql
syntax: (Mysql)Returns a new Mysql instance that can safely be used in tandem with other Mysql instances.
:connect
syntax: (:connect Mysql-instance str-host str-user str-pass str-db int-port str-socket)parameter: Mysql-instance - an instance of the Mysql class
parameter: str-host - the hostname to connect to
parameter: str-user - a MySQL username
parameter: str-pass - str-user's password
parameter: str-db - the database to initially connect to
parameter: int-port - (optional) port number of the MySQL server
parameter: int-str - (optional) socket file to connect through
Connects an initialized Mysql instance to a database. Returns true if successful logging in, nil if not.
example:(setf db (Mysql)) (:connect db "localhost" "user" "secret" "my-database") => true
:close
syntax: (:close Mysql-instance)parameter: Mysql-instance - an instance of the Mysql class
Closes the connection and frees any memory used. This does not free the memory used by results sets from this connection.
:error
syntax: (:error Mysql-instance)parameter: Mysql-instance - an instance of the Mysql class
Returns the last error message as a string or nil if there is none.
:coerce-type
syntax: (:coerce-type Mysql-instance object)parameter: Mysql-instance - an instance of the Mysql class
parameter: object - a newLisp object
Coerces object into something safe to use in a SQL statement. Lists are converted into MySQL lists (e.g. '("foo" "bar" "baz") to (foo, bar, baz)) and string values are escaped. This is a helper function for Mysql:query.
:query
syntax: (:query Mysql-instance str-statement [lst-format-args])parameter: Mysql-instance - an instance of the Mysql class
parameter: str-statement - a SQL statement to execute
parameter: lst-format-args - format arguments to the SQL statement
Executes str-statement. Throws an error if the statement fails with the reason. If the statement returns results, a MysqlResult class instance is returned. Otherwise, returns the number of affected rows.
If lst-format-args is specified, all parameters are escaped (as necessary) to generate safe, valid SQL. No quoting of values is required in the format string; quotes are inserted as needed. To generate a NULL in the SQL statement, pass nil or the string "NULL".
example: (:query db "SELECT name, employee_id FROM employees")
=> (MysqlResult 1069216)
(:query db "DELETE FROM employees WHERE fired = 1")
=> 14
(:query db '("SELECT id FROM employees WHERE name = %s" '("Johnson, John")))
; SQL generated: SELECT id FROM employees WHERE name = 'Johnson, John'
=> (MysqlResult 1069216):insert-id
syntax: (:insert-id Mysql-instance)parameter: Mysql-instance - an instance of the Mysql class
Returns the id of the last inserted row when the target table contains an AUTOINCREMENT field.
:affected-rows
syntax: (:affected-rows Mysql-instance)parameter: Mysql-instance - an instance of the Mysql class
Returns the number of rows affected by the most recent query.
:escape
syntax: (:escape Mysql-instance str-value)parameter: Mysql-instance - an instance of the Mysql class
parameter: str-value - the string to escape
Escapes a string to assure safety for use in a SQL statement.
:databases
syntax: (:databases Mysql-instance)parameter: Mysql-instance - an instance of the Mysql class
Returns a list of the databases on this server.
:tables
syntax: (:tables Mysql-instance str-database)parameter: Mysql-instance - an instance of the Mysql class
parameter: str-database - (optional) the database to query for tables
Returns a list of tables available on this server. If str-database is
provided, the list of tables will be limited to that database.
:fields
syntax: (:fields Mysql-instance str-table)parameter: Mysql-instance - an instance of the Mysql class
parameter: str-table - the table to display
Returns metadata about the fields in str-table. The data is the result of a SHOW FIELDS query.
MysqlResult
syntax: (MysqlResult int-pointer)parameter: int-pointer - a pointer to a MYSQL_RES struct
Objects of this class are returned by Mysql:query as a result of queries that generate result sets. This class is not generally instantiated directly by the client.
:free
syntax: (:free MysqlResult-instance)parameter: MysqlResult-instance - an instance of the MysqlResult class
Frees the memory used by a result. Must be called for each MysqlResult generated, even if unused.
:num-rows
syntax: (:num-rows MysqlResult-instance)parameter: MysqlResult-instance - an instance of the MysqlResult class
Returns the number of results in this result.
:fields
syntax: (:fields MysqlResult-instance)parameter: MysqlResult-instance - an instance of the MysqlResult class
Returns a list of MysqlField instances corresponding to the columns in this result.
:fetch-row
syntax: (:fetch-row MysqlResult-instance as-assoc)parameter: MysqlResult-instance - an instance of the MysqlResult class
parameter: as-assoc - (optional) whether to return results as a list or association list
Returns one row from this result. If as-assoc is true, the results will be returned as an association list (true by default). If this is the final row in the result set, the MysqlResult instance is automatically freed.
:fetch-all
syntax: (:fetch-all MysqlResult-instance as-assoc)parameter: MysqlResult-instance - an instance of the MysqlResult class
parameter: as-assoc - (optional) whether to return results as a list or association list
Returns all rows from this result. If as-assoc is true, the results will be returned as an association list (true by default).
MysqlField
syntax: (MysqlField int-pointer)parameter: int-pointer - a pointer to a MYSQL_FIELD struct
Objects of this class are returned by MysqlResult:fields. It is used internally in generating result rows. This class is not generally instantiated directly by the client.
:name
syntax: (:name MysqlField-instance)parameter: MysqlField-instance - an instance of the MysqlField class
Returns the name of this field (or its alias).
:table
syntax: (:table MysqlField-instance)parameter: MysqlField-instance - an instance of the MysqlField class
Returns this field's table (or its alias).
:type
syntax: (:type MysqlField-instance)parameter: MysqlField-instance - an instance of the MysqlField class
Returns this field's type.
mysql:on-connect
syntax: (mysql:on-connect list-credentials fn-callback)parameter: list-credentials - a list of parameters to pass to Mysql:connect
parameter: fn-callback - a function to call with the database connection
Connects to a MySQL server using list-credentials and calls fn-callback using the Mysql instance as the first argument. If an error occurred attempting connection, the error string is passed as the second parameter. The minimum contents of list-credentials must be '(str-host str-username str-password str-database).
The connection is automatically freed when mysql:on-connect returns.
example: (mysql:on-connect '("localhost" "user" "secret" "my_database")
(lambda (db err)
(if err
(println "Error! " err)
(println "Success! " db))))mysql:row-iter
syntax: (mysql:row-iter Mysql-instance str-sql bool-as-assoc fn-callback)parameter: Mysql-instance - a connect instance of the Mysql class
parameter: str-sql - a sql statement
parameter: bool-as-assoc - flags whether or not to pass rows as regular or association lists
parameter: fn-callback - a function to call for each row returned by the query
Iterates over the results of a query, passing a row at a time to fn-callback. The MysqlResult is automatically freed. The return value of mysql:row-iter is the result of the last call to fn-callback.
Note that each row is called with MysqlResult:fetch-row to avoid building intermediate lists.
example: (mysql:on-connect '("localhost" "user" "secret" "my_database")
(lambda (db err)
(if err
(println "Error! " err)
(mysql:row-iter db "SELECT * FROM some_table" true
(lambda (row) (println row))))))mysql:row-map
syntax: (mysql:row-map Mysql-instance str-sql bool-as-assoc fn-callback)parameter: Mysql-instance - a connect instance of the Mysql class
parameter: str-sql - a sql statement
parameter: bool-as-assoc - flags whether or not to pass rows as regular or association lists
parameter: fn-callback - a function to apply to each row returned by the query
Maps fn-callback over each row returned by querying Mysql-instance with str-sql. Memory used by the MysqlResult is automatically freed. Returns a list of the result of applying fn-callback to each row.
example: (mysql:on-connect '("localhost" "user" "secret" "my_database")
(lambda (db err)
(if err
(println "Error! " err)
(mysql:row-iter db "SELECT * FROM some_table" true first))))mysql:reduce-results
syntax: (mysql:reduce-results Mysql-instance str-sql bool-as-assoc fn-callback)parameter: Mysql-instance - a connect instance of the Mysql class
parameter: str-sql - a sql statement
parameter: bool-as-assoc - flags whether or not to pass rows as regular or association lists
parameter: fn-callback - a function to be applied in reducing the results of the query
Reduces the results of the query by applying fn-callback successively to slices of the list of rows from the left. On the first call to fn-callback, the arguments will be a number of rows equal to the number of parameters that fn-callback accepts. On each subsequent call, the first parameter will be replaced by the result of the previous call. See the apply function for a more detailed description of the mechanics of apply/reduce. The return value is the result of the final application of fn-callback.
example: (mysql:on-connect '("localhost" "user" "secret" "my_database")
(lambda (db err)
(if err
(println "Error! " err)
(mysql:row-reduce db "SELECT * FROM some_table" true
(lambda (row-1 row-2)
(+ (if (list? row-1) (first row-1) row-1) (first row-2)))))))Artful Code
