[sqlite] Different timings on min() and max()

On Tue, Apr 7, 2015 at 1:42 PM, Paul Caskey wrote: > Any idea why there is such a performance hit when I ask for both min() and > max() at the same time? Shouldn't it be just as fast as querying them > individually? This has come up before. Either alone can be satisfied from an index lookup,

[sqlite] fts5

On Thu, Sep 11, 2014 at 8:58 AM, Dan Kennedy wrote: > Fts5 is still in the experimental stage at the moment. > > If anybody has any ideas for useful features, or knows of problems with FTS4 > that could be fixed in FTS5, don't keep them to yourself! Apologies for not noticing this thread

[sqlite] fts5

On Wed, Apr 8, 2015 at 12:32 PM, Dan Kennedy wrote: > On 04/08/2015 04:49 AM, Scott Hess wrote: >> Something that bugged me a lot was that I had used deletion markers to >> cancel out hits, but did not provide a way for deletion markers to >> cancel o

[sqlite] Best way to temporarily store data before processing

On Tue, Apr 14, 2015 at 9:37 AM, Jim Callahan wrote: > My recollection is that SQLite has a "temp" or "tmp" namespace available > for intermediate tables -- it was on my todo list, but I never got around > to exploring that option. CREATE TEMP TABLE ... works very well for things like this. If

[sqlite] Best way to temporarily store data before processing

On Tue, Apr 14, 2015 at 1:10 PM, David Cotter wrote: > does this awesome sounding temp table then merge instantly when you commit it > even if it?s huge? Since I have no idea what "merge" means in this context, it's hard to answer your question. -scott

[sqlite] Destroy all evidence of a database

The only way SQLite can get to the disk is using the vfs, so if the vfs encrypts things, all of the files (main db, temp db, journal, everything) will be encrypted. I think in your case you can probably even get away without the more elaborate encrypted systems, because it sounds like you don't

[sqlite] Destroy all evidence of a database

On Wed, Apr 22, 2015 at 12:07 PM, Simon Slavin wrote: > On 22 Apr 2015, at 7:18pm, Scott Hess wrote: >> The only way SQLite can get to the disk is using the vfs, so if the >> vfs encrypts things, all of the files (main db, temp db, journal, >> everything) will be encry

[sqlite] Thoughts on storing arrays of complex numbers (Solved)

On Fri, Apr 24, 2015 at 12:01 PM, Drago, William @ CSG - NARDA-MITEQ wrote: > Since the data is received from the analyzer as an array of > real/imaginary pairs (R,I,R,I,R,I,R,I...), 3202 elements total, > that's how I will blob and store it. This is the simplest way > to add it to the database.

[sqlite] Read data from database slow the first time but then very fast thereafter. How can I improve the speed for the first time read?

On Tue, Aug 4, 2015 at 9:23 AM, John McKown wrote: > On Tue, Aug 4, 2015 at 10:45 AM, Simon Slavin > wrote: > > On 3 Aug 2015, at 1:58pm, Linquan Bai wrote: > > > I am trying to read large data from the database about 1 million > records. > > > It takes around 1min for the first time read. But

[sqlite] Determine query type

Also consider https://www.sqlite.org/c3ref/stmt_readonly.html -scott On Thu, Aug 6, 2015 at 10:08 AM, Stephan Beal wrote: > On Thu, Aug 6, 2015 at 6:57 PM, Ben Newberg wrote: > > > Excellent. This is exactly what I was looking for. > > > > Great :). Now that i have some code in front of me i

[sqlite] Compile warnings

Yeah, we saw this with Chromium, too. The patch we use is below. I'm with Dr Hipp that this is really more of a GCC issue. If it was literally a 0 constant, it would make sense to warn so that the code can be removed. But it's only a 0 if you optimize a certain way. -scott diff --git

[sqlite] Is this a bug ? Pragmas do not accept qualified names !

I think you wanted: PRAGMA attached_db.table_info(one_table); -scott On Fri, Aug 21, 2015 at 7:35 AM, sqlite-mail wrote: > Hello ! > > Today I'm working with sqlite3 with attached databases and when I tried to > get info about a tbale using "PRAGMA TABLE_INFO" I discovered that pragmas > do

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

On Fri, Aug 21, 2015 at 11:06 AM, Simon Slavin wrote: > On 21 Aug 2015, at 7:02pm, sqlite-mail wrote: > > I'm pointing this here because postgresql do manage this case properly ! > > If you want postgres, you know where to find it. > > Please don't forget that SQLite has to run on your

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

On Fri, Aug 21, 2015 at 1:02 PM, Simon Slavin wrote: > On 21 Aug 2015, at 8:13pm, Scott Hess wrote: > > Since renameTriggerFunc() > > follows renameParentFunc(), my guess is that triggers are also handled. > > The documentation says that statements within TRIGGERs are no

[sqlite] order by not working in combination with random()

I keep thinking I remember a thread from years ago where a lot of this was hashed out, but I cannot find it. I seem to remember one point which made sense was that while most functions with no parameters were reasonably considered static across the entire statement's execution, RANDOM() needed to

[sqlite] FTS4 - simulate phrase with NEAR/0

On Thu, Aug 27, 2015 at 10:50 AM, Martin Kucej < i.librarian.software at gmail.com> wrote: > Recently, I was asked to implement full-text search in an application > with up to 1 million items, each with several columns having AND, OR > and a phrase search capabilities. I can only work with FTS4,

[sqlite] FTS4 - simulate phrase with NEAR/0

On Thu, Aug 27, 2015 at 11:29 AM, Martin Kucej < i.librarian.software at gmail.com> wrote: > On Thu, Aug 27, 2015 at 1:02 PM, Scott Hess wrote: > > NEAR/0 will probably not care about ordering. > > Ah, yes. You are correct. This match expression: > > MATCH 'column:wor

[sqlite] TEXT columns with Excel/VBA

On Wed, Dec 2, 2015 at 4:29 PM, R Smith wrote: > > Personally I use VARCHAR(Len) in table column definitions - simply because > my schema is then directly interchangeable with MySQL/PostGres and the > SQLite query planner sometimes notes that length when considering data > shape - but for data

[sqlite] TEXT columns with Excel/VBA

On Thu, Dec 3, 2015 at 4:49 AM, R Smith wrote: > On 2015/12/03 3:04 AM, Scott Hess wrote: > >> I discourage this kind of usage because it means that in some distant >> future when someone has to make things work with a different database >> engine, they have to grind

[sqlite] pragma temp_store_directory is deprecated, what is the alternative?

Passing NULL to xOpen()'s zName parameter opens a temp file. -scott On Thu, Jul 30, 2015 at 4:29 PM, Howard Kapustein < Howard.Kapustein at microsoft.com> wrote: > >There cannot be a fully portable way, because path specifications are not > portable > Which begs the question, why isn't there

[sqlite] Should journal_mode=PERSIST plus secure_delete=ON be allowed?

Someone just pointed something out which basically comes down to if you're using these two together: PRAGMA secure_delete=ON; PRAGMA journal_mode=PERSIST; then the first makes sure that evidence of deleted data should be missing from the main database file, but the second can leak such

[sqlite] User-defined types

On Thu, Jun 4, 2015 at 1:54 AM, Dominique Devienne wrote: > On Thu, Jun 4, 2015 at 10:20 AM, Christopher Vance > wrote: >> If you really want your own types, you could always bundle with ASN.1 and >> store the result as a blob. > > Or Protobuf, or ... But you're back to option 1, you must

[sqlite] Mozilla wiki 'avoid SQLite'

For various reasons I've ended up as the SQLite rep w/in Chromium, and I bookmarked that page awhile back to periodically revisit. People often seem to believe that SQLite magically solves tons of problems with their persistence layer, without realizing that many of their assumptions are based on

[sqlite] Mozilla wiki 'avoid SQLite'

On Sun, Jun 14, 2015 at 1:19 PM, Richard Hipp wrote: > On 6/14/15, Scott Hess wrote: >> SQLite essentially gives you a set of >> b-trees with syntactic sugar over them, > > SQL (and I speak in general terms here, not just of SQLite) provides > way more than synt

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

On Tue, Mar 3, 2015 at 3:33 AM, Paolo Bolzoni wrote: >> I can't confirm that 100% off the top of my head but I'm uncoordinated >> enough to repeatedly confuse the bind and column value API calls and use >> 0-based indices for both and haven't noticed any really untoward behaviour >> (beyond my

[sqlite] Corrupted database

On Wed, Mar 4, 2015 at 1:11 AM, Alexandr N?mec wrote: > we have a product that uses SQLite. Because it was running very stable for > years, we are still using SQLite 3.7.17. Now we've seen on one of our > installations that the database has been corrupted, we saw that there was a > power failure

[sqlite] Memory leak?

On Fri, Mar 13, 2015 at 3:13 AM, Matthias Schmitt wrote: >> On 12 Mar 2015, at 17:35, Richard Hipp wrote: >> >> Let's start with the basics: How do you know that the memory was in >> fact leaked and is not instead simply being held for reuse? > > The Xcode development environment comes with a

[sqlite] It might be nice to have sqlite3_attach() and sqlite3_detach().

I'm thinking I could use something like: SQLITE_API int sqlite3_attach(sqlite3* db, const char* zPath, const char* dbname); SQLITE_API int sqlite3_detach(sqlite3* db, const char* dbname); Right now, I have a helper in Chromium which does "ATTACH DATABASE ? AS ?". This works, but AFAICT this

[sqlite] It might be nice to have sqlite3_attach() and sqlite3_detach().

On Thu, Mar 19, 2015 at 10:30 AM, Richard Hipp wrote: > On 3/18/15, Scott Hess wrote: >> I'm thinking I could use something like: >> >> SQLITE_API int sqlite3_attach(sqlite3* db, const char* zPath, const >> char* dbname); >> SQLITE_API int sqlite3_detac

[sqlite] saving :memory:database to disk

On Thu, May 7, 2015 at 10:53 AM, Paul Sanderson wrote: > I am contemplating a change to a program whereby a database is > initailly created in memory and then later if my users choose they can > save it to disk and then switch to using the disk based DB. > > I can obviously create a new disk

[sqlite] saving :memory:database to disk

On Thu, May 7, 2015 at 11:03 AM, Scott Hess wrote: > On Thu, May 7, 2015 at 10:53 AM, Paul Sanderson gmail.com> wrote: >> I am contemplating a change to a program whereby a database is >> initailly created in memory and then later if my users choose they can >> save it

[sqlite] Determine type of prepared statement via C Interface?

sqlite3_stmt_readonly(stmt)? This hits INSERT/UPDATE/DELETE, but not BEGIN/COMMIT/ROLLBACK. Or sqlite3_sql(stmt) if you want to do it heuristically by inspecting the statement. I think a "BEGIN READONLY" would be a sensible transaction type. Having a wrapper API force the developer to select

[sqlite] Determine type of prepared statement via C Interface?

On Fri, May 8, 2015 at 11:20 AM, Simon Slavin wrote: > On 8 May 2015, at 6:43pm, Peter Aronson wrote: >> Well, there's sqlite3_stmt_readonly which appears to do pretty much what >> you're asking for: https://www.sqlite.org/c3ref/stmt_readonly.html. > > Suppose you have this statement > > DELETE

Re: [sqlite] Optimizing list retrieval with a FTS3 table

On Wed, Mar 16, 2011 at 12:15 PM, Travis Orr wrote: > Can someone enlighten me about this. To me a lot of the details appear > to be hidden since my main SongTable is a FTS3 virtual table. You don't provide your schema, but based on your queries, I'll make unwarranted assumptions

[sqlite] WAL checkpoints versus fsync.

[From a thread about WAL checkpoints...] On Wed, Mar 30, 2011 at 6:28 AM, Richard Hipp wrote: > On Wed, Mar 30, 2011 at 8:46 AM, Mikael wrote: >> Therefore I now wish to check with you, is SQLite implemented to somehow >> make checkpoints not be able to

Re: [sqlite] Page Size with NAND FLASH

On Tue, May 3, 2011 at 4:44 PM, Sugathan, Rupesh wrote: > I am planning to use sqlite on a Linux system with JFFS2 file system on > NAND flash. NAND device that I am using has page size of 2048 bytes and > a erase sector size of 128K. I would like to take advantage of sqlite

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

On Mon, Jun 6, 2011 at 1:46 PM, Nico Williams wrote: > I can see two ways to add proper IEEE754 support in a > backwards-compatible way: a) via a pragma to enable raising exceptions > or returning +-inf/NaNs, b) by adding a callback via which to report > such exceptions,

[sqlite] Bug with fts3 when using a restrictive authorizer.

If the authorizer prevents PRAGMA, then fts3 fails with an SQLITE_AUTH error from fts3DatabasePageSize(). http://crbug.com/85522 is the Chromium bug where this originated (I upgraded the Chromium SQLite version a few weeks back). http://codereview.chromium.org/7230021 is the short-term patch I'm

Re: [sqlite] How to convert a database with FTS2 to FTS3/4?

On Fri, Jul 29, 2011 at 10:33 PM, Ray Van Dolson wrote: > sqlite newbie here.  I have a sqlite3 database that was built using the > FTS2 module.  As such I get errors about the non-existent fts2 module > when attempting to query the data. > > Is there a way to convert/migrate

Re: [sqlite] Fwd: Case Sensitive FTS searches.

On Thu, Aug 11, 2011 at 8:57 AM, Dennis Suehr wrote: > After some digging through the sqlite3 source code, I came across the code > for the ICU tokenizer.  After enabling that and then commenting out the one > line where u_foldCase() is called, i.e. icuOpen(), I retested and >

Re: [sqlite] How to reindex an FTS3 table after changing the tokenizer

On Fri, Aug 12, 2011 at 11:27 AM, john Papier wrote: > I have a FTS3 table that was created with the simple tokenizer. I want to > change the tokenizer and reindex the table. > > Is there a way to change the tokenizer in place and have it reindex with > minimal code? > >

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

On Tue, Sep 27, 2011 at 11:18 AM, Gabor Grothendieck < ggrothendi...@gmail.com> wrote: > On Tue, Sep 27, 2011 at 2:14 PM, David Garfield > wrote: > > Any entry in a pipe could be buffering. In a quick test here, awk is > > buffering. To find the buffering, try

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

On Tue, Sep 27, 2011 at 2:07 PM, Patrick Proniewski wrote: > On 27 sept. 2011, at 20:18, Gabor Grothendieck wrote: > > gawk has fflush() > > none of them is available out of the box on Mac OS X, or FreeBSD. gawk can > be installed, but I'd rather use my "while true" loop

Re: [sqlite] FTS vs INDEX

On Wed, Oct 19, 2011 at 7:56 AM, Fabian wrote: > 2011/10/19 Alexey Pechnikov >> FTS use index multi-tree and de-facto has _no_ insert speed degradation. > > Thanks, that's good to hear! It makes me wonder why SQLite doesn't use that > same

Re: [sqlite] FTS vs INDEX

On Wed, Oct 19, 2011 at 12:50 PM, Fabian <fabianpi...@gmail.com> wrote: > 2011/10/19 Scott Hess <sh...@google.com> >> To be clear, how it works is that new insertions are batched into a >> new index tree, with index trees periodically aggregated to keep >> selecti

Re: [sqlite] cache size and insert-only

On Fri, Oct 28, 2011 at 5:36 AM, Jay A. Kreibich wrote: > On Fri, Oct 28, 2011 at 01:33:31PM +0200, Gert Corthout scratched on the wall: >> we have a database that only performs insert statements on a table >> (database contains only this table), is it in this case a good idea >>

Re: [sqlite] Readonly error when copying to mem-based db

On Mon, Nov 7, 2011 at 9:02 AM, Mills, Steve wrote: >                if(pageSize > 0) { >                        SqliteStatement  cmd("PRAGMA page_size = ?", *this); > >                        cmd.BindToInt(1, syz); >                        cmd.Step(); >                }

Re: [sqlite] Segment merging in FTS and updates-deletes

On Fri, Nov 11, 2011 at 9:58 AM, nobre wrote: > Hi, I'm studying the indexing mechanism of FTS3/4, I can pretty much > understand how doclists, terms, segments are created and stored, but one > thing I can't grasp is about updating and deleting docs and keeping up

[sqlite] Random performance issues with SQLite

Also look for failures at the bus level. I have had cases where some component was mucking up the bus, and got long pauses because the OS kept resetting the bus. These caused huge pauses, like 30 seconds or more, and due to how various OS components mostly do synchronous disk access, that kind

[sqlite] Array or set type or some other indexable data?

On Thu, Nov 12, 2015 at 4:52 PM, J Decker wrote: > > So something like "select value from option_map_view where path is > > set type>"? > > A path name like '/system/device/com port/1' is used as an array of names > > here. Only the indexing with intst and substr is laborious. Maybe some > >

[sqlite] Retrieving the table info fails

On Mon, Nov 16, 2015 at 11:20 AM, R Smith wrote: > On 2015/11/16 7:59 PM, Igor Korot wrote: >> >> BTW, are only name, type and pk fields are guaranteed to have a value? >> > > Nothing is guaranteed to have a value unless created with NOT NULL in the > field specification in the CREATE TABLE

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

On Wed, Nov 18, 2015 at 3:22 PM, Yuri wrote: > On 11/18/2015 09:55, R Smith wrote: > >> There is no "first" constraint that can fail. There is a procession of >> constraints either within a statement or within a transaction (both can >> have many constraints) and as they are required, they are

[sqlite] Speed of count distinct rows if unique index on all fields

On Fri, Oct 2, 2015 at 7:43 AM, Bart Smissaert wrote: > > The Uniqueness of the output depends on which fields are included, JOINs, > UNIONs, etc. etc. > > I am not talking about that situation. I am only referring to a situation > where you want to count all > rows in a table. I know it will be

[sqlite] Speed of count distinct rows if unique index on all fields

wrote: > It is faster because if it knows there is no where or join or whatever row > limiting condition and it also knows there is > a unique index on all fields it can simply do select count(rowid) from > table1 and not do any count distinct. > > RBS > > > On Fri, Oc

[sqlite] Speed of count distinct rows if unique index on all fields

On Fri, Oct 2, 2015 at 8:41 AM, Bart Smissaert wrote: > > you're just throwing random terms around and hoping something sticks. > > Not sure where you got that idea from, but let me explain better: > AFAICT this is the first posting where you said "I want to count all the unique rows of this

[sqlite] Multiple connections to in-memory DB and virtual tables

Your thread made me ponder what might be up, so I wrote a test using fts3: http://pastebin.com/AKP2yHuM and AFAICT, it works alright. I haven't specifically verified each of the flags to sqlite3_open_v2(), I just spammed what looked relevant in there. Hmm, should have commented the #if's

[sqlite] [sqlite-dev] SQLite version 3.8.12 enters testing

On Wed, Oct 7, 2015 at 9:05 AM, Dominique Devienne wrote: > On Wed, Oct 7, 2015 at 5:39 PM, Richard Hipp wrote: > > On 10/7/15, Jaroslaw Staniek wrote: > > > ? would you elaborate what? is the > > > benefit of using x.y.z versioning scheme if so many new features come > to > > > the "z"

[sqlite] SQLITE_CANTOPEN after days of operation

On Sun, Oct 11, 2015 at 12:56 PM, Andrew Miles wrote: > Log showed it unable to open the directory and then unable to write the > journal file. The directory is root writable and the process is run as > root so I didn't expect a problem here. I've now modified the directory > access to be

[sqlite] SQLITE_CANTOPEN after days of operation

errno 24 is EMFILE "Too many open files". You almost certainly have a file-descriptor leak. -scott On Wed, Oct 14, 2015 at 12:52 AM, Andrew Miles wrote: > Fully opening the directory failed to fix the issue. So in summary the > program works for days then dies with this in the log: > > (14)

[sqlite] FTS5 issue on OS X

Dollars to donuts you're compiling SQLite but then linking against the system version. -scott On Thu, Oct 22, 2015 at 7:51 AM, Gergely Lukacsy (glukacsy) < glukacsy at cisco.com> wrote: > Hi Simon, > > Thanks for coming back to me. > > I ran sqlite3_compileoption_get in a loop to enumerate all

[sqlite] Simple Math Question

On Fri, Oct 23, 2015 at 7:39 AM, Dominique Devienne wrote: > On Fri, Oct 23, 2015 at 4:16 PM, Rousselot, Richard A < > Richard.A.Rousselot at centurylink.com> wrote: > > So I decided to output 1000 digits, because why not? So now I am more > > perplexed with all these digits showing it is

[sqlite] Simple Math Question

On Fri, Oct 23, 2015 at 8:19 AM, Jim Callahan < jim.callahan.orlando at gmail.com> wrote: > Pocket calculators and COBOL used binary coded decimal (bcd) numbers to > avoid the representation/round off issues. But this meant another entire > number type (supported with addition, subtraction and

[sqlite] Simple Math Question

uestion is why don't the two results, which are > coming from the same program, agree? (i.e. return 22.99 not > 23.0) > > Richard > > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto: > sqlite-users-bounces at mailinglists.

[sqlite] Simple Math Question

Marzocchi < alessandro.marzocchi at gmail.com> wrote: > Sorry, i replied to wrong Scott Hess... mine was meant to be a reply to his > message... > " Internally, they are base-2 scientific notation, > so asking for more significant digits in the base-10 representation won't >

[sqlite] How would sqlite read this from disk?

On Thu, Oct 29, 2015 at 10:20 AM, Jason H wrote: > > If I could ask a followup question. You made the statement "SQLite reads > that row of the table from storage, from the first column to the last > column needed by the SELECT, but perhaps not all the way to the end of the > columns in the

[sqlite] How would sqlite read this from disk?

On Thu, Oct 29, 2015 at 10:59 AM, Jason H wrote: > > The documentation does not go into the detail of the engine is able to > skip the reading of unneeded interior rows. In theory, it can because the > length is contained in the header. So instead of read() on every column in > the row, it can

[sqlite] Thread safety problem encountered by people working on WebKit

On Sat, Sep 5, 2015 at 8:02 PM, Richard Hipp wrote: > On 9/5/15, Darin Adler wrote: > > Hi folks. > > > > I?m sending this on behalf of Michael Catanzaro, a contributor to the > WebKit > > open source project, who is working on a WebKit bug report, "Crash when > >

[sqlite] Feature request for sqlite3_initialize().

On Sat, Sep 5, 2015 at 6:42 PM, Darin Adler wrote: > Michael is planning a workaround in WebKit that will call > sqlite3_initialize manually exactly once before WebKit uses sqlite, using > std::once to deal with the thread safety issue. > This reminds me ... I was recently working on a patch

[sqlite] Thread safety problem encountered by people working on WebKit

On Thu, Sep 10, 2015 at 4:58 PM, Scott Hess wrote: > > The same basic logic applies to sqlite3_initialize()'s testing and setting > of sqlite3GlobalConfig.isInit , in a different thread+core the test can see > "true" before that core sees the setup implied by isInit being s

[sqlite] Feedback request: JSON support in SQLite

On Fri, Sep 11, 2015 at 8:58 AM, Richard Hipp wrote: > Draft documentation for the current design of JSON support in SQLite > can be seen on-line at > > https://www.sqlite.org/draft/json1.html > > Your feedback is encouraged. > > All features described in the document above are implemented

[sqlite] What is the best page cache size when the database is larger than system RAM?

On Sun, Sep 13, 2015 at 8:18 PM, David Barrett wrote: > Hello! If I have a database that is larger than the system's physical RAM, > am I correct in thinking I should actually set a very *small* page cache so > as to avoid "double caching" the same pages in both sqlite and the file > cache? > >

[sqlite] json1.c: isalnum(), isspace(), and isdigit() usage

The problem is that there are LOCALE settings where tolower() does things C programmers don't expect. I think tr_TR was one case, the handling of 'I' (Google "tr_tr locale bug" and you'll see lots of people hitting the same general problem). It isn't a problem of type safety, it's a problem that

[sqlite] Suggestion: Regularize output of setting pragmas.

Often, PRAGMA are documented like mmap_size, like: > Query or change the maximum number of bytes that are set aside > for memory-mapped I/O on a single database. The first > form (without an argument) queries the current limit. The > second form (with a numeric argument) sets the limit for the >

[sqlite] json1.c: isalnum(), isspace(), and isdigit() usage

On Thu, Sep 17, 2015 at 1:24 PM, Ralf Junker wrote: > On 17.09.2015 20:14, Scott Hess wrote: > >> The problem is that there are LOCALE settings where tolower() does things >> C >> programmers don't expect. I think tr_TR was one case, the handling of 'I' >> (Google

[sqlite] Any database unique ID across multiple connections ?

On Thu, Sep 24, 2015 at 4:56 AM, ALBERT Aur?lien < aurelien.albert at alyotech.fr> wrote: > @ Stephan Beal > > "Every instance of a :memory: db is a unique instance, so you cannot have > multiple connections to a single :memory: db." > > >> I know, this is one of the various reasons that made my

[sqlite] Use of __builtin_expect in SQLite

On Sun, Feb 7, 2016 at 10:39 PM, Matthias-Christian Ott wrote: > On 2016-02-08 04:31, Roger Binns wrote: > > On 07/02/16 00:56, Dominique Pell? wrote: > >> I'm curious about the outcome on SQLite benchmarks. > > > > About a year ago I tried them out on some tight code (non-SQLite) that > >

[sqlite] SQLite tuning advice for caching scenario

Just FYI, FTS writes each transaction's index data in a segment, then does segment merges over time. So there's some advantage to bulk updates versus one-at-a-time updates in terms of index fragmentation and write overhead. Having an in-memory FTS table which you spill to the on-disk table(s) as

[sqlite] Patch to fix buffer overflow in icu extension.

Summary: Certain Unicode code points expand to more than two code points when run through u_strToUpper(). SQLite's src/ext/icu/icu.c contains icuCaseFunc16() which implements custom upper() and lower() functions. It allocates a buffer of twice the input size because some code points take more

[sqlite] Page_size

Also note that almost all current storage you can purchase uses 4k basic blocks. So it's not just some weird Windows thing. In addition to performance advantages of getting the block size right, there is also the advantage that most storage systems strive hard to make sure block operations are

[sqlite] whish list for 2016

Maybe one option would be to add a layer to affect that explicitly, so that instead of the problem being that the existing rows can't be reordered without re-writing the entire table, the problem is to just change the schema to indicate where the columns should appear in "SELECT *" statements.

[sqlite] How to make sqlite3_release_memory produce a result?

On Wed, Jan 6, 2016 at 3:03 PM, Bart Smissaert wrote: > Have compiled sqlite3.dll (latest) compiled with ENABLE_MEMORY_MANAGEMENT, > but sofar > not been able yet to make sqlite3_release_memory produce anything else than > 0. > What would be the simplest way to make this happen? > I don't want

[sqlite] How to make sqlite3_release_memory produce a result?

try that. > sqlite3_release_memory doesn't have the DB connection as an argument, but > found > sqlite3_db_release_memory and that has that as an argument and that may > work better. > > RBS > > On Thu, Jan 7, 2016 at 6:05 AM, Scott Hess wrote: > > > On Wed, Ja

[sqlite] How to make sqlite3_release_memory produce a result?

gt; > This was a single update, where I don't think a transaction is helpful. > > Still no idea how I can make sqlite3_release_memory produce non-zero. > > > > RBS > > > > > > > > On Thu, Jan 7, 2016 at 6:05 AM, Scott Hess wrote: > > > >&g

[sqlite] Some FTS5 guidance

With fts4 you could search for matching terms in an fts4aux table, then use those to construct a query against the original table. You'd have a full scan of the fts index, but you'd not have to do a full table scan of the primary data. Unfortunately if there were a large number of hits in the

[sqlite] hard links and SQLite

Since this doesn't provide a -journal file, certain kinds of crashes cannot be recovered correctly. Why you you hard-link before the commit? The schema doesn't exist until the commit is successful, so there's no advantage to anyone else reading the file before then. As far as preventing the

[sqlite] hard links and SQLite

On Mon, Jan 11, 2016 at 11:00 AM, Felipe Gasper wrote: > On 11 Jan 2016 1:45 PM, Scott Hess wrote: > >> As far as preventing the other process from using it before the schema >> exists, do "SELECT count(*) FROM sqlite_master", and if the result is 0, >> the sch

[sqlite] hard links and SQLite

On Mon, Jan 11, 2016 at 9:12 PM, Felipe Gasper wrote: > On 11 Jan 2016 9:06 PM, Rowan Worth wrote: > >> * if it returns SQLITE_OK and zero rows, the schema hasn't been created >> yet >> > > Sure; however, by the time you do the next action it?s possible that > something else will be creating the

[sqlite] whish list for 2016

On Tue, Jan 12, 2016 at 3:43 PM, Keith Medcalf wrote: > On Tuesday, 12 January, 2016 13:51, James K. Lowden < > jklowden at schemamania.org> said: > > On Fri, 8 Jan 2016 08:28:29 +0100 > > Dominique Devienne wrote: > > > > One way to do that would be to honor a special user-created table, > > >

[sqlite] whish list for 2016

On Wed, Jan 13, 2016 at 12:42 AM, Jean-Christophe Deschamps < jcd at antichoc.net> wrote: > At 08:28 13/01/2016, you wrote: > >> On Wed, Jan 13, 2016 at 2:39 AM, Simon Slavin >> wrote: >> > On 12 Jan 2016, at 11:56pm, Scott Hess wrote: >> > >

[sqlite] Wish list: allow developers use the power of sqliteparser

On Mon, Jan 18, 2016 at 10:27 PM, David Barrett wrote: > One use of this I would like is to create a security framework around > arbitrary SQL queries from the user. So, for example, I'd love to > determine which tables (and which columns of those tables) a particular > query is going to

[sqlite] Find SQLITE_BUSY reason?

On Thu, Jan 21, 2016 at 4:25 AM, Daniel Polski wrote: > Den 2016-01-21 kl. 11:30, skrev Simon Slavin: > >> On 21 Jan 2016, at 9:44am, Daniel Polski wrote: >> >>> The Webserver/PHP can process up to 16 requests simultanuously and will >>> share one database connection among all instances. >>>

[sqlite] [sqlite-dev] Changing the default page_size in 3.12.0

On Fri, Mar 4, 2016 at 7:48 AM, Richard Hipp wrote: > The tip of trunk (3.12.0 alpha) changes the default page size for new > database file from 1024 to 4096 bytes. I have noticed that the OSX sqlite library seems to use default page_size of 4096, and default cache_size of either -2000 or 500.

[sqlite] Article about pointer abuse in SQLite

Not sure where you're going with this. "Undefined behavior" in this case is obviously referring to things defined by the C standard. Things not defined by the standard can (and do) change over time as compilers advance, and also often differ between compilers from different vendors. -scott On

[sqlite] Calling some predefined SQL function from another custom SQL function?

On Thu, Mar 31, 2016 at 6:39 AM, Olivier Mascia wrote: > > Le 31 mars 2016 ? 11:03, Clemens Ladisch a ?crit : > >> I think it is obvious I could build a SQL statement from within the > >> function and execute it. But it sounds costly to involve the parser > >> (yes, it's fast) for that, isn't

[sqlite] Make mmap_size dynamic?

The existing mmap functionality only maps the actual blocks associated with the file. So if your file is 16kb and your mmap_size is 1GB, only 16kb is used. Unless you add data to the file, then the mmap area grows, obviously. -scott On Mon, May 2, 2016 at 2:01 AM, Mikael wrote: > Dear Dr.

[sqlite] Make mmap_size dynamic?

) in the OS. > > If I just force it on (by hacking the build script), as long as mmap_size > always is 2^63, will Sqlite access the file via memory accesses only, and > never using fread/fwrite which would lead to undefined behavior because of > the absence of a UBC? > > Than

[sqlite] 2 different SQLite versions inside the same process space

IF you have two different versions of SQLite linked into the same executable, both accessing the same database, then the problem that the globals work around can happen. It won't happen if different processes use different versions of SQLite (say two versions of the sqlite3 binary, or sqlite3

[sqlite] Canonical way to get the CVS as of a particular release.

My assumption would be that you'd use 'cvs log VERSION' to find the datestamp where the VERSION file was updated, then check the tree out using -D. Right? Thanks, scott - To unsubscribe, send email to [EMAIL PROTECTED]

Re: [sqlite] Canonical way to get the CVS as of a particular release.

On 4/13/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: "Scott Hess" <[EMAIL PROTECTED]> wrote: > My assumption would be that you'd use 'cvs log VERSION' to find the > datestamp where the VERSION file was updated, then check the tree out > using -D. > &

Re: [sqlite] Insert order maintained?

Additionally, note that if you use ORDER BY, and it _is_ in the indicated order already, then sqlite will optimize the ORDER BY away entirely. So use ORDER BY. -scott On 4/17/07, Andrew Finkenstadt <[EMAIL PROTECTED]> wrote: The order of the rows returned by a select that does not have an

  1   2   3   4   5   >