From: Noel W. <noe...@ya...> - 2002-10-25 15:45:37
|
Hi all, A few new additions in src/libs/database: a native PostgreSQL driver by Bruce, and a native MySQL driver by me. Both are very alpha; the MySQL driver hasn't even been tested. Both are generated using SWIG. SWIG is very easy to use; I strongly suggest anyone doing native bindings takes a look at it. At some point we want a common low-level DB API. This is to be a layer below SchemeQL, similar to Perl DBI/Java JDBC/Python whatever-they-call-it. We should aim for SRFI-cation. The plan, as such, is: > Layer 0: Absolutely minimal (connect, execute, ...) > Layer 1: Full but ugly (create-prepared-statement ...) > Layer 2: Nice macros (with-db-connection etc.) If anyone wants to start a discussion on the list, go for it. At the moment I feel I should finish my current projects before getting deeply involved in any others but I won't be able to resist responding to the odd email here and there. :-) Noel __________________________________________________ Do you Yahoo!? Y! Web Hosting - Let the expert host your web site http://webhosting.yahoo.com/ |
From: MJ R. <ma...@cl...> - 2002-10-25 16:32:57
|
> If anyone wants to start a discussion on the list, go for it. Which list? -- MJR |
From: MJ R. <ma...@cl...> - 2002-10-30 23:47:24
|
> At some point we want a common low-level DB API. This > is to be a layer below SchemeQL, similar to Perl > DBI/Java JDBC/Python whatever-they-call-it. We should > aim for SRFI-cation. The plan, as such, is: > > > Layer 0: Absolutely minimal (connect, execute, ...) > > Layer 1: Full but ugly (create-prepared-statement > ....) > > Layer 2: Nice macros (with-db-connection etc.) I'm not sure if I said, but I suspect these should be in parallel, or some overlapping Venn diagram rather than layers, as I suspect Layer 2 will be defined in cases where Layer 1 isn't (missing db API features, for example). I have implemented the following on top of Bruce's postgres.so module: DB:current-port DB:open-port DB:close-port DB:for-each DB:for-each-port DB:for-singleton DB:for-singleton-port DB:assoc-val DB:assoc-val-port DB:imperative-stmt DB:imperative-stmt-port The aim is to be similar to v2.11 of http://okmij.org/ftp/Scheme/db-util.scm but the concept of a database port and the variants that take a database port as their first argument were added after Oleg's suggestion. There are also two convenience functions: call-with-postgres DB:quote-string I'm now documenting and trying to refine the definitions, but I'd welcome comments on the shape of things at present. I think it's enough to do the tasks that I need done, but can other see any obvious problems yet? Once the documentation is done, who do I email the code to? -- MJR |
From: Francisco S. <so...@ac...> - 2002-10-31 01:20:15
|
MJ Ray <ma...@cl...> writes: [...] > I'm now documenting and trying to refine the definitions, but I'd welcome > comments on the shape of things at present. I think it's enough to do the > tasks that I need done, but can other see any obvious problems yet? I think this is a great start. I haven't used Bruce's driver yet, I'll give it a go tonight, and review again Oleg's Database Access Tools. This should give me enough to start thinking about how SchemeQL should use them, right now it uses ODBC through SrPersist, and many things work based on SrPersist `return values'. > > Once the documentation is done, who do I email the code to? I want a copy, please. TIA, --Francisco |
From: Noel W. <noe...@ya...> - 2002-10-31 14:56:27
|
--- MJ Ray <ma...@cl...> wrote: > I'm not sure if I said, but I suspect these should > be in parallel, or some > overlapping Venn diagram rather than layers Ok. > DB:current-port DB:open-port DB:close-port > DB:for-each DB:for-each-port > DB:for-singleton DB:for-singleton-port > DB:assoc-val DB:assoc-val-port > DB:imperative-stmt DB:imperative-stmt-port No need to shout. Not sure about the ':' either. How about db-for-each etc? I don't particularly like the (proc-init . proc) form that Oleg has in DB:for-each. It don't see the need for it. Also don't like the way the control flow is interwoven with the return value of proc. That's quite messy. And it isn't really a for-each, it more a map. > but the concept of a database port Nice. > and the variants that take a database > port as their first argument Hmmm. What about as their last argument, and use a parameter current-database-port as per read/current-input-port? > There are also two convenience functions: > > call-with-postgres DB:quote-string > call-with-database is more generic. Quoting is nice. Ok, that's enough quibbling. Noel __________________________________________________ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ |
From: MJ R. <ma...@cl...> - 2002-10-31 18:34:22
|
Noel Welsh <noe...@ya...> wrote: > No need to shout. Not sure about the ':' either. How > about db-for-each etc? Here I'm sticking with a scheme that I'm not particularly fond of for sake of compatibility with the original. Of course, in PLT Scheme's default db:for-each is the same. > I don't particularly like the (proc-init . proc) form that Oleg has in > DB:for-each. It don't see the need for it. Also don't like the way the > control flow is interwoven with the return value of proc. That's quite > messy. And it isn't really a for-each, it more a map. It's not really a map either, but I'll suggest to Oleg that map is a better name for it. The control flow aspect seems wrong at first glance but does make sense. The two special cases that are there are both quite common occurances when working with databases, I think, and it allows the DB:for-each to do the housekeeping required when you lose interest in a result set part way through. In a lot of cases, you can avoid having to use them, though. >> and the variants that take a database >> port as their first argument > Hmmm. What about as their last argument, and use a > parameter current-database-port as per > read/current-input-port? Most of procedures already took a variable number of arguments, so doing this is non-trivial. Actually, it can be done, but whether it's worth doing depends how slow reverse is. Anyone know? >> There are also two convenience functions: >> call-with-postgres DB:quote-string > > call-with-database is more generic. Yes, but this way allows a number of databases to be used at once. The idea was to make transferring between databases easy by using wrappers that only exported the call-with-* functions: (call-with-postgres "dbname=new" (lambda () (apply DB:imperative-stmt (cons "insert into ..." (some-function-of (call-with-mysql "db=old" (lamdba () (DB:for-each list "select ...")))))))) Actually, I've probably fluffed that: what scope would DB:.... be resolved in? > Ok, that's enough quibbling. I'm surprised there's so little so far. MJR |
From: Bruce B. <ba...@en...> - 2002-10-31 19:50:46
|
OK, I'm back from tapas-land and (other than becoming a bit more gravitationally enhanced) ready to get back to this stuff. MJ Ray wrote: > Noel Welsh <noe...@ya...> wrote: > >>No need to shout. Not sure about the ':' either. How >>about db-for-each etc? > > > Here I'm sticking with a scheme that I'm not particularly fond of for sake > of compatibility with the original. Of course, in PLT Scheme's default > db:for-each is the same. > I kind of like the look of '<namespace> ':' <proc>' but see below. > >>I don't particularly like the (proc-init . proc) form that Oleg has in >>DB:for-each. It don't see the need for it. Also don't like the way the >>control flow is interwoven with the return value of proc. That's quite >>messy. And it isn't really a for-each, it more a map. > > > It's not really a map either, but I'll suggest to Oleg that map is a better > name for it. > > The control flow aspect seems wrong at first glance but does make sense. > The two special cases that are there are both quite common occurances when > working with databases, I think, and it allows the DB:for-each to do the > housekeeping required when you lose interest in a result set part way > through. In a lot of cases, you can avoid having to use them, though. > 'for-each' implies to me a side-effect-only behavior whose return value is unspecified; this can certainly be changed for our usage but I think I prefer something like 'map-selected-rows' to make it more explicit that this will return a list of values that have been selected from the database and then operated upon by a procedure. Also, I think I agree with Noel about proc-init; it seems unnecessary. Is DB:for-singleton supposed to be an optimization of DB:for-each? Why is it needed in an API? However I can see lots of cases when I would like to (for instance) get a single aggregate value (SUM, MAX, AVG) from a query so DB:assoc-val seems OK. Why is DB:imperative-stmt defined as asynchronous while the other procedures are synchronous? This can't work! I would rather have a parameter or flag to make procedures synchronous/asynchronous and then we'd need polling/notification/callbacks to handle async stuff. I have to say I'm not wild about Oleg's API but I don't have an alternative yet. Stay tuned. > >>> There are also two convenience functions: >>> call-with-postgres DB:quote-string >> >>call-with-database is more generic. > > > Yes, but this way allows a number of databases to be used at once. The idea > was to make transferring between databases easy by using wrappers that only > exported the call-with-* functions: > > (call-with-postgres "dbname=new" > (lambda () > (apply DB:imperative-stmt > (cons > "insert into ..." > (some-function-of > (call-with-mysql "db=old" > (lamdba () > (DB:for-each list "select ...")))))))) > > Actually, I've probably fluffed that: what scope would DB:.... be resolved > in? This is where I'd use module renaming so you'd have: (require (prefix pg: (lib "postgresql-iface.ss")) (prefix mq: (lib "mysql-iface.ss"))) (pg:with-db "dbname=new" (lambda () (apply pg:imperative-stmt (cons "insert into..." (some-function-of (mq:with-db "db=old" (lambda () (mq:for-each list "select ...")))))) The "default" prefix could be 'db:' when namespace collision isn't an issue. |
From: MJ R. <ma...@cl...> - 2002-11-01 01:22:28
|
Bruce Butterfield <ba...@en...> wrote: > 'for-each' implies to me a side-effect-only behavior whose return value > is unspecified; this can certainly be changed for our usage but I think > I prefer something like 'map-selected-rows' to make it more explicit > that this will return a list of values that have been selected from the > database and then operated upon by a procedure. Need it be a list of rows that have been SELECTed? Does our database interface only work for SQL92 databases? Now there's a question. > Also, I think I agree with Noel about proc-init; it seems unnecessary. No-one is demanding its use and I'm being conservative about removing from the API I was copying: I assume there was a need for it some time, else why would it be there? > Is DB:for-singleton supposed to be an optimization of DB:for-each? Why > is it needed in an API? However I can see lots of cases when I would > like to (for instance) get a single aggregate value (SUM, MAX, AVG) from > a query so DB:assoc-val seems OK. Well, there are lots of cases where I get a single row from a database (eg user records), so the for-singleton convenience function makes sense to me. > Why is DB:imperative-stmt defined as asynchronous while the other > procedures are synchronous? This can't work! I would rather have a > parameter or flag to make procedures synchronous/asynchronous and then > we'd need polling/notification/callbacks to handle async stuff. I've not yet considered synchronicity of the interface. At the moment, it's all just as libpq left it. > I have to say I'm not wild about Oleg's API but I don't have an > alternative yet. Stay tuned. Well, I like it better than any other low-level interface I've seen to date (not only in Scheme, but in other languages too), which is part of the reason I'm copying it. Some of the bits look a bit hairy at first glance, but I can see why most of them could be useful. [...] > The "default" prefix could be 'db:' when namespace collision isn't an issue. Good idea. Is that specified in the module or the program? Sorry for the delay uploading. sf.net is being slow to sync the ssh keys for me. MJR |
From: Bruce B. <ba...@en...> - 2002-11-01 18:00:26
|
MJ Ray wrote: > Bruce Butterfield <ba...@en...> wrote: > >>'for-each' implies to me a side-effect-only behavior whose return value >>is unspecified; this can certainly be changed for our usage but I think >>I prefer something like 'map-selected-rows' to make it more explicit >>that this will return a list of values that have been selected from the >>database and then operated upon by a procedure. > > > Need it be a list of rows that have been SELECTed? Does our database > interface only work for SQL92 databases? Now there's a question. > Yeah, I think we're modelling an API for access to SQL92 databases or some subset of that. Otherwise we will be chasing our tails trying to be too general (extentions/hierarchical/network/OO/etc.) The underlying database should be capable of handling some portion of SQL92 or it is out of scope. > >>Also, I think I agree with Noel about proc-init; it seems unnecessary. > > > No-one is demanding its use and I'm being conservative about removing from > the API I was copying: I assume there was a need for it some time, else why > would it be there? > Sorry, I think the point I was trying to make is that you proposed an API for our purposes and I disagreed with some of its functionality. I have no doubt that Oleg has a bigger brain than I do but that doesn't make his API sacred. One of the things I'm trying to understand is where this "low-level" interface leaves off and SchemeQL begins. SchemeQL itself is a fairly low-level wrapper of SQL92 that uses an ODBC wrapper (SrPersist) to communicate to the DBMS. What I originally thought we were attempting to accomplish was to replace SrPersist with an "ODBC-lite" but it looks like we're getting into areas of abstraction of SQL itself that (I thought) SchemeQL was to accomplish. I have nothing against this since my area of interest is really in building a "functional" interface to existing DBMSs like MySQL and Postgresql but I think we may be in some pretty serious scope-creep here. |
From: Noel W. <noe...@ya...> - 2002-11-04 16:17:03
|
--- Bruce Butterfield <ba...@en...> wrote: > Yeah, I think we're modelling an API for access to > SQL92 databases or some subset of that. Agreed > One of the things I'm trying to understand is where > this "low-level" > interface leaves off and SchemeQL begins. SchemeQL > itself is a fairly > low-level wrapper of SQL92 that uses an ODBC wrapper > (SrPersist) to > communicate to the DBMS. What I originally thought > we were attempting to > accomplish was to replace SrPersist with an > "ODBC-lite" but it looks > like we're getting into areas of abstraction of SQL > itself that (I > thought) SchemeQL was to accomplish. My goal is to get an ODBC-lite. SchemeQL aims to be a high-level query language ala C.J. Date's D. One concrete difference is that the database drivers we're discussing deal with SQL strings. SchemeQL deals with query objects and provides a language to construct and manipulate them. That said there is value in providing abstractions on top of drivers (like with-transaction) that will be useful for both SchemeQL and those who choose not to use SchemeQL. My reasoning behind proposing the 3 level approach to a 'low-level' db api is thus: - We can get something working quickly (level 0) and play around with it a bit to get a feel for the design issue. We avoid vapour ware. - We need to wrap the full complexity of the database driver at some stage. This includes things like stored procedures and prepared statements. This is tedious and would slow us down, hence we delay it to level 1. - We will want to build some abstractions on top of our drivers. This is level 2. By putting of this work we gain some experience with real world use of level 0 or 1 drivers to see what types of abstractions we really want. I believe SchemeQL will be able to take advantage of these abstractions so we should see what we can provide to it. I suspect once SchemeQL isn't tied to ODBC its userbase will increase dramatically so we should get some good feedback to incorporate in here. Below is a design for a minimal (Layer 0) API. It's based on the Firebird API, which in turn is based on Oleg's API. The issues that came to mind as I wrote it are 1) the names of the procedures and 2) the decision to include both foldl and foldr. Noel > (create-database statement) -> connection : String -> Connection Creates a database using the "CREATE TABLE ..." given in statement. Returns a database connection object. Rationale: Firebird at least has a separate API call to create a database so we need to recognise when a CREATE statement. > (delete-database connection) : Connection -> void Deletes the database referenced by connection. Rationale: As for create-database > (connect-to-database . params) -> connection : Any ... -> Connection Connects to an existing database using the supplied parameters. Returns a database connection object. Rationale: Connection parameters are database specific. We expect specific drivers to provide specialised forms for the particular database they support. This is the most general form. > (disconnect-from-database connection) : Connection -> void Disconnects from the database referenced by connection > (db-execute-statement connection statement) : Connection String -> void Executes the statement on the database given by connection. The statement is assumed to not return results (e.g. a CREATE TABLE, INSERT etc.) > (db-foldl connection proc seed statement) : Connection ((Any ...) -> Any) Any String -> Any Executes the statement on the database referenced by connection. Folds proc over the results in the order they are returned by the database. Returns the value of the last call to proc. If the query returns tuples with n elements, proc takes n+1 values; the last value being the previous result of the call to proc, or the seed if there is no previous result. > (db-foldr connection proc seed statement) : Connection ((Any ...) -> Any) Any String -> Any Executes the statement on the database referenced by connection. Folds proc over the results in the opposite order they are returned by the database. Returns the value of the last call to proc. If the query returns tuples with n elements, proc takes n+1 values; the last value being the previous result of the call to proc, or the seed if there is no previous result. __________________________________________________ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ |
From: Bruce B. <ba...@en...> - 2002-11-04 17:13:57
|
Noel Welsh wrote: > --- Bruce Butterfield <ba...@en...> wrote: > >>(create-database statement) -> connection > > : String -> Connection > > Creates a database using the "CREATE TABLE ..." > given in statement. Returns a database connection > object. > > Rationale: Firebird at least has a separate API > call to create a database so we need to recognise > when a CREATE statement. > This may be biting off a bit much since it gets deeply into the areas of "what is a database" and "what is a connection" that each DBMS seems to have it's own ideas about. Certainly Oracle, Sybase, and Postgres have different concepts here. I think I could rationalize this by thinking about database creation in the same way I think about space allocation, clustering, index management, etc., that is as an administrative task done by a DBA and thus not covered in this API. foldr/foldl are excellent, just what we need. |
From: Noel W. <noe...@ya...> - 2002-11-05 13:06:34
|
--- Bruce Butterfield <ba...@en...> wrote: > Noel Welsh wrote: > This may be biting off a bit much since it gets > deeply into the areas of > "what is a database" and "what is a connection" that > each DBMS seems to > have it's own ideas about. Certainly Oracle, Sybase, > and Postgres have > different concepts here. Could you briefly expand a bit here? I'm not aware of the differences. That said, create- and delete- (or drop-) database are useful for testing purposes (so you can write tests that create a new db for each test and delete it afterwards; fast in Firebird but I guess you don't do that in Oracle ;-) but I'm happy to leave them out of the API. It doesn't look like JDBC supports them, for instance. Noel __________________________________________________ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ |
From: Bruce B. <ba...@en...> - 2002-11-05 20:48:42
|
Noel Welsh wrote: > --- Bruce Butterfield <ba...@en...> wrote: > >>Noel Welsh wrote: >>This may be biting off a bit much since it gets >>deeply into the areas of >>"what is a database" and "what is a connection" that >>each DBMS seems to >>have it's own ideas about. Certainly Oracle, Sybase, >>and Postgres have >>different concepts here. > > > Could you briefly expand a bit here? I'm not aware of > the differences. That said, create- and delete- (or > drop-) database are useful for testing purposes (so > you can write tests that create a new db for each test > and delete it afterwards; fast in Firebird but I guess > you don't do that in Oracle ;-) but I'm happy to > leave them out of the API. It doesn't look like JDBC > supports them, for instance. > Oracle considers a database an instance of an Oracle process(s); users have GRANT permissions on sets of tables, instances, tablespaces, etc. Sybase and Postgres have 'actual' database subdivisions within the DBMS; they are created and managed outside of SQL as administrative tasks. I don't think there is anything wrong with wrapping a test in a 'create database'/'drop database' script (which itself could be written in Scheme); it just shouldn't be part of the API. |
From: MJ R. <ma...@cl...> - 2002-11-04 17:21:51
|
Noel Welsh <noe...@ya...> wrote: >> (create-database statement) -> connection > : String -> Connection [...] > Rationale: Firebird at least has a separate API > call to create a database so we need to recognise > when a CREATE statement. Why not just test the input to imperative-stmt? Ditto for delete-stmt. >> (connect-to-database . params) -> connection > : Any ... -> Connection Is DB:open-port an acceptable name? Likewise close-port, imperative-stmt, fold and fold-right. I think we're mostly agreeing, modulo nomenclature, the current-port concept and what special cases should be in the basic API. Minimal subset is open-port, close-port, imperative-stmt, fold and fold-right. Maximal subset is that plus the with/without port variants, create-database, delete-database (why not drop btw?), map, for-each, for-singleton and assoc-val. Which do we go for? I'm inclined to be maximal, but not sure about create/delete-database and only have the with-port variants. People can use a single-connection version of the module if they have code for the old API. MJR |
From: MJ R. <ma...@cl...> - 2002-11-04 17:09:33
|
Bruce Butterfield <ba...@en...> wrote: >> No-one is demanding its use and I'm being conservative about removing from >> the API I was copying: I assume there was a need for it some time, else why >> would it be there? > Sorry, I think the point I was trying to make is that you proposed an > API for our purposes and I disagreed with some of its functionality. I > have no doubt that Oleg has a bigger brain than I do but that doesn't > make his API sacred. Once again, that's not what I meant to assert. I just think you have to have a *very* good reason to remove functionality from what is already in use. I can't see what harm it does to leave this in there, as it's really not much more code, and presumably it is required by some situations. MJR |
From: Ryan C. <rya...@ya...> - 2002-10-31 20:01:33
|
--- MJ Ray <ma...@cl...> wrote: > Noel Welsh <noe...@ya...> wrote: > > No need to shout. Not sure about the ':' either. How > > about db-for-each etc? > > Here I'm sticking with a scheme that I'm not particularly fond of > for sake > of compatibility with the original. Of course, in PLT Scheme's > default > db:for-each is the same. > > > I don't particularly like the (proc-init . proc) form that Oleg > has in > > DB:for-each. It don't see the need for it. Also don't like the > way the > > control flow is interwoven with the return value of proc. That's > quite > > messy. And it isn't really a for-each, it more a map. > > It's not really a map either, but I'll suggest to Oleg that map is > a better > name for it. I agree with Noel in that the "special return values for control flow" make me very uncomfortable. I wouldn't call this a map, either. It shouldn't be hard to write a real map, a real for-each, and a more general foldr and foldl to deal with pretty much every case you would want to see. (The first two can be implemented in terms of the second two.) (We could define db-spindle with the semantics of db:for-each, and then people could fold, spindle, and ... okay, sorry :) > The control flow aspect seems wrong at first glance but does make > sense. The two special cases that are there are both quite common > occurances when working with databases, I think, and it allows the > DB:for-each to do the housekeeping required when you lose interest > in a result set part way through. In a lot of cases, you can avoid > having to use them, though. I think the fold functions are general enough for the cases where you want to choose whether to deal with a particular row. If not, I wrote a method called mapfilter for spgsql which takes two functions: one to choose records to handle and another to handle them. Stopping processing is a little more complicated. To a first approximation, that's a good use of an escape continuation. Whatever code is reading records from the database connection will need to do some cleanup and get back into a ready state before the connection can be used again. So the behavior of this API in the presence of continuations is important to think about anyway. Why not simply use continuations as a method of stopping processing? (This is something I haven't gotten around to in spgsql, but I need to for correct behavior.) As far as usability goes, we could also offer a form which bound an escape procedure in the processing code. Then the users of the API wouldn't have to write the call/cc or let/cc themselves. > >> and the variants that take a database > >> port as their first argument > > Hmmm. What about as their last argument, and use a > > parameter current-database-port as per > > read/current-input-port? > > Most of procedures already took a variable number of arguments, so > doing > this is non-trivial. Actually, it can be done, but whether it's > worth doing > depends how slow reverse is. Anyone know? Linear in the length of the list :) Time-wise and allocation-wise. > >> There are also two convenience functions: > >> call-with-postgres DB:quote-string > ... I think quote-string is important (and it's another thing I haven't gotten to in spgsql, to my shame). More generally, some way of converting values between SQL external representations (strings) and PLT Scheme types (numbers, the date structure from mzlib, etc) would be very useful. Something like the following: ;; sql-format : symbol object -> string ;; Converts a value to its external representation in SQL. The ;; string returned is appropriate for embedding in a SQL query. eg (sql-format 'string "look at me!") => "'look at me!'" (sql-format 'date (make-date ...) => ?? ;; sql-parse : symbol string -> object ;; Converts a SQL external representation into a Scheme object. eg (sql-parse 'number "1234") => 1234 (sql-parse 'date "1 April 2002") => (make-date ...) These might have to be dependent on the dialects of SQL being used. Ryan __________________________________________________ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ |
From: MJ R. <ma...@cl...> - 2002-11-01 01:36:16
|
Ryan Culpepper <rya...@ya...> wrote: > I agree with Noel in that the "special return values for control > flow" make me very uncomfortable. I wouldn't call this a map, either. > It shouldn't be hard to write a real map, a real for-each, and a more > general foldr and foldl to deal with pretty much every case you would > want to see. (The first two can be implemented in terms of the second > two.) Do you mean fold and fold-right from SRFI-1? I admit that I'm not very familiar with their use. I probably should try them out a bit more. I'm leaving your continuation questions for later. Let's get the basics right first. I think continuations with the database API is going to be a pig whenever we add it. [...] >> depends how slow reverse is. Anyone know? > Linear in the length of the list :) Time-wise and allocation-wise. Damn these stupid machines! [...] > I think quote-string is important (and it's another thing I haven't > gotten to in spgsql, to my shame). More generally, some way of > converting values between SQL external representations (strings) and > PLT Scheme types (numbers, the date structure from mzlib, etc) would > be very useful. Useful? Yes, definitely. Suitable for inclusion in this SRFI? Maybe. Suitable for inclusion in this module's code? No, as it will depend on the database language being used rather than the database. We probably should have another module doing these things for SQL92 and all our SQL database-using scripts can use, regardless of low-level driver. Would you have time to write this? Also, mzscheme time/date or SRFI time/date? ;-) MJR |
From: Ryan C. <rya...@ya...> - 2002-11-04 15:29:34
|
--- MJ Ray <ma...@cl...> wrote: > Do you mean fold and fold-right from SRFI-1? I admit that I'm not > very familiar with their use. I probably should try them out a bit > more. Yes. PLT calls them foldl and foldr, and that's how I learned them. They cover pretty much every naturally recursive/iterative function over lists. > I'm leaving your continuation questions for later. Let's get the > basics right first. I think continuations with the database API is > going to be a pig whenever we add it. My point was just that it is unnecessary to add use hackish interfaces to allow users to abort processing. We can present a clean interface and deal with the complexity in the drivers. > [...] > > I think quote-string is important (and it's another thing I > haven't > > gotten to in spgsql, to my shame). More generally, some way of > > converting values between SQL external representations (strings) > and > > PLT Scheme types (numbers, the date structure from mzlib, etc) > would > > be very useful. > > Useful? Yes, definitely. Suitable for inclusion in this SRFI? > Maybe. > Suitable for inclusion in this module's code? No, as it will > depend on the > database language being used rather than the database. We probably > should > have another module doing these things for SQL92 and all our SQL > database-using scripts can use, regardless of low-level driver. > Would you > have time to write this? Also, mzscheme time/date or SRFI > time/date? ;-) I've lost track of what "this module" is, but I think this will need to be customized for each database. I believe most databases add their own types to the standard SQL types, or alias the names, or ... I might be able to write this. I don't know how much time I'll be able to devote to it, but I'll try to get it started. I haven't looked at SRFI-19, but if it provides better support for dates/time than mzscheme, I don't see a problem using it. Ryan __________________________________________________ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ |
From: MJ R. <ma...@cl...> - 2002-11-04 17:13:30
|
Ryan Culpepper <rya...@ya...> wrote: > Yes. PLT calls them foldl and foldr, and that's how I learned them. > They cover pretty much every naturally recursive/iterative function > over lists. OK. I'll try to implement them instead. Do you know how for-each and map would be implemented above it? >> I'm leaving your continuation questions for later. Let's get the >> basics right first. I think continuations with the database API is >> going to be a pig whenever we add it. > > My point was just that it is unnecessary to add use hackish > interfaces to allow users to abort processing. We can present a clean > interface and deal with the complexity in the drivers. Continuations seem to be a source of stress when dealing with the outside world. It seems all too easy to inundate the scheme with data if there's no way to abort this. Are SQL cursors required by SQL92? [...] > I've lost track of what "this module" is, but I think this will need > to be customized for each database. I believe most databases add > their own types to the standard SQL types, or alias the names, or ... Presumably they still have the standard SQL types and could be supersetted, while the DB: API could be consistent for other languages than SQL? > I might be able to write this. I don't know how much time I'll be > able to devote to it, but I'll try to get it started. [...] Thanks. It's the same for most of us, so I think we understand. MJR |
From: Ryan C. <rya...@ya...> - 2002-11-05 04:11:44
|
--- MJ Ray <ma...@cl...> wrote: > Ryan Culpepper <rya...@ya...> wrote: > > Yes. PLT calls them foldl and foldr, and that's how I learned > them. > > They cover pretty much every naturally recursive/iterative > function > > over lists. > > OK. I'll try to implement them instead. Do you know how for-each > and map would be implemented above it? (define (db-for-each f sql) (db-foldr (lambda (base . fields) (apply f fields)) #f ;; whatever, never used sql)) (define (db-map f sql) (db-foldr (lambda (base . fields) (cons (apply f fields) base)) null sql)) That assumes that the recursive result or accumulator is given as the first argument of f in foldr. If not, it requires some annoying and inefficient list hacking. > >> I'm leaving your continuation questions for later. Let's get > the > >> basics right first. I think continuations with the database API > is > >> going to be a pig whenever we add it. > > > > My point was just that it is unnecessary to add use hackish > > interfaces to allow users to abort processing. We can present a > clean > > interface and deal with the complexity in the drivers. > > Continuations seem to be a source of stress when dealing with the > outside > world. It seems all too easy to inundate the scheme with data if > there's no > way to abort this. Are SQL cursors required by SQL92? The PostgreSQL documentation mentions cursors being mentioned in SQL92, but I don't know to what extent (what operations are required on them, what behavior, etc). Do most DB APIs allow you to stop receiving results once you've sent a query? PostgreSQL doesn't. You get what you asked for and you have to deal with it. Would it be reasonable to say that the low-level driver has to deal with whatever it gets, and aborting can be dealt with at a higher level of the interface? Ryan __________________________________________________ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ |
From: Noel W. <noe...@ya...> - 2002-11-05 13:23:00
|
--- Ryan Culpepper <rya...@ya...> wrote: > Do most DB APIs allow you to stop receiving results > once you've sent > a query? PostgreSQL doesn't. You get what you asked > for and you have > to deal with it. Would it be reasonable to say that > the low-level > driver has to deal with whatever it gets, and > aborting can be dealt > with at a higher level of the interface? When you use MySQL cursors you _have_ to retrieve all the rows from the query: http://www.mysql.com/doc/en/mysql_use_result.html I don't think we should have cursors in level 0. My statement about streams wasn't intended to imply support for database cursors, only to suggest a possible interface on the Scheme side. The driver could implement this however was easiest (including getting all results at once which is the easiest method in MySQL) and still provide a stream interface on the Scheme side. Anyway, I think this is moot as we seem to be converging on a fold/fold-right interface (agreed about the names matching SRFI 1 BTW). In higher levels it should be possible to specify how results are fetched (all at once, or lazily) but that isn't necessary right now. I forgot to mention error handling in my DB API post: > (exn:db) All procedures throw an exception of type exn:db on error. My API proposal doesn't have a concept of the current-port but it isn't much extra work so I'm happy to see it added. I'm not sure about transactions. It's quite a bit more effort at not always necessary (see MySQL before v4 for example ;-). Any one got a feel for how hard it is to implement transactions? Maybe it's really easy now we're using SWIG. Noel __________________________________________________ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ |
From: Francisco S. <so...@ac...> - 2002-11-04 19:52:34
|
Ryan Culpepper <rya...@ya...> writes: > I think quote-string is important (and it's another thing I haven't > gotten to in spgsql, to my shame). More generally, some way of > converting values between SQL external representations (strings) and > PLT Scheme types (numbers, the date structure from mzlib, etc) would > be very useful. Something like the following: > > ;; sql-format : symbol object -> string > ;; Converts a value to its external representation in SQL. The > ;; string returned is appropriate for embedding in a SQL query. > eg (sql-format 'string "look at me!") => "'look at me!'" > (sql-format 'date (make-date ...) => ?? > > ;; sql-parse : symbol string -> object > ;; Converts a SQL external representation into a Scheme object. > eg (sql-parse 'number "1234") => 1234 > (sql-parse 'date "1 April 2002") => (make-date ...) > > These might have to be dependent on the dialects of SQL being used. This is a good idea, but I would promote it to the upper level (the sql level). If the lower-level db access standard is `smaller' we have better chances to have it widely adopted. The SQL level together with SRFI-13 (strings), and SRFI-14 (char-sets) might easily provide quote-string and friends. But, IMO, should be part of SchemeQL or equivalent. The basic idea is that I want a very fast low level driver, so that we can send it pre-compiled data with no much error checking, string quoting, etc., those should be performed by the user (or an sql level library). Does that make any sense at all? --Francisco |
From: MJ R. <ma...@cl...> - 2002-11-01 12:14:28
|
Noel Welsh <noe...@ya...> wrote: > [...] And it isn't really a for-each, it more a map. The importance of ordering means it isn't a map either, but more a for-each. Is the glass half-empty yet? |
From: Noel W. <noe...@ya...> - 2002-11-01 12:51:35
|
--- MJ Ray <ma...@cl...> wrote: > The importance of ordering means it isn't a map > either, but more a for-each. > Is the glass half-empty yet? Minor detail IMHO. The distinguising feature of map is that it returns a result. The ordering isn't specified to give wriggle room to implementors. Anyway, I'm with Ryan in that we should be implementing a fold, which is the universal iterator for this type of data. There is another option: to implement streams (aka lazy lists) and provide operations on them. E.g. streams-first, stream-rest etc. We can them build fold, map etc. on top of this stream implementation. There was a stream API posted to c.l.s a while ago. Maybe if we harass the author they'll finish it. Error-handling/cleanup seems like a problem to me. How do we ensure that database resources will be freed? E.g. if the user calls an escape continuation in the middle of a fold. I don't think we can guarantee this in general, at least not without support from the GC. I tend towards making this the user's problem. (In PLT Scheme we should be able to guarantee this but in R5RS Scheme no guarantees apply). Re names. PLT Scheme has modules so we don't need db: or db- prefixes (they use can add this via the module system). The Scheme standard is to have <type>-<operation-name> Eg: list-ref and vector-ref. I'm not in favour of crippling our implementation to support lame Scheme systems without a decent module system so I favour no prefixes in the PLT impl, and the standard prefix in SRFIs (which can be added by the user in the PLT implementation using the module system). Noel __________________________________________________ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ |
From: MJ R. <ma...@cl...> - 2002-11-04 17:07:56
|
Noel Welsh <noe...@ya...> wrote: > There is another option: to implement streams (aka lazy lists) and provide > operations on them. E.g. streams-first, stream-rest etc. How would you do that? Are all databases going to support this, or will it require some drivers to generate large data caches? > [...] I don't think we can guarantee this in general, at least not without > support from the GC. I tend towards making this the user's problem. So do I, for now. > Re names. PLT Scheme has modules so we don't need db: or db- prefixes > (they use can add this via the module system). [...] I agree, but we should add db: when importing in our own code, in most cases, for compatibility with the upcoming SRFI. -- MJR| v ---|--[ Something else will appear here eventually, I guess... ]-----| `--[ http://mjr.towers.org.uk/ ]---------[ slef at jabber.at ]-----' |