SQLite Forum

Timeline
Login

17 forum posts by user midijohnny

2021-11-26
21:41 Reply: group_concat - ORDER BY parameter please. (artifact: a49d9c4083 user: midijohnny)

Looking at this again - I like this idiom you gave:

select group_concat(x, ', ')
  from (
           select x
             from t
         order by x
        )

That is: the

from (...)
can only be 'reached' by this one statement. (That is: there is absolutely no chance for anybody else to mess with this - as opposed my initial approach - somebody else could 're-use' the 'sorted' results and re-order them - even if that somebody else was myself).

21:34 Reply: group_concat - ORDER BY parameter please. (artifact: 0692c97e0e user: midijohnny)

Ok - thankyou - I think I get this. I am going to use the following rule of thumb here going forward:

If the thing that generates the (ordered) rows can be run in isolation - such that the ORDER BY presents the rows to the end-user in the required order - then if that is 'piped-into' anything else that uses it - its ordering is not going to change.

As opposed to something like a correlated sub-select which sees an 'outer' scope - and therefore cannot be run in isolation.

Still - it would be nice if there was an explicit parameter to 'group_concat' I think. (So others reading code would see the intention/assumption more clearly).

21:00 Reply: sqlite CLI: .timer - should be whole transaction? (artifact: 32e7e72928 user: midijohnny)

Thanks ! And in the meantime I came up with some additional SQL to achieve what I need here.

--mysql.sql
DROP TABLE IF EXISTS _timer;
CREATE TEMPORARY TABLE _timer AS SELECT strftime('%s','now') AS start, NULL as end;

BEGIN TRANSACTION;
-- many inserts here --
END TRANSACTION;

UPDATE _timer SET end=strftime('%s','now');
SELECT PRINTF("Elapsed Time: %d seconds", end-start) FROM _timer;

Example Output:

sqlite> .read mysql.sql
Elapsed Time: 87 seconds
20:12 Reply: group_concat - ORDER BY parameter please. (artifact: bc1374ac5d user: midijohnny)

Just to add that the following - using a window function - might be a suitable workaround; it works empirically for this case, but still not sure if this can be relied on.

-- SQLite 3.36.0
WITH
  data(x) AS ( values(3),(1),(2),(9),(5),(4),(6),(8),(7),(0) ),
  sorted(x) AS ( SELECT ROW_NUMBER() OVER (ORDER BY x) AS r FROM data )
SELECT group_concat(x) AS out FROM data
UNION 
SELECT group_concat(x) AS out FROM sorted;

Gives:

out
1,2,3,4,5,6,7,8,9,10
3,1,2,9,5,4,6,8,7,0

Also: looks like what I'm asking for is something similar to the Oracle LISTAGG function, or the Postgres STRING_AGG function I guess.

19:35 Post: group_concat - ORDER BY parameter please. (artifact: 44f8245f43 user: midijohnny)

The docs for the group_concat aggregate function states the following:

"The order of the concatenated elements is arbitrary."

And the test below at least confirms that any ORDER BY clause is indeed ignored (which I assume is meant by 'arbitrary' here).

sqlite> with t(x) AS ( values(3),(1),(2) ) select group_concat(x) from t ORDER BY x;
3,1,2 

Would it be possible for this function to be enhanced in some future version so to either make use of any ORDER BY clause or perhaps have an explicit ordering expression as a parameter?

Something like:

with t(x) AS ( values(3),(1),(2) )
select group_concat(x,',', ORDER BY x) from t;

I note some examples in the docs also appear to make the assumption that the 'group_concat' will output in a 'sane' order - see the CTE examples , which appear to make this assumption. (Either that: or the author happens to know that the ordering is implicitly safe in these examples).

19:13 Post: sqlite CLI: .timer - should be whole transaction? (artifact: bd985c6ffb user: midijohnny)

I am loading ~80k rows ‚Äčof data using SQL via the 'sqlite3' CLI '.read' command:

sqlite> .version
SQLite 3.36.0 [...]
sqlite> .timer on
sqlite> .read load.sql

The SQL itself wrapped in a TRANSACTION like this:

BEGIN TRANSACTION;
-- many INSERT statements --
END TRANSACTION;

I notice the output from the .timer is for every statement, rather than the whole batch:

Run Time: real 0.000 user 0.000000 sys 0.000000
Run Time: real 0.912 user 0.015625 sys 0.125000
Run Time: real 0.000 user 0.000000 sys 0.000000
Run Time: real 0.794 user 0.000000 sys 0.484375
Run Time: real 0.000 user 0.000000 sys 0.000000
Run Time: real 0.968 user 0.000000 sys 0.484375
Run Time: real 0.000 user 0.000000 sys 0.000000
Run Time: real 0.855 user 0.000000 sys 0.203125
Run Time: real 0.000 user 0.000000 sys 0.000000
[...]

Is this the correct behaviour/useful? In my case; I happen to more interested in the total time/summary for the whole batch, rather than a list of individual timings. I also assume that this output in fact slows down the overall import? Wouldn't it be more appropriate to just treat the whole block as a batch and show an average timing here?

2021-11-25
13:39 Reply: Feature Request: ATTACH DB readonly? (artifact: f4c3c288b5 user: midijohnny)

Thanks !

13:39 Reply: Feature Request: ATTACH DB readonly? (artifact: 58b5053a17 user: midijohnny)

That works - thanks !

sqlite> .version
SQLite 3.36.0 2021-06-18 18:36:39 5c9a6c06871cb9fe42814af9c039eb6da5427a6ec28f187af7ebfb62eafa66e5
zlib version 1.2.11
gcc-5.2.0
sqlite> attach database "file:reference.db?mode=ro" as reference;
sqlite> PRAGMA database_list;
0|main|
2|reference|C:\Users\[...]\reference.db
sqlite> .tables
reference.x
sqlite> drop table reference.x;
Error: attempt to write a readonly database
2021-11-24
17:30 Post: Feature Request: ATTACH DB readonly? (artifact: 0a42231385 user: midijohnny)

Apologies if this is wrong place to request features (if so: is there somewhere else to do that?).

Could we have a extension to the ATTACH DATABASE command to allow a secondary DB to be attached as readonly?

I have a use-case where I want to access information from another DB, but want to avoid making any changes by accident; I also want to signal to others that the DB should be opened-up as readonly (or 'reference' etc).

(I am aware that you can make the file readonly : but this would also make the same data readonly for all other access to the same file; which isn't what I want to do).

2021-11-22
14:08 Reply: PRINTF - newlines, tabs etc? (artifact: bca2d11224 user: midijohnny)

Thank you for the clarification and explanation. So: I will use the following format going forward (single quotes, not double).

SELECT PRINTF('x%cx',char(9))
2021-11-18
21:22 Post: PRINTF - newlines, tabs etc? (artifact: 5a03f7b917 user: midijohnny)

From the docs I understand that SQLite has its own implementation of 'printf', rather than matching the C implementation. - so it can provide some more specific formatting options.

But is there something fundamental about the way this is written that precludes the use of escaped characters for TAB "\t" , newline "\n" etc? (maybe platform independence issues with this?)

I found a workaround, so not a huge problem - but just more wordy and less standard:

SELECT PRINTF("x%cx",char(9)) -- works, just not the same in C (etc)
INTERSECT
SELECT PRINTF("x\tx")
-- no rows: "\t" is returned literally rather than outputting a TAB.
2021-11-08
16:07 Reply: Feature request: Stored Procedures (artifact: f4b54a08cf user: midijohnny)

That looks like a very nice module: since it glues together existing features to give something very powerful - that is - create table-valued functions using only SQL.

Is there somewhere we can vote to have this included as a 'standard' module?

2021-11-04
17:47 Reply: Getting 'default' values: for a view? (artifact: 595f0b162a user: midijohnny)

Nice: I didn't know about the returning clause - could be useful for other things as well.

It won't quite work for what I need at the moment (but I see there are some features being looked at for future releases) - but good to know of this. Thanks!

2021-11-03
22:33 Post: Getting 'default' values: for a view? (artifact: af4f9bc564 user: midijohnny)

Is there a way of evaluating the 'default' expression for a table in a context other than INSERTing a new row?

Example:

create table people(name text default 'fred');
insert into people default values; -- will insert 'fred'
insert into people values(null);

I am wondering if there is some construction that would allow me to generate the row - without actually necessarily persisting it - in the same way?

select ifnull(name,'I would like the default here') from people;

I can see how to see the expression; so for static strings , you can do this:

SELECT TRIM(dflt_value,"'") -- bad assumption
FROM pragma_table_info('people')
WHERE NAME='name'

The TRIM here is to remove the single quotes from the default; but that only works of course if the default happens to be a static string, rather than a calculated one.

22:14 Reply: 'values' with no args : has to be syntax error? (artifact: c8c00f96ef user: midijohnny)

Additionally: I was wrong about the exact equivalence of the "SELECT 1 WHERE 1=0". In that - this is actually doing the equivalent of this:

with a("1") as (values(1)) select * from a;

That is: it names the column - not as 'column1' (as 'values' does) but by the value of the row.

22:11 Reply: 'values' with no args : has to be syntax error? (artifact: ecdd0373cf user: midijohnny)

Good explanation - makes sense. Thank you.

2021-10-29
12:20 Post: 'values' with no args : has to be syntax error? (artifact: 0d81ea8d28 user: midijohnny)

All things being equal - is there a reason why the no-arg version of 'values' isn't allowed?

This is just an academic question : no serious issue here - just one of consistency. (I'm writing a guide - and for that it is useful to show minimal examples).

The following will give exactly one row:

values(1)

Which is the exact equivalent of:

select 1

However; the next example is syntax error:

values()

But arguably should be the exact equivalent of:

select 1 where 0