Re: [sqlite] Maintaing a Last updated timestamp column using triggers

2020-03-12 Thread Jean-Luc Hainaut
On 12/03/2020 08:47, David Blake wrote: I'm looking for an easy way to maintain a last updated column for each record in several tables and considering if using a triggers is viable. I thought that defining a trigger like this on each table would work CREATE TRIGGER my_update_trigger BEFORE

Re: [sqlite] How to enforce a specific order of group_concat?

2020-03-02 Thread Jean-Luc Hainaut
On 1/03/2020 22:57, mailing lists wrote: Assume I create the following table: CREATE TABLE Test (ID INTEGER PRIMARY KEY, Value TEXT); INSERT INTO Test (Value) VALUES('Alpha'); INSERT INTO Test (Value) VALUES('Beta'); INSERT INTO Test (Value) VALUES('Beta'); INSERT INTO Test (Value)

Re: [sqlite] Trigger name missing

2020-02-26 Thread Jean-Luc Hainaut
On 26/02/2020 12:18, Richard Hipp wrote: On 2/26/20, Jean-Luc Hainaut wrote: Hi all, It seems that SQLite (version 31.1) accepts a trigger declaration in which the name is missing. When fired, this trigger doesn't crashes but exhibits a strange behaviour. In particular, while expression &quo

[sqlite] Trigger name missing

2020-02-26 Thread Jean-Luc Hainaut
lue, the equivalent expression "select from T where Id = new.Id" always returns null (column "Id" is the PK of table "T"). Similarly, "update T set = where Id = new.Id" (silently) fails. Not critical but annoying if you are, like me,

Re: [sqlite] How to group this?

2020-02-11 Thread Jean-Luc Hainaut
On 11/02/2020 01:35, Simon Slavin wrote: I don't think that creating an index on a view actually works, does it? You're right. What was I thinking ? Maybe I've used another implementation of SQL that it does work on. Thanks for picking me up on it. You are right, SQL Server allows you to

Re: [sqlite] case-insensitivity of keywords are hardly documented

2020-02-08 Thread Jean-Luc Hainaut
On 7/02/2020 20:08, Aapo Rantalainen wrote: Hi, I'm just doing some 'software archeology' and I found that: on Nov 21 01:02:00 2004 FossilOrigin-Name: ac72a1d5518f7b505ae2a1bd3be3d71db461ae7e git: f8565825622a1ed48bdaa835968a1137b2ffa593 This sentence have been dropped out of documentation:

Re: [sqlite] single table data collapse with constraints

2020-02-01 Thread Jean-Luc Hainaut
mporal-DB%281%29.pdf> ... and this one about temporal data manipulation, including coalescing: <

Re: [sqlite] Capturing the changes in columns in a table

2020-01-14 Thread Jean-Luc Hainaut
rder by Proj,"On"; Valid if there is one state for each project on each date. Jean-Luc Hainaut ___ sqlite-users mailing list

[sqlite] Death of Hector Garcia-Molina

2019-12-07 Thread Jean-Luc Hainaut
Sad news: death of Hector Garcia-Molina, one of the pioneers in the field of distributed databases. JLH ___ sqlite-users mailing list

Re: [sqlite] DELETE extremely slow

2019-10-31 Thread Jean-Luc Hainaut
is declared.. - no "on delete <...>" on trace.datasetid; so, default "no action". Is it what you want? - no index on this foreign key, hence potential full scan to identify children rows in "trace" (or absence thereof). - index trace_idx_01 declared twice. Most often on

Re: [sqlite] Is it possible to transpose a table using SQL?

2019-03-31 Thread Jean-Luc Hainaut
If by "a dynamically changing table " you mean that any data change (not schema change) in t1 will propagate to v1, and if the set of Product values doesn't change, then you can try this: create table t1(Product,Belgium,France,USA); insert into t1 values

Re: [sqlite] library interfering with input function when running in python console

2019-03-22 Thread Jean-Luc Hainaut
On 20/03/2019 22:48, Anthony-William Thibault wrote: Hello there! Consider the following program Import sqlite3 x = Input("Enter your name”) print(“Hello, ” + x) When you run the code directly with python (double click the .py file or choose open with python) it won’t work Not surprising,

Re: [sqlite] Recursive CTE on tree with doubly linked items

2019-03-11 Thread Jean-Luc Hainaut
Your implementation of trees is that of network databases at the pointer-based physical level but definitely not relational. Try this: create table TREE( ID integer not null primary key, Parent integer references TREE on delete ... on update cascade); -- Notice the absence of "not null"

Re: [sqlite] Displaying hierarchical structure

2019-01-31 Thread Jean-Luc Hainaut
On 31/01/2019 17:59, Bart Smissaert wrote: Thanks, will try that. order by PATH So, where is this path coming from? Simple, from a discrepancy between the script I have tested and the contents of this mail! Here is the complete (tested) script: create table CLOSURE(PARENT_ID integer,ID

Re: [sqlite] Displaying hierarchical structure

2019-01-31 Thread Jean-Luc Hainaut
Recursive CTEs are the most obvious technique to solve this kind of problems. However, a less known technique can do the job: recursive triggers. Here is how the closure of FOLDERS can be computed. It will be stored in table CLOSURE: create table CLOSURE(PARENT_ID integer, ID integer,

Re: [sqlite] Concatenating text literals with NULL yields NULL

2019-01-04 Thread Jean-Luc Hainaut
On 04/01/2019 10:48, Dominique Devienne wrote: I was just surprised by this behavior, see below. Googling it, seems like SQL Server has a setting the change the behavior in that case. Is this standard SQL behavior, as implemented in SQLite? Not complaining, just asking whether I can depend on

Re: [sqlite] Persistent snapshots and rollbacks

2018-10-07 Thread Jean-Luc Hainaut
This suggestion refers to temporal DB. To those interested by this approach, this tutorial could help (implementation coded in SQLite):

Re: [sqlite] [EXTERNAL] Last_row_id

2018-09-20 Thread Jean-Luc Hainaut
On 17/09/2018 14:05, Hick Gunter wrote: A trigger program does not return any result rows. True. But a "select" query in the body of a trigger can be used to evaluate a user-defined function (in the "where" clause for instance) in which any action allowed by your host language can be

Re: [sqlite] Why trigger (UPDATE) does not work?

2018-08-26 Thread Jean-Luc Hainaut
Please check the syntax of the case-end function. As you have written them, they just return boolean values. J-L Hainaut On 26/08/2018 14:16, Csányi Pál wrote: On Sun, Aug 26, 2018 at 07:45:33AM -0400, Brian Curley wrote: You don't list your trigger definition if there's anything that

Re: [sqlite] Common index for multiple databases

2018-08-02 Thread Jean-Luc Hainaut
On 02/08/2018 20:50, Keith Medcalf wrote: In no DBMS known can you index data sourced from multiple tables in the same index -- this applies to "Relational" databases and all other database models (such as pure hierarchical, network, network extended, etc.) In all DBMS systems the contents of

Re: [sqlite] Strange concatenation result

2018-02-27 Thread Jean-Luc Hainaut
Let me suggest an interpretation that seems to comply with the current implementation of "substr". 1. String X is stored in a (ficticious) infinite array, the cells of which are indexed -*, ..., -2, -1, 0, 1, 2,.., +*. 2. String X is stored from cell 1 upward. 3. String 'abcd' is stored in

Re: [sqlite] Strange concatenation result

2018-02-26 Thread Jean-Luc Hainaut
On 26/02/2018 12:19, Cezary H. Noweta wrote: Hello, On 2018-02-26 11:38, Hick Gunter wrote: The substr(x,y,z) function is defined only for nonzero values of y. SQlite can return whatever it feels like if you insist on providing invalid input. With "being nice to the user" and "making a best

Re: [sqlite] Strange concatenation result

2018-02-26 Thread Jean-Luc Hainaut
About the "substr(X,Y,Z)" function, I observe a strange behaviour when Y = 0. If I execute this script: select 'abcd',substr('abcd',0,1),substr('abcd',1,1),substr('abcd',2,1); select 'abcd',substr('abcd',0,2),substr('abcd',1,2),substr('abcd',2,2); select

Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"

2018-01-18 Thread Jean-Luc Hainaut
True, "some" parts of "some" games can be implemented with DB technology, particularly matrix- and graph-based ones. Not only for fast storage and retrieval of game data, but, more interestingly, for implementing complex computation algorithms through SQL queries, that may prove faster than

Re: [sqlite] Emulate right-join

2017-12-06 Thread Jean-Luc Hainaut
Actually, the left outer join is sufficient to execute all the outer join operators: - right outer join: just swap the "from" arguments - full outer joins: union of left and right outer joins Examples (classical "supplier-part-supply" example): create table S(SN,NAME); create table

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: Hoping it will help! Jean-Luc Hainaut On 3 Oct 2017, at

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

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

[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

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] 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 [] On Behalf Of Jean-Luc Hainaut Sent: Thursday,

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

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

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

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] 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] 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] 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: But a) it is ugly, b) performance impact of all the length(), replace()

[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

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