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

[Graham Holden]

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 ) )
Is the following "safe" to ensure that the next index value is used for 
inserted rows? (It seems to work fine from the shell).
insert or ignore into Servers values (    'MyServer',    12345,    ( select 
count (*) from Servers ) )
I.e. is the "count (*)" guaranteed to be the count before the insert?
Thanks.Graham Holden
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 feature or regression

[Vermes Mátyás]

> 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  
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 feature or regression

[Vermes Mátyás]

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 
writing  SQL interfaces to http://github.com/mrev11/ccc3;>CCC to 
various databases.  Ruby was chosen only because it can be run everywhere.   
Just run the script: A select of ten rows turns into an endless loop.

Consider my post as a bug report. I do not need any workaround, and do know how 
to use WAL or duplicate database connections.

-- 
Vermes Mátyás  
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Recursive Common Table Expression suggestion

[petern]

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 communicates correctly with the query optimizer.

Things have changed somewhat since I wrote those comments.  After the
introduction of row values in 3.15 https://www.sqlite.org/rowvalue.html ,
at least, now the sqlite ecosystem can cope with efficient vector valued
data for passing parameters into and out of the hypothetical eval() or
meta() vtab module.  Presumably there are more pleasant surprises like
rowvalues on the horizon?

Has anyone put thought into how completely general sqlite sql strings could
be executed with full optimizer support within vtab code so the properties
of the resulting rowset can be exposed in the result columns returning from
the desired vtab module?

Just a thought.  Looking forward to the dazzling thread of replies!










On Tue, Mar 7, 2017 at 11:26 AM, 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 emulate a Turing machine (or a register machine) means
> that there exists _some_ representation of the data, but not that it has
> the form you actually want.  To get back to the pivot example: if I want
> multiple columns, what use are thousands of rows that encode the Turing
> machine's tape?
>
>
> Regards,
> Clemens
> ___
> 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] SQLite Recursive Common Table Expression suggestion

[James K. Lowden]

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 emulate a Turing machine (or a register machine) means
> that there exists _some_ representation of the data, but not that it
> has the form you actually want.  To get back to the pivot example: if
> I want multiple columns, what use are thousands of rows that encode
> the Turing machine's tape?

I don't know.  It's popular nowadays to posit that recursion makes SQL
Turing-complete.  While I accept any loop can be expressed as
recursion, I cannot envision your pivot-table query without some form
of dynamic SQL.  How else to provide to the interpreter the names of
the output columns?  

--jkl

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Recursive Common Table Expression suggestion

[Clemens Ladisch]

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 data, but not that it has
the form you actually want.  To get back to the pivot example: if I want
multiple columns, what use are thousands of rows that encode the Turing
machine's tape?


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Dan Kennedy]

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 with Sqlite 3.15.2 on Ubuntu 14.04 using Bash.

Example:

Passing statements via stdin works fine:


echo "SELECT 1;
.mode csv
SELECT 1;" | sqlite3 mydb.sq3



(no error)


Passing the statements via a command line argument gives an error:


sqlite3 mydb.sq3 "SELECT 1;
.mode csv
SELECT 1;"

Error: near ".": syntax error


A work around is:

  sqlite3 mydb.sq3 "SELECT 1" ".mode csv" "SELECT 1"

Dan.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Rob Golsteijn]

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.

Example:

Passing statements via stdin works fine:


echo "SELECT 1;
.mode csv
SELECT 1;" | sqlite3 mydb.sq3



(no error)


Passing the statements via a command line argument gives an error:


sqlite3 mydb.sq3 "SELECT 1;
.mode csv
SELECT 1;"

Error: near ".": syntax error



Regards,
Rob Golsteijn



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Recursive Common Table Expression suggestion

[James K. Lowden]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 feature or regression

[James K. Lowden]

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.  

To the OP, Vermes: if you are updating row-by-row, then you are
combining some information in your application with other information
in the database.  Instead of combining that information using
application logic, use the DBMS.  Insert the application's information
into the database, and use a single UPDATE statement to apply it.  

SQL lets you work with sets.  Instead of thinking about each
"megnevezes", think about the set of "megnevezes" that share a
particular "szamla".  You'll write less code that way, and get done
sooner.  

--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Trevor]

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
CREATE TABLE rating_answer (
  idinteger primary key,
  idQuestionreferences rating_question (id),
  response  integer not null,  -- 1=strongly dislike, 5=strongly like
  answertexttext not null,
  statusinteger, -- 0 not active, 1=active
  datecreated   text not null
);
CREATE UNIQUE INDEX rating_answer_idx on rating_answer (idQuestion, response);

sqlite> .schema --indent rating_answer
CREATE TABLE rating_answer(
  id integer primary key,
  idQuestion references rating_question(id),
  response integer not null,
  -- 1=strongly dislike,
  5=strongly like
  answertext text not null,
  status integer,
  -- 0 not active,
  1=active
  datecreated text not null
);
CREATE UNIQUE INDEX rating_answer_idx on rating_answer(
  idQuestion,
  response
);

sqlite> .q
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Recursive Common Table Expression suggestion

[Brian Curley]

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
just to read in as an update.

Regards.

Brian P Curley



On Mar 7, 2017 7:30 AM, "Clemens Ladisch"  wrote:

> 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 you
> cannot construct and execute that query only from within SQLite.
>
>
> Regards,
> Clemens
> ___
> 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] Incorrect SEARCH link on "c3ref" page

[Richard Hipp]

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.
> "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
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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] SQLite Recursive Common Table Expression suggestion

[Brian Curley]

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 think that the CLI is often overlooked. You
can use it ad hoc, or in tandem with existing DBs, just like you can stream
data to the shell for other commercial products, like sqlplus.

There is quite a bit out there.

Regards.

Brian P Curley



On Mar 7, 2017 7:04 AM, "Michael Tiernan"  wrote:

> 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.
>
> Does anyone know where knowledge like this is shared? (Specifically aimed
> towards users of SQLite?)
> ___
> 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] SQLite Recursive Common Table Expression suggestion

[Clemens Ladisch]

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 you
cannot construct and execute that query only from within SQLite.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Recursive Common Table Expression suggestion

[Michael Tiernan]

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.

Does anyone know where knowledge like this is shared? (Specifically aimed
towards users of SQLite?)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Chris Green]

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
·

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Recursive Common Table Expression suggestion

[Brian Curley]

...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 triggers can sometimes serve in place of a
procedural language. I have successfully coupled shell scripts and the CLI
but in cases where one is limited to desktop options, this would really be
pretty awesome.

Regards.

Brian P Curley



On Mar 7, 2017 3:46 AM, "Clemens Ladisch"  wrote:

> 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 good next move.
>
> Define "good".  ;-)
>
>
> Regards,
> Clemens
> ___
> 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] SQLite 3.18.0 pre-release snapshot

[Richard Hipp]

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 prior query would have benefited from
>> having good sqlite_stat1 numbers during the current session.
>
> What if or what impact is there from this pragma optimize when the software
> has been built with SQLITE_ENABLE_STAT4?

Then the ANALYZE commands that are run will also build the
sqlite_stat4 table.  But STAT4 data is not a factor in determining
when ANALYZE is run.

-- 
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] confused getting started

[Cezary H. Noweta]

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 party, then you will have to download a shell binary: 
http://sqlite.org/download.html => ``Precompiled Binaries for ...''.



Been using PCs since the mid 1980s and have used dBase III+ and Approach
previously.


From http://sqlite.org/about.html: ``SQLite is an in-process library 
that implements a self-contained, serverless, zero-configuration, 
transactional SQL database engine.'' Primarily, SQLite is a library. 
Let's consider the following dBase program:


==
SET TALK OFF
CLEAR
CLEAR ALL
STORE 0 TO TOTAL_EMPLOYEES
STORE 0 TO TOTAL_SALARY
USE EMPLOYEE
DO WHILE .NOT. EOF()
  TOTAL_EMPLOYEES = TOTAL_EMPLOYEES + 1
  TOTAL_SALARY = TOTAL_SALARY + SALARY
  SKIP
ENDDO
@1,1 SAY "Average salary: $"
IF 0 < TOTAL_EMPLOYEES
  @1,18 SAY TOTAL_SALARY / TOTAL_EMPLOYEES PICTURE ".99"
ELSE
  @1,18 SAY "0.00"
ENDIF
SET TALK ON
==

If dBase had been SQLite, then you would have created the 
following--like ``salary.c'' file:


==
#include "dbase3.h"

int main()
{
  dbase3 *db;
  int result;

  result = dbase3_context_init();

  if ( DBASE3_OK == result ) {
dbase3_exec(db,
  "SET TALK OFF\n"
  "CLEAR\n" /* ... Rest of the program omitted */ );
dbase3_context_done(db);
  }

  return 0;
}
==

and compiled it: ``cl salary.c dbase3.c'', and run a resulting 
salary.exe file.


Certainly, SQLite does not implement xBase/Vulcan language, in exchange 
it uses SQL (similar to SQL subsystem of DOS' FoxPro 2.5 or 2.6 AFAIR). 
There are no separate files per table/index, There is one 
self--containing file per database (which contains all tables and indices).


If you are interested in the preceding scenario, then you will have to 
download an amalgamation (all SQLite in one file): 
http://sqlite.org/download.html => ``Source Code'' => 
``sqlite-amalgamation-*.zip''. It goes a working example:


1. Unzip it, and compile the shell: ``cl shell.c sqlite3.c''.

2. Run ``shell.exe'' and execute the following commands:
   .open company.db
   CREATE TABLE employees (salary);
   INSERT INTO employees VALUES (1000), (1120), (920), (840), (1220);
   .quit

Now, you have created ``company.db'' SQLite database file.

3. Create ``salary.c'' with the following content:
==
#include 
#include "sqlite3.h"

int moneyDisp(void *unused, int cc, char *values[], char *names[])
{
  (void)unused;
  (void)cc;
  (void)values;
  (void)names;

  if ( 0 < cc ) {
if ( NULL != names[0] && 0 != names[0][0] ) {
  printf("%s: ", names[0]);
}
printf("$%s\n", NULL == values[0] || 0 == values[0][0] ? "0" : 
values[0]);

  }

  return 0;
}

int main()
{
  int result;
  sqlite3 *db;
  char *errMsg;

  result = sqlite3_open("company.db", );

  if ( SQLITE_OK != result ) {
/* open error handling */
  } else {

result = sqlite3_exec(db,
  "SELECT AVG(salary) AS 'Average salary' FROM employees;",
  moneyDisp,
  NULL,
  );

if ( SQLITE_OK != result ) {
  /* select error handling */
}

sqlite3_free(errMsg);

result = sqlite3_close(db);

if ( SQLITE_OK != result ) {
  /* close error handling */
}
  }

  return 0;
}
==

4. Compile and run:
   cl salary.c sqlite3.c
   salary.exe

You should receive something like:

Average salary: $1020.0

-- best regards

Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 feature or regression

[Dominique Devienne]

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 szamla=?",
> row[1]+"*", row[0] )
>
> The equivalent Python code would be:
>
>   for row in db.execute("select ... from proba"):
> db.execute("update proba ...")
>
> > The linked ruby script demonstrates a feature of the newer sqlite3
> libraries, which may be a regression.
>
> 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.  In particular, the database might either fetch the
> next result on demand from the actual table or have some result rows
> already precomputed, so it is undefined whether continuing the SELECT
> sees the old values or the new values or both or neither.
>

Right, unless you use WAL mode for MVCC, and do the updates on a separate
connection and transaction.
That way you iterate on the old rows at the time (snapshot, SCN, etc...)
the select transaction starts,
and the updates (on that other connection, in a different transaction) do
not affect that select.

At least that's the conceptual model I have in my head for SQLite in WAL
mode.
If that's incorrect, please let me know. Thanks, --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.18.0 pre-release snapshot

[Olivier Mascia]

> 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_stat1 numbers during the current session.

What if or what impact is there from this pragma optimize when the software has 
been built with SQLITE_ENABLE_STAT4?

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Incorrect SEARCH link on "c3ref" page

[Graham Holden]

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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Recursive Common Table Expression suggestion

[Clemens Ladisch]

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 good next move.

Define "good".  ;-)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.18.0 pre-release snapshot

[Eduardo Morras]

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 default mask
> > of 254 (0xfe) might be better than the stated default of 14 (0x0e).
> 
> Default mask changed to 0xfffe, which allows for up to 14 new
> default-on optimizations and up to 48 new default-off optimizations.

Could a trigger be fired on optimize? This way we could add database/schema 
specific optimizations (Delete all rows in table tab where column value is 
NULL, for example)

Thanks for the great work.

> -- 
> D. Richard Hipp
> d...@sqlite.org


---   ---
Eduardo Morras 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 feature or regression

[Clemens Ladisch]

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 db.execute("select ... from proba"):
db.execute("update proba ...")

> The linked ruby script demonstrates a feature of the newer sqlite3 libraries, 
> which may be a regression.

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.  In particular, the database might either fetch the
next result on demand from the actual table or have some result rows
already precomputed, so it is undefined whether continuing the SELECT
sees the old values or the new values or both or neither.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.18.0 pre-release snapshot

[Eric Grange]

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
optimize.

The rational is that heavy queries can come over several hours (and
multiple DB connections). During those "rush hours" running an ANALYZE
could be quite detrimental when the databases are large (even for read-only
queries, it would affect the service performance through I/O usage), and in
my particular usage case, somewhat less efficient queries are less of a
problem than stalling the service.

Once the rush hours have passed, the optimize would be run with the
previously collected data (I currently run a regular ANALYZE outside of
rush hours, but I gather it is only rarely beneficial).

Eric

On Tue, Mar 7, 2017 at 4:22 AM, jose isaias cabrera 
wrote:

>
> Richard Hipp wrote...
>
> On 3/6/17, jose isaias cabrera  wrote:
>
>>
>> Richard Hipp wrote...
>>
>> Have you ever wondered when you should run ANALYZE on an SQLite
>>> database?  It is tricky to figure out when that is appropriate.  The
>>>
>> Thanks for this.  I actually run this ANALYZE weekly with a script.  This
>> will be better.  I can run it everyday don't do any harm.  Thanks.
>>
>
> Thanks for letting me know.  I don't know if this applies in your case
>> or not, but reading your note made me realize that the documentation
>> might be misleading and/or unclear as written.
>>
>
> The "PRAGMA optimize" command should be run from the same database
>> connection that is doing the heavy queries.  The reason for this is
>> that 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_stat1 numbers during the current session.  That is
>> why "PRAGMA optimize" should be run as the database connection is
>> closing, rather than when it is first opened.
>>
>
> So it is not (currently) helpful to run "PRAGMA optimize" from a
>> separate connection, or a connection that is mostly idle.  It needs to
>> be the connection that is actually doing the interesting queries so
>> that SQLite can know which tables need to be analyzed.
>>
>
> This is exactly how it's going to be used...
>
> josé
> ___
> 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