Re: [sqlite] [EXTERNAL] Inconsistency of CREATE/DROP TABLE with attached DBs

2020-03-04 Thread Hick Gunter 
Addendum: Note that CREATE TABLE has an optional schema name which must be one 
of

- main (which is the default)
- temp (which is the same as specifying TEMP or TEMPORARY between CREATE and 
TABLE)
- the name of an attached database

See https://sqlite.org/lang_createtable.html


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Hick Gunter
Gesendet: Donnerstag, 5. März 2020 08:48
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] Inconsistency of CREATE/DROP TABLE with 
attached DBs

I don't see any inconsistency here.

1) implicit attach of a.sqlite as main and create a.t1
2) implcit attach b.sqlite as main, attach a.sqlite as a and create (main).t1 
(in b.sqlite)
3) implicit attach c.sqlite as main, attaxh a.sqlite as a and drop the only 
table named t1 from a

c.sqlite never contains a table t1. If it did, then step 3 would drop c.t1 in 
step 3

see https://sqlite.org/lang_naming.html

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von no...@null.net
Gesendet: Mittwoch, 4. März 2020 18:31
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Inconsistency of CREATE/DROP TABLE with attached 
DBs

I ran into an inconsistency? between CREATE and DROP TABLE today:

# Pipe this example through sed -e 's/^ *//' before running through
# a shell
#
# 1. Set up table a.t1
rm -f a.sqlite b.sqlite c.sqlite

cat

Re: [sqlite] [EXTERNAL] Inconsistency of CREATE/DROP TABLE with attached DBs

2020-03-04 Thread Hick Gunter 
I don't see any inconsistency here.

1) implicit attach of a.sqlite as main and create a.t1
2) implcit attach b.sqlite as main, attach a.sqlite as a and create (main).t1 
(in b.sqlite)
3) implicit attach c.sqlite as main, attaxh a.sqlite as a and drop the only 
table named t1 from a

c.sqlite never contains a table t1. If it did, then step 3 would drop c.t1 in 
step 3

see https://sqlite.org/lang_naming.html

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von no...@null.net
Gesendet: Mittwoch, 4. März 2020 18:31
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Inconsistency of CREATE/DROP TABLE with attached 
DBs

I ran into an inconsistency? between CREATE and DROP TABLE today:

# Pipe this example through sed -e 's/^ *//' before running through
# a shell
#
# 1. Set up table a.t1
rm -f a.sqlite b.sqlite c.sqlite

cat

[sqlite] Inconsistency of CREATE/DROP TABLE with attached DBs

2020-03-04 Thread nomad 
I ran into an inconsistency? between CREATE and DROP TABLE today:

# Pipe this example through sed -e 's/^ *//' before running through
# a shell
#
# 1. Set up table a.t1
rm -f a.sqlite b.sqlite c.sqlite

cat

Re: [sqlite] Shell Feature Request: spaces before dot commands

2020-03-04 Thread nomad 
On Wed Mar 04, 2020 at 12:44:09PM +, Simon Slavin wrote:
> On 4 Mar 2020, at 12:19pm, no...@null.net wrote:
> 
> >I like to sometimes indent a block of SQL and change
> > settings or run dot commands within a transaction, e.g.:
> > 
> >BEGIN
> >.mode csv
> >.import ...
> >COMMIT;

> Dot commands are not SQL commands, they are instructions to the shell
> application.  Transactions, CASE structures, WITH structures, and
> other such things do not affect them.

Well commands like .import create a table. Since SQLite supports
transactional DDL there is certainly a relevance between BEGIN/COMMIT
and a call to .import, no?

But that is all kind of beside my point: I do not see a technical
necessity for dot commands to have no leading space, and my programming
experience would be better of without that requirement.

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

Re: [sqlite] Getting all changes within a begin; end; transaction

2020-03-04 Thread Jose Isaias Cabrera 

Simon Slavin, on Wednesday, March 4, 2020 10:47 AM, wrote...
>
> On 4 Mar 2020, at 3:28pm, Jose Isaias Cabrera  wrote:
>
> > The reason why I know is that if I have 238 INSERTS, but I have a
> constraint , there should be 238 INSERTs the first time I run a set of SQL,
> but if I run the same SQL again, there should not be any records INSERTED,
> and thus, the amount should be 0, correct?
>
> Can you try the same thing but instead of INSERT use INSERT OR IGNORE ?

Simon Slavin, you are a genius!  Well, you're pretty smart. ;-)  Yes, indeed, I 
was using
INSERT OR REPLACE, which will always work. ;-)  INSERT OR IGNORE is now 
providing
the result I am looking for.  As we say in Spanish, muchas gracias.

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

Re: [sqlite] Getting all changes within a begin; end; transaction

2020-03-04 Thread Simon Slavin 
On 4 Mar 2020, at 3:28pm, Jose Isaias Cabrera  wrote:

> The reason why I know is that if I have 238 INSERTS, but I have a constraint 
> , there should be 238 INSERTs the first time I run a set of SQL, but if I run 
> the same SQL again, there should not be any records INSERTED, and thus, the 
> amount should be 0, correct?

Can you try the same thing but instead of INSERT use INSERT OR IGNORE ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting all changes within a begin; end; transaction

2020-03-04 Thread Simon Slavin 
On 4 Mar 2020, at 3:28pm, Jose Isaias Cabrera  wrote:

> However, I have found that it does not actually provide the **ACTUAL** 
> changes, but a count of the possible changes.

Hmm.  I understand you.  Does this do something more like what you need ?


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

Re: [sqlite] Getting all changes within a begin; end; transaction

2020-03-04 Thread Jose Isaias Cabrera 

Simon Slavin, on Wednesday, March 4, 2020 09:42 AM, wrote...
>
> On 4 Mar 2020, at 2:37pm, Jose Isaias Cabrera  wrote:
>
> > Is there a way to know all the changes that may have happened within
> the full BEGIN and END? Thanks.
>
> Use this function
>
> 
>
> before and after your block, and subtract one from another.

Thanks, this is exactly what I needed.  However, I have found that it does not 
actually provide the **ACTUAL** changes, but a count of the possible changes.  
The reason why I know is that if I have 238 INSERTS, but I have a constraint , 
there should be 238 INSERTs the first time I run a set of SQL, but if I run the 
same SQL again, there should not be any records INSERTED, and thus, the amount 
should be 0, correct?  So, is there one that actually provides the actual 
number of changes?  Thanks.

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

Re: [sqlite] Getting all changes within a begin; end; transaction

2020-03-04 Thread Simon Slavin 
On 4 Mar 2020, at 2:37pm, Jose Isaias Cabrera  wrote:

> Is there a way to know all the changes that may have happened within the full 
> BEGIN and END?  Thanks.

Use this function



before and after your block, and subtract one from another.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Getting all changes within a begin; end; transaction

2020-03-04 Thread Jose Isaias Cabrera 

Greetings.

Imagine this SQL,

BEGIN TRANSACTION;
...
changes to records
...
END;

When I execute "int result = sqlite3_changes(database);" after that SQL 
execution, I always get 1.  I think that it is because it is only providing the 
result of the last statement that was successful within the BEGIN-END 
transaction.  I know that in all cases there should be many more than 1. Is 
there a way to know all the changes that may have happened within the full 
BEGIN and END?  Thanks.

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

Re: [sqlite] Shell Feature Request: spaces before dot commands

2020-03-04 Thread Simon Slavin 
On 4 Mar 2020, at 12:19pm, no...@null.net wrote:

>I like to sometimes indent a block of SQL and change
> settings or run dot commands within a transaction, e.g.:
> 
>BEGIN
>.mode csv
>.import ...
>COMMIT;

Dot commands are not SQL commands, they are instructions to the shell 
application.  Transactions, CASE structures, WITH structures, and other such 
things do not affect them.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Shell Feature Request: spaces before dot commands

2020-03-04 Thread nomad 
[SQLite version 3.22.0 2017-11-27 17:56:14]

The SQLite shell only recognizes .dot commands without leading spaces.
For clarity I like to sometimes indent a block of SQL and change
settings or run dot commands within a transaction, e.g.:

BEGIN
.mode csv
.import ...
COMMIT;

Unfortunately at present the above are parsed as SQL statements and
don't fail until the next ";".

I would like to request a change to the parsing behaviour to recognize 
".command" the same as ".command".

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