Re: [sqlite] Fwd: inserting new data only

[John McKown]

On Tue, Feb 25, 2020 at 11:03 AM Przemek Klosowski <
przemek.klosowski+sql...@gmail.com> wrote:

> I am storing time series data arriving from a sensor into (time,value)
> records, like so:
> 10:32  12
> 10:35  15
> 10:37  15
> 10:39  13
> 10:43  13
> 10:46  18
>
> and I want to avoid storing repetitive data, so that the database should
> contain
> 10:32  12
> 10:35  15
> 10:39  13
> 10:46  18
> where only the earliest time with the unchanging value is stored.
>
> I don't see how INSERT could be conditional on e.g.  value != (select
> value from tbl order by time descending limit 1), so I thought I'd use
> triggers. The only way I could think of was to delete the new
> duplicate record after it has been inserted:
>
> create trigger cull after insert on tbl when
>  (select value-lead(value) over (order by time desc) from a limit 1) = 0
> begin
>delete from a where time like new.time;
> end;
>
> Is there a simpler way?
> ___
>


Why not:

CREATE TABLE ME (ATIME TIME, VALUE INTEGER PRIMARY KEY);

You can't INSERT duplicate numbers into the "VALUE" column, it will fail.


-- 
People in sleeping bags are the soft tacos of the bear world.
Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Shell commands for controlling headers

[John McKown]

On Mon, Nov 25, 2019 at 8:42 AM Craig Maynard  wrote:

> All,
>
> Could someone clarify the difference between the two sqlite3 shell
> commands .header and .headers?
>
> The relevant documentation page: https://www.sqlite.org/cli.html
>
> On the cli page, .header is discussed in section 5 but does not appear in
> Section 3.
>
> Thanks,
> Craig
>
> --
> Craig H Maynard
> Rhode Island, USA
>
>
In the sqlite cli itself, doing an ".help", I see:

.header(s)

So I am guessing that they are the same things, perhaps for compatibility
with something in the past.

-- 
People in sleeping bags are the soft tacos of the bear world.
Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Opposite of SQLite

[John McKown]

On Thu, Oct 10, 2019 at 1:55 PM Ned Fleming  wrote:

>
> > Someone asked:
> >
> >>> What the opposite of "Lite”?
> >
>
> SQLessLite
>

NoSQLHeavy?



>
> --
> Ned
>
>

-- 
I find television very educational. The minute somebody turns it on, I go
into the library and read a good book
-- Groucho Marx

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output?

[John McKown]

On Wed, Jun 12, 2019 at 8:35 AM Richard Hipp  wrote:

> IEEE754 floating point numbers have separate representations for +0.0
> and -0.0.  As currently implemented, SQLite always display both
> quantities as just "0.0".
>
> Question:  Should SQLite be enhanced to show -0.0 as "-0.0"?  Or,
> would that create unnecessary confusion?
>

Is there any case where the display makes a difference? I cannot think of
any case where it is mathematically important. Actually the "0.0" is more
mathematically correct because zero is neither positive nor negative.

The IBM "mainframe" zSeries processors implement three floating points
formats: HFP (historic "hexadecimal Floating Point"), BFP (Binary Floating
Point -- IEEE754) and DFP (Decimal Floating Point -- IEEE754-2008). I am
not aware of any other architecture which does this.



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


-- 
This is clearly another case of too many mad scientists, and not enough
hunchbacks.


Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite on IBM z/OS Unix

[John McKown]

On Thu, Aug 30, 2018 at 11:12 AM Don V Nielsen 
wrote:

> "Having successfully ported sqlite to z/OS Unix as a 32 bit app"
>
> Totally Awesome! Do I have a solution? No. But I'll bet John McKown will. I
> believe he is a guru with the mainframe.
>

I don't know about being a "guru". Personally, all my HLASM for UNIX
program is LE based (starts up via a CEEENTRY macro) just so that I can use
C language subroutines. This doesn't seem to have any real drawbacks, other
than some learning and recoding the startup/return stuff. The parameter
passing is the same and an HLASM LE routine can do anything that a non-LE
can do, as best as I know.

If anyone is curious about a z/OS UNIX program written in LE HLASM, here:
https://github.com/JohnArchieMckown/utilities-1/blob/master/lsenq.s

This program also shows how to get to the UNIX arguments from the shell
command line. It is not as simple as in z/OS batch. More akin to a TSO
command processor, albeit different.



>
> It is not SqlLite. It is that communication mechanism between the non-LE
> program calling into the LE environment. Are you saying the LE is loading
> and not unloading. I would guess that it is, and it is that which is
> closing the connection. I'm sorry, but I'm 10+ years past working with
> mainframes. But I recall having to do something special when calling
> LE-Cobol from assembler. There was something that needed to be communicated
> to say "Get up and stay up until I tell you to close", otherwise, you are
> continually loading and unloaded LE with every call.
>
> Wish I could be more helpful,
> dvn
>
> On Wed, Aug 29, 2018 at 5:26 PM David Jackson 
> wrote:
>
> > Having successfully ported sqlite to z/OS Unix as a 32 bit app, I am now
> > looking at a c program to make SQL calls to this.
> > Starting with an Assembler routine that runs within z/OS (not Unix),
> which
> > is not LE (Language Environment)enabled, we then call a c routine
> (numerous
> > times) that is LE enabled. That is all working fine and making good SQL
> > calls to sqlite. the c program then returns back to the upper assembler
> > calling program. The problem is that the c routines is opening, issuing
> the
> > SQL and closing on each invocation.
> >
> > Now this may be a dumb question, so apologies up front.
> > Is there any way that the c program can open the sqlite db initially on
> the
> > first call and keep it open after it returns back to the calling program
> > until a final call at which point it will issue the sqlite3_close.
> >
> > Again - sorry if this was a dumb question.
> >
> > Thanks
> > ___
> > 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
>


-- 
People who frustrate us will be around for as long as we need them.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert with an '

[John McKown]

Very good point. I think that everyone should do it that way. It is a bit
more work, but is vastly superior.

On Mon, Jun 11, 2018, 03:23 Olivier Mascia  wrote:

> > Le 11 juin 2018 à 10:07, Peter Nacken  a écrit :
> >
> > I try to insert email addresses into a table and get an error with
> addresses they have a single quotation mark ( na'm...@domain.ltd ).
>
> > Sorry I forgot I‘m using C#
>
> (Assuming: "create table T(E text);" for the following.)
>
> If you're building the text of your insert statement before executing it,
> you will have either to build the statement string as:
>
> insert into T values('na''m...@domain.tld');
> or
> insert into T values("na'm...@domain.ltd");
>
> Both of which you can test with the command-line sqlite3.exe.
>
> But it would be far more appropriate to use prepare:
>
> insert into T values(?);
>
> and then bind the parameter before executing. You won't have to alter your
> data for inserting and it will be much better for SQL code injection
> protection, depending where the email address comes from.
>
> I'm sure there is plenty of documentation with your language binding for
> SQLite on how to prepare, bind, execute. Instead of building a final
> statement as a complete string and then execute it.
>
> --
> Best Regards, Meilleures salutations, Met vriendelijke groeten,
> Olivier Mascia
>
>
> ___
> 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

[sqlite] "missing" sqlite3 invocation option?

[John McKown]

The sqlite3 command has a input command to ".read" a file which "contains
SQL in FILENAME". I am wondering why there isn't an equivalent command line
argument to do this. That is, have something like:

sqlite3 -f FILENAME database.db3

which would do the same as:

sqlite3 database.db3
sqlite> .read FILENAME
sqlite> .quit

This would mirror the PostgreSQL supplied psql command. For whatever that
is worth.

-- 
We all have skeletons in our closet.
Mine are so old, they have osteoporosis.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[John McKown]

I see the point being made by many in this thread. I am not against
expanding SQLite's functionality. But, if I might, I will throw out some
contrarian ideas. First is that SQLite really is an embedded SQL data base.
It is meant to be combined into your application's main executable file. So
the larger you make the basic SQLite engine, the more "bloat" in the
applications which use it. Of course, a good developer is going to set up
all the proper SQLite #define variables to reduce SQLite's footprint to be
only as functional a possible. This is the proper thing to do. The problem,
if there is one, is that the more "options" that can be enabled/disabled
via #define variables, the more variants exist of SQLite. And the more
difficult it is to test every possible variant. Remember, adding just one
more #define for an "optional" function doubles the number of variants.
This is a quite a bit of work to ask of Dr. Hipp for a completely free
software product.

Another thing that I wonder is why people want all of this in an _embedded_
SQL engine? If somebody really needs a full blown relational database
management system, I really think that a client/server model is superior.
Yes, I agree, this is just my opinion. You can have a different one and
neither of us is really "wrong". I love SQLite for a number of things that
I do. But when I want a multi-gigabyte database used by multiple
applications, I go with PostgreSQL. And, yes, I'm aware that there are such
DBs based on SQLite. But I find the "extras" which come with the
client/server model RDBMS fill a great need. One which I must write myself
when I use SQLite, or find one already written by someone else that I can
adopt/adapt. I mentioned PostgreSQL instead of Oracle or MS SQL Server
mainly due to cost and licensing fees. PostgreSQL is FOSS and basically
allows you to "close source" not only your apps, but any mods to PostgreSQL
that you want to. Very similar to the 2-clause BSD and MIT licenses. In
addition, there is a commercial version, EDB, for people who want or need
professional maintenance, such as many larger companies like to have.
SQLite also has such paid support, if needed.

Well, I've just put up a couple of my thoughts. That's all they are. Maybe
some points for polite discussion. Or maybe I'm just more comfortable with
the way that I already do things. {shrug} What do you expect from a person
near retirement?

On Fri, May 4, 2018 at 2:33 PM, cherie  wrote:

>
> In 2008 I was part of a project which was mostly DB driven using Sybase
> 12.5. Sybase neither had support for user functions nor window functions &
> many other features, which other contemporary RDBMS had and same arguments
> was thrown why you need user functions or window functions if both can be
> accomplished by stored procedures. Version 1 release (early 2009) ended up
> with around 200 tables, 250 views and roughly 600 stored procs.
>
> Now after 10 years Sybase 15.7 has introduced user functions (and many
> other
> new features) saying it reduces TCO/time-to-market. Now my same application
> has grown to 500 tables, 410 views and 2600 stored procs. Most of these
> procs are repetitive codes with hard to read complex SQL written by
> developers mostly accomplishing reporting needs that grew in years (as
> application stabilized). And looking at those procs I can very clearly say
> that these are nothing but workarounds cooked to achieve what window
> functions provide in very easy to implement manner to developers. Every now
> and then we keep getting alerts that one of the proc is non performing or
> slow etc.
>
> I would love to see window function (before I die) being introduced in
> SQLite to reduce TCO because these output are anyhow being written in java,
> python or wherever if not in SQLite SQL, how its reducing the overall space
> footprint in android, I am not sure. 10 lines of window function can be
> achieved by 100 lines of CTE based SQL and if you do not have developers
> with SQL inclination they end up writing same in one language or the other.
> Think of testing effort that goes in verifying these codes.
>
> Everything in computer can done by machine/assembly codes, then why to use
> Java or C (its simplicity).
>
> -- Abstraction_(computer_science)
> <https://en.wikipedia.org/wiki/Abstraction_(computer_science)>
>
>
>
> --
> Sent from: http://sqlite.1065341.n5.nabble.com/
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
We all have skeletons in our closet.
Mine are so old, they have osteoporosis.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] printf() problem padding multi-byte UTF-8 code points

[John McKown]

On Tue, Feb 20, 2018 at 11:44 AM, Jens Alfke  wrote:

>
>
> > On Feb 19, 2018, at 7:49 PM, petern  wrote:
> >
> > 3. Why can't SQLite have the expected common static SQL functions for
> > getting rapid development done without external tools?
>
> Because its primary use case is as an embedded library for programs, not
> as a standalone tool or server. From that perspective, it’s wasteful for
> SQLite to include functionality that can be done as well or better by the
> program that calls it.
>

​I agree. Sometimes it seems to me that people are using SQLite as if it
were a "cheap" version of "MS SQL Server"​. And then wanting it to have all
the "bells and whistles" of a full fledged, multi-user, relational SQL data
base. I can even somewhat understand that because it is just so easy to
install and use. Much easier than MySQL, MariaDB, or PostgreSQL (or any POS
on MS Windows).



>
> It’s also very easy to add custom SQL functions to SQLite, so if you have
> a need for these, you can write them yourself and either link them into
> your app, or build them as a library that the sqlite3 tool can load.
>
> —Jens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Easiest way to get day of week as a string (not a number)?

[John McKown]

On Thu, Feb 1, 2018 at 6:55 AM, Chris Green  wrote:

> I want to get Sun, Mon, Tue, Wed etc. from a date, what's the easiest
> way of doing this in a sqlite select?
>
> I guess I can do something (horrible?) with the numeric day of week
> and substr() but is there not an easier way?
>

​-- horrible way
SELECT CASE strftime("%w",DateInRow)
   WHEN 0 THEN "Sun"
   WHEN 1 THEN "Mon"
   ...
   WHEN 6 THEN "Sat"
   END AS DayOfWeek



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



-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UTF8 and NUL

[John McKown]

On Fri, Jan 26, 2018 at 1:41 PM, Peter Da Silva <
peter.dasi...@flightaware.com> wr

> On 1/26/18, 1:37 PM, "sqlite-users on behalf of J Decker" <
> sqlite-users-boun...@mailinglists.sqlite.org on behalf of d3c...@gmail.com>
> wrote:
> >doesn't get 26 either. 0x1a
>
> 26 isn't EOF, it's SUB (substitute). It was used to represent
> untranslatable characters when converting (for example) EBCDIC to ASCII.
>

​In the distant past (CP/M-80), the filesystem meta data did not include
the actual _length_ of the data for a text data file. The I/O was done in
sectors. The CP/M-80 system, by convention, used 0x1A (26) and an "logical
EOF" indication and the C routines would detect it and report EOF.​ MS-DOS
basically didthe same thing, for compatibility reasons. I am not sure, but
I think that Windows still does this. A quick test with the command "type
x.txt" where "x.txt" contained "abc~def" (where ~ is standing in for 0x1a)
resulted in my seeing "abc". But "notepad x.txt" shows "abc def". So I
guess it depends on how old the Windows app is.


-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C++ compiler

[John McKown]

On Sat, Dec 30, 2017 at 5:35 AM, eli  wrote:

> Hello,
>
> It would be awesome if SQLite could compile as a part of bigger C++
> project.
> Right now there is a bunch of pointer casting errors, that can be fixed in
> a matter of hour IMHO.
>

​I'm not a very knowledgeable C++ programmer, but wouldn't a simple:

extern C {
...
... SQLite definitions
...
}

be a way to do it?

Or maybe I'm simplifying your question too much (wouldn't be the first time
for me).​



>
> Cheers,
>


-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Move to Github!!?

[John McKown]

On Tue, Dec 26, 2017 at 3:31 PM, Keith Medcalf  wrote:

>
> Heavens forbid!
>
> How is it possible to work if not from a Command-Line window?  That is,
> YUCK.
> Seriously how can anyone get any work done at all without having a command
> prompt at which one, how to put this, types commands?
>
> I happen to be one of those that holds Ashton-Tate's dBase II (or was it
> III, anyway, one of them) had the absolute best interface ever designed --
> a completely blank screen with a . in the upper left corner as a prompt at
> which you entered commands.  Absolutely beautiful design with absolutely no
> useless crap to get in the way!
>
> The first thing be consigned to the bitbucket on *ANY* Operating System
> worth using is the "Graphical User Interface".  And if you cannot get rid
> of it (like windows) its sole purpose is to open a command prompt to, you
> know, prompt for commands.
>
> Graphical User Interfaces are mostly designed by maroons for use by other
> maroons.  There are a few (very few) things which a GUI is any good for.
> Issuing commands and software development is not one of them.  (I have
> never seen a Graphical Editor that works worth a pinch of coon-poo, and
> so-called IDE's are useless steaming turds as well, most dependent on
> completely unusable editors -- the absolute worst abomination being, of
> course, Visual Studio).
>

​Well, this is very off-topic for this forum. But I'll interject that I
generally agree with you. However, I do think that some things are better
in a graphical environment. Such as, say, a paint program? {grin}. Now, for
an interactive SQL interface, I like using SQLite's command program; or
PostgreSQL's psql. I haven't really tried any SQL GUI interfaces other than
Oracle's. I use it mainly because it's all that I was given on the Windows
desktop at work. I haven't seen very many "character mode" applications any
more for things such as Word Processing or Spreadsheets. I did use Word
Perfect and​ Lotus 1-2-3 back in the MS-DOS days. But today's users want a
word process which is WYSIWYG and want their spreadsheets to create pretty
*π* charts.



>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>

-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Move to Github!!?

[John McKown]

On Tue, Dec 26, 2017 at 9:45 AM, Don V Nielsen 
wrote:

> > What's not so easy to replace is the Git integration in my editor (Visual
> Studio Code)
>
> Same here, but I use JetBrains products. I put a bug in the ear of
> JetBrains. At least its something.
>
> Have a good one, all
>

​I don't know either of those products. Mainly because I'm not a Windows'
developer. Do they have a publicly documented interface between their
product and a source maintenance system (e.g. git, cvs, ...)? If not, then
I guess they are dependent on writing a "one off" for every SCM that they
want to support. IMO, that would be very short sighted. ​

-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] values ?

[John McKown]

On Tue, Dec 12, 2017 at 12:34 PM, Mark Wagner  wrote:

> My reading of https://sqlite.org/syntax/select-core.html makes me think
> that I should be able to issue something like values('foo'); and get a row
> with a single column whose value is 'foo'.  But I get a syntax error.
>
> Probably obvious to the right people but what am I missing?
>
> sqlite> values('foo', 'bar');
> Error: near "values": syntax error
>

​Works for me too:

$ sqlite3
SQLite version 3.14.2 2016-09-12 18:50:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> values(1,2)
   ...> ;
1|2
sqlite> values('foo','bar');
foo|bar
sqlite>
​
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] random value get re-generated too often in SQLite

[John McKown]

correct" or not.​


-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Emulate right-join

[John McKown]

;
> What I want to do is essentially:
> select EventID,ContactInfo,TicketID,Priority,PriorityText,CreateDate from
> Events *RIGHT JOIN* ColorScheme on ColorScheme.PriorityLevel =
> Events.EventID order by {SomeSoftwareDefinedOrder}
>
> What this did in MSSQL2000 days, if I remember correctly, give me all
> results in the Events table even if the relevant info isn't in ColorScheme
> table.  The PriorityLevel and PriorityText would be returned as NULL.
>
> Does anyone have any working theories on how I can get ALL results in the
> Events table regardless if the Events.Priority isn't in
> ColorScheme.PriorityLevel?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[John McKown]

On Tue, Nov 21, 2017 at 10:27 AM, Drago, William @ CSG - NARDA-MITEQ <
william.dr...@l3t.com> wrote:

> > I really need to come up with an alternative to the mailing list.
> > Perhaps some kind of forum system.  Suggestions are welcomed.
> > --
> > D. Richard Hipp
> > d...@sqlite.org
>
> Please, not a forum. The email list is instant, dynamic, and convenient. I
> don't think checking into a forum to stay current with the brisk activity
> here is very practical or appealing.
>

​I completely agree. The problem with a forum is mainly that it is not _a_
forum. It is a forum per list. Which means I spend way too much time
"polling" 8 to 10 web "forums" during the day just to see if anybody has
said anything of interest.


>
> --
> Bill Drago
> Staff Engineer
> L3 Narda-MITEQ
> 435 Moreland Road
> Hauppauge, NY 11788
> 631-272-5947 / william.dr...@l3t.com
>

-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] make install tries to update /usr/share/tcl8.6 regardless of --prefix= value

[John McKown]

Just as background: I updated my sqlite3 directory using the "fossil pull
&& fossil checkout branch-3.21 --force" commands. I then did a "autoconf",
followed by an
"./configure --prefix=$PWD/bld ...". I then did a "make" followed by a
"make install". The first "make" ran just fine. But the "make install"
failed on the command:

/usr/bin/install -c -d /usr/share/tcl8.6/sqlite3

I may be totally off my bird, but I would have hope that a "make install",
in my case, would have tried to install the tcl portions into, perhaps:

$PWD/bld/tcl8.6/sqlite3

I don't know if the action that I saw is intentional or not.

-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Wanting to return REAL as formatted TEXT

[John McKown]

On Wed, Sep 20, 2017 at 11:01 AM, Phoenix 
wrote:

> I am trying to retrieve some numeric data from a table using COBOL (not
> a 'C' programmer).
>
> sqlite3_column_type says its type '2' so I am using
> sqlite3_column_double to return the data. The problem is that the
>

​SQLite3 is different from many other RDMS systems. In something like
PostgreSQL, I would do an SQL similar to " SELECT REAL_COLUMN::TEXT FROM
TABLE;" to cas​t the real data to a text value. In SQLite3, you can simply
sue the "sqlite3_column_text" to return the data. The SQLite3 engine will
"cast" the real data to a textual form.

On page: http://sqlite.org/c3ref/column_blob.html , it states:

The first six interfaces (_blob, _double, _int, _int64, _text, and _text16)
each return the value of a result column in a specific data format. If the
result column is not initially in the requested format (for example, if the
query returns an integer but the sqlite3_column_text() interface is used to
extract the value) then an automatic type conversion is performed.



> language I am using does not seem to like REAL numbers as I'm getting a
> zero value.
>
> From what I have been able to workout it should be possible to return a
> REAL number as a formatted text string, which would make things easier
> for me, but am not sure of the details to do it.
>
> I am not looking for somebody to do this, just some breadcrumbs to point
> me in the correct direction.
>
> regards, Robert
>
>
-- 
*L'Shanah Tovah Tikatevu*

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a performance difference between COUNT(*) and COUNT(name)

[John McKown]

Db=0 root=2
write=0
14Goto   0 1 000
sqlite>

​
​NOT NULL doesn't make a difference. The EXPLAIN shows the operations, and
the first seems to be much more efficient; fewer steps & no loop.​



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



-- 
Caution! The OP is an hyperpolysyllabicsesquipedalianist and this email may
cause stress to those with hippopotomonstrosesquipedaliophobia.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting number of rows with NULL

[John McKown]

On Tue, Sep 5, 2017 at 3:00 PM, Stephen Chrzanowski 
wrote:

> On behalf of Cecil, the fault in that logic is that count(*) returns the
> number of rows in that table, not whether there is a hole "somewhere:  Your
> query will either return 1, or, 0.
>
>
​I either don't understand you, or I am doing something wrong. I used "a"
instead of "Last Used" in my example because I'm just plain lazy.

QLite version 3.14.2 2016-09-12 18:50:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table x (a date,b integer);
sqlite> insert into x values(NULL, 1);
sqlite> insert into x values(NULL, 2);
sqlite> insert into x values('2017-09-01', 2);
sqlite> insert into x values('2017-09-02', 3);
sqlite> insert into x values('2017-09-04', 4);
sqlite> select count(*) from x;
5
sqlite> select count(*) from x where a is NULL;
2
sqlite> select count(*) from x where a is NOT NULL;
3
sqlite>
​


-- 
Caution! The OP is an hyperpolysyllabicsesquipedalianist and this email may
cause stress to those with hippopotomonstrosesquipedaliophobia.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] My 1st C++ SQLite3 program

[John McKown]

On Mon, Aug 28, 2017 at 1:11 PM, Papa  wrote:

> Thanks everyone.
>
> The problem was resolved by rebooting the computer, strange ain't it?
>

​Ah, yes, the main "solution" to most Windows problems. It's what our
desktop ask first - have you rebooted? Yes, and it didn't work? Did you
power cycle the PC? The best that I've gotten so far: Unplug the network
cable; turn off the PC; unplug the power cable; wait 10 minutes; reattach
the network cable; plug the PC back in; power up. I guess the next step is
"shoot either the machine or yourself".



>
>
> On 2017-08-27 10:55 AM, Tim Streater wrote:
>
>> On 27 Aug 2017, at 15:35, Papa  wrote:
>>
>> First and foremost, I'd like to thank everybody for your replies.
>>> Although I have sound knowledge and understanding of C++ [ that can be
>>> debatable ] and still remember a little how C works, I have no Idea how
>>> to utilize the API of SQLitle3. Therefore, it is very difficult for me
>>> to apply the advice given by you all, so, please, using the code I have
>>> provided in the OP, how can I implement your advice?
>>>
>>> Again, thanks ya'll for all the help.
>>>
>>>
>>> On 2017-08-26 11:28 PM, Simon Slavin wrote:
>>>
>>>> On 27 Aug 2017, at 2:34am, Papa  wrote:
>>>>
>>>> Why do I get this error message?
>>>>>
>>>>   Try having the program using file calls to open a simple text file in
>>>> the
>>>> same directory.  Does that succeed ?
>>>>
>>> Do what Simon suggests here. It does not require the SQLite3 API.
>>
>>
>> --
>> Cheers  --  Tim
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> --
> ArbolOne.ca
> Using Fire Fox and Thunderbird.
> ArbolOne is composed of students and volunteers dedicated to providing
> free services to charitable organizations.
> ArbolOne on Java Development in progress [ í ]
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
If you look around the poker table & don't see an obvious sucker, it's you.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE database using parameters

[John McKown]

On Fri, Jul 21, 2017 at 11:07 AM, Jens Alfke  wrote:

>
> > On Jul 21, 2017, at 6:45 AM, Peter Da Silva <
> peter.dasi...@flightaware.com> wrote:
> >
> > Have a look at prepared statements and statement parameters.
>
> Agreed.
>
> PLEASE, PLEASE, do not try to splice parameters into SQL strings! Any
> mistakes in this code leave you vulnerable to SQL Injection Attacks, which
> are depressingly common and cause great damage in the real world. (Some of
> the examples already posted in this thread contain such mistakes…)
>
> SQLite, like every other SQL database I know of, has APIs that let you
> safely plug runtime parameters into statements without having to do string
> concatenation or quoting or sanitization. The resulting code is safe, more
> readable, and as a bonus it’s faster because the database only has to
> compile and optimize the statement once, no matter how many times it runs.
>
> —Jens
>

​And, just to interject a politically incorrect statement, any "programmer"
who does not use the safer interface is either __extremely__ ignorant,  or
arrogantly stupid, and needs to be forced to write all their code in a
"baby" language like BASIC until they learn better.​ Either that or have
their fingers broken.


-- 
Veni, Vidi, VISA: I came, I saw, I did a little shopping.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [OT] UTF8-BOM and text encoding detection (was: UTF8-BOM not disregarded in CSV import)

[John McKown]

On Thu, Jun 29, 2017 at 12:18 PM, Simon Slavin  wrote:

> A couple of minor comments.
>
> On 29 Jun 2017, at 5:39pm, Warren Young  wrote:
>
> > Before roughly the mid 1970s, the size of a byte was whatever the
> computer or communications system designer said it was.
>
> You mean that size of a word.  The word "byte" means "by eight".  It did
> not always mean 7 bits of data and one parity bit, but it was always 8 bits
> in total.
>
> > A common example would be a Teletype Model 33 ASR hardwired by DEC for
> transmitting 7-bit ASCII on 8-bit wide paper tapes with mark parity
>
> Thank you for mentioning that.  First computer terminal I ever used.  I
> think I still have some of the paper tape somewhere.
>
> > The 8-bit byte standard — and its even multiples — is relatively recent
> in computing history.  You can point to early examples like the 32-bit IBM
> 360 and later ones like the 16-bit Data General Nova and DEC PDP-11, but I
> believe it was the flood of 8-bit microcomputers in the mid to late 1970s
> that finally and firmly associated “byte” with “8 bits”.
>
> Again, the word you want is "word".  There were architectures with all
> sorts of weird word sizes.  "byte" always meant "by eight" and was a
> synonym for "octet".
>
> As Warren wrote, words did not always encode text as 8 bits per
> character.  Computers with 16-bit word sizes might encode ASCII as three
> 5-bit characters plus a parity bit, or use two 16-bit words for five 6-bit
> characters plus 2 meta-bits.  With each bit of storage costing around
> 100,000 times what they do now, and taking 10,000 times the time to move
> across your communications network, there was a wide variety of ingenious
> ways to save a bit here and a bit there.
>
> Simon.
>
>
​In today's world, you are completely correct. However, according to
Wikipedia (https://en.wikipedia.org/wiki/Byte_addressing), there was at
least one machine (Honeywell) which had a 36 bit word which was divided
into 9 bit "bytes" (i.e. an address pointed to a 9 bit "byte").​


-- 
Veni, Vidi, VISA: I came, I saw, I did a little shopping.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [OT] UTF8-BOM and text encoding detection (was: UTF8-BOM not disregarded in CSV import)

[John McKown]

On Tue, Jun 27, 2017 at 4:02 PM, Keith Medcalf  wrote:

>
> > If an implementation "uses" 8 bits for ASCII text (as opposed to
> > hardware storage which is never less than 8 bits for a single C char,
> > AFAIK), then it is not a valid ASCII implementation, i.e. does not
> > interpret ASCII according to its definition. The whole point of
> > specifying a format as 7 bits is that the 8th bit is ignored, or
> > perhaps used in an implementation-defined manner, regardless of whether
> > the 8th bit in a char is available or not.
>
> ASCII was designed back in the days of low reliability serial
> communications -- you know, back when data was sent using 7 bit data + 1
> parity bits + 2 stop bits -- to increase the reliability of the
> communications.  A "byte" was also 9 bits.  8 bits of data and a parity bit.
>
> Nowadays we use 8 bits for data with no parity, no error correction, and
> no timing bits.  Cuz when things screw up we want them to REALLY screw up
> ... and remain undetectable.
>

​Actually, most _enterprise_ level storage & transmission facilities have
error detection and correction codes which are "transparent" to the
programmer. Almost everybody knows about RAID arrays which (other than
JBOD) have either "parity" (RAID5 is an example) or is "mirrored" (RAID1).
Most have also heard of ECC RAM memory. But I'll bet that few have heard
of​ RAIM memory, which is used on the IBM z series of computers. Redundant
Array of Independent Memory. This is basically "RAID 5" memory. In addition
to the RAID-ness, it still uses ECC as well. Also, unlike with an Intel
machine, if an IBM z suffers a "memory failure", there is usually the
ability for the _hardware_ to recover all the data in the memory module
("block") and transparently copy it to a "phantom" block of memory, which
then takes the place of the block which contains the error. All without
host software intervention.

https://www.ibm.com/developerworks/community/blogs/e0c474f8-3aad-4f01-8bca-f2c12b576ac9/entry/IBM_zEnterprise_redundant_array_of_independent_memory_subsystem
?


-- 
Veni, Vidi, VISA: I came, I saw, I did a little shopping.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 3.19.3 README.md Doc bug

[John McKown]

On Wed, Jun 14, 2017 at 10:40 AM, petern 
wrote:

> Was there a version in the past where the compile instructions made sense?
>
> tar xzf sqlite.tar.gz;#  Unpack the source tree into "sqlite"
> mkdir bld;#  Build will occur in a sibling
> directory
> cd bld   ;#  Change to the build directory
> ../sqlite/configure  ;#  Run the configure script
>
> Lost me there at the configure step...
>
> It seems to me the line should read simply
>
> ../configure  ;#  Run the configure script
>

​Makes sense. At the time you issued the "tar" command, you are in
directory "x". Perhaps the following will make more sense (stuff before the
> is the current working directory)

x>tar xzf sqlite.tar.gz
x># above creates directory ./sqlite
x>mkdir bld # make directory ./bld
x>cd bld
x/bld>../sqlite/configure
x/bld> # up to directory x, then down in into directory sqlite & run
configure residing there
x/bld> # results of configure are put in this directory
​


-- 
Veni, Vidi, VISA: I came, I saw, I did a little shopping.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

[John McKown]

On Thu, Jun 8, 2017 at 12:15 PM, Jens Alfke  wrote:

> SQLite is primarily an _embedded_ database library. In that use case,
> comments on the schema properly belong in the program that creates the
> database, next to the sqlite3_exec("CREATE TABLE…”) calls.
>
> I realize that when SQLite is being used as a command-line DBM tool,
> having comments in the schema itself would be useful. But this is a
> minority use case.
>
> As someone who uses SQLite in mobile and IOT app development, I really do
> not want features in the core — increasing its size, complexity and
> potential for bugs — that are not of use for embedded databases.
>
> —Jens
>

​I agree with you. Sometimes it seems to me that people are basically
saying "Why isn't SQLite identical to Oracle?" (or PostgreSQL).​


-- 
Prof: So the American government went to IBM to come up with a data
encryption standard and they came up with ...

Student: EBCDIC!

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] thousand separator for printing large numbers

[John McKown]

On Fri, Feb 10, 2017 at 11:21 AM, Jens Alfke  wrote:

>
> > On Feb 10, 2017, at 5:59 AM, Dominique Devienne 
> wrote:
> >
> > 2) ask DRH to consider enhancing SQLite's built-in printf() to support it
> > out-of-the-box.
>
> I disagree; this is feature bloat. SQLite is an embedded database, and the
> host app can do whatever it wants with the data, such as formatting it.
> There is no serious need to make SQLite into a Swiss army knife that can do
> all sorts of things that the host app could easily do itself.
>
> > But when you use SQLite to create databases to be viewed in any SQLite
> > client, as is the case here, you're limited to what SQLite provides
> out-of-the-box.
>
>
> I don’t see that use case as being as important as the primary ones. I
> suppose you can look at SQLite as a sort of interchange format for database
> client apps, but putting SQLite itself in charge of user interface features
> like localized number formatting is wrong-headed.
>
> I think you’re much better off going to the developers of the client apps
> that you use and asking them to add improved number formatting in their UI.
>
> —Jens
>
>
​I agree with J​ens. SQLite is not PostgreSQL, or even MySQL. For my part,
if I want a fancy data base, I go with PostgreSQL. At least for me, SQLite
is a really nice relational data store with an SQL interface which I use
when, in the past, I would have used some other application specific data
store (e.g. Berkeley DB or GDB). What I like about it is that I can, if I'm
careful, "scale up" from SQLite to a more powerful RDMBS like PostgreSQL if
my application needs to go from being "one user at a time" to "multiple
concurrent users". Just my opinion, of course. Others may reasonably
disagree.


-- 
Our calculus classes are an integral part of your education.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Query truncating String column at 255 chars long

[John McKown]

On Fri, Jan 27, 2017 at 2:51 PM, Warren Young  wrote:

>
> > There’s no obfuscated Perl contest because it’s pointless.
>
> Perl is not pointless:
>

​True. I love it. But most Perl code is, by design, already rather obscure.
Some of the "idioms​" used (like slurping input and then doing a single
regexp to replace something in the entire "document" at once rather than
line by line) are not obvious just from reading the language definitions.
So there is no need for a contest to make it so (C, on the other hand,
tends to be more "straight forward" as it is normally programmed).

-- 
There’s no obfuscated Perl contest because it’s pointless.

—Jeff Polk

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Query truncating String column at 255 chars long

[John McKown]

On Fri, Jan 27, 2017 at 12:58 PM, Antonio Carlos Jorge Patricio <
antonio...@gmail.com> wrote:

> I have a table in a SQLite 3 database file (.db3), which has a TEXT column
> whose fields often can be 1024 chars long or more.
>
> My application connects to this file using SQLite.NET library (from Nuget)
> and runs a simple "SELECT * FROM tabRxBinder;" command to populate a
> DataTable object.
>
> Inside this DataTable, all strings belonging to that column are truncated.
> In my tests, they got almost all chopped at 255 chars long, and just one
> was 270 chars long, but also truncated.
>
> I googled and found some people struggling with similar problems, but
> couldn't find a solution. Can anyone help me on this?
>
> Thank you very much.
>
>
​I'm guessing this is a problem with SQLite.NET, or .NET itself, which is
not supported here. I am not a Windows literate developer. I run SQLite on
Linux and don't have any problem with >255 characters: example

SQLite version 3.14.2 2016-09-12 18:50:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table testit (testtext text);
sqlite> insert into testit(testtext)
values('2');
sqlite> select * from testit;
2
sqlite> select length(testtext) from testit;
401
sqlite>
​


-- 
There’s no obfuscated Perl contest because it’s pointless.

—Jeff Polk

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [OT] mySQL ORDER BY clause in Views

[John McKown]

Loved that explanation. I could easily understand it.

On Jan 19, 2017 17:14, "James K. Lowden"  wrote:

> On Wed, 18 Jan 2017 23:36:14 +
> Peter Haworth  wrote:
>
> > if I include a WHERE claus, the view's ORDER BY clause is ignored and
> > the rows are returned in seemingly random order.
> >
> > Searching around the web suggests that this behavior is accepted as
> > correct in mySQL although I haven't been able to find a justification
> > for it
>
> I'd like to amplify Ryan's correct answer that a view, like a table,
> has no defined order.
>
> Regarding justification: That's what the SQL standard says.
>
> Why is that the standard?
>
> A view is not merely stored SQL; it is a *definition*. Since a data
> definition in SQL has no order, it's no surprise that a view has no
> order.  To impose order on a view (as part of its definition) is to
> impose meaning on the order, and order is not part of the data (what
> relational folks call the relation's "extension").  In SQL, data exists
> only when expressed explicitly, usually as a column.
>
> It's tempting to think of a view as a macro.  But SQL has no macro
> system.  When we drop a view into a query, it's convenient and correct
> to think of it as a "all that view's SQL here, as if a table", and to a
> large extent that's also how most SQL engines process it.  That
> conceptualization falls down when ORDER BY and LIMIT are included,
> because the view is no longer "like a table".
>
> I'm sure it's too late for the 2017 wish list, but ISTM this is an
> opportunity to suggest a "standard" mode (or perhaps "pedantic") that
> would warn about or refuse to process SQL constructs that are in
> exception to the SQL standard.  Then projects like your that have
> big-boy aspirations could guard against inadvertent reliance on
> nonstandard features.
>
> --jkl
>
>
> ___
> 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] locating a minimum in SQLite 2

[John McKown]

On Thu, Dec 1, 2016 at 1:12 PM, James Walker 
wrote:

> Let's say I have a table INFO with columns PRICE and IDENT, and I want to
> find the IDENT of the row with the minimum value of PRICE.  In SQLite 3, I
> can say
>
> SELECT MIN(PRICE), IDENT FROM INFO;
>
> and get what I want.  But in SQLite 2 (legacy code), this doesn't work...
> I get the minimum value, but NULL in the IDENT column.  I could say
>
> SELECT PRICE, IDENT FROM INFO ORDER BY PRICE;
>
> and ignore all but the first row of the result, but I'm sure there must be
> a better way?
>
>
​Well, standard SQL seems to work, but would return multiple lines if
multiple rows have the minimal price, but you could use LIMIT 1:

​SQLite version 3.14.2 2016-09-12 18:50:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table info(price int, ident text);
sqlite> insert into info(price,ident) values(1,'a1');
sqlite> insert into info(price,ident) values(2,'b');
sqlite> insert into info(price,ident) values(3,'c');
sqlite> insert into info(price,ident) values(1,'a');
sqlite> select price, ident from info where price=(select min(price) from
info);
1|a1
1|a
sqlite> select price, ident from info where price=(select min(price) from
info) limit 1;
1|a1
sqlite>

​

​In sqlite 2 the same works:

SQLite version 2.8.17
Enter ".help" for instructions
sqlite> create table info(ident text,price int);
sqlite> insert into table(ident, price) values('a',1);
SQL error: near "table": syntax error
sqlite> insert into info(ident, price) values('a',1);
sqlite> insert into info(ident, price) values('a1',1);
sqlite> insert into info(ident, price) values('b',2);
sqlite> insert into info(ident, price) values('c',3);
sqlite> select min(price), ident from info;
1|
sqlite> select price, ident from info where price=(select min(price) from
info);
1|a
1|a1
sqlite> select price, ident from info where price=(select min(price) from
info) limit 1;
1|a
sqlite>
​

-- 
Heisenberg may have been here.

Unicode: http://xkcd.com/1726/

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[John McKown]

On Wed, Nov 30, 2016 at 9:46 AM, Chris Locke  wrote:

> I recently had this problem. Values stored as real values. Had to check
> records in the database to see if any value had changed, and needed
> updating. Even though all values in my code were singles, I had bad
> rounding problems where (as an example) 0.1+2.2 did not equal 2.3 in the
> database. Aargh.
> Storing as integers is the way to go.
>

​At present, I think that is the best way.​ Of course, we could argue that
every RDMS "should" implement Decimal Floating Point.
https://en.wikipedia.org/wiki/Decimal_floating_point



>
> Thanks,
> Chris
>

-- 
Heisenberg may have been here.

Unicode: http://xkcd.com/1726/

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .mode column .width .separator

[John McKown]

On Thu, Oct 13, 2016 at 2:42 PM, Don V Nielsen 
wrote:

> Thanks, but it appears that ".mode column" triggers it, and .separator does
> not appear to have any influence in that mode.
>
> Out of curiosity, it appears that the row separator (in windows) is a
> single character. Do you know to specify as the row separator?  Everything
> I attempt is taken as literal characters.  I've attempted: 0x0D0x0A,
> 0x0D0A, 0Dx0Ax. Nothing appears to work.
>

​The row separator is specified using the 2nd parameter of the .separator
command. Example transcript:


SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .mode column
sqlite> .separator | -
sqlite> create table a(one text,two text);
sqlite> .width 10 10
sqlite> insert into a values('1a','2a');
sqlite> insert into a values('1b','2b');
sqlite> select * from a;
1a  2a-1b  2b-sqlite>

Note that the specified column separator is ignored in .mode column, but
the row separator is not. Also, for fun, note what happens with negative
widths

sqlite> .width -10 -10
sqlite> select * from a;
1a  2a-1b  2b-sqlite>

Also, I have looked at the current sqlite3.c source code. In .mode column,
the space separator character is "hard coded" and so cannot be set to any
other character.​

Lastly, you can specify a "control" character by using a C language escape.
E.g. (continuing from above examples)

sqlite> .separator - \n
sqlite> select * from a;
1a  2a
1b  2b

To address your desire, it would be necessary for the column separator
character to be honored in .mode column mode and the separator be made a
0x00, or \0. If Dr. Hipp were to do this, this would eliminate the column
separator entirely because \0 would result in "no" character between the
columns. This appears, to me, to be a rather simple change in shell.c.


> dvn
>
>

-- 
Heisenberg may have been here.

Unicode: http://xkcd.com/1726/

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] AS being optional

[John McKown]

That's the ANSI / ISO standard for the SQL language.

On Thu, Aug 11, 2016 at 10:02 AM, Richard Hipp  wrote:

> On Thu, 11 Aug 2016 15:57 +0100, Tim Streater 
> wrote:
> > Is there a reason why AS is optional
>
> Because that is what PostgreSQL does.  (Also MySQL.  Maybe others too.)
>
> --
> 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
>



-- 
Klein bottle for rent -- inquire within.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 64-bit SQLite3.exe

[John McKown]

On Wed, Aug 10, 2016 at 4:35 PM, Michael Falconer <
michael.j.falco...@gmail.com> wrote:

> Great discussion, if somewhat amusing (with a hint of deja vu) for an old
> hack like myself.
>
> I remember discussions like this back in CP/M days when it was a move rom 8
> to 16bit. Oh, and it all happened again when 32bit came along and yes now
> we are into 64bit and it carries on.
>

​A bit OT, but this entire thing is mildly amusing to me. I've worked on
the "obsolete" IBM mainframes for 30+ years. Originally 24 bit addressing;
then 31 bit (yeah, 31 not 32 - weird); now up to 64 bit. An guess what? The
programs that I wrote 30+ years ago in 24 bit still work correctly in
today's 31 ​and 64 bit systems as is without recompiling. Of course, the
programs don't use any of the new, fancy stuff. But they still work
correctly.


> --
> Regards,
>  Michael.j.Falconer.
>


-- 
Klein bottle for rent -- inquire within.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Missing source code in 3.14 release

[John McKown]

On Mon, Aug 8, 2016 at 3:17 PM, Darko Volaric  wrote:

> Though clearly a man committed to open source, it's a bit shocking that Dr
> Hipp has not released the source code for that pie.
>
>
​I have it. I got is from the fossil repository by doing:

fossil pull
fossil checkout version-3.14.0

It is not in the "trunk" branch right now. And it appears to be available
on this page: http://sqlite.org/download.html
​


-- 
Klein bottle for rent -- inquire within.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Correct, best, or generally accepted database structure for groups of things

[John McKown]

On Fri, Jun 17, 2016 at 1:37 AM, Chris Locke 
wrote:

> I fail to see what any of this has to do with sqlite.  I thought this was a
> mailing list for sqlite?  Seeing queries (no pun intended) on sql
> statements is very subjective, especially with the limited data provided by
> the original poster.
> Everyone will give helpful advice, but it won't stop there, and as soon as
> the original poster has another query (no pun intended) which would result
> in a schema change, this would have to be explained, etc.
>
> A specific group on SQL is required.
>

​Perhaps so. But such a group would run into problems because it would be a
case of "whose SQL?" The four "big" ones that I know of are: SQLite,
PostgreSQL, Oracle, and IBM's DB2. The basics are the same, but each has
their own peculiarities. ​I don't know what the intent of this forum really
is. It is only for SQLite related "perculiarities"? Or does it include
something like the OP's question which is basically "how do I do a SQLite
query to get this information?" I don't really know. I also monitor the
PostgreSQL forums and see this "how do I craft an SQL query to ...?" type
question quite often. What is weird to me, is that someone will post such a
question on the _bugs_ forums, phrasing it as "I did this SQL query and it
didn't do what I expected. Please fix your product to make it work." And
the reason it didn't work was because the SQL query is garbage. Ah, the
ever requested "do what I need, not what I said" fix.



>
> Just my thoughts...
>
>
> Chris
>
>
-- 
"Pessimism is a admirable quality in an engineer. Pessimistic people check
their work three times, because they're sure that something won't be right.
Optimistic people check once, trust in Solis-de to keep the ship safe, then
blow everyone up."
"I think you're mistaking the word optimistic for inept."
"They've got a similar ring to my ear."

From "Star Nomad" by Lindsay Buroker:

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trouble coding conditional UNIQUE

[John McKown]

On Tue, Jun 14, 2016 at 8:47 AM, James K. Lowden 
wrote:

> On Mon, 13 Jun 2016 19:11:29 +
> "Drago, William @ CSG - NARDA-MITEQ"  wrote:
>
> > I need UNIQUE(B, C) only when E=0.
>
> A conditional constraint is evidence that you have two kinds of things
> represented in one table: those E=0 types that are identified by {B,C},
> and the rest.  They're represented in a single table because they
> seem to have the same columns, although the E=0 types don't need an E
> column.
>
> A better solution might be to separate the two types into to two
> tables, each with its own constraints, and use a UNION to represent
> them as one.
>
> --jkl
>

​I was thinking the same thing, but couldn't phrase it as well as you did.
But I have this unusual(?) habit of liking to do things "by the book",
which in this case tends to be "Database Design & Relational Theory" and
"SQL and Relational Theory", both by Dr. C. J. Date​

​. Also tend to be more a theorist than an actual "real world"
practitioner. ​I.e. I favor design over performance more than I should.


-- 
"Pessimism is a admirable quality in an engineer. Pessimistic people check
their work three times, because they're sure that something won't be right.
Optimistic people check once, trust in Solis-de to keep the ship safe, then
blow everyone up."
"I think you're mistaking the word optimistic for inept."
"They've got a similar ring to my ear."

From "Star Nomad" by Lindsay Buroker:

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I solved the problem with gridview and database

[John McKown]

On Mon, Jun 13, 2016 at 8:15 AM, jumper  wrote:

> Can someone please tell me how to stop the previous post so everyone
> doesn't see it anymore?
> thank you
>
>
​This is a e-mail based forum. Your request is the equivalent of asking the
post office to "undeliver" an already delivered mass mailing. Can't be
done.​


-- 
The unfacts, did we have them, are too imprecisely few to warrant our
certitude.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQL Help: Finding case insensitive matches

[John McKown]

On Fri, May 13, 2016 at 10:14 AM, Dominique Devienne 
wrote:

> Imagine I have a single table containing a single text column, of user
> names.
> I'm trying to find user names which differ only by case.
>
> > select count(*) from os_users
> 1969
>
> > select u1.user, u2.user from os_users u1, os_users u2 where
> upper(u1.user) = upper(u2.user) and u1.user <> u2.user
> foo|FOO
> FOO|foo
>
> > select user from os_users u where exists (select 1 from os_users u2
> where upper(u2.user) = upper(u.user) and u2.user <> u.user)
> foo|FOO
> FOO|foo
>
> I tried both queries above, but they are slow (almost 3s, with close
> to 4M steps, i.e. O(N^2)), and ideally I'd like a query returning only
> one row. And that's w/ or w/o a PK or UNIQUE INDEX on the one column.
>
> > select group_concat(user) from os_users group by upper(user) having
> count(*) > 1
> foo,FOO
>
> I did manage the above, which returns in 7ms, and get me what I want,
> but in aggregated form (thus needs reparsing). Is there a query that
> can return just foo|FOO, i.e. the pairs of mixed-case matches
> efficiently?
>

?how about: SELECT group_concat(user,"|") FROM os_users GROUP BY
upper(user) HAVING count(*) > 1
??



>
> Thanks, --DD
>


-- 
The unfacts, did we have them, are too imprecisely few to warrant our
certitude.

Maranatha! <><
John McKown

[sqlite] Illegal SQL not rejected

[John McKown]

On Fri, Apr 29, 2016 at 1:23 PM, Igor Korot  wrote:

> Marc,
>
> On Fri, Apr 29, 2016 at 1:50 PM, Marc L. Allen
>  wrote:
> > That error is saying that you can't using HAVING on a column unless it's
> in a group by or it's referenced in an aggregate in the HAVING clause
> >
> > You could say, HAVING SUM(A) <> 0 or something.
> >
> > The query as stated
> >
> > SELECT SUM(A)
> > ...
> > HAVING A<>0
> >
> > makes no sense because A is not in the select list.  Only SUM(A) is.
>
> Does anybody in MS "speak any English?" (C) ;-)
>

?Yes, they speak the Microsoft revised standard English, which is
incompatibly extended and modified from U.S. English (as opposed to Her
Majesty's English?, and whatever it is that the Australians speak ('strine)
 [giggle]).



>
> Thank you.
>
> P.S.: This is a rhetorical question, BTW.
>
>

-- 
The unfacts, did we have them, are too imprecisely few to warrant our
certitude.

Maranatha! <><
John McKown

[sqlite] Illegal SQL not rejected

[John McKown]

On Fri, Apr 29, 2016 at 12:41 PM, Igor Korot  wrote:

> Hi,
>
> On Fri, Apr 29, 2016 at 1:28 PM, John McKown
>  wrote:
> > On Fri, Apr 29, 2016 at 12:00 PM, Jann Roder 
> > wrote:
> >
> >> Hi,
> >> It seems like a too obvious omission to not be intentional. But I wonder
> >> why a query like
> >>
> >> SELECT SUM(A)
> >> FROM TABLE
> >> GROUP BY B
> >> HAVING A <> 0
> >>
> >> Is not rejected. MS SQL server gives you this error message in this
> case:
> >>
> >> Column ?A? is invalid in the HAVING clause because it is not contained
> in
> >> either an aggregate function or the GROUP BY clause.
> >>
> >
> > I agree that it just looks _wrong_. And PostgreSQL certainly complains
> > about it.
>
> This is weird because A is part of sum(A), which IS aggregate function
> call.
> Or am I missing something?
>

?It is the syntax. PostgreSQL rejects

SELECT SUM(A) FROM TABLE GROUP BY B HAVING A<>0;

but  accepts

SELECT SUM(A) FROM TABLE GROUP BY B HAVING SUM(A) <>0;

?SQLite is processing the first one as if would the second, as best as I
can tell.

The PostgreSQL documentation states it as I am used to seeing:



HAVING eliminates group rows that do not satisfy the condition. HAVING is
different from WHERE: WHERE filters individual rows before the application
of GROUP BY, while HAVING filters group rows created by GROUP BY. Each
column referenced in conditionmust unambiguously reference a grouping
column, unless the reference appears within an aggregate function or the
ungrouped column is functionally dependent on the grouping columns.

?

The SQLite documentation states (point 3)



If a HAVING clause is specified, it is evaluated once for each group of
rows as a boolean expression <http://sqlite.org/lang_expr.html#booleanexpr>.
If the result of evaluating the HAVING clause is false, the group is
discarded. If the HAVING clause is an aggregate expression, it is evaluated
across all rows in the group. If a HAVING clause is a non-aggregate
expression, it is evaluated with respect to an arbitrarily selected row
from the group. The HAVING expression may refer to values, even aggregate
functions, that are not in the result.



?So, it turns out that SQLite is documented as accepting the statement. So
this is not a "bug", per se. But basing a resultant row on "evaluated with
respect an arbitrarily selected row from the group" seems "un-useful".?




> Thank you.
>
>

-- 
The unfacts, did we have them, are too imprecisely few to warrant our
certitude.

Maranatha! <><
John McKown

[sqlite] Illegal SQL not rejected

[John McKown]

On Fri, Apr 29, 2016 at 12:00 PM, Jann Roder 
wrote:

> Hi,
> It seems like a too obvious omission to not be intentional. But I wonder
> why a query like
>
> SELECT SUM(A)
> FROM TABLE
> GROUP BY B
> HAVING A <> 0
>
> Is not rejected. MS SQL server gives you this error message in this case:
>
> Column ?A? is invalid in the HAVING clause because it is not contained in
> either an aggregate function or the GROUP BY clause.
>

?I agree that it just looks _wrong_. And PostgreSQL certainly complains
about it.?



>
> It's not even clear to me what SQLite does with a query like that.
>

?I executed the above both with the HAVING clause and without it. I also
did an EXPLAIN on both. Judging by the EXPLAIN output and the actual
output, what it seems to do is exclude rows which have SUM(A) equal to
zero. I.e. the HAVING is acting on the SUM(A).


>
> Jann
>


-- 
The unfacts, did we have them, are too imprecisely few to warrant our
certitude.

Maranatha! <><
John McKown

[sqlite] Primary key values can be NULL

[John McKown]

On Fri, Apr 15, 2016 at 3:04 PM, R Smith  wrote:

>
> That's great news :)
> Let me just note that we do not really shun the likes of Postgress, MSSQL,
> MySQL etc. - those systems answer a different need. If you for instance
> want to store secret information with per-table user access controls, or
> run a networked client-server kind of data model, then you should not use
> SQLite, you should really use one of those. SQLite is much better for local
> storage though, and even as a software data storage for your own software -
> as Richard delightfully likes to point out (I'm paraphrasing a bit since I
> cannot recall the actual quote) - "We'd like you to not think of SQLite as
> a replacement for Postgress/MySQL/etc  We'd like you to think of it as
> a replacement for fOpen()."
>

I am not really a developer. More of a dilettante programmer. ?But this
last is how I think of ?using SQLite. I use for permanent storage, in
preference to reading regular file and putting the data into something like
a Java Hashmap.



>
>
> Cheers,
> Ryan
>


-- 
"He must have a Teflon brain -- nothing sticks to it"
Phyllis Diller

Maranatha! <><
John McKown

[sqlite] Primary key values can be NULL

[John McKown]

On Fri, Apr 15, 2016 at 1:00 PM, Cecil Westerhof 
wrote:
??


>
> ?I do not think it is. When you add something to the database to signify
> that a primary key is not allowed to be NULL, then this is not in an old
> database, ergo in the old database NULLs are allowed. Where does backward
> compatibility get broken?
>

?I am somewhat hesitant to ?join in to this, however briefly. What occurs
to me on the breaking of backward in compatibility is an old application,
which is dependent on NULLs in a primary key, creating a _new_ database.
Perhaps because it has a "unload" and "reload" or "import" capability. Or
even one which depends on the user using the sqlite3 command to do backups.
If a PRAGMA were to be established as you have suggested, then it needs to
default to the _old_ way of doing things simply because the aforementioned
old application will not know of it and thus not use it.



> As I see it, it is as with partial indexes. That is a big change (I think),
> but it did not break backward compatibility.
>
>
-- 
"He must have a Teflon brain -- nothing sticks to it"
Phyllis Diller

Maranatha! <><
John McKown

[sqlite] Article about pointer abuse in SQLite

[John McKown]

On Thu, Mar 24, 2016 at 5:59 PM, Keith Medcalf  wrote:

> On Thursday, 24 March, 2016 08:27, Tim Streater 
> said,
>
> > On 24 Mar 2016 at 13:41, Jim Callahan 
> > wrote:
>
> > > Assuming one did not get a cryptic compiler message (and in those days
> > all compiler messages were cryptic, "Probable user error:") ...
>
> > Not always: If you used JAIL as a variable in a computed GOTO, the XDS
> > FORTRAN compiler for the Sigma 7 would, in response to the statement:
>
> >GOTO JAIL
>
> > give you the message:
>
> > *** Warning: Go directly; do not pass GO; do not collect $200
>
> Ah the memories ...
>

?Me too. Along with one that I got that really upset me:

ABOVE BUL

That was the _entire_ message! Turns out BUL is "Background Upper Limit".
I.e. I had too big of an array, so the program wouldn't fit into? memory.
And, the ever popular:

END OF FILE ON UNIT 05 AT RUN TIME


-- 
How many surrealists does it take to screw in a lightbulb? One to hold the
giraffe and one to fill the bathtub with brightly colored power tools.

Maranatha! <><
John McKown

[sqlite] attach db limit

[John McKown]

In the source to 3.12.0, the source in src/main.c has:

#if SQLITE_MAX_ATTACHED<0 || SQLITE_MAX_ATTACHED>125
# error SQLITE_MAX_ATTACHED must be between 0 and 125
#endif

The limits.html on the Web site also says 125. As does the changes for
3.8.6. Why 125 would be a good question. The value relates to an "array" of
bits. If SQLITE_MAX_ATTACHED <= 30 the array is kept in a single int (2
bits are used for the dedicated "databases" (the "main" and "temp"). If >
30 then it is an array of ints, indexed by an integer value. Now, 125 is
127-2, which is the largest signed byte value. But having looked at the C
code, I don't see why this couldn't range up to 8*(2^31-1) == 8*2Gi == 16Ti
tables. But I could (easily) be missing something.

Anyway, it appears that the comment you found was never updated.

On Thu, Mar 3, 2016 at 2:40 PM, Tim Uy  wrote:

> Hi, I see that there is a default limit of 10 attached databases. And from
> the docs, 125 is limit. However, in the file sqliteLimit.h it says
>
> /*
> ** The maximum number of attached databases.  This must be between 0
> ** and 62.  The upper bound on 62 is because a 64-bit integer bitmap
> ** is used internally to track attached databases.
> */
> #ifndef SQLITE_MAX_ATTACHED
> # define SQLITE_MAX_ATTACHED 10
> #endif
>
>
> So is the limit 62 or is the limit 125?
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
A fail-safe circuit will destroy others. -- Klipstein

Maranatha! <><
John McKown

[sqlite] EBCDIC tester needed

[John McKown]

rsion 3.11.0 2016-02-05 14:11:12
Enter ".help" for usage hints.
sqlite> .tables
sqlite> create table one(one int);
sqlite> insert into one values(1);
sqlite> create table two (two text);
sqlite> insert into two values('two');
sqlite> select * from one;
1
sqlite> select * from two
   ...> ;
two
sqlite> insert into one values('ugly');
sqlite> select * from one;
1
ugly
sqlite> .quit
~/projects/sqlite-test$
exit
logout

Script done on Mon 08 Feb 2016 03:07:41 PM CST


===?


-- 
The man has the intellect of a lobotomized turtle.

Maranatha! <><
John McKown

[sqlite] Efficient relational SELECT

[John McKown]

On Thu, Feb 4, 2016 at 12:32 PM, Simon Slavin  wrote:

> Simplified explanation.  Here's the setup:
>
> Two tables:
>
> rooms: each room has an id (think the standard SQLite rowid/id) and some
> other columns
> bookings: includes room id, date, time, and some other stuff
>
> Date/time is encoded as a long COLLATEable string.  In other words sorting
> a column by my date/time format will sort correctly into date order.
>
> Bookings may be made in reverse order.  In other words, someone may book a
> room for next month, and after that someone may book the same room for
> tomorrow.
>
> System contains about 50 rooms and 5000 bookings.  I can create whatever
> indexes you want.
>
> Requirement:
>
> I want to show an HTML table which lists some rooms (using WHERE and ORDER
> BY) and the latest time each of those rooms is booked for.  At the moment
> my code uses one SELECT to get room details and one SELECT for each room to
> find the most recent booking.
>
> The obvious thing is to combine the two using JOIN.  But the best JOIN I
> can think of has to find the most recent booking using a clunky test for
> the biggest date.  And I can't figure out a good phrasing for a sub-select.
>
> Simon.
>

?CTE to the rescue!

WITH
   SELECT ID, MAX(DateTime_Column) as LastDateTime FROM bookings GROUP BY
ID?
AS room_last_used
SELECT rooms.ID, rooms.othercolumns, room_last_used.LastDateTime
FROM rooms
JOIN room_last_used
ON rooms.ID = room_last_used.ID
ORDER BY ...
;

Note - I'm old. I have arthritis. My typing sometimes stinks (like my
feet). And I can't test the above because I ain't got no data.


-- 
Werner Heisenberg is driving down the autobahn. A police officer pulls
him over. The officer says, "Excuse me, sir, do you know how fast you
were going?"
"No," replies Dr. Heisenberg, "but I know where I am."

Computer Science is the only discipline in which we view adding a new wing
to a building as being maintenance -- Jim Horning

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

He's about as useful as a wax frying pan.

Maranatha! <><
John McKown

[sqlite] Running Sqlite under Linux Terminal Server

[John McKown]

On Mon, Jan 25, 2016 at 12:22 PM, Simon Slavin  wrote:

>
> On 25 Jan 2016, at 5:02pm, Bernard McNeill  wrote:
>
> > Idea to have Sqlite database on server, accessed from a few ultra-thin
> > Terminal clients.
>

?I would guess that this harks back to the X terminal days. That is, a very
small machine which basically just drives a keyboard, mouse, and display;
with all the real work & data being on the remote server.



>
> SQLite is not a server/client system.  You would have to write your own
> server/client system.
>

?I was thinking that too. Of course, the OP could write his own C/S
database server using SQLite as the core, if there was a good need. But,
personally, I'd go with a database which is designed and tested for this,
myself. And, for me, that would be PostgreSQL. It is "heavy", but it can do
amazing things. And I just personally like it a bit better than
MariaDB/MySQL.?


>
> Simon.
>

-- 
Werner Heisenberg is driving down the autobahn. A police officer pulls
him over. The officer says, "Excuse me, sir, do you know how fast you
were going?"
"No," replies Dr. Heisenberg, "but I know where I am."

Computer Science is the only discipline in which we view adding a new wing
to a building as being maintenance -- Jim Horning

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

He's about as useful as a wax frying pan.

Maranatha! <><
John McKown

[sqlite] Function patternCompare() not EBCDIC friendly

[John McKown]

On Wed, Dec 30, 2015 at 9:55 AM, Roland Martin 
wrote:

> I have tested the code change on z/OS and it works.
>

?Any chance that I could beg the source and executable code from you?


>
> Thanks for the quick turnaround!
>
> Roland Martin
>
>
-- 
Computer Science is the only discipline in which we view adding a new wing
to a building as being maintenance -- Jim Horning

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] whish list for 2016

[John McKown]

On Thu, Dec 24, 2015 at 8:39 PM, Simon Slavin  wrote:

>
> On 25 Dec 2015, at 2:35am, Bernardo Sulzbach 
> wrote:
>
> >> ALTER TABLE table-name RENAME COLUMN column_field_name TO
> >> new_column_field_name;
> >
> > Are you sure? The documentation does not have anything about this and
> > I get a syntax error using 3.9.2 (a bit outdated, I know).
>
> John's confused.  The ALTER table RENAME command is for renaming tables,
> not columns.
>

?You're right. I'm wrong. Too much "nog" in the eggnog??



>
> Simon.
>
>
-- 
Computer Science is the only discipline in which we view adding a new wing
to a building as being maintenance -- Jim Horning

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] whish list for 2016

[John McKown]

On Thu, Dec 24, 2015 at 4:09 PM, Christian Schmitz <
realbasiclists at monkeybreadsoftware.de> wrote:

> Hi,
>
> better ALTER command would be very welcome.
>
> e.g. RENAME/DELETE column or field.
>

?RENAME exists.?

?ALTER TABLE table-name RENAME COLUMN column_field_name TO
new_column_field_name;

DELETE is normally done as: ALTER TABLE table-name DROP ?COLUMN
column_file_name; and would be a very nice addition. I hadn't noticed that
it is missing. I wonder why. Perhaps Dr. Hipp will comment after the
holidays.



>
> Instead of us writing code to do it, it could be a command where SQLite
> does things right.
>
> Sincerely
> Christian
>
> --
> Read our blog about news on our plugins:
>
> http://www.mbsplugins.de/
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Computer Science is the only discipline in which we view adding a new wing
to a building as being maintenance -- Jim Horning

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] whish list for 2016

[John McKown]

On Thu, Dec 24, 2015 at 10:14 AM, Simon Slavin  wrote:

>
> On 24 Dec 2015, at 3:12pm, John McKown 
> wrote:
>
> > ?I'm curious as to why. Doing so would, most likely, require rewriting
> the
> > entire table. If you want a SELECT * to get the columns in a particular
> > order, just create a VIEW with the columns in the order in which you want
> > them.
>
> I think Gunnar just wants forms such as
>
> ALTER TABLE CREATE COLUMN ...
> ALTER TABLE DROP COLUMN ...
>
> to aid with making small changes to the schema.  These are available in
> most SQL engines but the way SQLite3 is written makes it difficult or
> inefficient to implement them.
>
> I have a database where one table takes up more than 30 Gigabytes of
> space.  While developing the software I needed several times to change a
> column definition and since SQLite lacks these facilities I had to move 30
> Gig of data around every time I did it.  Annoying.  But it's not normally
> that much of a problem for me.
>
> Simon.
>
>
?I did overlook the DROP COLUMN request. I guess I got "shocked" by the OP
wanting something (I think) like: ALTER TABLE ADD COLUMN newcol TEXT AFTER
oldcol. Where "oldcol" is an existing column name which is not the "last"
one. I sometimes have an unusual take on things due to having read about
relational algebra _before_ doing SQL work. So I think of tables as
relationships, as a "set" for "attributes" which have no inherent order.
Read a bit too much by Dr. Codd. Oh, an Joe Celko too, for that matter. And
_no_ actual professional experience. Makes me a bit of a theoretician.

It really would be nice to be able to have a column defined as, say
VARCHAR(20) to be "redefined" as TEXT or VARCHAR(n) (where n>=20) with a
simple ALTER. That would be a simple change to the schema with no data
alteration. If one allowed to change a VARCHAR new length to be _less_ than
the old length, then it would be necessary to verify that all current rows
were still compliant with the new length. In that case, I guess it would be
"best" if the back end were do to the equivalent of a DELETE and ADD on the
now-invalid data, truncating the larger value to its new max size. That
would save some I/O by not rewriting compliant rows. I don't see any way to
avoid I/O if you want to change an INTEGER (1,2,3,4,6 or 8 bytes) to a
FLOAT? (always 8 bytes). You'd need to rewrite every row, either "in place"
(8 byte INTEGER to FLOAT) or with a DELETE / ADD to the "end". I would
really need to examine the internals to see how much I/O this might be.

I'm not aware of any RDMS which allows someone to alter the "type" (e.g.
INTEGER to FLOAT) of an existing column. What I have done, in PostgreSQL,
is something like:

ALTER TABLE table ADD COLUMN new-column FLOAT;
UPDATE table SET new-column=old-column;
ALTER TABLE table DROP COLUMN old-column;
UPDATE table ALTER COLUMN new-column RENAME TO old-name;

But the above would "move" those 30 Gig of data round, just more easily
from the standpoint of the user. What might be interesting in this type of
case would be a "column-oriented DBMS" (
https://en.wikipedia.org/wiki/Column-oriented_DBMS) Depending on how it was
implemented, it could be made to do the above operation easily. But that
would be a _major_ rewrite of SQLite internally. Hum, it could complicate
things, but this might be more easily possible if each column were placed
in a different OS file. When you add a new column, just create a new file
initialized with the same number of rows which contain NULL or the DEFAULT
value. When you drop a column, it would simply delete the row-containing
file. Maybe a VFS could be written to do this. But SQLite would need to be
enhanced to add the ALTER TABLE ... DROP COLUMN operation.



-- 
Computer Science is the only discipline in which we view adding a new wing
to a building as being maintenance -- Jim Horning

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] whish list for 2016

[John McKown]

On Thu, Dec 24, 2015 at 6:49 AM, gunnar  wrote:

> I would like a less limited 'alter table' statement, to be able to drop
> columns and to add columns at a position of my own choice instead of always
> at the end.
>
>
?I'm curious as to why. Doing so would, most likely, require rewriting the
entire table. If you want a SELECT * to get the columns in a particular
order, just create a VIEW with the columns in the order in which you want
them.

The SQL standard, I'm fairly sure, doesn't even specify the "natural order"
of the column returned in a "SELECT *". Of course, they will likely always
be return in the "natural" order. But that depends on how the back end is
programmed. I could see a vendor (as unlikely as it would be) deciding to
return the column in a "SELECT *" in lexicographical order based on the
server's code page, or the table's default code page, or perhaps even in
the client's code page.

But then, I admit that I am "anal" about my SELECT statements; at least
when embedded in a program. IMO, "SELECT *" is a very bad idea in any
programming language. As is making any assumption about the order of rows
returned when an ORDER BY is not specified. "Assuming _nothing_, other than
the worst." is my programming motto. Or the Russian: "Trust, but verify!".
Especially if it is coming in from "meatware" (people), or some other
organization. We have a process at work which consistently blows up because
the end user sends us junk. E.g. the cost is q.97 dollars, instead of 1.97
(q is below 1 and user is typing fast).

===
Hoping you have a nice Christmas, Hanukkah, Fetivus, Kwanza, or at least a
3 day week end.


-- 
Computer Science is the only discipline in which we view adding a new wing
to a building as being maintenance -- Jim Horning

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Problem with accumulating decimal values

[John McKown]

On Fri, Dec 11, 2015 at 8:21 AM, Frank Millman  wrote:

> Hi all
>
> I am having a problem accumulating decimal values.
>
> I am actually using Python, but I can reproduce it in the sqlite3
> interactive terminal.
>
> SQLite version 3.8.6 2014-08-15 11:46:33
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
>
> sqlite> CREATE TABLE fmtemp (acno INT, balance DECIMAL);
> sqlite> INSERT INTO fmtemp VALUES (1, 0);
>
> sqlite> UPDATE fmtemp SET balance = balance + 123.45;
> sqlite> SELECT bal FROM fmtemp;
> 123.45
>
> sqlite> UPDATE fmtemp SET balance = balance + 123.45;
> sqlite> SELECT balance FROM fmtemp;
> 246.9
>
> I repeat this a number of times, and it runs fine, until this happens -
>
> sqlite> UPDATE fmtemp SET balance = balance + 123.45;
> sqlite> SELECT bal FROM fmtemp;
> 5802.15
>
> sqlite> UPDATE fmtemp SET balance = balance + 123.45;
> sqlite> SELECT bal FROM fmtemp;
> 5925.599
>
> sqlite> UPDATE fmtemp SET balance = balance + 123.45;
> sqlite> SELECT bal FROM fmtemp;
> 6049.049
>
> Can anyone explain what is going on, and is there a way to avoid it?
>
> Thanks
>
> Frank Millman
>
>
?This is a common problem. It has been discussed here, and elsewhere, quite
a bit. Basically, you want _decimal_ accuracy from a _binary_ floating
point format. But _decimal_ floating point numbers may not have an exact
_binary_ floating point representation. Perhaps these will be of some help:

http://dba.stackexchange.com/questions/62491/why-does-sqlite-return-incorrect-sum
http://stackoverflow.com/questions/2100490/floating-point-inaccuracy-examples
http://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html (generic
despite being from Oracle Corp.)

The real solution is IEEE 754-2008 decimal floating point implementation.
https://en.wikipedia.org/wiki/Decimal_floating_point
https://software.intel.com/en-us/articles/intel-decimal-floating-point-math-library

The only _hardware_ implementation that I know of for this format is from
IBM, on their Power6 (and after) and z9 (and after) series machines. It is
definitely not (yet) available on an Intel based machine.


-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] "Shipping a New Mindset" - SQLite in Windows 10

[John McKown]

On Tue, Nov 10, 2015 at 3:15 PM, Keith Christian 
wrote:

> A great thing for Windows users.
>
> My only caveat is: Beware corporate pressure.  Stay Free.
>
>
?I agree. I'm not any kind of MS fan. I simply don't trust them _at all_.
They like to "embrace" ... "extend" ... "eliminate". ?

?They tried it with Java, but got pushed back. And basically "retaliated"
by making Java a real PITA under Windows.

Disclaimer: I'm a GNU/Linux bigot & FSF associate member.?

-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] A little light reading

[John McKown]

On Sun, Nov 8, 2015 at 10:36 AM, Simon Slavin  wrote:

>
> On 8 Nov 2015, at 4:11pm, John McKown 
> wrote:
>
> > I'm not a developer. So I guess that it's my ignorance as to why a
> program
> > would be confused by the string value of "null" or any variant thereof.
>
> NULL has a special meaning in SQL and some other database languages.
> Depending on what the programmer wanted at the time it means something like
> "Value unknown" or "No such value" or "Not applicable".  All such programs
> are meant to keep an extremely clear distinction between these two:
>
> surname = "Null"
> surname = NULL
>
> But fast, lazy, untested programs, especially cases where a working setup
> is moved to another language or another platform by someone who doesn't
> understand picky details of the original tend to mess this up.  Much more
> often than you'd think.
>
> Many languages have documentation specifically on the problems of handling
> NULLs. Here's part of SQLite's:
>
> <https://www.sqlite.org/nulls.html>
>

?I am, at least somewhat, aware of the problems with NULL in SQL (and Java
& C in which I "dabble"). I have read a number of Joe Celko's books. And
have formed the opinion that NULLs are anathema from the problems that they
introduce. But, then again, I do understand the need (i.e. a "person"
record which records a birth date and a death date. What if the person is
alive? What if you don't now the birth day?). ?

-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] A little light reading

[John McKown]

I'm not a developer. So I guess that it's my ignorance as to why a program
would be confused by the string value of "null" or any variant thereof. I
do understand looking for a string of length 0. If I were to want a special
name for Some purpose, I'd likely use xyzzy. I thought of plugh as well,
but a search turned up a article by a Mike Plugh.

I also  won't disclose the strange problem I saw for Mr. John Doe and his
wife Jane in some code.
On Nov 8, 2015 04:56, "Simon Slavin"  wrote:

> An article by Christopher Null on how database programmers are idiots.
>
> Come to think of it, if you stare at the above line for long enough you
> won't need the article.  But here it is anyway:
>
> 
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Simple Math Question

[John McKown]

On Fri, Oct 23, 2015 at 7:41 AM, Jean-Christophe Deschamps  wrote:

> AFAICT system 360 machines had BCD in microcode, just like any other basic
> datatype. Z is only the renamed survivor of 360/xx and 370/xxx "boxes".
>

I believe that's true. But only the latest z machines have DFP (Decimal
_Floating_ Point) in them. And the OP to which I was replying was talking
about decimal values & floating point ?(which has been binary in the past).


-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Simple Math Question

[John McKown]

On Fri, Oct 23, 2015 at 3:08 AM,  wrote:

> Computers don't store values in base-10 fractions when they use doubles or
> floats; they use base-2 fractions. This causes that your simple base-10
> fractions can't be stored exactly.
>

?Unless the architecture implements the newest IEEE-754-2008 "decimal
floating point"?
ref: https://en.wikipedia.org/wiki/Decimal_floating_point

?I, personally, only know of one such machine. And it is not really
"popular", except with really _huge_ customers: The IBM z series machine.
It can run 5 different OSes: 4 are IBM proprietary the 5th is Linux. This
machine is not Intel compatible, in any way, shape, or form. And it is
super expensive. Which is why it's not "popular". Running Linux, the
largest has 114 cores and SMT for 228 simultaneous "threads", all in a
single "box". OK, it is a _big_ box .
https://pbs.twimg.com/media/BP8HYj9CQAA17g3.jpg:medium That's about the
size on an entire 19 inch rack (more or less).

Also, as Mr. Long indicated, this entire series of machines have a decimal
data type in the hardware (normally called "packed decimal", which "packs"
2 decimal digits per bytes, except for the last byte which reserves the
last nybble for a sign indicator). There are instructions which translate
from binary to/from packed decimal and packed decimal to/from "printable"
decimal (called zoned decimal for historical reasons due to old style
punched cards).




>
> This is also why those other -not lite- engines have a decimal type,
> processed using much slower integer logic coded in their engines... but
> this doesn't make the current floating point arithmetic broken in any way.
>
> Bert
>
-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Using CTE with INSERT

[John McKown]

On Tue, Oct 13, 2015 at 8:28 AM, Don V Nielsen 
wrote:

> The syntax "WITH p AS (SELECT 1) INSERT INTO t(x) SELECT * FROM p;" throws
> the same exception: "[2015-10-13 08:20:22] [1] [SQLITE_ERROR] SQL error or
> missing database (near ")": syntax error)"
>
>
On my system, RedHat Linux, Fedora 22, x86_64:

SQLite version 3.8.11.1 2015-07-29 20:00:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t(x);
sqlite> WITH p AS (SELECT 1) INSERT INTO t(x) SELECT * FROM p;
sqlite> select * from t;
1
sqlite>
?


-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Order with another sequence

[John McKown]

On Fri, Oct 9, 2015 at 5:08 AM, Cecil Westerhof 
wrote:

> When I use ORDER BY an ? comes after a z. Is it possible to make an ? come
> after a z?
>
> If it is important I am using SQLite 3.8.6 and Python 3.4.1.
>
> --
> Cecil Westerhof
>
>
?I am unsure of how to do this _exactly_ as you want, but I am fairly sure
what you need is the COLLATE phrase in the ORDER BY:

SELECT column_name FROM table
ORDER BY colum_name COLLATE collation_name ASC?

The difficulty is in determining what "collation_name" needs to be. You may
need to create your own. Some pages on the SQLite site which might help:

?http://sqlite.org/lang_select.html
?http://sqlite.org/syntax/ordering-term.html?
https://www.sqlite.org/datatype3.html#collation
https://www.sqlite.org/c3ref/create_collation.html

I don't have the slightest idea if this sort of thing can be used with the
supplied "sqlite3" program. It seems to be designed to be used by C
programs. I've never used SQLite from Python.


-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] How do i submit a bug?

[John McKown]

Might give this a read:
http://www.sqlite.org/src/wiki?name=Bug+Reports
But, basically, this is the place to report your problem. Be as exact as
you can. I.e. Show the _exact_ statement that is not matching the
documentation. Also, give a good link to the doc that you think is in
error. Also, you might want to give a quote of the exact documentation,
_and_ what you think it means. I know that on more than one occasion, I
have read something, tried to explain why it was an error, and it ended up
that I just didn't understand the wording (in a language, the difference
between an "statement separator" and a "statement ending" symbol.Hey, what
can I say? I'm a Texan. Ain't much doc in Texan out there! [grin/].

On Sun, Oct 4, 2015 at 5:22 PM, Jacob  wrote:

> Hi, I have discovered a bug, or at least inconsistancy with documantation,
>
> DO i submit the bug through this mailing list, or is there a ticket system?
>
> Regards
> Jacob
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] get the data anyway no matter what the entry of the second table is existing or not

[John McKown]

On Tue, Sep 22, 2015 at 3:15 PM, ChingChang Hsiao <
ChingChang.Hsiao at overturenetworks.com> wrote:

>
>
> select * from service_table a,service_fib_table b where
> a.service_no=b.service_no;
>
> In this statement, the entry of service_fib_table must be existing to get
> the service_table and service_fib_table data.
>
> How can I write in one statement to get the service_table data no matter
> what the entry of service_fib_table is existing or not.
>
> ChingChang
>

?I am unsure of what you want, but it sounds like you want an OUTER JOIN.
Something like:

SELECT * FROM service_table AS a
LEFT JOIN service_fib_table AS b
WHERE a.service_no = b.service_no
;

In the above, if there is a row in service_table which does not have a
matching row in service_fib_table, then the data in service_table row(s)
will be selected and the columns whose values would have come from
service_fib_table will have a value of NULL.?

-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Handling the whole select query from an index

[John McKown]

On Thu, Sep 17, 2015 at 9:41 AM, R.Smith  wrote:

>
>
> On 2015-09-17 02:22 PM, Richard Hipp wrote:
>
>> On 9/17/15, John McKown  wrote:
>>
>>> Well, this may be a heretical answer.
>>>
>> Yes, it is.
>>
>> ??

??


> (11) This forum. Need help or suggestions with how to optimize your
> internal value-store? Not so easy. Need help optimizing an SQLite DB or
> query? We've all seen the very clever people here always eager to assist.
> It's an amazing and very valuable resource. (This one is also true for
> other community-supported DB systems in general)
>
>
> Cheers,
> Ryan
>
>
?OK, I did really put my foot in it. [grin/]. I do that often enough to not
be upset by it. But it did result in the OP doing a test and realizing that
the problem was _not_ data access, but apparently elsewhere in the code.
I'm a bit of an pro-SQL person myself. Especially when doing things which
require joins & CTEs! I trust the SQL people's code a lot more than I trust
my own (I'm a sysadmin who can program, but not a professional programmer).
But it is not _always_ the "one true way". This would be more for
"unstructured" data. But, then, I need to be reading up on FTS so I could
well be wrong on this point too.

-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Handling the whole select query from an index

[John McKown]

The latest PERL DBI for SQLite that I could see is at
http://www.cpan.org/authors/id/I/IS/ISHIGAKI/DBD-SQLite-1.48.tar.gz . I
took a look at it and it has sqlite3.c (and others) from the 3.8.10
amalgamation in it.

Just "for fun", I copied 3 files (sqlite3.c, sqlite3.h, and sqlite3ext.h)
from my SQLite (64abb65d4df11e5b3bcc4afc8e7c18e907c6080a 2015-08-28
03:48:04 UTC) source. In the DBD-SQLite-1.48 source directory, I then did:
perl Makefile.PL #create the Makefile
make #create the SQLite.so shared library
sudo make install # Install the new DBD for SQLite

The above was on RedHat Fedora 22 x86_64. All the test ran successfully.

If it were me, I'd download the latest SQLite almagamation & the above
mentioned DBD-SQLite. Copy the 3 files I mentioned from the amalgamation
source to the DBD-SQLite-1.48 directory, then re-install DBD-SQLite as I
did. But, of course, this will likely need to go though whatever change
control procedures that Rob's installation has. Hum, I guess that I assumed
that Rob is a sysadmin on this system. So maybe he will really to push an
update request through channels to get DBD-SQLite updated.


On Thu, Sep 17, 2015 at 9:00 AM, Simon Slavin  wrote:

>
> > On 17 Sep 2015, at 2:47pm, Rob Willett 
> wrote:
> >
> > 3. We cannot get WITHOUT ROWID working using Perl DBI. We get the
> following error
> >
> > DBD::SQLite::db prepare failed: malformed database schema (postcode) -
> near ?WITHOUT"
> >
> > This appears to be due to mismatched SQLite version but we are running
> >
> > macpro:postcode rwillett$ sqlite3 --version
> > 3.8.5 2014-08-15 22:37:57 c8ade949d4a2eb3bba4702a4a0e17b405e9b6ace
> >
> > The SQLite web page  on WITHOUT ROWID indicates we need 3.8.2 or higher
> so we?re confused as to what the problem is.
>
> SQLite is not 'installed in your system'.  It is compiled separately into
> each program which uses it.  The version number returned by the
> command-line tool (sqlite3) is the version of SQLite that is compiled into
> the command-line tool.
>
> Your version of Perl will have a different version of SQLite compiled into
> it.  To find out which version that is, I think you can do this:
>
>   $dbh->{sqlite_version};
>
> assuming $dbh is your handle to a SQLite connection.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Handling the whole select query from an index

[John McKown]

On Thu, Sep 17, 2015 at 7:21 AM, Rob Willett 
wrote:

> John,
>
> Thanks for this.
>
> The base OS is Linux on a a 64bit server, the wrapper will be Perl running
> as a web service under Mojolicious. It will eventually be presented as a
> web service.
>
> We could simply load up the entire database into Perl into an Assoc array
> and search on that.
>

?This is probably would I would do, in this particular case.?



>
> Richard,
>
> WITHOUT ROWID was what I was looking for.  Thanks


?I'm going to have to review this option as well. I'm not familiar with
what it accomplishes, performance wise.?



> .
>
> All,
>
> What we?ll do is write a quick Perl script to check the speed of an assoc
> array in Perl vs SQLite. It might be instructive to see the difference.
>

?Yes, it would. Please post your results. I'm really curious about it.?



>
> Rob.
>
>

-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Handling the whole select query from an index

[John McKown]

Well, this may be a heretical answer. Given what you have said, I wouldn't
even try to use SQLite. Well, at least not directly. I would use a "hash
table". SQLite's indexing, if I understand correctly, is a B-Tree. And that
is the _only_ option. What would likely work better is a "hash index". You
might get some more concrete answers if you were to post the OS and
implementation language. Such as, Windows 8.1 using C#. Or Linux 64-bit
using C++. Also, do you an SQL data base? Perhaps something else would be
better, if you're not really doing relational queries. But I don't know
what, given that I don't know your system environment.

On Thu, Sep 17, 2015 at 6:58 AM, Rob Willett 
wrote:

> Hi,
>
> There was a topic on here a few weeks ago which I cannot remember and
> cannot find in the mess that is my e-mail system and after spending the
> last hours search the SQLite archives I still can?t find it so will ask
> here if anybody can remember or help. I can?t even format the question for
> Google to search on :(
>
> The specific question I have is about trying to provide the fastest
> response possible to a select query.  I recall that the e-mail talked about
> using an index to satisfy the query and therefore never having to go out to
> get the rest of the data from the table, so it was a lot quicker. Is there
> anything that I need to do specially to make this happen. e.g. if I put all
> the fields of the table in the index BUT I really only search on the
> primary key
>
> The reason for this I want to look up UK postcodes (Zip codes to our
> American brethren) and get their longitude and latitude. A UK postcode
> identifies a number of houses or commercial buildings. Depending on the
> area it can be just one building (a big one) or if you are in the country
> it can be quite a big area. If you sent a letter just to a postcode with no
> other identifier it probably wouldn?t get delivered, but putting a name on
> it or a building number, there?s a very good chance the post(wo)?man will
> deliver it.
>
> The CSV file looks like this
>
> id,postcode,latitude,longitude
> 1,AB101XG,57.14416516000,-2.11484776800
> 2,AB106RN,57.13787976000,-2.12148668800
> 3,AB107JB,57.12427377000,-2.12718964400
> 4,AB115QN,57.14270109000,-2.09301461900
> 5,AB116UL,57.13754663000,-2.11269588600
> ?.
> Couple of million more lines
>
> The entire database schema looks like this. I know its complicated but
> bear with me :)
>
> CREATE TABLE "postcode" (
>  "postcode" text NOT NULL,
>  "long" TEXT NOT NULL,
>  "lat" TEXT NOT NULL,
> PRIMARY KEY("postcode")
> );
>
> The only query that will ever run will be
>
> select long,lat from postcode where postcode = ??
>
> Note I drop off the id field (column 0 in the CSV file) as its of no
> interest to me. I also store the long and lat as strings as I don?t want
> any number formatting changes at all. Rounding on a GPS number could cause
> the wrong location to be used.
>
> The database will do nothing but return long and lat based on doing a
> postcode lookup. There will never be any updates or changes. If there are,
> the whole database will be regenerated.
>
> I need this to be as fast as possible and if necessary I?ll put it all in
> RAM. The database is currently 120MB so it would easily fit in RAM. As it
> never changes (perhaps 4 times per year), it could stay there.
>
> Is there anything else from the database schema side that would make
> things quicker? e.g. If I created an index with postcode, long, lat in,
> would that be quicker? or if i changed the long, lat to real (though I?m
> reluctant to do so), would that make a lot of difference?
>
> Any suggestions gratefully received and apologies for not being able to
> find it in the archives.
>
> Thanks,
>
> Rob
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] I don't understand how to use NOT EXISTS

[John McKown]

Very good! I'll keep that one.

On Tue, Sep 15, 2015 at 1:26 PM, Igor Tandetnik  wrote:

> On 9/15/2015 2:04 PM, John McKown wrote:
>
>> sqlite> select count(a) from x;
>>
>
> Better still:
>
> select exists (select 1 from x);
>
> The difference is that the version with count() always scans the whole
> table, while the latter stops at the first record - which is good enough if
> you only need to check for existence, and don't actually need the count.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] I don't understand how to use NOT EXISTS

[John McKown]

On Tue, Sep 15, 2015 at 12:43 PM, Simon Slavin  wrote:

>
> On 15 Sep 2015, at 5:01pm, Nicolas J?ger  wrote:
>
> > I also would like to know how can I check if an entry exists,(or not
> > exists), in a table. Like,
> >
> > IF (exists) THEN (doSomething) END
>
> Here's another alternative to add to those in Ryan's excellent post.  With
> your schema
>
> > create table Tags
> > (
> >  id integer primary key,
> >  name text collate nocase unique,
> >  count integer not null
> > );
>
> You can do things like
>
> SELECT total(count) FROM Tags WHERE id=234;
> SELECT total(count) FROM Tags WHERE name='biology';
>
> You will definitely get a reply from this since 'total()' returns 0.0 even
> if there are no rows which satisfy the WHERE clause.
>

Might not work. I'd use count()

sqlite> create table x(a int);
sqlite> select total(a) from x;
0.0
sqlite> select count(a) from x;
0
sqlite> insert into x values(1);
sqlite> insert into x values(-1);
sqlite> select count(a) from x;
2
sqlite> select total(a) from x;
0.0
sqlite>





>
> So you can submit this query and know that there is definitely an answer
> which is definitely a floating point value.  And then in your programming
> language you can do your equivalent of
>
> IF (theanswer) > 0.0 THEN (doSomething) END
>
> Warning: although SQLite also has the function sum() it does not produce
> the same result to total() when no lines satisfy the WHERE clause.  Sorting
> out the possibilities is more complicated.
>
> Simon.
>

-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] sqlite3 file as database

[John McKown]

Like the "resource fork" on the older MacOS systems? I think that OS/2 also
has "extended attributes"(?) which could be set.
https://en.wikipedia.org/wiki/Extended_file_attributes
>

In OS/2 <https://en.wikipedia.org/wiki/OS/2> version 1.2 and later, the High
Performance File System
<https://en.wikipedia.org/wiki/High_Performance_File_System> was designed
with extended attributes in mind, but support for them was also
retro-fitted on the FAT
<https://en.wikipedia.org/wiki/File_Allocation_Table> filesystem of DOS.
For compatibility with other operating systems using a FAT partition, OS/2
attributes are stored inside a single file "EA DATA. SF" located in the
root directory. This file is normally inaccessible when an operating system
supporting extended attributes manages the disk, but can be freely
manipulated under, for example, DOS. Files and directories having extended
attributes use one or more clusters
<https://en.wikipedia.org/wiki/Cluster_(file_system)> inside this file. The
logical cluster number of the first used cluster is stored inside the
owning file's or directory's directory entry
<https://en.wikipedia.org/wiki/FAT_extended_file_attributes>. These two
bytes are used for other purposes on the FAT32 filesystem, and hence OS/2
extended attributes cannot be stored on this filesystem.

Parts of OS/2 version 2.0 and later such as the Workplace Shell
<https://en.wikipedia.org/wiki/Workplace_Shell> uses several standardized
extended attributes (also called *EAs*) for purposes like identifying the
filetype, comments, computer icons
<https://en.wikipedia.org/wiki/Computer_icon> and keywords about the file.
Programs written in the interpreted language Rexx
<https://en.wikipedia.org/wiki/Rexx> store an already parsed
<https://en.wikipedia.org/wiki/Parse> version of the code as an extended
attribute, to allow faster execution.


On Mon, Sep 14, 2015 at 2:02 PM, Tim Streater  wrote:

> On 14 Sep 2015 at 19:29, Warren Young  wrote:
>
> > On Sep 14, 2015, at 8:38 AM, Stephen Chrzanowski 
> wrote:
> >>
> >> There are many extensions of the same .. err..
> >> name(?)...value(?)..structure(?) that are completely different things.
> >
> > It?s fairly bad in the electronics engineering world, where it seems like
> > half the tools use *.sch for schematics and *.brd/pcb for printed circuit
> > board layouts, but none of the tools agree on the format of the actual
> file
> > data.  If you have two such apps installed, you have to make a hard
> choice
> > about which app becomes the default to open such files, and occasionally
> have
> > to fix it when updating the other app, as it takes over the extensions
> again.
> >
> > This widespread unwillingness to get beyond the 8.3 limits, particularly
> on
> > Windows, is annoying.  We haven?t had to worry about compatibility with
> > 3-character file extensions since Windows NT 3.5 and Windows 95, two
> decades
> > ago now.
>
> Of course in a sensible world, OS providers would all have implemented a
> common metadata API, and no one would need or use extensions.
>
> --
> Cheers  --  Tim
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>


-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Feedback request: JSON support in SQLite

[John McKown]

On Fri, Sep 11, 2015 at 11:31 AM, Richard Hipp  wrote:

> On 9/11/15, Petite Abeille  wrote:
> >  serialization of the week
>
> The json.org website has been up since 2002.  JSON itself predates
> that.  It is roughly the same age as SQLite itself and is older than
> SQLite3.  I'm thinking that maybe JSON is not just a passing fad.
> Could be wrong though.
> --
> D. Richard Hipp
> drh at sqlite.org
>
>
?PostgreSQL has had JSON for a couple of releases now. I am amazed at the
number of messages on that forum about "how do I use JSON in the WHERE
clause of a SELECT?" and other such where people want to not just store
JSON encoded data, but do SELECTs and UPDATEs on _parts_ of a JSON encoded
column. They're like polygamists in that they want their Kate and Edith
too. [grin/] I.e. relational queries against a non-relational field. ?


?Oh, and the latest DB/2 UDB from IBM is getting JSON columns as well. I
don't know what their implementation does with it.?


-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] BEGINNER - Transactions in shell script

[John McKown]

On Fri, Sep 11, 2015 at 6:51 AM, Stephen Chrzanowski 
wrote:

> You'd be surprised by what is out there trying to get into your system.
>
> I had port 22 open on my home router to go to a Linux machine so I could
> SSH into my home network from anywhere in the world, even though I rarely
> ever leave the 519 area code.  One day I went to look at my messages log
> file and noted numerous brute force attempts to get into my machine.
> Fortunately, the machine is setup so that you can't SSH in as root, and the
> single login name that has any kind of access root capable access is
> intentionally camel cased to thwart name dictionary attacks.  The attacks
> were automated at their end, obviously, but if you have a machine exposed,
> someone is going to have software that will do anything and everything to
> gain access through whatever weakest link you have.
>

?I do the same! With two modifications. I don't use port 22. I use an
"ephemeral" port (above 1024)? which most attack software doesn't even try.
This is easy to accomplish either by making SSH listen on another port, or
by having the WAN connected router redirect the port from "" on the
"outside" to port 22 on the "inside". Also, I _only_ use a digital
certificate for SSH. I don't allow use of passwords at all.


-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Does PRIMARY KEY imply NOT NULL?

[John McKown]

On Wed, Sep 9, 2015 at 3:56 PM, Baruch Burstein 
wrote:

> Question in the subject
>
> --
>
>
?Answered on the SQLite web site: http://sqlite.org/lang_createtable.html


According to the SQL standard, PRIMARY KEY should always imply NOT NULL.
Unfortunately, due to a bug in some early versions, this is not the case in
SQLite. Unless the column is an INTEGER PRIMARY KEY
<http://sqlite.org/lang_createtable.html#rowid> or the table is a WITHOUT
ROWID <http://sqlite.org/withoutrowid.html> table or the column is declared
NOT NULL, SQLite allows NULL values in a PRIMARY KEY column. SQLite could
be fixed to conform to the standard, but doing so might break legacy
applications. Hence, it has been decided to merely document the fact that
SQLite allowing NULLs in most PRIMARY KEY columns.
?



-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] WHERE clause not working in combination with random()

[John McKown]

OK, I am truly grateful for what I have learned in this thread! And I have
composed an new, personal, rule: don't use a function in _anything_ other
than in the column list portion of a SELECT statement. If necessary, this
means I will be using a CREATE TEMPORARY TABLE results type construct when
I need to ORDER BY or WHERE using a "function". Such as:

BEGIN TRANSACTION;
DROP TABLE __results;
CREATE TEMPORARY TABLE __results AS SELECT ...columns..., ...functions...
FROM sometable WHERE ...only have column names...;
SELECT * FROM __results ORDER BY  WHERE ..columns containing results of
functions... ;
COMMIT TRANSACTION;

Does anybody see where this will fail (other than, perhaps disk space or an
existing real table called __results already existing)? I agree is will
likely not perform as well as it could. But, personally, I will embrace
lower performance for correctness (I.e. getting what I really want).

On Fri, Aug 28, 2015 at 4:20 AM, Domingo Alvarez Duarte <
sqlite-mail at dev.dadbiz.es> wrote:

> Now that we have solved the expression column reevaluation problem on the
> "ORDER BY" clause what about the same problem on the "WHERE" clause ?
>
> CREATE TABLE myTable (a INTEGER);
> INSERT INTO myTable VALUES (1),(2),(3),(4),(5);
> CREATE VIEW myView AS SELECT a,random()%100 AS rr FROM myTable;
> SELECT a, rr FROM myView WHERE rr < 30 ORDER BY rr;
>
> Some outputs of the above through sqlite3 "sqlite3 <
> test-where-random.sql":
>
>
> =
>
> 2|-86
> 4|-60
> 1|59
> 
>
> 5|5
> 3|66
>
> 
>
> 5|-83
> 4|30
> 1|64
> 
>
> Please remember that the "random" function here is only a detail the real
> problem is the "column expression" been reevaluated more than once.
>
> Cheers !
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] order by not working in combination with random()

[John McKown]

In the case: SELECT random() AS rr FROM sometable ORDER BY rr, the SQLite
result is anti-intuitive. In my ignorance, I thought that ORDER BY sorted
the results of the SELECT. It sure _looks_ that way from my view point. I
cannot access the ANSI standard because I'm too cheap to buy them. So I
went to Wikipedia: https://en.wikipedia.org/wiki/Order_by (not
authoritative, I know) and got the following:

An *ORDER BY* clause in SQL <https://en.wikipedia.org/wiki/SQL> specifies
that a SQL SELECT <https://en.wikipedia.org/wiki/Select_(SQL)> statement
returns a result set <https://en.wikipedia.org/wiki/Result_set> with the
rows being sorted by the values of one or more columns. The sort criteria
do not have to be included in the result set. The sort criteria can be
expressions, including ? but not limited to ? column names, user-defined
functions <https://en.wikipedia.org/wiki/User_defined_function>, arithmetic
operations, or CASE expressions. The expressions are evaluated and the
results are used for the sorting, i.e. the values stored in the column or
the results of the function call.

So, applying this to our subject, "rr" is _NOT_ the name a column in the
table. It is an alias to a "user-defined function" (user-defined in this
case meaning not defined in the SQL standard itself). Therefore, based on
the last sentence of the above quote, the random() is evaluated AT THE TIME
THE ORDER BY IS EXECUTED. Therefore, although not what we might want and
expect, SQLite is conformant. I think that this logic also applies in the
case of a VIEW or CTE. Which basically means that, to get what is wanted,
we need to first put our data into another, perhaps TEMPORARY, table. And
then do the SELECT ... ORDER BY using that table with column names, not
functions.

This is only my thought on the situation. I don't claim that it is correct.
But it does, sort of [pun intended], explain the ORDER BY results which
were observed.

-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] order by not working in combination with random()

[John McKown]

On Wed, Aug 26, 2015 at 9:52 AM, Clemens Ladisch  wrote:

> Domingo Alvarez Duarte wrote:
> > This assumption is a bit naive :
> >
> >> In SQLite, this cannot happen because queries execute infinitely fast
> >> (as far as the built-in date/time functions are concerned).
>
> Nonetheless it's true.
>

?Isn't that within a given transaction (which a single SELECT always is?
within because SQLite will start & end one if one is not active at the time
of the SELECT)? I.e.



>
> <http://www.sqlite.org/releaselog/3_8_1.html> says:
> | the current time (ex: julianday('now')) is always the same for multiple
> | function invocations within the same sqlite3_step() call.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[John McKown]

On Fri, Aug 21, 2015 at 1:02 PM, sqlite-mail 
wrote:

> Thank you for your attention !
>
> I'm pointing this here because postgresql do manage this case properly !
>

?And is significantly larger and harder to install. PostgreSQL is not
"lite"! I know. I use it and love it. ?



>
> And I'm creating a tool to prototype database applications and I'm using
> sqlite as the primary database, when we are prototyping things can change
> drastically at any point and if we already have a lot of views/triggers
> it's
> a pain in the ass to fix it (postgresql does it fine).
>

?Which is why I keep all my definitions in a file. I edit them there, then
use the ".read" in sqlite3 to bring them all in. Granted this doesn't help
if you have a lot of data in the data base. In that case, I don't rename. I
create the new table and populate it with the data in the old table via a
INSERT INTO ... SELECT ... ?



>
> Also that we got to this point would be nice if sqlite implemented a basic
> SQL ANSI data dictioanry,  I mean sqlite already provide most of the info
> for a basic data dictionary but in a non compliant way "PRAGMAS", although
> is
> better than nothing we can not use that info on sql statements like
> views/joins.
>

?OK, implement one. This would be an _excellent_ "add on" product. Write a
program which reads the data base schema. Using that internal schema,
determine how to update all the dependencies. To implement, send all the
required commands to the sqlite3 API. ?

-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Getting row number in a sorted list.

[John McKown]

On Wed, Aug 19, 2015 at 7:40 AM, Simon Slavin  wrote:

>
> On 19 Aug 2015, at 1:16pm, Anthrathodiyil, Sabeel (S.) <
> santhrat at visteon.com> wrote:
>
> > Example, for below entries in NameTable
> >
> > Name
> >
> > 1.
> ??
>  PTN
> >
> > 2.   ABCD
> >
> > 3.   CDE
> >
> > 4.   PQRS
> >
> > 5.   AXN
> >
> >
> > I want to get the row number of the first name that starts with 'P' in
> the sorted list. Here it's going to be row number 4 (PQRS)in the sorted
> list.
> >
> > I need the row number, not the entry itself for my use case. How do I
> form a query to achieve this?
>
> SELECT rowid FROM NameTable
> WHERE name BETWEEN 'P' AND 'P'
> ORDER BY name
> LIMIT 1
>
> This will execute faster if you have an index on 'name' in NameTable.
>
> [Yes I know 'P' is lazy.  Until you find someone with that name
> (presumably Polish) with that name bite me.]
>
> Simon.
>

?Interesting. What happens if somebody did: CREATE TABLE NameTable (Name
text PRIMARY KEY) WITHOUT ROWID ??

?Also, what about the following:

sqlite> create table NameTable (Name TEXT);
sqlite> insert into NameTable(Name) Values('ABCD');
sqlite> insert into NameTable(Name) Values('CDE');
sqlite> insert into NameTable(Name) Values('PQRS');
sqlite> insert into NameTable(Name) Values('AXN');
sqlite> SELECT rowid, Name from NameTable;
1|ABCD
2|CDE
3|PQRS
4|AXN
sqlite> select rowid from NameTable where Name between 'P' and 'PZZZ'
limit 1;
3
sqlite> insert into NameTable(Name) Values('AXN2');
sqlite> select rowid, Name from NameTable;
1|ABCD
2|CDE
3|PQRS
4|AXN
5|AXN2
sqlite> insert into NameTable(Name) Values('PQRS2');
sqlite> select rowid, Name from NameTable;
1|ABCD
2|CDE
3|PQRS
4|AXN
5|AXN2
6|PQRS2
sqlite> delete NameTable where Name='PQRS';
Error: near "NameTable": syntax error
sqlite> delete from NameTable where Name='PQRS';
sqlite> select rowid, Name from NameTable;
1|ABCD
2|CDE
4|AXN
5|AXN2
6|PQRS2
sqlite> select rowid from NameTable where Name between 'P' and 'PZZZ'
limit 1;
6


Hum, that probably isn't what the OP wanted. I would guess in this latter
table, he would want "5" because that is the _relative_ row number you
would see by counting if you did a simple "SELECT Name FROM NameTable;"

The basic problem is that the question is "improper". In general, there is
no "relative row number" for the rows listed by a "SELECT" command. Oh,
there is when you look at it. But the order of the rows returned where then
is no ORDER BY clause is not guaranteed. And, even then, the order of
individual rows which have the same ordering with the ORDER BY are not
guaranteed. What I mean is, if you do SELECT A,B,C FROM TABLE ORDER BY A, B
; and there are two or more rows with equal A & B values, then the order of
the C values is not guaranteed.

So, what to do? Well, it would be possible to do something like:

sqlite> drop table if exists row_order;
sqlite> create temporary table row_order AS select * from NameTable;
sqlite> select rowid from row_order where Name between 'P' and 'PZZZ'
limit 1;
5
sqlite> select rowid, Name from row_order;
1|ABCD
2|CDE
3|AXN
4|AXN2
5|PQRS2
sqlite> select rowid, Name from NameTable;
1|ABCD
2|CDE
4|AXN
5|AXN2
6|PQRS2
sqlite>

This may answer the OP's question. But it only works for SQLite. And I am
not sure that it is guaranteed to work on all past and future versions of
SQLite. It depends on the non-standard ROWID facility in SQLite. Perhaps
Dr. Hipp can address this last issue.
?

-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] order by not working in combination with random()

[John McKown]

On Aug 17, 2015 21:53, "Yuriy M. Kaminskiy"  wrote:
>
> ... and then, at some wonderful moment, sqlite devs will implement query
> flattening for CTE (like they did for subquery above), and you'll be in
> square one.
>
> (Or, maybe, they will finally implement "common subexpression
> elimination", and original query will suddenly work again).
>
> It is not good idea to depend on undocumented behavior.

A very good point! Thanks for reminding me.

>
>

[sqlite] Enhance the SELECT statement?

[John McKown]

On Mon, Aug 17, 2015 at 1:15 PM, Petite Abeille 
wrote:

>
> > On Aug 17, 2015, at 8:08 PM, R.Smith  wrote:
> >
> > CORRECTION: It seems one of the two options I've mentioned earlier,
> namely the CREATE TABLE AS SELECT... does not actually work on the back of
> a WITH clause. The other option still do, but this request has more appeal
> now.
>
> Hmmm?!?
>
> create table foo as
>
> with
> DataSet( position )
> as
> (
>   select  1 as position
>   union all
>   select  DataSet.position + 1 as position
>   fromDataSet
>   where   DataSet.position < 10
> )
> select  *
> fromDataSet;
>
>
?WONDERFUL! That just never entered my poor head. I guess that I was too
used to the way I do it in PostgreSQL. The way things are going for me
today, I feel that I'm in the lead role of "Moron Monday".?


-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Enhance the SELECT statement?

[John McKown]

On Mon, Aug 17, 2015 at 11:41 AM, sqlite-mail 
wrote:

> Hello !
>
> The query you mention is not the same as:
>
> INSERT INTO table_of_intergers SELECT seqno FROM generate ORDER BY seqno;
>

?Thanks for that. When I read the SQLite documentation, I somehow assumed
that I _had_ to do a SELECT startement, not an INSERT INTO. I finally had
some time to test and this works. ?




>
> Cheers !
> >  Mon Aug 17 2015 17:44:58 CEST from "John McKown"
> >  Subject: [sqlite] Enhance the SELECT
> >statement?
> >
> >  I use both SQLite3 and PostgreSQL. One thing that would be really useful
> > for me in SQLite3 which is in PostgreSQL is the INTO phrase. The
> PostgreSQL
> > documentation this is here:
> > http://www.postgresql.org/docs/9.4/interactive/sql-selectinto.html
> >
> > The main reason that I could use this would be in conjunction with the
> WITH
> > clause. A minor example would be:
> >
> > WITH RECURSIVE generate AS
> > ( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate WHERE
> > seqno > SELECT seqno FROM generate INTO table_of_intergers ORDER BY seqno;
> >
> > This could also solve the "problem" that another person had, wanting a
> > sorted sequence of random numbers:
> >
> > WITH RECURSIVE generate AS
> > ( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate WHERE
> > seqno<),
> > random_number_generator
> > ( SELECT RANDOM() AS random_number FROM generate)
> > SELECT random_number FROM random_number_generator ORDER BY random_number;
> >
> > --
> >
> > Schrodinger's backup: The condition of any backup is unknown until a
> > restore is attempted.
> >
> > Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will
> be.
> >
> > He's about as useful as a wax frying pan.
> >
> > 10 to the 12th power microphones = 1 Megaphone
> >
> > Maranatha! <><
> > John McKown
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> >
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Enhance the SELECT statement?

[John McKown]

I use both SQLite3 and PostgreSQL. One thing that would be really useful
for me in SQLite3 which is in PostgreSQL is the INTO phrase. The PostgreSQL
documentation this is here:
http://www.postgresql.org/docs/9.4/interactive/sql-selectinto.html

The main reason that I could use this would be in conjunction with the WITH
clause. A minor example would be:

WITH RECURSIVE generate AS
   ( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate WHERE
seqno),
   random_number_generator
   ( SELECT RANDOM() AS random_number FROM generate)
SELECT random_number FROM random_number_generator ORDER BY random_number;

-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] I don't know how to cast ‘const unsigned char*’ to a 'const std::string'

[John McKown]

On Tue, Aug 11, 2015 at 9:06 PM, Nicolas J?ger 
wrote:

> Hi,
> I have some basic problem, but I didn't found the solution so far...
> maybe some people are using C++ and already deal with that problem :
>
> conversion from ?const unsigned char*? to non-scalar type ?std::string
>
> I got the `const unsigned char*` from `sqlite3_column_text()` and I
> want to pass the result as a `std::string` parameter to a C++ function
>
> regards,
> Nicolas J.
>

?Hi! Idiot me again after a bit of sleep. Have you considered casting away
the "unsigned"-ness?

const char *someVar = (const char *) sqlite3_column_text(...)?

?;
std::string some?String(someVar);

-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Lawyers, encryption, and RC4

[John McKown]

Just speaking as a cantankerous old cynic, I wonder how much of this is
being driven by a real concern and how much is being driven by someone with
an agenda to use a "more favored" product. I've been pushed even further in
the latter direction, in general, when I learned from a person who was
evaluating a new server that they had decided against it because "It's not
made by MicroSoft." Not that it was too expensive. Or didn't do the job.
Just that it was not by the "most favored" vendor.


-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] I don't know how to cast ‘const unsigned char*’ to a 'const std::string'

[John McKown]

Perhaps this will help:
http://stackoverflow.com/questions/1195675/convert-a-char-to-stdstring

IIRC, something ike

std::string return_string(pointer_to_const_unsigned_char_var);

return_string is an std::string initialized to the value pointed to my the
variable in the initializer. If you want the return_string to have more
space, say to append to it, then you can do:

std::string return_string(pointer_to_const_unsigned_char_var,) //
replace  with the size of the area to be allocated.

ref: http://en.cppreference.com/w/cpp/string/basic_string/basic_string for
the above, in the examples section.

FWIW, I am not a C++ programmer. I have read up a bit on it, but found the
above with a simple Google search.
https://www.google.com/webhp?ie=UTF-8#q=C%2B%2B+std%3A%3Astring+cast



On Tue, Aug 11, 2015 at 9:07 PM, Nicolas J?ger 
wrote:

> Hi,
> I have some basic problem, but I didn't found the solution so far...
> maybe some people are using C++ and already deal with that problem :
>
> conversion from ?const unsigned char*? to non-scalar type ?std::string
>
> I got the `const unsigned char*` from `sqlite3_column_text()` and I
> want to pass the result as a `std::string` parameter to a C++ function
>
> regards,
> Nicolas J.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Read data from database slow the first time but then very fast thereafter. How can I improve the speed for the first time read?

[John McKown]

If the OP shows us the data base schema, and the select, then perhaps its
performance could be enhanced by changing something. One thing which I
sometimes do is "denormalize" a table by splitting it into two, or more,
tables which are related via a shared UUID value. I put the often used
columns in one and the lesser used columns in the other. That way, when I
do a SELECT which only needs the columns in the first, I don't need to read
the other table at all. In my case, this table was rarely updated. But
updating it (changing column values and adding new rows) is a bit more
difficult. Too bad that SQLite does not implement updatable views.

On Thu, Aug 6, 2015 at 10:08 AM, Stephan Beal  wrote:

> On Thu, Aug 6, 2015 at 4:54 PM, Wade, William  wrote:
>
> > I'm an SQLite novice, so if I'm wrong about this, I hope someone will
> > correct me.
> >
> > The first query will be slower than later queries for the reason Simon
> > mentioned.
>
>
> Pedantically speaking, "will" is not quite right. That will _normally_
> apply, but depends on the whims (and memory pressure) of the OS.
>
> When this question/problem has come up before, some people have suggested
> "coercing" the raw db file into the filesystem cache with something like:
>
>   cat mydb > /dev/null
>
> (or whatever the equivalent is on Windows)
>
> which simply causes mydb to be read into the filesystem cache,
> independently of sqlite (again, subject to the whims of the OS).
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
> those who insist on a perfect world, freedom will have to do." -- Bigby
> Wolf
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Read data from database slow the first time but then very fast thereafter. How can I improve the speed for the first time read?

[John McKown]

On Tue, Aug 4, 2015 at 10:45 AM, Simon Slavin  wrote:

>
> On 3 Aug 2015, at 1:58pm, Linquan Bai  wrote:
>
> > I am trying to read large data from the database about 1 million records.
> > It takes around 1min for the first time read. But if I do the same
> process
> > thereafter, the time is significantly reduced to 3 seconds. How can I
> get a
> > fast speed for the first time read?
>
> You can't.  Some part of your computer has pulled that data into cache,
> and it's still in the cache when you run the process again, so it the data
> doesn't need to be fetched from disk again.
>

That sounds correct to me. I don't know which OS the OP is running (likely
Windows, which I don't know well). But I wonder if there is some way, like
running a program before he runs his application which can tell the OS to
"preload" the file into RAM cache. On Linux, I might do something like: "dd
if=/path/to/sqlite-database.sqlite3 of=/dev/null bs=1024 count=100" which
would, as a side effect, pull the first 100KiB of the file into RAM.

Of course, there is an expensive way: ?Put your SQLite data base on an PCIe
SATA III (6Gib/s) SSD drive. Lowest cost for a reasonable sized one which I
saw on Amazon was USD 250 for 240GiB.
http://smile.amazon.com/Kingston-Digital-Predator-SHPM2280P2H-240G/dp/B00V01C4RK
This is for me as a Amazon Prime member.




>
> If you tell us how big an average row is (how many bytes of data) then we
> can tell you whether 1 million rows in 1 minute is a reasonable time.  But
> my guess is that that's more or less the time you'd expect.
>
> Simon.
>


-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Thanks SQLite

[John McKown]

On Fri, Jul 31, 2015 at 8:40 AM, Gabor Grothendieck  wrote:

> >
> Here is a self contained example that can be fed into the sqlite3 command
> line tool.  It uses WITH to factor out the subquery; however, the annoying
> part about it is that even though we have factored out the subquery we
> still have to include a simplified version of the subquery, viz.
>   (select avgSalary from A)
> It would have been nice if we could just replace (select avgSalary from A)
> with (A.avgSalary) or even (A) but those substitutions do not work:
>
> -- based on query at: https://en.wikipedia.org/wiki/Correlated_subquery
> ??
> create table Emp (emp text, salary real, dept text);
> insert into Emp values ('a', 1, 'A');
> insert into Emp values ('b', 2, 'A');
> insert into Emp values ('c', 3, 'A');
> insert into Emp values ('d', 1, 'B');
> insert into Emp values ('e', 2, 'B');
> insert into Emp values ('f', 3, 'B');
> WITH A as
> ??
>  (SELECT AVG(salary) AS avgSalary FROM Emp WHERE dept = e1.dept)
> ??
>  SELECT *
>  FROM Emp AS e1
>  WHERE salary > (select avgSalary from A);
>
> Here is a sample run assuming the above has been placed into a file named
> test.sqlite.
>
> C:\> sqlite3 < test.sqlite
> c|3.0|A
> f|3.0|B
>
> (I am using sqlite 3.8.3 on Windows 8.1.)


?Using SQLite 3.8.11 on Linux (RedHat Fedora 22), I used the following. I
don't know that it is "better", but doesn't use sub-selects, if that is of
any use.

sqlite> WITH a AS
   ...> (SELECT dept, AVG(salary) AS avgSalary FROM Emp GROUP BY dept)
   ...> select Emp.emp, salary, Emp.dept
   ...> FROM Emp JOIN a ON Emp.dept = a.dept AND salary > avgSalary;
c|3.0|A
f|3.0|B

I did an EXPLAIN QUERY PLAN on both of those. But I don't understand, at
all, what I am being told. I have posted the output for each, in the hopes
that somebody will tell me which is better.

>From the original, using the sub-select

0|0|0|SCAN TABLE Emp AS e1
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
1|0|0|SCAN TABLE Emp

>From my alternate, using the JOIN with no sub-select

1|0|0|SCAN TABLE Emp
1|0|0|USE TEMP B-TREE FOR GROUP BY
0|0|0|SCAN TABLE Emp
0|1|1|SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (dept=?)

?
-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Thanks SQLite

[John McKown]

On Fri, Jul 31, 2015 at 7:40 AM, Simon Slavin  wrote:

>
> On 31 Jul 2015, at 1:26pm, John McKown 
> wrote:
>
> > WITH summary AS
> >   ( SELECT var, count(var) as total FROM table GROUP BY key )
> > SELECT a.var, 100*a.var/b.total as percentage FROM table AS a
> > INNER JOIN summary AS b ON a.key=b.key
> > ORDER BY key
>
> Thank you at last for an example of non-recursive CTE being useful in the
> real world.
>
> I am noting an overlap with the things SQLite users have been using
> sub-SELECTs for.
>

?Dang. That should be GROUP BY var, not GROUP BY key. Knew I'd mess up
something.?



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



-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Thanks SQLite

[John McKown]

I have also used CTE so that I could generate a "report" just using SQL.
Something like (simplified):

WITH summary AS
   ( SELECT var, count(var) as total FROM table GROUP BY key )
SELECT a.var, 100*a.var/b.total as percentage FROM table AS a
INNER JOIN summary AS b ON a.key=b.key
ORDER BY key
;

?Yes, I could do the equivalent in the program. But that does make it more
complicated. Which makes it easier to make a mistake. And more difficult to
modify. Being a bit weird, I have even encapsulated that in a BASH shell
script which "transforms" a CSV file (simplified):

#!/bin/sh
if [ $# -le 3 ]; then
   echo "At least 3 arguments required." >2
   echo "Argument 1 is the name of the input CSV." >2
   echo "Argument 2 is the name of the output CSV. " >2
   echo "Argument 3 is the name of the \"key\" field." >2
   echo "All arguments after than are the names of other variable to
include in the output." >2
   echo "Note that variables in the input CSV which are not specifically
named are excluded." >2
   exit 1;
fi
input="$1" #name of input CSV file
shift
output="$2" #name of output CSV file
shift
key="$3" #name of the key field
shift
varlist="$@" #names of other variables to output, in order
# Put in some percentages.
sqlite3 <<
John McKown

[sqlite] CSV excel import

[John McKown]

On Thu, Jul 30, 2015 at 3:40 PM, Sylvain Pointeau <
sylvain.pointeau at gmail.com> wrote:

>
> Well ... I am glad that you are right.
>
> When I saw your message, I wondered why it didn't work for me.
>
> I have taken back my samples:
>
> $ cat test.csv
> A,B,C
> T,TI,TIT
> "A,B",C,D
> "1st line
> 2nd line",E,F
>
> sqlite> .mode csv
> sqlite> .import test.csv T
> sqlite> select * from T;
> T,TI,TIT
> "A,B",C,D
> "1st line
> 2nd line",E,F
>
> I think this is where my mistake comes from, and when I did browse on
> internet, I saw some replies showing how to do an update to remove the
> quotes...
>
> now I should have done:
>
> sqlite> .mode csv
> sqlite> .import test.csv T
> sqlite> .mode list
> sqlite> select * from T;
> T,TI,TIT
> A,B,C,D
> 1st line
> 2nd line,E,F
> 0003,002,01
>
> so I was obviously wrong, sqlite import the csv very well, and I am glad to
> have been wrong!
>
> apologies for my mistake.
>

?Well, I made the identical mistake myself on my first try. But then I
noticed in the manual that the ".mode csv" also affected how the output
from a SELECT was displayed. That's when I tried the ".mode lines" just to
see what happened. The light then came on. ?



>
> Best regards,
> Sylvain
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] CSV excel import

[John McKown]

On Thu, Jul 30, 2015 at 12:58 PM, Sylvain Pointeau <
sylvain.pointeau at gmail.com> wrote:

> I understood from the mailing list, that CSV is not a defined format, then
> let's propose another format, well defined, the Excel one (which is in my
> experience a format to is good every time I had to exchange CSV files).
>
> Then why don't you propose an import of CSV from Excel (or similar)?
> csv(excel)
>
> is it possible? in a lot of cases, I cannot use sqlite (executable) because
> of the lack of a good CSV import. It would really great if this could be
> addressed.
>
> Best regards,
> Sylvain
>

?I am replying to your original message rather that later ones because I'm
curious about the CSV file which is giving you a problem. Using the sqlite3
command on Linux Fedora 22 (64 bit), I get the following (transcript):

$sqlite3
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table test(name1 text, name2 text, name3 text);
sqlite> .mode csv
sqlite> .import ./test.csv test
sqlite> .mode lines
sqlite> select * from test;
name1 = name1
name2 = name2
name3 = name3

name1 = line1a
name2 = line1b
name3 = line1c

name1 = line2"a
name2 = line2b
name3 = 'line2b'
sqlite> .quit
joarmc at mckown5 2015-07-30T14:43:21 ~/junk
$cat test.csv
name1,name2,name3
"line1a",line1b,line1c
"line2""a",line2b,'line2b'
joarmc at mckown5 2015-07-30T14:43:25 ~/junk
?



-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] CSV excel import

[John McKown]

On Thu, Jul 30, 2015 at 2:13 PM, Sylvain Pointeau <
sylvain.pointeau at gmail.com> wrote:

> >
> >
> My answer saying that I would like to have it in sqlite implied a correct
> csv import. In a previous email, I stated that it would be unreasonable to
> ask sqlite to have an xml parser etc.
> I just need a import of csv that is working well, not more.


?My apologies for my misunderstanding. If the CSV import is broken (in that
it does not properly import data which is properly formatted according RCF
4180, https://www.ietf.org/rfc/rfc4180.txt), I would?

?consider that a "bug fix" request.?


-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] CSV excel import

[John McKown]

On Thu, Jul 30, 2015 at 1:48 PM, Sylvain Pointeau <
sylvain.pointeau at gmail.com> wrote:

> On Thu, Jul 30, 2015 at 8:44 PM, Peter Aronson  wrote:
>
> > Actually there exists an open source tool that convert Excel data into
> > SQLite tables -- the ogr2ogr command line tool of OSGeo's GDAL library (
> > http://www.gdal.org/).  You do need a version of GDAL built with the
> > SQLite and XLSX and/or XLS drivers.  Actually, if you don't mind adding
> > SpatiaLite into the mix, you can make your spreadsheets show up a virtual
> > tables using the VirtualOGR module.
> > Peter
>
>
> really cool, but I would like to have a solution directly in the sqlite3
> executable, so it would be available on my mac and on my windows at work as
> well as everywhere where sqlite can be installed.
>
>
?I see and understand your desire. But I, personally, don't like the idea.
I really don't want Dr. Hipp and the other developers to be using up their
time trying to put in something that is MS specific. And then trying to
keep it up to date with future, incompatible version of the Excel file
format. I don't use Excel.?

?I normally use LibreOffice. And sometimes even Gnumeric.

?What might be of some, more generic, help would be if the SQLite
executable could do an IMPORT operation from an ODBC source. This could
address your problem because Excel, at least on Windows, supports being
used as an ODBC target. I don't know about the Mac. The plus of this would
be that would open up a standard interface to SQLite which could use many
other sources such as Oracle, PostgreSQL, MariaDB (MySQL), and anything
else which implements an ODBC source interface.?

-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[John McKown]

On Mon, Jul 27, 2015 at 9:21 AM, rotaiv  wrote:

> Problem solved.  I updated the version on the server and it completes in 16
> seconds.
>

?WOW! That is an amazing difference. Makes me glad that I'm OCD about
staying relatively current. And please ignore my previous post. I somehow
managed to not register that you would doing a LEFT join, an equi-join.?



>
> To answer your question, I am using the find command to created a delimited
> text file.  I copy that file to my home PC then importing that file into a
> sqlite database table.  I use the same process to create a text file on my
> home PC then import that as well.   The tables are very simple.  Just
> filename, type, date, time and epoch seconds (easy comparison).
>
> --

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[John McKown]

On Mon, Jul 27, 2015 at 8:42 AM, Richard Hipp  wrote:

> On 7/27/15, rotaiv  wrote:
> >
> > My question is ...  What is about my machine at home
> > that allows Sqlite to query 2 million files so quickly?
> >
>

?I missed the initial post, somehow.?

?Could there be an index on something on the home machine which does not
exist on the work machine? Can you do an EXPLAIN on both to see what the
query plan is??


?Perhaps another major reason would be if the home machine hardware is
faster than the other machine?. An extreme example: at work my machine is a
very old 2 GiB Pentium system running at 1.8 GiHz with a normal 5400 RPM
hard drive. My home machine is a 16 GiB, 3.7 GiHz Xeon with an 320 GiB SSD
+ 2 TiB 7200 RPM HD. Need I mention how much faster my home machine is? The
software configuration is almost identical: Fedora 22 x86_64.

I also wonder if the work PC is running other "server" type applications
which use up CPU and RAM. Also, is the work PC running 64 bit or 32 bit
Linux? How about Windows at home (I assume 64 bit here)?

But, in any case, 40 minutes seems really poor performance, unless there is
some hardware issue.

Also, I agree with Simon that the query itself, and it's use of NULL, looks
weird. And I don't see where it could find any matches because the "ON
work.fpath = home.fpath" should never match if either fpath is NULL.



>
> Probably a newer version of SQLite.  You didn't tell us the version
> numbers for SQLite on the two machines.
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown