[sqlite] Bug in "with" query

2014-06-21 Thread Jean-Luc Hainaut
ulation for the implementation of "with"! Jean-Luc Hainaut Prof. Jean-Luc Hainaut Faculté d'Informatique University of Namur Rue Grandgagnage, 21 B-5000 - Namur (Belgium) Phone (direct) : +32 (81) 72 49 96 Phone (secret.): +32 (81) 72 49 64 Fax: +32 (81) 72 49 67

[sqlite] Autocommit in "with" query: bug or feature?

2014-07-06 Thread Jean-Luc Hainaut
e last insertion (Jones should disappear) # and we check the contents of PERSON conn.rollback() displayDBcontents() # Surprise: Jones still is in the DB c.close() conn.close() Prof. Jean-Luc Hainaut Faculté d'Informatique University of Namur Rue Grandgagnage, 21 B-5000 - Namur (Belgi

[sqlite] Is this a regression?

2016-04-24 Thread Jean-Luc Hainaut
re" clause) then querying show the same behaviour. It also appears that removing the second argument of the union "solves" the problem. Has anybody observed this problem? Thanks for future help Jean-Luc Hainaut Prof. Jean-Luc Hainaut Facult? d'Informatique University of Namur Ru

[sqlite] Incremental backup/sync facility?

2016-05-06 Thread Jean-Luc Hainaut
Le 14:43 06/05/2016,Simon Slavin ?crit: >On 6 May 2016, at 1:32pm, Stephan Buchert wrote: > >> The largest database file has now grown to about 180 GB. I need to have >> copies of the files at at least two different places. The databases are >> updated regularly as new data from the satellites

[sqlite] How to get 1 row with no null columns

2016-05-11 Thread Jean-Luc Hainaut
Le 12:26 11/05/2016, vous avez ?crit: >All, > >Is there a simple way to find a row in a table where none of columns contain a >null value? For example: > >SELECT * FROM AnyTable WHERE (all columns IS NOT NULL) LIMIT 1; select * from AnyTable col1||col2||...||coln is not nul limit 1; Fine for

[sqlite] "Distinct" and "Order by" in "group_concat"

2016-05-13 Thread Jean-Luc Hainaut
g term, this technique may be felt unstable and unsecure. My question - Can we be made sure that this form will always, in all cases, produce the desired element ordering in the concatenated list? Thanks for your comments. Jean-Luc Hainaut

[sqlite] Computed column or create index on a view ?

2016-05-15 Thread Jean-Luc Hainaut
- No index on a view in SQLite (so far). - A computed column can be maintained through appropriate triggers (here, "on insert" and "on update"). Efficient if you read data more than you modify them. - Perhaps trying this: create table readings(...); create index trg_cx on

[sqlite] View workarounds

2016-05-23 Thread Jean-Luc Hainaut
As long as you don't try to modify data, a view just behaves like a base table. So, like in base tables, you can't extract, filter, sort, group by, etc. based on non-existing columns. SQLite views are read-only, but modifying data through a view can be done with "instead of" triggers. J-L

Re: [sqlite] Cyclic detection in recursive queries

2016-07-12 Thread Jean-Luc Hainaut
On 12/07/2016 13:59, New, Cecil (GE Aviation, US) wrote: The best I have been able to come with is documented at: http://stackoverflow.com/questions/32640043/cannot-detect-cyclic-data-in-an-sqlite-database/32719216#32719216 But a) it is ugly, b) performance impact of all the length(), replace()

Re: [sqlite] Bug using aggregate functions

2017-02-06 Thread Jean-Luc Hainaut
This is the way SQL (not only SQLite) interprets these queries. Basically you ask information about an empty set: - count(*) = 0, as expected - min(A) is undefined, which is translated in SQL by 'null' value; since 'null' is not 'nothing', you get a 1-line result comprising 'null' ! Regards

Re: [sqlite] About the performance of recursive WITH

2017-02-17 Thread Jean-Luc Hainaut
tive.sql TimeThis :Start Time : Thu Feb 16 07:22:28 2017 TimeThis : End Time : Thu Feb 16 07:22:28 2017 TimeThis : Elapsed Time : 00:00:00.124 -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Jean-Luc Hainaut Sent: Thursday,

Re: [sqlite] Group contiguous rows (islands)

2017-02-15 Thread Jean-Luc Hainaut
. Valid if maximal sequences do not overlap. This query also detects single row sequences (e.g., 'xxx'). An index on TT.test may be useful to support T1*T3 join. For large tables, an iterative procedure will be faster, though less elegant! Regards Jean-Luc Hainaut

[sqlite] About the performance of recursive WITH

2017-02-16 Thread Jean-Luc Hainaut
i.e., nearly 370 times less! Is there something wrong in my queries? Or is there an optimization trick for WITH queries by which one could approach the performance of the iterative version? The scripts are available here: https://www.dropbox.com/s/23t4ycftlk0doy

Re: [sqlite] Group contiguous rows (islands)

2017-02-15 Thread Jean-Luc Hainaut
On 15/02/2017 18:34, E.Pasma wrote: Hello, the query below is simpler. May be slower. But looks pretty relational. Thanks, E Pasma. create table T(date integer,test char(12)); insert into T values (1,'clim'),(3,'clim'),(7,'amb'),(10,'amb'),(12,'xxx'),

Re: [sqlite] About the performance of recursive WITH

2017-02-17 Thread Jean-Luc Hainaut
s : Command Line : sqlite64 graph.db < GRAPH-performance-iterative.sql TimeThis :Start Time : Thu Feb 16 07:22:28 2017 TimeThis : End Time : Thu Feb 16 07:22:28 2017 TimeThis : Elapsed Time : 00:00:00.124 -Original Message- From: sqlite-users [mailto:sqlite-users-boun...

Re: [sqlite] 2 consecutive rises in value

2016-10-20 Thread Jean-Luc Hainaut
What about this one? create table TABLE1(Seq integer primary key autoincrement, Id integer, Date date, Value integer); insert into TABLE1(Id,Date,Value) values(2,'2004-06-23', 42), (...), ...; select distinct T1.Id-- only one per Id from

Re: [sqlite] 2 consecutive rises in value

2016-10-21 Thread Jean-Luc Hainaut
> I had a look at this and tried it without using the extra auto-increment field, using the table ROWID > instead to check for consecutiveness. It would work great and a lot faster indeed if it wasn't for > the multiple values on the same date. Problem with that is that if you group by date it

Re: [sqlite] Drop view “automatically” when its associated table is dropped?

2017-03-22 Thread Jean-Luc Hainaut
On 22/03/2017 11:33, 邱朗 wrote: Hi, Is there any way to drop view “automatically” when its associated table is dropped? It seems no way to do. Then if I drop a table, is there any (easy) way to find views created based on it and I can drop view manually ? A quick and dirty procedure:

Re: [sqlite] group_concat() reverses order given where clause?

2017-08-16 Thread Jean-Luc Hainaut
that simulates the full group_concat version. But it would be nice to include a full-fledged function (whatever the syntax) in a future SQLite version. Why not in the Christmast version for example? Best regards Jean-Luc Hainaut ___ sqlite-

Re: [sqlite] Trigger firing order

2017-07-22 Thread Jean-Luc Hainaut
On 21/07/2017 18:13, petern wrote: a programming pattern that is missing in SQLite trigger body: "if (new.C1 <> old.C1) then ". It can be simulated for updates ("update ... where ... and new.C1 <> old.C1") but not for inserts ("insert" has no "where" clause). Maybe so. But, INSERT can accept

Re: [sqlite] Trigger firing order

2017-07-22 Thread Jean-Luc Hainaut
On 21/07/2017 19:00, Simon Slavin wrote: I’m minded to leave things as they are, with the order undefined. If you really want to trigger a number of different operations in a specific order, put those operations all in one trigger, one after another. Yes, possible now with the reminder of

[sqlite] Trigger firing order

2017-07-21 Thread Jean-Luc Hainaut
o always fire in "inverse creation time" order. Hence my modest proposal: wouldn't it be a nice idea to make this unofficial order a feature of SQLite (just like DB2)? This would make the multiple triggers of a kind much more useful as it currently are. Thanks for your attention H

Re: [sqlite] Hierarchical Queries with Looping in SQLite DB

2017-07-06 Thread Jean-Luc Hainaut
On 06/07/2017 08:08, Ashif Ahamed wrote: When there is some data bringing looping scenario : *INSERT INTO FOLDER VALUES(2, 'Loop Data', 5);* After inserting this loop data , when i trigger the above recursive query in SQLite it keeps on running without bringing any results. Note: In

Re: [sqlite] Providing incrementing column to query

2017-06-25 Thread Jean-Luc Hainaut
scores are linear wrt table size but the declarative one, which is quadratic. Regards Jean-Luc Hainaut ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ordering a GROUP BY, or other concatenating tricks?

2017-10-04 Thread Jean-Luc Hainaut
a simple UDF class that simulates the MySQL full version. It is written in Python 2.7 through the standard SQLite3 interface but it should be easy to translate it in C: https://www.dropbox.com/s/ilpx8duppbus8u3/group_concat2.py?dl=0 Hoping it will help! Jean-Luc Hainaut On 3 Oct 2017, at