Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

[Konrad Hambrick]

All --

My $0.02 is to not change anything except maybe, perhaps printing a warning 
message.

If they want to create a persistent DB, .quit ; and start over with a dbname on 
the command line ...

I am afraid any such change might break existing bash and bat scripts I've got 
'out there in the wild'.

Thanks for letting me pipe-in ...

-- kjh


> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf
> Of Richard Hipp
> Sent: Monday, February 10, 2014 12:14 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line 
> shell
> 
> On Mon, Feb 10, 2014 at 1:11 PM, Mike King  wrote:
> 
> > Why not show the warning on exit only if an in memory database is in use.
> >
> >
> 
> Because on windows, the likely "exit" will be when the user clicks the big
> red X in the upper right-hand corner, closing the terminal window down, so
> there is no opportunity to display a warning nor ask for confirmation.
> 
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Hints for the query planner

[Konrad Hambrick]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf
> Of Richard Hipp
> Sent: Tuesday, September 10, 2013 2:27 PM
> To: General Discussion of SQLite Database
> Subject: [sqlite] Hints for the query planner
> 
> There is a survey question at the bottom of this message.  But first some
> context...
> 

> 
> SURVEY QUESTION:
> 
> The question for today is what to call this magic hint function:
> 
> (1)  unlikely(EXPR)
> (2)  selective(EXPR)
> (3)  seldom(EXPR)
> (4)  seldom_true(EXPR)
> (5)  usually_not_true(EXPR)
> 
> Please feel free to suggest other names if you think of any.
> 
> ADDITIONAL INFORMATION:
> 
> The current implementation allows a second argument which must be a
> floating point constant between 0.0 and 1.0, inclusive. The second argument
> is an estimate of the probability that the expression in the first argument
> will be true.  The default is 0.05.  Names like "unlikely" or "seldom" work
> well when this probability is small, but if the second argument is close to
> 1.0, then those names seem backwards.  I don't know if this matters.  The
> optional second argument is not guaranteed to make it into an actually
> release.

All --

Since the optional second arg is not guaranteed to make it into a release, 
I like (3) - SELDOM( EXPR ) ...

--- cut  here --
SELECT DISTINCT aname
  FROM album, composer, track
 WHERE SELDOM( cname LIKE '%bach%' )
   AND composer.cid=track.cid
   AND album.aid=track.aid
;
--- cut there --

-- kjh

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

Re: [sqlite] BETWEEN and explicit collation assignment

[Konrad Hambrick]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf
> Of James K. Lowden
> Sent: Tuesday, August 27, 2013 8:11 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] BETWEEN and explicit collation assignment
> 
> On Mon, 26 Aug 2013 19:03:39 +
> Roman Fleysher  wrote:
> 
> > However, sometimes, in comparison we want to ignore some of the
> > attributes, or compare derived ones. Many busses can carry 25 people,
> > and may be considered equal if we simply need to transport people.
> > Busses certainly differ by other attributes.
> 
> Busses might indeed differ in many ways, but if you make NAME the
> primary key for BUSSES, the rule is not "compare BUSSES, ignoring
> columns other than NAME".  The rule is "compare BUSSES.NAME".
> 
> > it is the comparison ( "=", BETWEEN, IN , etc) statements that must
> > be modified
> 
> This not a syntax issue.  Equality is deeply embedded in the system, in
> many places where there's no SQL in play (e.g. keys).
> 
> It's a system of types and operators.  We can already convert between
> types and compare them.  If you can show some kind of comparison
> that *cannot* be done via type conversion using the operators exactly
> as they are, you might have a point.
> 

Roman --

Another issue is the fundamental but often confused difference between 
Object Oriented Programming and Relational DataBases ( AKA the Object
Relational Impedance mismatch ) 

One should not model a complex object like a Bus in a Relational DB as 
an amorphous Blob, it should instead be modeled as as a collection of 
orthogonal attributes in a set of atomic columns, each having a specific 
type in one-or-more tables.

Equality, or more generally, comparison of the column primitives is 
absolutely as James said, 'deeply embedded in the system itself'.

Be the CPU ...

To find the Bus that fits your needs, you would need to compare sets 
of primitive Attributes of interest to specific needs, invoking AND-OR 
clauses to combine those specific primitive comparisons.

For example:  to find the Name of all the short busses:

   -- one model of a bus might be:

   CREATE TABLE Bus
   (
  IdIntINTEGER PRIMARY KEY AUTOINCREMENT
, Name  varchar( 32 )  COLLATE NOCASE
, Color varchar( 16 )  COLLATE NOCASE
, Lengthnumeric( 5,3 )
, Capacity  int  
   ) 
   ;

   -- find the Name of each short bus:

   SELECT Name 
 FROM Bus
WHEREColor = 'yellow'
  AND (( Length < 20 ) 
  OR   ( Capacity >= 8 AND Capacity < 16 ))  
   ;
 
-- kjh


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

Re: [sqlite] Literature on the information theory behind SQL(lite)?

[Konrad Hambrick]

> -Original Message-
> On Tue, Jul 9, 2013 at 9:27 PM, Jay A. Kreibich  wrote:
> >
> >   If you want to learn more about the theory and concepts behind SQL, I
> >   would strongly recommend these two books:
> >
> > SQL and Relational Theory (2nd Ed) by C.J. Date
> > http://shop.oreilly.com/product/0636920022879.do
> >
> > Relational Theory for Computer Professionals by C.J. Date
> > http://shop.oreilly.com/product/0636920029649.do
> >
> O'Reilly is running a half-price sale on the latter book, today only.  ($16
> instead of the usual $32.)  Use the discount code "DEAL" to claim the
> reduced price.

Dr Hipp --

Nice tip !

Just now purchased one.

Thank you !

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

Re: [sqlite] sqlite3: .import command handles quotation incorrectly

[Konrad Hambrick]

P.S.  This might be helpful too.

I downloaded yesterday's amalgamation, backed up shell.c and then overwrote it 
with your most recent version.

$ ./sqlite3 foo.db
SQLite version 3.8.0 2013-06-26 13:22:28
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table foo( col1 int, col2 varchar(255) ) ;
sqlite> .import "|gawk 'BEGIN{ for ( i = 0 ; i < 10 ; i ++ ){ print i \"| this 
is row \" i } ; exit 0 ; }'" foo
sqlite> select * from foo
   ...> ;
0| this is row 0
1| this is row 1
2| this is row 2
3| this is row 3
4| this is row 4
5| this is row 5
6| this is row 6
7| this is row 7
8| this is row 8
9| this is row 9


> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf
> Of Konrad Hambrick
> Sent: Thursday, June 27, 2013 4:01 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] sqlite3: .import command handles quotation incorrectly
> 
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org 
> > [mailto:sqlite-users-boun...@sqlite.org] On Behalf
> > Of Richard Hipp
> > Sent: Thursday, June 27, 2013 9:16 AM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] sqlite3: .import command handles quotation incorrectly
> >
> > On Thu, Jun 27, 2013 at 8:58 AM, Richard Hipp <d...@sqlite.org> wrote:
> >
> > >
> > > What if, instead of a new command, we simply extend the ".import" command
> > > so that if the first character of the filename is "|" it interprets the
> > > filename as a pipe instead of a file.  The ".output" command works that 
> > > way.
> > >
> >
> > This alternative concept has now been implemented on trunk.  Example:
> >
> 
> Richard --
> 
> I had a chance to download shell.c and compile from trunk.
> 
> A sample session is below.
> 
> Thanks you very much !
> 
> -- kjh
> 
> sqlite> create table foo( col1 int, col2 varchar(255) ) ;
> sqlite> .import "|gawk 'BEGIN{ for ( i = 0 ; i < 10 ; i ++ ){ print i \"| 
> this is row \" i
> } ; exit 0 ; }'" foo
> sqlite> select * from foo ;
> 0| this is row 0
> 1| this is row 1
> 2| this is row 2
> 3| this is row 3
> 4| this is row 4
> 5| this is row 5
> 6| this is row 6
> 7| this is row 7
> 8| this is row 8
> 9| this is row 9
> ___
> 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] sqlite3: .import command handles quotation incorrectly

[Konrad Hambrick]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf
> Of Richard Hipp
> Sent: Thursday, June 27, 2013 9:16 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] sqlite3: .import command handles quotation incorrectly
> 
> On Thu, Jun 27, 2013 at 8:58 AM, Richard Hipp  wrote:
> 
> >
> > What if, instead of a new command, we simply extend the ".import" command
> > so that if the first character of the filename is "|" it interprets the
> > filename as a pipe instead of a file.  The ".output" command works that way.
> >
> 
> This alternative concept has now been implemented on trunk.  Example:
> 

Richard --

I had a chance to download shell.c and compile from trunk.

A sample session is below.

Thanks you very much !

-- kjh

sqlite> create table foo( col1 int, col2 varchar(255) ) ;
sqlite> .import "|gawk 'BEGIN{ for ( i = 0 ; i < 10 ; i ++ ){ print i \"| this 
is row \" i } ; exit 0 ; }'" foo
sqlite> select * from foo ;
0| this is row 0
1| this is row 1
2| this is row 2
3| this is row 3
4| this is row 4
5| this is row 5
6| this is row 6
7| this is row 7
8| this is row 8
9| this is row 9
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3: .import command handles quotation incorrectly

[Konrad Hambrick]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf
> Of Richard Hipp
> Sent: Thursday, June 27, 2013 9:16 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] sqlite3: .import command handles quotation incorrectly
> 
> On Thu, Jun 27, 2013 at 8:58 AM, Richard Hipp  wrote:
> 
> >
> > What if, instead of a new command, we simply extend the ".import" command
> > so that if the first character of the filename is "|" it interprets the
> > filename as a pipe instead of a file.  The ".output" command works that way.
> >
> 
> This alternative concept has now been implemented on trunk.



Dang, you're quick :)

This opens a lot of possibilities for me and I don't have to patch shell.c any 
more 

One of the handiest uses is importing directly from .xls via Perl and 
Spreadsheet::ParseExcel
and the xls2csv( ) function ...

Thank you, Richard !!

-- kjh


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

Re: [sqlite] sqlite3: .import command handles quotation incorrectly

[Konrad Hambrick]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf
> Of Richard Hipp
> Sent: Thursday, June 27, 2013 8:30 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] sqlite3: .import command handles quotation incorrectly
> 
> On Thu, Jun 27, 2013 at 9:23 AM, Konrad Hambrick <kon...@payplus.com> wrote:
> 
> > How do I learb more about the Contributor License Agreement ?
> >
> > I don't see it on the WebSite ...
> >
> 
> Background information:  http://www.sqlite.org/copyright.html
> CLA: http://www.sqlite.org/copyright-release.pdf
> 
>
Thanks !  

I'll pass it on to my Boss for a signature.

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

Re: [sqlite] sqlite3: .import command handles quotation incorrectly

[Konrad Hambrick]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf
> Of Richard Hipp
> Sent: Thursday, June 27, 2013 7:59 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] sqlite3: .import command handles quotation incorrectly
> 
> On Thu, Jun 27, 2013 at 8:53 AM, Konrad Hambrick <kon...@payplus.com> wrote:
> 
> >
> > Hmmm ... I don't believe my patch file went thru ...
> >
> > Is there an acceptable method to include text files ?
> >
> 
> We cannot accept your patch anyhow, unless you have a Contributor License
> Agreement on file.  To do otherwise would jeopardize the public domain
> status of SQLite.
> 
Thanks for the feedback, Richard.  

How do I learb more about the Contributor License Agreement ?

I don't see it on the WebSite ...

> But I am looking into your idea...
> 
> What if, instead of a new command, we simply extend the ".import" command
> so that if the first character of the filename is "|" it interprets the
> filename as a pipe instead of a file.  The ".output" command works that way.
> 
> One downside is that the entire command has to be in a single argument,
> which complicates the use of ' and " characters in the command itself.

I looked at the |command option for .import before I settled on a new .pimport
command but I didn't want to have to mess with the command line arg logic in
the do_meta_command( ) function.

That's why I opted for the .pimport command instead ... It greatly simplified 
command line handling -- the Table is in azArg[1] and then I popen the literal 
line that follows azArg[1] :)

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

Re: [sqlite] sqlite3: .import command handles quotation incorrectly

[Konrad Hambrick]

Hmmm ... I don't believe my patch file went thru ...

Is there an acceptable method to include text files ?

Thanks.

-- kjh

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

Re: [sqlite] sqlite3: .import command handles quotation incorrectly

[Konrad Hambrick]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] 
> On Behalf Of RSmith
> Sent: Wednesday, June 26, 2013 10:21 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] sqlite3: .import command handles quotation incorrectly
> 
All --

IMO, CSV Files generally suck.

I often had to resort to an external CSV-File preprocessor to clean up 
various Broken CSV File Formats.

But it took two-steps and a temp file:

   1. mypreprocessor Broken.CSV > Cleaned.TXT 

   2. echo "
  .import Cleaned.TXT TargetTable ;
  .exit ;" |sqlite3 -separator '|' MyDataBase.db 

One thing that I've found to be handy without breaking sqlite3 or adding 
too much code to shell.c was a new sqlite3 dot-command: .pimport ...

the .pimport command imports data from a pipe instead of a text file.

Syntax example:

   .pimport TargetTable command arg [arg [arg] ...] ;

Here's a sample session.

$ ./sqlite3 foo.db
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table foo( col1 int, col2 varchar(255) ) ;
sqlite> .pimport foo gawk 'BEGIN { for( i = 1 ; i <= 10 ; i ++ ){ print i "|" 
"this is string " i } ; exit 0 }' ;
sqlite> select * from foo ;
1|this is string 1
2|this is string 2
3|this is string 3
4|this is string 4
5|this is string 5
6|this is string 6
7|this is string 7
8|this is string 8
9|this is string 9
10|this is string 10
sqlite> .exit

Attached is a patch for shell.c  please use it if it's helpful.

While there's code for WIN32, I've never needed it nor tested it ...

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

Re: [sqlite] query optimization with "order by" in a view

[Konrad Hambrick]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf
> Of James K. Lowden
> Sent: Tuesday, February 19, 2013 12:07 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] query optimization with "order by" in a view
> 
> On Tue, 19 Feb 2013 10:19:26 +0100
> "Gabriel Corneanu"  wrote:
> 
> > I included the "order by" in view because it's meant for some
> > end-users and I wanted to avoid mistakes.
> ...
> > Am I doing a mistake??
> 
> Well, yes, by including ORDER BY in the view definition.  Most DBMSs
> don't allow that, and the SQL standard doesn't allow it.  So don't do
> it!  :-)
> 
> ORDER BY is best understood as *not* part of the SELECT statement.
> Rather, it's a post-processor.  Consider that there can be many SELECTs
> in a query, but only one ORDER BY.
> 
> According to the SQL standard, SELECT produces a "table expression" that
> can be used wherever a table can be used.  ORDER BY *reads* a table
> expression; what it returns is technically a "cursor".
> 
> It's tempting to think, OK, but the view's ORDER BY would be processed
> first and the final ORDER BY would be processed last, so the order is
> predictable.  In fact, though, there is no first and last.  The SQL
> statement is a declaration, not an imperative.  It describes which rows
> and columns to retrieve.  It specifies only an outcome, not an
> algorithm or an order of operation.
> 
> In effect, your query specified
> 
>   ORDER BY id
>   AND
>   ORDER BY data
> 
> which you would never do, and SQLite can't, either.  ;-)
> 
> HTH.
> 
> --jkl


Very nice explanation, James !

Thank you.

-- kjh( I had to look twice to make sure I was in my SQLite Mailbox and not in 
FreeTDS :)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users