Peoples :)
"D. Richard Hipp" <[EMAIL PROTECTED]>
07/04/2004 11:22 PM
To: [EMAIL PROTECTED]
cc:
Subject: [sqlite] A proposal for SQLite version 3.0
> A design proposal for SQLite version 3.0 can be found at:
> http://www.sqlite.org/prop2.html
> Feedback from the user community is strongly encouraged.
Since this is a fairly rare opportunity to make incompatible suggestions
that could make it into code, I thought I'd put this left-field one out
there:
My main use of sqlite is in a database that stores vast quantities of
historical data (individual changes on meter readings, that sort of
thing). The main table contains data for multiple instruments that each
provide their data in time order with their own updates, but out of order
with the updates of others:
Instrument1,2am,value is 0
Instrument2,1:59am,value is 3
Instrument1,3am,value is 1
Instrument2,3:01am,value is 4
This table is constructed in the order that data comes in, but the queries
I want to do are quite different:
SELECT * FROM thetable WHERE instrument="Instrument1" AND time >= 2am and
time <= 3am ORDER BY TIME;
Well, I have an index to make this work efficiently (the exact query and
index details probably aren't that important here). The problem is that
the index contributes significantly to the size of the database and the
fact that I keep the original ordering around but unused seems like a
waste of effort. Queries are also slower than they would be if they were
following an integer primary key along a real table, with the extra
O(log(n)) lookup for each result entry.
A little while ago a list reader suggested a kind of index (from ms
access, if I recall... I don't recall the term they used) that is not
external. Instead the index changes the order in which the table itself is
organised. You suggested at the time that if explicit ordering were
required the user could use a CREATE TABLE AS SELECT statement to get
things in that order, but that sqlite would have to table-scan as it would
have no saved knowledge of the table ordering. Moreover the table would
not remain ordered as new elements were ordered.
If a table could be ordered according to an index, rather than having an
external index, I think it would significantly improve the time and space
performance of my databases. I don't know whether my experience would be
shared by other users. It it were something that could go into 3.0 it
would at least do me some good.
The other thing that I think will help me most is the native storage of
numeric formats. It's great to see this happening :)
On the wchar_t subject, I've just looked up
http://docs.sun.com/db/doc/806-0477/6j9r2e2bp?a=view, which says wchar_t
is a long under 32-bit solaris and int under 64-bit solaris. Both numbers
are 32-bits long. According to http://en.wikipedia.org/wiki/UTF-32, a
32-bit representation is the only unicode that is actually fixed-width.
Both UTF-8 and UTF-16 are multi-byte, rather than wide characters. This
page also lists various known unicode encodings, so may be of some value.
Benjamin.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
