Another reason: because PostgreSQL supports it as well (including timezone) ;-)
- Original Message -
From: Peter da Silva
To: SQLite mailing list
Sent: Tuesday, August 13, 2019, 23:18:29
Subject: [sqlite] Backward compatibility vs. new features (was: Re: dates,
times and R)
If the
> And now you have a file which can't be edited with old versions of the CLI.
> However you cut it, you have compatibility problems.
One shouldn't do it at all. It's like trying to a edit a DOCX with Word95. It's
not *backward* compatibility. It's not the case you mentioned before. And
> A programmer uses a copy of the SQLite CLI to correct errors in a database
> made and maintained by a production program. This involves making a new
> table, copying some data from the old data to the new table, deleting the old
> table, then renaming the new table. When the programmer is
> I do understand the value of having date/time types in SQLite, but it is not
> easy to do while retaining backward compatibility. It'll have to wait for
> SQLite4 or something.
Actually I do not really understand the point about backward compatibility.
Many very useful suggestions are
> The issue for something like a data-time field is how would you indicate
that a field is a data-time field. Due to backwards compatibility it
can't use the term data or time to trigger that use, as existing
applications use that and expect a different result, based on published
and promised
Dear SQLite team,
I suppose I am not the only one having to convert between MySQL/MariaDB and
SQLite databases every now and then. I know there are converters for MySQL
dumps but none of any I have ever tried did work nearly reliable.
So my suggestion would be to add an import feature to the
archar(50) not null
END TRANSACTION;
- Original Message -
From: Simon Slavin
To: SQLite mailing list
Sent: Wednesday, August 7, 2019, 18:25:45
Subject: [sqlite] Feature request: import MySQL dumps in CLI
On 7 Aug 2019, at 5:13pm, Thomas Kurz wrote:
> So my suggestion would
> I highly doubt the SQLite team will undertake this task. They
> Surely have the skill to do so, but their priority is the one
> software product you desire to use, undoubtedly due to its
> high utility. I doubt that utility would exist if they were
> to wander off tacking the conversion
> You might prefer adding an explicit NOT NULL on both "client" and "salesman"
> columns.
> There is an historical reason why SQLite accepts NULL for primary key
> column(s).
Ok, thanks for the hint, I didn't know that either. But it is a very odd
behavior, because PRIMARY KEY per definition
Imho it would be helpful (especially for newbies that don't know the full
history of SQLite) to have a "PRAGMA strict_mode" or similar, to disable all
kinds of historical bugs. They might be relevant for existing applications but
in no way for newly created ones. Among the things to consider
ave?
-Original Message-
From: sqlite-users On Behalf Of
Thomas Kurz
Sent: Thursday, October 31, 2019 6:54 PM
To: SQLite mailing list
Subject: Re: [sqlite] DELETE extremely slow
> Do you have memory to run this in? Have you increased the sqlite cache size
> because that looks (
That's it!!! You're a genius! Thank you very very much!
Run Time: real 8.290 user 3.25 sys 1.906250
- Original Message -
From: Keith Medcalf
To: SQLite mailing list
Sent: Friday, November 1, 2019, 18:07:51
Subject: [sqlite] DELETE extremely slow
One of your triggers requires and
The database schema is not a secret. If it helps, I can post it, that's no
problem. Is it enough to run ".dump" on a database without data?
- Original Message -
From: Keith Medcalf
To: SQLite mailing list
Sent: Friday, November 1, 2019, 22:57:02
Subject: [sqlite] DELETE extremely slow
> It would/should have told you immediately that you needed those two
> additional indexes, I should think.
Unfortunately not. Someone told me about ".expert" some time ago and it's
indeed helpful for me because I never know what indexes to create and why. But
for this database everything
> Something is wrong. If you did multiple commands like
>
> DELETE FROM MyTable;
>
> to your child tables, they should be fast. Have you run an integrity check ?
I created a new database now, added the missing index "trace(datasetid)" as
suggested by Keith.
The result of "DELETE FROM dataset"
> According to the MariaDB reference manual, it does not "do anything" with
> references clauses on columns.
Thanks for that hint, I will try again tomorrow because I cannot say for sure
now whether it worked correctly or not. (And I don't have that data available
anymore.)
> What do you mean by "SQL-basics"?
I forgot to mention that at least some basic math would be very helpful as
well. I don't want to suggest a complete math support, that would really be far
away from liteness, but the discussion standard deviation has shown that at
least STDEV and POWER would
> Omitting RIGHT JOIN is good, that's a misfeature and LEFT JOIN does
> everything
useful it does.
With all dear respect, but I don't think that it is up to you to define what a
"feature" and a "misfeature" is. iirc, RIGHT JOIN is declared in SQL92, it is
part of the SQL standard, and
> SQLite has had geospatial support for years via the r-tree extension, and
> more recently GeoJSON.
But not compatible to standards from Open Geospatial Consortium, as far as I
know. Which requires additional conversions, and considering that geodata
usually have sizes of a GB or more, this
We recently had a discussion about date/time support, but also other
suggestions, which sooner or later end up at the point "cannot be done, would
break backward compatibility". (See also: "Backward compatibility vs. new
features (was: Re: dates, times and R)")
I'm always curious and
Ok, thanks for everone's answer.
- Original Message -
From: James K. Lowden
To: sqlite-users@mailinglists.sqlite.org
Sent: Monday, October 28, 2019, 18:27:06
Subject: [sqlite] Single or double quotes when defining alias?
On Fri, 25 Oct 2019 23:55:20 +0200
Thomas Kurz wrote
I'd kindly ask whether there is some sort of roadmap for SQLite development?
Someone recently pointed out how much he loves the "lite" and well-thought
features. I cannot see that: I observe that many "playground" gadgets keep
being implemented (like virtual columns, virtual tables, FTS3/4/5,
Dear all,
this might be a stupid question, but do I have to use single or double quotes
when defining an alias?
SELECT column1 AS 'c'
--or--
SELECT column2 AS "d"
On the one hand, the name refers to a column or table identifier. On the other
hand, at the time of using this statement, the
> Feel free to make suggestions. Which missing feature or features causes
you the most bother?
Thanks, Dan.
To me, the most puzzling thing is the lack of full ALTER TABLE support (DROP
COLUMN, MODIFY COLUMN, ADD CONSTRAINT, DROP CONSTRAINT). Modifying tables is
some kind of science in SQLite,
> The features you name don't take away from the "liteness", they are all quite
small and useful.
Yes of course they are useful, I wouldn't deny that. But they are prioritized
over SQL-basics, that's what I'm confused about.
___
sqlite-users mailing
> I suspect you are used to database servers, and haven’t used SQLite as an
> embedded library inside an app
Yes and no ;-)
I have used database servers, and I am currently (for about 2 years) using (and
appreciating!) SQLite library.
> Full text search is very common
Yes, of course. I
> Do you have memory to run this in? Have you increased the sqlite cache size
> because that looks (to me) an awful lot like I/O thrashing ...
Sorry to disappoint you, Keith and Simon, but in all cases the database file
has been located on a ramdisk. It's only about 50 MB in size, btw.
>
> Keith found the answer: you don't have the indexes required to make your
> FOREIGN KEYs run quickly.
Thanks, I will try that.
> If you DELETE FROM the child tables first, do you get fast or slow times ?
Yes, I already tried deleting from each table individually. It's slow
everywhere.
>
I'm using a database with 5 hierarchically strcutured tables using foreign
keys. The largest table contains about 230'000 entries. My problem is that
deleting in this database is extremely slow:
pragma foreign_keys=on;
pragma journal_mode=wal;
.timer on
delete from dataset;
--> Run Time: real
extremely slow
On Thu, Oct 31, 2019 at 10:57 AM Thomas Kurz wrote:
> I'm using a database with 5 hierarchically strcutured tables using foreign
> keys. The largest table contains about 230'000 entries. My problem is that
> deleting in this database is extremely slow:
> pragma foreign_keys=
> I hope you will experience such joy as well.
Well, I don't ;-) The lack of full ALTER TABLE support frustrates me every
time, even though I greatly appreciate most other parts of SQLite and the
developers' work. But a more complete SQL statement support would be very, very
helpful.
> It has LEFT JOIN and does not have RIGHT JOIN. Why? Because RIGHT can be made
> out of LEFT by swapping order of tables.
The paradigma of SQL is to let the user describe what he wants to do, not to
think about how to describe the problem so that the database system does
understand. The lack
I have a problem when opening a read-only database, which is a WAL-mode
database.
When trying to open it in read-only mode, i.e. using file:test.sqlite?mode=ro,
SHM and WAL file are created. That's unpleasant, but the actual problem is the
two files don't get deleted when closing the database.
> Do the same thing again without the mode=ro
> Do the files get deleted this time ?
No, this shows the same behavior, but in this case, it's actually what I'd
expect.
> Does the program have enough privs over the database file's folder ?
Yeah, sure, actually the file's on a FAT32 drive.
I would create an SQL dump ("sqlite3 file.db .dump") and search therein.
- Original Message -
From: Scott
To: SQLite Mailing List
Sent: Thursday, February 13, 2020, 15:01:06
Subject: [sqlite] Can I search all tables and columns of SQLite database for a
specific text string?
Can I
I would not choose a new wording. "Serverless" is correct, and just because
others start using "serverless" in a wrong manner, I don't see any need for a
change.
Just my 2 cts.
- Original Message -
From: Richard Hipp
To: General Discussion of SQLite Database
Sent: Monday, January
Does this mean there will be no possibility to prevent inserting a string into
an integer column anymore?
create table x (x integer check (typeof(x) == 'integer'));
insert into x values ('1');
--> will pass in future versions???
- Original Message -
From: Richard Hipp
To: SQLite
strain execution timing change? (Now a bug)
On 2/1/20, Thomas Kurz wrote:
> Does this mean there will be no possibility to prevent inserting a string
> into an integer column anymore?
> create table x (x integer check (typeof(x) == 'integer'));
> insert into x values ('1');
> --> will
> You say that you want to prevent the use of the string literal '123'
> for inserting into the integer field x. That will no longer be
> possible in SQLite beginning with 3.32.0 (assuming the change
> currently on trunk goes through.)
> But, why do you want to do that?
You are right. I
I'd just like to kindly ask whether there are any new plans for a full ALTER
TABLE support?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
I noticed that the .dump command in the CLI doesn't contain the "user_version"
and "application_id" fields. I don't know whether this is intentional, but
would you consider including these values in the output of .dump?
___
sqlite-users mailing list
Wouldn't be something like
SELECT sql FROM sqlite_master WHERE tbl_name='?' AND type='table'
contains "WITHOUT ROWID"
be sufficient?
Just being curious.
- Original Message -
From: sky5w...@gmail.com
To: SQLite mailing list
Sent: Saturday, February 15, 2020, 18:06:47
Subject:
Why can't the forum just forward all new postings to this mailing list and vice
versa? Then everyone could chose what to use ;)
- Original Message -
From: Richard Hipp
To: General Discussion of SQLite Database
Sent: Thursday, March 12, 2020, 21:17:59
Subject: [sqlite] New SQLite
101 - 143 of 143 matches
Mail list logo
