On Feb 12, 2007, at 7:32 PM, John Stanton wrote:
I suggest that you also look carefully at the manifest typing
implemented in Sqlite. If your language is strongly typed you will
have some design issues to address.
I am aware of this issue and already have a solution. It's part of
why I wanted precompiled queries to be distinct from their execution
state:
val AgeQuery = query db "select name, age from users where
age="iI";" oS oI $
will bind AgeQuery to a query factory that takes an integer as input
(the iI) and outputs a (string, integer) pair (from the oS oI). The
'$' is a terminating function.
This already works fine. Using a technique called functional
unparsing, the above line constructs a function (hidden behind the
query type) that converts+binds arguments and fetchs+converts result
columms. However, for some compiler implementations (not MLton), this
dynamic creation of a function is costly, so it should be done once
per 'query template'.
This binding should be good for more than just my favourite of the
many SML compilers.
sqlite3_exec ... implements callbacks and that you implement a
callback to handle each row. Then the logic of your interface is
elegant.
I already can do the functional equivalents. There is no real need
for a 'callback function' in a functional language. Functions can be
passed around like any other argument.
In the following code snippet, the function f takes the (string,
integer) pairs output by the query and creates a new string. The map
function takes the query result set and feeds it through this
function. The number '6' is the parameter which is bound to the
query's input.
fun f (s & i) = "User " ^ s ^ " has age: " ^ Int.toString i
val results = SQL.map f AgeQuery 6
The value results will now contain a 'string vector' with entries
like "User Tom has age: 6", "User Joe has age: 6", ...
The problem I have stems from sqlite3_stmt containing both the parsed
query and the query state. In a binding like the above, these would
ideally happen at two distinct points.
1. query parsing happens in the 'val AgeQuery = query ...' line
2. query state is created in the 'SQL.map f AgeQuery' line
Then the user function 'f' can quite happily re-use AgeQuery to
create a recursive use of the query, but with different state
(arguments and stack).
Without this separation, I am forced to choose to do both at step 1
or both at step 2:
1. both query state creation and parsing happen at 'val AgeQuery =
query ...'. This means that an attempt by 'f' to reuse AgeQuery must
result in an exception, as it would require two instances of the
AgeQuery state.
2. both query state creation and parsing happen at 'SQL.map f
AgeQuery'. This would allow 'f' to reuse AgeQuery, but it would mean
that every execution reparsed the query string. The documentation
implies this is unacceptably slow in some situations. As MLton-
compiled executables are about as fast as C [1], too slow for C is
too slow for SML.
If there were a separation between the execution state of a query,
and the prepared form of a query, then this would be a perfect fit.
At the moment, I take option #1. Now that I know SQLite can handle
subqueries, I would like to support this. It would be a shame to
prohibit recursive use of the same query.
I could also try hacking a hybrid approach. In this case, I mark the
AgeQuery as 'in-use' during the SQL.map. If a nested use of the query
occurs, I could clone the query, and use this clone for the nested
execution. From looking at the SQLite3 source code, it seems a clone
method would be relatively easy to add. I could also implement the
clone functionality in SML by re-executing prepare_v2 with a saved
copy of the query string.
You will find on reflection that the Sqlite API is simple and elegant
I didn't argue that it's inelegant. I *do* think it's a tad too
simple. The state of a query and the bytecode are combined into one
concept. It might be convenient most of the time, but it is a
confusion of two distinct things. You can run the same program more
than once in Unix. You can call the same method twice (even
recursively) in every programming language. I can't think of many
places where code must be one-to-one with data.
and will let you craft your interface provided you design it to
suit Sqlite, not have a preconceived notion and then be unable to
make the connection.
That's a valid point. I do have a preconceived notion of how this
should work. However, it comes from the standard idioms used in this
language, which (if at all possible) i would like to preserve.
[1] http://shootout.alioth.debian.org/
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
