[sqlite] SIGBUS errors with WAL mode and multiple simultaneous updating clients
Hi, I seeing some SIGBUS faults during startup in the debug version of my app, but only when running under valgrind, and only for some clients. The faults appear to be occurring around the same location in the sqlite WAL code. If I disable WAL then there are no faults. If I don't run it under valgrind then there are no problems (so don't run it under valgrind?). I don't know if this problem is caused by my code or perhaps an issue in the sqlite WAL code caused by timing (since the startup is much slower under valgrind). The error occurs with a newly created database file containing a number of tables and data. It is small -> about 80kb. I've upgraded everything to latest versions and the problem persists, currently using valgrind 3.6.1 and sqlite 3.7.7.1 (amalgamation). CentOS 5.6. app built with gcc 4.4.4. I'm not using any sqlite compile time flags. The app is a parent process that does a fork/exec of 5 children (same results with different numbers of children). Each child runs some simple test code mostly simultaneously. I find that usually 1 child faults, sometimes more or less. The busy handler is usually called a number of times for the clients, sometimes delaying for up to a few seconds before retrying. The parent may have multiple database connections open at the time of the fork. They are closed in the child process before the exec. I can't close them before that as they are in active use in the parent. I've checked that there are no open file descriptors (other than those that valgrind has) at the time of process startup. If there is something that anyone can suggest to narrow down the problem then I would be interested to hear it. Regards, Brodie Some example stack traces: ==23787== Process terminating with default action of signal 7 (SIGBUS): dumping core ==23787== Non-existent physical address at address 0x5D94068 ==23787== at 0x8484CAF: walIndexRecover (sqlite3.c:44817) ==23787== by 0x8485DE2: walIndexReadHdr (sqlite3.c:45569) ==23787== by 0x8485EC0: walTryBeginRead (sqlite3.c:45682) ==23787== by 0x8486231: sqlite3WalBeginReadTransaction (sqlite3.c:45839) ==23787== by 0x84802A6: pagerBeginReadTransaction (sqlite3.c:39822) ==23787== by 0x84820A5: sqlite3PagerSharedLock (sqlite3.c:41678) ==23787== by 0x848A0EC: lockBtree (sqlite3.c:49813) ==23787== by 0x848A7AA: sqlite3BtreeBeginTrans (sqlite3.c:50105) ==23787== by 0x84C637C: sqlite3InitOne (sqlite3.c:89829) ==23787== by 0x84C678D: sqlite3Init (sqlite3.c:89998) ==23787== by 0x84C6876: sqlite3ReadSchema (sqlite3.c:90035) ==23787== by 0x84C3298: sqlite3Pragma (sqlite3.c:88616) ==23787== by 0x84DF79A: yy_reduce (sqlite3.c:106258) ==23787== by 0x84E00FF: sqlite3Parser (sqlite3.c:106641) ==23787== by 0x84E0D13: sqlite3RunParser (sqlite3.c:107465) ==23787== by 0x84C6BD5: sqlite3Prepare (sqlite3.c:90212) ==23787== by 0x84C6ED4: sqlite3LockAndPrepare (sqlite3.c:90304) ==23787== by 0x84C7043: sqlite3_prepare (sqlite3.c:90367) ==23787== by 0x84C1A35: sqlite3_exec (sqlite3.c:86911) ==23787== by 0x840F554: cl::DatabaseSqlite::Connect(cl::StringBuffer const&, int) (DatabaseSqlite.cpp:113) ==23787== by 0x83F1BCC: cl::Database::InitDatabaseSqlite(void*) (Database.cpp:631) ==23787== by 0x83F07DA: cl::Database::Init(boost::shared_ptr&) (Database.cpp:465) ==23787== by 0x83F03E7: cl::Database::Connect(boost::shared_ptr&, boost::shared_ptr&) (Database.cpp:382) ==23787== by 0x82CDE08: clsoapmain(int, char**) (clsoapMain.cpp:306) ==23787== by 0x82DA099: main (clsoapUnix.cpp:32) ==23771== Process terminating with default action of signal 7 (SIGBUS): dumping core ==23771== Non-existent physical address at address 0x5D9B686 ==23771== at 0x84863B0: sqlite3WalRead (sqlite3.c:45931) ==23771== by 0x847FF1D: readDbPage (sqlite3.c:39604) ==23771== by 0x84822AF: sqlite3PagerAcquire (sqlite3.c:41841) ==23771== by 0x848929B: btreeGetPage (sqlite3.c:49066) ==23771== by 0x848938E: getAndInitPage (sqlite3.c:49119) ==23771== by 0x848C586: moveToChild (sqlite3.c:51633) ==23771== by 0x848C8EA: moveToLeftmost (sqlite3.c:51798) ==23771== by 0x848D2E3: sqlite3BtreeNext (sqlite3.c:52180) ==23771== by 0x848D2A7: sqlite3BtreeNext (sqlite3.c:52170) ==23771== by 0x84A2469: sqlite3VdbeExec (sqlite3.c:67148) ==23771== by 0x849ABBF: sqlite3Step (sqlite3.c:61204) ==23771== by 0x849ADAB: sqlite3_step (sqlite3.c:61277) ==23771== by 0x8410AEB: cl::RequestSqlite::Step() (DatabaseSqlite.cpp:566) ==23771== by 0x83F5777: cl::Database::LoadStrings(char, std::vector >&) (Database.cpp:1623) ==23771== by 0x83F0C91: cl::Database::TestDatabaseStrings() (Database.cpp:532) ==23771== by 0x83F0A96: cl::Database::Init(boost::shared_ptr&) (Database.cpp:508) ==23771== by 0x83F03E7: cl::Database::Connect(boost::shared_ptr&, boost::shared_ptr&) (Database.cpp:382) ==23771== by 0x82CDE08: clsoapmain(int, char**) (clsoapMain.cpp:306) ==23771== by 0x82DA099: main (clsoapUnix.cp
Re: [sqlite] .lib file?
>> -Original Message- >> From: sqlite-users-boun...@sqlite.org >> [mailto:sqlite-users-boun...@sqlite.org]on Behalf Of Doug >> Sent: Saturday, July 25, 2009 11:24 AM >> To: 'General Discussion of SQLite Database' >> Subject: Re: [sqlite] .lib file? >> >> >> Hi Paul -- >> >> When I build SQLite (from the amalgamation) into a DLL VS2008 (and earlier >> as I recall) automatically creates an import library in the same directory >> as the .DLL file. I also _used_ to run the following command separately to >> create the import lib: >> >> link /lib /def:sqlite3.def >> >> I haven't kept my sqlite3.def file up to date, but it's fairly recent if you >> want to use it. Since we can't post files to the newsgroup, I'll append it >> here. >> >> Doug Easy to generate the .def file too. http://www.google.com/search?q=create+.lib+from+.dll http://support.microsoft.com/kb/131313 Regards, Brodie ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] journal_mode = off crash in 3.6.10
Hi, I know that there has been a number of crashes involving journal_mode = off. This problem continues for me in sqlite 3.6.10 almalgamation though. A trigger seems to be the cause. The following test program crashes on the last line (sqlite3_step) with a NULL pointer dereference of id->pMethods when compiled on Windows with VC2003. sqlite.c line 12576 SQLITE_PRIVATE int sqlite3OsWrite(sqlite3_file *id, const void *pBuf, int amt, i64 offset){ DO_OS_MALLOC_TEST; return id->pMethods->xWrite(id, pBuf, amt, offset); } If the prop table and associated triggers are removed, the crash doesn't occur. Regards, Brodie The stack trace is: > testsqlite.exe!sqlite3OsWrite(sqlite3_file * id=0x00510b58, const void > * pBuf=0x0012e3e4, int amt=4, __int64 offset=0) Line 12576 + 0x1b C testsqlite.exe!write32bits(sqlite3_file * fd=0x00510b58, __int64 offset=0, unsigned int val=4) Line 31128 + 0x17 C testsqlite.exe!subjournalPage(PgHdr * pPg=0x00388a64) Line 33450 + 0x1bC testsqlite.exe!pager_write(PgHdr * pPg=0x00388a64) Line 34311 + 0x9 C testsqlite.exe!sqlite3PagerWrite(PgHdr * pDbPage=0x00388a64) Line 34420 + 0x9 C testsqlite.exe!insertCell(MemPage * pPage=0x00388e88, int i=0, unsigned char * pCell=0x00388f20, int sz=9, unsigned char * pTemp=0x, unsigned char nSkip=0) Line 40971 + 0xc C testsqlite.exe!sqlite3BtreeInsert(BtCursor * pCur=0x00385760, const void * pKey=0x0051d698, __int64 nKey=8, const void * pData=0x004f6c22, int nData=0, int nZero=0, int appendBias=0) Line 42175 + 0x19 C testsqlite.exe!sqlite3VdbeExec(Vdbe * p=0x00386808) Line 52819 + 0x2c C testsqlite.exe!sqlite3Step(Vdbe * p=0x00386808) Line 47849 + 0x9 C testsqlite.exe!sqlite3_step(sqlite3_stmt * pStmt=0x00386808) Line 47916 + 0x9 C testsqlite.exe!main(int argc=1, unsigned short * * argv=0x00381d88) Line 48 + 0x9 C++ testsqlite.exe!mainCRTStartup() Line 259 + 0x19C kernel32.dll!7c817067() ntdll.dll!7c915d27() The test program is: #include #include #include #include #include "sqlite3.h" int main(int argc, TCHAR * argv[]) { sqlite3 * pDatabase = NULL; char * pszError = NULL; sqlite3_stmt * pStatement = NULL; unlink("test.sqlite"); unlink("test.sqlite-journal"); assert(SQLITE_OK == sqlite3_open_v2("test.sqlite", &pDatabase, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL)); assert(SQLITE_OK == sqlite3_exec(pDatabase, "CREATE TABLE dic ( entryid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, headword TEXT NOT NULL, source TEXT NOT NULL ); " "CREATE INDEX dic_headword_idx ON dic ( headword ); " "CREATE TABLE prop ( pkey TEXT PRIMARY KEY NOT NULL, pval TEXT NOT NULL ); " "INSERT INTO prop(pkey, pval) VALUES ('total_count', 0); " "CREATE TRIGGER count_insert_trigger AFTER INSERT ON [dic] FOR EACH ROW BEGIN" "UPDATE prop SET pval = pval + 1 WHERE pkey = 'total_count'; " "END; " "CREATE TRIGGER count_delete_trigger AFTER DELETE ON [dic] FOR EACH ROW BEGIN" "UPDATE prop SET pval = pval - 1 WHERE pkey = 'total_count'; " "END; ", NULL, NULL, &pszError)); assert(SQLITE_OK == sqlite3_close(pDatabase)); assert(SQLITE_OK == sqlite3_open_v2("test.sqlite", &pDatabase, SQLITE_OPEN_READWRITE, NULL)); assert(SQLITE_OK == sqlite3_exec(pDatabase, "PRAGMA journal_mode = OFF; " "BEGIN IMMEDIATE TRANSACTION; ", NULL, NULL, &pszError)); assert(SQLITE_OK == sqlite3_prepare_v2(pDatabase, "INSERT INTO dic (entryid, headword, source) VALUES (?, ?, ?);", -1, &pStatement, NULL)); assert(SQLITE_OK == sqlite3_bind_null(pStatement, 1)); assert(SQLITE_OK == sqlite3_bind_text16(pStatement, 2, "foo", -1, NULL)); assert(SQLITE_OK == sqlite3_bind_text16(pStatement, 3, "bar", -1, NULL)); assert(SQLITE_DONE == sqlite3_step(pStatement)); // crash here return 0; } ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Extra functions for portability
Hi, My database layer needs to support PostgreSQL, MS SQL Server and SQLite and as much as possible I try to use the same SQL statements without modification. I found that for some of my uses, I needed extra functions or aliases to builtin sqlite functions and so I wrote them. Since others may find it useful, I have released it for others to use at http://code.jellycan.com/files/sqlite3_extra.c It piggybacks onto the sqlite3.c amalgamation file so that I can avoid recreating code that already exists in sqlite - if you are not using the amalgamation then you may need to modify it to suit. Hope it is useful to someone. Regards, Brodie ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_open_v2 and SQLITE_OPEN_READONLY
On Thu, 20 Sep 2007 10:14:10 -0700 drh wrote: > Sqlite3_open_v2() and SQLITE_OPEN_READONLY and a whole bunch > of other stuff is all new to 3.5.0. Version 3.5.0 is stable. > It has lots of cool stuff. Older versions are not supported > (except for paying customers) - by which we mean that if any > bugs are discovered they will be fixed in 3.5.0 only, not > in branches. You (and a lot of other people) really need > to upgrade. Stable? Since when was 3.5.0 even released? There has been no release announcement in this list (that I can find in the archives in any case), and the front page of http://sqlite.org announces that it is still in alpha. Surely until there is a formal release of 3.5.0, the existing version of 3.4.2 is still the current stable version? Regards, Brodie Sick of deleting your inbox? Yahoo!7 Mail has free unlimited storage. http://au.docs.yahoo.com/mail/unlimitedstorage.html - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Top 10 distinct record count
Perhaps this is what you are after? select file, count(file) from logtable where type = 'specific' group by file order by 2 desc limit 10; Hikka W wrote: Dear list Quite new to sqlite/sql - and just signed the list. Have a logtable where I need a TOP 10 output of the most representet field 'file' WHERE the field 'type' is specific. 'file' is the text of a filepath or name, and 'type' is also text... I relize by searching the list that LIMIT can be the way to go, instead of TOP, but I'm really stuck. Think I can do it with (a bunch of) nested calls, but is it possible within one call? And will there be a difference from using sqlite ver. 2.x to 3.x? Any help is appriciated Regards, Hikka - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite_sequence lacks a PK definition
Hi, At the moment the sqlite_sequence table: * doesn't exist when a database is first created * is created when an AUTOINCREMENT table is created * doesn't use a primary key on the name column * doesn't get populated with the sequence row for a table until a row is added to the table. Would adding a primary key definition to the sqlite_sequence table cause a performance problem? If there are many tables using autoincrement then surely it would help. Additionally, if it had a PK defined then it would be possible to update this table using INSERT OR REPLACE... Failing that, or in addition to that, populating the table with the sequence row for the table at the same time as the autoincrement table is created would be nice. Regards, Brodie - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] reusing prepared queries or dynamic generation of SQL?
Hi, I'm looking for some SQL advice. I have a query which is used as the base of a filter. At the moment I am using: SELECT * FROM table WHERE (1=? OR foo=?); The actual query is more complex and uses multiple of the constructions in this WHERE clause. If I wanted to select on the foo column then I would bind (0, 'desired-foo'). If I didn't then I would bind (1, ''). There is an index on the foo column, but this construction precludes the use of it even when I have a specific foo that I want. The idea behind this was to prepare a single select query, and then just reuse it all of the time. I am thinking now that this is false economy and it would be better (faster) to dynamically assemble the SQL, prepare, step, finalize each time instead? At the very least it seems like it should enable indexes to be used where available. Regards, Brodie - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Hexadecimal literals or MAX_INTEGER definition?
Does sqlite support numeric literals in hexadecimal? e.g.INSERT INTO table(mask) VALUES (0x); Additionally, is there a constant like MAX_INTEGER defined which I can use as the maximum value that an INTEGER field supports (assuming that types actually exist)? Regards, Brodie - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Correct use of sqlite3 API (release locks)
Hi, I have multiple processes using a single database for both read and write. I want to ensure that my interpretation of the v3 API spec is correct. In particular, I want to ensure that all processes lock the database for the minimum time possible and release the lock as soon as they have finished processing the SQL statement. Are locks released automatically on error/done, or manually by the sqlite3_reset() call, or some other function? I currently use the following logic in my internal DB layer. if sql not prepared sqlite3_prepare_v2() sqlite3_bind_* ... rc = sqlite3_step() while rc == SQLITE_ROW process row (sqlite3_column_*) ... rc = sqlite3_step() if rc != SQLITE_DONE process error sqlite3_reset() Will this release the locks on the database (assuming no transactions?) by the end of the function? Regards, Brodie - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Possible Memory Leak
The API documentation doesn't mention anything about this. It would be good to have it added there. http://www.sqlite.org/capi3ref.html#sqlite3_errmsg However drh stated a while ago that sqlite3_errmsg strings do NOT need to be freed. Error strings returned from sqlite3_exec do. http://www.mail-archive.com/sqlite-users@sqlite.org/msg16634.html Regards, Brodie John Stanton wrote: Your program does not free the memory malloc'd by sqlite3_errmsg by calling sqlite3_free. I guess that Valgrind is telling you that. You don't have to worry about Sqlite. Jose Miguel Goncalves wrote: Hi, Running a simple sqlite3_open()/sqlite3_close() program (attached) I get a report of a possible memory leak in valgrind: $ valgrind --leak-check=full ./test_sqlite ==11992== Memcheck, a memory error detector. ==11992== Copyright (C) 2002-2006, and GNU GPL'd, by Julian Seward et al. ==11992== Using LibVEX rev 1658, a library for dynamic binary translation. ==11992== Copyright (C) 2004-2006, and GNU GPL'd, by OpenWorks LLP. ==11992== Using valgrind-3.2.1-Debian, a dynamic binary instrumentation framework. ==11992== Copyright (C) 2000-2006, and GNU GPL'd, by Julian Seward et al. ==11992== For more details, rerun with: -v ==11992== OPEN OK CLOSE OK ==11992== ==11992== ERROR SUMMARY: 0 errors from 0 contexts (suppressed: 8 from 1) ==11992== malloc/free: in use at exit: 272 bytes in 2 blocks. ==11992== malloc/free: 114 allocs, 112 frees, 14,382 bytes allocated. ==11992== For counts of detected errors, rerun with: -v ==11992== searching for pointers to 2 not-freed blocks. ==11992== checked 16,889,208 bytes. ==11992== ==11992== 272 bytes in 2 blocks are possibly lost in loss record 1 of 1 ==11992==at 0x4A1AB81: calloc (vg_replace_malloc.c:279) ==11992==by 0x400EA3D: _dl_allocate_tls (in /lib/ld-2.3.6.so) ==11992==by 0x4EC3500: pthread_create@@GLIBC_2.2.5 (in /lib/libpthread-2.3.6.so) ==11992==by 0x4B4A427: (within /usr/lib/libsqlite3.so.0.8.6) ==11992==by 0x4B4A5F0: (within /usr/lib/libsqlite3.so.0.8.6) ==11992==by 0x4B4E133: sqlite3pager_open (in /usr/lib/libsqlite3.so.0.8.6) ==11992==by 0x4B385CC: sqlite3BtreeOpen (in /usr/lib/libsqlite3.so.0.8.6) ==11992==by 0x4B48CC6: sqlite3BtreeFactory (in /usr/lib/libsqlite3.so.0.8.6) ==11992==by 0x4B495C5: (within /usr/lib/libsqlite3.so.0.8.6) ==11992==by 0x4006F4: main (in /home/jmpg/mtm2006/tmp/test_sqlite) ==11992== ==11992== LEAK SUMMARY: ==11992==definitely lost: 0 bytes in 0 blocks. ==11992== possibly lost: 272 bytes in 2 blocks. ==11992==still reachable: 0 bytes in 0 blocks. ==11992== suppressed: 0 bytes in 0 blocks. ==11992== Reachable blocks (those to which a pointer was found) are not shown. ==11992== To see them, rerun with: --show-reachable=yes Is this a "real" memory leak or may I ignore this? Anyone has noticed this? I'm running Debian etch, with sqlite v3.3.8. José Gonçalves #include #include #include int main(void) { sqlite3*db = NULL; int ret; printf("OPEN "); ret = sqlite3_open("test.db", &db); if (ret != SQLITE_OK) { printf("error[%d]: %s\n", ret, sqlite3_errmsg(db)); return (EXIT_FAILURE); } printf("OK\n"); printf("CLOSE "); ret = sqlite3_close(db); if (ret != SQLITE_OK) { printf("error[%d]: %s\n", ret, sqlite3_errmsg(db)); return (EXIT_FAILURE); } printf("OK\n"); return (EXIT_SUCCESS); } - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Need a wince test
The utf8ToUnicode function uses MultiByteToWideChar(CP_UTF8) which it seems from recent comments isn't supported on all versions of Windows CE. This may need to be changed to use the internal UTF-8 to UTF-16 conversion routines. To be safe, the unicodeToMbcs needs to determine which codepage to convert to in the WideCharToMultiByte calls. This is done by calling the AreFileApisANSI() http://snipurl.com/arefileapisansi like: UINT codepage = AreFileApisANSI() ? CP_ACP : CP_OEM; Note also that many comments are wrong throughout the file. All WCHAR in Windows is UTF-16 not UTF-32 which is stated everywhere. You need casts for the void* to either char* or WCHAR* in the LoadLibraryA/W calls. Regards, Brodie [EMAIL PROTECTED] wrote: > win95/nt/ce users, please test check-in [3541] to see if extension > loading now works on wince and to make sure that nothing broke on > win95/nt. Tnx. > > http://www.sqlite.org/cvstrac/chngview?cn=3541 > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Need a wince test
Robert Simpson wrote: CP_UTF8 doesn't work on most CE platforms and hence your proposed patch doesn't work. Then neither did drh's. Which then only leaves the option of implementing it in os_win.c which I have been wanting to do all along. > Robert wrote: >> Brodie wrote: >>> Robert wrote: Here's what I propose ... > #ifdef _UNICODE > #define OSSTR wchar_t > #else > #define OSSTR char > > OSSTR *utf8toOS(char *utf8) > void utf8toOSFree(OSSTR *apiString) Although it is nice since it is simple, it is worse than the current solution. Firstly, with the current method, only Windows 95 is broken. Additionally, the current solution has the nice property of using the unicode functions whenever available (i.e. when running on WinNT) regardless of build type. Your proposal takes a step backwards to providing Unicode support only when compiled as a Unicode application. It is better to continue the current method of calling W functions where possible and falling back to A only when necessary, just process the string sent into the A function so that it is properly ACP/OEM as necessary. I'm not sure what you're getting all worked up over. How is "providing unicode support only when compiled as a unicode application" a Bad Thing? Because as explained, the current sqlite DLL provides Unicode support to Windows NT clients regardless of whether built as ansi or unicode. Therefore a single DLL supports all clients on either Win95 or WinNT with best available functionality. The Windows way of either/or is not as flexible. You compile with as a unicode DLL and it isn't usable on Win95 (unless you also use MSLU). You compile as multibyte and you lose full unicode functionality on winNT. It's lose/lose compared to the current method. New patch to fix this. Attached to the original bug and included. http://www.sqlite.org/cvstrac/tktview?tn=2023 --- ..\sqlite-source-3_3_8.orig\os.h2006-10-08 13:51:00.0 -0300 +++ os.h2006-12-20 21:30:39.612512000 -0300 @@ -133,4 +133,5 @@ #define sqlite3OsFree sqlite3GenericFree #define sqlite3OsAllocationSize sqlite3GenericAllocationSize +HANDLE sqlite3WinLoadLibrary(const char*); #endif #if OS_OS2 --- ..\sqlite-source-3_3_8.orig\os_win.c2006-10-08 13:51:00.0 -0300 +++ os_win.c2006-12-20 21:32:25.46472 -0300 @@ -1556,3 +1556,23 @@ return pTsd; } + +/* +** Return TRUE if the named file exists. +*/ +HANDLE sqlite3WinLoadLibrary(const char *zFilename){ + HANDLE h = NULL; + WCHAR *zWide = utf8ToUnicode(zFilename); + if( zWide ){ +h = LoadLibraryW(zWide); +sqliteFree(zWide); + }else{ +#if OS_WINCE +return NULL; +#else +h = LoadLibraryA(zFilename); +#endif + } + return h; +} + #endif /* OS_WIN */ --- ..\sqlite-source-3_3_8.orig\loadext.c 2006-10-08 13:51:00.0 -0300 +++ loadext.c 2006-12-20 21:28:19.420926400 -0300 @@ -224,5 +224,5 @@ # include # define SQLITE_LIBRARY_TYPE HANDLE -# define SQLITE_OPEN_LIBRARY(A) LoadLibrary(A) +# define SQLITE_OPEN_LIBRARY(A) sqlite3WinLoadLibrary(A) # define SQLITE_FIND_SYMBOL(A,B) GetProcAddress(A,B) # define SQLITE_CLOSE_LIBRARY(A) FreeLibrary(A) - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Need a wince test
Robert Simpson wrote: From: Brodie Thiesfield [mailto:[EMAIL PROTECTED] Robert, you are missing the point. Because of the way this is being defined, there is a need to check for _UNICODE. If you don't then a build with _UNICODE defined will fail. If it was implemented like the rest of the functions in os_win.c then it wouldn't be necessary. I can go either way on that. There is no need to check for _UNICODE if you change the defines ... to this: > > # ifdef _WIN32_WCE > //snip > # else > # define SQLITE_OPEN_LIBRARY(A) LoadLibraryA(A) // <-- changed to A > # define SQLITE_FIND_SYMBOL(A,B) GetProcAddress(A,B) > # endif That way you are ensuring that non-ASCII strings won't work on any platform other than WINCE. It's the worse solution so far. Why do you have such a problem accepting the _UNICODE define? I feel like I am arguing with a brick wall. The following patch is a version of drh's that will compile and work on in the cases that you and I want (e.g. Unicode build). See my original reply to drh as to why it is better than his (although see below for why it is still bad). # ifdef _UNICODE // hey, look, we catch _WIN_WCE here too! static HANDLE loadLibraryUtf8(const char *z){ WCHAR zWide[MAX_PATH]; DWORD dwLen = MultiByteToWideChar(CP_UTF8,0,z,-1,zWide,MAX_PATH); if (dwLen == 0 || dwLen > MAX_PATH) return NULL; return LoadLibraryW(zWide); } # define SQLITE_OPEN_LIBRARY(A) loadLibraryUtf8(A) # else # define SQLITE_OPEN_LIBRARY(A) LoadLibraryA(A) # endif # define SQLITE_FIND_SYMBOL(A,B) GetProcAddress(A,B) If _UNICODE is defined it works fine. When _UNICODE is not defined (the default) it has UTF-8/ANSI coding problems on *all* platforms (unlike os_win functions which have them only on Win9x). This is why at a minimum it needs to be included in os_win.c and implemented like all other functions there. As per my original patch on the bug report. Can we just implement it there, export it from there and use it in loadext.c without touching the os.h header that drh seems so allergic to? This is so simple. I'll draw a diagram. If you want... -> full support on WinNT/WinCE when built in _UNICODE, broken when using non-ASCII chars at all other times? == Implement as above. -> full support on WinNT/WinCE regardless of build type, broken when using non-ASCII chars on Win9x? == Implement like all other functions in os_win, export from there, use in load_ext.c -> full support on all platforms regardless of build type == Implement like all other functions in os_win, export from there, use in load_ext.c + fix all functions in os_win to work on Win9x Here's what I propose ... > #ifdef _UNICODE > #define OSSTR wchar_t > #else > #define OSSTR char > > OSSTR *utf8toOS(char *utf8) > void utf8toOSFree(OSSTR *apiString) Although it is nice since it is simple, it is worse than the current solution. Firstly, with the current method, only Windows 95 is broken. Additionally, the current solution has the nice property of using the unicode functions whenever available (i.e. when running on WinNT) regardless of build type. Your proposal takes a step backwards to providing Unicode support only when compiled as a Unicode application. It is better to continue the current method of calling W functions where possible and falling back to A only when necessary, just process the string sent into the A function so that it is properly ACP/OEM as necessary. Brodie - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Need a wince test
[EMAIL PROTECTED] wrote: > Brodie Thiesfield <[EMAIL PROTECTED]> wrote: [...] > SQLite should never expect strings in any encoding other > than UTF-8 or UTF-16. (Note that ASCII is a proper subset > of UTF-8 so SQLite will also accept ASCII.) I do not know > what CP_ACP is, but if it is not a subset of UTF-8 then > SQLite should (if implemented as designed) malfunction if > you give it a CP_ACP string that has a different representation > than the equivalent UTF-8. > > If you find a case where SQLite is expecting some character > encoding other than UTF-8 or UTF-16, then you have found a bug. > Please create a ticket and suggest an appropriate patch. CP_ACP is ANSI code page. The character encoding usually used by the file API on Windows. It will be different depending on the Windows legacy locale. e.g. Shift-JIS for Japanese. EUC-KR for Korean. I have filed the following ticket for this bug: http://www.sqlite.org/cvstrac/tktview?tn=2121 > My intent is to let yall continue to debate a proper fix > for porting the extension loading mechanism to wince and > then once you reach consensus I will check in whatever that > consensus happens to be. The problem is not just Windows CE. The current problem of the Unicode build (and therefore *also* a Windows CE build) failing can be temporarily fixed with the patch that I supplied in the first reply to your email. The larger problem of Win9x clients not seeing a ANSI char* API instead of the specified UTF-8 char* API is a problem that needs to be fixed with larger changes to the win_os.c layer. The LoadLibrary call should also be implemented there. What is the problem with implementing the LoadLibrary call in the os_win.c layer? Regards, Brodie - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Need a wince test
Robert, does the patch I provided work as is on Windows CE or not? Robert Simpson wrote: There's some flaws in your arguments, Brodie ... 1. There's no need to do this whole _UNICODE test, only the _WINCE test is needed. All versions of Windows that support unicode also support the ANSI versions of the API calls -- the only Windows platform that doesn't have any ANSI support is Windows CE. A single one-character modification to DRH's proposed patch is all that's needed for regular Windows desktop support. Robert, you are missing the point. Because of the way this is being defined, there is a need to check for _UNICODE. If you don't then a build with _UNICODE defined will fail. If it was implemented like the rest of the functions in os_win.c then it wouldn't be necessary. 2. You're ignoring that the ANSI versions that already exist in loadext.c are potentially passing a utf8 char * to LoadLibraryA() right now and have been since loadext.c was created. If you really want to be technical about it, all the strings passed to LoadLibrary() and GetProcAddress() need to be converted from utf8 to MBCS for non-CE platforms. As far as I understand, sqlite changed sometime in the early 3.0 versions such that char* strings (input and output) are always assumed to be UTF-8. See: http://www.sqlite.org/cvstrac/tktview?tn=1695 http://www.sqlite.org/cvstrac/tktview?tn=1533 If these strings are passed directly into win32 ansi functions then that is a different bug and one that needs to be fixed. It is not part of this bug. This patch would also need to be fixed too. There are two possible solutions, and these are the same issues we've had in os_win ... 1. Bite the bullet and realize that Windows API calls are natively MBCS, not utf8, and convert any char * from utf8 to mbcs before passing them to a Windows API call. Given the current API documentation and what has been written in the bug referenced earlier, this seems the only possibility. DRH, your proposed patch almost works with _UNICODE defined. Just change it to this instead (my changes are marked with -->): No, it doesn't. It needs to have the define check changed to _UNICODE instead of _WIN32_WCE (which will still support CE). It also needs error checking of the conversion. It needs to call the correct functions. Ideally it should be implemented like the rest of the functions in os_win.c Unfortunately the larger issue still remains, that all ANSI api calls in Windows expect MBCS strings and not utf8 strings. Technically what you should do is have two functions: loadLibraryUtf16() for WINCE that converts the utf8 string to utf16/unicode and calls LoadLibraryW() loadLibraryUtf8() for the rest of Windows, which converts a utf8 string to mbcs and calls LoadLibraryA() Yes. But this is a separate problem. Look at the code in os_win.c, e.g. sqlite3WinFileExists. On Windows NT platforms, it will work fine only if the caller supplies a ASCII or UTF-8 string (CP_ACP encoded strings will fail). On Windows 9x platforms it will work fine only if the caller supplies an ASCII or CP_ACP string (UTF-8 encoded strings will fail). Probably most users on Win9x are still passing in ANSI strings which is why it is working. Change it to actually match the documentation and you will probably see a few more failures. Brodie - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Need a wince test
There are a few problems with your patch. +# ifdef _WIN32_WCE +static HANDLE loadLibraryUtf8(const char *z){ + WCHAR zWide[MAX_PATH]; + MultiByteToWideChar(CP_ACP,0,z,-1,zWide,MAX_PATH); + return LoadLibrary(zWide); +} +# define SQLITE_OPEN_LIBRARY(A) loadLibraryUtf8(A) +# define SQLITE_FIND_SYMBOL(A,B) GetProcAddressA(A,B) +# else +# define SQLITE_OPEN_LIBRARY(A) LoadLibrary(A) +# define SQLITE_FIND_SYMBOL(A,B) GetProcAddress(A,B) +# endif The problem in question is not a Windows CE only one. It occurs with any client that builds in Unicode mode. Therefore you need to test for the _UNICODE define instead of _WINCE. Windows CE compilers will also set _UNICODE (I believe - Robert?). CP_ACP is not UTF-8. Use either CP_UTF8 or your own UTF-8 conversion functions from the OS library. Note also that MultiByteToWideChar may fail or return ERROR_NO_UNICODE_TRANSLATION (1113L) for UTF-8 conversions. There is no GetProcAddressA. You need to use GetProcAddress. The patch will need to be something like the following. Which I have tested and builds with no errors or warnings in _UNICODE mode. Still need someone to test it in WINCE to be sure. # include # define SQLITE_LIBRARY_TYPE HANDLE -# define SQLITE_OPEN_LIBRARY(A) LoadLibrary(A) +# ifdef _UNICODE +static HANDLE loadLibraryUtf8(const char *z){ + WCHAR zWide[MAX_PATH]; + DWORD dwLen = MultiByteToWideChar(CP_UTF8,0,z,-1,zWide,MAX_PATH); + if (dwLen == 0 || dwLen > MAX_PATH) return NULL; + return LoadLibraryW(zWide); +} +# define SQLITE_OPEN_LIBRARY(A) loadLibraryUtf8(A) +# else +# define SQLITE_OPEN_LIBRARY(A) LoadLibrary(A) +# endif # define SQLITE_FIND_SYMBOL(A,B) GetProcAddress(A,B) # define SQLITE_CLOSE_LIBRARY(A) FreeLibrary(A) Regards, Brodie [EMAIL PROTECTED] wrote: > Can somebody with access to wince please test patch [3537] > for me and let me know if it works to fix ticket #2023. > > http://www.sqlite.org/cvstrac/chngview?cn=3537 > http://www.sqlite.org/cvstrac/tktview?tn=2023 > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] building sqlite on windows in Unicode
Robert Simpson wrote: I wrote the original patch to loadext.c with the intent of making it as minimally obtrusive as possible to the existing code structure. I know firsthand how much DRH hates changing his codebase :) [snip] > FWIW, that patch I wrote is currently in production code in the ADO.NET 2.0 > provider and in use by Windows CE users since October. I don't doubt that your patch fixes your specific problem, however it has 2 main problems for me: 1) it doesn't address the larger problem of building the library in UNICODE mode in general (of which Windows CE is just one client) 2) you've assumed that the char* strings are in CP_ACP whereas I understand that all char* are UTF-8. That said I can easily write a patch that doesn't touch the OS abstraction code, it just seems crazy to hack missing OS abstraction functions into files all around the codebase just in order to avoid adding them to the existing OS abstraction layer. Regards, Brodie -Original Message----- From: Brodie Thiesfield [mailto:[EMAIL PROTECTED] Sent: Sunday, December 17, 2006 10:10 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] building sqlite on windows in Unicode [EMAIL PROTECTED] wrote: Brodie Thiesfield <[EMAIL PROTECTED]> wrote: Done. Is there anything else that is necessary to contribute code and patches to sqlite? For ticket #2023, the first patch seems unlikely to work right since it changes the character encoding for LoadLibrary() but leaves it unchanged for FreeLibrary(). That seems wrong to me, but not having any access to WinCE (and having zero desire to ever get access) I have no way of testing it. I didn't write the first patch. It should just be ignored as my patch is more comprehensive. The only function that has a different version for UNICODE vs MBCS is LoadLibrary (i.e. both LoadLibraryA and LoadLibraryW). This can be seen from the documentation. The T in LPCTSTR in the LoadLibrary definition signifies that there is both A and W versions. GetProcAddress takes only a LPCSTR which is always char*. FreeLibrary takes only the handle to the library that LoadLibrary created. These functions are pretty much the same as dlopen/dlsym/dlclose, with the exception that LoadLibrary needs to be specially handled. MSDN documentation: LoadLibrary http://snipurl.com/loadlibrary GetProcAddress http://snipurl.com/getprocaddress FreeLibrary http://snipurl.com/freelibrary The second patch extends the OS interface in ways that will break legacy implementations. A significant part of my income derives from people and companies who pay me to not do that. If those implementations are not be broken by the current implementation of loadext.c then surely these changes won't break them either. The whole idea of have the OS interface is that it abstracts the OS away in a single location. Hacking OS abstraction into other parts of the codebase (e.g. the current loadext.c) is not the correct thing to do. In any case, since all of those people/companies are either supported by the current method of dlopen/sym/close as is currently used in loadext.c, or they aren't using 3.3.7+ I could perhaps fix up either patch to do the right thing, but then I would have no way of testing the results, since I do not have access to WinCE. You do not need access to WinCE. It also breaks the build on Windows when defining _UNICODE. I'm sure that you an set the _UNICODE define (removing _MBCS) on your cross-compiler (if that is what you are using). If you can you elaborate more on the requirements for changes to the OS layer then I can adapt my patch to fit your requirements. The above are some of the reasons that there has been so little movement on ticket #2023. Your contributions are greatly appreciated. Please do not let anything I say or do discourage you from contributing again to either SQLite or other open source projects. User contributions are very important to open source projects like SQLite and I want to encourage them. But you also need to understand that there is no guarantee that a patch will be accepted. With SQLite in particular, with me in the drivers seat, it is very very difficult to get a patch accepted into the core. It has been done, but it does not happen very often. Usually, if I implement a suggested feature at all, I merely use the patch as a guideline to implement my own changes. Do not let this discourage you. Your patch has been recorded in the bug tracking system, and I have studied it closely. It will be likely used as a reference someday. Just not today. Having these comments added to the bug system would be useful to start with. You have your reasons for ignoring the bug, but with no movement at all it is frustrating to have to continually patch the source just to get it to build on Windows. Especially when it is so simple to get it to work. Regards, Brodie
Re: [sqlite] building sqlite on windows in Unicode
[EMAIL PROTECTED] wrote: > Brodie Thiesfield <[EMAIL PROTECTED]> wrote: >> Done. Is there anything else that is necessary to contribute code and >> patches to sqlite? > > For ticket #2023, the first patch seems unlikely to work right > since it changes the character encoding for LoadLibrary() but > leaves it unchanged for FreeLibrary(). That seems wrong to me, > but not having any access to WinCE (and having zero desire to > ever get access) I have no way of testing it. I didn't write the first patch. It should just be ignored as my patch is more comprehensive. The only function that has a different version for UNICODE vs MBCS is LoadLibrary (i.e. both LoadLibraryA and LoadLibraryW). This can be seen from the documentation. The T in LPCTSTR in the LoadLibrary definition signifies that there is both A and W versions. GetProcAddress takes only a LPCSTR which is always char*. FreeLibrary takes only the handle to the library that LoadLibrary created. These functions are pretty much the same as dlopen/dlsym/dlclose, with the exception that LoadLibrary needs to be specially handled. MSDN documentation: LoadLibrary http://snipurl.com/loadlibrary GetProcAddress http://snipurl.com/getprocaddress FreeLibrary http://snipurl.com/freelibrary > The second patch extends the OS interface in ways that will break > legacy implementations. A significant part of my income derives > from people and companies who pay me to not do that. If those implementations are not be broken by the current implementation of loadext.c then surely these changes won't break them either. The whole idea of have the OS interface is that it abstracts the OS away in a single location. Hacking OS abstraction into other parts of the codebase (e.g. the current loadext.c) is not the correct thing to do. In any case, since all of those people/companies are either supported by the current method of dlopen/sym/close as is currently used in loadext.c, or they aren't using 3.3.7+ > I could perhaps fix up either patch to do the right thing, but > then I would have no way of testing the results, since I do not > have access to WinCE. You do not need access to WinCE. It also breaks the build on Windows when defining _UNICODE. I'm sure that you an set the _UNICODE define (removing _MBCS) on your cross-compiler (if that is what you are using). If you can you elaborate more on the requirements for changes to the OS layer then I can adapt my patch to fit your requirements. > The above are some of the reasons that there has been so little > movement on ticket #2023. > > Your contributions are greatly appreciated. Please do not let > anything I say or do discourage you from contributing again to > either SQLite or other open source projects. User contributions > are very important to open source projects like SQLite and I want > to encourage them. But you also need to understand that there is > no guarantee that a patch will be accepted. With SQLite in > particular, with me in the drivers seat, it is very very difficult > to get a patch accepted into the core. It has been done, but it > does not happen very often. Usually, if I implement a suggested > feature at all, I merely use the patch as a guideline to implement > my own changes. Do not let this discourage you. Your patch has > been recorded in the bug tracking system, and I have studied it > closely. It will be likely used as a reference someday. Just > not today. Having these comments added to the bug system would be useful to start with. You have your reasons for ignoring the bug, but with no movement at all it is frustrating to have to continually patch the source just to get it to build on Windows. Especially when it is so simple to get it to work. Regards, Brodie - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] building sqlite on windows in Unicode
Done. Is there anything else that is necessary to contribute code and patches to sqlite? The bug database seems to lack feedback (many bugs seem to just lie stale, there is no way to create an account to login, etc). I can't find documentation on the website on contributions. Regards, Brodie Christian Smith wrote: Check the requirements in: http://www.sqlite.org/copyright.html for patches and other submissions to SQLite. This could be what is holding up inclusion of the patch. Christian Brodie Thiesfield uttered: Hi, Building sqlite on windows in Unicode mode broke with the addition of the loadable extensions. I found a bug matching this problem and attached a patch to it to fix it a while ago, however I haven't seen any other comments or movement in the bug. I'm not sure what else needs to be done to have a patch accepted, so I'm posting here in the hope to prod it along for review or acceptance. The problem is that the dlopen/LoadLibrary code looks like it was hacked in instead of being added to the platform abstraction API and it doesn't support windows unicode builds out of the box. The patch fixes that problem and silences a warning generated by the new index format. bug... http://www.sqlite.org/cvstrac/tktview?tn=2023 patch... http://www.sqlite.org/cvstrac/attach_get/309/sqlite3.patch Regards, Brodie - To unsubscribe, send email to [EMAIL PROTECTED] - -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Using a view is slower than using the query that comprises the view?
Hi, In my database I find that the explain program for the view (114 statements) is much longer than direct query that comprises the view (89) and almost twice as long as doing the 2 separate queries that make up the union in the view (39 + 30 = 69). To explain more clearly what I mean, if I create a database as... CREATE TABLE ta (a INTEGER NOT NULL, b VARCHAR(30)); CREATE TABLE tb (a INTEGER NOT NULL, b VARCHAR(30)); CREATE VIEW va AS SELECT a, b, 0 AS c FROM ta UNION SELECT a, b, 1 FROM tb; And then run the explain statements... EXPLAIN SELECT a, b, 0 AS c FROM ta; EXPLAIN SELECT a, b, 1 AS c FROM tb; EXPLAIN SELECT a, b, 0 AS c FROM ta UNION SELECT a, b, 1 FROM tb; EXPLAIN SELECT * FROM va; I get the results... 15, 15, 35, 45 (using sqlite 3.3.8) So in this contrived example, we have an overhead of 5 statements for the union, and 10 statements for using the view over issuing the direct union query itself. Not a lot in this example but still indicative of what I am seeing in my database. I assume from this, that querying from a view is slower than querying the tables directly or even just issuing the same query as the view directly? Is it worth going back to issuing direct queries? The advantage of the view is simplicity of course. Regards, Brodie - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] building sqlite on windows in Unicode
Hi, Building sqlite on windows in Unicode mode broke with the addition of the loadable extensions. I found a bug matching this problem and attached a patch to it to fix it a while ago, however I haven't seen any other comments or movement in the bug. I'm not sure what else needs to be done to have a patch accepted, so I'm posting here in the hope to prod it along for review or acceptance. The problem is that the dlopen/LoadLibrary code looks like it was hacked in instead of being added to the platform abstraction API and it doesn't support windows unicode builds out of the box. The patch fixes that problem and silences a warning generated by the new index format. bug... http://www.sqlite.org/cvstrac/tktview?tn=2023 patch... http://www.sqlite.org/cvstrac/attach_get/309/sqlite3.patch Regards, Brodie - To unsubscribe, send email to [EMAIL PROTECTED] -