Re: [sqlite] To edit in sqlite3 CLI multiline SQL statements?
Simon Slavin, on Saturday, January 18, 2020 04:58 PM, wrote... > > On 18 Jan 2020, at 9:30pm, Csanyi Pal, on > > > can one edit a multiline SQL statement in the sqlite3 CLI? > > No. > > But if you make a multiline SQL statement in a text file you can paste it > into the CLI all in one operation. Well, you could, but you need to hit the up arrow for every new line in that multiline, and edit the line. But, I agree with Simon: the best way to do it, is to use some text editor, and make the changes in the text editor and copy and paste to the CLI. Thanks. josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Obtaining rowid of an updated row in UPSERT
On Sun, 19 Jan 2020 17:07:38 +0100, you wrote:
>On 16.01.2020 14:02, Daniel Janus wrote:
>> Dear SQLiters,
>>
>> If an INSERT ... ON CONFLICT DO UPDATE statement detects that a row
>> already exists and needs to be updated, it doesn't seem to set lastRowid
>> to the rowid of that row. Observe (sqlite 3.30.1):
>>
>> > create table users (id integer primary key, firstname text, lastname
>> text, phonenumber text);
>> > create unique index idx_users_firstname_lastname on users (firstname,
>> lastname);
>> > insert into users (firstname, lastname, phonenumber) values ('John',
>> 'Doe', '1'),
>> ('Donald', 'Covfefe', '2');
>> > insert into users (firstname, lastname, phonenumber) values ('John',
>> 'Doe', '3')
>> on conflict (firstname, lastname) do update set phonenumber =
>> excluded.phonenumber;
>> > select last_insert_rowid();
>> 2
>>
>> I'd like to have obtained 1 instead, the rowid for John Doe.
>>
>> I imagine the reasoning behind this is that if there was no inserted row,
>> then last_*insert*_rowid should remain as it was... but is there any way
>> to obtain this information, other than making a subsequent SELECT?
>
> Replying to myself: I ended up doing exactly this – a subsequent SELECT after
> the
> upsert; while this approach is working, it does feel suboptimal.
On the source code side, yes, but you have to wrap the UPSERT and subsequent
SELECT in a transaction anyway, so you can be sure the relavant index and table
pages are still valid in the cache. So, performancewise, it doesn't matter much.
--
Regards,
Kees Nuyt
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Find schema of a table in a query
On Sunday, 19 January, 2020 01:47, x wrote: >Suppose you’re given a query ‘SELECT 1 from tbl’ by a user and you want >to know the name of the schema that tbl belongs to. What’s the easiest >way to do this? >I know sqlite will use temp.tbl if it exists else main.tbl if it exists >else it will search for the earliest attached schema with a table called >tbl. Finding that involves the use of PRAGMA database_list and then >querying each of the associated sqlite_master tables in turn for the >existence of tbl until you get a match. Is there an easier way? How about: select name from pragma_database_list as d where (select name from pragma_table_xinfo where schema == d.name and arg == 'x') is not null; Where you set "arg == 'tablename'" which will return all the schema's in which the specified tablename exists. -- 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] Bug? SQLITE_DEFAULT_DEFENSIVE and CLI .parameters
Defining SQLITE_DEFAULT_DEFENSIVE prevents proper working of the CLI .parameter commands. SQLite version 3.31.0 2020-01-19 18:49:07 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .schema sqlite> .param init sqlite> .schema sqlite> .param set :test test Error: no such table: temp.sqlite_parameters sqlite> CREATE TEMP TABLE sqlite_parameters(key TEXT PRIMARY KEY, value); Error: object name reserved for internal use: sqlite_parameters -- 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 schema of a table in a query
If I could answer the “such as” I wouldn’t have asked the question. The word “hoping” is the clue as in I was hoping there was some function I had overlooked. I’ve settled for using the sqlite3_table_column_metadata function in a loop using each db name in turn in the order sqlite3 does. From: sqlite-users on behalf of Warren Young Sent: Sunday, January 19, 2020 4:02:40 PM To: SQLite mailing list Subject: Re: [sqlite] Find schema of a table in a query On Jan 19, 2020, at 2:41 AM, x wrote: > > I was hoping for something simpler. Such as? I mean, your question basically reduces to “I need to be inside the parse loop,” and SQLite has come along and said, “Hey, check this out, you can be inside the parse loop.” I mean, how cool is that? ___ 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] Obtaining rowid of an updated row in UPSERT
On 16.01.2020 14:02, Daniel Janus wrote:
Dear SQLiters,
If an INSERT ... ON CONFLICT DO UPDATE statement detects that a row
already exists and needs to be updated, it doesn't seem to set lastRowid
to the rowid of that row. Observe (sqlite 3.30.1):
> create table users (id integer primary key, firstname text, lastname text,
phonenumber text);
> create unique index idx_users_firstname_lastname on users (firstname,
lastname);
> insert into users (firstname, lastname, phonenumber) values ('John',
'Doe', '1'),
('Donald', 'Covfefe', '2');
> insert into users (firstname, lastname, phonenumber) values ('John',
'Doe', '3')
on conflict (firstname, lastname) do update set phonenumber =
excluded.phonenumber;
> select last_insert_rowid();
2
I'd like to have obtained 1 instead, the rowid for John Doe.
I imagine the reasoning behind this is that if there was no inserted row,
then last_*insert*_rowid should remain as it was... but is there any way
to obtain this information, other than making a subsequent SELECT?
Replying to myself: I ended up doing exactly this – a subsequent SELECT after
the
upsert; while this approach is working, it does feel suboptimal.
– Daniel
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Find schema of a table in a query
On Jan 19, 2020, at 2:41 AM, x wrote: > > I was hoping for something simpler. Such as? I mean, your question basically reduces to “I need to be inside the parse loop,” and SQLite has come along and said, “Hey, check this out, you can be inside the parse loop.” I mean, how cool is that? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Find schema of a table in a query
Thanks Dominique. I was aware of that route but I was hoping for something simpler. From: sqlite-users on behalf of Dominique Devienne Sent: Sunday, January 19, 2020 9:32:28 AM To: SQLite mailing list Subject: Re: [sqlite] Find schema of a table in a query On Sun, Jan 19, 2020 at 9:47 AM x wrote: > Suppose you’re given a query ‘SELECT 1 from tbl’ by a user and you want to > know the name of the schema that tbl belongs to. What’s the easiest way to > do this? Set an authorizer. Requires to write code though, cannot be done in SQL. https://www.sqlite.org/c3ref/set_authorizer.html ___ 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 schema of a table in a query
On Sun, Jan 19, 2020 at 9:47 AM x wrote: > Suppose you’re given a query ‘SELECT 1 from tbl’ by a user and you want to > know the name of the schema that tbl belongs to. What’s the easiest way to > do this? Set an authorizer. Requires to write code though, cannot be done in SQL. https://www.sqlite.org/c3ref/set_authorizer.html ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Find schema of a table in a query
Suppose you’re given a query ‘SELECT 1 from tbl’ by a user and you want to know the name of the schema that tbl belongs to. What’s the easiest way to do this? I know sqlite will use temp.tbl if it exists else main.tbl if it exists else it will search for the earliest attached schema with a table called tbl. Finding that involves the use of PRAGMA database_list and then querying each of the associated sqlite_master tables in turn for the existence of tbl until you get a match. Is there an easier way? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
