Re: [sqlite] round function inconsistent
Great! Now use SQLite API and add a new user defined function for your used case. I suppose that SQlite should always follow a well-defined pattern: in this case as Mr. Hipp said, it is IEEE754. Cheers! > On 24 May 2019, at 08:27, radovan5 wrote: > > In Delphi I have 2 functions that works (I override default sqlite round): > > const > ExtEps = 1.0842021725E-19; > DblEps = 2.2204460493E-16; > KnownErrorLimit = 1.234375; > SafetyFactor = 2; > MaxRelErrDbl = DblEps * KnownErrorLimit * SafetyFactor; > MaxRelErrExt = ExtEps * KnownErrorLimit * SafetyFactor; > > function RoundExt(const AValue: Extended; const ADigit: Integer = -2): > Extended; > var > E: Extended; > begin > E := IntPower(10, -ADigit); > Result := Round(AValue * (1 + MaxRelErrExt) * E) / E; > end; > > function RoundDbl(const AValue: Double; const ADigit: Integer = -2): Double; > var > E: Double; > begin > E := IntPower(10, -ADigit); > Result := Round(AValue * (1 + MaxRelErrDbl) * E) / E; > end; > > You could implement it in sqlite. > > Regards Radovan > > > On 24.05.2019 13:13, Richard Hipp wrote: >> On 5/24/19, Hajo Bruns wrote: >>> Hi, >>> the round function seems to round inconsistently: >>> >>> ivesselect round(5.485,2), round (3.555,2),round (3.255,2) >>> gives >>> 5,49 3,56 3,25 >>> >>> Last result should be 3.26 >> 3.255 cannot be exactly represented as an IEEE754 double-precision >> binary floating point number. So the system has to use an >> approximation. The closest approximation is >> 3.25489341858963598497211933135986328125 and that value >> rounds to 3.25. >> > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange behavior on SQLite 3.7x compared with 3.6.22
Thank you D. Richard Hipp, for your time and nice work. On 10/12/2011, at 15:25, Richard Hipp wrote: On Thu, Dec 8, 2011 at 12:25 PM, Alessandro Merolli <amero...@mwneo.com >wrote: Hi, We've being working with SQLite version 3.6.22 in our project and we wish to upgrade it to the latest one. During the tests with the new library version, we noticed a strange behavior related to a trigger which updates the last inserted row. We where able to simplify the data model and produce a test case which can be executed using the sqlite3 shell This is a real problem that was introduced when we added recursive triggers. But it is very obscure and only comes up when you have really complicated queries inside of triggers - queries that make use of manifested views and/or automatic indices. The trouble ticket is at http://www.sqlite.org/src/info/7bbfb7d442 and the fix is checked in at http://www.sqlite.org/src/info/557c69055a with additional changes at http://www.sqlite.org/src/info/0064bab771 that fix related issues and verify with assert() statements that similar kinds of problems do not recur. These changes will be in the next release. Thanks for providing a test case. That was very helpful in tracking down the problem. Here are the outputs: Execution with latest SQLite 3.7.9: c:\>sqlite3shell.exe -init .\test_case.sql ":memory:" -- Loading resources from .\test_case.sql Expected result: 31|10 Problematic result: 31|0 31|0 SQLite version 3.7.9 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .quit c:\> Execution with latest SQLite 3.6.22: c:\>sqlite3shell.exe -init .\test_case.sql ":memory:" -- Loading resources from .\test_case.sql Expected result: 31|10 Problematic result: 31|0 31|10 SQLite version 3.6.22 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .quit We are using the amalgamation version of the source for both libraries and here are the compilation defines: TEMP_STORE=3 SQLITE_THREADSAFE=2 SQLITE_DEFAULT_CACHE_SIZE=**65568 SQLITE_DEFAULT_TEMP_CACHE_**SIZE=65568 SQLITE_MAX_ATTACHED=30 SQLITE_ENABLE_COLUMN_METADATA SQLITE_ENABLE_UNLOCK_NOTIFY The error was also reproduced on the original library available for Ubuntu 10.10. Additional information is available in the test_case.sql script comments. Thanks for the assistance. Regards, Alessandro Merolli. __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange behavior on SQLite 3.7x compared with 3.6.22
Tested on Windows XP with the binary download from SQlite's website (http://www.sqlite.org/sqlite-shell-win32-x86-3070900.zip ) and the problem occurred. See image below: On 08/12/2011, at 16:23, Black, Michael (IS) wrote: I tested 3.7.9 on both Windows and Linux. Both gave just "31|" as the output instead of "31|0" as you show. And changing the select 9 made no difference...still got the same "31|" answer for both. I'm compiling with default options. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users- boun...@sqlite.org] on behalf of Alessandro Merolli [amero...@mwneo.com ] Sent: Thursday, December 08, 2011 11:25 AM To: General Discussion of SQLite Database Subject: EXT :[sqlite] Strange behavior on SQLite 3.7x compared with 3.6.22 Hi, We've being working with SQLite version 3.6.22 in our project and we wish to upgrade it to the latest one. During the tests with the new library version, we noticed a strange behavior related to a trigger which updates the last inserted row. We where able to simplify the data model and produce a test case which can be executed using the sqlite3 shell. Here are the outputs: Execution with latest SQLite 3.7.9: c:\>sqlite3shell.exe -init .\test_case.sql ":memory:" -- Loading resources from .\test_case.sql Expected result: 31|10 Problematic result: 31|0 31|0 SQLite version 3.7.9 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .quit c:\> Execution with latest SQLite 3.6.22: c:\>sqlite3shell.exe -init .\test_case.sql ":memory:" -- Loading resources from .\test_case.sql Expected result: 31|10 Problematic result: 31|0 31|10 SQLite version 3.6.22 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .quit We are using the amalgamation version of the source for both libraries and here are the compilation defines: TEMP_STORE=3 SQLITE_THREADSAFE=2 SQLITE_DEFAULT_CACHE_SIZE=65568 SQLITE_DEFAULT_TEMP_CACHE_SIZE=65568 SQLITE_MAX_ATTACHED=30 SQLITE_ENABLE_COLUMN_METADATA SQLITE_ENABLE_UNLOCK_NOTIFY The error was also reproduced on the original library available for Ubuntu 10.10. Additional information is available in the test_case.sql script comments. Thanks for the assistance. Regards, Alessandro Merolli. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange behavior on SQLite 3.7x compared with 3.6.22
Here it goes. A more simplified version: -- -- Table updated by the trigger TGR_InventoryControl_AfterInsert -- CREATE TABLE InventoryControl ( InventoryControlId INTEGER PRIMARY KEY AUTOINCREMENT, SKU INTEGER NOT NULL, Variant INTEGER NOT NULL DEFAULT 0, ControlDate DATE NOT NULL, ControlState INTEGER NOT NULL DEFAULT -1, DeliveredQty VARCHAR(30) ); -- -- Trigger fired by the test case -- (It was simplified from the original data model) -- CREATE TRIGGER TGR_InventoryControl_AfterInsert AFTER INSERT ON InventoryControl FOR EACH ROW WHEN NEW.ControlState=-1 BEGIN INSERT OR REPLACE INTO InventoryControl (InventoryControlId,SKU,Variant,ControlDate,ControlState,DeliveredQty) SELECT T1.InventoryControlId AS InventoryControlId, T1.SKU AS SKU, T1.Variant AS Variant, T1.ControlDate AS ControlDate, 1 AS ControlState, COALESCE(T2.DeliveredQty,0) AS DeliveredQty FROM ( SELECT NEW.InventoryControlId AS InventoryControlId, II.SKU AS SKU, II.Variant AS Variant, COALESCE(LastClosedIC.ControlDate,NEW.ControlDate) AS ControlDate FROM InventoryItem II -- -- We noticed that this LEFT JOIN is one of causes: -- If we modify the "IN (SELECT 9)" to "IN (9)" the problem is solved. -- LEFT JOIN InventoryControl LastClosedIC ON LastClosedIC.InventoryControlId IN ( SELECT 9 ) WHERE II.SKU=NEW.SKU AND II.Variant=NEW.Variant ) T1 LEFT JOIN ( SELECT TD.SKU AS SKU, TD.Variant AS Variant, 10 AS DeliveredQty FROM TransactionDetail TD WHERE TD.SKU=NEW.SKU AND TD.Variant=NEW.Variant ) T2 -- -- This is the main problem: The LEFT JOIN never matches the T1 record. -- NOTE: if we change the ON clause to the following (just add a '+' sign): -- ON +T2.SKU=T1.SKU AND -- +T2.Variant=T1.Variant; -- the problem is solved. -- ON T2.SKU=T1.SKU AND T2.Variant=T1.Variant; END; -- -- Addtional table took from the original model necessary for the test case. -- CREATE TABLE InventoryItem ( SKU INTEGER NOT NULL, Variant INTEGER NOT NULL DEFAULT 0, DeptCode INTEGER NOT NULL, GroupCode INTEGER NOT NULL, ItemDescription VARCHAR(120) NOT NULL, PRIMARY KEY(SKU, Variant) ); -- -- Additional note: The test case works only with the record with SKU == 31. But, if we leave only the records -- related to it, the problem does not happen. It's needed to have records before and after it -- (SKUs 220 and 72 in this case). -- INSERT INTO InventoryItem VALUES(220,0,1,170,'Scoth Tampon Recurer'); INSERT INTO InventoryItem VALUES(31,0,1,110,'Fromage'); -- -- Addtional table took from the original model necessary for the test case. -- CREATE TABLE TransactionDetail ( TransactionId INTEGER NOT NULL, SKU INTEGER NOT NULL, Variant INTEGER NOT NULL DEFAULT 0, PRIMARY KEY(TransactionId, SKU, Variant) ); INSERT INTO TransactionDetail(TransactionId, SKU, Variant) VALUES(44, 31, 0); -- ** -- THE TEST CASE -- ** -- This is the INSERT statement which will fire the -- TGR_InventoryControl_AfterInsert trigger. -- NOTE: The INSERT statement must have a SELECT to reproduce -- the problem. INSERT INTO InventoryControl (SKU, Variant, ControlDate) SELECT II.SKU AS SKU, II.Variant AS Variant, '2011-08-30' AS ControlDate FROM InventoryItem II; -- -- Expected RESULT: 31|10 -- Problematic RESULT: 31|0 -- SELECT 'Expected result: 31|10'; SELECT 'Problematic result: 31|0'; SELECT SKU, DeliveredQty FROM InventoryControl WHERE SKU=31; SELECT CASE WHEN DeliveredQty=10 THEN "TEST PASSED!" ELSE "TEST FAILED!" END FROM InventoryControl WHERE SKU=31; On 08/12/2011, at 15:40, Black, Michael (IS) wrote: Your test_case.sql didn't come thru. Can you report it in-ilne with an email? Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users- boun...@sqlite.org] on behalf of Alessandro Merolli [amero...@mwneo.com ] Sent: Thursday, December 08, 2011 11:25 AM To: General Discussion of SQLite Database Subject: EXT :[sqlite] Strange behavior on SQLite 3.7x compared with 3.6.22 Hi, We've being working with SQLite version 3.6.22 in our project and we wish to upgrade it to the latest one. During the tests with the new library version, we noticed a strange behavior related to a trigger which updates the last
[sqlite] Strange behavior on SQLite 3.7x compared with 3.6.22
Hi,
We've being working with SQLite version 3.6.22 in our project and we
wish to upgrade it to the latest one. During the tests with the new
library version, we noticed a strange behavior related to a trigger
which updates the last inserted row. We where able to simplify the
data model and produce a test case which can be executed using the
sqlite3 shell. Here are the outputs:
Execution with latest SQLite 3.7.9:
c:\>sqlite3shell.exe -init .\test_case.sql ":memory:"
-- Loading resources from .\test_case.sql
Expected result: 31|10
Problematic result: 31|0
31|0
SQLite version 3.7.9
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .quit
c:\>
Execution with latest SQLite 3.6.22:
c:\>sqlite3shell.exe -init .\test_case.sql ":memory:"
-- Loading resources from .\test_case.sql
Expected result: 31|10
Problematic result: 31|0
31|10
SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .quit
We are using the amalgamation version of the source for both libraries
and here are the compilation defines:
TEMP_STORE=3
SQLITE_THREADSAFE=2
SQLITE_DEFAULT_CACHE_SIZE=65568
SQLITE_DEFAULT_TEMP_CACHE_SIZE=65568
SQLITE_MAX_ATTACHED=30
SQLITE_ENABLE_COLUMN_METADATA
SQLITE_ENABLE_UNLOCK_NOTIFY
The error was also reproduced on the original library available for
Ubuntu 10.10.
Additional information is available in the test_case.sql script
comments.
Thanks for the assistance.
Regards,
Alessandro Merolli.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Temporary views across attached databases
I've being using this feature massively since version 3.6.7. Now I'm working (production) with 3.6.22. It works just fine. I've being testing 3.7.x and it seems OK too. Best regards. Alessandro Merolli. On 01/10/2010, at 14:26, John Drescher wrote: > I see that normal views across attached databases do not work but it > looks like I can create a temporary view across attached databases. Is > this a supported configuration? I want to make sure before I put this > in my application. > > -- > John M. Drescher > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Strange SIGSEGV while using SQLITE 3.6.17 on Linux Ubuntu 8.04
I'm running SQLite 3.6.17 (almagamation source) on linux Ubuntu 8.04 and i got a strange SIGSEGV (see stack below). The strange thing is: the pPager parameter to sqlite3PagerAcquire function is NULL but right above the stack, its value seems to be correct. Any help is appreciated. Please, check the debugger information: (gdb) where #0 0xb7e40e79 in sqlite3PagerAcquire (pPager=0x0, pgno=10, ppPage=0xb47d24c0, noContent=0) at extsrc/sqlite/src/sqlite3.c:34349 #1 0xb7e44797 in btreeGetPage (pBt=0x8970528, pgno=10, ppPage=0xb262810c, noContent=0) at extsrc/sqlite/src/sqlite3.c:38391 #2 0xb7e44871 in getAndInitPage (pBt=0x8970528, pgno=10, ppPage=0xb262810c) at extsrc/sqlite/src/sqlite3.c:38442 #3 0xb7e47a3e in moveToRoot (pCur=0xb26280b0) at extsrc/sqlite/src/ sqlite3.c:40920 #4 0xb7e47ea5 in sqlite3BtreeMovetoUnpacked (pCur=0xb26280b0, pIdxKey=0xb47d27e8, intKey=0, biasRight=0, pRes=0xb47d27dc) at extsrc/ sqlite/src/sqlite3.c:41137 #5 0xb7e5b19b in sqlite3VdbeExec (p=0xb2627a58) at extsrc/sqlite/src/ sqlite3.c:54136 #6 0xb7e5500b in sqlite3Step (p=0xb2627a58) at extsrc/sqlite/src/ sqlite3.c:49497 #7 0xb7e55213 in sqlite3_step (pStmt=0xb2627a58) at extsrc/sqlite/src/ sqlite3.c:49556 #8 0xb7bd7075 in sqlite3_blocking_step (pStmt=0xb2627a58) at extsrc/ apr-util/dbd/apr_dbd_sqlite3_v2.c:220 #9 0xb7bd79ab in dbd_sqlite3_query (sql=0x8924e98, nrows=0xb47d2a68, query=0x8d2b855 "INSERT INTO orderdb.CurrentOrderItem(OrderId, LineNumber, ItemId, Level, PartCode, OrderedQty, PriceKey) SELECT OI.OrderId, OI.LineNumber, PI.ItemId, PI.RecursionLevel, PI.PartCode, NULL, PP.PriceKey "...) at extsrc/apr-util/dbd/apr_dbd_sqlite3_v2.c:477 #10 0xb7f2cff0 in apr_dbd_query (driver=0xb7bdde60, handle=0x8924e98, nrows=0xb47d2a68, statement=0x8d2b2e0 "DROP TABLE IF EXISTS temp.TempModifiers;CREATE TEMPORARY TABLE IF NOT EXISTS TempModifiers AS SELECT OIM.OrderId AS OrderId,OIM.LineNumber AS LineNumber,PI.PartCode AS ModifiedCode,OIM.PartCode AS Mod"...) at dbd/ apr_dbd.c:311 #11 0x0804eefd in sqlDBExecScrt (mpool=0x8bb4150, pdbconn=0x8924e48, sqlstmt=0x8d2b2e0 "DROP TABLE IF EXISTS temp.TempModifiers;CREATE TEMPORARY TABLE IF NOT EXISTS TempModifiers AS SELECT OIM.OrderId AS OrderId,OIM.LineNumber AS LineNumber,PI.PartCode AS ModifiedCode,OIM.PartCode AS Mod"..., nRows=0xb47d2a68) at src/kernel/ persistcomp/npersistcommon/npersistcommon.c:251 #12 0x0804d172 in execute_script (pool=0x8bb4150, connpool=0xbfb88410, connid=0x8bb6090 "30883532-9d9e-11de-aba1-d3df98c78e4e", stmt_array=0x8bb53a8, timeout=0x8bb60b8 "1", resp=0xb47d2b28, szresp=0xb47d2b24) at src/kernel/persistcomp/npersistagent/ npersistagent.c:923 #13 0x0804c0f6 in request_processor (self=0x8904338, jobctxt=0x8bb4190) at src/kernel/persistcomp/npersistagent/ npersistagent.c:428 #14 0xb7f24cb9 in thread_pool_func (t=0x8904338, param=0x8904150) at misc/apr_thread_pool.c:276 #15 0xb7f55446 in dummy_worker (opaque=0x8904338) at threadproc/unix/ thread.c:142 #16 0xb79ff50f in start_thread () from /lib/tls/i686/cmov/ libpthread.so.0 #17 0xb7b2e7ee in clone () from /lib/tls/i686/cmov/libc.so.6 (gdb) up #1 0xb7e44797 in btreeGetPage (pBt=0x8970528, pgno=10, ppPage=0xb262810c, noContent=0) at extsrc/sqlite/src/sqlite3.c:38391 38391 rc = sqlite3PagerAcquire(pBt->pPager, pgno, (DbPage**), noContent); (gdb) p pBt->pPager $40 = (Pager *) 0x8941808 (gdb) p pBt->pPager->state $42 = 1 '\001' (gdb) p pBt->pPager->tempFile $43 = 0 '\0' Additional informations: - Preprocessor definitions used to build the library: - SQLITE_THREADSAFE=1 - TEMP_STORE=3 - SQLITE_DEFAULT_CACHE_SIZE=65568 - SQLITE_DEFAULT_TEMP_CACHE_SIZE=65568 - SQLITE_MAX_ATTACHED=30 - SQLITE_ENABLE_COLUMN_METADATA - SQLITE_ENABLE_UNLOCK_NOTIFY - uname -a: Linux amerolli-ubuntu 2.6.27-14-generic #1 SMP Tue Aug 18 16:25:45 UTC 2009 i686 GNU/Linux - I'm using "Unlock Notification Feature" (sqlite3_unlock_notify) as described in http://www.sqlite.org/unlock_notify.html. Regards, Alessandro Merolli. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite lock behavior in shared-cache mode
Thanks again Dan for the quick response.
I understood.
Is this also true for any other database file attached to these
threads connections that, as the main.db file, are used for read-only
operations but might have one or more threads reading from it at the
same time?
Alessandro.
On 27/07/2009, at 13:27, Dan wrote:
>
> While compiling any statement (sqlite3_prepare_v2()), or while
> stepping
> (sqlite3_step()) a statement that accesses the main database, a mutex
> associated with the in-memory cache of the main database will be held.
>
> Dan.
>
>
>
> On Jul 27, 2009, at 11:16 PM, Alessandro Merolli wrote:
>
>> Hi,
>>
>> I'm using SQLite latest version (3.6.16) with shared-cache enable in
>> a process that has around 5 threads. Database connections for each
>> thread are created with the same main database file. After that, each
>> connection is attached to a particular database file (one for each
>> thread) using the same schema name. Final structure is similar to the
>> following:
>>
>> Main database file: main.db
>> Thread-1 database file: thread1.db
>> Thread-2 database file: thread2.db
>> Thread-3 database file: thread3.db
>> Thread-4 database file: thread4.db
>> Thread-5 database file: thread5.db
>>
>> Thread-1 connection is opened with the main.db file and attaches the
>> thread1.db as "extradb" schema name;
>> Thread-2 connection is opened with the main.db file and attaches the
>> thread2.db as "extradb" schema name;
>> Thread-3 connection is opened with the main.db file and attaches the
>> thread3.db as "extradb" schema name;
>> Thread-4 connection is opened with the main.db file and attaches the
>> thread4.db as "extradb" schema name;
>> Thread-5 connection is opened with the main.db file and attaches the
>> thread5.db as "extradb" schema name;
>>
>> Every SQL statement submitted to the process and passed to one of
>> these threads can read global informations maintained in the main.db
>> database file ("main" schema) and write/read particular informations
>> in the "extradb" schema in such a way that one thread does not need
>> to
>> wait for another thread to write its information, since each thread
>> has the "extradb" schema attached to a particular database file.
>>
>> Shared-cache is used for 2 reasons:
>> - to improve main.db database file data access; and,
>> - use the "Unlock Notification Feature" (sqlite3_unlock_notify) to
>> avoid many SQLITE_LOCKED errors based on the code provided in
>> http://www.sqlite.org/unlock_notify.html
>> .
>>
>> In my understanding, the expected behavior should be:
>> - If a SQL statement with only read (heavy) operations is passed to
>> Thread-1; and,
>> - Another SQL statement with a write (and some reads) operations is
>> passed to Thread-3;
>> - Both should run in parallel in a multi-core system.
>>
>> But, it seems that the Thread-3 is waiting for the Thread-1 to
>> finish
>> its work before continue.
>> This behavior is turning the solution into a non-scalable solution.
>>
>> As far as I could debug (and understand) using Visual Studio 2005,
>> it
>> seems that Thread-3 (in the above example) is waiting in for a lock
>> in
>> the sqlite3BtreeEnterAll function. See the piece of the call stack
>> below:
>>
>>> sqlite3.dll!winMutexEnter(sqlite3_mutex * p=0x012fb2d8) Line 15159
>> sqlite3.dll!sqlite3BtreeEnterAll(sqlite3 * db=0x1bb05ac8) Line
>> 36706 + 0x11 bytes
>> sqlite3.dll!sqlite3LockAndPrepare(sqlite3 * db=0x, const
>> char * zSql=0x00c0, int nBytes=180, int saveSqlFlag=1,
>> sqlite3_stmt * * ppStmt=0x, const char * * pzTail=0x0965f63c)
>> Line 9672
>> sqlite3.dll!sqlite3_prepare_v2(sqlite3 * db=0x1bb05ac8, const char
>> * zSql=0x00c0, int nBytes=180, sqlite3_stmt * *
>> ppStmt=0x013a9094,
>> const char * * pzTail=0x0965f63c) Line 9747 + 0x1f bytes
>> apr_dbd_sqlite3.dll!sqlite3_blocking_prepare_v2(sqlite3 *
>> db=0x, const char * zSql=, int nSql=, sqlite3_stmt * *
>> ppStmt=0x, const char * * pz=0x) Line 247 + 0x10
>> bytes
>> apr_dbd_sqlite3.dll!dbd_sqlite3_select(apr_pool_t *
>> pool=0x013a9050, apr_dbd_t * sql=0x029cc040, apr_dbd_results_t * *
>> results=0x0965f688, const char * query=0x00c0, int seek=0) Line
>> 307 + 0
[sqlite] SQLite lock behavior in shared-cache mode
ersion 3.6.16 (amalgamation source) - Preprocessor definitions used to build the library: - SQLITE_THREADSAFE=1 - TEMP_STORE=3 - SQLITE_DEFAULT_CACHE_SIZE=65568 - SQLITE_DEFAULT_TEMP_CACHE_SIZE=65568 - SQLITE_MAX_ATTACHED=30 - SQLITE_ENABLE_COLUMN_METADATA - SQLITE_ENABLE_UNLOCK_NOTIFY Any observation or suggestion about this behavior is appreciated. Thanks for your time. Regards, Alessandro Merolli. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite 3.6.16 and the Asynchronous I/O transaction fails with multiple database files
I'm trying to use the asynchronous I/O extension with the latest SQLite version for the first time in my project. This project is using database files attached into one database connection. I start a transaction which involves two different database files but, the commit operation is failing with the SQLITE_CANTOPEN (14) error. The transaction with only one database file is working fine. As far as I could debug, it seems that it's failing to open/create the master journal file (sqlite3async.c:1069). Does anybody knows if the asynchronous I/O feature was supposed to work in this scenario (database connection with multiple database files attached)? Any suggestion/help is appreciated. Thanks. Alessandro. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Transaction commit performance
Hi all, I'm working on a project that makes massively usage of SQL statements with many inserts/selects. It's a multi-threaded process that shares a certain amount of SQLite "connections" to be used by other process through an IPC layer. The connections are created at startup and 5 other database files are attached to them before being shared by this process. Most of the time, the statements requested are grouped into a database transaction like this: BEGIN TRANSACTION; INSERT INTO TBTEST (X,Y,Z) SELECT X,Y,Z FROM TBDATA JOIN ... ; DELETE FROM TBTEST WHERE X NOT IN (SELECT ); UPDATE TBTEST SET Y=y WHERE X=x; COMMIT TRANSACTION; I'm already considering all the issues that this kind of design has: database concurrency, database locks, etc... but, this is not the thing. I'm experiencing a behavior that seams strange to me and I'd like to check if anybody can give me some tips to minimize this: The COMMIT statement consumes 50% or more of the time of the whole transaction; for example: if the whole transaction costs 2000 ms, only the COMMIT operation took 1500 ms. I understood all the operations described in the SQLite documentation about transaction commit/rollback feature, but this scenario seems to me that some adjustments can be made to minimize this. I'm using: - The server is an Intel 2GHz, 2GB of RAM and a 80GB hard-disk SATA II (w/ 8MB of cache) 7200 RPM; - Windows XP SP3 professional using NTFS; - SQLite 3.6.13 (amalgamation version); - I'm using shared-cache with the wait-notify feature implemented in the previous 3.6.12; - Other compiler options being used are: SQLITE_THREADSAFE=1; TEMP_STORE=3; SQLITE_DEFAULT_CACHE_SIZE=65568; SQLITE_DEFAULT_TEMP_CACHE_SIZE=65568; SQLITE_MAX_ATTACHED=30; SQLITE_ENABLE_COLUMN_METADATA; SQLITE_ENABLE_UNLOCK_NOTIFY. - The database size is around 200MB. What I'm looking for is: - If someone has a similar environment, what can be done to optimize Windows XP disk writes? - What PRAGMA statements or compiler options can help me with this, without the risk of getting the database corrupted? Any suggestion is helpful, thanks for the attention. Best regards, Alessandro Merolli. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Access violation in SQLITE3.DLL (version 3.6.6.2) when using views joining with other views ...
Hi all, I'm sorry about the inconvenience. I sent the e-mail below to [EMAIL PROTECTED] but I do not know if it's the correct e-mail to tell about possible bugs. So, I'm just forwarding my yesterday's e-mail in sqlite-users@sqlite.org . Please check the message below, any help is appreciated. Begin forwarded message: > From: Alessandro Merolli <[EMAIL PROTECTED]> > Date: December 4, 2008 5:33:10 PM GMT-02:00 > To: [EMAIL PROTECTED] > Subject: Access violation in SQLITE3.DLL when using views joining > with other views ... > > Hi all, > > I'm facing some problems with SQLite since version 3.6.x was > released. > Until now, I was working with version 3.5.4 without errors but, I > need to upgrade to version 3.6 because it seems more efficient in > terms of database locking, performance, pragma options, etc... > > Last week I upgraded to version 3.6.6.2 and I'm still getting an > access violation with the following setup: > > - I'm working in Microsoft Windows XP SP3 (although the error was > reproduced in Linux and MacOS environments) > > - The database structure is defined like this: > CREATE TABLE Element ( > Code INTEGER PRIMARY KEY, > Name VARCHAR(60) > ); > > CREATE TABLE ElemOr ( > CodeOr INTEGER NOT NULL, > Code INTEGER NOT NULL, > PRIMARY KEY(CodeOr,Code) > ); > > CREATE TABLE ElemAnd ( > CodeAnd INTEGER, > Code INTEGER, > Attr1 INTEGER, > Attr2 INTEGER, > Attr3 INTEGER, > PRIMARY KEY(CodeAnd,Code) > ); > > - The data used in the test is: > INSERT INTO Element VALUES(1,'Elem1'); > INSERT INTO Element VALUES(2,'Elem2'); > INSERT INTO Element VALUES(3,'Elem3'); > INSERT INTO Element VALUES(4,'Elem4'); > INSERT INTO Element VALUES(5,'Elem5'); > INSERT INTO ElemOr Values(3,4); > INSERT INTO ElemOr Values(3,5); > INSERT INTO ElemAnd VALUES(1,3,1,1,1); > INSERT INTO ElemAnd VALUES(1,2,1,1,1); > > - And the views which are causing the access violation are defined > like this: > CREATE VIEW ElemView1 AS > SELECT > CAST(Element.Code AS VARCHAR(50)) AS ElemId, > Element.Code AS ElemCode, > Element.Name AS ElemName, > ElemAnd.Code AS InnerCode, > ElemAnd.Attr1 AS Attr1, > ElemAnd.Attr2 AS Attr2, > ElemAnd.Attr3 AS Attr3, > 0 AS Level, > 0 AS IsOrElem > FROM Element JOIN ElemAnd ON ElemAnd.CodeAnd=Element.Code > WHERE ElemAnd.CodeAnd NOT IN (SELECT CodeOr FROM ElemOr) > UNION ALL > SELECT > CAST(ElemOr.CodeOr AS VARCHAR(50)) AS ElemId, > Element.Code AS ElemCode, > Element.Name AS ElemName, > ElemOr.Code AS InnerCode, > NULL AS Attr1, > NULL AS Attr2, > NULL AS Attr3, > 0 AS Level, > 1 AS IsOrElem > FROM ElemOr JOIN Element ON Element.Code=ElemOr.CodeOr > ORDER BY ElemId, InnerCode; > > CREATE VIEW ElemView2 AS > SELECT > ElemId, > ElemCode, > ElemName, > InnerCode, > Attr1, > Attr2, > Attr3, > Level, > IsOrElem > FROM ElemView1 > UNION ALL > SELECT > Element.ElemId || '.' || InnerElem.ElemId AS ElemId, > InnerElem.ElemCode, > InnerElem.ElemName, > InnerElem.InnerCode, > InnerElem.Attr1, > InnerElem.Attr2, > InnerElem.Attr3, > InnerElem.Level+1, > InnerElem.IsOrElem > FROM ElemView1 AS Element > JOIN ElemView1 AS InnerElem ON Element.Level=0 AND > Element.InnerCode=InnerElem.ElemCode > ORDER BY ElemId, InnerCode; > > - Note that ElemView2 uses the ElemView1 joining itself. When I > query for data in ElemView1 (SELECT * FROM ElemView1) it returns > with success; but, when I query ElemView2 (SELECT * FROM ElemView2) > it fails, and an access violation occurs. Here is the stack in > Visual Studio 2005 SP1: > msvcr80d.dll!memcpy(unsigned char * dst=0x00ad3120, unsigned > char * src=0x1b8c71e0, unsigned long count=36) Line 188 > sqlite3.dll!sqlite3VdbeMemShallowCopy(Mem * pTo=0x00ad3120, > const Mem * pFrom=0x1b8c71e0, int srcType=256) Line 41982 + 0xf bytes > sqlite3.dll!sqlite3VdbeExec(Vdbe * p=0x00ac4c40) Line 47446 + > 0x3e bytes > sqlite3.dll!sqlite3Step(Vdbe * p=0x00ac4c40) Line 45476 + 0x9 > bytes > sqlite3.dll!sqlite3_step(sqlite3_stmt * pStmt=0x00ac4c40) Line > 45542 + 0x9 bytes > [application stack - ommited] > Seems that the pointer pFrom from sqlite3VdbeMemShallowCopy is > invalid. > > - The compiler options use
