Re: [sqlite] Permanently disabling AutoCommit

ansaction. But it is straightforward and you do not need to know about statement types and their effect on transactions. Edzard Pasma. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] completion of sql words

Op 11-feb-2011, om 23:30 heeft Simon Slavin het volgende geschreven: > > On 11 Feb 2011, at 7:19pm, prad wrote: > >> does the sqlite3 interface have completion of sql key words? >> in postgresql you can type SEL and press tab to complete. >> is there such a thing for sqlite3? > > sqlite3 doesn't

Re: [sqlite] completion of sql words

Here is the unbroken link: http://apidoc.apsw.googlecode.com/hg/shell.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] completion of sql words

Op 9-apr-2011, om 21:29 heeft Roger Binns het volgende geschreven: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 04/08/2011 02:40 PM, Edzard Pasma wrote: >> Just found that the APSW shell does tabcomplete (and even for >> tablenames). It is desc

Re: [sqlite] Big difference in performance between Python and gcc

Op 2-jun-2011, om 23:17 heeft Simon Slavin het volgende geschreven: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > > On 2 Jun 2011, at 7:24pm, Roger Binns wrote: > >> (Incidentally I am the author of a "competing" Python SQLite >> binding and >> hence know exactly which SQLite API calls

Re: [sqlite] Feature request: extend the IN operator

filter much more difficult. A built-in soultion would be welcome. Best regards, Edzard Pasma. SQLite version 3.7.7.1 (APSW 3.7.7.1-r1) Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .schema group_split CREATE VI

[sqlite] Index not used in simple alias-like views

Hello, I found a blind spot of the query optimizer. This appears when a table is accessed as a view. I think the problem can be phrased as "the optimizer failing to push an outer join predicate into a view"... This simply means that the following example does not use the existing index:

Re: [sqlite] Index not used in simple alias-like views

Op 24-mei-2010, om 17:59 heeft Dan Kennedy het volgende geschreven: > > On May 24, 2010, at 6:14 PM, Edzard Pasma wrote: > >> Hello, I found a blind spot of the query optimizer. This appears when >> a table is accessed as a view. I think the problem can be phrased as >

[sqlite] WAL still detects deadlock

Hello, The following scenario raises a BUSY error immediately process A. keeps a write lock process B keeps a read-lock and tries to promote this to a write-lock This is the traditional SQLite deadlock situation, detected by the engine as soon as possible. In my test this still occurs

Re: [sqlite] WAL still detects deadlock

Op 31-jul-2010, om 14:16 heeft Dan Kennedy wrote: > > On Jul 31, 2010, at 12:02 AM, Edzard Pasma wrote: > >> Hello, >> >> The following scenario raises a BUSY error immediately >> >> process A. keeps a write lock >> process B keeps a read-l

Re: [sqlite] WAL still detects deadlock

Op 31-jul-2010, om 18:30 heeft Dan Kennedy wrote: > > On Jul 31, 2010, at 10:47 PM, Edzard Pasma wrote: > >> Op 31-jul-2010, om 14:16 heeft Dan Kennedy wrote: >> >>> >>> On Jul 31, 2010, at 12:02 AM, Edzard Pasma wrote: >>> >>>>

Re: [sqlite] specific behavior of busy_timeout with different transaction modes

On 9-nov-2010, at 18:22 Peter Pawlowski wrote: > While debugging an issue with using SQLite with a Rails > application, we > discovered that the behavior of SQLite when setting the > 'sqlite3_busy_timeout' option was unexpected. > > After reading and grokking the SQLite documentation about

Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

lect * from (select [x].[a] from (select a from x) x); a = 123 This also cuts off the unnecessary table alias. If column descriptions could be changed to be like this by default? Edzard Pasma ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] reporting number of changes

that the value is not reset for other type of statements? Or is there an easy way to find if a statement was an update? Edzard Pasma ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] reporting number of changes

ike this is needed. Edzard Pasma --- [EMAIL PROTECTED] wrote: From: "Edzard Pasma" <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> Subject: [sqlite] reporting number of changes Date: Tue, 9 Dec 2008 01:36:39 -0800 Hello, The API function sqlite_changes reports the number of

Re: [sqlite] reporting number of changes

--- [EMAIL PROTECTED] wrote: > Your approach only works in simple cases. The number of changes is a > connection/sqlite3* wide number - ie any SQLite statements associated with it > can cause changes. This would certainly be the case when multi-threading is > used. Yes, agreed > Even in

Re: [sqlite] selecting the top 3 in a group

--- robert.ci...@gmail.com wrote: > I am still curious to know if there is a purely SQL way to do the same. This can be achieved using group_concat: select div, rtrim (substr (s, 1, 10)) nr1, rtrim (substr (s, 1, 10)) nr2, rtrim (substr (s, 1, 10)) nr3 from ( select div,

Re: [sqlite] having the Top N for each group

, to only the top-N. I thought may be SUBSTR is a further solution. The use of LIMIT would be more elegant but I don't see how. is a TopN function planned for the future version of sqlite? ? Edzard Pasma --- sylvain.point...@gmail.com wrote: From: Sylvain Pointeau <sylvain.point...@gmail.

Re: [sqlite] having the Top N for each group

sales.period = period.period order by sales.qty desc limit 3); -- Edzard Pasma --- sylvain.point...@gmail.com wrote: From: Sylvain Pointeau <sylvain.point...@gmail.com> To: sqlite-users@sqlite.org Subject: [sqlite] having the Top N for each group Date: Sat, 14 Feb 2009 09:21:15 +0100

Re: [sqlite] having the Top N for each group

incredible speed in doing UPDATE :) Edzard Pasma --- sylvain.point...@gmail.com wrote: From: Sylvain Pointeau <sylvain.point...@gmail.com> To: edz...@volcanomail.com, General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] having the Top N for each gro

[sqlite] Shared Cache unlock notification to multiple threads

ny unlock-notify callback registered, also if for the same connection. But would not wish to complicate things. It is a somewhat crazy case and may as well be dealt with in the application that wishes to support it. Thanks for this most interesting development, Edzard Pasma

Re: [sqlite] how can we solve IF EXIST in SQLite

Hello, you are cleverer than you think. Your initial idea to use INSERT OR REPLACE might look like: INSERT OR REPLACE INTO stock_tab(stock_id, prod_batch_code, stock_qty, stock_date) SELECT s.stock_id, p.prod_batch_code, IF_NULL (s.stock_qty, 0) + p.purchase_qty

Re: [sqlite] how can we solve IF EXIST in SQLite

Nuyt's idea which may be more comfortable if you like to keep the SQL simple.. Edzard --- edz...@volcanomail.com wrote: From: "Edzard Pasma" <edz...@volcanomail.com> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Cc: <sqlite-users@sqlite

Re: [sqlite] how can we solve IF EXIST in SQLite

, new.purchase_qty, > new.purchase_date ); >        END; > > instead of inserting the same products repeatedly in the stock table i jus > want the quantity as well as the dates to be updated . and wen i insert a > new product_batch_code to the purchase table its shuld be inserted in

[sqlite] Group_concat includes null if first in the group

Hello, I found an issue in group_concat, that appears mainly cosmetical, however may be good to be aware of for those making a more creative use of this function. See the ticket http://www.sqlite.org/cvstrac/tktview?tn=3923. Edzard. ___ sqlite-users

Re: [sqlite] Multiple Writers and Database is Locked Problems

I'd also make sure that all read-cursors are finished before starting a transaction. Or, take the exclusive lock already before the queries (possibly that is what was meant) --- kennethinbox-sql...@yahoo.com wrote: >From: Ken >To: General Discussion of SQLite

Re: [sqlite] Multiple Writers and Database is Locked Problems

ole > > > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Edzard Pasma > Sent: 19 July 2009 11:50 AM > To: kennethinbox-sql...@yahoo.com; General Discussion of SQLite Database > Cc: sqlite-use

Re: [sqlite] Reasons for SQLITE_BUSY

--- nikol...@rath.org wrote: > "Igor Tandetnik" writes: >> Nikolaus Rath wrote: >>> I am accessing the same database from different threads. Each thread >>> has its own connection. I have set the busy timeout for each >>> connection to 5000 milliseconds.

Re: [sqlite] Reasons for SQLITE_BUSY

--- nikol...@rath.org wrote: > "Edzard Pasma" <edz...@volcanomail.com> writes: >> --- nikol...@rath.org wrote: >>> "Igor Tandetnik" <itandet...@mvps.org> writes: >>>> Nikolaus Rath <nikol...@rath.org> wrote: >>>>> I

Re: [sqlite] Busy Handler in Shared Cache Mode?

Hi Nikolaus, Interesting to read your findings. I assume you finally disallowed read_uncommitted. Trying to explain why the wait times can get longer, I come to two reasons. - the time slots where a read-lock is obtained become smaller. This can be significant if there are a lot of

Re: [sqlite] Busy Handler in Shared Cache Mode?

to this. The good news is that Unlock Notification should handle it (http://www.sqlite.org/unlock_notify.html). --- edz...@volcanomail.com wrote: From: "Edzard Pasma" <edz...@volcanomail.com> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Cc: <s

[sqlite] Unlucky number for the ROUND function

I found a number where the ROUND () function goes wrong: SQLite version 3.2.5 Enter ".help" for instructions sqlite> select round (9.95, 1); :.0 Sorry, it must be that I have bad luck today Edzard Pasma _ Tired of spa

[sqlite] group by error in 3.2.6?

or the rest I'm very happy with this version. Thanks, Edzard Pasma _ Tired of spam and viruses? Get a VolcanoMail account with SpamShield Pro and Anti-Virus technology! http://www.volcanomail.com/

[sqlite] Rounding is not so easy

ore noticing any wait time at all. May this be something for a new built-in function? Thanks, Edzard Pasma _ Tired of spam and viruses? Get a VolcanoMail account with SpamShield Pro and Anti-Virus technology! http://www.volcanomail.com/

Re: [sqlite] double calculation in SELECT

--- proxi...@land.ru wrote: From: Michael To: sqlite-users@sqlite.org Subject: [sqlite] double calculation in SELECT Date: Sun, 04 Oct 2009 15:31:47 +0700 Hello, I have following SELECT statement in my program (Delphi+sqlite3):

Re: [sqlite] double calculation in SELECT

--- paiva...@gmail.com wrote: From: Pavel Ivanov To: General Discussion of SQLite Database Subject: Re: [sqlite] double calculation in SELECT Date: Mon, 5 Oct 2009 07:13:28 -0400 First of all, do you really store several rows with the same id in

Re: [sqlite] double calculation in SELECT

ill be different from the original query... Pavel On Mon, Oct 5, 2009 at 8:33 AM, Edzard Pasma <edz...@volcanomail.com> wrote: > > > --- paiva...@gmail.com wrote: > > From: Pavel Ivanov <paiva...@gmail.com> > To: General Discussion of SQLite Database <sqlite-users@

Re: [sqlite] readers and writer

base and/or reader if new > SHARED locks are denied while database is in a PENDING and/or > EXCLUSIVE lock state ( again, this point it not clear in documentation ). > Do I understand it correctly? > > -- > Dmitri Priimak Hello Dmitri, I understaod it the same way. However recently I observed that a PENDING lock does not perform its useful function (prevent writer starvation) in case readers and writers are threads of a single process! May that be the case? Best regards, Edzard Pasma. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Rollback over pending queries

Hello, I wonder if an automatic rollback, as described in //www.sqlite.org/c3ref/busy_handler.html, is more powerful than a rollback programmed in SQL. Particularly if it is able to rollback pending queries from other cursors in the same connection. The programmed rollback fails here with

[sqlite] Rollback over pending queries

Hello, I wonder if an automatic rollback, as described in //www.sqlite.org/c3ref/busy_handler.html, is more powerful than a rollback programmed in SQL. Particularly if it is able to rollback pending queries from other cursors in the same connection. The programmed rollback fails here with

Re: [sqlite] Rollback over pending queries

--- edz...@volcanomail.com wrote: > I wonder if an automatic rollback, as described in > //www.sqlite.org/c3ref/busy_handler.html, is more powerful than a > rollback programmed in SQL. Particularly if it is able to rollback > pending queries from other cursors in the same connection. The >

Re: [sqlite] Reasons for SQLITE_CANTOPEN

olaus Does your application attach at least 20 further databases within each of the 15 connections? Does it open at least 250 files any other way? If any yes, then you have too many open files! Regards, Edzard Pasma ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reasons for SQLITE_CANTOPEN

Op 11-jan-2010, om 1:15 heeft Nikolaus Rath het volgende geschreven > Edzard Pasma <pasm...@concepts.nl> writes: >> Op 10-jan-2010, om 19:25 heeft Nikolaus Rath het volgende geschreven: >> >>> Filip Navara <filip.nav...@gmail.com> writes: >>>>

Re: [sqlite] Passing Value from one query to another

Op 6-feb-2010, om 18:03 heeft Eric Bohlman het volgende geschreven: > BareFeet wrote: >> In general, I think it's much better (performance and logic) to do >> all you can in SQL, without passing values out of SQL results, into >> your non-SQL code, then re-injecting back into another SQL query

Re: [sqlite] Reduce database file size

If the subject is transferring data from a web server to a central database, I like to refer to a feature of an open source package that I published myself. It is only a side-side feature, resulting from the general design, but it was added with just this in mind. This feature, 'Connecting

Re: [sqlite] Please help test the latest query planner changes

Op 16-apr-2010, om 19:51 heeft D. Richard Hipp het volgende geschreven: > > On Apr 16, 2010, at 11:52 AM, Max Vlasov wrote: >> >> SELECT StihiAuthors.Id As AuthId, StihiAuthCandidates.Date as Date, >> StihiAuthCandidates.Num as Num FROM StihiAuthors >> INNER JOIN StihiAuthCandidates ON >>