[sqlite] Maybe A Bug in [fcntlSizeHint] function of SQLite?

2016-06-08 Thread sanhua.zh
In the source code of [fcntlSizeHint] in SQLite 3.9.2, Its comment said that "If the database is already nBytes or larger, this routine is a no-op.”. But if SQLITE_MAX_MMAP_SIZE0, there is no judgement about file size. #if SQLITE_MAX_MMAP_SIZE0 if( pFile-mmapSizeMax0 nBytepFile-mmapSize ){

[sqlite] The usage of SQLITE_ENABLE_ATOMIC_WRITE?

2016-05-27 Thread sanhua.zh
What’s the usage ofSQLITE_ENABLE_ATOMIC_WRITE macro? I can’t find any document or comment about it. How can I make sure whether I should open it? Will it reduce the corruption of SQLite or increase the performance? ___ sqlite-users mailing list

[sqlite] Why Corrupted DB File Cause No Error?

2015-12-02 Thread sanhua.zh
I make a database, create a table and insert some data. And then delete a section of data manually using binary editor, result that I can?t get any of column but SQLite does not give me a error message. console log before db file corrupted: sqlite .open test.db sqlite select * from testtable;

[sqlite] Why Corrupted DB File Cause No Error?

2015-12-02 Thread sanhua.zh
:2015?12?2?(??)?19:38 ??:Re: [sqlite] Why Corrupted DB File Cause No Error? On Wed, Dec 2, 2015 at 11:59 AM, sanhua.zh sanhua.zh at foxmail.com wrote: Why it does not show error, some thing like?Error: database disk image is malformed?? Is it a bug in SQLite? because you didn't corrupt a part

[sqlite] Why Corrupted DB File Cause No Error?

2015-12-02 Thread sanhua.zh
listsqlite-users at mailinglists.sqlite.org :2015?12?2?(??)?19:42 ??:Re: [sqlite] Why Corrupted DB File Cause No Error? On 2 Dec 2015, at 11:38am, Stephan Beal sgbeal at googlemail.com wrote: On Wed, Dec 2, 2015 at 11:59 AM, sanhua.zh sanhua.zh at foxmail.com wrote: Why it does not show error

[sqlite] Why Corrupted DB File Cause No Error?

2015-12-02 Thread sanhua.zh
/2/15, sanhua.zh sanhua.zh at foxmail.com wrote: I make a database, create a table and insert some data. And then delete a section of data manually using binary editor, Did you really "delete" the data, or did you simply change the data to have all NULL content? -- D. Richar

[sqlite] Why SQLITE_BUSY?

2015-12-14 Thread sanhua.zh
I queue all my db operation into one thread with single sqlite conn. neither multi-thread nor multi-process operation happened.But some SQLITE_BUSY error code still be catched. I can not re-produce this error code indeveloping environment,because it happen in alow probability. I only catch this

[sqlite] 回复: Why SQLITE_BUSY?

2015-12-15 Thread sanhua.zh
I?m very excited that I re-produce the SQLITE_BUSY code in a simple demo. Here is my test code, void showResultCode(int resultCode) { if (resultCode!=SQLITE_DONEresultCode!=SQLITE_OKresultCode!=SQLITE_ROW) { NSLog(@"unexperted result %d", resultCode); } } void SQLiteLog(void* userInfo,

[sqlite] 回复: Why SQLITE_BUSY?

2015-12-15 Thread sanhua.zh
- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von sanhua.zh Gesendet: Dienstag, 15. Dezember 2015 10:27 An: SQLite mailing list Betreff: [sqlite] ??? Why SQLITE_BUSY? I?m very excited that I re-produce the SQLITE_BUSY

[sqlite] SQLite take lower performance while using shared cache on iOS/Mac

2015-12-17 Thread sanhua.zh
I try to use shared cache to optimize my code. Sincesqlite3_enable_shared_cache is deprecated on iOS/Mac, I usesqlite3_open_v2 withSQLITE_OPEN_SHAREDCACHE flag to open shared cache mode. 4 threads select is running in my code, while each thread has its own sqlite connection and do the same

[sqlite] {Spam?} SQLite take lower performance while usingshared cache on iOS/Mac

2015-12-20 Thread sanhua.zh
on iOS/Mac 17 dec 2015, sanhua.zh: I try to use shared cache to optimize my code. Sincesqlite3_enable_shared_cache is deprecated on iOS/Mac, I usesqlite3_open_v2 withSQLITE_OPEN_SHAREDCACHE flag to open shared cache mode. 4 threads select is running in my code, while each thread has its own

[sqlite] Why SQLite take lower performance in multi-threadSELECTing?

2015-11-02 Thread sanhua.zh
le (threadWait); //begin [Ticker tickWithInfo:start]; sqlite3_exec(handle, "SELECT * FROM testtable", NULL, NULL, NULL); [Ticker stopWithInfo:end]; sqlite3_close(handle); }); } } @end ???:Richard Hippdrh at sqlite.org ???:SQLite mailing

[sqlite] Why SQLite take lower performance inmulti-threadSELECTing?

2015-11-02 Thread sanhua.zh
lite-users at mailinglists.sqlite.org :2015?11?2?(??)?10:45 ??:Re: [sqlite] Why SQLite take lower performance inmulti-threadSELECTing? On 2 Nov 2015, at 2:19am, sanhua.zh sanhua.zh at foxmail.com wrote: it seems that this mail list will filter my code. I re-send it now. It?s written by Objective-C and C

[sqlite] Why SQLite take lower performance inmulti-threadSELECTing?

2015-11-02 Thread sanhua.zh
I change my code to make it clear. - (double)now { struct timeval time; gettimeofday(time, NULL); double ms = time.tv_sec+time.tv_usec/100.0; return ms; } - (void)test { ... double before = [self now]; sqlite3_exec(handle, "SELECT * FROM testtable", NULL, NULL, NULL); double

[sqlite] Why SQLite take lower performanceinmulti-threadSELECTing?

2015-11-02 Thread sanhua.zh
reasons making lower performance. ???:Simon Slavinslavins at bigfraud.org ???:SQLite mailing listsqlite-users at mailinglists.sqlite.org :2015?11?2?(??)?11:38 ??:Re: [sqlite] Why SQLite take lower performanceinmulti-threadSELECTing? On 2 Nov 2015, at 3:12am, sanhua.zh sanhua.zh

[sqlite] Is it possible that keep SQLite in normal locking modewithout mmap?

2015-10-09 Thread sanhua.zh
Hello, I am using SQLite on iOS developing. Now I need the better performance of multithread-reading-and-writing and the robustness ofWAL journal mode without mmap. As I know,WALjournal mode without mmap requires EXCLUSIVE locking mode, which may prevent multithread-reading-and-writing. But mmap

[sqlite] Is it possible that keep SQLite in normal lockingmodewithout mmap?

2015-10-09 Thread sanhua.zh
at bigfraud.org ???:General Discussion of SQLite Databasesqlite-users at mailinglists.sqlite.org :2015?10?9?(??)?20:38 ??:Re: [sqlite] Is it possible that keep SQLite in normal lockingmodewithout mmap? On 9 Oct 2015, at 1:29pm, sanhua.zh sanhua.zh at foxmail.com wrote: 2. multithread-reading

[sqlite] How to customize my own wal-index using heap memory?

2015-10-13 Thread sanhua.zh
Ashttps://www.sqlite.org/wal.html mentioned, Specialized applications for which the default implementation of shared memory is unacceptable can devise alternative methods via a customVFS. For example, if it is known that a particular database will only be accessed by threads within a single

[sqlite] Why SQLite take lower performance in multi-thread SELECTing?

2015-10-30 Thread sanhua.zh
Hi, all I use SQLite in iOS. I found that SQLite will take lower performancein multi-thread SELECTing. Here is my test result, It costs 0.11s to select 100, elements,in 1-thread SELECTing: But the sameIn 4-thread SELECTing, it costs 0.2s avg. This test run on iPhone 6s. You can see that

[sqlite] Why SQLite use busy-retry but not lock-and-wait?

2016-04-06 Thread sanhua.zh
Recently, I am reading the source code of SQLite. I found that on OS level, SQLite use file lock to solve multi-processes problem and use VFS to solve multi-threads problem. But all of them might failed with racing and SQLite will return a SQLITE_BUSY result code to make it sleep-and-retry. I

[sqlite] Why SQLite use busy-retry but not lock-and-wait?

2016-04-06 Thread sanhua.zh
Slavinslavins at bigfraud.org ???:SQLite mailing listsqlite-users at mailinglists.sqlite.org :2016?4?6?(??)?13:31 ??:Re: [sqlite] Why SQLite use busy-retry but not lock-and-wait? On 6 Apr 2016, at 5:03am, sanhua.zh sanhua.zh at foxmail.com wrote: I found that on OS level, SQLite use file lock to solve

[sqlite] Why SQLite use busy-retry but not lock-and-wait?

2016-04-06 Thread sanhua.zh
busy-retry but not lock-and-wait? sanhua.zh wrote: in the unixLock, we can use F_SETLKW instead of F_SETLK, so that code will return immediatly when file unlocked. We have not need to sleep- and-retry, which may waste our running time. But then SQLite would have no control over the waiting

[sqlite] Why SQLite use busy-retry but not lock-and-wait?

2016-04-06 Thread sanhua.zh
6:03 AM, sanhua.zh wrote: Recently, I am reading the source code of SQLite. I found that on OS level, SQLite use file lock to solve multi-processes problem and use VFS to solve multi-threads problem. But all of them might failed with racing and SQLite will return a SQLITE_BUSY result code

[sqlite] Why SQLite lock 510 bytes for shared lock?

2016-04-07 Thread sanhua.zh
I found the code and the comment at [unixLock] of os_unix.h is different. The comment say that a shared lock will lock a random byte from ?shared byte range?(which is 510 bytes length). But the code show that it locks the whole ?shared byte range?. It makes me confused. Which one does the right

[sqlite] Why SQLite lock 510 bytes for shared lock?

2016-04-08 Thread sanhua.zh
?4?7?(??)?21:35 ??:Re: [sqlite] Why SQLite lock 510 bytes for shared lock? On 4/7/16, sanhua.zh sanhua.zh at foxmail.com wrote: I found the code and the comment at [unixLock] of os_unix.h is different. The comment say that a shared lock will lock a random byte from ?shared byte range?(which

[sqlite] empty page corrupt

2016-02-15 Thread sanhua.zh
I find some db corrupted and try to find out the reason. After a simple analysis, I found that many of the db pages are empty, which means all bits in this page is zero. This is the reason causing sqlite report db corrupt. I don?t know why this happen, does someone have the same problem ever?

[sqlite] empty page corrupt

2016-02-15 Thread sanhua.zh
I did it on iOS. I already checked the howtocorrupt.html, but I can?t find anything helpful. ???:Clemens Ladischclemens at ladisch.de ???:sqlite-userssqlite-users at mailinglists.sqlite.org :2016?2?15?(??)?18:42 ??:Re: [sqlite] empty page corrupt sanhua.zh wrote: I find some db

[sqlite] empty page corrupt

2016-02-15 Thread sanhua.zh
at mailinglists.sqlite.org :2016?2?15?(??)?20:03 ??:Re: [sqlite] empty page corrupt On 15 Feb 2016, at 11:59am, sanhua.zh sanhua.zh at foxmail.com wrote: I did it on iOS. I already checked the howtocorrupt.html, but I can?t find anything helpful. This is almost always a result of hardware failure

[sqlite] Why skip invoking busy handler while pBt->inTransaction!=TRANS_NONE

2016-02-24 Thread sanhua.zh
In the source code of SQLite, btree.c, sqlite3BtreeBeginTrans function, The code do { /* Call lockBtree() until either pBt-pPage1 is populated or ** lockBtree() returns something other than SQLITE_OK. lockBtree() ** may return SQLITE_OK but leave pBt-pPage1 set to 0 if after ** reading

[sqlite] Why skip invoking busy handler whilepBt->inTransaction!=TRANS_NONE

2016-02-25 Thread sanhua.zh
-inTransaction!=TRANS_NONE On 02/24/2016 08:32 PM, sanhua.zh wrote: In the source code of SQLite, btree.c, sqlite3BtreeBeginTrans function, The codedo { /* Call lockBtree() until either pBt-pPage1 is populated or ** lockBtree() returns something other than SQLITE_OK. lockBtree() ** may return

[sqlite] Why skip invoking busy handlerwhilepBt->inTransaction!=TRANS_NONE

2016-02-25 Thread sanhua.zh
at mailinglists.sqlite.org :2016?2?25?(??)?17:36 ??:Re: [sqlite] Why skip invoking busy handlerwhilepBt-inTransaction!=TRANS_NONE On 02/25/2016 09:53 AM, sanhua.zh wrote: So can this be understood as, if I run my code in WAL, I can invoke busy handler even it in TRAN_READ? No. The busy-handler

[sqlite] Database Corrupt While Disk Full

2016-01-14 Thread sanhua.zh
Recently, my monitoring system showed that the error code SQLITE_FULL and SQLITE_CORRUPT increasing in same trend. And thousands of users, who?s database is corrupt, also traped inlow disk free space and their log show that SQLITE_IOERR, SQLITE_FULL happenedsimultaneously. I confuse that

[sqlite] Database Corrupt While Disk Full

2016-01-14 Thread sanhua.zh
On 14 Jan 2016, at 8:44am, sanhua.zh sanhua.zh at foxmail.com wrote: Recently, my monitoring system showed that the error code SQLITE_FULL and SQLITE_CORRUPT increasing in same trend. And thousands of users, who?s database is corrupt, also traped inlow disk free space and their log show

[sqlite] Database Corrupt While Disk Full

2016-01-14 Thread sanhua.zh
] Database Corrupt While Disk Full On 14 Jan 2016, at 9:58am, sanhua.zh sanhua.zh at foxmail.com wrote: Through the error code timeline, it shows that much of SQLITE_FULL, SQLITE_IOERR, SQLITE_CANTOPEN happened before SQLITE_CORRUPT. You should never get to this situation. The three result codes

[sqlite] Database Corrupt While Disk Full

2016-01-14 Thread sanhua.zh
I don?t mean to be rude. BUT, I can?t agree with your opinion, Simon. 1. Not all other codes except SQLITE_OK, SQLITE_ROW, SQLITE_DONE should be treated as fatal errors. As an example, SQLITE_BUSY indicates that this op is temporarily failed, but it can be done later. (Note that sometimes

[sqlite] SQLite Corruption By Writing NULL Data

2016-03-04 Thread sanhua.zh
I am debugging db corruption. After I get some corrupted db, I found that they all corrupted by writing null data. So, I decide to add some check and dump call stackin the source code in order to find out who corrupts the db. Here is the code I added in the source code. int

[sqlite] SQLite Corruption By Writing NULL Data

2016-03-04 Thread sanhua.zh
NULL Data On 4 Mar 2016, at 8:22am, sanhua.zh sanhua.zh at foxmail.com wrote: 3. I guess it could be a problem of operating system. I work on iOS, but I have no any further idea. Almost all of these problems are caused by your program doing one of these A) Writing its own data into a pointer

[sqlite] SQLite Is Not Support Uint64?

2016-05-16 Thread sanhua.zh
I insert a value 18446744072708321492 as a integer into the table. In C code,sqlite3_column_type tells me is a type of 2, which is float point value. And I usesqlite3_column_double to get it. It returns18446744072708321280, which is already lost theaccuracy. Is SQLite not support Uint64 value,

[sqlite] How to study the Vdbe in SQLite?

2016-05-30 Thread sanhua.zh
I find that sqlite.org only explains the reference and a fewobsoletetutorialabout vdbe. Without a“Getting Started” tutorial, I feel a little hard to trace the source code in this part, although I have already read the btree part. Is there any suggestion for theinitiate like me to start up the

[sqlite] SQLite 4

2016-07-26 Thread sanhua.zh
Hello, I found this pagewhich is about SQLite 4. But there are no download page in it and the most recently update is 2015.08.15. Is SQLite 4 in a developing status? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

[sqlite] How to customize "Bytes of unused 'reserved' space at the end of each page" ?

2016-08-16 Thread sanhua.zh
Now I want to add my customize data for each page. And I find thatIt describles the"Bytes of unused‘reserved' space at the end of each page”. I think it’s the exact thing what I need. But I have no idea how to use it. It seems that there is no interface to do this. Can any one tell me about the

Re: [sqlite] How to customize "Bytes of unused 'reserved' spaceat the end of each page" ?

2016-08-16 Thread sanhua.zh
e end of each page" ? sanhua.zh wrote: It describles the"Bytes of unused‘reserved' space at the end of each page”. I think it’s the exact thing what I need. But I have no idea how to use it. It seems that there is no interface to do this. sqlite3_test_control(), or a he

Re: [sqlite] Why MMAP return ENOMEM in SQLite?

2016-08-31 Thread sanhua.zh
at most 3.2GB. The test device is iPhone 6S. 原始邮件 发件人:Simon slavinslav...@bigfraud.org 收件人:SQLite mailing listsqlite-us...@mailinglists.sqlite.org 发送时间:2016年8月31日(周三)  14:38 主题:Re: [sqlite] Why MMAP return ENOMEM in SQLite? On 31 Aug 2016, at 4:40am, sanhua.zh sanhua...@foxmail.com wrote

Re: [sqlite] Why MMAP return ENOMEM in SQLite?

2016-08-31 Thread sanhua.zh
? On Wed, Aug 31, 2016 at 10:43 AM, Stephan Beal sgb...@googlemail.com wrote: On Wed, Aug 31, 2016 at 10:39 AM, sanhua.zh sanhua...@foxmail.com wrote: In my testcase, I can only [mmap] a db file at most 1.4GB size. But in this new way, I can map a file at most 3.2GB. The test device is iPhone 6S

Re: [sqlite] Why MMAP return ENOMEM in SQLite?

2016-08-31 Thread sanhua.zh
, 2016 at 10:55 AM, sanhua.zh sanhua...@foxmail.com wrote: Yes, [mmap] can be larger than physical memory. Indeed, my mistake. And what do you think about the new mapping way I mentioned ? i think it's a "huge can of worms" - it's asking for more, bigger problems than the perceived p

Re: [sqlite] Why MMAP return ENOMEM in SQLite?

2016-08-31 Thread sanhua.zh
OK, I get your idea. Thanks for your advice. :) 原始邮件 发件人:Stephan bealsgb...@googlemail.com 收件人:SQLite mailing listsqlite-us...@mailinglists.sqlite.org 发送时间:2016年8月31日(周三) 17:09 主题:Re: [sqlite] Why MMAP return ENOMEM in SQLite? On Wed, Aug 31, 2016 at 11:03 AM, sanhua.zh sanhua...@foxmail.com

Re: [sqlite] Why MMAP return ENOMEM in SQLite?

2016-08-31 Thread sanhua.zh
listsqlite-us...@mailinglists.sqlite.org 发送时间:2016年8月31日(周三) 14:38 主题:Re: [sqlite] Why MMAP return ENOMEM in SQLite? On 31 Aug 2016, at 4:40am, sanhua.zh sanhua...@foxmail.com wrote: Is it caused by mmap file too big that the address space is not enough? Yes. Under iOS, memory maps can only

[sqlite] Why MMAP return ENOMEM in SQLite?

2016-08-30 Thread sanhua.zh
I try to mmap a BIG file which is around 1.8GB size, on iOS. Then I found that [sqlite3OSFetch] doesn’t work at all, which means the mmap is disable. After debug, I find that [osMmap] in [unixRemapFile] return an error. The error code is 12, named ENOMEM, which means cannot allocate memory. My

Re: [sqlite] Why MMAP return ENOMEM in SQLite?

2016-08-31 Thread sanhua.zh
? On 31 Aug 2016, at 9:39am, sanhua.zh sanhua...@foxmail.com wrote: Yes. even on iDevice, [mmap] can get faster than sequential I/O. Sometime it will be twice faster. Unless your users have complained about speed, this does not matter. There is no need to make everything happen as fast

[sqlite] 回复: The Performance Between [Drop Table] And [Rename Table] ?

2016-10-09 Thread sanhua.zh
Now I found the possible reason why [Rename Table] become slow. While the one table renamed, the schema need to be updated. But SQLite use the ['SELECT name, rootpage, sql FROM 'main'.sqlite_master WHERE tbl_name=’tablename' ORDER BY rowid”] to update the schema. Isn’t it too ugly ? Why we

[sqlite] CREATE TABLE/ALTER TABLE Slow Down When Table Count Grow

2016-10-09 Thread sanhua.zh
I foundCREATE TABLE/ALTER TABLE Slow Down When Table Count Grow. Since those SQLs modify the schema, SQLite use theOP_ParseSchema to update them, which cause the search of sqlite_master. (SELECT name, rootpage, sql FROM '%q'.%s WHERE %s ORDER BY rowid) As we all know, sqlite_master has no index

Re: [sqlite] CREATE TABLE/ALTER TABLE Slow Down When Table CountGrow

2016-10-10 Thread sanhua.zh
be no problem 原始邮件 发件人:Simon slavinslav...@bigfraud.org 收件人:SQLite mailing listsqlite-us...@mailinglists.sqlite.org 发送时间:2016年10月10日(周一) 17:03 主题:Re: [sqlite] CREATE TABLE/ALTER TABLE Slow Down When Table CountGrow On 10 Oct 2016, at 4:52am, sanhua.zh sanhua...@foxmail.com wrote: I

[sqlite] Drop Existing Table Results in 'no such table'

2016-10-20 Thread sanhua.zh
I am trying to rename a table by editing the sqlite_master directly. I do know there are a ‘ALTER TABLE … RENAME TO …’ syntax to do that. I just want to try it in this way. But I find that the ‘no such table’ error occurs while I try to drop the renamed table. Only I close the connection or

Re: [sqlite] Drop Existing Table Results in 'no such table'

2016-10-21 Thread sanhua.zh
] Drop Existing Table Results in 'no such table' sanhua.zh wrote: I am trying to rename a table by editing the sqlite_master directly. Don't do this. But I find that the ‘no such table’ error occurs while I try to drop the renamed table. Only I close the connection or start a new connection

Re: [sqlite] Drop Existing Table Results in 'no such table'

2016-10-21 Thread sanhua.zh
:SQLite mailing listsqlite-us...@mailinglists.sqlite.org 发送时间:2016年10月21日(周五) 14:39 主题:Re: [sqlite] Drop Existing Table Results in 'no such table' On 21 Oct 2016, at 4:42am, sanhua.zh sanhua...@foxmail.com wrote: I am trying to rename a table by editing the sqlite_master directly. I do know

Re: [sqlite] Drop Existing Table Results in 'no such table'

2016-10-21 Thread sanhua.zh
in 'no such table' On 21 Oct 2016, at 4:42am, sanhua.zh sanhua...@foxmail.com wrote: I am trying to rename a table by editing the sqlite_master directly. I do know there are a ‘ALTER TABLE … RENAME TO …’ syntax to do that. I just want to try it in this way. But I find that the ‘no such table

[sqlite] Is msync necessary for SQLite?

2016-11-03 Thread sanhua.zh
I notice that SQLite may not call msync for mmap. Instead, it calls the fsync. As my search, fsync does not imply the msync. See this,https://groups.google.com/forum/#!topic/comp.unix.programmer/pIiaQ6CUKjU So, is it necessary call a msync for SQLite?

Re: [sqlite] Is msync necessary for SQLite?

2016-11-03 Thread sanhua.zh
时间:2016年11月3日(周四) 17:38 主题:Re: [sqlite] Is msync necessary for SQLite? On 11/03/2016 02:13 PM, sanhua.zh wrote: I notice that SQLite may not call msync for mmap. Instead, it calls the fsync. As my search, fsync does not imply the msync. See this,https://groups.google.com/forum/#!topic

Re: [sqlite] Is msync necessary for SQLite?

2016-11-03 Thread sanhua.zh
for SQLite? On 11/3/16, sanhua.zh sanhua...@foxmail.com wrote: I run in iOS. The mmap is enable in recent version. So, once a mmap enable, data may lost after os crash unless msync. But I can’t find anywhere in the source of SQLite call the msync. Is it a bug? No. Mmap() is only used for reading

Re: [sqlite] Is msync necessary for SQLite?

2016-11-03 Thread sanhua.zh
So, it means that this is a bug in old version of SQLite ? 原始邮件 发件人:Dan kennedydanielk1...@gmail.com 收件人:sqlite-userssqlite-us...@mailinglists.sqlite.org 发送时间:2016年11月3日(周四) 18:38 主题:Re: [sqlite] Is msync necessary for SQLite? On 11/03/2016 04:53 PM, sanhua.zh wrote: Here is a part of code

Re: [sqlite] Is msync necessary for SQLite?

2016-11-03 Thread sanhua.zh
? On 11/3/16, sanhua.zh sanhua...@foxmail.com wrote: Here is a part of code in [unixWrite]. (os_unix.h) Is not it written by mmap ? You are looking at older code that is not longer used. Look at latest sources and you will see that the code you quote below is enclosed within #if defined

Re: [sqlite] CREATE TABLE/ALTER TABLE Slow Down When Table CountGrow

2016-10-10 Thread sanhua.zh
? -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of sanhua.zh Sent: Sunday, 9 October, 2016 21:53 To: sqlite-users Subject: [sqlite] CREATE TABLE/ALTER TABLE Slow Down When Table Count Grow I foundCREATE TABLE/ALTER TABLE Slow

[sqlite] The Performance Between [Drop Table] And [Rename Table] ?

2016-10-09 Thread sanhua.zh
I found that [Rename Table](using ‘ALTER TABLE RENAME TO') is much slower than [Drop Table]. The cost of [Rename Table] may be twice, even if the table is empty(which means it has no index, no trigger, no view and no column). As I known, both [Drop Table] and [Rename Table] just modify the

[sqlite] Why SQLite Use Different Malloc Zone While CPU Count > 1?

2017-01-11 Thread sanhua.zh
Following the code in mem1.c, Why SQLite Use Different Malloc Zone While CPU Count 1? static int sqlite3MemInit(void *NotUsed){ #if defined(__APPLE__) !defined(SQLITE_WITHOUT_ZONEMALLOC) int cpuCount; size_t len; if( _sqliteZone_ ){ return SQLITE_OK; } len = sizeof(cpuCount); /* One

[sqlite] Is it safe to use same sqlite connection sequentially between threads ?

2017-08-14 Thread sanhua.zh
Here is a sample. As for a specific sqlite connection, 1. sql is executed in thread A 2. sql is prepared in thread B 3. Prepared statement in 2. is stepped and finalized in thread C All 1. 2. 3. steps are run sequentially, which means that the step 2 runs after step 1 finished and step 3 runs

[sqlite] A Descending Auto Increment Key

2017-07-19 Thread sanhua.zh
When I call `CREATE TABLE test(i INTEGER PRIMARY KEY DESC AUTO INCREMENT)`, SQLite comes to an error "AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY". But as the document http://www.sqlite.org/lang_createtable.htmlsays, in the column-constraint, it shows that `PRIMARY DESC

Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !

2017-08-20 Thread sanhua.zh
assert(rc==0); ``` Original Message Sender:Jay kreibich...@kreibi.ch Recipient:SQLite mailing listsqlite-us...@mailinglists.sqlite.org Date:Friday, Aug 18, 2017 19:46 Subject:Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed ! On Aug 18, 2017, at 4:04 AM, sanhua.zh sanhua...@foxmail

Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !

2017-08-20 Thread sanhua.zh
al Message Sender:Jay kreibich...@kreibi.ch Recipient:SQLite mailing listsqlite-us...@mailinglists.sqlite.org Date:Friday, Aug 18, 2017 19:46 Subject:Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed ! On Aug 18, 2017, at 4:04 AM, sanhua.zh sanhua...@foxmail.com wrote: I am using SQLit

Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !

2017-08-20 Thread sanhua.zh
Subject:Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed ! On 21 Aug 2017, at 3:22am, sanhua.zh sanhua...@foxmail.com wrote: If so, who or which doc can tell me that which SQL will or will not update the schema ? Commands with CREATE, DROP, or ALTER update schema. Simon

[sqlite] SQLite's Results Are Expired While Schema Is Changed !

2017-08-18 Thread sanhua.zh
I am using SQLite in multi-thread mode, which means that different threads using different SQLite connection. And now I find an issue that the results of SQLite C interface returned is expired while the schema of database is changed. The following sample runs in different threads, but I force

[sqlite] PRAGMA table_info could not update schema

2017-08-21 Thread sanhua.zh
I find that `PRAGMA table_info(tableName)` will not check the expired schema which is modified by other sqlite connections. 1. Open conn 1 and conn 2. 2. Run a SQL to load the schema for conn 2 3. Change the schema using conn 1 by create-table-statement. 4. Get the schema using conn 2 by

Re: [sqlite] PRAGMA table_info could not update schema

2017-08-21 Thread sanhua.zh
serssqlite-us...@mailinglists.sqlite.org Date:Monday, Aug 21, 2017 17:25 Subject:Re: [sqlite] PRAGMA table_info could not update schema sanhua.zh wrote: I find that `PRAGMA table_info(tableName)` will not check the expired schema which is modified by other sqlite connections. Here is the sample code:

[sqlite] UNION ALL bug in Multi-threading

2018-08-01 Thread sanhua.zh
I find a bug that exists in `UNION ALL`. tldr: `UNION ALL` will not merge the data in different schemas in the moment that one of schema is committed but the another not. BUT, `UNION` will. Here are the reproduce steps: Preparation: 1. Prepare a database named "OLD" 1.1 Create a table for

[sqlite] UNION ALL bug in Multi-threading

2018-08-01 Thread sanhua.zh
I find a bug that exists in `UNION ALL`. tldr: `UNION ALL` will not merge the data in different schemas in the moment that one of schema is committed but the another not. BUT, `UNION` will. Here are the reproduce steps: Preparation: 1. Prepare a database named "OLD" 1.1 Create a table for

[sqlite] How to use WITH CLAUSE in a UPDATE statement?

2018-03-08 Thread sanhua.zh
I find that a UPDATE statement with WITH CLAUSE always fails, although I use the syntax as SQLite syntax suggested. Also, the `lang_with.html` do no show a UPDATE statement with WITH CLAUSE. They all run in a SELECT statement. Here is the sample SQL I tried: ``` CREATE TABLE t(i INTEGER);

[sqlite] How to use WITH CLAUSE in a UPDATE statement?

2018-03-08 Thread sanhua.zh
I find that a UPDATE statement with WITH CLAUSE always fails, although I use the syntax as SQLite syntax suggested. Also, the `lang_with.html` do no show a UPDATE statement with WITH CLAUSE. They all run in a SELECT statement. Here is the sample SQL I tried: ``` CREATE TABLE t(i INTEGER);

[sqlite] ROLLBACK TO stmt within committed transaction cause write operation to the WAL file.

2018-12-27 Thread sanhua.zh
Here is the reproduction path. ``` sqlite3 db PRAGMA journal_mode=WAL; CREATE TABLE t(i INTEGER); INSERT INTO t VALUES(1); PRAGMA wal_checkpoint=TRUNCATE; // check the WAL file size without checkpoint/close sqlite connection ls -l | grep wal // print 0 db-wal BEGIN IMMEDIATE; SAVEPOINT s1;