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

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

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

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

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

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

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

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,

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

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

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

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

Re: [sqlite] WITHOUT ROWID tables with a ROWID

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

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

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

[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

Re: [sqlite] WITHOUT ROWID tables with a ROWID

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[sqlite] Cannot Modify Table if Part of a View

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:

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

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

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

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

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

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