Re: [sqlite] How to refer to `this` table?

[Amit Yaron]

Try creating a trigger(https://sqlite.org/lang_createtrigger.html) 
instead of the constraint "noCircularRef_when the table already exists.


On 23.2.2019 8:43, Rocky Ji wrote:

If I do

CREATE TABLE Sample (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   parent_id INTEGER,
   CONSTRAINT p FOREIGN KEY (parent_id) REFERENCES Sample (id)
);

I don't get any errors and the schema behaves as expected. But if I try

CREATE TABLE Aliases (
   alias_id INTEGER PRIMARY KEY AUTOINCREMENT,
   real_name TEXT NOT NULL,
   aka TEXT NOT NULL,
   CONSTRAINT xyz UNIQUE (real_name, aka),
   CONSTRAINT noCircularRef_A CHECK (
 real_name NOT IN (SELECT aka FROM Aliases)
   ),
   CONSTRAINT noCircularRef_B CHECK (
 aka NOT IN (SELECT real_name FROM Aliases)
   )
);

I am getting an `Error: no such table: Aliases` error. So how do I
implement this constraint? Are there any special keywords, like NEW and OLD
of trigger statements, to refer to current table?
___
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] How to refer to `this` table?

[Clemens Ladisch]

Rocky Ji wrote:
> CREATE TABLE Aliases (
>   alias_id INTEGER PRIMARY KEY AUTOINCREMENT,
>   real_name TEXT NOT NULL,
>   aka TEXT NOT NULL,
>   CONSTRAINT xyz UNIQUE (real_name, aka),
>   CONSTRAINT noCircularRef_A CHECK (
> real_name NOT IN (SELECT aka FROM Aliases)
>   ),
>   CONSTRAINT noCircularRef_B CHECK (
> aka NOT IN (SELECT real_name FROM Aliases)
>   )
> );
>
> Error: no such table: Aliases

 says:
| The expression of a CHECK constraint may not contain a subquery.

You'd have to write triggers to check this:

CREATE TRIGGER noCircularRef_insert
AFTER INSERT ON Aliases
FOR EACH ROW
WHEN NEW.real_name IN (SELECT aka FROM Aliases)
  OR NEW.aka IN (SELECT real_name FROM Aliases)
BEGIN
  SELECT RAISE(FAIL, "circular reference");
END;
-- same for AFTER UPDATE OF real_name, aka


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

Re: [sqlite] How to refer to `this` table?

[Simon Slavin]

On 23 Feb 2019, at 6:43am, Rocky Ji  wrote:

> CREATE TABLE Aliases (
>  alias_id INTEGER PRIMARY KEY AUTOINCREMENT,
>  real_name TEXT NOT NULL,
>  aka TEXT NOT NULL,
>  CONSTRAINT xyz UNIQUE (real_name, aka),
>  CONSTRAINT noCircularRef_A CHECK (
>real_name NOT IN (SELECT aka FROM Aliases)
>  ),
>  CONSTRAINT noCircularRef_B CHECK (
>aka NOT IN (SELECT real_name FROM Aliases)
>  )
> );
> 
> I am getting an `Error: no such table: Aliases` error. So how do I
> implement this constraint?

You can't implement a SELECT of the same table inside a CONSTRAINT.  But you 
can inside a TRIGGER.  So implement the last two CONSTRAINTs as TRIGGERs, 
returning RAISE(FAIL)

BEGIN
SELECT RAISE(ABORT, 'aka matches old real_name.')
WHERE EXISTS (
SELECT 1 FROM Aliases WHERE OLD.real_name = NEW.aka
);
END;

The above should work.  The following, which looks better, may work too:

BEGIN
SELECT RAISE(ABORT, 'aka matches old real_name.')
FROM Aliases
WHERE OLD.real_name = NEW.aka;
SELECT RAISE(ABORT, 'real_name matches old aka.')
FROM Aliases
WHERE OLD.aka = NEW.real_name;
END;

Perform your own tests.

You should define these for UPDATE as well as INSERT.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is the recommended way to write to views?

[Rocky Ji]

Thanks for the suggestion on `UNIQUE`, I should have done it. Based on this
new schema I wrote (pseudoSQL code):

create table dummy (d text);  -- empty table to let us use
case...when...then syntax
create table variables(
variable_id integer primary key autoincrement,
name text not null,
value integer not null
);

create trigger summary_insert
instead of insert on summary
begin
select *,
case when (select a_id from Alpha where a_attribute =
NEW.a_attribute) not null
then
(insert into variables (name, value) values ("AlphaPK", (select
a_id from Alpha where a_attribute = NEW.a_attribute)))
else
-- insert into Alpha
-- get last_insert_rowid
/*
nested case...when...then till all pks are attained
then insert into appropriate tables using something like
`select max(value) from variables where name = AlphaPK`
*/
from dummy;
end;


But I guess this is too ugly, and difficult to maintain. I'll use DAL to do
all this work and insert in tables directly.

On Wed, Feb 20, 2019 at 5:53 PM Simon Slavin  wrote:

> On 20 Feb 2019, at 11:14am, Rocky Ji  wrote:
>
> > create view summary
> > as
> >select
> >a.a_attribute,
> >b.b_attribute,
> >c.c_attribute
> >from
> >m2mAlphaBeta m
> >inner join Alpha a on a.a_id = m.FK_a_id
> >inner join Beta b on b.b_id = m.FK_b_id
> >inner join Charlie c on c.FK_C_a_id = a.a_id;
> >
> > And assuming all incoming data (say from CSV read) is correct. How do I
> write a record to `summary`?
>
> First, congratulations on getting "integer primary key autoincrement"
> right for keys which are FOREIGN KEY keys.  Common mistake.  However, I
> suspect you've missed a bet.  Consider
>
> > create table Alpha (
> >a_id integer primary key autoincrement,
> >a_attribute text
> > );
>
> Would "a_attribute TEXT UNIQUE" be better ?  This would guard against two
> entries in Alpha having the same attribute, which I think you wouldn't
> don't want.  You can do the same thing for the other _attribute columns.
>
> In answer to the question, "summary" is a VIEW.  You don't have to write
> anything to it.  It looks like it already does what you want.
>
> > Like how do I get ROWID of the "just inserted" record in A to insert
> into C properly. I am thinking "should I make transactions, but we don't
> have variables"... and going round-and-round without solution.
>
> Ah, I see.  Okay, I'm going to ignore Charlie for clarity.  You have a CSV
> file containing two columns: a.a_attribute and b.b_attribute, and you're
> wondering how to get the data into Alpha, Beta and m2mAlphaBeta.  The
> answer is ... not in one command.  It can't be done.
>
> Reading two values from your CSV file, theAAttrib and theBAttrib,
>
> First, make sure that Alpha and Beta have the rows they need.
>
> INSERT OR IGNORE INTO Alpha (a_attribute) VALUES (theAAttrib);
> INSERT OR IGNORE INTO Beta (b_attribute) VALUES (theBAttrib);
>
> But you don't know whether those created new rows or not, so you have to
> find the rows you're going to want to use:
>
> newA_id = result of "SELECT a_id FROM Alpha WHERE a_attribute = '<
> theAAttrib>'"
> newB_id = result of "SELECT b_id FROM BETA WHERE b_attribute = <
> theBAttrib>"
>
> Then you have the values you need for "INSERT INTO Charlie".
>
> Unforunately, you cannot use last_insert_rowid() because you do not know
> whether your "INSERT OR IGNORE" inserted a new row or not.
>
> Fortunately, your "UNIQUE" constraints on the attribute columns means that
> you have indexes on them.  This means that the "SELECT" commands will
> execute very quickly.
>
> Extend the above for Charlie.
>
> 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] What is the recommended way to write to views?

[Rocky Ji]

Wow, that's some clever use. I tried doing it all in one go using a `vars`
table (name, value, timestamp)  and a `dummy` (empty table to enable access
to case syntax) table, made a mess of case...when...then... only to realize
that a then-expr can't contain insert clause. I guess I'll implement this
in DAL itself.

On Wed, Feb 20, 2019 at 9:15 PM David Raymond 
wrote:

> Well, you _can_ with this specific simplified schema, but it's a little
> sketchy and most likely will not scale. So if at all possible this sort of
> thing should be done from the controlling program rather than by a trigger.
> Also as mentioned, if the a/b/c _attribute fields aren't unique in the
> tables then it will also just result in duplication. So not the most
> useful. But hey, here's my solution for the over simplified version:
>
>
> As you said we don't have variables, so last_insert_rowid() can't get
> saved for each table.
>
> For this specific schema though the lack of "not null" on the foreign keys
> makes it doable, as long as "not null" is the actual expectation and
> nothing in the m2m table stays around with a null.
>
>
> create trigger trg_summary_insert
> instead of insert on summary
> for each row
> begin
>
> insert into Beta (b_attribute) values (new.b_attribute);
>
> insert into m2mAlphaBeta (FK_b_id) select last_insert_rowid();
> --This leaves FK_a_id null.
>
> insert into Alpha (a_attribute) values (new.a_attribute);
>
> update m2mAlphaBeta set FK_a_id = last_insert_rowid() where FK_a_id is
> null;
> --Only the new row inserted above should have a null, so the new row gets
> updated
> --without us needing to know the rowid for the m2m table.
>
> --And since the previous statement was an update,
> --then last_insert_rowid() will keep the value from the Alpha insert.
> insert into Charlie (c_attribute, FK_C_a_id) values (new.c_attribute,
> last_insert_rowid());
>
> end;
>
>
> sqlite> insert into summary values ('A', 1.0, 'One'), ('B', 2.2, 'Two');
>
> sqlite> select * from summary;
> a_attribute|b_attribute|c_attribute
> A|1.0|One
> B|2.2|Two
>
> sqlite> select * from Alpha;
> a_id|a_attribute
> 1|A
> 2|B
>
> sqlite> select * from Beta;
> b_id|b_attribute
> 1|1.0
> 2|2.2
>
> sqlite> select * from Charlie;
> c_id|c_attribute|FK_C_a_id
> 1|One|1
> 2|Two|2
>
> sqlite> select * from m2mAlphaBeta;
> _id|FK_a_id|FK_b_id
> 1|1|1
> 2|2|2
>
> --Throw in junk values to make sure it's not just because of coincidence
> that the new rowid's are the same for each table
>
> sqlite> insert into Alpha values (17, 'Seventeen');
>
> sqlite> insert into Beta values (32, 32.0);
>
> sqlite> insert into Charlie values (57, 'Fifty-Seven', null);
>
> --And try it again
> sqlite> insert into summary values ('C', 3.3, 'Three');
>
> sqlite> select * from summary;
> a_attribute|b_attribute|c_attribute
> A|1.0|One
> B|2.2|Two
> C|3.3|Three
>
> --However, this just blindly throws in duplicates since there're no unique
> constraints
>
> sqlite> insert into summary values ('C', 3.3, 'Three');
>
> sqlite> select * from summary;
> a_attribute|b_attribute|c_attribute
> A|1.0|One
> B|2.2|Two
> C|3.3|Three
> C|3.3|Three
>
> sqlite>
>
>
> Also at the very end here I realize this also requires no triggers on
> inserts for any of the real tables. Otherwize last_insert_rowid() will be
> giving results from the inserts in the recursive triggers, and throw things
> off.
>
> So yes, if possible do it in the controlling program rather than in
> triggers :)
>
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Rocky Ji
> Sent: Wednesday, February 20, 2019 6:14 AM
> To: SQLite mailing list
> Subject: [sqlite] What is the recommended way to write to views?
>
> Hi everyone,
>
> I know the simple answer to be `instead of insert ...`. But consider a
> situation where:
>
> Alpha has-many Beta,and
> Beta has-many Alpha
> Alpha has-many Charlie, while
> Charlie has one Alpha
>
> So if my SQL looks like:
> -- PRAGMA fk ON;
> create table Alpha (
> a_id integer primary key autoincrement,
> a_attribute text
> );
>
> create table Beta (
> b_id integer primary key autoincrement,
> b_attribute real
> );
>
> create table Charlie (
> c_id integer primary key autoincrement,
> c_attribute text,
> FK_C_a_id integer,
> constraint abc foreign key (FK_C_a_id) references Alpha (a_id)
> );
>
> create table m2mAlphaBeta (
> _id integer primary key autoincrement,
> FK_a_id integer,
> FK_b_id integer,
> constraint def foreign key (FK_a_id) references Alpha (a_id),
> constraint ghi foreign key (FK_b_id) references Beta (b_id)
> );
>
>
> create view summary
> as
> select
> a.a_attribute,
> b.b_attribute,
> c.c_attribute
> from
> m2mAlphaBeta m
> inner join Alpha a on a.a_id = m.FK_a_id
> inner join Beta b on b.b_id = m.FK_b_id
> inner join Charlie c on c.FK_C_a_id = a.a_id
> ;
>
>
> And assuming all 

[sqlite] How to refer to `this` table?

[Rocky Ji]

If I do

CREATE TABLE Sample (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  parent_id INTEGER,
  CONSTRAINT p FOREIGN KEY (parent_id) REFERENCES Sample (id)
);

I don't get any errors and the schema behaves as expected. But if I try

CREATE TABLE Aliases (
  alias_id INTEGER PRIMARY KEY AUTOINCREMENT,
  real_name TEXT NOT NULL,
  aka TEXT NOT NULL,
  CONSTRAINT xyz UNIQUE (real_name, aka),
  CONSTRAINT noCircularRef_A CHECK (
real_name NOT IN (SELECT aka FROM Aliases)
  ),
  CONSTRAINT noCircularRef_B CHECK (
aka NOT IN (SELECT real_name FROM Aliases)
  )
);

I am getting an `Error: no such table: Aliases` error. So how do I
implement this constraint? Are there any special keywords, like NEW and OLD
of trigger statements, to refer to current table?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WITHOUT ROWID tables with a ROWID

[Tom Bassel]

Ah I see now. Sorry I should have read the docs more carefully -- it is working 
according to spec in all cases.

Great answers. Thanks guys!
Tom
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug: table-valued functions for PRAGMA ignore database names

[Peter Gunold]

Hello,

I found a bug in sqlite when using table-values pragma functions with
attached databases when using the sqlite3.exe for windows.
In my tests I found out, that it is not possible to use table-valued pragma
functions on attached databases e.g. to read user_version or schema_version.

So the Query:

SELECT a.user_version as 'main_version', b.user_version as
'attached_version' FROM
  main.pragma_user_version() as 'a',
   test_db.pragma_user_version() as 'b';

Should display the user_version of main-database and attached database as
wel, but both values display always the version of the main database.

I tested this with 2 szenarios, first with attaching a in-memory database
and second with attaching a physical database.
Results are identical for both.




Infos about test envoirement

OS:Windows XP Professional Service Pack 3 (32-Bit)
Sqlite-Version:3.27.1 2019-02-08 13:17:39
0eca3dd3d38b31c92b49ca2d311128b74584714d9e7de895b1a6286ef959a1dd
Test-Command:sqlite3 -echo bug_test.db < pragma_bug-testcase.sql



To make it easy to reconstruct this Bug I added example outputs and my
inputs-commands (as sql file) for sqlite3.exe.

Simply run
"sqlite3 -echo bug_test.db < pragma_bug-testcase.sql"

on any test-databse and compare results to my "test-ouputs" for szenario 1
and szenario 2.
-- switch display mode
.mode column
.header on


-- show my sqlite-version
SELECT sqlite_version();
sqlite_version()

3.27.1  


-- TEST SZENARIO 1: attach an in-memory test-database
ATTACH DATABASE ':memory:' AS test_db;


-- TEST SZENARIO 2: attach another psysical database



-- lets check if attached database is here
SELECT * FROM pragma_database_list();
seq namefile

--  --  

0   mainJ:\bug_test.db
2   test_db 





SELECT a.user_version as 'main_version', b.user_version as 'attached_version' 
FROM  
  main.pragma_user_version() as 'a', 
   test_db.pragma_user_version() as 'b';
main_version  attached_version
  
0 0 



-- Change user Version of main-db and attached db
PRAGMAmain.user_version=123;
PRAGMA test_db.user_version=456;





-- WRONG-BEHAVOIR: both cols show the values for main-db
-- Expected Behavoir: Col 'test_db.pragma_user_version()' should show values 
from attached database 'test_db' like 'PRAGMA test_db.user_version;'

SELECT a.user_version as 'main_version', b.user_version as 'attached_version' 
FROM  
  main.pragma_user_version() as 'a', 
   test_db.pragma_user_version() as 'b';
main_version  attached_version
  
123   123 



-- This shows correct values
PRAGMA main.user_version;
user_version

123 
PRAGMA test_db.user_version;
user_version

456 



-- Show that database-name prefix is completly ignored:
-- Expected behavoir: 'Error: unknown database not_existing_database' - because 
no database name 'not_existing_database' exists
-- Actual behavoir: no error, it shows the value for pragma user_version from 
main-db

SELECT * FROM  not_existing_database.pragma_user_version();
user_version

123 



.quit
-- switch display mode
.mode column
.header on


-- show my sqlite-version
SELECT sqlite_version();
sqlite_version()

3.27.1  


-- TEST SZENARIO 1: attach an in-memory test-database
-- ATTACH DATABASE ':memory:' AS test_db;


-- TEST SZENARIO 2: attach another psysical database
ATTACH DATABASE './physical.db' AS test_db;



-- lets check if attached database is here
SELECT * FROM pragma_database_list();
seq namefile

--  --  

0   mainJ:\bug_test.db
2   test_db J:\physical.db  




SELECT a.user_version as 'main_version', b.user_version as 'attached_version' 
FROM  
  main.pragma_user_version() as 'a', 
   test_db.pragma_user_version() as 'b';
main_version  attached_version
  
0 0 



-- Change user Version of main-db and attached db
PRAGMAmain.user_version=123;
PRAGMA test_db.user_version=456;





-- WRONG-BEHAVOIR: both cols show the values for main-db
-- Expected Behavoir: Col 'test_db.pragma_user_version()' should show values 
from attached database 'test_db' like 'PRAGMA test_db.user_version;'

SELECT a.user_version as 'main_version', b.user_version as 'attached_version' 
FROM  
  main.pragma_user_version() as 'a', 
   test_db.pragma_user_version() as 'b';
main_version  attached_version
  
123   123  

[sqlite] SQLite3's vulnerability in 3.27.1 and 3.26

[范龙飞]

?SELECT(+++ last_insert_rowid()++sum(0)oVER())ORDER BY 
(+++ last_insert_rowid()++sum(0)oVER())ORDER BY 
1,1,1,1,1,1?


Best regards
Longfei Fan from 360 Codesafe Team of Legendsec?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WITHOUT ROWID tables with a ROWID

[tom-sqlite]

Ah I see now. Sorry I should have read the docs more carefully -- it is working 
according to spec in all cases.
   
Great answers. Thanks guys!
Tom
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Find if an upsert operation did an insert or an update.

[Keith Medcalf]

On Friday, 22 February, 2019 09:08, Constantine Yannakopoulos 
:

> I would like to find whether an upsert operation actually did an
> insert or an update, preferably without having to execute extra 
> SQL statements before or after it. 

> I thought of using last_insert_rowid() before and after the
> upsert and check if the result has changed but while this 
> seems to work for normal tables 

> it will not work for WITHOUT ROWID tables. Is there another
> way that works consistently both with tables with or without rowid?

I suppose you could add a field to the table and set that field according to 
whether the UPSERT UP'ed or SERT'ed ... then go look at that field ... 
Personally, I should think it would be simpler to simply devolve the single 
declarative UPSERT into its component parts are merely watch what is happening 
as it goes along ...

You are doing the equivalent of asking whether there is a way to tell if 
"create table if not exists " created the table or not, which is a 
ridiculous question.  After the execution of the statement the table exists.  
If you care whether the statement created the table or not then your issue is 
that you failed to understand and have chosen the statement incorrectly.

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

Re: [sqlite] Find if an upsert operation did an insert or an update.

[Keith Medcalf]

On Friday, 22 February, 2019 09:08, Constantine Yannakopoulos 
:

> I would like to find whether an upsert operation actually did an
> insert or an update, preferably without having to execute extra 
> SQL statements before or after it. 

I doubt it very much.  You see, an UPSERT statement (or rather an INSERT with 
one or more ON CONFLICT clauses) is an atomic statement.  If no error 
(ABORT/FAIL) is thrown, the statement executed successfully and did whatever it 
was that the statement was "a declaration of doing".  If you require to know 
what the various "component parts" of the declared statement are doing, then 
you need to execute in smaller parts.

> I thought of using last_insert_rowid() before and after the
> upsert and check if the result has changed but while this 
> seems to work for normal tables 

It only seems to work but is not reliable (that is, it can be 
proven/demonstrated that there is at least one case in which reliance on this 
method will lead to an incorrect assumption and therefore the method is 
unreliable).

> it will not work for WITHOUT ROWID tables. Is there another
> way that works consistently both with tables with or without rowid?

Yes.  That is to "unbundle" the UPSERT into its component parts and execute 
them in a single transaction (which is what the combined UPSERT does) tracking 
the progress along the way.  If you already have an outer transaction in 
progress then SAVEPOINTs might be in order.

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

Re: [sqlite] Find if an upsert operation did an insert or an update.

[Simon Slavin]

On 22 Feb 2019, at 5:18pm, David Raymond  wrote:

> Also I think total_changes will just tell you that the statement changed 
> "something", but you still won't know if it was an insert or an update, or 
> how many of each.

As OP wrote, you do it before and after "INSERT OR IGNORE" OR "UPDATE ... WHERE 
..." and see whether it changed.

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

Re: [sqlite] Find if an upsert operation did an insert or an update.

[David Raymond]

I think pragma data_version only tells you if someone _else_ made any file 
updates.

Also I think total_changes will just tell you that the statement changed 
"something", but you still won't know if it was an insert or an update, or how 
many of each.

Doing a "select count(*) from ...;" both before and after will let you know how 
many were inserted, but not how many were updated. Though if you're only 
expecting a single row to be upserted that would work; if it's the same then it 
was an update, if different then an insert. But expensive to run yeah.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Friday, February 22, 2019 11:49 AM
To: SQLite mailing list
Subject: Re: [sqlite] Find if an upsert operation did an insert or an update.

On 22 Feb 2019, at 4:08pm, Constantine Yannakopoulos  
wrote:

> I would like to find whether an upsert operation actually did an insert or an 
> update, preferably without having to execute extra SQL statements before or 
> after it.

Try



int sqlite3_total_changes(sqlite3*);

However, note that this is sensitive to how you're structuring your 
transactions, and to whether another connection has changed the database.  Test 
it out for your use-pattern to see if it helps.

If not, then the cannonical method is probably



which, of course, takes a SQL command.

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] Find if an upsert operation did an insert or an update.

[Simon Slavin]

On 22 Feb 2019, at 4:08pm, Constantine Yannakopoulos  
wrote:

> I would like to find whether an upsert operation actually did an insert or an 
> update, preferably without having to execute extra SQL statements before or 
> after it.

Try



int sqlite3_total_changes(sqlite3*);

However, note that this is sensitive to how you're structuring your 
transactions, and to whether another connection has changed the database.  Test 
it out for your use-pattern to see if it helps.

If not, then the cannonical method is probably



which, of course, takes a SQL command.

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

Re: [sqlite] Cannot Modify Table if Part of a View

[Dan Kennedy]

On 22/2/62 17:17, Chris Locke wrote:

This issue was found via DB Browser for SQLite, but relates to SQLite, not
DB Browser for SQLite, so please bear with me

If a table is part of a view, then SQLite complains that "Error: error in
view view1: no such table: main.table11"


ALTER TABLE ... RENAME TO ... commands fail in new versions of SQLite if 
the schema contains views or triggers that refer to tables or columns 
that do not exist. That's what is happening here. To restore the legacy 
behaviour, run:


  PRAGMA legacy_alter_table = 1;

  https://sqlite.org/pragma.html#pragma_legacy_alter_table

Dan.





The link to the full issue is here:
https://github.com/sqlitebrowser/sqlitebrowser/issues/1686

Martin has followed the instructions here (
https://www.sqlite.org/lang_altertable.html#otheralter) which raises the
above error.
(link to comment:
https://github.com/sqlitebrowser/sqlitebrowser/issues/1686#issuecomment-464136036
)
We are using SQLite 3.27.1 and 3.26 (in two different applications) and (as
per the above GitHub thread) 3.27.1 mentions fixing an ALTER TABLE bug
(item 11) with views.  However, there is no WITH clause and no redundant
UNIQUE clauses involved in our case.

Its worked fine in earlier versions of SQLite, and note a comment about the
improved ALTER TABLE functionality:
"In version 3.25.0, the ALTER TABLE statement has been enhanced to run a
test-parse of the whole schema after it alters the schema, to make sure
that the edits it performed on the schema didn't break anything."

it sounds like this extra sanity check is what is causing the problem
described here which also explains why it worked for older versions of
SQLite.

In this issue (https://www.sqlite.org/src/tktview?name=31c6e64ff9) drh
describes a very similar issue to the one here. He is using a trigger
instead of a view but besides that is is pretty much the same problem.
Again some more explanations from the ticket:

"The DROP TABLE removes the table t1, which leaves a dangling reference to
t1 in the trigger. Then during the ALTER TABLE, the trigger is reparsed,
but the reparse fails due to the dangling reference."

Again, it sounds exactly like the issue we are having here. The second
ticket is still open, so no fix there yet. Last modification was 2018-10-03.

Is there any confirmation or further details on this issue at all?


Thanks,
Chris
___
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] Find if an upsert operation did an insert or an update.

[Constantine Yannakopoulos]

Hello,

I would like to find whether an upsert operation actually did an insert or
an update, preferably without having to execute extra SQL statements before
or after it. I thought of using last_insert_rowid() before and after the
upsert and check if the result has changed but while this seems to work for
normal tables it will not work for WITHOUT ROWID tables. Is there another
way that works consistently both with tables with or without rowid?

Best regards,

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

Re: [sqlite] Fossil new mailing list doesn't seen to work

[Warren Young]

On Feb 22, 2019, at 2:34 AM, Domingo Alvarez Duarte  wrote:
> 
> I'm writing here because I've tried to create an account on fossil-scm.org 
> and it doesn't seems to work.

No user with this email is in the Fossil forum user table.  I’ve just tested it 
with an email address I don’t use on the Fossil forums, and subscribing does 
instantly create such a user, even before you’ve confirmed your email address.

(This also proves that yes, it does work!)

I’m guessing you didn’t go through the right entry point:

https://fossil-scm.org/forum/subscribe

If you tried to go through the Login link instead, you might get such a symptom.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fossil new mailing list doesn't seen to work

[Richard Hipp]

On 2/22/19, Domingo Alvarez Duarte  wrote:
> Hello !
>
> I'm writing here because I've tried to create an account on
> fossil-scm.org and it doesn't seems to work.
>
> I fill in all the fields and then send the form, it refresh the screen
> and gives no feedback about what happened, it created the acount ?
>
> Then I try to login it says invalid userid/password.
>
> Then I create another account again with same data it behaves the same,
> refresh the screen and gives no feedback, user already exists ? It
> create the account ?

You should receive an email verification message.  Check your spam folder.

-- 
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] Cannot Modify Table if Part of a View

[Chris Locke]

This issue was found via DB Browser for SQLite, but relates to SQLite, not
DB Browser for SQLite, so please bear with me

If a table is part of a view, then SQLite complains that "Error: error in
view view1: no such table: main.table11"

The link to the full issue is here:
https://github.com/sqlitebrowser/sqlitebrowser/issues/1686

Martin has followed the instructions here (
https://www.sqlite.org/lang_altertable.html#otheralter) which raises the
above error.
(link to comment:
https://github.com/sqlitebrowser/sqlitebrowser/issues/1686#issuecomment-464136036
)
We are using SQLite 3.27.1 and 3.26 (in two different applications) and (as
per the above GitHub thread) 3.27.1 mentions fixing an ALTER TABLE bug
(item 11) with views.  However, there is no WITH clause and no redundant
UNIQUE clauses involved in our case.

Its worked fine in earlier versions of SQLite, and note a comment about the
improved ALTER TABLE functionality:
"In version 3.25.0, the ALTER TABLE statement has been enhanced to run a
test-parse of the whole schema after it alters the schema, to make sure
that the edits it performed on the schema didn't break anything."

it sounds like this extra sanity check is what is causing the problem
described here which also explains why it worked for older versions of
SQLite.

In this issue (https://www.sqlite.org/src/tktview?name=31c6e64ff9) drh
describes a very similar issue to the one here. He is using a trigger
instead of a view but besides that is is pretty much the same problem.
Again some more explanations from the ticket:

"The DROP TABLE removes the table t1, which leaves a dangling reference to
t1 in the trigger. Then during the ALTER TABLE, the trigger is reparsed,
but the reparse fails due to the dangling reference."

Again, it sounds exactly like the issue we are having here. The second
ticket is still open, so no fix there yet. Last modification was 2018-10-03.

Is there any confirmation or further details on this issue at all?


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

Re: [sqlite] How to backup a memory database to a memory stream?

[Amit Yaron]

Is "sqlite3_serialize" (https://sqlite.org/c3ref/serialize.html) helpful?

On 22.2.2019 8:57, heribert wrote:

Is there any way to backup a memory database directly to a memory stream?

In my case i have to backup a small memory database directly into a byte 
array, without using any os based temp file.


___
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] Fossil new mailing list doesn't seen to work

[Domingo Alvarez Duarte]

Hello !

I'm writing here because I've tried to create an account on 
fossil-scm.org and it doesn't seems to work.


I fill in all the fields and then send the form, it refresh the screen 
and gives no feedback about what happened, it created the acount ?


Then I try to login it says invalid userid/password.

Then I create another account again with same data it behaves the same, 
refresh the screen and gives no feedback, user already exists ? It 
create the account ?


Cheers !

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

Re: [sqlite] How to backup a memory database to a memory stream?

[Shawn Wagner]

See https://www.sqlite.org/c3ref/serialize.html

(You might have to compile a custom version of sqlite if the one you're
using wasn't built with support for serialization enabled)


On Thu, Feb 21, 2019, 10:58 PM heribert  wrote:

> Is there any way to backup a memory database directly to a memory stream?
>
> In my case i have to backup a small memory database directly into a byte
> array, without using any os based temp file.
>
> ___
> 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