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
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
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
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
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
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
- 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
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
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()
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
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,
.
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
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
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'),
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...
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
> 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
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:
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-
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
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
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
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
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
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
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
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
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
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
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
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
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
This suggestion refers to temporal DB. To those interested by this
approach, this tutorial could help (implementation coded in SQLite):
https://staff.info.unamur.be/dbm/Documents/Tutorials/SQLfast/SQLfast-Case09-Temporal-DB(1).pdf
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
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
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
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"
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,
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
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,
Sad news: death of Hector Garcia-Molina, one of the pioneers in the
field of distributed databases.
https://news.stanford.edu/2019/12/06/hector-garcia-molina-influential-computer-scientist-database-expert-dies-65/.
JLH
___
sqlite-users mailing list
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
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,
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
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)
mporal-DB%281%29.pdf>
... and this one about temporal data manipulation, including coalescing:
https://staff.info.unamur.be/dbm/Documents/Tutorials/SQLfast/SQLfast-Case10-Temporal-DB(2).pdf
<https://staff.info.unamur.be/dbm/Documents/Tutorials/SQLfast/SQLfast-Case10-Temporal-DB%282%29.pdf&g
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:
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
rder by Proj,"On";
Valid if there is one state for each project on each date.
Jean-Luc Hainaut
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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
50 matches
Mail list logo
