Re: [sqlite] 2 problems with sqlite3explorer.exe

[Ralf Junker]

Hello F.W.A. van Leeuwen,

I have reason to believe that the the problem you describe might be present in 
my "SQLite Spy" (http://www.yunqa.de/delphi/sqlitespy/) as well, since the 
program is very similar to SQLite3 explorer.

However, I was not able to reproduce the problem with SQLite3 Explorer in the 
first place. Could you describe it in a little more detail to get me going?

Thanks,

Ralf

>2. I have a table with signed 32-bit integers. Doing queries with negative 
>values doesn't work. I have to specify the 2's complement value to get it 
>working.

RE: [sqlite] info

[Reid Thompson]

Reid Thompson wrote:
> info

apologies - i was trying to get the mailing list to give me a listing of
available commands.  Trying to suspend my subscription while out of the
office.

reid

[sqlite] info

[Reid Thompson]

info

Re: [sqlite] 2 problems with sqlite3explorer.exe

[F.W.A. van Leeuwen]

My previous post was not accurate.
In fact, my data was unsigned, but sqlite3explorer DISPLAYS numbers > 2^31 
as negative numbers.
Maybe sqlite3explorer could use 64-bit integers to display numeric integer 
values.

Best regards,
Frank.

Re: [sqlite] Index with two columns

[D. Richard Hipp]

On Fri, 2005-06-03 at 13:20 +0200, Jakub Adamek wrote:
> Hello, please, is there any way to make SQLite use an index on two 
> columns when I want to select all rows which have some combination of 
> the two columns?
> 
> SELECT * FROM PointFeature WHERE
> DsetId=203 AND SectId IN (4,400);
> 

SQLite does use multiple columns of a multi-column index
for == constraints.  But for an IN operator, it will only
using a single column.

This is something that I need to work on.

In the meantime, I suggest the following work-around:

   SELECT * FROM PointFeature WHERE DsetId=203 AND SectId=4
   UNION ALL
   SELECT * FROM PointFeature WHERE DsetId=203 AND SectId=400;

-- 
D. Richard Hipp <[EMAIL PROTECTED]>

[sqlite] 2 problems with sqlite3explorer.exe

[F.W.A. van Leeuwen]

To the author of sqlite3explorer: Thanks! It's a great tool!!

I would like to support the tool by submitting 2 problems I've found:
1. The rowid (implicit column) is not displayed.
2. I have a table with signed 32-bit integers. Doing queries with negative 
values doesn't work. I have to specify the 2's complement value to get it 
working.

Best regards,
Frank.

Re: [sqlite] Porting SQLite to platform with no 64-bit integer support

[F.W.A. van Leeuwen]

Never mind, the native compiler DOES support 64-bit integers :-)

Re: [sqlite] Index with two columns

[Derrell . Lipman]

Jakub Adamek <[EMAIL PROTECTED]> writes:

> Hello, please, is there any way to make SQLite use an index on two 
> columns when I want to select all rows which have some combination of 
> the two columns?
>
> My table is:
> CREATE TABLE PointFeature (
> DSetId INTEGER,
> SectId INTEGER,
> PntItemId INTEGER);
>
> CREATE INDEX xxx ON PointFeature (DSetId, SectId, PntItemId);
>
> and the query is
> SELECT * FROM PointFeature WHERE
> DsetId=203 AND SectId IN (4,400);
>
> But the index is used just to find the DsetId and not to find SectId.
> Is there another form of the SQL which could do that? Or do I have to 
> use two separate queries

Try this instead:

  SELECT * FROM PointFeature WHERE
DsetId=203 AND (SectId = 4 OR SectId = 400);

Derrell

Re: [sqlite] Index with two columns

[Brass Tilde]

> Hello, please, is there any way to make SQLite use an index on two
> columns when I want to select all rows which have some combination of
> the two columns?

> SELECT * FROM PointFeature WHERE
> DsetId=203 AND SectId IN (4,400);

I can't answer the question you asked, but I will point out one thing.  Many 
SQL engines attempt to optimize index use in queries.

For instance, in MS SQL Server, your query might *not* use the second column in 
the index if using the index would take perform more
poorly than not using it.  Using an index is not always faster than doing a 
table scan.  If there are only a few rows where DsetId
is equal to 203, the table scan of that subset could very well be faster than 
using an index to look up the values.

In our MS SQL Server environment, we usually don't even bother to create 
indexes on more than one field if there are going to be
less than a few hundred rows in a subset of a query like that.  It takes the 
server longer to do the index lookup than it would the
table scan.

[sqlite] Lemon question

[Ludvig Strigeus]

I'm using lemon to make a simple parser.I want to get more descriptive
error messages.

Right now, it prints
Syntax error near '123'.

Is there some way to make it print
Syntax error near '123', expecting ';'

or something similar?

/Ludvig

Re: [sqlite] Large databases howto

[Pedro Pascual]

Thanks for your answers. They seem encouraging.

A few extra comments and questions:

   * We are doing tests for the active/daily (5M records) file with
 BEGIN END packages of 1000 inserts (with 2 indexes), and seems to
 be OK. We need also to do some queries/updates on this file
 (around 100K a day), but it seems that sqlite can cope with them.
   * ¿What about querying/updating the around 200 (6 months) historical
 data files (5M records each)? We know of the limitation to
 connecting to, at most, 32 files. ¿Any advice on improving the
 performance of querying such a huge database?
   * We are thinking on merging 7 daily files (5M records, with 2
 indexes, each file) into one weekly file. ¿Which is the optimum
 way of doing this?

Thanks again,
Pedro Pascual

Pedro Pascual wrote:


Hi,

We are evaluating using sqlite for a huge database: around 1000 
millions records splitted into 200 files. Any experiences about this 
and tricks/howto's?


The characteristics of our project:

   * The environment is a Unix box (IBM pSeries 64 bit) with fast
 (USCSI-3) disks.
   * No record deletes
   * Most of the (history) data will have a low update rate, and will
 be used mainly for queries.
   * Heavy inserts in active file (500 per day), closed every day
 (no more inserts)
   * Just two indexes on the data.

Regards,
Pedro Pascual