Re: [sqlite] Column alignment in bash with utf8
On Mon, Jan 02, 2006 at 04:47:31PM -0500, [EMAIL PROTECTED] wrote:
>
> I suppose you could argue that this is a bug in the command-line
> shell. I won't contradict you. But being a monolingual american,
> I have no clue how to reproduce the problem, much less fix it.
You just need to compensate for the discrepancy between the number of
chars in a multibyte string and the actual amount of space (in
columns) that is used when printing that multibyte string. The
attached patch is one way to do this. It uses wcswidth(), which is a
UNIX98 function, so this isn't extremely portable. Therefore, I
wouldn't recommend applying it without adding some autoconf-style
detection and stub out wstrlen() for platforms that don't have the
needed functions.
Cheers,
Matt
--
Matt Wilson
rPath, Inc.
[EMAIL PROTECTED]
Index: src/shell.c
===
RCS file: /sqlite/sqlite/src/shell.c,v
retrieving revision 1.130
diff -u -r1.130 shell.c
--- src/shell.c 29 Dec 2005 12:53:10 - 1.130
+++ src/shell.c 2 Jan 2006 23:38:52 -
@@ -20,6 +20,9 @@
#include
#include "sqlite3.h"
#include
+#include
+#include
+
#if !defined(_WIN32) && !defined(WIN32) && !defined(__MACOS__)
# include
@@ -263,6 +266,31 @@
*/
#define ArraySize(X) (sizeof(X)/sizeof(X[0]))
+/* return the width (number of printed columns) for a multibyte string */
+static int wstrlen(const char *s){
+ size_t len;
+ wchar_t *wcs = NULL;
+ int width, slen;
+
+ /* first figure out how much space we need for the wide char version
+ of the string */
+ len = mbstowcs(NULL, s, 0) + 1;
+ /* we'll need the length of the char string to fall back on */
+ slen = strlen(s);
+ if (-1 == len)
+/* multibyte conversion failure, fall back to strlen */
+return slen;
+ wcs = malloc(len * sizeof(wchar_t));
+ if (NULL == wcs) {
+fprintf(stderr,"Out of memory!\n");
+exit(1);
+ }
+ mbstowcs(wcs, s, len);
+ width = wcswidth(wcs, slen);
+ free(wcs);
+ return width;
+}
+
/*
** Output the given string as a quoted string using SQL quoting conventions.
*/
@@ -438,6 +466,11 @@
}else{
w = 10;
}
+
+if (azArg[i]) {
+ /* compensate for multibyte strings */
+ w += strlen(azArg[i]) - wstrlen(azArg[i]);
+}
fprintf(p->out,"%-*.*s%s",w,w,
azArg[i] ? azArg[i] : p->nullvalue, i==nArg-1 ? "\n": " ");
}
@@ -1665,6 +1698,7 @@
Argv0 = argv[0];
main_init();
+ setlocale(LC_ALL, "");
/* Make sure we have a valid signal handler early, before anything
** else is done.
Re: [sqlite] bug in SELECT DISTINCT ?
On Sat, Sep 17, 2005 at 03:23:04PM +0200, Miha Vrhovnik wrote: > > both > SELECT DISTINCT id, * FROM t1, map WHERE t1.id = map.idT1; > and > SELECT * FROM t1, map WHERE t1.id = map.idT1 AND id IN (SELECT DISTINCT idT1 > AS id FROM map WHERE d0 = 2); sqlite> SELECT id, * FROM t1, map WHERE t1.id = map.idT1 GROUP BY id; 1|1|1|dd|1|2 2|2|1|dd|2|2 3|3|1|dd|3|2 Not sure if this is really what you want, because you're losing data from the map as rows are excluded. -- Matt Wilson rpath, Inc. [EMAIL PROTECTED]
Re: [sqlite] SQLITE_BUSY returned without busy handler called
On Tue, Oct 19, 2004 at 11:58:48AM -0700, Kevin Schmeichel wrote: > > Really, what I was concerned about was getting > SQLITE_BUSY from sqlite_finalize - if I try and call > sqlite_finalize again, I get SQLITE_MISUSE. I haven't > gone through the code in enough detail to determine > what the effects of an incomplete sqlite_finalize > might be (possible memory leaks?). This confused me as well, but the documentation can clear things up: The sqlite3_finalize() function is called to delete a prepared SQL statement obtained by a previous call to sqlite3_prepare() or sqlite3_prepare16(). If the statement was executed successfully, or not executed at all, then SQLITE_OK is returned. If execution of the statement failed then an error code is returned. So if your sqlite3_step() resulted in SQLITE_BUSY, sqlite3_finalize() will also return SQLITE_BUSY. sqlite3_reset() behaves the same way, although the expected return values are not documented. Cheers, Matt
Re: [sqlite] Improving Performance of INSERT INTO?
On Wed, Sep 15, 2004 at 02:14:58PM -0700, Roger Dant wrote:
>
> Here's the slow code:
>
> sqlite3* db;
> CString sql;
> sqlite3_open("c:\\test.db", );
> sqlite3_exec(db, "PRAGMA SYNCHRONOUS", NULL, NULL, NULL);
> sqlite3_exec(db, "CREATE TABLE X (I LONG, J LONG)", NULL, NULL, NULL);
> for (int i = 0; i < 1000; i++) {
>for (int j = 0; j < 500; j++) {
> sql.Format("INSERT INTO X VALUES (%d,%d)", i, j);
> sqlite3_exec(db, sql, NULL, NULL, NULL);
>}
> }
> sqlite3_close(db);
Adding a BEGIN at the start and COMMIT at the end makes this test
complete in 14.5 seconds for me.
Cheers,
Matt
[sqlite] invalid btree cursor read locks honored?
I'm having problems with code that starts a transaction, creates a
table, commits, begins a new transaction, creates a temporary table,
inserts data into the temporary table, and the inserts into the table
the results of a select from the temporary table. The insert
statement results in a SQLITE_ERROR on sqlite3_step. This is because
there are two open btree cursors, one invalid read cursor, one write
cursor. I don't totally understand the significance of the isValid
flag of a btree cursor, but if I ignore cursors with isValid == 0 in
checkReadLocks(), the attached test case succeeds.
Current HEAD CVS has 15 failures both with and without the attached
patch, but this is in a subtle area of the database, so I'm not
certain this is the right fix.
Cheers,
Matt
#include
#include
#include
#include
void exec(sqlite3 *pDb, const char *sql) {
sqlite3_stmt *pStmt;
const char *zLeftover;
int rc;
rc = sqlite3_prepare(pDb, sql, -1, , );
if (rc != SQLITE_OK) {
printf("error occurred while preparing statement: %s\n",
sqlite3_errmsg(pDb));
abort();
}
assert(rc == SQLITE_OK);
assert(*zLeftover == '\0');
rc = sqlite3_step(pStmt);
if (rc != SQLITE_DONE) {
printf("error: sqlite3_step returned %d, expected %d.\n",
rc, SQLITE_DONE);
abort();
}
rc = sqlite3_finalize(pStmt);
assert(rc == SQLITE_OK);
}
int main(void) {
sqlite3 *pDb;
int rc;
rc = sqlite3_open(":memory:", );
assert(rc == SQLITE_OK);
exec(pDb, "BEGIN");
exec(pDb, "CREATE TABLE Dependencies(depId integer primary key,"
"class integer, name str, flag str);");
exec(pDb, "COMMIT");
exec(pDb, "BEGIN");
exec(pDb, "CREATE TEMPORARY TABLE DepCheck(troveId INT, depNum INT, "
"flagCount INT, isProvides BOOL, class INTEGER, name STRING, "
"flag STRING)");
exec(pDb, "INSERT INTO DepCheck "
"VALUES(-1, 0, 1, 0, 2, 'libc.so.6', 'GLIBC_2.0')");
exec(pDb, "INSERT INTO Dependencies \
SELECT DISTINCT \
NULL, \
DepCheck.class, \
DepCheck.name, \
DepCheck.flag \
FROM DepCheck LEFT OUTER JOIN Dependencies ON \
DepCheck.class == Dependencies.class AND \
DepCheck.name == Dependencies.name AND \
DepCheck.flag == Dependencies.flag \
WHERE \
Dependencies.depId is NULL");
exec(pDb, "ROLLBACK");
printf("success\n");
return 0;
}
Index: src/btree.c
===
RCS file: /sqlite/sqlite/src/btree.c,v
retrieving revision 1.189
diff -u -r1.189 btree.c
--- src/btree.c 8 Sep 2004 20:13:05 - 1.189
+++ src/btree.c 15 Sep 2004 18:54:31 -
@@ -3498,7 +3498,7 @@
static int checkReadLocks(Btree *pBt, Pgno pgnoRoot, BtCursor *pExclude){
BtCursor *p;
for(p=pBt->pCursor; p; p=p->pNext){
-if( p->pgnoRoot!=pgnoRoot || p==pExclude ) continue;
+if( p->pgnoRoot!=pgnoRoot || p==pExclude || p->isValid == 0) continue;
if( p->wrFlag==0 ) return SQLITE_LOCKED;
if( p->pPage->pgno!=p->pgnoRoot ){
moveToRoot(p);
Re: [sqlite] SQLite on 64-bit unix
On Wed, Sep 08, 2004 at 04:13:57PM -0400, D. Richard Hipp wrote: > > Please try the latest code in CVS and see if it helps. Tnx. Works with the attached patch. Oh, forgot to mention that printf tests fail: Failures on these tests: printf-1.10.1 printf-1.10.2 printf-1.10.3 printf-1.10.4 printf-1.10.5 printf-1.10.6 printf-1.10.7 printf-1.11.1 printf-1.11.2 printf-1.11.3 printf-1.11.4 printf-1.11.5 printf-1.11.6 printf-1.11.7 printf-1.12.1 printf-1.12.2 printf-1.12.3 printf-1.12.4 printf-1.12.5 printf-1.12.6 printf-1.12.7 printf-1.13.1 printf-1.13.2 printf-1.13.3 printf-1.13.4 printf-1.13.5 printf-1.13.6 printf-1.13.7 printf-1.14.1 printf-1.14.2 printf-1.14.3 printf-1.14.4 printf-1.14.5 printf-1.14.6 printf-1.14.7 printf-1.15.1 printf-1.15.2 printf-1.15.3 printf-1.15.4 printf-1.15.5 printf-1.15.6 printf-1.15.7 printf-1.16.1 printf-1.16.2 printf-1.16.3 printf-1.16.4 printf-1.16.5 printf-1.16.6 printf-1.16.7 printf-8.1 printf-8.2 They're all failures of this type: Expected: [Three integers: -1 17] Got: [Three integers: -1 377] Cheers, Matt Index: Makefile.in === RCS file: /sqlite/sqlite/Makefile.in,v retrieving revision 1.96 diff -u -r1.96 Makefile.in --- Makefile.in 28 Aug 2004 16:19:01 - 1.96 +++ Makefile.in 8 Sep 2004 20:38:07 - @@ -145,6 +145,7 @@ $(TOP)/src/test3.c \ $(TOP)/src/test4.c \ $(TOP)/src/test5.c \ + $(TOP)/src/util.c \ $(TOP)/src/utf.c \ $(TOP)/src/vdbe.c \ $(TOP)/src/md5.c
Re: [sqlite] SQLite on 64-bit unix
On Wed, Sep 08, 2004 at 11:53:30AM -0400, Matt Wilson wrote:
>
> That's not the solution. Your btree cursor (and other pointers) are
> being truncated:
Here's a patch to correct this. There are also some cosmetic changes
to reduce warnings on 64 bit platforms. The patch may need to be
modified to keep from breaking Windows. Probably making more
functions like getDbPointer() and getStmtPointer() would be a good
idea.
Cheers,
Matt
Index: src/btree.c
===
RCS file: /sqlite/sqlite/src/btree.c,v
retrieving revision 1.188
diff -u -r1.188 btree.c
--- src/btree.c 5 Sep 2004 00:33:43 - 1.188
+++ src/btree.c 8 Sep 2004 18:08:35 -
@@ -1404,8 +1404,8 @@
for(pCur=pBt->pCursor; pCur; pCur=pCur->pNext){
MemPage *pPage = pCur->pPage;
char *zMode = pCur->wrFlag ? "rw" : "ro";
-printf("CURSOR %08x rooted at %4d(%s) currently at %d.%d%s\n",
- (int)pCur, pCur->pgnoRoot, zMode,
+printf("CURSOR %#xl rooted at %4d(%s) currently at %d.%d%s\n",
+ (long)pCur, (int) pCur->pgnoRoot, zMode,
pPage ? pPage->pgno : 0, pCur->idx,
pCur->isValid ? "" : " eof"
);
Index: src/pager.c
===
RCS file: /sqlite/sqlite/src/pager.c,v
retrieving revision 1.162
diff -u -r1.162 pager.c
--- src/pager.c 2 Sep 2004 14:57:08 - 1.162
+++ src/pager.c 8 Sep 2004 18:08:37 -
@@ -305,8 +305,8 @@
static int cnt = 0;
if( !pager3_refinfo_enable ) return;
printf(
- "REFCNT: %4d addr=0x%08x nRef=%d\n",
- p->pgno, (int)PGHDR_TO_DATA(p), p->nRef
+ "REFCNT: %4d addr=%#lx nRef=%d\n",
+ p->pgno, (long)PGHDR_TO_DATA(p), p->nRef
);
cnt++; /* Something to set a breakpoint on */
}
@@ -3169,8 +3169,8 @@
PgHdr *pPg;
for(pPg=pPager->pAll; pPg; pPg=pPg->pNextAll){
if( pPg->nRef<=0 ) continue;
-printf("PAGE %3d addr=0x%08x nRef=%d\n",
- pPg->pgno, (int)PGHDR_TO_DATA(pPg), pPg->nRef);
+printf("PAGE %3d addr=%#xl nRef=%d\n",
+ pPg->pgno, (long) PGHDR_TO_DATA(pPg), pPg->nRef);
}
}
#endif
Index: src/table.c
===
RCS file: /sqlite/sqlite/src/table.c,v
retrieving revision 1.17
diff -u -r1.17 table.c
--- src/table.c 6 Sep 2004 17:24:13 - 1.17
+++ src/table.c 8 Sep 2004 18:08:38 -
@@ -31,7 +31,7 @@
int nAlloc;
int nRow;
int nColumn;
- int nData;
+ long nData;
int rc;
} TabResult;
Index: src/test2.c
===
RCS file: /sqlite/sqlite/src/test2.c,v
retrieving revision 1.24
diff -u -r1.24 test2.c
--- src/test2.c 22 Jul 2004 01:19:35 - 1.24
+++ src/test2.c 8 Sep 2004 18:08:38 -
@@ -88,7 +88,7 @@
}
sqlite3pager_set_cachesize(pPager, nPage);
sqlite3pager_set_pagesize(pPager, test_pagesize);
- sprintf(zBuf,"0x%x",(int)pPager);
+ sprintf(zBuf,"%p",pPager);
Tcl_AppendResult(interp, zBuf, 0);
return TCL_OK;
}
@@ -111,7 +111,7 @@
" ID\"", 0);
return TCL_ERROR;
}
- if( Tcl_GetInt(interp, argv[1], (int*)) ) return TCL_ERROR;
+ if( sscanf(argv[1], "%p", ) != 1 ) return TCL_ERROR;
rc = sqlite3pager_close(pPager);
if( rc!=SQLITE_OK ){
Tcl_AppendResult(interp, errorName(rc), 0);
@@ -138,7 +138,7 @@
" ID\"", 0);
return TCL_ERROR;
}
- if( Tcl_GetInt(interp, argv[1], (int*)) ) return TCL_ERROR;
+ if( sscanf(argv[1], "%p", ) != 1 ) return TCL_ERROR;
rc = sqlite3pager_rollback(pPager);
if( rc!=SQLITE_OK ){
Tcl_AppendResult(interp, errorName(rc), 0);
@@ -165,7 +165,7 @@
" ID\"", 0);
return TCL_ERROR;
}
- if( Tcl_GetInt(interp, argv[1], (int*)) ) return TCL_ERROR;
+ if( sscanf(argv[1], "%p", ) != 1 ) return TCL_ERROR;
rc = sqlite3pager_commit(pPager);
if( rc!=SQLITE_OK ){
Tcl_AppendResult(interp, errorName(rc), 0);
@@ -192,7 +192,7 @@
" ID\"", 0);
return TCL_ERROR;
}
- if( Tcl_GetInt(interp, argv[1], (int*)) ) return TCL_ERROR;
+ if( sscanf(argv[1], "%p", ) != 1 ) return TCL_ERROR;
rc = sqlite3pager_stmt_begin(pPager);
if( rc!=SQLITE_OK ){
Tcl_AppendResult(interp, errorName(rc), 0);
@@ -219,7 +219,7 @@
" ID\"", 0);
return TCL_ERROR;
}
- if( Tcl_GetInt(interp, argv[1], (int*)) ) return TCL_ERROR;
+ if( sscanf(argv[1], "%p", ) != 1 ) return TCL_ERROR;
rc = sqlite3pager_stmt_rollback(pPager);
if( rc!=SQLITE_OK ){
Tcl_AppendResult(interp, errorName(rc), 0);
@@ -246,7 +246,7 @@
" ID\"", 0);
return TCL_ERROR;
}
- if( Tcl_GetInt(interp, argv[1
Re: [sqlite] SQLite on 64-bit unix
On Wed, Sep 08, 2004 at 11:10:35AM -0400, D. Richard Hipp wrote: > > Please try again with the lastest version under CVS and see if it helps. > Check-in [1948] will have fixed this problem if I am not badly mistaken. That's not the solution. Your btree cursor (and other pointers) are being truncated: 646 sprintf(zBuf,"0x%x", (int)pCur); 647 Tcl_AppendResult(interp, zBuf, 0); 648 return SQLITE_OK; 105 if( Tcl_GetInt(interp, argv[1], (int*)) ) return TCL_ERROR; 132 if( Tcl_GetInt(interp, argv[1], (int*)) ) return TCL_ERROR; 767 if( Tcl_GetIntFromObj(interp, objv[1], (int*)) ) return TCL_ERROR; Cheers, Matt
Re: [sqlite] sqlite3_busy_timeout() not working with 3.0.x?
On Fri, Sep 03, 2004 at 09:20:05AM -0400, D. Richard Hipp wrote: > > It does if you put the timeout on pDb2. You're right. That's what I get for hacking up test cases at 1 AM. Thanks, Matt
Re: [sqlite] host parameters / bind variables - further workouts
On Wed, Aug 25, 2004 at 09:46:38PM -0700, Darren Duncan wrote: > > While I see this issue now closed, following Richard's explanation of > how things actually are working now, I'm curious as to where in the > SQL:2003 standard it mentions positional host parameters and '?'; > please give a reference; I only saw the named host parameters > mentioned in the standard. -- Darren Duncan ISO/IEC 9075-2:2003 (E) DRAFT 4.24 Dynamic SQL concepts Many SQL-statements can be written to use parameters (which are manifested in static execution of SQL statements as host parameters in s contained in s in s or as host variables in s contained in s). In SQL-statements that are executed dynamically, the parameters are called dynamic parameters (s) and are represented in SQL language by a (?).
Re: [sqlite] host parameters / bind variables - further workouts
On Wed, Aug 25, 2004 at 06:22:26PM -0700, Darren Duncan wrote: > > For "backwards compatability", any plain '?' could still be allowed, > and be mixed with both other usages, and each '?' occurance would > implicitly be the same as ?1, ?2, etc. Not only backwards compatibility, but standards compliance as well. Cheers, Matt
Re: [sqlite] Python bindings for sqlite 3
On Wed, Aug 25, 2004 at 12:27:28AM -0700, David M. Cook wrote:
>
> Yeah, it's read-only, though, (no __setitem__), you have to "cast" to a dict
> if you want to use the rows in your app.
Hmmm. Anything beyond providing the sequence protocol for the result
of a fetchone() is an extension anyway...
> > Example?
>
> Currently you have to use the "--types" hack if you use a BOOL column type
> (which I only use as a way to communicate to my app how the data should be
> displayed.) I avoid the --types hack by converting the value to an int
> after retrieval and before setting it back, but I'd rather not have to worry
> about it.
pysqlite3 in CVS now returns True or False as the results for columns
declared as BOOL in the schema:
cursor.execute('create table test(a bool)')
cursor.execute('insert into test(a) values (?)', True)
cursor.execute('select * from test')
row = cursor.fetchone()
assert(row[0] == True)
> > > * I do like the current pysqlite's transparent handling of date/time types.
>
> > How could it be better? (of course, pysqlite3's handling is non
> > existent...)
>
> Works great currently. I have a need for an interval type, although
> previously I've just used strings and converted them only when I need to add
> them up.
Sounds like I'll need to add date/time handling back at some point.
Python 2.4 will make this easier.
Cheers,
Matt
Re: [sqlite] Python bindings for sqlite 3
On Tue, Aug 24, 2004 at 08:43:47PM -0700, David M. Cook wrote: > > * DBAPI compliance is important to me. sqlite is only one of the DBs I'd > like to support in my apps. Do you know what's currently lacking in conformance? > * I use pyformat pretty heavily. I like being able to use dictionaries as a > data format, and pyformat makes this really easy. But it would probably not > be too hard to create a wrapper for this (there's already a cookbook > recipe for this.) Right. Writing a wrapper wouldn't be too hard. But I would like to encourage the use of standard format SQL and keep the binding as light-weight as possible. I don't see a problem implementing this at a higher level as a wrapper. > * A dictionary-like row object would be nice (pyPgSQL has this). Currently > pysqlite has read-only row objects. But his is not that important as it > is easy to convert tuples to dicts. pysqlite already had that, as does pysqlite3 (see the Row class). > * I'd like to have more transparent handling of bools. Example? > * I do like the current pysqlite's transparent handling of date/time types. How could it be better? (of course, pysqlite3's handling is non existent...) > * Not having having the "--types" hack is not a loss IMO, as I find it > rather awkward. You have to build a string of types for each possible > query you might do, and it's very specific to pysqlite. Yes, definitely. I haven't needed user-defined types in any code I've written. Some method to allow user-defined types to be registered would be nice, but it's going to be an pysqlite-specific extension, which would be good to avoid...
Re: [sqlite] bind variables
On Tue, Aug 24, 2004 at 07:10:55PM -0700, Darren Duncan wrote: > > Meanwhile, look here: http://www.wiscorp.com/SQLStandards.html ISO/IEC 9075-2:2003 (E) (DRAFT) 5.4 Names and identifiers ::= Also see 4.29 "Host parameters" all in 5WD-02-Foundation-2003-09.pdf Cheers, Matt
[sqlite] Re: [Pysqlite-devel] Re: [sqlite] Python bindings for sqlite 3
On Tue, Aug 24, 2004 at 03:37:15PM -0400, D. Richard Hipp wrote:
> >
> >d = { 'blob': 'a\0b', 'id': 2 }
> >cursor.execute("UPDATE t1 SET value=%(bigblob)s WHERE rowid=%(id)d", d)
> >
>
> I'd be willing to extend the lexer/parser of SQLite to accept this kind
> of thing. The only problem here is that '%' is already used to mean the
> remainder-after-integer-division operator, like in C. Could another
> character be used instead? "@" perhaps? Or maybe two "%%" instead of
> just '%'?
It's awfully Python-specific. I think adopting something that other
SQL engines use (":name") would be more generic. SQLServer uses
"@name" iirc.
Cheers,
Matt
Re: [sqlite] bind variables
On Tue, Aug 24, 2004 at 03:15:30PM -0400, Andrew Piskorski wrote: > > Btw, I've used these database APIs and know that they all use ':' to > indicate a named bind variable which then maps to a Tcl variable, in > very much the same scheme you've explained above: Using : to name the variable would work fine, then the parameter binding code in the python binding just needs to iterate through the names and retrieve the item from the dictionary that matches. Cheers, Matt
[sqlite] Re: [Pysqlite-devel] Re: [sqlite] Python bindings for sqlite 3
On Tue, Aug 24, 2004 at 02:55:51PM -0400, D. Richard Hipp wrote:
>
> I do not know if this new technique will be helpful to Python
> or not, but I thought I would bring it to your attention, just
> in case it is. Please note that the changes to support this
> are in CVS but have not be added to a "release". Also note
> that these changes are still considered experimental and are
> subject to change as more experience with the design becomes
> available.
Normally python programmers would like to see named arguments in
dictionary substation format:
d = { 'blob': 'a\0b', 'id': 2 }
cursor.execute("UPDATE t1 SET value=%(bigblob)s WHERE rowid=%(id)d", d)
but I don't know if this will map to named parameters. The binding
could transform %(bigblob)s into $bigblob and store the 's' format
character to do type checking at binding time. It seems like a lot of
work, though.
Cheers,
Matt
[sqlite] Python bindings for sqlite 3
Hi. I've been working on some refactoring of the Python bindings for sqlite. I now have a working Python binding for sqlite 3 which is fairly different than the bindings for sqlite 2. I created a quick test case that creates a new table, inserts 500,000 rows, then selects all of them. Memory utilization is printed after the rows have been inserted, after the transaction is committed, after the SELECT is executed, and after the result set has been iterated through. The test can be seen here: http://www.specifixinc.com/~msw/t4.py results: --- sqlite 2 --- [EMAIL PROTECTED] conary]$ rm -f test.db; time python2.3 t4.py 8656 5968 40520 40520 real0m36.269s user0m35.520s sys 0m0.310s --- sqlite3 --- [EMAIL PROTECTED] conary-work]$ rm -f test.db; time python2.3 t4.py 8084 5840 5840 8084 real0m28.635s user0m28.330s sys 0m0.130s The biggest difference in memory utilization comes from storing a compiled statement in the cursor object and fetching new rows only when cursor.fetch*() is called. The old python binding retrieved the entire result set at cursor.execute() time. Using a cursor as an iterator allows us to traverse the result set while using constant memory. Some other major changes: 1) Wildcards in the SQL passed to cursor.execute() now use the sqlite native '?' or ':N:' format. Previously Python syntax was allowed. Making this change lets us bind parameters to compiled SQL statements natively, without converting them to strings. This will also allow us to re-use a compiled statement by rebinding parameters later. 2) Currently the code that does parameter binding does not handle as many types as the old quoting code did, nor does it allow one to register their own converters for custom types. None of the old mx.DateTime works anymore. Newer versions of Python will have the objects required built-in, then adding support is trivial. 3) The pysqlite-specific magic "-- types" SQL statement is no longer supported. Results are returned according to the schema definition or sqlite_column_type() results. 4) Multiple statements in one cursor.execute() call were supported in the old binding. This won't work with the new compiled-statement method, so an exception will be raised. A _PRELIMINARY_ snapshot of the binding can be found here: http://www.specifixinc.com/~msw/pysqlite3-0.0.0.tar.gz I've been doing development in our Conary CVS repository. You can browse the repository here: http://cvs.specifixinc.com/viewcvs/viewcvs.cgi/conary/pysqlite3/ I'm not trying to hijack the work by Gerhard - but our project needed new features that sqlite 3 brings, and we couldn't wait for a few months while new support was added. I welcome feedback, and am more than happy to throw this work away if a better Python binding for sqlite emerges. Cheers, Matt
Re: [sqlite] Problems with the C interface
On Tue, Aug 24, 2004 at 06:58:32PM +0200, Holger Brunck wrote: > > In my case I skip the sqlite_step() command, but I assume that the > sqlite_compile() command is the important one. You have to call sqlite_step(), or the database engine never does anything. Cheers, Matt
