[sqlite] Is this safe use of SELECT in an INSERT?

I want to keep a semi-persistent list of server/port pairs with an associated "index" that can be used to refer to entries elsewhere. Given: create table Servers (    serverName    text,    serverPort       integer,    serverIdx         integer unique,    primary key ( serverName, serverPort ) )

Re: [sqlite] sqlite3 feature or regression

> It is also unnecessarily complex and slow. The script demonstrates a regression (a bug). It is written in Ruby so that everybody can run it, and see its _results_. It is absolutely not interesting that it is slow or complex. -- Vermes Mátyás

Re: [sqlite] sqlite3 feature or regression

On Mon, 6 Mar 2017 18:34:40 -0500 Richard Hipp wrote: > For the benefit of those of us who do not do Ruby, perhaps you could > explain in words what you think it is that SQLite is doing > incorrectly? I am not a Ruby programmer either nor a real SQLite user. I am interested in

Re: [sqlite] SQLite Recursive Common Table Expression suggestion

Further to sqlite pivot function, matrix functions, or any other result set meta query language feature, I commented about this before with a concrete suggestion. The core problem is the awkward complexity of building a completely general virtual table (vtab) based eval("") or meta("") which

Re: [sqlite] SQLite Recursive Common Table Expression suggestion

On Tue, 7 Mar 2017 20:26:41 +0100 Clemens Ladisch wrote: > James K. Lowden wrote: > > Clemens Ladisch wrote: > >> Recursive CTEs make SQL Turing complete. > >> > >> But they cannot do everything. > > > > Isn't that a contradiction? > > Being able to

Re: [sqlite] SQLite Recursive Common Table Expression suggestion

James K. Lowden wrote: > Clemens Ladisch wrote: >> Recursive CTEs make SQL Turing complete. >> >> But they cannot do everything. > > Isn't that a contradiction? Being able to emulate a Turing machine (or a register machine) means that there exists _some_ representation of the

Re: [sqlite] Error using multiline command line argument with dot-command

On 03/08/2017 12:03 AM, Rob Golsteijn wrote: Hi List, I want to report a minor issue for the Sqlite shell. It does not handle multiline command line arguments in which the second line contains a dot-command correctly. If the same statements are passed via stdin they are handled fine. Tested

[sqlite] Error using multiline command line argument with dot-command

Hi List, I want to report a minor issue for the Sqlite shell. It does not handle multiline command line arguments in which the second line contains a dot-command correctly. If the same statements are passed via stdin they are handled fine. Tested with Sqlite 3.15.2 on Ubuntu 14.04 using Bash.

Re: [sqlite] SQLite Recursive Common Table Expression suggestion

On Tue, 7 Mar 2017 13:30:00 +0100 Clemens Ladisch wrote: > Recursive CTEs make SQL Turing complete. > > But they cannot do everything. Isn't that a contradiction? --jkl ___ sqlite-users mailing list

Re: [sqlite] sqlite3 feature or regression

On Tue, 7 Mar 2017 09:36:34 +0100 Clemens Ladisch wrote: > I do not know what you expect to happen, or what actually happens, but > changing a table and reading it through a query at the same time has > an unspecified result. It is also unnecessarily complex and slow.

[sqlite] Possible bug in cli: .schema --indent mishandles trailing comments

Here is a sample output to illustrate the problem of mishandled trailing comments. The original create table statement included two leading spaces for each attribute. $ sqlite3 ~/db-lib/data.db SQLite version 3.17.0 2017-02-13 16:02:40 Enter ".help" for usage hints. sqlite> .schema rating_answer

Re: [sqlite] SQLite Recursive Common Table Expression suggestion

Maybe so. Even simpler recursion doesn't get executed, such as a quick poll of the sqlite_master table to trigger a system-wide count(*) of all tables isn't allowed, so it seems that it's held at the gate. Even if I mock up a transaction or a thorough UNION set through a view, I need to output it

Re: [sqlite] Incorrect SEARCH link on "c3ref" page

Thanks for the report. Should be fixed now. (You will need to press "reload" or otherwise invalidate your web-browsers cache in order for the fix to work.) On 3/7/17, Graham Holden wrote: > Using the SEARCH function on (at least a couple of) the "c3ref" pages (e.g. >

Re: [sqlite] SQLite Recursive Common Table Expression suggestion

Reached back into the tape storage in my head for this one, but to paraphrase a movie older than me: the future is in pipes. http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html Note that DRH likes to mention that SQLite is meant to replace fopen() more than a full-bore RBDMS, but I

Re: [sqlite] SQLite Recursive Common Table Expression suggestion

Brian Curley wrote: > What I wonder though is if CTEs could actually serve as a stand-in for the > lack of Dynamic SQL Recursive CTEs make SQL Turing complete. But they cannot do everything. For example, when you want to do a pivot operation, the number of columns is determined by the data, and

Re: [sqlite] SQLite Recursive Common Table Expression suggestion

On Mar 7, 2017 6:56 AM, "Brian Curley" wrote: > I have successfully coupled shell scripts and the CLI I'd love to see examples of this sort of use case and I suspect that there's others who would benefit from seeing how others approach solving some of the common problems.

Re: [sqlite] Need some help running sqlite3 command line

Jacob Sylvia wrote: > I know what the problem was... bash was interpreting the `table_name` piece > as a command. I had to escape the backticks... > Yes, `command` is the old-fashioned way of saying $(command) in bash. -- Chris Green ·

Re: [sqlite] SQLite Recursive Common Table Expression suggestion

...besides, one might argue that anyone who can programmatically predict the best route for Minesweeper should actually focus on a tool that predicted the lottery (or even elections... ;) What I wonder though is if CTEs could actually serve as a stand-in for the lack of Dynamic SQL, sort of how

Re: [sqlite] SQLite 3.18.0 pre-release snapshot

On 3/7/17, Olivier Mascia wrote: >> Le 7 mars 2017 à 04:13, Richard Hipp a écrit : >> >> the database connection remembers (in RAM) specifically which >> tables and indexes it has considered for use and will only run ANALYZE >> on those tables for which some

Re: [sqlite] confused getting started

Hello, On 2017-03-05 01:10, John Albertini wrote: I can't seem to grasp what I need to download / install to use SQLite? Can someone guide me through the process? Looking to use it with RootsMagic. If you want to use a tool like dBaseIII+ to examine/modify a database created by a 3rd

Re: [sqlite] sqlite3 feature or regression

On Tue, Mar 7, 2017 at 9:36 AM, Clemens Ladisch wrote: > Vermes Mátyás wrote: > > http://comfirm.hu/pub/sqlite3-regression.rb > > > db.execute("select szamla,megnevezes from proba") do |row| > > ... > > db.execute( "update proba set megnevezes=? where

Re: [sqlite] SQLite 3.18.0 pre-release snapshot

> Le 7 mars 2017 à 04:13, Richard Hipp a écrit : > > the database connection remembers (in RAM) specifically which > tables and indexes it has considered for use and will only run ANALYZE > on those tables for which some prior query would have benefited from > having good

[sqlite] Incorrect SEARCH link on "c3ref" page

Using the SEARCH function on (at least a couple of) the "c3ref" pages (e.g. "sqlite.org/c3ref/exec.html") sends you to "sqlite.org/c3ref/search?q=xxx" (instead of "sqlite.org/search?q=xxx") and gives a page not found error instead of the search results. Graham

Re: [sqlite] SQLite Recursive Common Table Expression suggestion

Simon Slavin wrote: > I’ve seen many amusing examples of using Common Table Expressions to > solve Sudoko puzzles. Has anyone tried using one to suggest the best > next move for Minesweeper ? https://en.wikipedia.org/wiki/Minesweeper_(video_game)#Computational_complexity > have SQLite suggest a

Re: [sqlite] SQLite 3.18.0 pre-release snapshot

On Mon, 6 Mar 2017 18:52:48 -0500 Richard Hipp wrote: > On 3/6/17, Simon Slavin wrote: > > > >> See > >> https://www.sqlite.org/draft/pragma.html#pragma_optimize for > >> additional information. > > > > I?m sure this is extremely far-future-looking, but a

Re: [sqlite] sqlite3 feature or regression

Vermes Mátyás wrote: > http://comfirm.hu/pub/sqlite3-regression.rb > db.execute("select szamla,megnevezes from proba") do |row| > ... > db.execute( "update proba set megnevezes=? where szamla=?", > row[1]+"*", row[0] ) The equivalent Python code would be: for row in

Re: [sqlite] SQLite 3.18.0 pre-release snapshot

Very interesting development, thanks for pushing the boundaries at each new release! Would it be possible to consider some form of deferred optimize? ie. rather than optimize when closing the connection, it would just write the optimize info gathered during the heavy queries, for use in a future