SQLite Forum

Timeline
Login

50 most recent forum posts by user curmudgeon

2021-11-05
14:47 Reply: Error when updating sqlite3.c using trunk (artifact: 4d06a3bd7d user: curmudgeon)

Thanks Richard.

11:25 Post: Error when updating sqlite3.c using trunk (artifact: 633cd0648c user: curmudgeon)

ar: u' modifier ignored sinceD' is the default (see U') sqlite3.c: In function 'sqlite3Fts3EvalPhrasePoslist': sqlite3.c:178422:14: warning: implicit declaration of function 'sqlite3Fts3Corrupt'; did you mean 'sqlite3Fts3ErrMsg'? [-Wimplicit-function-declaration] 178422 | rc = sqlite3Fts3Corrupt(); | ^~~~~~~~~~~~~~~~~~ | sqlite3Fts3ErrMsg /usr/sbin/ld: /tmp/ccP4nmee.o: in functionsqlite3Fts3EvalPhrasePoslist': /home/tom/sqlite/sqlite3.c:178422: undefined reference to `sqlite3Fts3Corrupt' collect2: error: ld returned 1 exit status make: *** [Makefile:658: sqlite3] Error 1

2021-10-21
13:40 Reply: Inconsistency in BETWEEN Query (artifact: 1f9ff2c06c user: curmudgeon)

What is the type of col_a and col_b. It looks as if the BETWEEN is treating the 3 values as TEXT.

2021-09-23
10:33 Reply: Best way to use carray (artifact: 3aeaf93dcd user: curmudgeon)

You could just create a temp sorted table and populate that with the array but that would be slower. If the array itself is sorted you could do a binary search on it in your own code to find the first and last index and bind the resulting start pointer and number of entries.

2021-08-23
11:40 Reply: How to select columns that have name beginning with same prefix? (artifact: 4782cc3828 user: curmudgeon)

Nice one John.

2021-08-22
19:23 Edit reply: How to select columns that have name beginning with same prefix? (artifact: 189bef64fe user: curmudgeon)
It's easy enough to get the SQL using

select 'select ' || group_concat(name) || ' from mytable' from pragma_table_info('mytable') where substr(name,1,7)='PREFIX_'

but I think you'd need the eval function to execute the SQL which takes you into load extension territory

https://sqlite.org/c3ref/load_extension.html

I'm not sure if eval is automatically loaded into the CLI and I don't have access to it at the moment and to be honest I'm not sure if eval can return multiple rows. You might need a user defined function to execute the SQL.

https://sqlite.org/c3ref/create_function.html
19:14 Reply: How to select columns that have name beginning with same prefix? (artifact: c078464c56 user: curmudgeon)
It's easy enough to get the SQL using

select 'select ' || group_concat(name) || ' from mytable' from pragma_table_info('mytable') where substr(name,1,7)='PREFIX_'

but I think you'd need the eval function to execute the SQL which takes you into load extension territory

https://sqlite.org/c3ref/load_extension.html

I'm not sure if eval is automatically loaded into the CLI and I don't have access to it at the moment.
2021-08-15
10:31 Reply: Need TCL pkg for arch (manjaro) (artifact: 6fb586ca75 user: curmudgeon)

It turned out all I needed to do was run 'sudo pacman -Syu tcl' in terminal.

2021-08-14
18:56 Reply: Need TCL pkg for arch (manjaro) (artifact: 129e7c0b88 user: curmudgeon)

Thanks Larry. Will try it tomorrow.

15:30 Post: Need TCL pkg for arch (manjaro) (artifact: cccda43106 user: curmudgeon)

I recently changed from ubuntu 20.04 to manjaro. On ubuntu I had cloned the sqlite repository and installed tcl-dev with 'sudo apt install tcl-dev'. This allowed me always to have the latest trunk code available.

I've cloned the sqlite repository on manjaro but there's no simple tcl-dev to install. There are tcl pkgs available for Arch on pkgs.org but I've no idea if they'll suffice. I remember when I was installing on ubuntu that installing tcl was insufficient, it had to be tcl-dev.

Is anyone else using arch / manjaro linux who can tell me how to get this set up?

2021-07-10
12:16 Reply: Can this SQL be simplified? (artifact: 800a308d44 user: curmudgeon)

Does

limit max((select count(*) from T2)-(select maxrows from T1), 0)

not work?

2021-07-08
11:36 Reply: Awesome people (artifact: 0ce10773bc user: curmudgeon)

Seconded.

2021-05-13
16:09 Reply: Nothing for several days from the forum (artifact: 07fcb7c4a9 user: curmudgeon)

I'm the same (fossil forum also) and started a thread 'Not receiving emails' below. Today they started again out of the blue (going to junk) but it doesn't seem like they're all getting through.

2021-05-12
11:15 Reply: Index Btree (artifact: d4199709aa user: curmudgeon)

Would this help?

https://sqlite.org/dbstat.html

08:05 Post: Not receiving emails (artifact: 50c4ec6b98 user: curmudgeon)

I'm on hotmail. A few days ago I stopped receiving emails from this forum and the fossil forum (they usually go into my junk folder despite efforts to prevent this). I've checked my forum settings and all the right boxes are ticked. I'm still receiving emails from a non-sqlite forum into my junk folder but I also seem to be getting less junk altogether. I suspect it's a hotmail problem rather than an sqlite forum one but was just wondering if anyone else had experienced this.

2021-04-19
13:44 Reply: SQLite doesn't use indexes for bitwise clauses? (artifact: 08fa31f099 user: curmudgeon)

If it's an integer you could work out that x will be between a and b yourself and include that condition in the where such that the index will be used.

2021-03-18
16:45 Reply: Proper set up for working with SQLite, C, Tcl/Tk? (artifact: 8ae14ac950 user: curmudgeon)

I hated Visual Studio and swore I'd never go near it again but, a few months back, I downloaded visual studio code along with wsl2 (both free) so I could use a single app to access both windows and linux files. I love it. It has syntax highlighting for many languages (c, c++ & tcl included). I can open a bash shell or a command shell without leaving the app and compile c code using gcc.

2021-02-26
15:23 Reply: How do I create a "rolling cursor" ? (artifact: e105286906 user: curmudgeon)
SELECT t.*
  FROM ids
  JOIN t ON t.rowid = ids.id
 WHERE ids.rowid >= ?1 
 ORDER BY ids.rowid
 LIMIT n
;

Agreed Ryan, that is better and maybe less confusing than what I suggested.
13:09 Reply: How do I create a "rolling cursor" ? (artifact: c951d18cea user: curmudgeon)
Store the rowids of the table in a temp table and use them to retrieve sections of the table. e.g. suppose you want to view a table t sorted on col s.

create temp table ids as select rowid as id from t order by s;

If you want to view records i..i+n of the sorted table use this

select * from t where rowid in
(select id from ids where rowid between ?1 and ?1+n order by rowid);

Use bind to set ?1 to i.

If you'd rather store the id's (faster) in an array (a) or vector (v) you can do the same thing using the carray extension

https://sqlite.org/carray.html#:~:text=The%20carray%20()%20function%20is%20not%20compiled%20into,first%20added%20to%20SQLite%20in%20version%203.14%20(2016-08-08)

and execute

select * from t where rowid in carray(?1,n);

binding ?1 to a+i (array case) or v.data()+i (vector case).

Cons:
You're taking a snapshot of t so if others are using separate connections to insert or delete records from t there will be id values missing or no longer valid in the ids table.
For large tables (as in tens of millions of records) storing the rowids can sometimes be sluggish.
2021-02-22
10:24 Edit: Latest trunk/extensions & UDF's on tap (artifact: ecd01af533 user: curmudgeon)
For a while now I've been wishing that, at the click of a button, I could have the latest trunk & extensions as well as all my own functions automatically available to all connections (CLI as well as in my own code). I've recently started usung linux (ubuntu 20.04 via WSL2) and to that end I've written a bash script which is shown below in the hope that it might demistify the process for other wee diddy programmers like myself. I'd also like to thank the forum gurus for their help in getting to this stage (too many of them to mention but Keih Medcalf played a starring role in the core_init stuff) and ask them to cast a critical eye.

1) Create the directory you want to store the sqlite repository in. I chose ~/sqlite.


2) If you want any extensions &/or UDF's to be automatically available to all your connections and want to avoid creating libraries you'll need a core_init function (you can call it something else provided you replace core_init with your own name in the 2 files below). I've written a bare bones example creating 2 simple UDF's and including 2 extensions to illustrate the process.

file : core_init.c (it must be saved in your chosen sqlite directory)

--------------------------------
void mult2(sqlite3_context *ctx,int nargs,sqlite3_value **values)
{
    sqlite3_result_int(ctx,2*sqlite3_value_int(values[0]));
}

void plus2(sqlite3_context *ctx,int nargs,sqlite3_value **values)
{
    sqlite3_result_int(ctx,2+sqlite3_value_int(values[0]));
}

#define TXTREP SQLITE_UTF8 | SQLITE_DETERMINISTIC

int sqlite3_myfcts_init(sqlite3 *db,char **pzErrMsg,const sqlite3_api_routines *pApi)
{
    return sqlite3_create_function_v2(db,"mult2",1,TXTREP,0,&mult2,0,0,0) +
    sqlite3_create_function_v2(db,"plus2",1,TXTREP,0,&plus2,0,0,0);
}

#include "eval.c"
#include "carray.c"

int core_init(const char* dummy)
{
	int nErr = sqlite3_auto_extension((void(*)())sqlite3_myfcts_init);
	nErr += sqlite3_auto_extension((void(*)())sqlite3_eval_init);
#ifndef SQLITE_OMIT_VIRTUALTABLE
	nErr += sqlite3_auto_extension((void(*)())sqlite3_carray_init);
#endif
	return nErr ? SQLITE_ERROR : SQLITE_OK;
}
--------------------------------


3) Create and save the following bash script (I called it upd8) in your sqlite directory.

--------------------------------
#!/bin/bash
cd "$(dirname $(realpath "$0"))"

echo "installing dependencies ..."
sudo apt uninstall sqlite3 -y > /dev/null 2>&1 # uninstall sqlite3 if it was installed with sudo apt
set -e # exit if an error occurs
sudo apt install fossil tcl-dev libreadline-dev -y # if you manually install any of these then omit from list
echo "updating system ..."
sudo apt update && sudo apt upgrade -y # take opportunity to update everything else not sqlite related
echo

if [[ -f sqlite.fossil ]]; then
    echo "updating repository ..."
    fossil update trunk > /dev/null
else
    echo "cloning sqlite repository ..." 
    fossil clone https://www.sqlite.org/src/sqlite sqlite.fossil
    fossil open sqlite.fossil > /dev/null
fi

echo "configuring sqlite ..."
{ configure; make; } > /dev/null
echo "creating tools ..."
make sqlite3_analyzer sqldiff > /dev/null
# make test # uncomment this line if you want sqlite tests run 
if [[ -f core_init.c ]]; then
    echo "integrating core_init.c ..."
    cat core_init.c >> sqlite3.c
    gcc -Os -I. -I./ext/misc \
    -DSQLITE_THREADSAFE=0 \
    -DSQLITE_EXTRA_INIT=core_init -DSQLITE_ENABLE_DBSTAT_VTAB -DSQLITE_ENABLE_FTS5 \
    -DSQLITE_ENABLE_MATH_FUNCTIONS -DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_RTREE \
    -DSQLITE_ENABLE_EXPLAIN_COMMENTS -DHAVE_READLINE -DHAVE_USLEEP \
    sqlite3.c shell.c -o sqlite3 -ldl -lreadline -lncurses -lz -lm > /dev/null 
    # if you omit DSQLITE_THREADSAFE=0 add -pthread to library list
 fi
--------------------------------
 
4) On the command line type 'chmod +x upd8' to make the script executable.

5) Add your sqlite directory to the system PATH. If you're using WSL like myself you'll have to do it by editing (I had to create the file) /etc/profile.d/myenvvars.sh as WSL doesn't seem to pick up on the /etc/environment path (there's a thread on github about this problem that has went unresolved for years). My myenvvars.sh shows the following

tom@sp4:~$ cat /etc/profile.d/myenvvars.sh
#!/bin/bash
export PATH=~/sqlite:$PATH

6) Run 'upd8' in bash. The tail of my output shows

configuring sqlite ...
ar: `u' modifier ignored since `D' is the default (see `U')
ar: `u' modifier ignored since `D' is the default (see `U')
creating tools ...
integrating core_init.c ...
tom@sp4:~$ 
The warnings appear to be harmless.

7) Now test with sqlite3

tom@sp4:~$ sqlite3
SQLite version 3.35.0 2021-02-21 01:19:42
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select eval('select 2+3'), mult2(8), plus2(4);
5|16|6
sqlite> .exit
tom@sp4:~$

Edit: Amended upd8 so it uninstalls sqlite3 if already installed using sudo apt.
2021-02-21
14:31 Post: Latest trunk/extensions & UDF's on tap (artifact: 1840776ac4 user: curmudgeon)
For a while now I've been wishing that, at the click of a button, I could have the latest trunk & extensions as well as all my own functions automatically available to all connections (CLI as well as in my own code). I've recently started usung linux (ubuntu 20.04 via WSL2) and to that end I've written a bash script which is shown below in the hope that it might demistify the process for other wee diddy programmers like myself. I'd also like to thank the forum gurus for their help in getting to this stage (too many of them to mention but Keih Medcalf played a starring role in the core_init stuff) and ask them to cast a critical eye.

1) Create the directory you want to store the sqlite repository in. I chose ~/sqlite.


2) If you want any extensions &/or UDF's to be automatically available to all your connections and want to avoid creating libraries you'll need a core_init function (you can call it something else provided you replace core_init with your own name in the 2 files below). I've written a bare bones example creating 2 simple UDF's and including 2 extensions to illustrate the process.

file : core_init.c (it must be saved in your chosen sqlite directory)

--------------------------------
void mult2(sqlite3_context *ctx,int nargs,sqlite3_value **values)
{
    sqlite3_result_int(ctx,2*sqlite3_value_int(values[0]));
}

void plus2(sqlite3_context *ctx,int nargs,sqlite3_value **values)
{
    sqlite3_result_int(ctx,2+sqlite3_value_int(values[0]));
}

#define TXTREP SQLITE_UTF8 | SQLITE_DETERMINISTIC

int sqlite3_myfcts_init(sqlite3 *db,char **pzErrMsg,const sqlite3_api_routines *pApi)
{
    return sqlite3_create_function_v2(db,"mult2",1,TXTREP,0,&mult2,0,0,0) +
    sqlite3_create_function_v2(db,"plus2",1,TXTREP,0,&plus2,0,0,0);
}

#include "eval.c"
#include "carray.c"

int core_init(const char* dummy)
{
	int nErr = sqlite3_auto_extension((void(*)())sqlite3_myfcts_init);
	nErr += sqlite3_auto_extension((void(*)())sqlite3_eval_init);
#ifndef SQLITE_OMIT_VIRTUALTABLE
	nErr += sqlite3_auto_extension((void(*)())sqlite3_carray_init);
#endif
	return nErr ? SQLITE_ERROR : SQLITE_OK;
}
--------------------------------


3) Create and save the following bash script (I called it upd8) in your sqlite directory.

--------------------------------
#!/bin/bash
set -e # exit if an error occurs
cd "$(dirname $(realpath "$0"))"

echo "installing dependencies ..."
sudo apt install fossil tcl-dev libreadline-dev -y # if you manually install any of these then omit from list
echo "updating system ..."
sudo apt update && sudo apt upgrade -y # take opportunity to update everything else not sqlite related
echo

if [[ -f sqlite.fossil ]]; then
    echo "updating repository ..."
    fossil update trunk > /dev/null
else
    echo "cloning sqlite repository ..." 
    fossil clone https://www.sqlite.org/src/sqlite sqlite.fossil
    fossil open sqlite.fossil > /dev/null
fi

echo "configuring sqlite ..."
{ configure; make; } > /dev/null
echo "creating tools ..."
make sqlite3_analyzer sqldiff > /dev/null
# make test # uncomment this line if you want sqlite tests run 
if [[ -f core_init.c ]]; then
    echo "integrating core_init.c ..."
    cat core_init.c >> sqlite3.c
    gcc -Os -I. -I./ext/misc \
    -DSQLITE_THREADSAFE=0 \
    -DSQLITE_EXTRA_INIT=core_init -DSQLITE_ENABLE_DBSTAT_VTAB -DSQLITE_ENABLE_FTS5 \
    -DSQLITE_ENABLE_MATH_FUNCTIONS -DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_RTREE \
    -DSQLITE_ENABLE_EXPLAIN_COMMENTS -DHAVE_READLINE -DHAVE_USLEEP \
    sqlite3.c shell.c -o sqlite3 -ldl -lreadline -lncurses -lz -lm > /dev/null 
    # if you omit DSQLITE_THREADSAFE=0 add -pthread to library list
 fi
--------------------------------
 
4) On the command line type 'chmod +x upd8' to make the script executable.

5) Add your sqlite directory to the system PATH. If you're using WSL like myself you'll have to do it by editing (I had to create the file) /etc/profile.d/myenvvars.sh as WSL doesn't seem to pick up on the /etc/environment path (there's a thread on github about this problem that has went unresolved for years). My myenvvars.sh shows the following

tom@sp4:~$ cat /etc/profile.d/myenvvars.sh
#!/bin/bash
export PATH=~/sqlite:$PATH

6) Run 'upd8' in bash. The tail of my output shows

configuring sqlite ...
ar: `u' modifier ignored since `D' is the default (see `U')
ar: `u' modifier ignored since `D' is the default (see `U')
creating tools ...
integrating core_init.c ...
tom@sp4:~$ 
The warnings appear to be harmless.

7) Now test with sqlite3

tom@sp4:~$ sqlite3
SQLite version 3.35.0 2021-02-21 01:19:42
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select eval('select 2+3'), mult2(8), plus2(4);
5|16|6
sqlite> .exit
tom@sp4:~$
2021-02-19
18:47 Edit reply: sqlite3 history error in vscode terminal (artifact: d9157e2738 user: curmudgeon)

Thanks for that explanation Warren. I googled xterm wondering if it could be used for the vscode terminal and came across this page

https://www.npmjs.com/package/vscode-xterm

which seems to suggest vscode is using xterm.js ????

18:46 Reply: sqlite3 history error in vscode terminal (artifact: 8c41dc7311 user: curmudgeon)

Thanks for that explanation Warren. I googled xterm wondering if it could be used for the vscode terminal and across this page

https://www.npmjs.com/package/vscode-xterm

which seems to suggest vscode is using xterm.js ????

11:37 Reply: sqlite3 history error in vscode terminal (artifact: f41e253b35 user: curmudgeon)

Thanks Warren, I now realise why 3.33 worked. Despite googling I'm none the wiser about your final sentence but presume it means it's ms's fault and I'll just have to live with it while using linux through wsl.

09:44 Reply: sqlite3 history error in vscode terminal (artifact: 4209513949 user: curmudgeon)
Thanks Warren. I was sending the output to /dev/null thinking everything was fine. I installed libreadline-dev. I still get these messages

checking for library containing readline... no
checking for library containing tgetent... -lncurses
checking for readline in -lreadline... yes
checking readline.h usability... no
checking readline.h presence... no
checking for readline.h... no
checking for /usr/include/readline.h... no
checking for /usr/include/readline/readline.h... yes

Is there something else I'm missing? Either way, the arrow keys do now scroll through the history. I'm assuming When I installed 3.33 using 'sudo apt install sqlite3' ubuntu automatically installed libreadline-dev and then uninstalled it when I purged as otherwise I'd be wondering why 3.33 scrolled through the history without me installing libreadline-dev.

Thanks Warren.

PS It did nothing for bottom line error which I assume is a vscode / wsl problem. There's a video of it at
https://github.com/microsoft/vscode/issues/116174
07:55 Reply: sqlite3 history error in vscode terminal (artifact: 007e7e8adb user: curmudgeon)

Sorry Larry, I'm unsure if you're saying the arrow keys work as expected in 3.34 or they're printing ^[[A^[[B^[[D^[[C as on mine.

2021-02-18
18:45 Reply: sqlite3 history error in vscode terminal (artifact: adb7ae1590 user: curmudgeon)

I wouldn't have a clue how to check if that was the case Gunter.

Could anyone using 3.35 directly on linux let me know if it's displaying any of these traits in the bash shell.

14:42 Edit reply: Table size (artifact: 1e3d2d38b7 user: curmudgeon)

sqlite> select payload from dbstat where summary=1 and name='big'; Error: no such column: summary

I get the same error Keith using 3.35.

summary replaced with aggregate works.

14:37 Reply: Table size (artifact: 62c623d013 user: curmudgeon)

sqlite> select payload from dbstat where summary=1 and name='big'; Error: no such column: summary

I get the same error Keith using 3.35.

14:07 Reply: Table size (artifact: 081531c713 user: curmudgeon)

I think Keith means

select payload from dbstat('main',1) where name='tblname';

If it's an attached db replace 'main' with the attached schema name.

11:52 Post: sqlite3 history error in vscode terminal (artifact: 1fb161b543 user: curmudgeon)
I'm Using the vscode wsl terminal with distro Ubuntu 20.04.

If I install sqlite3 with 'sudo apt install sqlite3' I get version 3.33 and, when running sqlite3, the arrow up/down keys allow me to scroll through the history of previous commands and the left/right keys allow me to move through the current command to correct errors. 

If I use the sqlite3 executable created by the configure and make scripts in the trunk I get version 3.35 but pressing arrow up, arrow down, arrow left, arrow right while running sqlite3 shows

sqlite> ^[[A^[[B^[[D^[[C

There is another problem in vscode terminal with both the aforementioned versions in that if you're on the bottom line and enter a particularly long command the text wraps and then overwrites the bottom line rather than scroll the window up and continue the text on the new bottom line. This I suspect is a vscode problem and I've reported it on github but I'm not sure about the arrow key problem.
2021-02-04
15:24 Reply: Primary Key v Unique Index (artifact: 73e92abaf3 user: curmudgeon)

Thanks Gunter. It's what I expected but my OCD demanded confirmation.

15:04 Post: Primary Key v Unique Index (artifact: 94d070a1a9 user: curmudgeon)

If I do this

CREATE TABLE t(id INTEGER PRIMARY KEY, .... );

then id is the same as rowid.

If I do this

CREATE TABLE t(id INTEGER, ...);

CREATE UNIQUE INDEX id on t(id);

is that the same thing or is id different from rowid.

If the latter, is it possible to add a primary key after the fact? From what I've read I would guess not but I keep reading it's faster to build indexes after inserting large amounts of data but that wouldn't be possible in the case of an INTEGER PRIMARY KEY.

2021-02-02
12:12 Edit reply: Approximate COUNT(*) using sqlite_stat4 (artifact: 68257946ee user: curmudgeon)
Thanks TripleHound, interesting reading, but it wasn't any of those threads. From what I can determine "est_count" appears in several test files in the trunk but doesn't feature in the amalgamation.

tom@sp4:~/sqlite$ grep -Rl 'est_count' .
./ext/fts5/test/fts5aa.test
./src/test_func.c
./test/fuzz_malloc.test
./test/memleak.test
./test/walcrash3.test
./test/tester.tcl
./test/soak.test
./test/backcompat.test

EDIT: Ignore the above list. I didn't include the grep -w option so it was showing up files containing "test_counter". It returned nothing with -w set.
12:05 Reply: Approximate COUNT(*) using sqlite_stat4 (artifact: 6fef7e2b08 user: curmudgeon)
Thanks TripleHound, interesting reading, but it wasn't any of those threads. From what I can determine "est_count" appears in several test files in the trunk but doesn't feature in the amalgamation.

tom@sp4:~/sqlite$ grep -Rl 'est_count' .
./ext/fts5/test/fts5aa.test
./src/test_func.c
./test/fuzz_malloc.test
./test/memleak.test
./test/walcrash3.test
./test/tester.tcl
./test/soak.test
./test/backcompat.test
2021-02-01
15:16 Reply: Approximate COUNT(*) using sqlite_stat4 (artifact: ba1fa9f647 user: curmudgeon)
Thanks Richard although I'm sure what I read was more suited to dummies like myself.
11:03 Reply: create sqlite3_analyzer from trunk (artifact: 2bf5406b61 user: curmudgeon)

You're right Rowan. Just checked it.

10:05 Reply: Approximate COUNT(*) using sqlite_stat4 (artifact: 92c88829df user: curmudgeon)
No, it wasn't that one. Having went through all my posts on that forum I'm starting to think I'm havering. I remain convinced however that I either saw a post from drh or an explanation in the documentation about getting an approximate count.

I think it was something to do with counting the lower level nodes and estimating the number of nodes leading from each of them via a sample. To be honest I've little knowledge of b-trees so I don't even know if that makes sense.
2021-01-31
16:40 Edit reply: Approximate COUNT(*) using sqlite_stat4 (artifact: 17152e3e78 user: curmudgeon)
I'm sure there's some way of getting an approximate row count but I cannot remember what it is despite having spent the best part of an hour searching for a post I made on the very subject on the old nabble forum. I'm guessing it's something to do with the DBSTAT virtual table. Something to do with counting leafs and using a sample to approximate the average number of rows on a leaf.

Hopefully someone with a better memory will step in because I'd like to make use of it myself.

Edit: Not sure if this is related

https://sqlite.org/srcx/timeline?r=est_count_pragma&c=2016-12-16+15%3A57%3A40
08:38 Reply: Approximate COUNT(*) using sqlite_stat4 (artifact: 297607a0ad user: curmudgeon)
I'm sure there's some way of getting an approximate row count but I cannot remember what it is despite having spent the best part of an hour searching for a post I made on the very subject on the old nabble forum. I'm guessing it's something to do with the DBSTAT virtual table. Something to do with counting leafs and using a sample to approximate the average number of rows on a leaf.

Hopefully someone with a better memory will step in because I'd like to make use of it myself.
2021-01-30
12:01 Edit reply: create sqlite3_analyzer from trunk (artifact: 67ba5094de user: curmudgeon)
I initially thought the 'whereis' command searched all directories so it may have been tclConfig.sh existed under tcl8.6 and installing tcl8.6-dev was unnecessary.

My problems were also compounded by the fact I was accessing ubuntu via win10 WSL. It took days of investigation and a thread on git hub to find out that WSL wasn't picking up on changes to the PATH in /etc/environment and that any changes had to be made in the /etc/profile.d/myenvvars.sh file. I had to create the latter file and add
export PATH=~/sqlite:/usr/lib/tcl8.6:$PATH
and reboot WSL to get those directories on the PATH.

Edit: Going by https://packages.ubuntu.com/
tcl8.6 doesn't have the tclConfig.sh and tcl8.6-dev is needed.
2021-01-29
18:07 Reply: create sqlite3_analyzer from trunk (artifact: b7c8c4ec91 user: curmudgeon)
I initially thought the 'whereis' command searched all directories so it may have been tclConfig.sh existed under tcl8.6 and installing tcl8.6-dev was unnecessary.

My problems were also compounded by the fact I was accessing ubuntu via win10 WSL. It took days of investigation and a thread on git hub to find out that WSL wasn't picking up on changes to the PATH in /etc/environment and that any changes had to be made in the /etc/profile.d/myenvvars.sh file. I had to create the latter file and add
export PATH=~/sqlite:/usr/lib/tcl8.6:$PATH
and reboot WSL to get those directories on the PATH.
14:40 Reply: create sqlite3_analyzer from trunk (artifact: edd5d7c468 user: curmudgeon)
After reading a thread on stackoverflow I uninstalled tcl8.6 and then did 'sudo apt-get install tcl8.6-dev'. After that I found tclConfig.sh in '/usr/lib/tcl8.6' and added that to my PATH.

I ran this script

echo "updating trunk ..."
exec > /dev/null
fossil update trunk
configure
make
make sqlite3_analyzer
make sqldiff
make test

with response

updating trunk ...
ar: `u' modifier ignored since `D' is the default (see `U')
ar: `u' modifier ignored since `D' is the default (see `U')

So all working now :-)

The make test footnote was

SQLite 2021-01-27 19:15:06 9dc7fc9f04d5c14fc436e5ff5b4c06c1969ddde5857ebeb5dccd59b7c748c339
0 errors out of 249944 tests on sp4 Linux 64-bit little-endian
All memory allocations freed - no leaks
Maximum memory usage: 9196992 bytes
Current memory usage: 0 bytes
Number of malloc()  : -1 calls

Sublime stuff Richard. it's a wonder your head doesn't explode. I only started working with linux a couple of months back and if I was forced to eat a sweetie every tine I went off at a tangent I suspect I would've put on 30 stone by now.
14:35 Delete reply: create sqlite3_analyzer from trunk (artifact: 832b60950f user: curmudgeon)
Deleted
14:35 Edit reply: create sqlite3_analyzer from trunk (artifact: a2bcda03d1 user: curmudgeon)
Sorry, the above WARNINGs were from when I had tcl8.6 installed. I read on stackoverflow that installing tcl8.6-dev solved the missing tclConfig.sh problem.

The message after installing tcl8.6-dev and running

echo "updating trunk ..."
exec > /dev/null
fossil update trunk
configure
make

was

updating trunk ...
configure: error: /usr/include/tcl8.6 directory doesn't contain tclConfig.sh
make: *** No targets specified and no makefile found.  Stop.

whereis tclConfig.sh finds nothing. Am I supposed to create it? 

tom@sp4:/$ whereis tclConfig.sh
tclConfig:

EDIT: The configure error was down to me adding an incorrect --with to the configure command in the script. This post therefore only adds to the confusion so I'm deleting it after this edit.
10:21 Reply: create sqlite3_analyzer from trunk (artifact: c46298bbc7 user: curmudgeon)
Sorry, the above WARNINGs were from when I had tcl8.6 installed. I read on stackoverflow that installing tcl8.6-dev solved the missing tclConfig.sh problem.

The message after installing tcl8.6-dev and running

echo "updating trunk ..."
exec > /dev/null
fossil update trunk
configure
make

was

updating trunk ...
configure: error: /usr/include/tcl8.6 directory doesn't contain tclConfig.sh
make: *** No targets specified and no makefile found.  Stop.

whereis tclConfig.sh finds nothing. Am I supposed to create it? 

tom@sp4:/$ whereis tclConfig.sh
tclConfig:
2021-01-28
16:33 Reply: create sqlite3_analyzer from trunk (artifact: ff8df27204 user: curmudgeon)
Hi Richard, is the Makefile supposed to create the sqlite3_analyzer executable automatically? If so I suspect it's a TCL problem.

I installed TCL using 'sudo apt-get install tcl8.6-dev'.

I've got the trunk files in directory ~/sqlite. I run the following script from that directory

echo "updating trunk ..."
exec > /dev/null
fossil update trunk
configure
make

It does create sqlite3.c, sqlite3.o & sqlite3 but no sqldiff or sqlite3_analyzer. I get the following messages

updating trunk ...
configure: WARNING: Can't find Tcl configuration definitions
configure: WARNING: *** Without Tcl the regression tests cannot be executed ***
configure: WARNING: *** Consider using --with-tcl=... to define location of Tcl ***
ar: `u' modifier ignored since `D' is the default (see `U')

If I run 'make sqlite3_analyzer' I get

sqlite3_analyzer.c:233072:11: fatal error: tcl.h: No such file or directory
233072 | # include "tcl.h"
       |           ^~~~~~~
compilation terminated.
make: *** [Makefile:1308: sqlite3_analyzer] Error 1

I have spent 2 hours trying to find what I should add to the PATH so tcl can be accessed but I'm none the wiser.
10:29 Post: create sqlite3_analyzer from trunk (artifact: f1c7b03644 user: curmudgeon)
I've recently started using ubuntu and I've been practicing gcc by trying to compile the sqlite3.c amalgamation and the sqlite3, sqldiff & sqlite3_analyzer executables from the trunk source code. I've managed it all bar the sqlite3_analyzer. I see a sqlite3_analyzer.c.in file in the tools directory but it's contents don't really give me any clues. Anyone help?
2021-01-20
11:52 Reply: Need If column exist option in SQLite (artifact: 65a97afbee user: curmudgeon)

If you're checking from within c / c++ code

https://sqlite.org/c3ref/table_column_metadata.html

is probably the simplest way.

2021-01-18
15:56 Reply: Compiling amalgamation with gcc (artifact: f79c49313a user: curmudgeon)

Thanks Keith and anon.

More ↓