Re: [sqlite] A CTE to count the records (rows) for each table

[Richard Hipp]

On 3/15/17, Donald Griggs  wrote:
>> >  Does anyone knows a Common Table Expression (CTE) to be used with the
>> >  sqlite_master table so we can count for each table how many rows it
>> >  has.
>
> I wonder if it's always accurate to piggyback on the work of ANALYZE and
> obtain row counts as of the last ANALYZE via:
>
>select tbl, max(substr(stat, 1, instr((stat || ' '), ' ') -1 )) from
> sqlite_stat1 group by tbl order by tbl;
>
> Equivalently, if one relies on CAST to obtain the first integer:
>
> select tbl, max(cast (stat AS NUMERIC)) from sqlite_stat1 group by
> tbl order by tbl;

The current ANALYZE always makes an exact row-count.  But there is
code on a branch
(https://www.sqlite.org/src/timeline?r=est_count_pragma) that only
does an approximation.  And that "approximate" ANALYZE may land on
trunk within the next release or two.

So, no, I would not trust the sqlite_stat1 data if you need an accurate count.

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

Re: [sqlite] A CTE to count the records (rows) for each table

[Donald Griggs]

> >  Does anyone knows a Common Table Expression (CTE) to be used with the
> >  sqlite_master table so we can count for each table how many rows it
> >  has.

I wonder if it's always accurate to piggyback on the work of ANALYZE and
obtain row counts as of the last ANALYZE via:

   select tbl, max(substr(stat, 1, instr((stat || ' '), ' ') -1 )) from
sqlite_stat1 group by tbl order by tbl;

Equivalently, if one relies on CAST to obtain the first integer:

select tbl, max(cast (stat AS NUMERIC)) from sqlite_stat1 group by
tbl order by tbl;


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

Re: [sqlite] A CTE to count the records (rows) for each table

[Marco Silva]

Excerpts from Richard Hipp's message of 2017-03-13 14:47:49 -0400:
> On 3/13/17, Marco Silva  wrote:
> > Hi,
> >
> >  Does anyone knows a Common Table Expression (CTE) to be used with the
> >  sqlite_master table so we can count for each table how many rows it
> >  has.
> 
> That is not possible.  Each table (potentially) has a different
> structure, and so table names cannot be variables in a query - they
> must be specified when the SQL is parsed.
> 
> But you could do this with an extension such as
> https://www.sqlite.org/src/artifact/f971962e92ebb8b0 that implements
> an SQL function that submits new SQL text to the SQLite parser.  For
> example:
> 
>   SELECT name, eval(printf('SELECT count(*) FROM "%w"',name))
>   FROM sqlite_master
>WHERE type='table' AND coalesce(rootpage,0)>0;
> 

Worked pretty well, with the extension you mentioned. Thanks, Dr. Hipp

-- 
Marco Arthur @ (M)arco Creatives
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A CTE to count the records (rows) for each table

[Dominique Devienne]

On Tue, Mar 14, 2017 at 10:14 AM, Dominique Devienne 
wrote:

>
> On Mon, Mar 13, 2017 at 7:47 PM, Richard Hipp  wrote:
>
>> On 3/13/17, Marco Silva  wrote:
>> > Hi,
>> >
>> >  Does anyone knows a Common Table Expression (CTE) to be used with the
>> >  sqlite_master table so we can count for each table how many rows it
>> >  has.
>>
>> That is not possible.  Each table (potentially) has a different
>> structure, and so table names cannot be variables in a query - they
>> must be specified when the SQL is parsed.
>>
>> But you could do this with an extension such as
>> https://www.sqlite.org/src/artifact/f971962e92ebb8b0 that implements
>> an SQL function that submits new SQL text to the SQLite parser.  For
>> example:
>>
>>   SELECT name, eval(printf('SELECT count(*) FROM "%w"',name))
>>   FROM sqlite_master
>>WHERE type='table' AND coalesce(rootpage,0)>0;
>>
>
>
> You can also use SQLite to generate SQL text which you feed back into
> SQLite :)
> Below's my little experimentation on that. Nothing new, but was fun
> nonetheless. --DD
>

[...]

> C:\Users\ddevienne>sqlite3.18.0rc test.db "select group_concat('select
> '''||name||''' as name, count(*) from '|| name, ' union all ') from
> sqlite_master where type = 'table' group by type" | sqlite3.18.0rc test.db
> t|3
> u|2
> v|0
>
>

And thinking more about this, what we'd need is a new .eval command to the
official shell,
so we don't have to use two instances of SQLite on the same DB connected
via a pipe.

.eval would run the SQL that follows as usual, but each output row of that
SQL should start
with a text value being to SQL text to evaluate, and we could even imagine
optional extra columns
being the bind parameters of that SQL text, if bind placeholders are used.

Now that would be fun and powerful. That's not dynamic SQL in SQLite
itself, only the shell,
but that would already be very powerful IMHO. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A CTE to count the records (rows) for each table

[Dominique Devienne]

On Mon, Mar 13, 2017 at 7:47 PM, Richard Hipp  wrote:

> On 3/13/17, Marco Silva  wrote:
> > Hi,
> >
> >  Does anyone knows a Common Table Expression (CTE) to be used with the
> >  sqlite_master table so we can count for each table how many rows it
> >  has.
>
> That is not possible.  Each table (potentially) has a different
> structure, and so table names cannot be variables in a query - they
> must be specified when the SQL is parsed.
>
> But you could do this with an extension such as
> https://www.sqlite.org/src/artifact/f971962e92ebb8b0 that implements
> an SQL function that submits new SQL text to the SQLite parser.  For
> example:
>
>   SELECT name, eval(printf('SELECT count(*) FROM "%w"',name))
>   FROM sqlite_master
>WHERE type='table' AND coalesce(rootpage,0)>0;
>


You can also use SQLite to generate SQL text which you feed back into
SQLite :)
Below's my little experimentation on that. Nothing new, but was fun
nonetheless. --DD

C:\Users\ddevienne>sqlite3.18.0rc test.db
SQLite version 3.18.0 2017-03-06 20:44:13
Enter ".help" for usage hints.
sqlite> create table t (id);
sqlite> create table u (id);
sqlite> create table v (id);
sqlite> insert into t values (1), (2), (3);
sqlite> insert into u values (4), (5);
sqlite> .exit

C:\Users\ddevienne>sqlite3.18.0rc test.db "select count(*) from t";
3

C:\Users\ddevienne>sqlite3.18.0rc test.db "select count(*) from u";
2

C:\Users\ddevienne>sqlite3.18.0rc test.db "select count(*) from v";
0

C:\Users\ddevienne>sqlite3.18.0rc test.db "select 'select '''||name||''' as
name, count(*) from '|| name from sqlite_master where type = 'table'"
select 't' as name, count(*) from t
select 'u' as name, count(*) from u
select 'v' as name, count(*) from v

C:\Users\ddevienne>sqlite3.18.0rc test.db "select group_concat('select
'''||name||''' as name, count(*) from '|| name, ' union all ') from
sqlite_master where type = 'table' group by type"
select 't' as name, count(*) from t union all select 'u' as name, count(*)
from u union all select 'v' as name, count(*) from v

C:\Users\ddevienne>sqlite3.18.0rc test.db "select group_concat('select
'''||name||''' as name, count(*) from '|| name, char(10)||'union
all'||char(10)) from sqlite_master where type = 'table' group by type"
select 't' as name, count(*) from t
union all
select 'u' as name, count(*) from u
union all
select 'v' as name, count(*) from v

C:\Users\ddevienne>sqlite3.18.0rc test.db "select group_concat('select
'''||name||''' as name, count(*) from '|| name, ' union all ') from
sqlite_master where type = 'table' group by type" | sqlite3.18.0rc test.db
t|3
u|2
v|0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A CTE to count the records (rows) for each table

[Brian Curley]

Statistics, even if generated at run-time, might be useful.

One option, at least per the CLI, might be to output a variant of DRH's
last SQL to be read back in. I've no idea as to the portability of this
onto embedded systems, but it works "okay" on the CLI and on desktop
apps...my test file was fairly large and I'm including views, which add to
the overhead.

SELECT

CASE WHEN rowid == (select max(rowid) from sqlite_master where type in
('table','view') and name not like 'sqlite_%')

THEN printf('SELECT "%w", count(*) FROM "%w" ',name,name)

ELSE printf('SELECT "%w", count(*) FROM "%w" UNION',name,name)

END counted

FROM

sqlite_master

WHERE

type

IN ('table','view')

AND name

NOT LIKE 'sqlite_%'

;


Dynamic SQL would be very helpful here, but I haven't seen it on SQLite.
Maybe if an attached db could be leveraged...?

Regards.

Brian P Curley



On Mon, Mar 13, 2017 at 2:47 PM, Richard Hipp  wrote:

> On 3/13/17, Marco Silva  wrote:
> > Hi,
> >
> >  Does anyone knows a Common Table Expression (CTE) to be used with the
> >  sqlite_master table so we can count for each table how many rows it
> >  has.
>
> That is not possible.  Each table (potentially) has a different
> structure, and so table names cannot be variables in a query - they
> must be specified when the SQL is parsed.
>
> But you could do this with an extension such as
> https://www.sqlite.org/src/artifact/f971962e92ebb8b0 that implements
> an SQL function that submits new SQL text to the SQLite parser.  For
> example:
>
>   SELECT name, eval(printf('SELECT count(*) FROM "%w"',name))
>   FROM sqlite_master
>WHERE type='table' AND coalesce(rootpage,0)>0;
>
>
> --
> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A CTE to count the records (rows) for each table

[Richard Hipp]

On 3/13/17, Marco Silva  wrote:
> Hi,
>
>  Does anyone knows a Common Table Expression (CTE) to be used with the
>  sqlite_master table so we can count for each table how many rows it
>  has.

That is not possible.  Each table (potentially) has a different
structure, and so table names cannot be variables in a query - they
must be specified when the SQL is parsed.

But you could do this with an extension such as
https://www.sqlite.org/src/artifact/f971962e92ebb8b0 that implements
an SQL function that submits new SQL text to the SQLite parser.  For
example:

  SELECT name, eval(printf('SELECT count(*) FROM "%w"',name))
  FROM sqlite_master
   WHERE type='table' AND coalesce(rootpage,0)>0;


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

Re: [sqlite] A CTE to count the records (rows) for each table

[Simon Slavin]

On 13 Mar 2017, at 6:40pm, Marco Silva  wrote:

> Does anyone knows a Common Table Expression (CTE) to be used with the
> sqlite_master table so we can count for each table how many rows it
> has.

The way SQLite stores its data is not helpful to counting the rows.  The number 
of rows isn’t stored anywhere and to find out the number of rows in a table 
SQLite has to iterate through them all.

The fastest way to find the number of rows in a table is

SELECT COUNT(*) FROM MyTable

and I’m afraid there is no simple command which does this for more than one 
table.

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