RE: Database interface - would like advice on oracle library bind ing
From: oleg@pobox.com [mailto:oleg@pobox.com]
I'd like to remark first that many real databases let us avoid opening many cursors at once. It seems to be more efficient to do as much as possible within the engine ...
I agree (and Tom Pledger makes the same comment). It's rare that I want to have more than one cursor open at once. When I've seen other developers do it, they're often doing something that would be better accomplished as a join. And that last time I remember doing it, it was a nested loops situation (which, on reflection, probably could have been done as a join... oh well). OTOH, I have seen interleaved fetches used to simulate a join where the specific DBMS product struggled to perform the join efficiently itself. In this case the developer is making up for a deficiency in the DBMS, and I don't want to exclude the option from the interface (if you agree with what you read on http://www.dbdebunk.com , then most DBMS products are deficient in a number of ways).
Still, opening several cursors may be unavoidable. The left fold approach may still help -- we _can_ mechanically invert a left fold combinator (to be precise, a half-baked version of it) into a lazy list. Please see a separate message "how to invert the left fold"
Found it. I'll have a look...
I believe the extract functions can be constructed automatically -- similar to the way Quickcheck constructs test cases. I believe that instead of
I though this might be possible, but I had no idea how to do it. I'll have a look at the Quickcheck source to see how it's done, unless you can suggest a better example.
buffers. It's hard for me to write the corresponding code because I don't have Oracle handy (besides, I like Informix better). Is it possible to come up with a "stub" that uses flat files? We are only interested in fetching rows. It doesn't matter if these rows come from a file or from a database. That would make prototyping the interface quite easier.
I wasn't interested so much in prototyping the interface, as trying to write an implementation that supported the interface(s) discussed previously. I intended to provide the left-fold interface, and was wondering if that was all that was needed (for selects). Still, this would be a good exercise for me, at least so I can figure out how to generate extraction functions.
From: Tom Pledger [mailto:Tom.Pledger@peace.com]
Here's one approach: find the OCI equivalent of JDBC's ResultSetMetaData, and use it to drive the allocation and freeing of buffers.
I've considered this, and I think it's the next route I'll take (the OCI supports it). At the least it'll give me the ability to perform arbitrary queries (at present I have to know the types of the result set columns and construct the extraction function manually). I've also considered stuffing more information into the Cursor type (which I've introduced), and using this in a modal fashion to decide what to do at each point.
Here's another: ... Make getInt (and friends) behave differently depending on the mode of the cursor they're passed: either allocate a buffer and return _|_, decode and return the current column of the current row, or free a buffer and return _|_.
Not wanting to sound too dumb, but how do you return _|_ ? Thanks, Alistair. ***************************************************************** The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). This information may be subject to legal professional or other privilege or may otherwise be protected by work product immunity or other legal rules. It must not be disclosed to any person without our authority. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are not authorised to and must not disclose, copy, distribute, or retain this message or any part of it. *****************************************************************
Here's another: ... Make getInt (and friends) behave differently depending on the mode of the cursor they're passed: either allocate a buffer and return _|_, decode and return the current column of the current row, or free a buffer and return _|_.
Not wanting to sound too dumb, but how do you return _|_ ?
Return
undefined
or better,
error "Erroneously demanded bottom result of buffer allocation phase"
Note that
undefined :: a
undefined = undefined
error :: String -> a
{- defined internally -}
HTH.
--KW 8-)
--
Keith Wansbrough
The following code illustrates a _generic_ interface to low-level database code. The left-fold iterator doQuery is completely generic over any possible iterator -- no matter how many columns the query returns, what are the types of these columns and what is the type of the seed (accumulator). The code for doQuery remains the same. The iterator allocates buffers for columns at the beginning and frees the buffers at the very end. Again, this buffer handling is generic. There is no longer need to write extraction/unmarshalling function for specific types of rows. We only need fetching functions for specific datatypes (not columns!). Again, the query and the row buffer management code is completely generic. I guess I'm repeating myself. The tests: -- Query returns one column of type String -- Never mind undefined: we return some static data in the buffers, -- we don't have any oracle to bind to test1 = doQuery undefined undefined iter1 ([]::[String]) where iter1:: String -> [String] -> Either [String] [String] iter1 s acc = Right $ s:acc -- Query returns two columns of types String and Int test2 = doQuery undefined undefined iter2 ([]::[(String,Int)]) where iter2:: String -> Int -> [(String,Int)] -> Either [(String,Int)] [(String,Int)] iter2 s i acc = Right $ (s,i):acc -- Query returns three columns of types Int, String and Int test3 = doQuery undefined undefined iter3 ([]::[(Int,String,Int)]) where iter3:: Int -> String -> Int -> [(Int,String,Int)] -> Either [(Int,String,Int)] [(Int,String,Int)] iter3 i1 s i2 acc = Right $ (i1,s,i2):acc Use the function runtests to run either of these tests. The code follows. Compiler flags: -fglasgow-exts -fallow-overlapping-instances -- DB column buffers type BufferSize = Int data BufferType = ORA_char | ORA_int type Position = Int -- column number of the result table data Buffer = Buffer { bufptr :: String -- for this stub, just use String , nullindptr :: String -- likewise , retsizeptr :: String -- likewise , size:: BufferSize , pos:: Position , ora_type:: BufferType } -- understandably, below is just a stub ... alloc_buffer (siz, typ) ps = return $ Buffer { bufptr = show ps, pos = ps, size = siz, ora_type = typ} -- In this stub, don't do anything free ptr = return () -- DB Column types class DBType a where alloc_buffer_hints:: a -> (BufferSize, BufferType) col_fetch:: Buffer -> IO a instance DBType String where alloc_buffer_hints _ = (2000, ORA_char) col_fetch buffer = return (bufptr buffer) instance DBType Int where alloc_buffer_hints _ = (4, ORA_int) col_fetch buffer = return (read $ bufptr buffer) -- need to add more ... -- Row iteratees. Note, the folowing two instances cover ALL possible -- iteratees. No other instances are needed class SQLIteratee iter seed where iter_apply:: [Buffer] -> seed -> iter -> IO (Either seed seed) alloc_buffers:: Position -> iter -> seed -> IO [Buffer] instance (DBType a) => SQLIteratee (a->seed->Either seed seed) seed where iter_apply [buf] seed fn = col_fetch buf >>= (\v -> return$ fn v seed) alloc_buffers n _ _ = sequence [alloc_buffer (alloc_buffer_hints (undefined::a)) n] instance (SQLIteratee iter' seed, DBType a) => SQLIteratee (a->iter') seed where iter_apply (buf:others) seed fn = col_fetch buf >>= (\v -> iter_apply others seed (fn v)) alloc_buffers n fn seed = do this_buffer <- alloc_buffer (alloc_buffer_hints (undefined::a)) n other_buffers <- alloc_buffers (n+1) (fn (undefined::a)) seed return (this_buffer:other_buffers) free_buffers = mapM_ free -- The left fold iterator -- the query executor data Session -- not relevant for this example data SQLStmt db_execute session query = return () db_fetch_row buffers = return () -- use static data doQuery:: (SQLIteratee iter seed) => Session -> SQLStmt -> iter -> seed -> IO seed -- In this example, we just allocate buffers, "fetch" two rows and terminate -- with a clean-up doQuery session query iteratee seed = do buffers <- alloc_buffers 0 iteratee seed db_execute session query db_fetch_row buffers (Right seed) <- iter_apply buffers seed iteratee db_fetch_row buffers (Right seed) <- iter_apply buffers seed iteratee free_buffers buffers return seed -- Tests -- Query returns one column of type String test1 = doQuery undefined undefined iter1 ([]::[String]) where iter1:: String -> [String] -> Either [String] [String] iter1 s acc = Right $ s:acc -- Query returns two columns of types String and Int test2 = doQuery undefined undefined iter2 ([]::[(String,Int)]) where iter2:: String -> Int -> [(String,Int)] -> Either [(String,Int)] [(String,Int)] iter2 s i acc = Right $ (s,i):acc -- Query returns three columns of types Int, String and Int test3 = doQuery undefined undefined iter3 ([]::[(Int,String,Int)]) where iter3:: Int -> String -> Int -> [(Int,String,Int)] -> Either [(Int,String,Int)] [(Int,String,Int)] iter3 i1 s i2 acc = Right $ (i1,s,i2):acc runtests test = test >>= (mapM_ $ putStrLn . show)
participants (3)
-
Bayley, Alistair -
Keith Wansbrough -
oleg＠pobox.com