[sqlite] SIGBUS errors with WAL mode and multiple simultaneous updating clients

[Brodie Thiesfield]

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**) 

Re: [sqlite] .lib file?

[Brodie Thiesfield]

>> -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

[Brodie Thiesfield]

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",
, 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, ));

assert(SQLITE_OK == sqlite3_close(pDatabase));

assert(SQLITE_OK == sqlite3_open_v2("test.sqlite", , 
SQLITE_OPEN_READWRITE, NULL));

assert(SQLITE_OK == sqlite3_exec(pDatabase,
"PRAGMA journal_mode = OFF; "
"BEGIN IMMEDIATE TRANSACTION; ",
NULL, NULL, ));

assert(SQLITE_OK == sqlite3_prepare_v2(pDatabase,
"INSERT INTO dic (entryid, headword, source) VALUES (?, ?, ?);", -1, 
, 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

[Brodie Thiesfield]

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

[Brodie Thiesfield]

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

[Brodie Thiesfield]

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

[Brodie Thiesfield]

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?

[Brodie Thiesfield]

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?

[Brodie Thiesfield]

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)

[Brodie Thiesfield]

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

[Brodie Thiesfield]

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", );
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

[Brodie Thiesfield]

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

[Brodie Thiesfield]

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

[Brodie Thiesfield]

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

[Brodie Thiesfield]

[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

[Brodie Thiesfield]

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

[Brodie Thiesfield]

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

[Brodie Thiesfield]

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

---
--
To unsubscribe

Re: [sqlite] building sqlite on windows in Unicode

[Brodie Thiesfield]

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?

[Brodie Thiesfield]

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

[Brodie Thiesfield]

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]
-