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

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

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

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

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

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

[sqlite] website documentation wording

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

[sqlite] attempt at output with thousands separator via extension

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

[sqlite] Tables and Columns of Database of Whatsapp

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

[sqlite] full table scan ignores PK sort order?

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

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

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

Re: [sqlite] Partial index to find maximum

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,

Re: [sqlite] Without ROWID and Autoincrement question

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

Re: [sqlite] CRUD Statistics

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

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

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

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

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

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

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

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

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

[sqlite] R-Tree Storage Optimization for Points

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

Re: [sqlite] Best compression for sqlite3 ?

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

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

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,

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

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,

Re: [sqlite] Happy New Year

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!

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

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.

Re: [sqlite] Bug in sqlite.exe?

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

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

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

Re: [sqlite] Big number of tables

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

Re: [sqlite] Out of memory error

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

Re: [sqlite] Out of memory error

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

[sqlite] Out of memory error

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

Re: [sqlite] to encrypt sqlite db

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

Re: [sqlite] to encrypt sqlite db

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:

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

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

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

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 <m...@onghu.com> wrote: We could try to renumber the IDs so that all the IDs are in sequence, but that is not the easiest thing

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

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

Re: [sqlite] SEE + CEROD

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

[sqlite] SEE + CEROD

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

Re: [sqlite] RTree Documentation Error?

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:

[sqlite] RTree Documentation Error?

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

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

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 <m...@onghu.com> wrote: Hi, we are using SQLite3 + CEROD for a number of databases in an embedded systems application running on W

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

Hi Richard, On 14/3/2013 8:17 PM, Richard Hipp wrote: On Thu, Mar 14, 2013 at 12:34 AM, Mohit Sindhwani <m...@onghu.com> 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 t

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

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

[sqlite] Slow sqlite3_open() - possible culprits?

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:

Re: [sqlite] Run sqlite from a batch file

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

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

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

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

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:

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

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:

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

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

Re: [sqlite] Setting initial database size.

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

Re: [sqlite] Setting initial database size.

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

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

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

Re: [sqlite] SQLite support on windows

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

Re: [sqlite] SQLite support on windows

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

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

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);

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

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

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

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

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

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

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

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):

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

Hi Igor, On 29/5/2012 10:52 AM, Igor Tandetnik wrote: Mohit Sindhwani<m...@onghu.com> 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 ob

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

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.

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

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,

Re: [sqlite] Memory Usage/ Drawbacks of Statements

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

Re: [sqlite] Memory Usage/ Drawbacks of Statements

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

[sqlite] Memory Usage/ Drawbacks of Statements

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)

Re: [sqlite] Using FTS for '='

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

[sqlite] Using FTS for '='

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

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

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')

Re: [sqlite] Found it

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

Re: [sqlite] replying to thread

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.

Re: [sqlite] Efficient usage of sqlite

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,

Re: [sqlite] search

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

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

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

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

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

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

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

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

Hi Filip, On 21/11/2011 4:33 PM, Filip Navara wrote: On Mon, Nov 21, 2011 at 4:13 AM, Mohit Sindhwani<m...@onghu.com> 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 -

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

Hi Abhinav, On 21/11/2011 2:52 AM, Abhinav Upadhyay wrote: On Mon, Nov 21, 2011 at 12:17 AM, Mohit Sindhwani<m...@onghu.com> 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 *

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

) - 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 woul

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

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

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

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

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

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<m...@onghu.com> 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? Yo

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

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

[sqlite] Can pre-sorted data help?

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

Re: [sqlite] SQLite Cache Usage

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

[sqlite] SQLite Cache Usage

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

Re: [sqlite] Unicode Confusion and Database Size

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

Re: [sqlite] Unicode Confusion and Database Size

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

Re: [sqlite] Unicode Confusion and Database Size

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

[sqlite] Unicode Confusion and Database Size

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

[sqlite] Text searching

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

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

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.

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

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] Selecting indexes to use & NOT INDEXED

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

Re: [sqlite] Ideas for Searching Names in Contacts

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

[sqlite] Ideas for Searching Names in Contacts

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

Re: [sqlite] Optimizing Database Size

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

[sqlite] Optimizing Database Size

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

Re: [sqlite] Confused about Multiple Indexes

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

Re: [sqlite] Confused about Multiple Indexes

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

Re: [sqlite] Confused about Multiple Indexes

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>

[sqlite] Confused about Multiple Indexes

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

Re: [sqlite] Books which cover C API

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

Re: [sqlite] Books which cover C API

On 8/7/2010 8:06 AM, Tito Ciuro wrote: > On 8 Jul 2010, at 01:58, Mohit Sindhwani<m...@onghu.com> wrote: > > >> On 8/7/2010 2:55 AM, Jay A. Kreibich wrote: >> >>> On Wed, Jul 07, 2010 at 07:45:02PM +0100, Andrew Wood scratched on the wall: >

Re: [sqlite] Books which cover C API

On 8/7/2010 2:55 AM, Jay A. Kreibich wrote: > On Wed, Jul 07, 2010 at 07:45:02PM +0100, Andrew Wood scratched on the wall: > >> Which of the books on the market is the best for covering the C API? >> >http://sqlite.org/books.html > > > > >If you want to go out and buy something

  1   2   >