Re: [sqlite] SQLite DELUXE Re: sqlite command line tool NUL support

2018-01-25 Thread Luke Amery
https://www.sqlite.org/privatebranch.html

This command both creates the new repository and populates it with all the
latest SQLite could
->
This command both creates the new repository and populates it with all the
latest SQLite code

On Wed, Jan 24, 2018 at 10:44 PM Richard Hipp  wrote:

> On 1/24/18, petern  wrote:
> > Have you worked out an automated way for your changes to shadow and
> > auto-merge from the official trunk?
>
> https://www.sqlite.org/privatebranch.html
>
> --
> 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] SQLite DELUXE Re: sqlite command line tool NUL support

2018-01-24 Thread Richard Hipp
On 1/24/18, petern  wrote:
> Have you worked out an automated way for your changes to shadow and
> auto-merge from the official trunk?

https://www.sqlite.org/privatebranch.html

-- 
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 DELUXE Re: sqlite command line tool NUL support

2018-01-24 Thread J Decker
On Wed, Jan 24, 2018 at 1:30 PM, petern  wrote:

> Have you worked out an automated way for your changes to shadow and
> auto-merge from the official trunk?  That is, aside from collision edits,
> is it automated?  Longer term, your shadow distribution also needs
> new/merged test cases and a regression test run to re-qualify the merged
> changes at each cycle.
>
>
(yes, git pretty much manages a 'if merged with master compatibility')
Sometimes there may be modifications in master that affect the patch (so
far there may have been one or two)

(against my own fossil->github echo which can get me fossil head without
delay)
https://github.com/d3x0r/sqlite3/compare/escape-inline-nul-strings

then I port to this... making sure tests pass at least as well as expected
(usually)
(against mackyle repo which lags the official repository a little)
https://github.com/mackyle/sqlite/compare/master...d3x0r:escape-shell-inline-nuls

(fossil head composite)
https://github.com/d3x0r/sqlite3/compare/MySqlite
(against mackle; which would work great for a base of an official switch
(if never))
https://github.com/mackyle/sqlite/compare/master...d3x0r:MySqlite


> It seems to me there are many small usability changes which could be
> incorporated into a more comprehensive SQLite shadow release,  call it
> "SQLite DELUXE".  For example, here is another straightforward and useful
> change by Cezary that was never adopted despite having zero impact by
> conditional compilation switch:  http://sqlite.chncc.eu/
>
> The argument against improving generality of the official release because
> it slows down some phones will never go away.  Your changes may have to
> shadow the official release forever.
>
> Peter
>
> On Wed, Jan 24, 2018 at 11:46 AM, J Decker  wrote:
>
> > This is a picture.  This is a tortoise git log view of merges.
> >
> > https://drive.google.com/open?id=1RosGlprJRoutFsou2XDRlflxc8xWoHks
> >
> > On Wed, Jan 24, 2018 at 8:59 AM, J Decker  wrote:
> >
> > >
> > > So for output of a select in the shell ( unlike .dump ) is this.
> > >
> > > for(i=1; i > >   utf8_printf(p->out, ",%s", sqlite3_column_text(pSelect, i));
> > > }
> > >
> > > option 1) add text conversion in the column_text to escape NUL
> > characters.
> > > in TEXT... then everyone everywhere would inherit the change.
> > > 'asdf' is as valid as ''||'a'||'s'||'d'||'f'||''
> > >
> > > ( the postgres/sqlite way is to use  "\'||CHAR(0)||\'" (as a C string)
> if
> > > '\0' is required outside of a bind )
> > >
> > > 2) output literal characters regardless of terminal...
> > > //utf8_printf(p->out, "%s", z);
> > > raw_fwrite( z, 1, sqlite3_column_bytes(pSelect, 0), p->out);
> > >  for(i=1; i > >   //utf8_printf(p->out, ",%s", sqlite3_column_text(pSelect, i));
> > >   raw_fputc(p->out, ',');
> > >   raw_fwrite( sqlite3_column_text(pSelect, i), 1,
> > >   sqlite3_column_bytes(pSelect, 0), p->out);
> > > }
> > >
> > > 3)
> > >
> > > .dump uses
> > > output_quoted_string
> > > output_quoted_escaped_string
> > > (the latter of which will wrap "text\r\n" with
> > > replace(replace("text\\r\\n" ),'\\r',char(13)),'\\n',char(10))
> > > so it could additionally wrap that with
> > >
> > > the latter of which will wrap "u\0tf\r\n" with
> > > replace(replace(replace("u\\0tf\\r\\n" ),'\\r',char(13)),'\\n',char(1
> > > 0)),'\\0',char(0))
> > >
> > > instead of
> > > replace(replace("u'||CHAR(0)||'tf\\r\\n" ),'\\r',char(13)),'\\n',char(
> > 10))
> > >
> > >
> > >
> > >
> > > --
> > > * Change select * output to write full length text value
> > > * Change .dump output to include supporting char(0) substitution ( !
> > Shell
> > > Newlines )
> > > * Change other .dump output to inline subst '||CHAR(0)||'  (
> > > ShellHasFlag(p, SHFLG_Newlines) )
> > >
> > > https://drive.google.com/open?id=1UXaFBRoNypZBhkpfPh7TAwncJMbBT1cH
> > >
> > >
> > > Sample output (shell without newlines)
> > > "d\n and" has a char \x14
> > >
> > > (shell only; doesn't change test1.c)
> > >
> > > SQLite version 3.23.0 2018-01-24 01:58:49
> > > Enter ".help" for usage hints.
> > > sqlite> .dump test
> > > PRAGMA foreign_keys=OFF;
> > > BEGIN TRANSACTION;
> > > CREATE TABLE test (a);
> > > INSERT INTO test VALUES(0);
> > > INSERT INTO test VALUES(replace('TEST\0NUL\0','\0',char(0)));
> > > INSERT INTO test VALUES(replace('TEST\0\0NUL\0','\0',char(0)));
> > > INSERT INTO test VALUES(0);
> > > INSERT INTO test VALUES(0);
> > > INSERT INTO test VALUES(replace(replace(replace('char test\rand\n\0
> and
> > > all\0','\r',char(13)),'\n',char(10)),'\0',char(0)));
> > > INSERT INTO test VALUES(replace(replace('char test\rand\n and
> > > all(null)','\r',char(13)),'\n',char(10)));
> > > INSERT INTO test VALUES(replace(replace('char test\rand\n and
> > > all(null)','\r',char(13)),'\n',char(10)));
> > > INSERT INTO test VALUES(replace(replace(replace('char test\rand\n\0
> 

[sqlite] SQLite DELUXE Re: sqlite command line tool NUL support

2018-01-24 Thread petern
Have you worked out an automated way for your changes to shadow and
auto-merge from the official trunk?  That is, aside from collision edits,
is it automated?  Longer term, your shadow distribution also needs
new/merged test cases and a regression test run to re-qualify the merged
changes at each cycle.

It seems to me there are many small usability changes which could be
incorporated into a more comprehensive SQLite shadow release,  call it
"SQLite DELUXE".  For example, here is another straightforward and useful
change by Cezary that was never adopted despite having zero impact by
conditional compilation switch:  http://sqlite.chncc.eu/

The argument against improving generality of the official release because
it slows down some phones will never go away.  Your changes may have to
shadow the official release forever.

Peter

On Wed, Jan 24, 2018 at 11:46 AM, J Decker  wrote:

> This is a picture.  This is a tortoise git log view of merges.
>
> https://drive.google.com/open?id=1RosGlprJRoutFsou2XDRlflxc8xWoHks
>
> On Wed, Jan 24, 2018 at 8:59 AM, J Decker  wrote:
>
> >
> > So for output of a select in the shell ( unlike .dump ) is this.
> >
> > for(i=1; i >   utf8_printf(p->out, ",%s", sqlite3_column_text(pSelect, i));
> > }
> >
> > option 1) add text conversion in the column_text to escape NUL
> characters.
> > in TEXT... then everyone everywhere would inherit the change.
> > 'asdf' is as valid as ''||'a'||'s'||'d'||'f'||''
> >
> > ( the postgres/sqlite way is to use  "\'||CHAR(0)||\'" (as a C string) if
> > '\0' is required outside of a bind )
> >
> > 2) output literal characters regardless of terminal...
> > //utf8_printf(p->out, "%s", z);
> > raw_fwrite( z, 1, sqlite3_column_bytes(pSelect, 0), p->out);
> >  for(i=1; i >   //utf8_printf(p->out, ",%s", sqlite3_column_text(pSelect, i));
> >   raw_fputc(p->out, ',');
> >   raw_fwrite( sqlite3_column_text(pSelect, i), 1,
> >   sqlite3_column_bytes(pSelect, 0), p->out);
> > }
> >
> > 3)
> >
> > .dump uses
> > output_quoted_string
> > output_quoted_escaped_string
> > (the latter of which will wrap "text\r\n" with
> > replace(replace("text\\r\\n" ),'\\r',char(13)),'\\n',char(10))
> > so it could additionally wrap that with
> >
> > the latter of which will wrap "u\0tf\r\n" with
> > replace(replace(replace("u\\0tf\\r\\n" ),'\\r',char(13)),'\\n',char(1
> > 0)),'\\0',char(0))
> >
> > instead of
> > replace(replace("u'||CHAR(0)||'tf\\r\\n" ),'\\r',char(13)),'\\n',char(
> 10))
> >
> >
> >
> >
> > --
> > * Change select * output to write full length text value
> > * Change .dump output to include supporting char(0) substitution ( !
> Shell
> > Newlines )
> > * Change other .dump output to inline subst '||CHAR(0)||'  (
> > ShellHasFlag(p, SHFLG_Newlines) )
> >
> > https://drive.google.com/open?id=1UXaFBRoNypZBhkpfPh7TAwncJMbBT1cH
> >
> >
> > Sample output (shell without newlines)
> > "d\n and" has a char \x14
> >
> > (shell only; doesn't change test1.c)
> >
> > SQLite version 3.23.0 2018-01-24 01:58:49
> > Enter ".help" for usage hints.
> > sqlite> .dump test
> > PRAGMA foreign_keys=OFF;
> > BEGIN TRANSACTION;
> > CREATE TABLE test (a);
> > INSERT INTO test VALUES(0);
> > INSERT INTO test VALUES(replace('TEST\0NUL\0','\0',char(0)));
> > INSERT INTO test VALUES(replace('TEST\0\0NUL\0','\0',char(0)));
> > INSERT INTO test VALUES(0);
> > INSERT INTO test VALUES(0);
> > INSERT INTO test VALUES(replace(replace(replace('char test\rand\n\0 and
> > all\0','\r',char(13)),'\n',char(10)),'\0',char(0)));
> > INSERT INTO test VALUES(replace(replace('char test\rand\n and
> > all(null)','\r',char(13)),'\n',char(10)));
> > INSERT INTO test VALUES(replace(replace('char test\rand\n and
> > all(null)','\r',char(13)),'\n',char(10)));
> > INSERT INTO test VALUES(replace(replace(replace('char test\rand\n\0 and
> > all 23\0','\r',char(13)),'\n',char(10)),'\0',char(0)));
> > INSERT INTO test VALUES(replace(replace(replace('char test\rand\n\0 and
> > all merged content\0','\r',char(13)),'\n',char(10)),'\0',char(0)));
> > COMMIT;
> > sqlite> select * from test
> >...> ;
> > 0
> > TEST NUL
> > TEST  NUL
> > 0
> > 0
> > andr test
> >   and all
> > andr test
> > and all
> > andr test
> > and all
> > andr test
> >   and all 23
> > andr test
> >   and all merged content
> > 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] command shell .timeout

2017-08-03 Thread Roman Fleysher
Thank you, Richard.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Richard Hipp [d...@sqlite.org]
Sent: Thursday, August 03, 2017 3:38 PM
To: SQLite mailing list
Subject: Re: [sqlite] command shell .timeout

On 8/3/17, Roman Fleysher <roman.fleys...@einstein.yu.edu> wrote:
> Dear SQLiters,
>
> I am using sqlit3 command shell. It has ".timeout" command. What is the
> difference between:
>
> .timeout MS
> PRAGMA busy_timeout = milliseconds;

They accomplish the same thing.  The ".timeout" command (which I had
forgotten about) existed long before the "PRAGMA busy_timeout" command
was created.  Both commands invoke sqlite3_busy_timeout() for you.
--
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] command shell .timeout

2017-08-03 Thread Richard Hipp
On 8/3/17, Roman Fleysher  wrote:
> Dear SQLiters,
>
> I am using sqlit3 command shell. It has ".timeout" command. What is the
> difference between:
>
> .timeout MS
> PRAGMA busy_timeout = milliseconds;

They accomplish the same thing.  The ".timeout" command (which I had
forgotten about) existed long before the "PRAGMA busy_timeout" command
was created.  Both commands invoke sqlite3_busy_timeout() for you.
-- 
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] Command line not accepting multi-line statements.

2016-11-08 Thread John G
Thanks, that worked.

John Gillespie

On 8 November 2016 at 14:30, Richard Hipp  wrote:

> On 11/8/16, John G  wrote:
> > I normally use the 3.8.8.3 supplied with MacOS El Capitan.
> > I downloaded version 3.15.1 from the Download page - precompiled
> > command-line tools : (sqlite-tools-osx-x86-3150100.zip).
> >
> > When I tried copying and pasting multiple or multi-line statements from
> my
> > editor (jEdit) the command-line shell ignored everything after the first
> > line. This is not the case with 3.8.8.3.
> >
>
> This appears to be a bug in the "linenoise" library that we link
> against when building the precompiled shell - it has nothing to do
> with SQLite.  See the https://github.com/antirez/linenoise/issues/75
> bug report.  We first started linking precompiled SQLite binaries
> against linenoise with 3.8.9 (2015-04-08) and you are the first person
> to notice the difference.
>
> We'll see if we can't update the linenoise implementations on our
> build machines and upload new binaries for 3.15.1
>
> In the meantime, you can always build SQLite yourself from sources.
> On a mac, just type "./configure; make" (after installing Xcode, which
> is free on the apple store).
>
> --
> 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] Command line not accepting multi-line statements.

2016-11-08 Thread Dominique Devienne
On Tue, Nov 8, 2016 at 3:30 PM, Richard Hipp  wrote:

> On 11/8/16, John G  wrote:
> > I normally use the 3.8.8.3 supplied with MacOS El Capitan.
> > I downloaded version 3.15.1 from the Download page - precompiled
> > command-line tools : (sqlite-tools-osx-x86-3150100.zip).
> >
> > When I tried copying and pasting multiple or multi-line statements from
> my
> > editor (jEdit) the command-line shell ignored everything after the first
> > line. This is not the case with 3.8.8.3.
> >
>
> This appears to be a bug in the "linenoise" library that we link
> against when building the precompiled shell - it has nothing to do
> with SQLite.  See the https://github.com/antirez/linenoise/issues/75
> bug report.  We first started linking precompiled SQLite binaries
> against linenoise with 3.8.9 (2015-04-08) and you are the first person
> to notice the difference.
>
> We'll see if we can't update the linenoise implementations on our
> build machines and upload new binaries for 3.15.1
>
> In the meantime, you can always build SQLite yourself from sources.
> On a mac, just type "./configure; make" (after installing Xcode, which
> is free on the apple store).
>

FWIW, I'm using linenoise-ng in my own command line app (see below),
wrote a couple commands in VS, each on its own line, and pasted them to
my linenoise-ng based app running on Linux in a Putty window, and
both lines where pasted (and executed) OK.

Unlike the original, the -ng variant works OK on Windows,
but is C++11 based. But it doesn't seem to be bug free either,
got a crash using its linenoiseHistorySetMaxLen().

Supports prompt coloring using ANSI escape codes too,
which work in both the DOS prompt, Putty, and RedHat terminal.

Since I consider this a bit "experimental", I have an SQLite-inspired
.linenoise on/off "dot" command, to switch to/from basic cout/getstring :)

// From https://github.com/arangodb/linenoise-ng
// Commit df1cfb41e3de9d2e716016d0571338ceed62290f
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Command line not accepting multi-line statements.

2016-11-08 Thread Richard Hipp
On 11/8/16, John G  wrote:
> I normally use the 3.8.8.3 supplied with MacOS El Capitan.
> I downloaded version 3.15.1 from the Download page - precompiled
> command-line tools : (sqlite-tools-osx-x86-3150100.zip).
>
> When I tried copying and pasting multiple or multi-line statements from my
> editor (jEdit) the command-line shell ignored everything after the first
> line. This is not the case with 3.8.8.3.
>

This appears to be a bug in the "linenoise" library that we link
against when building the precompiled shell - it has nothing to do
with SQLite.  See the https://github.com/antirez/linenoise/issues/75
bug report.  We first started linking precompiled SQLite binaries
against linenoise with 3.8.9 (2015-04-08) and you are the first person
to notice the difference.

We'll see if we can't update the linenoise implementations on our
build machines and upload new binaries for 3.15.1

In the meantime, you can always build SQLite yourself from sources.
On a mac, just type "./configure; make" (after installing Xcode, which
is free on the apple store).

-- 
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] Command line shell not flushing stderr when interactive

2014-01-22 Thread Peter Aronson
Microsoft seems to only make the stderr stream unbuffered when writing to a 
character device: "The stdout and stderr functions are flushed whenever they 
are full or, if you are writing to a character device, after each library 
call."  It doesn't seem to consider pipe that emacs is reading from a character 
device.  This seems to violate the ISO C standard, which I believe requires 
stderr to be unbuffered or line-buffered at start-up, but I doubt that 
Microsoft is losing any sleep over that.
 
My office mate, who also uses Emacs on Windows, modified our local copy of 
shell.c to deal with this.  First, he made a similar addition of a fflush that 
you did (our changes are bracketed by ifdef ESRI):
 
static int process_input(struct callback_data *p, FILE *in){
  char *zLine = 0;
  char *zSql = 0;
  int nSql = 0;
  int nSqlPrior = 0;
  char *zErrMsg;
  int rc;
  int errCnt = 0;
  int lineno = 0;
  int startline = 0;
  while( errCnt==0 || !bail_on_error || (in==0 && stdin_is_interactive) ){
#ifdef ESRI
    fflush(stderr);
#endif
    fflush(p->out);
    free(zLine);
 
And made this addition to main to get interactive behavior when running is an 
Emacs *shell* window:
 
int main(int argc, char **argv){
  char *zErrMsg = 0;
  struct callback_data data;
  const char *zInitFile = 0;
  char *zFirstCmd = 0;
  int i;
  int rc = 0;
  if( strcmp(sqlite3_sourceid(),SQLITE_SOURCE_ID)!=0 ){
    fprintf(stderr, "SQLite header and source version mismatch\n%s\n%s\n",
    sqlite3_sourceid(), SQLITE_SOURCE_ID);
    exit(1);
  }
  Argv0 = argv[0];
  main_init();
  stdin_is_interactive = isatty(0);
#ifdef ESRI
  if (!stdin_is_interactive) {
    /* If Emacs shell window's TERM is set to "emacs". 
    ** Then set interactive mode on to redirect STDIN to Emacs shell window.
    */
    char *env_var = getenv("TERM");
    if (env_var) {
  if (!strcmp (env_var, "emacs"))
    stdin_is_interactive = 1;
    }
  }
#endif

Peter

From: Christopher Wellons 
>To: sqlite-users@sqlite.org 
>Sent: Sunday, January 19, 2014 9:10 AM
>Subject: [sqlite] Command line shell not flushing stderr when interactive
>
>
>
>When the shell is set to interactive (i.e. "-interactive"), the output
>(stdout) is flushed with every prompt (shell.c:422) but stderr is not.
>In some situations this leads to no error messages being displayed until
>the stderr buffer fills.
>
>This happens when running the official sqlite3 binary as subprocess of
>Emacs under Windows 7. The error messages do not appear in a timely
>fashion. I was unable to trigger the misbehavior in a plain shell so my
>only demo is a bit of Emacs Lisp. When this Elisp code below is run, a
>buffer will pop up that *should* contain the output of .help. Under
>Windows it does not. The same occurs even when it's launched via a shell
>subprocess using "2>&1", so it's not simply an issue with Emacs not
>reading from the subprocess's stderr output fast enough.
>
>    (let* ((buffer (generate-new-buffer "sqlite"))
>          (proc (start-process "sqlite" buffer "sqlite3" "-interactive")))
>      (process-send-string proc ".help\n")
>      (pop-to-buffer buffer))
>
>I suspect it has to do with being compiled without readline, which is
>why it behaves better elsewhere. I couldn't figure out how to link with
>libreadline on Windows, though, so I couldn't test this.
>
>With the following change to the amalgamation release I got the behavior
>I was looking for: timely error messages from the SQLite command line
>shell. I understand this is probably not the Right Way to do this, but
>it's just a demonstation of a possible fix.
>
>--- a/shell.c
>+++ b/shell.c
>@@ -418,6 +418,7 @@ static char *one_input_line(FILE *in, char *zPrior, int 
>isCont
>    zResult = readline(zPrompt);
>    if( zResult && *zResult ) add_history(zResult);
>#else
>+    fflush(stderr);
>    printf("%s", zPrompt);
>    fflush(stdout);
>    zResult = local_getline(zPrior, stdin);
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Command line shell not flushing stderr when interactive

2014-01-19 Thread Simon Slavin

On 19 Jan 2014, at 7:32pm, Luuk  wrote:

> It is acceptable—and normal—for standard output and standard error to be 
> directed to the same destination, such as the text terminal. Messages appear 
> in the same order as the program writes them, unless buffering is involved. 
> (For example, a common situation is when the standard error stream is 
> unbuffered but the standard output stream is line-buffered; in this case, 
> text written to standard error later may appear on the terminal earlier, if 
> the standard output stream's buffer is not yet full.)
> 
> source:
> http://en.wikipedia.org/wiki/Standard_streams#Standard_error_.28stderr.29

Buffering matters only if an app is going to use stderr as a warning stream 
instead of its original purpose of "I'm about to crash and here's why.".

When stderr was thought up, a program wrote some text to it just before it 
quit.  The question of buffering wasn't important because any buffer would be 
flushed an instant later when the program that wrote it quit.  So it didn't 
matter whether stderr was buffered or not.

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


Re: [sqlite] Command line shell not flushing stderr when interactive

2014-01-19 Thread Luuk

On 19-01-2014 19:59, Christopher Wellons wrote:



When the shell is set to interactive (i.e. "-interactive"), the output
(stdout) is flushed with every prompt (shell.c:422) but stderr is not.



Stderr is suppose to be unbuffered so that flushing is not required.  Or is
that different for windows?


According to the stderr Linux man page stderr is unbuffered, which would
be why I'm not having a problem in Linux:


The stream stderr is unbuffered.  The  stream  stdout  is  line-buffered
when  it  points  to  a  terminal.


I'm unable to find any documentation about this for Windows, but since
I'm seeing stderr buffering it must not be unbuffered in Windows.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



Is this not biting you?


It is acceptable—and normal—for standard output and standard error to be 
directed to the same destination, such as the text terminal. Messages 
appear in the same order as the program writes them, unless buffering is 
involved. (For example, a common situation is when the standard error 
stream is unbuffered but the standard output stream is line-buffered; in 
this case, text written to standard error later may appear on the 
terminal earlier, if the standard output stream's buffer is not yet full.)


source:
http://en.wikipedia.org/wiki/Standard_streams#Standard_error_.28stderr.29

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


Re: [sqlite] Command line shell not flushing stderr when interactive

2014-01-19 Thread Christopher Wellons

>> When the shell is set to interactive (i.e. "-interactive"), the output
>> (stdout) is flushed with every prompt (shell.c:422) but stderr is not.

> Stderr is suppose to be unbuffered so that flushing is not required.  Or is
> that different for windows?

According to the stderr Linux man page stderr is unbuffered, which would
be why I'm not having a problem in Linux:

> The stream stderr is unbuffered.  The  stream  stdout  is  line-buffered
> when  it  points  to  a  terminal.

I'm unable to find any documentation about this for Windows, but since
I'm seeing stderr buffering it must not be unbuffered in Windows.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Command line shell not flushing stderr when interactive

2014-01-19 Thread Richard Hipp
On Sun, Jan 19, 2014 at 11:10 AM, Christopher Wellons <
well...@nullprogram.com> wrote:

>
> When the shell is set to interactive (i.e. "-interactive"), the output
> (stdout) is flushed with every prompt (shell.c:422) but stderr is not.
>

Stderr is suppose to be unbuffered so that flushing is not required.  Or is
that different for windows?



> In some situations this leads to no error messages being displayed until
> the stderr buffer fills.
>
> This happens when running the official sqlite3 binary as subprocess of
> Emacs under Windows 7. The error messages do not appear in a timely
> fashion. I was unable to trigger the misbehavior in a plain shell so my
> only demo is a bit of Emacs Lisp. When this Elisp code below is run, a
> buffer will pop up that *should* contain the output of .help. Under
> Windows it does not. The same occurs even when it's launched via a shell
> subprocess using "2>&1", so it's not simply an issue with Emacs not
> reading from the subprocess's stderr output fast enough.
>
> (let* ((buffer (generate-new-buffer "sqlite"))
>(proc (start-process "sqlite" buffer "sqlite3" "-interactive")))
>   (process-send-string proc ".help\n")
>   (pop-to-buffer buffer))
>
> I suspect it has to do with being compiled without readline, which is
> why it behaves better elsewhere. I couldn't figure out how to link with
> libreadline on Windows, though, so I couldn't test this.
>
> With the following change to the amalgamation release I got the behavior
> I was looking for: timely error messages from the SQLite command line
> shell. I understand this is probably not the Right Way to do this, but
> it's just a demonstation of a possible fix.
>
> --- a/shell.c
> +++ b/shell.c
> @@ -418,6 +418,7 @@ static char *one_input_line(FILE *in, char *zPrior,
> int isCont
>  zResult = readline(zPrompt);
>  if( zResult && *zResult ) add_history(zResult);
>  #else
> +fflush(stderr);
>  printf("%s", zPrompt);
>  fflush(stdout);
>  zResult = local_getline(zPrior, stdin);
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Command-line utility

2013-07-11 Thread RSmith

http://www.sqlite.org/download.html

scroll to "Precompiled Binaries for Windows"
It runs just fine on 32 bit windows.

Adam


Hi, thanks, and yes I have these, but am specifically interested in the latest development trunk, which I don't think is included on 
this page in compiled form (unless I'm mistaken in which case please point me in the right direction).






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


Re: [sqlite] Command-line utility

2013-07-11 Thread Adam DeVita
http://www.sqlite.org/download.html

scroll to "Precompiled Binaries for Windows"
It runs just fine on 32 bit windows.

Adam

On Thu, Jul 11, 2013 at 12:20 PM, RSmith  wrote:
> Could someone send me a build with the current trunk of the command-line
> utility for Windows 32Bit with the standard option set for testing purposes
> please, or point me to where I can download it if a standard build already
> exists.
> Thanks!
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Command history not working in sqlite3 tool for MAC OS

2012-05-11 Thread Neo Anderson

Yes, I installed the latest version myself.

> Date: Fri, 11 May 2012 07:46:16 +0100
> From: amit.k.chaudh...@gmail.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Command history not working in sqlite3 tool for MAC OS
> 
> My Mac is running system provided sqlite3 (3.6.12) and up arrow returns
> last command as expected.  Did you install 3.7.11 your self, if so I wonder
> if you missed a component (e.g. one which provides the cmd line
> functionality).
> 
> On Fri, May 11, 2012 at 6:33 AM, Neo Anderson <neo_in_mat...@msn.com> wrote:
> 
> >
> > The UP/DOWN arrow keys do not work in sqlite3 for MAC OS X.
> > For example, I start sqlite3 and hit UP key, then I get:
> >
> > $ sqlite3 Documents/test.db
> > SQLite version 3.7.11 2012-03-20 11:35:50
> > Enter ".help" for instructions
> > Enter SQL statements terminated with a ";"
> > sqlite> ^[[A
> >
> > Is this a known problem?
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] command line to get number of tables in sqlite

2011-12-21 Thread Mohit Sindhwani

On 22/12/2011 7:25 AM, Matt Young wrote:

select count() from sqlite_master;

No??

...where type='table';




Cheers,
Mohit.
22/12/2011 | 12:51 PM.

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


Re: [sqlite] command line to get number of tables in sqlite

2011-12-21 Thread Matt Young
select count() from sqlite_master;

No??

On Wed, Dec 21, 2011 at 10:32 AM, smallboat  wrote:

> Hello,
>
> I have a sqlite file. I would like to open it and know how many tables in
> it.
>
> What is the command line to open a sqlite file and get to know how many
> tables in it? Thanks.
>
> Regards,
> Joe
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] command line to get number of tables in sqlite

2011-12-21 Thread Roger Andersson

Somthing like
sqlite3  sqlite.file
sqlite> select count(*) from sqlite_master where type = 'table';

/Roger
On 12/21/11 19:32, smallboat wrote:

Hello,

I have a sqlite file. I would like to open it and know how many tables in it.

What is the command line to open a sqlite file and get to know how many tables 
in it? Thanks.



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


Re: [sqlite] command line to get number of tables in sqlite

2011-12-21 Thread Tim Streater
On 21 Dec 2011 at 18:32, smallboat  wrote: 

> I have a sqlite file. I would like to open it and know how many tables in it.
>
> What is the command line to open a sqlite file and get to know how many tables
> in it? Thanks.

See:

http://www.sqlite.org/sqlite.html

and:

http://www.sqlite.org/faq.html#q7

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


Re: [sqlite] Command line option equivalent for .read

2011-12-10 Thread David Walker
Thanks Simon that does it

It does strike me though that there would be some value in making the in app 
'.' (dot) commands and the command line options consistent especially as there 
has been an increase in the number of both as new versions have come about. 
From a programming perspective I suspect it would also be more maintainable as 
well

So you get:
 .header [ON|OFF] and -[no]header
But you only get
.echo [ON|OFF] and -echo so there is no -noecho option

There is a .read but no -read

There is .mode csv and a -csv but  wouldn't it be better to have a -mode csv 
(and by implication a -mode tcl where there is not -tcl)

Don't get me wrong this is not a major whinge - I love sqlite and appreciate 
the effort of the developers put into maintaining it but as it grows having a 
consistent single command set for .commands and options would be a good thing

David M Walker
Data Management & Warehousing
0118 321 5930
dav...@datamgmt.com
http://www.datamgmt.com

> Date: Thu, 8 Dec 2011 17:26:55 +
> From: Simon Davies <simon.james.dav...@gmail.com>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Subject: Re: [sqlite] Command line option equivalent for .read
> Message-ID:
>   <CANG6AhQjNs08OM93iaE0xNONbTRPJsRRzhKkSsdy+N3b=fp...@mail.gmail.com>
> Content-Type: text/plain; charset=ISO-8859-1
> 
> On 8 December 2011 17:05, David Walker <dav...@datamgmt.com> wrote:
>> Hi,
>> 
>> Having played around with a shell script that calls SQLite I have noticed 
>> that I can something like
>> 
>> ? ? ? ?.separator STRING ? ? ?at the SQLite prompt
>> ? ? ? ?.mode line
>> 
>> or I can do
>> 
>> ? ? ? ?sqlite3 -separator STRING at the command line
>> ? ? ? ?sqlite3 - line
>> 
>> This is both useful and consistent
>> However if I want to execute a file I can only do
>> 
>> ? ? ? ?.read FILENAME ? ? at the SQLite prompt
>> 
>> or I can do either of
>> 
>> ? ? ? ?cat FILENAME | sqlite3
>> or
>> ? ? ? ?sqlite3 > 
>> Is there an equivalent '-read' for the command line and if not might there 
>> be in the future?
>> The '-init' option does not seem to perform the same function although it 
>> does read the file
> 
> Does
>sqlite3 theDb ".read FILENAME"
> do what you want?
> 
>> 
>> It is inconsistent and when writing wrapper shell/perl scripts on occasions 
>> makes scripts a little more complex
>> e.g. try doing it with a Perl IO::CaptureOutput(qxx) call where you don't 
>> have access to piped stdin :-(
>> 
>> rgds
>> davidw
>> 
> 
> Regards,
> Simon

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


Re: [sqlite] Command line option equivalent for .read

2011-12-08 Thread Simon Davies
On 8 December 2011 17:05, David Walker  wrote:
> Hi,
>
> Having played around with a shell script that calls SQLite I have noticed 
> that I can something like
>
>        .separator STRING      at the SQLite prompt
>        .mode line
>
> or I can do
>
>        sqlite3 -separator STRING at the command line
>        sqlite3 - line
>
> This is both useful and consistent
> However if I want to execute a file I can only do
>
>        .read FILENAME     at the SQLite prompt
>
> or I can do either of
>
>        cat FILENAME | sqlite3
> or
>        sqlite3 
> Is there an equivalent '-read' for the command line and if not might there be 
> in the future?
> The '-init' option does not seem to perform the same function although it 
> does read the file

Does
sqlite3 theDb ".read FILENAME"
do what you want?

>
> It is inconsistent and when writing wrapper shell/perl scripts on occasions 
> makes scripts a little more complex
> e.g. try doing it with a Perl IO::CaptureOutput(qxx) call where you don't 
> have access to piped stdin :-(
>
> rgds
> davidw
>

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


Re: [sqlite] Command line client and Tcl interface behaves differently?

2010-11-20 Thread Fredrik Karlsson
Hi,

Sorry, never mind this post. I haven't found the problem actually yet,
but a "bare minimum" example fed directly to the interpreter through
the command line works correctly:

% package require sqlite3
3.7.2
% set inf [open 1_schema.sql r]
file6
% set sql [read $inf]
[... the contents of the read file is dumped ..]
% close $inf
% sqlite3 db :memory:
% db eval $sql
% puts [db eval {SELECT sqlite_version(), sqlite_source_id()}]
3.7.2 {2010-08-23 18:52:01 42537b60566f288167f1b5864a5435986838e3a3}
% db eval {select * from SQLITE_MASTER} values {parray values}
[... data concerning all tables are dumped... ]

So, the error is somewhere else in the code. Sorry about that. :-/

/Fredrik

-- 
"Life is like a trumpet - if you don't put anything into it, you don't
get anything out of it."
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Command line client and Tcl interface behaves differently?

2010-11-20 Thread Fredrik Karlsson
On Sat, Nov 20, 2010 at 2:43 PM, Richard Hipp  wrote:
> On Sat, Nov 20, 2010 at 8:25 AM, Fredrik Karlsson wrote:
>
>> Dear list,
>>
>> I am having a silly problem, and need your expertise. I just want to
>> initiate a SQLite database using a schema file in Tcl, but I just get
>> an empty database whatever I do. I asked this question on the Tcl
>> list, and got a reply which I interpret to mean that this is not a
>> problem in my Tcl knowledge - but in my SQLite implementation.
>>
>> Anyway, loading from the same SQL file within the "sqlite3" command
>> line client is no problem
>>
>> Here is what I am doing:
>>
>> proc init_db {dbDir {dbFile db.sqlite3} {force 1} } {
>>
>>        set dbFile [file join [file normalize $dbDir] $dbFile ]
>>        if { $force == 1 && [file exists $dbFile]} {
>>                file delete $dbFile
>>        }
>>        sqlite3 db $dbFile
>>        set schemaFile [file normalize [file join  .. setup 1_schema.sql] ]
>>
>>        if {! [file exists $schemaFile] } {
>>                return -code error "Unable to open schema file $schemaFile"
>>        }
>>        set inf [open $schemaFile r]
>>        set sql [read $inf]
>>        close $inf
>>
>> Add here:   puts $sql
> Let's see what file you are really loading
>
>
>>        db eval $sql
>>
>
> And here:  puts [db eval {SELECT sqlite_version(), sqlite_source_id()}]
>
>
>>
>>        db close
>>        return [file normalize $dbFile ]
>>
>> }
>>
>> Does the Tcl interface and the "sqlite3" binary behave differently
>> when parsing instructions from a file?
>>
>> The SQL code in the 1_schema.sql" file is just table definitions and
>> comments using the /* */ syntax which loads ok in the command like
>> client, so what could be wrong?
>>
>> Thankful for all the help I could get on this.
>>
>> /Fredrik
>>
>>
>> --
>> "Life is like a trumpet - if you don't put anything into it, you don't
>> get anything out of it."
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

Thank you Rickard for the quick response. This is really code that is
part of a benchmarking framework (which used to work, oddly enough) so
the output is a bit verbose.

-
$ tclsh8.6 run_all_benchmarks.tcl
[Sat Nov 20 14:50:09 CET 2010] [bench] [debug] '::bench::run -errors 1
-iters 10 -match Emu*simple*one* /usr/local/bin/tclsh8.6
./emuquery.benchmark'
[Sat Nov 20 14:50:09 CET 2010] [bench] [info] 'Benchmark
/usr/local/bin/tclsh8.6'
[Sat Nov 20 14:50:09 CET 2010] [bench] [info] 'emuquery.benchmark'
[Sat Nov 20 14:50:09 CET 2010] [bench] [info] 'Sourcing ./emuquery.benchmark'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'Running '
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'Copied 1 files to
/private/var/folders/N8/N8aqQyE1FE8Bb0ONohLfsk+++TM/-Tmp-'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info]
'/private/var/folders/N8/N8aqQyE1FE8Bb0ONohLfsk+++TM/-Tmp-'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'CREATE TABLE utterances ('
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'id INTEGER PRIMARY KEY
AUTOINCREMENT,'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'name TEXT,'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'filelength REAL,'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'updated_at TEXT,'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'checksum_algorithm TEXT,'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'checksum TEXT,'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'UNIQUE(name) ON CONFLICT FAIL'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] ');'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'CREATE TABLE levels ('
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'id INTEGER PRIMARY KEY
AUTOINCREMENT,'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'name TEXT UNIQUE ON
CONFLICT FAIL'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] ',weight FLOAT UNIQUE'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] ');'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'CREATE TABLE level_level ('
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'parent_id INTEGER
REFERENCES levels(id),'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'child_id INTEGER
REFERENCES levels(id),'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'PRIMARY KEY(parent_id,child_id)'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] ');'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'CREATE TABLE tc_level_level ('
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'ancestor_id INTEGER
REFERENCES levels(id),'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'descendant_id INTEGER
REFERENCES levels(id),'

Re: [sqlite] Command line client and Tcl interface behaves differently?

2010-11-20 Thread Richard Hipp
On Sat, Nov 20, 2010 at 8:25 AM, Fredrik Karlsson wrote:

> Dear list,
>
> I am having a silly problem, and need your expertise. I just want to
> initiate a SQLite database using a schema file in Tcl, but I just get
> an empty database whatever I do. I asked this question on the Tcl
> list, and got a reply which I interpret to mean that this is not a
> problem in my Tcl knowledge - but in my SQLite implementation.
>
> Anyway, loading from the same SQL file within the "sqlite3" command
> line client is no problem
>
> Here is what I am doing:
>
> proc init_db {dbDir {dbFile db.sqlite3} {force 1} } {
>
>set dbFile [file join [file normalize $dbDir] $dbFile ]
>if { $force == 1 && [file exists $dbFile]} {
>file delete $dbFile
>}
>sqlite3 db $dbFile
>set schemaFile [file normalize [file join  .. setup 1_schema.sql] ]
>
>if {! [file exists $schemaFile] } {
>return -code error "Unable to open schema file $schemaFile"
>}
>set inf [open $schemaFile r]
>set sql [read $inf]
>close $inf
>
> Add here:   puts $sql
Let's see what file you are really loading


>db eval $sql
>

And here:  puts [db eval {SELECT sqlite_version(), sqlite_source_id()}]


>
>db close
>return [file normalize $dbFile ]
>
> }
>
> Does the Tcl interface and the "sqlite3" binary behave differently
> when parsing instructions from a file?
>
> The SQL code in the 1_schema.sql" file is just table definitions and
> comments using the /* */ syntax which loads ok in the command like
> client, so what could be wrong?
>
> Thankful for all the help I could get on this.
>
> /Fredrik
>
>
> --
> "Life is like a trumpet - if you don't put anything into it, you don't
> get anything out of it."
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Command line tool always return the first column in a select

2010-08-04 Thread Benoit Aubuchon
I upgraded to 3.7.0 and it fixes the problem.

Thanks!

Ben

On Wed, Aug 4, 2010 at 8:07 AM, Black, Michael (IS)
wrote:

> I just ran your code on 3.6.23.1 and it works just fine.
>
> Can you try a newer version?
>
> x.sql:
> create table mytable (id VARCHAR(255), name VARCHAR(255), address
> VARCHAR(255), PRIMARY KEY(id));
> insert into mytable (id, name, address) VALUES ('123abc','charlie', '123
> st');
> insert into mytable (id, name, address) VALUES ('yyzz', 'bob',
> '456nowhere');
>
> SQLite version 3.6.23.1
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> .read x.sql
> sqlite> select * from mytable;
> 123abc|charlie|123 st
> yyzz|bob|456nowhere
>
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of Benoit Aubuchon
> Sent: Tue 8/3/2010 4:55 PM
> To: sqlite-users@sqlite.org
> Subject: EXTERNAL:[sqlite] Command line tool always return the first column
> in a select
>
>
>
> Everytime I select something from a table I always get the first selected
> field only. Here's what I mean:
>
> # sqlite3 mytable.db
> SQLite version 3.6.22
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> *sqlite> .show*
> echo: off
>  explain: off
>  headers: off
> mode: list
> nullvalue: ""
>   output: stdout
> separator: "|"
>width:
> *sqlite> create table mytable (id VARCHAR(255), name VARCHAR(255), address
> VARCHAR(255), PRIMARY KEY(id));*
> *sqlite> insert into mytable (id, name, address) VALUES ('123abc',
> 'charlie', '123 st');*
> *sqlite> insert into mytable (id, name, address) VALUES ('yyzz', 'bob',
> '456
> nowhere');*
> *sqlite> select * from mytable;*
> 123abc
> yyzz
> *sqlite> .head ON*
> *sqlite> select * from mytable;*
> id
> 123abc
> yyzz
> *sqlite> select id, name, address from mytable;*
> id
> 123abc
> yyzz
> *sqlite> select name from mytable;*
> name
> charlie
> bob
> *sqlite> select name, address from mytable;*
> name
> charlie
> bob
> *sqlite> select address, id from mytable;*
> address
> 123 st
> 456 nowhere
> *sqlite> .dump*
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE mytable (id VARCHAR(255), name VARCHAR(255), address
> VARCHAR(255), PRIMARY KEY(id));
> INSERT INTO "mytable" VALUES('123abc','charlie','123 st');
> INSERT INTO "mytable" VALUES('yyzz','bob','456 nowhere');
> COMMIT;
> *sqlite> select id, name, address from mytable;*
> id
> 123abc
> yyzz
> *sqlite> .mode csv*
> *sqlite> select id, name, address from mytable;*
> id
> 123abc
> yyzz
> *sqlite> .mode line*
> *sqlite> select id, name, address from mytable;*
>   id = 123abc
>
>   id = yyzz
> *sqlite> select * from mytable;*
>   id = 123abc
>
>   id = yyzz
> *sqlite> .dump*
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE mytable (id VARCHAR(255), name VARCHAR(255), address
> VARCHAR(255), PRIMARY KEY(id));
> INSERT INTO "mytable" VALUES('123abc','charlie','123 st');
> INSERT INTO "mytable" VALUES('yyzz','bob','456 nowhere');
> COMMIT;
>
> # uname -a
> Linux appserver1 2.6.32-gentoo-r1 #1 SMP Wed Jan 13 05:48:57 EST 2010
> x86_64
> Intel(R) Xeon(R) CPU X5550 @ 2.67GHz GenuineIntel GNU/Linux
>
> If I use sqlite from PHP it works as expected. Its only from the command
> line that it doesn't show the other fields.
>
> Has anyone encountered this problem before?
>
> Thanks
>
> Ben
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Command line tool always return the first column in a select

2010-08-04 Thread Black, Michael (IS)
I just ran your code on 3.6.23.1 and it works just fine.
 
Can you try a newer version?
 
x.sql:
create table mytable (id VARCHAR(255), name VARCHAR(255), address VARCHAR(255), 
PRIMARY KEY(id));
insert into mytable (id, name, address) VALUES ('123abc','charlie', '123 st');
insert into mytable (id, name, address) VALUES ('yyzz', 'bob', '456nowhere');

SQLite version 3.6.23.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .read x.sql
sqlite> select * from mytable;
123abc|charlie|123 st
yyzz|bob|456nowhere
 
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Benoit Aubuchon
Sent: Tue 8/3/2010 4:55 PM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:[sqlite] Command line tool always return the first column in 
a select



Everytime I select something from a table I always get the first selected
field only. Here's what I mean:

# sqlite3 mytable.db
SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
*sqlite> .show*
 echo: off
  explain: off
  headers: off
 mode: list
nullvalue: ""
   output: stdout
separator: "|"
width:
*sqlite> create table mytable (id VARCHAR(255), name VARCHAR(255), address
VARCHAR(255), PRIMARY KEY(id));*
*sqlite> insert into mytable (id, name, address) VALUES ('123abc',
'charlie', '123 st');*
*sqlite> insert into mytable (id, name, address) VALUES ('yyzz', 'bob', '456
nowhere');*
*sqlite> select * from mytable;*
123abc
yyzz
*sqlite> .head ON*
*sqlite> select * from mytable;*
id
123abc
yyzz
*sqlite> select id, name, address from mytable;*
id
123abc
yyzz
*sqlite> select name from mytable;*
name
charlie
bob
*sqlite> select name, address from mytable;*
name
charlie
bob
*sqlite> select address, id from mytable;*
address
123 st
456 nowhere
*sqlite> .dump*
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE mytable (id VARCHAR(255), name VARCHAR(255), address
VARCHAR(255), PRIMARY KEY(id));
INSERT INTO "mytable" VALUES('123abc','charlie','123 st');
INSERT INTO "mytable" VALUES('yyzz','bob','456 nowhere');
COMMIT;
*sqlite> select id, name, address from mytable;*
id
123abc
yyzz
*sqlite> .mode csv*
*sqlite> select id, name, address from mytable;*
id
123abc
yyzz
*sqlite> .mode line*
*sqlite> select id, name, address from mytable;*
   id = 123abc

   id = yyzz
*sqlite> select * from mytable;*
   id = 123abc

   id = yyzz
*sqlite> .dump*
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE mytable (id VARCHAR(255), name VARCHAR(255), address
VARCHAR(255), PRIMARY KEY(id));
INSERT INTO "mytable" VALUES('123abc','charlie','123 st');
INSERT INTO "mytable" VALUES('yyzz','bob','456 nowhere');
COMMIT;

# uname -a
Linux appserver1 2.6.32-gentoo-r1 #1 SMP Wed Jan 13 05:48:57 EST 2010 x86_64
Intel(R) Xeon(R) CPU X5550 @ 2.67GHz GenuineIntel GNU/Linux

If I use sqlite from PHP it works as expected. Its only from the command
line that it doesn't show the other fields.

Has anyone encountered this problem before?

Thanks

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


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


Re: [sqlite] command line does not accept arrow keys

2009-11-21 Thread Qianqian Fang
hi Jay

Jay A. Kreibich wrote:
>   Yes.  Readline support is not on by default.
>
>   You need to compile it with -DHAVE_READLINE and add -lreadline
>   to the linker:
>
> $ cc -DHAVE_READLINE -o sqlite3 sqlite3.c shell.c -lreadline

recompiling sqlite3 from source code with your command
solved the problem. thank you so much

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


Re: [sqlite] command line does not accept arrow keys

2009-11-20 Thread Jay A. Kreibich
On Fri, Nov 20, 2009 at 10:29:20PM -0500, Qianqian Fang scratched on the wall:

> Is this possibly caused by how sqlite binary was compiled?

  Yes.  Readline support is not on by default.

  You need to compile it with -DHAVE_READLINE and add -lreadline
  to the linker:

$ cc -DHAVE_READLINE -o sqlite3 sqlite3.c shell.c -lreadline

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] command line does not accept arrow keys

2009-11-20 Thread Qianqian Fang
I don't know, is there a way I can tell?

the binary was downloaded from sqlite website, version
is 3.6.2. The binary stores in a remote server, running
Debian 3, I used ssh in a Terminator/gnome-terminal
bash shell.

I also installed sqlite on my local machine, running
Ubuntu Karmic, interestingly, the one from Ubuntu repo,
v2.8.17, does not have this problem, but the one
I downloaded from sqlite's website, v3.6.18, has the
same issue.

Is this possibly caused by how sqlite binary was
compiled?

Stephan Wehner wrote:
> On Fri, Nov 20, 2009 at 6:02 PM, Qianqian Fang
>  wrote:
>   
>> hi
>>
>> I can not type arrow keys in the sqlite3 command line, all the arrow
>> keys (as well as other keys in the small keyboard) will be shown as
>> escape sequence "^]]A".
>> 
>
> Could it be a problem with readline? See
>
> http://www.sqlite.org/cvstrac/wiki?p=ReadLine
>
> Stephan
>
>   
>> I am using Terminator/gnome-terminal, is there a setting I need to
>> twig in order to get this working?
>>
>> thanks
>>
>> Qianqian
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>> 
>
>
>
>   

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


Re: [sqlite] command line does not accept arrow keys

2009-11-20 Thread Stephan Wehner
On Fri, Nov 20, 2009 at 6:02 PM, Qianqian Fang
 wrote:
> hi
>
> I can not type arrow keys in the sqlite3 command line, all the arrow
> keys (as well as other keys in the small keyboard) will be shown as
> escape sequence "^]]A".

Could it be a problem with readline? See

http://www.sqlite.org/cvstrac/wiki?p=ReadLine

Stephan

> I am using Terminator/gnome-terminal, is there a setting I need to
> twig in order to get this working?
>
> thanks
>
> Qianqian
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Stephan Wehner

-> http://stephan.sugarmotor.org (blog and homepage)
-> http://loggingit.com
-> http://www.thrackle.org
-> http://www.buckmaster.ca
-> http://www.trafficlife.com
-> http://stephansmap.org -- http://blog.stephansmap.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Command to create a database in sqlite

2008-03-24 Thread John Stanton
sqlite3_open.

Mozaharul Haque wrote:
> Hi,
> 
> A silly asking. I found the command to create a table but what about to
> create a database?
> 
> The table would be stored in the sqlite_master table.
> 
> And how do I refer (full path) to the database  using application like
> Basic4ppc 6.05.
> 
> 
> Please help.
> 
> 
> regards,
> 
> Mozaharul Haque
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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


Re: [sqlite] Command to create a database in sqlite

2008-03-24 Thread Igor Tandetnik
"Mozaharul Haque" <[EMAIL PROTECTED]>
wrote in message news:[EMAIL PROTECTED]
> A silly asking. I found the command to create a table but what about
> to create a database?

Just give a file name to sqlite3_open, then create a table. If the file 
didn't exist, it will be created at this point.

Igor Tandetnik 



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


Re: [sqlite] command

2007-08-30 Thread nishit sharma
using UODATE command its working.
thanks for the help

regards
Nishit


On 8/30/07, Jim Dodgen <[EMAIL PROTECTED]> wrote:
>
> Also I recommend you get a very basic SQL database book.
>
>
> Quoting John Stanton <[EMAIL PROTECTED]>:
>
> > nishit sharma wrote:
> > > i have given some text in a database file as
> > > America|england|   and many more.
> > > now i want to replace some text with new text how
> > > this is possible. Also my database has around 15 rows now
> > > which will be incremented.
> > >
> > > so, plz anyone tell me how to replace a text in a row and
> > > how to delete a particular row.
> > >
> > > waiting for response
> > >
> > > regads
> > > Nishit
> > >
> > How about UPDATE?
> >
> >
> -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> >
> -
> >
> >
>
>
>
>
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>


Re: [sqlite] command

2007-08-30 Thread Jim Dodgen
Also I recommend you get a very basic SQL database book.


Quoting John Stanton <[EMAIL PROTECTED]>:

> nishit sharma wrote:
> > i have given some text in a database file as
> > America|england|   and many more.
> > now i want to replace some text with new text how
> > this is possible. Also my database has around 15 rows now
> > which will be incremented.
> > 
> > so, plz anyone tell me how to replace a text in a row and
> > how to delete a particular row.
> > 
> > waiting for response
> > 
> > regads
> > Nishit
> > 
> How about UPDATE?
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 






-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] command

2007-08-30 Thread John Stanton

nishit sharma wrote:

i have given some text in a database file as
America|england|   and many more.
now i want to replace some text with new text how
this is possible. Also my database has around 15 rows now
which will be incremented.

so, plz anyone tell me how to replace a text in a row and
how to delete a particular row.

waiting for response

regads
Nishit


How about UPDATE?

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] command

2007-08-30 Thread kirrthana M
Update can be used to replace a text with some new text.
To delete a particular row use 'where' command along with 
'delete'.

-Original Message-
From: nishit sharma [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 30, 2007 4:04 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] command


i have given some text in a database file as
America|england|   and many more.
now i want to replace some text with new text how
this is possible. Also my database has around 15 rows now
which will be incremented.

so, plz anyone tell me how to replace a text in a row and
how to delete a particular row.

waiting for response

regads
Nishit


This message (including any attachment) is confidential and may be legally 
privileged.  Access to this message by anyone other than the intended 
recipient(s) listed above is unauthorized.  If you are not the intended 
recipient you are hereby notified that any disclosure, copying, or distribution 
of the message, or any action taken or omission of action by you in reliance 
upon it, is prohibited and may be unlawful.  Please immediately notify the 
sender by reply e-mail and permanently delete all copies of the message if you 
have received this message in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] command-line shell handling of errors.

2006-10-26 Thread Alejo Sanchez

"Yes and yes."
:)

Alejo

On 10/26/06, Kees Nuyt <[EMAIL PROTECTED]> wrote:


Hi Richard,

On Thu, 26 Oct 2006 17:23:17 +, you wrote:

> So the question:
> Who will be adversely effected by the new error behavior
> in the sqlite command-line shell?

Not really. I prefer the new behaviour. At the moment
I have to jump through hoops and scan my make logs to
detect errors and signal them.

> Who is using the sqlite command-line shell in scripts in
> such a way that the script will no longer work with the
> new behaviors?

It might break some of my scripts, but rightly so.
I'll be glad to repair them.

> Do I need to change the behavior back to the way it was
> and provide a command-line option to provoke the new
> (more rational) behavior?

Not really needed, but it would be nice to have a choice
to explicitly suppress errors or explicitly provoke the
new behaviour, either way. As my makefiles use a macro
$(SQLITE) instead of the program name itself it is easy
to add any switch anyway. Your approach in the remarks
of said ticket is right, in my view.

Thank you for any abort-on-error solution and the
beautiful, consistent product sqlite is!


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] command-line shell handling of errors.

2006-10-26 Thread Kees Nuyt

Hi Richard,

On Thu, 26 Oct 2006 17:23:17 +, you wrote:

> So the question:
> Who will be adversely effected by the new error behavior
> in the sqlite command-line shell?

Not really. I prefer the new behaviour. At the moment
I have to jump through hoops and scan my make logs to
detect errors and signal them.

> Who is using the sqlite command-line shell in scripts in
> such a way that the script will no longer work with the
> new behaviors?

It might break some of my scripts, but rightly so.
I'll be glad to repair them.
 
> Do I need to change the behavior back to the way it was
> and provide a command-line option to provoke the new
> (more rational) behavior?

Not really needed, but it would be nice to have a choice
to explicitly suppress errors or explicitly provoke the
new behaviour, either way. As my makefiles use a macro
$(SQLITE) instead of the program name itself it is easy
to add any switch anyway. Your approach in the remarks
of said ticket is right, in my view.

Thank you for any abort-on-error solution and the
beautiful, consistent product sqlite is!
-- 
  (  Kees Nuyt
  )
c[_]

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] command-line shell handling of errors.

2006-10-26 Thread Derrell . Lipman
[EMAIL PROTECTED] writes:

> So the question:  Who will be adversely effected by the new
> error behavior in the sqlite command-line shell?  Who is
> using the sqlite command-line shell in scripts in such a
> way that the script will no longer work with the new
> behaviors?  Do I need to change the behavior back to the
> way it was and provide a command-line option to provoke the
> new (more rational) behavior?

My use of the command line shell in scripts expects, in some cases, errors to
be ignored and for the remainder of the queries to be processed regardless of
the earlier errors.

I agree that your proposed change would have been preferred as the original
implementation.  I wouldn't mind if the new behavior became the default as
long as there was a new command-line option to enable backward-compatibility
mode of ignoring errors and continuing to process.

Cheers,

Derrell

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] command-line shell handling of errors.

2006-10-26 Thread Alex Roston
I don't use the command-line shell, but I'd definitely prefer not to see 
a fundamental change in the behavior of any tool I use. The debugging 
could get nasty and it's possible that someone using the tool in an 
unsupervised script might not notice the problem until after it had done 
some damage, or until the new, undesirable behavior had been taking 
place for days/weeks.


If you make the more rational behavior a command-line option, I suspect 
everyone will be happier.


Thanks,

Alex



[EMAIL PROTECTED] wrote:

In previous versions of SQLite, when the command-line shell 
encountered an error, it would print an error message but
continue processing its input.  


This seems wrong.  In response to ticket #2045, I changed
the command-line shell so that when it is reading from a
file, it stops reading whenever it encounters an error.  The
new behavior seems more rational.  But it is not backwards
compatible.

So the question:  Who will be adversely effected by the new
error behavior in the sqlite command-line shell?  Who is
using the sqlite command-line shell in scripts in such a
way that the script will no longer work with the new
behaviors?  Do I need to change the behavior back to the
way it was and provide a command-line option to provoke the
new (more rational) behavior?

For additional information:

  http://www.sqlite.org/cvstrac/tktview?tn=2045

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-


 




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Command line interface SQLITE 2.8.16

2005-09-14 Thread Nemanja Corlija
On 9/14/05, Sijm, Norbert <[EMAIL PROTECTED]> wrote:
> 
> 
> 
> Hi, I'm trying to execute a command in a DOS batchfile , but I get a syntax
> error. Is this supported in 2.8.16 ?
> ( sqlite mydbase.db select * from table1; )

Try enclosing your SQL statements in double quotes, like this:

sqlite mydbase.db "select * from table1;"


-- 
Nemanja Corlija <[EMAIL PROTECTED]>


Re: [sqlite] command history

2004-05-03 Thread ben . carlyle
Puneet,





Puneet Kishor <[EMAIL PROTECTED]>
03/05/2004 05:01 AM

 
To: SQLite <[EMAIL PROTECTED]>
cc: 
Subject:[sqlite] command history


> Is there a way to enable some kind of command history in the SQLite 
> shell? You know, press the up arrow to get the previous command... I 
> think it is called READLINE support, no?

If you're still having trouble, perhaps you could try starting sqlite in a 
different way. Instead of this:

$ sqlite my.db
> SELECT ...;
> CREATE ...;

try:

$ sqlite my.db 'SELECT ...;'
$ sqlite my.db 'CREATE ...;'

This method doesn't allow you to run transactions across multiple lines, 
but does give the advantage of immediately conforming to the way your 
shell does its command history :) I like to do things this way with ksh 
and vi editing keys, myself.

Benjamin.


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



Re: [sqlite] command history

2004-05-03 Thread Puneet Kishor
On May 3, 2004, at 3:48 AM, eno wrote:

Puneet Kishor wrote:

Actually I am on Mac OS X 10.3. I have no idea what I need to do to 
enable READLINE. I just downloaded the source and did the 
./configure, make, make install dance and got no READLINE. It must be 
somewhere on my system because the behavior I want is very much a 
part of tcsh.
Assuming you have readline-devel installed on your system (watch out 
for a file "readline.h" somewhere under (for example) /usr/include or 
/usr/local/include) you might try to edit the Makefile generated from 
./configure changing some lines:

# Compiler options needed for programs that use the readline() library.
#
READLINE_FLAGS = -DHAVE_READLINE=1 -I/usr/include/readline
# The library that programs using readline() must link against.
#
LIBREADLINE = -lreadline
If you don't have readline devel on your system you might have to 
install that beforehand, of course.

Thanks eno, that was just what I needed. I have updated the SQLite wiki 
on this subject appropriately.

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


Re: [sqlite] Command-line SQLite

2003-11-28 Thread Paul Smith
At 15:20 28/11/2003, [EMAIL PROTECTED] wrote:
I'm attempting to use the command-line SQLite to test the speed of certain 
"selects" and how writing them in different fashions affects speed

OK, can anyone explain (no pun intended!) what I should be looking for in 
what information "explain" returns? Are there any timing numbers in there 
I can look at?
I always look for the use of indices, and loops and things

create temp table xx (name varchar(30), title varchar(30));
create temp index xx_y on xx(name);
sqlite> explain select * from xx where name="fred";
addr  opcodep1  p2  p3
    --  --  
0 ColumnName0   0   name
1 ColumnName1   0   title
2 Integer   1   0
3 OpenRead  0   3   xx
4 Integer   1   0
5 OpenRead  1   4   xx_y
6 String0   0   fred
7 MakeKey   1   0   t
8 MemStore  0   0
9 MoveTo1   18
10MemLoad   0   0
11IdxGT 1   18
12IdxRecno  1   0
13MoveTo0   0
14Column0   0
15Column0   1
16Callback  2   0
17Next  1   10
18Close 0   0
19Close 1   0
20Halt  0   0
This shows (step 5) that the index is being opened, steps 6-9 (I think) 
that a lookup on the index for 'fred' is being perfomed,
then step 11 checks if the current index value is bigger than 'fred', and 
jumps to step 18 if so
12-13 moves to the next index record
steps 14-15 get the data
step 17 moves to the next record in the index at step 10

So, this will iterate through the index 'xx_y' from 'fred' until the value 
of the index > 'fred'. (ie not much looping)

(I think)

Then

sqlite> explain select * from xx where title="fred";
addr  opcodep1  p2  p3
    --  --  ---
0 ColumnName0   0   name
1 ColumnName1   0   title
2 Integer   1   0
3 OpenRead  0   3   xx
4 Rewind0   12
5 Column0   1
6 String0   0   fred
7 StrNe 1   11
8 Column0   0
9 Column0   1
10Callback  2   0
11Next  0   5
12Close 0   0
13Halt  0   0
This doesn't open the index.
5-7 compares column 1 ('title') with the text 'fred', if it isn't that it 
jumps to step 11
8-9 gives the data
11 goes to the next record at step 5

So, this will iterate through the entire database looking for 'fred' 
(potentially lots of looping)

(Note I'm not entirely sure what everything means, but this is what I've 
surmised over time)

In general, in a loop, index operations are good, things like 'strne', 'ne' 
etc aren't as good because they probably operate more often.

You can't have timing information, because, the 'explain' doesn't look at 
the actual data available, so, if you just look at timing, my unindexed 
query above would probably look to be quicker, but in a large data set, the 
indexed query would actually probably be a lot quicker, because it'd have 
to go around the loop less times, even though the index operations 
themselves might well be slower than the plain comparisons.



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]