SQLite Forum

Timeline
Login

25 forum posts by user holgerj

2021-11-14
12:14 Reply: A newbie's question about database structure design (artifact: 29a1161684 user: holgerj)

The suggestion is exactly what is usually being done by "materialized views", that is, physical table which have a statement to refresh them.

But of course you can use ordinary tables and store the end-of-day stock quotes there, resulting in exactly one row per stock and day.

After a while, the intra-day quotes are of no interest any more, so you may as well keep them elsewhere or totally delete them.

2021-10-10
12:25 Reply: x86_64/amd64 sqlite3 binaries (artifact: 7c26c30129 user: holgerj)

The discussion here was about Linux, not about other platforms. Therefore, 64bit is key. At least on the desktop and servers.

2021-10-09
12:21 Reply: x86_64/amd64 sqlite3 binaries (artifact: aba1013ae0 user: holgerj)

Yes, it's difficult to understand that the default isn't 64bit nowadays.

I'd think that it's rather problematic to find 32bit hardware to buy.

All major Linux distributions are available in 64bit only.

2021-08-13
12:21 Reply: Test ACID properties SQLITE (artifact: 737fbec672 user: holgerj)

Almost every Android and iOS app has at least one SQLite DB. Per phone 50 to 100 DBs is quite common.

2021-06-30
13:15 Reply: Loading bundled extensions in dotnet on linux (artifact: b7e78a4fd0 user: holgerj)

Probably the file your code tries to load SQLite.Interop.dll.so is actually named SQLite.Interop.so

Don't add the Windows specific file name extension on the Linux platform.

13:11 Reply: Very Newbie Question (artifact: 535bdf3bd5 user: holgerj)

It would be very nice if you stated what your question is about instead of classifying it as a "newbie question".

2021-05-28
12:39 Reply: nested case statement not working (artifact: 8dcc7ad8fd user: holgerj)

I know this link is to PostgreSQL's documentation, but I reckon the hint is valid for all current SQL databases, esp. SQLite:

https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_char.28n.29

Don't use the type char(n). You probably want text.

Why not?

Any string you insert into a char(n) field will be padded with spaces to the declared width. That's probably not what you actually want.

(... explanation continues ...)

2021-05-21
12:55 Reply: Column name includes quotes used in RETURNING expr (artifact: 6724f28e07 user: holgerj)

Actually 't' is a string literal, "t" is an object name (here: a column name), while `t` is not defined in SQL.

SQL doesn't use (square) brackets [], (curly) braces {}, backticks ``, because is quite reduced using special characters.

2021-05-04
06:37 Reply: "Office Space"... I'm losing all those pennies (artifact: 1ad02cb8a7 user: holgerj)

That's exactly the point. The SQL standard is very clear about the data types NUMERIC and DECIMAL (which are identical) to do decimal arithmetic and are painfully missed in SQLite.

2021-04-21
12:40 Reply: The NUMERIC data type? (artifact: 0b84318a48 user: holgerj)

According to https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL (which is not itself the standard)

The types decimal and numeric are equivalent. Both types are part of the SQL standard.

It's really a pity that SQLite doesn't implement a true DECIMAL/NUMERIC data type, because all binary floating point types are not precise and therefore unusable for commercial calculations, and all integer type lack digits after the decimal point.

2021-02-14
12:34 Reply: SQLite isolation clarification needed (artifact: a5e327668b user: holgerj)

Just use the sqlite3 executable for your experiments, leaving out any language specific drivers and wrappers.

Several transactions over the same connection cannot happen at the same time, only one after the other.

2021-02-01
12:14 Reply: How should RETURNING work with a cascading delete? (artifact: 4ca09d6125 user: holgerj)

Do it the same way PostgreSQL does.

2021-01-28
12:17 Reply: Are there any plans / chances of supporting a RETURNING clause? (artifact: cf1749029a user: holgerj)

+1

If there is anybody who doubts that this is a useful feature, these are good readings: https://www.postgresql.org/docs/current/dml-returning.html https://dzone.com/articles/the-returning-keyword-in-postgresql

It saves a lot of round trips to the database after INSERTs and UPDATEs and allows deleting rows from one table while immediately inserting them into another. Advantages:

  • Data are not transferred between client and server (in case of SQLite between the lib and the application).
  • No explicit transaction is necessary as the whole statement is an autocommit transacation.
2020-12-22
13:29 Reply: bad plan for a query with an OR condition (artifact: 6fdb48f661 user: holgerj)

PostgreSQL 13 shows the behaviour below. Of course, the data type had to be a proper BOOLEAN, since an INT cannot be checked for truth. And I added an ORDER BY 1 to allow for easier comparison.

CREATE TABLE t(x INT,c1 boolean,c2 boolean);
INSERT INTO t VALUES
  (1,false,false),
  (2,true,false),
  (3,false,true),
  (4,true,true);

SELECT x FROM t 
WHERE c1 OR c2 
ORDER BY 1;
┌───┐
│ x │
├───┤
│ 2 │
│ 3 │
│ 4 │
└───┘


SELECT x fROM t WHERE c1
UNION ALL 
SELECT x FROM t WHERE c2
ORDER BY 1;

┌───┐
│ x │
├───┤
│ 2 │
│ 3 │
│ 4 │
│ 4 │
└───┘


SELECT x fROM t WHERE c1
UNION
SELECT x FROM t WHERE c2
ORDER BY 1;


┌───┐
│ x │
├───┤
│ 2 │
│ 3 │
│ 4 │
└───┘
2020-12-20
15:48 Reply: 'ON DELETE CASCADE' seems to not be working (artifact: 88a426c0e4 user: holgerj)

Regarding the table itself it may be synonymous, but when referencing a table with ''referencing tablename'' it will always and only refer to the primary key.

Referencing anything only ''unique'' requires ''refererencing tablename(col1,col1)''.

2020-12-19
18:44 Reply: 'ON DELETE CASCADE' seems to not be working (artifact: 7b75765b26 user: holgerj)
Not in SQLite, but in DB2, PRIMARY KEY without NOT NULL produces an error telling you to add NOT NULL.
2020-12-15
15:06 Reply: Change from SQL Server CE to SQLite. Something similar to SQLCEResultSet and SQLCEUpdatableRecord. (artifact: 6eaff4217d user: holgerj)

We don't even know what kind of client or programming language you are using. All these things depend on this and on the driver involved.

Plus, it's quite unlikely that there are many people with knowledge about your previous development environment.

If you supply more information, we might be able to help.

2020-12-06
16:41 Reply: SQLite 3.34 binary not working on Ubuntu 20.04 (not found) (artifact: 067ffb55aa user: holgerj)

Yes, the file /lib/ld-linux.so.2 isn't present on my system.

I still wonder why the message said that sqlite3 was missing instead of /lib/ld-linux.so.2 was missing.

Of course that's nothing sqlite3 can do about.

But there should be a very clear hint on the downloads page that it is a 32 bit version, because that's nothing anybody would expect nowadays.

And if there were two versions - 32 and 64 bit - that would be ideal.

16:39 Reply: SQLite 3.34 binary not working on Ubuntu 20.04 (not found) (artifact: fd3a87777e user: holgerj)

Since I tried to start it with ./sqlite3 the directory doesn't matter, as long as it is the current one.

And yes, the problem was 32 bit - see my answer above.

16:37 Reply: SQLite 3.34 binary not working on Ubuntu 20.04 (not found) (artifact: d1c0e0d975 user: holgerj)

Yes, now I checked and found

$ file sqlite3
sqlite3: ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), dynamically linked, interpreter /lib/ld-linux.so.2, for GNU/Linux 4.3.0, stripped

I wonder why

  • a 32 bit executable is provided as the Linux version, while most distributions aren't even available in 32 bit versions,
  • why the system doesn't give a proper message saying that a 32 bit executable cannot be run on the current system.
2020-12-05
17:42 Reply: SQLite 3.34 binary not working on Ubuntu 20.04 (not found) (artifact: 0e65ee081b user: holgerj)

Sorry, but no. The behaviour is the same.

The message doesn't complain about a missing library, even the previous executable was statically linked. It complained that the executable itself wasn't there, although ls and md5sum clearly proved that it existed.

And of course Ubuntu has zlib installed (not the source/dev version, of course), because many programs need it.

2020-12-04
10:51 Post: SQLite 3.34 binary not working on Ubuntu 20.04 (not found) (artifact: 25971b2267 user: holgerj)

After downloading and unpacking sqlite-tools-linux-x86-3340000.zip I find the following behaviour:

hj@tuxedo:/usr/local/bin$ ls -l sqlite3
-rwxrwxr-x 1 root hj 1014308 2020-12-04 11:25 sqlite3
hj@tuxedo:/usr/local/bin$ ./sqlite3
bash: ./sqlite3: No such file or directory
hj@tuxedo:/usr/local/bin$ md5sum sqlite3
7f85edb0fc9c302c7d0e543b5eda1654  sqlite3
hj@tuxedo:/usr/local/bin$ ldd sqlite3
	not a dynamic executable

Downloading the autoconf-sources, configure, make produces executable sqlite3.

10:30 Reply: libreadline problem with Ubuntu 20.04 (artifact: 803374b4d5 user: holgerj)

I expect the binary to have been linked against libraries available in major flavors of Linux.

This has been the case for many years, and I cannot find a reason to deviate from this policy.

2020-11-26
12:13 Reply: Proposal: Add the SUBSTRING alias to the SUBSTR function (artifact: 9a244bbd23 user: holgerj)

PostgreSQL also supports the ANSI/ISO standard version. It's a bit verbose, but standard is standard.

2020-11-19
13:44 Post: libreadline problem with Ubuntu 20.04 (artifact: 68fdfa584e user: holgerj)
The SQLite binary 3.31 for Linux seems not to recognise the libreadline libraries available on Ubuntu 20.04

  $ apt-cache search libreadline
  libreadline5 - GNU readline and history libraries, run-time libraries
  libreadline8 - GNU readline and history libraries, run-time libraries

I had to copy libreadline.so.7 from an 18.04 machine in order to get the readline functionality again.