Re: [sqlite] "Responsive" website revamp at www.sqlite.org

2016-09-06 Thread Stephan Beal
On Tue, Sep 6, 2016 at 2:26 PM, Richard Hipp <d...@sqlite.org> wrote:

> On 9/6/16, Nelson, Erik - 2 <erik.l.nel...@bankofamerica.com> wrote:
> >
> > What percentage of sqlite.org hits are mobile browsers?
>
> How do I tell?
>
> The website saw 2748 distinct UserAgent strings within just the past
> 24 hours (a holiday in the USA, FWIW).  How do I tell which of those
> are bots, mobile devices, and/or desktops?
>
>
Two projects i've worked on the past few years have tasked me with figuring
that out. The answer is, unfortunately, that it cannot be done anywhere
near reliably. They are free-form strings, often intentionally obfuscated
(MSIE does this now, we recently discovered, presumably to try to keep
people from implementing IE-specific workarounds).

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] "Responsive" website revamp at www.sqlite.org

2016-09-06 Thread Stephan Beal
On Tue, Sep 6, 2016 at 10:30 AM, Eric Grange <zar...@gmail.com> wrote:

> > A counter-opinion, though apparently in the small minority: i _absolutely
> > despise_ fixed-width web site layouts.
>
> Just to clarify, this is not fixed width, but limited max width, ie. it
> only kicks in when the browser window is very large. The site is fluid
> (like now) at smaller widths.
>

And i mis-typed :/. i _despise_ both fixed widths and designer-specified
maximum widths ;). No designer on this planet knows what my screen
resolution/viewing preferences are and has no business assuming they do.
It's likely that assumption about my preferences/limits which annoys me
more than the limitation itself does.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] "Responsive" website revamp at www.sqlite.org

2016-09-06 Thread Stephan Beal
On Tue, Sep 6, 2016 at 10:15 AM, Eric Grange <zar...@gmail.com> wrote:

> However, while you are at it, an improvement for the website when browsed
> from a computer would be to limit the max width of the pages, especially
> for the documentation pages. Currently if you have a big screen the lines
> of text stretch to the whole browser width, which is not very readable.
>

A counter-opinion, though apparently in the small minority: i _absolutely
despise_ fixed-width web site layouts.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Why MMAP return ENOMEM in SQLite?

2016-08-31 Thread Stephan Beal
On Wed, Aug 31, 2016 at 11:03 AM, sanhua.zh <sanhua...@foxmail.com> wrote:

> Why do you think it will corrupt the database?
> Can you give me more explainations or examples?
>

It's only my intuition - i don't have a concrete example. sqlite and ios
are "well-oiled machines." They do their jobs and they do it well. If you
start interfering with that, trying to take over or abuse their
responsibilities because you think you can do it better, you will
_eventually_ run into problems. In my experience, the chances of a
back-fire when trying to push software beyond what it's designed to do are
very high.

You explicitly want to add complexity to an already complex system.
Additional complexity almost always comes with a higher bug rate. A
telephone is _not_ a high-performance computing platform, but a
_convenience_ platform. Whether a db operation takes 10ms or 800ms should,
for such platforms, be irrelevant. i _suspect_ that you are overestimating
the impact of your perceived performance problem on the end users.

But that's all just my opinion based on experience - i have no facts or
statistics to back it up. Maybe it will work well for you.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Why MMAP return ENOMEM in SQLite?

2016-08-31 Thread Stephan Beal
On Wed, Aug 31, 2016 at 10:55 AM, sanhua.zh <sanhua...@foxmail.com> wrote:

> Yes, [mmap] can be larger than physical memory.
>

Indeed, my mistake.


> And what do you think about the new mapping way I mentioned ?
>

i think it's a "huge can of worms" - it's asking for more, bigger problems
than the perceived performance problems you have right now. The chances
that something breaks with that approach is, i suspect, very high. You will
eventually corrupt a database and then write back to the list to ask why
that approach corrupted it.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Why MMAP return ENOMEM in SQLite?

2016-08-31 Thread Stephan Beal
On Wed, Aug 31, 2016 at 10:43 AM, Stephan Beal <sgb...@googlemail.com>
wrote:

> On Wed, Aug 31, 2016 at 10:39 AM, sanhua.zh <sanhua...@foxmail.com> wrote:
>
>> In my testcase, I can only [mmap] a db file at most 1.4GB size. But in
>> this new way, I can map a file at most 3.2GB. The test device is iPhone 6S.
>>
>
> According to google, the iPhone 6s only has 2GB of RAM, so you can't
> memmap 3.2G.
>

Correction: you can map larger than physical memory, up to limits set by
the OS environment. In your case, see:

http://stackoverflow.com/questions/9184773/is-there-a-practical-limit-on-the-number-of-memory-mapped-files-in-ios


> In any case, as Simone said, _other apps_ require memory of their own as
> well.
>

Correction 2: Simon, not Simone (my roommate's name, so i type the 'e' out
of habit)

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Why MMAP return ENOMEM in SQLite?

2016-08-31 Thread Stephan Beal
On Wed, Aug 31, 2016 at 10:39 AM, sanhua.zh <sanhua...@foxmail.com> wrote:

> In my testcase, I can only [mmap] a db file at most 1.4GB size. But in
> this new way, I can map a file at most 3.2GB. The test device is iPhone 6S.
>

According to google, the iPhone 6s only has 2GB of RAM, so you can't memmap
3.2G. In any case, as Simone said, _other apps_ require memory of their own
as well. Your app does not have that whole 2G to itself.

-- 
----- stephan beal
http://wanderinghorse.net/home/stephan/
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] SQLite in VS2015 Server Explorer (Bugs)

2016-08-24 Thread Stephan Beal
On Tue, Aug 23, 2016 at 4:13 PM, Tyler Merle <tme...@invsqr.com> wrote:

> I believe I’ve uncovered two bugs when using VS2015’s Server Explorer to
> design a SQLite schema.
>
> 1. When adding more than one relationship to a table, the first
> relationship’s “to” table name is used for all new relationships
> 2. The right-click -> design option for tables doesn’t open the table
> design on the first try. Takes two right-click -> design selections to open
> the designer
>

Wrong mailing list. This is the list for the sqlite library and its shell
program (neither of which include a schema design tool). Server Explorer is
a 3rd-party product.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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

2016-08-11 Thread Stephan Beal
On Thu, Aug 11, 2016 at 7:53 AM, Michael Falconer <
michael.j.falco...@gmail.com> wrote:

> Thanks Jay,
>
> excellent response. I'll ask for clarity on one statement though.
>
> That’s the basic theory, but even knowing that, most people get it wrong.
> > In short, if you’re using string manipulation functions to build your
> query
> > string, you’re very very very very likely doing it wrong.
> >
>
> I have a self styled routine (similar to the glibc manual example) for
> concatenating the strings values that make up the sql statement. It uses
> memcpy rather than the built in strcat etc. So what exactly is the issue
> with the string building if it does  not include sql derived from user
> input? I'm not quite seeing that bit, sorry or the vagueness.
>

This short strip explains SQL injection better than any book, IMO:

https://xkcd.com/327/


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

FWIW, internally, exec() is just a proxy for prepare/step/finalize.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [Spellfix] Inexplicable discrepancy concerning query results

2016-07-21 Thread Stephan Beal
On Thu, Jul 21, 2016 at 10:54 AM, Adamek, Jochen (CQTN) <
jochen.ada...@carmeq.com> wrote:

> The first 4 rows have a different score and a different distance. This
> discrepancy occurs with most example queries. But changing the scope value
> of the query in my Python code, it changes the result, so it is not the
> case, that SQLite does not process the scope operation via the
> Python-SQLite interface.
>
> Any idea?
>

It sounds to me like your python and local sqlite shell are using different
sqlite versions.


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] ":memory:" path does not seem to work on Windows anymore

2016-05-30 Thread Stephan Beal
On Mon, May 30, 2016 at 8:35 AM, Kirill Müller <krlmlr...@mailbox.org>
wrote:

> I can't reproduce the issue on Windows with the current command-line
> client, but it is real in our environment. How can I help you replicate it?
> Thanks.
>
>
> -Kirill
>
> On 26.05.2016 14:57, Kirill Müller wrote:
>
>> Hi
>>
>>
>> In the R interface to SQLite [1], we observe that opening a database with
>> ":memory:" does not work anymore on Windows (both 32- and 64-bit versions),
>
>
might it be a problem with the R wrapper? If it cannot be reproduced in the
cli client then the wrapper is the most likely culprit.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] sqlite3_prepare*() statement argument detailed

2016-05-16 Thread Stephan Beal
On Mon, May 16, 2016 at 2:58 PM, E.D.  wrote:

> Hi.
>
> The sqlite3 documentation specifies, that sqlite3_prepare*() compile "the
> first statement" from the sqlite3_stmt ** argument. This argument ought to
> be explained thoroughly.
> What exactly can be put through this argument? Compound statements,
> transactions? What syntax?
>

The first _SQL statement_ is processed. Anything after that is ignored. e.g.

select 1 from foo; -- first statement ends here
select 2 from foo; -- this is ignored.


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] Podcast with Dr Hipp: SQLite history, success and funding

2016-05-15 Thread Stephan Beal
On Sun, May 15, 2016 at 2:29 PM, Tim Streater  wrote:

> What's all this about licences. AIUI, SQLite is explicitly in the public
> domain. Meaning the question of licence doesn't arise.
>

it does, actually, because PD is not recognized in all jurisdictions.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] Why the back-up database file does not shrink as the source database shrink?

2016-05-13 Thread Stephan Beal
On Fri, May 13, 2016 at 8:29 AM, Yihong Zhan  wrote:

> Hi sqlite experts,
>
> I am using sqlite back up API of the latest sqlite version. I find an
> issue that the target database seems not shrink when the source shrinks.
> ...
> Is it a bug, a known issue? I believe the size of the target database
> should be kept consistent with the source database. Otherwise, quite lots
> of disk space will be wasted in case source database shrinks



Not a bug: sqlite saves removed "pages" for later use. Use the VACUUM
command to tell it to get rid of them and shrink your file.


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] determining is-leap-year in sqlite

2016-05-09 Thread Stephan Beal
On Mon, May 9, 2016 at 7:00 PM, jungle Boogie 
wrote:

> On 8 May 2016 at 23:13, Stephan Beal  wrote:
> > On Mon, May 9, 2016 at 5:40 AM, Stephan Beal 
> wrote:
> >
> >> That suggests that the script is not consistently telling sqlite which
> TZ
> >> to use in all calculations. i will take a look at it as time
> >>
> >
> > just fyi: i can now reproduce the problem on my x64, where my days are
> > shifted 1 to the left. Not sure what's causing it, but probably won't be
> > able to look at it until next weekend :/. i apparently broke it at some
> > point without noticing.
>
>
> Not a problem. Thanks for checking into this and I'm happy to know
> this is not sqlite problem!


Something to try out, if you have a machine handy: change all of the
strftime date creation references and add " 23:59". i.e. move them to the
end of the day. That might offset any calculations being done with the
default(?) time of midnight (the _start_ of the day).


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] Good way for CEIL, or is there a better way

2016-05-09 Thread Stephan Beal
On Mon, May 9, 2016 at 1:52 PM, Cecil Westerhof 
wrote:

> ?But I want it to be possible for ?everyone? to use the application. People
> need to implement my function then. Or am I wrong about that?
>

fyi, ceil(3) is c99, not c89, which is likely the (or a) reason it's not
included in sqlite by default.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] determining is-leap-year in sqlite

2016-05-09 Thread Stephan Beal
On Mon, May 9, 2016 at 5:40 AM, Stephan Beal  wrote:

> That suggests that the script is not consistently telling sqlite which TZ
> to use in all calculations. i will take a look at it as time
>

just fyi: i can now reproduce the problem on my x64, where my days are
shifted 1 to the left. Not sure what's causing it, but probably won't be
able to look at it until next weekend :/. i apparently broke it at some
point without noticing.

On May 8, 2016 21:34, "jungle Boogie"  wrote:
>
>> On 8 May 2016 at 12:28, jungle Boogie  wrote:
>> > I'll set the TZ on the pi to match and see what happens.
>>
>> We're on to something!
>>
>> pi time:
>> $ date
>> Sun May  8 12:29:54 PDT 2016
>>
>> x86 time:
>> % date
>> Sun May  8 12:30:04 PDT 2016
>>
>>
>> They match with cal.sql now!
>> http://kopy.io/GbbDR
>>
>>
>> So no problem with your script unless you're not using UTC time!
>>
>>


[sqlite] determining is-leap-year in sqlite

2016-05-09 Thread Stephan Beal
That suggests that the script is not consistently telling sqlite which TZ
to use in all calculations. i will take a look at it as time allows.
Probably just need to be sure to consistently pass the final argument to
strftime().

- stephan
(Sent from a mobile device, possibly from bed. Please excuse brevity,
typos, and top-posting.)
On May 8, 2016 21:34, "jungle Boogie"  wrote:

> On 8 May 2016 at 12:28, jungle Boogie  wrote:
> > I'll set the TZ on the pi to match and see what happens.
>
> We're on to something!
>
> pi time:
> $ date
> Sun May  8 12:29:54 PDT 2016
>
> x86 time:
> % date
> Sun May  8 12:30:04 PDT 2016
>
>
> They match with cal.sql now!
> http://kopy.io/GbbDR
>
>
> So no problem with your script unless you're not using UTC time!
>
> --
> ---
> inum: 883510009027723
> sip: jungleboogie at sip2sip.info
> xmpp: jungle-boogie at jit.si
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


[sqlite] determining is-leap-year in sqlite

2016-05-08 Thread Stephan Beal
On Sun, May 8, 2016 at 10:53 AM, Stephan Beal  wrote:

> The system clock is correct on your x64 machine, i assume? (Even if it's
> wrong, that doesn't explain the days being shifted left by 1.)
>

One idea comes to mind: perhaps it doesn't consistently deal with timezones
everywhere, and you've got vastly different timezones set up on those
machines?

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] determining is-leap-year in sqlite

2016-05-08 Thread Stephan Beal
On Sun, May 8, 2016 at 2:14 AM, jungle Boogie 
wrote:

> (8) is on Saturday, that's correct for 2016.
>
> (7) is on Friday and in fact, all the days are shifted once.
>
> Both of those are a bit difficult to follow here so this link has both:
> http://kopy.io/NLViy
>
> What would cause the same exact cal.sql file obtained from your link
> be displayed differently on different architectures but the same
> sqlite3 version?
>
> Both sqlite3 versions were installed from trunk and configured with
> plain ./configure
>

Good morning!

i cannot for the life of me explain that. Very strange. My PC unfortunately
has 3.8.2 installed (from 2013, before CTEs were supported) and i haven't
got the dev tools installed to build a newer one, but will try it out
tomorrow on my work machine (which is Mint/Ubuntu x64 - the same machine
cal.sql was developed on).

The system clock is correct on your x64 machine, i assume? (Even if it's
wrong, that doesn't explain the days being shifted left by 1.)

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] strftime accepts an illegal time string

2016-05-05 Thread Stephan Beal
On Thu, May 5, 2016 at 10:08 AM, R Smith  wrote:

> seconds. Leap years themselves also have problems - the easiest check is
> to see if the year is divisible by 4 and then allow a 29th on Feb, but of
> course for the year 1900 this would have been wrong, but for 2000 this is
> right again, etc


i think this is easier: check if the year as 365 or 366 days:

sqlite> select strftime('%j', '2016-12-31');
366
sqlite> select strftime('%j', '2015-12-31');
365

with the usual caveats for dates in the far past.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] BUG?

2016-04-22 Thread Stephan Beal
On Fri, Apr 22, 2016 at 9:18 AM, Rowan Worth  wrote:

> On 22 April 2016 at 14:54, Stephan Beal  wrote:
> > but i beg to differ that that works in 100% of cases.
> >
>
> Lets see, for a 64-bit float we have 53 bits of significand. The number
> ...of ambiguity. Pretty sure your conversion issues are not the fault of
> the
> julian day format :)
>

that may be and i can't prove otherwise. i'm gonna drop the topic here
because i'm limited to 1-hand typing and this is getting physically painful
:/.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] BUG?

2016-04-22 Thread Stephan Beal
On Fri, Apr 22, 2016 at 8:43 AM, Clemens Ladisch  wrote:

> Stephan Beal wrote:
> > On Thu, Apr 21, 2016 at 4:12 PM, jrhgame  wrote:
> >> SELECT julianday('2016-04-15 12:10:10')  ==>2457494.00706
> >> SELECT datetime(2457494.00706)   ==>2016-04-15 12:10:09
> >
> > fwiw, i've done lots and lots of testing with round-trip conversions
> > between those two formats, and it cannot be done 100% reliably (at least
> on
> > consumer-grade hardware). There is always a minority percentage of cases
> > which round/truncate one second here or there.
>
> With enough precision, seconds can be handled just fine:
>

That's the operative term resp. limitation, yes.


>  sqlite> select julianday('2000-01-01 00:00:00');
> ...> select julianday('2000-01-01 00:00:01');
> ...> select julianday('2000-01-01 00:00:02');
>  2451544.5
>  2451544.50001157
>  2451544.50002315
>

but i beg to differ that that works in 100% of cases. My test inputs read
Julian timestamps from Fossil source repos and convert them back and forth
between ISO8601 using published algorithms for doing so (as opposed to
using sqlite for doing so). In such cases, a small minority (roughly
0.5-3%, but that's heavily platform-dependent (32-bit ARM performs more
poorly here)) exhibit 1-second truncation/rounding errors.


> You can get problems only if
> - you are not using enough precision, or
> - the number does not represent a full second, but some random point
>   somewhere in the middle between two whole seconds.
>

The latter is exactly the case here - fossil remembers the millisecond part
during commits, as sown on the "D" line here:

http://fossil-scm.org/index.html/artifact/4abf607937fac8e0

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] BUG?

2016-04-22 Thread Stephan Beal
On Thu, Apr 21, 2016 at 4:12 PM, jrhgame  wrote:

> SELECT julianday('2016-04-15 12:10:10')  ==>2457494.00706
> SELECT datetime(2457494.00706)   ==>2016-04-15 12:10:09
>

fwiw, i've done lots and lots of testing with round-trip conversions
between those two formats, and it cannot be done 100% reliably (at least on
consumer-grade hardware). There is always a minority percentage of cases
which round/truncate one second here or there. It's particularly ugly when
it happens on a day boundary. You may see that the percentage of
occurrences is higher on ARM platforms, compared to i64.

-- 
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] Expecting syntax error on delete

2016-04-10 Thread Stephan Beal
On Sat, Apr 9, 2016 at 5:20 PM, Richard Williams <
richard at roguewavelimited.com> wrote:

> I have a PHP program where I have the equivalent of the following code. The
> code was not deleting the expected rows ('abc' & 'def') because of the bad
> syntax. However the error did not throw an exception. Is this what I should
> expect?
>
> $p = new PDO('sqlite::memory:');
> $p->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
> $p->exec("create table a (a text)");
> $sql = "delete from a where a in (a in ('abc','def'))";
> $stat = $p->exec($sql);
>

('abc' & 'def') does not produce a syntax error:

sqlite> select 'abc' & 'def';
0

so your SQL was well-formed, just not what you wanted. Your IN(...)
effectively resolved to IN(0).

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] You may add sha256 checksum for files in the download page?

2016-04-02 Thread Stephan Beal
On Sat, Apr 2, 2016 at 7:13 PM, Simon Slavin  wrote:

>
> On 2 Apr 2016, at 11:35am, Pavel Volkov  wrote:
>
> > You may add sha256 checksum for files in the download page?
>
> SHA1 is so easy to crack now it might make sense to replace the SHA1
> checksums with SHA256.  Does anyone have a good reason why SHA1 is still
> needed ?
>

FWIW:

https://en.wikipedia.org/wiki/SHA-2

"Although (as of 2015) no example of a SHA-1 collision has been published
yet..."


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] SQLite with wall enabled what's wrong C demo

2016-03-24 Thread Stephan Beal
On Thu, Mar 24, 2016 at 11:55 AM, Domingo Alvarez Duarte <
sqlite-mail at dev.dadbiz.es> wrote:

> There is something wrong with the program or with sqlite3 ?
>
>


> rc = sqlite3_bind_text(stmt_insert, 1, session_id,
> sizeof(session_id)-1, NULL);
> rc = sqlite3_bind_text(stmt_insert, 2, data, sizeof(data)-1,
> NULL);
> rc = sqlite3_bind_text(stmt_insert, 3, ip_address,
> sizeof(ip_address)-1, NULL);
> rc = sqlite3_step(stmt_insert);
> rc = sqlite3_reset(stmt_insert);
>

You have not checked a single error code there. If you'll check those
codes, you may be able to find out immediately what the problem is.

A Golden Rule of C APIs is: if you ignore the result codes, the API may
ignore you.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] dump only data, change schema, reload

2016-03-23 Thread Stephan Beal
On Wed, Mar 23, 2016 at 10:50 AM, Luca Ferrari 
wrote:

> ...The problem is that .dump provides data and schema, while I'd like to
> have data only.
> Other commands like .clone and .backup works pretty much the same, as
> far as I understand.
>
> This leads me to either use awk/sed to manipulate the dump or to
> hard-code single select statements into the script to extract data.
> Is there any smarter way to dump only data in a loadable form?
>


Probably the simplest approach is something like (untested):

alter table original_table rename to foo; -- move the original table
create table original_table (...); -- w/ new schema
insert into original_table (a,b,c) select a,b,c from foo; -- assuming no
transformation needs to take place
drop table foo; -- though you'll probably want to keep the old copy "just
in case"


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] CAST STRING => INTEGER

2016-03-14 Thread Stephan Beal
On Mon, Mar 14, 2016 at 12:03 PM, Clemens Ladisch 
wrote:

> Cezary H. Noweta wrote:
> > Is there some more-or-less official list posted somewhere with things
> which is better not to be done?
>
> The documentation is quite clear that INTEGERs have 64 bits.  So trying
> to use integers above 9223372036854775807 is something that is better
> not to be done.
>

Also beware that some scripting languages only support 48 bits of integer
precision, so 64 bits may or may not be usable in any given scripting
language environment.


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] compile switches: SQLITE_OMIT_ATTACH & SQLITE_OMIT_VIRTUALTABLE

2016-03-09 Thread Stephan Beal
On Tue, Mar 8, 2016 at 2:47 PM, Gert Corthout 
wrote:

> hello,
> when I compile the amalgation with these compile
> switches:SQLITE_OMIT_VIRTUALTABLEorSQLITE_OMIT_VIRTUALTABLE
>

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

If any of these options are defined, then the same set of SQLITE_OMIT_*
options must also be defined when using the 'lemon' tool to generate the
parse.c file and when compiling the 'mkkeywordhash' tool which generates
the keywordhash.h file. Because of this, these options may only be used
when the library is built from canonical source, not from theamalgamation
<https://www.sqlite.org/amalgamation.html>. Some SQLITE_OMIT_* options
might work, or appear to work, when used with the amalgamation
<https://www.sqlite.org/amalgamation.html>. But this is not guaranteed. In
general, always compile from canonical sources in order to take advantage
of SQLITE_OMIT_* options.



-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] How does your sqlite script binding handle aggregate UDFs?

2016-03-06 Thread Stephan Beal
On Sat, Mar 5, 2016 at 9:22 PM, Stephan Beal  wrote:

> Aggregates are  _currently_ modeled as a single function which gets called
> just like normal function, but in the aggregate's "final" call the engine
> calls the aggregate function with no arguments (this is how the call knows
> it's the "final" one).
>

Follow-up, for anyone searching the archives later:

select myaggregate(*) from t;

indeed makes all calls to the aggregate without any arguments, so the
no-arguments heuristic to recognize the final call isn't useful. In my case
i'm solving it by setting a flag on the callback function instance itself
when making the final call, so that the callback can behave appropriately.

Thank you Richard and R. Smith for the confirmations!

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] How does your sqlite script binding handle aggregate UDFs?

2016-03-06 Thread Stephan Beal
On Sat, Mar 5, 2016 at 11:58 PM, Richard Hipp  wrote:

> On 3/5/16, Stephan Beal  wrote:
> > On Sat, Mar 5, 2016 at 10:43 PM, Domingo Alvarez Duarte <
> >
> > The scenario i'm concerned about is that sqlite calls my aggregate N
> times,
> > then an error is triggered elsewhere which keeps sqlite from making the
> > final() aggregate call.
>
> IIRC, SQLite always calls the final() function.  Even after an error.
>

Perfect :). Thank you!

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] How does your sqlite script binding handle aggregate UDFs?

2016-03-05 Thread Stephan Beal
On Sat, Mar 5, 2016 at 11:21 PM, Stephan Beal  wrote:

> On Sat, Mar 5, 2016 at 10:43 PM, Domingo Alvarez Duarte <
> sqlite-mail at dev.dadbiz.es> wrote:
>
>> Hello !
>>
>> There is an user pointer that you pass and you can get it back using
>> https://www.sqlite.org/c3ref/user_data.html !
>>
>
> i've got that, but that user data pointer is my callback function.
>

To clarify: function as in script-side Function.

In C code, all script-defined UDFs necessarily go through the same 1 (or 2,
for aggregates) C-side UDFs.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] How does your sqlite script binding handle aggregate UDFs?

2016-03-05 Thread Stephan Beal
On Sat, Mar 5, 2016 at 10:43 PM, Domingo Alvarez Duarte <
sqlite-mail at dev.dadbiz.es> wrote:

> Hello !
>
> There is an user pointer that you pass and you can get it back using
> https://www.sqlite.org/c3ref/user_data.html !
>

i've got that, but that user data pointer is my callback function. The
callback doesn't have a way to know if aggregation is just starting,
though, so it doesn't know (except in the final() call) that it can reset
its internal state.

The scenario i'm concerned about is that sqlite calls my aggregate N times,
then an error is triggered elsewhere which keeps sqlite from making the
final() aggregate call. Currently i reset my accumulation state in the
final() bits, but if final() is never called, then the _next_ time someone
calls the aggregate, it will still have accumulated state from the previous
attempt which failed partway through.


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] How does your sqlite script binding handle aggregate UDFs?

2016-03-05 Thread Stephan Beal
Hi, all,

this question is aimed at any of you who have experience adding
script-defined UDF support to sqlite/script bindings (regardless of the
scripting language). Everyone can tap delete now :).

"i've got this friend" who has an sqlite3/script-language binding which (as
of an hour or so ago) now allows sqlite UDFs to be created via script code,
e.g.:

myDb.createUDF('myfunc', function(a,b) { return a+b });

it also does aggregates, but i've got questions about those...

Aggregates are  _currently_ modeled as a single function which gets called
just like normal function, but in the aggregate's "final" call the engine
calls the aggregate function with no arguments (this is how the call knows
it's the "final" one). The final call does any last-minute work returns
whatever the accumulated value is. In pseudocode, such an aggregate
callback might look like:

function aggregateCallback() {
  if no arguments then
this is the final call. Return accumulated data.
  else
this is a "normal" call. Accumulate/calculate/whatever. Result is
ignored.
  end
}

i'm not aware of any aggregates which (in normal use) take no arguments, so
this "seems" kosher to me (i.e., i "don't think" the convention of passing
no arguments for the final call is going to bite me). However, i'm
envisioning corner cases involving resetting of the data accumulator
(assuming a SUM-like aggregator). Specifically: if sqlite calls the
aggregate's step() function once, and then is interrupted by an error
triggered from another function in the same statement, the final() call for
the aggregate will never(?) be made. i.e., the final() call cannot be
reliably used as a place to initialize the aggregate before the _next_
round of calls.

How are script bindings handling such situations? Where are they
initializing and resetting any "accumulator data" in their aggregates?

Any insights and suggestions would be appreciated.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] Random-access sequences

2016-03-01 Thread Stephan Beal
On Tue, Mar 1, 2016 at 12:59 PM, Matthias-Christian Ott 
wrote:

> Unfortunately, this limits the maximum number of elements that can ever
> be inserted during a table's life-time to 2^63 - 1. While this might be
> acceptable in some cases it is an artificial limitation.
>

Artificial, yes, but so is "64 bits." You will likely hit other limitations
far before getting anywhere near 2^63-1 insertions:

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

e.g. point #13:

*Maximum Number Of Rows In A Table*

The theoretical maximum number of rows in a table is 264 (18446744073709551616
or about 1.8e+19). This limit is unreachable since the maximum database
size of 140 terabytes will be reached first. A 140 terabytes database can
hold no more than approximately 1e+13 rows, and then only if there are no
indices and if each row contains very little data.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] MIN/MAX of column loses decltype

2016-02-22 Thread Stephan Beal
On Mon, Feb 22, 2016 at 7:15 PM, Eric Hill  wrote:

> to the data.  But SQLite doesn't really have any functions that manipulate
> Julian dates (as far as I can tell).  We have written our own SQL date
> functions anyway.  So maybe I can just avoid this issue entirely.
>

FWIW, strftime() uses them and the Fossil SCM (which hosts sqlite) uses
them:

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


-- 
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] User-defined SQL functions

2016-02-21 Thread Stephan Beal
On Sun, Feb 21, 2016 at 6:09 PM, Olivier Mascia  wrote:

> Dear all,
>
> Is it possible to implement a SQL function (
> https://www.sqlite.org/c3ref/create_function.html), which implementation
> would be able to return the same value for the duration of the current
> transaction?
>

This _might_ suffice for you, but maybe not: the SQLITE_DETERMINISTIC flag
for sqlite3_create_function().

Copied from the header file:


 ^The fourth parameter may optionally be ORed with [SQLITE_DETERMINISTIC]
** to signal that the function will always return the same result given
** the same inputs within a single SQL statement.  Most SQL functions are
** deterministic.  The built-in [random()] SQL function is an example of a
** function that is not deterministic.  The SQLite query planner is able to
** perform additional optimizations on deterministic functions, so use
** of the [SQLITE_DETERMINISTIC] flag is recommended where possible.

Note that it says within a single statement, not a transaction.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] determining is-leap-year in sqlite

2016-02-19 Thread Stephan Beal
On Fri, Feb 19, 2016 at 3:03 AM, Quan Yong Zhai  wrote:

> My SQLite cte exercise, the output looks like cal in Linux shell:
> ...
> 
>February 2016
> Su Mo Tu We Th Fr Sa
> 1  2  3  4  5  6
> 7  8  9 10 11 12 13
> 14 15 16 17 18 19 20
> 21 22 23 24 25 26 27
> 28 29
>

LOL! i needed about 6 hours (and 6 times the code) to do that!

Extremely impressive!

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] determining is-leap-year in sqlite

2016-02-19 Thread Stephan Beal
On Fri, Feb 19, 2016 at 1:53 AM, Stephan Beal  wrote:

> It can now optionally mark the current date (but this feature slowed it
> down from 'instant' to 'just under a second or so', possibly due to SQL
> inefficiencies on my part).
>

Trimming the list of years from 100 years to now +/-5 years brought it back
to 'instant'. :) The latest code is online.

-- 
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] determining is-leap-year in sqlite

2016-02-19 Thread Stephan Beal
On Fri, Feb 19, 2016 at 12:36 AM, k  wrote:

> On 18/02/2016 21:55, Stephan Beal wrote:
>
>>
>>> http://fossil.wanderinghorse.net/download/cal.sql
>>>
>>>
>> Excellent CTE query, thanks, but one question: the query uses
> group_concat() and the documentation says 'The order of the concatenated
> elements is arbitrary.'
>

Great question, and Keith answered it for both of us.

i've been tinkering (and just uploaded)...

It can now optionally mark the current date (but this feature slowed it
down from 'instant' to 'just under a second or so', possibly due to SQL
inefficiencies on my part). It also allows one to specify utc or localtime.
(Yes, it actually made a difference in the results for me at the time i
added that feature, which was why it was added.)


[stephan at host:~/tmp]$ sqlite3 < cal.sql
++
   Jan 2016
 Mo  Tu  We  Th  Fr  Sa  Su
  1   2   3
  4   5   6   7   8   9  10
 11  12  13  14  15  16  17
 18  19  20  21  22  23  24
 25  26  27  28  29  30  31
++

++
   Feb 2016
 Mo  Tu  We  Th  Fr  Sa  Su
  1   2   3   4   5   6   7
  8   9  10  11  12  13  14
 15  16  17  18 (19) 20  21
 22  23  24  25  26  27  28
 29
++

++
   Mar 2016
 Mo  Tu  We  Th  Fr  Sa  Su
  1   2   3   4   5   6
  7   8   9  10  11  12  13
 14  15  16  17  18  19  20
 21  22  23  24  25  26  27
 28  29  30  31
++



-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] determining is-leap-year in sqlite

2016-02-18 Thread Stephan Beal
On Thu, Feb 18, 2016 at 10:42 PM, Stephan Beal 
wrote:

> Here we go:
>
> http://fossil.wanderinghorse.net/download/cal.sql
>

sorry, one more: it was just updated with minor doc improvements and better
syntax conformance (i had used a lot of double-quotes simply out of recent
scripting habit).


-- 
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] determining is-leap-year in sqlite

2016-02-18 Thread Stephan Beal
On Thu, Feb 18, 2016 at 10:22 PM, Stephan Beal 
wrote:

> On Thu, Feb 18, 2016 at 10:19 PM, Richard Hipp  wrote:
>
>> On 2/18/16, Stephan Beal  wrote:
>> >
>> > Thanks again to all for the feedback and suggestions!
>> >
>>
>> After your talk, can we publish your calendar CTE as another example
>> in the SQLite documentation?
>>
>
> i would be humbled. No need to wait for the presentation (still no time
> slot scheduled - might be 4-6 weeks). i'll shoot it off to you once i've
> added some comments about what each part is for.
>

Here we go:

http://fossil.wanderinghorse.net/download/cal.sql

i'm not claiming this is a world-class solution, but considering how lowly
my CTE skills are, i'm quite pleased with myself :).

[stephan at host:~/tmp]$ sqlite3 < cal.sql
--
  Jan 2016
 Mo Tu We Th Fr Sa Su
  1  2  3
  4  5  6  7  8  9 10
 11 12 13 14 15 16 17
 18 19 20 21 22 23 24
 25 26 27 28 29 30 31

--
  Feb 2016
 Mo Tu We Th Fr Sa Su
  1  2  3  4  5  6  7
  8  9 10 11 12 13 14
 15 16 17 18 19 20 21
 22 23 24 25 26 27 28
 29



-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] determining is-leap-year in sqlite

2016-02-18 Thread Stephan Beal
On Thu, Feb 18, 2016 at 10:19 PM, Richard Hipp  wrote:

> On 2/18/16, Stephan Beal  wrote:
> >
> > Thanks again to all for the feedback and suggestions!
> >
>
> After your talk, can we publish your calendar CTE as another example
> in the SQLite documentation?
>

i would be humbled. No need to wait for the presentation (still no time
slot scheduled - might be 4-6 weeks). i'll shoot it off to you once i've
added some comments about what each part is for.

i got the last-line-truncated problem solved (missing a clause in a
group-by), so it currently looks like:

...
select str from strMonth sm
  where sm.year=2016 and sm.monthNum in (1,2,3)
;

[stephan at host:~/tmp]$ sqlite3 < cal.sql
--
  Jan 2016
  1  2  3
  4  5  6  7  8  9 10
 11 12 13 14 15 16 17
 18 19 20 21 22 23 24
 25 26 27 28 29 30 31

--
  Feb 2016
  1  2  3  4  5  6  7
  8  9 10 11 12 13 14
 15 16 17 18 19 20 21
 22 23 24 25 26 27 28
 29

--
  Mar 2016
 1  2  3  4  5  6
  7  8  9 10 11 12 13
 14 15 16 17 18 19 20
 21 22 23 24 25 26 27
 28 29 30 31


NOW to try to mark the current day... no... i'll leave that as an exercise
for the attendees!

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] determining is-leap-year in sqlite

2016-02-18 Thread Stephan Beal
On Thu, Feb 18, 2016 at 9:52 PM, Stephan Beal  wrote:

> --
>>   Feb 2016
>>   1  2  3  4  5  6  7
>>   8  9 10 11 12 13 14
>>  15 16 17 18 19 20 21
>>  22 23 24 25 26 27 28
>>
>> Thank you!!!
>>
>> Not half bad, if i may say so :).
>>
>
> Except that Feb. has 29 days this year (and my WITH-bits know that). Hmm.
>

Just a rendering problem. The very last line of the very last month, no
matter how many months i'm generating, gets sliced off and i'm not sure
why. Oh, well. Time to walk the dog.

Thanks again to all for the feedback and suggestions!

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] determining is-leap-year in sqlite

2016-02-18 Thread Stephan Beal
On Thu, Feb 18, 2016 at 9:50 PM, Stephan Beal  wrote:

> i could do with the \r, but CHAR(10) does indeed do the trick:
>

withOUT the \r...


>
> --
>   Feb 2016
>   1  2  3  4  5  6  7
>   8  9 10 11 12 13 14
>  15 16 17 18 19 20 21
>  22 23 24 25 26 27 28
>
> Thank you!!!
>
> Not half bad, if i may say so :).
>

Except that Feb. has 29 days this year (and my WITH-bits know that). Hmm.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] determining is-leap-year in sqlite

2016-02-18 Thread Stephan Beal
On Thu, Feb 18, 2016 at 9:42 PM, R Smith  wrote:

> Use the Mandelbrot set CTE for a cheat-sheet...
> CHAR(13)||CHAR(10)... etc.


Doh!

i could do with the \r, but CHAR(10) does indeed do the trick:

select str from strMonth
  where year=2016 and monthNum in (1,2)

looks like...

[stephan at host:~/tmp]$ sqlite3 < cal.sql
--
  Jan 2016
  1  2  3
  4  5  6  7  8  9 10
 11 12 13 14 15 16 17
 18 19 20 21 22 23 24
 25 26 27 28 29 30 31
--
  Feb 2016
  1  2  3  4  5  6  7
  8  9 10 11 12 13 14
 15 16 17 18 19 20 21
 22 23 24 25 26 27 28

Thank you!!!

Not half bad, if i may say so :).


i will post the complete solution (for a given definition of "solution")
once i've cleaned it up notably... and figure out how the last part of it
actually works. :/ Look for it over the weekend.

Thanks again!

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] determining is-leap-year in sqlite

2016-02-18 Thread Stephan Beal
On Thu, Feb 18, 2016 at 9:16 PM, Stephan Beal  wrote:

> Okay, i've hit a small stump and i'm looking for a hint without giving it
> away:
>
> January and February 2016:
>
> [stephan at host:~/tmp]$ sqlite3 < cal.sql
>   1  2  3
>   4  5  6  7  8  9 10
>  11 12 13 14 15 16 17
>  18 19 20 21 22 23 24
>  25 26 27 28 29 30 31
>


[stephan at host:~/tmp]$ sqlite3 < cal.sql
-\n  1  2  3
  4  5  6  7  8  9 10
 11 12 13 14 15 16 17
 18 19 20 21 22 23 24
 25 26 27 28 29 30 31
-\n  1  2  3  4  5  6  7
  8  9 10 11 12 13 14
 15 16 17 18 19 20 21
 22 23 24 25 26 27 28
 29

doh! How do i get newlines?

@Jose: good point. %4 would have sufficed for this purpose.

@R. Smith: i'm already storing the month names in a CTE:

...
monthData(name, monthNum, year, days) as (
 select 'Jan', 1, y.year, 31 from years y
 union all
 select 'Feb', 2, y.year,
 28 + (CAST(strftime("%j", y.year||"-12-31") AS INTEGER) % 365)
 from years y union all
 select 'Mar', 3, y.year, 31 from years y union all
 select 'Apr', 4, y.year, 30 from years y union all
 select 'May', 5, y.year, 31 from years y union all
 select 'Jun', 6, y.year, 30 from years y union all
 select 'Jul', 7, y.year, 31 from years y union all
 select 'Aug', 8, y.year, 31 from years y union all
 select 'Sep', 9, y.year, 30 from years y union all
 select 'Oct', 10, y.year, 31 from years y union all
 select 'Nov', 11, y.year, 30 from years y union all
 select 'Dec', 12, y.year, 31 from years y
),


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] determining is-leap-year in sqlite

2016-02-18 Thread Stephan Beal
On Thu, Feb 18, 2016 at 8:59 PM, R Smith  wrote:

> etc.
> Nice job on the calendar and good luck with the presentation!


Okay, i've hit a small stump and i'm looking for a hint without giving it
away:

January and February 2016:

[stephan at host:~/tmp]$ sqlite3 < cal.sql
  1  2  3
  4  5  6  7  8  9 10
 11 12 13 14 15 16 17
 18 19 20 21 22 23 24
 25 26 27 28 29 30 31
  1  2  3  4  5  6  7
  8  9 10 11 12 13 14
 15 16 17 18 19 20 21
 22 23 24 25 26 27 28
 29

What's missing, obviously, is the month separators/labels. Trying to figure
that out now.

The indentation wasn't half as problematic as i expected.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] determining is-leap-year in sqlite

2016-02-18 Thread Stephan Beal
On Thu, Feb 18, 2016 at 7:53 PM, R Smith  wrote:

> May I offer this CTE from the tutorials in SQLitespeed in case you have a
> Math library linked.
> (your math function names for cos(), sin() and degtorad() may differ):



> with graph(gWidth, aInc, gAngle, gCos, gCosA, gSin, gSinA) AS (
> SELECT 20, 10, -90, 0, 0, 0, 0
>   UNION ALL
> SELECT gWidth,aInc,gAngle+aInc,
>printf('%d',  round( cos( degtorad( gAngle +
> aInc ) ) * gWidth + gWidth + 1 ) ),
>printf('%d', gWidth * 2 + 2 - round( cos( degtorad( gAngle +
> aInc ) ) * gWidth + gWidth + 1 ) ),
>printf('%d',  round( sin( degtorad( gAngle +
> aInc ) ) * gWidth + gWidth + 1 ) ),
>printf('%d', gWidth * 2 + 2 - round( sin( degtorad( gAngle +
> aInc ) ) * gWidth + gWidth + 1 ) )
>   FROM graph
>  WHERE gAngle < 720
> )
> SELECT printf( '%4d', gAngle ) AS Angle,
>printf( '.%'||gCos||'s%'||gCosA||'s', '+', '.' ) AS Cosine,
>printf( '.%'||gSin||'s%'||gSinA||'s', '+', '.' ) AS Sine
>   FROM graph
>  WHERE gAngle >= 0;
>

Okay, that's going in the presentation.

HOLY COW!

Contrast with my modest:

...
select year, monthName, group_concat(dayOfMonth, ' ')
   from monthDays where year=2016
   group by monthNum
   order by monthNum;

year|monthName|group_concat(dayOfMonth, ' ')
2016|Jan|1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
26 27 28 29 30 31
2016|Feb|1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
26 27 28 29
2016|Mar|1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
26 27 28 29 30 31
2016|Apr|1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
26 27 28 29 30
2016|May|1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
26 27 28 29 30 31
2016|Jun|1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
26 27 28 29 30
2016|Jul|1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
26 27 28 29 30 31
2016|Aug|1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
26 27 28 29 30 31
2016|Sep|1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
26 27 28 29 30
2016|Oct|1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
26 27 28 29 30 31
2016|Nov|1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
26 27 28 29 30
2016|Dec|1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
26 27 28 29 30 31

Next to figure out indentation/line breaks based on the dayOfWeek (which i
have but isn't shown above).


> The first CTE sets up some parameters in the first 3 fields used to draw
> the graph - play with those parameters for fun.
>

i wouldn't even know what to do with them :/.


> (I hope the mail system don't mess up the format too much...)
>

Nope - came across loud and clear.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] determining is-leap-year in sqlite

2016-02-18 Thread Stephan Beal
On Thu, Feb 18, 2016 at 7:27 PM, R Smith  wrote:

>
>> While I won't spoil your calendar fun, I have to ask, why not simply use
> the functionality SQLite already has to know exactly which months has which
> days?
>

Good question: the presentation is specifically about CTEs and i want to
show some things which people don't normally consider SQL a solution for.
It's "purely academic." In fact, our work projects, almost exclusively,
Oracle, but, aside from Oracle just generally being a pain in my side,
sqlite's just a better option for my presentation environment (and the
visitors won't have to hear me swear like a sailor at Oracle SQL Developer).


> For reference, consider this simple CTE and its months output for the
> current year:
>
> WITH CAL(mthDays) AS (
> SELECT strftime('%Y','now')||'-02-01 00:00:00'
>   UNION ALL
> SELECT datetime(mthDays,'+1 month') FROM CAL LIMIT 12
> )
> SELECT date(mthDays,'-1 day') AS CalDate FROM CAL;
>

i've got similar functionality in place now, and have just collected the
day-of-week, so that i can calculate the display offsets and know where to
do line breaks.

(Btw: SQLite will work for dates below 1752 with a few caveats, the entire
> World didn't quite agree on dates before then - in fact, I'm not sure they
> do now!).


Every calendar known to man sucks rocks in some regard or other, so i'm not
gonna sweat it. This is just a demo, and i've got a few hours of budget
left on it, so i'm working on this as the finale. (The Mandelbrot CTE will
be first, just to kind of blow their minds, before we back way up and ease
into it.)

[stephan at host:~/tmp]$ sqlite3 < cal.sql
month|monthNum|day|dayOfWeek
Feb|2|1|1
Feb|2|2|2
Feb|2|3|3
Feb|2|4|4
Feb|2|5|5
Feb|2|6|6
Feb|2|7|7
Feb|2|8|1
Feb|2|9|2
Feb|2|10|3
Feb|2|11|4
Feb|2|12|5
Feb|2|13|6
Feb|2|14|7
Feb|2|15|1
Feb|2|16|2
Feb|2|17|3
Feb|2|18|4
Feb|2|19|5
Feb|2|20|6
Feb|2|21|7
Feb|2|22|1
Feb|2|23|2
Feb|2|24|3
Feb|2|25|4
Feb|2|26|5
Feb|2|27|6
Feb|2|28|7
Feb|2|29|1

(Note that i adjust day-of-week to Sunday=7 (instead of 0) because that's
just how we roll in Germany.)

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] determining is-leap-year in sqlite

2016-02-18 Thread Stephan Beal
On Thu, Feb 18, 2016 at 6:59 PM, Eric Rubin-Smith  wrote:

> >
> >
> >   select 2, 28 + (CAST(strftime("%j", c.year||"-12-31") AS INTEGER) %
> 365)
> >
>
> Here you assume that all years have either 365 or 366 days.  Would that it
> were so!
>
> Look at the year 1752 -- you may notice something odd happened that
> September. :-)
>

Yeah, i should have mentioned that i'm simplifying to the range of dates
"sometime within my lifetime." Anything else is irrelevant for my
presentation ;).

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] determining is-leap-year in sqlite

2016-02-18 Thread Stephan Beal
Hi, all,

i just found a useful trick i thought someone else might be able to use...

As part of a presentation i'm preparing to introduce colleagues to CTEs,
i'm attempting to build a calendar (with output similar to the Unix 'cal'
command). (Please no spoilers - let me figure it out!)

As part of that, i've of course got to determine if it's a leap year. My
first attempt was something like this snippet:

with
config(year,month) AS(
  select strftime("%Y"), strftime("%m")
),
daysPerMonth(month, days) as (
 select 1, 31
 union all
 select 2,
 case WHEN NOT c.year%4 AND c.year%100
 THEN 29
 ELSE CASE
  WHEN NOT c.year%4 AND NOT c.year%100 AND NOT c.year%400
  THEN 29
  ELSE 28
  END
 END
...

(Not 100% sure that's right, but the replacement solution makes it a moot
point.)

Then it occurred to me that strftime() can tell us the day-of-year, which
answers that question for us much more tersely because December has a fixed
number of days:

...
daysPerMonth(month, days) as (
 select 1, 31
 union all
 select 2,
 CASE WHEN '366'=strftime("%j", c.year||"-12-31") THEN 29 ELSE 28 END
 from config c
...
)

Note that string comparison is required, as +"2016" in sqlite does not
coerce a string to an integer like -"2016" does (i'm a bit surprised by
that, but expect it's an immutable compatibility constraint).

Similar reformulations could also work, taking care to cast the strftime
results to integers and performing a single math op:

  select 2, 28 + (CAST(strftime("%j", c.year||"-12-31") AS INTEGER) % 365)

(i think that's about as terse as i can get it.)

So far my calendar CTE outputs:

[stephan at host:~/tmp]$ sqlite3 < cal.sql
2016|1|31
2016|2|29
2016|3|31
2016|4|30
2016|5|31
2016|6|30
2016|7|31
2016|8|31
2016|9|30
2016|10|31
2016|11|30
2016|12|31

Obviously still lots to do here. (Again, _please_ don't post spoilers for
calendar CTE solutions (in this thread)!)

Have fun!

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] Possible error using length on UTF-8 characters

2016-02-17 Thread Stephan Beal
On Wed, Feb 17, 2016 at 2:59 PM, Glyn Jones  wrote:

> Agreed.
> The problem is that "length(id)" returns double the number of UTF-8
> characters, rather than the actual number input to the field using "insert".
>

According to the docs, i does that only if you've stored the data as a
BLOB, not TEXT. You need to confirm that you haven't stored the field as a
blob.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] Possible error using length on UTF-8 characters

2016-02-17 Thread Stephan Beal
On Wed, Feb 17, 2016 at 2:36 PM, Glyn Jones  wrote:

> The field is TEXT:
>
> sqlite> .schema operators
> CREATE TABLE operators (
> uid INTEGER PRIMARY KEY,
> id TEXT UNIQUE NOT NULL CHECK(length(id) BETWEEN 1 AND 20),
> name TEXT CHECK(length(trim(name)) > 0)
> );
>

FWIW, the _declared_ type means very little in sqlite - it can store _any_
type in that field, regardless of the declaration. You might want to add a
type check to your CHECK() constraint. i don't currently remember how to do
that, but someone on this list does and will likely answer very soon. (Yes,
i'm looking at you, Simon!)

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] Possible error using length on UTF-8 characters

2016-02-17 Thread Stephan Beal
On Wed, Feb 17, 2016 at 11:53 AM, Glyn Jones  wrote:

> My database is configured with encoding "UTF-8".
> I have a table with a constraint check for 20 characters on a column. This
> mostly works, but sometimes has some strange behaviour.
>
> One character in particular has caused a problem. The hex value is "C2AC"
> - the "NOT SIGN (U+00AC)".
> Hopefully the character will appear between the quotes "?".
>
> When I insert this character into the table, it will only allow ten
> characters to be input.
>
> If I do "select length(name) from myTable;" the result shows that each of
> the "C2AC" characters  is counted as two characters.
>
> I noticed that "C2" is a valid ANSI character, as is "AC", so wonder if
> the length() function is not dealing correctly with the range from
> 0x80-0xff.
>

Is your field TEXT or BLOB? The docs say they behave differently:

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

For a string value X, the length(X) function returns the number of
characters (not bytes) in X prior to the first NUL character. Since SQLite
strings do not normally contain NUL characters, the length(X) function will
usually return the total number of characters in the string X. For a blob
value X, length(X) returns the number of bytes in the blob.



-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] Unicode issue on windows consoles. Was: Version 3.11.0 beta

2016-02-11 Thread Stephan Beal
On Thu, Feb 11, 2016 at 4:46 PM, Olivier Mascia  wrote:

> The way I patched it works nicely for the interactivity, but more work
> needs to be done for handling the command-line itself properly (it always
> is ANSI - or Wide and not OEM-something or UTF-8). In addition my code will
> need deep focused review and probably some refactoring for best integration
> within the coding guidelines of SQLite.
>

@Richard: reminder: Fossil has tons of code for the conversions between the
Windows console and rest-of-world. It "should" be trivial to port it over.


> On the other hand Dominique and Clemens pointed to interesting other paths
> to handle this, with possible bumps on the road.  I personally would like
> to get time to experiment with that and see where it goes.
>

You might want to look at:

https://www.fossil-scm.org/index.html/finfo?name=src/utf8.c

It also takes care of some weird Appleness cases.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] Fossil sqlite clone problem

2016-02-09 Thread Stephan Beal
On Tue, Feb 9, 2016 at 6:16 PM, Domingo Alvarez Duarte <
sqlite-mail at dev.dadbiz.es> wrote:

> Thanks for the answer !
>
> Although it seems a bit weird this behavior of get stuck on one specific
> branch.
>

You might have done 'update tip' at some point when that was the newest.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] Fossil sqlite clone problem

2016-02-09 Thread Stephan Beal
On Tue, Feb 9, 2016 at 5:38 PM, Stephan Beal  wrote:

> fossil pull --verily
>
> in the past that's helped people reporting problems about a repo silently
> failing to pull past a certain version.
>

OTOH, if that tag is checked out, which your output indicates is the case,
then 'update' will apply no changes because that's the last commit in that
branch. You'll need to 'fossil update trunk' once to get back to the trunk.


https://www.sqlite.org/src/timeline?c=2016-01-30+14:53:06

-- 
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] Fossil sqlite clone problem

2016-02-09 Thread Stephan Beal
On Tue, Feb 9, 2016 at 5:20 PM, Domingo Alvarez Duarte <
sqlite-mail at dev.dadbiz.es> wrote:

> I frequently follow the updates of fossil and sqlite but I noticed that my
> sqlite clone stop updating the source tree at this commit :
>

Can you try doing this one from the sqlite tree:

fossil pull --verily

in the past that's helped people reporting problems about a repo silently
failing to pull past a certain version.


-- 
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] How to enter Unicode character?

2016-02-08 Thread Stephan Beal
On Mon, Feb 8, 2016 at 4:27 PM, Igor Korot  wrote:

> At the moment I'm using Win 8.1.
> And the sqlite3.exe shell tool.
>
> So if I go with option 2, I will copy the character and the paste it
> into the command.
> Something like :
>
> sqlite3> CREATE TABLE abc();
>
> right?
>

That's what Unix would do. So... on Windows, probably not ;).


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] How to enter Unicode character?

2016-02-08 Thread Stephan Beal
On Mon, Feb 8, 2016 at 4:17 PM, Igor Korot  wrote:

>  Hi, ALL,
> I live in US and therefore have an English-based laptop with an
> English-based keyboard.
>
> I am also a programmer and would like to test what happen if I have a
> SQLite table
> which contains a Unicode character.
>

It depends entirely on your environment/shell. You have multiple options:

- add German a secondary keyboard layout and switch keys as needed. Doh -
your keyboard is US, so you won't know where the 'sharp s' is :/. (My
keyboard is physically German but it's mapped to a US layout.)

- Google for "utf8 character tables" and copy/paste them.


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] json_group_array

2016-02-05 Thread Stephan Beal
On Fri, Feb 5, 2016 at 11:23 AM, J Decker  wrote:

> -
> var array = [1,2,,3];
> console.log( JSON.stringify( array ) );
> -
> outut : [1,2,null,3]
> ...
> So seems like having null in array for JSON is perfectly expected.
>

See also:

http://stackoverflow.com/questions/30585552/how-to-represent-an-array-with-empty-elements-in-json

tl;dr: "jsonlint" also says empty array elements aren't allowed.


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] json1 not escaping CRLF characters

2016-02-04 Thread Stephan Beal
On Thu, Feb 4, 2016 at 11:04 AM, Dominique Devienne 
wrote:

> \" represents the quotation mark character (U+0022).
> \\ represents the reverse solidus character (U+005C).
> \/ represents the solidus character (U+002F).
> ...
> The wording above doesn't seem to "require" these characters to be escaped.
> Only double-quote, and backlash need to be.
> My own JSON serializer does, but the spec doesn't require it apparently. So
> json1 seems OK and to-spec...
>

FWIW, i once asked Doug Crockford about must-vs-may here and he responded:

--
    From: Douglas Crockford 
To: Stephan Beal 
Subject: Re: Is escaping of forward slashes required?

It is allowed, not required. It is allowed so that JSON can be
safely
embedded in HTML, which can freak out when seeing strings containing
" Hello, Jsonites,
>
> i'm a bit confused on a small grammatic detail of JSON:
>
> if i'm reading the grammar chart on http://www.json.org/
correctly,
> forward slashes (/) are supposed to be escaped in JSON. However,
the
> JSON class provided with my browsers (Chrome and FF, both of
which i
> assume are fairly standards/RFC-compliant) do not escape such
characters.
>
> Is backslash-escaping forward slashes required? If so, what is the
> justification for it? (i ask because i find it unnecessary and
hard to
> look at.)


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] sqldiff.c : 2 benign warnings in 64 bits builds

2016-01-26 Thread Stephan Beal
On Tue, Jan 26, 2016 at 6:40 PM, Scott Robison 
wrote:

> On Tue, Jan 26, 2016 at 8:21 AM, Stephan Beal 
> wrote:
> > fwiw, in case this matters: size_t has an unspecified size and it's not
> in
> > C89. It's defined by C99 in stddef.h
> >
>
> size_t (and ptrdiff_t) are both in C89/C90 in stddef.h. Really ptrdiff_t is
> what one wants for the difference between two pointers. Their size is
> platform specified.
>

Indeed, my apologies for the misinformation - i had misinterpreted my
source as being exclusive to c99.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] sqldiff.c : 2 benign warnings in 64 bits builds

2016-01-26 Thread Stephan Beal
On Tue, Jan 26, 2016 at 3:47 PM, J Decker  wrote:

> should be (size_t) instead of (int) though... since size_t will retain
> the precision... and then back propagate the change to the function
> return type and the things receiving the return... then you don't need
> the cast anyway.
>

fwiw, in case this matters: size_t has an unspecified size and it's not in
C89. It's defined by C99 in stddef.h

-- 
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] Bug: Successfully committed transaction rolled back after power failure

2016-01-25 Thread Stephan Beal
On Mon, Jan 25, 2016 at 5:08 PM, Richard Hipp  wrote:

> On 1/25/16, Stephen Chrzanowski  wrote:
> >
> > You also have to look at balance across many millions (or is it
> billions?)
> > of devices out there that use SQLite for their primary operations.
>
> Billions and billions.
>

https://en.wikipedia.org/wiki/Billions_and_Billions


> I don't think it is even that serious.  This problem is that if you
> (for example) set a new bookmark on your browser just as the cat is
> tripping over the power cord, then after reboot the bookmark
> disappears.  The bookmark database is still completely intact - it
> just went backwards in time a little.
>

Or, alternately, it _never went forward_ in time.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] Get X number of random integer numbers between A and B

2016-01-22 Thread Stephan Beal
On Fri, Jan 22, 2016 at 12:22 PM, Stephan Beal 
wrote:

>
>
> On Fri, Jan 22, 2016 at 12:11 PM, Bart Smissaert  > wrote:
>
>> Say I want 1 random numbers between 100 and 1000 how can I do that
>> without
>> selecting from a table?
>> I know I can do:
>> select abs(random() %(1000 - 100)) + 100 as rnd from TableWith1Rows
>> but there must be a better way.
>>
>>
> Maybe not perfect, but this seems to work...
>
> sqlite> with conf(max) as (select 10), rnd(n, x) as (select abs(random()
> %(1000 - 100)) + 100, 1 union all select abs(random() %(1000 - 100)) + 100,
> x+1 from rnd where x<(select max from conf)) select * from rnd;
>

Another variant which moves all the configurable bits up one level:

sqlite> with conf(max,slack) as (select 20, 2), rnd(n, x) as (select
abs(random() %(max - slack)) + slack, 1 from conf union all select
abs(random() %(max - slack)) + slack, x+1 from rnd, conf where x<conf.max)
select * from rnd;
6|1
3|2
10|3
18|4
12|5
5|6
2|7
2|8
11|9
18|10
16|11
7|12
9|13
13|14
10|15
3|16
17|17
2|18
17|19
19|20

To get the range from your original description, swap out max/slack with
1000/100, but if i'm not mistaken it will returns the range inclusive range
[0,999].

It also works as a view:

sqlite> create view rng10 as with conf(max,slack) as (select 10, 0), rnd(n,
x) as (select abs(random() %(max - slack)) + slack, 1 from conf union all
select abs(random() %(max - slack)) + slack, x+1 from rnd, conf where
x<conf.max) select * from rnd;
sqlite> select * from rng10;
5|1
0|2
3|3
2|4
4|5
8|6
3|7
1|8
1|9
1|10
sqlite> select * from rng10;
6|1
5|2
0|3
1|4
5|5
3|6
3|7
4|8
5|9
1|10

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] Get X number of random integer numbers between A and B

2016-01-22 Thread Stephan Beal
On Fri, Jan 22, 2016 at 12:11 PM, Bart Smissaert 
wrote:

> Say I want 1 random numbers between 100 and 1000 how can I do that
> without
> selecting from a table?
> I know I can do:
> select abs(random() %(1000 - 100)) + 100 as rnd from TableWith1Rows
> but there must be a better way.
>
>
Maybe not perfect, but this seems to work...

sqlite> with conf(max) as (select 10), rnd(n, x) as (select abs(random()
%(1000 - 100)) + 100, 1 union all select abs(random() %(1000 - 100)) + 100,
x+1 from rnd where x<(select max from conf)) select * from rnd;
668|1
301|2
619|3
380|4
412|5
263|6
563|7
877|8
573|9
468|10

just swap out the 'conf' part with 1000.



-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] .read bug

2016-01-21 Thread Stephan Beal
The problem is that you are using unusual quotes. Use only standard
double-quotes for identifiers and single-quotes for strings. Your example
uses "fancy" quotes commonly seen in word processors.

- stephan
Sent from a mobile device, possibly from bed. Please excuse brevity, typos,
and top-posting.
On Jan 21, 2016 14:18, "DONALD LEUNG"  wrote:

> hello,
>
> .read name.sql has a bug for mac os x 10.10 for the latest edition of
> SQLITE on Jan 20th
>
>
> I can confirm that I have created table C.
>
>
> INPUT
>
> From Script.sql in home folder with database.db file
>
> INSERT INTO "C? (Names) VALUES (?Al?);
> INSERT INTO "C? (Names) VALUES (?Ale?);
>
>
>
> OUTPUT:
>
> copying and pasting into sqlite3 error
>
> Error: near line 1: no such table: ?C?
> Error: near line 2: no such column: ?Al?
> Error: near line 3: no such column: ?Ale?
> Error: near line 4: no such column: ?Andy?
> Error: near line 5: near "?": syntax error
> Error: near line 6: no such column: ?Anti'
> Error: near line 7: no such column: ?Atina?
> Error: near line 8: no such column: ?Armen?
> Error: near line 9: no such column: ?Austy?
> Error: near line 10: no such column: ?Azer?
> Error: near line 11:
> Error: incomplete SQL: INSERT INTO "C? (Names) VALUES (?Candy?);
>
>
>
> ?
> Another thing is when I copy and paste from OS X sqlite3, the script:
> INSERT INTO ?C? (Names) VALUE (?names?);
>
> the paste looks turns out different, and becomes INSERT INTO ?C? (Names)
> VALUE  );?names? (
>
> Please tell me what to do and how to fix.
>
>
> d
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


[sqlite] Database Corrupt While Disk Full

2016-01-14 Thread Stephan Beal
On Thu, Jan 14, 2016 at 2:09 PM, Stephan Beal  wrote:

> FULL means the drive is full. Most apps can't do much about that. It
> generally needs to be resolved by user action - freeing up space.
>

Alternately, FULL can mean that the current VFS cannot allocate space, even
though it's underlying storage might have some space. So, in theory, the
VFS (if any) used by your device might be restricting the amount of space
the app can use.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] Database Corrupt While Disk Full

2016-01-14 Thread Stephan Beal
On Thu, Jan 14, 2016 at 2:02 PM, sanhua.zh  wrote:

> I don?t mean to be rude. BUT, I can?t agree with your opinion, Simon.
>
>
> 1. Not all other codes except SQLITE_OK, SQLITE_ROW, SQLITE_DONE should be
> treated as fatal errors.
>   As an example, SQLITE_BUSY indicates that this op is temporarily failed,
> but it can be done later. (Note that sometimes you should not retry
> forever.)
>

Simon's fundamentally right, though. The cases you mention here are not
ERROR codes - they're result codes which indicate varying types of success
(or recoverable errors). The errors you ignored early on are not (for most
cases) recoverable errors. If you continue to use an sqlite3 handle after
its API has returned a true error code, you're invoking Undefined Behaviour.

Aside from that, all OSes i've worked with tend to fail in strange ways
when their disks are out of space.

2. Quit while get error is also not a great enough idea.


sqlite has told you "something bad has happened." If you continue, you do
so at your own risk. Weird things happen as various apps and daemons
compete for that last byte of drive space.


> Because not all pragram is a command line tool. It can be a user-oriented
> application instead of a developer-oriented tool. Users don?t wish to meet
> a crash.
>

Pop up a dialog saying, "I/O error!" and _then_ quit. That's better than
the app corrupting their data.


> So, find out how SQLITE_FULL leading to SQLITE_CORRUPT, and fix it or
> avoid it (if it can?t be fixed) might be the better solution.
>

FULL means the drive is full. Most apps can't do much about that. It
generally needs to be resolved by user action - freeing up space.

CORRUPT means sqlite cannot work with it - there is no generic recovery
strategy aside from throwing the DB away and building it anew.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] Database Corrupt While Disk Full

2016-01-14 Thread Stephan Beal
On Thu, Jan 14, 2016 at 10:58 AM, sanhua.zh  wrote:

> Through the error code timeline, it shows that much of SQLITE_FULL,
> SQLITE_IOERR, SQLITE_CANTOPEN happened before SQLITE_CORRUPT. Database
> might be in an obscure state while disk is full, then it corrupt in some
> unknown reason.
>
>
> As you said, disk full might corrupt the database. Can you teach me a
> little more in detail ? I haven?t found the relative code in SQLite source
> code.
>

FWIW, the reason for the corruption is not unknown: your app continued to
use sqlite after (apparently) ignoring 3 different error codes (all of them
serious). That misuse is what corrupted it.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] hard links and SQLite

2016-01-11 Thread Stephan Beal
On Mon, Jan 11, 2016 at 7:57 PM, Stephan Beal  wrote:

> On Mon, Jan 11, 2016 at 7:55 PM, Warren Young  wrote:
>
>> On POSIX systems, you can securely create a temp file that only your user
>> can see via the mkstemp(3) C library call.  SQLite will happily open the
>> resulting 0-byte file, allowing you to create your schema inside it.  Then
>> when the file is set up, you can move it into the desired location and
>> change its file modes so that the other processes can open it.
>>
>> There must be an equivalent of mkstemp() on Windows, doubtless taking 3
>> times as many parameters and with a function name 4 times as long. :)
>>
>
> sqlite exposes the functionality of fetching a temp file name using its
> mechanism, but i don't recall at the moment how it's done. A quick google
> isn't revealing it but i recall using it but finding out that it doesn't
> work with the :memory: VFS.
>

https://www.sqlite.org/c3ref/file_control.html
http://sqlite-users.sqlite.narkive.com/oehps0E9/proper-use-of-sqlite3-file-control

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] hard links and SQLite

2016-01-11 Thread Stephan Beal
On Mon, Jan 11, 2016 at 7:55 PM, Warren Young  wrote:

> On POSIX systems, you can securely create a temp file that only your user
> can see via the mkstemp(3) C library call.  SQLite will happily open the
> resulting 0-byte file, allowing you to create your schema inside it.  Then
> when the file is set up, you can move it into the desired location and
> change its file modes so that the other processes can open it.
>
> There must be an equivalent of mkstemp() on Windows, doubtless taking 3
> times as many parameters and with a function name 4 times as long. :)
>

sqlite exposes the functionality of fetching a temp file name using its
mechanism, but i don't recall at the moment how it's done. A quick google
isn't revealing it but i recall using it but finding out that it doesn't
work with the :memory: VFS.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] Can SQLite know from the statement string if it is row producing or not?

2016-01-09 Thread Stephan Beal
On Sat, Jan 9, 2016 at 5:57 PM, Simon Slavin  wrote:

>
> On 9 Jan 2016, at 1:18pm, Bart Smissaert  wrote:
>
> >> but only after at least one sqlite3_step() occurred.
> >
> > It will work before any step, but indeed the statement needs to be
> prepared.
> > I need to know before stepping, but it can do that.
>
> For a SELECT which returns no rows I presume it returns the number of
> columns asked for.


It does. i use this in a db abstraction layer to fetch column names without
needing to fetch data.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] batch or one by one?

2015-12-17 Thread Stephan Beal
On Thu, Dec 17, 2015 at 8:04 AM, ??? <2004wqg2008 at 163.com> wrote:

> Testing shows that sqlite3_get_table is faster than sqlite3_prepare_v2 and
> sqlite3_step together.  In fact.
>
>
And uses, on average, much more memory, as it stores all rows for the query
results in the result table. If your results have 10 rows, that method will
use, abstractly speaking, 10x as much memory. prepare/step allows code to
have a more or less constant memory usage, independent of the number of
rows in the result set.

The very first line of the get_table documentation says:

This is a legacy interface that is preserved for backwards compatibility.
Use of this interface is not recommended.


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] Very Strange and Interesting Problem

2015-12-14 Thread Stephan Beal
On Mon, Dec 14, 2015 at 11:47 AM, Stephan Beal 
wrote:

> On Mon, Dec 14, 2015 at 11:21 AM, ??? <2004wqg2008 at 163.com> wrote:
>
>> hi, every one.
>>  Here is a very strange and interesting problem.
>>  I used the following SQL to create the table teacher.
>>  CREATE TABLE techer(poiId INTEGER NOT NULL PRIMARY KEY,...
>
>

> 1) if you do not alias column names using "AS" then the name you get from
>> the db driver is UNDEFINED. It might call all of them "foo" and still be
>> legal. The ONLY way to guaranty the names is to use "AS" to give them an
>> explicit name.
>>
>
>

> 2) i'm guessing that you are using an abstraction layer which is changing
> the returned name of your rowid.
>

i overlooked that you explicitly declared a rowid replacement. That is the
reason for what you are seeing. The link from Quan explains it in more
detail.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] Very Strange and Interesting Problem

2015-12-14 Thread Stephan Beal
On Mon, Dec 14, 2015 at 11:21 AM, ??? <2004wqg2008 at 163.com> wrote:

> hi, every one.
>  Here is a very strange and interesting problem.
>  I used the following SQL to create the table teacher.
>  CREATE TABLE techer(poiId INTEGER NOT NULL PRIMARY KEY,
> versionId INTEGER NOT NULL,
> regionId INTEGER ,
> postalCode TEXT ,
> phone TEXT ,
> attrBitMask INTEGER ,
> attributeBlob BLOB)
>
>  and  then I used the following SQL,
>  select rowid,*  from teacher.So the strange and interesting
> problem happen.
>  the name of the rowid column change to poiId, and the name of the
> poiId become poiId_1. But the two columns have the same values.
>
>  Why the rowid change is column name?
>  Is the problem reasonable ?  How do you think about this problem? why?
>  I  am looking forward to hearing from you.
>

Two answers come to mind:

1) if you do not alias column names using "AS" then the name you get from
the db driver is UNDEFINED. It might call all of them "foo" and still be
legal. The ONLY way to guaranty the names is to use "AS" to give them an
explicit name.

2) i'm guessing that you are using an abstraction layer which is changing
the returned name of your rowid.


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] Why Corrupted DB File Cause No Error?

2015-12-02 Thread Stephan Beal
On Wed, Dec 2, 2015 at 12:56 PM, sanhua.zh  wrote:

> I guess so too.
> But, It can be happen in real scene.
>

A part of data may be lost by a low chance accident in the application
> lifecycle. It would be very hard to find out this problem and fix it
> because of causing no error.
>


It's a hypothetical problem. It doesn't happen with correct usage, and
sqlite cannot protect users from all possible misuse.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] Why Corrupted DB File Cause No Error?

2015-12-02 Thread Stephan Beal
On Wed, Dec 2, 2015 at 11:59 AM, sanhua.zh  wrote:

> Why it does not show error, some thing like?Error: database disk image is
> malformed?? Is it a bug in SQLite?
>

because you didn't corrupt a part it actually read. Imagine if you have a
20GB db and you expect it to report such errors when you open the db. It
would have to read the whole db to figure that out, slowing sqlite to a
crawl. Since most dbs are not corrupt, it would be slowest for the average
case and faster for the error case (since it must stop reading on the first
error).

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] attempt at output with thousands separator via extension

2015-11-23 Thread Stephan Beal
On Mon, Nov 23, 2015 at 1:57 PM, Bruce Hohl  wrote:

> www.sqlite.org/loadext.html states that: "Loadable extensions are C-code."
>  Can someone confirm this please.
>

pedantically speaking: the _entry point_ for the extension is C. The
implementation may be in any language.


> If I figure out some clever I will share for the benefit of other shell
> junkies that like neat easily readable numeric output - all 6 of us :)
>

i think you mean all 6,0 of you ;).


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] Retrieving the table info fails

2015-11-16 Thread Stephan Beal
On Mon, Nov 16, 2015 at 6:42 PM, Stephan Beal  wrote:

> On Mon, Nov 16, 2015 at 6:11 PM, Igor Korot  wrote:
>
>> The variables referenced are defined as "std::string" and the code is in
>> C++.
>>
>
> the std::string(char const *) constructor does  not, last time i checked,
> accept a NULL value. You will need to pass it "" in that case.
>
> [stephan at host:~/tmp]$ cat foo.cpp
> #include 
>
> int main(){
> std::string s(0);
> return 0;
> }
>

Minor clarification: what you're doing is using the copy constructor, which
also doesn't like NULL:

[stephan at host:~/tmp]$ cat foo.cpp
int main(){
//std::string s(0);
std::string s = 0;
return 0;
}

[stephan at host:~/tmp]$ gcc -o foo foo.cpp -lstdc++

[stephan at host:~/tmp]$ ./foo
terminate called after throwing an instance of 'std::logic_error'
  what():  basic_string::_S_construct null not valid
Aborted


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] Retrieving the table info fails

2015-11-16 Thread Stephan Beal
On Mon, Nov 16, 2015 at 6:11 PM, Igor Korot  wrote:

> The variables referenced are defined as "std::string" and the code is in
> C++.
>

the std::string(char const *) constructor does  not, last time i checked,
accept a NULL value. You will need to pass it "" in that case.

[stephan at host:~/tmp]$ cat foo.cpp
#include 

int main(){
std::string s(0);
return 0;
}

[stephan at host:~/tmp]$ gcc -o foo foo.cpp -lstdc++
[stephan at host:~/tmp]$ ./foo
terminate called after throwing an instance of 'std::logic_error'
  what():  basic_string::_S_construct null not valid
Aborted

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] [AGAIN] SQLite on network share

2015-11-13 Thread Stephan Beal
On Fri, Nov 13, 2015 at 11:15 PM, A. Mannini 
wrote:

> Yes I use it in other contests but, as written in another message, in
> need a serverless solutions.
>

A shared filesystem _is_ a network service! Since they have  a system
sharing a drive, they can just as easily install MySQL on it and completely
bypass the file-sharing problems and corruption which _will_ happen if you
try to use sqlite3 on a shared network filesystem.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] Array or set type or some other indexable data?

2015-11-12 Thread Stephan Beal
On Thu, Nov 12, 2015 at 6:44 PM, J Decker  wrote:

> So... I guess something like this works; other than the triplication
> of the initial path string. works for read-only access... doesn't
> return where it failed (although can with leaf == 0 )
>

You can use multiple WITHs to get rid of some of that duplication, moving
the duplicated values into "upper" WITHs.

something like (untested):

with jackpot(v) as (
  select './DEFAULT/jackpot_sign/sack/PSI/Frame border/Height'
), -- don't remember if comma is needed between WITHs


> with option (option_id,path,rpath, leaf) as (\
> select option_id   \
> ,j.v  as path\
> , substr( j.v\
> , instr( j.v, '/' ) + 1 ) as rpath \
> , 0 as leaf \
>  from option4_map \
>
, jackpot j

>  join option4_name on
> option4_map.name_id=option4_name.name_id   \
>
...

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] A little light reading

2015-11-08 Thread Stephan Beal
On Sun, Nov 8, 2015 at 5:11 PM, John McKown 
wrote:

> I'm not a developer. So I guess that it's my ignorance as to why a program
> would be confused by the string value of "null" or any variant thereof.


Consider this XML snippet, which very likely passes through some of the
apps Mr. Null has been processed by (or failed to be processed by, as the
case may be):

Null

i've seen careless apps which would consider that to be a "program NULL" as
opposed to the string "Null".

I do understand looking for a string of length 0.
>

depending on the language/environment, an empty string is of type _string_
with a length of 0, whereas null is of a separate type called null. More
commonly, null/NULL is generic placeholder which can be used together with
references of any concrete type, but (when used with a string-typed
reference) never has a length property (whereas an empty string does). In
type-flexible languages, one typically cannot have a "null string," but
instead can have _either_ a string or a null value. In C++, one can have a
string-typed pointer to NULL, which is basically (because it keeps its data
type) a "null string," but still doesn't have a length (which is subtly
different than having a length of 0, even though the semantics are
identical for many purposes).

i can see why some systems have a problem with "Null," but in order to have
such problems i would argue that someone wasn't paying attention in the
design and/or implementation.

That poor guy.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] field name in UDF

2015-11-07 Thread Stephan Beal
On Fri, Nov 6, 2015 at 6:50 PM, Nelson, Erik - 2 <
erik.l.nelson at bankofamerica.com> wrote:

> I have a user-defined function something like
>
> void quarter_sqlite3(sqlite3_context *context, int argc, sqlite3_value
> **argv);
>
> for each sqlite3_value being passed in, it would sometimes be helpful to
> have the associated field (if any) that the value is associated with.
>
> Is there any way to retrieve that?
>

An idea came to mind, but i'm not sure if it's usable for your case (it's
only potentially useful if the queries are static, not entered by end
users): UDFs are variadic, so you could pass an optional 2nd parameter with
any information as a string to the final parameter, e.g.:

  select quarter(t.a, 't.a'), quarter(4,'four') from t;

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] field name in UDF

2015-11-06 Thread Stephan Beal
On Fri, Nov 6, 2015 at 7:04 PM, Nelson, Erik - 2 <
erik.l.nelson at bankofamerica.com> wrote:

> select quarter(t1.a) from t1  ;
>
> I might hope to get 'a' or 't1.a'.  Any ideas?
>

UDFs (in any scripting environment) don't get that level of info. They
only get passed the values resolved by the surrounding evaluation engine.

By the same token (no pun intended), in JavaScript:

var x = 3;
quarter(x);

gets only the number 3, not any information about where that 3 comes from
or how it was derived.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] field name in UDF

2015-11-06 Thread Stephan Beal
On Fri, Nov 6, 2015 at 6:50 PM, Nelson, Erik - 2 <
erik.l.nelson at bankofamerica.com> wrote:

> I have a user-defined function something like
>
> void quarter_sqlite3(sqlite3_context *context, int argc, sqlite3_value
> **argv);
>
> for each sqlite3_value being passed in, it would sometimes be helpful to
> have the associated field (if any) that the value is associated with.
>
> Is there any way to retrieve that?
>

UDFs receive expanded/evaluated values, not fields:

  select quarter(t1.a+t2.a+t3.a+3.0) from t1, t2, t3...;

what field would you expect to get there?

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] Non-transitive numeric equality

2015-11-05 Thread Stephan Beal
On Thu, Nov 5, 2015 at 3:36 PM, Zsb?n Ambrus  wrote:

> It seems that equality of numeric values isn't transitive, when both
> integers and reals are involved.


See this really, really, really, really long thread on that topic from a
couple weeks ago for far, far, far more information than you could possible
want on the reason:

http://www.mail-archive.com/sqlite-users at 
mailinglists.sqlite.org/msg04466.html


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] Simple Math Question

2015-10-22 Thread Stephan Beal
On Thu, Oct 22, 2015 at 9:45 PM, Rousselot, Richard A <
Richard.A.Rousselot at centurylink.com> wrote:

> Doing the following math, why is it that the results are not all returning
> "yes"?
>
> SELECT
> (9.2+7.9+0+4.0+2.6+1.3),
> case when (9.2+7.9+0+4.0+2.6+1.3)=25.0 then "yes" else
> "no" end,
> (9.2+7.8+0+3.0+1.3+1.7),
> case when (9.2+7.8+0+3.0+1.3+1.7)=23.0 then "yes" else
> "no" end,
> (9.2+7.9+0+1.0+1.3+1.6),
> case when (9.2+7.9+0+1.0+1.3+1.6)=21.0 then "yes" else
> "no" end
> FROM
> sometable;
>

Try the same with integers as you'll likely see different results.
Floating-point math is fraught with problems when it comes to expecting
exact results at a specific precision.

See: http://floating-point-gui.de/

the first example of which demonstrates the problem you are seeing.


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] sqlite 3.8.11 - binary size

2015-10-22 Thread Stephan Beal
> We carefully monitor the size of the compiled SQLite binary.  A graph
> of that size is shown at
>
>  https://www.sqlite.org/binary-size.jpg
>
> The 627.4KB for 3.8.11 is within reason, depending on what compiler
> you are using.  But SQLite has *never* been as small as 44.8 KB.  Is
> that a typo?

Dynamically linked, perhaps?

- stephan
Sent from a mobile device, possibly from bed. Please excuse brevity, typos,
and top-posting.


[sqlite] SQLite list user phishing Alexa

2015-10-18 Thread Stephan Beal
On Sun, Oct 18, 2015 at 12:30 PM, Richard Hipp  wrote:

> On 10/18/15, Stephan Beal  wrote:
> > It didn't appear to come directly from the list - i suspect someone is
> > scraping the ML archives.
> >
>
> Are the messages you are receiving passing through the sqlite.org
> server at any point?
>

Not that i see. Here's the full header:

Delivered-To: sgbeal at gmail.com
Received: by 10.129.53.66 with SMTP id c63csp742959ywa;
Sat, 17 Oct 2015 13:58:15 -0700 (PDT)
X-Received: by 10.68.247.106 with SMTP id yd10mr24815358pbc.105.1445115495204;
Sat, 17 Oct 2015 13:58:15 -0700 (PDT)
Return-Path: 
Received: from server.nsadatemail.com (server.nsadatemail.com. [103.226.179.51])
by mx.google.com with ESMTPS id ln5si329888pab.101.2015.10.17.13.58.12
for 
(version=TLSv1.2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128);
Sat, 17 Oct 2015 13:58:15 -0700 (PDT)
Received-SPF: pass (google.com: domain of alexa at nsadatemail.com
designates 103.226.179.51 as permitted sender)
client-ip=103.226.179.51;
Authentication-Results: mx.google.com;
   spf=pass (google.com: domain of alexa at nsadatemail.com
designates 103.226.179.51 as permitted sender)
smtp.mailfrom=alexa at nsadatemail.com;
   dkim=pass header.i=@nsadatemail.com
DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed;
d=nsadatemail.com; s=default; 
h=Content-Type:MIME-Version:Message-Id:Subject:
Date:To:From; bh=rIWNoTNlgOfRHoT0fqR6oX/lurido9IJdL739NXjuT0=; 
b=DQveZQ8vJsWf

MUXh30/uiz18PGm6gMz2qZIVfbVLWeivTpzJdAAis607LEySDyZGpv4B8K6SgeS7SHvEsL+JTxCnH

5Nsr9CHtq3dGckNlqA+L+HyIXOtVgbQVGfkbB71qpAWfyWCIN/97gk/dqi3aksfxtiqaszdTweG2t

vcFjv2tDSbaBHg3iSa/6xnMVayf/KFt1NlHDQCZU09Yvip3kKI/OBMDssCOd6JxB7/L0pWMQzrChU

V4jvL8fJ3jM9SRvu6S60mBTR6/C1t7E/Z7FwJMq/4OU/K4CyEXBIn+o4puI3r42p5bSBBZQel1SO+
Hzdly+pkXR/V2+O4DvcEzw==;
Received: from 74-50-117-59.static.hvvc.us ([74.50.117.59]:53544)
by server.nsadatemail.com with esmtpsa 
(TLSv1.2:DHE-RSA-AES256-GCM-SHA384:256)
(Exim 4.86)
(envelope-from )
id 1ZnYYA-0005Ke-BS
for sgbeal at googlemail.com; Sat, 17 Oct 2015 16:58:09 -0400
From: Alexa <al...@nsadatemail.com>
To: sgbeal at googlemail.com
Date: Sat, 17 Oct 2015 16:57:49 -0400
Subject: Re: Re: [sqlite] Sqlite good on Windows XP but very very slow on
Windows Seven
Message-Id: <25QH1QHREWT4.9LIKTLY2LHOB3 at workhorse>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="=-uWxy/UCma2/WlXlqAK3eAQ=="
X-AntiAbuse: This header was added to track abuse, please include it
with any abuse report
X-AntiAbuse: Primary Hostname - server.nsadatemail.com
X-AntiAbuse: Original Domain - googlemail.com
X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12]
X-AntiAbuse: Sender Address Domain - nsadatemail.com
X-Get-Message-Sender-Via: server.nsadatemail.com: authenticated_id:
alexa at nsadatemail.com
X-Authenticated-Sender: server.nsadatemail.com: alexa at nsadatemail.com
X-Source:
X-Source-Args:
X-Source-Dir:



(trivia: interestingly, i uses the gmail.com domain for my account, which i
don't use because when i got my gmail account, google wasn't allowed to use
the name gmail in Germany because another company owned the name. Now i
logistically can't switch to gmail.com because so many of my online
accounts are tied to googlemail.com instead of gmail.com.)

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] SQLite list user phishing Alexa

2015-10-18 Thread Stephan Beal
On Sun, Oct 18, 2015 at 12:08 PM, Richard Hipp  wrote:

> I've gotten several.  There is no such subscriber on the mailing list.
> I've asked Mike to look into the matter, but he hasn't found anything
> yet.
>

It didn't appear to come directly from the list - i suspect someone is
scraping the ML archives.

-- 
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] SQLite list user phishing Alexa

2015-10-18 Thread Stephan Beal
On Sun, Oct 18, 2015 at 8:57 AM, Darren Duncan 
wrote:

> This just happened to me as well.
>

me as well, within minutes of responding to the post mentioned here:


> and it looks like a phishing attempt; it had the email subject "Re: Re:
> [sqlite] Sqlite good on Windows XP but very very slow on Windows Seven", a
> post I replied to today.
>

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] Sqlite good on Windows XP but very very slow on Windows Seven

2015-10-17 Thread Stephan Beal
On Sat, Oct 17, 2015 at 9:53 PM, Lucas Ratusznei Fonseca <
lucas.ratusznei at gmail.com> wrote:

> Hi all,
>
> I am using sqlite for years with my software on Windows XP, no more than 1
> or 2 milliseconds per transaction (insert), so speed has never been a
> concern. Until now.
> I had to migrate my system to Windows Seven recently, I am still doing
> tests and stuff. It happens that some processes became very slow. Digging
> in the source code, I found out that Sqlite transactions now take about 120
> milliseconds, which is unacceptable for me.
>

Another hypotheses: if you are on the same hardware which was running XP,
the hardware might simply be too old to perform well (for anything) on Win7.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] Odd download file names

2015-09-30 Thread Stephan Beal
On Wed, Sep 30, 2015 at 3:02 AM, Stephen Chrzanowski 
wrote:

> Yes, I did run [fossil ui], which launched a web browser to localhost/
> 127.0.0.1.
>
> I didn't know about showsql.  That'll absolutely help.
>

FYI: on the CLI the equivalent is:

fossil timeline [options] --sqltrace

that option works with any fossil CLI commands.

-- 
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] Any database unique ID across multiple connections ?

2015-09-24 Thread Stephan Beal
On Thu, Sep 24, 2015 at 11:42 AM, ALBERT Aur?lien <
aurelien.albert at alyotech.fr> wrote:

>  (for example, if one day I need the same about ":memory:" databases, bad
> things are gonna to happen)
>

Every instance of a :memory: db is a unique instance, so you cannot have
multiple connections to a single :memory: db.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


[sqlite] I don't understand why I get "cannot start a transaction within a transaction"

2015-08-31 Thread Stephan Beal
On Mon, Aug 31, 2015 at 4:50 AM, Nicolas J?ger 
wrote:

> I'm starting to use transaction in my C++ code to delete some entries
> (tags) in my db. during execution, the first transaction (each
> transaction is contained in one string), like :
>
> BEGIN TRANSACTION;
> DELETE FROM TAGS WHERE NAME = 'loki';
> DELETE FROM TAGSMAP WHERE COLLECTION_ID = '3' AND TAG_id = '54';
> COMMIT;
>
> is executed by running `sqlite3_prepare_v2()` and the `sqlite3_stmt`
> returns `SQLITE_DONE`. So I consider the transaction made and closed.
> but if I looked in the db I still see the row/entry for 'loki'
>

Nope - you have only run the BEGIN part of the transaction. prepare()
prepares only one single statement, not multiples (you have 4 statements in
your SQL). Thus when you try to run another transaction, that BEGIN is
still open.



-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf


  1   2   3   4   5   6   7   >