Re: [sqlite] DRH interview on why/how SQLite succeeded

[Don V Nielsen]

DRH sounds so much more human in the podcast than the DRH I have assembled
in my head from reading this mailing list. :)

On Wed, Feb 19, 2020 at 11:39 AM Simon Slavin  wrote:

> 
>
> Podcast / transcription of DRH interview:
>
> " We talked to Richard about the history of SQLite, where it came from,
> why it succeeded as a database, how its development is sustainably funded
> and also how it’s the most widely deployed database engine in the world. "
>
> Not a formal treatment, but you can piece together what's said and get a
> lot of information on how SQLite developed into what it is today, who is in
> the development team, and how it is funded through the SQLite Consortium.
> Plus random anecdotes about computing over the last 40 years.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New word to replace "serverless"

[Don V Nielsen]

As R Smith pointed out, you already have a good description in your
existing documentation: "SQLite is a self-contained, server-free,
zero-configuration ... "

I would also throw in the term "library", because it is what it is. Sqlite
is just non-executable code that doesn't function on it's own. Even Sqlite
needs the command line tool to do something, otherwise it is just a library
waiting to be linked in.

Off topic, I loathe when terms like "server-less" change meaning, or when
new terms are developed to resell something that has existed for decades.

On Mon, Jan 27, 2020 at 4:19 PM Richard Hipp  wrote:

> For many years I have described SQLite as being "serverless", as a way
> to distinguish it from the more traditional client/server design of
> RDBMSes.  "Serverless" seemed like the natural term to use, as it
> seems to mean "without a server".
>
> But more recently, "serverless" has become a popular buzz-word that
> means "managed by my hosting provider rather than by me."  Many
> readers have internalized this new marketing-driven meaning for
> "serverless" and are hence confused when they see my claim that
> "SQLite is serverless".
>
> How can I fix this?  What alternative word can I use in place of
> "serverless" to mean "without a server"?
>
> Note that "in-process" and "embedded" are not adequate substitutes for
> "serverless".  An RDBMS might be in-process or embedded but still be
> running a server in a separate thread. In fact, that is how most
> embedded RDBMSes other than SQLite work, if I am not much mistaken.
>
> When I say "serverless" I mean that the application invokes a
> function, that function performs some task on behalf of the
> application, then the function returns, *and that is all*.  No threads
> are left over, running in the background to do housekeeping.  The
> function does send messages to some other thread or process.  The
> function does not have an event loop.  The function does not have its
> own stack. The function (with its subfunctions) does all the work
> itself, using the callers stack, then returns control to the caller.
>
> So what do I call this, if I can no longer use the word "serverless"
> without confusing people?
>
> "no-server"?
> "sans-server"?
> "stackless"?
> "non-client/server"?
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record

[Don V Nielsen]

Keith, what if one has a peanut allergy?

On Tue, Oct 15, 2019 at 1:33 PM Jose Isaias Cabrera 
wrote:

>
>
> Keith Medcalf, on Tuesday, October 15, 2019 02:26 PM, wrote...
> >
> >
> > On Tuesday, 15 October, 2019 09:35, Philippe RIO, on
> >
> > >A short question : how could I know if I am reading the last record with
> > >sqlite  (sqlite3_step)? sqlite3_step only returns SQLITE_ROW. Is there a
> > >function for that case which returns SQLITE_DONE? A function which is
> one
> > >record in advance from sqlite3_step.
> >
> > When sqlite3_step returns SQLITE_DONE there are no more rows.
> >
> > A prepared statement is a row generator.  Each time you ask it to
> produce a
> > row (sqlite3_step) it does whatever needs to be done to get you the next
> row
> > and gives it to you (SQLITE_ROW).  Eventually it cannot generate another
> row
> > and you get the message that the generator is empty (SQLITE_DONE).
>
> []clip]
>
> > The only way that you have to know that the tin is empty is that when
> you ask
> > for a peanut the maid replies "Sorry luv, but the tin is empty", rather
> than
> > giving you a peanut.
>
> Keith, may I say thank you.  Your parables are so teach-friendly, and more
> than that, funny.  I appreciate them. Thanks.
>
> josé
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record

[Don V Nielsen]

Pardon me for being thick. But the end of what? The end of the sqlite file?
The end of a table? The end of a select?

I always thought there was no such thing as "a start or an end" as the
database is basically air until you request something from it. Even when
you have something, it could change in the very next exec of the query. So
what is the "start" and what is the "end"?

Looking at a sqlite file as a whole, is it the row at the very end of the
file or the last row inserted, which could be located in some page that is
not even the end of the file.

I don't think I understand too well.

On Tue, Oct 15, 2019 at 11:53 AM Jens Alfke  wrote:

>
>
> > On Oct 15, 2019, at 9:44 AM, Simon Slavin  wrote:
> >
> > You can then remember the value(s) of some column(s) of the row
> returned, and watch for the same one(s) when you do your desired SELECT.
>
> As long as all the result rows are unique…
>
> All of these workaround seem more expensive/complex than just adapting
> your code so it doesn't have to know the last row in advance.
>
> —Jens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Programming methodology (was DEF CON (wasL A license plate of NULL))

[Don V Nielsen]

If I were to have coded that junk (and I do see it too many times to
count), I would have coded it even junkier, as in

bool is_true (bool tf)
{
if (tf == true) return true; else return false;
}

If it's single statement following an if and that statement isn't beyond 80
characters, I will code it as a single line

bool is_true (bool tf)
{
if (tf == true) { return true;}
return false;
}

I would normally comment the method with something like

// SWEET MOTHER OF MOSES, WHO CODED THIS
// But since it is existing code, I have chosen not to touch it and kick
the can down the road.
// I don't want to be responsible for retesting the 97 million functions
that actually employ
// this piece of




On Tue, Aug 13, 2019 at 2:52 PM Keith Medcalf  wrote:

>
> On Tuesday, 13 August, 2019 13:17, Jose Isaias Cabrera 
> wrote:
>
> >James K. Lowden, on Tuesday, August 13, 2019 12:31 PM, wrote...
>
> >> On Mon, 12 Aug 2019 14:14:08 -0600 "Keith Medcalf", on
>
> >>> Perhaps I am just lazy but I see no point in engaging in extra
> >>> work for no advantage
>
> >> bool
> >> is_true (bool tf) {
> >> if (tf == true) {
> >> return true;
> >> }
> >> return false;
> >> }
>
> >Completely, completely off the subject, but since I see this code
> >here, and I have always wanted to ask this...
>
> >When I started programming, back in 1982, my teachers taught me to
> >match my end bracket to the same column where the beginning bracket
> >was.  And they explained the whole theory behind it, which I think
> >it's true, to today.  For example the above code, I would have
> >written it this way:'
>
> >bool is_true (bool tf)
> >{
> >if (tf == true)
> >{
> >return true;
> >}
> >return false;
> >}
>
> >Where, the brackets, begins/ends, would match the same column.  When
> >did this ideology change?  I see all of you smart programmers using
> >this non-column matching behavior, and I ask myself why?  Thoughts?
> >Or not. :-)  Thanks.
>
> It is a matter of taste I suppose, since there are numerous bits of
> software which can prettify various languages to a number of different
> formats.  The primary reason I have heard putting the opening brace on the
> same line is that it takes less space on the screen, and after all we can
> only afford 5 line monitors, am-I-right?
>
> Personally I like the format where the braces line up with the start of
> the statement that they belong to and appear on a line by themselves, and
> the contained block is indented.  Then again I can afford an absolutely
> humongous monitor that can display about 50 lines per page.
>
> Some people are severely allergic to white-space and so eliminate every
> non-required space/tab character all line-feeds/carriage-returns that are
> not within a quoted string and write their software as one big never-ending
> single line of code 40 miles long.
>
> There are also some wierd formats that some seem to like as well where
> they half-indent the braces and other such malarky.
>
> It is all a matter of taste and what you can see easily.  I also tend to
> use blocks around code that technically does not need them (as in the above
> example) because it makes it easier to see what is going on -- the visual
> appearance matches the parse tree generated by the compiler as it were.
> Only the folks that do not use blocks obviously are struck by decades old
> code editing errors that they did not intend (and we have had a few of
> those in the last couple of years where the "visual depiction" did not
> match the "computer generated parse tree" ...
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Grammar police

[Don V Nielsen]

Not to be argumentative with Keith, as I'm have the sinking feeling that
slitting my own throat would be a more pleasurable experience. But, here it
goes:

A *lexicon* is a list of words that belong to a particular language.

Sometimes, *lexicon* is used as another word for *thesaurus* (see below)

A *dictionary* is a list of words and phrases that are (or were) in common
usage, *together with their definitions* - so a dictionary is different
from a lexicon because a lexicon is a simple list and doesn't define the
words.

A *thesaurus* is a dictionary of synonyms (different words and phrases that
have the same or similar meaning).

Finally, for completeness, a *vocabulary* is a list of words that an
individual knows or uses regularly. Vocabulary is different from lexicon
because vocabulary is about what an individual or group of people know,
whereas lexicon is about the language itself.

On Fri, Jul 12, 2019 at 1:52 PM Keith Medcalf  wrote:

>
> >> I can highly recommend the book “Word by Word: The Secret Life of
> >> Dictionaries,” written by one of the editors at Merriam-Webster.
> >> The author spends much of her book illustrating why prescriptivist
> >> approaches to language are doomed to failure.
>
> Merriam-Webster does not publish a Dictionary.  The thing that most 'tards
> refer to as a "Dictionary" is actually a Lexicon, not a Dictionary.
>
> Now back to our regularly scheduled programming ...
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Grammar police

[Don V Nielsen]

Sorry. This was in the Quirks, Caveats page, #2.

On Thu, Jul 11, 2019 at 9:57 AM Don V Nielsen  wrote:

> " An application interact with the database engine using function calls,
> not be sending messages to a separate process or thread."
>
> "An applications [interacts] ..., [not by]...
>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Grammar police

[Don V Nielsen]

" An application interact with the database engine using function calls,
not be sending messages to a separate process or thread."

"An applications [interacts] ..., [not by]...
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Making blob as a sqlite database.

[Don V Nielsen]

Sorry to bother, Mohd. What is your use case? I mentioned this to
developers around me and they are intrigued. You are storing a database
file as blob in a database? We are curious as to the application. I am
assuming the database being stored is a collection of sensor or event data?

On Fri, Apr 26, 2019 at 5:48 PM Mohd Radzi Ibrahim 
wrote:

> Hi, is there a vfs that could be used to open a blob column as a database?
>
>
> thanks.
>
> Radzi.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Documentation correction

[Don V Nielsen]

On the heel of Tom's input is the fragment " then that name always refers
the explicitly declared column". Should that be " then that name always
refers *to* the explicitly declared column"?

On Mon, Apr 15, 2019 at 3:32 PM  wrote:

> Hi, I just wanted to point out a minor discrepancy in the docs below.
>
> https://www.sqlite.org/lang_createtable.html#rowid
>
> where it says:
>
> If a table contains a user defined column named "rowid", "oid" or
> "_rowid_", then that name always refers the explicitly declared column and
> cannot be used to retrieve the integer rowid value.
>
> Shouldn't it say at the end "unless that user defined column is an alias
> for the rowid."
>
> For example, you can retrieve the integer rowid value from a user defined
> column named "rowid" if it defined as the integer primary key:
>
> CREATE TABLE t(rowid integer primary key);
> insert into t values(15);
> select rowid, oid from t;
>
> Results:
> 15|15
>
> Thanks,
> Tom
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can I get help with db design for SQLite use?

[Don V Nielsen]

>  Do be aware that almost all of us are just users like you.

And be aware these guys are freakin brilliant. No lie.

On Tue, Apr 2, 2019 at 10:26 AM Simon Slavin  wrote:

> On 2 Apr 2019, at 3:48pm, Tom Browder  wrote:
>
> > I need help with a db design to be modeled for use with SQLite. The
> design uses foreign keys and I am just now using the foreign pragmas and
> other parts of SQLite to help me make it all work together.
> >
> > Is this list appropriate for presenting my design and requesting advice?
>
> Yes, you can post a schema here (paste it into your message, attachments
> are stripped) and ask for advice on whether you have the right tables,
> columns, indexes and keys.  Be prepared to get the advice that you're doing
> everything wrong and should rethink it all from scratch.
>
> Do be aware that almost all of us are just users like you.  We are not
> professional designers, we argue with one-another, and you can't sue us if
> we give you bad advice.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a tool to convert `where`s to equivalent `join`s?

[Don V Nielsen]

>   So for learning sake, is there a tool that converts a query using
`WHERE`
to a query (that yields identical results) using JOINs?

I'm not aware of a tool, but I would assert that not using a tool is for
learning sake. Recoding by hand is going to be your best learning
experience.

Note: there was a lot of good reading in the links you provided. One went
right down in logical order how Select statement is evaluated by SQL
engines. That there is the greatest tool for learning sake.


On Fri, Mar 1, 2019 at 2:15 AM Rocky Ji  wrote:

> In SQL world, generally
> ,
> not just SQLite
> <
> https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg114165.html
> >,
> a lot  of
> pros say that the constructs `from...inner join...on` and `from...where`
> are equivalent
>  >,
> and that query optimizer should build identical plans for either variant.
>
> Understanding `WHERE` is simple / straight-forward / intutive, to those
> 1. without mathematics background, or
> 2. used to imperative code and for-loops
>
> So for learning sake, is there a tool that converts a query using `WHERE`
> to a query (that yields identical results) using JOINs? Like a English ->
>  side-by-side translator.
>
> Cheers
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQlite.NET.chm

[Don V Nielsen]

Is the chm file on a server? I remember this problem from years ago...I
haven't used a chm file in a long time. Unblocking did not fix it while it
resided on the server. However, copying it locally and unblocking it did
work.

Hope this helps,
dvn

On Wed, Jan 9, 2019 at 11:29 AM Rizzuto, Raymond 
wrote:

>
> When I download SQlite.NET.chm from the internet, and open it, I can see
> the table of content but not the actual content.  It appears to be due to a
> block caused by downloading from the internet.
>
> Googling came up with this:
> https://community.spiceworks.com/topic/1961503-solved-windows-10-chm-help-files-showing-up-blank
>
> However, my company has apparently set our pc's so we can neither see nor
> change the block status of a file from the internet.  As a work-around, I
> used cygwin's cp command to copy the file, and the copy is not blocked.
>
> Since .chm is a compiled html file, I would imagine the concern is that
> there can be embedded (and malicious) scripts in the html.  Would it be
> possible to provide documentation in a safer format, such as a .pdf?
>
> 
> Ray Rizzuto
> Susquehanna International Group
> (610)747-2336 (W)
> (856)617-1638 (C)
> Sent from Outlook
>
>
> 
>
> IMPORTANT: The information contained in this email and/or its attachments
> is confidential. If you are not the intended recipient, please notify the
> sender immediately by reply and immediately delete this message and all its
> attachments. Any review, use, reproduction, disclosure or dissemination of
> this message or any attachment by an unintended recipient is strictly
> prohibited. Neither this message nor any attachment is intended as or
> should be construed as an offer, solicitation or recommendation to buy or
> sell any security or other financial instrument. Neither the sender, his or
> her employer nor any of their respective affiliates makes any warranties as
> to the completeness or accuracy of any of the information contained herein
> or that this message or any of its attachments is free of viruses.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Grouping guidance

[Don V Nielsen]

Two thumbs up on Igor. They say the quickest way to the correct answer is
to post the wrong answer on the internet. Hypothesis just proven.

On Thu, Dec 13, 2018 at 3:00 PM Igor Tandetnik  wrote:

> On 12/13/2018 3:41 PM, Igor Tandetnik wrote:
> > On 12/13/2018 3:27 PM, Don V Nielsen wrote:
> >> Making a mountain out of a mole hill, but isn't the solution more
> complex
> >> that that? The description has to be Foo & Bar. But if given the
> following,
> >> then the simple answer dies.
> >
> > select Request from MyTable group by Request
> > having sum(Description='Foo') > 0 and sum(Description='Bar') > 0;
>
> ... and sum(Description not in ['Foo', 'Bar']) = 0
>
> Add conditions to taste.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Grouping guidance

[Don V Nielsen]

Making a mountain out of a mole hill, but isn't the solution more complex
that that? The description has to be Foo & Bar. But if given the following,
then the simple answer dies.

create table requests (request,task,description);
insert into requests values
  ('REQ0090887','TASK0236753','Foo'),
  ('REQ0090887','TASK0234920','Bar'),
  ('REQ0090887','TASK0234921','Bar'),
  ('REQ0090887','TASK0237261','Foo'),
  ('REQ0090887','TASK0237261','Wrench'),
  ('REQ0086880','TASK0224045','Foo'),
  ('REQ0086880','TASK0224045','Wrench'),
  ('REQ0086903','TASK0224555','Bar'),
  ('REQ0086990','TASK0223977','Bar'),
  ('REQ0087061','TASK0226748','Foo'),
  ('REQ0087061','TASK0223810','Bar');

with exclude_requests as (
  select distinct request from requests where description not in ('Foo','Bar')
)
select request from requests
where request not in (select request from exclude_requests)
group by request
having count(distinct description) = 2
order by request


Doesn't one need to exclude the outliers first?

Have a good one




On Thu, Dec 13, 2018 at 1:44 PM Joseph L. Casale 
wrote:

> -Original Message-
> From: sqlite-users  On
> Behalf
> Of Igor Tandetnik
> Sent: Thursday, December 13, 2018 12:16 PM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] Grouping guidance
>
> > select Request from MyTable group by Request having count(distinct
> > Description) = 2
>
> Nice, I managed it with a join but this is far simpler.
>
> Thanks!
> jlc
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Displaying row count

[Don V Nielsen]

I really enjoy using JetBrains DataGrip. It connects to everything and has
great intellisense, find and replace tools, sql templates, all the goodies
a big IDE brings to the table.

On Wed, Oct 31, 2018 at 11:05 AM Dominique Devienne 
wrote:

> On Wed, Oct 31, 2018 at 3:55 PM Clemens Ladisch 
> wrote:
>
> > David Fletcher wrote:> Hi all,
> > > Is there a mode in the sqlite shell, or some fancy extension, that will
> > display a row
> > > number when outputting results?
> >
> > No.  You'd have to modify the shell, or add the row_number() window
> > function to the query.
>
>
> Unless you need to use bleeding edge new features, use some GUI SQLite tool
> instead.
>
> I use SQliteSpy myself for that. Gives me the time to process the query,
> how many rows,
> how many VM steps, Sort steps, that sort of thing. And shows me the result
> in tabular fashion,
> with color-coding based on value types (useful for a ducktyping DB like
> SQLite).
>
> Windows only, very simple, but very fast. Uses SQLite 3.21, but updated
> once in a while.
>
> I typically keep several queries I'm playing with, and CTRL-F9 the selected
> one to run it.
>
> Don't get me wrong, the CLI shell is great, but for many rows, or wide
> rows,
> or plain convenience, a GUI tool is often more practical IMHO. My $0.02.
> --DD
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regarding CoC

[Don V Nielsen]

I really feel for you, DRH. You tried covering all the bases,
unfortunately, you going to get tagged out at everyone because everybody is
referee and they all follow their own rules.This goes straight to your
first point, "What is professional to some might be unprofessional to
others." This akin to people saying, "What good is religion because I am a
good person." Oh, yah? By whose measure? Their own. And that measure varies
from person to person. You reached to higher higher authority through St
Benedictine. But others don't agree with the authority. So they are going
to insist that it gets thrown out because they don't agree.

Your third point is the heart of the problem, "having a written CoC is
increasingly a business requirement". I read this as, "you felt the
pressure from significant businesses". Being global opens a wasps nest. We
all have to be inclusive, but all groups demand recognition. In plain
English, "You are damned if you do and damned if you don't." In the end,
you cannot win this.

IMO, you're best bet is to fold your hand and keep your chips you have in
front of you. You have made your feelings known. For those who have read
the CoC and share in your community, we will accept CoC changes that
eliminate those elements others are insulted by. That's just who we are.
But we know where they came from.

Lastly, props to Rowan Worth. "> 23. Do not nurse a grudge. ::sigh:: DROP
TABLE grudges; I was amassing such a good collection :(" Totally awesome
comment. I wish there was a like button for that.

On Mon, Oct 22, 2018 at 10:29 AM Richard Hipp  wrote:

> On 10/22/18, Chris Brody  wrote:
> >> Looks like that happened this morning.
> >> https://news.ycombinator.com/item?id=18273530
> >
> > I saw it coming, tried to warn you guys in private.
>
> There is indeed a reactionary hate mob forming on twitter.  But most
> of the thoughtful commentators have been supportive, even if they
> disagree with the particulars of our CoC, They total get that we are
> not being exclusive, but rather setting a standard of behavior for
> participation in the SQLite community.
>
> I have tried to make that point clear in the preface to the CoC, that
> we have no intention of enforcing any particular religious system on
> anybody, and that everyone is welcomed to participate in the community
> regardless of ones religious proclivities.  The only requirement is
> that while participating in the SQLite community, your behavior not be
> in direct conflict with time-tested and centuries-old Christian
> ethics.  Nobody has to adhere to a particular creed.  Merely
> demonstrate professional behavior and all is well.
>
> Many detractors appear to have not read the preface, or if they read
> it, they did not understand it.  This might be because I have not
> explained it well.  The preface has been revised, months ago, to
> address prior criticism from the twitter crowd.  I think the current
> preface is definitely an improvement over what was up at first.  But,
> there might be ways of improving it further.  Thoughtful suggestions
> are welcomed.
>
> So the question then arises:  If strict adherence to the Rule of St.
> Benedict is not required, why even have a CoC?
>
> Several reasons:  First, "professional behavior" is ill-defined.  What
> is professional to some might be unprofessional to others.  The Rule
> attempts to clarify what "professional behavior" means.  When I was
> first trying to figure out what CoC to use (under pressure from
> clients) I also considered secular sources, such as Benjamin
> Franklin's 13 virtues (http://www.thirteenvirtues.com/) but ended up
> going with the Instruments of Good Works from St. Benedict's Rule as
> it provide more examples.
>
> Secondly, I view a CoC not so much as a legal code as a statement of
> the values of the core developers.  All current committers to SQLite
> approved the CoC before I published it.  A single dissent would have
> been sufficient for me to change course.  Taking down the current CoC
> would not change our values, it would merely obscure them.  Isn't it
> better to be open and honest about who we are?
>
> Thirdly, having a written CoC is increasingly a business requirement.
> (I published the currrent CoC after two separate business requested
> copies of our company CoC.  They did not say this was a precondition
> for doing business with them, but there was that implication.) There
> has been an implicit code of conduct for SQLite from the beginning,
> and almost everybody has gotten along with it just fine.  Once or
> twice I have had to privately reprove offenders, but those are rare
> exceptions.  Publishing the current CoC back in February is merely
> making explicit what has been implicit from the beginning.  Nothing
> has really changed.  I did not draw attention to the CoC back in
> February because all I really needed then was a hyperlink to send to
> those who were specifically curious.
>
> So then, why not use a more modern CoC?  I looked at 

Re: [sqlite] A SQL statement reformatter

[Don V Nielsen]

Slick. That line width slider feature is something we don't get using Poor
Man's Sql Formatter.

Thanks!

On Thu, Sep 27, 2018 at 7:03 PM Simon Slavin  wrote:

> For those times when you have to understand a poorly-formatted SQL
> statement:
>
> 
>
> I seem to prefer 'full' mode.
>
> 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

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

[Don V Nielsen]

This is what I was trying to remember. It is for calling LE Cobol, but it
demonstrates the use of the CEEENTRY and CEETERM macro that are used to
establish the environment without having to use a LE C stub program. I used
to use the Cobol stub method in the past.

https://www.ibm.com/support/knowledgecenter/en/SSLTBW_2.1.0/com.ibm.zos.v2r1.ceea400/priasm.htm



On Thu, Aug 30, 2018 at 4:13 PM David Jackson 
wrote:

> Thanks for the various replies to this question.
> I have my link working now. It starts off wit a basic 3 line c program (to
> establish the LE enclave) that then calls an Asm program that passes the
> SQL that then loads and branches to a c program to do the grunt work. There
> is an example IBM program that does something similar.
> That called c program  then returns and is re-invoked any number of times
> by the asm caller.  I now only open and close the db on the first and final
> call.
> So all is good as far as that goes .
>
> The challenge is to now determining a means of getting the query result set
> back to the calling asm program in a usable format.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Don V Nielsen]

"Having successfully ported sqlite to z/OS Unix as a 32 bit app"

Totally Awesome! Do I have a solution? No. But I'll bet John McKown will. I
believe he is a guru with the mainframe.

It is not SqlLite. It is that communication mechanism between the non-LE
program calling into the LE environment. Are you saying the LE is loading
and not unloading. I would guess that it is, and it is that which is
closing the connection. I'm sorry, but I'm 10+ years past working with
mainframes. But I recall having to do something special when calling
LE-Cobol from assembler. There was something that needed to be communicated
to say "Get up and stay up until I tell you to close", otherwise, you are
continually loading and unloaded LE with every call.

Wish I could be more helpful,
dvn

On Wed, Aug 29, 2018 at 5:26 PM David Jackson 
wrote:

> Having successfully ported sqlite to z/OS Unix as a 32 bit app, I am now
> looking at a c program to make SQL calls to this.
> Starting with an Assembler routine that runs within z/OS (not Unix), which
> is not LE (Language Environment)enabled, we then call a c routine (numerous
> times) that is LE enabled. That is all working fine and making good SQL
> calls to sqlite. the c program then returns back to the upper assembler
> calling program. The problem is that the c routines is opening, issuing the
> SQL and closing on each invocation.
>
> Now this may be a dumb question, so apologies up front.
> Is there any way that the c program can open the sqlite db initially on the
> first call and keep it open after it returns back to the calling program
> until a final call at which point it will issue the sqlite3_close.
>
> Again - sorry if this was a dumb question.
>
> Thanks
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Add Column with "If Not Exists"

[Don V Nielsen]

This makes me feel there is a lot of pain coming in the future.

Given an update statement for n dbs of unknown state,
When a db lacks columns necessary to successfully execute the sql
Then add the columns to the db

I'm trying to imagine how to keep n remote dbs in a known state, say z,
when various updates sent to them put result in states a, b, c, d...z. How
do you keep all the db states sync'd when update 1 could create a column
but it fails or was not sent to all n dbs, and update 2 could create a
column but it fails or was not sent to all n dbs? How do you know what
state each remote db is in, or isn't in?

On Wed, Aug 1, 2018 at 10:46 AM R Smith  wrote:

> On 2018/08/01 5:29 PM, Charles Leifer wrote:
> > You can simply use:
> >
> > PRAGMA table_info('my_table')
> >
> > To get a list of columns, which you can check against and then
> > conditionally add your column.
>
> Aye, but during a script in SQL-only you don't have that luxury. One
> could also use a similar pragma to check if a table exists before
> creating it, but the SQL for:
> CREATE TABLE IF NOT EXISTS... makes it possible to add things without
> failing mid-script with no programmatic help (and to be blunt, much
> easier and nicer).
>
> That said, I never add columns this way -  but that might only be
> precisely because its IF NOT EXISTS does not exist. So...
>
> +1
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Back on-line. Was: Mailing list shutting down...

[Don V Nielsen]

Any thought, comments, or observations of using Slack?

On Fri, Jun 15, 2018 at 1:25 PM J. King  wrote:

> On June 15, 2018 12:17:31 PM EDT, dmp 
> wrote:
> >> Mailing lists are now back on-line and once again accepting
> >> subscriptions.  I have implemented measures to block the subscription
> >> robots and to better log subscription activity to better detect
> >future
> >> mischief.
> >
> >> I consider this to be a stop-gap measure that will buy me some time
> >> to implement and test a better log-term solution. .
> >
> >> D. Richard Hipp
> >> d...@sqlite.org
> >
> >As a comment, again with past post with regard to Mailing List.
> >
> >This mailing list is a very informative, simple, and a conveniant
> >method to disperse information in a bulk format. A change to a web
> >interface, (forum, other), that requires a login each day is most
> >likely going to push me away.
> >
> >Hope a fix can be accomplished.
> >
> >danap.
> >
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> Opinions on this have made the rounds here before, but I will reiterate
> that I feel the same. I would be especially sad to see this discussion list
> move to Discourse (as was suggested and apparently explored earlier this
> week), as I find that software very unresponsive and difficult to use on
> Firefox for Android, and I do much of my reading of this list on the go
> (like right now).
>
>
> --
> J. King
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Back on-line. Was: Mailing list shutting down...

[Don V Nielsen]

 > Do we need any further evidence that the heart of man is deceitful above
all things, and desperately wicked?

Per Keith Metcalf:  "The fact that there's a Highway to Hell but only a
Stairway to Heaven says a lot about anticipated traffic volume."


On Thu, Jun 14, 2018 at 12:38 PM R Smith  wrote:

> On 2018/06/14 5:58 PM, Richard Hipp wrote:
> >
> > Do we need any further evidence that the heart of man is
> > deceitful above all things, and desperately wicked?
>
> That's only the heart of woman...
>
>
> The heart of man is much much worse.
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Newbie help

[Don V Nielsen]

>   the monitoring program is constantly launching sqlite3.exe with new
commands or files of commands

So the monitoring program has the ability to interact with the outside
world when something changes? Can you write a service that listens for
activity from our monitoring program, and the service is connected to the
sqlite db updating it with the activity details?

So many languages can connect to sqlite db's: c, c#, ruby, php, python...
Write the service using your favorite language.


On Fri, Jun 8, 2018 at 12:49 PM Stephen Chrzanowski 
wrote:

> SQLite is a library that is built into applications.  Other than
> sqlite3.exe, its not a standalone 'thing', if you will.  If your
> application can't be rebuilt to directly write to a SQLite database, then
> the only alternatives I can think of are
> - to keep doing what you're doing, or,
> - look at your monitoring application to see if it has some kind of API
> that can talk to an external application or service and write the
> application or service to handle the requests, or
> - If your application can pipe its findings to a text file, an application
> that watches for these text files changes could be written to deal with the
> output of your monitoring program.
>
> On Fri, Jun 8, 2018 at 1:26 PM,  wrote:
>
> >
> >  Hello -
> >
> > I've been following for a while, a lot of very intelligent
> > people here, and not a lot of fuss!!! Very nice, my compliments to
> > all!!
> >
> > I'm just learning SQLite and using it to collect alarm values from a
> > monitoring system. The monitoring system can't access the SQLite file
> > directly, so I've been using sqlite3.exe as the intermediate.
> >
> > Since I'm
> > making many access to the database the monitoring program is constantly
> > launching sqlite3.exe with new commands or files of commands, very
> wasteful
> > of resources. (Windows 7)
> >
> > Is there a platform/program that can be run as a
> > SQLite "front end" that could be launched once and take commands from
> > another program and pass back results, or is there a way to do that with
> > sqlite3.exe? I've looked around the web and not found anything, so
> > far.
> >
> > Any guidance is greatly appreciated, thanks for your time!!
> >
> > Leland
> >
> > ___
> > 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

Re: [sqlite] random rows

[Don V Nielsen]

??

SELECT * FROM table WHERE id IN (SELECT id FROM table ORDER BY RANDOM() LIMIT x)

Maybe. It is more memory efficient then trying to sort the entire lot of data.


On Thu, May 31, 2018 at 7:13 PM Torsten Curdt  wrote:

> I need to get some random rows from a large(ish) table.
>
> The following seems to be the most straight forward - but not the fastest.
>
>   SELECT * FROM table ORDER BY random() limit 200
>
> Is there a faster/better approach?
>
> cheers,
> Torsten
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lots of enhancements coming to version 3.24.0 - please test

[Don V Nielsen]

Two thumbs up, Dan. Thanks for the clarification.

On Tue, May 8, 2018 at 11:11 AM, Dan Kennedy <danielk1...@gmail.com> wrote:

> On 05/08/2018 11:04 PM, Don V Nielsen wrote:
>
>> " UPDATE <https://www.sqlite.org/draft/lang_update.html> avoids writing
>>>
>> database pages that do not actually change. For example, "UPDATE t1 SET
>> x=25 WHERE y=?" becomes a no-op if the value in column x is already 25."
>>
>> I have a ridiculous assertion concerning this, but please here me out.
>>
>> Given rows where y = ? are true
>> When an update is made to those rows
>> Then I expect the row ChangeDate column to be set to the current date/time
>> (dt)
>>
>> Even if x = 25, would I want it changed to show that thew row was touched
>> by the update ran on dt.
>>
>
> Triggers will still fire. Auth callbacks will still be made. And
> sqlite3_total_changes() will still be updated. The change is at quite a low
> level - after doing all the work to compose the new database record, if
> SQLite notices that it is exactly the same as the record being replaced,
> the IO is just omitted.
>
> Does that assuage your concerns?
>
> Dan.
>
>
>
>
>
>   If it is not touched, then I may be left
>> investigating why counts against dt to not equal the anticipated number of
>> changes. For example, as part of my qc procedure, I would do a count to
>> see
>> how many rows should be changed before running the update. The qc count
>> would be base on y=?. When the update executes, it will not reflect the
>> number of updates, no would a post-update frequency count reflect it
>> because the ChangeDate on those rows would not be updated.
>>
>> I realize my pre|post-update now has to be y = ? AND x <> 25. But I wonder
>> what else might be affected by this. It has a complexity to automated qc
>> procedures.
>>
>> Just my thoughts.
>> dvn
>>
>>
>>
>> On Tue, May 8, 2018 at 9:42 AM, Richard Hipp <d...@sqlite.org> wrote:
>>
>> There are a lot of important changes in the SQLite code base since the
>>> previous release.  See
>>> https://www.sqlite.org/draft/releaselog/current.html for the latest
>>> summary.
>>>
>>> All of our legacy tests pass and many new test cases have been added.
>>> Nevertheless, your testing of SQLite in real-world applications is
>>> greatly appreciated.  Please consider downloading the latest
>>> pre-release snapshot (https://www.sqlite.org/download.html) and giving
>>> it a whirl.
>>>
>>> Report any problems or concerns to this mailing list, or directly to me.
>>>
>>> Thanks.
>>> --
>>> D. Richard Hipp
>>> d...@sqlite.org
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lots of enhancements coming to version 3.24.0 - please test

[Don V Nielsen]

> " UPDATE  avoids writing
database pages that do not actually change. For example, "UPDATE t1 SET
x=25 WHERE y=?" becomes a no-op if the value in column x is already 25."

I have a ridiculous assertion concerning this, but please here me out.

Given rows where y = ? are true
When an update is made to those rows
Then I expect the row ChangeDate column to be set to the current date/time
(dt)

Even if x = 25, would I want it changed to show that thew row was touched
by the update ran on dt. If it is not touched, then I may be left
investigating why counts against dt to not equal the anticipated number of
changes. For example, as part of my qc procedure, I would do a count to see
how many rows should be changed before running the update. The qc count
would be base on y=?. When the update executes, it will not reflect the
number of updates, no would a post-update frequency count reflect it
because the ChangeDate on those rows would not be updated.

I realize my pre|post-update now has to be y = ? AND x <> 25. But I wonder
what else might be affected by this. It has a complexity to automated qc
procedures.

Just my thoughts.
dvn



On Tue, May 8, 2018 at 9:42 AM, Richard Hipp  wrote:

> There are a lot of important changes in the SQLite code base since the
> previous release.  See
> https://www.sqlite.org/draft/releaselog/current.html for the latest
> summary.
>
> All of our legacy tests pass and many new test cases have been added.
> Nevertheless, your testing of SQLite in real-world applications is
> greatly appreciated.  Please consider downloading the latest
> pre-release snapshot (https://www.sqlite.org/download.html) and giving
> it a whirl.
>
> Report any problems or concerns to this mailing list, or directly to me.
>
> Thanks.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JDBC driver experience

[Don V Nielsen]

1. Xerial
2. Couple years?
3. None. But I am just a dumb user. I use JetBrains IDE's (RubyMine, Rider,
Datagrip). The IDE's provide direct access to the datasource for looking at
the db, manipulating data, manipulating tables. That sort of stuff. Never
once have I had an issue with the driver crashing, or munging data, or
being a general kludge. It has been effective.
4.
5. I use the driver's through other products. I don't program with them
directly. I'm not at that level of smart.

dvn - wish I could be greater help

On Wed, Apr 18, 2018 at 2:34 PM, Richard Hipp  wrote:

> Are you using SQLite with JDBC?  If so, can you please answer a few
> questions below?
>
> You can post on this mailing list or send your reply directly to me.
>
> 1. Which JDBC are you using?
> 2. For how long have you been using it?
> 3. What issues you had with this driver?
> 4. What advise do you have for avoiding problems in this driver?
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Are you getting spam when you post to sqlite-users ?

[Don V Nielsen]

And my dating success rate has dropped dramatically

On Wed, Apr 18, 2018 at 8:54 AM, José María Mateos 
wrote:

> On Tue, Apr 17, 2018, at 17:39, Simon Slavin wrote:
> > Dear list-posters,
> >
> > Are you getting a new dating-spam each time you post to this list ?  If
> > you are, please post a brief follow-up to this message.  Please do /not/
> > include any details about the spam, its headers, or the person it's
> > apparently from.  Just a "me too" until I say I have seen enough
> > responses.
>
> For what I understand, this exact behavior is happening right now on the
> r-help mailing list too. People were wondering if addresses were being
> scrapped from nabble.com or something similar.
>
> Cheers,
>
> --
> José María (Chema) Mateos
> https://rinzewind.org/blog-es || https://rinzewind.org/blog-en
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Documentation update

[Don V Nielsen]

I'm adding to my bucket list meeting some of you in person.

On Mon, Apr 9, 2018 at 5:58 AM, Niall O'Reilly  wrote:

> On 9 Apr 2018, at 11:02, R Smith wrote:
>
> > Gentlemen - shall we call pistols at dawn to settle this?
> > Or my favourite duel:  Face-pulling at midnight.  :)
>
> 8-)
>
> /Niall
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Documentation update

[Don V Nielsen]

"to be", or not "to be"  :)

On Sun, Apr 8, 2018 at 7:08 PM, Don V Nielsen <donvniel...@gmail.com> wrote:

> "For the purposes of unique indices, all NULL values are considered [to]
> different from all other NULL values and are thus unique."
>
> I think it should read "...NULL value are considered different..."
>
> https://www.sqlite.org/lang_createindex.html
>
> dvn
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Documentation update

[Don V Nielsen]

"For the purposes of unique indices, all NULL values are considered [to]
different from all other NULL values and are thus unique."

I think it should read "...NULL value are considered different..."

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

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

Re: [sqlite] SQLite equivalent of SQL Over(Partition)

[Don V Nielsen]

That seems like an odd application of OVER (Partition by). Is there some
performance reason one would want to do DISTINCT OVER (PARTITION BY)
instead of a simple GROUP BY Sites.Customer, Sites.Digit, Count()?


On Fri, Apr 6, 2018 at 12:20 PM, Simon Slavin  wrote:

> On 5 Apr 2018, at 11:41am, DThomas  wrote:
>
> > Select DISTINCT Sites.Customer, Sites.Digit,
> > Count(TblContractTasks.TaskNumber)
> > OVER (PARTITION BY Sites.Digit)  As TaskCount
> > FROM TblContractTasks INNER Join (Sites INNER Join TblContractDetails On
> > Sites.Digit = TblContractDetails.SiteDigit) On
> > TblContractTasks.ContractNumber = TblContractDetails.ContractNumber
> > WHERE (Sites.Consultant='xx' ) ORDER BY Sites.Digit
>
> Instead of PARTITION BY use GROUP BY .  See "with COUNT function" example
> here:
>
> 
>
> I think everything else used will continue to work.
>
> 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

Re: [sqlite] non-returned column aliases for repeating expressions?

[Don V Nielsen]

I'm not the sharpest tool in the shed, here, but a couple of observations /
ideas. First, why in the ORDER clause use _3 and then "id" instead of _1?
Using one alias and not the other is inconsistent and could be confusing.

Personally, I would have did the following. Acquire the data using the CTE.
The select what is needed and order it from the CTE. Being script
generated, I find it really easy to create the primary select as a template
and insert the select within the CTE.

WITH data AS (
SELECT
  "id"AS _1,
  "json"  AS _2,
  json_extract(json, '$.foo') AS _3
FROM "testing"
)
SELECT *
FROM data
where _3 < 50
ORDER BY _3 DESC, _1


I read a lot about efficiency with sqlite here. I guess I've become
complacent about it. I find sqlite so fast that efficiency is distracting.
Just me.

dvn

On Sat, Mar 24, 2018 at 11:31 AM, Wout Mertens 
wrote:

> Hi list,
>
> I often have (autogenerated) queries like
>
> SELECT "id" AS _1,"json" AS _2 FROM "testing"
> WHERE json_extract(json, '$.foo') < 50
> ORDER BY json_extract(json, '$.foo') DESC,"id"
>
> where the json_extract(json, '$.foo') is indexed
>
> I wonder if it would be more efficient to write instead
>
> SELECT _1, _2 FROM (
> SELECT "id" AS _1,"json" AS _2, json_extract(json, '$.foo') AS _3 FROM
> "testing"
> WHERE _3 < 50
> ORDER BY _3 DESC,"id")
>
> , so aliasing the common expression? Or is that just extra work for SQLite?
>
> If I did this, I would want to do it always, also when I'm using the
> expression only once (so only ordering or only filtering)
>
> Wout.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can this be done with SQLite

[Don V Nielsen]

Cecil, you need to make a backup of the values before they are altered, and
then alter them from the backup. That way, by the time you have come full
circle, you are not working with the changed values.


On Mon, Jan 22, 2018 at 4:11 PM, Cecil Westerhof 
wrote:

> 2018-01-22 23:07 GMT+01:00 Igor Tandetnik :
>
> > On 1/22/2018 4:36 PM, Cecil Westerhof wrote:
> >
> >>
> >> When I do this, I get:
> >> sqlite> SELECT *
> >> ...> FROM   playYouTubeVideo
> >> ...> WHERE  key BETWEEN '1' AND '5'
> >> ...> ;
> >> 1|1.0
> >> 2|2.0
> >> 3|3.0
> >> 4|4.0
> >> 5|5.0
> >>
> >> [snip]
> >>
> >> sqlite> SELECT *
> >> ...> FROM   playYouTubeVideo
> >> ...> WHERE  key BETWEEN '1' AND '5'
> >> ...> ;
> >> 1|2.0
> >> 2|3.0
> >> 3|4.0
> >> 4|5.0
> >> 5|2.0
> >>
> >> But I want the last one needs to be 1.0.
> >>
> >
> > Something along these lines, perhaps:
> >
> > update playYouTubeVideo set key=char(61440+unicode(key));
> > update playYouTubeVideo set key=case when key=char(61440+unicode('1'))
> > then '5' else char(unicode(key)-61440-1) end;
> >
>
> ​This also expects the values to be constant. But what I want is that the
> record with key 1 gets the value from key 2, with key 2 from key 3, …
>
> --
> Cecil Westerhof
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Infinite loop when updating indexed with case and where clause

[Don V Nielsen]

Off topic question regarding fix: "0!=(wctrlFlags & WHERE_ONEPASS_MULTIROW)"

Is there a performance bonus or compiler optimization if one compares a
target constant to a source condition versus comparing a target condition
to a source constant, as in "(wctrlFlags & WHERE_ONEPASS_MULTIROW)!=0"?

The only reason I ask is that it is the opposite of how I code (in any
language) and how expect to read things.

Just curious
dvn

On Tue, Jan 16, 2018 at 7:59 AM, Richard Hipp  wrote:

> On 1/16/18, Harald Klimach  wrote:
> >
> > here is a weird behavior I observe with the following kind of setup:
> >
>
> Bug fix is in.  You can download the latest snapshot from
> https://sqlite.org/download.html.
>
> Alternatively, you can apply the patch at
> https://www.sqlite.org/src/info/feb2c2b6f66b0f45 to whatever prior
> version of SQLite that you happen to be using.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Move to Github!!?

[Don V Nielsen]

> What's not so easy to replace is the Git integration in my editor (Visual
Studio Code)

Same here, but I use JetBrains products. I put a bug in the ear of
JetBrains. At least its something.

Have a good one, all

On Tue, Dec 26, 2017 at 9:27 AM, J. King  wrote:

> I use Git, but I'm not attached to it. I run my own publicly-accessible
> remote (using Gitea), but that would be completely replaceable with Fossil
> (which I am very impressed by).
>
> What's not so easy to replace is the Git integration in my editor (Visual
> Studio Code) which allows me to easily perform basic operation like commit,
> push, pull, and rebase.
>
> Even just to the ability to review diffs and perform checkins in my editor
> would be enough, but I doubt it will happen anytime soon, if ever, and I
> don't have the expertise required to hack it on myself.
>
> Until the landscape changes (or someone can suggest suitable Windows
> software), I will continue to admire Fossil from afar.
>
> On December 26, 2017 10:08:08 AM EST, Damien Sykes <
> dam...@dcpendleton.plus.com> wrote:
> >Hi,
> >This is a question I have asked myself many times (I.E. Git projects
> >moving
> >to Fossil).
> >GitHub is well known and boasts over 74 million repositories, yet
> >Fossil,
> >which both hosts and utilises one of the most well-known database
> >systems in
> >the world, I doubt can count a thousand. Even the ChiselApp hosting
> >platform
> >hosts a mere 360 public repositories, Hydra hosts 11, WanderingHorse
> >hosts
> >23, outside of which lie Fossil itself, the Fossil book, SQLite and
> >friends
> >(5 publicly accessible repositories in all), and TCL and friends (7
> >repositories), making a total of 408. Add SQLite private repositories,
> >and
> >private repositories that I host, have access to or otherwise generally
> >know
> >exist, and I come up with an estimate of roughly 470 repositories. Of
> >course
> >this is not an accurate statistic since it may exclude more private
> >repositories, and definitely excludes any local repositories (I for one
> >have
> >about a dozen Git repositories as Fossil repositories).
> >While I am making every attempt to try to persuade friends towards
> >Fossil,
> >they are also choosing Git. Looks to me like the only people who seem
> >to use
> >Fossil are those who are most associated with it, which is a real
> >shame.
> >The only advantage I can see with GitHub is that it's the source code
> >Twitter equivalent. Everybody's repository is in one place. As long as
> >you
> >know the username and repository name you know the full repository URL,
> >and
> >you don't have to worry about server administration. With Fossil, if I
> >wanted to make it feel like github, I.E. address.tld/user/repo, I would
> >have
> >to script it and serve it via a webserver rather than Fossil's own
> >server,
> >two processes which I am not at all skilled enough, at least at the
> >moment,
> >to undertake. To give you an example, I am currently having to run two
> >systems, one for my website and one for Fossil, so that they can both
> >work
> >on port 80, because I know nothing about networking in order to
> >understand
> >IP addresses, ports and connections in the way I'd need to get a server
> >and
> >Fossil to run on port 80 on the same machine, nor do I know enough
> >about
> >webservers to be able to get it to work with CGI. In fact I know so
> >little
> >that I follow installation guides to the letter and have to do a
> >complete
> >fresh server reset and reinstall from scratch when something goes wrong
> >
> >because I haven't a clue how to fix it. If I'm to be totally honest at
> >the
> >moment I'm even beginning to doubt my own software development skills.
> >If there were a Fossil-based github-like system, and both Fossil and
> >the
> >hosting system were well promoted, Fossil may or may not become the
> >norm.
> >Having said that, the advantage of Fossil over Git is that, thanks to
> >the
> >webserver, you can easily look at your changes in a laid-out website,
> >even
> >on your own machine. I've many a time found myself importing Git
> >repositories into Fossil just to look at the timeline. Plus, you don't
> >have
> >to worry about complicated concepts like pull requests, synchronising
> >forks
> >and submodules etc. Though it doesn't matter what VCS I use I always
> >seem to
> >come across, and struggle with, the concept of branching and merging!
> >In any case, my incompetencies aside. People seem to be slowly moving
> >away
> >from SourceForge in favour of GitHub. If only we could make the same
> >revolution with Fossil! Ironically when I first came across a site
> >called
> >FossHub I actually thought that was an attempt to make a Fossil-based
> >GitHub. Seems that isn't the case after all.
> >Cheers.
> >Damien.
> >-Original Message-
> >From: Richard Hipp
> >Sent: Tuesday, December 26, 2017 1:10 PM
> >To: SQLite mailing list
> >Cc: shekharreddy.k...@gmail.com
> >Subject: Re: [sqlite] Move to 

Re: [sqlite] Best way to develop a GUI front-end

[Don V Nielsen]

Ruby on Rails

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

> > -Original Message-
> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On
> > Behalf Of Bart Smissaert
> > Sent: Monday, November 13, 2017 6:52 PM
> > To: SQLite mailing list 
> > Subject: Re: [sqlite] Best way to develop a GUI front-end
> >
> > As you are familiar with VBA I can see two other options:
> >
> > 1. Use Christian Werner's ODBC driver from Access (or Excel):
> > http://www.ch-werner.de/sqliteodbc/
> >
> > 2. Use Olaf Schmidt's COM dll with Access (or Excel):
> > http://www.vbrichclient.com/#/en/About/
> >
> > RBS
>
> Better yet, use the official SQLite provider for the .NET languages. This
> will work with any .NET/Visual Studio language (C#/F#/VB):
>
> http://system.data.sqlite.org/
>
> Building GUI apps with Visual Studio is very easy, and you can get the
> community edition of Visual Studio for free.
>
> https://www.visualstudio.com/vs/community/
>
> --
> Bill Drago
> Staff Engineer
> L3 Narda-MITEQ
> 435 Moreland Road
> Hauppauge, NY 11788
> 631-272-5947 / william.dr...@l3t.com
> CONFIDENTIALITY NOTICE: This email and any attachments are for the sole
> use of the intended recipient and may contain material that is proprietary,
> confidential, privileged or otherwise legally protected or restricted under
> applicable government laws. Any review, disclosure, distributing or other
> use without expressed permission of the sender is strictly prohibited. If
> you are not the intended recipient, please contact the sender and delete
> all copies without reading, printing, or saving.
>
> Beginning April 1, 2018, L3 Technologies, Inc. will discontinue the use of
> all @L-3Com.com email addresses. To ensure delivery of your messages to
> this recipient, please update your records to use william.dr...@l3t.com.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple Search using LIKE or something else

[Don V Nielsen]

A good StackOverflow post on adapting in C# for use in Linq queries:
https://stackoverflow.com/questions/24229785/sqlite-net-sqlitefunction-not-working-in-linq-to-sql/26155359#26155359


On Fri, Nov 10, 2017 at 8:31 AM, Don V Nielsen <donvniel...@gmail.com>
wrote:

> Brent Ashley has a nice blog post on integrating your languages regular
> express processing into sqlite: http://www.ashleyit.
> com/blogs/brentashley/2013/11/27/using-regular-expressions-with-sqlite/
>
>
> On Fri, Nov 10, 2017 at 8:15 AM, Stephen Chrzanowski <pontia...@gmail.com>
> wrote:
>
>> I'd take regular expressions, sure, but, how do you get SQLite syntax
>> to take that on?
>>
>> On Fri, Nov 10, 2017 at 8:45 AM, Don V Nielsen <donvniel...@gmail.com>
>> wrote:
>> > Assuming you are looking for "Abc" or "Def" anywhere in the argument,
>> how
>> > about regular expression? (Abc|Def)
>> >
>> > On Thu, Nov 9, 2017 at 3:00 PM, Peter Da Silva <
>> > peter.dasi...@flightaware.com> wrote:
>> >
>> >> On 11/9/17, 2:51 PM, "sqlite-users on behalf of Stephen Chrzanowski" <
>> >> sqlite-users-boun...@mailinglists.sqlite.org on behalf of
>> >> pontia...@gmail.com> wrote:
>> >> > I've added a simple filter function that uses SQLites LIKE operator,
>> but
>> >> I'd like something a little bit more advanced.  Right now, the SQL
>> code is
>> >> like:
>> >> >
>> >> > select * from Events where Title like '%Abc%Def%'
>> >> >
>> >> >  This works if the Title is AbcRfeDef, but would fail with FedRfeAbc.
>> >> >
>> >> > I've thought about doing some kind of delimiter, then have the code
>> >> generate the SQL code by just looping through the keywords and
>> generate the
>> >> "or Title like '%keyword%'" statement (With appropriate escaping), but
>> that
>> >> just smells bad to me.
>> >>
>> >> The best way to do it is not to use complex keys, but if you have to
>> (say
>> >> because the input is free form) then generating
>> >>
>> >> SELECT * FROM EVENTS WHERE title LIKE ‘%Abc%’ AND title LIKE ‘%Def%’;
>> >>
>> >> is probably the only option.
>> >>
>> >>
>> >> ___
>> >> 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
>>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple Search using LIKE or something else

[Don V Nielsen]

Brent Ashley has a nice blog post on integrating your languages regular
express processing into sqlite:
http://www.ashleyit.com/blogs/brentashley/2013/11/27/using-regular-expressions-with-sqlite/


On Fri, Nov 10, 2017 at 8:15 AM, Stephen Chrzanowski <pontia...@gmail.com>
wrote:

> I'd take regular expressions, sure, but, how do you get SQLite syntax
> to take that on?
>
> On Fri, Nov 10, 2017 at 8:45 AM, Don V Nielsen <donvniel...@gmail.com>
> wrote:
> > Assuming you are looking for "Abc" or "Def" anywhere in the argument, how
> > about regular expression? (Abc|Def)
> >
> > On Thu, Nov 9, 2017 at 3:00 PM, Peter Da Silva <
> > peter.dasi...@flightaware.com> wrote:
> >
> >> On 11/9/17, 2:51 PM, "sqlite-users on behalf of Stephen Chrzanowski" <
> >> sqlite-users-boun...@mailinglists.sqlite.org on behalf of
> >> pontia...@gmail.com> wrote:
> >> > I've added a simple filter function that uses SQLites LIKE operator,
> but
> >> I'd like something a little bit more advanced.  Right now, the SQL code
> is
> >> like:
> >> >
> >> > select * from Events where Title like '%Abc%Def%'
> >> >
> >> >  This works if the Title is AbcRfeDef, but would fail with FedRfeAbc.
> >> >
> >> > I've thought about doing some kind of delimiter, then have the code
> >> generate the SQL code by just looping through the keywords and generate
> the
> >> "or Title like '%keyword%'" statement (With appropriate escaping), but
> that
> >> just smells bad to me.
> >>
> >> The best way to do it is not to use complex keys, but if you have to
> (say
> >> because the input is free form) then generating
> >>
> >> SELECT * FROM EVENTS WHERE title LIKE ‘%Abc%’ AND title LIKE ‘%Def%’;
> >>
> >> is probably the only option.
> >>
> >>
> >> ___
> >> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple Search using LIKE or something else

[Don V Nielsen]

Assuming you are looking for "Abc" or "Def" anywhere in the argument, how
about regular expression? (Abc|Def)

On Thu, Nov 9, 2017 at 3:00 PM, Peter Da Silva <
peter.dasi...@flightaware.com> wrote:

> On 11/9/17, 2:51 PM, "sqlite-users on behalf of Stephen Chrzanowski" <
> sqlite-users-boun...@mailinglists.sqlite.org on behalf of
> pontia...@gmail.com> wrote:
> > I've added a simple filter function that uses SQLites LIKE operator, but
> I'd like something a little bit more advanced.  Right now, the SQL code is
> like:
> >
> > select * from Events where Title like '%Abc%Def%'
> >
> >  This works if the Title is AbcRfeDef, but would fail with FedRfeAbc.
> >
> > I've thought about doing some kind of delimiter, then have the code
> generate the SQL code by just looping through the keywords and generate the
> "or Title like '%keyword%'" statement (With appropriate escaping), but that
> just smells bad to me.
>
> The best way to do it is not to use complex keys, but if you have to (say
> because the input is free form) then generating
>
> SELECT * FROM EVENTS WHERE title LIKE ‘%Abc%’ AND title LIKE ‘%Def%’;
>
> is probably the only option.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to create primary key from two another PK's?

[Don V Nielsen]

Just asking some leading questions. You have students. And students have
work pieces. You are then creating a list "uniqueworkpiece" showing the
work pieces associated to each student. Your primary key will ensure the
uniqueness of the student to work piece.

Do you also need to ensure that the work piece can have one and only one
student? Do you need a unique constraint on both student>work piece and
work piece>student?

On Sat, Oct 21, 2017 at 12:09 PM, Igor Korot  wrote:

> Hi,
>
>
>
> On Oct 21, 2017 5:18 AM, "csanyipal"  wrote:
>
> I try to follow advices and modify my database so it is now like this:
> *CREATE TABLE "student" (
>   "idnum" TEXT NOT NULL CONSTRAINT "pk_student" PRIMARY KEY,
>   "studentname" TEXT NOT NULL,
>   "teachinglang" VARCHAR(2) NOT NULL,
>   "grade" TINYINT,
>   "classname" VARCHAR(1) NOT NULL,
>   "formmaster" TEXT NOT NULL
> );
>
> CREATE TABLE "workpiecelist" (
>   "id" INTEGER CONSTRAINT "pk_workpiecelist" PRIMARY KEY AUTOINCREMENT,
>   "grade" TINYINT,
>   "quarter" TINYINT,
>   "workpiecenamehu" TEXT NOT NULL,
>   "workpiecenamesr" TEXT NOT NULL
> );
>
> CREATE TABLE "uniqueworkpiece" (
>   "student" TEXT NOT NULL REFERENCES "student" ("idnum"),
>   "workpiece_list" INTEGER NOT NULL REFERENCES "workpiecelist" ("id"),
>   "mark" TINYINT,
>   "cause" TEXT NOT NULL,
>   CONSTRAINT "pk_uniqueworkpiece" PRIMARY KEY ("student", "workpiece_list")
> );
>
>
> You can as well drop the PK on the table above and live with just 2 FK
> constraints. If you really want a PK on this table you can create a dummy
> field and make it a primary key.
>
> Other than that you should be good.
>
> Thank you.
>
>
> CREATE INDEX "idx_uniqueworkpiece__workpiece_list" ON "uniqueworkpiece"
> ("workpiece_list")*
>
> What do you think, did I achieve what I want?
>
>
>
> -
> Best, Pál
> --
> Sent from: http://sqlite.1065341.n5.nabble.com/
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with strftime('%s') < strftime('%s')

[Don V Nielsen]

> Of course now I think about it, strftime always being a string means that
> you either should avoid the '%s' conversion or cast it to a number on
both
> sides, so you don't get the '100' < '20' situation.

>> sqlite> select strftime('%s', '1970-01-01 00:01:40') < strftime('%s',
'1970-01-01 00:00:20');
>> 1

Ding Ding Ding. A winner. That was my fear is somehow numerics treated as
text was going to upset an apple cart somewhere.

Thanks for the followups and illustrations!
dvn

On Wed, Oct 11, 2017 at 1:11 PM, David Raymond <david.raym...@tomtom.com>
wrote:

> All correct, yes.
>
> Of course now I think about it, strftime always being a string means that
> you either should avoid the '%s' conversion or cast it to a number on both
> sides, so you don't get the '100' < '20' situation.
>
> Better yet, either one of the datetime() or julianday() functions (with
> the same one used consistently in all places) will work best for comparison
> since the output for either one sorts correctly against itself. strftime()
> should be saved for display formatting.
>
>
> sqlite> select strftime('%s', '1970-01-01 00:01:40') < strftime('%s',
> '1970-01-01 00:00:20');
> 1
>
> sqlite> select datetime('1970-01-01 00:01:40') < datetime('1970-01-01
> 00:00:20');
> 0
>
> sqlite> select julianday('1970-01-01 00:01:40') < julianday('1970-01-01
> 00:00:20');
> 0
>
> sqlite> select datetime('now', '+300 seconds') < datetime('now');
> 0
>
> sqlite> select datetime('now', '-300 seconds') < datetime('now');
> 1
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Don V Nielsen
> Sent: Wednesday, October 11, 2017 1:15 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Possible bug with strftime('%s') < strftime('%s')
>
> So strftime always returns TEXT. Correct? It was the application of +300 to
> that result that changed the type to INTEGER. And had "+300 seconds" been
> applied as a modifier in the strftime function, then the addition would
> have occurred before producing the result, with the result being type TEXT.
> Correct?
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with strftime('%s') < strftime('%s')

[Don V Nielsen]

So strftime always returns TEXT. Correct? It was the application of +300 to
that result that changed the type to INTEGER. And had "+300 seconds" been
applied as a modifier in the strftime function, then the addition would
have occurred before producing the result, with the result being type TEXT.
Correct?

On Wed, Oct 11, 2017 at 9:56 AM, David Raymond 
wrote:

> Sorter version of a longer bit I was writing that got complicated:
> Expressions don't have "Affinity" so when both sides of a comparison
> operator are expressions they need to be the same type for it to mean
> anything. One of the ways to do that in this example is to move the +300
> inside the strftime call, so that both sides end up as text...
>
> sqlite> select strftime('%s', '2017-10-11 10:04:43', '+300 seconds') <
> strftime('%s','2017-10-11 10:04:43');
> strftime('%s', '2017-10-11 10:04:43', '+300 seconds') <
> strftime('%s','2017-10-11 10:04:43')
> 0
>
> Other options include using cast, or adding +0 to the other expression to
> turn it into an integer as well.
>
> sqlite> select strftime('%s', '2017-10-11 10:04:43') + 300 <
> strftime('%s','2017-10-11 10:04:43') + 0;
> strftime('%s', '2017-10-11 10:04:43') + 300 < strftime('%s','2017-10-11
> 10:04:43') + 0
> 0
>
>
> -Original Message-
> From: David Raymond
> Sent: Wednesday, October 11, 2017 9:10 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: RE: [sqlite] Possible bug with strftime('%s') < strftime('%s')
>
> See https://sqlite.org/draft/datatype3.html in section 4.1, Sort Order.
> Any integer is less than any text. So you'll want to have both as one of
> the number types to do valid comparison.
>
> 4.1 Sort Order
> The results of a comparison depend on the storage classes of the operands,
> according to the following rules:
>
> A value with storage class NULL is considered less than any other
> value (including another value with storage class NULL).
>
> An INTEGER or REAL value is less than any TEXT or BLOB value. When an
> INTEGER or REAL is compared to another INTEGER or REAL, a numerical
> comparison is performed.
>
> A TEXT value is less than a BLOB value. When two TEXT values are
> compared an appropriate collating sequence is used to determine the result.
>
> When two BLOB values are compared, the result is determined using
> memcmp().
>
> sqlite> select typeof(strftime('%s', '2017-10-11 10:04:43') + 300);
> typeof(strftime('%s', '2017-10-11 10:04:43') + 300)
> integer
>
> sqlite> select typeof(strftime('%s', '2017-10-11 10:04:43'));
> typeof(strftime('%s', '2017-10-11 10:04:43'))
> text
>
>
> David Raymond | GIS Engineer | TomTom | Lebanon, NH, United States
> e-mail: david.raym...@tomtom.com  | office +1 603 306 8498 |
> www.tomtom.com
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Eric Bollengier
> Sent: Wednesday, October 11, 2017 8:55 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] Possible bug with strftime('%s') < strftime('%s')
>
> Hello,
>
> I have noticed a problem in SQLite 3.20.1 for a simple operation based
> on strftime('%s').
>
> With SQLite 3.20.1 and 3.6.18
>
> sqlite> select (strftime('%s', '2017-10-11 10:04:43') + 300) <
> strftime('%s', '2017-10-11 10:04:43');
>
> 1
>
> If I use the CAST operator on the second member, it works
>
> sqlite> select
> (strftime('%s', '2017-10-11 10:04:43') + 300) <  CAST(strftime('%s',
> '2017-10-11 10:04:43') as decimal);
>
> 0
>
> If I use the following query, it works too:
>
> sqlite> select
> (strftime('%s', '2017-10-11 10:04:43') + 300 -  strftime('%s',
> '2017-10-11 10:04:43')) < 0;
>
> 0
>
> on SQlite 2.8, the operator < with strftime('%s') works.
>
> Any idea if it is the expected behavior?
>
> Thanks,
>
> Best Regards,
> Eric
>
>
> ___
> 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

Re: [sqlite] bug: failure to write journal reported as "disk I/O error"

[Don V Nielsen]

I'm sorry gentlemen, but the argument has gotten thick and petulant.

Every complaint and response is resolving down to a mainframe line of
thought (thank God), which few today are willing to accept. That is, the
SQLite software is kept compatible with its root. How many System 370 Cobol
programs can run on to today's hyper-tech mainframes? All of them. Sqlite
was inspired by a need and built at a time when PC's and O/S's were more
primitive. It has some flaws from then that are still with us today. Why?
Because of compatibility. It is more important for this product to be
compatible with its origin because people and machines are dependent on it
being that way.

The error system is what it is because it worked back then. Efforts have
been made to improve things as far as giving the developer more information
to work with and figure things out. The developer knows their version of
SQLite and their operating system(s). It's the developer's responsibility
to match what SQLite provides given the values available in the environment
that it exists in. If the developer's application is going to run atop of
Linux, and Windows, and Android, it is the developers job to create their
application in a way that is sensitive to them.

SQLite is capable of running anywhere. It is not its responsibility for
knowing exactly where it is being run. It doesn't function at that layer.
If the codes are not enough, the amalgamation is out there. Get a copy and
build into it a new layer of error interpretation logic and have it return
what is needed by the O/S that is specific to the application's needs and
wants.

If I'm wrong, I'm sorry. But I got the feeling the original post (the very
first) was a tantrum, and no matter what anyone does to sooth the situation
is working. It is only getting worse.

Again, I apologize for losing my control.

On Wed, Sep 27, 2017 at 12:12 PM, Guy Harris  wrote:

> On Sep 27, 2017, at 10:00 AM, Keith Medcalf  wrote:
>
> > On Wednesday, 27 September, 2017 10:39, Guy Harris 
> wrote:
> >
> >> On Sep 27, 2017, at 6:58 AM, Keith Medcalf  wrote:
> >
> >>> Well, the terminology is correct.  These *ARE* I/O Errors.  The
> >>> system attempted I/O.  It failed.  Hence the term I/O Error.
> >
> >> Just don't call it a "disk I/O error".
> >
> > Well, maybe.  However the I/O that had the error was associated with a
> disk operation (as opposed to a "Video I/O Error", or a "Cardpunch I/O
> Error", "Printer I/O Error", etc.).
>
> Actually, if it had occurred on my machine, it wouldn't have been
> associated with a disk operation; should the application check where the
> data is stored and say "flash memory I/O" error if appropriate? :-)
>
> The point is that the *disk* isn't particularly relevant to some possible
> errors - the problem isn't with the *disk*, which reported no error, the
> problem is with something in the *file system*, such as the amount of space
> available, the permissions on files, etc..
>
> >>> It is irrelevant whether the error was caused because the heads on
> >>> the tape drive need cleaning, access was denied to spool storage, the
> >>> disk was full, someone yanked the cable out of the disk drive, or the
> >>> card reader got jammed up.
> >
> >> I.e., SQLITE_IOERR is equivalent to -1 as a return from various UN*X
> >> system calls, so that, when a program sees it, it needs to get
> >> further error information, such as an errno value, to deal with the
> >> error and, if necessary, to report it.
> >
> > Yes.  An I/O operation of some sort was attempted.  That I/O operation
> involved some sort of "disk" access.  That operation failed with an error.
>
> ...and the next step is to determine what the exact error was.
>
> >> So it *is* relevant to what to do next.
> >
> > Well, in the same sort of way as the message from attempting to send
> Snail mail "Mail Undeliverable" is relevant to what to do next.  You know
> that the error was related to the delivery of the postal item just as the
> "Disk I/O Error" indicates that an I/O operation that involved a disk
> operation failed with an error.
> >
> > In both cases you need to query for the underlying error condition in
> order to determine what to do.
>
> Well, in the first case, the postal service may well say more than just
> "Mail undeliverable", such as "no such person at that address", "no such
> address", etc..
>
> > So in that sense it is relevant to what to do next -- you need to query
> for more particulars.  This is opposed to say a "Syntax Error" in which it
> is pretty clear that the error is a mis-formed statement.
>
> Yes, but even in *that* case, it's often possible to say, for example,
> "there's no operator between the operands "foo" and "bar"" rather than just
> "syntax error".
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> 

Re: [sqlite] Please remove me from the mailing list

[Don V Nielsen]

SWEET MOTHER OF MOSES. I saw "Please remove me from your list" with Simon
Slavin's name next to it. I nearly had a heart attack.

On Wed, Sep 13, 2017 at 9:39 AM, Simon Slavin  wrote:

>
>
> On 13 Sep 2017, at 2:44pm, Dan K McCormick  wrote:
>
> > Thanking you in advance Dan
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> You can use the link at the end of every post, including this one, to
> remove yourself.
>
> 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

Re: [sqlite] Sharing data between desktop and Android

[Don V Nielsen]

I use an app called AirDroid: https://www.airdroid.com/

It goes beyond copying files to/from phone and pc, but it is one of its
functionalities.


On Wed, Sep 6, 2017 at 6:08 AM, Cecil Westerhof 
wrote:

> 2017-09-06 12:54 GMT+02:00 Andy Ling :
>
> > > Cecil Westerhof wrote:
> > > > I am thinking about writing some Android applications. I would like
> to
> > > > share data between the phone (or tablet) and de desktop. What is the
> > best
> > > > way to do this? In a way that would also be convenient for other
> > people.
> > >
> > > I use an Android app that does this. It has a companion PC app that
> lets
> > > you backup and modify the underlying sqlite database. It transfers the
> > > database between Android and PC using a web link. There is a "sync
> > > to PC" menu on the app that opens the connection.
> > >
> >
> > ​> Can you share information about it?
> >
> > It's not my app, I just use it. It's called MobileSheetsPro. There's more
> > information
> > here
> >
> > http://www.zubersoft.com/mobilesheets/
> >
> > Mike, the guy that wrote it, is extremely helpful. So if you contact him
> > I'm sure
> > he'll help you out.
> >
>
> ​OK, I will do that. Thanks.
>
> --
> Cecil Westerhof
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Don V Nielsen]

database_name is never assigned a value? In SQLite3_RDB::SQLite3_RDB() it
is spelled databese_name?

On Sat, Aug 26, 2017 at 10:28 PM, Simon Slavin  wrote:

>
>
> On 27 Aug 2017, at 2:34am, Papa  wrote:
>
> > Why do I get this error message?
>
> Try having the program using file calls to open a simple text file in the
> same directory.  Does that succeed ?
>
> 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

Re: [sqlite] GROUP_CONCAT separator and DISTINCT

[Don V Nielsen]

I think David Blake is suggesting that GROUP_CONCAT intuitively suggests it
should take the separator argument, regardless of DISTINCT being present.
It is logical that it should, given GROUP_CONCAT takes two arguments, not
one. The second argument defaults to a comma when omitted. The presence of
DISTINCT is really a lexical issue; an attribute of the first argument and
not an argument itself.

Using replace() or a subquery are workarounds, which is how they look and
feel. Using replace() is the most logical workaround...unless...the column
already contains a comma. In that case, then an awkward subquery is
required. This eventually leads one to think "what is this guy trying to
accomplish" when one reads it.

Just my two cents

On Fri, Aug 25, 2017 at 3:01 AM, Clemens Ladisch  wrote:

> Dave Blake wrote:
> > It seems that it is not possible to specify the concatenation separator
> > when using GROUP_CONCAT with DISTINCT.
>
> The documentation  says:
> | In any aggregate function that takes a single argument, that argument
> | can be preceded by the keyword DISTINCT.
>
> > Is there another way I can specify the separator when using DISTINCT?
>
> If your values do not contain commas, you can use replace() afterwards.
>
> Otherwise, use a subquery with DISTINCT first, and then run the
> group_concat() over that.
>
>
> Regars,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tutorials, books, video about SQLite

[Don V Nielsen]

Safari Books Online. I believe there are a number of SQLite title there.

On Wed, Aug 9, 2017 at 2:06 PM, Lars Frederiksen  wrote:

> Hi
>
>
>
> I would appreciate very much  if you clever people out there have some
> booktitles or links to tutorials (websites, video etc) about SQLite.
>
>
>
> Regards
>
> Lars
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Poll: Include the recent sqlite3_column_name() fix in the upcoming 3.20.0 release?

[Don V Nielsen]

Merge it now.

As stated in documentation: "The SQLite documentation clearly states that,
if there is no AS clause, the names of output columns are indeterminate,
arbitrary, and subject to change." It would be my hope that legacy
applications exploiting the returned column name given said documentation
would be compiling a specific version of the code, or providing a specific
dll to accompany the app. The legacy apps should not have a problem, in
those scenarios.

Moving forward, wanting to implement the latest SQLite, the change has been
clearly documented. And so, testing will be required to upgrade.

IMO, dvn

On Mon, Jul 31, 2017 at 10:30 AM, Neville Dastur 
wrote:

> IMHO the inconsistency is a bug of sorts and so would “vote” for a merge
> now.
> In a sense applications that risk breaking are due to hacking around what
> is documented. And if the change is going to happen it might as well be now
> along with the major versions change.
>
> --
> L: http://uk.linkedin.com/in/nevilledastur  nevilledastur>
> T: @nevilledastur 
> Clinical Software Solutions: http://www.clinsoftsolutions.com <
> http://www.clinsoftsolutions.com/>
> hospify.com  - secure healthcare communication
>
>
>
> > On 31 Jul 2017, at 16:21, Richard Hipp  wrote:
> >
> > Ticket https://sqlite.org/src/info/de3403bf5ae5f72ed describes a
> > problem with column naming, and a proposed solution.  Today's
> > question:  Should the proposed solution be merged into the 3.20.0
> > release?
> >
> > Pros:  (1)  The change makes column names more consistent.  (2) The
> > change fixes some breakage caused by a query planner enhancement
> > introduced in 3.19.0.  (3) The change makes column naming in SQLite
> > work (more) like it does in PostgreSQL, MySQL, and SQLServer.  (4) The
> > change will likely be in 3.21.0 even it it isn't in 3.20.0.  Better to
> > go ahead and get over the pain of any breakage that results now,
> > rather than putting it off until later.
> >
> > Cons: (5) The change might cause breakage for legacy applications that
> > depend on the older (arguably buggy) behavior.  (6) This seems like a
> > big change to receive so little beta exposure prior to the official
> > release.  (7) Making the merge will (or should) delay the release by a
> > day or so.  The release was going to happen tomorrow (2017-08-01) but
> > if we do the merge, I think the release should be postponed until
> > 2017-08-02 or 2017-08-03.
> >
> > Let me know your thoughts.   Replies to the mailing list are
> > preferred, but private email directly to me is also accepted.
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A Descending Auto Increment Key

[Don V Nielsen]

On the heel of this is a grammatical error: "declared type "INTEGER"
includes an "PRIMARY KEY DESC" clause". It should read "includes a
"primary...".

On Wed, Jul 19, 2017 at 11:03 PM, Keith Medcalf  wrote:

>
> AUTOINCREMENT can only be used with INTEGER PRIMAY KEY columns in a rowid
> table definition to declare an alias for the rowid.  Such columns must not
> have the DESC ordering (the rowid is ascending).  If it does, then "PRIMARY
> KEY" is syntactic sugar for "UNIQUE" (ie, creating a unique index) and the
> AUTOINCREMENT keyword cannot be applied.
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of sanhua.zh
> Sent: Wednesday, 19 July, 2017 20:07
> To: sqlite-users
> Subject: [sqlite] A Descending Auto Increment Key
>
> When I call `CREATE TABLE test(i INTEGER PRIMARY KEY DESC AUTO
> INCREMENT)`, SQLite comes to an error "AUTOINCREMENT is only allowed on an
> INTEGER PRIMARY KEY".
>
>
> But as the document http://www.sqlite.org/lang_createtable.htmlsays, in
> the column-constraint, it shows that `PRIMARY DESC AUTOINCREMENT` is a
> valid syntax.
>
>
> Why the original SQL failed? Is that really not supported?
> ___
> 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

Re: [sqlite] Version 3.20.0 coming soon...

[Don V Nielsen]

> perhaps the devs can invent some other namespace

Seconded. I would like to think the devs, themselves, would like to create
some sort of namespace or isolation for the structures created by the
extensions. A little more gusto in the name, rather than STMT, would always
be appreciated. imo

On Fri, Jul 14, 2017 at 5:31 AM, Simon Slavin  wrote:

>
>
> On 14 Jul 2017, at 8:50am, Clemens Ladisch  wrote:
>
> > There already is an "sqlite_" namespace, and it is reserved for internal
> > objects of SQLite itself.
> >
> > The STMT virtual table is an extension that must be explicitly enabled.
> > (The sqlite3 command-line shell does so by default, but it already did
> > with other extensions like fileio, and it does not actually have the
> > same API stability guarantees as the SQLite library.)
>
> Then perhaps the devs can invent some other namespace for this new
> category of thing.  I don’t mind what the name is, I would just like the
> entity to be clearly marked as not one of mine.  The ability to filter out
> names including underline characters is very useful.
>
> 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

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

[Don V Nielsen]

"CREATE TABLE meta_comments"

Simon, isn't your approach the most logical solution rather than
incorporating a comment column into the master? Once incorporated, wouldn't
you be opening yourself up to a litany of "Not the way we work", "We need
feature x to be useful", "What if we want a null comment?" and user stuff
like that? Your framework gives something to build off of, can be adapted
at will, and is not part of the core.

Personally, I would find it *helpful* to have a comment column, but it is
certainly not critical to the operation of the database. It's not a must
have feature for this db.

Just my two cents, or less

On Wed, Jun 7, 2017 at 12:57 PM, Simon Slavin  wrote:

>
>
> On 6 Jun 2017, at 2:17pm, PICCORO McKAY Lenz 
> wrote:
>
> > how its the status of this work?
>
> The work of parsing comments in the CREATE TABLE command ?  I don’t think
> anyone else thinks this is worth working on.  Discussion in this list has
> come up with many reasons why it’s a poor way to store comments, including
>
> * Difficulty of parsing text which may have CR, LT, tab, comma, etc..
> * Impossible to update the comments without DROPping and reCREATEing the
> table because SQLite implements only a few ALTER TABLE commands.
> * Documentation restricted to one language.
>
> Here’s a simple version of the best system I ever came up with from
> working in multi-programmer projects, where clear comments were important
> to letting one developer know what another intended.  Comments for a
> database can be stored in the following table in that database:
>
> CREATE TABLE meta_comments (
> entityType TEXT COLLATE NOCASE NOT NULL,
> theTable TEXT COLLATE NOCASE NOT NULL,
> theName TEXT COLLATE NOCASE NOT NULL,
> theComment TEXT COLLATE NOCASE NOT NULL,
> PRIMARY KEY (entityType, theTable, theName));
>
> Values for "entityType" can be ’schema’,'table','index','trigger','view',
> and anything else you want to document.
>
> If you need multilanguage documentation (required for some countries which
> work to protect a language) add a "language TEXT COLLATE NOCASE NOT NULL"
> field and include it in the primary key.  Ih one use of an early version of
> this we also used a field called "theVersion" to document changes in each
> entity, though I don’t know how sensible that is for most uses.  We also
> used to use a table like this to store commands, though if I was designing
> that system from scratch now I’d use a different table.
>
> I came up with the above structure myself, warrant that it is not
> encumbered by any intellectual property, and dedicate it to the public
> domain.  Anyone can use it for anything they want.
>
> 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

Re: [sqlite] sqlite3 feature or regression

[don v nielsen]

Vermes, I'm late to the party but would still like to comment. The 
problem is the ruby code, not sqlite. The following is what you coded in 
the Ruby:


db.execute("select szamla,megnevezes from proba") do |row|

In some shape or fashion, the result set is getting mangled by the 
update. When I debug it, the code is getting deeper than I have the 
ability to understand, but the execute is being reevaluated. That is 
probably getting more complicated due to the open (non-committed 
transaction). Again, this is deeper than my ability to understand.


What you need is a static collection and then iterate over the results:

(db.execute("select szamla,megnevezes from proba")).each {|row|

- or -

rows = db.execute("select szamla,megnevezes from proba")
rows.each {|row|

The above will execute the select, returning a collection of the ten 
rows, then iterate for each member of the collection. The collection is 
not influenced by the update.


dvn


On 03/07/2017 09:00 AM, Vermes Mátyás wrote:

On Mon, 6 Mar 2017 18:34:40 -0500
Richard Hipp  wrote:


For the benefit of those of us who do not do Ruby, perhaps you could
explain in words what you think it is that SQLite is doing
incorrectly?


I am not a Ruby programmer either nor a real SQLite user. I am interested in writing  SQL interfaces 
to http://github.com/mrev11/ccc3;>CCC to various databases.  Ruby 
was chosen only because it can be run everywhere.   Just run the script: A select of ten rows turns 
into an endless loop.

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



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

Re: [sqlite] UNION

[don v nielsen]

Might I suggest: https://www.w3schools.com/sql/

dvn

On 03/01/2017 09:02 AM, jose isaias cabrera wrote:

Ryan,

I just want to thank you for your kindness and display of goodwill to
mankind.  This is a great response.  I even learned something from this
post.  Thanks so much for your responses.  There are others like us in
this group that love to learn and your posts always are well received.
Thanks.  In Spanish we say, "muchas gracias."

josé

On 2017-03-01 09:51, R Smith wrote:


On 2017/03/01 3:40 AM, do...@mail.com wrote:


# SELECT * FROM processors UNION SELECT * FROM storage;
Error: SELECTs to the left and right do not have the same number of
result columns.

All tables that I created in my database have differing column names,
values, and amounts of columns with the noted exception of the one
column which is common (board). I've no idea what to do now.

Why is this an error?

I think that perhaps you are new to SQL and other replies assumed you simply 
wanted what you wrote. I could be wrong, but just in case, here are my 
suggestions:

Perhaps what you would rather like to do is JOIN these tables and not UNION 
them?

Do you wish to match /every/ processor with /every/ board?
In this case, the statement should read: (Warning: this could produced 
excessively long listings)
SELECT * FROM processors, storage;

Do you wish to match only processors and storage that fit on the same boards?
In this case the statement might read something like:
SELECT *
FROM processors
JOIN storage USING board
;

Do you wish to list /all/ processors and add the storage options for the same 
board /if/ there are any?
In this case the statement might read something like:
SELECT *
FROM processors
LEFT JOIN storage ON storage.board = processors.board
;

As you can see, lots of different things can be achieved. A quick course in SQL 
via perhaps W3Schools will teach all these in a few hours.

Cheers,
Ryan

___
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

[sqlite] Fossil documentation edit

[don v nielsen]

On page http://fossil-scm.org/index.html/doc/trunk/www/quickstart.wiki 
it reads:


" To merge two branches back together, firstupdate 
to the branch you want to 
merge into.Then do amerge 
another branch that you 
want to incorporate the changes from."


Is it more appropriate for the second sentence to read: "Then do amerge 
[of] another branch..." or 
"Then merge another branch..."?


dvn

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

Re: [sqlite] Using CSV.c

[Don V Nielsen]

I would recommend using a script or batch file to automate the process
using the command line tool. It would be easier to maintain and remove a
level or two of complexity.


On Tue, Jan 10, 2017 at 4:03 AM, tbuck...@frontier.com <
tbuck...@frontier.com> wrote:

> Hello,I am having a little trouble implementing the csv.c program for
> SQLite.  Are their any programming examples available?
> All I want to do is import a csv file to a database, sort the file and
> output the results to another csv file all in c code.  I can manually do it
> with the dot (.) tools, but I want to automate this by executing one exe
> file.Tom...
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bad query plan selection only with "LEFT JOIN"

[Don V Nielsen]

In the below view, what is "a"? A FROM is not defined.

--
-- Ideally I want to write the next query using the previous view
--
CREATE VIEW if not exists "despesas_master_list_view_tidy" AS
SELECT
a.*,
b.name as modalidade_licitacao,
c.name as credor,
d.*
LEFT JOIN licitacao_modalidades AS b ON a.modalidade_lic=b.id
LEFT JOIN credores AS c ON a.credor_id=c.id
LEFT JOIN empenhos_list_view AS d ON a.empenho_id=d.id;


On Fri, Jan 6, 2017 at 3:27 AM, Domingo Alvarez Duarte 
wrote:

> Hello Richard !
>
> The simple example I've sent is the minimal to show the problem, the real
> database schema where I found this problem has a lot of small tables joined
> and I was using the views to simplify (not duplicate) code, so on that case
> it'll result in bloat and repetition. see bellow:
>
> ===
>
> create table if not exists municipios(
> id integer primary key,
> name varchar not null unique collate nocase_slna
> );
>
> create table if not exists municipios_orgaos(
> id integer primary key,
> name varchar not null unique collate nocase_slna
> );
>
> create table if not exists municipios_poder(
> id integer primary key,
> name varchar not null unique collate nocase_slna
> );
>
> create table if not exists credores(
> id integer primary key,
> cnpj_cpf varchar collate nocase_slna,
> name varchar not null collate nocase_slna,
> unique(cnpj_cpf, name)
> );
>
> create table if not exists tipos_despesa(
> id integer primary key,
> name varchar not null unique collate nocase_slna
> );
>
> create table if not exists funcoes_governo(
> id integer primary key,
> name varchar not null unique collate nocase_slna
> );
>
> create table if not exists subfuncoes_governo(
> id integer primary key,
> name varchar not null unique collate nocase_slna
> );
>
> create table if not exists programas_governo(
> id integer primary key,
> code integer,
> name varchar not null collate nocase_slna,
> unique(code, name)
> );
>
> create table if not exists acoes_governo(
> id integer primary key,
> code integer,
> name varchar not null collate nocase_slna,
> unique(code, name)
> );
>
> create table if not exists fontes_recursos(
> id integer primary key,
> name varchar not null unique collate nocase_slna
> );
>
> create table if not exists aplicacoes_fixo(
> id integer primary key,
> code integer,
> name varchar not null collate nocase_slna,
> unique(code, name)
> );
>
> create table if not exists aplicacoes_variavel(
> id integer primary key,
> code integer,
> name varchar not null collate nocase_slna,
> unique(code, name)
> );
>
> create table if not exists licitacao_modalidades(
> id integer primary key,
> name varchar not null unique collate nocase_slna
> );
>
> create table if not exists elementos_despesa(
> id integer primary key,
> name varchar not null unique collate nocase_slna
> );
>
> --
> -- the table bellow has 6M records
> --
> CREATE TABLE if not exists empenhos(
> id integer primary key,
> ano_exercicio integer not null,
> nr_empenho varchar not null,
> valor decimal,
> municipio_id integer not null,
> orgao_id integer not null,
> funcao_governo_id integer,
> subfuncao_governo_id integer,
> cd_programa integer,
> cd_acao integer,
> fonte_recurso_id integer,
> cd_aplicacao_fixo integer,
> elemento_id integer,
> unique(municipio_id, nr_empenho)
> );
>
>
> --
> -- the table bellow has 6M records
> --
> CREATE TABLE if not exists despesas_master(
> id integer primary key,
> empenho_id integer not null,
> modalidade_lic integer not null,
> credor_id integer not null,
> historico_despesa varchar collate nocase_slna,
> unique(credor_id, empenho_id)
> );
>
> --
> -- the table bellow has 24M records
> --
> CREATE TABLE if not exists despesas_detalhe(
> id integer primary key,
> id_despesa_detalhe integer not null,
> despesa_id integer not null,
> mes_referencia integer not null,
> tp_despesa_id integer not null,
> dt_emissao_despesa date not null,
> vl_despesa decimal
> );
>
> CREATE VIEW if not exists "empenhos_list_view" AS
> SELECT
> a."id",
> a."ano_exercicio",
> a."nr_empenho",
> a."valor",
> d.name as municipio,
> e.name as orgao,
> f.name as funcao,
> g.name as subfuncao,
> h.name as programa,
> i.name as acao,
> k.name as fonte_recurso,
> l.name as aplicacao_fixo,
> n.name as elemento,
> 

Re: [sqlite] Commit ad601c7962 degrade performance in a client program

[Don V Nielsen]

I loved this remark posted in the bugzilla chain:   "(... Fossil?
where do people find these version control systems?)"

On Thu, Jan 5, 2017 at 2:09 AM, Richard Hipp  wrote:
> On 1/3/17, Jianxun Zhang  wrote:
>> I am working in Yocto project. We have a fake-root program “pseudo” that
>> uses sqlite3 at a step when building Yocto images. We found a 2% increase of
>> the whole build time, and my bisecting shows the ad601c7962 in sqlite3 is
>> the root cause.
>
> That change was backed out by
> https://www.sqlite.org/src/timeline?c=9675518b33e8d407 and so your
> code should return to its old speed, or be faster.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] date-2.2c tests fail for sqlite-3.16.1 on Fedora / i686

[Don V Nielsen]

Graham, you may be on to something: "I don't know what the tests are
doing, but could it be connected with the fact that a leap-second was
added as we changed from 2016 to 2017". I noticed a whole bunch of
rspec tests (my projects completely unrelated to sqlite) failing with
microsecond differences. My issue is likely the result of the leap_sec
shift. Thanks!

On Thu, Jan 5, 2017 at 4:08 AM, Graham Holden  wrote:
> I don't know what the tests are doing, but could it be connected with the 
> fact that a leap-second was added as we changed from 2016 to 2017 and one of 
> expected/got is taking this into account and the other isn't?
> Graham
>  Original message From: Richard Hipp  Date: 
> 05/01/2017  08:12  (GMT+00:00) To: SQLite mailing list 
>  Subject: Re: [sqlite] date-2.2c tests 
> fail for sqlite-3.16.1 on Fedora / i686
> On 1/4/17, Jakub Dorňák  wrote:
>> Example output:
>>
>> ...
>> ! date-2.2c-1 expected: [06:28:00.001]
>> ! date-2.2c-1 got:  [06:28:00.000]
>> ! date-2.2c-4 expected: [06:28:00.004]
>> ! date-2.2c-4 got:  [06:28:00.003]
>> ! date-2.2c-7 expected: [06:28:00.007]
>> ! date-2.2c-7 got:  [06:28:00.006]
>> ! date-2.2c-8 expected: [06:28:00.008]
>> ! date-2.2c-8 got:  [06:28:00.007]
>> ...
>
> This is probably a function of the underlying floating-point hardware.
> What CPU is this running on?
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with union and join.

[Don V Nielsen]

Thanks for the correction/information! Merry Christmas and Happy New
Year to you.

On Tue, Dec 27, 2016 at 9:48 AM, Richard Hipp <d...@sqlite.org> wrote:
> On 12/27/16, Don V Nielsen <donvniel...@gmail.com> wrote:
>> Theory related question. I'm being argumentative, I know. But this
>> issue is in the same category as one discussed weeks ago.
>>
>> SQLite is, in a sense, typeless. All data is stored as text (ignore
>> blob). Correct? It is when one casts a column to something other than
>> text that triggers SQLite to treat the text differently.
>
> Incorrect.  SQLite stores content in memory and on disk in multiple
> formats, including 2's-complement integers, IEEE 754 floating point
> numbers, text formatted as UTF8, UTF16be, or UTF16le, and binary
> blobs.  See, for example,
> https://www.sqlite.org/fileformat2.html#serialtype
>
>>
>> Disregarding auto-incremented key values, why have an integer key.
>
> Special optimizations apply to tables with an INTEGER PRIMARY KEY that
> make such tables particularly fast.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with union and join.

[Don V Nielsen]

Theory related question. I'm being argumentative, I know. But this
issue is in the same category as one discussed weeks ago.

SQLite is, in a sense, typeless. All data is stored as text (ignore
blob). Correct? It is when one casts a column to something other than
text that triggers SQLite to treat the text differently.

Disregarding auto-incremented key values, why have an integer key.
Even if the key value will only be numeric digits like 1, 255, 1024,
etc..., are they "truly" integers? If the value is not used in a
mathematical formula, why think of it as an integer? It is still just
text...a string of ascii digits... but still text. Is there something
behind the scenes of how text data comprised of numeric digits is
stored?

Like the previous issue I suggested keeping the keys between tables
the same data type. The issue resolves itself. The same would be true,
here. One table has text which could be '1,10'. But in the other
table, it is integer 1 & 10. It could be text '1' & '10'. No type
conversion problems.

I don't know. I would like to hear what others have to say.
dvn

On Sun, Dec 25, 2016 at 2:43 PM, Adrian Stachlewski
 wrote:
> Fortunately names of columns are much more transparent and documented in
> our internal specification. 'Id' was created only for example, but thanks
> for advice :)
>
> Adrian
>
> 2016-12-25 13:44 GMT+01:00 Simon Slavin :
>
>>
>> On 23 Dec 2016, at 4:55pm, Adrian Stachlewski 
>> wrote:
>>
>> > Id field in one table is defined as TEXT, because there are stored
>> > identifiers which can be numeric or text mostly like in the example ("4",
>> > "4,5", "10-1") (to be precise this map is created on the fly by
>> > concatenating some ids and names from another tables).  In second table
>> > there are stored identifiers which are integer only. This ids means
>> > something entirely different, but there is one case, when table with date
>> > keeps ids from both tables. Unfortunately I cannot change input data - it
>> > is taken from some APIs using csv files.
>>
>> Okay.  You’re wedded to a data format created by someone else.  That
>> explains the problem.
>>
>> If you have the opportunity to rename your columns when you import from
>> the CSV files, I might recommend that you do not call the TEXT field 'id'.
>> The convention for 'id' is for an INTEGER PRIMARY KEY and it might confuse
>> other people who see your database.
>>
>> Good luck with problem you posted about.
>>
>> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speaking of Ruby & Sqlite...a weekend oddity for someone

[Don V Nielsen]

You are correct, sir. I discovered in the rake tasks the following
little bit of code. It directs which version of sqlite to download and
compile.

# BINARY_VERSION = "3.8.11.1"
# URL_VERSION= "3081101"
# URL_PATH   = "/2015"

Now I just need to figure out how to reinitiate the rake compile task
after making the following update, then test.

BINARY_VERSION = "3.15.2.0"
URL_VERSION= "3150200"
URL_PATH   = "/2016"

On Fri, Dec 9, 2016 at 5:27 PM, Roger Binns <rog...@rogerbinns.com> wrote:
> On 09/12/16 14:09, Don V Nielsen wrote:
>> However,
>> it fails using the sqlite3 gem. The specific exception is "in
>> 'initialize': near "with": syntax error
>
> That will be a SQLite version issue - older SQLite's didn't support
> with.  While you made some effort around versions, whatever is happening
> there is an older library is actually used.
>
> You can use this to find out exactly what library version is used:
>
>   SELECT sqlite_version(), sqlite_source_id();
>
> Also this will give a list of compilation options, to verify:
>
>   PRAGMA compile_options;
>
> Roger
>
>
>
> ___
> 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] Speaking of Ruby & Sqlite...a weekend oddity for someone

[Don V Nielsen]

I have an odd problem. It involves Linux & Windows, and it involves
the ruby gem sqlite3. I've already contacted that author/maintainer of
the gem, but I have not heard back in several weeks.

I have a sql file that contains a ctas statement that employs cte with
statements. On Windows, the sql executes fine using the sqlite3
command line tool "sqlite3 aan.db -init cte_with.sql". And it runs
fine calling sqlite3 via the sqlite3 gem:

db = SQLite3::Database.new File.join(FOLDER,'aan.db')
db.execute 'drop table if exists fullfilled;'
sql = File.readlines( File.join(FOLDER,'cte_with.sql') ).collect {|ln| ln.chomp}
db.execute sql.join(' ')


Now with linux, the exact same sql code and ruby files are used. The
sql exec completes successfully using the command line tool. However,
it fails using the sqlite3 gem. The specific exception is "in
'initialize': near "with": syntax error (Sqlite3::SQLException)". It
is failing in the preparation of the statement. From all appearances,
sqlite3 is throwing the exception.

The gem compiles sqlite, creating sqlite_native.so, by downloading the
latest source file, as
"http://sqlite.org#{URL_PATH}/sqlite-autoconf-#{URL_VERSION}.tar.gz;
and compiling that. I have been comparing the gem code between the o/s
environments, and the C code (to this point) is identical.

I have dug into this deeper than my brain can figure out. Is it
possible that there is some kind of #ifdef or #ifndef being triggered,
based on o/s environment, that could effect the parsing or execution
of a cte?

I have a package that contains the sql, the ruby code, and a test db
(1.4k zipped) if anyone would like to give this a shot in their linux
environment and see if you get the same result. Just email me back and
I will send it to you.

* If I change the sql to create temporary tables instead of using the
cte with statements, then everything runs fine regardless of the o/s
environment.

I'm scratching my head on this, one. As always, thanks for your time
and consideration.
dvn

final note: if you take this one, a successful run will create an
empty table. it is good that the table is created. the problem is the
exception, not an empty table.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] using sqlite extensions within Ruby

[Don V Nielsen]

Documentation update to https://sqlite.org/sessionintro.html:

"The session extension provide a mechanism". Should that read, "The session
extension provides a mechanism"?

dvn

On Thu, Dec 8, 2016 at 9:23 AM, Don V Nielsen <donvniel...@gmail.com> wrote:

> Like you, I like ruby and working with sqlite via sqlite3 gem. So you have
> recompiled sqlite3 with the sessions extension. Correct? And this modified
> sqlite3 is the nearest sqlite3 available in your execution path. Correct?
> The sqlite_ruby library is an interface to sqlite3...the base model.
> Granted, I have no knowledge of the sessions extension, nor any extension
> for that matter. But I do not know how sqlite_ruby would have any language
> to the extended functionality; it is simply unaware of the bindings. If the
> functionality is available via function calls in the sql itself, have you
> tried executing the sql directly, i.e.:
>
>   conn = ActiveRecord::Base.establish_connection(adapter:'sqlite3',
> database:DB_NAME)
>   conn.connection.execute(pragma_this_or_that)
>   conn.connection.execute(your_sql_with_functions)
>
> Without you augmenting the sqlite3 gem and teaching it how to interact
> with the new functions, I don't know how your would accomplish what you are
> suggesting.
>
> dvn
>
> On Wed, Dec 7, 2016 at 12:07 PM, Will Parsons <varro@nodomain.invalid>
> wrote:
>
>> I don't usually see questions about using SQLite3 from Ruby here, so I
>> hope this is not out of place.
>>
>> Ruby is my normal programming language of choice nowadays, and I've
>> been very happy with the sqlite3 ruby gem to use SQLite3 within my
>> programs.  I'd now like to make use of the SQLite3 session extension
>> under Ruby using the sqlite3 gem, but can't figure out how to do that.
>>
>> I'm currently using FreeBSD and have compiled sqlite3 with the
>> SESSION extension.
>>
>> I previously asked this question on stackoverflow:
>>
>> http://stackoverflow.com/questions/40875967/how-to-use-sqlit
>> e3-session-extension-with-ruby
>>
>> and got the reply that no such support was available.  I'm hoping that
>> someone here may have helpful advice.
>>
>> --
>> Will
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] using sqlite extensions within Ruby

[Don V Nielsen]

Like you, I like ruby and working with sqlite via sqlite3 gem. So you have
recompiled sqlite3 with the sessions extension. Correct? And this modified
sqlite3 is the nearest sqlite3 available in your execution path. Correct?
The sqlite_ruby library is an interface to sqlite3...the base model.
Granted, I have no knowledge of the sessions extension, nor any extension
for that matter. But I do not know how sqlite_ruby would have any language
to the extended functionality; it is simply unaware of the bindings. If the
functionality is available via function calls in the sql itself, have you
tried executing the sql directly, i.e.:

  conn =
ActiveRecord::Base.establish_connection(adapter:'sqlite3',database:DB_NAME)
  conn.connection.execute(pragma_this_or_that)
  conn.connection.execute(your_sql_with_functions)

Without you augmenting the sqlite3 gem and teaching it how to interact with
the new functions, I don't know how your would accomplish what you are
suggesting.

dvn

On Wed, Dec 7, 2016 at 12:07 PM, Will Parsons 
wrote:

> I don't usually see questions about using SQLite3 from Ruby here, so I
> hope this is not out of place.
>
> Ruby is my normal programming language of choice nowadays, and I've
> been very happy with the sqlite3 ruby gem to use SQLite3 within my
> programs.  I'd now like to make use of the SQLite3 session extension
> under Ruby using the sqlite3 gem, but can't figure out how to do that.
>
> I'm currently using FreeBSD and have compiled sqlite3 with the
> SESSION extension.
>
> I previously asked this question on stackoverflow:
>
> http://stackoverflow.com/questions/40875967/how-to-use-
> sqlite3-session-extension-with-ruby
>
> and got the reply that no such support was available.  I'm hoping that
> someone here may have helpful advice.
>
> --
> Will
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like operator

[Don V Nielsen]

Igor, I'm not sure if you gain anything from"length(lower(name))". Just
"length(name)" would suffice.

On Mon, Dec 5, 2016 at 10:11 AM, Dominique Devienne 
wrote:

> On Mon, Dec 5, 2016 at 4:24 PM, Igor Tandetnik  wrote:
>
> > On 12/5/2016 10:19 AM, Igor Tandetnik wrote:
> >
> >> On 12/5/2016 7:30 AM, ravi.shan...@cellworksgroup.com wrote:
> >>
> >>> select name from employee table where name like '%Araya%' or name like
> >>> '%Amul%' or name like '%Aj%';
> >>>
> >>> Table - Employee
> >>>
> >>> Id | Name | age |
> >>> 1  | Arayan Kuma | 29  |
> >>> 2  | Amul Kanth  | 30  |
> >>> 3  | Ajay Kumar | 45  |
> >>>
> >>> I dont like to use may or conditions for pattern matching using like
> >>> operator.
> >>> Is there any other way I can workaround without using or condition in
> >>> like operator in sqlite.
> >>>
> >>
> >> WHERE length(replace(replace(replace(name, 'Araya', ''), 'Amul', ''),
> >> 'Aj', '')) != length(name)
> >>
> >
> > Actually, this is not quite the same: it's case-sensitive, whereas LIKE
> is
> > case-insensitive by default. To be equivalent, make it
> >
> > WHERE length(replace(replace(replace(lower(name), 'araya', ''), 'amul',
> > ''), 'aj', '')) != length(lower(name))
>
>
> Or use the pragma [1].
> Thanks, didn't realize/know LIKE was case-insensitive (for ASCII chars
> only) by default, in SQLite.
> Also made me double-check whether Oracle is case-sensitive or not (it is)
> [2]
>
> BTW, Igor: wow :)
> Not that I'd use that ever, but still, very clever! --DD
>
> [1] https://www.sqlite.org/pragma.html#pragma_case_sensitive_like
> [2]
> http://stackoverflow.com/questions/5391069/case-insensitive-searching-in-
> oracle
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with CASE in WHERE clause

[Don V Nielsen]

Sorry, but the whole scenario is messy, at best. The column is declared
bool, and then a string '1' is assigned to it. The case lacks an else, so
it resulting in one of two types: a string when true and an integer when
false. Correct? And then on top of that, as Simon pointed out, the column
affinity is bool, so a string is being interpreted as a bool (technically
integer) and so the first one is resulting in true when it appears that the
second one should do so. Please agree that there is way more happening that
what should be.

My recommendation is this. Make [posted] a varchar(1) with only two valid
values: 'y' and 'n'. Then rewrite your logic to work with 'y' and 'n' and
see if that works across every database. It is much more explicit, cleaner,
and does not rely on any underlying interpretations.

Just my opinion. Merry Christmas.
dvn

On Sun, Dec 4, 2016 at 2:46 AM, Frank Millman  wrote:

>
> From: Simon Slavin
> Sent: Sunday, December 04, 2016 10:26 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] Problem with CASE in WHERE clause
>
>
> > On 4 Dec 2016, at 6:55am, Frank Millman  wrote:
> >
> > > If a column contains a ‘1’, I would expect sqlite3 to return true when
> testing for ‘1’, but in my example it returns false.
> >
> > I think I’ve found the problem ...
> >
>
> Thank you very much for your explanation, Simon.
>
> My live situation is a bit more complex than my example, so I will have to
> experiment to find the ideal solution.
>
> But you have given me the information I need to move forward – much
> appreciated.
>
> Frank
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table name syntax

[Don V Nielsen]

> And since the "*" forms are considered bad style

I have done this, not knowing it is bad style. Can you provide some reasons
why it is bad? I can assume, "Applications are supposed to be controlled
environments, and using tbl.* introduces uncertainty outside the
applications control." But are there more specific reasons?

Thanks,dvn


On Mon, Nov 21, 2016 at 3:09 PM, Richard Hipp  wrote:

> On 11/21/16, David Raymond  wrote:
> >
> > Following the nice SQL diagrams it looks like in a select you can only
> have
> > * or table-name.*, whereas in other places you can have
> > schema-name.table-name. Granted, the second version can be made prettier
> and
> > more readable, but I would have assumed the first version would be ok. Is
> > this par for all SQL versions?
>
> I don't know what other database engines do, but you are correct that
> SCHEMA.TABLE.* is not allowed in SQLite.  And since the "*" forms are
> considered bad style (to be used only interactively, and not in
> applications) we are not motivated to change it, lest developers be
> tempted to use "*" in their applications.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange problem with using random with a table

[Don V Nielsen]

Does the same thing happen if you select from Psalms, or Thessalonians,
instead of Proverbs?  Sorry. Little religion humor. Too early in the
morning and I haven't had my coffee, yet.

On Fri, Nov 11, 2016 at 4:34 AM, Cecil Westerhof 
wrote:

> 2016-11-11 11:22 GMT+01:00 Clemens Ladisch :
> > Cecil Westerhof wrote:
> >> When I execute the following query:
> >> SELECT *, randomiser * 1000
> >> FROM (SELECT abs(random()) / 1 AS randomiser);
> >>
> >> I get normal results:
> >> 61|61000
> >>
> >> But when I change it to:
> >> SELECT *, randomiser * 1000
> >> FROM (SELECT abs(random()) / 1 AS randomiser
> >>   FROM   proverbs)
> >> LIMIT 5;
> >>
> >> I get very strange results:
> >> 382|606000
> >> 172|148000
> >> 144|592000
> >> 181|136000
> >> 123|469000
> >>
> >> What could be happening here?
> >
> > In the first query, the subquery is computed with a temporary table
> > (actually, a coroutine) before the values of the outer query are
> > computed.
> >
> > The second query is flattened, so it ends up like this:
> >
> >   SELECT abs(random()) / 1,
> >  abs(random()) / 1 * 1000
> >   FROM proverbs
> >   LIMIT 5;
> >
> > "randomiser" is not a table column, it is just an _alias_ for the
> > expression, so this is an allowed transformation.
> >
> > To prevent this, violate one of the rules that the optimizer currently
> > uses to determine whether flattening is possible:
> > 
> > For example, put a LIMIT clause also into the subquery.
>
> I need all the records, so I use:
> SELECT *
> ,  randomiser * 1000
> ,  randomiser * 1000
> ,  randomiser * 1000
> ,  randomiser * 1000
> ,  randomiser * 1000
> FROM (
> SELECT abs(random()) / 1 AS randomiser
> FROM   proverbs
> LIMIT  (SELECT COUNT(*) FROM proverbs)
> )
> LIMIT 5;
>
> Not very clean, but it works.
>
> Maybe a good idea to have an option to disable flattening?
>
> --
> Cecil Westerhof
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Don V Nielsen]

> It guess it comes down to what one wants from "INTEGER PRIMARY KEY
AUTOINCREMENT"

What I would want, ...expect, is that a primary key autoincrement column
would be left completely alone. And if was altered, it was altered on
accident. I always thought "integer primary key" was synonymous with
__rowid__.  What application would want to mess with that?



On Mon, Oct 31, 2016 at 12:56 PM, Richard Hipp  wrote:

> On 10/27/16, Adam Goldman  wrote:
> > I expected the test case below to print 5679, but it prints 1235
> > instead. I tested under a few versions including 3.15.0. It's a bit of a
> > corner case and I worked around it in my application, but I guess it's a
> > bug.
> >
> > CREATE TABLE foo (bar INTEGER PRIMARY KEY AUTOINCREMENT);
> > INSERT INTO foo (bar) VALUES(1234);
> > UPDATE foo SET bar=5678;
> > DELETE FROM foo;
> > INSERT INTO foo DEFAULT VALUES;
> > SELECT * FROM foo;
>
> Yes, this is a discrepancy between the implementation and the
> documentation.  But the implementation of AUTOINCREMENT has *never*
> before modified the content of the sqlite_sequence table on an UPDATE,
> since the AUTOINCREMENT feature was first introduced in 2002 (SQLite
> version 2.5.2). For 14 years, AUTOINCREMENT has always worked as it
> does in 3.15.0.  If we "fix" the implementation now, we run a risk of
> breaking some of the millions of applications that use AUTOINCREMENT.
> For that reason, we have tentatively decided to update the
> documentation rather than the code.
>
> Note that the implied purpose of AUTOINCREMENT is to generate a unique
> and immutable identifier.  Running an UPDATE on an immutable
> identifier breaks the contract.  Even though this contract was not
> previously stated in the documentation, apparently most people
> understood it because this issue has never come up before in 14 years
> of heavy use.
>
> Thanks for pointing out the problem.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Development environment reccomendation

[Don V Nielsen]

Ruby to script out and automate the process. Could use Sinatra as a
lightweight web interface, or go Rails for a full blown web service.

On Mon, Oct 24, 2016 at 5:02 AM, Simon Slavin  wrote:

>
> On 24 Oct 2016, at 6:26am, Philip Rhoades  wrote:
>
> > What development environment would people suggest for building the
> sqlite app?
>
> If I understand correctly, 'mmssms.db' is itself a SQLite database file.
> So your choice comes down to whatever programming language/environment
> you're familiar with, as long as it supports the SQLite API or has a
> library which does.
>
> Ruby is fine, since you mention that:
>
> 
>
> 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

Re: [sqlite] Import 5000 xml files in a sqlite database file

[Don V Nielsen]

Ruby scripting language, using Nokogiri library to parse the html.

On Sun, Oct 23, 2016 at 11:12 AM, Preguntón Cojonero Cabrón <
preguntoncojon...@gmail.com> wrote:

> Scripting powershell? C#?
>
> El 23/10/2016 18:04, "Sylvain Pointeau" 
> escribió:
>
> > hello,
> >
> > I am not sure if Oxygen or another XML specialized software could do it,
> > however it would be easy done using C or C++ or Java. Advantage is that
> it
> > is then easy to automatize in a batch mode. I can propose you my services
> > if you are interested.
> >
> > Best regards,
> > Sylvain
> >
> > Le samedi 22 octobre 2016,  a écrit :
> >
> > > Hi,
> > >
> > > I have more than 5000 xml files. All files have the same xml-structure.
> > >
> > > Each file has different values (timestamps, numbers and strings). I
> would
> > > like to put all these values in a sqlite database tabke, all in one
> > table.
> > > => Import the data values into a sqlite database table.
> > >
> > > Can you please tell me a software program, that can do this quickly?
> > >
> > > Thank you for your answers.
> > >
> > > Best regards
> > >
> > > Bob
> > > ___
> > > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Don V Nielsen]

Not that I don't enjoy this conversation, but this should be end of this
thread. Thanks for telling me to look at shell.c. I did. Interesting read.
I can see that simply changing "  " to "" in the printf call, recompiling,
and tada, I have what I want.  Thanks David.

- HOWEVER -

New logic is required to make an appropriate update to shell.c such that
the current user experience is not changed. One cannot simply replace "  "
with p->colSeparator. That is because in column mode, the default .mode
column separator is hardcoded as "  ". However, the default colSeparator
value is a pipe '|' (which affects other modes.) The following
considerations have to be resolved:

a) .separator allows one to override colSeparator
b) specifying .separator and .column are not dependent on sequence in the
current implementation

So to maintain the current behavior:

c) some indicator must be in place to identify when colSeparator was changed
d) when mode is changed to .column, colSeparator would be internally set to
"  " only when colSeparator was not already changed by .separator
e) .separator would have to set the indicator when it was implemented

Not following c) thru e) would change the behaviors in a way that would
require the user to know:
f) changing modes will change internal default values, and to know what
those values are
g) .separator must be specified after .mode column to change the
colSeparator value from internal default

The above f) & g) are not currently required of the user. It's messy when
one thinks about it.

dvn

On Fri, Oct 14, 2016 at 2:27 PM, Don V Nielsen <donvniel...@gmail.com>
wrote:

> Thanks, David. I can read C, but I don't know how to compile it. :(  Just
> not a language I have learned.
>
> On Fri, Oct 14, 2016 at 10:22 AM, David Raymond <david.raym...@tomtom.com>
> wrote:
>
>> If you're using the CLI exclusively you can go into shell.c and get rid
>> of the double spaces. In the copy I'm looking at that's bundled with the
>> 3.14.2 amalgamation it looks like there're 5 lines to alter and you'll be
>> good.
>>
>> They're in
>> static int shell_callback(
>> ...
>> case MODE_Column: {
>> ...
>> Lines 1007, 1010, 1026 look like they deal with displaying the header line
>> Lines 1050 and 1054 deal with each row.
>>
>> All of those lines are the end of a utf8_printf( call and have...
>>
>> i==nArg-1 ? rowSep : "  ");
>>
>> I tried turning the 2 spaces there into "" recompiled it, and it seems to
>> display the way you're wanting it to.
>>
>> Of course since I don't know C, by doing that I have no clue what else I
>> might just have broken which relied on the old format, but hey :)
>>
>>
>> -Original Message-
>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>> On Behalf Of Don V Nielsen
>> Sent: Friday, October 14, 2016 7:39 AM
>> To: SQLite mailing list
>> Subject: Re: [sqlite] .mode column .width .separator
>>
>> Thank you, Gunter. I will investigate virtual tables; it's a bit over my
>> head at the moment.
>>
>> My final implementation was to route the select via .output, employ a CTE
>> to prepare the data, use printf to setup the record format, and then
>> select
>> the data from the CTE. Again, I do not enjoy the redundancy of the two
>> selects, but it gets the job done. A script will handle the statement in
>> the future.
>>
>>
>> with pre_process as (
>>   select
>>   ... columns blah blah ...
>> case when piecerate in ('AF','RF') and version_id = '81' then '81'
>> else
>> segment end as segment,
>>   ... columns blah blah ...
>>   from address_txt
>> )
>> select
>> printf(
>>
>> '%-1s%-15s%-5s%-4s%-2s%-1s%-4s%-4s%-1s%-5s%-1s%-10s%-10s%-10
>> s%-1s%-20s%-20s%-15s%-1s%-10s%-20s%-20s%-50s%-50s%-50s%-50s%
>> -50s%-25s%-2s%-8s%-3s%-6s%-9s',
>>   ... columns, blah blah ...
>> )
>> from pre_process
>> ;
>>
>>
>> Works like a champ.
>> Have a good one.
>>
>> On Fri, Oct 14, 2016 at 2:58 AM, Hick Gunter <h...@scigames.at> wrote:
>>
>> > You can eliminate separators by using
>> > .mode list
>> > .sepa ""
>> >
>> > But then you need to format your values to the correct widths for the
>> > record description. Text processing is not the primary domain of SQLite
>> and
>> > is best left to the presentation layer.
>> >
>> > Alternatively you may consider writing a virtual table module to do the
>> > processing for you. Semantics could be s

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

[Don V Nielsen]

Thanks, David. I can read C, but I don't know how to compile it. :(  Just
not a language I have learned.

On Fri, Oct 14, 2016 at 10:22 AM, David Raymond <david.raym...@tomtom.com>
wrote:

> If you're using the CLI exclusively you can go into shell.c and get rid of
> the double spaces. In the copy I'm looking at that's bundled with the
> 3.14.2 amalgamation it looks like there're 5 lines to alter and you'll be
> good.
>
> They're in
> static int shell_callback(
> ...
> case MODE_Column: {
> ...
> Lines 1007, 1010, 1026 look like they deal with displaying the header line
> Lines 1050 and 1054 deal with each row.
>
> All of those lines are the end of a utf8_printf( call and have...
>
> i==nArg-1 ? rowSep : "  ");
>
> I tried turning the 2 spaces there into "" recompiled it, and it seems to
> display the way you're wanting it to.
>
> Of course since I don't know C, by doing that I have no clue what else I
> might just have broken which relied on the old format, but hey :)
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Don V Nielsen
> Sent: Friday, October 14, 2016 7:39 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] .mode column .width .separator
>
> Thank you, Gunter. I will investigate virtual tables; it's a bit over my
> head at the moment.
>
> My final implementation was to route the select via .output, employ a CTE
> to prepare the data, use printf to setup the record format, and then select
> the data from the CTE. Again, I do not enjoy the redundancy of the two
> selects, but it gets the job done. A script will handle the statement in
> the future.
>
>
> with pre_process as (
>   select
>   ... columns blah blah ...
> case when piecerate in ('AF','RF') and version_id = '81' then '81' else
> segment end as segment,
>   ... columns blah blah ...
>   from address_txt
> )
> select
> printf(
>
> '%-1s%-15s%-5s%-4s%-2s%-1s%-4s%-4s%-1s%-5s%-1s%-10s%-10s%-
> 10s%-1s%-20s%-20s%-15s%-1s%-10s%-20s%-20s%-50s%-50s%-50s%-
> 50s%-50s%-25s%-2s%-8s%-3s%-6s%-9s',
>   ... columns, blah blah ...
> )
> from pre_process
> ;
>
>
> Works like a champ.
> Have a good one.
>
> On Fri, Oct 14, 2016 at 2:58 AM, Hick Gunter <h...@scigames.at> wrote:
>
> > You can eliminate separators by using
> > .mode list
> > .sepa ""
> >
> > But then you need to format your values to the correct widths for the
> > record description. Text processing is not the primary domain of SQLite
> and
> > is best left to the presentation layer.
> >
> > Alternatively you may consider writing a virtual table module to do the
> > processing for you. Semantics could be similar to:
> >
> > CREATE VIRTUAL TABLE mainframe_file USING mainframe ( '',
> > '', []); --> open filename, prepare to write in
> > specified format, write header
> > INSERT INTO mainframe_file SELECT ...; -> stuff in data, write record
> > DROP TABLE mainframe_file; -> close the file, write trailer record if
> > required
> >
> > -Ursprüngliche Nachricht-
> > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > Im Auftrag von Don V Nielsen
> > Gesendet: Donnerstag, 13. Oktober 2016 20:51
> > An: General Discussion of SQLite Database <sqlite-users@mailinglists.
> > sqlite.org>
> > Betreff: [sqlite] .mode column .width .separator
> >
> > I am using the command line shell SQLite 3.14.2 2016-09-12. I'm working
> > with mainframe data in a fixed format.
> >
> > I would like to use .mode column to create my output text file in a fixed
> > layout. I set all my column widths using .width. I then output my data
> to a
> > file. Unfortunately, there are two blanks separating each column, space I
> > don't want to be there. The .separator command does not provide any
> > mechanism for turning it off. Is there a way?
> >
> > I realize there is a printf function available. However, it appears that
> > output values must come from a table column, where as below, I could use
> > case statements in the sql select of the data.
> >
> > Any suggestions? I think I am overlooking a .separator option that says
> > "don't put spaces between output columns". I assume that John McKown has
> > faced this already, given his mainframe pedigree.
> >
> > Thanks for your time,
> > dvn
> >
> >
> >
> > Sample output:
> > "H  0NZOX0001687395  83501  5827  "...
> >
> > .mode column
> > .width 1 15 5 4 2 1 4 4 1 5 1 10 1

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

[Don V Nielsen]

Thank you, Gunter. I will investigate virtual tables; it's a bit over my
head at the moment.

My final implementation was to route the select via .output, employ a CTE
to prepare the data, use printf to setup the record format, and then select
the data from the CTE. Again, I do not enjoy the redundancy of the two
selects, but it gets the job done. A script will handle the statement in
the future.


with pre_process as (
  select
  ... columns blah blah ...
case when piecerate in ('AF','RF') and version_id = '81' then '81' else
segment end as segment,
  ... columns blah blah ...
  from address_txt
)
select
printf(

'%-1s%-15s%-5s%-4s%-2s%-1s%-4s%-4s%-1s%-5s%-1s%-10s%-10s%-10s%-1s%-20s%-20s%-15s%-1s%-10s%-20s%-20s%-50s%-50s%-50s%-50s%-50s%-25s%-2s%-8s%-3s%-6s%-9s',
  ... columns, blah blah ...
)
from pre_process
;


Works like a champ.
Have a good one.

On Fri, Oct 14, 2016 at 2:58 AM, Hick Gunter <h...@scigames.at> wrote:

> You can eliminate separators by using
> .mode list
> .sepa ""
>
> But then you need to format your values to the correct widths for the
> record description. Text processing is not the primary domain of SQLite and
> is best left to the presentation layer.
>
> Alternatively you may consider writing a virtual table module to do the
> processing for you. Semantics could be similar to:
>
> CREATE VIRTUAL TABLE mainframe_file USING mainframe ( '',
> '', []); --> open filename, prepare to write in
> specified format, write header
> INSERT INTO mainframe_file SELECT ...; -> stuff in data, write record
> DROP TABLE mainframe_file; -> close the file, write trailer record if
> required
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Don V Nielsen
> Gesendet: Donnerstag, 13. Oktober 2016 20:51
> An: General Discussion of SQLite Database <sqlite-users@mailinglists.
> sqlite.org>
> Betreff: [sqlite] .mode column .width .separator
>
> I am using the command line shell SQLite 3.14.2 2016-09-12. I'm working
> with mainframe data in a fixed format.
>
> I would like to use .mode column to create my output text file in a fixed
> layout. I set all my column widths using .width. I then output my data to a
> file. Unfortunately, there are two blanks separating each column, space I
> don't want to be there. The .separator command does not provide any
> mechanism for turning it off. Is there a way?
>
> I realize there is a printf function available. However, it appears that
> output values must come from a table column, where as below, I could use
> case statements in the sql select of the data.
>
> Any suggestions? I think I am overlooking a .separator option that says
> "don't put spaces between output columns". I assume that John McKown has
> faced this already, given his mainframe pedigree.
>
> Thanks for your time,
> dvn
>
>
>
> Sample output:
> "H  0NZOX0001687395  83501  5827  "...
>
> .mode column
> .width 1 15 5 4 2 1 4 4 1 5 1 10 10 10 1 20 20 15 1 10 20 20 50 50 50 50 50
> 25 2 8 003 006 009
> .output vo_pwprep.txt
> select
>   recid,
>   z_num,
>   zip,
>   zip4,
>   dpbc,
>   ckdig,
>   cart,
>   lot,
>   lot_order,
>   walk_seq,
>   walk_seq_bic,
>   case when piecerate in ('AF','RF') and version_id = '81' then '81' else
> segment end as segment,
>   version_id,
>   message,
>   seed,
>   seed_id,
>   seed_key,
>   planet,
>   ocr_acr,
>   priority,
>   keycode,
>   custno,
>   name,
>   title,
>   firm_id,
>   addr1,
>   addr2,
>   city,
>   state,
>   seq_number,
>   srvc_type,
>   imb_mid,
>   imb_serial
> from address_txt
> ;
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
>
> This communication (including any attachments) is intended for the use of
> the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please immediately notify the sender
> by return e-mail message and delete all copies of the original
> communication. Thank you for your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Don V Nielsen]

Thanks John. I had a feeling you would have encountered this sort of stuff.

And thanks for your time, Simon.

All is appreciated.

And thanks in advance, Dr. Hipp, if you act on this...allowing a \0 row
separator in column mode.  It would simplify the life of us mainframers.

dvn

On Thu, Oct 13, 2016 at 3:31 PM, John McKown <john.archie.mck...@gmail.com>
wrote:

> On Thu, Oct 13, 2016 at 2:42 PM, Don V Nielsen <donvniel...@gmail.com>
> wrote:
>
> > Thanks, but it appears that ".mode column" triggers it, and .separator
> does
> > not appear to have any influence in that mode.
> >
> > Out of curiosity, it appears that the row separator (in windows) is a
> > single character. Do you know to specify as the row separator?
> Everything
> > I attempt is taken as literal characters.  I've attempted: 0x0D0x0A,
> > 0x0D0A, 0Dx0Ax. Nothing appears to work.
> >
>
> ​The row separator is specified using the 2nd parameter of the .separator
> command. Example transcript:
>
>
> SQLite version 3.11.0 2016-02-15 17:29:24
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .mode column
> sqlite> .separator | -
> sqlite> create table a(one text,two text);
> sqlite> .width 10 10
> sqlite> insert into a values('1a','2a');
> sqlite> insert into a values('1b','2b');
> sqlite> select * from a;
> 1a  2a-1b  2b-sqlite>
>
> Note that the specified column separator is ignored in .mode column, but
> the row separator is not. Also, for fun, note what happens with negative
> widths
>
> sqlite> .width -10 -10
> sqlite> select * from a;
> 1a  2a-1b  2b-sqlite>
>
> Also, I have looked at the current sqlite3.c source code. In .mode column,
> the space separator character is "hard coded" and so cannot be set to any
> other character.​
>
> Lastly, you can specify a "control" character by using a C language escape.
> E.g. (continuing from above examples)
>
> sqlite> .separator - \n
> sqlite> select * from a;
> 1a  2a
> 1b  2b
>
> To address your desire, it would be necessary for the column separator
> character to be honored in .mode column mode and the separator be made a
> 0x00, or \0. If Dr. Hipp were to do this, this would eliminate the column
> separator entirely because \0 would result in "no" character between the
> columns. This appears, to me, to be a rather simple change in shell.c.
>
>
> > dvn
> >
> >
>
> --
> Heisenberg may have been here.
>
> Unicode: http://xkcd.com/1726/
>
> Maranatha! <><
> John McKown
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Don V Nielsen]

Thanks, but it appears that ".mode column" triggers it, and .separator does
not appear to have any influence in that mode.

Out of curiosity, it appears that the row separator (in windows) is a
single character. Do you know to specify as the row separator?  Everything
I attempt is taken as literal characters.  I've attempted: 0x0D0x0A,
0x0D0A, 0Dx0Ax. Nothing appears to work.

dvn

On Thu, Oct 13, 2016 at 2:11 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 13 Oct 2016, at 7:51pm, Don V Nielsen <donvniel...@gmail.com> wrote:
>
> > The .separator command does not provide any
> > mechanism for turning it off. Is there a way?
>
> Can't try it now but does
>
> .separator ""
>
> do what you want ?
>
> 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

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

[Don V Nielsen]

I can do this, which works. But the redundancy bothers me, and is prone to
finger-check errors.

with pre_process as (
  select
recid,
z_num,
zip,
zip4,
dpbc,
case when piecerate in ('AF','RF') and version_id = '81' then '81' else
segment end as segment,
  ... blah blah ...
  from address_txt
)
select
printf(
'%-1s%-15s%-5s%-4s%-2s%-1s%-4s%-4s%-1s%-5s%-1s%-10s%-10s%-10s%-1s%-20s%-20s%-15s%-1s%-10s%-20s%-20s%-50s%-50s%-50s%-50s%-50s%-25s%-2s%-8s%-003s%-006s%-009s',
recid,
z_num,
zip,
zip4,
dpbc,
segment
  ... blah blah ...
)
from pre_process
limit 10
;


On Thu, Oct 13, 2016 at 2:02 PM, Don V Nielsen <donvniel...@gmail.com>
wrote:

> These are simply blanks, 0x20, use to create separation of the output
> columns. I'm assuming this is an inherent behavior for readability. If the
> output was not being directed to the output file, it would be directed to
> the display.
>
> I'm trying to avoid pre processing (creating a table or view of the
> preprocessed data) and post processing (having to pass 10g of text data to
> removed blanks -- which is dangerous on its own.)
>
> dvn
>
> On Thu, Oct 13, 2016 at 1:56 PM, Simon Slavin <slav...@bigfraud.org>
> wrote:
>
>>
>> On 13 Oct 2016, at 7:51pm, Don V Nielsen <donvniel...@gmail.com> wrote:
>>
>> > Unfortunately, there are two blanks separating each column
>>
>> Can you tell what characters these are ?  Perhaps use a hexdump facility.
>>
>> My guess at this point is that you should continue with the file you have
>> already developed and then post-process it to remove the blank characters.
>> If those characters are used only in the blanks you don't want, it should
>> be possible to use a simple find/replace utility to do it.
>>
>> 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

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

[Don V Nielsen]

These are simply blanks, 0x20, use to create separation of the output
columns. I'm assuming this is an inherent behavior for readability. If the
output was not being directed to the output file, it would be directed to
the display.

I'm trying to avoid pre processing (creating a table or view of the
preprocessed data) and post processing (having to pass 10g of text data to
removed blanks -- which is dangerous on its own.)

dvn

On Thu, Oct 13, 2016 at 1:56 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 13 Oct 2016, at 7:51pm, Don V Nielsen <donvniel...@gmail.com> wrote:
>
> > Unfortunately, there are two blanks separating each column
>
> Can you tell what characters these are ?  Perhaps use a hexdump facility.
>
> My guess at this point is that you should continue with the file you have
> already developed and then post-process it to remove the blank characters.
> If those characters are used only in the blanks you don't want, it should
> be possible to use a simple find/replace utility to do it.
>
> 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

[sqlite] .mode column .width .separator

[Don V Nielsen]

I am using the command line shell SQLite 3.14.2 2016-09-12. I'm working
with mainframe data in a fixed format.

I would like to use .mode column to create my output text file in a fixed
layout. I set all my column widths using .width. I then output my data to a
file. Unfortunately, there are two blanks separating each column, space I
don't want to be there. The .separator command does not provide any
mechanism for turning it off. Is there a way?

I realize there is a printf function available. However, it appears that
output values must come from a table column, where as below, I could use
case statements in the sql select of the data.

Any suggestions? I think I am overlooking a .separator option that says
"don't put spaces between output columns". I assume that John McKown has
faced this already, given his mainframe pedigree.

Thanks for your time,
dvn



Sample output:
"H  0NZOX0001687395  83501  5827  "...

.mode column
.width 1 15 5 4 2 1 4 4 1 5 1 10 10 10 1 20 20 15 1 10 20 20 50 50 50 50 50
25 2 8 003 006 009
.output vo_pwprep.txt
select
  recid,
  z_num,
  zip,
  zip4,
  dpbc,
  ckdig,
  cart,
  lot,
  lot_order,
  walk_seq,
  walk_seq_bic,
  case when piecerate in ('AF','RF') and version_id = '81' then '81' else
segment end as segment,
  version_id,
  message,
  seed,
  seed_id,
  seed_key,
  planet,
  ocr_acr,
  priority,
  keycode,
  custno,
  name,
  title,
  firm_id,
  addr1,
  addr2,
  city,
  state,
  seq_number,
  srvc_type,
  imb_mid,
  imb_serial
from address_txt
;
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Help needed for COPY Command.

[Don V Nielsen]

You need to handle the load yourself.  Since you are talking bulk load, a
scripting language is probably out of the question because they are too
slow.  I used C# with the System.Data.Sqlite binary.

The db handling you need to worry about is the following:

connection to db
prepare an insert statement
read your input
while not eof
  populate prepared insert arguments
  execute the insert
  read your input
end
close connection

It's about that simple.  Depending on how many millions your are loading,
you might want to setup a transaction cycle and commit every so often.  I
think I committed every 1mm records.  It is a very fast load.

With what I was doing at the time, I started using Ruby because it was
quick to implement (less than 5 minutes).  But the execution time of the
load was going to be 24hrs or in that range.  Using a compiled language
(C#) the load happened in about 15 minutes (~750m a minute).  So the time
it took to write in C# was worth it.

If this is one time load, I have used small sqlite managers to do the bulk
load (Sqlite Expert to be exact.)  The bulk load is very fast.  But you
must go through the interactive wizard every time to do a load.  You cannot
save the setup and repeat the action from the command line, unfortunately.

dvn

On Mon, Mar 28, 2016 at 2:45 AM, Simon Slavin  wrote:

>
> On 28 Mar 2016, at 6:49am, Mahi Gurram  wrote:
>
> > I have tried .import and its working for command line interface. But i
> need
> > to do a bulk import (copy data from file into table) from c interface.
>
> Sorry, but SQLite has no facilities for handling text files.  It reads and
> writes to its own database files only.  You will have to write your own
> routines to read text from your text files.
>
> You might instead like to use system calls to script the command line
> shell:
>
> https://www.sqlite.org/cli.html
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] sqlite fixed data loading extension

[Don V Nielsen]

Well, like I mentioned, I do have the basic functionality that I want in an
existing C# application. Unfortunately it works in the opposite direction
that I was hoping...meaning it implements Sqlite and not Sqlite
implementing it. The functionality would be more generally usable if Sqlite
could implement that logic as an extension.

And maybe I could leverage that experience into developing a window
extension...row_number over( partition by/order by ).  Couple years ago I
was seeking said functionality from Sqlite but none existed.  And now that
I think about it, I probably can't avoid said C# application because of the
lack of windowing. I needed at that time some kind of compiled language
because I could not get the i/o performance I need from a scripting
language.

[it's been a day since I drafted the above email. but now these thoughts
have crossed my mind]

Has anyone else attempted writing a window extension for Sqlite? With
enough people requesting it, what difficulties are involved such that it is
low on a development list? What negatives does a window extension impose
that make it undesirable. What am I in for and what advice can you provide?

I probably can get away with a scripting language controlling my process. A
lot of the effort can be handed off to Sqlite in batch, handling the
queries of 20mm rows.  The final result set that needs to be windowed is
typically 5/10m rows, which should be pretty manageable...performance wise.

dvn - just the ramblings of a simple mind.



On Fri, Mar 25, 2016 at 4:48 PM, James K. Lowden 
wrote:

> On Fri, 25 Mar 2016 06:49:22 -0500
> Don V Nielsen  wrote:
>
> > I have a need for something that can parse and load into sqlite tables
> > fixed length data.
>
> Insert your own separators.
>
> $ cat input
> 12345678910111213141516171819202122232425
>
> Print two 5-byte ranges separated by ", ".
>
> $ awk '{ OFS=", "; print substr($0, 1, 5), substr($0, 6, 5); }'  \
> input
> 12345, 67891
>
> --jkl
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] sqlite fixed data loading extension

[Don V Nielsen]

I have a need for something that can parse and load into sqlite tables
fixed length data. I know sqlite has a csv import, so I would like to
duplicate that kind of functionality handling fixed columnar data. I
thought an extension would be perfect so I could specify something as
simple as "sqlite3 -fx parameter.dat" on the command line and it would
import the data.

Has anyone written a data loading extension already that would be willing
to share the source code with me? I have not written an sqlite extension
before, I know C# not C/C++, and leveraging someone else's effort would
help me a lot in learning the language and the extension. I've already
written this type of sqlite data loading logic into a specific application
using C#, but I would like it to create something more generic and
generally usable by anyone.

I've seen a lot of traffic on the mailing list about sqlite's csv import
abilities and wondered if someone has improved them with their own
extension. (I guess this begets the question..."Is sqlite's csv import an
extension?")

All the applications I have had experience with in the past (Sqlite Expert,
Navicat, and some others) require the user to run an application and setup
the functionality each and every time you used it. They do not give the
ability to save the import setup into a meaningful parameters file that can
be edited an rerun from the command line. So these types of applications
are ruled out. I would think I could do this in some fashion with
Informatica or like professional product, but at the expense of $$$ and
more $.

Any observations, comment, or suggestions? Is there a different mail list I
should hit up?

Thanks for your time and consideration
dvn


"My dad said to me as a child, 'Why be taught what you can learn on your
own. That's why he would just throw me in the lake...so he could learn
CPR." - Anthony Jeselnik.

[sqlite] SQLite Pronunciation

[Don V Nielsen]

> appear to know slightly what I was talking about

O, I'm confident that I'm not the only one that relates to that
assertion

On Thu, Mar 17, 2016 at 12:25 PM,  wrote:

> > On 3/16/16, Daniel Telvock  wrote:
> >
> >> I was at the Investigative Reporters and Editors Conference last week
> >> and the presenter for SQLite courses 1 and 3 said that it is actually
> >> pronounced SQ Lite. Even he thought that was odd considering SQL is a
> >> term or acronym.
> >
> > I wrote SQLite, and I think it should be pronounced "S-Q-L-ite".  Like
> > a mineral.  But I'm cool with y'all pronouncing it any way you want. :-)
> >
> >
> > --
> > D. Richard Hipp
> > drh at sqlite.org
>
>
> Thank you all for the responses, video reference, and apologizes for
> the origin post of pasting directly from the Wikepedia webpage, they
> have (eskju:el'lait) or (si:kwel.lait).
>
> I'm making some tutorial videos for my project and just wished to
> appear to know slightly what I was talking about when using SQLite,
> pronouncing, for the examples database connection for demonstration.
>
> Dana Proctor
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] SQLite Pronunciation

[Don V Nielsen]

WHAT A GREAT VIDEO.  Thanks for sharing.

Everyone I work with...or worked with...that sees me work with SQLite
(sequel lite to me) asks...what is that?  why are using that?  I love it.
It is the best configuration manager.

On Wed, Mar 16, 2016 at 2:09 PM, Jay Kreibich  wrote:

>
>
> IIRC, the SQL ISO standard defines the pronunciation of the term ?SQL? as
> "ess-cue-ell?, although if you trace SQL back to the IBM days of SEQUEL,
> there is a strong argument that the term ?sequel? makes more sense.
>
> I know when the SQLite development team speaks about it, they tend to use
> the term ?ess cue ell lite? (technically doubling the ?L?).
>
> https://www.youtube.com/watch?v=Jib2AmRb_rk
>
> When writing the book ?Using SQLite,? I actually had a rather long
> discussion with my editor about this, since the pronunciation affects the
> choice of words when talking about ?an ess-cue-ell lite database? vs ?a
> sequel-lite database? (?an? vs ?a?).  Knowing the development team tends to
> pronounce the letters, I went with ?an.?
>
>   -j
>
>
>
> On Mar 16, 2016, at 1:38 PM, danap at dandymadeproductions.com wrote:
>
> > Hello,
> >
> > Please grant me some leeway here since as someone who has
> > not been in an academic school for years and is mainly self
> > taught. I have Mainly deriving information from reading
> > manuals and occasionally viewing some videos.
> >
> > Maybe I'm wrong, but according to Wikepedia SQLite appears
> > to be pronounced the same has it is spelled,
> > (sikwl.lat).
> > Maybe not a long A there perhaps.
> >
> > Where as I first heard Microsoft's MSSQL pronounce (sequent),
> > which I have also heard in academic videos by professors.
> > Following that logic, SQLite, (sequent.light)?
> >
> > Dana Proctor
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> --
> 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
>

[sqlite] Parsing the contents of a field

[Don V Nielsen]

You will thank yourself by using a scripting language such Ruby, php, or
python.

Is there a reg ex library for sqlite that could be employed?

On Tue, Jan 12, 2016 at 11:42 PM, audio muze  wrote:

> I have a table of roughly 500k records with a number of fields
> containing delimited text that needs to be parsed and written to
> separate tables as a master lists.  In order to do this I need to
> parse the field contents, however, I don't see any functions within
> SQLite to enable that.  The number of delimited entries embedded in a
> field can vary from none to as man as 20/30.  Is there an addin I can
> compile with SQLite that provides the ability to parse a string?
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] website documentation wording

[Don V Nielsen]

Tangeant off this note, kind of a history question.  "an SQLite".  I
personally would write "a SQL" because it is "sequel" to me.  When did
SQL--sequel become SQL--ess queue ell?  I always remember it as being
sequel, and it rolls off the tongue easier.  And as sequel, it would be "a
SQLite".

Happy Holidays, all.
dvn

On Thu, Dec 3, 2015 at 3:41 PM, Bernardo Sulzbach  wrote:

> Good catch, Dirk
>
> On Thu, Dec 3, 2015 at 4:47 PM, Dirk Jagdmann  wrote:
> > I'm currently looking at https://www.sqlite.org/autoinc.html
> >
> > I suggest you change "You can access the ROWID of an SQLite table using
> > one {of} the special column names..." and insert the word "of".
> >
> > --
> > ---> Dirk Jagdmann
> > > http://cubic.org/~doj
> > -> http://llg.cubic.org
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> --
> Bernardo Sulzbach
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] sqlite window functions extension?

[Don V Nielsen]

I'm disappointed.  I killed it after 1.5hrs, and suspected everything you
explained in the other thread.  It is always true.

Que Sera Sera

dvn

On Fri, Oct 16, 2015 at 2:33 PM, Igor Tandetnik  wrote:

> On 10/16/2015 3:23 PM, Don V Nielsen wrote:
>
>>  limit (
>>SELECT net_non_pieces
>>FROM crrt_net_non [b]
>>WHERE [b].zip = zip AND [b].crrt = crrt
>>
>
> Again, I don't think this does what you think it does. Test with at least
> two rows in crrt_net_non, with different values for net_non_pieces, and
> confirm that as many rows are being updated as you've hoped. I suspect you
> may be disappointed.
>
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] sqlite window functions extension?

[Don V Nielsen]

Wrapping up this thread.

What I am going to go with is the following.  I'm using the initial WITH to
reduce as much as possible the number of records to be repeatedly searched:
maybe 25/30%.  The addresses table currently has 4.4mm rows; it will go up
to 11.5mm when this goes to prod.  I'm testing performance right now.
Might not be too good.

begin transaction;
with ver_addresses AS (
  SELECT rowid as id,zip,crrt
  from addresses
  where version_id = '0060'
)
update addresses
set segment = '60'
where
  rowid in (
select id
from ver_addresses [a]
where [a].zip = [addresses].zip and [a].crrt = [addresses].crrt
limit (
  SELECT net_non_pieces
  FROM crrt_net_non [b]
  WHERE [b].zip = zip AND [b].crrt = crrt
)
  );
commit;


On Fri, Oct 16, 2015 at 9:24 AM, Don V Nielsen 
wrote:

> The final solution at this point is Ruby.  I really want to push
> everything I can into Sqlite because *it is so freakin fast!*
> Unfortunately. I I am just not getting it.  Also unfortunately, iterating,
> getting data, and updating data in scripting languages is not efficient.
> I've implemented a transaction for each crrt_net_non iteration, but that is
> still painfully slow.  I will have to be more creative in my data handling
> (move to dbi versus activerecord) and partitioning of transactions
> (committing after every 100,000 updates, for example.)
>
> NetNon.all.each {|nn|
>   ActiveRecord::Base.transaction {
> addrs = Address.
> where(zip:nn.zip,crrt:nn.crrt,version_id:non_sel_ver.ver_id).
> select(:id,:segment).
> take(nn.net_non_pieces)
> addrs.each {|addr| addr.update(segment:'xx') }
>   }
>   pb.inc
> }
>
> In English..get all rows from crrt_net_non: it has the control quantity
> net_non_pieces.  Select from addresses all rows with the same zip and crrt
> values, and matching the version to be updated.  I only need columns id and
> segment, and take only the number of records as calculated in
> net_non_pieces.  Update the segment code.  Loop.
>
> On Thu, Oct 15, 2015 at 9:47 AM, Don V Nielsen 
> wrote:
>
>> I'm surprised that and extension for this type of functionality has not
>> been been developed by someone with the c/c++.  It's seems like a natural
>> fit.  I wish I had the kind of ability & smarts to do it.
>>
>> On Thu, Oct 15, 2015 at 9:04 AM, Igor Tandetnik 
>> wrote:
>>
>>> On 10/15/2015 9:36 AM, Don V Nielsen wrote:
>>>
>>>>  limit ifnull( (
>>>>select net_non_pieces from crrt_net_non net
>>>>where net.zip=zip and net.crrt=crrt
>>>>
>>>
>>> I suspect this WHERE clause is equivalent to "where net.zip=net.zip and
>>> net.crrt=net.crrt" - that is, always true. The actual limit value comes
>>> from whichever row accidentally happens to be first.
>>>
>>> --
>>> Igor Tandetnik
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>

[sqlite] Puzzled about table alias and where they are accessible

[Don V Nielsen]

Given the following:

select * from tmp_addresses [x]
where
  rowid in (
select rowid
from tmp_addresses [a]
where a.version_id = '0060'
  and [a].zip = [x].zip and [a].crrt = [x].crrt
limit (
  SELECT net_non_pieces
  FROM crrt_net_non [b]
  WHERE [b].zip = [x].zip AND [b].crrt = [x].crrt
)
  )

The above sql fails parsing because of the alias [x] usage in the limit
clause.  If I eliminate the [x] alias from the limit, i.e. "WHERE [b].zip =
zip AND [b].crrt = crrt", then the sql parses and executes.  Note the
performance: [2015-10-16 13:49:37] completed in 2m 30s 730ms.

I'm curious as to why the alias is not accessible from the limit sub-query?

[sqlite] sqlite window functions extension?

[Don V Nielsen]

The final solution at this point is Ruby.  I really want to push everything
I can into Sqlite because *it is so freakin fast!*  Unfortunately. I I am
just not getting it.  Also unfortunately, iterating, getting data, and
updating data in scripting languages is not efficient.  I've implemented a
transaction for each crrt_net_non iteration, but that is still painfully
slow.  I will have to be more creative in my data handling (move to dbi
versus activerecord) and partitioning of transactions (committing after
every 100,000 updates, for example.)

NetNon.all.each {|nn|
  ActiveRecord::Base.transaction {
addrs = Address.
where(zip:nn.zip,crrt:nn.crrt,version_id:non_sel_ver.ver_id).
select(:id,:segment).
take(nn.net_non_pieces)
addrs.each {|addr| addr.update(segment:'xx') }
  }
  pb.inc
}

In English..get all rows from crrt_net_non: it has the control quantity
net_non_pieces.  Select from addresses all rows with the same zip and crrt
values, and matching the version to be updated.  I only need columns id and
segment, and take only the number of records as calculated in
net_non_pieces.  Update the segment code.  Loop.

On Thu, Oct 15, 2015 at 9:47 AM, Don V Nielsen 
wrote:

> I'm surprised that and extension for this type of functionality has not
> been been developed by someone with the c/c++.  It's seems like a natural
> fit.  I wish I had the kind of ability & smarts to do it.
>
> On Thu, Oct 15, 2015 at 9:04 AM, Igor Tandetnik 
> wrote:
>
>> On 10/15/2015 9:36 AM, Don V Nielsen wrote:
>>
>>>  limit ifnull( (
>>>select net_non_pieces from crrt_net_non net
>>>where net.zip=zip and net.crrt=crrt
>>>
>>
>> I suspect this WHERE clause is equivalent to "where net.zip=net.zip and
>> net.crrt=net.crrt" - that is, always true. The actual limit value comes
>> from whichever row accidentally happens to be first.
>>
>> --
>> Igor Tandetnik
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>

[sqlite] sqlite window functions extension?

[Don V Nielsen]

I'm surprised that and extension for this type of functionality has not
been been developed by someone with the c/c++.  It's seems like a natural
fit.  I wish I had the kind of ability & smarts to do it.

On Thu, Oct 15, 2015 at 9:04 AM, Igor Tandetnik  wrote:

> On 10/15/2015 9:36 AM, Don V Nielsen wrote:
>
>>  limit ifnull( (
>>select net_non_pieces from crrt_net_non net
>>where net.zip=zip and net.crrt=crrt
>>
>
> I suspect this WHERE clause is equivalent to "where net.zip=net.zip and
> net.crrt=net.crrt" - that is, always true. The actual limit value comes
> from whichever row accidentally happens to be first.
>
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] sqlite window functions extension?

[Don V Nielsen]

The correlated query was not accepted in the select statement.  However, I
modified your original UPDATE query, as follows, and it did execute, and
passed the proper quantity of rows for the first zip/crrt combination,
only.  The rows were not updated.  It counted correctly, but did not change
the values.

sql> UPDATE tmp_addresses
  set segment='xx'
  where rowid in (
select x2.rowid from tmp_addresses x2 where x2.zip=zip and x2.crrt=crrt
order by
  CASE
  WHEN x2.version_id = '0060' THEN 0
  WHEN x2.version_id = '0064' THEN 2
  WHEN x2.version_id = '0061' THEN 3
  ELSE 99
  END ASC
limit ifnull( (
  select net_non_pieces from crrt_net_non net
  where net.zip=zip and net.crrt=crrt
), 0)
  )
[2015-10-15 06:54:18] *42 row(s)* affected in 189ms

I have not yet attempted your most recent suggestion.  I will get to that
some time today.



On Wed, Oct 14, 2015 at 9:21 PM, Igor Tandetnik  wrote:

> On 10/14/2015 9:29 PM, Don V Nielsen wrote:
>
>> But I am having problems with the LIMIT statement.  It throws an exception
>> no matter what table alias is used: X or x2.  It says "no such column".
>>
>
> Ah, interesting. LIMIT clause doesn't appear to allow correlated
> subqueries; only self-contained expressions. Here goes that idea.
>
> Something like this should work:
>
> select X.*
> from addresses X join crrt_net_non_pieces using (zip, crrt)
> where net_non_pieces > (
>   select count(*) from addresses x2
>   where X.zip=x2.zip and X.crrt=x2.crrt and
>   (x2.version_id < X.version_id or (x2.version_id = X.version_id and
> x2.rowid < X.rowid))
> );
>
> I took the liberty to simplify the ordering expression, for purposes of
> exposition (it's very long and would need to be repeated four times).
> Replace all occurrences of T.version_id with your CASE clause, calculated
> against table T. Also, I'm breaking ties by rowid; your original problem
> statement is underspecified unless there's a total order on Addresses.
>
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] sqlite window functions extension?

[Don V Nielsen]

The only reason NUM exists in the result set is so I could use it to limit
the outer most select.

But I am having problems with the LIMIT statement.  It throws an exception
no matter what table alias is used: X or x2.  It says "no such column".

Thanks for your time, by the way.

On Wed, Oct 14, 2015 at 6:08 PM, Igor Tandetnik  wrote:

> On 10/14/2015 6:24 PM, Don V Nielsen wrote:
>
>> zip crrt   version_id   NUM   segment
>> [truncated num 1..38]
>> 53001   R501   0060 39xx
>> 53001   R501   0060 40xx
>> 53001   R501   0060 41xx
>> 53001   R501   0060 42xx
>> [truncated num 1..24]
>> 53001   R502   0060 21xx
>> 53001   R502   0060 22xx
>> 53001   R502   0060 23xx
>> 53001   R502   0060 24xx
>>
>
> select * from addresses X where rowid in (
>   select x2.rowid from addresses x2 where X.zip=x2.zip and X.crrt=x2.crrt
>   order by (CASE ...)
>   limit ifnull( (
>  select net_non_pieces from crrt_net_non net
>  where X.zip=net.zip and X.crrt=net.crrt
>   ), 0)
> );
>
> Do you actually need NUM column in the resultset? That one would be tricky
> to pull off.
> --
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] sqlite window functions extension?

[Don V Nielsen]

Table crrt_net_non_pieces:

zip crrt   pkg_pieces   sel_pieces   non_pieces   net_non_pieces
53001   R501   52   646   42
53001   R502   34   727   24


Addresses (as I have been referring to as X) is going to have too many rows
(86 for this example, 52 rows for 53001.R501 and 34 rows for 53001.R502.
The calculation result says I need 42 of the 52 X rows from 53001.R501 to
be recoded and 24 of the 34 X rows from 53001.R502.

Addresses table:

zip crrt   segment   version_id
53001   R501   060060
53001   R501   060060
53001   R501   060060
53001   R501   060060
53001   R501   060060

In sql server, I would be doing something like this:

  select x.* ,'xx' as segment from ( select -- count(1) a.zip ,a.crrt
,a.version_id ,ROW_NUMBER() OVER ( PARTITION BY a.zip,a.crrt ORDER BY CASE WHEN
a.version_id = '0060' THEN 0 WHEN a.version_id = '0064' THEN 2 WHEN
a.version_id = '0061' THEN 3 ELSE 99 END ASC ) AS NUM from
dbwork..addresses a )x join dbwork..crrt_net_non y on y.zip = x.zip and
y.crrt = x.crrt where x.num <= y.net_non_pieces ;
And the results would come out something like the following:  I truncate
the rows with num values 1..whatever.  Notice NUM is the sequential value
applied to the input (1..52 in the case of R501 and 1..34 in the case of
R502).  I then only kept the number of rows as identified by the
corresponding y.net_non_pieces value.

zip crrt   version_id   NUM   segment
[truncated num 1..38]
53001   R501   0060 39xx
53001   R501   0060 40xx
53001   R501   0060 41xx
53001   R501   0060 42xx
[truncated num 1..24]
53001   R502   0060 21xx
53001   R502   0060 22xx
53001   R502   0060 23xx
53001   R502   0060 24xx


Is this better?  Sorry that I imagined/assumed that you guys just picture
this stuff in your head.  I get that impression reading this list.

dvn


These window functions seem to be some sort of Holy Grail that I just can't
imagine duplicating without it.


On Wed, Oct 14, 2015 at 4:27 PM, R.Smith  wrote:

>
>
> On 2015-10-14 11:20 PM, Don V Nielsen wrote:
>
>> X has columns zip & crrt, just like crrt_net_non.  These form a composite
>> key identifying groups within x.  A value "53001.R501" would be an
>> example...53001 being the zip code and R501 being the carrier route.
>> There
>> are 52 rows in X that have the key 53001.R501.  A calculation determined
>> that I need 42 rows from that key and saved the result in crrt_net_non,
>> the
>> row looking like "53001.R501.52.6.46.42".  What I need is a sql function
>> that can iterate over crrt_net_non, then grab the rows from X,
>> "53001.R501"
>> being first key, sort them into an internal group sequence, then update a
>> code of the first 42 rows of that sorted group, and then doing this until
>> crrt_net_non is exhausted.
>>
>
> Hi Don, usually a CTE can be pressed into service to make things work.
> Maybe others know exactly what you mean here but it's a bit Greek to me.
>
> As Igor suggested - perhaps some data to accompany this schema and an
> example result-set (or resulting updated table) from it will make it clear
> what you want to achieve with the query. Feel free to simplify so the
> essence of what is needed remain, but be sure to give an example that
> cannot be achieved by other arbitrary means.
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] sqlite window functions extension?

[Don V Nielsen]

X has columns zip & crrt, just like crrt_net_non.  These form a composite
key identifying groups within x.  A value "53001.R501" would be an
example...53001 being the zip code and R501 being the carrier route.  There
are 52 rows in X that have the key 53001.R501.  A calculation determined
that I need 42 rows from that key and saved the result in crrt_net_non, the
row looking like "53001.R501.52.6.46.42".  What I need is a sql function
that can iterate over crrt_net_non, then grab the rows from X, "53001.R501"
being first key, sort them into an internal group sequence, then update a
code of the first 42 rows of that sorted group, and then doing this until
crrt_net_non is exhausted.

On Wed, Oct 14, 2015 at 4:03 PM, Igor Tandetnik  wrote:

> On 10/14/2015 4:49 PM, Don V Nielsen wrote:
>
>> What am I looking to do?  Using a table X, I've built a summary table,
>> calculating a value called net_non_pieces.  net_not_pieces is a qty of
>> rows
>> from a group rows that needs to be recoded to a different value.  So what
>> I
>> want to do is for each group in X, look up the calculated value in
>> crrt_net_non, then grab the first net_non_pieces (sequenced, of course)
>> and
>> apply a code to those rows, leaving the remaining rows alone.
>>
>
> It might help if you show the definition of X, a sample of data in it, and
> the desired result of the query over that data. I, for one, have difficulty
> following your description.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] sqlite window functions extension?

[Don V Nielsen]

Is there an extension to sqlite the provide window functions such as
row_number over and partition?  There are plenty of googles seeking said
functionality.

What am I looking to do?  Using a table X, I've built a summary table,
calculating a value called net_non_pieces.  net_not_pieces is a qty of rows
from a group rows that needs to be recoded to a different value.  So what I
want to do is for each group in X, look up the calculated value in
crrt_net_non, then grab the first net_non_pieces (sequenced, of course) and
apply a code to those rows, leaving the remaining rows alone.

I'm not sure how to accomplish such a thing

CREATE TABLE crrt_net_non -- developed from table X
(
zip TEXT,   --composite key
crrt TEXT,  --composite key
pkg_pieces integer,  --total row in group
sel_pieces integer,
non_pieces integer,
net_non_pieces integer  --qty of this group to recode
);

Its a pretty innocent thing to do with window functions.  It is not trivial
to do it without.  I can't even figure out how.

Why am I not dumping Sqlite and going with prostgres or the like?  Because
sqlite is awesome.  It is a portable file, easily created as I need for
each project, that can be moved around or deleted, making it super easy to
maintenance in my production environment.  Love the thing.  But this
problem has me flummuxed.

Thanks for your time and consideration,
dvn

[sqlite] Using CTE with INSERT

[Don V Nielsen]

This is an IDE issue.  If I run my queries from the command line, all is
good.  Sorry for the confusion.  It is when I run my queries through my
Rubymine IDE that I am getting exceptions thrown.  I need to investigate
what version of Sqlite is being implemented.

Something learned is always a good experience,
dvn

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

> The syntax "WITH p AS (SELECT 1) INSERT INTO t(x) SELECT * FROM p;" throws
> the same exception: "[2015-10-13 08:20:22] [1] [SQLITE_ERROR] SQL error or
> missing database (near ")": syntax error)"
>
>
>
> On Tue, Oct 13, 2015 at 8:17 AM, Clemens Ladisch 
> wrote:
>
>> Don V Nielsen wrote:
>> > I'm struggling to implement an INSERT statement that get's is values
>> from a
>> > CTE.
>> >
>> > insert into vo_crrt_pieces (recid)
>> > values (
>> >   with
>> >   pkg_controls AS (
>> > SELECT * FROM d_pkg WHERE pkg_level = 'CRD'
>> >   )
>> >   -- select pieces that meeting pkg and pkg_level minimums
>> >   , min_pkgs_met AS (
>> >   ...
>> >   )
>> >   SELECT recid
>> >   FROM addresses [c]
>> >   JOIN min_pkgs_met [a] ON a.zip = c.zip AND a.crrt = a.crrt
>> > )
>>
>> Without the database schema, this is impossible to test.
>>
>> A CTE is allowed where a SELECT is allowed, so you have to use the
>> SELECT form of the INSERT statement:
>>
>>  CREATE TABLE t(x);
>>  INSERT INTO t(x) SELECT 1;
>>
>>  INSERT INTO t(x) WITH p AS (SELECT 1) SELECT * FROM p; -- or:
>>  WITH p AS (SELECT 1) INSERT INTO t(x) SELECT * FROM p;
>>
>>
>> Regards,
>> Clemens
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>