Re: [sqlite] "INSERT INTO table AS alias" is invalid inside triggers

[nomad]

On Mon Feb 10, 2020 at 01:34:12AM +0100, no...@null.net wrote:
> I suspect I have found a parsing error in SQLite 3.30.1. Given the
> ...
> However if I wrap it inside a trigger:
> 
> CREATE TABLE t2(b INTEGER);
> 
> CREATE TRIGGER t2_ai
> AFTER INSERT ON t2
> FOR EACH ROW BEGIN
> 
> INSERT INTO t1 AS original (a) VALUES(NEW.b)
>   ON CONFLICT DO NOTHING;
> 
> END;
> 
> Then SQLite fails to prepare: near "AS": syntax error [for Statement
> "CREATE TRIGGER"]

I see the same behaviour with 3.31.1. Could I ask the devs if this is
likely to be fixed at some point or will remain as is?

On a related note and perhaps more generally interesting, I find the
UPSERT mechanism quite useful for updating specific multiple rows in a
specific order:

INSERT INTO
table
SELECT
columns
FROM
table
LEFT JOIN
other_table
ON
join_condition
WHERE
where_condition
ORDER BY
order_condition
ON CONFLICT DO UPDATE SET
x,y,z = (values or some other query)

Previously I have used temporary tables and complicated recursive
triggers to achieve what the above appears to do. I would be very
interested in hearing from those that know better if the above is
reliable and functions the way I think it does.  It is certainly much
easier to understand and I assume more efficient. 

Some systems apparently support an UPDATE ... JOIN syntax but I find
the SQLite UPSERT implementation more powerful because of the ORDER BY
possibility.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] "INSERT INTO table AS alias" is invalid inside triggers

[nomad]

I suspect I have found a parsing error in SQLite 3.30.1. Given the
following:

CREATE TABLE t1(a INTEGER PRIMARY KEY);

The following statement is accepted by the parser:

INSERT INTO t1 AS original (a) VALUES(1)
  ON CONFLICT DO NOTHING;

However if I wrap it inside a trigger:

CREATE TABLE t2(b INTEGER);

CREATE TRIGGER t2_ai
AFTER INSERT ON t2
FOR EACH ROW BEGIN

INSERT INTO t1 AS original (a) VALUES(NEW.b)
  ON CONFLICT DO NOTHING;

END;

Then SQLite fails to prepare: near "AS": syntax error [for Statement
"CREATE TRIGGER"]

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT OR REPLACE in trigger body fails with UNIQUE constraint

[Clemens Ladisch]

Mike _ wrote:
> The trigger documentation says: "An ON CONFLICT clause may be specified as
> part of an UPDATE or INSERT action within the body of the trigger. However
> if an ON CONFLICT clause is specified as part of the statement causing the
> trigger to fire, then conflict handling policy of the outer statement is
> used instead."

Therefore, you should not ever use INSERT OR ... in a trigger.

> Is this because the "ON UPDATE CASCADE" action is considered to be the
> statement causing the trigger to fire,

This is the only thing that updates the child table.

> meaning the default conflict handling policy of ABORT is used?

Apparently.

> If so, is there any way to write something like "ON UPDATE CASCADE OR 
> REPLACE"?

No.  You have to do it by hand.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] INSERT OR REPLACE in trigger body fails with UNIQUE constraint

[Mike _]

Hello mailing list, happy new year! I was wondering if anyone can help
explain why this is an error:
---
sqlite> pragma foreign_keys = on;
sqlite> drop table if exists child;
sqlite> drop table if exists parent;
sqlite> drop table if exists changelog;
sqlite> create table parent (
   ...> parent_id text not null primary key,
   ...> parent_name text
   ...> );
sqlite> create table child (
   ...> child_id text not null primary key,
   ...> parent_id text not null references parent on update cascade,
   ...> child_name text
   ...> );
sqlite> create table changelog (
   ...> change_id integer not null primary key autoincrement,
   ...> tbl text not null,
   ...> id text not null,
   ...> unique (tbl, id)
   ...> );
sqlite> DROP TRIGGER IF EXISTS child_inserts;
sqlite> CREATE TRIGGER child_inserts AFTER INSERT ON child
   ...> BEGIN
   ...> INSERT OR REPLACE INTO changelog (tbl, id) VALUES ('child',
NEW.child_id);
   ...> END;
sqlite> DROP TRIGGER IF EXISTS child_updates;
sqlite> CREATE TRIGGER child_updates AFTER UPDATE ON child
   ...> BEGIN
   ...> INSERT OR REPLACE INTO changelog (tbl, id) VALUES ('child',
NEW.child_id);
   ...> END;
sqlite> insert into parent (parent_id, parent_name) VALUES
('some_parent_uuid', 'the parent');
sqlite> insert into child (child_id, parent_id, child_name) VALUES
('some_child_uuid', 'some_parent_uuid', 'the child');
sqlite> update parent set parent_id = 'updated' where parent_id =
'some_parent_uuid';
Error: UNIQUE constraint failed: changelog.tbl, changelog.id
---

The trigger documentation says: "An ON CONFLICT clause may be specified as
part of an UPDATE or INSERT action within the body of the trigger. However
if an ON CONFLICT clause is specified as part of the statement causing the
trigger to fire, then conflict handling policy of the outer statement is
used instead."

I tried adding an "OR REPLACE" to the update statement but that doesn't fix
the issue.
sqlite> update or replace parent set parent_id = 'updated' where parent_id
= 'some_parent_uuid';
Error: UNIQUE constraint failed: changelog.tbl, changelog.id

Is this because the "ON UPDATE CASCADE" action is considered to be the
statement causing the trigger to fire, meaning the default conflict
handling policy of ABORT is used? If so, is there any way to write
something like "ON UPDATE CASCADE OR REPLACE"? My current workaround is to
just manually delete from changelog in the child_updates trigger instead of
relying on INSERT OR REPLACE to do it.

Any insight would be much appreciated!
Thanks,
Mike
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT/UPDATE generated columns?

[Keith Medcalf]

On Tuesday, 29 October, 2019 20:48, no...@null.net wrote:

>The gencol.html document does not describe the result of attempting to
>INSERT INTO or UPDATE a generated column. Does this raise an error (my
>preference) or is it simply ignored? Could the behaviour be added to
>the documentation?

sqlite> create table t(data, updated as (current_timestamp) stored);
sqlite> insert into t (data, updated) values (1,45);
Error: cannot INSERT into generated column "updated"
sqlite> insert into t (data) values (1);
sqlite> update t set updated=4;
Error: cannot UPDATE generated column "updated"

It thows an error during the prepare ...

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




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

[sqlite] INSERT/UPDATE generated columns?

[nomad]

The gencol.html document does not describe the result of attempting to
INSERT INTO or UPDATE a generated column. Does this raise an error (my
preference) or is it simply ignored? Could the behaviour be added to
the documentation?

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] insert: how to force application to provide value for int primary key?

[Dominique Devienne]

On Tue, Sep 10, 2019 at 4:32 PM R Smith  wrote:

> > So "fat" tables, with large rows, and lost of inserts/updates, is
> basically
> > the worse case
> > scenario for such WITHOUT ROWID tables. It works, no issue there, and as
> > typical of
> > SQLite is often fast enough for most DB sizes, but it *can* matter. Just
> be
> > aware of it.
>
> That is interesting - could you elaborate on how exactly lots of inserts
> would be worse in WITHOUT_ROWID tables than in normal tables?*
>

WITHOUT ROWID tables have a "real" natural key, which as such is much
more likely to have a random distribution, resulting in splicing new rows
all
over the place. While regular tables typically have an auto-incrementing
ROWID,
acting as the B-Tree key, which means new rows are mostly inserted "at the
end",
in the last page, yielding fewer non-leaf page rewrites I'm guessing.

You're probably right to challenge what I wrote. It's mostly intuition, not
hard-facts,
so I could well be completely off-base. I may also have read the above when
I looked
into Oracle IOTs (Index Organized Tables), which are similar I think
(again, perhaps
I'm wrong).

I guess one would need to run experiments with a shim VFS to track IO to
verify
my claims above :). For now, just take it with a grain of salt or just
plain assume
it was talking out of my a..! --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] insert: how to force application to provide value for int primary key?

[R Smith]

On 2019/09/10 2:28 PM, Dominique Devienne wrote:

On Tue, Sep 10, 2019 at 2:09 PM Marek Wieckowski  wrote:


Yes, indeed works. Great, thank you!


Note though that it has performance implications perhaps.

This changes to physical structure of the table, to be stored as an index
basically.

So if you do lots of insertions "in the middle", you could have "write
amplifications".
WITHOUT ROWID tables are ideal for read-mostly tables, and function as-if
the
table is an index that covers all columns. So there's no need for separate
IO for the
index (to lookup a PK) then to its table (to lookup the actual row), it's
just one IO.

It's no different from updating an index, except indexes are typically
"skinnier" (have
fewer and smaller columns) than their corresponding tables, limiting the
impact.



I feel like there's some concepts here that are not perfectly defined in 
the above statements, or I am mistaken, so please allow me to add:


Any SQLite table is essentially itself a covering Index with mostly 
rowid as the indexed key, but in WITHOUT_ROWID tables, it takes whatever 
other primary key is offered as the indexed key (not specifying a PK is 
an error). There is no other difference, and no difference in 
computation and typically a net speed/size gain if your Primary Key is 
not exactly INTEGER. (This is the entire point of offering the 
WITHOUT_ROWID optimization).


I do not think it is "better" suited to specifically reads or writes - 
both can work better (depending on your suitability definition for 
"better"). It is however better suited to smaller data widths in the 
columns (especially for blobs).


If the above is not true, someone kindly point out to me the truth and 
the why of it - thanks!





So "fat" tables, with large rows, and lost of inserts/updates, is basically
the worse case
scenario for such WITHOUT ROWID tables. It works, no issue there, and as
typical of
SQLite is often fast enough for most DB sizes, but it *can* matter. Just be
aware of it.



That is interesting - could you elaborate on how exactly lots of inserts 
would be worse in WITHOUT_ROWID tables than in normal tables?*
Or point me to documentation or even a dev explaining it in a forum 
post, because I have definitely missed that very important bit of info.  
The larger column data thing I do get.  I really need to know how it 
"can" matter, because I use this setup near everywhere and so far been 
blissfully under the impression that it's never worse, and possibly a 
little better for non-integer key use-cases.



*[I get that a text key might have slower insertion into a B-Tree than 
an integer key might have into a B*-Tree, but if your PK needs to be 
text anyway, I don't see how adding it in a normal table (along with the 
normal row_id) could have better performance, unless I'm missing 
something important.]





My $0.02. --DD


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

Re: [sqlite] insert: how to force application to provide value for int primary key?

[Jose Isaias Cabrera]

Dominique Devienne, on Tuesday, September 10, 2019 08:21 AM, wrote...
>
> On Tue, Sep 10, 2019 at 2:20 PM Jose Isaias Cabrera, on
> wrote:
>
> > Marek Wieckowski, on Tuesday, September 10, 2019 08:08 AM, wrote...
> > > > Make it a WITHOUT ROWID table:
> > > >
> > > >   CREATE TABLE example_table   (
> > > >id INTEGER PRIMARY KEY,
> > > >description TEXT NOT NULL
> > > >) WITHOUT ROWID;
> >
> > Will someone point me to a spot where I can understand this piece of SQL?
> > Or, a simple explanation would do.  Thanks.
> >
>
> https://www.sqlite.org/withoutrowid.html

Thanks, Dominique.  It was that easy. :-)

josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] insert: how to force application to provide value for int primary key?

[Dominique Devienne]

On Tue, Sep 10, 2019 at 2:09 PM Marek Wieckowski  wrote:

> Yes, indeed works. Great, thank you!
>

Note though that it has performance implications perhaps.

This changes to physical structure of the table, to be stored as an index
basically.

So if you do lots of insertions "in the middle", you could have "write
amplifications".
WITHOUT ROWID tables are ideal for read-mostly tables, and function as-if
the
table is an index that covers all columns. So there's no need for separate
IO for the
index (to lookup a PK) then to its table (to lookup the actual row), it's
just one IO.

It's no different from updating an index, except indexes are typically
"skinnier" (have
fewer and smaller columns) than their corresponding tables, limiting the
impact.

So "fat" tables, with large rows, and lost of inserts/updates, is basically
the worse case
scenario for such WITHOUT ROWID tables. It works, no issue there, and as
typical of
SQLite is often fast enough for most DB sizes, but it *can* matter. Just be
aware of it.

My $0.02. --DD

https://www.sqlite.org/withoutrowid.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] insert: how to force application to provide value for int primary key?

[Dominique Devienne]

On Tue, Sep 10, 2019 at 2:20 PM Jose Isaias Cabrera 
wrote:

> Marek Wieckowski, on Tuesday, September 10, 2019 08:08 AM, wrote...
> > > Make it a WITHOUT ROWID table:
> > >
> > >   CREATE TABLE example_table   (
> > >id INTEGER PRIMARY KEY,
> > >description TEXT NOT NULL
> > >) WITHOUT ROWID;
>
> Will someone point me to a spot where I can understand this piece of SQL?
> Or, a simple explanation would do.  Thanks.
>

https://www.sqlite.org/withoutrowid.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] insert: how to force application to provide value for int primary key?

[Jose Isaias Cabrera]

Marek Wieckowski, on Tuesday, September 10, 2019 08:08 AM, wrote...
>
> Yes, indeed works. Great, thank you!
>
> Marek
>
>
> On Tue, Sep 10, 2019 at 9:13 AM Richard Hipp, on
>
> > On 9/10/19, Marek Wieckowski, on
> > >
> > > Is there a way in sqlite to ensure (on the database side) that all
> > inserts
> > > for such a table have to have explicit values for such a column?
> >
> > Make it a WITHOUT ROWID table:
> >
> >   CREATE TABLE example_table   (
> >id INTEGER PRIMARY KEY,
> >description TEXT NOT NULL
> >) WITHOUT ROWID;

Will someone point me to a spot where I can understand this piece of SQL?  Or, 
a simple explanation would do.  Thanks.

josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] insert: how to force application to provide value for int primary key?

[Marek Wieckowski]

Yes, indeed works. Great, thank you!

Marek


On Tue, Sep 10, 2019 at 9:13 AM Richard Hipp  wrote:

> On 9/10/19, Marek Wieckowski  wrote:
> >
> > Is there a way in sqlite to ensure (on the database side) that all
> inserts
> > for such a table have to have explicit values for such a column?
>
> Make it a WITHOUT ROWID table:
>
>   CREATE TABLE example_table   (
>id INTEGER PRIMARY KEY,
>description TEXT NOT NULL
>) WITHOUT ROWID;
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] insert: how to force application to provide value for int primary key?

[Richard Hipp]

On 9/10/19, Marek Wieckowski  wrote:
>
> Is there a way in sqlite to ensure (on the database side) that all inserts
> for such a table have to have explicit values for such a column?

Make it a WITHOUT ROWID table:

  CREATE TABLE example_table   (
   id INTEGER PRIMARY KEY,
   description TEXT NOT NULL
   ) WITHOUT ROWID;

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] insert: how to force application to provide value for int primary key?

[Marek Wieckowski]

Hi,

I have an application where a table has an unique not null int field which
identifies the records. I would like to use this column as a primary key.

However, the value has a meaning from the application point of view. There
should never be a record inserted into this table with some default
(random/autoincrement) value filled in automatically by the database.


Is there a way in sqlite to ensure (on the database side) that all inserts
for such a table have to have explicit values for such a column?

Example:
CREATE TABLE example_table
(
id INTEGER PRIMARY KEY,
description TEXT NOT NULL
);
I want
INSERT INTO example_table (description) VALUES ('abc');
to fail with an error, and
INSERT INTO example_table (id, description) VALUES (5, 'abc');
to succeed.

Is it at all possible?

For example, defining a trigger before insert and checking that NEW.id IS
NULL does not work - in before trigger the row has already the field value
(automatically) filled in.

PS: If it's not possible I will end up with a table like
CREATE TABLE example_table
(
id INTEGER PRIMARY KEY,
value INTEGER UNIQUE NOT NULL,
description TEXT NOT NULL
);
with and value being meaningful from the application point of view and id
being internal/artificial. But: I would rather avoid such an additional
artificial column...

Best,
Marek
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT vs BEGIN

[Clemens Ladisch]

Rob Richardson wrote:
> I didn't know it is possible to insert multiple rows into a table using a
> command like this.  Is this just an SQLite feature, or is this part of the
> SQL standard?

This is defined since SQL-92, but only at the Full SQL conformance level.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT vs BEGIN

[Richard Damon]

On 9/3/19 7:18 AM, Dominique Devienne wrote:
> On Tue, Sep 3, 2019 at 12:03 PM Rob Richardson 
> wrote:
>
>> I didn't know it is possible to insert multiple rows into a table using a
>> command like this.
>
> Added over 7 years ago: See
> https://www.sqlite.org/changes.html#version_3_7_11 #1
>
>
>> Is this just an SQLite feature, or is this part of the SQL standard?
>
> I suspect it's non-standard, since Oracle does not support it. But it's
> just a guess on my part. --DD
Many databases I have used include it, so I thought it was standard (if
not universally supported, but that is somewhat common with SQL)

-- 
Richard Damon

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

Re: [sqlite] INSERT vs BEGIN

[Dominique Devienne]

On Tue, Sep 3, 2019 at 12:03 PM Rob Richardson 
wrote:

> I didn't know it is possible to insert multiple rows into a table using a
> command like this.


Added over 7 years ago: See
https://www.sqlite.org/changes.html#version_3_7_11 #1


> Is this just an SQLite feature, or is this part of the SQL standard?


I suspect it's non-standard, since Oracle does not support it. But it's
just a guess on my part. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT vs BEGIN

[Rob Richardson]

I didn't know it is possible to insert multiple rows into a table using a
command like this.  Is this just an SQLite feature, or is this part of the
SQL standard?

RobR

On Mon, Sep 2, 2019 at 8:14 AM Dominique Devienne 
wrote:

> On Mon, Sep 2, 2019 at 12:52 PM Simon Slavin  wrote:
>
> > > One must know that I am obliged to use "BEGIN DEFERRED TRANSACTION"
> > because others threads needs to access to tables.
> > SQLite copes very well when you have one connection writing to the
> > database and other connections reading.  The problems come when you have
> > two connections writing to the database at once.
> >
>
> In WAL mode only! Otherwise readers are blocked when the writer is active,
> and readers prevent the writer from proceeding. --DD
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT vs BEGIN

[Dominique Devienne]

On Mon, Sep 2, 2019 at 12:52 PM Simon Slavin  wrote:

> > One must know that I am obliged to use "BEGIN DEFERRED TRANSACTION"
> because others threads needs to access to tables.
> SQLite copes very well when you have one connection writing to the
> database and other connections reading.  The problems come when you have
> two connections writing to the database at once.
>

In WAL mode only! Otherwise readers are blocked when the writer is active,
and readers prevent the writer from proceeding. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT vs BEGIN

[Dominique Devienne]

On Mon, Sep 2, 2019 at 12:04 PM Grincheux <51...@protonmail.ch> wrote:

> What is the best ?
>
> INSERT INTO artists (name) VALUES
> ("Gene Vincent") ...
> ("Moi _ Me");
>

You're missing commas.
And you should not use double-quotes but single-quotes for string-literals.


> I want to insert 1 000 000 records.
> The other manner tot do is creating a transaction with one insert command
> by line.
>

In all cases, you should have as few transactions as possible IMHO,
typically a single one.

Whether to have one statement per-row or one-statement for multiple-rows is
separate from transactions.
The multi-row insert statement might hit the parser limit if too large.
I've converted in the past a very large
insert-per-row SQL file, to one-insert-per-table (for all that table's row)
and ran into that limit. So I gave up,
since hard to know how many rows to put per statement.

And as Gunter wrote, SQLite will need to parse each statement in full in
memory, so the larger the statement
the more memory used.

If you're doing massive inserts from text files, maybe from a CSV rather
than SQL file might be faster.
You're trading one parser (SQL) for another (CSV), but since DRH wrote
both, and CSV is significantly
simpler than SQL (for parsing), it's possible CSV might have an edge. But
that remains to be seen.


> My question is what is the best thing to do ?
> Or having a transaction for the first sample?
>
> One must know that I am obliged to use "BEGIN DEFERRED TRANSACTION" because
> others threads needs to access to tables.
>

Unless you're using WAL, other threads will be blocked during inserts,
whether you use a
single transactions, or not. They might be able to "sneak-in" betweeb
inserts TX if using multiple TXs,
but then you might block the "inserter".


> Please help me.
>

Programmatically, prepare() and bind() as Gunter wrote again. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT vs BEGIN

[Simon Slavin]

On 1 Sep 2019, at 7:27am, Grincheux <51...@protonmail.ch> wrote:

> INSERT INTO artists (name) VALUES
> ("Gene Vincent")
> ("John Lennon")
> ("Ringo Starr")
> ("Paul McCartney")
> .
> .
> .
> ("Moi _ Me");
> 
> I want to insert 1 000 000 records.

SQLite has to parse the entire command line before it can execute any of it.  
If you use a single long line, SQLite will use up a lot of memory to store that 
entire line until it can start to execute it.  Also, once SQLite begins to 
execute that line the database will be locked for a very long time, since one 
INSDRT command is executed in one lock.

It is probably better to do your insertions as lots of short commands instead.  
If you are binding parameters, you can prepare one INSERT command and just 
rebind the text each time, which will save you a little time and a lot of 
processing.

> One must know that I am obliged to use "BEGIN DEFERRED TRANSACTION" because 
> others threads needs to access to tables.

SQLite copes very well when you have one connection writing to the database and 
other connections reading.  The problems come when you have two connections 
writing to the database at once.

If you have a million rows to insert at once, it is normal to put them in 
transaction batches of, say, a hundred or a thousand.  So you start with BEGIN; 
, then every thousand you do END;BEGIN; again, then end with END .

Remember to set a timeout of a few minutes on /every/ connection to the 
database.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] INSERT vs BEGIN

[Grincheux]

What is the best ?

INSERT INTO artists (name) VALUES
("Gene Vincent")
("John Lennon")
("Ringo Starr")
("Paul McCartney")
.
.
.
("Moi _ Me");

I want to insert 1 000 000 records.
The other manner tot do is creating a transaction with one insert command by
line.
My question is what is the best thing to do ?
Or having a transaction for the first sample?

One must know that I am obliged to use "BEGIN DEFERRED TRANSACTION" because
others threads needs to access to tables.

Please help me.

Grincheux




--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT INTO ... ON CONFLICT(...) DO UPDATE ...

[Richard Hipp]

On 6/14/19, Olivier Mascia  wrote:
> Dear,
>
> Assuming no explicit transaction, do statements like:
>
>   INSERT INTO ... ON CONFLICT(...) DO UPDATE ...
>
> are treated completely within a _single_ implicit transaction?

Yes

> And is this _single_ implicit transaction of type IMMEDIATE?

Yes.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] INSERT INTO ... ON CONFLICT(...) DO UPDATE ...

[Olivier Mascia]

Dear,

Assuming no explicit transaction, do statements like:

INSERT INTO ... ON CONFLICT(...) DO UPDATE ...

are treated completely within a _single_ implicit transaction?
And is this _single_ implicit transaction of type IMMEDIATE?

—  
Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten 
Grüßen,
Olivier Mascia



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

Re: [sqlite] INSERT OR FAIL inserts row although it violates a table constraint

[Richard Hipp]

On 5/7/19, Manuel Rigger  wrote:
> Great, thanks! In which commits were they addressed? I saw two commits that
> address https://www.sqlite.org/src/info/e63cbcfd3378afe6980d6. Was this
> issue derived from the test case?

Issue 1:  Foreign key constraints are not checked until the end of a statement,
so the INSERT OR IGNORE needs to act like INSERT OR ABORT when dealing
with FK constraints.  This will involve a (pending) change to the
documentation as well.
Check-in https://www.sqlite.org/src/info/659c551dcc374a0d

Issue 2: Foreign key constraint checking might cause some elements of
an inserted or updated row to change datatypes, which causes the row
to be constructed incorrectly.  Fixed by check-in
https://www.sqlite.org/src/info/3f1c8051648a341d
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT OR FAIL inserts row although it violates a table constraint

[Manuel Rigger]

Great, thanks! In which commits were they addressed? I saw two commits that
address https://www.sqlite.org/src/info/e63cbcfd3378afe6980d6. Was this
issue derived from the test case?

Best,
Manuel

On Tue, May 7, 2019 at 10:08 PM Richard Hipp  wrote:

> Two separate problems, both now fixed on trunk.
>
> On 5/6/19, Manuel Rigger  wrote:
> > Hi everyone,
> >
> > consider the following example:
> >
> > PRAGMA foreign_keys=true;
> > CREATE TABLE t0 (c0 UNIQUE, c1 UNIQUE, FOREIGN KEY(c0) REFERENCES
> t0(c1));
> > INSERT OR FAIL INTO t0(c0, c1) VALUES
> > (0, 1),
> > (0, 2);
> > SELECT * FROM t0; -- returns 0|1
> >
> > I expect the INSERT to fail, since both the UNIQUE and the FOREIGN KEY
> > constraints are violated. However, the (0, 1) row is inserted, as the
> > result of the SELECT query above demonstrates. When splitting up the
> INSERT
> > into two INSERTS, no row is inserted, as expected:
> >
> > PRAGMA foreign_keys=true;
> > CREATE TABLE t0 (c0 UNIQUE, c1 UNIQUE, FOREIGN KEY(c0) REFERENCES
> t0(c1));
> > INSERT OR FAIL INTO t0(c0, c1) VALUES (0, 1);
> > INSERT OR FAIL INTO t0(c0, c1) VALUES (0, 2);
> > SELECT * FROM t0; -- returns no row
> >
> > I found this bug because a WHERE clause did not fetch the row after a
> > REINDEX:
> >
> > PRAGMA foreign_keys=true;
> > CREATE TABLE t0 (c0 INT UNIQUE, c1 TEXT UNIQUE, FOREIGN KEY(c0)
> REFERENCES
> > t0(c1));
> > INSERT OR FAIL INTO t0(c0, c1) VALUES
> > (0, 1),
> > (0, 2);
> > REINDEX;;
> > SELECT * FROM t0; -- returns 0|1
> > SELECT * FROM t0 WHERE c1=1; -- returns nothing
> >
> > Best,
> > Manuel
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT OR FAIL inserts row although it violates a table constraint

[Richard Hipp]

Two separate problems, both now fixed on trunk.

On 5/6/19, Manuel Rigger  wrote:
> Hi everyone,
>
> consider the following example:
>
> PRAGMA foreign_keys=true;
> CREATE TABLE t0 (c0 UNIQUE, c1 UNIQUE, FOREIGN KEY(c0) REFERENCES t0(c1));
> INSERT OR FAIL INTO t0(c0, c1) VALUES
> (0, 1),
> (0, 2);
> SELECT * FROM t0; -- returns 0|1
>
> I expect the INSERT to fail, since both the UNIQUE and the FOREIGN KEY
> constraints are violated. However, the (0, 1) row is inserted, as the
> result of the SELECT query above demonstrates. When splitting up the INSERT
> into two INSERTS, no row is inserted, as expected:
>
> PRAGMA foreign_keys=true;
> CREATE TABLE t0 (c0 UNIQUE, c1 UNIQUE, FOREIGN KEY(c0) REFERENCES t0(c1));
> INSERT OR FAIL INTO t0(c0, c1) VALUES (0, 1);
> INSERT OR FAIL INTO t0(c0, c1) VALUES (0, 2);
> SELECT * FROM t0; -- returns no row
>
> I found this bug because a WHERE clause did not fetch the row after a
> REINDEX:
>
> PRAGMA foreign_keys=true;
> CREATE TABLE t0 (c0 INT UNIQUE, c1 TEXT UNIQUE, FOREIGN KEY(c0) REFERENCES
> t0(c1));
> INSERT OR FAIL INTO t0(c0, c1) VALUES
> (0, 1),
> (0, 2);
> REINDEX;;
> SELECT * FROM t0; -- returns 0|1
> SELECT * FROM t0 WHERE c1=1; -- returns nothing
>
> Best,
> Manuel
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] INSERT OR FAIL inserts row although it violates a table constraint

[Manuel Rigger]

Hi everyone,

consider the following example:

PRAGMA foreign_keys=true;
CREATE TABLE t0 (c0 UNIQUE, c1 UNIQUE, FOREIGN KEY(c0) REFERENCES t0(c1));
INSERT OR FAIL INTO t0(c0, c1) VALUES
(0, 1),
(0, 2);
SELECT * FROM t0; -- returns 0|1

I expect the INSERT to fail, since both the UNIQUE and the FOREIGN KEY
constraints are violated. However, the (0, 1) row is inserted, as the
result of the SELECT query above demonstrates. When splitting up the INSERT
into two INSERTS, no row is inserted, as expected:

PRAGMA foreign_keys=true;
CREATE TABLE t0 (c0 UNIQUE, c1 UNIQUE, FOREIGN KEY(c0) REFERENCES t0(c1));
INSERT OR FAIL INTO t0(c0, c1) VALUES (0, 1);
INSERT OR FAIL INTO t0(c0, c1) VALUES (0, 2);
SELECT * FROM t0; -- returns no row

I found this bug because a WHERE clause did not fetch the row after a
REINDEX:

PRAGMA foreign_keys=true;
CREATE TABLE t0 (c0 INT UNIQUE, c1 TEXT UNIQUE, FOREIGN KEY(c0) REFERENCES
t0(c1));
INSERT OR FAIL INTO t0(c0, c1) VALUES
(0, 1),
(0, 2);
REINDEX;;
SELECT * FROM t0; -- returns 0|1
SELECT * FROM t0 WHERE c1=1; -- returns nothing

Best,
Manuel
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT OR REPLACE and foreign keys

[Olivier Mascia]

> Le 23 oct. 2018 à 19:52, Roman Fleysher  a 
> écrit :
> 
> I am using INSERT OR REPLACE ... it seems that "replace" is implemented as 
> "delete then insert" rather than "update". Is that normal and expected? Am I 
> doing something wrong?

Normal and expected.  Check https://www.sqlite.org/lang_insert.html. The OR 
REPLACE is a kind of exception handling mechanism.

What you are looking for is the upsert clause for your insert statement: 
https://www.sqlite.org/syntax/upsert-clause.html.

Some other engines might have an INSERT OR UPDATE (not REPLACE) and you have 
lead to think the OR REPLACE implemented by SQLite is that feature. It is not. 
The more general ON CONFLICT ... DO ... is what you're after.


-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


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

Re: [sqlite] INSERT OR REPLACE and foreign keys

[Shawn Wagner]

That's normal. It deletes the conflicting row and inserts a new one.

On Tue, Oct 23, 2018, 10:58 AM Roman Fleysher <
roman.fleys...@einstein.yu.edu> wrote:

> Dear SQLiters,
>
> I am using INSERT OR REPLACE to update a table which holds a column which
> servers as a foreign key. But I noticed a strange behavior: If the parent
> record existed, then replace mechanism replaces it, but the records from
> children tables are deleted. The foreign key is set up to cascade on
> delete, as I think it should. So it seems that "replace" is implemented as
> "delete then insert" rather than "update". Is that normal and expected? Am
> I doing something wrong?
>
> Thank you,
>
> Roman
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] INSERT OR REPLACE and foreign keys

[Roman Fleysher]

Dear SQLiters,

I am using INSERT OR REPLACE to update a table which holds a column which 
servers as a foreign key. But I noticed a strange behavior: If the parent 
record existed, then replace mechanism replaces it, but the records from 
children tables are deleted. The foreign key is set up to cascade on delete, as 
I think it should. So it seems that "replace" is implemented as "delete then 
insert" rather than "update". Is that normal and expected? Am I doing something 
wrong?

Thank you,

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

Re: [sqlite] insert or replace performance with self "references" column

[Simon Slavin]

On 2 Jul 2018, at 2:45pm, Allen  wrote:

> My C program was creating a "Parent_Index" on two different tables, so
> the second Parent_Index on the Transactions table was not being
> created, and this was causing the "replace" to do a table scan.  I
> solved the problem by making the index names globally unique.

Easy mistake to make.  Glad you figured it out.

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

Re: [sqlite] insert or replace performance with self "references" column

[Allen]

Thank you for your assistance. I got the same results as you using the
shell, and was only getting the problem in my C program.

My C program was creating a "Parent_Index" on two different tables, so
the second Parent_Index on the Transactions table was not being
created, and this was causing the "replace" to do a table scan.  I
solved the problem by making the index names globally unique.

Thanks again.


> Date: Thu, 28 Jun 2018 18:09:09 +0100
> From: Simon Slavin 
> To: SQLite mailing list 
> Subject: Re: [sqlite] insert or replace performance with self
> "references" column
> Message-ID: 
> Content-Type: text/plain;   charset=us-ascii
>
> On 27 Jun 2018, at 11:20pm, Allen  wrote:
>
>> I have a table with an additional index and a query:
>>
>> "create table Transactions (Id integer primary key not null, Parent
>> references Transactions(id), Body varchar);"
>>
>> "create index Parent_Index on Transactions (Parent);"
>>
>> EXPLAIN QUERY PLAN insert or replace into Transactions (Id, Parent,
>> Body) values (?1, ?2, ?3);
>> 23 0 0 SCAN TABLE Transactions
>> 43 0 0 SCAN TABLE Transactions
>
> SQLite version 3.22.0 2017-12-05 15:00:17
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> PRAGMA foreign_keys = ON;
> sqlite> CREATE TABLE Transactions (id INTEGER PRIMARY KEY,
>...> parent REFERENCES Transactions(id),
>...> Body TEXT);
> sqlite> create index Parent_Index on Transactions (Parent);
> sqlite> EXPLAIN QUERY PLAN insert into Transactions (Id, Parent, Body) values 
> (3, 4, 'Hello');
> sqlite> EXPLAIN QUERY PLAN insert OR REPLACE into Transactions (Id, Parent, 
> Body) values (3, 4, 'Hello');
> 0|0|0|SCAN TABLE Transactions USING COVERING INDEX Parent_Index
> 0|0|0|SCAN TABLE Transactions USING COVERING INDEX Parent_Index
>
> If you replace "not null' from your CREATE TABLE command, do you get the 
> result I do ?
> What, specifically, INSERT OR REPLACE command did you use ?
> Which version of the command-line shell tool did you use ?
>
> Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] insert or replace performance with self "references" column

[Simon Slavin]

On 27 Jun 2018, at 11:20pm, Allen  wrote:

> I have a table with an additional index and a query:
> 
> "create table Transactions (Id integer primary key not null, Parent
> references Transactions(id), Body varchar);"
> 
> "create index Parent_Index on Transactions (Parent);"
> 
> EXPLAIN QUERY PLAN insert or replace into Transactions (Id, Parent,
> Body) values (?1, ?2, ?3);
> 23 0 0 SCAN TABLE Transactions
> 43 0 0 SCAN TABLE Transactions

SQLite version 3.22.0 2017-12-05 15:00:17
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> PRAGMA foreign_keys = ON;
sqlite> CREATE TABLE Transactions (id INTEGER PRIMARY KEY,
   ...> parent REFERENCES Transactions(id),
   ...> Body TEXT);
sqlite> create index Parent_Index on Transactions (Parent);
sqlite> EXPLAIN QUERY PLAN insert into Transactions (Id, Parent, Body) values 
(3, 4, 'Hello');   
sqlite> EXPLAIN QUERY PLAN insert OR REPLACE into Transactions (Id, Parent, 
Body) values (3, 4, 'Hello');
0|0|0|SCAN TABLE Transactions USING COVERING INDEX Parent_Index
0|0|0|SCAN TABLE Transactions USING COVERING INDEX Parent_Index

If you replace "not null' from your CREATE TABLE command, do you get the result 
I do ?
What, specifically, INSERT OR REPLACE command did you use ?
Which version of the command-line shell tool did you use ?

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

Re: [sqlite] insert or replace performance with self "references" column

[Clemens Ladisch]

Allen wrote:
> create table Transactions (Id integer primary key not null, Parent references 
> Transactions(id), Body varchar);
> create index Parent_Index on Transactions (Parent);
>
> EXPLAIN QUERY PLAN insert or replace into Transactions (Id, Parent, Body) 
> values (?1, ?2, ?3);
> 23 0 0 SCAN TABLE Transactions
> 43 0 0 SCAN TABLE Transactions
>
> - Is sqlite really doing one or two table scans to perform the "insert
> or replace" with a "references" self column?

It does the two scans for the foreign key constraint processing.
(The rowid processing is not mentioned in the EQP output.)

> - If so, is there a way to eliminate this (other than removing either
> the "references" or the "or replace")?

As mentioned by Keith, the index is not used because of the wrong
affinity of the Parent column.  With "Parent integer", both scans
are efficient.


Regars,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] insert or replace performance with self "references" column

[Donald Griggs]

On Wed, Jun 27, 2018, 7:47 PM Keith Medcalf  wrote:

>
> If you give the parent column a proper affinity (ie, integer) do you get
> "happiness making" results?
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Allen
> >Sent: Wednesday, 27 June, 2018 16:20
> >To: sqlite-users@mailinglists.sqlite.org
> >Subject: [sqlite] insert or replace performance with self
> >"references" column
> >
> >I have a table with an additional index and a query:
> >
> >"create table Transactions (Id integer primary key not null, Parent
> >references Transactions(id), Body varchar);"
> >
> >"create index Parent_Index on Transactions (Parent);"
> >
> >EXPLAIN QUERY PLAN insert or replace into Transactions (Id, Parent,
> >Body) values (?1, ?2, ?3);
> >23 0 0 SCAN TABLE Transactions
> >43 0 0 SCAN TABLE Transactions
> >
> >The double SCAN TABLE seems to have something to do with both the
> >"references" column and the "or replace" statement.  If I remove
> >either, then the SCAN goes away.
> >
> >Questions:
> >
> >- Is my syntax for the " insert or replace" statement correct, and
> >will it do what I expect (insert a new row with a new auto-generated
> >Id if Id is NULL, insert a new row with the given Id if Id is not
> >NULL
> >and no row exists with that Id, or update the existing row with the
> >given Id if Id is not NULL and a row exists with that Id)?
> >
> >- Is sqlite really doing one or two table scans to perform the
> >"insert
> >or replace" with a "references" self column?
> >
> >- If so, is there a way to eliminate this (other than removing either
> >the "references" or the "or replace")?
> >
> >Thanks much.
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] insert or replace performance with self "references" column

[Keith Medcalf]

If you give the parent column a proper affinity (ie, integer) do you get 
"happiness making" results?

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Allen
>Sent: Wednesday, 27 June, 2018 16:20
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] insert or replace performance with self
>"references" column
>
>I have a table with an additional index and a query:
>
>"create table Transactions (Id integer primary key not null, Parent
>references Transactions(id), Body varchar);"
>
>"create index Parent_Index on Transactions (Parent);"
>
>EXPLAIN QUERY PLAN insert or replace into Transactions (Id, Parent,
>Body) values (?1, ?2, ?3);
>23 0 0 SCAN TABLE Transactions
>43 0 0 SCAN TABLE Transactions
>
>The double SCAN TABLE seems to have something to do with both the
>"references" column and the "or replace" statement.  If I remove
>either, then the SCAN goes away.
>
>Questions:
>
>- Is my syntax for the " insert or replace" statement correct, and
>will it do what I expect (insert a new row with a new auto-generated
>Id if Id is NULL, insert a new row with the given Id if Id is not
>NULL
>and no row exists with that Id, or update the existing row with the
>given Id if Id is not NULL and a row exists with that Id)?
>
>- Is sqlite really doing one or two table scans to perform the
>"insert
>or replace" with a "references" self column?
>
>- If so, is there a way to eliminate this (other than removing either
>the "references" or the "or replace")?
>
>Thanks much.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

[sqlite] insert or replace performance with self "references" column

[Allen]

I have a table with an additional index and a query:

"create table Transactions (Id integer primary key not null, Parent
references Transactions(id), Body varchar);"

"create index Parent_Index on Transactions (Parent);"

EXPLAIN QUERY PLAN insert or replace into Transactions (Id, Parent,
Body) values (?1, ?2, ?3);
23 0 0 SCAN TABLE Transactions
43 0 0 SCAN TABLE Transactions

The double SCAN TABLE seems to have something to do with both the
"references" column and the "or replace" statement.  If I remove
either, then the SCAN goes away.

Questions:

- Is my syntax for the " insert or replace" statement correct, and
will it do what I expect (insert a new row with a new auto-generated
Id if Id is NULL, insert a new row with the given Id if Id is not NULL
and no row exists with that Id, or update the existing row with the
given Id if Id is not NULL and a row exists with that Id)?

- Is sqlite really doing one or two table scans to perform the "insert
or replace" with a "references" self column?

- If so, is there a way to eliminate this (other than removing either
the "references" or the "or replace")?

Thanks much.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert with an '

[John McKown]

Very good point. I think that everyone should do it that way. It is a bit
more work, but is vastly superior.

On Mon, Jun 11, 2018, 03:23 Olivier Mascia  wrote:

> > Le 11 juin 2018 à 10:07, Peter Nacken  a écrit :
> >
> > I try to insert email addresses into a table and get an error with
> addresses they have a single quotation mark ( na'm...@domain.ltd ).
>
> > Sorry I forgot I‘m using C#
>
> (Assuming: "create table T(E text);" for the following.)
>
> If you're building the text of your insert statement before executing it,
> you will have either to build the statement string as:
>
> insert into T values('na''m...@domain.tld');
> or
> insert into T values("na'm...@domain.ltd");
>
> Both of which you can test with the command-line sqlite3.exe.
>
> But it would be far more appropriate to use prepare:
>
> insert into T values(?);
>
> and then bind the parameter before executing. You won't have to alter your
> data for inserting and it will be much better for SQL code injection
> protection, depending where the email address comes from.
>
> I'm sure there is plenty of documentation with your language binding for
> SQLite on how to prepare, bind, execute. Instead of building a final
> statement as a complete string and then execute it.
>
> --
> Best Regards, Meilleures salutations, Met vriendelijke groeten,
> Olivier Mascia
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert with an '

[Richard Damon]

On 6/11/18 4:23 AM, Olivier Mascia wrote:
>> Le 11 juin 2018 à 10:07, Peter Nacken  a écrit :
>>
>> I try to insert email addresses into a table and get an error with addresses 
>> they have a single quotation mark ( na'm...@domain.ltd ).
>> Sorry I forgot I‘m using C#
> (Assuming: "create table T(E text);" for the following.)
>
> If you're building the text of your insert statement before executing it, you 
> will have either to build the statement string as:
>
> insert into T values('na''m...@domain.tld');
> or
> insert into T values("na'm...@domain.ltd");
>
> Both of which you can test with the command-line sqlite3.exe.
>
> But it would be far more appropriate to use prepare:
>
> insert into T values(?);
>
> and then bind the parameter before executing. You won't have to alter your 
> data for inserting and it will be much better for SQL code injection 
> protection, depending where the email address comes from.
>
> I'm sure there is plenty of documentation with your language binding for 
> SQLite on how to prepare, bind, execute. Instead of building a final 
> statement as a complete string and then execute it.
>
And for a simple example of why you want to prepare (or be very careful
how you sanitize your data) see https://xkcd.com/327/ (Bobby Tables).
You should NEVER just build an SQL statement (or any other sort of
executable statement) by just inserting data from some source into a SQL
string. Using prepare is the best, at a minimum you need to run it
through a quoting function appropriate for that language.

-- 
Richard Damon

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

Re: [sqlite] Insert with an '

[Peter Nacken]

Dear all,

Thanks for your help. It works !

Peter

> Am 11.06.2018 um 11:14 schrieb Tim Streater :
> 
>> On 11 Jun 2018, at 09:07, Peter Nacken  wrote:
>> 
>> I try to insert email addresses into a table and get an error with addresses
>> they have a single quotation mark ( na'm...@domain.ltd ). 
>> 
>> Sorry I'm facing this problem for weeks, I can't find a solution.
>> 
>> Is there a known workaround for it ?
> 
> See:
> 
> 
> 
> and look at question 14 and its answer.
> 
> 
> 
> -- 
> Cheers  --  Tim
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Insert with an '

[Tim Streater]

On 11 Jun 2018, at 09:07, Peter Nacken  wrote:

> I try to insert email addresses into a table and get an error with addresses
> they have a single quotation mark ( na'm...@domain.ltd ). 
>
> Sorry I'm facing this problem for weeks, I can't find a solution.
>
> Is there a known workaround for it ?

See:



and look at question 14 and its answer.



-- 
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert with an '

[Olivier Mascia]

> Le 11 juin 2018 à 10:07, Peter Nacken  a écrit :
> 
> I try to insert email addresses into a table and get an error with addresses 
> they have a single quotation mark ( na'm...@domain.ltd ).

> Sorry I forgot I‘m using C#

(Assuming: "create table T(E text);" for the following.)

If you're building the text of your insert statement before executing it, you 
will have either to build the statement string as:

insert into T values('na''m...@domain.tld');
or
insert into T values("na'm...@domain.ltd");

Both of which you can test with the command-line sqlite3.exe.

But it would be far more appropriate to use prepare:

insert into T values(?);

and then bind the parameter before executing. You won't have to alter your data 
for inserting and it will be much better for SQL code injection protection, 
depending where the email address comes from.

I'm sure there is plenty of documentation with your language binding for SQLite 
on how to prepare, bind, execute. Instead of building a final statement as a 
complete string and then execute it.

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


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

Re: [sqlite] Insert with an '

[Simon Slavin]

On 11 Jun 2018, at 9:07am, Peter Nacken  wrote:

> I try to insert email addresses into a table and get an error with addresses 
> they have a single quotation mark ( na'm...@domain.ltd ). 
> 
> Sorry I'm facing this problem for weeks, I can't find a solution.

Every single ' character in a string needs to become two ' characters.  You can 
write a function which accepts one string and produces another with this change 
made.

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

Re: [sqlite] Insert with an '

[Peter Nacken]

Sorry I forgot I‘m using C#

> Am 11.06.2018 um 10:07 schrieb Peter Nacken :
> 
> Hi,
> 
> I'm new in SQLite. 
> 
> I try to insert email addresses into a table and get an error with addresses 
> they have a single quotation mark ( na'm...@domain.ltd ). 
> 
> Sorry I'm facing this problem for weeks, I can't find a solution.
> 
> Is there a known workaround for it ?
> 
> Thanks for help
> 
> Peter
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] Insert with an '

[Peter Nacken]

Hi,

I'm new in SQLite. 

I try to insert email addresses into a table and get an error with addresses 
they have a single quotation mark ( na'm...@domain.ltd ). 

Sorry I'm facing this problem for weeks, I can't find a solution.

Is there a known workaround for it ?

Thanks for help

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

Re: [sqlite] Insert with foreign keys enabled prevented in transaction

[Clemens Ladisch]

J Decker wrote:
> foreign key mismatch - "option4_map" referencing "option4_name"
>
> create table `option4_name` (`name_id` char(36) NOT NULL,
> ...
> create table `option4_map` (...
> FOREIGN KEY  (`name_id`) REFERENCES `option4_name`(`name_id`)

name_id in the parent table must be the primary key (or at least be unique).


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Insert with foreign keys enabled prevented in transaction

[J Decker]

 I was trying to debug this set of commands, which is actually run on
two
connections within the same process... but that detail apparently
doesn't matter.



PRAGMA foreign_keys=on;

create table `option4_name` (`name_id` char(36) NOT NULL,`name`
varchar(255) NOT NULL default '' CONSTRAINT `name` UNIQUE);

create table `option4_map` (`option_id` char(36) NOT
NULL,`parent_option_id` char(36) NOT NULL default '0',`name_id` char(36)
NOT NULL default '0',`description` tinytext,CONSTRAINT `parent_key2`
UNIQUE
(`parent_option_id`,`name_id`), FOREIGN KEY  (`parent_option_id`)
REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE
CASCADE,
FOREIGN KEY  (`name_id`) REFERENCES `option4_name`(`name_id`)ON UPDATE
CASCADE ON DELETE CASCADE);

create table `option4_values` (`option_id` char(36) default '0',`string`
varchar(100) default NULL,`segment` int(11) default 0,CONSTRAINT
`value_id`
UNIQUE (`option_id`,`segment`)ON CONFLICT IGNORE, FOREIGN KEY
(`option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON
DELETE CASCADE);select tbl_name,sql from sqlite_master where
type='table'
and

BEGIN TRANSACTION;

insert into option4_name (name_id,name) values(
'82093fa2-3d93-11e8-98aa-6e01a5d0577f','.' );

insert into option4_map

(option_id,parent_option_id,name_id)values('----','----','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
);

--SQLITE ERROR:Result of prepare failed? foreign key mismatch -
"option4_map" referencing "option4_name" at char 185[] in [insert into
option4_map

(option_id,parent_option_id,name_id)values('----','----','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
)]
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT OR REPLACE statement and triggers

[David Raymond]

Result of a test with recursive off then on.

"before insert" fired on "insert or ignore"

With recursive triggers on, the before and after delete triggers fired between 
the before and after insert triggers.


SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table t (id integer primary key, foo text);

sqlite> create table trigActions (trig text, oldID int, newID int, oldFoo text, 
newFoo text);

sqlite> create trigger bi before insert on t begin insert into trigActions 
values ('Before Insert', null, new.id, null, new.foo); end;

sqlite> create trigger ai after insert on t begin insert into trigActions 
values ('After Insert', null, new.id, null, new.foo); end;

sqlite> create trigger bu before update on t begin insert into trigActions 
values ('Before Update', old.id, new.id, old.foo, new.foo); end;

sqlite> create trigger au after update on t begin insert into trigActions 
values ('After Update', old.id, new.id, old.foo, new.foo); end;

sqlite> create trigger bd before delete on t begin insert into trigActions 
values ('Before Delete', old.id, null, old.foo, null); end;

sqlite> create trigger ad after delete on t begin insert into trigActions 
values ('After Delete', old.id, null, old.foo, null); end;

sqlite> insert into t values (1, 'one');

sqlite> insert into t values (2, 'two');

sqlite> insert into t values (3, 'three');

sqlite> insert into t values (1, 'new one');
Error: UNIQUE constraint failed: t.id

sqlite> insert or replace into t values (2, 'new two');

sqlite> insert or ignore into t values (3, 'new three');

sqlite> select * from trigActions order by rowid;
trig|oldID|newID|oldFoo|newFoo
Before Insert||1||one
After Insert||1||one
Before Insert||2||two
After Insert||2||two
Before Insert||3||three
After Insert||3||three
Before Insert||2||new two
After Insert||2||new two
Before Insert||3||new three

sqlite> pragma recursive_triggers;
recursive_triggers
0

sqlite> pragma recursive_triggers = true;

sqlite> delete from t;

sqlite> delete from trigActions;

sqlite> insert into t values (1, 'one');

sqlite> insert into t values (2, 'two');

sqlite> insert into t values (3, 'three');

sqlite> insert into t values (1, 'new one');
Error: UNIQUE constraint failed: t.id

sqlite> insert or replace into t values (2, 'new two');

sqlite> insert or ignore into t values (3, 'new three');

sqlite> select * from trigActions order by rowid;
trig|oldID|newID|oldFoo|newFoo
Before Insert||1||one
After Insert||1||one
Before Insert||2||two
After Insert||2||two
Before Insert||3||three
After Insert||3||three
Before Insert||2||new two
Before Delete|2||two|
After Delete|2||two|
After Insert||2||new two
Before Insert||3||new three

sqlite>


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of skywind mailing lists
Sent: Saturday, December 23, 2017 9:10 AM
To: SQLite mailing list
Subject: Re: [sqlite] INSERT OR REPLACE statement and triggers

Hello,

thanks for the documentation references!

Regards,
Hartwig

> Am 2017-12-23 um 13:18 schrieb J. King <jk...@jkingweb.ca>:
> 
> The documentation states that in cases of replacement the DELETE trigger only 
> fires if recursive triggers are enabled. Recursive triggers are currently off 
> by default. 
> 
> <https://sqlite.org/lang_conflict.html>
> <https://sqlite.org/pragma.html#pragma_recursive_triggers>
> 
> On December 23, 2017 7:05:59 AM EST, R Smith <ryansmit...@gmail.com> wrote:
>> 
>> 
>> On 2017/12/23 1:57 PM, skywind mailing lists wrote:
>>> Hello,
>>> 
>>> I have checked the documentation but did not find an explicit answer
>> concerning my case. Of course I can test it but this does not mean that
>> the functionality is guaranteed also for future versions. Assume I have
>> an insertion statement like
>>> 
>>> INSERT OR REPLACE INTO TestTable VALUES(1,2);
>>> 
>>> Furthermore, I have an INSERT, UPDATE and DELETE Trigger for the
>> table. Which triggers are triggered in case of
>>> 
>>> a) insertion (pretty obvious, should always be the INSERT trigger)
>>> b) replace (DELETE and INSERT trigger or UPDATE trigger or depends
>> on... .)
>> 
>> INSERT OR REPLACE amounts to either just "INSERT" or "DELETE, then 
>> INSERT" based on whether or not the Constraint/Key is violated, so you 
>> are guaranteed to have the INSERT trigger fire, but if the item did 
>> exist before, the DELETE trigger will fire first.
>> 
>> HTH,
>> Ryan
>> 
>> _

Re: [sqlite] INSERT OR REPLACE statement and triggers

[skywind mailing lists]

Hello,

thanks for the documentation references!

Regards,
Hartwig

> Am 2017-12-23 um 13:18 schrieb J. King :
> 
> The documentation states that in cases of replacement the DELETE trigger only 
> fires if recursive triggers are enabled. Recursive triggers are currently off 
> by default. 
> 
> 
> 
> 
> On December 23, 2017 7:05:59 AM EST, R Smith  wrote:
>> 
>> 
>> On 2017/12/23 1:57 PM, skywind mailing lists wrote:
>>> Hello,
>>> 
>>> I have checked the documentation but did not find an explicit answer
>> concerning my case. Of course I can test it but this does not mean that
>> the functionality is guaranteed also for future versions. Assume I have
>> an insertion statement like
>>> 
>>> INSERT OR REPLACE INTO TestTable VALUES(1,2);
>>> 
>>> Furthermore, I have an INSERT, UPDATE and DELETE Trigger for the
>> table. Which triggers are triggered in case of
>>> 
>>> a) insertion (pretty obvious, should always be the INSERT trigger)
>>> b) replace (DELETE and INSERT trigger or UPDATE trigger or depends
>> on... .)
>> 
>> INSERT OR REPLACE amounts to either just "INSERT" or "DELETE, then 
>> INSERT" based on whether or not the Constraint/Key is violated, so you 
>> are guaranteed to have the INSERT trigger fire, but if the item did 
>> exist before, the DELETE trigger will fire first.
>> 
>> HTH,
>> Ryan
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> -- 
> Sent from my Android device with K-9 Mail. Please excuse my brevity.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 

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

Re: [sqlite] INSERT OR REPLACE statement and triggers

[J. King]

The documentation states that in cases of replacement the DELETE trigger only 
fires if recursive triggers are enabled. Recursive triggers are currently off 
by default. 




On December 23, 2017 7:05:59 AM EST, R Smith  wrote:
>
>
>On 2017/12/23 1:57 PM, skywind mailing lists wrote:
>> Hello,
>>
>> I have checked the documentation but did not find an explicit answer
>concerning my case. Of course I can test it but this does not mean that
>the functionality is guaranteed also for future versions. Assume I have
>an insertion statement like
>>
>> INSERT OR REPLACE INTO TestTable VALUES(1,2);
>>
>> Furthermore, I have an INSERT, UPDATE and DELETE Trigger for the
>table. Which triggers are triggered in case of
>>
>> a) insertion (pretty obvious, should always be the INSERT trigger)
>> b) replace (DELETE and INSERT trigger or UPDATE trigger or depends
>on... .)
>
>INSERT OR REPLACE amounts to either just "INSERT" or "DELETE, then 
>INSERT" based on whether or not the Constraint/Key is violated, so you 
>are guaranteed to have the INSERT trigger fire, but if the item did 
>exist before, the DELETE trigger will fire first.
>
>HTH,
>Ryan
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT OR REPLACE statement and triggers

[R Smith]

On 2017/12/23 1:57 PM, skywind mailing lists wrote:

Hello,

I have checked the documentation but did not find an explicit answer concerning 
my case. Of course I can test it but this does not mean that the functionality 
is guaranteed also for future versions. Assume I have an insertion statement 
like

INSERT OR REPLACE INTO TestTable VALUES(1,2);

Furthermore, I have an INSERT, UPDATE and DELETE Trigger for the table. Which 
triggers are triggered in case of

a) insertion (pretty obvious, should always be the INSERT trigger)
b) replace (DELETE and INSERT trigger or UPDATE trigger or depends on... .)


INSERT OR REPLACE amounts to either just "INSERT" or "DELETE, then 
INSERT" based on whether or not the Constraint/Key is violated, so you 
are guaranteed to have the INSERT trigger fire, but if the item did 
exist before, the DELETE trigger will fire first.


HTH,
Ryan

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

[sqlite] INSERT OR REPLACE statement and triggers

[skywind mailing lists]

Hello,

I have checked the documentation but did not find an explicit answer concerning 
my case. Of course I can test it but this does not mean that the functionality 
is guaranteed also for future versions. Assume I have an insertion statement 
like

INSERT OR REPLACE INTO TestTable VALUES(1,2);

Furthermore, I have an INSERT, UPDATE and DELETE Trigger for the table. Which 
triggers are triggered in case of

a) insertion (pretty obvious, should always be the INSERT trigger)
b) replace (DELETE and INSERT trigger or UPDATE trigger or depends on... .)

Regards,
Hartwig

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

Re: [sqlite] INSERT OR IGNORE ignores constraints. Bug ?

[Keith Medcalf]

Simon,

Policy is being enforced.  You specifically declared in the table definition 
that rows must have (a > 10) in order to be "in the table".  The IGNORE as in 
INSERT OR IGNORE means exactly and precisely what it says:  INSERT the record 
if it is valid and IGNORE it oherwise.  You tried to insert a "bad" row and 
specified to ignore the error causing rows so that operation was ignored and 
the record was not inserted.

This is working as designed and as documented.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
>Sent: Thursday, 21 December, 2017 10:50
>To: SQLite mailing list
>Subject: [sqlite] INSERT OR IGNORE ignores constraints. Bug ?
>
>
>
>On 21 Dec 2017, at 3:46pm, David Raymond <david.raym...@tomtom.com>
>wrote:
>
>> The only potential problem with "insert or ignore into" is that it
>will ignore any constraint violation for that record insert
>
>Wait.  What ?
>
>SQLite version 3.19.3 2017-06-27 16:48:08
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> CREATE TABLE MyTable (a INTEGER, CONSTRAINT noless CHECK (a >
>10));
>sqlite> INSERT INTO MyTable VALUES (15);
>sqlite> INSERT INTO MyTable VALUES (5);
>Error: CHECK constraint failed: noless
>sqlite> INSERT OR IGNORE INTO MyTable VALUES (6);
>sqlite> SELECT * FROM MyTable;
>15
>sqlite>
>
>What the hell ?  Why does that work ?  Isn’t it a huge bug ?  How did
>you discover it ?
>
>My understanding is that using INSERT OR IGNORE meant that bad
>inserts would fail, but they would do so silently, without triggering
>an error result.
>
><https://sqlite.org/lang_conflict.html>
>
>"When an applicable constraint violation occurs, the IGNORE
>resolution algorithm skips the one row that contains the constraint
>violation and continues processing subsequent rows of the SQL
>statement as if nothing went wrong. Other rows before and after the
>row that contained the constraint violation are inserted or updated
>normally."
>
>If I understand correctly, "the IGNORE resolution algorithm skips the
>one row that contains the constraint violation and continues
>processing subsequent rows of the SQL statement as if nothing went
>wrong" means that a row that violates constraints will not be
>inserted.
>
>I thought I could enforce policy by setting constraints.  Apparently
>not.
>
>Simon.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] INSERT OR IGNORE ignores constraints. Bug ?

[Simon Slavin]

On 21 Dec 2017, at 5:58pm, Igor Tandetnik  wrote:

> Isn't that precisely what happened in your example? Inserting 6 failed 
> silently. What again seems to be the problem?

I’m sorry.  You and Scott are quite right.  I have no idea what I was thinking.

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

Re: [sqlite] INSERT OR IGNORE ignores constraints. Bug ?

[Scott Robison]

On Dec 21, 2017 10:50 AM, "Simon Slavin" <slav...@bigfraud.org> wrote:



On 21 Dec 2017, at 3:46pm, David Raymond <david.raym...@tomtom.com> wrote:

> The only potential problem with "insert or ignore into" is that it will
ignore any constraint violation for that record insert

Wait.  What ?

SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE MyTable (a INTEGER, CONSTRAINT noless CHECK (a > 10));
sqlite> INSERT INTO MyTable VALUES (15);
sqlite> INSERT INTO MyTable VALUES (5);
Error: CHECK constraint failed: noless
sqlite> INSERT OR IGNORE INTO MyTable VALUES (6);
sqlite> SELECT * FROM MyTable;
15
sqlite>

What the hell ?  Why does that work ?  Isn’t it a huge bug ?  How did you
discover it ?

My understanding is that using INSERT OR IGNORE meant that bad inserts
would fail, but they would do so silently, without triggering an error
result.


Insert 15 succeeded, 5 failed with error, 6 failed without error. Seems
exactly as advertised. What am I missing?

Note that or ignore is statement specific, not transaction specific, if I
understand correctly.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT OR IGNORE ignores constraints. Bug ?

[Igor Tandetnik]

On 12/21/2017 12:50 PM, Simon Slavin wrote:

SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE MyTable (a INTEGER, CONSTRAINT noless CHECK (a > 10));
sqlite> INSERT INTO MyTable VALUES (15);
sqlite> INSERT INTO MyTable VALUES (5);
Error: CHECK constraint failed: noless
sqlite> INSERT OR IGNORE INTO MyTable VALUES (6);
sqlite> SELECT * FROM MyTable;
15
sqlite>

What the hell ?  Why does that work ?  Isn’t it a huge bug ?  How did you 
discover it ?

My understanding is that using INSERT OR IGNORE meant that bad inserts would 
fail, but they would do so silently, without triggering an error result.


Isn't that precisely what happened in your example? Inserting 6 failed 
silently. What again seems to be the problem?


If I understand correctly, "the IGNORE resolution algorithm skips the one row that 
contains the constraint violation and continues processing subsequent rows of the SQL 
statement as if nothing went wrong" means that a row that violates constraints will 
not be inserted.


And in your example, it was not.
--
Igor Tandetnik

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

[sqlite] INSERT OR IGNORE ignores constraints. Bug ?

[Simon Slavin]

On 21 Dec 2017, at 3:46pm, David Raymond <david.raym...@tomtom.com> wrote:

> The only potential problem with "insert or ignore into" is that it will 
> ignore any constraint violation for that record insert

Wait.  What ?

SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE MyTable (a INTEGER, CONSTRAINT noless CHECK (a > 10));
sqlite> INSERT INTO MyTable VALUES (15);
sqlite> INSERT INTO MyTable VALUES (5);
Error: CHECK constraint failed: noless
sqlite> INSERT OR IGNORE INTO MyTable VALUES (6);
sqlite> SELECT * FROM MyTable;
15
sqlite> 

What the hell ?  Why does that work ?  Isn’t it a huge bug ?  How did you 
discover it ?

My understanding is that using INSERT OR IGNORE meant that bad inserts would 
fail, but they would do so silently, without triggering an error result.

<https://sqlite.org/lang_conflict.html>

"When an applicable constraint violation occurs, the IGNORE resolution 
algorithm skips the one row that contains the constraint violation and 
continues processing subsequent rows of the SQL statement as if nothing went 
wrong. Other rows before and after the row that contained the constraint 
violation are inserted or updated normally."

If I understand correctly, "the IGNORE resolution algorithm skips the one row 
that contains the constraint violation and continues processing subsequent rows 
of the SQL statement as if nothing went wrong" means that a row that violates 
constraints will not be inserted.

I thought I could enforce policy by setting constraints.  Apparently not.

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

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Robert M. Münch]

On 29 Jun 2017, at 19:06, Jens Alfke wrote:

>> On Jun 29, 2017, at 12:13 AM, Hick Gunter  wrote:
>>
>> Double quotes is specifically for building identifiers that "look strange" 
>> (i.e. embedded spaces, keywords, ...) which IMHO should be avoided because 
>> it tends to clutter up the statement.
>
> I agree that if you’re generating the schema by hand you should avoid 
> creating names that require quoting.
>
> However, if tables/columns/indexes are being generated dynamically, it can be 
> very convenient to name them based on the external item that uses them, and 
> that name might involve “strange” characters.

Yes, I wrote a big function that strips all non-conforming chars, words etc. 
from column names (since we use '...' and I got some errors) because we import 
CSV user data into a table where the columns are the CSV columns.

I might x-check to see if I can switch to "..." and avoid all the hassle at all.


> In my case, I create indexes on the fly based on JSON paths like 
> “address[0].zipcode”, so I use the path as part of the index name. This is 
> simpler than escaping the punctuation, using a digest of the path, or making 
> up an identifier that then has to be stored somewhere else.

Good point. We need to do the same in the future.

> It also makes the schema a lot easier to understand when looking at generated 
> statements or poking around in the sqlite3 tool.

True too.

-- 

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Jens Alfke]

> On Jun 29, 2017, at 12:13 AM, Hick Gunter  wrote:
> 
> Double quotes is specifically for building identifiers that "look strange" 
> (i.e. embedded spaces, keywords, ...) which IMHO should be avoided because it 
> tends to clutter up the statement.

I agree that if you’re generating the schema by hand you should avoid creating 
names that require quoting.

However, if tables/columns/indexes are being generated dynamically, it can be 
very convenient to name them based on the external item that uses them, and 
that name might involve “strange” characters.

In my case, I create indexes on the fly based on JSON paths like 
“address[0].zipcode”, so I use the path as part of the index name. This is 
simpler than escaping the punctuation, using a digest of the path, or making up 
an identifier that then has to be stored somewhere else. It also makes the 
schema a lot easier to understand when looking at generated statements or 
poking around in the sqlite3 tool.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Peter da Silva]

On 6/29/17, 5:20 AM, "sqlite-users on behalf of R Smith" 
 
wrote:
> SQLite isn't helping the confusion in this case, because it allows 
> double-quotes to be regarded as string values IF an identifier with that name 
> doesn't exist. This is of course all good and well until you misspell a 
> column name...

Shades of REXX.

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

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Peter da Silva]

On 6/29/17, 1:22 AM, "sqlite-users on behalf of Robert M. Münch" 
 wrote:
> Hi, sorry, should have mentioned that this doesn't work in my case, because 
> we are building the column placeholders dynamically. So, we would have to 
> handle putting the necessary column names in there all the time, which is not 
> feasible.

I have been generating SQL dynamically on a number of projects over the past 
10+ years, and have found that generating INSERT with column names in is (a) 
not really that much extra work, and (b) eliminates a whole class of bugs 
involving schema changes or even schema regeneration. It’s genuinely worth 
taking the time to do it right.

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

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[R Smith]

On 2017/06/29 8:15 AM, Robert M. Münch wrote:

On 27 Jun 2017, at 22:11, David Raymond wrote:


Single quotes should be used for strings, so DEFAULT '-'

I thought it doesn't matter if I use " or ' for strings. What's the difference?


I had this misconception at some point too.  Double quotes are for 
specifying Identifiers (The names of stuff) in SQLite so as to not 
confuse a possible column name (aka identifier) with say an internal 
keyword or a string value.


SQLite isn't helping the confusion in this case, because it allows 
double-quotes to be regarded as string values IF an identifier with that 
name doesn't exist. This is of course all good and well until you 
misspell a column name...


To demonstrate the difference and possible pitfalls:

  -- SQLite version 3.17.0  [ Release: 2017-02-13 ]  on SQLitespeed 
version 2.0.2.4.
  -- 



CREATE TABLE QTest(
  "ID" INT,
  Value1 TEXT,
  Value2 TEXT
);

INSERT INTO QTest(ID, Value1) VALUES
 (1, 'Gorilla Conflict')
,(2, 'Moroccan Coffee Beans')
,(3, "Monaco Raceway")
;
-- All these were regarded as strings.

UPDATE QTest SET Value2 = 'Value1' || ' in the Jungle.';

SELECT ID, Value2 FROM QTest;

  --  ID  | Value2
  --  | -
  --   1  | Value1 in the Jungle.
  --   2  | Value1 in the Jungle.
  --   3  | Value1 in the Jungle.

-- Here we expected that outcome because using single quotes means
-- that can be nothing other than the string 'Value1'


UPDATE QTest SET Value2 = "Value1" || " in the Jungle.";

SELECT ID, Value2 FROM QTest;

  --  ID  | Value2
  --  | 
  --   1  | Gorilla Conflict in the Jungle.
  --   2  | Moroccan Coffee Beans in the Jungle.
  --   3  | Monaco Raceway in the Jungle.

-- Here "value1" is correctly regarded as an identifier but
-- " in the Jungle" is regarded as a string, even though it's
-- in double-quotes. This time the weirdness helped us


UPDATE QTest SET Value2 = "Valeu1" || " in the Jungle.";

SELECT ID, Value2 FROM QTest;

  --  ID  | Value2
  --  | -
  --   1  | Valeu1 in the Jungle.
  --   2  | Valeu1 in the Jungle.
  --   3  | Valeu1 in the Jungle.

-- Here our troubles start. It's exactly the same format as before,
-- but because of the spelling mistake, our needed-to-be-an-identifier
-- value1 simply got transformed to a string, no error.
-- (In fact, this entire script runs without errors, you can copy-paste
-- it into your own DB script mechanism)


UPDATE QTest SET Value2 = Value1 || ' in the Jungle.';

SELECT ID, Value2 FROM QTest;

  --  ID  | Value2
  --  | 
  --   1  | Gorilla Conflict in the Jungle.
  --   2  | Moroccan Coffee Beans in the Jungle.
  --   3  | Monaco Raceway in the Jungle.

-- This is the most correct way to do it. The same spelling mistake
-- here would error out.

-- The only time it is needed (or really a good idea) to use double
-- quotes, is when an identifier name either contains weird
-- characters or the identifier is the same as an internal Keyword.
-- These are not valid identifiers:  *1 A~;  ,  JOIN
-- But these definitely are valid:  "*1 A~;" , "JOIN"
-- In SQLite, even this is valid: CREATE TABLE " "(" " INT);
-- Yes - that is a table with the name SPACE and a column named SPACE.



DROP TABLE QTest;-- Cleanup

  --   Script Stats: Total Script Execution Time: 0d 00h 00m and 
00.030s
  -- Total Script Query Time: 0d 00h 00m and 
00.001s

  -- Total Database Rows Changed: 15
  -- Total Virtual-Machine Steps: 289
  -- Last executed Item Index:11
  -- Last Script Error:
  -- 




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

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Hick Gunter]

Double quotes is specifically for building identifiers that "look strange" 
(i.e. embedded spaces, keywords, ...) which IMHO should be avoided because it 
tends to clutter up the statement.

Single quotes is for building strings.

Integer is a keyword, "integer" is an identifier and 'integer' a string.

asql> create temp table test (id integer primary key, "integer" integer default 
'integer');
asql> .desc test
+-++++
|   Name  |Datatype|Size| Hidden |
+-++++
| id  | integer|UNKNOWN ||
| integer | integer|UNKNOWN ||
+-++++
Field count: 2
asql> insert into test (id) values (1);
rows inserted
-
1
asql> select * from test;
id  integer
--  --
1   integer

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Robert M. Münch
Gesendet: Donnerstag, 29. Juni 2017 08:16
An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Betreff: Re: [sqlite] INSERT ... VALUES / want to "skip" default values

On 27 Jun 2017, at 22:11, David Raymond wrote:

> Single quotes should be used for strings, so DEFAULT '-'

I thought it doesn't matter if I use " or ' for strings. What's the difference?


> So there is no method to do something like...
>
> INSERT INTO test VALUES ('field a', DEFAULT, 'field c', 'field d');

That's what I want to do.

> PS: Simon: Specifying NULL will just put a NULL value in there, it won't use 
> the default.

I tried NULL and as you said, that doesn't work because NULL is put in.

--

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Clemens Ladisch]

Robert M. Münch wrote:
> Is this behaviour standard or a SQLite variant?

Autoincrementing is an SQLite variant.
Default values are standard SQL.

It should be noted that standard SQL (above Entry SQL level) allows
DEFAULT in row value constructors.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Robert M. Münch]

On 28 Jun 2017, at 14:51, Simon Slavin wrote:

> Really ?  In that case I withdraw my previous answer.  I thought that NULLs 
> were converted to the default value for a column (which is usually NULL but 
> can be overridden with a DEFAULT clause).

I had exactly the same understanding. BTW: Is this behaviour standard or a 
SQLite variant?

-- 

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Robert M. Münch]

On 28 Jun 2017, at 9:49, Maks Verver wrote:

> I'm surprised nobody mentioned that you can specify the columns to be
> inserted in the query:
>
>   INSERT INTO test(a, c, d) VALUES (1, 2 3);
>
> (Note that `b` is missing it `a, c, d`. It will take the default value,
> which will be NULL, unless a different default was specified explicitly in
> the CREATE TABLE statement.)

Hi, sorry, should have mentioned that this doesn't work in my case, because we 
are building the column placeholders dynamically. So, we would have to handle 
putting the necessary column names in there all the time, which is not feasible.

-- 

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Robert M. Münch]

On 27 Jun 2017, at 22:24, David Raymond wrote:

> If you have to provide 4 values then the way you can use null to do that is 
> to add in a trigger to set the default, since NULL _is_ a value and _is_ 
> legal for that field.

Ha, that's a very good idea. I didn't have triggers in the radar. Great, I 
think that's solving my problem. Thanks a lot!

-- 

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Robert M. Münch]

On 27 Jun 2017, at 22:11, David Raymond wrote:

> Single quotes should be used for strings, so DEFAULT '-'

I thought it doesn't matter if I use " or ' for strings. What's the difference?


> So there is no method to do something like...
>
> INSERT INTO test VALUES ('field a', DEFAULT, 'field c', 'field d');

That's what I want to do.

> PS: Simon: Specifying NULL will just put a NULL value in there, it won't use 
> the default.

I tried NULL and as you said, that doesn't work because NULL is put in.

-- 

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Bond, Liz]

Dear all,

Please can any e-mail address that ends "@sentec.co.uk" be removed from the 
mailing list as I am receiving a number of e-mails for ex-employees but there 
is no way to unsubscribe on the e-mails.

Many thanks

With kind regards

Liz

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Maks Verver
Sent: 28 June 2017 08:50
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] INSERT ... VALUES / want to "skip" default values

I'm surprised nobody mentioned that you can specify the columns to be inserted 
in the query:

  INSERT INTO test(a, c, d) VALUES (1, 2 3);

(Note that `b` is missing it `a, c, d`. It will take the default value, which 
will be NULL, unless a different default was specified explicitly in the CREATE 
TABLE statement.)

It's usually preferable to specify column names in an INSERT query explicitly, 
because it makes it easier to see what the values are supposed to mean. It 
prevents mistakes like swapping the meaning of two adjacent columns, or 
inserting a phone number in an email field, and things like that.

On Tue, Jun 27, 2017 at 10:24 PM, David Raymond <david.raym...@tomtom.com>
wrote:

> If you have to provide 4 values then the way you can use null to do 
> that is to add in a trigger to set the default, since NULL _is_ a 
> value and _is_ legal for that field.
>
> CREATE TRIGGER test_populate_b
>   AFTER INSERT ON test
>   WHEN new.b is null
>   BEGIN
> UPDATE test
> SET b = '-'
> WHERE rowid = new.rowid;
>   END;
>
> INSERT INTO test VALUES ('field a', NULL, 'field c', 'field d');
>
> a   b   c   d
> --  --  --  --
> field a -   field c field d
>
> -Original Message-
> From: sqlite-users 
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Simon Slavin
> Sent: Tuesday, June 27, 2017 4:08 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] INSERT ... VALUES / want to "skip" default 
> values
>
>
>
> On 27 Jun 2017, at 8:13pm, Robert M. Münch 
> <robert.mue...@saphirion.com>
> wrote:
>
> > CREATE TABLE test(a, b DEFAULT "-", c, d)
> >
> > Now I would like to use
> >
> > INSERT VALUES(a,?,c,d)
> >
> > Where ? is something that the default value is used and not the 
> > provided
> value. Is this possible at all?
>
> You provide the text "NULL" (not in any quotes) for that value:
>
> INSERT INTO test VALUES(12, NULL, 84, 'endomorph')
>
> If you’ve set up a statement with parameters …
>
> INSERT INTO test VALUES(?1, ?2, ?3, ?4)
>
> … you can leave that paramater unbound (all parameters are bound to 
> NULL by default) or you can explicitly bind it to NULL using 
> sqlite3_bind_null() .
>
> Do not confuse NULL, which is the NULL value, with 'NULL' in those 
> quotes, which is a four character string.
>
> Simon.
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Liz Bond, Finance Controller

Sentec Ltd  phone:  +44(0) 1223 303800
5 The Westbrook Centre  fax:+44(0) 1223 303801
Milton Road  
Cambridge   email:  eb...@sentec.co.uk
CB4 1YG, UK web:www.sentec.co.uk

This email is confidential. If you have received it in error, please notify 
Sentec Ltd UK at postmas...@sentec.co.uk immediately,
delete it from your system and note that you may not copy, distribute or use 
its contents.

Sentec Limited is registered at the above address UK Company Number 3452194.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Petite Abeille]

> On Jun 28, 2017, at 4:15 PM, R Smith  wrote:
> 
> I did ponder whether it would be a nice "feature" to use the default if both 
> a DEFAULT and a NOT NULL constraint existed on a column - but then again, 
> that will go against strict design principles and can cause a lot of 
> confusion later.

Some databases, which we shall not name, provide both options [1]:

- DEFAULT for columns without an explicit value
- DEFAULT ON NULL for columns with an explicitly null

[1] 
https://oracle-base.com/articles/12c/default-values-for-table-columns-enhancements-12cr1#nulls

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

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Scott Robison]

On Jun 28, 2017 6:51 AM, "Simon Slavin"  wrote:



On 28 Jun 2017, at 9:45am, Clemens Ladisch  wrote:

> An explicit NULL works only for the autoincrement column, but not for
default values.

Really ?  In that case I withdraw my previous answer.  I thought that NULLs
were converted to the default value for a column (which is usually NULL but
can be overridden with a DEFAULT clause).  Thanks for the correction.


Depending on needs, one can make a column not null with on conflict
replace, at which point trying to insert or update with null will use the
default, but that is only available if your column can never be null.


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

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[R Smith]

On 2017/06/28 2:51 PM, Simon Slavin wrote:



An explicit NULL works only for the autoincrement column, but not for default 
values.

Really ?  In that case I withdraw my previous answer.  I thought that NULLs 
were converted to the default value for a column (which is usually NULL but can 
be overridden with a DEFAULT clause).  Thanks for the correction.


if this was the case, how would you insert values which you WANT to be 
NULL into a DB?


The only time a NULL gets converted is for a Primary Key Auto-increment 
column, because those can ever be NULL (except of course in SQLite's 
case, but the exception survived for other legacy reasons).


I did ponder whether it would be a nice "feature" to use the default if 
both a DEFAULT and a NOT NULL constraint existed on a column - but then 
again, that will go against strict design principles and can cause a lot 
of confusion later.


Omitting a column from the Insert prototype or specifying DEFAULTS for 
it will do the trick in SQLite - I'm not entirely sure if this holds 
true for all other SQL DB systems, but I suppose strictly it should.


Cheers,
Ryan

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

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Simon Slavin]

On 28 Jun 2017, at 9:45am, Clemens Ladisch  wrote:

> An explicit NULL works only for the autoincrement column, but not for default 
> values.

Really ?  In that case I withdraw my previous answer.  I thought that NULLs 
were converted to the default value for a column (which is usually NULL but can 
be overridden with a DEFAULT clause).  Thanks for the correction.

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

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Clemens Ladisch]

Simon Slavin wrote:
> On 27 Jun 2017, at 8:13pm, Robert M. Münch  
> wrote:
>> CREATE TABLE test(a, b DEFAULT "-", c, d)
>>
>> Now I would like to use
>>
>> INSERT VALUES(a,?,c,d)
>>
>> Where ? is something that the default value is used and not the provided 
>> value. Is this possible at all?
>
> INSERT INTO test VALUES(12, NULL, 84, 'endomorph')

An explicit NULL works only for the autoincrement column, but not for default 
values.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Maks Verver]

I'm surprised nobody mentioned that you can specify the columns to be
inserted in the query:

  INSERT INTO test(a, c, d) VALUES (1, 2 3);

(Note that `b` is missing it `a, c, d`. It will take the default value,
which will be NULL, unless a different default was specified explicitly in
the CREATE TABLE statement.)

It's usually preferable to specify column names in an INSERT query
explicitly, because it makes it easier to see what the values are supposed
to mean. It prevents mistakes like swapping the meaning of two adjacent
columns, or inserting a phone number in an email field, and things like
that.

On Tue, Jun 27, 2017 at 10:24 PM, David Raymond <david.raym...@tomtom.com>
wrote:

> If you have to provide 4 values then the way you can use null to do that
> is to add in a trigger to set the default, since NULL _is_ a value and _is_
> legal for that field.
>
> CREATE TRIGGER test_populate_b
>   AFTER INSERT ON test
>   WHEN new.b is null
>   BEGIN
> UPDATE test
> SET b = '-'
> WHERE rowid = new.rowid;
>   END;
>
> INSERT INTO test VALUES ('field a', NULL, 'field c', 'field d');
>
> a   b   c   d
> --  --  --  --
> field a -   field c field d
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Simon Slavin
> Sent: Tuesday, June 27, 2017 4:08 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] INSERT ... VALUES / want to "skip" default values
>
>
>
> On 27 Jun 2017, at 8:13pm, Robert M. Münch <robert.mue...@saphirion.com>
> wrote:
>
> > CREATE TABLE test(a, b DEFAULT "-", c, d)
> >
> > Now I would like to use
> >
> > INSERT VALUES(a,?,c,d)
> >
> > Where ? is something that the default value is used and not the provided
> value. Is this possible at all?
>
> You provide the text "NULL" (not in any quotes) for that value:
>
> INSERT INTO test VALUES(12, NULL, 84, 'endomorph')
>
> If you’ve set up a statement with parameters …
>
> INSERT INTO test VALUES(?1, ?2, ?3, ?4)
>
> … you can leave that paramater unbound (all parameters are bound to NULL
> by default) or you can explicitly bind it to NULL using sqlite3_bind_null()
> .
>
> Do not confuse NULL, which is the NULL value, with 'NULL' in those quotes,
> which is a four character string.
>
> Simon.
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[David Raymond]

If you have to provide 4 values then the way you can use null to do that is to 
add in a trigger to set the default, since NULL _is_ a value and _is_ legal for 
that field.

CREATE TRIGGER test_populate_b
  AFTER INSERT ON test
  WHEN new.b is null
  BEGIN
UPDATE test
SET b = '-'
WHERE rowid = new.rowid;
  END;

INSERT INTO test VALUES ('field a', NULL, 'field c', 'field d');

a   b   c   d
--  --  --  --
field a -   field c field d

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Tuesday, June 27, 2017 4:08 PM
To: SQLite mailing list
Subject: Re: [sqlite] INSERT ... VALUES / want to "skip" default values



On 27 Jun 2017, at 8:13pm, Robert M. Münch <robert.mue...@saphirion.com> wrote:

> CREATE TABLE test(a, b DEFAULT "-", c, d)
> 
> Now I would like to use
> 
> INSERT VALUES(a,?,c,d)
> 
> Where ? is something that the default value is used and not the provided 
> value. Is this possible at all?

You provide the text "NULL" (not in any quotes) for that value:

INSERT INTO test VALUES(12, NULL, 84, 'endomorph')

If you’ve set up a statement with parameters …

INSERT INTO test VALUES(?1, ?2, ?3, ?4)

… you can leave that paramater unbound (all parameters are bound to NULL by 
default) or you can explicitly bind it to NULL using sqlite3_bind_null() .

Do not confuse NULL, which is the NULL value, with 'NULL' in those quotes, 
which is a four character string.

Simon.

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

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[David Raymond]

Single quotes should be used for strings, so DEFAULT '-'

Not quite sure what you're asking. Do you mean how to insert defaults in 
general?

INSERT INTO test (a, c, d) VALUES ('field a', 'field c', 'field d');
will get you
a   b   c   d
--  --  --  --
field a -   field c field d

You need to explicitly state which fields you are providing values for, and any 
field you don't specify/provide will get the default value, either what you 
defined or null. When using VALUES or bindings there is no way to explicitly 
say "use the default for this field", you have to not provide anything and 
exclude the field from the insert.

So there is no method to do something like...

INSERT INTO test VALUES ('field a', DEFAULT, 'field c', 'field d');

There is also no way to give it 4 values and have it use only 3 of them.

Hopefully that answers what you were looking for.

PS: Simon: Specifying NULL will just put a NULL value in there, it won't use 
the default.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Robert M. Münch
Sent: Tuesday, June 27, 2017 3:13 PM
To: SQLite mailing list
Subject: [sqlite] INSERT ... VALUES / want to "skip" default values

Hi, I have a table like:

CREATE TABLE test(a, b DEFAULT "-", c, d)

Now I would like to use

INSERT VALUES(a,?,c,d)

Where ? is something that the default value is used and not the provided value. 
Is this possible at all?

Viele Grüsse.

-- 

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Simon Slavin]

On 27 Jun 2017, at 8:13pm, Robert M. Münch  wrote:

> CREATE TABLE test(a, b DEFAULT "-", c, d)
> 
> Now I would like to use
> 
> INSERT VALUES(a,?,c,d)
> 
> Where ? is something that the default value is used and not the provided 
> value. Is this possible at all?

You provide the text "NULL" (not in any quotes) for that value:

INSERT INTO test VALUES(12, NULL, 84, 'endomorph')

If you’ve set up a statement with parameters …

INSERT INTO test VALUES(?1, ?2, ?3, ?4)

… you can leave that paramater unbound (all parameters are bound to NULL by 
default) or you can explicitly bind it to NULL using sqlite3_bind_null() .

Do not confuse NULL, which is the NULL value, with 'NULL' in those quotes, 
which is a four character string.

Simon.

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

[sqlite] INSERT ... VALUES / want to "skip" default values

[Robert M. Münch]

Hi, I have a table like:

CREATE TABLE test(a, b DEFAULT "-", c, d)

Now I would like to use

INSERT VALUES(a,?,c,d)

Where ? is something that the default value is used and not the provided value. 
Is this possible at all?

Viele Grüsse.

-- 

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert into with Id...

[hfiandor]

Thanks very much, in special to Mr. Slavin.

Saludos
Ing. Héctor F. Fiandor Rosario

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

Re: [sqlite] Insert into with Id...

[hfiandor]

Dear Mr. Simon:

Today, early in the morning, I have reviewed the former ExcelToSQLite.exe and 
try to  run it  with data from my last application. I have corrected some 
mistakes and pretty work.

I thought my problems was solved.

Thanks very much,

Ing. Héctor F. Fiandor Rosario

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

Re: [sqlite] Insert into with Id...

[Kees Nuyt]

Dear Héctor, 

On Fri, 13 Jan 2017 21:38:04 -0500, "hfiandor"
 wrote:

> I have implemented the lectura of a csv file in my program as
> "import". I have followed yours instructions (the last one and
> others previous) . Thanks for your instructions.
> 
> The program works fine with csv files of one or two rows, but
> when I try to read a file with 9 rows, it fails.

In what way does it fail?
Does it fail silently or are there any error messages?
After the failure, are there any rows inserted, or none at all?
Does the program check and interpret the status of each sqlite
API call?
 
> I have reviewed the routines and not found the errors.
> 
> Please, I suspect that I have missed something. If you have
> any suggestion, I will appreciate very much.
> 
> If you need the procedure "import" I can send to you.

That depends on the answers to my questions above.

> Thanks in advance,
> 
> Ing. Héctor F. Fiandor Rosario

Hope this helps,

-- 
Regards,
Kees Nuyt

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

Re: [sqlite] Insert into with Id...

[hfiandor]

Dear Mr. Simon Slavin:

I have implemented the lectura of a csv file in my program as "import". I have 
followed yours instructions (the last one and others previous) . Thanks for 
your instructions.

The program works fine with csv files of one or two rows, but when I try to 
read a file with 9 rows, it fails.

I have reviewed the routines and not found the errors.

Please, I suspect that I have missed something. If you have any suggestion, I 
will appreciate very much.

If you need the procedure "import" I can send to you.

Thanks in advance,

Ing. Héctor F. Fiandor Rosario

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

Re: [sqlite] INSERT is corrupting a database

[Kevin O'Gorman]

On Fri, Jan 13, 2017 at 3:34 AM, Clemens Ladisch  wrote:

> Kevin O'Gorman wrote:
> > On Tue, Jan 10, 2017 at 11:29 PM, Clemens Ladisch 
> wrote:
> >> Kevin O'Gorman wrote:
> >>> If I go on to the second table, it appears to finish normally, but
> when I
> >>> try to look at the database with sqlite3, a command-line tool for
> >>> interacting with SQLite, it says the database is corrupt.
> >>
> >> What version?
> >
> > It's whatever is in Python 3.5.2.'s builtin sqlite package.
>
> The sqlite3 command-line shell does not ship with Python.
>
> >> It's possible that there is a bug in your code.  Which you have not
> shown.
> >
> > My opinion is that no user bug whatever should cause DB integrity
> problems without
> > raising an exception.
>
> 
> But it's unlikely that you'd manage to do any of this in Python.
>
> Anyway, my own test program works.
>

That's twisted backwards.

My database builder is built with pure Python, using the SQLite package
that comes with it.  Then sqlite3 just refuses to open the result.  That's
just what's in the Xubuntu 16.04 repos, i.e. version 3.11.0-1ubuntu1; I
didn't report that because I don't suspect sqlite3 of being a cause.
Indeed, it was changes to the python code that seems to have stopped
provoking the error -- nothing about squlite3 has changed.  Anyway, I did
not save the problem code, so I can no longer pursue this.

-- 
word of the year: *kakistocracy*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT is corrupting a database

[Clemens Ladisch]

Kevin O'Gorman wrote:
> On Tue, Jan 10, 2017 at 11:29 PM, Clemens Ladisch  wrote:
>> Kevin O'Gorman wrote:
>>> If I go on to the second table, it appears to finish normally, but when I
>>> try to look at the database with sqlite3, a command-line tool for
>>> interacting with SQLite, it says the database is corrupt.
>>
>> What version?
>
> It's whatever is in Python 3.5.2.'s builtin sqlite package.

The sqlite3 command-line shell does not ship with Python.

>> It's possible that there is a bug in your code.  Which you have not shown.
>
> My opinion is that no user bug whatever should cause DB integrity problems 
> without
> raising an exception.


But it's unlikely that you'd manage to do any of this in Python.

Anyway, my own test program works.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT is corrupting a database

[Jens Alfke]

> On Jan 12, 2017, at 3:52 PM, Kevin O'Gorman  wrote:
> 
> My opinion is that no user bug whatever should cause DB integrity problems
> without raising an exception.

That is a totally reasonable attitude … for programs running in a “safe” 
environment like an interpreter.
However, in the world of low-level native code, there’s nothing SQLite can do 
about a C program overwriting parts of its data structures or file buffers.

Granted, Clemens’ program is written in Python, a “safe” environment, so it 
shouldn’t be able to corrupt a database. There might be a bug in Python’s 
SQLite glue code, though.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT is corrupting a database

[Kevin O'Gorman]

On Tue, Jan 10, 2017 at 7:52 PM, Simon Slavin  wrote:

>
> On 11 Jan 2017, at 3:28am, Kevin O'Gorman  wrote:
>
> > I have a modest amount of data that I'm loading into an SQLite database
> for
> > the first time.  For the moment it contains just two tables and a few
> > indices, nothing else.  The first table loads okay, and if I stop the
> > process at that point, all is well and I can look at the database.
> >
> > If I go on to the second table, it appears to finish normally, but when I
> > try to look at the database with sqlite3, a command-line tool for
> > interacting with SQLite, it says the database is corrupt.
>
> Make absolutely sure you’re starting with a new database file each time,
> not continuing to write to an already-corrupt file.
>
> I'm sure.  The program tests for the existence of the main table before
starting, and throws an exception if it's there, then creates that table as
its first action.


> At stages during your Python program, including after you’ve finished
> loading the first table, use the following command to check to see whether
> the database is correct:
>
> It's no longer possible.  In fixing other things, the program has changed,
and it no longer corrupts the database.  Thanks for this next thing,
though



> PRAGMA integrity_check
>

Thanks for that.  I was not aware of this tool.  I'll keep it handy.


> Use the same command in the command-line tool.
>
> Simon.
>

-- 
word of the year: *kakistocracy*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT is corrupting a database

[Kevin O'Gorman]

On Tue, Jan 10, 2017 at 11:29 PM, Clemens Ladisch 
wrote:

> Kevin O'Gorman wrote:
> > If I go on to the second table, it appears to finish normally, but when I
> > try to look at the database with sqlite3, a command-line tool for
> > interacting with SQLite, it says the database is corrupt.
>
> What version?
>

It's whatever is in Python 3.5.2.'s builtin sqlite package.


> > If however, I split the program into two programs, one for each table,
> and
> > run them one after another, all is well.  Same code, each with parts of
> it
> > if-else-ed out.
>
> It's possible that there is a bug in your code.  Which you have not shown.
>
>
My opinion is that no user bug whatever should cause DB integrity problems
without
raising an exception.




-- 
word of the year: *kakistocracy*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert into with Id...

[Simon Slavin]

On 12 Jan 2017, at 4:06am, hfiandor  wrote:

> I’m trying to read a .csv file and introduce in an SQLite table, using the
> insert into command.
> 
> 
> 
> In the .csv file I have not used this Id field (it was defined as integer
> and autoincrease).

Specify the fields you need in the INSERT command.  So instead of

INSERT INTO MyTable VALUES ('a','b','c')

you should do

INSERT INTO MyTable (name, address, phone) VALUES ('a','b','c')

If you do this, you can miss out the Id field.  When you do that, SQL makes up 
its own values for the Id field based on the INTEGER AUTOINCREMENT you 
specified.

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

[sqlite] Insert into with Id...

[hfiandor]

Dear list members:

 

I’m trying to read a .csv file and introduce in an SQLite table, using the
insert into command.

 

In the .csv file I have not used this Id field (it was defined as integer
and autoincrease).

 

I will appreciate any help about how to copy from a .csv file to an SQLite
table.

 

Thanks in advance,

 

Ing. Héctor F. Fiandor Rosario

 

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

Re: [sqlite] INSERT is corrupting a database

[Clemens Ladisch]

Kevin O'Gorman wrote:
> If I go on to the second table, it appears to finish normally, but when I
> try to look at the database with sqlite3, a command-line tool for
> interacting with SQLite, it says the database is corrupt.

What version?

> If however, I split the program into two programs, one for each table, and
> run them one after another, all is well.  Same code, each with parts of it
> if-else-ed out.

It's possible that there is a bug in your code.  Which you have not shown.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT is corrupting a database

[Simon Slavin]

On 11 Jan 2017, at 3:28am, Kevin O'Gorman  wrote:

> I have a modest amount of data that I'm loading into an SQLite database for
> the first time.  For the moment it contains just two tables and a few
> indices, nothing else.  The first table loads okay, and if I stop the
> process at that point, all is well and I can look at the database.
> 
> If I go on to the second table, it appears to finish normally, but when I
> try to look at the database with sqlite3, a command-line tool for
> interacting with SQLite, it says the database is corrupt.

Make absolutely sure you’re starting with a new database file each time, not 
continuing to write to an already-corrupt file.

At stages during your Python program, including after you’ve finished loading 
the first table, use the following command to check to see whether the database 
is correct:

PRAGMA integrity_check

Use the same command in the command-line tool.

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

[sqlite] INSERT is corrupting a database

[Kevin O'Gorman]

This is a problem I don't quite know how to report in a way that will be
useful.

I'm using Python 3.5 and its builtin sqlite package.

I have a modest amount of data that I'm loading into an SQLite database for
the first time.  For the moment it contains just two tables and a few
indices, nothing else.  The first table loads okay, and if I stop the
process at that point, all is well and I can look at the database.

If I go on to the second table, it appears to finish normally, but when I
try to look at the database with sqlite3, a command-line tool for
interacting with SQLite, it says the database is corrupt.

If however, I split the program into two programs, one for each table, and
run them one after another, all is well.  Same code, each with parts of it
if-else-ed out.

I don't know what to blame, or what to try.

-- 
word of the year: *kakistocracy*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Article with time trials for SQLite INSERT command

[Simon Slavin]

Mike Willekes tests how fast INSERT runs under many different conditions, with 
results from 85 inserts per second to 96,000 inserts per second.

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

Re: [sqlite] Insert silently failing.

[Simon Slavin]

On 2 Sep 2016, at 8:52am, Simon Lepasteur  wrote:

> I have multiple processes accessing the same database file (sqlite 3.8.7.4
> on linux). The DB contains only one table and every process execute
> prepared statements (sqlite3_prepare_v2) like the following one:
> 
> INSERT  INTO table (process,type,status,occurence,data)
> VALUES('test',2,2,1472800896400,'{"key1":"value","key2":"value"}')

What are the replies to

PRAGMA journal_mode
PRAGMA integrity_check
PRAGMA busy_timeout

at the time you're executing your INSERT commands ?  You don't need to report 
these when an error has happened, just tell us what they are set to normally.

Are you executing any PRAGMAs in your program which change any modes, so any 
speedups, etc. ?

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

[sqlite] Insert silently failing.

[Simon Lepasteur]

Hello,

I have multiple processes accessing the same database file (sqlite 3.8.7.4
on linux). The DB contains only one table and every process execute
prepared statements (sqlite3_prepare_v2) like the following one:

INSERT  INTO table (process,type,status,occurence,data)
VALUES('test',2,2,1472800896400,'{"key1":"value","key2":"value"}')

or

SELECT (COUNT(table.id)-1000) AS row_limit FROM table

Everything works perfectly but, on some rare occasion, nothing is inserted
in the database but no error is returned, i.e.:
sqlite3_step(prepared_statement)
returns either SQLITE_OK, SQLITE_ROW, SQLITE_DONE. However if I look into
the DB, nothing is inserted. Once it starts failing, nothing is inserted
anymore. The only way to make the database work again is to delete the file
and start with a new one.

This behavior is really hard to reproduce. Do you have any clue on what
could cause this kind of behavior or where to look to start debugging this?

Best regards,
Simon
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] insert into not inserting float values as expected

[R Smith]

On 2016/07/16 2:04 PM, Ertan Küçükoğlu wrote:

Hi everyone,

It turns out to be my mistake. I did find out that Values in FIS table do
really has a value of 6.0. Sorry for all the fuss.

Thanks.


No problem. I would also suggest, just as a matter of form and having 
correct expectations, that you change all the types you specified as 
"Float" rather to REAL, change Integer and smallint to just INT and any 
Char(xx) to just TEXT. (and in case you ever add dates, make them NUMERIC).


SQLite doesn't have types - it's an un-typed construct, but uses "column 
affinity" - which simply means a column will behave as if it has entries 
with that kind of value in it, unless you specifically put something 
else in there or read it as a different type.


SQLite will translate these types you have correctly into its internal 
affinity types as far as I know, but the ones I mentioned above are the 
precise affinities SQLite will use, and the reason I suggest it is that 
if you expect a column to behave as TEXT rather than CHAR(20), you will 
not be disappointed. If you expect it to behave like CHAR(20) - i.e. 
limit values you add to 20 characters, or error out when you do, or 
right-pad spaces up to 20 characters - then you will be very 
disappointed - because it simply won't do any of that.
(You can still enforce any or all of these rules using CHECK constraints 
- feel free to ask if you need more information)


See the SQLite documentation on types here:
https://www.sqlite.org/datatype3.html


Good luck!
Ryan





-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Ertan
Küçükoğlu
Sent: Saturday, July 16, 2016 12:32 PM
To: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org>
Subject: [sqlite] insert into not inserting float values as expected

Hello,

  


I have below table definitons:

CREATE TABLE IF NOT EXISTS FIS(

   ID  Integer  NOT NULL PRIMARY KEY AUTOINCREMENT,

   Barkod  Char(20) COLLATE NOCASE NOT NULL,

   BarkodAciklamasiChar(50) COLLATE NOCASE,

   UrunKoduChar(20) COLLATE NOCASE,

   UrunAciklamasi  Char(50) COLLATE NOCASE,

   KisaAd  Char(15) COLLATE NOCASE,

   GrupKoduChar(20) COLLATE NOCASE,

   KdvOran Integer  not null check(KdvOran >= 0),

   Miktar  Floatnot null,

   Birim   Char(10) not null COLLATE NOCASE,

   OrjinalBirimFiyat   Floatnot null check(OrjinalBirimFiyat >= 0),

   KartBirimFiyat  Floatnot null check(KartBirimFiyat >= 0),

   TeraziBirimFiyatFloatnot null check(TeraziBirimFiyat >= 0),

   IndirimliBirimFiyat Floatnot null check(IndirimliBirimFiyat >= 0),

   PromosyonBirimFiyat Floatnot null check(PromosyonBirimFiyat >= 0),

   BirimFiyat  Floatnot null check(BirimFiyat >= 0),

   Tutar   Floatnot null,

   Kasiyer Char(20) not null COLLATE NOCASE,

   OturumIDSmallInt not null

);




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

Re: [sqlite] insert into not inserting float values as expected

[Ertan Küçükoğlu]

Hi everyone,

It turns out to be my mistake. I did find out that Values in FIS table do
really has a value of 6.0. Sorry for all the fuss.

Thanks.



-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Ertan
Küçükoğlu
Sent: Saturday, July 16, 2016 12:32 PM
To: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org>
Subject: [sqlite] insert into not inserting float values as expected

Hello,

 

I have below table definitons:

CREATE TABLE IF NOT EXISTS FIS(

  ID  Integer  NOT NULL PRIMARY KEY AUTOINCREMENT,

  Barkod  Char(20) COLLATE NOCASE NOT NULL,

  BarkodAciklamasiChar(50) COLLATE NOCASE,

  UrunKoduChar(20) COLLATE NOCASE,

  UrunAciklamasi  Char(50) COLLATE NOCASE,

  KisaAd  Char(15) COLLATE NOCASE,

  GrupKoduChar(20) COLLATE NOCASE,

  KdvOran Integer  not null check(KdvOran >= 0),

  Miktar  Floatnot null,

  Birim   Char(10) not null COLLATE NOCASE,

  OrjinalBirimFiyat   Floatnot null check(OrjinalBirimFiyat >= 0),

  KartBirimFiyat  Floatnot null check(KartBirimFiyat >= 0),

  TeraziBirimFiyatFloatnot null check(TeraziBirimFiyat >= 0),

  IndirimliBirimFiyat Floatnot null check(IndirimliBirimFiyat >= 0),

  PromosyonBirimFiyat Floatnot null check(PromosyonBirimFiyat >= 0),

  BirimFiyat  Floatnot null check(BirimFiyat >= 0),

  Tutar   Floatnot null,

  Kasiyer Char(20) not null COLLATE NOCASE,

  OturumIDSmallInt not null

);

 

CREATE TABLE IF NOT EXISTS FISYAZDIR(

  ID  Integer  NOT NULL PRIMARY KEY AUTOINCREMENT,

  Barkod  Char(20) COLLATE NOCASE NOT NULL,

  BarkodAciklamasiChar(50) COLLATE NOCASE,

  UrunKoduChar(20) COLLATE NOCASE,

  UrunAciklamasi  Char(50) COLLATE NOCASE,

  KisaAd  Char(15) COLLATE NOCASE,

  GrupKoduChar(20) COLLATE NOCASE,

  KdvOran Integer  not null check(KdvOran >= 0),

  Miktar  Floatnot null,

  Birim   Char(10) not null COLLATE NOCASE,

  OrjinalBirimFiyat   Floatnot null check(OrjinalBirimFiyat >= 0),

  KartBirimFiyat  Floatnot null check(KartBirimFiyat >= 0),

  TeraziBirimFiyatFloatnot null check(TeraziBirimFiyat >= 0),

  IndirimliBirimFiyat Floatnot null check(IndirimliBirimFiyat >= 0),

  PromosyonBirimFiyat Floatnot null check(PromosyonBirimFiyat >= 0),

  BirimFiyat  Floatnot null check(BirimFiyat >= 0),

  Tutar   Floatnot null,

  Kasiyer Char(20) not null COLLATE NOCASE,

  OturumIDSmallInt not null

);

 

I have following records for FIS table:


Barkod

Miktar

BirimFiyat

Tutar


1005

1

6.5

6.5


1005

1

6.5

6.5


1005

1

6.5

6.5


1005

-1

6.5

-6.5

 

 

I am running below query when fisyazdir table is completely empty:

insert into fisyazdir select

null,Barkod,BarkodAciklamasi,UrunKodu,UrunAciklamasi,KisaAd,GrupKodu,KdvOran
,

Sum(Miktar),

Birim,OrjinalBirimFiyat,KartBirimFiyat,TeraziBirimFiyat,IndirimliBirimFiyat,
PromosyonBirimFiyat,BirimFiyat,

sum(Tutar),

Kasiyer,OturumID from FIS

group by

Barkod,BarkodAciklamasi,UrunKodu,UrunAciklamasi,KisaAd,GrupKodu,KdvOran,

Birim,OrjinalBirimFiyat,KartBirimFiyat,TeraziBirimFiyat,IndirimliBirimFiyat,
PromosyonBirimFiyat,BirimFiyat,

Kasiyer,OturumID

 

 

Result in fisyazdir is something like:


Barkod

Miktar

BirimFiyat

Tutar


1005

2

6.5

13


1005

-1

6

-6.5

 

I expected BirimFiyat to be 6.5, but it is not. Is it me doing something
wrong? I cannot understand the reason.

 

Thanks.

Ertan Küçükoğlu

 

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

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