SQLite, in its 'default' code configuration, contains enough functionality to
be a replacement for a shapefile.
However, there is one very significant feature that is wasteful to ignore:
spatial indexes.
In order for the SQLite RTree to be used, it needs accompanying code that
understands the
assignment in the previous line is possible only because
** the WO_ and SQLITE_INDEX_CONSTRAINT_ codes are identical. The
** following asserts verify this fact. */
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of Ben Harper
Sent
Hi,
I have attached a one-liner (vtab-collation.patch) that sends collation
information through to virtual tables.
The bigger patch (vtab-collation-full.patch) illustrates the header file
changes necessary to make this compile.
Unfortunately, it's not as simple as just one line of code, since
>From xBestIndex and xFilter, there is no way to determine the intended
>collation.
The default collation for a field is binary.
However, a LIKE operator needs an ASCII collation, and in the case of a virtual
table, the
constraints passed in to xBestIndex and xFilter assume an ASCII collation.
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Dan Kennedy [danielk1...@gmail.com]
Sent: 14 July 2011 06:02 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] caveat on blanket UPDATE triggers
On 07/14/2011 10:42 PM, Ben Harper wrote
This may be specific to RTree indexes - I haven't taken the time to fully
understand it.
Dangerous:
CREATE TRIGGER ON UPDATE ON tablename { SET RTREE MIN/MAX }
Fine:
CREATE TRIGGER ON UPDATE OF geometry OF tablename { SET RTREE MIN/MAX }
Failure to specify the field name explicitly results in
sible to change the database from within a
function called from SELECT statement. SELECT query doesn't ever
changes any data.
Pavel
On Thu, Jul 7, 2011 at 10:38 AM, Ben Harper <b...@imqs.co.za> wrote:
> I have a user-defined function that I register with create_function.
>
> Inside my fu
I have a user-defined function that I register with create_function.
Inside my function I try to drop an r-tree virtual table, but I get the error
SQLITE_LOCKED.
Is there something special one needs to do in order to drop a table from inside
a user-defined function?
To test, I call it simply
To: General Discussion of SQLite Database
Subject: Re: [sqlite] False negatives from RTree
On Tue, Jul 5, 2011 at 8:55 AM, Ben Harper <b...@imqs.co.za> wrote:
> Hi,
> Has anybody ever seen anomalous behaviour on an R*Tree. Specifically, false
> negatives?
> I'm using Spatialite 2.4.0
org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of Richard Hipp
Sent: 05 July 2011 03:20 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] False negatives from RTree
On Tue, Jul 5, 2011 at 8:55 AM, Ben Harper <b...@imqs.co.za> wrote:
> Hi,
> Has anybody ever s
Hi,
Has anybody ever seen anomalous behaviour on an R*Tree. Specifically, false
negatives?
I'm using Spatialite 2.4.0 RC.
What I end up with is a table into which I can insert new geometry, but that
geometry does not get retrieved by an appropriate RTree search.
Indeed, even a search which
This is low probability, but maybe the Fault Tolerant Heap is turned on for
sqlite.exe?
I believe you'll see the exe mentioned in here if that is the case:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\FTH\State
FTH was introduced in Windows 7.
Ben
___
: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of Dan Kennedy
Sent: 17 December 2010 03:53 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] BUSY on COMMIT when EXCLUSIVE is held is an unexpected
result
On 12/17/2010 07:54 PM, Ben Harper wrote:
> I h
I have this case:
Thread 1 Thread 2
--
BEGIN EXCLUSIVE
BEGIN EXCLUSIVE -> BUSY
... etc ...
BEGIN EXCLUSIVE -> BUSY
COMMIT
BEGIN EXCLUSIVE -> OK
...
ical for you...
I think this kind of a solution can work of me too (I dont want to block
the main thread EVER, and I have a steady stream of writes to the DB).
Can you please elaborate how did you implemented your solution?
Yoni.
On 25/11/2010 8:42 AM, Ben Harper wrote:
> A cyclical WAL
A cyclical WAL file has this problem:
You can never be sure when is the right time to wrap around.
If you wrap around too early, you run the risk of hitting the wall
put up by the earliest reader, who is still using the end of the
WAL file. In which case the writer would have to block on the
I have a database library that abstracts various DB types, such as DBF,
shapefiles, CSV, PostGIS, etc.
I use the Sqlite virtual table mechanism to provide SQL functionality on top of
any of these DB types.
Normally one would only need to do this for crude databases, such as a CSV
file, but
the
I have a virtual table, but I cannot figure out how to discover the
collating sequence of a query expression passed to xBestIndex.
As far as I can tell, these three statements ...
SELECT * FROM tab WHERE col = 'val';
SELECT * FROM tab WHERE col = 'val' COLLATE BINARY;
SELECT * FROM tab WHERE col
In wal.c, it reads "8 or 10 comparisons (on average) suffice to either locate a
frame in the WAL or to establish that the frame does not exist in the WAL".
I'm wondering -- how often does it occur that only a small subset of pages is
written to again and again, in sequence, such that the WAL
Are you sure that double precision in the RTree is necessary for your purposes?
Here is a table of floating point precision at various values:
The 'flip' values are the 'org' values with the lowest bit flipped.
org flip (delta)
0.01 0.01 (
On Wed, Nov 03, 2010 at 12:31:35PM +0200, Ben Harper scratched on the wall:
> I guess I could actually dump the hash table into a blob.
> I'm also doing something like a bloom filter, and I guess that can just
> as well go into a blob too.. Basically the system is a big cache,
>
] Using sqlite's WAL with a hash table store
FTS3 extension is very fast and scalable hash engine. I did test FTS3 up to
400+ millions of record and it's nice.
2010/11/3 Ben Harper <b...@imqs.co.za>
> Hi,
> I know the answer to this question is really "Just try it and see",
vember 2010 11:17 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Using sqlite's WAL with a hash table store
On 3 Nov 2010, at 8:30am, Ben Harper wrote:
> I know the answer to this question is really "Just try it and see", but I
> want to gauge whether the idea
Hi,
I know the answer to this question is really "Just try it and see", but I want
to gauge whether the idea is sane or not before I spend/waste time on the
effort:
I want to build a custom hash table DB, and to solve the concurrency+durability
I need something akin to a WAL, and SQLite's WAL
September 2010 03:24 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Breakpoints uncorrelated on MSVC amalgamation build
Which MSVC compiler has this bug? There's a link to information on that ?
On Thu, Sep 23, 2010 at 10:17, Ben Harper <b...@imqs.co.za> wrote:
&g
users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of Richard Hipp
Sent: 23 September 2010 01:57 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] How does xBestIndex know that a LIKE query is case
insensitive?
On Thu, Sep 23, 2010 at 7:16 AM, Ben Harper <b.
OK.
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of Teg
Sent: 23 September 2010 03:20 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Breakpoints uncorrelated on MSVC amalgamation build
Hello Ben,
I just discovered the MSVC compiler generates bad debug info for source files
larger than 64k lines, which is the case with the Sqlite amalgamation.
Does anyone know of a workaround?
Thanks,
Ben
___
sqlite-users mailing list
sqlite-users@sqlite.org
I have a virtual table implementation that implements the xBestIndex/xFilter
functions.
Problem:
A LIKE query (for eg. field LIKE "prefix%") gets sets to xBestIndex as a GT/LT
pair.
However, I can't tell from the sqlite3_index_info whether that GT/LT should be
NOCASE collation or BINARY
: Re: [sqlite] Any way to make xUpdate only alter the columns that are
changed?
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 07/28/2010 07:37 AM, Ben Harper wrote:
> then it would be great if the xUpdate function could inform one that only the
> field 'age' is being altered. As it
It seems apparent from the docs (and the function headers) that there is no way
to cause Sqlite's Virtual Table xUpdate function to notify the implementation
of the exact fields that were altered.
For example, if I do
UPDATE people SET age=40 WHERE name='jim'
then it would be great if the
To determine the type of columns in a view I use
SELECT typeof(column) FROM viewname LIMIT something;
Unfortunately if most of the column data is NULL then you can end up having to
scan the entire table.
I'm not sure how SQlite calculates these types, but this simple workaround has
been OK for
: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of Simon Slavin
Sent: 12 March 2010 11:54 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] rowid availability from views is inconsistent
On 12 Mar 2010, at 8:47am, Ben Harper wrote:
> Under cert
Under certain conditions, VIEWs do not have rowid values.
Is there some recommended workaround that I can use to ensure that I always get
non-null rowids with every VIEW?
CREATE TABLE one (i32 INTEGER);
INSERT INTO one VALUES(100);
INSERT INTO one VALUES(200);
CREATE VIEW v1 AS SELECT *
Hi,
Is there any official way of obtaining the database modification counter?
I'm talking about the counter that is mentioned in the Atomic Commit docs:
" In order to determine whether or not the database file has changed,
SQLite uses a counter in the database header (in bytes 24 through 27)
Thanks, that's perfect!
On Mon, Dec 1, 2008 at 3:06 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> "Ben Harper" <[EMAIL PROTECTED]> wrote in
> message
> news:[EMAIL PROTECTED]
>> To the best of my findings, it seems to me that one needs to write a
>>
To the best of my findings, it seems to me that one needs to write a
mini SQL parser in order to read the 'sql' field from sqlite_master,
in order to discover the fields in an Sqlite table. Is this really a
necessary design? Would it not be better if
sqlite3_table_column_metadata had a mode that
I was thinking about this issue some time ago, and it occurred to me
that the root node of a Btree is a great estimator of the contents of
the tree. Some brief mucking around proves this to me empirically, and
I'm guessing you could prove that it is never out by more than 2x, due
to the balancing
The only limitation imposed by SQL that I can think of would be
inefficiencies in its query plan builder. That is the part that
figures out how to use the indexes available in the database in order
to execute the SQL query most efficiently. So it really depends on
what type of SQL queries you are
The short answer is no. If you want to use the database for the
purposes for which it was created, then you have to play by its rules-
which in this case is to create fields for each of those categories.
On Tue, Mar 11, 2008 at 10:38 PM, Derek Developer
<[EMAIL PROTECTED]> wrote:
> Is it possible
40 matches
Mail list logo
