"Doug" <pa...@poweradmin.com> schrieb im
Newsbeitrag news:043201cba22a$f023df40$d06b9d...@poweradmin.com...
> This is where I think the problems will crop up.
> Can you imagine making a
> network round trip for each row fetch, and then for each
> column fetch of each row (sqlite3_column_type,
> sqlite3_column_double, etc).
>
> To avoid that, you would need to fetch a lot (buffer) of data and
> bring it back to the client. And if the response is large, will
> you page, or keep the transaction open on the server thus
> blocking all other clients?
>
> The devil is always in the details :)
In this case, not really...
One can perfectly work without the DBConnection
(SQLites DB-Hdl) on the clientside.
Read-Mode:
The only thing the SQLite-API needs in addition to support
an "RPC-AppServer-mode" better (but also worthwhile for
normal, "connected mode", e.g. for back-forward-navigation),
is a new to introduce resultset-container-object (or struct).
Just a "convenience-addition" in analogy to:
sqlite3_get_table
sqlite3_free_table
But instead of delivering an Row/Col-array of "sidewards allocated"
UTF8-string-pointers as the 2D-set - it could work in a way
similar to what for example my wrapper implements in its
cRecordset-Class.
I've done (already years ago) my performance-tests for that,
and by far the best (fastest) approach was, to allocate memory
vertically!, meaning in "stripes per column" (respecting the
expected storage-class of the column in question) - and *not*
a recordwise (horizontally) allocated chunk for each row.
For each column I reallocate (growing by a factor of ~1.5,
to avoid reallocs in each and every sqlite-step) usually two
different memory-areas ...:
e.g. for a "double-storage-class-column":
an ubyte array for Null- and "unexpected storageclass"-Flags
a double array which holds the non-null values
or for the blobs- and string-storage-class:
an ubyte array for Null- and "unexpected storageclass"-Flags
an "UTF8-stream-array", which contains concatenated Field-Values
a ulong array for the start-offsets within the UTF8-stream
for the integer-storage-class, I assume signed int8 first,
until the first value appears, which does not fit into
signed int8 anymore - in this case an "elevation" of
the allocated array-area is performed (moving over all
the previous values in a small cast-loop).
The stages in this elevation-mechanism are int8, int32, int64 -
this way especially the boolean-flag-columns (fitting into int8)
don't take up that much memory.
As mentioned above, there's already a flagging-mechanism
for unexpected storage-class-values, which could either
be "forced" into the expected storage-class (according to
a mapper-set of column-type-descriptions) - or just
converted to string and stored in a "sparse-matrix-area"
of the resultset, to be able to deliver such values later
on anyways (over a Binary-Lookup on a RowIdx/ColIdx-
key).
So in addition to:
sqlite3_get_table
sqlite3_free_table
We could use an accompanying and fast resultset-api as:
sqlite3_get_resultset
sqlite3_resultset_get_recordcount
sqlite3_resultset_get_columncount
sqlite3_resultset_get_columninfo(hRs, ColIdx, colInfoEnum)
sqlite3_resultset_get_value(hRs, RowIdx, ColIdx, strgTypeEnum)
sqlite3_free_resultset
Coming back to the RPC-stuff again:
The idea to have an encapsulation of vertically allocated
memory-stripes per column in such an resultset is not
only fast in normal Desktop-Mode (in delivering a "typed
copy of a Select" - together with a bunch of useful column-
infos, retrievable e.g. over: "Select * From T Where 0"),
the main-advantage of this kind of mem-alloc-scheme
comes into play within additional functions like that:
sqlite3_resultset_serialize(hRs, *pBytes)
sqlite3_resultset_deserialize(*pBytes, *hRs)
Instead of looping over single fields (or records), to build up
a resulting bytestream-serialization, it is now much faster, to loop
over the columnwise allocated chunks and concatenate these
in the resulting serialized byte-stream - and the allocation for
the resulting bytestream can now be done *once* (without
any needed reallocs) since all the column-stripe-sizes are
known beforehand.
As said, all this is not made "out of thin air" - this is
(basically) the implementation I'm working with in my
wrapper-classes in dhRichClient3.dll, which contains
classes for a DCOM-like AppServer-Mode too.
Able to deliver thousands of resultsets per second
over worker-objects, hosted in a threadpool.
What my serializable ResultSet-implementation supports
additionally, is a "CreateTableFromContent" method,
which I use often, to "beam up" a freshly received
serialized resultset-stream directly into a clientside
(usually InMemory-DB-) table.
sqlite3_resultset_deserialize_to_table(*pBytes, DbHdl, tblName)
So, for a reliable (and simple) servermode, Sylvains
suggestion is (from my practical experience) perfect for
SQLite.
Keep the sqlite-api "as is" - maybe introduce the roughly
outlined resultset-API directly into the sqlite-core-lib
(for convenience-benefits not only in RPC-mode) -
and then a ("loosely attached to the SQLite-project")
implementation of a multithread- (or multi-process)
RPC-server could be written - in a way that
resembles somewhat to the Fossil-implementation.
A standalone-executable, able to deliver serialized
resultsets already (and to receive and parse SQL-
Execute-Commands, to support simple write-transactions)
and for "more advanced stuff" such a server could support
"RPCs of any kind" too, against Dlls which are placed
beside the executable at the serverside, and which one
could call dynamically from the clientside then "by name".
RpcHdl = rpc_connect(IP, Port, optUserName, optPassWord, optMode)
errResult = rpc_doroundtrip(RpcHdl, DllName, FunctionName,
pResult, serializedParamSet)
This way also more advanced transactions at the serverside
could be covered.
Just my 2ct.
Olaf
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
