Re: [sqlite] New word to replace "serverless"

[Mohit Sindhwani]

On 2020-1-28 7:11 am, John McMahon wrote:
Define what "serverless" means to you in the SQLite context and 
provide a link or pop-up to that definition wherever "serverless" 
occurs in the documentation. Perhaps also include what it doesn't mean 
if you think this is becoming an issue.


How others choose to define "serverless" should not be your problem.

Just my pennies worth,
John


...and my penny would be an English penny in stating it as "server-less" 
rather than serverless.


Best Regards,
Mohit.
2020-2-2 | 10:59 pm.

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

Re: [sqlite] "Responsive" website revamp at www.sqlite.org

[Mohit Sindhwani]

Hi Richard,


On 6/9/2016 8:26 PM, Richard Hipp wrote:

How do I tell?

The website saw 2748 distinct UserAgent strings within just the past
24 hours (a holiday in the USA, FWIW).  How do I tell which of those
are bots, mobile devices, and/or desktops?


I forget how the SQLite3 website is served, but I would probably slip in 
the very simple Google Analytics Javascript so that in a few days, you 
could just log into the GA website and get a very quick idea of the 
different parameters.


Best Regards,
Mohit.


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

Re: [sqlite] "Responsive" website revamp at www.sqlite.org

[Mohit Sindhwani]

Hi Stephan,

On 6/9/2016 4:35 PM, Stephan Beal wrote:

On Tue, Sep 6, 2016 at 10:30 AM, Eric Grange  wrote:


A counter-opinion, though apparently in the small minority: i _absolutely
despise_ fixed-width web site layouts.

Just to clarify, this is not fixed width, but limited max width, ie. it
only kicks in when the browser window is very large. The site is fluid
(like now) at smaller widths.


And i mis-typed :/. i _despise_ both fixed widths and designer-specified
maximum widths ;). No designer on this planet knows what my screen
resolution/viewing preferences are and has no business assuming they do.
It's likely that assumption about my preferences/limits which annoys me
more than the limitation itself does.


This article makes some sweeping generalizations, but fundamentally, 
reading speed is greatly affected by how much content is in your field 
of vision:

http://www.selfgrowth.com/articles/tips_for_reading_faster_from_the_computer_screen

That said, it's probably a pretty simple piece of Javascript to allow 
font size changes and text reflow/ max width on a web page.


Best Regards,
Mohit.


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

[sqlite] website documentation wording

[Mohit Sindhwani]

On 5/12/2015 2:08 AM, Keith Medcalf wrote:
> Well, a Gartner Report paid for by Microsoft, which said that if you 
> pronounced it "ess queue ell" you were labelling yourself as a professional 
> programmer who understood relational database technologies, had probably used 
> them since the 1970's or before, and belonged in a dinosaur pen.
>
> On the other hand, the modern "buzzword compliant" pronunciation for "mere 
> coders" that had no knowledge of how relational database systems worked and 
> could not distinguish Boyce-Codd Normal Form from an Eggplant was "sequel".

Well, at least as far as SQLite goes, I think this settles it 
unambiguously for me:
https://www.youtube.com/watch?v=giAMt8Tj-84

Best Regards,
Mohit.

[sqlite] attempt at output with thousands separator via extension

[Mohit Sindhwani]

On 23/11/2015 11:32 AM, Rowan Worth wrote:
> Hi Bruce,
>
> I had a go at post-processing the sqlite3 shell's output to apply thousand
> separators. I don't recommend looking too hard at the sed for the sake of
> your sanity, but the gist is it repeatedly prepends a comma to trailing
> groups of three digits, and then repeatedly removes commas which appear
> after a decimal point[1].

  I thought that it should be "easy enough" to add a custom function 
that outputs the formatted view for numbers... so, instead of
 > select int_val
you could do:
 > select to_thousands_formatted(int_val)
with an optional parameter that says how you want it separated "," being 
the default.

It would be a bit like using upper(X) with a syntax that uses parameters 
like group_concat() does.  Would that not work?  In that case, this 
pretty_printer coule be code only within the sqlite3 shell (or as an 
extension, it could be in anything).

Best Regards,
Mohit.

[sqlite] Tables and Columns of Database of Whatsapp

[Mohit Sindhwani]

On 30/6/2015 11:11 PM, Stephen Chrzanowski wrote:
> There are a few posts in this mailing list that have people putting up
> public domain database structures, with data, for different things to
> track.  Google search also has come up with a few out there as well, but,
> you really got to nail down the query as just searching for "Public domain
> database" puts SQLite itself up at the top of the list.
>
> If you want to work ground up, I'd recommend coming up with one of the
> following
> - Address/Contact book (Already mentioned)
> - Game tracking database
> - Twitter type of  database
> - Notes database (Say, heading, sub-headings, content, footers stored in a
> different table, etc)
> - Library database - Keep tabs on owned books, ISBN numbers (Plenty of free
> ISBN lookup services), who's borrowed what
> - Authentication system
>


Or something like the database for the open source Radiant CMS - the 
application is written in Rails but has a nice, just-complex-enough 
schema that spans hierarchy, pages, page parts, authors, 
authentications, authorizations, etc.

Best Regards,
Mohit.

[sqlite] full table scan ignores PK sort order?

[Mohit Sindhwani]

On 3/3/2015 6:59 PM, Jean-Christophe Deschamps wrote:
> At 11:27 03/03/2015, you wrote:
> 
>> - the full table scan returns rows in rowID order, which is the order 
>> in which the rows were added to the table
> `---
>
> No and no.
>
> An SQL engine doesn't guarantee any row "order" unless you explicitely 
> force an ORDER BY clause. Think of row order as random, where rowid 
> order is just a possibility among zillions others. Of course neither 
> SQLite nor other engines willingly use random() to foil your 
> expectations but you should never rely on such an implementation detail.
>
> Also rowids are technically independant of insertion order: you may 
> feed any valid random literal rowids at insert time.
>

If it wasn't a performance issue, I wish that SQLite would sometimes 
actually return values that are not in the order of insertion or by 
rowid just so that people would learn this lesson earlier :)

For sure, it took me a while... actually, till I read a book about SQlite.

Cheers,
Mohit.

Re: [sqlite] Looking for SQLite schema doc generator (in HTML) for tables, fields, etc

[Mohit Sindhwani]

On 4/2/2015 12:20 AM, Gerald Bauer wrote:

Hello,
 I have started two hours ago ;-) to put together a little script.
The idea is to "dump" the schema as json and then use a static site
generator w/ html templates to generate the docu.

So far the script that generates the json dump (is twenty lines of
Ruby). You can see an example, for the football.db online [1]. Cheers.

[1] https://github.com/book-templates/schema/blob/master/_data/schema.json

PS: The static site generator (e.g. Jekyll) is actually built into
GitHub - so the idea is you upload the schema in json and the docu
auto-builds itself (incl. free hosting thanks to GitHub Pages).


I had something that I would use to draw a few pictures of the table 
schema... since you're using Ruby, I'm tempted to revisit that...


Best Regards,
Mohit.


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

Re: [sqlite] Partial index to find maximum

[Mohit Sindhwani]

On 29/12/2014 4:33 PM, Baruch Burstein wrote:

Hi,

I have a table with a 2 column PK, say 'a' and 'b'. I need to find, for a
given value of 'a', the highest matching 'b'. The query itself it simple:

 SELECT max(b) FROM t WHERE a=:whatever

To speed this up, I would add an index on 'a'. Now, the question is is
there some way to tell the index that I am only interested in the maximum
value of b? For example, for the following table:

a|b
1|1
1|2
2|2
2|3

I only need the index to contain the rows (1,2) and (2,3). The docs for
partial indexes say that they can't contain functions (like max()). Any
suggestions?



I don't know if you can do a partial index for this, but if this is a 
use case that is slow for you and is very critical, I would use a 
trigger to update a separate table that stores just the value of "a" and 
its corresponding maximum.  It's quite easy to manage this for the 
create and insert cases.  You may have to run that whole query in the 
trigger again if there a deletion done.  So, in a way this table is the 
cache you want.


Not sure if that helps your case.

Best Regards,
Mohit.





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

Re: [sqlite] Without ROWID and Autoincrement question

[Mohit Sindhwani]

On 27/12/2014 12:13 AM, Teg wrote:

Because AUTOINCREMENT keyword changes the behavior of the ROWID
selection algorithm, AUTOINCREMENT is not allowed on WITHOUT ROWID
tables or on any table column other than INTEGER PRIMARY KEY. Any
attempt to use AUTOINCREMENT on a WITHOUT ROWID table or on a column
other than the INTEGER PRIMARY KEY column results in an error.--

I think this comment would be clearer if the "without rowid" part was
pulled out and made absolute.



The AUTOINCREMENT keyword is not allowed on WITHOUT ROWID tables.
Because the AUTOINCREMENT keyword changes the behavior of the ROWID
selection algorithm, Any attempt to use AUTOINCREMENT on a column
other than the INTEGER PRIMARY KEY column results in an error.--



I think this misses the point that if you try to apply it on a WITHOUT 
ROWID table, you will get an error.  Here's my attempt at reqriting 
thetext...


The AUTOINCREMENT keyword changes the behavior of the ROWID selection 
algorithm and is only allowed on the INTEGER PRIMARY KEY column and   
Consequently, any attempt to use AUTOINCREMENT on WITHOUT ROWID tables 
or on a column other than the INTEGER PRIMARY KEY column results in an 
error.


Best Regards,
Mohit.


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

Re: [sqlite] CRUD Statistics

[Mohit Sindhwani]

Hi Sandu,

On 8/7/2014 2:46 PM, Sandu Buraga wrote:

Is there a possibility to extract a statistic of how many CRUD operations
do I have during an application life-cycle? I would like to find-out how
many INSERTs do I have, how many SELECTs and so on.


My guess is that the best way would be by using triggers to update a 
table from which you can read the counts.


Best Regards,
Mohit.


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

Re: [sqlite] R-Tree Storage Optimization for Points

[Mohit Sindhwani]

On 20/6/2014 8:08 PM, Clemens Ladisch wrote:

Not without changing the SQLite code.

A non-leaf R-tree node must store the extents covered by all its
children, so these are (n-dimensional) rectangles.  At the moment,
SQLite assumes that user data has exactly the same format, so such
a change would not be trivial.



Thanks Clemens - I was afraid that might be the case.  I guess that's a 
project for a different time and day.


Best Regards,
Mohit.


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

Re: [sqlite] R-Tree Storage Optimization for Points

[Mohit Sindhwani]

Hello All...

On 20/6/2014 3:01 AM, Wolfgang Enzinger wrote:


I stand corrected. Should have tried this before:

sqlite> INSERT INTO abc VALUES(2,30,20);
Error: constraint failed

Note to self: r-tree is about *ranges* in 1 to 5 dimensions.



Coming back to the original problem again... I was wondering if there is 
a way that we could save space on the R-Tree storage if the item being 
inserting is just a single point (such that x1=x2 and y1=y2).


Thanks for the answers thus far.

Best Regards,
Mohit.

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

Re: [sqlite] R-Tree Storage Optimization for Points

[Mohit Sindhwani]

Hi Wolfgang,

On 19/6/2014 11:54 PM, Wolfgang Enzinger wrote:

Not sure why you think you have to store those point coordinates twice.

This works:

sqlite> CREATE VIRTUAL TABLE abc USING rtree(id,x,y);
sqlite> INSERT INTO abc VALUES(1,20,30);
sqlite> SELECT id FROM abc WHERE x>=10 AND x<=30 AND y >=20 AND y<=40;
1
sqlite> SELECT id FROM abc WHERE x>=40 AND x<=50 AND y >=40 AND y<=50;
sqlite>


I do feel a bit stupid after reading your email... but I guess I was 
working on the basis that the data we have is 2 dimensional and my 
recollection was that we need 2 items per dimension.


Am I reading this wrong?
The SQLite R*Tree module is implemented as a virtual table. Each R*Tree 
index is a virtual table with an odd number of columns between 3 and 11. 
The first column is always a 64-bit signed integer primary key. The 
other columns are pairs, one pair per dimension, containing the minimum 
and maximum values for that dimension, respectively. A 1-dimensional 
R*Tree thus has 3 columns. A 2-dimensional R*Tree has 5 columns. A 
3-dimensional R*Tree has 7 columns. A 4-dimensional R*Tree has 9 
columns. And a 5-dimensional R*Tree has 11 columns. The SQLite R*Tree 
implementation does not support R*Trees wider than 5 dimensions.


Best Regards,
Mohit.



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

Re: [sqlite] R-Tree Storage Optimization for Points

[Mohit Sindhwani]

Hi Noël,

Thanks for our reply.

On 19/6/2014 2:19 PM, Noel Frankinet wrote:

Hi Mohit,

Maybe you should use the spatialite extension ?

Noël


I have to see if indeed spatialite handles the data more efficiently 
since it also relies on the R-Tree for quite a bit of stuff.  That said, 
I do remember that once upon a time (admittedly 3 - 4 years ago), we had 
trouble getting Spatialite compiled for a Windows CE target.  Maybe, it 
is time to revisit that again.



Best Regards,
Mohit.




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

[sqlite] R-Tree Storage Optimization for Points

[Mohit Sindhwani]

Hello!  We are using SQLite3 for storing geographical points that can be 
queried using a bounding box (find everything that lies within this 
box).  Obviously, this query fits the capabilities of the RTree module 
very well and it is a simple 2 dimensional search using an R-Tree that 
has 5 columns.


However, since these are points that are stored in the table, x1=x2 and 
y1=y2 when we do the insertion.  As a former embedded systems engineer, 
this feels like a waste since I can see that we are inserting exactly 
the same value into the table.


INSERT into data_rtree(1000, 10, 5, 10, 5);
INSERT into data_rtree(1000, 17, 1, 17, 1);
and so on.

Is there a way that we could optimize the module so that we don't need 
to store the same value twice?  We are using this on a system with 
constrained resources, so it helps to reduce the amount of storage space 
we need for our database.


Thanks,
Mohit.

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

Re: [sqlite] Best compression for sqlite3 ?

[Mohit Sindhwani]

On 13/5/2014 2:45 PM, Simon Slavin wrote:


There are two versions of your question: one for compression of a database 
which is only going to be read from now on and another for compression of a 
database which has to support writing.

hwaci, Richard Hipp's own company, support both ZIPVFS and CEROD:

http://www.hwaci.com/sw/sqlite/zipvfs.html
http://www.hwaci.com/sw/sqlite/cerod.html

which do both things.  Since the guy who runs that company is also the main man 
behind SQLite itself, there's a good chance that support will continue.

When comparing database sizes and compression factors you should bear in mind 
that different DBMSs access data at different speeds and require more or fewer 
indexes to do it at the same speeds.  In other words, publishing a simpler '40% 
of file size' doesn't tell the whole story.


While Simon is absolutely correct, I can confirm that in production, our 
databases using CEROD (read only) are typically around 40% ~ 45% of the 
size of the original database.  However, we have also seen cases, where 
we reduce the size of the original database by 200MB and the resulting 
compressed database size reduces only by 20MB since the original data 
had a lot of data that was very easy to compress.


As always, your mileage may vary - but this is what we have observed 
with CEROD.


Best Regards,
Mohit.

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

Re: [sqlite] Database access slowness on specific WinCE devices

[Mohit Sindhwani]

On 15/4/2014 3:36 PM, Joe Mistachkin wrote:


It might be useful to measure the processor and storage performance of
the device, using some external benchmarking tool, and then compare it
against the other devices you have.


If the disk performance is indeed slower on this particular device, 
maybe, another question to ask is:
Is there a way to make SQLite3 less disk-bound?  What are the sorts of 
things that could help?


1. I guess the obvious one would be to increase the cache size.
2. I think the query pattern would determine if increasing the page size 
is a good idea... a larger page size will certainly hold more records, 
but if your reading pattern jumps all around the database, then reading 
in an 8KB page for every thing instead of a 4KB page would likely slow 
down things as well.


Any other thoughts?

Best Regards,
Mohit.


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

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-lineshell

[Mohit Sindhwani]

On 11/2/2014 1:57 AM, Richard Hipp wrote:

On Mon, Feb 10, 2014 at 12:51 PM,  wrote:


I second the idea of a kind of "WARNING: All your work will be lost, are
you sure you want to quit? (y/N)" on trying to exit, but *ONLY* if the
application was started by (double-)clicking on it, otherwise the warning
will be a nuisance when running test scripts.


I think I know how to detect a double-click launch versus a command-line
launch on windows.  But I don't know how to do this, or even if it is
possible to do, on Mac or Linux.  Anybody have any ideas?


I genuinely feel that we should give a warning at start up and leave it 
at that... everything else is likely to rely on numerous assumptions.  
For example, a slightly more eager user may go to the command line and 
run sqlite3.exe but not realize that his database is not saved.  I think 
we should keep it simple.  Most users learn very quickly once their data 
has gone away!


Best Regards,
Mohit.


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

Re: [sqlite] Happy New Year

[Mohit Sindhwani]

On 1/1/2014 8:46 AM, Igor Korot wrote:

Hi, ALL,
I want to wish everybody who is reading and involved with the list
Happy and oyful New Year!
Let's have a great time in it and lets make a lot of good products and
new releases with the software that everybody involve with.

Thank you.


Indeed!  Happy New Year to all... thanks for all the enlightening 
discussions and support!


Cheers,
Mohit.

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

Re: [sqlite] Does SQLIte run under Windows CE?

[Mohit Sindhwani]

On 30/11/2013 1:28 AM, J Trahair wrote:
Does SQLIte run under Windows CE? And if so, which version should I 
download?


Thank you.

J Trahair


Yes, we are using various versions of 3.7.xx in Windows CE 5.0 and 6.0 
without any problems.


Best Regards,
Mohit.
1/12/2013 | 12:59 AM.

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

Re: [sqlite] Bug in sqlite.exe?

[Mohit Sindhwani]

On 1/12/2013 12:55 AM, Eric Teutsch wrote:

.Tables doesn't show the 2 new tables.  A select statement on one of those
tables says "No such table".  And "select * from sqlite_master" shows the 8
tables and 1 trigger.  And ends there.  But when using sqliteodbc, I can run
the select statement on a new table.


Do any table names have spaces?

Best Regards,
Mohit.

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

Re: [sqlite] Eficiency : 1 table vs several tables

[Mohit Sindhwani]

Hi Carlos,

On 30/11/2013 4:27 AM, Carlos Ferreira wrote:

My tables have all the same number of columns ( 1 column only.. of BLOBs.)
and Simon Slavin suggested I could use only one big table where I add an
extra column to identify the table name.


I would consider searching by an integer ID rather than a string if that 
is possible.



This seems quite a nice approach, because it does seems to be more memory
efficient in terms of disk usage.

However my question is the following:

Let's assume that TABLE now designates my real tables that can be either
SQLITE tables or sub groups of records inside one big real SQLite table

I have to load to memory and save to DB groups of these TABLE at the same
time ( by saving I refer to update or save the blobs inside each table ).


From the sound of it, you don't (and can't) delete a single row from 
the table.  Is that intentional?



What if  faster?

Accessing a table in SQLite and updating deleting or adding new records
Or

Querying the records of one table in such a way that the select records have
a field = Table Name..and then adding and updating these records.


My gut feeling is that accessing a smaller table is likely to be faster 
but a lot depends on the number of records.  I don't think you would see 
much difference for a few thousand or few tens of thousands records.


That said, since you know the exact query that you want to perform, you 
may want to look into partial indexes as a way to speed up these queries 
by avoiding a full table scan.

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

Best Regards,
Mohit.



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

Re: [sqlite] Big number of tables

[Mohit Sindhwani]

Hi Carlos,

On 29/11/2013 9:33 PM, Carlos Ferreira wrote:

Any of you know how to speed up the creation of empty tables in SQlite?

If I have to create more than 1000 empty tables to initialize my application
document it takes a while..

Is there any workaround?


Workaround:
1. I would probably create the database with all the empty tables once 
and store it as a file.  Whenever a new one is needed, I'd make a copy 
of it and use that.  The copy may be a file or a blob in memory within 
your program or a blob in a database that you're using for your program, 
etc.
This is assuming that the SQLite3 table creation is indeed slow.  I have 
never tried with 1000 empty tables, so I'm not sure that it is.
2. If it is an option, do a lazy creation of tables - create only the 
tables that are needed when they are needed.  Frankly, if you often read 
and write from the database, this is a bit painful since you have to add 
quite a few more checks


As always, some of the other things that can give you a bit more speed:
* Transactions
* The correct kind of journal mode (including moving it to memory)
etc.

Best Regards,
Mohit.


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

Re: [sqlite] Out of memory error

[Mohit Sindhwani]

Hi Simon,

Thanks for the reply.

On 10/9/2013 1:37 AM, Simon Slavin wrote:

I do not think this will solve your problem in one go, but execute the SQL 
command

ANALYZE

then try it again.  If it doesn't help, please post the new query plan.


The query plan hasn't changed:
0|0|0|SCAN TABLE itemdata AS TI (~1749597 rows)
0|1|1|SEARCH TABLE weekdays AS WD USING INTEGER PRIMARY KEY (rowid=?) 
(~1 rows)

0|0|0|USE TEMP B-TREE FOR GROUP BY

It was:
0|0|0|SCAN TABLE itemdata AS TI (~100 rows)
0|1|1|SEARCH TABLE weekdays AS WD USING INTEGER PRIMARY KEY (rowid=?) 
(~1 rows)

0|0|0|USE TEMP B-TREE FOR GROUP BY

I know I can do what I want to do in a programming language but since I 
have the correct SQL in hand, I am trying to see if it can be done 
within SQLite3 directly.


Here's an update.  I tried the query with sqlite3 shell "3.8.0.2 
2013-09-03 17:11:13" and "3.7.15.2 2013-01-09 11:53:05" and both of them 
work as expected.  For now, I will proceed to use this since 3.7.4 is a 
bit dated already.  I'm wondering now if somehow I have been using a 
custom build of SQLite shell...


Thanks for the help guys..

Best Regards,
Mohit.

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

Re: [sqlite] Out of memory error

[Mohit Sindhwani]

Hi Richard

On 9/9/2013 11:10 PM, Richard Hipp wrote:


SQLite does not normally *require* a lot of memory.  (But it generally runs
faster the more memory you give it, so the default configuration is to use
as much as it wants.)


That is what I have always observed.


I'm guessing you have set "PRAGMA temp_store=MEMORY" which causes certain
temporary tables to be kept in memory rather than on disk.

I get this:
sqlite> pragma temp_store;
0

Does 0 equate to MEMORY?  Maybe it does because PRAGMA temp_store=FILE 
seems to set it to 1.



In the case of
your GROUP BY query, if there is no index on the GROUP BY terms, then
SQLite has to do a sort, and that sort will occur in memory and require
sufficient memory to hold the entire 1.5-million-row table.
The GROUP BY terms come from different tables, TI and WD.  The join 
field is day.

  WHERE TI.day=WD.day
  GROUP BY itemid, WD.wday

I just added indexes as best I saw:
* an index on TI(itemid) -- this was already there
* an index on TI(itemid, day) -- I just added this
* an index on WD(day, wday) -- I just added this
* an index on WD(wday) -- I just added this
[WD(day) is actually the primary key on that table]

It still doesn't solve it - still out of memory.

This is the query plan:
0|0|0|SCAN TABLE itemdata AS TI (~100 rows)
0|1|1|SEARCH TABLE weekdays AS WD USING INTEGER PRIMARY KEY (rowid=?) 
(~1 rows)

0|0|0|USE TEMP B-TREE FOR GROUP BY

(I can see the TEMP B-TREE but just don't know what to do about it)


Can you try
setting "PRAGMA temp_store=FILE" and see if that doesn't solve your OOM
problem?


Unfortunately, the temp_store=FILE hasn't yet solved the problem!  I 
still get the same error.


Thanks... I have already learned something new today (temp_store).

Best Regards,
Mohit.


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

[sqlite] Out of memory error

[Mohit Sindhwani]

Hi Guys,

I have hit a new problem and would like some advice if there's a way 
around it.


Error: near line 2: out of memory

Basically, I have a table with 291 columns, 1.5 million rows.  288 
columns have numeric values, additionally there is an itenid and day.
I want to use SQL to basically do count(Vn), avg(Vn), sum(Vn) and then 
group by itenid and daytype (daytype comes from a mapping of day--> 
daytype from a different table).


Statistics:
* Table has 291 columns
* There are about 50,000 unique itemid and the day runs from 1 - 31.
* The dump with count, avg, sum would have 288 x 3 + a couple of other 
columns

* day --> daytype mapping results in 3 different daytypes

So, we are doing averages, counts, sum on 288 columns and grouping the 
data from 5 - 20 rows each to generate 1 statistics row (itemid, 
daytype).  These would eventually result into 50,000 x 3 results. 
Currently,  I am trying to do this a limit 1 and it's already giving me 
the out of memory error.


This is based on using sqlite3.exe downloaded from the sqlite3 shell 
version 3.7.4.


So, the questions are:
* Am I doing something wrong?
* What can I change?
* Is there a setting that I could change?
* Am I just asking SQLite for a calculation that naturally needs a lot 
of memory and I just need to think of restructuring it in some way.


Best Regards,
Mohit.

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

Re: [sqlite] to encrypt sqlite db

[Mohit Sindhwani]

On 31/8/2013 9:52 PM, dd wrote:

Thank you for your quick response.

I am looking for freeware. If freeware not available, I have to implement
encryption support for sqlite on winrt.

What is the procedure to implement encryption support on winrt?

Thanks,
dd


Many others have replied with the couple of other known alternatives.  
Frankly, I find this insistence on "freeware" a bit flawed unless you 
plan to open source the final solution and want something that offers 
you a license to do so.  Assuming you get paid at least US$500 per 
month, I think you'll find that the solutions will be cheaper than 
actually implementing it yourself.  I am confident that you'll spend at 
least 4 man months to convince management, search other alternatives, 
figure out how encryption works, design a solution that works within 
SQLite, create tools for building the encrypted database, create code 
for querying the encrypted database, test the solution works well for 
you and then document it for other devs to use.  Instead of that, a 
US$2000 solution that is royalty free and can be used on any number of 
projects (as long as they are built at the same site IIRC), is a "steal".


On the other hand, maybe, your requirements are dead simple.  What do 
you want as "encryption support"?  Something that encrypts the full 
file, the content in some of the fields/ tables, or something that also 
supports querying such a database?  I don't think that the last one is 
quite that straightforward.  I would recommend you sit down someone from 
management and explain to them that what the cost tradeoffs are.


Best Regards,
Mohit.

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

Re: [sqlite] to encrypt sqlite db

[Mohit Sindhwani]

On 31/8/2013 8:40 PM, Paolo Bolzoni wrote:

There is a non-free version of sqlite that
encrypt the db. If it is that you want then
you have to contact them directly.

Otherwise just use sqlite on a EncFs mounted
disk?



Adding on to Paolo's answer, see this: 
http://www.hwaci.com/sw/sqlite/prosupport.html

See SEE and CEROD on that page.

Best Regards,
Mohit.



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

Re: [sqlite] Understanding how data is stored and the index is managed

[Mohit Sindhwani]

Hi Mike,

Thanks for the steps to try.  I was hoping for some theoretical 
(implementation) insight before we do the normal battery of tests... 
we'll get onto that next week if there are no other inputs on how data 
storage is handled.



On 12/7/2013 8:36 PM, Michael Black wrote:

One more test I would is first principles.
Load 1200 records and just do "select * from items" -- you aren't going to
get any faster than that.
Then add the index query.
You should find a performance knee as you add records (try adding them in
powers of 2).
To test I would use "select * from items where rowid%2==0" for 2400 records,
and rows%4 for 4800 records, etc.


We'll give this a shot.  I remember that %x queries are usually 
significantly slower, but I do know what you're getting at, so we'll 
give it a try.



Also, what happens if you don't encrypt?

Also, what if you turn off SQLite caching completely.  Let CE have a bit
more cache space?


There is enough available memory in the system right now.  So, we're not 
choking anything else at this stage.



You could also create 2 tables -- one for you frequent data and one for the
non-frequent.
That's 2 selects but might be noticeably faster if the frequent is small
enough.


There are a number of queries that use these tables and they don't know 
if the data is in the more frequent area or the less frequent area.  So, 
every query would have to bounce through both tables.  I know that we're 
using it more as a cache in the expectation that most queries execute on 
only the first table.  But there are other uses of the data where the 
distribution is more general.


Thanks for the inputs - we'll see what we can do.

Best Regards,
Mohit.


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

Re: [sqlite] Understanding how data is stored and the index is managed

[Mohit Sindhwani]

Hi Simon,

As always thanks for your prompt reply.  My answers inline.


On 12/7/2013 1:11 PM, Simon Slavin wrote:

On 12 Jul 2013, at 5:19am, Mohit Sindhwani  wrote:


We could try to renumber the IDs so that all the IDs are in sequence, but that 
is not the easiest thing to do.  Does insertion order have an impact on how the 
data is stored?  If we inserted the most frequently accessed records first, 
would it mean that they would be closer to each other in the table and the 
index and therefore, we could get a better performance?

There are some 'yes' answers here, but some of them depend on many things about 
your setup and how the database file was generated.  Generally speaking, the 
fastest way to make such a file would be to write all the rows in primary key 
order, assuming that your primary key was an AUTOINCREMENTed INTEGER.


Our primary key is not an AUTOINCREMENTED integer, it is a unique 
numeric ID for the record.  The insertion during preparation is done 
using a .import for the file, ordered in increasing ID.



One simple thing you might try is running VACUUM on the database file.  Given 
your setup, this may be an overnight run.  And it can temporarily require twice 
as much spare disk space as your database file occupies.  It will result in the 
index of each table being very efficiently packed.


VACUUM is done in our production process before we compress and encrypt 
the database.  So, it should be efficiently packed.



But I'm not familiar with CEROD in real life.  It may be that the process of 
building the packed database inherently does a VACUUM.  If not, it would be 
best to do the VACUUM immediately before packing the database.

Yes, that is what we do.

Best Regards,
Mohit.

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

[sqlite] Understanding how data is stored and the index is managed

[Mohit Sindhwani]

Hi All,

We have a system in which there are around 3 million records in a 
particular table within SQLite3 on Windows CE.  There is a primary key 
index on the table.


We are selecting 1200 records from the table using a prepare - bind - 
step - reset approach.  We find the time seems unreasonably long given 
that the query is of the type select * from items where id = ? (id is 
the primary key).  On this device, it takes around 6seconds.


We think it's because the table is probably quite large, as may be the 
index but the cache is only 2MB.  If we are hitting the table at random 
places, it could be that every step actually takes us to a different 
part of the table, so the cache is not helping.


To test this hypothesis, we did the following:
* Forced the queries to be far apart (basically every query was to a 
record that 3million/ 1200 apart) - this would be like the worst case, 
and it was.  The time went up to 9 seconds (+3 seconds)
* Forced the queries to be within the first 15% of the space - we expect 
that this would increase the cache hit.  The time came down to around 4 
seconds (-2 seconds).


Given this, are there things that we can do?  We know that when we do 
the lookup, there is a high chance that a significant portion of the 
1200 results will always lie in within the same 15% of the database.  
Can we make use of that knowledge somehow?


We could try to renumber the IDs so that all the IDs are in sequence, 
but that is not the easiest thing to do.  Does insertion order have an 
impact on how the data is stored?  If we inserted the most frequently 
accessed records first, would it mean that they would be closer to each 
other in the table and the index and therefore, we could get a better 
performance?


The database is read only and we are using CEROD, so we don't have to 
worry about data changing.


(The page size is 4KB and that matches the file system block size).

Thanks for any thoughts.

Best Regards,
Mohit.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SEE + CEROD

[Mohit Sindhwani]

On 6/5/2013 7:24 PM, Richard Hipp wrote:

Yes.  SEE and CEROD can be combined to work together.

Remember how with CEROD you append some code to the end of the sqlite3.c
amalgamation file?  SEE works the same way.  To use them both, you just
append both additions to the amalgamation.


Thanks Richard.  That makes the decision super simple.

Best Regards,
Mohit.


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

[sqlite] SEE + CEROD

[Mohit Sindhwani]

Hi Guys,

We already have a license for CEROD and are now contemplating getting a 
license for SEE to use within our products.  I notice that both products 
are separately provided as amalgamation sqlite3.c files.  Is it possible 
to use these two together in the same system?  Just looking for someone 
with experience of this to share whether there are any gotchas that we 
should be aware of.


Best Regards,
Mohit.


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

Re: [sqlite] RTree Documentation Error?

[Mohit Sindhwani]

Hi Richard, et al,

I'm sorry that it's bad manners for me to raise an issue and then 
disappear, but I didn't get a chance to check the list till a few 
minutes ago...


On 17/4/2013 9:36 PM, Richard Hipp wrote:

On Wed, Apr 17, 2013 at 9:25 AM, Michael Black  wrote:


Correct -- no results for the "real" 2nd query


I see.  I ran two queries together in my script so what I thought was the
2nd query was really the 3rd.

The SQLite HQ coordinates are fixed in
http://www.sqlite.org/draft/rtree.html now.


Thanks for the fix!  It all works now like it says it should.  We had a 
bit of a moment when we tried the query and it didn't seem correct :)


Best Regards,
Mohit.


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

[sqlite] RTree Documentation Error?

[Mohit Sindhwani]

Hi, I was looking at the RTree documentation page with one of my 
colleagues - http://www.sqlite.org/rtree.html


We tried the example with the schema in 3.1, populated the data from 3.2 
and then queried it using the query of 3.3 - we got no results while the 
documentation says that "the query would very quickly locate the id of 1 
even if the R*Tree contained millions of entries".


Is the line "AND minY>=35.00  AND maxY<=35.44;" supposed to be "AND 
minY>=33.00  AND maxY<=35.44;" (33 instead of 35.00)?


Best Regards,
Mohit.
17/4/2013 | 1:15 AM.



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

Re: [sqlite] Slow sqlite3_open() - possible culprits?

[Mohit Sindhwani]

Hi!

On 15/3/2013 11:40 PM, Mohit Sindhwani wrote:

Hi Richard,

On 14/3/2013 8:17 PM, Richard Hipp wrote:
On Thu, Mar 14, 2013 at 12:34 AM, Mohit Sindhwani  
wrote:


Hi, we are using SQLite3 + CEROD for a number of databases in an 
embedded
systems application running on Windows CE.  We're finding 
unexpectedly long

time to open the database (0.5s ~ 2.8sec).  Maybe, these times are
reasonable, but they seem long to us.



Did you read this thread:

http://www.mail-archive.com/sqlite-users%40sqlite.org/msg75761.html


I did read this thread and I thought that it may not be related since 
we are on Windows CE with FAT, we're using CEROD (so the database is 
implicitly meant for Read Only operations) and we're opening the 
database with SQLITE_READ_ONLY specified in the _open() API.  I'll try 
to check the permissions and attributes again to be sure.


I checked again and the file attributes are not set for "read only".

Any other thoughts on what could be slowing sqlite3_open() on a CEROD 
database?


Best Regards,
Mohit.
25/3/2013 | 12:14 PM.

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

Re: [sqlite] Slow sqlite3_open() - possible culprits?

[Mohit Sindhwani]

Hi Richard,

On 14/3/2013 8:17 PM, Richard Hipp wrote:

On Thu, Mar 14, 2013 at 12:34 AM, Mohit Sindhwani  wrote:


Hi, we are using SQLite3 + CEROD for a number of databases in an embedded
systems application running on Windows CE.  We're finding unexpectedly long
time to open the database (0.5s ~ 2.8sec).  Maybe, these times are
reasonable, but they seem long to us.



Did you read this thread:

 http://www.mail-archive.com/sqlite-users%40sqlite.org/msg75761.html


I did read this thread and I thought that it may not be related since we 
are on Windows CE with FAT, we're using CEROD (so the database is 
implicitly meant for Read Only operations) and we're opening the 
database with SQLITE_READ_ONLY specified in the _open() API.  I'll try 
to check the permissions and attributes again to be sure.


Best Regards,
Mohit.


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

Re: [sqlite] Slow sqlite3_open() - possible culprits?

[Mohit Sindhwani]

Hi Stephen,

Thanks for the reply!

On 14/3/2013 2:20 PM, Stephen Chrzanowski wrote:

If you're attempting to open a read only SQLite database, it seems as
though it will take that time to validate write permissions.


By "read only", I meant that the database is for reading only.  It 
doesn't have a "read only attribute" set on the file system. Further, 
the open API is called with READ_ONLY specified as a parameter.  I'll 
double check the file attributes on the disk.



I don't know what CEROD is.
CEROD is an SQLite extension from HWACI for "Compressed Encrypted 
Read-Only Database" - it encrypts and compresses the database after 
which it is much smaller and safe from prying eyes, but can only be read 
from, not written to.  Our database is a database of reference 
information, so that works very well for our application.



In that thread, to which I was a part of, you'll notice
that when I changed just the basic file attribute to read only, even the
CLI paused for the 2 second period.  I've NEVER looked at, which inherently
means I've never traced into, SQLites code to validate what I've found.
But with my Delphi SQLite wrapper, and the SQLite CLI, I get the same
delayed results.


Thanks for the details.  I'll double check the file attributes once to 
be sure, but since we're opening it with READ_ONLY flag specified in the 
API, I would expect that the problem may be elsewhere.


Best Regards,
Mohit.


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

[sqlite] Slow sqlite3_open() - possible culprits?

[Mohit Sindhwani]

Hi, we are using SQLite3 + CEROD for a number of databases in an 
embedded systems application running on Windows CE.  We're finding 
unexpectedly long time to open the database (0.5s ~ 2.8sec).  Maybe, 
these times are reasonable, but they seem long to us.


We are using:
sqlite3_open_v2(sFilePath, &db, SQLITE_OPEN_READONLY, NULL);

What are the kinds of things that would slow down opening the database?
> Database size?
> Database schema?
> CEROD?

If someone in the know can throw some light on this, we can try to see 
how to mitigate the timing.


I know there was a recent thread on sqlite3_open taking 1.5s under IIS 
and that was a permissions issue.  That is not the case for us (since we 
are using a read-only CEROD database)


Thanks & Best Regards,
Mohit.


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

Re: [sqlite] Run sqlite from a batch file

[Mohit Sindhwani]

Hi Rick,

Welcome to the group!

I get the feeling that you hijacked an earlier thread given the long 
bodies of text I removed from the email.


Anyway, it's great to see a clear detailed email with the stuff you tried.

The first thing I would check for is if sqlite3 is on the path of the 
environment when the command file is executed.  Clearly, your batch file 
is running.  So, if you're not sure if an error message was being thrown 
up, I'd add a "pause" to stop the command window from closing..


On 1/3/2013 4:03 PM, Rick Guizawa wrote:

MyBatch.bat contains...

sqlite3 mydb.db < a.txt
echo "test" > c:\test.txt



Could you change it this and report what you see.

MyBatch.bat:

sqlite3 mydb.db < a.txt
echo "test" > c:\test.txt
pause


Best Regards,
Mohit.
1/3/2013 | 9:46 PM.
 





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

Re: [sqlite] How to get the RecNo ???

[Mohit Sindhwani]

Hi Igor, Keith,

I think my explanation wasn't very clear.

I just meant to say that ROWID is not a sequence number of insertion in 
the case when an INTEGER PRIMARY KEY is used - it comes across as a 
sequence number when we don't have an integer primary key.


Rest of the answers less relevant now - but inline.

On 11/2/2013 11:45 AM, Igor Tandetnik wrote:

On 2/10/2013 10:06 PM, Mohit Sindhwani wrote:

The OP reads records in a loop. I imagine he or she may simply use a 
loop counter as a sequence number, if that's what is needed. However, 
I too read the original question as asking for a unique identifier, 
such as ROWID, rather than a sequence number (which, as I said, is 
easy to obtain). The OP is asking for "record number that sqlite 
creates when it creates your record" - that is, the ID generated on 
row insertion, also known as ROWID.

That is, of course, correct.




2. The problem with INTEGER PRIMARY KEY.. it is not representative of
what we expect from a ROWID.  We expect ROWID to be a sequential number
that increases with every record insertion.


Who do you mean "we", Kemo Sabe?


By "we", I probably meant myself :)  When I saw ROWID first, I assumed 
it was a sequence number of sorts.. and that lead to all sorts of 
problems.  My intention was to warn someone else to not make that 
assumption.






 However, if you have
something like num INTEGER PRIMARY KEY, you will be able to do this:
insert with num=1  #rowid = 1
insert with num=2  #rowid = 2
insert with num=4  #rowid = 4
insert with num=3  #rowid = 3

It could be argued that the OP wanted the 3rd record to have a row
number of 3, rather than 4.


Why then would the OP insert the row with num=4, rather than 3? Why do 
the extra work for the sole purpose of hurting your goal? That doesn't 
make much sense.


Was meant to be an illustration that ROWID is not a sequence number...




So, it doesn't give the concept of sequence at all since an INTEGER
PRIMARY KEY needs to be unique, but does not have to be monotonically
increasing.


Well, if you go out of your way to intentionally break the 
monotonically increasing sequence, then you end up with a broken 
sequence. Gun, meet foot.


Again, meant to illustrate that when you are not using an INTEGER 
PRIMARY KEY, the ROWID is monotonically increasing.  When using an 
INTEGER PRIMARY KEY, that will not be so.  So, just be careful if you 
intend to do something like select * from mytable ORDER by ROWID DESC 
LIMIT 1; and hope to select the last inserted record if you have an 
INTEGER PRIMARY KEY on that table.





I have been caught out by this - I read what the documentation says but
just did not carefully understand it.  What the above means is this:
* You do an insert in the sequence as above, you say that I should not
sort by id ASC because you want it in insertion order
* You decide then to do a sort by ROWID ASC - expecting that ROWID is
maintaining the sequence since when you do a general SELECT * from
mytable; you often get it in insertion sequence (if I'm not wrong).


You are wrong. Normally, with a simple SELECT like this, you get the 
results ordered by ROWID. SQLite table is stored as a b-tree, with 
ROWID as its key, so that's the natural order of traversal.


Learn something every day :)

Thanks!

Best Regards,
Mohit.


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

Re: [sqlite] How to get the RecNo ???

[Mohit Sindhwani]

Hi Peter,

I have been caught out on this.

On 11/2/2013 8:40 AM, Peter Aronson wrote:
You can add it to the select list as OID, ROWID or _ROWID_ or, if the 
table has a column defined INTEGER PRIMARY KEY (but not INTEGER 
PRIMARY KEY DESC) it'll also be this value.  See: 
http://www.sqlite.org/lang_createtable.html#rowid


There are a couple of very fine points here:

1. If I'm not wrong, ROWID does not change when you delete records. So, 
if the OP wants the ability to always say "it's the 9th record in the 
table right now", it won't work.  Given that an RDBMS table is a set and 
in theory, sets don't have that property, actually, I don't think SQLite 
by itself can give you that number.  What you need is actually something 
like a monotonically increasing number (say id) and then your query will 
need to take into account a way to count the number of records where id 
< my_record_id - an alternative could be to use the time stamp of 
insertion and do the same.


2. The problem with INTEGER PRIMARY KEY.. it is not representative of 
what we expect from a ROWID.  We expect ROWID to be a sequential number 
that increases with every record insertion.  However, if you have 
something like num INTEGER PRIMARY KEY, you will be able to do this:

insert with num=1  #rowid = 1
insert with num=2  #rowid = 2
insert with num=4  #rowid = 4
insert with num=3  #rowid = 3

It could be argued that the OP wanted the 3rd record to have a row 
number of 3, rather than 4.
In these small numbers, it can still be understood because you could say 
that num=3 comes before num=4 and that's what the user should expect to 
get.  But remember that SQLite will behave the same way with large numbers

insert with num=2383831  #rowid = 2383831
insert with num=2343832  #rowid = 2343832
insert with num=4273  #rowid = 4273
insert with num=300029393  #rowid = 300029393

So, it doesn't give the concept of sequence at all since an INTEGER 
PRIMARY KEY needs to be unique, but does not have to be monotonically 
increasing.


I have been caught out by this - I read what the documentation says but 
just did not carefully understand it.  What the above means is this:
* You do an insert in the sequence as above, you say that I should not 
sort by id ASC because you want it in insertion order
* You decide then to do a sort by ROWID ASC - expecting that ROWID is 
maintaining the sequence since when you do a general SELECT * from 
mytable; you often get it in insertion sequence (if I'm not wrong).


Depending on what problem the OP wants to solve, the solution is likely 
to be a bit more complex.


Best Regards,
Mohit.



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

Re: [sqlite] I suspect not - but is the database separator configurable?

[Mohit Sindhwani]

Hi Ryan,

On 2/2/2013 1:55 AM, Ryan Johnson wrote:
That would break sybase, though: the quotes would also tell it to 
treat the db name and periods as part of the table name, too:


sqlite3> create table foo(x,y);
sqlite3> .tables
foo
sqlite3> select * from "main.foo";
Error: no such table: main.foo


Since the OP (you aren't the OP, right?)  seems to have control over the 
code, I wonder if a compile-time macro could be used to attach the 
quotes around the database name only in the test environment. But, 
otherwise, I think we're at the end of what can be done with SQLite.


Best Regards,
Mohit.



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

Re: [sqlite] I suspect not - but is the database separator configurable?

[Mohit Sindhwani]

Hi Marc,

On 1/2/2013 10:42 PM, message adams wrote:

My applications actually run against sybase, but I'd love to use a
connection to an in-memory sqlite to carry out my testing.
As part of the unit-test, I'd pass the sqlite conenction into my source
code hoping it would be none the wiser.

The only real problem I see, is sybase uses a double period separator
between the database and table. e.g.
select * from database..table

... whereas sqlite
select * from database.table


Can you use double quotes in the query?

For example, this works in SQLite3

sqlite> create table "db..abc"(p INTEGER, q INTEGER);
sqlite> .tables
db..abc
sqlite> select * from "db..abc";
sqlite>

Best Regards,
Mohit.


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

Re: [sqlite] Setting initial database size.

[Mohit Sindhwani]

Hi Roger

On 1/9/2012 10:46 PM, Roger Binns wrote:

You can use sqlite3_file_control.  See:

   http://www.sqlite.org/c3ref/c_fcntl_chunk_size.html

SQLITE_FCNTL_CHUNK_SIZE would be the most useful - set it to something you
have measured as useful to your data patterns and filesystems.



This teaches me something new - thanks!

Cheers,
Mohit.
2/9/2012 | 4:20 PM.

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

Re: [sqlite] Setting initial database size.

[Mohit Sindhwani]

Hi Kjell,

On 31/8/2012 8:28 PM, kjell.gunnars...@sungard.com wrote:

Hi,

Does somebody know if it's possible to set an initial database size when the 
database is created ?.
(A large file doesn't have to be incremented in size when records are inserted).


I have never seen any thing that suggests that this can be done by 
design - generally, the database grows as we go along.  In practice, you 
could emulate this by creating your base schema, then creating a 
temporary table with some schema and inserting 10s of millions of 
records into it so that the database size grows.  Once it's got to a 
level that makes you happy, you can drop the table.  SQLite does not 
reduce the database size by itself till you do a "VACUUM;" - so, that 
initial size will be maintained and the previously allocated pages will 
be reused as you insert your live data.


Out of curiosity, what's the use case?

Best Regards,
Mohit.
1/9/2012 | 10:08 PM.


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

Re: [sqlite] What Windows versions are supported by SQLite3

[Mohit Sindhwani]

On 26/8/2012 3:38 AM, Klaas V wrote:

Mohit wrote:


SQlite3 works fine on Windows CE (at least 5.0 and 6.0) and Windows

Mobile 6.x >or so.  Windows Phone 7 doesn't support end-users compiling
native C/ C++ code >and accessing it from their applications - but there
is a C# port that does work (as >far as I know).

We used 3.6.x (don't remember exact update) successfully under Vista two years 
ago. Now we have OSX ( SQLite r.v.u 3.7.13)



I may have overlooked mentioning it, but yes, Windows XP/2000/Vista/ Win 
7 and Windows Server 2000 - 2008 all work fine for SQlite3.


Best Regards,
Mohit.
26/8/2012 | 1:13 PM.

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

Re: [sqlite] SQLite support on windows

[Mohit Sindhwani]

On 26/8/2012 12:30 PM, Arbol One wrote:

I am glad someone asked this question, we are almost finish with a small
application that is best suited for a hand held device. Is there a device
anyone here could suggest?
As I said, the application is very small and uses SQLite as its RDB. The
application resembles a questionnaire... fill in the blanks kind of thing,
the data collected goes in to the RDB and later on downloaded to a pc.

TIA


What kind of device are you looking for?  A lot also depends on what 
platform your application is built for, and what it needs.  Your current 
description tends to suggest that it's rather well suited as an app for 
any (or many) of the mobile phones.  There are a number of devices that 
run Windows CE and you can compile SQLite3 with Visual Studio for those 
devices.


Best Regards,
Mohit.
26/8/2012 | 1:11 PM.

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

Re: [sqlite] SQLite support on windows

[Mohit Sindhwani]

On 22/8/2012 11:49 PM, Rajahariharan Jayam wrote:

Dear,



What are the windows O/S version does the SQLite supports?



As far as I know, it should support any version of Windows that you can 
compile it for... I don't remember seeing any posts in the past that 
support for any specific version of Windows has been dropped. So, it 
probably goes back enough to still support Windows 98 or so. It does 
work fine on recent Windows versions (I have used it on Windows XP, 
Windows 2000, Windows Server 2003/ 2008, and Windows Vista/7).


SQlite3 works fine on Windows CE (at least 5.0 and 6.0) and Windows 
Mobile 6.x or so.  Windows Phone 7 doesn't support end-users compiling 
native C/ C++ code and accessing it from their applications - but there 
is a C# port that does work (as far as I know).


That said, I think there is a build in the works for WinRT - I'm not 
sure of the status of that.  Other than that, Windows 8 and Windows 
Phone 8 support native C/ C++ code and there's no reason why it should 
not work.


Now, the real question is - what is the set of version(s) that you are 
keen to check support for.. as you can see, your question is quite vague.


Best Regards,
Mohit.
25/8/2012 | 6:24 PM.


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

Re: [sqlite] How to know what terms were created using FTS

[Mohit Sindhwani]

Hi Dominique,

Thanks!

On 17/8/2012 7:14 PM, Dominique Pellé wrote:


This gives the tokens:

sqlite> CREATE VIRTUAL TABLE ft USING fts4(x);
sqlite> INSERT INTO ft VALUES("hello world");
sqlite> INSERT INTO ft VALUES("hello there");

sqlite> CREATE VIRTUAL TABLE ft_terms USING fts4aux(ft);

sqlite> .header on
sqlite> SELECT * FROM ft_terms;
term|col|documents|occurrences
hello|*|2|2
hello|0|2|2
there|*|1|1
there|0|1|1
world|*|1|1
world|0|1|1


Actually, I want to know:
* terms for "hello world" are "hello" and "world"
* terms for "hello there" are "hello" and "there"
...and so on.

The aux table doesn't give an easy way to find that, as far as I can see.

However, thanks for taking the time to reply!  I have received a couple 
of other solutions that may also help!


Best Regards,
Mohit.
19/8/2012 | 12:59 AM.



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

Re: [sqlite] How to know what terms were created using FTS

[Mohit Sindhwani]

On 17/8/2012 6:41 PM, Dan Kennedy wrote:

On 08/17/2012 03:58 PM, Mohit Sindhwani wrote:

Hi Ralf,


On 17/8/2012 3:50 PM, Ralf Junker wrote:

On 17.08.2012 09:30, Mohit Sindhwani wrote:


We're using FTS4 and it works well for many things.  One of the things
that we'd like to do is to see what terms are being created by the
tokenizer in use.  What would be the easiest way to do that?

I tried looking through the fts_aux table and the segments and content
tables, but nothing struck me directly as usable.  Any suggestions?

http://www.sqlite.org/fts3.html#fts4aux


I did look at this - but I couldn't figure out a way that allowed me to
see what terms were created by the tokenizer for a particular
expression.  Example "SOME TEXT" becomes "SOME", "TEXT" - is there a way
to find that?


You could, I suppose, obtain a handle for the tables tokenizer using
the fts3_tokenizer() function:

  http://www.sqlite.org/fts3.html#section_8_1

Then use it to tokenize your expression using the API in
fts3_tokenizer.h. See static function "testFunc()" in
fts3_tokenizer.c for an example.



Thanks, Dan... yes, I guess that should work.  Thanks for pointing me to 
the correct file to go into there.


Best Regards,
Mohit.
19/8/2012 | 12:57 AM.

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

Re: [sqlite] How to know what terms were created using FTS

[Mohit Sindhwani]

Hi Ralf,


On 17/8/2012 3:50 PM, Ralf Junker wrote:

On 17.08.2012 09:30, Mohit Sindhwani wrote:


We're using FTS4 and it works well for many things.  One of the things
that we'd like to do is to see what terms are being created by the
tokenizer in use.  What would be the easiest way to do that?

I tried looking through the fts_aux table and the segments and content
tables, but nothing struck me directly as usable.  Any suggestions?

http://www.sqlite.org/fts3.html#fts4aux


I did look at this - but I couldn't figure out a way that allowed me to 
see what terms were created by the tokenizer for a particular 
expression.  Example "SOME TEXT" becomes "SOME", "TEXT" - is there a way 
to find that?


Best Regards,
Mohit.
17/8/2012 | 4:57 PM.



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

[sqlite] How to know what terms were created using FTS

[Mohit Sindhwani]

Hi,

We're using FTS4 and it works well for many things.  One of the things 
that we'd like to do is to see what terms are being created by the 
tokenizer in use.  What would be the easiest way to do that?


I tried looking through the fts_aux table and the segments and content 
tables, but nothing struck me directly as usable.  Any suggestions?


Best Regards,
Mohit.
17/8/2012 | 3:29 PM.


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

Re: [sqlite] Question about multi-threading with a read-onlydatabase

[Mohit Sindhwani]

Hi Simon,

On 29/5/2012 7:36 PM, Simon Slavin wrote:


It might be worth pointing out that the creator of SQLite feels that threads 
are evil.  In fact it's in the FAQ, together with a pointer to the standard 
work on the subject (which I have never managed to understand all the way 
through):



If you haven't already gone to effort to write your program to use threads, 
then you might consider not doing it.  Perhaps you could use processes instead. 
 Multiprocessing is fine.



We're on a Windows CE embedded device and are already using processes 
for a couple of things, but still think we'll  need threads to decouple 
some drawing that uses the database from the rest of the system that 
also needs to access the database for its work.


Best Regards,
Mohit.
30/5/2012 | 12:17 AM.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about multi-threading with a read-onlydatabase

[Mohit Sindhwani]

Hi Igor,


On 29/5/2012 11:41 AM, Igor Tandetnik wrote:



Multi-thread. In this mode, SQLite can be safely used by multiple

threads provided that no single database connection is used
simultaneously in two or more threads.

Yes. But note that the default mode is "serialized". You must explicitly switch 
to multi-thread and consciously reduce thread safety guarantees. So, just don't do that.


Thank you very much for the clarification.  I guess we'll move to the 
multi-threaded mode in a later release.


Best Regards,
Mohit.
29/5/2012 | 3:00 PM.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about multi-threading with a read-only database

[Mohit Sindhwani]

Hi Igor,

On 29/5/2012 10:52 AM, Igor Tandetnik wrote:

Mohit Sindhwani  wrote:

However, looking at this:
http://www.sqlite.org/cvstrac/wiki?p=MultiThreading

It has never been safe to use the same database connection simultaneously in
multiple threads.

That document is obsolete. It has been safe for a long time now. For details, 
see http://www.sqlite.org/threadsafe.html


Thank you very much.  However, reading this page also, it says:

> Multi-thread. In this mode, SQLite can be safely used by multiple 
threads provided that no single database connection is used 
simultaneously in two or more threads.


So, that means, we should open the DB once in each thread, right?  Or am 
I misunderstanding the word "connection"?


By the way, how can I mark the earlier wiki document to be obsolete.  I 
couldn't see a way to sign up and edit.


Best Regards,
Mohit.
29/5/2012 | 11:01 AM.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Question about multi-threading with a read-only database

[Mohit Sindhwani]

Hi All,

We have a read-only database (compiled with CEROD) that needs to be 
accessed from within the same program using multiple threads.  
Currently, we open the database once and all the threads use the 
sqlite3_db* to create their own personal prepared queries and run their 
queries.


However, looking at this:
http://www.sqlite.org/cvstrac/wiki?p=MultiThreading

It has never been safe to use the same database connection simultaneously in
multiple threads. If you use the sqlite3_prepare() API to create prepared
statements, each prepared statement is considered to be a part of the
database connection from which it was derived. So you cannot run two
prepared statements originating from the same database connection in
different threads at the same time.

Just trying to understand better:
* What does it mean that "you cannot run two..." --> will it cause an 
error, or the results are undefined, or something else?
* Does this apply completely even to systems that definitely have only 
read queries?

* Should we open the DB separately from each thread?

Thanks for any inputs.

Best Regards,
Mohit.
29/5/2012 | 9:57 AM.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Good books to lear SQL using SQLite?

[Mohit Sindhwani]

On 30/4/2012 10:53 PM, Simon Slavin wrote:

On 30 Apr 2012, at 3:18pm, Arbol One  wrote:


I am learning SQL using SQLite, can anyone tell me of a good book to learn SQL 
using SQLite?

Here's the cannonical list:



The second one down, _Using SQLite_ is by a frequent contributor to this list, 
and may suit you best.



I found "the definitive guide" very useful when I started.  but it's not 
the book to "learn SQL using SQLite" - it's more a book to understand 
the background of databases, a bit of SQL and a good guide on using 
SQLite for a number of things.  On the other hand, by name, "The SQL 
Guide to SQLite " sounds like what you want - but I haven't read it and 
can't recommend it one way or the other.


Best Regards,
Mohit.
30/4/2012 | 11:30 PM.

Best Regards,
Mohit.
30/4/2012 | 11:29 PM.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory Usage/ Drawbacks of Statements

[Mohit Sindhwani]

Hi Eric,

On 24/4/2012 10:03 PM, Eric Minbiole wrote:

Similar to Pavel's suggestion, our implementation maintains a simple
cache of prepared statements, keyed by the SQL query that created
them.  For example:

   pStatement = Cache.GetQuery("SELECT * FROM xyz");

would return the cached statement if the query had been seen before,
or would auto-create it, if needed.

This (attempts to) give the best of both worlds: Statements are only
created / prepared once, improving run-time performance.  In addition,
the programmers aren't burdened with trying to figure out up-front
which queries might / might not be used later, simplifying
development.

This approach has worked well in our application.


Thanks!  This is similar to what we were thinking... it's good to know 
that you're successfully using it.


Best Regards,
Mohit.
25/4/2012 | 12:01 AM.

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

Re: [sqlite] Memory Usage/ Drawbacks of Statements

[Mohit Sindhwani]

Thanks Pavel,

That gives me something new to do with SQLite over the next few weeks.

On 23/4/2012 8:47 PM, Pavel Ivanov wrote:

1. Do statements do any thing that would require a lot of memory to be
maintained?

No, they don't need a lot of memory, but still some memory is used. So
if you have like thousands of statements you should worry about this.
If you have 20 or 30 statements your database cache will likely
consume much more memory, so don't worry.


2. Are there any known drawbacks of doing this?

Preparing all statements takes some time which adds to startup time of
your application. Also you could prepare some statements which won't
be used later. If those are not problems for you then preparing all
statements at startup is a way to go.


3. Finally, if sqlite3_reset is called multiple times before a bind, is
there a problem?

No, there's no problem in here.


Pavel



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

[sqlite] Memory Usage/ Drawbacks of Statements

[Mohit Sindhwani]

Hi, our system does fairly predictable queries when it runs.  A number 
of modules all access data using a handful of queries of each.  We open 
the database at the start and close it at the end of the program.


Each query follows the usual pattern of prepare - bind - step - reset - 
(eventually) finalize.


I was wondering if there is any known drawback in creating statements 
up-front when the system is started and use them as and when they are 
needed.


1. Do statements do any thing that would require a lot of memory to be 
maintained?


2. Are there any known drawbacks of doing this?

3. Finally, if sqlite3_reset is called multiple times before a bind, is 
there a problem?


Thanks,
Mohit.

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

Re: [sqlite] Using FTS for '='

[Mohit Sindhwani]

Hi Simon,

On 20/2/2012 12:26 AM, Simon Slavin wrote:

You may get only marginally slower results by having just an index on 
text_field.  It depends on how 'chunky' your values are: whether you have lots 
of rows with the same value in [text_field] but different values in [category], 
for example.  Try some timing comparisons of the current setup with one after 
deleting all those indexes and creating just one for text_field.

But the great advantage of using FTS is in searching text fields for contents.  
If you're searching for exact contents of a text field FTS is a waste of time 
and filespace.


That is a very good point!  In our case, we have a very large spread of 
data... so, each text_field appears in less than 5 rows!  So, you're 
right - if we do index just text_field, there would be a scan of just 5 
rows or so to find the correct answer!


Thanks, we'll try this.

Best Regards,
Mohit.
20/2/2012 | 12:58 AM.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Using FTS for '='

[Mohit Sindhwani]

Hi Guys,

We have a table that has a number of fields (category, type, format, 
etc.) all of which are integers and we have a text field that we need to 
search for.  So, what we want to do is a query like:

select * from myTable where text_field = '...' and category = 2;

Currently, to support this, we have indexes such as
category, text_field
type, text_field,
etc.

Since we're searching text, it should be possible (and possibly faster?) 
to use FTS.  But I couldn't figure out how we could do a simple '=' 
query using FTS.


I tried:
- fts_table.fts_column match "A"  # returns items like 'A', 'A ZIU' as 
well as 'ZI A' (we wanted only 'A')
- using fts_table.fts_column match "^A" (is this allowed?) seems to 
return items such 'A', 'A ZIU' but not 'ZI A'


What do you recommend we use?

Thanks,
Mohit.


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

Re: [sqlite] SQL query causes various hangs/crashes/exits

[Mohit Sindhwani]

Hello...

On 27/1/2012 3:20 AM, Larry Knibb wrote:

On 25 January 2012 21:01, Igor Tandetnik  wrote:

This index can help satisfy conditions of the form (traditional='X') or  
(traditional='X' AND simplified='Y'). But it doesn't help at all for conditions 
on (simplified='Y') or (traditional='X' OR simplified='Y'). So your query 
devolves to a full table scan, and apparently, that just takes a long time.

Think about it this way. Imagine you have a phonebook with people's names, 
sorted by last name then first name. This makes it easy to find all Smiths, or 
all John Smiths, but doesn't help with the task of finding all Johns, nor all 
people who are either John or Smith.

You want two separate indexes, one on hanzi(traditional) and another on 
hanzi(simplified)

Thanks! Splitting the index into two did the trick and now the query
is working well.

Cheers,
Larry

I was under the impression that SQLite only uses 1 index per table per query 
(not sure where I got that idea).. it's interesting to not that this works..!  
Is this the recommended way to optimize OR queries involving 2 different fields 
of the same table?

But as I search the SQLite website again, I find:

Note that in most cases, SQLite will only use a single index for each table in 
the FROM clause of a query. The second OR-clause optimization described here is 
the exception to that rule. With an OR-clause, a different index might be used 
for each subterm in the OR-clause.

Something learned, thanks!

Thanks,
Mohit.



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

Re: [sqlite] Found it

[Mohit Sindhwani]

On 26/1/2012 5:21 AM, Nico Williams wrote:

On Wed, Jan 25, 2012 at 3:07 PM, Richard Hipp  wrote:

The SQLite byte-code engine was being too conservative and was reparsing
the schema in places where it was not strictly necessary.  The fix was to
restrict the places where the schema was reparsed to situations that really
needed it.

This thread has been awesome, gripping reading.  Thanks to both of you
for sticking with it and figuring it out.



Absolutely!  I come home from work and tune in to this thread, gripped :)

Thanks,
Mohit.
26/1/2012 | 9:53 PM.



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

Re: [sqlite] replying to thread

[Mohit Sindhwani]

On 24/1/2012 3:02 PM, David Henry wrote:

How do I reply to a thread?

I put Re: Re: [thread title] into the subject but it comes out as a new
thread

e.g. I put  "Re: Re: [sqlite] ftruncate implementation" in the subject field
but a new thread "[sqlite] ftruncate implementation" was started.




Normally, you should just need to do a "reply", "reply all" or "reply 
List" to an email in the thread - that should do it!


Best Regards,
Mohit.
24/1/2012 | 3:11 PM.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Efficient usage of sqlite

[Mohit Sindhwani]

On 2/1/2012 3:47 PM, Simon Slavin wrote:


5 or 10 tables is not a problem.  If you were talking about 50 to 100 tables, 
that might start to be a problem.  But yes, you can store any number of files 
in the following way:

CREATE TABLE resources (
pathAndName TEXT PRIMARY KEY,
content BLOB)

Compressing the resulting SQLite database often results in very small files.



...and if it's going to be a read-only dataset and there's a budget that 
allows a one-time charge of US$2500 to be incurred, I would highly 
recommend HWACI's CEROD extension that encrypts and compresses the data 
store so that to the world outside, it appears to be a "proprietary" 
format while your code sees it as a simple SQLite3 database..


Best Regards,
Mohit.
2/1/2012 | 9:22 PM.


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

Re: [sqlite] search

[Mohit Sindhwani]

On 27/12/2011 9:25 PM, Eduardo Morras wrote:

At 11:27 27/12/2011, you wrote:

Thank you. I agree. It's correct.

I already have data base with /country/state/city/village format. Is it
possible to do that while virtual table creation time?

if yes, how?


Change '/' to ','. This way you get a csv file which you can import 
directly. Perhaps you need to add a line with the table column 
description.




or at the SQLite3 shell do
sqlite>separator /
before doing a .import

no?

Cheers,
Mohit.
27/12/2011 | 10:10 PM.

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

Re: [sqlite] command line to get number of tables in sqlite

[Mohit Sindhwani]

On 22/12/2011 7:25 AM, Matt Young wrote:

select count() from sqlite_master;

No??

...where type='table';




Cheers,
Mohit.
22/12/2011 | 12:51 PM.

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

Re: [sqlite] Cache design using sqlite3...

[Mohit Sindhwani]

On 7/12/2011 6:33 AM, Simon Slavin wrote:

Right.  One way to do it is to have a table in the database used to point to 
the next database.  Normally that table has zero rows in it.  You can check it 
with

SELECT COUNT(*) FROM databaseObsolete

when an entry does appear in it the count switches to 1.  If your operating 
system supports aliases/shortcuts/links you can change one of them to point to 
whatever file is the current database file.

Simon.



This is such a simple, yet elegant, solution!  Thanks, Simon.

Best Regards,
Mohit.
7/12/2011 | 9:35 PM.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS3/FTS4 - Finding the term(s) that completes the input

[Mohit Sindhwani]

Hi Simon, Abhinav and Filip,

On 21/11/2011 11:32 AM, Simon Slavin wrote:

You have to look at the rows it returns and see how many of them there are.  If 
there's only one, that's your hit.  If there are more than one, see how many 
characters you can move along the row before they start to be different.  For 
instance suppose I have

Jones, Jenny
Smith, John
Smith, James
Zbignew, Andrew

Immediately they type 's', by looking at the two rows returned you can move all 
the way along to the 'j' before the strings are different.  The only efficient 
way to do it is programming.


Thanks for your inputs - I think I have enough material now to work 
from.  I should be able to achieve what I'm after based on what you've 
said so far.


Time for me to put in my energy now :-)

Best Regards,
Mohit.
23/11/2011 | 2:39 AM.


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

Re: [sqlite] FTS3/FTS4 - Finding the term(s) that completes the input

[Mohit Sindhwani]

Hi Filip,


On 21/11/2011 4:33 PM, Filip Navara wrote:

On Mon, Nov 21, 2011 at 4:13 AM, Mohit Sindhwani  wrote:


I think my examples muddied the waters.  I have looked at Section 3 of the
FTS documents and that lets me bring back the "full result" that matches -
so, if I search for 'ling j', it can tell me that the result that matches
is 'james ling, alibaba'.  What it does not let me do is figure out that
the partly entered term "j" completes to the word 'james' - that is the
part I'm trying to figure out.

Best Regards,
Mohit.


http://sqlite.org/fts3.html#section_4_1



I am so sure that I read this part so many times... and I always missed 
it!  This will obviously help a lot!


Best Regards,
Mohit.
21/11/2011 | 8:36 PM.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS3/FTS4 - Finding the term(s) that completes the input

[Mohit Sindhwani]

Hi Abhinav,

On 21/11/2011 2:52 AM, Abhinav Upadhyay wrote:

On Mon, Nov 21, 2011 at 12:17 AM, Mohit Sindhwani  wrote:

What I'd like to be able to do is something like this:
- let's say that the FTS4 table has values such as:
* mohit sindhwani, onghu
* john doe, gmail
* james ling, alibaba
* john barn, yahoo
...and so on

If the user enters "j", I'd like to suggest that this would complete to the
words:
john and james in the current set

If the user enters 'ling j', I'd like to restrict it and say:

james is the only word that can be matched now
james ling, alibaba is the result


I think you might want to look at Token Prefix queries:
http://sqlite.org/fts3.html#section_3


I think my examples muddied the waters.  I have looked at Section 3 of 
the FTS documents and that lets me bring back the "full result" that 
matches - so, if I search for 'ling j', it can tell me that the result 
that matches is 'james ling, alibaba'.  What it does not let me do is 
figure out that the partly entered term "j" completes to the word 
'james' - that is the part I'm trying to figure out.


Best Regards,
Mohit.

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

[sqlite] FTS3/FTS4 - Finding the term(s) that completes the input

[Mohit Sindhwani]

Hi, I'm finding my way through FTS3/FTS4 to replace some of the old code 
that we have for searching terms within titles.  I now know that FTS3/4 
should be the way to proceed.


So far, I have this:
- an FTS4 table that has two columns: title (main column), ext (certain 
conditions to match)

- an FTS4aux table

What I'd like to be able to do is something like this:
- let's say that the FTS4 table has values such as:
* mohit sindhwani, onghu
* john doe, gmail
* james ling, alibaba
* john barn, yahoo
...and so on

If the user enters "j", I'd like to suggest that this would complete to 
the words:

john and james in the current set

If the user enters 'ling j', I'd like to restrict it and say:
> james is the only word that can be matched now
> james ling, alibaba is the result

Similarly, if the user enters 'yahoo j', I should be able to zoom in to 
'john barn, yahoo'.


I think this should be within the reach of FTS3/FTS4, but I'm having 
trouble framing the queries, etc.  Are you able to nudge me in the 
correct direction?


Thanks,
Mohit.

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

Re: [sqlite] Can pre-sorted data help?

[Mohit Sindhwani]

Thanks Petite!

On 9/10/2011 10:58 PM, Petite Abeille wrote:

On Oct 9, 2011, at 4:49 PM, Mohit Sindhwani wrote:


Maybe, if we had a column called 'published_date' and we did a query for data 
within a date range.. the fastest way to get the information back would be to 
have an index on that column.  Suppose we sorted all the data by date - would 
there be a way to use that information so that we don't have to create the 
index?

Not really. Alternatively, you could partition your data into different tables 
and then simulate partition pruning at query time. This is not supported 
directly by SQLite, but is straightforward enough to simulate.

http://www.orafaq.com/tuningguide/partition%20prune.html


I'll take a look.


Maybe, I'm worrying about the wrong things :)

Maybe.


Thanks for indulging me, nonetheless :)

Best Regards,
Mohit.
9/10/2011 | 11:04 PM.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can pre-sorted data help?

[Mohit Sindhwani]

Hi Michael and Petite,

Thanks for the replies.


On 9/10/2011 10:08 PM, Petite Abeille wrote:

On Oct 9, 2011, at 1:03 PM, Black, Michael (IS) wrote:


For your example create a separate table with just the first letter and build 
an index on it.

This is most likely pointless as the selectivity of such index is going to be 
very low.


I agree about FTS... we use that too... I'm actually just looking to see 
how we can reduce the indexes that we have.  In a discussion someone 
mentioned 'would it help if we sorted all our data before inserting into 
a table?'  (given that we have a read-only database) and we got the 
feeling that we would probably need to create an index anyway - there is 
probably no way to tell SQLite something like 'the data in this table is 
sorted by the field that we are querying'..


Maybe, if we had a column called 'published_date' and we did a query for 
data within a date range.. the fastest way to get the information back 
would be to have an index on that column.  Suppose we sorted all the 
data by date - would there be a way to use that information so that we 
don't have to create the index?


Maybe, I'm worrying about the wrong things :)

Best Regards,
Mohit.
9/10/2011 | 10:49 PM.


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

Re: [sqlite] Can pre-sorted data help?

[Mohit Sindhwani]

Thanks Richard and Petite,



On 9/10/2011 1:05 AM, Richard Hipp wrote:

On Fri, Oct 7, 2011 at 12:30 PM, Mohit Sindhwani  wrote:


Hi All,

I have been trying to see how we can make one of our databases more space
efficient.


Have you tried increasing the page size?  You seem to currently be using
1024-byte pages.  Try increasing that to 4096 or 8192.  You might get both a
performance increase and a database size reduction.

 PRAGMA page_size=8192; VACUUM;

The VACUUM might take a little while on your database,.



Richard: The page size is indeed changed to 8KB in one of the final 
steps before we pack it up with CEROD - and it does help with the 
database size!


Petite: thanks for pointing that out - we'll drop the index on the 
primary key!


Both these suggestions will help.

I'm still trying to see if we make use of the fact that some data can be 
sorted... I can't find the best example to illustrate the need for it..  
Let me see if I can find something that explains it better.  As an 
example, if we had something like

create table titles (id integer primary key, title text, ...);
could we sort the records by title and use that in some way to restrict 
the search space when searching titles starting with a specific letter?


Thanks,
Mohit.
9/10/2011 | 6:13 PM.




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

Re: [sqlite] Can pre-sorted data help?

[Mohit Sindhwani]

Hi Richard,

On 8/10/2011 12:51 AM, Richard Hipp wrote:

Hi All,

I have been trying to see how we can make one of our databases more space
efficient.  I am now looking at seeing if we have the right indexes and if
there is a way to save space by removing indexes.


Have you run the sqlite3_analyzer.exe utility (available from
http://www.sqlite.org/download.html) on your database to see how efficiently
the data is being stored and which tables are taking up the most space?  Can
you share the output of sqlite3_analyzer.exe with us?


I have run sqlite_analyzer (and I use it all the time to figure out what 
to optimize next!).  The specific database in question has about 80 
tables - let me see if I can extract out a small part to compare.  This 
is from one of the analyses.


*** Table NCX and all its indices ***

Percentage of total database..  15.4%
Number of entries. 2128132
Bytes of storage consumed. 173074432
Bytes of payload.. 139889514   80.8%
Average payload per entry. 65.73
Average unused bytes per entry 9.03
Average fanout 89.00
Fragmentation.   3.6%
Maximum payload per entry. 1083
Entries that use overflow. 90.0%
Index pages used.. 1691
Primary pages used 167318
Overflow pages used... 9
Total pages used.. 169018
Unused bytes on index pages... 207792  12.0%
Unused bytes on primary pages. 1899955911.1%
Unused bytes on overflow pages 850  9.2%
Unused bytes on all pages. 1920820111.1%

*** Table NCX w/o any indices ***

Percentage of total database..  13.8%
Number of entries. 1064066
Bytes of storage consumed. 155700224
Bytes of payload.. 128184788   82.3%
Average payload per entry. 120.47
Average unused bytes per entry 15.91
Average fanout 89.00
Fragmentation.   3.3%
Maximum payload per entry. 1083
Entries that use overflow. 90.0%
Index pages used.. 1691
Primary pages used 150351
Overflow pages used... 9
Total pages used.. 152051
Unused bytes on index pages... 207792  12.0%
Unused bytes on primary pages. 1672587510.9%
Unused bytes on overflow pages 850  9.2%
Unused bytes on all pages. 1693451710.9%

*** Indices of table NCX 

Percentage of total database..   1.5%
Number of entries. 1064066
Bytes of storage consumed. 17374208
Bytes of payload.. 1170472667.4%
Average payload per entry. 11.00
Average unused bytes per entry 2.14
Fragmentation.   5.9%
Maximum payload per entry. 11
Entries that use overflow. 00.0%
Primary pages used 16967
Overflow pages used... 0
Total pages used.. 16967
Unused bytes on primary pages. 2273684 13.1%
Unused bytes on overflow pages 0
Unused bytes on all pages. 2273684 13.1%

The table and its indexes are as follows:
CREATE TABLE ncx (zid INTEGER PRIMARY KEY, str1 TEXT, str2 TEXT);
CREATE INDEX nc_qiid on newconns(zid);

We have many table that have zid (unique) and all will usually have an 
index on zid and (zid,...) for other queries.


I'm not sure if there is enough information here?

Best Regards,
Mohit.
8/10/2011 | 10:41 PM.

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

[sqlite] Can pre-sorted data help?

[Mohit Sindhwani]

Hi All,

I have been trying to see how we can make one of our databases more 
space efficient.  I am now looking at seeing if we have the right 
indexes and if there is a way to save space by removing indexes.


Is there some way to make use of the fact that the data can be sorted by 
a specific column in the desired order before it is imported into the 
table, or do we need to create an index on that column anyway?   
Eliminating the index could save us space.  One of the things in our 
case is that the data is in a read only database (and further 
compressed/ encrypted by CEROD).


Any ideas?

Best Regards,
Mohit.
8/10/2011 | 12:29 AM.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to improve sqlite3 performance

[Mohit Sindhwani]

On 11/9/2011 10:33 PM, template wrote:

Hello,

I have 3 million rows in a read only sqlite3 database (select only).  I am
looking for the best possible lookup performance. Below is what has been
done so far, what else can be done to improve performance of select for
single threaded access via C++ API on Linux 64 bit machin?

1. Load entire database/index loaded into memory using backup api.
2. Index all columns that may be in the where clause
3. Build, prepare and cache in place statements



In general, a lot depends on your query and the structure of your data.  
You should also look at the following:
* Increasing the page size and the number of pages for the cache (will 
matter less if the data is in memory)
* De-normalize - this is not the best way usually, but given that you 
have a read-only database, you should be able to reduce the number of 
JOINs by de-normalizing the database
* Use ANALYZE so that SQLite has a better chance to select the correct 
indexes
* Use EXPLAIN query for queries that are slow and make sure that the 
correct indexes are being used... having too many indexes may not always 
be a good thing since it can result in SQLite picking the wrong index
* See if extensions like RTree and FTS solve any of the problems that 
you are facing (e.g., if you need to search text, don't be using LIKE, etc.)


However, I think a lot depends on how your data is stored, and what the 
queries you plan to run.


Cheers,
Mohit.
12/9/2011 | 2:42 AM.


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

Re: [sqlite] SQLite Cache Usage

[Mohit Sindhwani]

Hi Pavel,

On 7/9/2011 10:16 PM, Pavel Ivanov wrote:

1. If we have a 1MB cache (1000 pages x 1KB/page), is it allocated
immediately and used or is it allocated as queries come in?

Cache is not allocated immediately. It's allocated when SQLite needs
to read something from disk (or write a newly created page). SQLite
reads it and caches.


2. Does the cache keep filling up till it's full, i.e., each new query that
accesses a different page causes the cache to fill up with those pages - or
does it reuse pages when new queries happen even if the cache is not full?

It fills up until it's full.


3. Does the cache store only results or does it store the table data?

Cache stores raw database pages (which can be table data or indexes).
So, I'd say the general rule should be the bigger the database the
bigger the cache should be for a comfortable work.


Thank you for your answers - this gives me enough ideas on managing 
memory in our systems better!


Thanks,
Mohit.
8/9/2011 | 1:28 AM.


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

[sqlite] SQLite Cache Usage

[Mohit Sindhwani]

Hi All,

We are right now reviewing our usage of SQLite3 and are seeing how we 
can optimize its usage of disk space, run-time memory and its 
performance for our applications.  So far, we are discovering a lot of 
new things - and it's very interesting to see things we may have 
overlooked at first glance.  Most of our usage of SQLite3 is in 
read-only databases and we're already using CEROD.


We are now starting to see how the performance of our queries is 
affected by the usage of the cache.  We have the cache designated as 
1000 pages with a page size of 1Kbyte.  Increasing the page size helps 
and increasing the cache size also helps.


However, I am trying to understand how the cache is used.  These are 
some of the questions I have:
1. If we have a 1MB cache (1000 pages x 1KB/page), is it allocated 
immediately and used or is it allocated as queries come in?
2. Does the cache keep filling up till it's full, i.e., each new query 
that accesses a different page causes the cache to fill up with those 
pages - or does it reuse pages when new queries happen even if the cache 
is not full?

3. Does the cache store only results or does it store the table data?

One of the reasons to ask is that currently, we use a few different 
databases and each has the same cache setting.  We are wondering if we 
should tune the cache settings in each of the databases.


Thanks for any help.

Best Regards,
Mohit.
7/9/2011 | 8:01 PM.


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

Re: [sqlite] Unicode Confusion and Database Size

[Mohit Sindhwani]

Hi Simon,

On 4/9/2011 12:01 AM, Simon Slavin wrote:
Have you tried speed tests on your platform ? It's hard to tell which 
will be faster because it depends on what language and OS you're using 
that interacts with SQLite. So if you have your schema designed and 
any part of your application written you could try doing lots of 
information shuffling and see if UTF-8 or UTF-16 is usefully faster 
than the other. Saving space for your database files may be 
interesting, but it's useful only if you're short of space, or need 
faster backups, or something else related. These days most work 
computers have tons of free space on their hard disks.


No, we have not yet done speed tests actually.  We are using SQLite3 
from C/C++ on a Windows CE device... so, space is a premium (as is CPU 
performance actually).  Some of the things we are doing are hitting 1.6 
~ 1.8GB in the databases.  We're already using CEROD to compress the 
databases to bring down the disk requirement and that brings it down by 
around 35%.


We're right now refactoring the database across all its uses and trying 
to bring down the space required while improving the performance... 
we've not decided what to do - just exploring ideas that we've collected 
in the past few weeks.


Cheers,
Mohit.
4/9/2011 | 11:20 PM.

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

Re: [sqlite] Unicode Confusion and Database Size

[Mohit Sindhwani]

Hi Igor,

Thanks for your advice and guidance.

On 1/9/2011 11:57 PM, Igor Tandetnik wrote:

On 9/1/2011 10:24 AM, Mohit Sindhwani wrote:


On the other hand, the other language that we are storing seems to
require 3 bytes in UTF-8. Given that, it would appear that using UTF-8
would be a better idea since it will store more "efficiently".


If you have lots of Chinese (or Japanese or Korean) text to store, 
then UTF-16 might be more compact. For these languages, one character 
takes three bytes in UTF-8 but only two in UTF-16. On the other hand, 
plain ASCII characters take one byte in UTF-8 but still two bytes in 
UTF-16. So if you have a mix of the two, the issue gets murky.






I already have a database that has a couple of tables that are in UTF-8
- is there an easy way for me to build a database from this that is 
UTF-16?


Using sqlite3 command line utility, run .dump command on the old 
database. Create a new database. Use "PRAGMA encoding" to set it to 
UTF-16. Run .import command on it using the dump file from the old one.


I tried what you suggested and for our data, we can get savings in the 
region of 25% - 33% in the case of strings being stored in a language 
that does require 3bytes/ character.  So, given that, we should explore 
UTF-16 in more detail.  However, we also have a lot of text that is only 
in English - so, it seems that we should go down the path of separating 
the data in the two languages and use an ATTACH to bring in the other 
language.  That may be best for our needs.


Thanks for the tips so far!

Best Regards,
Mohit.

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

Re: [sqlite] Unicode Confusion and Database Size

[Mohit Sindhwani]

Hi Igor,

On 1/9/2011 11:57 PM, Igor Tandetnik wrote:

On 9/1/2011 10:24 AM, Mohit Sindhwani wrote:

I understand that the database could be either UTF-8 or UTF-16 - but
that would apply to the full DB not to a single column, right?


Right.



*many useful answers snipped*

Thank you very much!! Your answers are perfect to get me started.  I 
have a couple of tables that are going to have the wider characters - it 
may be that we'll end up splitting the text and FTS tables for that 
language into a separate database and just attach it if we need it.


But at least, I can try this out now.

Thanks,
Mohit.


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

[sqlite] Unicode Confusion and Database Size

[Mohit Sindhwani]

Hi All,

I apologize first if this question has its roots in my partial 
understanding of unicode and the various UTF-encodings.  We're using 
Windows CE and SQLite3 - so far, we have only used ASCII data.  Now, 
we're going to store data in other languages and feel the need to go 
towards unicode in SQLite3.


I understand that the database could be either UTF-8 or UTF-16 - but 
that would apply to the full DB not to a single column, right?  If that 
is the case, would it not make the database larger if we had a lot of 
content that was originally ASCII?


On the other hand, the other language that we are storing seems to 
require 3 bytes in UTF-8.  Given that, it would appear that using UTF-8 
would be a better idea since it will store more "efficiently".


In addition, there are a few other questions:
- FTS would work fine on both UTF-8 and UTF-16 databases, right?
- Can we attach two databases that have different encodings?
- When using Wide Strings in Windows CE, is one encoding more preferable 
over the other to minimize conversions?


I already have a database that has a couple of tables that are in UTF-8 
- is there an easy way for me to build a database from this that is UTF-16?


I'm sorry for the many questions - thanks for the help.

Best Regards,
Mohit.
1/9/2011 | 10:24 PM.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Text searching

[Mohit Sindhwani]

We have an interesting problem here that has me at my wit's end - so, I 
thought I'd ask for advice.

We have a list of words that we want to quickly search.  We used FTS3 
for it and it is fast and works very well.  The only problem is that it 
breaks the original entry at word boundaries (spaces, punctuation, 
etc.).  We would like to allow a person to search starting within a 
word.  So, we want to do something that gives FTS like speed for queries 
like "LIKE '%s%'

What would you recommend?  We've tried a few things, none of them 
elegant.. yet!  The original table has around 650,000 names (2 - 5 words 
per name).

Then, we want to be able to suggest what characters a user can type once 
he's already entered something - So, if he's already entered 'In', we'd 
like to suggest that he can only type 'D' (for India or Indonesia) or 
'T' for International.  Previously, we used substr because everything 
started from the start of the name being searched.  but, FTS now allows 
us to search from any word in the sentence.  I'm stuck trying to figure 
out an elegant way of supporting this.

This is further complicated by allowing the user to start from anywhere 
in the middle of a word...

Any suggestions?  I'm really stuck now!  Thanks :)

Best Regards,
Mohit.
6/8/2011 | 12:00 AM.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[Mohit Sindhwani]

On 1/7/2011 5:37 PM, Martin.Engelschalk wrote:
> Hi,
>
> i apologize beforehand if my post does not answer your question
> directly. It seems to me that you may be missing a basic concept.
>
> Data in an SQL table is never sorted in itself. So, you can not sort a
> table before you query it.

SQLite does give you the ability to sort by _rowid_ which returns the 
data in the manner that it was stored into the table.  The idea of 
sorting the data before doing the query doesn't map to SQL very well... 
and is probably not required.

Best Regards,
Mohit.
1/7/2011 | 6:14 PM.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Selecting indexes to use & NOT INDEXED

[Mohit Sindhwani]

Hi Igor and Puneet,

On 27/6/2011 11:47 PM, Igor Tandetnik wrote:
> You can suppress the index on CAT with a unary plus operator, like this:
> ... AND +CAT=25;

Thanks for the suggestions.  I'll try these :)

Best Regards,
Mohit.
28/6/2011 | 11:50 PM.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Selecting indexes to use & NOT INDEXED

[Mohit Sindhwani]

Hi All,

Sorry for the long email, but the background is probably needed :)

1. We have a geographical query that is supported by an R-Tree index.  A 
join is required to filter by category while restricting by the bounding 
box.  On the main table (objects), we have an index IDX_OBJ_CAT on 
Objects(cat) and we have a virtual index on the RTree table (Objects_Index).

We felt that the query was a bit slow:

select * FROM Objects, Objects_Index
WHERE Objects.id = Objects_Index.id
 AND minx <= 668632 + 250 AND maxx >= 668632 - 250
 AND  miny <= 1518661 + 250 AND maxy >= 1518661 - 250
 AND CAT=25;

Doing an explain query plan revealed that SQlite was scanning Objects 
with the IDX_OBJ_CAT first and then using the R-Tree.  Without 
restricting by CAT, we found the query was much faster and was using 
only the virtual R-Tree index.

We wanted to get SQLite to use the R-Tree first, so we tried a few 
different things - eventually, we killed the IDX_OBJ_CAT index and the 
query became (yes, wait for it) almost 100x faster!

So, now we want to go about fixing our schema and queries to benefit 
from this.

What do you recommend?  What would be the best way to make SQLite3 use 
the R-Tree?  We're a bit unsure about dropping the category index since 
we are not sure which other queries will be affected.

2. While searching around, I found INDEXED BY and NOT INDEXED and NOT 
INDEXED worked very well for this query.  But, an email from 2009 said: 
"many are of the opinion that this feature is prone to misuse."

> There is the "INDEXED BY" clause. But many are of the opinion that
> this feature is prone to misuse.
>
> http://www.sqlite.org/lang_indexedby.html
>
> Dan.

What's the current wisdom?

Thanks,
Mohit.



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

Re: [sqlite] Ideas for Searching Names in Contacts

[Mohit Sindhwani]

On 24/2/2011 1:49 AM, Simon Slavin wrote:
> I would not try to make each individual word of a name a row in a table.  I 
> think 'LIKE' is designed almost exactly for your problem:
>
> 
>
> This means you can search on any component of the name.  for instance,
>
> SELECT id,name FROM contacts WHERE name LIKE '%ell%'
>
> will find all the 'Kelly' entries and also all the 'Ella' entries.  Similarly
>
>
> SELECT id,name FROM contacts WHERE name LIKE '%Simon Kelly%'
>
> Would return 'Simon Kelly Smith' and 'Simon Kelly Grant' and 'Eric Simon 
> Kelly' and even 'Simon Kellyson' and 'Jossimon Kellysen'.

Hi Simon and Simon,

Thank you for your suggestions - I tried the approach using LIKE and it 
seemed quite slow (this was on Windows CE).. FTS3/4 seem good to explore.

Cheers,
Mohit.
24/2/2011 | 11:23 PM.

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

[sqlite] Ideas for Searching Names in Contacts

[Mohit Sindhwani]

I am trying to build a simple contacts database in SQLite.

I want people to be able to search starting with any word of the name.

So, if I have:
1,John Smith
2,Simon James
3,Simon Kelly Smith
4,Jimmy Garcia
5,Smith White Jones
6,Simon Kelly Grant
...

If a user types "Smi", he should get the following results:
John Smith
Simon Kelly Smith
Smith White

I am confused as to how to go about structuring this database.  So far, 
all I have is that I could split all the names into individual words and 
then create a join table that joins every single word with every contact 
that matches  it.  So, I would have something like:
Smith,1
Smith,3
Smith,5

This handles single-word cases... but if he types in "Simon Kelly", I'd 
like to return:
Simon Kelly Smith
Simon Kelly Grant

Any suggestions on how I should handle and model this requirement?

Thanks,
Mohit.



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

Re: [sqlite] Optimizing Database Size

[Mohit Sindhwani]

Hi Simon,

>> 1. We are already using CEROD, so the final database is compressed.
>>
>> 2. We ran the sqlite_analyzer and identified the tables that take up a
>> lot of space and are now re-organizing the schema.  One thing I observed
>> is that we have a number of tables that have the same primary key
>> (o_id).  Each table has exactly the same number of entries and the a
>> record corresponding to a particular o_id is stored at the same _row_id_
>> in each table.  I was wondering if there is a way to use this
>> information to remove some of the indexes without affecting the query
>> speed much?
> The best way to save space on that would be to merge the tables into one.

Thanks for the reply.  We are a bit concerned that merging everything 
into a single table will increase the query times further... especially 
because we have blob fields in the tables too.

I guess we will need to:
* Merge all always-populated fields into a single table
* Separate sparsely populated fields into a separate table
* Separate blobs into different table(s)

Cheers,
Mohit.
3/2/2011 | 11:51 AM.

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

[sqlite] Optimizing Database Size

[Mohit Sindhwani]

Hi All,

I'm facing an issue where we want to make the database size smaller than 
it is.

1. We are already using CEROD, so the final database is compressed.

2. We ran the sqlite_analyzer and identified the tables that take up a 
lot of space and are now re-organizing the schema.  One thing I observed 
is that we have a number of tables that have the same primary key 
(o_id).  Each table has exactly the same number of entries and the a 
record corresponding to a particular o_id is stored at the same _row_id_ 
in each table.  I was wondering if there is a way to use this 
information to remove some of the indexes without affecting the query 
speed much?

3. We are also storing multiple indexes on the same table to accommodate 
the different queries that exist.  We are re-organizing the indexes 
again to ensure that we have indexes in the correct sequence to reduce 
the number of indexes we need to store.

4. Any other tips to keep the database smaller?

Best Regards,
Mohit.
3/2/2011 | 10:55 AM.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Confused about Multiple Indexes

[Mohit Sindhwani]

Hi Max

Thanks for the reply.

On 26/11/2010 7:11 PM, Max Vlasov wrote:
> Mohit said that he uses a someone's db, so I can imagine a possibility that
> with two indexes ...
> CREATE INDEX IDX1 on tx(name ASC);
> CREATE INDEX IDX2 on tx(type, name ASC);
> ... the creator of database wanted to search for a name regardless of the
> type with a help of first index, and in the context of chosen type with the
> second. So, Mohit, if some unknown queries or code involved I'd better leave
> them as they are
I have access to all the queries that are planned - I will confirm that 
the points Swithun mentioned are covered.  If I remember correctly, he 
searches for all entries that have a certain kind of name, or all 
entries that are a certain type and have a certain kind of name.

Best Regards,
Mohit.
27/11/2010 | 12:41 AM.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Confused about Multiple Indexes

[Mohit Sindhwani]

On 26/11/2010 6:34 PM, Swithun Crowe wrote:
> Hello
>
> CREATE INDEX idx ON tx(name ASC, type);
>
> With the columns in this order (name followed by type), the index will be
> used for queries which have either just name, or both name and type in
> their WHERE clauses.

Swithun, thank you very much for the clear explanation.

Best Regards,
Mohit.
27/11/2010 | 12:35 AM.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Confused about Multiple Indexes

[Mohit Sindhwani]

Hi Swithun

Thank you for your reply.  I'm sorry I was simplifying the schema when I 
sent it out.

On 26/11/2010 5:35 PM, Swithun Crowe wrote:
> Hello
>
> MS>  CREATE TABLE tx (name TEXT, type INTEGER, seq INTEGER, seq_record TEXT,
> MS>  ...);
>
> MS>  CREATE INDEX IDX1 on tx(name ASC);
> MS>  CREATE INDEX IDX2 on tx(type, search_name ASC);
>
> The two indexes cover different columns, so they do different things. The
> indexes you need depend on the queries you will be doing, so there is no
> way I can tell if you need these indexes. Columns which are used in WHERE
> clauses are good candidates for indexing, generally speaking.
>
> You don't show a column called search_name in the CREATE TABLE line. I
> assume there is one.

The second index should be:
CREATE INDEX IDX2 on tx(type, name ASC);
What I had meant to ask was whether there is any benefit in having two 
indexes when one of the indexes is exactly within the other.

IDX1 is index on 'name ASC' while IDX2 is an index on 'type, name ASC' - 
does this mean that in a sense IDX1 is a subset of IDX2 and can be removed?

> A more specific answer would require more information, but I hope this
> helps.

I apologize for the mistake I made in sending out the schema.  Thanks 
again for taking the time to answer.

Best Regards,
Mohit.
26/11/2010 | 5:58 PM.


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

[sqlite] Confused about Multiple Indexes

[Mohit Sindhwani]

Hi All,

I am looking at a database that someone created earlier and for one of 
the table, the schema shows:
CREATE TABLE tx (name TEXT, type INTEGER, seq INTEGER, seq_record TEXT, 
...);
CREATE INDEX IDX1 on tx(name ASC);
CREATE INDEX IDX2 on tx(type, search_name ASC);

I am under the impression that IDX1 is not necessary in this case.  
Would there be any (significant) benefit in retaining that index?

Thanks,
Mohit.
26/11/2010 | 2:54 PM.

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

Re: [sqlite] Books which cover C API

[Mohit Sindhwani]

On 8/7/2010 8:17 AM, Tito Ciuro wrote:
> Yes. The book is quite good, I admit, but if you need to find specific 
> things... the answer might not be referenced where you think it'd be. Or not 
> mentioned at all. Is it really *that* difficult? I truly hope they pay 
> attention to this whenever in future releases.
>

To be fair, I read the book cover to cover when I started out on 
SQLite... so, I'm probably more familiar with where the content that I 
need is.

Best Regards,
Mohit.
8/7/2010 | 8:21 AM.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users