Re: [sqlite] Check for existence of substring in table

[Ulrich Schöbel]

Igor Tandetnik wrote:
> "Ulrich Schöbel"
> <u...@bmu.office-on-the.net> wrote in
> message news:4997ddb2.9070...@bmu.office-on-the.net
>> I have a very simple table 'friends' with only one column
>> 'link':
>>
>> create table friends (link text);
>>
>> Lets assume there are 2 rows, 'abc' and 'def'.
>>
>> Then there is a Tcl variable x containing a string. If $x
>> starts with either abc or def (if $x starts with any value
>> in the table) I want a TRUE value (or something
>> comparable) otherwise a FALSE.
> 
> select exists (select 1 from friends where ? like link || '%');
> 
> Igor Tandetnik 
> 

Wow! Nifty! Thanks Igor.

Regards

Ulli
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check for existence of substring in table

[Ulrich Schöbel]

John Machin wrote:
> On 15/02/2009 8:17 PM, Ulrich Schöbel wrote:
>> Hi all,
>>
>> I'm stuck with my problem. Hopefully someone here can help.
>>
>> I have a very simple table 'friends' with only one column
>> 'link':
>>
>> create table friends (link text);
>>
>> Lets assume there are 2 rows, 'abc' and 'def'.
>>
>> Then there is a Tcl variable x containing a string. If $x
>> starts with either abc or def (if $x starts with any value
>> in the table) I want a TRUE value (or something
>> comparable) otherwise a FALSE.
> 
> You haven't said whether you problem is writing the SQL or the Tcl or 
> both ...

SQL

> here's some SQL that will return 1 if any such link value 
> exists, else 0. Are you sure you don't want to know which link value 
> matches?

Yes.

> What happens if more than 1 link value matches -- do you care?

No.

> 
> 
> sqlite> create table f (link text);
> sqlite> insert into f values('abc');
> sqlite> insert into f values('def');
> sqlite> select * from f;
> abc
> def
> sqlite> select exists(select 1 from f where link = substr('defend', 1, 
> length(link)));
> 1
> sqlite> select exists(select 1 from f where link = substr('abcpqr', 1, 
> length(link)));
> 1
> sqlite> select exists(select 1 from f where link = substr('xyzzy', 1, 
> length(link)));
> 0
> sqlite>
> 

Thanks a lot.

> You'll need to write the Tcl code to do that with your variable $x where 
>   I've got 'defend' etc ...

I'll do.

> all I know about Tcl is that I don't want to 
> know any more about Tcl :-)

You should want to ;-)

> 
> HTH,
> John
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Thanks again

Ulli
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Check for existence of substring in table

[Ulrich Schöbel]

Hi all,

I'm stuck with my problem. Hopefully someone here can help.

I have a very simple table 'friends' with only one column
'link':

create table friends (link text);

Lets assume there are 2 rows, 'abc' and 'def'.

Then there is a Tcl variable x containing a string. If $x
starts with either abc or def (if $x starts with any value
in the table) I want a TRUE value (or something
comparable) otherwise a FALSE.

How can I do that?

Thanks for your time and help.

Ulli
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Split a table

[Ulrich Schöbel]

Hi Yves, hi Filip,

thanks a lot for your help. You solved my problem
and I learned something new.

Kind regards

Ulrich

Am Wednesday 11 July 2007 20:00 schrieb [EMAIL PROTECTED]:
> Ulrich Schöbel a écrit :
> > As this list has excellent SQL wizards, I hope someone can help
> > me on the problem to split a table into two.
> >
> > I have something like this:
> >
> > create table org (
> >   a text,
> >   b text,
> >   c text,
> >   d text
> > );
> >
> > I need to split it into rwo tables as follows:
> >
> > create tbl_a (
> >   a_id integer not null autoincrement,
> >   a text,
> >   b text
> > );
> >
> > create tbl_b (
> >   b_id integer,
> >   c text,
> >   d text
> > );
> >
> > with b_id corresponding to a_id and a_id autogenerated.
> >
> > I know how to do this within a tcl script, but I need a way
> > in pure SQL. Is it at all possible?
> >
> > Thanks for your help
> >
> > Ulrich
> >
> > -
> >
> >
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
> >
>
> Hello,
>
> Look at this example with triggers...
>
> regards,
> Yves.
>
>
> SQLite version 3.4.0
> Enter ".help" for instructions
> sqlite> .read test.sql
>
> .echo ON
> BEGIN TRANSACTION;
>
> create table org (
>a text,
>b text,
>c text,
>d text
> );
>
> create table tbl_a (
>a_id integer PRIMARY KEY NOT NULL,
>a text,
>b text
> );
>
> create table tbl_b (
>b_id integer PRIMARY KEY NOT NULL,
>c text,
>d text
> );
>
>
> CREATE TRIGGER [tbl_a_after_insert] AFTER Insert ON tbl_a
> BEGIN
>INSERT into tbl_b (b_id, c, d)
>SELECT new.a_id, org.c, org.d FROM org
>WHERE org.a = new.a AND org.b = new.b;
> END;
>
> insert into org values('aaa','bbb','ccc','ddd');
> insert into org values('111','222','333','444');
> insert into org values('abc','def','999','888');
>
> COMMIT;
>
> select * from org;
> aaa|bbb|ccc|ddd
> 111|222|333|444
> abc|def|999|888
>
> insert into tbl_a select null, a, b from org;
>
> select * from tbl_a;
> 1|aaa|bbb
> 2|111|222
> 3|abc|def
>
> select * from tbl_b;
> 1|ccc|ddd
> 2|333|444
> 3|999|888
>
> .echo OFF
> sqlite>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Problem with SQLite Pecl extension for PHP4

[Ulrich Schöbel]

It's the other way round: Your database is 3.x, your SQLite library
used by PHP is 2.8.14. Upgrade your PHP.

Ulrich


On Thursday 10 May 2007 14:46, Brandon Eley wrote:
> Thank you for this information. How would I access the 2.8.x database
> from within PHP? is it even possible?
>
> The database is created from a shopping cart program, so I can't
> upgrade it, it has to remain the same version (for now).
>
> Brandon Eley
> [EMAIL PROTECTED]
>
> On May 9, 2007, at 10:59 PM, Chris Peachment wrote:
> > There was a change of file structure with version 3.x.x
> > and this is described in the documentation under Version 3
> > Overview.
> >
> > You are attempting to use Sqlite3 on a version 2.8.x database
> > and the formats are not compatible.
> >
> > On Wed, 9 May 2007 19:42:06 -0400, Brandon Eley wrote:
> >> I've gone through a few tutorials and can't seem to get this right.
> >> I've had my web host install the PECL extension and the following is
> >> what is in my phpinfo();
> >>
> >> sqlite
> >> SQLite support enabled
> >> PECL Module version1.0.3 $Id: sqlite.c,v 1.62.2.25 2004/07/10
> >> 12:25:33 wez Exp $
> >> SQLite Library 2.8.14
> >> SQLite Encodingiso8859
> >>
> >> Directive  Local Value Master Value
> >> sqlite.assoc_case  0   0
> >>
> >> ***
> >>
> >> This is the PHP code I'm using:
> >>
> >>  >> $db = sqlite_open("/home/xxx/data/xxx.db");
> >> ?>
> >>
> >> ***
> >>
> >> This is the error I'm getting:
> >>
> >> Warning: sqlite_open() [function.sqlite-open]: file is encrypted or
> >> is not a database in /home/xxx/public_html/test.php on line 140
> >>
> >> ***
> >>
> >> What am I doing wrong? I can run queries all day long using the
> >> command line sqlite3 but I can't seem to get PHP to read the stinkin
> >> database!
> >>
> >> Any help would be GREATLY appreciated!
> >>
> >> Brandon Eley
> >> [EMAIL PROTECTED]
> >
> > --
> > ---
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > --
> > ---
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>--

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Japanese-Korean characters

[Ulrich Schöbel]

Hi Pavan,

try
http://reddog.s35.xrea.com/wiki/TkSQLite.html

It's excellent.

Ulrich


On Thursday 26 April 2007 10:26, Pavan wrote:
> Hi Ralf,
>
> Thanks for the link.  My OS is Linux.  Is there something available on
> these lines for linux ?
>
> Best Regards,
> Pavan.
>
> On 4/26/07, Ralf Junker <[EMAIL PROTECTED]> wrote:
> > Hello Pavan,
> >
> > >Can we store/retrieve Japanese/korean characters in sqlite db ?
> >
> > Yes, you can well do so, as others have already pointed out.
> >
> > If you are also looking for a Unicode GUI SQLite database manager to
> > display and edit Japanese / Korean character databases, you might want to
> > have a look at SQLiteSpy, freeware from
> > http://www.yunqa.de/delphi/sqlitespy/ .
> >
> > SQLiteSpy is designed to support any language when run on a Windows NT 4
> > and later operating system (Win2K, WinXp, Vista, etc.). I have received
> > positive reports that it works well with German, French, Eastern
> > European, Greek and Cyrillic characters. Given that your system fonts
> > supports Japanese and Koean characters, they should work just as well. If
> > you are experiencing problems, please contact me via e-mail and I will
> > see what I can do.
> >
> > Ralf
> >
> >
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> >
> > -
> >

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: sqlite3 shell doesn't install correctly

[Ulrich Schöbel]

On Sunday 22 April 2007 18:01, Joe Wilson wrote:
> > I've used SQLite-3.3.4 for quite a while, mostly the Tcl binding.
> > A few days ago I installed 3.3.15 and the Tcl binding worked fine.
> > Tonight I downloaded 3.3.16 and compiled it without any errors
> > or warnings and then installed it. When I tried to execute sqlite3
> > the following error appered:
> >
> > sqlite3: symbol lookup error: sqlite3: undefined symbol: sqlite3_io_trace
> >
> > I switched back to 3.3.15 with the same result. A switch back to
> > 3.3.4 behaved normal.
> >
> > What's wrong with 3.3.15 and 3.3.16?
>
> Okay, I get it now. You seem to be using a 3.3.15 sqlite3 binary requiring
> the shared library sqlite3.so. This 3.3.15 sqlite3 binary's shell.c makes
> reference to sqlite3_io_trace in the 3.3.15 sqlite3.so's main.c.
> You cannot use an older 3.3.4 sqlite3.so against the 3.3.15 sqlite3 binary
> because the older shared library lacks sqlite3_io_trace.
>
> In general, sqlite3.so is backwards compatible, but not necessarily
> forward compatible. So you can upgrade an older binary to use a more
> recent 3.x sqlite3.so release, but not the other way around.
>
More or less, yes. I had 3.3.4 and wanted to upgrade to 3.3.16.
The sqlite3 binary then came up with that "sqlite3_io_trace" error.
After I applied your patch it ran fine, but then I noticed the 3.3.4
version info on startup. After all, it was just a ldconfig issue, now
everything works as it should.

Thanks again for your help.

Ulrich

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite3 shell doesn't install correctly

[Ulrich Schöbel]

On Saturday 21 April 2007 17:52, [EMAIL PROTECTED] wrote:
> Ulrich =?iso-8859-1?q?Sch=F6bel?= <[EMAIL PROTECTED]> wrote:
> > On Saturday 21 April 2007 15:40, Joe Wilson wrote:
> > > Apply this patch to fix this problem.
> >
> > Thanks for the patch, but there is still something wrong in the
> > linking stage.
>
> I do not understand why you are having problems.  Nor can
> I figure out why Joe's patch might help.
>
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>--
It was a case of Murphy's Law enforcement.

I must have changed ldconfig, don't know why and when.
A simple rerun of ldconfig and everything was ok.

I apologize for the noise.

Ulrich

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: sqlite3 shell doesn't install correctly

[Ulrich Schöbel]

On Saturday 21 April 2007 15:40, Joe Wilson wrote:
> > I've used SQLite-3.3.4 for quite a while, mostly the Tcl binding.
> > A few days ago I installed 3.3.15 and the Tcl binding worked fine.
> > Tonight I downloaded 3.3.16 and compiled it without any errors
> > or warnings and then installed it. When I tried to execute sqlite3
> > the following error appered:
> >
> > sqlite3: symbol lookup error: sqlite3: undefined symbol: sqlite3_io_trace
> >
> > I switched back to 3.3.15 with the same result. A switch back to
> > 3.3.4 behaved normal.
> >
> > What's wrong with 3.3.15 and 3.3.16?
>
> Apply this patch to fix this problem.
>
>
> Index: src/main.c
> ===
> RCS file: /sqlite/sqlite/src/main.c,v
> retrieving revision 1.370
> diff -u -3 -p -r1.370 main.c
> --- src/main.c  18 Apr 2007 14:24:33 -  1.370
> +++ src/main.c  21 Apr 2007 13:35:18 -
> @@ -27,6 +27,7 @@ const char sqlite3_version[] = SQLITE_VE
>  const char *sqlite3_libversion(void){ return sqlite3_version; }
>  int sqlite3_libversion_number(void){ return SQLITE_VERSION_NUMBER; }
>
> +#if !defined(SQLITE_OMIT_TRACE) && defined(SQLITE_ENABLE_IOTRACE)
>  /*
>  ** If the following function pointer is not NULL and if
>  ** SQLITE_ENABLE_IOTRACE is enabled, then messages describing
> @@ -34,6 +35,7 @@ int sqlite3_libversion_number(void){ ret
>  ** are intended for debugging activity only.
>  */
>  void (*sqlite3_io_trace)(const char*, ...) = 0;
> +#endif
>
>  /*
>  ** If the following global variable points to a string which is the
> Index: src/shell.c
> ===
> RCS file: /sqlite/sqlite/src/shell.c,v
> retrieving revision 1.160
> diff -u -3 -p -r1.160 shell.c
> --- src/shell.c 28 Feb 2007 06:14:25 -  1.160
> +++ src/shell.c 21 Apr 2007 13:35:19 -
> @@ -1242,6 +1242,7 @@ static int do_meta_command(char *zLine,
>  }
>}else
>
> +#if !defined(SQLITE_OMIT_TRACE) && defined(SQLITE_ENABLE_IOTRACE)
>if( c=='i' && strncmp(azArg[0], "iotrace", n)==0 ){
>  extern void (*sqlite3_io_trace)(const char*, ...);
>  if( iotrace && iotrace!=stdout ) fclose(iotrace);
> @@ -1261,6 +1262,7 @@ static int do_meta_command(char *zLine,
>}
>  }
>}else
> +#endif
>
>  #ifndef SQLITE_OMIT_LOAD_EXTENSION
>if( c=='l' && strncmp(azArg[0], "load", n)==0 && nArg>=2 ){
>
>
> __
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>--

Thanks for the patch, but there is still something wrong in the
linking stage.

I have 3 instances of SQLite on my system:
The first is 3.2.1, installed with the system and not used, at least
not by me, library in /usr/lib.
The second is installed in a separate directory tree, version 3.3.4,
which I don't want to change, at least not now.
The third is in /usr/local/..., version 3.3.4 until now, to be substituted
by 3.3.16.

When I run make it produces an sqlite3 binary linked against the
3.3.4 library. I made it temporarily unsearchable, now it links
against the 3.2.1. Next step was to remove the 3.2.1 lib in /usr/lib.
Now the linker doesn't find a library. It simply refuses to accept the
right one. Scanning libtool showed all three lib directories in place.

The shell script sqlite3 runs fine as it did yesterday without the patch.

I'm stuck.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] sqlite3 shell doesn't install correctly

[Ulrich Schöbel]

Hi all,

I've used SQLite-3.3.4 for quite a while, mostly the Tcl binding.
A few days ago I installed 3.3.15 and the Tcl binding worked fine.
Tonight I downloaded 3.3.16 and compiled it without any errors
or warnings and then installed it. When I tried to execute sqlite3
the following error appered:

sqlite3: symbol lookup error: sqlite3: undefined symbol: sqlite3_io_trace

I switched back to 3.3.15 with the same result. A switch back to
3.3.4 behaved normal.

What's wrong with 3.3.15 and 3.3.16?

Kind regards

Ulrich

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] PHP5 and SQLite3.3.13

[Ulrich Schöbel]

Thanks again for your help.

I've given up to use SQLite with PHP and tried Postgres. This works.
I'll probably give up entirely on this PHP crap. As you say, it's a lazy
dog and with 12MB module size it's also a monster dog.

Long live Tcl and, of course, SQLite!

Ulrich


On Thursday 05 April 2007 22:18, John Stanton wrote:
> It is some time since I compiled it so my memory is hazy and I rely on
> my sparse notes.  I compiled php-5.1.0 with those options and what was
> then the current version of Sqlite.  I then compiled and linked it into
> Apache V2.  It worked.  The only ugly part was having to learn the PDO
> rules, but it had compensations because we also use PostgreSQL and it is
> a common interface.
>
> Ultimately we did not persevere because PHP turned out to be a lazy dog
> and we moved on so I cannot give you current details.
>
> I suspect that --with-PDO-sqlite is a default and you have to define
> --without-PDO-sqlite to defeat it.
>
> My dim memory suggests that I used grep to ferret out the sqlite options
> rather than the --help.
>
> The utf8 is to do with the character set.  Others here are better
> equipped to explain that than I am.
>
> Ulrich Schöbel wrote:
> > Thanks, John, this is much better, although still not what I want.
> >
> > It now has (mysteriously) a sqlite version of 3.3.3. No idea where
> > this is originated, maybe part of the bundle.
> >
> > I compiled sqlite-3.3.13, it's running fine, the lib is in
> > /usr/local/lib, the headers are in /usr/local/include, the sources
> > in /usr/local/src/sqlite-3.3.13.
> >
> > You didn't mention --with-pdo-sqlite=... and --with-sqlite3 is
> > not to be found with ./configure -help.
> >
> > What about --enable-sqlite-utf8? What does that mean?
> >
> > Ulrich
> >
> > On Thursday 05 April 2007 21:25, John Stanton wrote:
> >>This is what I used -
> >>To install with SQLITE V3 configure -
> >>
> >>  --without-sqlite --with-sqlite3=/usr/local
> >>--with-apxs=/usr/local/apache/bin/apxs
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
> >
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>--

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] PHP5 and SQLite3.3.13

[Ulrich Schöbel]

Thanks, John, this is much better, although still not what I want.

It now has (mysteriously) a sqlite version of 3.3.3. No idea where
this is originated, maybe part of the bundle.

I compiled sqlite-3.3.13, it's running fine, the lib is in /usr/local/lib,
the headers are in /usr/local/include, the sources 
in /usr/local/src/sqlite-3.3.13.

You didn't mention --with-pdo-sqlite=... and --with-sqlite3 is
not to be found with ./configure -help.

What about --enable-sqlite-utf8? What does that mean?

Ulrich


On Thursday 05 April 2007 21:25, John Stanton wrote:
>This is what I used -
>To install with SQLITE V3 configure -
>
>   --without-sqlite --with-sqlite3=/usr/local
> --with-apxs=/usr/local/apache/bin/apxs

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] PHP5 and SQLite3.3.13

[Ulrich Schöbel]

Yes, I'm using
./configure --enable-shared --with-pdo-sqlite=/usr/local/src/sqlite-3.3.13 
--enable-sqlite-utf8

sqlite itself compiled fine, no problems there.

Ulrich

On Thursday 05 April 2007 19:45, Nathan Biggs wrote:
> Are you using the PDO object in PHP?  If not, PHP only supports 2.8.x.
> To use 3 or greater, you must use the PDO object to connect.  Also make
> sure that you extension is enabled for pdo_sqlite.
>
> -Original Message-
> From: Ulrich Schöbel [mailto:[EMAIL PROTECTED]
> Sent: Thursday, April 05, 2007 1:40 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] PHP5 and SQLite3.3.13
>
> Hi all,
>
> my apologies if this is the wrong list to ask:
>
> I'm trying to get php5 with sqlite3.3.13 running, but I'm completely
> stuck.
> Whatever I tell configure, it always configures the bundled 2.8.x
> sqlite.
>
> Is there anybody in the know?
>
> Thanks
>
> Ulrich
>
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> 
> -
>
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>--

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] PHP5 and SQLite3.3.13

[Ulrich Schöbel]

Hi all,

my apologies if this is the wrong list to ask:

I'm trying to get php5 with sqlite3.3.13 running, but I'm completely stuck.
Whatever I tell configure, it always configures the bundled 2.8.x sqlite.

Is there anybody in the know?

Thanks

Ulrich

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: Unicode Help

[Ulrich Schöbel]

SQLite includes a Tcl API. Tcl does all these conversions with ease.
See the encoding convertto/convertfrom commands and fconfigure.

On Tuesday 05 December 2006 20:42, Nicolas Williams wrote:
> On Tue, Dec 05, 2006 at 06:53:28PM +0100, Marten Feldtmann wrote:
> > Perhaps it would be nice to change sqlite3 in that way, that (when
> > columns with storage class text) these columns are converted to the host
> > platform code page. But actually even in that situation you may have
> > strings, which are not displayable on your screen - because you have no
> > suitable font.
>
> No, but having built-in functions that can do codeset conversion would
> be nice.
>
> -- convert from the default SQLite codeset/encoding (UTF-8) to a given
> -- codeset
> select iconv(foo, NULL, 'ISO-8859-1') from ...;
>
> -- convert to a codeset given by some row column
> select iconv(foo, from_cs, to_cs) from ...;
>
> -- convert to the current locale's codeset
> select iconv(foo, NULL, NULL) from ...;
>
> And functions for Unicode normalization and what not would be nice as
> well.
>
> Nico

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] running a script?

[Ulrich Schöbel]

On Wednesday 23 August 2006 21:45, John Salerno wrote:
> Hi everyone. Can someone tell me the proper syntax for running a sql
> script when starting up sqlite from the command line interface?
>
> Thanks,
> John
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>--

cat scriptfile | sqlite3 mydb

or

sqlite3 -init scriptfile mydb

or

sqlite3 mydb < scriptfile

or (for a single sql command)

sqlite3 mydb 'sqlcmd'

Kind regards

Ulrich

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Tcl - timeout method

[Ulrich Schöbel]

On Wednesday 16 August 2006 12:55, Christian Nassau wrote:
> Ulrich Schöbel wrote:
> > I thought about that, but I didn't want to include
> > the selects into the locked phase, keeping lock
> > times as short as possible. Shouldn't it work
> > correctly with a deferred lock?
>
> I don't think so: imagine two processes that have succesfully carried
> out their selects (possible, since you've only got a shared lock at that
> point) and now want to proceed to the update:
>
>   SELECT(1) SELECT(2)
>   <-- process 1 here<-- process 2 here
>   UPDATE(1) UPDATE(2)
>
> At this point SQLite cannot allow UPDATE(1) because it might potentially
> invalidate the result of SELECT(2) (and vice versa). So there's no sane
> way through and at least one transaction is forced to error out.
>
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>--
Yes, sounds plausible. It's obviously the way to go.

Thanks again

Ulrich

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Tcl - timeout method

[Ulrich Schöbel]

On Wednesday 16 August 2006 12:16, [EMAIL PROTECTED] wrote:
> "Christian Nassau" <[EMAIL PROTECTED]> wrote:
> > I would guess that your requests acquire (shared) read locks when the
> > transaction starts and then fail/deadlock when they try to upgrade this
> > to a write lock in your insert/update statement. Maybe it would help to
> > start the transactions with "BEGIN IMMEDIATE" or "BEGIN EXCLUSIVE"...?
>
> In TCL, you can do this as:
>
>db transaction immediate {
>  # code here
>}
>
> --
> D. Richard Hipp   <[EMAIL PROTECTED]>
>
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>--
Hi Christian, Richard,

I thought about that, but I didn't want to include
the selects into the locked phase, keeping lock
times as short as possible. Shouldn't it work
correctly with a deferred lock?

Nevertheless, I'll give it a try.

Thanks for your help and special thanks to
Richard for sqlite

Ulrich

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Tcl - timeout method

[Ulrich Schöbel]

Hi all,

I'm using sqlite3 inside a Tcl module (mod_websh) of
apache. Everything works fine, except when it comes
to concurrent write accesses.

There's a small transaction where I update two rows.
When I stress the web server it generates a
"database locked" error. I invoke a "db timeout 2000"
just before the transaction, but it doesn't seem to
have any effect.

When I stumbled over this problem I increased the
timeout to 50, which is much longer than the
stress test takes, but the error persists. What's
going wrong here?

Here's a short code snippet:

  db timeout 50
  db transaction {
set last_acc [lindex [db eval {
  select acc_time from last_hit where site = $site and from_ip = $ip
}] 0]
if {![string length $last_acc]} {
  db eval {
insert into last_hit values ($site , $ip , $now)
  }
  set last_acc 0
} else {
  db eval {
update last_hit set acc_time = $now where site = $site and from_ip = 
$ip  }
}
..
  } ;# end of transaction

There's another update inside this transaction, but
it's always the one above that fails.

Maybe the "busy" method is better suited here, but
the docs say nothing about its usage.

Thanks for any help

Ulrich

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] DLLs containing user-defined SQL functions

[Ulrich Schöbel]

Hi Richard,

I'm no windows expert, but why don't you use something like
the tcl stubs mechanism? Build a static sqlite_stubs.a library
and link all loadable dynamic libs against it.

Kind regards

Ulrich


On Wednesday 07 June 2006 16:30, [EMAIL PROTECTED] wrote:
> I'm trying to add the ability to dynamically load DLLs
> containing SQL functions and collating sequences to
> SQLite.  Things are working great on Unix, but I'm having
> issues with Windows.  Windows experts, please help me.
>
> Suppose the main program (the .exe file) contains a
> function procA() and the DLL contains a function procB().
> I want procB() to be able to call procA().  The idea
> is that the main program uses LoadLibrary() to pull
> in the DLL, then GetProcAddress() to find the address
> of procB().  Then the main program calls procB() in
> the DLL which in turn calls procA() in the main program.
>
> This all works great on Unix.  When I use dlopen() to
> attach the shared library, the procA() reference in
> the shared library is automatically resolved to the
> address of procA() in the main program.
>
> But on Windows, I cannot get the DLL to compile because
> it is complaining about the missing procA().
>
> Another way to ask the question is this:  How do I build
> a DLL in windows that can call routines contained in the
> main program that attached the DLL using LoadLibrary()?
>
> --
> D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Purging the mailing list roles. Was: Please Restore Your Account Access

[Ulrich Schöbel]

How about asking a new subscriber to copy some magic number
from the body to the subject of his reply? AFAIK autoresponders
can't do that.

Kind regards

Ulrich


On Wednesday 31 May 2006 17:17, Clark Christensen wrote:
> > And yet somehow, the spammer still managed to get signed up
> > using a "paypal.com" address.  How did they do that?
> > --
>
> As others have pointed-out, there's probably a simple autoresponder on many
> [EMAIL PROTECTED] mailboxes.  It replied, and that was good
> enough :-)
>
> I think if the list confirm messages had a link to click on to validate the
> subscription (that leads somewhere other than replying to the message), the
> anonymous autoresponders wouldn't validate.  Plus, it wouldn't lock-out
> legitimate users at paypal.com (somebody suggested rejecting by domain).
>
>  -Clark

Re: [sqlite] UI framework for sqlite

[Ulrich Schöbel]

Hi David,

if you're looking for a truly portable tool, you should consider
Tcl/Tk. SQLite has a builtin interface to Tcl, Starkits are portable
between many OSes without any code change and the tktable widget
ot tablelist should be what you are looking for to present your data.

Visit http://wiki.tcl.tk/2633 (sqlite), http://wiki.tcl.tk/1877 (tktable)
and http://wiki.tcl.tk/5527 (tablelist) for more info.

For starkits see http://www.equi4.com/starkit.html

Good luck for your project and happy Tcl'ing

Ulrich


On Wednesday 05 April 2006 15:42, David Bicking wrote:
> This is probably off-topic for this list, so let me apologize in advance
> if it is. I don't have a probably in using sqlite itslef, but in rather
> how to use a UI to present the info to the user. I am not neccesarily
> asking for how to advice here, but rather pointers on where I can find
> such advice.
>
> I have written applications that will grab a record from an sqlite
> table, and insert the values in pre-existing text boxes, which the user
> can then change, finally clicking a save button, which sends the changed
> data back to sqlite.
>
> I have also written applications that will select multiple records, and
> dump that data as a printed report on paper.
>
> What I don't know how to do (without specialized tools) is to grab
> multiple records and present them in editable fashion to the user,
> keeping track of changes so thy can be written back. I am looking for
> something like a datasheet view or continuous form on MS Access.
>
> I have been advised to just place enough edits boxes for five or so
> records, then fill in the first five, then give the user "Next Five" and
> "Previous Five" buttons to click. But I would rather a solution in which
> the user can scroll down to see all the records, (within reason).
>
> I also want to do this with the least overhead and the most portable way
> possible. My target audience is like my brother in laws business: they
> have a few non-networked PC's with a mixture of versions of windows. My
> second audience is my own home network of PC's running windows and
> linux.
>
> This, I believe, leaves out any tool that requires KDE or Gnome or
> anything big and bloated. While I consider GTK to be big and bloated, I
> guess that is the upper limit that I want to consider.
>
> My language of choice is Basic, but do program in C, and have programmed
> in C++.
>
> So I am looking for links to  tutorials or how-tos, or even the right
> string of text to use on google to point me in the right direction.
>
> Any advice is appreciated.
> Thanks,
> David

Re: [sqlite] REGEXP

[Ulrich Schöbel]

Hi Danilo,

take a look at
http://aspn.activestate.com/ASPN/docs/ActiveTcl/tcl/TclCmd/re_syntax.htm

Kind regards

Ulrich


On Friday 17 March 2006 10:13, Danilo wrote:
> Hi to All ,
> I'm looking for further information about REGEXP
> (http://www.sqlite.org/lang_expr.html).
> Someone knows how to point out me some examples!
>
> Thanks, Danilo.
> Home Page: http://www.digitazero.org
> venerdì 17 marzo 2006, 9.07

Re: [sqlite] Help needed to diagnose "NOT IN" query

[Ulrich Schöbel]

Hi David,

I don't know wether it's faster, but try

select a,b,c from d where c in (select c from d except select c from e)

Maybe you're lucky and it's faster.

Kind regards

Ulrich

On Wednesday 15 March 2006 22:44, CARTER-HITCHIN, David, FM wrote:
> Hi Brett,
>
> Many thanks for replying.  Well I tried PRAGMA temp_store=memory and that
> sadly did not help.
>
> I'm going to adopt a different approach. Say I've got:
>
> select a,b,c
> from   d
> where  c NOT IN (select c from e)
>
> What I'm trying is:
>
> 1. select a,b,c from d into a temp1 table  (as far as I can see SQLite
> can't do SELECT INTO, so I'll do it for it by creating the table and
> selecting and inserting the rows - please correct me if there's an easier
> way).
> 2. select c from e into a second temp2 table
> 3. delete from temp1 where temp1.c = temp2.c
> 4. the results left in temp1 will be the rows where c NOT IN (select c from
> e) [hopefully :-]
>
> I used to do this with Sybase back in the days when Sybase was really bad
> with NOT IN too - they seemed to improve on things around version 12 or
> 12.5...
>
> Thanks,
>
> David Carter-Hitchin.
> --
> Royal Bank of Scotland
> Interest Rate Derivatives IT
> 135 Bishopsgate
> LONDON EC2M 3TP
>
> Tel: +44 (0) 207 085 1088
>
> > -Original Message-
> > From: Brett Wilson [mailto:[EMAIL PROTECTED]
> > Sent: 14 March 2006 16:48
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] Help needed to diagnose "NOT IN" query
> >
> >
> > David,
> >
> > I asked a similar question, and here is drh's response to me, adapted
> > to your situation. I think it is probably applicable to you as well.
> > I'm not sure this is the type of query that can be made to go really
> > fast no matter what. I actually haven't gotten around to doing this
> > yet, so I can't comment on how well it works.
> >
> >
> > To implement this, SQLite creates a temporary index that contains all
> > unique values of holiday_list.number. Then it scans through the table
> > and for each entry where holiday.hol_id is not in the temp index.
> >
> > Do make sure that your temp tables are stored in RAM.  Compile with
> > -DTEMP_STORE=2.  Or at run-time set
> >
> >  PRAGMA temp_store=memory;
> >
> > That will put the temporary index in RAM and things will tend
> > to go faster.
> >
> > Brett
> >
> > On 3/14/06, CARTER-HITCHIN, David, FM
> >
> > <[EMAIL PROTECTED]> wrote:
> > > Hi,
> > >
> > > Hope someone out there can help - I've got this query which
> >
> > runs VERY
> >
> > > slowly:
> > >
> > > SELECTab_tran.ins_num, holiday.hol_id, holiday.ins_num
> > > FROM  ab_tran, holiday
> > > WHERE ab_tran.ins_num = holiday.ins_num
> > > AND   holiday.hol_id NOT IN ( SELECT number from holiday_list )
> > >
> > > All the tables are fairly chunky, except holiday_list which
> >
> > has 89 rows.  I
> >
> > > just realised that holiday.ins_num is needlessly selected
> >
> > but I doubt very
> >
> > > much that would be causing things to slow down massively.
> >
> > I can run this on
> >
> > > a similar powered Sybase server and it runs in a couple of
> >
> > minutes - with
> >
> > > SQLite, using an in-memory database I've left it all night
> >
> > and it didn't
> >
> > > finish!  Evidently something is wrong somewhere.  I've
> >
> > created indexes on
> >
> > > all the columns in the join and the "NOT IN" clause, but
> >
> > that didn't seem to
> >
> > > help.
> > >
> > > Is there anything else I check?
> > >
> > > I've run the EXPLAIN (output below), but I'm a novice here,
> >
> > not sure what
> >
> > > could be wrong.
> > >
> > > Any ideas will be highly appreciated.
> > >
> > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:476 - 64 rows
> >
> > extracted in
> >
> > > 0.03532 seconds.
> > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 0
> >
> > Goto0   60
> >
> > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 1
> >
> > Integer 0   0
> >
> > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 2
> >
> > OpenRead0
> >
> > > 3
> > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 3
> >
> > SetNumColumns   0
> >
> > > 7
> > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 4
> >
> > Integer 0   0
> >
> > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 5
> >
> > OpenRead1
> >
> > > 5
> > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 6
> >
> > SetNumColumns   1
> >
> > > 2
> > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 7
> >
> > Integer 0   0
> >
> > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 8
> >
> > OpenRead3
> >
> > > 137675  keyinfo(1,BINARY)
> > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 9
> >
> > Rewind  0   56
> >
> > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 10
> >
> > Column  0   1
> >
> > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 11
> >
> > NotNull -1  14
> >
> > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 12Pop
> >
> > 1   0
> >
> > > Tue Mar 14 12:05:10 2006 DEBUG at 

Re: [sqlite] Strange execution times

[Ulrich Schöbel]

Hi John,

there isn't really much to remove, but nevertheless I
followed your advice and replaced the sqlite select
by a small standard tcl procedure. Maybe that set me
on the right track.

There were also some exceptionally high execution
times in between, but not in every test run and not
always at the same repetition count. Those peaks
had a slight tendency to occur at 500, 1000 and
5000 repetitions. From time to time they occured at
other counts or not at all.

I have a couple of daemons running on the machine,
doing several things at more or less arbitrary moments.
I suspect them to be the reason for this behaviour.

One thing still boggles me: The peaks in my timing
experiments with sqlite are reproducibly at a constant
repetition count with approximately predictible times,
as opposed the 'statistical' peaks in the sqlite-less
case.

Unless someone comes up with a similar timing description,
I herewith declare this 'dilemma' solved.

Thank you all for your help and sorry for the alarm.

Kind regards

Ulrich

On Wednesday 22 February 2006 23:41, John Stanton wrote:
> Ulrich, try designing an experiment which removes SQLITE and measures
> the performance of the other software layers.  That might resolve your
> dilemma.
> JS
>

Re: [sqlite] Strange execution times

[Ulrich Schöbel]

On Wednesday 22 February 2006 15:59, Adrian Ho wrote:

> > Do you come to similar results?
>
> Nope, mine were a lot more consistent (Centrino 1.6GHz laptop, 512MB RAM):
>
> t(1)=254 microseconds per iteration
> t(5)=186.6 microseconds per iteration
> t(10)=156.1 microseconds per iteration
> t(50)=147.24 microseconds per iteration
> t(100)=144.88 microseconds per iteration
> t(500)=153.658 microseconds per iteration
> t(1000)=142.218 microseconds per iteration
> t(5000)=142.7774 microseconds per iteration
> t(1)=143.1704 microseconds per iteration

As were Richards. Looks like an issue of my machine/installation,
but I'm perfectly clueless.

> > I have to oppose your statement, Tcl has garbage collection.
> > It doesn't, at least in the sense, that it calls a routine to
> > collect unused space and free it at arbitrary times, i.e. during
> > idle times.
>
> Ah, that's where our perspectives differ.  The definition of "garbage
> collection" which I'm operating from (which I believe is the classical
> CS one) makes no reference whatsoever to time, other than the implicit
> "at some point after it's been identified as garbage".

OK, almost no difference except for the definition.

> While that's true, I'd point out that unset'ing a large list/array,
> or otherwise destroying a heavily-referenced Tcl_Obj, can cause a huge
> cascade of derefs/deletes, so it's not necessarily the case that Tcl's
> GC times are always predictably similar even across iterations of the
> same script.

Right, but my test script is way too simple for such effects.

> I'm keeping an open mind on this one, since I have no hard data to back
> up any conclusion whatsoever.  And since you've subsequently indicated
> that it's now a non-issue (esp. in comparison to MySQL), I guess I'll
> shut up now.  8-)

Thanks for your support. If you have an idea on this later on,
please let me know. I'm staying tuned.

Kind regards

Ulrich

Re: [sqlite] Strange execution times

[Ulrich Schöbel]

Hi Richard,

thanks for trying to reproduce my 'problem'.

I'm using Linux 2.6.12 (Ubuntu Breezy Badger, a debian distro),
Tcl/Tk 8.4.12, sqlite 3.3.4, all pretty recent versions.

I made the same tests today with the same results.

Nevertheless, sqlite is by far faster than mySQL, so I'm going
to switch to it, anyway. But I'm still curious, where this effect
is coming from.

Thanks

Ulrich


On Wednesday 22 February 2006 14:27, [EMAIL PROTECTED] wrote:
> Ulrich =?iso-8859-1?q?Sch=F6bel?= <[EMAIL PROTECTED]> wrote:
> > % time {db eval {select * from cust_persons where first_name='Ulrich'}}
> > 1000 75.498 microseconds per iteration
> > % time {db eval {select * from cust_persons where first_name='Ulrich'}}
> > 1 51.6179 microseconds per iteration
> > % time {db eval {select * from cust_persons where first_name='Ulrich'}}
> > 100 309.95 microseconds per iteration
> > % time {db eval {select * from cust_persons where first_name='Ulrich'}}
> > 10 66.8 microseconds per iteration
> >
> > Where do those 309.95 microseconds come from? What's the
> > difference between running a query 100 times or 1 times?
> > Should I avoid running a select exactly 100 times for some
> > obscure reason?
>
> FWIW, I am unable to reproduce your problem)  I get
> a smooth transition from 10 iterations (84 uS/iteration) to
> 1 (58 uS/iteration).
>
> What OS are you using?  And what version of Tcl/Tk?
> --
> D. Richard Hipp   <[EMAIL PROTECTED]>

[sqlite] Comparison (see: Strange execution times)

[Ulrich Schöbel]

Hi all,

out of curiosity I made the same timing tests as described
in my posting 'Strange execution times' with mySQL.

There's no reason to whine about the 300 microseconds
at 100 selects. MySQL, connected by mysqltcl to Tcl, delivers
after about 26305 microseconds, decreasing for 1
runs to about 232 microseconds.

I'll definitely switch to sqlite! Thanks, Richard.

This is just for your information.

Kind regards

Ulrich

Re: [sqlite] Strange execution times

[Ulrich Schöbel]

Hi Ran,

no, sorry. It's running on a normal Linux PC, runnning 24/7.
No laptop, no stepping up. Time calculation is probably
a bit less accurate in the lower regions, but not that much.

It's really a mind boggler.

Thanks and kind regards

Ulrich


On Wednesday 22 February 2006 11:27, Ran wrote:
> Could it be connected to the stepping up of the CPU? Do you run those tests
> on a laptop? This at least could explain how the many iterations are faster
> (the CPU has time to step up).
> It does not explain why the 10 and 5 are fast as well (maybe when doing few
> iterations, the time calculation is less accurate), but mmm... maybe it
> could explain part of the phenomena?
>
> Ran
>
> On 2/22/06, Ulrich Schöbel <[EMAIL PROTECTED]> wrote:
> > Hi Adrian,
> >
> > I tried your script and got, after a slight modification, quite
> > consistent results. When I tried it as is, I got slightly varying
> > time results with a peak in the 50 to 100 region. Then I
> > commented out all lines concerning the deletion, creation
> > and filling to get the pure retrieval times. Drom then on
> > I got the following almost invariable results,
> >
> > t(1)=538 microseconds per iteration
> > t(5)=69.2 microseconds per iteration
> > t(10)=39.9 microseconds per iteration
> > t(50)=391.48 microseconds per iteration
> > t(100)=215.61 microseconds per iteration
> > t(500)=73.154 microseconds per iteration
> > t(1000)=54.753 microseconds per iteration
> > t(5000)=40.9094 microseconds per iteration
> > t(1)=39.4558 microseconds per iteration
> >
> > The t(1) time is probably due to Tcls bytecode engine, but
> > the t(50) and t(100) times are inexplicable, at least for me.
> >
> > The 'mini database' you use is, apart from a few additional
> > fields, almost identical to the one I used in my previous tests.
> >
> > Do you come to similar results?
> >
> > I have to oppose your statement, Tcl has garbage collection.
> > It doesn't, at least in the sense, that it calls a routine to
> > collect unused space and free it at arbitrary times, i.e. during
> > idle times. Tcl collects its garbage when there is some. Tcls
> > objects are reference counted and as soon as this count
> > reaches zero the object is cleaned up. This costs time, of
> > course, but it happens each time the garbage is due. That
> > has the effect, that garbage collection times are simply
> > included in execution times, regularly. It should not produce
> > the peak times I see at t(50) and t(100).
> >
> > Thanks for your help
> >
> > Ulrich
> >
> > On Wednesday 22 February 2006 02:45, Adrian Ho wrote:
> > > On Wed, Feb 22, 2006 at 01:13:19AM +0200, Ulrich Sch??bel wrote:
> > > > I don't think it's an interface problem. I'm using Tcl, more or less
> > > > the 'natural' language for sqlite. Tcl doesn't have a garbage
> > > > collection.
> > >
> > > Tcl certainly *does* have garbage collection:
> > >
> > > <http://wiki.tcl.tk/3096>
> > > <http://wiki.tcl.tk/12144>
> > >
> > > > The strangest thing is, I can reproduce this behaviour.
> > > > I'm absolutely clueless. I stumbled over it by coincidence.
> > > > Tried 1000 repetitions, was quite fast, so I tried 1,
> > > > which was even faster. This led me to the (obviously wrong)
> > > > conclusion, that sqlite spends some time parsing the sql.
> > > > Next I tried 100 repetitions, expecting a bit more than
> > > > 76 microseconds. 310 microsecs didn't bother me really,
> > > > I tried the 10 reps expecting even more. Then came the surprise:
> > > > only 67 microsecs.
> > > >
> > > > My first feeling was, something like a busy disk or so came
> > > > in just when I tried the 100 reps. But the results were reproducible,
> > > > deviating only by a few microseconds.
> > >
> > > Try running the following script and see if there's an odd pattern to
> > > the timing variations:
> > >
> > > #!/usr/bin/env tclsh
> > > package require sqlite3
> > > if {[file exists aho.db]} {
> > >   file delete aho.db
> > > }
> > > sqlite3 db aho.db
> > > db eval {create table cust_persons ( first_name string, last_name
> > > string )}
> > > db eval {insert into cust_persons values ('Adrian','Ho')}
> > > db eval {insert into cust_persons values ('Thunder','Lightning')}
> > > foreach rounds {1 5 10 50 100 500 1000 5000 1} {
> > >   puts "t($rounds)=[time {db eval {select * from cust_persons where
> > > first_name = 'Adrian'}} $rounds]" }
> > > db close
> > >
> > > - Adrian

Re: [sqlite] Strange execution times

[Ulrich Schöbel]

Hi Adrian,

I tried your script and got, after a slight modification, quite
consistent results. When I tried it as is, I got slightly varying
time results with a peak in the 50 to 100 region. Then I
commented out all lines concerning the deletion, creation
and filling to get the pure retrieval times. Drom then on
I got the following almost invariable results,

t(1)=538 microseconds per iteration
t(5)=69.2 microseconds per iteration
t(10)=39.9 microseconds per iteration
t(50)=391.48 microseconds per iteration
t(100)=215.61 microseconds per iteration
t(500)=73.154 microseconds per iteration
t(1000)=54.753 microseconds per iteration
t(5000)=40.9094 microseconds per iteration
t(1)=39.4558 microseconds per iteration

The t(1) time is probably due to Tcls bytecode engine, but
the t(50) and t(100) times are inexplicable, at least for me.

The 'mini database' you use is, apart from a few additional
fields, almost identical to the one I used in my previous tests.

Do you come to similar results?

I have to oppose your statement, Tcl has garbage collection.
It doesn't, at least in the sense, that it calls a routine to
collect unused space and free it at arbitrary times, i.e. during
idle times. Tcl collects its garbage when there is some. Tcls
objects are reference counted and as soon as this count
reaches zero the object is cleaned up. This costs time, of
course, but it happens each time the garbage is due. That
has the effect, that garbage collection times are simply
included in execution times, regularly. It should not produce
the peak times I see at t(50) and t(100).

Thanks for your help

Ulrich

On Wednesday 22 February 2006 02:45, Adrian Ho wrote:
> On Wed, Feb 22, 2006 at 01:13:19AM +0200, Ulrich Sch??bel wrote:
> > I don't think it's an interface problem. I'm using Tcl, more or less
> > the 'natural' language for sqlite. Tcl doesn't have a garbage
> > collection.
>
> Tcl certainly *does* have garbage collection:
>
> 
> 
>
> > The strangest thing is, I can reproduce this behaviour.
> > I'm absolutely clueless. I stumbled over it by coincidence.
> > Tried 1000 repetitions, was quite fast, so I tried 1,
> > which was even faster. This led me to the (obviously wrong)
> > conclusion, that sqlite spends some time parsing the sql.
> > Next I tried 100 repetitions, expecting a bit more than
> > 76 microseconds. 310 microsecs didn't bother me really,
> > I tried the 10 reps expecting even more. Then came the surprise:
> > only 67 microsecs.
> >
> > My first feeling was, something like a busy disk or so came
> > in just when I tried the 100 reps. But the results were reproducible,
> > deviating only by a few microseconds.
>
> Try running the following script and see if there's an odd pattern to
> the timing variations:
>
> #!/usr/bin/env tclsh
> package require sqlite3
> if {[file exists aho.db]} {
>   file delete aho.db
> }
> sqlite3 db aho.db
> db eval {create table cust_persons ( first_name string, last_name string
> )}
> db eval {insert into cust_persons values ('Adrian','Ho')}
> db eval {insert into cust_persons values ('Thunder','Lightning')}
> foreach rounds {1 5 10 50 100 500 1000 5000 1} {
>   puts "t($rounds)=[time {db eval {select * from cust_persons where
> first_name = 'Adrian'}} $rounds]" }
> db close
>
> - Adrian

Re: [sqlite] Strange execution times

[Ulrich Schöbel]

Hi Nathaniel,

even if it is not absolutely accurate, a factor of about 5
is beyond accuracy tolerances.

Kind regards

Ulrich

On Wednesday 22 February 2006 01:38, Nathaniel Smith wrote:
> On Wed, Feb 22, 2006 at 01:13:19AM +0200, Ulrich Schöbel wrote:
> > I tried the 10 reps expecting even more. Then came the surprise:
> > only 67 microsecs.
> >
> > My first feeling was, something like a busy disk or so came
> > in just when I tried the 100 reps. But the results were reproducible,
> > deviating only by a few microseconds.
>
> Perhaps your timing harness simply can't get accurate results at a
> mere 10 repetitions?
>
> -- Nathaniel

Re: [sqlite] Strange execution times

[Ulrich Schöbel]

Hi Thomas,

no, I can't reproduce it in C. The problem is not worth the effort,
I can live with these timings, it's just strange.

I don't think it's an interface problem. I'm using Tcl, more or less
the 'natural' language for sqlite. Tcl doesn't have a garbage
collection.

The strangest thing is, I can reproduce this behaviour.
I'm absolutely clueless. I stumbled over it by coincidence.
Tried 1000 repetitions, was quite fast, so I tried 1,
which was even faster. This led me to the (obviously wrong)
conclusion, that sqlite spends some time parsing the sql.
Next I tried 100 repetitions, expecting a bit more than
76 microseconds. 310 microsecs didn't bother me really,
I tried the 10 reps expecting even more. Then came the surprise:
only 67 microsecs.

My first feeling was, something like a busy disk or so came
in just when I tried the 100 reps. But the results were reproducible,
deviating only by a few microseconds.

The solution to this riddle is not really urgent, it's just
totally confusing. I'm curious.

Thanks and kind regards

Ulrich


On Wednesday 22 February 2006 00:08, Thomas Chust wrote:
> On Tue, 21 Feb 2006, UlrichSchöbel wrote:
> > [...] Where do those 309.95 microseconds come from? What's the
> > difference between running a query 100 times or 1 times? Should I
> > avoid running a select exactly 100 times for some obscure reason? [...]
>
> Hello,
>
> can you reproduce similar timings in plain C? If not, I would guess that
> the "problem" lies somewhere at the interface between C and the language
> you are using. There can be lots of different reasons; a common one for
> high level languages are garbage collection cycles, for example.
>
> cu,
> Thomas Chust

[sqlite] Strange execution times

[Ulrich Schöbel]

Hi all,

I just made my first steps into sqlite (3.3.4).

I created  a small table, filled it with two rows of data
and timed a select. Those were the results:

% time {db eval {select * from cust_persons where first_name='Ulrich'}} 1000
75.498 microseconds per iteration
% time {db eval {select * from cust_persons where first_name='Ulrich'}} 1
51.6179 microseconds per iteration
% time {db eval {select * from cust_persons where first_name='Ulrich'}} 100
309.95 microseconds per iteration
% time {db eval {select * from cust_persons where first_name='Ulrich'}} 10
66.8 microseconds per iteration

Do you notice the 309.95 microseconds? I first blamed it on a
busy harddisk and repeated all four commands several times.
The results vary slightly but keep in the above picture.

Where do those 309.95 microseconds come from? What's the
difference between running a query 100 times or 1 times?
Should I avoid running a select exactly 100 times for some
obscure reason?

Maybe someone here knows the secret.

Kind regards

Ulrich