SQLite Forum

Timeline
Login

50 most recent forum posts by user younique

2021-08-22
10:59 Reply: about "strict" mode (artifact: 0350a50061 user: younique)

What's a well-formatted date?

<datetime literal> ::=
                <date literal>
              | <time literal>
              | <timestamp literal>

         <date literal> ::=
              DATE <date string>

         <time literal> ::=
              TIME <time string>

         <timestamp literal> ::=
              TIMESTAMP <timestamp string>

         <date string> ::=
              <quote> <date value> <quote>

         <time string> ::=
              <quote> <time value> [ <time zone interval> ] <quote>

         <timestamp string> ::=
              <quote> <date value> <space> <time value> [ <time zone interval> ] <quote>


         <time zone interval> ::=

         90  Database Language SQL

 





                                                    X3H2-92-154/DBL CBR-002
                                                               5.3 <literal>


              <sign> <hours value> <colon> <minutes value>

         <date value> ::=
              <years value> <minus sign> <months value> <minus sign> <days value>


         <time value> ::=
              <hours value> <colon> <minutes value> <colon> <seconds value>


         <interval literal> ::=
              INTERVAL [ <sign> ] <interval string> <interval qualifier>

         <interval string> ::=
              <quote> { <year-month literal> | <day-time literal> } <quote>


         <year-month literal> ::=
                <years value>
              | [ <years value> <minus sign> ] <months value>

         <day-time literal> ::=
                <day-time interval>
              | <time interval>

         <day-time interval> ::=
              <days value>
                [ <space> <hours value> [ <colon> <minutes value> [ <colon> <seconds value> ] ] ]


         <time interval> ::=
                <hours value> [ <colon> <minutes value> [ <colon> <seconds value> ] ]

              | <minutes value> [ <colon> <seconds value> ]
              | <seconds value>

         <years value> ::= <datetime value>

         <months value> ::= <datetime value>

         <days value> ::= <datetime value>

         <hours value> ::= <datetime value>

         <minutes value> ::= <datetime value>

         <seconds value> ::=
                <seconds integer value> [ <period> [ <seconds fraction> ] ]


         <seconds integer value> ::= <unsigned integer>

         <seconds fraction> ::= <unsigned integer>

                                                       Lexical elements   91

 





          X3H2-92-154/DBL CBR-002
         5.3 <literal>


         <datetime value> ::= <unsigned integer>

2021-08-21
21:50 Edit reply: about "strict" mode (artifact: afb7e3bf4e user: younique)

Restrict column data type names in CREATE TABLE statements to one of INT, INTEGER, REAL, BLOB, TEXT so that we always know exactly which datatype is allowed in that column.

This is a very interesting point as it caused strict mode to be more restrictive than standard SQL. Is that really the goal? Shouldn't all SQL data types (especially DATE, TIME, DATETIME which often led to confusion or inquiries here) be allowed but just behave as declared. So inserting a well-formated date into a date field should be fine, imho.

Edit: what about foreign-key-enforcement? While following the discussions here, I notice that even experts seem to sometimes forget about "pragma foreign_keys=on".

21:48 Reply: about "strict" mode (artifact: 4b3f61b468 user: younique)

Restrict column data type names in CREATE TABLE statements to one of INT, INTEGER, REAL, BLOB, TEXT so that we always know exactly which datatype is allowed in that column.

This is a very interesting point as it caused strict mode to be more restrictive than standard SQL. Is that really the goal? Shouldn't all SQL data types (especially DATE, TIME, DATETIME which often led to confusion or inquiries here) be allowed but just behave as declared. So inserting a well-formated date into a date field should be fine, imho.

21:44 Reply: about "strict" mode (artifact: 52773ae384 user: younique)

We've been lobbying hard here for strict-mode and love that this is becoming a reality, but we should understand that it is a hard paradigm shift and should not be compromised

I agree with you: strict mode should be very carefully designed because it cannot be undone or changed once published.

14:27 Reply: about "strict" mode (artifact: 5cdb0a913d user: younique)

I had already though about that point, too. But imho it is not solvable at all, regardless of whether strict mode applies to single tables, a whole database or a connection.

The reason is that the database will always be allowed to be opened in an older version of SQLite not knowing about strict mode. If it's in the table declaration, (I assume) older SQLite version will just ignore it. And so they will with a database property and a connection property as well.

I'd argue in favor of performing the check only on an explicit "pragma integrity_check" as is currently (as far as I know) the same with foreign key enforcement.

2021-08-20
22:52 Edit: about "strict" mode (artifact: 1b9d073a37 user: younique)

Dear team,

I am following the SQLite timeline and have seen your efforts about introducing a strict mode to SQLite. First of all, thanks for that.

I remember that some time ago (probably in the old mailing list) there's been a suggestion for strict mode using a pragma statement. Have you considered this suggestion? (As far as I understand, the implementation used now requires "strict" in the table definition.)

Imho, a pragma had some major advantages:

  1. Those who like strict handling could easily enable it for every table in every database by just setting "pragma strict=true" when opening the connection. Existing databases/tables don't need to be changed. Adding the pragma to .sqliterc would make SQLite-CLI always operate in strict mode.

  2. Foreign key enforcement could be included. Currently, it's not clear to me how "mixed" tables would behave: Do I need the referencing or the referenced table to be strict, or both?

  3. The "create table" syntax wouldn't diverge from standard SQL. No need to take care when importing SQL dumps.

  4. Enforcing strings to be declared in single quotes and identifiers in double quotes could be enabled as well; not only for the table declaration, but for any other statement (especially SELECT) as well.

  5. No need to use the rather complicated ALTER TABLE algorithm for switching existing databases to strict mode. (And if it turns out for someone that he was better without strict mode, he could easily disable it again.)

As the feature hasn't been released yet, maybe one could reconsider the implementation. What do others think here?

22:49 Post: about "strict" mode (artifact: 37cef34e3d user: younique)

Dear team,

I am following the SQLite timeline and have seen your efforts about introducing a strict mode to SQLite. First of all, thanks for that.

I remember that some time ago (probably in the old mailing list) there's been a suggestion for strict mode using a pragma statement. Have you considered this suggestion? (As far as I understand, the implementation used now requires "strict" in the table definition.)

Imho, a pragma had some major advantages:

  1. Those who like strict handling could easily enable it for every table in every database by just setting "pragma strict=true" when opening the connection. Existing databases/tables don't need to be changed. Adding the pragma to .sqliterc would make SQLite-CLI always operate in strict mode.

  2. Foreign key enforcement could be included. Currently, it's not clear to me how "mixed" tables would behave: Do I need the referencing or the referenced table to be strict, or both?

  3. The "create table" syntax wouldn't diverge from standard SQL. No need to take care when importing SQL dumps.

  4. Enforcing strings to be declared in single quotes and identifiers in double quotes could be enabled as well; not only for the table declaration, but for any other statement (especially SELECT) as well.

As the feature hasn't been released yet, maybe one could reconsider the implementation. What do others think here?

2021-08-13
10:58 Reply: SQLite Binary Log support! (artifact: 166c8b420d user: younique)

This is not related to the thread starter's question, but the disadvantage of BedrockDB is that it doesn't compile for Windows.

09:26 Reply: Old Nabble SQLite mailing list site down? (artifact: 8beedbc9a5 user: younique)

After the old mailing list was abandoned, I downloaded all messages for my own reference. There have been many good threads which still help me today.

If anyone is interested, I can provide the archive. It's 320 MB zipped.

Maybe even one of the admins has an interested in converting/migrating these topics into fossil. It should easily be convertible using a small script.

09:03 Post: Retrieve list of tables (artifact: d718f8dbc9 user: younique)

I use SELECT name FROM sqlite_master WHERE type='table' when I need a list of all tables within a database. This list includes internal tables as well, e.g. "sqlite_sequence". The ".tables" CLI command does not.

Is there a more convenient way which does not need any filtering (there seems to be no pragma either)? I'm not sure whether internal tables always start with "sqlite_". How about temporary tables?

If not, may I suggest the beautiful "SHOW TABLES" statement from MySQL. Simple as could be, very helpful, easy to remember, and working not only in CLI but everywhere.

08:42 Reply: LEFT JOIN Misunderstanding (artifact: 3823b93b09 user: younique)

<outer join type> ::= LEFT | RIGHT | FULL

Maybe this is a good time to renew the request for supporting RIGHT joins. I know that right and left are interchangeable but sometimes it is more intuitive to use the "right" variant.

08:35 Reply: Calculating Trip Duration in Minutes? (artifact: 5825ce2c09 user: younique)

There are EXACTLY 60 seconds in a minute, EXACTLY 60 minutes in an hour, and EXACTLY 24 hours in a day (EXACTLY 1440 minutes in a day and EXACTLY 86400 seconds in a day). EXACTLY. ALWAYS. EVERYTIME. WITHOUT EXCEPTION.

Sorry, but this was only true in former days where a second was defined as 1/86400th of a day (based on rotation of earth). Nowadays, physically, a second is defined on Cs-133 (see SI unit system) which is approximately, but not exactly, coupled to earth rotation. So while your definition is, of course, correct for every-day use, physically is it wrong.

2021-08-09
13:39 Reply: Awesome people (artifact: c90a3b6ae5 user: younique)

100% ACK 👍

11:52 Reply: Repeat values n times (artifact: 563d09bcf4 user: younique)

Thanks for this great explanation!

2021-08-08
19:47 Reply: Best practice: Save partial dates (artifact: 2cc5940721 user: younique)

I'm aware this question is some days old meanwhile, but if I were you, I'd use TEXT and store the date information as JSON:

INSERT INTO table(date) VALUES (json_object('year',2021,'month',6,'day',NULL));

2021-06-28
14:53 Post: DOS? (artifact: 42bd705e2f user: younique)

Hello to all,

as SQLite is designed to also work in embedded environments with very limited capabilities, I wonder if it is possible to get it compiled for use in a DOS application. Has anyone tried that yet or can give some advice?

Kind regards, Thomas

2021-06-09
14:12 Reply: Question on internals: Sync between WAL and B Tree (artifact: bb2e97806e user: younique)

I know this thread is some days old, but want to point you to https://sqlite.org/atomiccommit.html which is about traditional journal mode but explains all details very well and understandably.

2021-05-17
20:59 Reply: How to reset auto increment on DB browser (artifact: bced10942b user: younique)

Yepp, sorry about that. Didn't realize that

is a string in this case, not an identifier ;)

2021-05-16
21:13 Reply: How to reset auto increment on DB browser (artifact: a42ca36f0e user: younique)

You cannot use ALTER TABLE for that. The autoincrement counter is stored in a separate table named "sqlite_sequence". You can modify the value there:

UPDATE sqlite_sequence SET seq=1 WHERE name="<table>"

2021-05-15
10:18 Reply: MySQL (artifact: 011820d16c user: younique)

There is a conversion script which works quite ok.

2021-05-13
22:12 Edit: About automatic undo/redo (artifact: e4bc992add user: younique)

By incident, I stumbled upon the automatic undo/redo suggestion for SQLite databases. Very interesting and clever solution!

I'd like to known why you chose temporary triggers and a temporary table. As far as I understand, temporary tables and triggers are limited to the connection in which they were created. This means that a permanent connection has to be kept to allow undoing an operation. Wouldn't it be easier to keep those in triggers and the table in the database itself and delete the log when terminating the app?

And shouldn't table and column names be put in double quotes?

e.g.: set sql "CREATE TEMP TRIGGER ^_${tbl}_it^ AFTER INSERT ON ^$tbl^ BEGIN\n"

(I've used the ^ sign to indicate where I had put double quotes)

21:04 Post: About automatic undo/redo (artifact: 18ff22fdfa user: younique)

By incident, I stumbled upon the automatic undo/redo suggestion for SQLite databases. Very interesting and clever solution!

I'd like to known why you chose temporary triggers and a temporary table. As far as I understand, temporary tables and triggers are limited to the connection in which they were created. This means that a permanent connection has to be kept to allow undoing an operation. Wouldn't it be easier to keep those in triggers and the table in the database itself and delete the log when terminating the app?

2021-05-06
20:52 Reply: "Office Space"... I'm losing all those pennies (artifact: 42d4c7a481 user: younique)

Why would you expect differently from a Database being told to use floating-point numbers to be different than a C program using floating-point numbers.

Ok, but shouldn't other RDBMSs do the same? However, in MariaDB 10.5.8, I get:

SELECT SUM(CAST('16.15' AS FLOAT))*100; // 1614.9999618530273

SELECT CAST(SUM(CAST('16.15' AS FLOAT))*100 AS INT); // 1615

SELECT SUM(CAST(CAST('16.15' AS FLOAT)*100 AS INT)); // 1615

I clearly understand the pros and cons of floating-point arithmetic. But what puzzle me each time is that different software returns different results for the same thing??? My confidence in floating-point arithmetic would be much greater if it was consistent everywhere.

09:06 Reply: order of INSERT affects the output (artifact: 2a691ad75a user: younique)

SQLite allows columns in an aggregate query that are not arguments to an aggregate function nor arguments to the GROUP BY clause. This is not standard SQL, and is sometimes criticized. But it does have its uses.

I don't know why there could be any criticism because imho it has absolutely no negative impact. Those who want strict SQL behavior just don't have to use it.

To me, it's one of the best (of not the best at all) variance from strict SQL that one can do. It's a great aid and not a misfeature at all. I love it! 💙

2021-05-05
14:00 Reply: SQLite Plus: all the missing SQLite functions (artifact: 8ce546c64e user: younique)

Imho, it'd be much more useful if everything were together in a single "plus.dll" :)

2021-03-25
23:36 Reply: DROP TABLE failure - is it intentional or a bug? (artifact: 946d615bf5 user: younique)

Thanks for your detailed explanation. It's more clear to me now.

23:31 Post: possible to use output of a SELECT-query for ordering? (artifact: 09f7ed2e1d user: younique)

Hello,

is it possible to order a table by using the result of another query?

Example: CREATE TABLE a (b INTEGER, c INTEGER); INSERT INTO a VALUES (1,3),(2,7),(3,2),(5,1),(8,9),(9,3); SELECT * FROM a ORDER BY (SELECT 'c'); -- the constant 'c' is just for demonstration; this should eventually be a select from a different table

The manual says

Otherwise, if the ORDER BY expression is any other expression, it is evaluated and the returned value used to order the output rows. If the SELECT statement is a simple SELECT, then an ORDER BY may contain any arbitrary expressions.

To me it sounds as if it was possible, by it's not working for me.

2021-03-24
21:57 Reply: DROP TABLE failure - is it intentional or a bug? (artifact: 51c667a08a user: younique)
Thanks Ryan for your explanation. However, I do not really understand it. The reason why I posted this long schema is that I was unable to create a minimal example triggering the same error.

For example:

CREATE TABLE a (id INTEGER PRIMARY KEY UNIQUE NOT NULL);
CREATE TABLE b (id INTEGER PRIMARY KEY UNIQUE NOT NULL, a_id REFERENCES a(id));
CREATE TABLE c (id INTEGER PRIMARY KEY UNIQUE NOT NULL, b_id REFERENCES b(id));
DROP TABLE a; 
DROP TABLE c; -- works fine despite missing reference in table "b"
18:22 Reply: DROP TABLE failure - is it intentional or a bug? (artifact: fc15814690 user: younique)
Thanks for pointing me to that thread. It's very informative, indeed. I agree with the author of one of the bottom-most posts that CACHED would have been a much better wording. Then I wouldn't have fallen into this trap.

Is there any comment about my actual problem? Do others think that this is correct behavior?
2021-03-23
00:03 Reply: DROP TABLE failure - is it intentional or a bug? (artifact: c9c3299320 user: younique)

Thanks, Keith, for your comments. I understand that julianday() cannot be used in GENERATED ALWAYS AS ... VIRTUAL columns, but I do not see why the use in STORED columns has to be forbidden. That's very sad news.

2021-03-22
21:05 Post: DROP TABLE failure - is it intentional or a bug? (artifact: 814a51ba88 user: younique)
Dear all,

assume the following schema:

CREATE TABLE IF NOT EXISTS "type" (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL CHECK (name <> '') UNIQUE COLLATE NOCASE, created DATETIME DEFAULT CURRENT_TIMESTAMP, modified DATETIME AS (datetime(julianday())) STORED);
CREATE TABLE IF NOT EXISTS "quantity" (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, unit TEXT NOT NULL, sumsqr BOOLEAN NULL DEFAULT NULL, logarithmical BOOLEAN NOT NULL, created DATETIME DEFAULT CURRENT_TIMESTAMP, modified DATETIME AS (datetime(julianday())) STORED, UNIQUE (name, unit, sumsqr, logarithmical) ON CONFLICT IGNORE);
CREATE TABLE IF NOT EXISTS "dataset" (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NULL DEFAULT NULL COLLATE NOCASE, parentid INTEGER NULL DEFAULT NULL REFERENCES dataset(id) ON UPDATE CASCADE ON DELETE SET NULL CHECK (parentid <> id), created DATETIME DEFAULT CURRENT_TIMESTAMP, modified DATETIME AS (datetime(julianday())) STORED);
CREATE TABLE IF NOT EXISTS "trace" (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NULL DEFAULT NULL COLLATE NOCASE, typeid INTEGER NULL DEFAULT NULL REFERENCES "type"(id) ON UPDATE CASCADE ON DELETE SET NULL, setupname TEXT COLLATE NOCASE NULL DEFAULT NULL CHECK (setupname <> ''), setuptype TEXT COLLATE NOCASE NULL DEFAULT NULL CHECK (setuptype <> ''), datasetid INTEGER NOT NULL REFERENCES dataset(id) ON UPDATE CASCADE ON DELETE CASCADE, quantityid INTEGER NOT NULL REFERENCES quantity(id) ON UPDATE CASCADE ON DELETE RESTRICT, stored DATETIME NULL DEFAULT NULL, created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, modified DATETIME AS (datetime(julianday())) STORED);
CREATE TABLE IF NOT EXISTS "item" (id INTEGER PRIMARY KEY AUTOINCREMENT, traceid INTEGER NOT NULL REFERENCES trace(id) ON UPDATE CASCADE ON DELETE CASCADE, freq BIGINT NOT NULL CHECK (freq >= 0), value REAL NOT NULL, noiseflag BOOLEAN NULL DEFAULT NULL, nameid INTEGER NULL REFERENCES meta2(id) ON UPDATE CASCADE ON DELETE SET NULL);
CREATE TABLE IF NOT EXISTS meta2 (id INTEGER PRIMARY KEY AUTOINCREMENT, parameter TEXT NOT NULL COLLATE NOCASE, value TEXT NOT NULL COLLATE NOCASE, UNIQUE (parameter, value) ON CONFLICT IGNORE);

Now try to delete these tables in the wrong order, e.g.:

DROP TABLE IF EXISTS "type"; -- statement A
DROP TABLE IF EXISTS dataset; -- statement B

Result:

Error: no such table: main.type

The problem is clear, the (already deleted) table "type" is still referenced by other tables. But imho, statement A should fail, not B.
2021-02-23
14:14 Reply: DROP COLUMN feature with indexes (artifact: fdc2b114ea user: younique)

Lastly - MariaDB/MySQL, great DB as it is, should not be held up as the defacto standard for DB operations, ever. Show PostGres doing it, and you may have a more valid point.

MySQL is still much more wide spread than Postgres ;)

13:42 Reply: DROP COLUMN feature with indexes (artifact: 7cdc147f27 user: younique)

I do not understand your problem. No one will stop you from explicitly dropping an index first and the column afterwards. This is always possible, even now.

Having DROP COLUMN should be an easement compared to the current procedure. And having to programatically check for indexes, remember them and recreate them is no way easier than the current situation.

And yes, indexes are a banality. They are in no way an existential part of any database, they only speed things up. Notabene, we are talking about indexes, not constraints!

08:55 Reply: DROP COLUMN feature with indexes (artifact: c979722b00 user: younique)

Imho, DROP COLUMN should not fail because of such a banality as it makes it impossible to due automatic database manipulation (see my post above).

MariaDB recreates such an index without the dropped column and I vote for following this paradigm.

00:06 Reply: DROP COLUMN feature with indexes (artifact: 9423215c3a user: younique)

Well, following your argumentation, "if you drop a column then all indexes which apply to that column (as in specified in the ON clause) should be dropped because they are entirely no longer applicable."

2021-02-22
22:57 Edit reply: DROP COLUMN feature with indexes (artifact: 51ef366599 user: younique)

Please, no new "DROP TABLE IF EXISTS" affair! 😉

What I mean is that DROP COLUMN should not fail in such a case. This might be nice when working in the SQLite-CLI, but it is a no-go for automatic routines. It makes DROP COLUMN worthless if one has to know about the backgrounds to get it to work.

A common usecase of an application database is upgrading the database schema. Due to the restricted ALTER TABLE command, this is currently a very complex and error-prone procedure. Imho, it should get easier to handle with DROP COLUMN, and not only shift the complexity.

Example: An application upgrades its database schema and knows that column X has to be dropped because it has been replaced by something else. The application has never used an index on column X, so the upgrade procedure just says "DROP COLUMN X". But some user might have used X quite often and added an index for better performance. Bang -- the application's upgrade procedure fails. Solution: query all indexes first, remember them, drop what is needed, drop column, maybe recreated some indexes.... does one have to do the same with views as well? This is no improvement. It just keeps the complexity of the current ALTER TABLE procedure.

22:17 Reply: DROP COLUMN feature with indexes (artifact: c0f015cd6e user: younique)

Maybe, the documentation from MariaDB provides some help:

... DROP COLUMN [IF EXISTS] col_name [CASCADE RESTRICT]

Drops the column from the table. If you are using IF EXISTS you will not get an error if the column didn't exist. If the column is part of any index, the column will be dropped from them, except if you add a new column with identical name at the same time. The index will be dropped if all columns from the index were dropped. If the column was used in a view or trigger, you will get an error next time the view or trigger is accessed. MariaDB starting with 10.2.8

Dropping a column that is part of a multi-column UNIQUE constraint is not permitted. For example:

CREATE TABLE a ( a int, b int, primary key (a,b) );

ALTER TABLE x DROP COLUMN a; [42000][1072] Key column 'A' doesn't exist in table

The reason is that dropping column a would result in the new constraint that all values in column b be unique. In order to drop the column, an explicit DROP PRIMARY KEY and ADD PRIMARY KEY would be required. Up until MariaDB 10.2.7, the column was dropped and the additional constraint applied, resulting in the following structure:

ALTER TABLE x DROP COLUMN a; Query OK, 0 rows affected (0.46 sec)

DESC x; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | b | int(11) | NO | PRI | NULL | | +-------+---------+------+-----+---------+-------+

MariaDB starting with 10.4.0

MariaDB 10.4.0 supports instant DROP COLUMN. DROP COLUMN of an indexed column would imply DROP INDEX (and in the case of a non-UNIQUE multi-column index, possibly ADD INDEX). These will not be allowed with ALGORITHM=INSTANT, but unlike before, they can be allowed with ALGORITHM=NOCOPY

RESTRICT and CASCADE are allowed to make porting from other database systems easier. In MariaDB, they do nothing.

22:11 Reply: DROP COLUMN feature with indexes (artifact: 2e62dba69f user: younique)

Please, no new "DROP TABLE IF EXISTS" issue!

What I mean is that DROP COLUMN should not fail in such a case. This might be nice when working in the SQLite-CLI, but it is a no-go for automatic routines. It makes DROP COLUMN worthless if one has to know about the backgrounds to get it to work.

A common usecase of an application database is upgrading the database schema. Due to the restricted ALTER TABLE command, this is currently a very complex and error-prone procedure. Imho, it should get easier to handle with DROP COLUMN, and not only shift the complexity.

Example: An application upgrades its database schema and knows that column X has to be dropped because it has been replaced by something else. The application has never used an index on column X, so the upgrade procedure just says "DROP COLUMN X". But some user might have used X quite often and added an index for better performance. Bang -- the application's upgrade procedure fails. Solution: query all indexes first, remember them, drop what is needed, drop column, maybe recreated some indexes.... does one have to do the same with views as well? This is no improvement. It just keeps the complexity of the current ALTER TABLE procedure.

17:34 Reply: DROP COLUMN feature with indexes (artifact: 6284d1be61 user: younique)

I would suggest completely dropping all involved indexes, but issuing a warning about what indexes have been dropped.

2021-02-21
12:02 Reply: Table and index in separate files (artifact: 35a87cd8ab user: younique)

I have never understood why one might want to have separate files for that. In fact, I still hate Shapefiles for being a collection of about ~10 different files which is horror to me when having to copy them.

Using a single file for all is one of the most lovely things of SQLite!👍

2021-02-17
12:16 Reply: SELECT skips empty fields ? (artifact: 91d31fb025 user: younique)

But you can choose where NULL values should be placed by adding NULLS FIRST or NULLS LAST

2021-02-01
09:53 Reply: Suggestion for sqldiff (artifact: 8c51181b6e user: younique)

Great 👍

Would be nice if this patch made it into the official code 😉

2021-01-31
23:06 Post: Suggestion for sqldiff (artifact: cc3a01ee3f user: younique)

I'm often using columns like "created DEFAULT CURRENT_TIMESTAMP". Now, when importing the same data into two different databases, the result will be identical except for the "created" column. This is a great annoyance when comparing these databases.

It would be a valuable enhancement to sqldiff if it could ignore all "DEFAULT CURRENT_TIMESTAMP" columns in the comparison. One would get a much better impression on whether the data contained are identical or different.

22:41 Reply: Apparent bug in ALTER TABLE (artifact: ca93756100 user: younique)

It introduced an unnecessary and undocumented interaction between processes working in entirely different parts of the database. Suppose for example that process A is doing the sequence described in section 5 of the ALTER TABLE documentation on page https://www.sqlite.org/lang_altertable.html. Between steps 6 and 7 any view that referenced table X now refers to a nonexistent table. If process B (perhaps belonging to a different user) attempts to do an ALTER TABLE in another part of the database at this point, it will get an error.

Can this happen at all? Imho the 12-step recipe has to be executed within a transaction, so process B should not have the chance to do anything between steps 6 and 7 - only before step 1 or after step 12.

2021-01-25
19:05 Post: Are E-Mail notifications working properly? (artifact: 1e1e2504a2 user: younique)

In the past days I observe that I'm not getting an e-mail for all posts, e.g. posts ab5c97d3b7 or 5aee6f038c are missing.

2021-01-24
15:36 Reply: DROP VIEW IF EXISTS failure (artifact: 3dee3808e5 user: younique)

The problem is, most people obviously think about using SQLite within an application. There it is, of course, possible to query the list of tables and views and to decide whether or not to drop one or the other. But if I have to do so, the whole "IF EXISTS" construct is dispensable.

But there's also the situation where databases have to be changed outside an application with an SQL-diff. And there I cannot do conditionals to check whether I need to drop a view or a table. And again, if I have to do so, "IF EXISTS" is dispensable at all.

2021-01-19
12:39 Reply: Need If column exist option in SQLite (artifact: aebac5060e user: younique)

One could argue about "DROP VIEW IF EXIST", "CREATE TABLE IF NOT EXISTS", etc. the same.

It's a very useful construct and I vote for supporting it. Btw, Postgres has it too ;)

2021-01-11
11:14 Reply: Are there any plans / chances of supporting a RETURNING clause? (artifact: 01ba1157f0 user: younique)

My usecase for DELETE RETURNING would be to have a list of all deleted id's so that I can easiliy remove them from a virtual treeview component. Currently, I have to do a SELECT first to get the id's, then delete them afterwards. So it's 2 steps instead of just 1.

2021-01-10
15:12 Reply: Are there any plans / chances of supporting a RETURNING clause? (artifact: aa0b6e46d5 user: younique)

I'd even be willing to toss money into the pot to support its development, but currently only see 1 way to fund development and that is the 85k/year business subscription....

Me too. I would pay for a more complete ALTER TABLE support, but cannot afford the business subscription. Imho, there should be a possibility for private customers to donate, too!

2021-01-08
17:57 Reply: Are there any plans / chances of supporting a RETURNING clause? (artifact: 637e191de0 user: younique)

+1

More ↓