SQLite Forum

Timeline
Login

36 forum posts by user tonyp

2020-06-14
20:03 Reply: storing number with 2 decimal place (artifact: 2cdc1a86c2 user: tonyp)

Nothing wrong with it. But the OP asked how to store that way.

08:06 Reply: storing number with 2 decimal place (artifact: eb45ead5ec user: tonyp)

Save it as string.

create table t(s);
insert into t values(printf('%.2f',0));
select * from t;
2020-06-11
18:44 Post: .mode html fails on html content (artifact: ceba329da6 user: tonyp)

I have a table where one column needs to be a clickable HTML link.

Something like: <a href=http://www.hatev.er>http://www.hatev.er</a>

When using .mode html to create the result set, the HTML column content is escaped losing correct display and click-ability.

Any way for .mode html to leave existing HTML code alone?

Or, if I have the link as plain text, some way to automatically convert it to clickable link within the resulting HTML table?

Can the SQLite3 shell do this without having to resort to external scripting?

Thank you.

2020-05-29
15:52 Reply: Minor documentation fixes (artifact: 66f3ebc8a9 user: tonyp)

Section 1.1 of this page, end of second to last paragraph.

  • "though only a percent of or two"
2020-05-27
14:06 Reply: SQLITE_HAS_CODE is gone? (artifact: dd056b6f40 user: tonyp)

I have to make sure that any VFS edition of the encryption is 100% backward compatible

Or, it may be simpler to offer your clients an old-encryption and new-encryption SQLite3 shell to pipe a dump from old to new encrypted DB before using your updated encryption application. This also frees you to use whatever incompatible encryption you decide from now on.

2020-05-26
13:17 Post: Minor documentation fixes (artifact: 7d5efd3a63 user: tonyp)

Paragraph 2.1 of this page:

  • "SQLite current supports" => "SQLite currently supports"
  • "is upgraded to write transaction" => "is upgraded to a write transaction"
  • "an historic snapshot the database" => "an historic snapshot of the database"

Paragraph 2.2:

  • "without waiting for a writes statement" => "without waiting for a write statement"
2020-05-25
13:20 Reply: SQLDIFF not showing true differences (artifact: 6f7d12c16f user: tonyp)

Could an UPDATE affecting only case on a COLLATE nocase column be validly ignored as an optimization?

Good point, and ... obviously not!

Database content has two primary uses, retrieval and display. The collate nocase should only affect retrieval. Otherwise, the database engine would be free to convert all text of a collate nocase column to either all-lower or all-upper case for storage. (This would actually provide micro-optimizations in search speed [case-sensitive only comparisons] and required storage [better compression of a smaller char set].)

But, you need to preserve the actual case at least for display purposes. And although I can understand the 'logical' perspective argument, I consider SQLDIFF's failure to update case-only changes of collate nocase columns, incorrect behavior from the 'practical' perspective.

And if an option is added to allow both scenarios, the default (IMO) should be to ignore collate nocase and give a 'practical' DB difference which is what I believe most users would expect to see.

BTW, from SQLDIFF's help screen:

Output SQL text that would transform DB1 into DB2.

Well, not really, as I won't get identical data displays from either db.

2020-05-23
00:24 Post: Minor documentation error (artifact: 1bee587194 user: tonyp)

IIF mentions the function as iff instead of iif in the second sentence.

2020-05-20
16:29 Reply: SQLDIFF not showing true differences (artifact: fedeaeb58c user: tonyp)

In that case could there at least be some option to ignore collate nocase?

Or else, how can one create 'patch' files to update one database from another?

16:18 Post: SQLDIFF not showing true differences (artifact: 2388863e15 user: tonyp)

If a column has collate nocase, SQLDIFF utility fails to report any differences.

Database a.db:

CREATE TABLE t(s collate nocase);
INSERT INTO t VALUES('sample');

and database b.db:

CREATE TABLE t(s collate nocase);
INSERT INTO t VALUES('SAMPLE');

Now, use SQLDIFF a.db b.db and you won't see any differences. This is because of the collate nocase as when you remove it, the difference is shown.

I don't know if this is intentional behavior but to me it appears wrong. I expect a difference tool to show true differences between two databases.

The use of collate nocase may be used for case-insensitive retrieval but if the actual contents are different, a difference tool should report that.

2020-05-15
07:17 Edit reply: Feature request for command shell: External editing of schema (artifact: 823d039dd9 user: tonyp)

Philosophical question: Can an anonymous user second a request from either an anonymous or eponymous user considering the latter could be the same person? :)

(Also, can an eponymous user second an anonymous request?)

06:57 Reply: Feature request for command shell: External editing of schema (artifact: 3e36440a8c user: tonyp)

Philosophical question: Can an anonymous user second a request from either an anonymous or eponymous user considering the latter could be the same person? :)

2020-05-13
18:55 Edit: Shell .mode column width auto adjustment (artifact: 35494a8516 user: tonyp)

I don't know if this issue has come up before but here it goes.

Currently, (for .mode column at least) there seems to be a very simple 'heuristic' calculation of a column's width based on the first row's contents.

This has at least two problems:

  1. If the first row's column widths are not representative of the majority of rows (i.e., either much shorter or much longer than average width), the 'wrong' widths are used.

  2. An identical columns query result will end up with different column widths if only ordered differently (for example).

With ad-hoc queries, normally one adjusts column widths with the .width shell command, manually. (I don't consider the script case here as there is no problem there to have pre-configured .width commands all over the script.)

The problem with it is that for each different query a new adjusted .width command has to be issued.

To illustrate:

.mode column

create table t(s varchar(30));
insert into t values('A longer value ending here!');        -- length 27
insert into t values('Short value!');                       -- length 12

select * from t order by length(s) desc;
-- s
-- ---------------------------
-- A longer value ending here!
-- Short value!

select * from t order by length(s);
-- s
-- ------------
-- Short value!
-- A longer val

As you can see the second result has truncated much of the column's content.

For comparison, MySQL produces this:

+-----------------------------+
| s                           |
+-----------------------------+
| A longer value ending here! |
| Short value!                |
+-----------------------------+
2 rows in set (0.01 sec)

+-----------------------------+
| s                           |
+-----------------------------+
| Short value!                |
| A longer value ending here! |
+-----------------------------+
2 rows in set (0.13 sec)

MySQL seems to have a solution that works quite well. I don't know the internals of how they do it so I'm purely guessing here that it keeps track of each column's max content length (not the definition but the actual content) as rows are fetched/generated during query processing.

Would this (not the actual method of doing it but the effect) be something that SQLite3 library could eventually support?

Otherwise, a couple of suggestions for poor man's alternatives:

  1. If a length is specified for a column (such as varchar(10) or int(5) which is ignored anyway by SQLite3 so there cannot be any compatibility issues), this could become a hint for the expected width of the column overriding the currently otherwise heuristically assumed width.

    I think this way it will become very simple to get visually consistent query results for the same columns (at least for simple queries) without the need for .width.

    I understand there are cases where it becomes more involved (like a||b where widths could probably be added), or derived view columns, which makes the 'count-as-you-go' method the only [?] possibility. But, at least wherever the heuristic can work the results will be more visually pleasing than the current way.

  2. Buffer a few rows (e.g., 10) at the shell level before displaying the results and calculate the columns' widths based on their content average/max length.

Thanks for reading this far!

18:52 Edit: Shell .mode column width auto adjustment (artifact: 8b0cd79b84 user: tonyp)

I don't know if this issue has come up before but here it goes.

Currently, (for .mode column at least) there seems to be a very simple 'heuristic' calculation of a column's width based on the first row's contents.

This has at least two problems:

  1. If the first row's column widths are not representative of the majority of rows (i.e., either much shorter or much longer than average width), the 'wrong' widths are used.

  2. An identical columns query result will end up with different column widths if only ordered differently (for example).

With ad-hoc queries, normally one adjusts column widths with the .width shell command, manually. (I don't consider the script case here as there is no problem there to have pre-configured .width commands all over the script.)

The problem with it is that for each different query a new adjusted .width command has to be issued.

To illustrate:

.mode column

create table t(s varchar(30));
insert into t values('A longer value ending here!');        -- length 27
insert into t values('Short value!');                       -- length 12

select * from t order by length(s) desc;
-- s
-- ---------------------------
-- A longer value ending here!
-- Short value!

select * from t order by length(s);
-- s
-- ------------
-- Short value!
-- A longer val

As you can see the second result has truncated much of the column's content.

For comparison, MySQL produces this:

+-----------------------------+
| s                           |
+-----------------------------+
| A longer value ending here! |
| Short value!                |
+-----------------------------+
2 rows in set (0.01 sec)

+-----------------------------+
| s                           |
+-----------------------------+
| Short value!                |
| A longer value ending here! |
+-----------------------------+
2 rows in set (0.13 sec)

MySQL seems to have a solution that works quite well. I don't know the internals of how they do it so I'm purely guessing here that it keeps track of each column's max content length (not the definition but the actual content) as rows are fetched during query processing.

Would this (not the actual method of doing it but the effect) be something that SQLite3 library could eventually support?

Otherwise, a couple of suggestions for poor man's alternatives:

  1. If a length is specified for a column (such as varchar(10) or int(5) which is ignored anyway by SQLite3 so there cannot be any compatibility issues), this could become a hint for the expected width of the column overriding the currently otherwise heuristically assumed width.

    I think this way it will become very simple to get visually consistent query results for the same columns (at least for simple queries) without the need for .width.

    I understand there are cases where it becomes more involved (like a||b where widths could probably be added), or derived view columns, which makes the 'count-as-you-go' method the only [?] possibility. But, at least wherever the heuristic can work the results will be more visually pleasing than the current way.

  2. Buffer a few rows (e.g., 10) at the shell level before displaying the results and calculate the columns' widths based on their content average/max length.

Thanks for reading this far!

18:51 Edit: Shell .mode column width auto adjustment (artifact: cc97fb6b17 user: tonyp)

I don't know if this issue has come up before but here it goes.

Currently, (for .mode column at least) there seems to be a very simple 'heuristic' calculation of a column's width based on the first row's contents.

This has at least two problems:

  1. If the first row's column widths are not representative of the majority of rows (i.e., either much shorter or much longer than average width), the 'wrong' widths are used.

  2. An identical columns query result will end up with different column widths if only ordered differently (for example).

With ad-hoc queries, normally one adjusts column widths with the .width shell command, manually. (I don't consider the script case here as there is no problem there to have pre-configured .width commands all over the script.)

The problem with it is that for each different query a new adjusted .width command has to be issued.

To illustrate:

.mode column

create table t(s varchar(30));
insert into t values('A longer value ending here!');        -- length 27
insert into t values('Short value!');                       -- length 12

select * from t order by length(s) desc;
-- s
-- ---------------------------
-- A longer value ending here!
-- Short value!

select * from t order by length(s);
-- s
-- ------------
-- Short value!
-- A longer val

As you can see the second result has truncated much of the column's content.

For comparison, MySQL produces this:

+-----------------------------+
| s                           |
+-----------------------------+
| A longer value ending here! |
| Short value!                |
+-----------------------------+
2 rows in set (0.01 sec)

+-----------------------------+
| s                           |
+-----------------------------+
| Short value!                |
| A longer value ending here! |
+-----------------------------+
2 rows in set (0.13 sec)

MySQL seems to have a solution that works quite well. I don't know the internals of how they do it so I'm purely guessing here that it keeps track of each column's max content length (not the definition but the actual content) as rows are fetched during query processing.

Would this (not the actual method of doing it but the effect) be something that SQLite3 library could eventually support?

Otherwise, a couple of suggestions for poor man's alternatives:

  1. If a length is specified for a column (such as varchar(10) or int(5) which is ignored anyway by SQLite3 so there cannot be any compatibility issues), this could become a hint for the expected width of the column overriding the currently otherwise heuristically assumed width.

    I think this way it will become very simple to get visually consistent query results for the same columns (at least for simple queries) without the need for .width.

    I understand there are cases where it becomes more involved (like a||b where widths could probably be added), or derived view columns, which makes the 'count-as-you-go' method the only [?] possibility. But, at least wherever the heuristic can work the results will be more visually pleasing than the current way.

  2. Buffer a few rows (e.g., 10) at the shell level before displaying the results and calculate the columns widths based on their content average/max length.

Thanks for reading this far!

18:48 Post: Shell .mode column width auto adjustment (artifact: c1fc053b64 user: tonyp)

I don't know if this issue has come up before but here it goes.

Currently, (for .mode column at least) there seems to be a very simple 'heuristic' calculation of a column's width based on the first row's contents.

This has at least two problems:

  1. If the first row's column widths are not representative of the majority of rows (i.e., either much shorter or much longer than average width), the 'wrong' widths are used.

  2. An identical columns query result will end up with different column widths if only ordered differently (for example).

With ad-hoc queries, normally one adjusts column widths with the .width shell command, manually. (I don't consider the script case here as there is no problem there to have pre-configured .width commands all over the script.)

The problem with it is that for each different query a new adjusted .width command has to be issued.

To illustrate:

.mode column

create table t(s varchar(30));
insert into t values('A longer value ending here!');        -- length 27
insert into t values('Short value!');                       -- length 12

select * from t order by length(s) desc;
-- s
-- ---------------------------
-- A longer value ending here!
-- Short value!

select * from t order by length(s);
-- s
-- ------------
-- Short value!
-- A longer val

As you can see the second result has truncated much of the column's content.

For comparison, MySQL produces this:

+-----------------------------+
| s                           |
+-----------------------------+
| A longer value ending here! |
| Short value!                |
+-----------------------------+
2 rows in set (0.01 sec)

+-----------------------------+
| s                           |
+-----------------------------+
| Short value!                |
| A longer value ending here! |
+-----------------------------+
2 rows in set (0.13 sec)

MySQL seems to have a solution that works quite well. I don't know the internals of how they do it so I'm purely guessing here that it keeps track of each column's max content length (not the definition but the actual content) as rows are fetched during query processing.

Would this (not the actual method of doing it but the effect) be something that SQLite3 library could eventually support?

Otherwise, a couple of suggestions for poor man's alternatives:

  1. If a length is specified for a column (such as varchar(10) or int(5) which is ignored anyway by SQLite3 so there cannot be any compatibility issues), this could become a hint for the expected width of the column overriding the currently otherwise heuristically assumed width.

    I think this way it will become very simple to get visually consistent query results for the same columns (at least for simply queries).

    I understand there are cases where it becomes more involved (like a||b where widths could probably be added), or derived view columns, which makes the 'count-as-you-go' method the only [?] possibility. But, at least wherever the heuristic can work the results will be more visually pleasing than the current way.

  2. Buffer a few rows (e.g., 10) at the shell level before displaying the results and calculate the columns widths based on their content average/max length.

Thanks for reading this far!

2020-05-06
14:31 Reply: select between two dates (artifact: 6f8e6dbc94 user: tonyp)

date('now','-14 days') will give you the date 14 days ago.

2020-05-04
16:50 Edit reply: Feature request: Dynamically execute SQL from shell (artifact: f44316a661 user: tonyp)

I don't think I'm making any mistake. I clearly stated in the title that I'm talking about the shell.

SQLite3 may be the name of the library (the SQL engine) but it also happens to be the official name of the compiled shell.

Don't you get sqlite3[.exe] when you compile?

It's also the name used for the shell inside the official pre-compiled binaries:

For example, https://www.sqlite.org/2020/sqlite-tools-win32-x86-3310100.zip

At any rate, how does that invalidate my previous statement (about removing functionality from the shell to the point of not needing it anymore as the same functionality can be had from scripting)?

16:46 Reply: Feature request: Dynamically execute SQL from shell (artifact: ffe9ea3bb1 user: tonyp)

I don't think I'm making any mistake. I clearly stated in the title that I'm talking about the shell.

SQLite3 may be the name of the library (the SQL engine) but it also happens to be the official name of the compiled shell.

Don't you get sqlite3[.exe] when you compile?

It's also the name used for the shell inside the official pre-compiled binaries:

For example, https://www.sqlite.org/2020/sqlite-tools-win32-x86-3310100.zip

12:57 Reply: Feature request: Dynamically execute SQL from shell (artifact: 091de9c8bc user: tonyp)

With that reasoning a lot of functionality could be removed from SQLite3 as it can also be done with scripting. Actually, the whole shell could be removed. :)

08:03 Reply: Feature request: Dynamically execute SQL from shell (artifact: a3ce38de0d user: tonyp)

I tried it, and it appears to work well with the little testing I did. Thanks.

Suggestion for improvement (unless it can already do this somehow I didn't see).

To make it more dynamic, allow more arguments with the first being the SQL statement and the remaining positional substitutes for the SQL statements.

Example:

eval('update t set a=?, b=?','one','two')

to do this:

update t set a ='one', b='two'

2020-05-03
20:05 Edit reply: Feature request: Dynamically execute SQL from shell (artifact: 6556bf0244 user: tonyp)

I fail to see the dangers as this is meant to run from the shell only. If you have access to the database from the shell you can run the same SQL statements anyway. So, no difference.

Your link is nice but has two issues:

  1. All statements will run, not just the ones selected.
  2. A result must match the expected one.
20:04 Reply: Feature request: Dynamically execute SQL from shell (artifact: 3cdcec8ede user: tonyp)

I fail to see the dangers as this is meant to run from the shell only. If you have access to the database from the shell you can run the same SQL statements anyway. So, no difference.

Your link is nice but has two issues: 1. All statements will run, not just the ones selected. 2. A result must match the expected one.

18:42 Post: Feature request: Dynamically execute SQL from shell (artifact: bb9021bddd user: tonyp)

Imagine something this:

create table sql(stmt,notes);
insert into sql values('update t set name = trim(name)','Trim all names in t');
...

exec(select stmt from sql);

This would execute each SQL statement retrieved by SELECT from the sql table.

Many applications, such as automatically conditioning data by running multiple SQL statements in sequence.

2020-05-01
09:28 Edit: Ambiguous 'ambiguous error', is it normal or bug? (artifact: 492bf91d56 user: tonyp)
create table t(s);
select * from t a join t b order by s;            -- no error
select * from t a join t b order by length(s);    -- ambiguous column name: s

Why an error for the second case and not the first?

09:27 Post: Ambiguous 'ambiguous error', is it normal or bug? (artifact: 281e572565 user: tonyp)
create table t(s);
select * from t a join t b order by s;            -- no error
select * from t a join t b order by s,length(s);  -- ambiguous column name: s

Why an error for the second case and not the first?

2020-04-27
09:08 Reply: .dump feature request (artifact: dcf44442f2 user: tonyp)

A switch for the less likely use case to avoid having to type it most of the time.

For me at least, the most common use (only case actually) is the drop is either required or indifferent (e.g. same table structure).

The same for views.

08:10 Edit: .dump feature request (artifact: 1a7a46420e user: tonyp)

Could the .dump command first drop the table before creating it?

So, instead of

BEGIN TRANSACTION;
...
CREATE TABLE xxx(s);
...

this:

BEGIN TRANSACTION;
...
DROP TABLE IF EXISTS xxx;
CREATE TABLE xxx(s);
...

This is because a dump is often used to transfer changes from one database (e.g. a single table) to another, not only for a full rebuild of a database.

The way it currently works, only data can be transferred but assuming table structure is unchanged.

But if the table structure also changes, the transfer fails unless one first manually deletes the destination table before feeding the .dump output to the destination database.

Thank you

08:07 Edit: .dump feature request (artifact: 289eea4db3 user: tonyp)

Could the .dump command first drop the table before creating it?

So, instead of

BEGIN TRANSACTION;
CREATE TABLE xxx(s);
...

this:

BEGIN TRANSACTION;
DROP TABLE IF EXISTS xxx;
CREATE TABLE xxx(s);
...

This is because a dump is often used to transfer changes from one database (e.g. a single table) to another, not only for a full rebuild of a database.

The way it currently works, only data can be transferred but assuming table structure is unchanged.

But if the table structure also changes, the transfer fails unless one first manually deletes the destination table before feeding the .dump output to the destination database.

Thank you

08:06 Post: .dump feature request (artifact: 7383950297 user: tonyp)

Could the .dump command first drop the table before creating it?

So, instead of

BEGIN TRANSACTION;
CREATE TABLE xxx(s);
...

this:

BEGIN TRANSACTION;
DROP TABLE IF EXISTS xxx(s);
CREATE TABLE xxx(s);
...

This is because a dump is often used to transfer changes from one database (e.g. a single table) to another, not only for a full rebuild of a database.

The way it currently works, only data can be transferred but assuming table structure is unchanged.

But if the table structure also changes, the transfer fails unless one first manually deletes the destination table before feeding the .dump output to the destination database.

Thank you

2020-04-24
17:55 Reply: SQL compliance on Wikipedia (artifact: 8d9820afd2 user: tonyp)

SQLite3 currently being at the top of the SQL engines (at least in terms of people using it either directly or indirectly), it IS by definition the standard.

There are many niceties that SQLite3 offers that make it more usable and practical for everyday use.

From zero setup to single file database and engine, ... to virtually no max column width, and many more...

So, maybe the burden should be on others to try to be more compatible with SQLite3.

2020-04-23
22:57 Edit reply: Feature Request: consistent .dump order (artifact: 5d6a71648f user: tonyp)

... and "PRAGMA page_size"

Thank you

22:57 Reply: Feature Request: consistent .dump order (artifact: 1a1fb216b9 user: tonyp)

... "PRAGMA page_size"

Thank you

2020-04-16
11:48 Reply: select in order (artifact: 3bf078bc6f user: tonyp)

This is very useful.

How would one use json_each() with strings instead of numbers? I couldn't figure out the syntax.

2020-04-03
22:20 Reply: -nocreate -- shell tool feature request/offer (artifact: 8ebfc83cb0 user: tonyp)

I would be happy with just a warning followed by "Create? y/N" response (N being the default when ENTER is pressed) when specifying a new file to save me from accidentally working on an empty database due to mistyping the filename.

2020-03-13
08:21 Post: Search does not search the message title (artifact: af65b87574 user: tonyp)

Search does not search the message title, just the message content.