[sqlite] FTS tokenize=unicode61: "full" or "simple" case folding?

[Tomash Brechko]

Hello,

https://www.sqlite.org/fts3.html#tokenizer page says that unicode61
tokenizer implements _full_ case folding (it doesn't emphasize the word,
but it's there).  ftp://unicode.org/Public/6.1.0/ucd/CaseFolding.txt has
the following rules:

-- cut --
...
00DF; F; 0073 0073; # LATIN SMALL LETTER SHARP S
...
1E9E; F; 0073 0073; # LATIN CAPITAL LETTER SHARP S
1E9E; S; 00DF; # LATIN CAPITAL LETTER SHARP S
...
-- cut --

I.e. in _full_ case folding both "?" (U+1E9E) and "?" (U+00DF) are mapped
to "ss", whereas in _simple_ case folding first one is mapped to the
second.  SQLite 3.11.0 works according to simple rules:

-- cut --
CREATE VIRTUAL TABLE t USING fts3tokenize(unicode61);
SELECT token FROM t WHERE input = "? ?";
-- cut --
gives
-- cut--
?
?
-- cut--

So which one is correct, documentation or implementation?  I also wonder
what a native German speaker would expect in full-text search case?
(Google gives different result counts for "Schlo?" and "Schloss", which
actually surprises me a bit).

-- 
  Tomash Brechko

[sqlite] [BUG] 3.11.0: FTS3/4 index emptied by 'optimize' inside transaction

[Tomash Brechko]

Hello,

With 3.11.0 if you run the following SQL you will get no result (which is
wrong):

-- cut --
BEGIN;
CREATE VIRTUAL TABLE fts USING fts4 (t);
INSERT INTO fts (rowid, t) VALUES (1, 'test');
INSERT INTO fts (fts) VALUES ('optimize');
COMMIT;
SELECT rowid FROM fts WHERE fts MATCH 'test';
-- cut --

If however you comment out either 'optimize' statement or BEGIN/COMMIT
you'll get '1' (which is correct).

Tested with 3.11.0 FTS3/4.  I have no 3.11.1 nor FTS5 so unable to test
there.  News for 3.11.1 mentions

  Fix an FTS5 issue in which the 'optimize' command could cause index
corruption.

which may or may not be related.


Regards,
-- 
  Tomash Brechko

[sqlite] [BUG?] BEFORE INSERT trigger has NEW.pk = -1 when NULL is passed for PK

[Tomash Brechko]

2015-05-25 15:27 GMT+03:00 Richard Hipp :

>
> https://www.sqlite.org/mark/lang_createtrigger.html?Cautions+val*ger.#mark
>

Missed that.  Thanks!

-- 
  Tomash Brechko

[sqlite] [BUG?] BEFORE INSERT trigger has NEW.pk = -1 when NULL is passed for PK

[Tomash Brechko]

Hello,

The following code

-- beg --
CREATE TEMP TABLE t (pk INTEGER PRIMARY KEY, i);
CREATE TEMP TABLE b (before_pk, i);
CREATE TEMP TABLE a (after_pk, i);

CREATE TEMP TRIGGER tb BEFORE INSERT ON t FOR EACH ROW
BEGIN
  INSERT INTO b VALUES (NEW.pk, NEW.i);
END;

CREATE TEMP TRIGGER ta AFTER INSERT ON t FOR EACH ROW
BEGIN
  INSERT INTO a VALUES (NEW.pk, NEW.i);
END;

INSERT INTO t VALUES (-1, -1);
INSERT INTO t VALUES (0, 0);
INSERT INTO t VALUES (NULL, 1);
INSERT INTO t (i) VALUES (2);

.header on
.mode column
.width -2 -9 -8
SELECT pk, before_pk, after_pk
FROM t NATURAL JOIN b NATURAL JOIN a;
-- end --

with SQLite 3.8.10.2 (also 3.8.9 and possibly earlier) produces

-- beg --
pk  before_pk  after_pk
--  -  
-1 -1-1
 0  0 0
 1 -1 1
 2 -1 2
-- end --

As you can see BEFORE INSERT trigger observes -1 in NEW.pk for INTEGER
PRIMARY KEY field when NULL is passed explicitly or implicitly.  Such magic
-1 makes it impossible to distinguish in BEFORE INSERT trigger the NULL (a
command to generate new PK) from explicit -1 (which is a valid value for
integer PK).  I couldn't find a description of this feature in either
https://www.sqlite.org/autoinc.html (section Background),
https://www.sqlite.org/lang_createtable.html#rowid or
https://www.sqlite.org/lang_createtrigger.html , so I consider this a bug
(expect to see NULL in NEW.pk for the last two inserts).

-- 
  Tomash Brechko

Re: [sqlite] [BUG] Adding an index changes query result

[Tomash Brechko]

2010/6/9 Pavel Ivanov 

> You can see that these 2 cases compare the same way. They both show
> that string is always greater than number and thus '11' > 2 and '2' >
> 11. And no affinity rules are applicable here because you use
> constants which don't have any affinity.


Perhaps this is the way it was supposed to work.  But presence of index
does affect something, so I assumed it somehow messes affinity (what else?).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [BUG] Adding an index changes query result

[Tomash Brechko]

Hello,

With SQLite 3.6.23.1 I see the following:

  $ ./sqlite3 /tmp/a.sqlite
  SQLite version 3.6.23.1
  Enter ".help" for instructions
  Enter SQL statements terminated with a ";"
  sqlite> CREATE TABLE t (c1 INTEGER, c2 INTEGER);
  sqlite> INSERT INTO t (c1, c2) VALUES (5, 5);
  sqlite> SELECT * FROM t WHERE c1 = 5 AND c2 > 0 AND c2 <= '2';
  sqlite> CREATE INDEX t_c1_c2 ON t (c1, c2);
  sqlite> SELECT * FROM t WHERE c1 = 5 AND c2 > 0 AND c2 <= '2';
  5|5

Note that the same query may or may not return the result based on
whether there's an index present.  Apparently on second invocation the
comparison is 5 <= '2', and numbers compare before strings.

It's not clear to me which result should be considered correct though:

  sqlite> select 11 > 2;
  1
  sqlite> select '11' > '2';
  0
  sqlite> select '11' > 2;
  1
  sqlite> select 11 > '2';
  0

It seems that last two cases should compare the same way, no matter
what the actual affinity rules are.


Regards,

-- 
   Tomash Brechko
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unique Index not working properly

[Tomash Brechko]

On Fri, May 25, 2007 at 09:16:25 +0300, Cariotoglou Mike wrote:
> > > Seems that there is a problem on unique key fields when null
> > > values are allowed
> > >
> > > CREATE TABLE z (
> > >   id VARCHAR(32) NOT NULL,
> > >   f1 VARCHAR(32) NOT NULL,
> > >   f2 VARCHAR(20),
> > >   PRIMARY KEY (id)
> > > );
> > > CREATE UNIQUE INDEX z_I1 ON z (f1, f2)
> > >
> > > insert into z values ('1', '1', null);
> > > insert into z values ('2', '1', null);
> > >
> this should give a unique constraint error, but does not.
> 
> testing with sql server and oracle shows that they *will* give an
> error. in this case, NULL = NULL seems to be true
>
> can this be fixed ? it is quite a deviation from standard behavior.

This page

  http://www.sqlite.org/nulls.html

explains the matter, line "nulls are distinct in a UNIQUE column".  It
says Oracle treats NULLs as distinct though.


-- 
   Tomash Brechko

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] What is the column value after ALTER TABLE ADD COLUMN?

[Tomash Brechko]

On Fri, May 18, 2007 at 14:00:21 -0500, Doug Nebeker wrote:
> UPDATE xyz SET newcol=function(other_column) WHERE newcol=null;
>  
> Both of the above fail. What is the value in newcol?

The value is NULL, however you have to say "IS NULL":

  UPDATE xyz SET newcol=function(other_column) WHERE newcol IS NULL;

NULLs aren't equal to each other:

sqlite> .nullvalue 
sqlite> select NULL = NULL;




-- 
   Tomash Brechko

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] default values at CREATE TABLE

[Tomash Brechko]

On Fri, May 11, 2007 at 20:43:46 +0200, Frank Pool wrote:
> I want to create a table with two colums:
> 
> One ist the primary key (test_num)
> and the second column sholud contain the value of the primary key (maybe as
> a string) by default.
> How can I define this table in sql ?
> 
> CREATE TABLE test_table ("test_num integer primary key AUTOINCREMENT NOT
> NULL, test_name varchar(256) DEFAULT ??? NOT NULL,")

You can't do this with SQL.  DEFAULT value is a constant, it is not
re-evaluated on every insert.  You may consider inserting the same
value into both test_num and test_name columns explicitly, since the
next value of AUTOINCREMENT column is predictable (and the largest
used value may be learned from sqlite_sequence table).


-- 
   Tomash Brechko

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Odd results return by SELECT query WHERE word = "word"

[Tomash Brechko]

On Fri, May 11, 2007 at 09:57:01 +0100, Matteo Vescovi wrote:
> Hi,
> I am getting weird results when executing a query that
> has this WHERE clause: WHERE word = "word".
> 
> The query works fine if I use WHERE word = 'word'.

The WHERE word = "word" is a no-op.  From "SQLite Keywords" section of
http://www.sqlite.org/lang.html:

'keyword'   A keyword in single quotes is interpreted as a literal
string if it occurs in a context where a string literal is
allowed, otherwise it is understood as an identifier.

"keyword"   A keyword in double-quotes is interpreted as an identifier
if it matches a known identifier. Otherwise it is
    interpreted as a string literal.


-- 
   Tomash Brechko

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] DBD::SQLite with FTS

[Tomash Brechko]

On Thu, May 10, 2007 at 08:04:51 -0500, P Kishor wrote:
> are there any guidelines on how to hook a new SQLite lib with the
> DBD package since the CPAN version seems to be running a few
> versions late.

No special actions needed, default build of DBD::SQLite will use
pre-installed shared library instead of the shipped code:

$ ldd /usr/lib/perl5//DBD/SQLite/SQLite.so
linux-gate.so.1 =>  (0xb7fc)
 (!)libsqlite3.so.0 => /usr/local/lib/libsqlite3.so.0 (0xb7f25000)
libc.so.6 => /lib/tls/libc.so.6 (0xb7dfe000)
/lib/ld-linux.so.2 (0x8000)



-- 
   Tomash Brechko

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] perfomance degradation for expr "foo = X or bar =X"

[Tomash Brechko]

On Wed, May 09, 2007 at 14:45:33 +, [EMAIL PROTECTED] wrote:
> You need an R-Tree index to do something like this.  The
> public-domain version of SQLite only supports B-Tree indices.
> So, no, indices are not going to help you here.

Alternatively to R-tree index, you may simply partition the space into
NxM cells, with, say, left and bottom border belonging to the cell
itself (while right and upper borders belonging to the right and upper
cells as their left and bottom borders respectively), and enumerate
these cells row-by-row like

  10|11|12|13|14
 ---+--+--+--+---
   5| 6| 7| 8| 9
 ---+--+--+--+---
   0| 1| 2| 3| 4


This way every point belongs to exactly one cell.  Then you create

   CREATE TABLE map (
   x INTEGER,
   y INTEGER,
   name TEXT,
   cell_no INTEGER
   );
   CREATE INDEX map_cell_no ON map (cell_no);

When inserting a point, you compute its cell_no (something like

  cell_no(x, y) = y / cell_height * cells_in_row + x / cell_width;


).  When doing a region query, you compute a set of cell numbers that
intersect with a query window, accumulate them in a (memory) table
selected_cells, and then do

   SELECT map.*
   FROM mem.selected_cells sc CROSS JOIN map ON sc.cell_no = map.cell_no;

Better yet to compute two sets: those cells that reside completely
within the query window, and those that intersect window border.
Points from the latter cells should be filtered further.

Reasonable cell dimensions based on typical query window size and
points distribution will give quite reasonable performance.


-- 
   Tomash Brechko

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] perfomance degradation for expr "foo = X or bar =X"

[Tomash Brechko]

On Wed, May 09, 2007 at 17:45:52 +0400, bash wrote:
> > One index per table rule.  At first glance it seems like SQLite could
> > use at least one index for "x=5 OR y=7" case too, but there is no
> > point in that, as the other part of the OR would require full table
> > scan anyway.
> 
> Why full table scan? :/
> SQLite can takes set (1) of rowid by ex(x) index for
> "X=5". Then takes another set (2) of rowid by ex(y) for "Y=7".
> Then SQLite need only to union this two set (1) and (2).
> Final SQLite should returns rows where rowid in (set1 union set2).

You should read it the following way: "SQLite can't use two indexes
per table, and using only one index is pointless, hence no index is
used at all".

So your question is actually "why SQLite uses at most one index per
table?".  My guess is that the benefits are out-weighted by the
implementation complexity.


-- 
   Tomash Brechko

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] perfomance degradation for expr "foo = X or bar =X"

[Tomash Brechko]

On Wed, May 09, 2007 at 16:34:57 +0400, bash wrote:
> On Wed, 9 May 2007 14:24:27 +0400
> Tomash Brechko <[EMAIL PROTECTED]> wrote:
> > From http://www.sqlite.org/optoverview.html section 6.0:
> > 
> >   Each table in the FROM clause of a query can use at most one index...
> > 
> > So the first query can't benefit from both idx1 and idx2.  You may use
> > 
> >   EXPLAIN QUERY PLAN SELECT ...
> > 
> > to see what indexes will be used.
> 
> If i understand right from previous post by Peter there will be not
> used any indexes because of "OR".

I think those slides are a bit outdated.  On the same
http://www.sqlite.org/optoverview.html page the section "3.0: The OR
optimization" says that the query from the slide 52

  SELECT * FROM ex4 WHERE x=5 OR x=7;

will be rewritten as

  SELECT * FROM ex4 WHERE x IN (5, 7);

and IN can use indexes.  But "x=5 OR y=7" (i.e. conditions on
_different_ columns) can't be rewritten that way---exactly your
situation.


On Wed, May 09, 2007 at 16:32:34 +0400, bash wrote:
> SELECT * FROM ex1 WHERE x>'abc' AND y>'abc';
> In this form only one indexes will be used, why not both?

One index per table rule.  At first glance it seems like SQLite could
use at least one index for "x=5 OR y=7" case too, but there is no
point in that, as the other part of the OR would require full table
scan anyway.

And for the solution with the UNION,

  sqlite> explain query plan
 ...>   SELECT id, n1, n2
 ...>   FROM tbl
 ...>   WHERE  n1 = $I
 ...> UNION
 ...>   SELECT id, n1, n2
 ...>   FROM tbl
 ...>   WHERE  n2 = $I
 ...> ORDER BY id DESC;
  0|0|TABLE tbl WITH INDEX idx1
  0|0|TABLE tbl WITH INDEX idx2

both indexes are used.


-- 
   Tomash Brechko

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: perfomance degradation for expr "foo = X or bar =X"

[Tomash Brechko]

On Wed, May 09, 2007 at 14:03:54 +0400, bash wrote:
> Im simplify environment:
> 
> CREATE TABLE tbl(
>   id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
>   n1 int,
>   n2 int
> );
> CREATE INDEX idx1 on tbl(n1);
> CREATE INDEX idx2 on tbl(n2);
> 
> sqlite> select count(*) from tbl;
> 63026
> 
> 1 query:
> SELECT id, n1, n2
> FROM tbl
> WHERE  n1 = $I OR n2 = $I
> ORDER BY id DESC;
> 
> 2 query:
>   SELECT id, n1, n2 
>   FROM tbl
>   WHERE  n1 = $I
> UNION
>   SELECT id, n1, n2
>   FROM tbl
>   WHERE  n2 = $I
> ORDER BY id DESC;
> 
> Timing:
> 1: 0.080 s.
> 2: 0.000 s.

>From http://www.sqlite.org/optoverview.html section 6.0:

  Each table in the FROM clause of a query can use at most one index...

So the first query can't benefit from both idx1 and idx2.  You may use

  EXPLAIN QUERY PLAN SELECT ...

to see what indexes will be used.


-- 
   Tomash Brechko

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Help wiith SQL - first row of each group

[Tomash Brechko]

On Mon, May 07, 2007 at 19:20:49 +0400, Tomash Brechko wrote:
> But with SQLite 3.x there is only one user data pointer per user
> defined aggregate function, so no parallel aggregate execution is
> possible.

Ah, I was too fast again :/.  GROUP BY is really two-pass, but my
understanding why it is so was wrong.  Then, why not to call aggregate
function the very moment we decided which bucket the row will go to?


-- 
   Tomash Brechko

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Help wiith SQL - first row of each group

[Tomash Brechko]

On Mon, May 07, 2007 at 18:10:29 +0400, Tomash Brechko wrote:
> The solution with two table scans might be
> 
>   SELECT * FROM RESULT_TABLE a
>   WHERE P = (SELECT MIN(P) FROM RESULT_TABLE b WHERE b.G = a.G);
> 
> You may also try to play with indexes G, P.
> 
> It seems to be impossible to do only one table scan with plain SQL.
> However, you may trick it:
> 
>   SELECT G, minrow(P, V), min(P) FROM RESULT_TABLE GROUP BY G;

After considering a bit more, no, it won't be faster.  I was thinking
that there are several aggregates in progress, and the row is pushed
into aggregate function of the corresponding bucket right away.
Perhaps this is how SQLite 2.8.x has worked (based on VDBE Tutorial
description).  I guess there were no user defined aggregates back
then.

But with SQLite 3.x there is only one user data pointer per user
defined aggregate function, so no parallel aggregate execution is
possible.  Thus the algorithm of GROUP BY is effectively two-scan:
first order by GROUP BY columns, then process all ordered rows with
AggStep, calling AggFinal when key changes.

So the first query with the index on G (maybe on (G, P)) is probably
near optimal.


-- 
   Tomash Brechko

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Help wiith SQL - first row of each group

[Tomash Brechko]

On Mon, May 07, 2007 at 15:38:22 +0300, Adler, Eliedaat wrote:
> I need to define a query that returns only the "first' row in each group
> - i.e. the row with the lowest display priority:
> 
> - most preferably a query that doesn't require selecting RESULT_TABLE
> more than once.

The solution with two table scans might be

  SELECT * FROM RESULT_TABLE a
  WHERE P = (SELECT MIN(P) FROM RESULT_TABLE b WHERE b.G = a.G);

You may also try to play with indexes G, P.

It seems to be impossible to do only one table scan with plain SQL.
However, you may trick it:

  SELECT G, minrow(P, V), min(P) FROM RESULT_TABLE GROUP BY G;

where minrow() is a user defined aggregate (that you have to write)
that returns its second argument that corresponds to the minimum value
of its first argument (i.e. at any moment it remembers (P, V) pair for
minimum P seen so far).

However, the plain SQL query should be fast enough :).


-- 
   Tomash Brechko

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] how to add a new column quickly

[Tomash Brechko]

On Sat, May 05, 2007 at 19:30:59 +0800, ronggui wong wrote:
> Thanks. But there is no typo, what I want is a general solution.
> 
> 2007/5/5, Tomash Brechko <[EMAIL PROTECTED]>:
> >On Sat, May 05, 2007 at 14:01:56 +0800, ronggui wong wrote:
> >> . update tablename set newcolname=1 where ROWID=1
> >> . update tablename set newcolname=2 where ROWID=2
> >> . update tablename set newcolname=2 where ROWID=3

If there is no correlation between newcolname and other columns that
can be expressed as a formula, but rather you want to set newcolname
to some known Func(ROWID), you may register this function with
sqlite3_create_function() (or its equivalent for your language
bindings), and then do a single statement

  UPDATE tablename SET newcolname = Func(ROWID);

This will be faster then repeatedly searching for a row with a given
ROWID.


-- 
   Tomash Brechko

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] how to add a new column quickly

[Tomash Brechko]

On Sat, May 05, 2007 at 14:01:56 +0800, ronggui wong wrote:
> . update tablename set newcolname=1 where ROWID=1
> . update tablename set newcolname=2 where ROWID=2
> . update tablename set newcolname=2 where ROWID=3
> .
> 
> My question is: how to add the above task efificiently? Thanks!

If there is a typo in the last line, and it should have been
'newcolname=3' (not 2), then your operation is effectively

  UPDATE tablename SET newcolname = ROWID;


-- 
   Tomash Brechko

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] May I ask why the source distribution mechanism was changed starting with 3.3.14?

[Tomash Brechko]

On Fri, May 04, 2007 at 14:04:24 +, [EMAIL PROTECTED] wrote:
> Can somebody please explain to my how 2 files is less manageable
> than 60?

To my mind, the only missing feature is CPP #line directives, like

  #line 1 "alter.c"

when contents of alter.c begins.  If they are in place, everything is
perfectly manageable.


-- 
   Tomash Brechko

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] [BUG or FR] OpenEphemeral invariant optimization

[Tomash Brechko]

Hello,

On Thu, May 03, 2007 at 14:50:14 +, [EMAIL PROTECTED] wrote:
> Your interpretation of what is happening isn't right.  Look more 
> closely at instructions 5 through 7:
> 
>   5 MemLoad 0   0  
>   6 If  0   19   
>   7 MemInt  1   0
> 
> Memory location 0 is being used as a flag that indicates whether
> or not the temporary table has been initialized.

Yep, I was wrong, thanks for the insight.  One of my queries that used
ephemeral tables took an unnaturaly long to run, and I was ready to
believe that constant repopulation is the cause.  Now I know that it's
the constance temp table re-creation that eats the time.

Still, yes, my mistake, sorry.


-- 
   Tomash Brechko

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] [BUG or FR] OpenEphemeral invariant optimization (was [sqlite] Best way to optimize this query?)

[Tomash Brechko]

 every
  OpenEphemeral in the loop (which is a costly operation: path checks,
  name generation, open() and unlink()), we may simply pop the handle,
  and do ftruncate() on it.  The stack should be freed (i.e. all
  cached handles closed) at the end of the statement (and also at
  UNION, which joins two separate statements; possibly at other places
  to prevent file handle starvation).


Dr. Hipp, what do you think?  I can't call any of that a bug, because,
as said in the docs, SQLite's primary goal is being robust and have a
manageable code base, speed comes as a consequence.  But, at least
point 3 doesn't seem to be hard to do, and for a certain queries it
will give a noticeable boost ("then submit a patch"---yeah, I know,
but I'm not that much into SQLite code :-/ (yet)).


-- 
   Tomash Brechko

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Makefile

[Tomash Brechko]

On Wed, May 02, 2007 at 11:43:04 -0700, Ken wrote:
> Is there a way to disable the -g flag for the library? 

Assuming you are using configure,

  ./configure CFLAGS='-O2'

After that 'make' will use only -O2, without -g. 


> Is there a way to tell the Make to build a 32bit version vs a 64
> bit? If not this would be really nice.

You may pass arbitrary compilation options as shown above, or you may
override the compiler itself with

  ./configure CC=/path/to/gcc32bit


> Can the Make that is provided build a libsqlite3.a and libsqlite3.so
> from the amalgamated sqlite3.c ???

No.  But the following quick-n-dirty-cut-n-paste patch will
(hopefully) do the job :)


--- Makefile.in-orig2007-05-02 19:12:21.0 +0400
+++ Makefile.in 2007-05-03 00:16:07.0 +0400
@@ -130,6 +130,9 @@ LIBOBJ = alter.lo analyze.lo attach.lo a
  vdbe.lo vdbeapi.lo vdbeaux.lo vdbefifo.lo vdbemem.lo \
  where.lo utf.lo legacy.lo vtab.lo
 
+LIBOBJ = sqlite3.lo
+
+
 # All of the source code files.
 #
 SRC = \
@@ -315,6 +318,9 @@ lemon$(BEXE):   $(TOP)/tool/lemon.c $(TOP)
 
 # Rules to build individual files
 #
+sqlite3.lo:sqlite3.c
+   $(LTCOMPILE) -c sqlite3.c
+
 alter.lo:  $(TOP)/src/alter.c $(HDR)
$(LTCOMPILE) -c $(TOP)/src/alter.c
 



-- 
   Tomash Brechko

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Amalgamation and CPP defines

[Tomash Brechko]

Hello,

This letter has three distinct questions, with Q1 being the main.


Q1:

This new amalgamation feature is really great.  A simple test program
inserting 24 millions rows, each row is an integer PK and three
integers of data, runs 40% faster for me.

But how to properly compile the amalgamation wrt C preprocessor
defines?  Suppose I got sources tarball, and did './configure
--some-options CFLAGS=...'.  This produces a Makefile, I can do 'make
sqlite3.c', but I also would like to be able to do 'make sqlite3.o',
so that compilation would actually use all the proper C preprocessor
defines and options resulted from 'configure' run.  Currently, I have
to look though generated Makefile to see what options/defines I should
use to get the same result.  It there a better way?

Also, when linking with sqlite3.o options '-pthread -ldl' to gcc are
required.  It would also be nice if, for instance, 'make
amalgamation-link-options' would print the required options.


Q2:

Why pread()/pwrite() aren't used by default?  Yes, we don't _have_ to
use them when the connection object is not shared across threads, but
lseek() before every read()/write() is a bit of overkill.
'-D_FILE_OFFSET_BITS=64 -DUSE_PREAD64=1' did the trick for me with gcc
3.4.2 and glibc 2.3.3 (surprisingly, other combinations didn't work,
and I didn't look into that further), but I'm worried a bit that
USE_PREAD/USE_PREAD64 defines don't look like a part of the user
interface (don't begin with 'SQLITE_').


Q3 (not really core SQLite-specific, but maybe someone have the answer
right away):

I wrote a C application that prepares a statement, and then, in a
loop, inserts 24M rows as described above, binding new values on each
iteration.  When I link it with libsqlite3.so.0.8.6, it runs a certain
amount of time, say, 206 seconds.  When I rewrite it in Perl using
DBI+DBD::SQLite (and I'm certain that DBD::SQlite uses the same shared
library), it, of course runs longer, the whole 885 seconds.  But
OProfile shows:

  1416755 100.000 perl
GLOBAL_POWER_E...|
  samples|  %|
--
   577749 40.7797 libperl.so
   494994 34.9386 libsqlite3.so.0.8.6
   110445  7.7956 libc-2.3.3.so
82901  5.8515 SQLite.so
81748  5.7701 DBI.so
48687  3.4365 libpthread-2.3.3.so
...

Now, 885 * 34.9% = 308 seconds, which is much bigger that 206 seconds
for C program.  The main loop in Perl program is simply

  for (my $i = 0; $i < 24_000_000; ++$i) {
  $insert->execute($i, $i, $i);
  }

where $insert is a handle of prepared statement, and the main loop of
C program is functionally the same, so I wonder, what may cause such a
big difference.

I could dig into this myself, but maybe someone has encountered the
same problem before?


Thanks!


-- 
   Tomash Brechko

-
To unsubscribe, send email to [EMAIL PROTECTED]
-