Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

If the trouble comes from a big "IN()", an approach can be to pass all the values in a JSON array (one parameter) and use json_each in the query. This is completely safe vs SQL injection, and IME quite efficient. IME using JSON + json_each is also very efficient to fill temporary tables (indexed

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

Maybe "edge" database ? Or "local" database ? Both are trending terms, on the theme of taking control and performance back from the cloud. "Embedded" would be technically good, but is often associated with devices and small things these days. Le mar. 28 janv. 2020 à 05:58, Rowan Worth a écrit

Re: [sqlite] FW: Questions about your "Performance Matters" talk re SQLite

SQLite databases from a single process, I have not benchmarked yet, but I am probably hitting that mutex hard as well) Eric Le ven. 3 janv. 2020 à 17:36, Keith Medcalf a écrit : > On Friday, 3 January, 2020 09:30, sky5w...@gmail.com wrote: > > >I get SQLITE_MISUSE when attempting

Re: [sqlite] Performance vs. memory trade-off question

may be able to further build upon a new lookaside configuration by tweaking the structure sizes to more tightly match with the slot sizes f.i., and reduce the performance hit even in single-threaded cases. So "on by default" for me. Eric Le sam. 14 déc. 2019 à 14:27, Richard Hip

Re: [sqlite] Things you shouldn't assume when you store names

On Wed, 13 Nov 2019 17:18:05 -0700 SQLite mailing list sqlite-users@mailinglists.sqlite.org said 8>< Give up on names and use something else? (SSN, phone number, DOB…) None of the above are safe primary keys. I don't think there is any single combination which is. Eric -- ms fnd in

Re: [sqlite] Things you shouldn't assume when you store names

_any_ minority by applying an abusive label to them. One of these days you may well be a minority in some context or other. Eric -- ms fnd in a lbry ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/c

Re: [sqlite] Things you shouldn't assume when you store names

number of foreign family names, which probably helps it along. UK postcodes are incredibly fine-grained, compared to most of the rest of the world, where they would be much less useful for identification. Eric -- ms fnd in a lbry ___ sqlite-users mailin

Re: [sqlite] Limit on number of columns in SQLite table

nal table which can then be indexed and used directly as usual (you can use triggers to automate that extraction). You may also be able to use indexes on expression if you only want indexing (https://www.sqlite.org/expridx.html) Eric Le jeu. 17 oct. 2019 à 14:50, Mitar a écrit : > Hi! > &g

[sqlite] Incorrect query result

ROUP BY in the WITH clause make the query work again: WITH ITI_cmd AS ( SELECT id, nom, sens FROM generated_ITI) SELECT ITI1.id, ITI2.nom, ITI1.sens FROM ITI_cmd AS ITI1 JOIN ITI_cmd AS ITI2 WHERE ITI1.sens <> ITI2.sens

[sqlite] Incorrect query result

second query returns nothing, which is not expected, as shown in the result of the first query. Thank you and kind regards, Eric ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Floating point literals

Is there a way to pass binary representations of floating point numbers to a SQL query? If sqlite's internal representation of floating point numbers is 8-byte IEEE doubles, it would be convenient to be able to pass the literal value of a float or double to the underlying SQL parser without

Re: [sqlite] System.Data.SQLite.dll.config in final build

ld seems to not cause any problem. So it sounds like I don't need them. Thanks . eric ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] System.Data.SQLite.dll.config in final build

output directory? Or do I just need to delete it after the build? Or is it ok to just leave it there? thanks, Eric Kundl "Every man has a right to his opinion, but no man has a right to be wrong in his facts." --Bernard M. Baruch (1

[sqlite] System.Data.SQLite.dll.config in final build

it from appearing in the build output directory? Or do I just need to delete it after the build? Or is it ok to just leave it there? Also, what about the System.Data.SQLite.xml file. is it necessary to be included in the final build? Thanks. eric

[sqlite] Location of error in SQL statements ?

et, a line number or a larger code snippet would be enough. Best regards, Eric ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to select using cid?

0 1 > 1 value text0 0 Why on earth would you want to do that? Eric -- ms fnd in a lbry ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Import data into a temporary table

Hi, Is it possible to add the option of importing data into a temporary table? Currently you have to create a temporary table first before importing to it, or having to drop the table afterwards. .import dump.csv temp.table or .import dump.csv attach.table Regards Eric

Re: [sqlite] [EXTERNAL] json_group_array() and sorting

could end up wrecking existing queries where the subtype currently survives. Eric Le mar. 8 janv. 2019 à 11:41, Dominique Devienne a écrit : > On Tue, Jan 8, 2019 at 11:04 AM Dominique Devienne > wrote: > > > > On Tue, Jan 8, 2019 at 10:50 AM Eric Grange wrote: > >&g

Re: [sqlite] [EXTERNAL] json_group_array() and sorting

der, with an order by clause in a > subselect if necessary. > > If you have an order by clause which is already fulfilled by the > visitation order, SQLite will not sort again. > > -Ursprüngliche Nachricht- > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlit

[sqlite] json_group_array() and sorting

I can get the proper order when I use a subquery for the joins & filters, and aggregate in a top level query, but that is rather more verbose, and I am not sure the ordering being preserved in that case is not just 'circumstancial' and could be affected by future SQLite query optimizations.

Re: [sqlite] upsert from select

ocabulary(word) > SELECT w FROM A WHERE 1 > ON CONFLICT(word) DO UPDATE SET wcount=wcount+1 > ; > > > SELECT * FROM vocabulary > >-- word |wcount >-- -- | >-- jovial | 2 > > > PS: I used "wcount" rather because &

[sqlite] upsert from select

bulary (word, count) SELECT atom, 1 from json_each('["alpha","beta"]') ON CONFLICT(word) DO UPDATE SET count=count+1 Eric ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Replication

here was once a short-lived product actually called "The Last One"? > that was going to be closest to the relational model and/or the SQL > standard Just "or", the standard itself contradicts the model. > - right before re

Re: [sqlite] Replication

On Thu, 11 Oct 2018 15:44:23 -0600, Warren Young wrote: > On Oct 11, 2018, at 2:25 PM, Eric wrote: >> >> On Thu, 11 Oct 2018 10:20:08 -0600, Warren Young wrote: >>> On Oct 11, 2018, at 12:26 AM, Darren Duncan wrote: >> 8>< >> >&g

Re: [sqlite] Replication

t that the various implementers got it wrong, including missing out some key points of the Model. 8>< > PS: While I feel some ambivalence towards the subject, I was nodding in > agreement with most of your post, till that line appeared. :) Thankyo

Re: [sqlite] Replication

ckchain.html Hope you enjoy it! Thankyou, I did, except for their use of the word "query" to mean inserts, updates, and deletes :-) Eric -- ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/m

Re: [sqlite] Replication

u happen to have a reference for it? My actual point was that Fossil is now described as blockchain when it predates what we (currently) call blockchain, and so do other similar things. Eric -- ms fnd in a lbry ___ sqlite-users mailing list sqlite-users@mailin

[sqlite] Replication

or database replication. Not realistic for most existing databases though. Eric -- ms fnd in a lbry ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-users Digest, Vol 130, Issue 11

you could ignore the threads you don't want to read, there really is plenty of technical discussion. But since you replied to the digest your post won't be included in the right thread, and nor will this response - rgh! Eric -- ms fnd in a lbry ___

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

; https://fossil-scm.org/forum/forumpost/ba1144bc9f Thanks for that link, I will read it properly. Eric -- ms fnd in a lbry ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

small number of people. Eric -- ms fnd in a lbry ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

On Wed, 10 Oct 2018 11:10:24 -0600, Warren Young wrote: > On Oct 10, 2018, at 10:39 AM, Eric wrote: >> >> * mailing lists come to me, I don't have to go and get them > > So do Fossil email alerts. Do they thread? Anyway I have to go and get context, and go elsewhere to

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

said most of this before in various places at various times, but this seems like a good time to say it all at once. Eric -- ms fnd in a lbry ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

On Tue, 9 Oct 2018 15:17:52 -0600, Warren Young wrote: > On Oct 9, 2018, at 1:56 PM, Eric wrote: > > > > I suppose I must be an "anti-forum type" even though I have never used > > Gmane, but it does rather sound as though you are applying a somewhat > >

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

ugh time in the day, it's a lot easier to keep a finger on the pulse without reading everything in mailing lists than it is with forums. I suppose all that amounts to a somewhat blurry feature request ;-) Eric -- ms fnd in a lbry ___ sqlite-users

[sqlite] SQLite pre-compiled DLL for Windows x64

(1222656 bytes vs 910716 bytes for the one in the zip), and the x64 dll fails when loaded with LoadLibrary(). Eric ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS5 MATCH using "string*" matches just the exact string

iginal arabic text to make a longer word.) MacBook-II:Programs eric$ ./sqlite3 > SQLite version 3.24.0 2018-06-04 19:24:41 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. >

Re: [sqlite] Reducing index size

Mon, Jul 30, 2018 at 7:23 PM, Donald Griggs wrote: > There's a good chance this comment won't be useful to you, Eric. > Nevertheless, > > Any chance of relaxing your space requirement? I.e., what bad things > happen if the space is not reduced? > > Maybe you're writing for a f

Re: [sqlite] Reducing index size

12:05 PM, Rowan Worth wrote: > On 30 July 2018 at 17:53, Eric Grange wrote: > > > @Rowan Worth > > > What if you could create a "lite" index, which stores just the rowids > in > > a particular order and > > > refers back to the table for the rest

Re: [sqlite] Reducing index size

n SQL outputs, but at worst each query will be doing thousandths of those. So a little loss of performance there could be acceptable there as well. Eric On Mon, Jul 30, 2018 at 11:40 AM, Simon Slavin wrote: > On 30 Jul 2018, at 10:25am, Dominique Devienne > wrote: > > > The former allows

Re: [sqlite] Quick way to figure SQLite database block size per table & indexes

are quite fragmented, since I am using SSDs, the gains from a vacuum defragmentation appears marginal. Eric On Mon, Jul 30, 2018 at 10:33 AM, Dominique Devienne wrote: > Oops, sent too early... > > On Mon, Jul 30, 2018 at 10:29 AM Dominique Devienne > wrote: > > > On Mon, Jul

Re: [sqlite] Reducing index size

ight be possible to do a partial base64 decoding, and then filter on re-encoded base64, but that would be quite complex for "just" 25% size gained :/ Indexing on the string start still allows the filtering to occur with "substr(value, 1, 8) between x and y" f.i. Eric On Mon,

[sqlite] Reducing index size

te, is there another way? My searches on those indexes are by either exact value or by value start (human search & auto-completion) Eric ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Quick way to figure SQLite database block size per table & indexes

download sqlite3_analyzer Thanks, the text output is interesting with built-in documentation. On Mon, Jul 30, 2018 at 9:06 AM, D Burgess wrote: > download sqlite3_analyzer > from > http://www2.sqlite.org/2018/sqlite-tools-linux-x86-324.zip > > > > On Mon, Jul 30, 2018 at 4:46

[sqlite] Quick way to figure SQLite database block size per table & indexes

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

Re: [sqlite] Performance issue with left join in 3.24 compared with 3.22

is dynamically generated from user options and filters (which can indeed lead to SQL that is not really "optimal"). Is having a cross join somewhere among the joins enough to "disable" the left join strength reduction for other joins? On Tue, Jun 26, 2018 at 5:58 PM, Richard Hipp wrote:

Re: [sqlite] Performance issue with left join in 3.24 compared with 3.22

results in the same table scan of high_volume_table first, just without the index. - using the unary "+" on the d table filters has no effect on the query plan (as these are not indexed in the first place I guess) Using unlikely() on the d table filters seems to be the only option that works. On Tue, Jun

[sqlite] Performance issue with left join in 3.24 compared with 3.22

Hi, I am experiencing a massive performance issue on a query with a left join in 3.24, now taking 40 seconds, whereas in 3.22 it was just a few milliseconds. The problematic query looks like select d.key_field, count(*) nb from low_volume_table b join mid_volume_table c on

Re: [sqlite] How to Handle BigInt

will not be able to do arithmetic on those or convert them to string or anything, but you can add custom functions to handle that format should you need them. Eric On Thu, May 3, 2018 at 1:54 PM, Simon Slavin <slav...@bigfraud.org> wrote: > On 2 May 2018, at 6:08pm, Thomas Kurz <

Re: [sqlite] sqlite3_column_decltype and affinity

ed type of "type". >> so the sql engine already has to keep track of the affinity specified through "cast()" if I understood the above correctly On Sun, Apr 1, 2018 at 3:19 PM, Simon Slavin <slav...@bigfraud.org> wrote: > On 1 Apr 2018, at 8:19am, Eric Grange <

Re: [sqlite] sqlite3_column_decltype and affinity

to be able to recover that info whe it is provide through a cast. On Fri, Mar 30, 2018 at 6:04 PM, Simon Slavin <slav...@bigfraud.org> wrote: > On 30 Mar 2018, at 11:22am, Eric Grange <zar...@gmail.com> wrote: > > > Is there a way to have sqlite3_column_decltype return the affini

[sqlite] sqlite3_column_decltype and affinity

, as is documented in https://www.sqlite.org/c3ref/column_decltype.html I would like to have the affinity for purposes of presenting the results of an SQL query (for column display width etc.). Thanks! Eric ___ sqlite-users mailing list sqlite-users

Re: [sqlite] The upcoming 3.23.0 release

> Add the LEFT JOIN strength reduction optimization that converts a LEFT JOIN into an ordinary JOIN A question on this one: I have been using LEFT JOIN for many years (and not juste in SQLIte) in cases where a JOIN could have been used as a way to "hint" query optimizers which tables to scan

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

t case ? On Tue, Jan 9, 2018 at 6:21 PM, Simon Slavin <slav...@bigfraud.org> wrote: > On 9 Jan 2018, at 11:35am, Eric Grange <egra...@glscene.org> wrote: > > > In both cases, since things are constantly in flux, the absolute rank and > > neighbor do not really matte

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

s are for the top 100 / top 1000, those results are cached and only infrequently hit the db (so even if finding out the top 1000 was slow and inefficient, it would not really matter). In practice, the queries that really hit on the db are for "random" keys far from the top 1000. Eric On

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

<slav...@bigfraud.org> wrote: > On 9 Jan 2018, at 9:50am, Eric Grange <zar...@gmail.com> wrote: > > > then I fill that table with something like > > > > INSERT INTO RANKED > > SELECT key, value > > FROM ...something rather

[sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

RANK with UPDATE queries ran much slower than deleting and recreating everything, though this could just be bad implementations from my part. Are there any other strategies I could use that could update just the RANK field and mitigate the temporary B-tree size?

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

eaning. > > Because the use of NULL is not 'reserverd' for SQL, and in SQL it is not > more special than in any other environment. I don't understand what you mean. NULL has a special meaning in SQL (Structured Query Language), and that is what we

Re: [sqlite] Good resources for TCL/TK

all be given" page http://wiki.tcl.tk/37862 . Eric -- ms fnd in a lbry ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Starting with TCL

bases/general.sqlite > > puts [db version] > > > But I would prefer to check the version before connecting to a database. Is > this possible? Yes: set ver [package require sqlite3] puts $ver Eric -- ms fnd in a lbry __

[sqlite] Possible bug with strftime('%s') < strftime('%s')

0 on SQlite 2.8, the operator < with strftime('%s') works. Any idea if it is the expected behavior? Thanks, Best Regards, Eric ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Seeking advice on improving batch sql update performance on zipvfs

> I've also attached results of "EXPLAIN UPDATE ITEM SET FIELD4 = 10 WHERE DS = 15;". Not zipvfs specific, but that kind of update can be quite inefficient if the record is large (in terms of bytes, or records per database page) as your table declaration hints to. This will be especially

Re: [sqlite] Proof that a line has been modified

r level than the row, a blockchain or DAG (like git) being the simplest solutions, ie. the hash of a row is the hmac of (data of that row + hash of previous row), storing/signing the last row hash externally then allows verifying all previous rows. Eric On Fri, Sep 8, 2017 at 1:56 AM, Michael

Re: [sqlite] Mailinglist question

at it is a major source of spam to the groups. Eric -- ms fnd in a lbry ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

among full relational DBs, few manage these correctly. IME only PostgreSQL and Firebird handle these correctly by default, for Oracle or MSSQL you have to use special locking modes and transaction options with significant performance penalties. Eric On Fri, Aug 4, 2017 at 8:50 AM, Luc DAVID

Re: [sqlite] New draft document on the new pointer-passing interfaces

>https://www.sqlite.org/draft/bindptr.html Thank you very much for this, detailed, informative write-up, Dr Hipp. It's very helpful to see the sensible rationale behind the new interfaces. Thanks for continuing to enhance the API! ___ sqlite-users

Re: [sqlite] Version 3.20.0 coming soon...

> Could I suggest that the STMT virtual table be renamed as something like sqlite_statements ? > Or anything else that starts with "sqlite_" ? Seconded, especially as more and more virtual tables become available (a good thing), there is a greater risk of name collision, so an "sqlite_" namespace

Re: [sqlite] UTF8-BOM and text encoding detection (was: UTF8-BOM not disregarded in CSV import)

der to figure out down the road where the garbage in your database originated from (incorrect input? bug in the heuristics? etc.) On Wed, Jun 28, 2017 at 10:40 PM, Tim Streater <t...@clothears.org.uk> wrote: > On 28 Jun 2017 at 14:20, Rowan Worth <row...@dug.com> wrote: > > > On

Re: [sqlite] [OT] UTF8-BOM and text encoding detection (was: UTF8-BOM not disregarded in CSV import)

> ASCII / ANSI is a 7-bit format. ASCII is a 7 bit encoding, but uses 8 bits in just about any implementation out there. I do not think there is any 7 bit implementation still alive outside of legacy mode for low-level wire protocols (RS232 etc.). I personally have never encountered a 7 bit ASCII

Re: [sqlite] [OT] UTF8-BOM and text encoding detection (was: UTF8-BOM not disregarded in CSV import)

> In case 7 we have little choice but to invoke heuristics or defer to the > user, yes? Yes in theory, but "no" in the real world, or rather "not in any way that matters" In the real world, text files are heavily skewed towards 8 bit formats, meaning just three cases dominate the debate: - ASCII

Re: [sqlite] UTF8-BOM not disregarded in CSV import

>Easily solved by never including a superflous BOM in UTF-8 text And that easy option has worked beautifully for 20 years... not. Yes, BOM is a misnommer, yes it "wastes" 3 bytes, but in the real world "text files" have a variety of encodings. No BOM = you have to fire a whole suite of

Re: [sqlite] UTF8-BOM not disregarded in CSV import

Alas, there is no end in sight to the pain for the Unicode decision to not make the BOM compulsory for UTF-8. Making it optional or non-necessary basically made every single text file ambiguous, with non-trivial heuristics and implicit conventions required instead, resulting in character

[sqlite] Possible issue with 3.19.3

Greetings, Version 3.19.3 is yielding different results than previous releases for a query in the Entity Framework Core test suite. The query looks like this: SELECT [e].[Id], [e].[Discriminator], [e].[Name], [e].[BaseId], [t].[Id], [t].[BaseParentId], [t].[Discriminator], [t].[Name],

Re: [sqlite] Questions about SQLite Encryption Extension (SEE)

hould use disk encryption, it can only be seen as an added security layer, never as a primary security layer. Eric On Fri, Jun 9, 2017 at 12:13 AM, Wout Mertens <wout.mert...@gmail.com> wrote: > Isn't it all just obfuscation? Any root user can read your key, if not from > disk then fr

Re: [sqlite] NOT NULL integer primary key

n IMPLEMENTATION detail) looks just like an auto-increment integer, and behaves like an integer primary key, so if you want an auto-increment PK, overload the syntax to declare it and overload the implementation detail to make it work. Eric -- ms fnd in a lbry ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RIGHT JOIN! still not supported?

For the sake of curiosity, is anyone (as in any human) using RIGHT JOIN? Personally I never had a need for a RIGHT JOIN, not because of theoretical or design considerations, but it just never came into my flow of thought when writing SQL... I guess some automated SQL query generators could use

Re: [sqlite] SQLite does not support ARM platform?

You are encountering a limitation of the compiler you are using, not a limitation of SQLite (which compiles for ARM just fine). The error message is telling you that Microsoft does not support desktop applications compiled for ARM. What environment are you trying to compile for? -- E On Mon,

Re: [sqlite] More built-in functions for basic math

custom implementations. Eric On Thu, Mar 9, 2017 at 1:16 PM, R Smith <rsm...@rsweb.co.za> wrote: > I second this - Been having a hard time making basic queries with a simple > x^y function in SQL for SQLite since there is no guarantee what the > end-user's system will have it com

Re: [sqlite] SQLite 3.18.0 pre-release snapshot

nly rarely beneficial). Eric On Tue, Mar 7, 2017 at 4:22 AM, jose isaias cabrera <jic...@barrioinvi.net> wrote: > > Richard Hipp wrote... > > On 3/6/17, jose isaias cabrera <jic...@barrioinvi.net> wrote: > >> >> Richard Hipp wrote... >> >> Ha

Re: [sqlite] SQLite 3.16.0 enters testing

enter sleep/wait states in a simulated, deterministic environment, but could occasionnally do in a more realistic setting, which could lead to a very different performance profile when that happens, and ultimately favor different optimization strategies ("slower but more robust"). Eric

Re: [sqlite] Typical suffixes of sqlite database files

> What suffixes to sqlite database files do you use or see in the wild? .sql3 .dat On Fri, Oct 21, 2016 at 7:46 AM, Jean-Christophe Deschamps wrote: > At 01:17 21/10/2016, you wrote: > > What suffixes to sqlite database files do you use or see in the wild? >> > > I

Re: [sqlite] LIMIT doesn't return expected rows

> Your problem is that although SQL accepts the clauses written in the order > you wrote them in, the LIMIT clause is processed before the ORDER BY clause. > > Is that really true? I had always thought that the ORDER BY was processed first, though I admit I don't see anything authoritative either

Re: [sqlite] Fastest way to backup a "live" database

. After looking with a profiler, it seems that the extra time spent seems in ntdll.dll, not SQLite itself. The source cache_size has an impact but small, the main impact is for the destination cache_size. Also of note, the backup operation can take about half of a CPU core (a fast E3 core at 3.5 GHZ).

Re: [sqlite] Fastest way to backup a "live" database

, both on source or destination, between cache_size 1 and the default of SQLite (-2000) there is a 20% difference in performance during backup. Note that this is in Windows, on SSD and with lots of free RAM, so it seems that the OS is better at handling cache than SQLite in that particular use case.

Re: [sqlite] Fastest way to backup a "live" database

ant to provide some progress indicator or pace the backup? (ie. pace I/O bandwidth ?) Or in other words, given WAL mode, what do we "lose" when using -1 and to copy everything at once during a backup? On Tue, Oct 4, 2016 at 5:33 PM, Clemens Ladisch <clem...@ladisch.de> wrote:

Re: [sqlite] Fastest way to backup a "live" database

On Tue, Oct 4, 2016 at 4:05 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 4 Oct 2016, at 2:53pm, Eric Grange <zar...@gmail.com> wrote: > > > I am going on the assumption that if something fails during backup, the > > backup itself will be toast anyway, but

[sqlite] Fastest way to backup a "live" database

y too high or could it be worth going with a 64bits CLI? Thanks! Eric ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ideas for optional json fields?

Nice solution and works like a charm, thanks Clemens! On Mon, Sep 26, 2016 at 4:22 PM, Clemens Ladisch <clem...@ladisch.de> wrote: > Eric Grange wrote: > > I am generating a json with json_object() function, and I would like to > > omit the null or default fields from the o

Re: [sqlite] Ideas for optional json fields?

t;:"value","field2":""} I also tried setting the field name to null, but that triggers an error "json_object() labels must be TEXT" On Mon, Sep 26, 2016 at 2:28 PM, R Smith <rsm...@rsweb.co.za> wrote: > > On 2016/09/26 11:15 AM, Eric Grange wrote: &g

[sqlite] Ideas for optional json fields?

ct('field1', field1) else json_object('field1', field1, 'field2', field2) end ... but when there are multiple such optional fields, this approach becomes unwieldy. Anyone has a better idea? Eric ___ sqlite-users mailing list sq

Re: [sqlite] Result code 5 from sqlite3_prepare_v2() in WAL mode?

Excellent. Thanks. -- E On Tue, Sep 13, 2016 at 3:05 PM, Richard Hipp <d...@sqlite.org> wrote: > On 9/13/16, Eric Sink <e...@sourcegear.com> wrote: > > > > I can fit this into your explanation: > > > > "Another process might have opened the s

Re: [sqlite] Result code 5 from sqlite3_prepare_v2() in WAL mode?

same database with locking_mode=EXCLUSIVE" if I change the word "process" to "thread", and if I assume that sqlite3_open_v2() on a WAL-mode file can [perhaps sometimes] involve an exclusive lock, if even for a short time. Would this be a correct understanding? -- E On Tue,

Re: [sqlite] Result code 5 from sqlite3_prepare_v2() in WAL mode?

This is happening in an Android app. No other process is involved, but the filesystem there is weird, so I'm focusing on the third possibility you mentioned. Thanks, -- E On Mon, Sep 12, 2016 at 7:52 PM, Richard Hipp <d...@sqlite.org> wrote: > On 9/12/16, Eric Sink <e...@so

[sqlite] Result code 5 from sqlite3_prepare_v2() in WAL mode?

OK, this seems like a simple thing, but I'm stuck and looking for inspiration or clues. How can sqlite3_prepare_v2() return SQLITE_BUSY for a simple SELECT statement when in WAL mode? Immediately prior, a sqlite3_exec("BEGIN TRANSACTION") succeeded. The failing call is just

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

Looks quite good and is very readable on my iPhone 6s and iPad Mini 4, both portrait and landscape modes. ..Eric __ Plan A is always more effective when the device you are working on understands that Plan B involves either a large hammer

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

t; > On 2016/09/06 10:19 AM, Stephan Beal wrote: > >> On Tue, Sep 6, 2016 at 10:15 AM, Eric Grange <zar...@gmail.com> wrote: >> >> However, while you are at it, an improvement for the website when browsed >>> from a computer would be to limit the max width of th

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

On Tue, Sep 6, 2016 at 10:19 AM, Stephan Beal <sgb...@googlemail.com> wrote: > On Tue, Sep 6, 2016 at 10:15 AM, Eric Grange <zar...@gmail.com> wrote: > > > However, while you are at it, an improvement for the website when browsed > > from a computer would be

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

d: #fff; padding: 0 2em; box-shadow: 0 0 .3em #888; margin-top: .5em; } Eric On Tue, Sep 6, 2016 at 2:32 AM, Stephen Chrzanowski <pontia...@gmail.com> wrote: > I have my ancient Galaxy S (Original > <http://www.gsmarena.com/samsung_i9000_galaxy_s-3115.php>) Android h

Re: [sqlite] SQLite in Android N

FWIW, I wrote a blog entry about this issue to shine a bit more light on it: http://ericsink.com/entries/sqlite_android_n.html -- E On Mon, Jun 13, 2016 at 3:51 PM, Richard Hipp <d...@sqlite.org> wrote: > On 6/13/16, Eric Sink <e...@sourcegear.com> wrote: > > "Ric

Re: [sqlite] SQLite in Android N

rt of the NDK? -- E On Mon, Jun 6, 2016 at 12:26 PM, Eric Sink <e...@sourcegear.com> wrote: > > It is my understanding that Android N will no longer allow apps to use the > system-installed SQLite library (unless they go through the Android Java > API, android.database.sqlite). >

  1   2   3   4   5   6   >