From oleg@pobox.com Fri Oct 31 13:59:38 1997 Return-Path: dopost@grunt.dejanews.com Received: from growl.pobox.com (growl.pobox.com [208.210.124.27]) by www.lh.com (8.6.10/lighthouse-1.0) with ESMTP id NAA14048 for ; Fri, 31 Oct 1997 13:59:29 -0500 Received: from grunt.dejanews.com (grunt.dejanews.com [205.238.157.87]) by growl.pobox.com (8.8.7/8.8.5) with ESMTP id OAA27476 for ; Fri, 31 Oct 1997 14:05:50 -0500 (EST) Received: (from dopost@localhost) by grunt.dejanews.com (8.8.5/8.8.5) id NAA29578; Fri, 31 Oct 1997 13:02:55 -0600 From: oleg@pobox.com To: oleg Subject: The most primitive and nearly universal database interface Date: Fri, 31 Oct 1997 13:02:50 -0600 Reply-To: oleg@pobox.com Keywords: SQL, database, client-server, pipe, Scheme, Informix, Illustra Message-ID: <878324175.28994@dejanews.com> Newsgroups: comp.lang.scheme Organization: Deja News Posting Service Path: grunt.dejanews.com!not-for-mail Summary: Scheme/database interface via scripting of a SQL user front-end X-Article-Creation-Date: Fri Oct 31 18:56:18 1997 GMT X-Authenticated-Sender: oleg@pobox.com X-Http-User-Agent: Mozilla/3.0 (Macintosh; I; 68K) Status: RO There has been a request on this newsgroup for info on free relational/object database interfaces and ready-to-use packages. I'd like to point out an easily available poor-man solution. Its extended version - a client-server, gateway kind of thing - will be shown as well (using an Informix On-Line as an example). Here is the gist: ; Configuration parameters, for a particular case of ; Illustra RDBMS (define DB:RUN-SQL-CMD "/usr/local/ill/bin/nsql -U miadmin -n -w 0 -T ndb") ; Run a given SQL statement through the database, and ; return the result as a list of tokens (fields) ; We assume that different fields in the SQL output are ; separated with a character '%' (define (DB:run-sql-statement stmt) (with-input-from-file (string-append DB:RUN-SQL-CMD " -c \"" stmt "\" | ") (lambda () (do ((result '() (cons (next-token '(#\space #\% #\tab #\newline) '(#\% #\newline *eof*)) result))) ((eof-object? (skip-while '(#\newline))) (reverse result)))))) It's ugly, it is trivial. Yet it is nearly universal: _any_ RDBMS that has a user front-end can be interfaced to Scheme in this way. Indeed, given an SQL statement, this interface merely passes it to a front-end interpreter, takes the result and splits it into tokens, returning a list of values for all queried fields. For example, if a query returns three rows 2 fields each, the result would be a list of 6 strings. It's easy to modify the function to group fields pertaining to the same row in a sublist. Yet I found a flat list easier to deal with. Well, to actually separate fields within the lines of text the SQL front-end spits out, we need to make sure the field values are properly delimited. nsql separates fields with white spaces in its output; unfortunately a character field may contain white spaces of its own. The following trick helps in this case: (define (describe id) (DB:run-sql-statement (string-append "select unique term_id::text || '%', name || '%', context_t " "from Terms where term_id='"id "';"))) An SQL statement executed this way obviously shouldn't be too long. In reality, however, the limit isn't very constraining: one of the statements I submitted once to DB:run-sql-statement was 17 lines long. I also used this interface to compute a transitive closure of two tables (A "bill of materials" sort of problem, with complications due to interchangeable parts). Despite its gross inefficiency, this interface provides nevertheless a satisfactory response time for CGI scripts. A front-end SQL interpreter can easily be scripted from within Scheme in a genuine client-server way. In this case, there are no extra restrictions at all on the size of a SQL statement. Furthermore, in a true client-server spirit, a database connection is established only once per session, during which a multitude of queries/updates can be made. Here's an example using Informix 7.2 On-Line. ; Configuration parameters (define DB:PIPE-TO-SQL "/tmp/sql-to") (define DB:PIPE-FROM-SQL "/tmp/sql-from") (define DB:NAME "sysmaster") ; A sample db to query ; Incidentally, it's a system catalog of ; an Informix On-Line RDBMS (when #t (cerr nl "Launching an SQL middle-server..." nl) (OS:remove DB:PIPE-TO-SQL) (OS:remove DB:PIPE-FROM-SQL) (OS:system (string-append "/etc/mknod " DB:PIPE-TO-SQL " p ")) (OS:system (string-append "/etc/mknod " DB:PIPE-FROM-SQL " p ")) (OS:system (string-append "exec_with_piped " DB:PIPE-TO-SQL " 'dbaccess " DB:NAME " - > /tmp/log 2>&1' &")) ) ; Run a given SQL statement through the database, and ; return the result as a list of tokens; ; We assume that different fields in the SQL output are ; separated with a character '%' ; This function takes a variable number of arguments, which are ; all "concatenated" together to form a single SQL statement (define (DB:run-sql-statement stmt1 . stmt-others) (with-output-to-file DB:PIPE-TO-SQL (lambda () (display "unload to ") (display DB:PIPE-FROM-SQL) (display " delimiter '%' ") (display stmt1) (for-each (lambda (stmt) (display stmt)) stmt-others))) (with-input-from-file DB:PIPE-FROM-SQL (lambda () (do ((result '() (cons (next-token '(#\space #\% #\tab #\newline) '(#\% #\newline *eof*)) result))) ((eof-object? (skip-while '(#\newline #\%))) (reverse result)))))) ; A demo procedure that looks up a table based on its name (pattern) ; and prints the number of its rows (define (lookup-table-by-patname table-pat-name) (cout nl "Information for tables " table-pat-name nl) (let ((result (DB:run-sql-statement "select N.tabname, I.ti_nrows from systabnames N, systabinfo I " "where N.partnum = I.ti_partnum AND tabname LIKE '" table-pat-name "' ORDER BY 2;"))) (do ((result result (cddr result))) ((null? result) (newline)) (cout "Table " (car result) " has " (cadr result) " row(s)" nl)))) (lookup-table-by-patname "%auth%") (lookup-table-by-patname "%systa%") P.S. next-token, skip-while, etc. Scheme primitives, and exec_with_piped are other poor-man contraptions of mine. See http://pobox.com/~oleg/ftp/Scheme/ http://pobox.com/~oleg/ftp/Communications.html#sh-agents for the source code and more details. -------------------==== Posted via Deja News ====----------------------- http://www.dejanews.com/ Search, Read, Post to Usenet