[sqlite] retrieving data from multiple tables

[Kirrthana M]

Hi all,
I have created a database with four tables,i have to search and retrieve
data from all the four tables based on the entry i get from the previous
table and display all the entries,could anybody tell how to do it.

Regards
Kirrthana

Re: [sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT

[COS]

Hi,

- Original Message - 
From: "Stef Mientki" <[EMAIL PROTECTED]>
To: 
Sent: Tuesday, March 13, 2007 3:47 PM
Subject: Re: [sqlite] Pragma table_info(), why no fields like UNIQUE,
AUTOINCREMENT


> >
> >
> > You should also consider how your change might effect
> > backwards compatibility.  The last time that table_info()
> > was modified, the Ruby-On-Rails community got really
> > upset.  I'm rather of a mind to leave table_info() alone.
> >
> Forgive my ignorance, I'm just a beginner in databases,
> but what about TABLE_INFO2 ( ) ?
> (with the explicit restriction that it can be extended in the future
> when needed,
> so Ruby users leave it alone ;-)
> I really can't imagine that extension of a function can cause serious
> compatibility issues)
>
> The alternative is now that I've to
> - build a table from table_info()
> - query sqlite_master, to get the SQL string with which the table was
> generated
> - parse the SQL string from sqlite_master
> - and add it to my edit grid form table_info
> or are there simpler ways ?
>
> -- 
> cheers,
> Stef Mientki
> http://pic.flappie.nl
>

A small opinion on that matter: what I would really like to see is something
like system tables. Today sqlite uses only sqlite_master to keep information
about its objects and parsing is required to getter better info of each
object (if one needs to). Using other system tables to keep information
about each object seems very appropriated since most RDBMS already implement
that and it is compatible with SQL ANSI. I think it shouldn't be much of a
problem since sqlite engine already parses each object when it opens a
database. This would remove the need for PRAGMA commands and would make life
much simpler.

Just a thought.

Best Regards,

Crercio O. Silva / DBTools Software
http://www.dbtools.com.br


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

Re: [sqlite] MingW32 help wanted

[Stefan de Konink]

Scott Hess wrote:

I took Makefile.linux-gcc, and made the obvious changes (there are
mingw lines all over in there).  EXE = .exe, SO =dll, SHPREFIX = ,
[that was nothing for that setting], and TCC, AR, and RANLIB set to
the path to the appropriate commands from mingw.

I should warn that I haven't actually built using mingw for maybe 9
months!  This is just going off of the Makefile that I still have
lying around.


Thanks Scott!

With a little bit of creativity I managed to get it all going! :)


Stefan


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

Re: [sqlite] MingW32 help wanted

[Scott Hess]

I took Makefile.linux-gcc, and made the obvious changes (there are
mingw lines all over in there).  EXE = .exe, SO =dll, SHPREFIX = ,
[that was nothing for that setting], and TCC, AR, and RANLIB set to
the path to the appropriate commands from mingw.

I should warn that I haven't actually built using mingw for maybe 9
months!  This is just going off of the Makefile that I still have
lying around.

-scott


On 3/13/07, Stefan de Konink <[EMAIL PROTECTED]> wrote:

Mike Frysinger wrote:
> On Tuesday 13 March 2007, Stefan de Konink wrote:
>> My target is to get sqlite compiled on my host platform i686 and my
>> target platform mingw32. Could someone get a step by step manual to get
>> the 'precious' dll/.a?
>
> well, this could be autotoolized and force people to do
> ./configure --host=mingw32
> i do that now with a library of my own ... but i cross-compile everything from
> my Linux host ;)
> -mike

Ok that doesn't work. Because:

lemon needs to run on the host system. os_unix doesn't compile because
it has undefined 'locks'. removing os_unix leads to unresolved symbols
in a lot of files when making the dll.

So a step by step tutorial would be nice :)


So what I did.

./configure --prefix=/opt/gtk
Adapted Makefile and replaced TCC and added a --tag to libtool (because
it otherwise complains).
make
everything is ok until os_unix...


Stefan

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




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

Re: [sqlite] MingW32 help wanted

[Stefan de Konink]

Mike Frysinger wrote:

On Tuesday 13 March 2007, Stefan de Konink wrote:

My target is to get sqlite compiled on my host platform i686 and my
target platform mingw32. Could someone get a step by step manual to get
the 'precious' dll/.a?


well, this could be autotoolized and force people to do
./configure --host=mingw32
i do that now with a library of my own ... but i cross-compile everything from 
my Linux host ;)

-mike


Ok that doesn't work. Because:

lemon needs to run on the host system. os_unix doesn't compile because 
it has undefined 'locks'. removing os_unix leads to unresolved symbols 
in a lot of files when making the dll.


So a step by step tutorial would be nice :)


So what I did.

./configure --prefix=/opt/gtk
Adapted Makefile and replaced TCC and added a --tag to libtool (because 
it otherwise complains).

make
everything is ok until os_unix...


Stefan

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

[sqlite] MingW32 help wanted

[Stefan de Konink]

Hello,


I have found this post:

http://marc.10east.com/?l=sqlite-users=115689163614925=2


My target is to get sqlite compiled on my host platform i686 and my 
target platform mingw32. Could someone get a step by step manual to get 
the 'precious' dll/.a?


When removing the os_unix.c from the Makefile I have everything 
compiled. But running mkdll.sh doesn't work for me :(




Stefan

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

Re: [sqlite] Degrouping, desummarizing or integrating headings

[T]

Thanks Puneet for your response.


this is a programming problem and not necessarily a SQLite problem.


Well, I can program it, but hope that instead there's a way to do it  
within SQL.


Nevertheless, you don't specify how you are getting this summary  
data... are they in a database? are they just a text file? Are they  
XML?


It comes as a CSV or tabbed text file, then into a table in my  
database. So, it's in a table in my database, eg:


CREATE TABLE Shopping_Grouped(Aisle TEXT, Product TEXT, Cost REAL)

But the Aisle entries are in records by themselves, and apply to the  
subsequent records containing Product and Cost, for example:


INSERT INTO Shopping_Grouped(Aisle) VALUES('Dairy');
INSERT INTO Shopping_Grouped(Product, Cost) VALUES('Milk', 2);
INSERT INTO Shopping_Grouped(Product, Cost) VALUES('Cream', 1);
INSERT INTO Shopping_Grouped(Product, Cost) VALUES('Cheese', 3);

INSERT INTO Shopping_Grouped(Aisle) VALUES('Bakery');
INSERT INTO Shopping_Grouped(Product, Cost) VALUES('Sliced', 4);
INSERT INTO Shopping_Grouped(Product, Cost) VALUES('Sliced', 3);
INSERT INTO Shopping_Grouped(Product, Cost) VALUES('Cake', 2);

But I want to get it into this schema:

INSERT INTO Shopping(Aisle, Product, Cost) VALUES('Dairy', 'Milk', 2);
INSERT INTO Shopping(Aisle, Product, Cost) VALUES('Dairy', 'Cream', 1);
INSERT INTO Shopping(Aisle, Product, Cost) VALUES('Dairy', 'Cheese', 3);

INSERT INTO Shopping(Aisle, Product, Cost) VALUES('Bakery', 'Sliced',  
4);
INSERT INTO Shopping(Aisle, Product, Cost) VALUES('Bakery', 'Sliced',  
3);

INSERT INTO Shopping(Aisle, Product, Cost) VALUES('Bakery', 'Cake', 2);


You could create a schema out of this normalizing aisles and products

CREATE TABLE aisles (aisle_id, aisle_name);
CREATE TABLE products (product_id, product_name, product_cost,  
aisle_id);


It's the "normalizing" that I'm asking how to do, via SQL (ie clever  
SELECT statements).



and go from there with

SELECT a.aisle_name, p.product_name, p.product_cost
FROM products p JOIN aisles a ON p.aisle_id = a.aisle_id


That's the reverse of what I need. The data is already in this final  
form and I need to "unscramble" it.


you will have to figure out how to get your source data into the  
SQLite schema you create


It's already in the schema I outlined above. But how to get it into  
the schema I want, is my question.



but that should be trivial depending on your programming environment.


But is it possible via SQL?

Thanks,
Tom

 
From: T <[EMAIL PROTECTED]>
Date: 14 March 2007 3:07:24 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Degrouping, desummarizing or integrating headings

Hi All,

I come across a fair bit of source data in summary format, where the  
one field's values are used as a heading for a group of records for  
which it applies. For instance, this shopping list:


Aisle   Product  Cost
Dairy
Milk $2
Cream$1
Cheese   $3
Bakery
Sliced   $4
Rolls$3
Cake $2

How can I select the data from that table of 8 records so that the  
result is this table of 6 records?:


Aisle   Product  Cost
Dairy   Milk $2
Dairy   Cream$1
Dairy   Cheese   $3
Bakery  Sliced   $4
Bakery  Rolls$3
Bakery  Cake $2

Thanks,
Tom


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

Re: [sqlite] FTS: index only, no text storage - Was: [sqlite] FTS: Custom Tokenizer / Stop Words

[Ralf Junker]

Hello Scott,

I was hoping that you would read my message, many thanks for your reply!

>UPDATE and DELETE need to have the previous document text, because the
>docids are embedded in the index, and there is no docid->term index
>(or, put another way, the previous document text _is_ the docid->term
>index).

This is very understandable given the present design.

>Keeping track of that information would probably double the
>size of the index.

With your estimate, the SQLite full text index (without document storage) would 
still take up only 50% of the documents' size. In my opinion, this is still a 
very good ratio, even if some specialized full text search engines apparently 
get away with less than 30%. I think you have done an enourmous job on FTS2!

I am optimistic that the proper implementation will use even less than 50%: My 
modifications are completely rudimentary and not at all optimized - the column 
to store the document text still exists. The only difference is that it is not 
used - it stores a null value which could be saved. In fact, the entire FTS 
table (the one without the suffixes) would not be needed and cut down storage 
space.

>A thing I've considered doing is to keep deletions
>as a special index to the side,

Would this open the door to "insert only, but no-modify and no-delete" indexes? 
I am sure users would like pay this cost for the benefit of even smaller FTS 
indexes!

>which would allow older data to be
>deleted during segment merges.  Unfortunately, I suspect that this
>would slow things down by introducing another bit of data which needs
>to be considered during merges.

I found that _not_ adding the original text turned out to be a great time 
saver. This makes sense if we know that the original text is about 4 times the 
size of the index. Storing lots of text by itself is already quite time 
consuming even without creating a FTS index. So I do not expect really bad slow 
downs by adding a docid->term index.

>Of course, there's no way the current system could generate snippets
>without the original text, because doclists don't record the set of
>adjacent terms.  That information could be recorded, but it's doubtful
>that doing so would be an improvement on simply storing the original
>text in the first place.  The current system _does_ have everything
>needed to generate the offsets to hits even without the original text,
>so the client application could generate snippets, though the code is
>not currently in place to expose this information.

Snippets are of course nice to have out of the box as it is right now. But even 
without storing the original text, snippets could be created by

1. supplying the text through other means (additional parameter or callback 
function), so that not FTS but the application would read it from a disk file 
or decompress it from a database field.

2. constructing token-only snippets from the document tokens and offsets. This 
would of course exclude all non-word characters, but would still return legible 
information.

>Being able to have an index without storing the original data was a
>weak goal when fts1 was being developed, but every time we visitted
>it, we found that the negatives of that approach were substantial
>enough to discourage us for a time.  [The "we" in that sentence means
>"me and the various people I run wacky ideas past."]  I'm keeping an
>eye out for interesting implementation strategies and the time to
>explore them, though.

Maybe my arguments could influence the opinion of "we"? I would love to see FTS 
without text storage, especially since I just lost a project to another FTS 
product because duplicating data was unfortunately "out of disk space".

All the best and keep up your good work,

Ralf  


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

Re: [sqlite] FTS: index only, no text storage - Was: [sqlite] FTS: Custom Tokenizer / Stop Words

[Ralf Junker]

Ion Silvestru wrote:

>Just a question: did you eliminated stop-words in your tests?

No, I did not eliminate any stop-words. The two test runs were equal except for 
the small changes in FTS 2.

My stop words question was not intended for source code but for human language 
texts.

Ralf  


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

Re: [sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT

[Stef Mientki]

You should also consider how your change might effect
backwards compatibility.  The last time that table_info()
was modified, the Ruby-On-Rails community got really
upset.  I'm rather of a mind to leave table_info() alone.
  

Forgive my ignorance, I'm just a beginner in databases,
but what about TABLE_INFO2 ( ) ?
(with the explicit restriction that it can be extended in the future 
when needed,

so Ruby users leave it alone ;-)
I really can't imagine that extension of a function can cause serious 
compatibility issues)


The alternative is now that I've to
- build a table from table_info()
- query sqlite_master, to get the SQL string with which the table was 
generated

- parse the SQL string from sqlite_master
- and add it to my edit grid form table_info
or are there simpler ways ?

--
cheers,
Stef Mientki
http://pic.flappie.nl


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

[sqlite] LEMON question

[Cesar Rodas]

I want to know if I can here public some LEMON PARSER examples that I have
done... because as i far i know LEMON is maintained  into SQLite project.

Best Regards

--
Cesar Rodas
http://www.cesarodas.com/
Mobile Phone: 595 961 974165
Phone: 595 21 645590
[EMAIL PROTECTED]
[EMAIL PROTECTED]

Re: [sqlite] date/time implementation question

[Dennis Cote]

Rafi Cohen wrote:

Hi, I hope this question is not off-topic for this list.
I'm recieveing, occasionally, a .csv file from a source which I need to
process accordcing to some criteria and either insert, delet or update
it's rows into a database. One of it's criterias is date comparison.
In other words, proceeding differently when the date/time mentioned on a
specific row is a past date or future date comparing with the current
date/time.
My problem is the format in which I recieve the date and time:
"HH:MM:SS, --mm-dd".
This means separate fileds, one for time and one for date.
If the format was: "-mm-dd HH:MM:SS", I could call strptime to stor
this in a struct tm, then cakk mktime to get this in a time_t varialbe
and then call difftime with the current date/time to make the comparison
(I'm using C as programmikng language on Linux).
So, my question is: should I unify the 2 strings into one to obtain the
second format and then proceed as I explained above, or is there a way
to compare the date with the current date separately and the time with
the current time separately in case the dates are equal?
If the conclusion will be to uify the strings into one, should I create
a table with a single date/time column or still keep the 2 fileds
separately in my sql table?
Thanks, Rafi.

  

Rafi,

It depends. :-)

It depends on what you think is more important, execution speed, 
database size, simplicity of coding, etc...


Assuming the date format you gave has a typo and there is really only 
one '-' between the year and month in your existing fields, you should 
be able to do your date comparisons in sqlite. Use concatenation to 
build a single date and time string. With your date and time in ISO 
format you can then use string comparisons as date comparisons


   select
   case when (date || ' ' || time) < ?limit_date
   then field_one
   else field_two
   end
   from my_table


HTH
Dennis Cote

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

[sqlite] date/time implementation question

[Rafi Cohen]

Hi, I hope this question is not off-topic for this list.
I'm recieveing, occasionally, a .csv file from a source which I need to
process accordcing to some criteria and either insert, delet or update
it's rows into a database. One of it's criterias is date comparison.
In other words, proceeding differently when the date/time mentioned on a
specific row is a past date or future date comparing with the current
date/time.
My problem is the format in which I recieve the date and time:
"HH:MM:SS, --mm-dd".
This means separate fileds, one for time and one for date.
If the format was: "-mm-dd HH:MM:SS", I could call strptime to stor
this in a struct tm, then cakk mktime to get this in a time_t varialbe
and then call difftime with the current date/time to make the comparison
(I'm using C as programmikng language on Linux).
So, my question is: should I unify the 2 strings into one to obtain the
second format and then proceed as I explained above, or is there a way
to compare the date with the current date separately and the time with
the current time separately in case the dates are equal?
If the conclusion will be to uify the strings into one, should I create
a table with a single date/time column or still keep the 2 fileds
separately in my sql table?
Thanks, Rafi.

Re: [sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT

[drh]

"Vivien Malerba" <[EMAIL PROTECTED]> wrote:
> On 3/13/07, Martin Jenkins <[EMAIL PROTECTED]> wrote:
> > Vivien Malerba wrote:
> > > I've already sent a proposal along with a patch some time ago about
> > > that, but nobody seemed to care, see
> > > http://www.mail-archive.com/sqlite-users@sqlite.org/msg21285.html
> >
> > Vivien, I can't see any patch attached to that post. Perhaps you should
> > resubmit it? And I'll guess the odds of any patch being accepted are
> > significantly improved if the patch both passes and extends the test suite.
> >
> 
> The patch was not archived. I guess I'll work on it to follow your
> advice and make sure it passes the test suite. I can send it to you if
> you want to have a look at it, though.
> 

You should also consider how your change might effect
backwards compatibility.  The last time that table_info()
was modified, the Ruby-On-Rails community got really
upset.  I'm rather of a mind to leave table_info() alone.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] FTS: index only, no text storage - Was: [sqlite] FTS: Custom Tokenizer / Stop Words

[Scott Hess]

On 3/13/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

Ion Silvestru <[EMAIL PROTECTED]> wrote:
> To Ralf:
> >As a side effect, the offsets() and snippet() functions stopped working,
> >as they seem to rely on the presence of the full document text in the
> >current implementation.
>
> Did you tested "phrase" searching on the index-only version, didn't this
> kind of search rely on offsets()?

Phrase searches do *not* use the full document text.
But UPDATE and DELETE do, ironically.  Or at least they
used to, unless Scott has changed that in FTS2.


Indeed, phrase searches should continue to work, because since we have
the terms from the query, we can look them up and compare their token
positions in the document (offsets being the character positions of
the tokens).

UPDATE and DELETE need to have the previous document text, because the
docids are embedded in the index, and there is no docid->term index
(or, put another way, the previous document text _is_ the docid->term
index).  Keeping track of that information would probably double the
size of the index.  A thing I've considered doing is to keep deletions
as a special index to the side, which would allow older data to be
deleted during segment merges.  Unfortunately, I suspect that this
would slow things down by introducing another bit of data which needs
to be considered during merges.

Of course, there's no way the current system could generate snippets
without the original text, because doclists don't record the set of
adjacent terms.  That information could be recorded, but it's doubtful
that doing so would be an improvement on simply storing the original
text in the first place.  The current system _does_ have everything
needed to generate the offsets to hits even without the original text,
so the client application could generate snippets, though the code is
not currently in place to expose this information.

Being able to have an index without storing the original data was a
weak goal when fts1 was being developed, but every time we visitted
it, we found that the negatives of that approach were substantial
enough to discourage us for a time.  [The "we" in that sentence means
"me and the various people I run wacky ideas past."]  I'm keeping an
eye out for interesting implementation strategies and the time to
explore them, though.

-scott

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

Re: [sqlite] Degrouping, desummarizing or integrating headings

[P Kishor]

On 3/13/07, T <[EMAIL PROTECTED]> wrote:

Hi All,

I come across a fair bit of source data in summary format, where the
one field's values are used as a heading for a group of records for
which it applies. For instance, this shopping list:

Aisle   Product  Cost
Dairy
 Milk $2
 Cream$1
 Cheese   $3
Bakery
 Sliced   $4
 Rolls$3
 Cake $2

How can I select the data from that table of 8 records so that the
result is this table of 6 records?:

Aisle   Product  Cost
Dairy   Milk $2
Dairy   Cream$1
Dairy   Cheese   $3
Bakery  Sliced   $4
Bakery  Rolls$3
Bakery  Cake $2




this is a programming problem and not necessarily a SQLite problem.
Nevertheless, you don't specify how you are getting this summary
data... are they in a database? are they just a text file? Are they
XML?

You could create a schema out of this normalizing aisles and products

CREATE TABLE aisles (aisle_id, aisle_name);
CREATE TABLE products (product_id, product_name, product_cost, aisle_id);

and go from there with

SELECT a.aisle_name, p.product_name, p.product_cost
FROM products p JOIN aisles a ON p.aisle_id = a.aisle_id

you will have to figure out how to get your source data into the
SQLite schema you create, but that should be trivial depending on your
programming environment.


--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

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

Re: [sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT

[Vivien Malerba]

On 3/13/07, Martin Jenkins <[EMAIL PROTECTED]> wrote:

Vivien Malerba wrote:
> I've already sent a proposal along with a patch some time ago about
> that, but nobody seemed to care, see
> http://www.mail-archive.com/sqlite-users@sqlite.org/msg21285.html

Vivien, I can't see any patch attached to that post. Perhaps you should
resubmit it? And I'll guess the odds of any patch being accepted are
significantly improved if the patch both passes and extends the test suite.



The patch was not archived. I guess I'll work on it to follow your
advice and make sure it passes the test suite. I can send it to you if
you want to have a look at it, though.

Cheers,

Vivien

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

[sqlite] Degrouping, desummarizing or integrating headings

[T]

Hi All,

I come across a fair bit of source data in summary format, where the  
one field's values are used as a heading for a group of records for  
which it applies. For instance, this shopping list:


Aisle   Product  Cost
Dairy
Milk $2
Cream$1
Cheese   $3
Bakery
Sliced   $4
Rolls$3
Cake $2

How can I select the data from that table of 8 records so that the  
result is this table of 6 records?:


Aisle   Product  Cost
Dairy   Milk $2
Dairy   Cream$1
Dairy   Cheese   $3
Bakery  Sliced   $4
Bakery  Rolls$3
Bakery  Cake $2

Thanks,
Tom


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

Re: [sqlite] SQLite v/s SQLite3 Performance Assay

[Nitin Kashyap]

Hi Doug,

My bad... the getTickCount() in the snippet is basically
a wrapper over ftime call, and the same is used on the
linux platform also for timing.

time_t getTickCount()
{
 timeb tm = {0};
 ftime();
 return ( (tm.time*1000) + ((time_t)tm.millitm) );
}

Thanks & Regards
Nitin K

On 3/13/07, Doug Nebeker <[EMAIL PROTECTED]> wrote:


Be aware that the Windows GetTickCount call has a resolution of 10 to 15
ms on most machines, so that could throw throw your timings off if
you're timing each individual test case as it appears below.  To get
better timer resolution, use QueryPerformanceCounter.

Now, would that make SQLite3 looks slower than v2?  I wouldn't think
so--you'd think it would skew the results equally for both tests.

Doug


This email was sent to you by Reuters, the global news and information
company.
To find out more about Reuters visit www.about.reuters.com

Any views expressed in this message are those of the individual sender,
except where the sender specifically states them to be the views of
Reuters Limited.

Reuters Limited is part of the Reuters Group of companies, of which
Reuters Group PLC is the ultimate parent company.
Reuters Group PLC - Registered office address: The Reuters Building, South
Colonnade, Canary Wharf, London E14 5EP, United Kingdom
Registered No: 3296375
Registered in England and Wales

Re: [sqlite] SQLite v/s SQLite3 Performance Assay

[drh]

"Nitin Kashyap" <[EMAIL PROTECTED]> wrote:
> 
> My Intentions are towards exploring the reason behind these differences;
> and what can be done to counter these performance differences. I'm 
> seeking some pointers from the Community.
> 

I use SQLite for a lot of different things (as you would expect)
over the course of many years.  And my experience has been that 
SQLite3 is almost always faster than SQLite2.  Sometimes remarkably 
faster.  I'm guessing that the poor performance you are getting
from SQLite3 is a test artifact of some kind.

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


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

Re: [sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT

[Dennis Cote]

Vivien Malerba wrote:



I've already sent a proposal along with a patch some time ago about
that, but nobody seemed to care, see
http://www.mail-archive.com/sqlite-users@sqlite.org/msg21285.html



Vivien,

This mailing list does not pass attachments.

A patch such as your is best handled by submitting a feature request 
through the bug report tracker at 
http://www.sqlite.org/cvstrac/captcha?cnxp=/cvstrac/tktnew and attaching 
your code and test suite. You will also have to submit a dedication as 
described at http://www.sqlite.org/copyright.html.


HTH
Dennis Cote


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

Re: [sqlite] FTS: index only, no text storage - Was: [sqlite] FTS: Custom Tokenizer / Stop Words

[drh]

Ion Silvestru <[EMAIL PROTECTED]> wrote:
> To Ralf:
> 
> >As a side effect, the offsets() and snippet() functions stopped working, as 
> >they seem to rely on the presence of the full document text in the current 
> >implementation.
> 
> Did you tested "phrase" searching on the index-only version, didn't this
> kind of search rely on offsets()?
> 

Phrase searches do *not* use the full document text.
But UPDATE and DELETE do, ironically.  Or at least they
used to, unless Scott has changed that in FTS2.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

RE: [sqlite] SQLite v/s SQLite3 Performance Assay

[Doug Nebeker]

Be aware that the Windows GetTickCount call has a resolution of 10 to 15
ms on most machines, so that could throw throw your timings off if
you're timing each individual test case as it appears below.  To get
better timer resolution, use QueryPerformanceCounter.

Now, would that make SQLite3 looks slower than v2?  I wouldn't think
so--you'd think it would skew the results equally for both tests.  

Doug

-Original Message-
From: Nitin Kashyap [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, March 13, 2007 2:51 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite v/s SQLite3 Performance Assay

> Version 3 has a different default safety-level (default FULL) to 
> version 3 (default NORMAL). So if you didn't explicitly set the 
> safety-level during the tests, then version 3 was syncing the disk 
> more often than version 2. I think this might be why version 3 appears

> slower in Test Case I (Inserts).
>
> The results of cases II to IV seem odd. Can you post the test code to 
> the list?
>
> Dan.


Hi Dan,
   You seem to be right on the Sync end; But it does not explains why
read is taking more time.  Below are the snippet u requested.

Select Query:
#define SQLQuery4 "select tbl01.code, * from tbl01, tbl02, tbl03, tbl04
"
\
 "where tbl01.code=tbl02.code01 "
\
 "and tbl01.code=tbl03.code01 "
\
 "and tbl01.code=tbl04.code01 "
\
 "order by tbl04.orderField "

#define SQLQuery2 "select tbl03.code, * from tbl03, tbl04 "
\
 "where tbl03.code = tbl04.code03 "
\
 "order by tbl04.orderField "

SQLite2 Snippet:
{
end getTickCount();

/ Start Select: 2 table */
printf("Perfroming Simple Select of 2 Table...");
fflush(stdout);
beg = end;
i=0;
snprintf(sqlQry, 1024, "%s",SQLQuery2);
if( sqlite_compile(pSource, sqlQry, NULL, , ) ==
SQLITE_OK )
{
while( sqlite_step( pVm
   ,
   ,(const char ***) 
   ,(const char ***) ) == SQLITE_ROW
)
{
 i++;
}
sqlite_finalize(pVm, );
}
else
{
printf("err in sql:  : %s\n",__LINE__, sqlQry);
if(errMsg != NULL)
printf("errMsg: %s\n", errMsg);
goto cleanUp;
}
end = getTickCount();
printf("Done\n");
printf("Time To Select 2 Table with entries returned(%d),(%d):
%ld\n", i, numColumn, (end-beg)); }

SQLite3 Snippet:
{
end = getTickCount();
/ Start Selecting: 2 table
*/
printf("Perfroming Simple Select of 2 Table");
beg = end;
i=0;
snprintf(sqlQry, 1024, "%s",SQLQuery1);
if( sqlite3_prepare(pSource, sqlQry, -1, , ) ==
SQLITE_OK )
{
while( sqlite3_step(pSqlStmt) == SQLITE_ROW )
{
i++;
}
sqlite3_finalize(pSqlStmt);
}
else
{
printf("err in sql:  : %s\n",__LINE__, sqlQry);
if(errMsg != NULL)
printf("errMsg: %s\n", errMsg);
goto cleanUp;
}
end = getTickCount();
printf("Done\n");
printf("Time To Select 2 Table with entries returned(%d): %ld\n", i,
(end-beg)); }

Thanks & Regards
Nitin K

This email was sent to you by Reuters, the global news and information company. 
To find out more about Reuters visit www.about.reuters.com

Any views expressed in this message are those of the individual sender, 
except where the sender specifically states them to be the views of Reuters 
Limited.

Reuters Limited is part of the Reuters Group of companies, of which Reuters 
Group PLC is the ultimate parent company.
Reuters Group PLC - Registered office address: The Reuters Building, South 
Colonnade, Canary Wharf, London E14 5EP, United Kingdom
Registered No: 3296375
Registered in England and Wales



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

Re: [sqlite] FTS: index only, no text storage - Was: [sqlite] FTS: Custom Tokenizer / Stop Words

[Ion Silvestru]

To Ralf:

>As a side effect, the offsets() and snippet() functions stopped working, as 
>they seem to rely on the presence of the full document text in the current 
>implementation.

Did you tested "phrase" searching on the index-only version, didn't this
kind of search rely on offsets()?


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

Re[2]: [sqlite] FTS: index only, no text storage - Was: [sqlite] FTS: Custom Tokenizer / Stop Words

[Ion Silvestru]

>Just a question: did you eliminated stop-words in your tests?

Sorry, you specified that you indexed source code files, so no
stop-words are applicable here.


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

Re: [sqlite] FTS: index only, no text storage - Was: [sqlite] FTS: Custom Tokenizer / Stop Words

[Ion Silvestru]

Thank you.

Just a question: did you eliminated stop-words in your tests?

>Concluding: Given the great database size savings possible by separating full 
>text index from data storage, I wish that
>developers would consider adding such an option to the SQLite FTS interface.

If such an option will be added, I see a big future for using SQLite
as a simple, but powerful and easily customized (user tokenizers etc)
full-text search engine, and not only as a DB engine.

Currently we don't have many options for full-text desktop engine,
there are some, like DTSearch, Onix, Lucene, but these are
over-priced, can't be easily customized or too complex.



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

[sqlite] FTS: index only, no text storage - Was: [sqlite] FTS: Custom Tokenizer / Stop Words

[Ralf Junker]

>But what about:
>
>I am very interested to know if it would be possible to use an FTS indexing 
>module to store the inverted index only, but
>not the document's text. This would safe disk space if the text to index is 
>stored on disk rather than inside the database.

This is possible with just minor modifications to fts2.c (below). I commented 
out the instructions responsible for inserting and updating the text body into 
the %_content table. As a side effect, the offsets() and snippet() functions 
stopped working, as they seem to rely on the presence of the full document text 
in the current implementation. Neverthelses, I ran FTS2 over a collection of 
source code files, and the results are astonishing:

With the original fts2.c, the database figures are as follows: 

Number of documents:10739 Files
Total size of document text stored:   234 MB
Total size of database:  ===> 295 MB <=== 
Size of index within database: 61 MB
Index / Text ratio:26 Percent

With the modified fts2.c (no text stored), the database size was obviously much 
smaller:

Number of documents:10739 Files
Total size of document text stored:   234 MB
Total size of database:   ===> 61 MB <=== 
Index / Text ratio:26 Percent

I addition to the database size savings, I can think of a number of other 
benefits in separating text and reverted index storage:

1. Indexing docuements stored in another database would not need to duplicate 
storage. A small "FTS database" could be attached to the "Data database" if 
necessary, so the "data" database stays smaller without the index.  Deleting 
the "FTS database" would leave the the data untouched.

2. Point 1 from above would allow to distribute CDs without FTS and let the 
user create a small FTS index on local storage to speed up searching. This way 
more data can be shipped on single CD volumes.

3. Indexing compressed text would become possible. The current implementation 
does not allow text compression because the FTS tables always store 
uncompressed.

4. Ease maintainance and consistency of data as long as FTS is experimental. If 
data and FTS are separated, only the FTS index must be rebuild if FTS changes, 
while the current implementation potentially requires to upgrade entire tables 
to yet unknown formats.

5. FTS could be removed from a database without touching the data: Only the FTS 
tables would have to be deleted.

Concluding: Given the great database size savings possible by separating full 
text index from data storage, I wish that developers would consider adding such 
an option to the SQLite FTS interface.

Finally, here are the changes I applied to fts2.c as proof of concept:

/* insert into %_content (rowid, ...) values ([rowid], [pValues]) */
static int content_insert(fulltext_vtab *v, sqlite3_value *rowid,
  sqlite3_value **pValues){
  sqlite3_stmt *s;
  int i;
  int rc = sql_get_statement(v, CONTENT_INSERT_STMT, );
  if( rc!=SQLITE_OK ) return rc;

  rc = sqlite3_bind_value(s, 1, rowid);
  if( rc!=SQLITE_OK ) return rc;

/*  for(i=0; inColumn; ++i){
rc = sqlite3_bind_value(s, 2+i, pValues[i]);
if( rc!=SQLITE_OK ) return rc;
  } */

  return sql_single_step_statement(v, CONTENT_INSERT_STMT, );
}

/* update %_content set col0 = pValues[0], col1 = pValues[1], ...
 *  where rowid = [iRowid] */
static int content_update(fulltext_vtab *v, sqlite3_value **pValues,
  sqlite_int64 iRowid){
  sqlite3_stmt *s;
  int i;
  int rc = sql_get_statement(v, CONTENT_UPDATE_STMT, );
  if( rc!=SQLITE_OK ) return rc;

/*  for(i=0; inColumn; ++i){
rc = sqlite3_bind_value(s, 1+i, pValues[i]);
if( rc!=SQLITE_OK ) return rc;
  } */

  rc = sqlite3_bind_int64(s, 1+v->nColumn, iRowid);
  if( rc!=SQLITE_OK ) return rc;

  return sql_single_step_statement(v, CONTENT_UPDATE_STMT, );
}

Ralf 


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

Re: [sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT

[Martin Jenkins]

Vivien Malerba wrote:

I've already sent a proposal along with a patch some time ago about
that, but nobody seemed to care, see
http://www.mail-archive.com/sqlite-users@sqlite.org/msg21285.html


Vivien, I can't see any patch attached to that post. Perhaps you should 
resubmit it? And I'll guess the odds of any patch being accepted are 
significantly improved if the patch both passes and extends the test suite.


Martin



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

Re: [sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT

[Vivien Malerba]

On 3/12/07, Stef Mientki <[EMAIL PROTECTED]> wrote:

If ask the table sturcture, with pragma table_info()

I get of course the basic fields, like:
   CID,Name,Type,

And also SOME special values, like
  Null, DefaultValue, PrimaryKey

But NOT the following special values (and probably a lot more)
  Unique, AutoIncrement



I've already sent a proposal along with a patch some time ago about
that, but nobody seemed to care, see
http://www.mail-archive.com/sqlite-users@sqlite.org/msg21285.html

Cheers,

Vivien

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

Re: [sqlite] SQLite v/s SQLite3 Performance Assay

[Nitin Kashyap]

Version 3 has a different default safety-level (default FULL) to
version 3 (default NORMAL). So if you didn't explicitly set the
safety-level during the tests, then version 3 was syncing the
disk more often than version 2. I think this might be why version 3
appears slower in Test Case I (Inserts).

The results of cases II to IV seem odd. Can you post the test
code to the list?

Dan.



Hi Dan,
  You seem to be right on the Sync end; But it does not explains why
read is taking more time.  Below are the snippet u requested.

Select Query:
#define SQLQuery4 "select tbl01.code, * from tbl01, tbl02, tbl03, tbl04 "
\
"where tbl01.code=tbl02.code01 "
\
"and tbl01.code=tbl03.code01 "
\
"and tbl01.code=tbl04.code01 "
\
"order by tbl04.orderField "

#define SQLQuery2 "select tbl03.code, * from tbl03, tbl04 "
\
"where tbl03.code = tbl04.code03 "
\
"order by tbl04.orderField "

SQLite2 Snippet:
{
   end getTickCount();

   / Start Select: 2 table */
   printf("Perfroming Simple Select of 2 Table...");
   fflush(stdout);
   beg = end;
   i=0;
   snprintf(sqlQry, 1024, "%s",SQLQuery2);
   if( sqlite_compile(pSource, sqlQry, NULL, , ) == SQLITE_OK )
   {
   while( sqlite_step( pVm
  ,
  ,(const char ***) 
  ,(const char ***) ) == SQLITE_ROW )
   {
i++;
   }
   sqlite_finalize(pVm, );
   }
   else
   {
   printf("err in sql:  : %s\n",__LINE__, sqlQry);
   if(errMsg != NULL)
   printf("errMsg: %s\n", errMsg);
   goto cleanUp;
   }
   end = getTickCount();
   printf("Done\n");
   printf("Time To Select 2 Table with entries returned(%d),(%d): %ld\n",
i, numColumn, (end-beg));
}

SQLite3 Snippet:
{
   end = getTickCount();
   / Start Selecting: 2 table */
   printf("Perfroming Simple Select of 2 Table");
   beg = end;
   i=0;
   snprintf(sqlQry, 1024, "%s",SQLQuery1);
   if( sqlite3_prepare(pSource, sqlQry, -1, , ) ==
SQLITE_OK )
   {
   while( sqlite3_step(pSqlStmt) == SQLITE_ROW )
   {
   i++;
   }
   sqlite3_finalize(pSqlStmt);
   }
   else
   {
   printf("err in sql:  : %s\n",__LINE__, sqlQry);
   if(errMsg != NULL)
   printf("errMsg: %s\n", errMsg);
   goto cleanUp;
   }
   end = getTickCount();
   printf("Done\n");
   printf("Time To Select 2 Table with entries returned(%d): %ld\n", i,
(end-beg));
}

Thanks & Regards
Nitin K