Re: [sqlite] Roadmap?
> On Oct 26, 2019, at 5:12 PM, Thomas Kurz wrote: > > Geospatial support would be one of the features I would *LOVE* to see in > SQLite :-) SQLite has had geospatial support for years via the r-tree extension, and more recently GeoJSON. As for time stamps ... I’ve been using SQLite since 2005 and have never felt the need to have it do more with dates than store a numeric timestamp in seconds-since-Unix-epoch. I have access to a lot of powerful platform APIs to do stuff with dates, so I don’t feel a need to have the database do similar things for me. —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Roadmap?
On October 26, 2019 8:07:57 p.m. EDT, Thomas Kurz wrote: >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, and thus, very >error-prone. I'd second this (that altering the schema is error-prone). I'm not puzzled by SQLite's omission of most table altering (a good designer will choose a good schema from the start and thus rarely need to change it, making schema alteration a bit more of a niche feature), but it has been a point of difficulty in getting people used to other databases to take SQLite seriously. Earlier this year I spent some time implementing support for SQLite in Movim, a Web-based XMPP client and social media platform. All went smoothly until it was discovered that neither database schema migration library Movim uses actually correctly handles SQLite (despite both claiming to do so). I started fixing up one of them (Phinx), but got bogged down half-way because, of course, the whole thing assumes the database is capable of arbitrary ALTER TABLE statements. For now Movim no longer supports SQLite, despite there being demand for it, for want of good tooling to perform schema alterations. -- J. King ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Roadmap?
> 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 didn't mean to deny that. I was just wondering why it's got priority over SQL-standards (because, as far as I know, but I might be wrong here, FTS is not part of one the SQL-standards). > You didn’t mention geo-queries Geospatial support would be one of the features I would *LOVE* to see in SQLite :-) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Roadmap?
> 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, and thus, very error-prone. I'd be willing to donate for that but as a private user I cannot affort 85 k$ ;-) If you are collecting suggestions, here's some ideas: - RIGHT JOIN - Time periods, temporal referential integrity, temporal predicates from SQL:2011 - native geospatial support (storage using well-known binary representation from Open Geospatial Consortium); I know there's Spatialite, but there are massive bugs in Spatialite that imho arise only due to the lack of basic native geo support Some non-standard but very useful behaviors from other RDBMs: - ON UPDATE CURRENT_TIMESTAMP (from MySQL) - SHOW TABLES, SHOW COLUMNS, etc. (from MySQL) - RETURNING (from Postgres) (I have left out some things I know about that they have already been discussed recently, like DATETIME.) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Roadmap?
> 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 list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: Infite loop on SELECT with .explain on
While on errata for the shell, there is a little cosmetic bugaboo with the output of .eqp full: (1) When .mode col is in effect the "explain" column widths are used, not the .width or the defaults used when .eqp full is not in effect. (2) .head on is ignored -- table output column headers are not output. Also, if you give .eqp or .explain a nonsense argument they report that the argument must be a boolean. .explain also appears to take auto and .eqp also takes more than just a boolean (eg full). -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users On >Behalf Of Richard Hipp >Sent: Saturday, 26 October, 2019 10:08 >To: SQLite mailing list >Subject: Re: [sqlite] Bug: Infite loop on SELECT with .explain on > >Thanks for the report. This problem is fixed on trunk. > >To be clear, this is an issue in the "sqlite3" shell command, not in >the SQLite core. > >Also, it is an issue in the deprecated and undocumented ".explain on" >command of the shell. Years ago, it used to be necessary to run >".explain on" prior to running an EXPLAIN query in order to set up the >output formatting correctly so that the EXPLAIN output was readable. >But that setup is now done automatically. There is no need to run >".explain on" any more and that dot-command is now deprecated and >undocumented. It exists only to prevent legacy scripts from blowing >up. Perhaps I should make the ".explain" command into a no-op? >-- >D. Richard Hipp >d...@sqlite.org >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: Infite loop on SELECT with .explain on
Thanks for the report. This problem is fixed on trunk. To be clear, this is an issue in the "sqlite3" shell command, not in the SQLite core. Also, it is an issue in the deprecated and undocumented ".explain on" command of the shell. Years ago, it used to be necessary to run ".explain on" prior to running an EXPLAIN query in order to set up the output formatting correctly so that the EXPLAIN output was readable. But that setup is now done automatically. There is no need to run ".explain on" any more and that dot-command is now deprecated and undocumented. It exists only to prevent legacy scripts from blowing up. Perhaps I should make the ".explain" command into a no-op? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stream loading SQL script
Dne 25. 10. 19 v 21:41 Keith Medcalf napsal(a): > The sqlite3 command line shell already does this. see function process_input Thanks, it helped. I see that it checks whether the input contains a semicolon and only then it calls sqlite3_complete(). So I implemented it in a similar way in C++. Franta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug: Infite loop on SELECT with .explain on
I am running sqlite3 on a Linux Mint 18 desktop.
I first realized the problem with the stock sqlite3 client 3.11.0
2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f.
I confirmed it still exists with the currently published most recent
version 3.30.1 2019-10-10 20:19:45
18db032d058f1436ce3dea84081f4ee5a0f2259ad97301d43c426bc7f3df1b0b
Steps to reproduce:
--
$ sqlite3
SQLite version 3.30.1 2019-10-10 20:19:45
Enter ".help" for usage hints.
sqlite> create table feedback (fid integer primary key autoincrement,
cid integer not null references characters, game_number integer not null
default 1, players integer not null default 2, playtime text check
(playtime in ("kurz", "20-30", "30-45", "45-60", "lang")),
beginning_player boolean, liked integer, duration integer, luckiness
integer, complexity integer, rules integer, rule_suggestions text,
budget integer, text text);
sqlite> insert into feedback values (1, 1, 1, 2, "kurz", 1, 10, 3, 2, 3,
9, NULL, 10, NULL);
sqlite> select * from feedback;
1|1|1|2|kurz|1|10|3|2|3|9||10|
sqlite> .explain on
sqlite> select * from feedback;
--
This triggers an infinite loop. It uses a full cpu. strace shows it is
doing write(1, ""..., 1024) repeatedly (spaces).
gdb shows the following stack (no debug symbols in the sqlite binary
unfortunately):
(gdb) bt
#0 0xf7fd7fe9 in __kernel_vsyscall ()
#1 0xf7e4fb93 in write () from /lib/i386-linux-gnu/libc.so.6
#2 0xf7de30b1 in _IO_file_write () from /lib/i386-linux-gnu/libc.so.6
#3 0xf7de23e4 in ?? () from /lib/i386-linux-gnu/libc.so.6
#4 0xf7de412d in _IO_do_write () from /lib/i386-linux-gnu/libc.so.6
#5 0xf7de4518 in _IO_file_overflow () from /lib/i386-linux-gnu/libc.so.6
#6 0xf7de379e in _IO_file_xsputn () from /lib/i386-linux-gnu/libc.so.6
#7 0xf7dd95f5 in _IO_padn () from /lib/i386-linux-gnu/libc.so.6
#8 0xf7dbd6b7 in vfprintf () from /lib/i386-linux-gnu/libc.so.6
#9 0xf7dc3668 in fprintf () from /lib/i386-linux-gnu/libc.so.6
#10 0x080ea46b in ?? ()
#11 0x080eb033 in ?? ()
#12 0x080eccc9 in ?? ()
#13 0x080ed698 in ?? ()
#14 0x080f5e8f in ?? ()
#15 0x0804f03a in ?? ()
#16 0xf7d92637 in __libc_start_main () from /lib/i386-linux-gnu/libc.so.6
#17 0x0804f139 in ?? ()
Regards,
-Jan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Unsuscribe
Unsuscribe ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Single or double quotes when defining alias?
On 26 Oct 2019, at 4:44am, Keith Medcalf wrote: > If the identifier is also a keyword and used in a location where it could be > that keyword Actually, any location. A SQL parser may pick out a SQL keyword even if it's in the wrong place in the wrong kind of SQL statement. And then issue a syntax error. You have a good point. So the options are these: A) quote all your entity names B) avoid using keywords in your entity names My personal style leans to one of these, but I suppose someone else's may lean to the other. (Obligatory note: although double quotes (undirected speech marks) are preferred, SQLite also understands square brackets and backward apostrophes.) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
