Re: [sqlite] UPSERT available in pre-release

hard Hipp <d...@sqlite.org> Sent: Saturday, April 21, 2018 3:49:08 AM To: SQLite mailing list Subject: Re: [sqlite] UPSERT available in pre-release Please try again with the latest pre-release snapshot. On 4/19/18, Quan Yong Zhai <q...@msn.com> wrote: > Dear Richard, > > I modifi

Re: [sqlite] UPSERT available in pre-release

Dear Richard, I modified the wordcount.c in SQLite/test directory, to use the new upsert command: INSERT INTO wordcount(word,cnt) VALUES(?1,1) ON CONFLICT(word) DO UPDATE SET cnt=cnt+1 Before: wordcount --all :memory: sqlite3.c 2.406 wordcount --insert 2.296 wordcount --insert

Re: [sqlite] How to optimise a somewhat-recursive query?

Drop the view, add a new column “last_retrieval_datetime” to the table urls, and whenever fetch a page, update this column 发自我的 iPhone > 在 2018年3月19日，06:15，Clemens Ladisch 写道： > > I have not looked at the schema and queries in detail. > But at a first glance: > >> CREATE

Re: [sqlite] If I got many to many relationship data first, how do I insert them to my table?

对于这样的问题SQLite也没有特别的解决方案。你说的方法几乎就是“标准”的做法，先把customer_id 设置为null, 然后插入该订单，（ 这样就不会引起外部键检查失败）， 当获取该订单准确的customer_id后，再把它更新。 App的逻辑应考虑到这一点，所有customer_id 为null的订单都是不完整的，相当于草稿。 Zhai Sent from Mail for Windows 10 From: 邱朗

Re: [sqlite] Disk I/O utilization

1. The latest SQLite improved REPLACE operation, It's alot faster now. 2.use SAVEPOINT to batch transactions. They'll save many IO. 发自我的 Mobile Phone 发件人: Vince Scafaria 发送时间: ‎2016/‎12/‎13 9:24 收件人:

Re: [sqlite] CREATE TABLE fails

CREATE TABLE IF NOT EXISTS abc("abc_tnam" char(129) NOT NULL, "abc_tid" integer, "abc_ownr" char(129) NOT NULL, "abc_cnam" char(129) NOT NULL , "abc_cid" smallint, "abc_labl" char(254), "abc_lpos" smallint, "abc_hdr" char( 254), "abc_hpos" smallint, "abc_itfy" smallint, "abc_mask" char(31),

Re: [sqlite] Changing ID's to UUID

Create a custom function MD5 , Custum_uuid() -> MD5(table_name || Numeric_ID || "salt string" ) Update table tab1 set id= md5('tab1' || '$' || ID || '$' || 'My custom string') Update table tab1 set ref_id= md5('tab2' || '$' || ref_id || '$' || 'My custom string') 发自我的 Windows Phone

Re: [sqlite] Are identical calls to deterministic functions coalesced?

>Does the query optimizer coalesce identical calls to deterministic functions? It’s not coalesce identical calls when inserting new record. CREATE TABLE t(x json); CREATE INDEX i2 on t(json_extract(x,'$.type')) where json_extract(x,'$.type') not null; explain insert into t values(?); addr

[sqlite] Possible optimization oppurtunity

Dear SQLite developers, SQLite does not use covering index on following situation CREATE TABLE t(x json); CREATE INDEX i1 on t( json_extract(x, "$.name")); explain query plan select json_extract(x,"$.name") from t where json_extract(x,"$.name")=?; 0|0|0|SEARCH TABLE t USING INDEX i1 (=?)

Re: [sqlite] A possible double bug?

ite.org> 主题: Re: [sqlite] A possible double bug? On 10/17/2016 07:12 AM, Quan Yong Zhai wrote: > I can’t reproduce the problem, As it has already been pointed out, this is normal for floating point arithmetic. In your machine, implementation, and SQLite installation the two value represent

Re: [sqlite] A possible double bug?

I can’t reproduce the problem, http://sqlite.org/datatype3.html#type_affinity “When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if such conversion is lossless and reversible” So after “create table test

[sqlite] a small defrag tool for SQLite database file

Hi guys, I have a modified version of the “scrub” tool, the origin one found in SQLite source tree /ext/misc/scrub.c It’s a defrag tool for SQLite database file, implement a function drop out all freelist-pages and rearrange the other useful databases pages. And it’s blazing faster than

Re: [sqlite] Is it possible to support 64-bit value in rtree module?

How about splits the 64 bits integer to two 32 bits integer, and store it in two fields of rtree table. 发自我的 Windows Phone 发件人: Gelin Yan 发送时间: ‎2016/‎10/‎11 1:23 收件人: SQLite mailing list

Re: [sqlite] SQLite3 database slowdown at 1GB

To avoid unnecessary rebalance and split operation of b-tree, sort the data before insert it to table. Create temp table bf_temp(id integer, offset); -- careful, no primary key Begin; Insert into bf_temp(id, offset) values(?,?); … create table if not exists bf_fav_nis(id integer primary

Re: [sqlite] SQLite 3.15.0 scheduled for 2016-10-14

http://sqlite.org/draft/rowvalue.html Quote<< A "row value" is an ordered list of two or more scalar values. In other words, a "row value" is a vector.>> some word in the above sentence not strictly correct. A “row value” is a tuple, not a vector. When your using a tuple, you know how many

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

DELETE FROM tab WHERE rowid in (SELECT rowid from tab ORDER BY … LIMIT 1000); If SQLite compiled with SQLITE_ENABLE_UPDATE_DELETE_LIMIT http://www.sqlite.org/compile.html#enable_update_delete_limit DELETE FROM tab WHERE … ORDER BY … LIMIT 1000; From: Simon Slavin

Re: [sqlite] CREATE FUNCTION

>From: Rousselot, Richard A >Sent: 2016年8月23日 22:14 >To: SQLite mailing list >Subject: Re: [sqlite] CREATE FUNCTION >Trust, me if I could remove that dumb message I would. :) There is no such thing as a dumb question. Packing all the core business logic in SQL language has some advantage, It’s

Re: [sqlite] Exec vs Prepare, step, finalize.

> From: michael.j.falco...@gmail.com > Date: Thu, 11 Aug 2016 15:53:39 +1000 > To: sqlite-users@mailinglists.sqlite.org > Subject: Re: [sqlite] Exec vs Prepare, step, finalize. > > I have a self styled routine (similar to the glibc manual example) for > concatenating the strings values that make

Re: [sqlite] sqlite 3.13.0 does not use indexes as 3.8.11.1 does

SQLite version 3.14.0 2016-07-26 04:49:43 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table t(x,y,z); sqlite> create index i1 on t(x,y); sqlite> explain query plan select * from t where y=100

Re: [sqlite] sqlite 3.13.0 does not use indexes as 3.8.11.1 does

Query plan changed, It's a bug 发件人: Alessandro Fardin 发送时间: ‎2016/‎7/‎26 16:37 收件人: sqlite-users@mailinglists.sqlite.org 主题: [sqlite] sqlite 3.13.0 does not use indexes as

Re: [sqlite] REFERENCES from TEMPORARY table to main database table?

You can maintain a local conditional "index" table via temp trigger, as long as there's no other processes modify the permanent table. Create table t(id integer primary key, x); Create temp table t1(id integer primary key); Create temp trigger trig_1 after insert on t begin Insert into t1 select

Re: [sqlite] In what case will the online backup api hang for acquiring the Btree lock of the destination on-disk file?

If remove use of shared cache from file specifications, they will be two separated memory databases 发件人: Simon Slavin 发送时间: ‎2016/‎7/‎22 9:32 收件人: SQLite mailing list 主题: Re: [sqlite] In

Re: [sqlite] In what case will the online backup api hang for acquiring the Btree lock of the destination on-disk file?

https://www.sqlite.org/faq.html#q5 “But use caution: this locking mechanism might not work correctly if the database file is kept on an NFS filesystem. This is because fcntl() file locking is broken on many NFS implementations. You should avoid putting SQLite database files on NFS if multiple

Re: [sqlite] Problem with savepoints

It seemed that SQLite can’t create a temporary file,: PRAGMA TEMP_STORE=MEMORY From: Quan Yong Zhai<mailto:q...@msn.com> Sent: 2016年7月6日 22:35 To: Jeff Archer<mailto:jeffarch...@gmail.com>; sqlite-users@mailinglists.sqlite.org<mailto:sqlite-users@mailinglists.sqlite.org> Su

Re: [sqlite] Problem with savepoints

PRAGMA JOURNAL_MODE=MEMORY Sent from Mail for Windows 10 From: Jeff Archer Sent: 2016年7月6日 22:10 To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Problem with

[sqlite] optimization: slight faster linked list merge sorter

Dear SQLIte developers, Here is a small patch for the linked-list merge sorter in SQLite to sort N items, It will save about 2*N CPU instructions by eliminate unnecessary null pointer check, Regards make test passed. fossil diff Index: src/pcache.c

[sqlite] Changing the default page_size in 3.12.0

>From: tonyp at acm.org >To: SQLite mailing list >Subject: Re: [sqlite] Changing the default page_size in 3.12.0 >I ran some tests and almost all of my databases (about 100 of them with >different content mix and with the biggest one being around 500MB) inflated >by a lot while only a couple or

[sqlite] Why does the stored schema strings have their beginning upper-cased?

VACUUM depends on upper cased "CREATE [UNIQUE] INDEX" sql text in sqlite_master when " pragma case_sensitive_like = on " , or the VACUUM will not work properly ???: Simon Slavin : ?2016/?3/?2 17:27 ???: SQLite mailing

[sqlite] Select top 1 from duplicate values

sqlite> create table mytable(f1 integer,f2 integer,f3 integer); sqlite> insert into mytable values(1,2,8),(1,3,9),(2,4,8),(2,5,2),(3,6,4),(3,7,8),(4,2,4); sqlite> select f1, max(f2), f3 from mytable where (f3&8)!=0 group by f1; 1|3|9 2|4|8 3|7|8 sqlite>

[sqlite] Multiple Column index

I supposed your SQL command like this "select * from table where (DateTime>X AND DateTimeX AND DateTimemailto:michele.pradella at selea.com> : ?2016/?2/?19 16:06 ???: sqlite-users at mailinglists.sqlite.org ??: [sqlite] Multiple

[sqlite] determining is-leap-year in sqlite

My SQLite cte exercise, the output looks like cal in Linux shell: -- with recursive parameters(aday) as ( select date() ), months(n, nm) as ( values(1,'January'),(2,'February'),(3,'March'),(4,'April'),(5,'May'),(6,'June'),

[sqlite] Can't understand why I need this cast

. So why does strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 * 60)) work and strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180 * 60)) not work. Thanks, Rob On 18 Feb 2016, at 11:25, Quan Yong Zhai wrote: > It looks like strftime('%s',...) shou

[sqlite] Can't understand why I need this cast

It looks like strftime('%s',...) should return an integer and strftime('%J', ...) should return a double value. But it always return text value. ???: Rob Willett : ?2016/?2/?18 18:20 ???: SQLite mailing

[sqlite] Can't understand why I need this cast

sqlite>Select typeof(strftime('%s','now')); text sqlite>select typeof('1' + 1); integer ???: Rob Willett : ?2016/?2/?18 18:20 ???: SQLite mailing list ??: [sqlite] Can't

[sqlite] the default synchronous setting in WAL mode is "FULL", not "NORMAL"

http://sqlite.org/pragma.html#pragma_synchronous Says:?NORMAL is the default when in WAL mode.? But, sqlite3 e:\files\t.db SQLite version 3.8.8.3 2015-02-25 13:29:11 Enter ".help" for usage hints. sqlite> pragma journal_mode; wal sqlite> pragma synchronous; -- ??? 2

[sqlite] A question

Create an r-tree index on poitable(poiid), the query time of your SQL will drop to 1ms. http://www.sqlite.org/rtree.html ???: ??? : ?2016/?2/?17 16:34 ???: sqlite-users at mailinglists.sqlite.org

[sqlite] query Benchmark

http://sqlite.org/rtree.html To improve your query performance, you need a r-tree index on DateTime ???: Michele Pradella : ?2016/?2/?12 17:28 ???: sqlite-users at mailinglists.sqlite.org

[sqlite] load a .db3 file into in-memory database

>From: ??? >Sent: 2016?1?19? 14:04 >To: sqlite-users at mailinglists.sqlite.org >Subject: Re: [sqlite] load a .db3 file into in-memory database >Now i want to load the whole database into memory. Is there a direct method to >do it? Should i load record one by one by myself? Using the SQLite

[sqlite] 答复: Best way to terminate a dead-transaction: commit or rollback?

I prefer to COMMIT TRANSACTION, because 1. As a rule, all successfully executed transactions should be commit, otherwise should be rollback. 2. To rollback a transaction with no errors occurred will misguide some developers. 3. if someone puts a writing statement or function in the " read

[sqlite] 答复: Do you mean utf8_fprintf on this commit ?

Hi, Mistachkin Thanks for you fix the shell There is still some issue in the sqlite shell, in windows sqlite3_open need file name encoding with utf-8, and fopen need ansi , if the file name in command line include multi-byte characters, there will be a problem, eg Shell.exe ???.db // not

[sqlite] A small patch for the SQLite shell in windows.

bstr(y,3,1) from t; ?| B|? sqlite> select length(''); 4 sqlite> select substr('',2,3); ??? sqlite> select instr('','?'); 4 From: Richard Hipp Sent: 2015?12?30? 21:38 To: Quan Yong Zhai Cc: SQLite mailing list Subject: Re: [sqlite] A small patch for the SQLite shell in windows.

[sqlite] A small patch for the SQLite shell in windows.

>From: Richard Hipp >Sent: 2015?12?30? 20:21 >To: SQLite mailing list >Subject: Re: [sqlite] A small patch for the SQLite shell in windows. >Please send a unified diff, generated using the -u option to the >"diff" command.? Even better would be the "-U 8" option, to include >more context. diff

[sqlite] A small patch for the SQLite shell in windows.

Hi, I have a small patch for the SQLite Shell 3.9.2, it convert sql command text to utf-8 before sending to SQLite engine, And convert the result text back to default code page after sqlite3_exec. Before patch( SQLite shell in windows 10 Chinese version): SQLite version 3.9.2 2015-11-02

[sqlite] 答复: about compile configure

pragma mmap_size= 51200; Pragma page_size = 8192; Vacuum; Pragma cache_size = N; ???: ??? : ?2015/?12/?21 18:08 ???: SQLite mailing list ??: Re: [sqlite] about compile configure

[sqlite] 答复: Very Strange and Interesting Problem

https://www.sqlite.org/lang_createtable.html#rowid ???: ??? : ?2015/?12/?14 18:21 ???: sqlite-users at mailinglists.sqlite.org ??: [sqlite] Very Strange and Interesting Problem hi,

[sqlite] 答复: Making data unique

Create Table T(ID integer, datetime Integer, data integer, primary key(ID, datetime)) ???: Andrew Stewart : ?2015/?12/?7 23:01 ???: 'SQLite mailing list' ??: [sqlite] Making

[sqlite] Possible bug?

SQLite version 3.9.2 2015-11-02 18:31:45 sqlite> .header on sqlite> select 0x1zzz; zzz 1 sqlite>