Initial commit of newLISP.
[newlisp.git] / modules / mysql51.lsp
bloba79c617d6990ed12d02298ba4fe986d862435dcf
1 ;; @module mysql51.lsp
2 ;; @description MySQL v.5.1 interface
3 ;; @version 2.6 - addition for mysql_escape_string (Jeff)
4 ;; @author Lutz Mueller 2003-2006, Gordon Fischer 2005, Jeff Ober 2007
5 ;;
6 ;; This MySQL 5.1 interface module has been tested on version 5.1.21
7 ;; of mysql from @link http://www.mysql.com www.mysql.com
8 ;;
9 ;; This implementation supports a maximum of 2,147,483,647
10 ;; rows in a database table. Now automatically adjusts row indexes to
11 ;; endian type of host CPU, but higher 32 bits are treated as 0 for now.
13 ;; <h3>Requirements</h3>
14 ;; At the beginning of the program file include a 'load' statment for the module:
15 ;; <pre>
16 ;; (load "/usr/share/newlisp/mysql5.lsp")
17 ;; </pre>
19 ;; A version of 'libmysqlclient' for a specific platform is required:
20 ;;
21 ;; on LINUX/UNIX: '/usr/local/mysql/libmysqlclient.so' <br>
22 ;; on Mac OS X: '/usr/local/mysql/libmysqlclient.dylib'
24 ;; To compile MySQL with client libraries use:
26 ;; './configure --prefix=/usr/local --enable-shared'
28 ;; This library might be in a different location on a particular
29 ;; installation of MySQL or have a different name.
30 ;; Change accordingly in the code at the beginning.
32 ;; The MySQL server itself may reside on a different machine
33 ;; on the network. The library 'libmysqlclient' will communicate
34 ;; with that server. The correct connection is created using
35 ;; the 'MySQL:connect' call.
37 ;; At the bottom of the module file 'mysql51r.lsp' a test routine 'test-mysql'
38 ;; is included to test for correct installation of MySQL.
40 ;; In the 'MySQL:connect' call of that test routine the correct parameters
41 ;; for the MySQL server location and user and password have to be inserted.
43 ;; <h3>Adapting mysql.lsp to other versions of MySQL</h3>
44 ;; Some of the functions like 'mysql_num_rows()' cannot be imported
45 ;; because they are really macros extracting data from structures
46 ;; like 'MYSQL' or 'MYSQL_RES'. See the file 'mysql.h' in your MySQL distribution.
48 ;; The file 'sql.c' in the newLISP distribution contains a program
49 ;; calculating the offsets of the most important fields in these
50 ;; structures. These offsets are used here to retrieve values for
51 ;; the number of rows in a result set, etc. Using these offsets
52 ;; and the information found in 'mysql.h' and 'mysql_com.h', other
53 ;; functions can be imported and wrappers built around them.
54 ;; In this case one needs to install the developer's version of
55 ;; MySQL to get the header files mentioned.
57 ;; <h3>Functions available</h3>
58 ;; <pre>
59 ;; MySQL:init ................ get a database handle MYSQL
60 ;; MySQL:connect ............. connect to a database
61 ;; MySQL:query ............... execute a SQL statement
62 ;; MySQL:num-rows ............ rows in result of query
63 ;; MySQL:num-fields .......... columns in result of query
64 ;; MySQL:fetch-row ........... get row from the query result
65 ;; MySQL:fetch-all ........... get all rows from the last query
66 ;; MySQL:database ............ return all database names
67 ;; MySQL:tables .............. return all tables names
68 ;; MySQL:fields .............. return all fields in a table
69 ;; MySQL:data-seek ........... position in result for fetching
70 ;; MySQL:affected-rows ....... number of affected rows from operation
71 ;; MySQL:inserted-id ......... last value of auto increment id operation
72 ;; MySQL:escape .............. escapes SQL input string using mysql_real_escape_string
73 ;; MySQL:error ............... get error message
74 ;; MySQL:close-db ............ close database connection
75 ;; </pre>
77 ;; <h3>A typical MySQL session</h3>
78 ;; The following code piece outlines a typical MySQL session:
79 ;; @example
80 ;; (load "mysql51.lsp) ; load the module file
82 ;; (MySQL:init) ; initialize
83 ;; (MySQL:connect "192.168.1.10" "auser" "secret" "mydb") ; logon
84 ;; (MySQL:query "select ...;") ; SQL query
85 ;; (MySQL:query "insert ...;") ; SQL query
86 ;; ...
87 ;; (MySQL:close-db)
89 ;; The database server is listening on IP 192.168.1.10. The program
90 ;; connects with username '"auser"' password '"secret"' to a database with
91 ;; the name '"mydb"'. After connecting SQL statements are performed and
92 ;; finally the program disconnects from the server.
94 (context 'MySQL)
96 (set 'files '(
97 "/usr/lib/libmysqlclient.so" ; Linux, UNIX
98 "/usr/local/mysql/lib/libmysqlclient.dylib" ; MacOS X
101 (set 'libmysqlclient (files (or
102 (find true (map file? files))
103 (begin (println "cannot find libmysqlclient library") (exit)))))
105 (import libmysqlclient "mysql_init")
106 (import libmysqlclient "mysql_real_connect")
107 (import libmysqlclient "mysql_get_host_info")
108 (import libmysqlclient "mysql_real_escape_string")
109 (import libmysqlclient "mysql_query")
110 (import libmysqlclient "mysql_real_query")
111 (import libmysqlclient "mysql_store_result")
112 (import libmysqlclient "mysql_free_result")
113 (import libmysqlclient "mysql_data_seek")
114 (import libmysqlclient "mysql_fetch_row")
115 (import libmysqlclient "mysql_close")
116 (import libmysqlclient "mysql_fetch_field_direct")
117 (import libmysqlclient "mysql_insert_id")
119 ; following constant offsets into 'C' data structures are different on each mayor MySQL
120 ; version compile and run util/sql.c from the distribution to obtain these numbers
122 ; check endianess of the host CPU
123 (set 'big-endian (= (pack ">ld" 1) (pack "ld" 1)))
125 (constant 'NUM_ROWS_OFFSET (if big-endian 4 0))
126 (constant 'NUM_FIELDS_OFFSET 72)
127 (constant 'ERROR_OFFSET 95)
128 (constant 'INSERT_ID_OFFSET (if big-endian 708 704))
129 (constant 'AFFECTED_ROWS_OFFSET (if big-endian 700 696))
131 ;; @syntax (MySQL:init)
132 ;; @return 'true' on success, 'nil' on failure.
134 (define (init)
135 (set 'MYSQL (mysql_init 0))
136 (if (= MYSQL 0) (set 'MYSQL nil))
137 (not (= MYSQL nil)))
139 ;; @syntax (MySQL:connect <str-server> <str-userID> <str-password> <str-db>)
140 ;; @param <str-server> The host name or IP address or <tt>0</tt> for localhost.
141 ;; @param <str-userID> The user ID for authentication.
142 ;; @param <str-password> The password for authentication.
143 ;; @param <str-db> The name of the database to connect to.
144 ;; @return 'true' for success or 'nil' for failure.
146 ;; Connects to a database on server and authenticates a user ID.
147 ;; '(MySQL:init)' must have been called previously.
149 (define (connect host user passw database)
150 (not(= (mysql_real_connect MYSQL host user passw database 0 0 0) 0)))
152 ;; @syntax (MySQL:query <str-sql>)
153 ;; @param <str-sql> A valid SQL query string.
154 ;; @return For 'insert' queries rerturns the inserted ID else 'true'
155 ;; for success or 'nil' for failure.
157 ;; Sends a SQL query string to the database server for evaluation.
159 (define (MySQL:query sql)
160 (if MYSQL_RES (mysql_free_result MYSQL_RES))
161 (set 'result (= (mysql_query MYSQL sql) 0))
162 (set 'MYSQL_RES (mysql_store_result MYSQL))
163 (if (= MYSQL_RES 0) (set 'MYSQL_RES nil))
164 (if (and result (find "insert into" sql 1)) (set 'result (inserted-id)))
165 result)
167 ;; @syntax (MySQL:num-rows)
168 ;; @return Number of rows from last query.
170 (define (num-rows)
171 (if MYSQL_RES (get-int (int (+ MYSQL_RES NUM_ROWS_OFFSET)))))
173 ;; @syntax (MySQL:num-fields)
174 ;; @return Number of columns from last query.
176 (define (num-fields)
177 (if MYSQL_RES (get-int (int (+ MYSQL_RES NUM_FIELDS_OFFSET)))))
180 ; format the result based on the field type.
183 (define (keep-type res_ptr field_addr column_num, data)
184 (set 'type_ptr (mysql_fetch_field_direct res_ptr (int column_num)))
185 ; The field type is the 20th field of the MySQL_FIELD structure
186 ; since fields 1-19 are all 4 byte fields we get the enum value
187 ; like so
188 (set 'data (get-int (int (+ type_ptr (* 19 4)))))
189 ; Consult 'enum_field_types' in mysql_com.h for values
190 (if (= data 1) ;; boolean
191 (get-string field_addr)
192 (= data 3) ;; integer
193 (int (get-string field_addr))
194 (= data 12) ;; datetime
195 (apply date-value (map int (parse (get-string field_addr) "[-: ]" 0)))
196 (= data 4) ;; float
197 (float (get-string field_addr))
198 ; else (will handle TEXT type 252)
199 (get-string field_addr)
203 ;; @syntax (MySQL:fetch-row)
204 ;; @return A list of field elements.
206 ;; Fetches a row from a previous SQL 'MySQL:query' 'select' statement.
207 ;; Subsequent calls fetch row by row from the result table until the
208 ;; end of the table is reached.
210 (define (fetch-row)
211 (if MYSQL_RES
212 (set 'rdata (mysql_fetch_row MYSQL_RES))
213 (set 'rdata 0))
214 (if (!= rdata 0)
215 (begin
216 (set 'row '())
217 (dotimes (field (num-fields))
218 (set 'field_addr (get-int (int (+ rdata (* field 4)))))
219 (if (= field_addr 0)
220 (push nil row -1) ;; what to do when the field contains NULL
221 (push (keep-type MYSQL_RES field_addr field) row -1)))
222 row)))
224 ;; @syntax (MySQL:fetch-all)
225 ;; @return All rows/fields from the last query.
227 ;; The whole result set from the query is returned at once as a list of row lists.
229 (define (fetch-all)
231 (dotimes (x (num-rows)) (push (fetch-row) all))
232 (reverse all))
234 ;; @syntax (MySQL:databases)
235 ;; @return A list of databases.
237 ;; Performs a 'show databases;' query.
239 (define (databases)
240 (query "show databases;")
241 (fetch-all))
243 ;; @syntax (MySQL:table)
244 ;; @return A list of tables in the database.
246 ;; Performs a 'show tables;' query.
248 (define (tables)
249 (query "show tables;")
250 (fetch-all))
252 ;; @syntax (MySQL:fields <str-table>)
253 ;; @param <str-table> The name of the table.
254 ;; @return A list of field description lists.
256 ;; For each field name in the table a list of specifications
257 ;; for that field is returned. The list starts with the name
258 ;; for the field followed by the type size/precision and
259 ;; other optional field descriptions.
261 (define (fields table)
262 (query (append "show fields from " table ";"))
263 (fetch-all))
265 ;; @syntax (MySQL:data-seek <num-offset>)
266 ;; @param <num-offset> The <tt>0</tt> based offset to position inside the data set.
267 ;; @return Always 'true'.
269 ;; Positions in the result set at a zero based offset
270 ;; for a subsequent 'MySQL:fetch-row' call. If the offset
271 ;; is out of the allowed range for the result set a subsequent
272 ;; fetch-row will return 'nil'.
274 (define (data-seek offset)
275 (if MYSQL_RES
276 (if big-endian
277 (mysql_data_seek MYSQL_RES 0 (int offset))
278 (mysql_data_seek MYSQL_RES (int offset) 0)))
279 true
282 ;; @syntax (MySQL:error)
283 ;; @return Text info about the last error which occured.
285 (define (error)
286 (if MYSQL (get-string (+ MYSQL ERROR_OFFSET))))
289 ;; @syntax (MySQL:affected-rows)
290 ;; @return Number of affected rows by the last 'MySQL:query' operation.
292 (define (affected-rows)
293 (if MYSQL
294 (get-int (int (+ MYSQL AFFECTED_ROWS_OFFSET)))))
296 ;; @syntax (MySQL:inserted-id)
297 ;; @return Last insert ID from an auto increment field.
299 (define (inserted-id)
300 ; (if MYSQL (mysql_insert_id MYSQL))
301 (if MYSQL (get-int (int (+ MYSQL INSERT_ID_OFFSET)))))
303 ;; @syntax (MySQL:escape <str-sql>)
304 ;; @return escaped string
306 ;; This function will escape special characters in <str-sql>, so that it
307 ;; is safe to place it in a MySQL query.
308 (define (escape value , safe-value)
309 (set 'safe-value (dup " " (+ 1 (length value))))
310 (MySQL:mysql_real_escape_string MySQL:MYSQL safe-value value (length value))
311 safe-value)
313 ;; @syntax (MySQL:close-db)
314 ;; @return Always 'true'.
316 ;; Closes database access. For new database acess, both 'MySQL:init' and
317 ;; 'MySQL:connect' functions have to be called.
319 (define (close-db)
320 (if MYSQL_RES (mysql_free_result MYSQL_RES))
321 (if MYSQL (mysql_close MYSQL))
322 (set 'MYSQL nil)
323 (set 'MYSQL_RES nil)
324 true)
326 (context 'MAIN)
328 ; ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
330 ; test data base functions
333 (define (test-mysql)
334 (MySQL:init)
335 (MySQL:connect 0 "" 0 "test")
337 (println "databases:")
338 (MySQL:query "show databases;")
340 (dotimes (x (MySQL:num-rows)) (println (MySQL:fetch-row)))
341 (println)
343 (MySQL:query "create table fruits (name TEXT(2000),
344 qty INT(3),
345 num INT(4) AUTO_INCREMENT UNIQUE);")
347 (MySQL:query "insert into fruits values ('apples', 11, null);")
348 (println "inserted-id: " (MySQL:inserted-id))
349 (MySQL:query "insert into fruits values ('oranges', 22, null);")
350 (println "inserted-id: " (MySQL:inserted-id))
351 (MySQL:query "insert into fruits values ('bananas', 33, null);")
352 (println "inserted-id: " (MySQL:inserted-id))
354 (println "inserted into fruits:")
355 (MySQL:query "select * from fruits;")
356 (println "\n" (MySQL:affected-rows) " affected rows in query select")
357 (dotimes (x (MySQL:num-rows)) (println (MySQL:fetch-row)))
359 (println "no rows = " (MySQL:num-rows) " no fields = " (MySQL:num-fields))
360 (println "fields = " (MySQL:fields "fruits"))
361 (println)
363 (println "tables:")
364 (MySQL:query "show tables;")
365 (dotimes (x (MySQL:num-rows)) (println (MySQL:fetch-row)))
366 (println)
368 (MySQL:query "select * from fruits;")
369 (MySQL:data-seek 2)
371 (println "data-seek to offset 2:")
372 (println (MySQL:fetch-row))
373 (println)
375 (println "fetch-all:")
376 (println (MySQL:query "select * from fruits;"))
377 (println (MySQL:fetch-all))
379 (MySQL:query "drop table fruits;")
380 (MySQL:close-db)
383 ; eof