SQLite Forum

Timeline
Login

49 forum posts by user GSnyder

2021-09-30
22:39 Edit reply: How to filter find duplicate and show row with same field using sqlite code? (artifact: c6a3826af4 user: GSnyder)
Another possibility:

sqlite> create table a(id,name);
sqlite> insert into a values (1,'john'), (2,'john'), (3,'ann'), (4,'pete');
sqlite> select * from a where name in (select name from a group by name having count(*) > 1);

1|john
2|john

Gerry
18:23 Reply: How to filter find duplicate and show row with same field using sqlite code? (artifact: d152509e39 user: GSnyder)
Another possibility:

sqlite> create table a(id,name);
sqlite> insert into a values (1,'john'), (2,'john'), (3,'ann'), (4,'pete');
select * from a where name in (select name from a group by name having count(*) > 1);

1|john
2|john

Gerry
2021-09-27
22:03 Post: Tiny nit in ALTER TABLE documentation (artifact: 6afce10ce7 user: GSnyder)

In the final sentence of of the overview, only three options are mentioned.

RENAME COLUMN is missing.

Gerry Snyder

2021-09-15
14:30 Reply: about "strict" mode (artifact: 911c944756 user: GSnyder)

Yes, you can handle it either way you want.

Both are easy to do, and you can choose which depending on your needs.

2021-09-07
17:08 Reply: Segmentation fault in function isLikeOrGlob() (artifact: cec04777d4 user: GSnyder)

Dr. Hipp,

I can't think of a less productive way for you to spend your time than answering this, but I am curious how much simpler the query could have been and still trigger the fault.

Gerry Snyder

2021-08-21
20:06 Edit reply: about "strict" mode (artifact: 374cdfe6d1 user: GSnyder)

I can see advantages to having the mode controllable by PRAGMA, and I for one would be willing for the results to situation (2) be that existing tables with nonstandard data types be allowed to exist and be used, but any data insertion into them be an error.

Gerry Snyder

20:06 Reply: about "strict" mode (artifact: 5d8cb4e7f6 user: GSnyder)

I can see advantages to having the mode controllable by PRAGMA, and I for one would be willing for the results to situation (2) be that existing tables with nonstandard data types be allowed, but any data insertion into them be an error.

Gerry Snyder

2021-08-07
23:03 Reply: Not understanding a subquery - Some guidance? (artifact: ebab6e213c user: GSnyder)

Maybe you should move the Final close parenthesis to after the

t.ritm = r.ritm

2021-08-05
03:13 Reply: LEFT JOIN Misunderstanding (artifact: 4900e74360 user: GSnyder)

Thank you.

The examples are very instructive.

2021-07-12
20:47 Edit reply: Awesome people (artifact: 9f1ad95b8d user: GSnyder)

Igor Tandetnik has not been really active recently, but he has contributed a lot over the years, also.

Gerry Snyder

18:43 Reply: Awesome people (artifact: ea63b2e9ba user: GSnyder)

Igor Tandnetik has not been really active recently, but he has contributed a lot over the years, also.

Gerry Snyder

2021-06-26
05:28 Post: Use the Sessions extension from Tcl? (artifact: af5572546f user: GSnyder)

I feel that the answer should be obvious, but I am not sure whether it is obviously Yes or obviously No.

Since some of the C functions have arguments that are pointers, and Tcl does not have pointers, I think that, at the least, some kind of glue would be needed between the extension and Tcl. But my SQLite usage has been strictly Tcl, and I well could be missing something.

Any advice appreciated.

Gerry Snyder

2021-05-27
00:02 Reply: index to help with selection and ordering (artifact: 49e7be4b21 user: GSnyder)

Does

https://sqlite.org/queryplanner.html#_sorting_by_covering_index

help?

2021-05-25
18:39 Reply: Concurrency not working (artifact: 436fc023be user: GSnyder)

"Writers merely append new content to the end of the WAL file. Because writers do nothing that would interfere with the actions of readers, writers and readers can run at the same time. However, since there is only one WAL file, there can only be one writer at a time."

From Section 2.2 of: https://sqlite.org/wal.html#activating_and_configuring_wal_mode

2021-05-19
23:42 Edit reply: Novice question concerning tcl sqlite and eval options and reusing prepared statements (artifact: 2a45b00a04 user: GSnyder)

Gary,

Your first answer is correct. The prepared statement is stored with the space for the open variables to be filled in.

If you need to be really tricky, you can put the statement in quotation marks, and variables with dollar sign will be filled in immediately (by Tcl), and those with colon will not be filled in until actually executed.

Of course, unless you execute a statement a skazillion times, you won't see any difference in the timing. Tcl is slow, but not that slow. Disk access time dominates all.

Gerry

23:35 Reply: Novice question concerning tcl sqlite and eval options and reusing prepared statements (artifact: 07a9ff334c user: GSnyder)

Gary,

Your first answer is correct. The prepared statement is stored with the space for the open variables to be filled in.

If you need to be really tricky, you can put the statement in quotation marks, and variables with dollar sign will be filled in immediately (by Tcl), and those with colon will not be filled in until actually executed.

Of course, unless you execute a statement a skazillion times, you won't see any difference in the timing. Tcl is slow, but not that slow.

Gerry

2021-05-16
21:08 Reply: Novice question concerning tcl sqlite and eval options and reusing prepared statements (artifact: 1ff014eae4 user: GSnyder)

A partial response:

Nothing special is needed to re-use prepared statements. And no need to reset. By default 10 statements will be prepared and cached, and it is all automatic.

I had done a lot of stuff with Tcl and SQLite before TDBC came along, and I have not used it. If I did, it would probably be to be able to use the same code for SQLite and other DB systems. But it does look pretty neat.

Hope this helps, at least a little,

Gerry Snyder

2021-05-11
18:13 Post: How powerful is SQLite? (artifact: 8d5851d326 user: GSnyder)

Dr. Hipp,

As I remember, SQLite started as a means of having a local copy of data from a client-server DB system, when even that level of data transmission was slow.

I am curious whether you could compare the capabilities of SQLite now with that old client-server system.

I suspect that the child has far surpassed the parent.

Gerry Snyder, SQLite user since the very late days of SQLite2

PS Feel free to delete if Dr. Hipp declines to comment

2021-05-01
00:30 Reply: SQLite database in space? (artifact: d942c82b38 user: GSnyder)

Does linux necessarily include SQLite?

If so, then "The Ingenuity helicopter runs an embedded Linux distribution on its navigation computer" (from https://github.com/readme/nasa-ingenuity-helicopter ) would imply SQLite is on Mars.

2021-04-07
22:19 Reply: Saving Wikipedia to SQLite (artifact: f735ac99a9 user: GSnyder)

I had wondered whether the book was worth your time. I am glad that it seems so.

I learned a lot from it, but the one specific I remember was not SQLite-specific. It was the more general observation that having a lot of tables with the same schemas (schemata?) might be a bad idea.

It took me a bunch of hours to fix it, but my code ended up much cleaner.

Thank you, Jay.

Gerry Snyder

2021-03-25
23:46 Edit reply: possible to use output of a SELECT-query for ordering? (artifact: 6b35f11ffc user: GSnyder)

I am not sure how what you want should work.

ORDER BY needs to be something that pertains to stuff in the outer SELECT.

Can you give an example of your real need?

23:44 Reply: possible to use output of a SELECT-query for ordering? (artifact: f08d53b58b user: GSnyder)

I am not sure how what you want should work.

ORDER BY needs to something that selects stuff in the outer SELECT.

Can you give an example of your real need?

2021-03-23
17:17 Reply: The opposite of AUTOINCREMENT (artifact: aafa097f45 user: GSnyder)

If the real requirement is just to keep the id small, can't you just let SQLite do its thing?

Do you know that that is not good enough?

Gerry Snyder

2021-03-20
00:11 Reply: Proper set up for working with SQLite, C, Tcl/Tk? (artifact: b56fa23aac user: GSnyder)

I have no great love for either Win or Visual Studio, but they (and magicsplat) make it trivial to compile the Tcl bindings.

2021-03-19
23:26 Reply: Error compiling Tcl binding with ICU (artifact: 42c0108eb1 user: GSnyder)

Thank you, Keith.

That certainly makes sense, but I had not realized it.

23:05 Reply: Error compiling Tcl binding with ICU (artifact: c0244b518f user: GSnyder)

I did not actually set up ICU (as far as I knew, the amalgamation included everything needed), and I just did the Visual Studio 2019 compile for Win 10 that worked without ICU.

22:58 Edit: Error compiling Tcl binding with ICU (artifact: 3519397ff8 user: GSnyder)

After compiling the vanilla 3.35.2 Tcl binding, I modified the makefile.vc to include the new math functions, and that went well. Then I added ICU, and got an error:

....sqlite3.c(197501): fatal error C1083: Cannot open include file: 'unicode/utypes.h': No such file or directory

What am I doing wrong? I used magicsplat for the Tcl stubs.

Gerry Snyder

22:57 Post: Error compiling Tcl binding with ICU (artifact: 0418fcef25 user: GSnyder)

After compiling the vanilla 3.35.2 Tcl binding, I modified the makefile.vc to include the new math functions, and that went well. Then I added ICU, and got an error:

....sqlite3.c(197501): fatal error C1083: Cannot open include file: 'unicode/utypes.h': No such file or directory

What am I doing wrong?

Gerry Snyder

2021-02-12
20:44 Reply: SQLLite the right choice for storing web game data for many users (artifact: dfb73813c4 user: GSnyder)

I think it would make more sense to have a single database with the game info for all the players. With appropriate indices queries almost certainly would be more than fast enough.

Gerry Snyder

2021-01-19
18:37 Reply: 2 queries asking the same, give different answers (artifact: 93a78b5bf0 user: GSnyder)

If you are still struggling, try

SELECT MAX(fld0), typeof(fld0), fld1, typeof(fld1)

in each of your queries

It seems that either the database is corrupt, or not all data are the same type.

2021-01-16
18:34 Edit reply: 2 queries asking the same, give different answers (artifact: 5c21dc6d2a user: GSnyder)

Try

SELECT MAX("fld0"), fld0 FROM table EXCEPT SELECT MAX("fld0"), fld0 FROM table where ("fld1" = 'val1' or "fld1" = 'val2' or "fld1" = 'val3');

and see if that gives any clue as to what is going on.

18:08 Reply: 2 queries asking the same, give different answers (artifact: 6536362d41 user: GSnyder)

Try

SELECT MAX("fld0") FROM table EXCEPT SELECT MAX("fld0") FROM table where ("fld1" = 'val1' or "fld1" = 'val2' or "fld1" = 'val3');

and see if that gives any clue as to what is going on.

2021-01-01
15:42 Reply: Checking database took about 350 more time as normal (artifact: 63c359dda5 user: GSnyder)

Overactive virus "protection"?

Gerry Snyder

2020-12-24
16:39 Reply: Interesting question: Why aren't you using SQLite more? (artifact: 9ad99739a8 user: GSnyder)

I can't imagine any software using SQLite "to its full potential."

I would guess that most programmers just solve their specific problems with it, and don't worry about the bajillion additional things the library is doing for others.

I use SQLite with Tcl, and have found that the official documentation pretty much all I have needed, with additional help from the old email list three or four times over the couple decades I have been using it.

I am not sure that additional documentation would have helped me very much.

Gerry Snyder

2020-12-09
16:26 Reply: Visual Studio Compile Issue (artifact: 0a17fdc4ac user: GSnyder)

I had a vaguely similar situation when compiling the Tcl interface a few days ago.

My problem was that I was doing the compile in a VS PowerShell instead of a VS Command Prompt.

Probably not your situation, but I thought it worth mentioning.

Gerry Snyder

2020-10-12
17:41 Reply: "ALTER TABLE ..." looks at other tables (artifact: 6faca79b79 user: GSnyder)
Thank you.

It had seemed about a googol times more likely that my s/w was doing something stupid, or that my db had been corrupted (also by my s/w), than that I had found a real problem.

I feel relieved.

Gerry Snyder
2020-10-11
22:30 Reply: "ALTER TABLE ..." looks at other tables (artifact: bf1a80b041 user: GSnyder)
sqlite> .schema
CREATE TABLE blobs(filename text, length integer, time text default current_timestamp, content blob);
CREATE TABLE testnocase(a text collate nocase);
CREATE TABLE tc(procname text not null on conflict ignore,tcl text,version text,comments text);
CREATE TABLE gigo(a text,--comment
b int,C real,aa blob,"ggg integer" int, "ggg int" int default 0 not null, abc, xyz, abcxyz, unique(c,aa));
CREATE TABLE norowid(a text, b text primary key) WITHOUT ROWID;
CREATE TABLE norowid2(a text, b text, primary key(a,b)) WITHOUT ROWID;
CREATE TABLE CenturyClub_countries (country text, visited text, region text);
CREATE TABLE CenturyClub_regions (region text);
CREATE TABLE animal(type char (100),sound char(100));
CREATE TABLE ot(RegistrationYear,IntroductionYear,Status,Iris_Name,Hybridizer,Ctry,SeedlingNumber,Class,HeightInches,Season,Introducer,Description,Pedigree_Exporter_No_Period);
CREATE TABLE cmd_history(cmdtype text,tablename text,created default current_timestamp,command text);
CREATE TABLE ancestors_GCS (depth integer, id integer, name_etc text, America_born integer);
CREATE TABLE tclcode(procname text not null on conflict ignore unique on conflict ignore,version text,hidden integer default 0,tcl text,comments text);
CREATE TABLE ancestors1_GCS (depth integer, id integer, name text, birthdate text, birthplace text, America_born integer);
CREATE TABLE OED_index(headword text,position text,length text);
CREATE TABLE OED_index_collated(headword text,position text collate compare64,length text);
CREATE TABLE OED_index_sorted(headword text,position text collate compare64,length text);
CREATE TABLE abc(xyz collation missing);
CREATE TABLE def(ghi, jkl);
CREATE UNIQUE INDEX cmd_history_index ON cmd_history(cmdtype, tablename, command) WHERE cmdtype != 'patch';
CREATE INDEX OED_index_index ON OED_index(headword);
CREATE VIEW procname as select procname from tclcode
/* procname(procname) */;
CREATE VIEW hidden_views as select command from cmd_history where cmdtype = 'view' and tablename = 'temp'
/* hidden_views(command) */;
CREATE VIEW dict_view AS SELECT * from OED_index
/* dict_view(headword,position,length) */;
CREATE VIEW ancestor_depth AS SELECT COUNT(*) FROM ancestors1_GCS GROUP BY depth
/* ancestor_depth("COUNT(*)") */;
CREATE VIEW ancestor_depth_American AS SELECT depth, COUNT(*) FROM ancestors1_GCS WHERE America_born = 1 GROUP BY depth
/* ancestor_depth_American(depth,"COUNT(*)") */;
sqlite> alter table gigo rename to ggiiggoo;
Error: error in table OED_index_collated: no such collation sequence: compare64
21:03 Post: "ALTER TABLE ..." looks at other tables (artifact: 9d784e5028 user: GSnyder)

My db has a table that I don't use very often that has a custom collation (in Tcl), which I load only when I am going to be using that table. This has not caused any problems until now.

I have been doing some things with the native ALTER TABLE command and it seems that ADD COLUMN is not a problem, but both RENAME TO and RENAME COLUMN fail with errors because of the missing collation in another table.

Is this expected?

I tried creating a tiny example, but the problem did not arise.

The file the situation occurs in passes the INTEGRITY_CHECK, and I use it all the time, but there could be some problem that the check does not see.

My file is 137,312 KB, and I can send it or a link to it if you (drh) care to look into it.

Gerry Snyder

2020-10-09
20:13 Edit reply: joining tables on multiple conditions; how to do it when second table lacks some rows? (artifact: 40a39961d9 user: GSnyder)

If I understand your problem correctly, using a:

LEFT OUTER JOIN

is what you need to do. It returns all the rows from the left-hand table, with matching data from the right-hand table if there is any, and NULL if there is not.

18:59 Reply: joining tables on multiple conditions; how to do it when second table lacks some rows? (artifact: 9143366b5b user: GSnyder)

If I understand your problem correctly, using a:

LEFT OUTER JOIN

is what you need to do.

2020-09-20
17:41 Reply: 64-bit Win compile of Tcl interface, revisited (artifact: d1a352ef87 user: GSnyder)

Thank you for your response.

apn found what I was doing wrong. I needed to run vcvars64.bat and then do the nmake in the same command window. Without that I was doing a 32-bit compile using a 64-bit Tcl.

I am a happy camper now.

Gerry

04:00 Post: 64-bit Win compile of Tcl interface, revisited (artifact: 2dc6133293 user: GSnyder)
I have paused trying MSYS2, and am seeing if MSVC might be easier. I have magicsplat Tcl installed in c:/Program Files/Tcl which is the rules.vc default location.

The compilation seems fine, but the link step fails. Below is the output from running nmake:

sqlite-autoconf-3330000\tea\win> nmake /f makefile.vc

Microsoft (R) Program Maintenance Utility Version 14.27.29111.0
Copyright (C) Microsoft Corporation.  All rights reserved.

===============================================================================
*** Compiler has 'Optimizations'
*** Compiler does not have 'Pentium 0x0f fix'
*** Linker does not have 'Win98 alignment problem'
*** Intermediate directory will be '.\Release_VC13\tclsqlite3_ThreadedDynamic'
*** Output directory will be '.\Release_VC13'
*** Suffix for binaries will be 't'
*** Optional defines are '-DTCL_CFGVAL_ENCODING=\"cp1252\" -DSTDC_HEADERS -DTCL_THREADS=1 -DUSE_THREAD_ALLOC=1 -DNDEBUG -DTCL_CFG_OPTIMIZED'
*** Compiler version 13. Target machine is IX86
*** Host architecture is AMD64
*** Compiler options '-W3 -DUNICODE -D_UNICODE  -Ot -Oi -fp:strict -Gs -GS -GL  -RTC1 -W3'
*** Link options '-ltcg'
        cl -DBUILD_sqlite -DUSE_TCL_STUBS  -DPACKAGE_VERSION="\"3.33.0\"" -nologo -c -W3 -D_CRT_SECURE_NO_WARNINGS -YX -Fp.\Release_VC13\tclsqlite3_ThreadedDynamic\ -O2 -Op -Gs -MD  -I"C:\Program Files\Tcl\lib\..\include" -I"..\win"  -I"..\generic" -I"..\.."  -DSQLITE_3_SUFFIX_ONLY=1 -DSQLITE_ENABLE_RTREE=1  -DSQLITE_ENABLE_FTS3=1 -DSQLITE_OMIT_DEPRECATED=1  -DTCL_CFGVAL_ENCODING=\"cp1252\" -DSTDC_HEADERS -DTCL_THREADS=1 -DUSE_THREAD_ALLOC=1 -DNDEBUG -DTCL_CFG_OPTIMIZED -DBUILD_tclsqlite3 -Fo.\Release_VC13\tclsqlite3_ThreadedDynamic\  -c ..\generic\tclsqlite3.c
cl : Command line warning D9002 : ignoring unknown option '-YX'
cl : Command line warning D9002 : ignoring unknown option '-Op'
tclsqlite3.c
        link -nologo -machine:IX86 -release -opt:ref -opt:icf,3 -dll -out:.\Release_VC13\tclsqlite3.dll "C:\Program Files\Tcl\lib\..\lib\tclstub86.lib" @C:\Users\mesme\AppData\Local\Temp\nm32A0.tmp
   Creating library .\Release_VC13\tclsqlite3.lib and object .\Release_VC13\tclsqlite3.exp
tclsqlite3.obj : error LNK2019: unresolved external symbol _Tcl_InitStubs referenced in function _Sqlite3_Init
tclsqlite3.obj : error LNK2019: unresolved external symbol _tclStubsPtr referenced in function _incrblobClose
.\Release_VC13\tclsqlite3.dll : fatal error LNK1120: 2 unresolved externals

I have no idea what to do about the missing symbols.
2020-09-15
20:13 Reply: 64-bit Win compile of Tcl interface (artifact: b54fddc132 user: GSnyder)

Still don't have a working dll, but making progress.

The problem I ran into yesterday was caused by an error in the Makefile created by ./configure -- it set SHLIB_LD to "" (blank).

I edited the Makefile line to

SHLIB_LD = ${CC} -shared

which is what the 32-bit Makefile has, and running make completed and created libsqlite3.33.0

It won't load, and has the error message "this library or a dependent library could not be found in library path." I am pretty sure the pkgIndex is what it should be.

I don't know what I can do about this other than see if any compile options were incorrect, but I may try to figure what went wrong with the configure script.

04:25 Edit reply: 64-bit Win compile of Tcl interface (artifact: ea00e37047 user: GSnyder)

Thank you for the pointer. I was not aware of MSys2.

I have made some progress, but still not there. Installed MSys2, and added packages for make and gcc.

In a MINGW64 window ./configure runs, and mingw32-make gets started, but terminates with:

./../sqlite3.c:43082:22: warning: ‘sqlite3Win32Wait’ defined but not used [-Wunused-function] 43082 | SQLITE_PRIVATE DWORD sqlite3Win32Wait(HANDLE hObject){ | ^~~~~~~~~~~~~~~~

rm -f libsqlite3.33.0 o libsqlite3.33.0 tclsqlite3.o -lpthread -Wl,-ltclstub8.6 process_begin: CreateProcess(NULL, o libsqlite3.33.0 tclsqlite3.o -lpthread -Wl,-ltclstub8.6, ...) failed. make (e=2): The system cannot find the file specified. mingw32-make: [Makefile:263: libsqlite3.33.0] Error 2 (ignored) : libsqlite3.33.0

and does not create the dll file.

I will have to compare the outputs from the 32-bit and 64-bit versions of the make command and try to see what went wrong.

So thank you for getting me going with MSys2.

Gerry

04:24 Edit reply: 64-bit Win compile of Tcl interface (artifact: 15c6706fb8 user: GSnyder)

Thank you for the pointer. I was not aware of MSys2.

I have made some progress, but still not there. Added packages for make and gcc.

In a MINGW64 window ./configure runs, and mingw32-make gets started, but terminates with:

./../sqlite3.c:43082:22: warning: ‘sqlite3Win32Wait’ defined but not used [-Wunused-function] 43082 | SQLITE_PRIVATE DWORD sqlite3Win32Wait(HANDLE hObject){ | ^~~~~~~~~~~~~~~~

rm -f libsqlite3.33.0 o libsqlite3.33.0 tclsqlite3.o -lpthread -Wl,-ltclstub8.6 process_begin: CreateProcess(NULL, o libsqlite3.33.0 tclsqlite3.o -lpthread -Wl,-ltclstub8.6, ...) failed. make (e=2): The system cannot find the file specified. mingw32-make: [Makefile:263: libsqlite3.33.0] Error 2 (ignored) : libsqlite3.33.0

and does not create the dll file.

I will have to compare the outputs from the 32-bit and 64-bit versions of the make command and try to see what went wrong.

So thank you for getting me going with MSys2.

Gerry

00:32 Reply: 64-bit Win compile of Tcl interface (artifact: 5d1cdc437b user: GSnyder)

Thank you for the pointer. I was not aware of MSys2.

But I am still having at least roughly the same problem: I can't run the "make" command. Even after finding and installing the GNU make package. Keep getting "bash: make: command not found"

It's getting late. Maybe I'll be smarter tomorrow.

But thanks again.

Gerry

2020-09-14
22:29 Post: 64-bit Win compile of Tcl interface (artifact: 073435f7c2 user: GSnyder)

For years I have done 32-bit compilations of the Tcl interface using MSYS/MinGW, with ./configure and make.

I want to the the same but 64-bit. There is MinGW-w64, but I have not been able to figure out to use it. It does not come with MSYS. I copied the msys folder from the 32-bit MinGW, and ran a compile from there. It seemed to run, but a 64-bit tclkit did not like the dll file. The error message suggested a possible 32/64-bit mismatch.

Dependency Walker shows the top level as KERNEL32.DLL

Any suggestions for how to fix this, or a better way to go?

2020-04-21
16:56 Reply: tcl / db eval error (artifact: 25d05b8f2a user: GSnyder)

The [join ...] command creates the following string:

-- This is a commentnSELECT * FROM t1 ORDER BY a

I don't think this is what you want.

2020-03-27
17:39 Reply: Natural sort order (artifact: d5cf6c6317 user: GSnyder)

I don't suppose that "dictionary" order could include accented letters too, so that é would sort next to e?