Re: [sqlite] Best practices for huge batch inserts with WAL?

What I'd do: 1) Look at the indices, and make sure the input is sorted to insert in index order. Also drop any unnecessary indices and add them back at the end. [Read the code for vacuum to see what kinds of things make sense to defer.] 2) Bump up the cache a lot. Inserting in sorted order

Re: [sqlite] Memoization in sqlite json1 functions

What is the goal, though? Your app knows your data and performance needs, so if you find yourself running the same query to read off the same result set over and over, change your app to do the right thing. If it's somehow more convenient to have SQLite do it, populate a temporary table and pull

Re: [sqlite] Massively multithreaded SQLite queries

You might want to try enabling mmap mode: pragma mmap_size = 4294967296; or something like that. Try to make it larger than your databases. I'd expect that if you're running with that many cores, you're _probably_ running in a 64-bit address space, so it'll probably work. -scott On Fri, Mar

Re: [sqlite] Massively multithreaded SQLite queries

Yes, if they are lock bound, then they need to have the number of cores which reduces the locking overhead to the point where it's not degrading performance too much. Though I guess the OP really didn't say that (more CPUs may spend more time in spinlocks and still spend less wallclock time).

Re: [sqlite] Massively multithreaded SQLite queries

I'd say you should consider switching to some sort of queue feeding a worker pool, then experimenting with pool sizes. Often problems reward the first few threads you add, but at some point additional threads become a negative unless the system is specifically designed for high thread counts (and

Re: [sqlite] Thread safety of serialized mode

On Tue, Feb 14, 2017 at 5:05 PM, Darren Duncan wrote: > On 2017-02-14 4:46 PM, Richard Hipp wrote: > >> This is yet another reason why I say "threads are evil". For >> whatever reason, programmers today think that "goto" and pointers and >> assert() are the causes of

Re: [sqlite] Patch for consideration: auto_vacuum slack.

On Mon, Feb 13, 2017 at 12:13 PM, Richard Hipp wrote: > > Scott: The motivation for your patch seem to be to get auto-vacuum to > run a little faster. But if performance is your goal, why not just > turn auto-vacuum off? Or, failing that, set it to INCREMENTAL and > then run

[sqlite] Patch for consideration: auto_vacuum slack.

A developer was asking me questions about auto_vacuum I/O characteristics, because they were worried about "churn", where a page is moved to fill a freelist gap, then soon enough a new page is allocated anyhow, so the move wasn't really necessary. This made me wonder if auto_vacuum recognized

Re: [sqlite] [OT] mySQL ORDER BY clause in Views

On Thu, Jan 19, 2017 at 1:03 PM, R Smith <rsm...@rsweb.co.za> wrote: > On 2017/01/19 9:01 PM, Simon Slavin wrote: >> On 19 Jan 2017, at 6:54pm, Scott Hess <sh...@google.com> wrote: >>> Just to be clear, you're saying that the VIEW has an ORDER BY, but >>> w

Re: [sqlite] [OT] mySQL ORDER BY clause in Views

On Wed, Jan 18, 2017 at 3:36 PM, Peter Haworth wrote: > I am in the process of converting an SQLite database to mySQL. The SQLIte > db includes several views with ORDER BY clauses that have always returned > qualifying rows in the correct order. > > I am discovering that in mySQL

Re: [sqlite] Executing multiple statements at once

On Thu, Jan 19, 2017 at 9:24 AM, Igor Korot wrote: > Is it possible to write something like this: > > sqlite3_prepare_v2( m_db, "BEGIN TRANSACTION; CREATE TEMP TABLE temp > AS SELECT * FROM mytable; DROP TABLE mytable; CREATE TABLE mytable(id > INTEGER PRIMARY KEY, name TEXT

Re: [sqlite] extension to run bash

Though it may be cleaner long-term to implement system() to pass individual arguments, rather than passing a single string which will have to be re-processed by the shell. So the API would end up like: UPDATE result SET nRows = system('wc', '-l', fileNames); The reason I suggest this is

Re: [sqlite] Allow overriding unsigned 64-bit integer

On Sun, Jan 8, 2017 at 6:23 AM, Kirill Müller wrote: > On 08.01.2017 14:20, Clemens Ladisch wrote: >> Kirill Müller wrote: >>> On 08.01.2017 12:54, Clemens Ladisch wrote: Kirill Müller wrote: > ... there's no portable support for 64-bit integers. > I'm working

Re: [sqlite] Bug report + fix: SQLite 3.11+ broken on EBCDIC systems

On Mon, Dec 12, 2016 at 9:30 PM, Bradford Larsen wrote: > An alternative possibility would be to revert to the pre-3.11 tokenizer on > EBCDIC systems. If I recall, the old tokenizer used a big switch statement > with character literals instead of the 'aiClass' table. I

Re: [sqlite] Is there any way to close a connection without checkpointing the WAL?

On Mon, Dec 5, 2016 at 1:34 PM, Simon Slavin <slav...@bigfraud.org> wrote: > On 5 Dec 2016, at 9:26pm, Scott Hess <sh...@google.com> wrote: >> An obvious solution would be to simply not call sqlite3_close(), >> though that has various other unfortunate side effects. &

Re: [sqlite] Is there any way to close a connection without checkpointing the WAL?

On Mon, Dec 5, 2016 at 1:38 PM, Richard Hipp <d...@sqlite.org> wrote: > On 12/5/16, Scott Hess <sh...@google.com> wrote: >> Is there any clean way to request no WAL checkpoint on sqlite3_close()? > > sqlite3_db_config(SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE, db). See > http

[sqlite] Is there any way to close a connection without checkpointing the WAL?

At Chromium shutdown, various services desire to write data to their SQLite databases, which results in a (small) thundering herd of fsyncs, which makes shutdown slower than it could be. Normally, one could enable WAL mode to amortize the fsync cost across longer periods than a single

Re: [sqlite] Read-only access which does not block writers

On Tue, Nov 29, 2016 at 10:10 AM, Mark Hamburg wrote: > On Nov 29, 2016, at 9:09 AM, Simon Slavin wrote: >>> On 29 Nov 2016, at 4:18pm, Mark Hamburg wrote: >>> >>> Does this make sense? Does it seem useful? (It seems useful to

Re: [sqlite] Pragma to flag unknown pragma?

On Tue, Nov 22, 2016 at 10:50 PM, R Smith <rsm...@rsweb.co.za> wrote: > On 2016/11/23 2:08 AM, Scott Hess wrote: >> https://www.sqlite.org/pragma.html has: >> "No error messages are generated if an unknown pragma is issued. >> Unknown pragmas are simply ignore

[sqlite] Pragma to flag unknown pragma?

https://www.sqlite.org/pragma.html has: "No error messages are generated if an unknown pragma is issued. Unknown pragmas are simply ignored. This means if there is a typo in a pragma statement the library does not inform the user of the fact." I just lost some time due to this, even though I was

Re: [sqlite] Question about sqlite3_backup() versus page_size.

the window for this to confuse something is pretty narrow. -scott On Thu, Oct 27, 2016 at 4:08 PM, Scott Hess <sh...@google.com> wrote: > Here's an example code for reference: >http://pastebin.com/pQdfkneR > > I just noticed that if you use sqlite3_backup() where the source > da

[sqlite] Question about sqlite3_backup() versus page_size.

Here's an example code for reference: http://pastebin.com/pQdfkneR I just noticed that if you use sqlite3_backup() where the source database page_size isn't the same as the destination database page_size, after a successful backup the destination database continues to report the page_size from

Re: [sqlite] Q about integer overflow in sqlite3MulInt64().

Yes - once the undefined behavior has happened, the compiler can dispense with everything else, so if it can prove that your after-the-fact checks can only happen in case of signed overflow, it can simply omit them. Great fun. Dr Hipp landed https://www.sqlite.org/src/info/db3ebd7c52cfc5fc ,

[sqlite] Q about integer overflow in sqlite3MulInt64().

sqlite3MulInt64() in util.c appears to try to detect integer overflow by dividing the inputs by 2^32. If both inputs are 0 when divided by 2^32, it does the 64-bit multiplication and moves on. In the case of something like |SELECT 3452005775*3452005775|, both inputs are greater than 2^31 but

Re: [sqlite] sqlite - Delete large table in sqlite

Do you have auto_vacuum turned on? It may be that the cost isn't actually in deleting the table, it may be that the cost is rearranging the rest of the file to fill the gaps left by deleting the table. In that case you could turn off auto_vacuum, or you could use incremental vacuum to smooth out

Re: [sqlite] page_size on ATTACH-ed databases

Is there any possibility that the attached db already existed before you ran this? Because once a db exists (contains pages) the page size is fixed until you run vacuum. On Tue, Aug 16, 2016 at 10:53 AM, Ward WIllats wrote: > >>> On Aug 12, 2016, at 11:44 PM, Dan

Re: [sqlite] Bad db feature request

On Wed, Jun 29, 2016 at 10:36 AM, Simon Slavin wrote: > On 29 Jun 2016, at 5:45pm, Drago, William @ CSG - NARDA-MITEQ > wrote: >> Aren't there things like that already built in to the hard disk controllers >> (CRC, Reed Solomon, etc.)? > > Yes.

Re: [sqlite] Bad db feature request

On Wed, Jun 29, 2016 at 2:17 AM, R Smith wrote: > In response to a recent forum post and many other posts, where SQLite > corrupt files or Index integrity was the problem at hand, I was wondering if > we could ask for an API function that would corrupt a DB for us. I have

Re: [sqlite] SQlite database corrupt , PHP and multithread C program accessing same database

On Wed, Jun 22, 2016 at 8:13 PM, Simon Slavin wrote: > On 23 Jun 2016, at 3:52am, mon siong wrote: >> PHP is using sqlite3 library (http://php.net/manual/en/book.sqlite3.php) >> and C program is handle the sqlite using Serialized. >> >> Both of them are

Re: [sqlite] Managing SQLite indices.

One thing I would add is to try to populate your example database with representative data - in fact, try hard to figure out what representative data looks like, it informs many decisions. My experience is that sometimes people assume that because something is fast enough on their workstation,

[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] 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] 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] 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] 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] [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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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

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] 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] 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] 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?

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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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

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

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] 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] 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] 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-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] 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] 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] 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 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] 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] 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] 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

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] 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] 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] 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] 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 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] 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] 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] 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] 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] 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 ? 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] 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] 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] 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] 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] 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] 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] 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] 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] 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

[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] 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] 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] 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.

  1   2   3   4   5   >