Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[Michael Falconer]

Ahthe inevitable becomes reality. I knew when this stuff surfaced a few
years back that it was not going to go away, and this outcome was just a
matter of time. Not a great a great shock as a life in programming has seen
so much change. I don't need to Fossil, because I am one. Also a bit of a
lurker who has enjoyed lurking here for years because of the excellent
standard of technical replies and the interesting banter that evolves from
differing technical opinions. So I'll take the opportunity now to thank
(too many to single out) the many contributors to this list who have made
it unique, and well...just a nice place to be.

So while you are all off playing with your interface, javascript forum
thingy's I guess I'll have to find somewhere else to go. You'll notice I
did not mention any specific technical objections and that's because
sometimes (even in I.T.) it's just not about the tech! Can't help feeling
that someone just shot my dog!



On Sat, 14 Mar 2020 at 07:01, Jim Dodgen  wrote:

> Another lurker here ...
> I have always preferred mailing lists and found forums to be too fat.  That
> said, I like the fossil forum.  It was referred to bring a "enginerish"
> style which is fine given the audience
> Kudos for its speed and light weight,  No fluff just data.
>
> Jim "Jed" Dodgen
> j...@dodgen.us
>
>
> On Fri, Mar 13, 2020 at 12:24 PM Simon Slavin 
> wrote:
>
> > On 13 Mar 2020, at 6:50pm, Thomas Kurz  wrote:
> >
> > > Why can't the forum just forward all new postings to this mailing list
> > and vice versa? Then everyone could chose what to use ;)
> >
> > I think one of the objectives of moving away from email is to prevent
> > email spam.
> > ___
> > 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
>


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

Re: [sqlite] 18 minutes 41 seconds

[Michael Falconer]

>
> There is no "year 0" between 1 BC and 1 AD.  This is perhaps the most
> common fencepost problem in existance.  The "great renaming" of AD to CE
> and doing away with BC by replacing them with "off by one" numbers less
> than 1 does not change the fact that there was, in fact, no year 0.

Obviously the character(s) responsible  for dates etc were NOT C
programmers!

On Tue, 31 Dec 2019 at 14:45, Richard Damon 
wrote:

> On 12/30/19 10:10 PM, Pierpaolo Bernardi wrote:
> > On Tue, Dec 31, 2019 at 4:07 AM Keith Medcalf 
> wrote:
> >>
> >> On Monday, 30 December, 2019 19:29, Michael Falconer <
> michael.j.falco...@gmail.com> wrote:
> >>
> >>> As we approach the end of yet another year ( and indeed decade ).
> >> Technically, every year is the end of a decade, if one means the
> immediately preceding ten years.
> >>
> >> However, if you mean the end of the second decade of the 21st century,
> you will have to wait another year for that.  January 1st, 0001 AD was the
> first day of the year 1.  The first decade ended at the end of December
> 31st 0011 AD, not December 31st, 0010 AD. (if following the proleptic
> Gregorian calendar).
> > Languages don't work like this.
> >
> > https://www.collinsdictionary.com/dictionary/english/decade
> >
> > Cheers
>
> Its a difference between ordinals and numerals. The 20th century was
> from the beginning of 1901 to the end of 2000. We also have the century
> called the 1900's which went from 1900 to the end of 1999.
>
> Decade would work the same way, the 202st decade goes from 2011 to end
> of 2020, but the 2010s go from 2010 to end of 2019.
>
> --
> Richard Damon
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: [sqlite] 18 minutes 41 seconds

[Michael Falconer]

Great work but pretty much what we have come to expect from DRH and the
SQLite team. As we approach the end of yet another year ( and indeed decade
) can I indulge the list in a simple congratulations to all involved and to
the outstanding support on offer when a member of this email list. SQLite
is personally my favourite software release  and IMHO right up there as the
most useful and reliable software of all time! Thanks to all who have made
it possible.


On Tue, 31 Dec 2019 at 11:13, Richard Hipp  wrote:

> That's the total elapse time from me checking in a bug (check-in
> https://www.sqlite.org/src/info/40d10e7aad5b8992) until Manuel
> Rigger's fuzzer had located the bug and issued a ticket against it:
> (ticket https://www.sqlite.org/src/info/892575cdba4e1e36).
>
> Well, at least the bisect didn't take very long!
>
> --
> 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
>


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

Re: [sqlite] OT!!!!! Understanding the WITH clause

[Michael Falconer]

SELECT peace FROM disaster WHERE disaster.cause = 'Windows';

Returns: *nix


On Thu, 20 Jun 2019 at 14:26, Sam Carleton  wrote:

> James,
>
> That is a really, really interesting quote.  it really got me thinking
> about peace and the definition of it.  It got me thinking so much I wanted
> to know the context of the sentence, so I went looking for it in Letter
> from the Birmingham Jail, this is my source link (
> http://web.cn.edu/kwheeler/documents/letter_birmingham_jail.pdf).  I am
> not
> able to find that sentence in the PDF.
>
> Ah, hold the press, I searched a bit more and discovered that what you
> quote is not a direct quote but a paraphrased version of this sentence:
>
> I have almost reached the regrettable conclusion that the Negro's great
> stumbling block in the stride toward freedom is not the White Citizens
> Councillor or the Ku Klux Klanner but the white moderate who is more
> devoted to order than to justice; who prefers a negative *peace* which is
> the *absence of tension* to a positive peace which i*s the presence of
> justice*; who constantly says, "I agree with you in the goal you seek, but
> I can't agree with your methods of direct action"; who paternalistically
> feels that he can set the timetable for another man's freedom; who lives by
> the myth of time; and who constantly advises the Negro to wait until a
> "more convenient season."
>
> Here is what I learned from this.  I was WRONG!  I made a very arrogant
> statement that everyone knows what peace is, as if there is only one
> meaning of peace!
>
> MLK is talking about a very different type of peace then what I am talking
> about.  The way I read what MLK is talking about, I get the impression he
> is talking about civil peace, aka everyone following the laws the civil
> athorities have put in place.  As I think most folks get, today, many of
> these laws were not so good and thus denied folks justice.
>
> I was referring to a feeling of inner peace.  For example:  Today I learned
> my company is terminating my employment as of Oct 1.  I'm at "inner" peace
> with that.
>
> James, thank you so much for pointing out that quote and modivating me to
> dig into it, I learned a lot tonight, which helps me continue to maintain
> that inner peace after such an "interesting" day.
>
>
> Pax vobiscum,
> Sam Carleton
>
>
> On Tue, Jun 18, 2019 at 2:57 PM James K. Lowden 
> wrote:
>
> > On Mon, 17 Jun 2019 20:46:41 -0400
> > Sam Carleton  wrote:
> >
> > > It is my view that peace is not something that can be defined with
> > > some words, rather it is a universal experience.
> >
> > "But peace is not merely the absence of this tension, but the
> > presence of justice."
> > -- MLK, Letter from the Birmingham Jail
> >
> > --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
>


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

Re: [sqlite] How many digits do you need ?

[Michael Falconer]

Nice one Simon,

as a resident Australian (and a lover of levity) I liked your link, which
led me on to the data source. I took a pair of those 15 digit coordinates
and pumped them into good old Google maps but sadly I was unable to zoom in
far enough to see any chlorine atoms. :-(. Shame that.

On Thu, 4 Apr 2019 at 16:50, Simon Slavin  wrote:

> Please allow me a little levity, spinning off an earlier discussion of how
> many digits a decimal number type needs to store.
>
> 
>
> " Carbrook, for instance, is at -27.673862 153.25624 and at
> -27.673861999297635 153.25624388146.
>
> [...] those 15-place figures locate the suburb's latitude to the nearest
> tenth of a nanometre, about half the diameter of a chlorine atom. "
>
> First, spot the '999' and '' suggesting a problem.  Second wonder
> whether anyone read the data.
>
> Apart from that, the article is complimentary about the format used for
> making a lot of data easily searchable.  So it's a nice example to use when
> talking about care with data preparation.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: [sqlite] Is it possible to concatenate an arbitrary number of columns into one?

[Michael Falconer]

As Simon points out there is no SQL solution to your issue. Some sort of
external utility processing with things like awk, sed or even cut may
assist or for a quick and dirty method you could set the sqlite3 command
line utility .separator value to a blank string which may (or may not)
provide a temporary method. Not in raw SQL though.

On Wed, 21 Nov 2018 at 11:32, Simon Slavin  wrote:

> On 20 Nov 2018, at 11:54pm, Shane Dev  wrote:
>
> > Is there an SQL statement to concatenate all columns into a single
> column without explicitly naming them?
>
> No.  And I can't think of any short-cut way to do what you want.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: [sqlite] Regarding CoC

[Michael Falconer]

Oh dear,

it's all gone...while my devout atheism is generally pleased my
somewhat annoying 'free will, free speech ' ethic has rust on it! Richard,
it's your joint and it's such a good place, friendly and mostly respectful.
My atheism was NOT offended in any way by all that God speak and I do
support the notion that you are perfectly entitled to have a CoC and for it
to take whatever form you feel appropriate. But I'm an honest guy and will
unsub if my un-godliness is just totally unacceptable, but I'll still be
using SQLite!

On Thu, 25 Oct 2018 at 06:10, Mantas Gridinas  wrote:

> Or a capture card.
>
> On Wed, Oct 24, 2018, 21:28 Brian Chrzanowski  wrote:
>
> > Probably a virtual machine.
> >
> > On Wed, Oct 24, 2018, 2:27 PM R Smith  wrote:
> >
> > >
> > > On 2018/10/24 8:19 PM, Stephen Chrzanowski wrote:
> > > > ..// without users consent. ... unlike...
> > > >
> > >
> >
> https://www.extremetech.com/wp-content/uploads/2016/08/Windows10-BSOD-640x353.jpg
> > >
> > > How did you take a screenshot while Windows was hanging/recovering?
> > >
> > > I call foul!
> > >
> > > (Or is that a new Windows 10 feature?)
> > >
> > >
> > > ___
> > > 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-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: [sqlite] Regarding CoC

[Michael Falconer]

>
> I found code of conduct in documentation and I was wondering if it were
> true. Checking the version history it appears to have been added on
> 2018-02-22.
>

Sure that publishing date wasn't 2018-04-01?

On Wed, 24 Oct 2018 at 08:02, Stefan Evert  wrote:

>
> > On 23 Oct 2018, at 07:04, Paul  wrote:
> >
> > If my opinion has any value, even though being atheist, I prefer this
> CoC 100 times over
> > the CoC that is being currently pushed onto the many open-source
> communities, that was
> > created by some purple-headed feminist with political motives. This one
> does not have
> > any hidden intentions (at least, it seems so to me, knowing that you're
> honest person).
>
> Exactly my feelings.
>
> – Stefan
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: [sqlite] SQLite CVE-2015-6607 (Escalation of privilege issue )

[Michael Falconer]

Just amusing, and found while looking for above.

Researchers have disclosed a vulnerability in *Android's* SQLite that can
> leak sensitive information without an application having adequate
> privileges.
>
​So when did DRH sell out to Android? :-)

From an old (2012) blog report ​HERE


Getting a sense that it's more about Android than sqlite, just as DRH
suggested.


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

Re: [sqlite] SQLite CVE-2015-6607 (Escalation of privilege issue )

[Michael Falconer]

These may enlighten a little..at least it appears to be related?

 http://www.cvedetails.com/cve/CVE-2015-6607/

IBM report 




On 17 April 2017 at 22:09, Simon Slavin  wrote:

>
> On 17 Apr 2017, at 10:35am, Saurav Sarkar 
> wrote:
>
> > Can you please also let me know how this bug can be exploited in an
> > application.
>
> The problem was apparently spotted as a theoretical vulnerability and no
> demonstration code was submitted.  It was never reported to the SQLite
> development team, so the team has no record of what was wrong, what it did,
> or how to exploit it.
>
> From what I can see, it affected only versions of Android before Android
> 5.1.  It allowed an application with exploit code in, if given sufficient
> privilages, to modify certain system files.  I don’t know which files it
> could modify or what damage could be done that way.  If you can find
> discussion of the problem or demonstration code I think it would be welcome
> here.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Why isn't my time formatting working?

[Michael Falconer]

UmI'm one of those sad old hacks who doesn't store dates at all. Just
Years, Months, and Days. You can do just about anything with them stored
that way, and in sqlite it seems to matter little whether they are string
or numeric columns. Dates are a rubbery concept not well suited to db
storage IMHO. ;-)


On 11 March 2017 at 05:35, Jens Alfke  wrote:

>
> > On Mar 8, 2017, at 12:52 PM, R Smith  wrote:
> >
> >> Interestingly I rarely see dates stored in ISO8601 format/text
> >
> > Because every programmer is a self-proclaimed optimization genius!
>
> In this case it often makes sense to optimize in advance. In multiple
> situations over the years I’ve seen date-string parsing be a major
> bottleneck, in operations like database indexing and file reading. It’s
> surprisingly expensive; some of that is due to handling the weirdnesses of
> human date systems, but a lot seems to be because the typical functions
> have to handle arbitrary formats and decipher the format string as well as
> the input. (I’ve found you can do a lot better with a function that’s
> hardcoded to parse a specific date format.)
>
> > If speed/space isn't critical, I always advise ISO8601 dates, typically
> stored (in SQLite anyway) in a NUMERIC typed column.
>
> I basically agree, it’s just that the speed seems to be critical more
> often than one would think :)
>
> At least some date formats, including ISO-8601 with times in UTC, have the
> feature that you can compare dates as strings without having to parse them.
> That makes sorting by date a lot faster.
>
> —Jens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
 Michael.j.Falconer.
___
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

[Michael Falconer]

Congrats to all contributors to this thread. Robust discussions like this
make this my absolute favourite list. For the record I like the OP's
suggestion which was more about the features of the printf() function than
anything else. Everybody wins though, because of the great discussion and
the differing viewpoints brought to light. Another great SQLite thread! ;-)

On 12 February 2017 at 05:38, R Smith  wrote:

>
>
> On 2017/02/11 6:50 PM, Clemens Ladisch wrote:
>
>> James K. Lowden wrote:
>>
>>> I doubt you'll win that argument.
>>>
>> You should have checked before writing this.  ;-)
>> http://www.sqlite.org/cgi/src/info/064445b12f99f76e
>>
>
> Pfff, my subsequent points all made moot. Well done and thanks for this!
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Simple web query tool

[Michael Falconer]

>
> Running the sqlite3 command-line shell via cgi works way better than you
> may expect.
>

​Yay verily, and that is really not doing a great tool justice. I've done a
lot of similar things to what Lindsay outlines above both with web and
application targets, Often these procedures are set up as proof of concept
and in a sort of testing mode. I have found that the so-called test setup
actually functioned more reliably and consistently than the application
based code we eventually hacked up. Simple, reliable and very, very
flexible. Sqlite just does it's thing with a minimum amount of fuss and
minimal impact on system resources, can't rave enough. :-)​


On 3 February 2017 at 18:29, Lindsay Lawrence  wrote:

> Running the sqlite3 command-line shell via cgi works way better than you
> may expect.
> The command-line shell has a small footprint and works well with stdio in
> batch mode.
>
> You can run a shell script that runs an instance of the cli shell and reads
> and runs a .sql file.  The sql file and bash can be as complex as it needs
> to be.  You can pass in params on the command-line by inserting env values
> into a temp table and then using that table as necessary in subsequent sql.
>
> For example:
> Configure your httpd for cgi then have a cgi script, say "*report.cgi*":
>
> #!/bin/bash
> /path/to/sqlite3 -bail -batch "/path/to/my.s3db" ".read
> /path/to/report.sql"
>
> and in *"/path/to/report.sql*"
>
> .mode html
> .headers on
> .print Content-Type: text/html
> .print
> .print 
> select * from from report_view;
> .print 
>
> For large datasets, or something you just want to import conveniently into
> a spreadsheet, or another db, for further munging you could set csv mode
> and/or force a download. As a note, unless you are sorting a very large
> dataset the resource usage of all of this is quite low as sqlite just pipes
> the dataset out over the http response as it is generated.
>
> /Lindsay
>
>
> On Wed, Feb 1, 2017 at 8:10 AM, Jay Kreibich  wrote:
>
> > I'm looking for an *extremely* simple web tool that will allow me to
> > configure a dozen or so stored queries, which people can then select and
> > run on an internal server.  If the system supports a query variable or
> two,
> > that would be fantastic, but I don't even need that.  Any thoughts?  Or
> do
> > I dust off the PHP tutorials and spend an afternoon throwing something
> > together?
> >
> >  -j
> >
> >
> > --
> > Jay A. Kreibich < J A Y @ K R E I B I.C H >
> >
> > "Intelligence is like underwear: it is important that you have it, but
> > showing it to the wrong people has the tendency to make them feel
> > uncomfortable." -- Angela Johnson
> > ___
> > 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
>



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

Re: [sqlite] SQLite output mode

[Michael Falconer]

Just from some quick command line tool playing around, a dirt quick fix
presented itself. .mode csv followed by .mode column (following the .mode
ascii operation) seems to return the .column mode to normal. This on SQLite
3.16.2 on Linux command line. I'm not suggesting this as a fix as Dr Hipp
has already attended to that but I found it interesting anyway.



On 27 January 2017 at 12:54, Richard Hipp  wrote:

> On 1/26/17, Nikolas Manes  wrote:
> > Hello,
> > I am Nikolas and facing an issue with sqlite, could you please help?
> > You will find more details on the link bellow.
> > http://stackoverflow.com/q/41730574/6293866
>
> The ".mode ascii" command changes the default row and column separators.
>
> Check-in https://www.sqlite.org/src/info/58f02e6eae8fc9e2 enhances the
> command-line shell to change the row and column separator strings back
> to the default when you do ".mode column".  This enhancement will
> appear in the next release.  Or you can recompile using the code on
> trunk, which is stable.
>
> --
> 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
>



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

Re: [sqlite] Possible bug when creating a calculated index

[Michael Falconer]

Thank you Simon,

I do see the inconsistency and thanks for those examples. I had answered
previously before I saw your explanation and I now see why there is
concern. It certainly appears to be inconsistent given such use cases.

On 27 January 2017 at 10:26, Michael Falconer <michael.j.falco...@gmail.com>
wrote:

> Ersin,
>
> apologies if I seem to be suffering from terminal thickness, but I still
> don't get it. Why would I expect anything other than column interpretation
> from a single quoted argument. I *want to be told* that my column does
> not exist, I don't want a calculated index so why should I be expecting
> one. On the other hand if I choose double quotes I'm probably doing
> something different. Maybe someone else should weigh in and the penny will
> finally drop if I am missing the point, but I'm still not seeing crawly
> things. :-)
>
>
> On 27 January 2017 at 10:01, Ersin Akinci <ersin.aki...@gmail.com> wrote:
>
>> Michael,
>>
>> If I understood DRH and Simon correctly, I think the cause for concern
>> is that SQLite should be interpreting the single quotes as a string
>> literal, yet it interprets it as a column. Perhaps it's a strange
>> example (i.e., why would you want to index a string literal?), but
>> still, the behavior deviates from what's expected, the expected
>> behavior being that we should get a calculated index.
>>
>> Best,
>> Ersin
>>
>> On Thu, Jan 26, 2017 at 2:56 PM, Michael Falconer
>> <michael.j.falco...@gmail.com> wrote:
>> > Simon,
>> >
>> > as I see it there is no problem here. Explicit quoting regardless, the
>> > column does not exist and an error is returned, isn't this the expected
>> > outcome? In the DRH quoted section a reason is presented as to why no
>> error
>> > is returned due to a built in default action. This may or may not be a
>> > point for further analysis (ie. is this an appropriate default) but I'm
>> not
>> > seeing obvious crawly things. Perhaps it's me missing something Simon
>> but
>> > I'm not overly concerned about the above. Don't use double quotes
>> (always
>> > single) and it would appear things are just fine. You'll get told if
>> your
>> > column is non-existent.
>> >
>> >
>> > On 27 January 2017 at 00:12, Simon Slavin <slav...@bigfraud.org> wrote:
>> >
>> >>
>> >> On 25 Jan 2017, at 12:50pm, Simon Slavin <slav...@bigfraud.org> wrote:
>> >>
>> >> > Bug is as follows:
>> >>
>> >> Anyone ?  Did I miss something and you’re all too polite to point it
>> out ?
>> >>
>> >> Simon.
>> >> ___
>> >> sqlite-users mailing list
>> >> sqlite-users@mailinglists.sqlite.org
>> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >>
>> >
>> >
>> >
>> > --
>> > Regards,
>> >  Michael.j.Falconer.
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@mailinglists.sqlite.org
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>> --
>> Ersin Y. Akinci -- ersinakinci.com
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> Regards,
>  Michael.j.Falconer.
>



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

Re: [sqlite] Possible bug when creating a calculated index

[Michael Falconer]

Ersin,

apologies if I seem to be suffering from terminal thickness, but I still
don't get it. Why would I expect anything other than column interpretation
from a single quoted argument. I *want to be told* that my column does not
exist, I don't want a calculated index so why should I be expecting one. On
the other hand if I choose double quotes I'm probably doing something
different. Maybe someone else should weigh in and the penny will finally
drop if I am missing the point, but I'm still not seeing crawly things. :-)


On 27 January 2017 at 10:01, Ersin Akinci <ersin.aki...@gmail.com> wrote:

> Michael,
>
> If I understood DRH and Simon correctly, I think the cause for concern
> is that SQLite should be interpreting the single quotes as a string
> literal, yet it interprets it as a column. Perhaps it's a strange
> example (i.e., why would you want to index a string literal?), but
> still, the behavior deviates from what's expected, the expected
> behavior being that we should get a calculated index.
>
> Best,
> Ersin
>
> On Thu, Jan 26, 2017 at 2:56 PM, Michael Falconer
> <michael.j.falco...@gmail.com> wrote:
> > Simon,
> >
> > as I see it there is no problem here. Explicit quoting regardless, the
> > column does not exist and an error is returned, isn't this the expected
> > outcome? In the DRH quoted section a reason is presented as to why no
> error
> > is returned due to a built in default action. This may or may not be a
> > point for further analysis (ie. is this an appropriate default) but I'm
> not
> > seeing obvious crawly things. Perhaps it's me missing something Simon but
> > I'm not overly concerned about the above. Don't use double quotes (always
> > single) and it would appear things are just fine. You'll get told if your
> > column is non-existent.
> >
> >
> > On 27 January 2017 at 00:12, Simon Slavin <slav...@bigfraud.org> wrote:
> >
> >>
> >> On 25 Jan 2017, at 12:50pm, Simon Slavin <slav...@bigfraud.org> wrote:
> >>
> >> > Bug is as follows:
> >>
> >> Anyone ?  Did I miss something and you’re all too polite to point it
> out ?
> >>
> >> Simon.
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> >
> >
> > --
> > Regards,
> >  Michael.j.Falconer.
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> --
> Ersin Y. Akinci -- ersinakinci.com
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Possible bug when creating a calculated index

[Michael Falconer]

Simon,

as I see it there is no problem here. Explicit quoting regardless, the
column does not exist and an error is returned, isn't this the expected
outcome? In the DRH quoted section a reason is presented as to why no error
is returned due to a built in default action. This may or may not be a
point for further analysis (ie. is this an appropriate default) but I'm not
seeing obvious crawly things. Perhaps it's me missing something Simon but
I'm not overly concerned about the above. Don't use double quotes (always
single) and it would appear things are just fine. You'll get told if your
column is non-existent.


On 27 January 2017 at 00:12, Simon Slavin  wrote:

>
> On 25 Jan 2017, at 12:50pm, Simon Slavin  wrote:
>
> > Bug is as follows:
>
> Anyone ?  Did I miss something and you’re all too polite to point it out ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] SQLite3 Tutorial error

[Michael Falconer]

UmSnobol?

Nope absolutely deny all knowledge. Well.actually, didn't cut any code
in it but did hear about Spitbol. No I kid you not, sort of a super Snobol
I was led to believe. I was cutting Cobol at the time, eons ago. :-) A
colleague told me about it and I took a look but not a close one.



On 5 January 2017 at 16:35, Ken Wagner  wrote:

> Keith,
>
> Thanks for the further explanation and the ll alias.
>
> What is so hard to digest is that:
>
> 1. SQLiteMan - a Linux GUI does the *[^1-9]* pattern and omits rows
> with digits 1-9 in them. Using 3.11.0.
> 2. DBBrowser - Linux GUI does the same using 3.9.2 and 3.11.0 in
> different versions, one older.
> 3. SQLite Tutorial @ www.sqlitetutorial.net - Has GLOB lesson page.
> Uses version @ https://www.sqlite.org/download.html. I downloaded
> sqlite-tools-linux-x86-3160100.zip  qlite-tools-linux-x86-3160100.zip>(1.77 MiB), also 3.15.0. Worked as in
> #1 above in both cases in the SQLite Tutorial, but oppositely in the
> SQLite3 CLI.
> 4. The FireFox 50 SQLite Manager add-on. This one is quite good. Good
> layouts, easy to use and detailed. Works as in #1 above. Uses SQLite 3.13.0.
>
> Yet the SQLite3 CLI does the OPPOSITE and _includes_ any names with digits
> 1-9 in them with vv 3.15.0 and 3.16.0.
>
> Do you have any idea why? Is it really the dim sum??
>
> This will have to be explained to my intended customers because some will
> use the SQLite3 CLI. Most will use a GUI as they are not very computer
> literate. I will be offering them training and also directing them to
> training at a good web SQLite tutorial.
>
> All of the above are using the 'chinook.db'. My system is Linux/Ubuntu
> 16.04, SQLite3 3.15.0 and 3.16.0 CLIs and the above programs. I use the CLI
> in both the Terminator and Gnome-Terminal. Some minor differences with
> encoding.
>
>
> Ken
>
>
>
> On 01/04/2017 10:49 PM, Keith Medcalf wrote:
>
> Yes.  The GLOB was invented on Unix.  I posted an example of the Unix
>> filename globbing (which has not changed, to my knowledge, since the 60's),
>> which works exactly the same as the GLOB operator in SQLite 3.9.0 through
>> the current head of trunk.  Perhaps there were minor changes, but nothing
>> that affects the output of the *[1-9]* or *[^1-9]* patterns when applied to
>> the same data used in the Linux demonstration.  However, I did not try and
>> build every single version of SQLite between 3.9.0 to 3.17.0 to see if one
>> of them happened to be broken.  The two ends and a sampling from the middle
>> all worked the same.
>>
>> And by the way, GLOB predates REGEX by about 15 years.  REGEX borrowed
>> (and modified) GLOB syntax.
>>
>> (in case you have never used a Linux/Unix system with an ll command
>> alias, the command to create it is:  alias ll='ls -l')
>>
>> Are you ABSOLUTELY SURE that the authors of the third-party tools have
>> not provided their own GLOB function that works differently, perhaps in
>> accordance with their Dim Sum because their little hearts did not desire
>> the built in one?
>>
>> -Original Message-
>>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>>> On Behalf Of Ken Wagner
>>> Sent: Wednesday, 4 January, 2017 21:24
>>> To: SQLite mailing list
>>> Subject: Re: [sqlite] SQLite3 Tutorial error
>>>
>>> Aha! GLOB is an implementation of the UNIX Glob function. It just
>>> borrows the regex character notation of [^1-9].
>>>
>>> I have 3.15 and 3.16 CLIs installed. Trying any other CLI versions at
>>> this point won't help for the customer. They will be using a SQLite3
>>> GUI. I will explain the difference between the CLI and the GUI versions.
>>>
>>> Where is this UNIX Glob notation spelled out? My system is Linux. I
>>> expect the UNIX version will be somewhat different.
>>>
>>> The Linux GLOB is used to find patterns in pathnames (not databases.) Is
>>> the Unix version the same?
>>>
>>> Thanks,
>>>
>>> Ken
>>>
>>> On 01/04/2017 11:51 AM, R Smith wrote:
>>>

 On 2017/01/04 7:01 PM, Jens Alfke wrote:

> On Jan 4, 2017, at 5:57 AM, R Smith  wrote:
>>
>> As I have it (and as is implemented by SQLite) the GLOB operator
>> implements a REGEXP that matches against a regexp pattern
>>
> No, these are NOT regular expressions in the usual sense of the word.
> GLOB's syntax is incompatible with what are commonly called “regular
> expressions”, and its feature set is a lot more limited. (It may
> technically implement a type of regular expression in the underlying
> algorithmic sense, but I think using the term is misleading.)
>
 Quite correct, I meant REGEXP as an internal function of the
 Regular-expression type, not the official "regular expression" syntax
 - So a "misleading term" then in your words. Allow me to be more clear
 then: GLOB in SQLite specifically matches Unix file globbing syntax
 (which is very 

Re: [sqlite] importing csv

[Michael Falconer]

Alan,

if you are new to sqlite3 then let me give you a little heads up on
something that tripped me up a couple of times in early days. I am assuming
you are the command line sqlite3 interface which will require you to set
the separator for data imports.

.separator ,

...is likely what you want if your data came from Excel or the like. The
default '|' is probably not what you will want. After that

.import 'datafile' 'table' should have a fighting chance given a good match
between column types and data fields.


On 25 August 2016 at 10:26, Brian Curley  wrote:

> Windows requires that you escape the \ in the path, effectively doubling
> them up.
>
> Regards.
>
> Brian P Curley
>
>
> On Aug 24, 2016 8:24 PM, "Simon Slavin"  wrote:
>
> > I hope someone else can help.
> >
> > Does that table already exist in the database file ?
> >
> > Is the first line of the csv file a line of data or a line of column
> names
> > ?
> >
> > Simon.
> > ___
> > 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
>



-- 
Regards,
 Michael.j.Falconer.
___
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

[Michael Falconer]

Yes Richard,

in my experience your quoted time benefits are very much on target. I have
rewritten Excel/Access (VBA) which I originally developed (and was quite
proud of). Re-writes using C/C++ and SQLite indeed delivered the
performance gains you experienced and relieved me of the pain associated
with VBA development. A sort of win-win, and if you throw in moving away
from Windoze to something sensible you can get even more benefit. But that
is another tale...and sadly not always under our control.


On 19 August 2016 at 00:12, Rousselot, Richard A <
richard.a.rousse...@centurylink.com> wrote:

> Yes, it is much faster.  The process was done in Excel/Access before and
> took ages.  I have had processes go from 8 hours before to 30 min now using
> SQLite.
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Michael Gratton
> Sent: Wednesday, August 17, 2016 8:05 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] 64-bit SQLite3.exe
>
>
> Richard,
>
> On Thu, Aug 18, 2016 at 2:23 AM, Rousselot, Richard A <
> richard.a.rousse...@centurylink.com> wrote:
> > I was only interested in doing these calculations quickly; that is my
> > real-world.  The SQL scripts I am using were built with multiple steps
> > with intermediate temp tables that are used to create a final result
> > table.
> >
> > I understand my needs are not generally how others use SQLite.  I am
> > using it as a platform for fast calculations that happens to store
> > results in a way that can be quarried.  Every time, my process begins
> > all prior results are deleted, only the input tables stay the same.
>
>
> Out of curiosity, are you using these in an end-user application, i.e.
> as an interactive response to user actions? I would be curious to know if
> building intermediate temporary tables is fast enough for returning results
> for such uses.
>
> Thanks,
> //Mike
>
> --
> ⊨ Michael Gratton, Percept Wrangler.
> ⚙ 
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> This communication is the property of CenturyLink and may contain
> confidential or privileged information. Unauthorized use of this
> communication is strictly prohibited and may be unlawful. If you have
> received this communication in error, please immediately notify the sender
> by reply e-mail and destroy all copies of the communication and any
> attachments.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] SpeedTest1 Comparison of 32 vs 64 bit on Windows 10 13483.15

[Michael Falconer]

Nice Keith,

and very topical as well as being informative. Note a couple of things that
got my curiosity chip activating:

*subquery in result set* test produces interesting outcome with the 64 bit
version bucking the trend. Any ideas there?
Also it would appear *select* clauses demonstrate an above average gain
from 64 bit. Lack of disk activity sprung to my mind but interested to hear
any thoughts there.

excellent information and thanks for sharing it.


On 18 August 2016 at 14:38, Keith Medcalf  wrote:

>
> Same code, same compile options, same compiler version
> options -s -O3 -pipe -march=native -mtune=native -falign-functions=16
> -falign-loops=16 -flto
>
> 32-bit GCC 4.9.3
>
> >speedtest1 --size 1000
> -- Speedtest1 for SQLite 3.15.0 2016-08-17 11:14:39
> a861713cc6a3868a1c89240e8340bc
>  100 - 50 INSERTs into table with no index.
> 0.781s
>  110 - 50 ordered INSERTS with one index/PK
> 1.266s
>  120 - 50 unordered INSERTS with one index/PK..
> 1.672s
>  130 - 25 SELECTS, numeric BETWEEN, unindexed..
> 1.281s
>  140 - 10 SELECTS, LIKE, unindexed.
> 3.031s
>  142 - 10 SELECTS w/ORDER BY, unindexed
> 3.032s
>  145 - 10 SELECTS w/ORDER BY and LIMIT, unindexed..
> 3.063s
>  150 - CREATE INDEX five times.
> 2.641s
>  160 - 10 SELECTS, numeric BETWEEN, indexed
> 8.814s
>  161 - 10 SELECTS, numeric BETWEEN, PK.
> 9.282s
>  170 - 10 SELECTS, text BETWEEN, indexed...
> 3.241s
>  180 - 50 INSERTS with three indexes...
> 2.781s
>  190 - DELETE and REFILL one table.
> 3.111s
>  200 - VACUUM..
> 2.282s
>  210 - ALTER TABLE ADD COLUMN, and query...
> 0.094s
>  230 - 10 UPDATES, numeric BETWEEN, indexed
> 8.969s
>  240 - 50 UPDATES of individual rows...
> 1.641s
>  250 - One big UPDATE of the whole 50-row table
> 0.453s
>  260 - Query added column after filling
> 0.078s
>  270 - 10 DELETEs, numeric BETWEEN, indexed
> 2.235s
>  280 - 50 DELETEs of individual rows...
> 2.078s
>  290 - Refill two 50-row tables using REPLACE..
> 5.110s
>  300 - Refill a 50-row table using (b&1)==(a&1)
> 2.798s
>  310 - 10 four-ways joins..
> 5.320s
>  320 - subquery in result set..
>  22.936s
>  980 - PRAGMA integrity_check..
> 4.969s
>  990 - ANALYZE.
> 1.657s
>TOTAL...
> 104.616s
>
> 64-bit GCC 4.9.3
>
> >speedtest1 --size 1000
> -- Speedtest1 for SQLite 3.15.0 2016-08-17 11:14:39
> a861713cc6a3868a1c89240e8340bc
>  100 - 50 INSERTs into table with no index.
> 0.797s
>  110 - 50 ordered INSERTS with one index/PK
> 1.250s
>  120 - 50 unordered INSERTS with one index/PK..
> 1.609s
>  130 - 25 SELECTS, numeric BETWEEN, unindexed..
> 0.969s
>  140 - 10 SELECTS, LIKE, unindexed.
> 2.859s
>  142 - 10 SELECTS w/ORDER BY, unindexed
> 2.860s
>  145 - 10 SELECTS w/ORDER BY and LIMIT, unindexed..
> 2.813s
>  150 - CREATE INDEX five times.
> 2.219s
>  160 - 10 SELECTS, numeric BETWEEN, indexed
> 6.751s
>  161 - 10 SELECTS, numeric BETWEEN, PK.
> 7.229s
>  170 - 10 SELECTS, text BETWEEN, indexed...
> 2.719s
>  180 - 50 INSERTS with three indexes...
> 2.266s
>  190 - DELETE and REFILL one table.
> 2.266s
>  200 - VACUUM..
> 1.735s
>  210 - ALTER TABLE ADD COLUMN, and query...
> 0.062s
>  230 - 10 UPDATES, numeric BETWEEN, indexed
> 7.329s
>  240 - 50 UPDATES of individual rows...
> 1.516s
>  250 - One big UPDATE of the whole 50-row table
> 0.437s
>  260 - Query added column after filling
> 0.047s
>  270 - 10 DELETEs, numeric BETWEEN, indexed
> 2.047s
>  280 - 50 DELETEs of individual rows...
> 1.938s
>  290 - Refill two 50-row tables using REPLACE..
> 4.438s
>  300 - Refill a 50-row table using (b&1)==(a&1)
> 2.360s
>  

Re: [sqlite] Exec vs Prepare, step, finalize.

[Michael Falconer]

...as for Stephen, Mr Beal you need to get out more LOL! Little Johnny
Tables indeed. Rub it in, why not? LOL


On 12 August 2016 at 09:38, Michael Falconer <michael.j.falco...@gmail.com>
wrote:

> Thanks all,
>
> must admit to being around db's for years but I never did get my head
> around the whole injection thing, sad but true. Keith summed it up in usual
> succinct fashion which when read by one old hack cause much reddening of
> the facial features. Bugger, says I, that speaks my language and it's
> saying you are a goose! I'm admitting to no more!
>
> Thanks all for opening my eyes, at long last, and excuse me while I grep
> my code for sqlite3_exec()grr...damnetc.
>
>
> On 11 August 2016 at 23:40, Quan Yong Zhai <q...@msn.com> wrote:
>
>> > From: michael.j.falco...@gmail.com
>> > Date: Thu, 11 Aug 2016 15:53:39 +1000
>> > To: sqlite-users@mailinglists.sqlite.org
>> > Subject: Re: [sqlite] Exec vs Prepare, step, finalize.
>> >
>> > I have a self styled routine (similar to the glibc manual example) for
>> > concatenating the strings values that make up the sql statement. It uses
>> > memcpy rather than the built in strcat etc.
>> sqlite3_mprintf  http://www.sqlite.org/c3ref/mprintf.html provide some
>> formattingoptions to defending SQL injection. '%Q' to quote string
>> parameters, '%w' to quote table name or column name..
>> >So what exactly is the issue
>> > with the string building if it does  not include sql derived from user
>> > input? I'm not quite seeing that bit, sorry or the vagueness
>> >
>> > It does however sound like it would just be better to adopt the three
>> step
>> > functions as the preferred method in all cases, which is probably what
>> I'm
>> > trying to come to grips with. I do see the prepare/step/finalize process
>> > with bound parameters etc is very much preferred in most cases, but
>> > wondered if those cases where SQL is application provided were an
>> > exception. I'm leaning towards a no on that now. Thanks for your input
>> and
>> > in advance or any additional insight.
>> >
>>
>> I am not a security expert, but I think the culprit of SQL injection
>> vulnerability in SQLite is not sqlite3_exec(). It's the way  how the SQL
>> command text constructed. if you look into the SQLite source code, there
>> are many places used sqlite3_exec(), and  theparameters are carefully
>> quoted by '%Q', '%q' or '%w'.
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> Regards,
>  Michael.j.Falconer.
>



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

Re: [sqlite] Exec vs Prepare, step, finalize.

[Michael Falconer]

Thanks all,

must admit to being around db's for years but I never did get my head
around the whole injection thing, sad but true. Keith summed it up in usual
succinct fashion which when read by one old hack cause much reddening of
the facial features. Bugger, says I, that speaks my language and it's
saying you are a goose! I'm admitting to no more!

Thanks all for opening my eyes, at long last, and excuse me while I grep my
code for sqlite3_exec()grr...damnetc.


On 11 August 2016 at 23:40, Quan Yong Zhai  wrote:

> > From: michael.j.falco...@gmail.com
> > Date: Thu, 11 Aug 2016 15:53:39 +1000
> > To: sqlite-users@mailinglists.sqlite.org
> > Subject: Re: [sqlite] Exec vs Prepare, step, finalize.
> >
> > I have a self styled routine (similar to the glibc manual example) for
> > concatenating the strings values that make up the sql statement. It uses
> > memcpy rather than the built in strcat etc.
> sqlite3_mprintf  http://www.sqlite.org/c3ref/mprintf.html provide some
> formattingoptions to defending SQL injection. '%Q' to quote string
> parameters, '%w' to quote table name or column name..
> >So what exactly is the issue
> > with the string building if it does  not include sql derived from user
> > input? I'm not quite seeing that bit, sorry or the vagueness
> >
> > It does however sound like it would just be better to adopt the three
> step
> > functions as the preferred method in all cases, which is probably what
> I'm
> > trying to come to grips with. I do see the prepare/step/finalize process
> > with bound parameters etc is very much preferred in most cases, but
> > wondered if those cases where SQL is application provided were an
> > exception. I'm leaning towards a no on that now. Thanks for your input
> and
> > in advance or any additional insight.
> >
>
> I am not a security expert, but I think the culprit of SQL injection
> vulnerability in SQLite is not sqlite3_exec(). It's the way  how the SQL
> command text constructed. if you look into the SQLite source code, there
> are many places used sqlite3_exec(), and  theparameters are carefully
> quoted by '%Q', '%q' or '%w'.
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Exec vs Prepare, step, finalize.

[Michael Falconer]

Thanks Jay,

excellent response. I'll ask for clarity on one statement though.

That’s the basic theory, but even knowing that, most people get it wrong.
> In short, if you’re using string manipulation functions to build your query
> string, you’re very very very very likely doing it wrong.
>

I have a self styled routine (similar to the glibc manual example) for
concatenating the strings values that make up the sql statement. It uses
memcpy rather than the built in strcat etc. So what exactly is the issue
with the string building if it does  not include sql derived from user
input? I'm not quite seeing that bit, sorry or the vagueness.

It does however sound like it would just be better to adopt the three step
functions as the preferred method in all cases, which is probably what I'm
trying to come to grips with. I do see the prepare/step/finalize process
with bound parameters etc is very much preferred in most cases, but
wondered if those cases where SQL is application provided were an
exception. I'm leaning towards a no on that now. Thanks for your input and
in advance or any additional insight.


On 11 August 2016 at 14:32, Jay Kreibich <j...@kreibi.ch> wrote:

>
> On Aug 10, 2016, at 9:21 PM, Michael Falconer <
> michael.j.falco...@gmail.com> wrote:
>
> > Hi all,
> >
> > just seeking some opinions, and perhaps some dev indications about
> > deprecation, in relation to the sqlite3_exec facility. I kind of like the
> > callback functionality in certain cases as it is convenient in some
> > circumstances where the sql injection problem is not an issue.
> >
> > Ok I say it is not an issue, but am I right. I am no security expert and
> > have often been surprised at some of the hack techniques used over the
> > years. The sql injection issue as far as I can tell depends on where the
> > offending sql originates, but don't hesitate to contradict that
> assumption
> > if you believe it is wrong.
>
> That’s the basic theory, but even knowing that, most people get it wrong.
> In short, if you’re using string manipulation functions to build your query
> string, you’re very very very very likely doing it wrong.
>
> > In a scenario where the sql supplied to the callback routine is
> application
> > generated or indeed application constant based does the sql injection
> > threat disappear?
>
> Yes.  The threat is when you start to use sprintf() to built your query
> strings.
> Even more so if some of those inputs can trace their origin to user
> generated
> values.
>
> > user supplied sql via arguments, with only database name and table name
> > required from the user. This would appear to be immune to that technique
> or
> > am I misguided? I'm never certain when it comes to security stuff, I hate
> > it.
>
> You are misguided.  The whole idea behind injections is that you can alter
> that
> database name so that it is much more than a database name.  If you can’t
> understand the whys of it, you can’t defend against it.  And that’s
> important
> in this case, since you can’t use bound parameters for database names or
> table names.  Switching to _prepare() won’t help in this specific case
> because
> you have no choice but to build the query from string primitives.
>
>
> Another issue with sqlite_exec() is that all the values are returned as
> strings.
> You have no idea what the type of the field is, and if it is a non-string
> value,
> it is converted to a string before the callback is called.  This can be a
> big issue
> for many designs.
>
> > In a similar vein I noted in an O'Reilly publication it mentioned that
> the
> > exec method was semi depracated and should be avoided. I wondered what
> the
> > view of the SQLite dev crew was. and if there were any plans in the
> future
> > to drop the exec function? In light of the teams focus on backward
> > compatibility I suspect there are no such plans but I thought I'd ask
> > anyway just to be sure. Thanks in advance for any helpful comments.
>
>   -j  (Author, Using SQLite).
>
>
> --
> Jay A. Kreibich < J A Y @ K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it, but
> showing it to the wrong people has the tendency to make them feel
> uncomfortable." -- Angela Johnson
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] Exec vs Prepare, step, finalize.

[Michael Falconer]

Hi all,

just seeking some opinions, and perhaps some dev indications about
deprecation, in relation to the sqlite3_exec facility. I kind of like the
callback functionality in certain cases as it is convenient in some
circumstances where the sql injection problem is not an issue.

Ok I say it is not an issue, but am I right. I am no security expert and
have often been surprised at some of the hack techniques used over the
years. The sql injection issue as far as I can tell depends on where the
offending sql originates, but don't hesitate to contradict that assumption
if you believe it is wrong.

In a scenario where the sql supplied to the callback routine is application
generated or indeed application constant based does the sql injection
threat disappear? Is this a valid assumption? In other words there is no
user supplied sql via arguments, with only database name and table name
required from the user. This would appear to be immune to that technique or
am I misguided? I'm never certain when it comes to security stuff, I hate
it.

In a similar vein I noted in an O'Reilly publication it mentioned that the
exec method was semi depracated and should be avoided. I wondered what the
view of the SQLite dev crew was. and if there were any plans in the future
to drop the exec function? In light of the teams focus on backward
compatibility I suspect there are no such plans but I thought I'd ask
anyway just to be sure. Thanks in advance for any helpful comments.


-- 
Regards,
 Michael.j.Falconer.
___
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

[Michael Falconer]

Thanks Ryan,

and apologies for singling your comment out from the many that suggested a
roll-your-own CLI. That last statement pretty much says it all IMHO but I
would go even further. For many it will not even be choice in future times.
There will always be those who can make their own arrangements and hedge
around the inevitable but for most it will be 64bit or nothing one day. An
above post regarding Android 32 bit platform support is one indication that
the choices will not last for much longer. I should add that it is not
entirely high on my personal wish list (64bit CLI) because I do take the
valid points made earlier that the current CLI works just fine in a 32 bit
environment.

All of that being said, I have ultimate faith in the core SQLite
development team to make the right call about when it would be appropriate
to go the 64 bit path. There would be much to consider and it should be
considered carefully as always, I'm in no rush but apparently some others
have more pressing needs. Just as I am not in a rush, I also have no
problem with the OP's request being implemented. I'm with Ryan on the warm
and cuddly approach, we might as well.


On 11 August 2016 at 10:58, R Smith <rsm...@rsweb.co.za> wrote:

>
> On 2016/08/10 11:35 PM, Michael Falconer wrote:
>
>> ...// Ryan is very
>> right about the relative ease of such exercises but IMHO wrong that it
>> suits all, or even most cases.
>>
>
> Just to be clear - if my post wasn't, I never advocated against the 64-bit
> CLI or that making it suits all, I very much supported the OP in the
> request (and still do) for some of the reasons Michael mentions here. All I
> said was that right now (until it is supplied), if he needs it as much as
> claimed, rolling his own is not all that hard (and certainly not a useless
> skill).
> I do not agree with the notion that the 64-bit CLI "shouldn't be
> pre-compiled because it's easy to do it yourself" - if that was true, why
> supply the 32-bit even?, or indeed ANYthing pre-compiled?
>
> To reiterate: 64-bit is the new thing - let's embrace it!
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
 Michael.j.Falconer.
___
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

[Michael Falconer]

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.

Simple really, the OP is completely correct to be requesting a 64bit CLI as
we are now very much in that world. Of course a legacy 32bit version is
appropriate but surely the mainstream dev must move on to 64bit at  some
stage? Not for me to say when this should occur but surely it is inevitable
or am I just an old dinosaur?

Some excellent side points made (single out DD here) and one o the
standouts is the quite ridiculous stance many developers take regarding
user requests such as the one made in the OP. There is absolutely no
requirement at all for end users to be software developers/engineers, that
would be absurd and I doubt this was ever the intent of sqlite development.
Roll your own compilations in some cases are not an option and quite
possibly for multiple reasons. I'm not going to bore you by expanding the
obvious, but the OP identified one such case as a regulated corporate
environment, a beast I am unfortunately all too familiar with. Ryan is very
right about the relative ease of such exercises but IMHO wrong that it
suits all, or even most cases.

I may be old, but I'm a progressive dinosaur. We have to move on, and in
this biz, quickly if we want to remain in any way current. We can make
choices as developers about whether we embrace such things as OO or R or
whatever new methodology comes along but we don't get that same choice
about base system changes such as 16/32/64 bit processors. They are like
death and taxes, inevitable.

So when the 64bit CLI arrives, as it surely will do, the OP will be
vindicated and all the rest will be historic fluff. Just my opinion, but as
I said great discussion from my very favourite technical list.


On 11 August 2016 at 01:11, Jim Callahan 
wrote:

> The issue is not simply recompiling the CLI for 64-bit; I assume you want
> the tested reliability of SQLite.
>
> "The [SQLite] project has 787 times as much test code and test scripts" as
> it does source code for the executable.
> https://www.sqlite.org/testing.html
>
>
> Running the exact same tests would not be enough, because you (and I) would
> want special tests for larger than 4 GB
> RAM. So, additional tests would have to be developed.
>
> So, I see three alternate solutions:
>
> 1. Find a corporate sponsor to fund development of 64-bit SQLite CLI
>
> 2. Remove some tables from memory (though it sounds like your difficulties
> are caused
> by recursion rather than the size of the raw data)
>
> 3. Use SQLite for persistent storage but move the in memory (tree
> navigation) operations to another (open source)  language
> such as C, Java, Python or R (or the new Julia language that is approaching
> version 1.0)  that has interfaces for SQLite
> and a 64-bit build for Windows.  You will probably need another language to
> display the output anyway why not take
> advantage of Python, R or Julia?
>
> Even if you move to another language, you may find that your problem is
> recursion.
> In my experience, computer science textbooks give elegant examples using
> recursion,
> but then say the solution is not scale-able and give a less elegant
> solution using iterative techniques.
>
> Jim Callahan
> Data Scientist
> Orlando, FL
>
>
> On Tue, Aug 9, 2016 at 10:31 AM, Rousselot, Richard A <
> richard.a.rousse...@centurylink.com> wrote:
>
> > I would like to request a SQLite official 64-bit SQLite3.exe CLI (not
> DLL)
> > be created.
> >
> > I have reviewed the prior discussions regarding 64-bit SQLite3 and the
> > reasoning for which why creating a 64-bit version is denied are "it does
> > not make a real difference", "you can just use ram disks", etc., etc.
> >
> > Here is my plea...  I am using a set of complicated CTEs to crawl through
> > a network (tree) to aggregate and calculate formulas.  I don't have
> > exceptionally large datasets but my CTEs result in a ton of memory usage.
> > The process works well from disk, in Windows, but using a smaller test
> > sample I get about a 30% to 40% increase in processing time if I set the
> > PRAGMA to temp_store = 2.  If I use a normal dataset, not a small test, I
> > hit an approximate 2G limit and get a "out of memory" message, which I
> > understand is due to SQLite3.exe being 32-bit.  I have found some 3rd
> party
> > 64-bit builds for SQLite3 (best found is 3.8.5) but they are out of date
> > and don't allow all functionality that I am using.  So, I do have a use
> > case that requires 64-bit and I would see a significant increase in
> speed.
> >
> > As to RAM disks, I work in a corporate environment that locks down user
> > rights which precludes me from distributing a tool that requires the
> > creation of a tool that needs administrator rights.  I 

[sqlite] Get count of unique values?

[Michael Falconer]

Both Igor's and Simon's solutions work on my test system.

The only additional comment is that Simon's works in Sqlite as expected.
However running the same on PostgreSQL bombs with an error complaining
about an sub-query with no name! Interesting? Perhaps watch out for
portability with that one. Igor's 'select count(distinct)' worked on both
without problem and returned the same results.


On 18 February 2016 at 22:14, Simon Slavin  wrote:

>
> On 18 Feb 2016, at 5:42am,   
> wrote:
>
> > Then I want to obtain the total count of unique values for F1 field. In
> the
> > above sample, since unique F1 field values are 1, 2, 3 and 4, so the
> count
> > is 4. How to write SQL query to obtain the value?
>
> First, get a list of the unique values:
>
> SELECT F1 FROM MyTable GROUP BY F1
>
> then count them
>
> SELECT count(*) FROM (SELECT F1 FROM MyTable GROUP BY F1)
>
> The above code is just from my head.  I have not tested it.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
 Michael.j.Falconer.

[sqlite] Performance comparison between SQLite and SQL Server?

[Michael Falconer]

Good thread,

which absolutely nails the point 'dev decisions for app cases' make a
developers world go round. I personally couldn't think of a greater waste
of time than a benchmark comparison between client server rdbms's and
sqlite. Do what benefits your case most. The above from Jim pretty much
encapsulates my thoughts:

"SQLite is not directly comparable to client/server SQL database engines
> such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to
> solve a different problem.   Client/server SQL database engines strive to
> implement a shared repository of enterprise data. ...SQLite strives to
> provide local data storage for individual applications and devices."
>

I could bang on about my own preferences and decisions I've made but they'd
only be reiterating the points made above. They were based on system
requirement specs and where local storage was involved it was a blindingly
obvious decision to go with sqlite. Rob above made another excellent point
often overlooked (usually an afterthought for many dev's):

4. The support is top notch. I have brought and paid for govt scale
> databases for governments and to be honest the support for SQLite is just
> as good, and to be honest I would say better than Big Red or Big Blue (and
> I used to work for Big Blue).
>

It is another unique property of a great product. Support is not just
sqlite specific either (a cop out on many a tech forum) and particularly on
this list the topics can be rather broad. There is plenty of good quality
feedback and many a good general SQL solution which just adds to the sqlite
package as a whole.


On 16 February 2016 at 09:42, Jim Callahan 
wrote:

> SQLite would be most comparable to *SQL Server Express LocalDB* edition
> which is introduced in this July 2011 blog post
>
> https://blogs.msdn.microsoft.com/sqlexpress/2011/07/12/introducing-localdb-an-improved-sql-express/
>
> More uptodate information about *SQL Server Express LocalDB* edition
> is in this 2016 Microsoft Developer's Network (MSDN) article
> https://msdn.microsoft.com/en-us/library/hh510202.aspx
>
> This page "*Appropriate Uses for SQLite*" (whentouse.html) describes BOTH
> "*Situations Where SQLite Works Well*"
>
> and
>
> "*Situations Where A Client/Server RDBMS May Work Better*"
> http://sqlite.org/whentouse.html
>
>
> Opening lines of whentouse.html:
>
> "SQLite is not directly comparable to client/server SQL database engines
> such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to
> solve a different problem.   Client/server SQL database engines strive to
> implement a shared repository of enterprise data. ...SQLite strives to
> provide local data storage for individual applications and devices."
>
> Even Microsoft has adopted SQLite for some limited tasks (such as storing
> state) within every shipping copy of Windows 10.
> "SQLite is a unique case: it is an open source, externally developed
> software that is used by core system components, and our flagship apps like
> Cortana and Skype.  ...After shipping SQLite as a system component in July,
> we wanted to include it in our SDK for November. With more than 20,000
> Windows Apps and more than half of our top apps using SQLite, it made sense
> to just make expose the system SQLite to app developers."
> http://engineering.microsoft.com/2015/10/29/sqlite-in-windows-10/
>
>
> There is a historical and unfair (specially compiled version of SQLite
> against default settings of PostgreSQL) benchmark
> available on this page, but now that you understand the use cases, this
> particular benchmark is not that useful in addition
> to being out of date and unfair.
> https://www.sqlite.org/speed.html
>
> Jim Callahan
> Data Scientist
> https://www.linkedin.com/in/jamesbcallahan
> Orlando, FL
>
> On Mon, Feb 15, 2016 at 4:54 PM, Simon Slavin 
> wrote:
>
> >
> > On 15 Feb 2016, at 9:41pm, James K. Lowden 
> > wrote:
> >
> > > SQL Server has none of those restrictions, and probably keeps pace with
> > > SQLite even on its home turf.  But the administration of SQL Server is
> > > nontrivial.  For that reason alone, I would never use it in situations
> > > where SQLite would do.
> >
> > That's the fella.  Major advantage of SQLite: zero admin.  Not even a
> > background task.
> >
> > Second advantage: you know exactly where you data is.  Better still, it's
> > simple: one database == one file, and the file has the same name as the
> > database.  I remember trying to reconstruct a MySQL database from a dead
> > server.  One folder with a confusing mass of files in.  Your database is
> > part of some of those files, but the files may be huge even if the one
> > database you care about is tiny.  That was not a fun time.
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> 

[sqlite] json_group_array

[Michael Falconer]

I just love this list and the contained discussions. Doffing my cap to
Keith and his succinct and very useful summary of the subtle differences
regarding aggregate type functions. Especially like the nested stuff, which
I must admit had not really occurred to me. Nice stuff Keith and thanks for
sharing that info. It also clarifies the underlying fact that these
functions are often slightly misunderstood even by experienced SQL hacks.


On 6 February 2016 at 07:24, Keith Medcalf  wrote:

>
> count(*) counts the rows of the result set selected
> count(column) counts the NOT NULL values in the column of the result set
> selected
> count(DISTINCT column) counts the number of distinct values (excluding
> NULLs) in the column of the result set selected
>
> count(column IS NULL) is equivalent to count(*) (the expression always
> returns true (1) or false(0)
> count(column IS NOT NULL) is equivalent to count(*)  for every row so
> therefore all rows are counted)
>
> sum(column IS NULL) returns the count of the number of rows in which the
> column is null
> sum(column IS NOT NULL) returns the count of the number of rows in which
> the column is not null
>
> These can be combined, so for example count(DISTINCT column) + sum(column
> IS NULL) returns the number of unique values in the column where NULLs are
> distinct.
> and count(DISTINCT column) + (sum(column IS NULL) > 0) returns the number
> of unique values where NULLs are not distinct.
>
> and so on and so forth
>
> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> > bounces at mailinglists.sqlite.org] On Behalf Of R Smith
> > Sent: Friday, 5 February, 2016 00:55
> > To: sqlite-users at mailinglists.sqlite.org
> > Subject: Re: [sqlite] json_group_array
> >
> >
> >
> > On 2016/02/05 6:34 AM, TJ O'Donnell wrote:
> > > I can't argue for the correctness of including nulls in aggregate
> > functions
> > > or not.
> > > It truly is an arbitrary decision meant for standards-makers.  Yet,
> most
> > > aggregate
> > > function do not include nulls.  Interestingly, some SQL's do include
> > them
> > > in count()
> > > but sqlite does not.  In my example table, select count(a) from x
> > returns 3,
> > > but select count(*) from x returns 4 even though a is the only column.
> > > I haven't tried every sqlite agg, but I think they all exclude null,
> > except
> > > json_group_array
> > > and json_group_object.
> >
> > I think you are mistaken in your understanding. While JSON has some
> > rules and some conventions, when used inside an SQL engine, the rules of
> > SQL needs to be adhered to before any "convention" of JSON.
> > Not showing Null values in JSON is a convention, not a rule. (Else, why
> > else would json even need the 'NULL' construct?)
> >
> > Further to this, in SQL, how would you know how many elements are
> > present in a json array and which of them are null if there is no way to
> > output them? Agreed, sometimes it isn't needed to know, but then you are
> > welcome to exclude them via the WHERE clause.
> >
> > You are also mistaken about the SQL convention and SQLite-specific
> > operations re. Nulls - If I have a table t with one single column "a"
> > with 3 rows (2 text values and one null value) then doing SELECT
> > COUNT(a) FROM t; will show 2 and SELECT COUNT(*) FROM t; will show 3, as
> > it should - yes, even though a is the only column. The * doesn't mean
> > "a", even if the only column is "a". It means "all the DB rows" and so
> > include nulls. (The standard might be hazy on this, I didn't check, but
> > this is definitely how SQLite works, and not as you suggested).
> >
> > This is also very important. Sometimes we'd want to know how many rows
> > are in the DB, not JUST which non-null rows are in the only column in
> > the DB - that is why we can decide to use either COUNT(a) or COUNT(*),
> > or more deliberate with an explicit GROUP BY clause. I would never want
> > this convention to be altered.
> >
> >
> > > As a side issue here, but important still I think, what should
> > json(null)
> > > mean?
> > > In my table x, select json(a) from x returns valid json integers for
> > > non-null rows,
> > > but return a sql null (a blank from command-llne sqlite) not a json
> null
> > > (which would
> > > be the string null) when a is null.  In other words, json(null) returns
> > > null,
> > > not 'null'.
> >
> > Here I'm with you - the null should output 'null'
> > (Devs: I'm guessing this might be an oversight in the CLI rather than
> > the SQL engine?)
> >
> > > I know the json stuff is new in sqlite, but I think it's worth getting
> > > these issues worked
> > > out, considering how useful json has become.
> >
> > Right you are, but first the issues need discovery - which is what is
> > happening in this very thread. :)
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > 

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

[Michael Falconer]

Outstanding!

it is kudos which is so richly deserved, and IMHO, an example of REAL
integrity earned. Over the years I have watched as SQLite developed,
learned much from this list, and was impressed by Dr Hipps hands-on
leadership in directing development in what he and the team deemed an
appropriate direction. Many contentious issues have been raised over the
years but they have been consistently handled with a core ethic in mind.
Make it better, more efficient, scalable and usable on multiple platforms.

It is not often that you truly see such a development process remain true
to it's founding principles, we humans have a powerful capability to
corrupt and in IT we tend to excel at that. In SQLite we have a shining
example of what can be achieved when a development process is well handled,
and it is sadly all to rare. So well done to all involved in developing a
truly great system and to all on this list who have made so many good
recommendations and offered their seasoned technical opinions so openly and
in collaborative spirit. It is a genuine open source triumph of which you
should all be proud. Thanks.


On 12 November 2015 at 04:51, Jay Kreibich  wrote:

>
> On Nov 10, 2015, at 6:38 PM, Richard Hipp  wrote:
>
> > On 11/10/15, Jay Kreibich  wrote:
> >>
> >> I want to be a Certified SQLite Professional.
> >>
> >
> > The inventors and developers of the SQLite database engine to all to
> > whom these presents may come, Greetings:  Whereas Mr. Jay Kreibich has
> > exhibited detailed knowledge of the use and inner workings of SQLite
> > by publishing an outstanding textbook on that subject, we do hereby
> > confirm unto him the title of
> >
> >Certified SQLite Professional
> >
> > with all the rights, honors, and duties thereunto appertaining.  In
> > witness whereof, the electronic signature of the BDLF of SQLite is
> > hereto subscribed.  Given in Charlotte on this the tenth day of
> > November and in the year of our Lord two thousand and fifteen.
>
>
> w00t!  Time to update my resume!
>
>  -j
>
> --
> Jay A. Kreibich < J A Y @ K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it, but
> showing it to the wrong people has the tendency to make them feel
> uncomfortable." -- Angela Johnson
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
 Michael.j.Falconer.

[sqlite] factbook.sql World Factbook Country Profiles in SQL (Incl. factbook.db - Single-File SQLite Distro)

[Michael Falconer]

Gerald,

thanks for this, it looks most interesting on both levels. i.e. The data
release and your project work. Thank you for the information and your
project.

On 2 November 2015 at 01:49, Gerald Bauer  wrote:

> Hello,
>
>   I've started a new project, that is, /factbook.sql [1] that offers
> an SQL schema for the World Factbook and also includes a pre-built
> single-file SQLite database, that is, factbook.db [2] for download.
>
>   What's the World Factbook?
>
>   The World Factbook [3] published by the Central Intelligence Agency (CIA)
>   offers free 260+ country profiles in the public domain
>   (that is, no copyright(s), no rights reserved).
>
>   Anyways, what's it good for? For example, to find the ten largest
> countries by area, try:
>
> SELECT name, area FROM facts ORDER BY area DESC LIMIT 10;
>
>   Resulting in:
>
>   Russia | 17_098_242
>   Canada |  9_984_670
>   United States  |  9_826_675
>   China  |  9_596_960
>   Brazil |  8_515_770
>   Australia  |  7_741_220
>   European Union |  4_324_782
>   India  |  3_287_263
>   Argentina  |  2_780_400
>   Kazakhstan |  2_724_900
>
>Or to find the ten largest countries by population, try:
>
>   SELECT name, population FROM facts ORDER BY population DESC LIMIT 10;
>
>Resulting in:
>
>World  | 7_256_490_011
>China  | 1_367_485_388
>India  | 1_251_695_584
>European Union |   513_949_445
>United States  |   321_368_864
>Indonesia  |   255_993_674
>Brazil |   204_259_812
>Pakistan   |   199_085_847
>Nigeria|   181_562_056
>Bangladesh |   168_957_745
>
>And so on. Note: Using the factbook command line tool and scripts
> you can build yourself an up-to-date copy.
>
>Questions? Comments? Welcome. Enjoy. Cheers.
>
> [1] https://github.com/factbook/factbook.sql
> [2] https://github.com/factbook/factbook.sql/releases
> [3] https://www.cia.gov/library/publications/the-world-factbook
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
 Michael.j.Falconer.

Re: [sqlite] Search query alternatives.

[Michael Falconer]

I am glad I posted the question.

Yes James, there is little I can disagree with in your excellent summary.
Even the critique of my perhaps poorly framed question is indeed valid. I
take your point regarding spec vs implementation, and in my experience
across different rdbms's I have frequently seen evidence which supports
your assertions regarding db/os system influence on implementation
approaches.

Application code too has it's part to play. How do we plan to access the
data? There's a strong case too, IMHO, to have flexibility in the design,
perhaps leading to differing approaches with the variable types of data we
may be storing.

The original design decisions were made by someone who was, and still is,
essentially a hobby programmer. I don't think he'd ever heard of of Chris
Date or Mr Codd at that time and like all novice application programmers,
he had little understanding about the effect db design could have on his
application and it's source code. His design choices were initially made on
the basis of what he could easily understand and what was (as it appeared
to him then) easy to program with. It is some time ago, and we who have
lived with rdbms's for years get to say, 'that is a horrible design!'. I
think R. Smith hit on a point above, regarding code overhead. Yep, plenty
of that. And so the lesson is learned the hard way for someone who until
recently had viewed normalisation as a way to make coding harder and to
slow down the execution of queries.

So with that perspective you can perhaps come some way to understanding the
why component. On analysis, I agree with suggested design changes at the
higher level. i.e. Dynamic tables are at the root of issues going forward.
They are requiring tedious application code gymnastics, more difficult
query analysis and poorer query performance.  I suppose the upside is that
it will be a challenge to see what improvements can be made, and that is
always fun and games. I kind of like Mr Smith's other suggestion about an
SQLITE testbed or prototype. So easy to work with SQLITE, and probably
perfect for this task. Thanks all for your contributions.

Just FYI James, the application is coded in php and connects to a mysql
database. It can be installed either as a browser based, stand alone or
client server app. It's common implementation is on low end shared hosts,
even free hosting services. So this limits us somewhat to what is commonly
allowed on such platforms. Things like Stored Procedures are unfortunately
outside our scope when it come to design considerations.

Thanks all.


On 18 October 2014 02:24, James K. Lowden <jklow...@schemamania.org> wrote:

> On Thu, 16 Oct 2014 09:05:51 +1100
> Michael Falconer <michael.j.falco...@gmail.com> wrote:
>
> > we just wonder if there is a better way to perform this search in
> > SQL. Is there a general technique which is superior either in speed,
> > efficiency or load bearing contexts?
>
> The simple answer is No, because SQL is a specification, not an
> implementation.  Different systems implement it differently and
> therefore perform differently.  Any "general technique" affecting
> performance belongs to the implementation per se, not the SQL, which is
> a logical construction. SQLite itself has changed its performance
> characteristics over the course of its development.
>
> For that reason, any question of performance has to be answered in
> terms of a particular implementation, even its specific version, and
> the OS and hardware it's running on.
>
> That said, there is reason to suppose that a single-table design would
> be more efficient.  If the queries can be expressed with recursion and
> the indexes lead to efficient searches, the query optimizer has less
> work to do.  It has fewer permutations to consider, and the search is
> apt to touch fewer pages.  The analysis tools of the system you're
> using should be able to confirm or deny that supposition.
>
> I would remind your fellows, though, that efficiency is not all.  The
> utility of a model (that is, the database design) is measured by how
> well, to its purpose, it describes the real world.  Any model that must
> be changed as that reality changes in predictable ways isn't really
> much of a model; it turns the designer into a component of the model.
> By recognizing all trees as one, you generalize your model and make it
> do work you are now doing yourself (manually, or in application
> logic).  By any measure, that makes it a better model.
>
> HTH.
>
> --jkl
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Search query alternatives.

[Michael Falconer]

Thanks Eduardo,

a most interesting link.

On 17 October 2014 05:41, Eduardo Morras <emorr...@yahoo.es> wrote:

> On Thu, 16 Oct 2014 09:05:51 +1100
> Michael Falconer <michael.j.falco...@gmail.com> wrote:
>
> > Hi all,
> >
> > first off I must start with an apology. I know I'm sort of doing the
> > wrong thing here as this question is NOT related to sqlite. It is a
> > general SQL question but I ask it here because I have great respect
> > for the answers and discussions I have seen on this forum over many
> > years. I rarely post myself as there are always several contributors
> > who beat me to the answer and often their response is far better than
> > mine would have been. I'm not a code leper, I don't need the actual
> > SQL just the method really, though a short code example would be well
> > received for illustration.
> >
> > Any thoughts? Suggestions? Missiles? Good approach, bad approach, or
> > completely off the grid? I do use sqlite quite a bit, but not on this
> > particular project.
>
> I point you to sqlite closure extension. It may shows you some ideas for
> tree implementation and parent/child relations under sql/sqlite.
>
> http://www.sqlite.org/src/artifact/636024302cde41b2bf0c542f81c40c624cfb7012
> http://www.sqlite.org/src/finfo?name=ext/misc/closure.c
>
> HTH
>
> >
> > --
> > Regards,
> >  Michael.j.Falconer.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ---   ---
> Eduardo Morras <emorr...@yahoo.es>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Search query alternatives.

[Michael Falconer]

Thanks Stephen,

good point, I was just after some general results, I do take your point
about caching etc. However it is logical to apply UNION ALL if appropriate
in preference to UNION which in this context is a bit lazy. I'm a bit
annoyed I didn't pick up on it myself, but thankful to Igor for reminding
me of the difference in the two statements.


On 16 October 2014 11:14, Stephen Chrzanowski <pontia...@gmail.com> wrote:

> Careful with the timing.  You may be looking at OS memory caching the
> result set instead of pulling from the drive.  For best bets, either re-run
> both queries several times, ditch the longest and shortest times, then take
> the mean or average times and do the comparison that way.
>
> On Wed, Oct 15, 2014 at 7:54 PM, Michael Falconer <
> michael.j.falco...@gmail.com> wrote:
>
> > Igor,
> >
> > Nice one. A quick test using 10 lookup trees and the same search
> criteria:
> > *Showing rows 0 - 29 (30 total, Query took 0.4838 sec)*
> >
> > Now with UNION ALL replacing UNION:
> > *Showing rows 0 - 29 (30 total, Query took 0.2050 sec)*
> >
> > You weren't kidding about cheaper were you? LOL
> >
> >
> > On 16 October 2014 10:30, Michael Falconer <michael.j.falco...@gmail.com
> >
> > wrote:
> >
> > > Yes, I agree with the general sentiment. It is not exactly the design I
> > > would have chosen either, but it is what exists. Design change is
> > occurring
> > > but for the moment we are stuck with the current schema.
> > >
> > > If you cannot change the schemata to be more suitable, then your
> > >> demonstrated queries are very near as good as it gets. I can think of
> > other
> > >> ways to achieve the same, but nothing that would be more efficient to
> a
> > >> tangible level. Maybe someone else here can come up with something a
> bit
> > >> more tight.
> > >>
> > > You are preaching to the converted I'm afraid, but I appreciate the
> > points
> > > you made. The quoted paragraph probably answers most of my questions
> > > really, except for this from Igor:
> > >
> > > Unless you do expect duplicates and need to eliminate them, use UNION
> ALL
> > >> - it's much cheaper (this is assuming you insist on keeping multiple
> > >> tables).
> > >>
> > > Cheaper because it drops the operation to remove the duplicate records
> > > from the result set? I'm assuming. I'll give that a try.
> > >
> > > On 16 October 2014 10:05, RSmith <rsm...@rsweb.co.za> wrote:
> > >
> > >>
> > >> On 2014/10/16 00:05, Michael Falconer wrote:
> > >>
> > >>> Hi all,
> > >>>
> > >>> first off I must start with an apology. I know I'm sort of doing the
> > >>> wrong//...
> > >>>
> > >>
> > >> No need to apologise, this flies quite close to the central theme.
> > >> Whether you are using SQLite or any other SQL RDBMS, this is horrible
> DB
> > >> design and it is so for precisely the reasons you are asking the
> > question.
> > >> Tables should not be dynamic: The simple rule of thumb being - if you
> > >> cannot tell (or at least accurately predict) before-hand exactly how
> > many
> > >> tables will be in the final system, then you are doing it wrong.  Why
> > not
> > >> have one set of tables and in stead of preceding each of them with a
> > >> , simply add a column that can host the  as a simple
> > value,
> > >> which will immediately make your life very much easier and get the SQL
> > >> engine to do the work you are now trying to compensate for in a
> > lop-sided
> > >> manual kind of way.
> > >>
> > >> I can only imagine the amount of code you invested into your system to
> > >> track and deal with these dynamic sets of tables, so I know changing
> it
> > >> will seem like a rather large undertaking, but really it will mostly
> > >> involve removing loads of code to end up with a small set of simple
> > queries
> > >> that does all the work for you.
> > >>
> > >> The only reason I can imagine this sort of breakdown useful is if your
> > >> tree tables are all really really huge, like Gigabytes, and so there
> > might
> > >> be some efficiency to be gained from splitting it up, but I doubt this
> > is
> > >> the case.
> > >>
> > >> Just imagine the ease of thos

Re: [sqlite] Search query alternatives.

[Michael Falconer]

Igor,

Nice one. A quick test using 10 lookup trees and the same search criteria:
*Showing rows 0 - 29 (30 total, Query took 0.4838 sec)*

Now with UNION ALL replacing UNION:
*Showing rows 0 - 29 (30 total, Query took 0.2050 sec)*

You weren't kidding about cheaper were you? LOL


On 16 October 2014 10:30, Michael Falconer <michael.j.falco...@gmail.com>
wrote:

> Yes, I agree with the general sentiment. It is not exactly the design I
> would have chosen either, but it is what exists. Design change is occurring
> but for the moment we are stuck with the current schema.
>
> If you cannot change the schemata to be more suitable, then your
>> demonstrated queries are very near as good as it gets. I can think of other
>> ways to achieve the same, but nothing that would be more efficient to a
>> tangible level. Maybe someone else here can come up with something a bit
>> more tight.
>>
> You are preaching to the converted I'm afraid, but I appreciate the points
> you made. The quoted paragraph probably answers most of my questions
> really, except for this from Igor:
>
> Unless you do expect duplicates and need to eliminate them, use UNION ALL
>> - it's much cheaper (this is assuming you insist on keeping multiple
>> tables).
>>
> Cheaper because it drops the operation to remove the duplicate records
> from the result set? I'm assuming. I'll give that a try.
>
> On 16 October 2014 10:05, RSmith <rsm...@rsweb.co.za> wrote:
>
>>
>> On 2014/10/16 00:05, Michael Falconer wrote:
>>
>>> Hi all,
>>>
>>> first off I must start with an apology. I know I'm sort of doing the
>>> wrong//...
>>>
>>
>> No need to apologise, this flies quite close to the central theme.
>> Whether you are using SQLite or any other SQL RDBMS, this is horrible DB
>> design and it is so for precisely the reasons you are asking the question.
>> Tables should not be dynamic: The simple rule of thumb being - if you
>> cannot tell (or at least accurately predict) before-hand exactly how many
>> tables will be in the final system, then you are doing it wrong.  Why not
>> have one set of tables and in stead of preceding each of them with a
>> , simply add a column that can host the  as a simple value,
>> which will immediately make your life very much easier and get the SQL
>> engine to do the work you are now trying to compensate for in a lop-sided
>> manual kind of way.
>>
>> I can only imagine the amount of code you invested into your system to
>> track and deal with these dynamic sets of tables, so I know changing it
>> will seem like a rather large undertaking, but really it will mostly
>> involve removing loads of code to end up with a small set of simple queries
>> that does all the work for you.
>>
>> The only reason I can imagine this sort of breakdown useful is if your
>> tree tables are all really really huge, like Gigabytes, and so there might
>> be some efficiency to be gained from splitting it up, but I doubt this is
>> the case.
>>
>> Just imagine the ease of those search queries you demonstrated... one
>> query, no UNIONs, a single set of joins and an extra where-clause check...
>> Add to that increased efficiency at finding results (run one query in stead
>> of 50).  Easy-mode. As a proof-of-concept, just make an SQLite DB, one set
>> of tables as per your list, add column(s) for prefixing as needed, import
>> all the tables from some large set of them (specifying additionally the
>> column for the prefix) and ten run search queries on them. I am willing to
>> bet on the fact it will be much quicker in addition to being much simpler.
>>
>> If you cannot change the schemata to be more suitable, then your
>> demonstrated queries are very near as good as it gets. I can think of other
>> ways to achieve the same, but nothing that would be more efficient to a
>> tangible level. Maybe someone else here can come up with something a bit
>> more tight.
>>
>>
>>  thing here as this question is NOT related to sqlite. It is a general SQL
>>> question but I ask it here because I have great respect for the answers
>>> and
>>> discussions I have seen on this forum over many years. I rarely post
>>> myself
>>> as there are always several contributors who beat me to the answer and
>>> often their response is far better than mine would have been. I'm not a
>>> code leper, I don't need the actual SQL just the method really, though a
>>> short code example would be well received for illustration.
>>>
>>> It's about a search performed on multiple tables. However 

Re: [sqlite] Search query alternatives.

[Michael Falconer]

Yes, I agree with the general sentiment. It is not exactly the design I
would have chosen either, but it is what exists. Design change is occurring
but for the moment we are stuck with the current schema.

If you cannot change the schemata to be more suitable, then your
> demonstrated queries are very near as good as it gets. I can think of other
> ways to achieve the same, but nothing that would be more efficient to a
> tangible level. Maybe someone else here can come up with something a bit
> more tight.
>
You are preaching to the converted I'm afraid, but I appreciate the points
you made. The quoted paragraph probably answers most of my questions
really, except for this from Igor:

Unless you do expect duplicates and need to eliminate them, use UNION ALL -
> it's much cheaper (this is assuming you insist on keeping multiple tables).
>
Cheaper because it drops the operation to remove the duplicate records from
the result set? I'm assuming. I'll give that a try.

On 16 October 2014 10:05, RSmith <rsm...@rsweb.co.za> wrote:

>
> On 2014/10/16 00:05, Michael Falconer wrote:
>
>> Hi all,
>>
>> first off I must start with an apology. I know I'm sort of doing the
>> wrong//...
>>
>
> No need to apologise, this flies quite close to the central theme. Whether
> you are using SQLite or any other SQL RDBMS, this is horrible DB design and
> it is so for precisely the reasons you are asking the question.  Tables
> should not be dynamic: The simple rule of thumb being - if you cannot tell
> (or at least accurately predict) before-hand exactly how many tables will
> be in the final system, then you are doing it wrong.  Why not have one set
> of tables and in stead of preceding each of them with a , simply
> add a column that can host the  as a simple value, which will
> immediately make your life very much easier and get the SQL engine to do
> the work you are now trying to compensate for in a lop-sided manual kind of
> way.
>
> I can only imagine the amount of code you invested into your system to
> track and deal with these dynamic sets of tables, so I know changing it
> will seem like a rather large undertaking, but really it will mostly
> involve removing loads of code to end up with a small set of simple queries
> that does all the work for you.
>
> The only reason I can imagine this sort of breakdown useful is if your
> tree tables are all really really huge, like Gigabytes, and so there might
> be some efficiency to be gained from splitting it up, but I doubt this is
> the case.
>
> Just imagine the ease of those search queries you demonstrated... one
> query, no UNIONs, a single set of joins and an extra where-clause check...
> Add to that increased efficiency at finding results (run one query in stead
> of 50).  Easy-mode. As a proof-of-concept, just make an SQLite DB, one set
> of tables as per your list, add column(s) for prefixing as needed, import
> all the tables from some large set of them (specifying additionally the
> column for the prefix) and ten run search queries on them. I am willing to
> bet on the fact it will be much quicker in addition to being much simpler.
>
> If you cannot change the schemata to be more suitable, then your
> demonstrated queries are very near as good as it gets. I can think of other
> ways to achieve the same, but nothing that would be more efficient to a
> tangible level. Maybe someone else here can come up with something a bit
> more tight.
>
>
>  thing here as this question is NOT related to sqlite. It is a general SQL
>> question but I ask it here because I have great respect for the answers
>> and
>> discussions I have seen on this forum over many years. I rarely post
>> myself
>> as there are always several contributors who beat me to the answer and
>> often their response is far better than mine would have been. I'm not a
>> code leper, I don't need the actual SQL just the method really, though a
>> short code example would be well received for illustration.
>>
>> It's about a search performed on multiple tables. However the structure of
>> this database is somewhat unorthodox. It contains genealogical data and
>> this is clustered into trees. Each tree has 8 tables, there can be as many
>> as 100 trees but most installations of the associated application software
>> contain between 5 - 50 trees. These 8 tables contain a family trees data
>> and are named:
>> addresses
>> connections
>> events
>> family
>> person
>> repositories
>> sources
>> texts
>>
>> The  changes and is unique for each tree.
>>
>> There are extensive search options offered to users as well as simple name
>> searching and it is one of the best 

[sqlite] Search query alternatives.

[Michael Falconer]

Hi all,

first off I must start with an apology. I know I'm sort of doing the wrong
thing here as this question is NOT related to sqlite. It is a general SQL
question but I ask it here because I have great respect for the answers and
discussions I have seen on this forum over many years. I rarely post myself
as there are always several contributors who beat me to the answer and
often their response is far better than mine would have been. I'm not a
code leper, I don't need the actual SQL just the method really, though a
short code example would be well received for illustration.

It's about a search performed on multiple tables. However the structure of
this database is somewhat unorthodox. It contains genealogical data and
this is clustered into trees. Each tree has 8 tables, there can be as many
as 100 trees but most installations of the associated application software
contain between 5 - 50 trees. These 8 tables contain a family trees data
and are named:
addresses
connections
events
family
person
repositories
sources
texts

The  changes and is unique for each tree.

There are extensive search options offered to users as well as simple name
searching and it is one of the best features of the app. It works pretty
well, so it ain't broke in any sense, we just wonder if there is a better
way to perform this search in SQL. Is there a general technique which is
superior either in speed, efficiency or load bearing contexts?

I am sure you can see one of the pitfalls here is the exponential growth of
such a search query as both total number of trees and indeed user search
criteria increase. For each criteria component, and there are quite a few,
the appropriate tables must be queried and results joined.

Searches return records of individuals meeting the entered search criteria
so the query focuses on the person table as it's anchor, performs searches
on required other tables in the tree and joins the results to the person
data. The results from each tree search are then UNION'ed to provide the
result set. Here is a contrived example of the SQL query code which should
make things clearer.

The user enters simple search criteria - any person with a last name
containing 'mac' and a first name containing the character 'a'. This is the
resulting query (generated by php code), which searches a small 4 family
tree installation.

(SELECT humo1_person.*, event_kind, event_event, address_place, address_zip
FROM humo1_person
LEFT JOIN humo1_events ON pers_gedcomnumber=event_person_id
LEFT JOIN humo1_addresses ON pers_gedcomnumber=address_person_id
WHERE pers_lastname LIKE '%mac%'
  AND (pers_firstname LIKE '%a%'
  OR (event_kind='name' AND event_event LIKE '%a%')
  )
GROUP BY pers_gedcomnumber
)
UNION
(SELECT humo2_person.*, event_kind, event_event, address_place,
address_zip
FROM humo2_person LEFT JOIN humo2_events ON
pers_gedcomnumber=event_person_id
LEFT JOIN humo2_addresses ON pers_gedcomnumber=address_person_id
WHERE pers_lastname LIKE '%mac%'
  AND (pers_firstname LIKE '%a%'
  OR (event_kind='name' AND event_event LIKE '%a%') )
GROUP BY pers_gedcomnumber
)
UNION
(SELECT humo3_person.*, event_kind, event_event, address_place,
address_zip
FROM humo3_person LEFT JOIN humo3_events ON
pers_gedcomnumber=event_person_id
LEFT JOIN humo3_addresses ON pers_gedcomnumber=address_person_id
WHERE pers_lastname LIKE '%mac%'
  AND (pers_firstname LIKE '%a%'
  OR (event_kind='name' AND event_event LIKE '%a%')
  )
GROUP BY pers_gedcomnumber
)
UNION
(SELECT humo4_person.*, event_kind, event_event, address_place,
address_zip
FROM humo4_person
LEFT JOIN humo4_events ON pers_gedcomnumber=event_person_id
LEFT JOIN humo4_addresses ON pers_gedcomnumber=address_person_id
WHERE pers_lastname LIKE '%mac%'
  AND (pers_firstname LIKE '%a%'
  OR (event_kind='name' AND event_event LIKE '%a%')
  )
GROUP BY pers_gedcomnumber
)
ORDER BY pers_lastname ASC , pers_firstname ASC LIMIT 0,30

Any thoughts? Suggestions? Missiles? Good approach, bad approach, or
completely off the grid? I do use sqlite quite a bit, but not on this
particular project.


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

Re: [sqlite] Best way to implement Find commands

[Michael Falconer]

Hi Vance,

...yes it seems reasonable to expect the same resulting order if your
subsequent (temp creating) query is identical to that which was run
previously. However I can understand, from a theoretical and experience
point of view, why you might feel uneasy about relying on a rowid as the
basis for your operations.

I think the real issue here is 'what happens after new_expression?'. Is it
a requirement that the resulting record set (SELECTed from temp_table) be
in identical order to that which is produced by the initial query which
created temp_table? That will be a problem as there is no guarantee that
will be the case. The concerns regarding ordering then gather significant
weight but perhaps can be addressed with a judicious ORDER BY clause in
'new_expression'. I'm thinking that any complex join operation is very
likely to have an explicit ORDER BY clause or relies on a unique index. If
that ordering is duplicated in 'new_expression' then perhaps better results
can be expected?

You could always grab the resultset from that initial query, plonk in an
appropriate container (list, array, dict etc) in your application language,
and control the rest of the find/next/first/previous/last process from
there. If that's possible or appropriate?

Regards,
Michael.



On Fri, May 24, 2013 at 10:42 PM,  wrote:

> I should have also noted that in most cases the original query is a
> complicated multiple join statement, not a simple table query so there is
> no rowid that I can rely on unless I do generate a temp table.
>
> Vance
>
> on May 24, 2013, ven...@intouchmi.com wrote:
> >
> >Thanks to James, Keith and Michael for your input!
> >
> >I don't have any control over the original query. It may or may not
> include an ORDER
> >BY clause. Ideally it would, which makes the question about repeated
> result order
> >moot.
> >I was hoping that by making the exact same query to build a temporary
> table, SQLite
> >would go through the same procedure thereby loading the temp table in the
> same order
> >that stepping would have generated. If this hope is faulty, it would be
> interesting
> >to know why. And, since the temporary table generates a rowid with
> sequential #s
> >starting with '1', I could use that to control how the various Find
> commands selects
> >a row based on the current value of X. Note that X was originally the
> number of times
> >the original prepared statement was stepped through by repeated calls to
> sqlite_step.
> >Note to Michael - I realize that SQLite does not have find commands which
> is why
> >I have to implement them.
> >I don't think that I can just combine the new criteria with the original
> select because
> >that would most likely cause the rows to be presented in a different
> order.
> >
> >Vance
> >
> >on May 24, 2013, James K. Lowden  wrote:
> >>
> >>On Thu, 23 May 2013 18:38:57 -0600
> >>"Keith Medcalf"  wrote:
> >>
> >>> > And, can I depend on SQLite to generate results in the same order
> >>> > as the original prepare/step sequence and the temp table
> >>> > generation, ie. is the temp table's rowid going to be consistent
> >>> > with the original step order?
> >>>
> >>> If you use an ORDER BY clause, yes. If not, then the rows are
> >>> returned in random order.
> >>
> >>Pardon me while I fix that for you.
> >>
> >> s/random/nondeterministic/
> >>
> >>but for the purposes of discussion much the same: without ORDER BY, the
> >>order cannot be relied on.
> >>
> >>--jkl
> >>___
> >>sqlite-users mailing list
> >>sqlite-users@sqlite.org
> >>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >___
> >sqlite-users mailing list
> >sqlite-users@sqlite.org
> >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Best way to implement Find commands

[Michael Falconer]

OOoops typo on the FindNext, should be a min() instead of max(), i.e.

SELECT * FROM some_table WHERE id = ( SELECT min(rowid) FROM ( SELECT *
FROM some_table WHERE original_expression AND new_expression AND rowid > X
) );

...typing has never been my strong point :-)




On Fri, May 24, 2013 at 12:47 PM, Michael Falconer <
michael.j.falco...@gmail.com> wrote:

> Leading on from what Keith suggests above re: 'and join' can we simplify
> as:
>
> Findfirst:
>
> SELECT * FROM some_table WHERE original_expression AND new_expression
> LIMIT 1;
>
> What the LIMIT 1 will return depends on what index/primary key is in
> effect, sort of hoping for rowid here :-)
>
> This appears equivalent to the temp_table from query + new_query approach,
> but perhaps there is something you have omitted that makes this invalid???
> Also not not sure where you are getting your 'X' value for 'next' and
> 'previous' processing. as there is no real find operation (which would
> likely return multiple records). If this is being stored, perhaps in
> external program code, then fine, there is a clear concept of current
> record and therefore next and previous become trivial (less than, greater
> than current, being X).
>
> FindPrevious:
>
> SELECT * FROM some_table WHERE id = ( SELECT max(rowid) FROM ( SELECT *
> FROM some_table WHERE original_expression AND new_expression AND rowid < X
> ) );
>
> FindNext:
>
> SELECT * FROM some_table WHERE id = ( SELECT max(rowid) FROM ( SELECT *
> FROM some_table WHERE original_expression AND new_expression AND rowid > X
> ) );
>
> FindLast:
>
> SELECT * FROM some_table WHERE id = ( SELECT max(rowid) FROM ( SELECT *
> FROM some_table WHERE original_expression AND new_expression ) );
>
> You can of course use the temp table approach, but sqlite I believe
> creates a temp table to facilitate the nested SELECT so we are indirectly
> using the temp approach..hope this helps.
>
> regards,
> Michael.j.Falconer.
>
>
>
>
>
> On Fri, May 24, 2013 at 10:38 AM, Keith Medcalf <kmedc...@dessus.com>wrote:
>
>>
>> > And, can I depend on SQLite to generate results in the same order as the
>> > original prepare/step sequence and the temp table generation, ie. is the
>> > temp table's rowid going to be consistent with the original step order?
>>
>> If you use an ORDER BY clause, yes.  If not, then the rows are returned
>> in random order.
>>
>> As for the rest of your questions, why do you not just join the search
>> conditions with an AND?
>>
>> ---
>> ()  ascii ribbon campaign against html e-mail
>> /\  www.asciiribbon.org
>>
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> Regards,
>  Michael.j.Falconer.
>



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

Re: [sqlite] Best way to implement Find commands

[Michael Falconer]

Leading on from what Keith suggests above re: 'and join' can we simplify as:

Findfirst:

SELECT * FROM some_table WHERE original_expression AND new_expression LIMIT
1;

What the LIMIT 1 will return depends on what index/primary key is in
effect, sort of hoping for rowid here :-)

This appears equivalent to the temp_table from query + new_query approach,
but perhaps there is something you have omitted that makes this invalid???
Also not not sure where you are getting your 'X' value for 'next' and
'previous' processing. as there is no real find operation (which would
likely return multiple records). If this is being stored, perhaps in
external program code, then fine, there is a clear concept of current
record and therefore next and previous become trivial (less than, greater
than current, being X).

FindPrevious:

SELECT * FROM some_table WHERE id = ( SELECT max(rowid) FROM ( SELECT *
FROM some_table WHERE original_expression AND new_expression AND rowid < X
) );

FindNext:

SELECT * FROM some_table WHERE id = ( SELECT max(rowid) FROM ( SELECT *
FROM some_table WHERE original_expression AND new_expression AND rowid > X
) );

FindLast:

SELECT * FROM some_table WHERE id = ( SELECT max(rowid) FROM ( SELECT *
FROM some_table WHERE original_expression AND new_expression ) );

You can of course use the temp table approach, but sqlite I believe creates
a temp table to facilitate the nested SELECT so we are indirectly using the
temp approach..hope this helps.

regards,
Michael.j.Falconer.





On Fri, May 24, 2013 at 10:38 AM, Keith Medcalf  wrote:

>
> > And, can I depend on SQLite to generate results in the same order as the
> > original prepare/step sequence and the temp table generation, ie. is the
> > temp table's rowid going to be consistent with the original step order?
>
> If you use an ORDER BY clause, yes.  If not, then the rows are returned in
> random order.
>
> As for the rest of your questions, why do you not just join the search
> conditions with an AND?
>
> ---
> ()  ascii ribbon campaign against html e-mail
> /\  www.asciiribbon.org
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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